一. 外鍵的變種: (*********************************************************)
1. 唯一索引:
create table t5(
id int,
num int,
unique(num)
)engine=Innodb charset=utf8;
作用:
num列的值不能重復
加速查找
create table t6(
id int,
num int,
unique(id, num)
)engine=Innodb charset=utf8;
聯合唯一索引作用:
num列和id列的值不能重復
加速查找
create table t6(
id int,
num int,
unique(id, num......)
)engine=Innodb charset=utf8;
2. 一對多:
部門表:
id depart_name
1 公關部
2 公共部
3 保安部
員工表:
id name age depart_id(外鍵)
1 lxxx 12 2
2 xxxx 13 1
3 xxxx 13 2
3. 一對一:
用戶表:
id name age
1 zekai 23
2 eagon 34
3 lxxx 45
4 owen 83
博客表:
id url user_id (外鍵 + 唯一約束)
1 /linhaifeng 2
2 /zekai 1
3 /lxxx 3
4 /lxxx 4
4. 多對多:
用戶表:
id name phone
1 root1 1234
2 root2 1235
3 root3 1236
4 root4 1237
5 root5 1238
6 root6 1239
7 root7 1240
8 root8 1241
主機表:
id hostname
1 c1.com
2 c2.com
3 c3.com
4 c4.com
5 c5.com
為了方便查詢, 用戶下面有多少臺主機以及某一個主機上有多少個用戶, 我們需要新建第三張表:
user2host:
id userid hostid
1 1 1
2 1 2
3 1 3
4 2 4
5 2 5
6 3 2
7 3 4
創建的時候, userid 和 hostid 必須是外鍵, 然后聯合唯一索引 unique(userid, hostid)
Django orm 也會設計
二. 數據行的操作:
增:
insert into 表名 (列名1, 列名2,) values(值1, 值2);
insert into 表名 (列名1, 列名2,) values(值1, 值2),(值1,值2),(值n,值n);
insert into 表名 (列名1, 列名2,) select 列名1, 列名2 from 表名;
刪除:
delete from 表名;
delete from 表名 where id > 10
delete from 表名 where id < 10
delete from 表名 where id <= 10
delete from 表名 where id >= 10
delete from 表名 where id != 10
delete from 表名 where id = 10 and name='xxx'; and : 并且 兩個條件都必須要成立
delete from 表名 where id = 10 or name='xxx'; or : 或者 只要滿足一個條件成立
修改:
update 表名 set name='zekai', age=23 where id > 10;
查詢:
基本:
select * from 表名;
select name , age from 表名;
高級:
a. where 條件查詢:
select * from 表名 where id=10;
select * from 表名 where id >10 and id<15;
select * from 表名 where id > 10;
!= : 不等與
>= <=
between and: 閉區間
select * from t4 where id between 9 and 12;
in: 在某一個集合中
select * from t4 where id in (9,10,11....);
select * from t4 where id in (select id from t3 where id between 2 and 4)
是可以這樣使用的, 但是不建議大家使用;
b. 通配符:
alex
select * from 表 where name like 'ale%' - ale開頭的所有（多個字符串）
select * from 表 where name like 'ale_' - ale開頭的所有（一個字符）
Python 4、 c. 限制取幾條:
select * from 表名 limit 索引偏移量, 取出多少條數據;
select * from t3 limit 0, 10; 第一頁
select * from t3 limit 10, 10; 第二頁
page = input('page:')
page 索引偏移量 數據量(offset)
1 0 10
2 10 10
3 20 10
4 30 10
page (page-1)*offset offset
分頁核心SQL:
select * from t3 limit (page-1)*offset, offset;
d. 排序:
order by
降序:
select * from t4 order by 列名 desc; descending
升序:
select * from t4 order by 列名 asc; ascending
多列:
create table t7(
id int auto_increment primary key,
num int not null default 0,
age int not null default 0
)charset=utf8;
insert into t7 (num, age) values (2, 12),(3,13),(4, 12);
select * from t4 order by num desc, name asc;
如果前一列的值相等的話, 會按照后一列的值進行進一步的排序.
e. 分組
select age, 聚合函數(count(num)/sum(num)/max(num)/min(num)/avg(num)) from 表名 group by 列名;
select age, avg(num) from t7 group by age;
select age, count(num) from t7 group by age;
select age, count(num) as cnt from t7 group by age; 顯示別名 as
having的二次刪選:
select age, count(num) as cnt from t7 group by age having cnt>1;
where 和 having的區別:
1). having與where類似,可篩選數據
2). where針對表中的列發揮作用,查詢數據
3). having針對查詢結果中的列發揮作用,二次篩選數據, 和group by配合使用
f. 連表操作
select * from userinfo, department; (笛卡爾積)
select * from userinfo, department where userinfo.depart_id=department.id;
左連接:
select * from userinfo left join department on userinfo.depart_id=department.id;
左邊的表全部顯示, 右邊沒有用到不顯示
右連接:
select * from userinfo right join department on userinfo.depart_id=department.id;
右邊的表全部顯示, 左邊沒關聯的用null表示
內連接:
左右兩邊的數據都會顯示
ps:
a.只需要記住左連接 left join
b.可以連接多張表 通過某一個特定的條件
注意查詢的順序:
select name,sum(score) from 表 where id > 10 group by score having age> 12 order by age desc limit 2, 10
