一、表的約束條件
1、約束條件與資料型別的寬度一樣，都是可選引數
作用：用于保證資料的完整性和一致性
2、主鍵primary key是innodb存盤引擎組織資料的依據，innodb稱之為索引（索引是一種樹狀結構）組織表，一張表中必須有且只有一個主鍵
ps: 無 primary key欄位 ，無not null+unique
就會自動生成一個隱藏欄位，
所以建立表的時候要有id欄位，不為空且唯一的結構建立主鍵
作用：加速查詢，表結構，表資料檔案
# not null default create table t1(x int not null); insert into t1 values(); create table t2(x int not null default 111); insert into t2 values(); # unique # 單列唯一 create table t3(name varchar(10) unique); insert into t3 values("egon"); insert into t3 values("tom"); mysql> insert into t3 values("egon"); ERROR 1062 (23000): Duplicate entry 'egon' for key 'name' # 聯合唯一 create table server( id int, name varchar(10), ip varchar(15), port int, unique(ip,port), unique(name) ); insert into server values (1,"web1","10.10.0.11",8080); insert into server values (2,"web2","10.10.0.11",8081); mysql> insert into server values(4,"web4","10.10.0.11",8081); ERROR 1062 (23000): Duplicate entry '10.10.0.11-8081' for key 'ip' mysql> # not null 和unique的化學反應=>會被識別成表的主鍵 create table t4(id int,name varchar(10) not null unique); create table t5(id int,name varchar(10) unique); # 主鍵primary key # 特點 # 1、主鍵的約束效果是not null+unique # 2、innodb表有且只有一個主鍵，但是該主鍵可以是聯合主鍵 create table t6( id int primary key auto_increment, name varchar(5) ); insert into t6(name) values ("egon"), ("tom"), ("to1"), ("to2"); # 聯合主鍵（了解） create table t7( id int, name varchar(5), primary key(id,name) );
二、表之間的三種關系
多對一
關聯方式：foreign key
多對多
關聯方式：foreign key+一張新的表
一對一
關聯方式：foreign key+unique
ps: foreign key 限制表與表之間關系
# 引入 # 先創建被關聯表 create table dep( id int primary key auto_increment, name varchar(6), comment varchar(30) ); # 再創建關聯表 create table emp( id int primary key auto_increment, name varchar(10), gender varchar(5), dep_id int, foreign key(dep_id) references dep(id) on delete cascade on update cascade ); # 先往被關聯表插入資料 insert into dep(id,name) values (1,'技術部'), (2,'人力資源部'), (3,'銷售部'); # 先往關聯表插入資料 insert into emp(name,gender,dep_id) values ('egon',"male",1), ('alex1',"male",2), ('alex2',"male",2), ('alex3',"male",2), ('李坦克',"male",3), ('劉飛機',"male",3), ('張火箭',"male",3), ('林子彈',"male",3), ('加特林',"male",3) ; # 多對一 # 多對多 create table author( id int primary key auto_increment, name varchar(10) ); create table book( id int primary key auto_increment, name varchar(16) ); create table author2book( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on delete cascade on update cascade, foreign key(book_id) references book(id) on delete cascade on update cascade ); # 一對一 create table customer( id int primary key auto_increment, name varchar(16), phone char(11) ); create table student( id int primary key auto_increment, class varchar(10), course varchar(16), c_id int unique, foreign key(c_id) references customer(id) on delete cascade on update cascade );
三、表記錄相關操作
增加記錄
修改記錄
洗掉記錄
查詢記錄（單表查詢，多表聯合）
單表查詢語法(一定要注意執行順序！)
select distinct 欄位1,欄位2,欄位3,... from 表名
where 過濾條件
group by 分組的條件
having 篩選條件
order by 排序欄位
limit n;
# 插入 mysql> create table user(name varchar(16),password varchar(10)); Query OK, 0 rows affected (0.29 sec) mysql> mysql> insert into user select user,password from mysql.user; # 洗掉 delete from 表 where 條件; # 更新 update 表 set 欄位=值 where 條件; # 單表查詢語法 select distinct 欄位1,欄位2,欄位3,... from 表名 where 過濾條件 group by 分組的條件 having 篩選條件 order by 排序欄位 limit n; # 簡單查詢 select name,sex from emp; select name as 名字,sex 性別 from emp; select * from emp; # 避免重復（針對的是記錄） select distinct post from emp; # 進行四則運算 select name as 名字,salary*12 as 年薪 from emp; # concat()拼接記錄的內容 select name ,concat(salary*12,"$") from emp; select name ,concat("annual_salary",':',salary*12) as 年薪 from emp; select name ,concat("annual_salary",':',salary*12,':','$') as 年薪 from emp; select name ,concat_ws(":","annual_salary",salary*12,'$') as 年薪 from emp; select ( case when name="egon" then name when name="alex" then concat(name,"_dsb") else concat(name,"_sb") end ) as 名字 from emp; ===========================================where select * from emp where id >= 3 and id <= 5; select * from emp where id between 3 and 5; select * from emp where id not between 3 and 5; select * from emp where id=3 or id=5 or id=7; select * from emp where id in (3,5,7); select * from emp where id not in (3,5,7); select * from emp where id=3 or id=5 or id=7; select * from emp where name like 'jin%'; select * from emp where name like 'jin___'; select * from emp where name regexp 'n$'; mysql> select * from emp where post_comment is not null; Empty set (0.00 sec) mysql> update emp set post_comment='' where id=3; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from emp where post_comment is not null; +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | | 8300.00 | 401 | 1 | +----+---------+------+-----+------------+---------+--------------+---------+--------+-----------+ 1 row in set (0.00 sec) mysql> select * from emp where name="丫丫"; select * from emp where name regexp "丫$"; select * from emp where name like "丫_"; select * from emp where name regexp "^程"; select hex(name) from t4 where hex(name) regexp 'e[4-9][0-9a-f]{4}'; ===========================================group by 分完組之后只能看到分組的欄位以及聚合的結果 max() min() avg() sum() count() select depart_id,count(id),avg(salary),max(age),min(salary),sum(salary) from emp group by depart_id; # 每個部門都有多少個人 select depart_id,count(id) from emp group by depart_id; # 每個職位男生的平均薪資 select post,avg(salary) from emp where sex="male" group by post; select post, group_concat(name) from emp group by post; select post, group_concat(name) from emp where sex="male" group by post; ===========================================having # having與where本質區別就是在于having是在分組之后發生過濾，可以使用聚合函式 mysql> select max(salary) from emp where max(salary) > 100000; ERROR 1111 (HY000): Invalid use of group function mysql> select max(salary) from emp having max(salary) > 100000; +-------------+ | max(salary) | +-------------+ | 1000000.31 | +-------------+ 1 row in set (0.00 sec) mysql> # 找出來男生平均薪資大于3000的職位 select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 8000; ===========================================order by排序 select * from emp order by salary; select * from emp order by salary desc; select * from emp order by age,id desc; ps:asc升序（默認為升序，一般就不要寫了）
desc降序 select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000; mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16000.043333 | | teacher | 175650.051667 | | xxxxxxxxx駐沙河辦事處外交大使 | 7300.330000 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> select post,avg(salary) from emp where sex="male" group by post having avg(salary) > 3000 order by avg(salary); +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | xxxxxxxxx駐沙河辦事處外交大使 | 7300.330000 | | operation | 16000.043333 | | teacher | 175650.051667 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> select post,avg(salary) as v from emp where sex="male" group by post having avg(salary) > 3000 order by v; +-----------------------------------------+---------------+ | post | v | +-----------------------------------------+---------------+ | xxxxxxxxx駐沙河辦事處外交大使 | 7300.330000 | | operation | 16000.043333 | | teacher | 175650.051667 | +-----------------------------------------+---------------+ 3 rows in set (0.00 sec) mysql> 分頁顯示：（比如淘寶里商品頁面顯示，可以分好多頁） select * from emp limit 0,5; select * from emp limit 5,5; select * from emp limit 10,5; select * from emp limit 15,5; select * from emp limit 20,5;
四、
五、
---44---
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/1949.html
標籤：Python