今日內容概要
- 約束條件
- default默認值
- unique唯一
- primary key 主鍵
- auto_increment自增
- 總結
- default默認值
- 表與表之間建關系
- 外鍵
- 表關系
- 一對多關系
- 多對多關系
- 一對一關系
- 總結
- 修改表
- 復制表
今日內容詳細
約束條件
default默認值
""" # 補充知識點，插入資料的時候，可以指定欄位順序 create table t1( id int, name char(16) ); insert into t1(name,id） values('jason',1); create table t2( id int, name char(16) not null, gender enum('male','female','others') default 'male' ); mysql> desc t2; +--------+-------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(16) | NO | | NULL | | | gender | enum('male','female','other') | YES | | male | | +--------+-------------------------------+------+-----+---------+-------+ 3 rows in set (0.05 sec) insert into t2(id,name) values(1,'jason'); insert into t2 values(2,'egon','female'); mysql> select * from t2; +------+------------------+--------+ | id | name | gender | +------+------------------+--------+ | 1 | jason | male | | 2 | egon | female | +------+------------------+--------+ 2 rows in set (0.00 sec) """
unique唯一
""" # 單列唯一 create table t3( id int unique, name char(16) ); insert into t3 values(1,'jason'),(1,'egon'); insert into t3 values(1,'jason'),(2,'egon'); mysql> desc t3; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.05 sec) mysql> insert into t3 values(1,'jason'),(1,'egon'); ERROR 1062 (23000): Duplicate entry '1' for key 'id' # 聯合唯一 ip 和 port 單個都可以重復，但是加在一起必須唯一 id ip port 1 1 1 2 1 2 3 2 1 4 3 1 create table t4( id int, ip char(16), port int, unique(ip,port) ); mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | ip | char(16) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.05 sec) insert into t4 values(1,'127.0.0.1',8080); insert into t4 values(2,'127.0.0.1',8081); insert into t4 values(3,'127.0.0.2',8080); mysql> insert into t4 values(3,'127.0.0.2',8080); Query OK, 1 row affected (0.05 sec) 前面三條執行成功 insert into t4 values(4,'127.0.0.1',8080); mysql> insert into t4 values(4,'127.0.0.1',8080); ERROR 1062 (23000): Duplicate entry '127.0.0.1 -8080' for key 'ip' """
primary key唯一
""" # 1.單單從約束效果上來看 primary key 等價于 not null + unique 非空且唯一！！！！ create table t5(id int primary key); mysql> desc t5; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.04 sec) insert into t5 values(null); mysql> insert into t5 values(null); ERROR 1048 (23000): Column 'id' cannot be null insert into t5 values(1),(2); insert into t5 values(3),(3); mysql> insert into t5 values(3),(3); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' #2.它除了有約束效果之外，還是存盤引擎Innodb存盤引擎組織資料的依據 Innodb 存盤引擎規定在創建表的時候，必須要有primary key 因為它類似于數的目錄，能夠幫助提升查詢效率，并且也是建表的依據 2.1一張表中有且只能有一個主鍵，如果你沒有設定主鍵，那么會從上往下搜索知道遇到一個非空且唯一的欄位將它主動升為主鍵 create table t6( id int, name char(16), age int not null unique, addr char(32) not null unique ); mysql> desc t6; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(16) | YES | | NULL | | | age | int(11) | NO | PRI | NULL | | | addr | char(32) | NO | UNI | NULL | | +-------+----------+------+-----+---------+-------+ 4 rows in set (0.06 sec) 2.2 如果表中既沒有設定主鍵也沒有其他任何非空且唯一的欄位，那么Innodb會采用自己內部提供的一個
隱藏欄位作為主鍵，隱藏意味著你無法使用它，就無法提升查詢速度 2.3 一張表中通常都應該有一個主鍵欄位，并且通常將id/uid/sid欄位作為主鍵 #單個欄位主鍵 create table t5( id int primary key, name char(16) ); #聯合主鍵（多個欄位聯合起來作為表的主鍵，本質還是一個主鍵） create table t7( id int, ip char(16), port int, primary key(ip,port) ); mysql> desc t7; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | ip | char(16) | NO | PRI | NULL | | | port | int(11) | NO | PRI | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 也就意味著，以后我們在創建表的時候，id欄位一定要加primary key """
auto_increment自增
""" # 當編號特別多的時候，人為的去維護太麻煩 create table t8( id int primary key auto_increment, name char(16) ); mysql> desc t8; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(16) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.05 sec) insert into t8(name) values('jason'),('egon'),('tank'); mysql> insert into t8(name) values('jason'),('egon'),('tank'); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t8; +----+------------------+ | id | name | +----+------------------+ | 1 | jason | | 2 | egon | | 3 | tank | +----+------------------+ 3 rows in set (0.00 sec) #注意！！！！！！ auto_increment通常都是加在key上，不能給普通欄位加 create table t9( id int primary key auto_increment, name char(16), cid int auto_increment ); mysql> create table t9( -> id int primary key auto_increment, -> name char(16), -> cid int auto_increment -> ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column
and it must be defined as a key """
總結
以后再創建表的時候：id欄位要這樣定義
id int primary key auto_increment
補充
""" delete from 在洗掉表資料的時候，主鍵的自增不會停止 mysql> delete from t8; Query OK, 3 rows affected (0.05 sec) mysql> insert into t8(name) values('egon'); Query OK, 1 row affected (0.05 sec) mysql> select * from t8; +----+------------------+ | id | name | +----+------------------+ | 4 | egon | +----+------------------+ 1 row in set (0.00 sec) truncate t8 清空資料并且重置主鍵 mysql> truncate t8; Query OK, 0 rows affected (0.08 sec) mysql> select * from t8; Empty set (0.00 sec) mysql> insert into t8(name) values('egon'); Query OK, 1 row affected (0.05 sec) mysql> select * from t8; +----+------------------+ | id | name | +----+------------------+ | 1 | egon | +----+------------------+ 1 row in set (0.00 sec) """
表與表之間建關系
""" 定義一張員工表，表中有很多欄位 id name gender dep_name dep_desc 員工表 id emp_name emp_gender dep_name dep_desc 1 jason male 外交部 漂泊游蕩 2 egon female 教學部 教書育人 3 tank male 教學部 教書育人 4 kevin male 教學部 教書育人 5 oscar female 技術部 技術能力有限公司 # 1 該表的組織結構不是很清晰（可忽略） # 2 浪費硬碟空間，資料重復（可忽略） # 3 資料的擴展性差，無法修改（不可忽略） #如何優化 上述問題就類似于你將所有的代碼都寫在了同一個py檔案中 將員工表拆分：員工表 部門表 員工表 id emp_name emp_gender 1 jason male 2 egon female 3 tank male 4 kevin male 5 oscar female 部門表 id dep_name dep_desc 1 外交部 漂泊游蕩 2 教學部 教書育人 3 技術部 技術能力有限公司 拆分好之后，隨之而來的問題是：兩張表之間沒有了聯系 應該給員工表加一個欄位 對應的部門應該是id 員工表 外鍵 id emp_name emp_gender dep_id 1 jason male 1 2 egon female 2 3 tank male 2 4 kevin male 2 5 oscar female 3 """
外鍵
外鍵就是幫助我們建立表與表之間的關系的
foreign key
表關系
表與表之間最多有四種關系
一對多
多對多
一對一
沒有關系
一對多關系
""" 在判斷表與表關系的時候，一定要換位思考，分別站在兩張表的角度思考 以員工表和部門表為例 先站在員工表的角度：員工表里的一個員工能否對應多個部門 不能！！！！（這個時候還不能得出兩張表的關系） 再站在部門表的角度：部門表里的一個部門能否有多個員工 能！！！！！ 結論：員工表和部門表是單向的一對多關系 ，那么部門表和員工表就構成了“一對多”的表關系 foreign key： 1.一對多的表關系，外鍵建在多的一方 2.在創建表的時候，一定要先創建被關聯表 3.在錄入資料的時候，也必須先錄入被關聯表 SQL陳述句建立表關系 create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, emp_name char(16), emp_gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_ip) references dep(id) ); mysql> desc dep; +----------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | dep_name | char(16) | YES | | NULL | | | dep_desc | char(32) | YES | | NULL | | +----------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> desc emp; +------------+--------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | emp_name | char(16) | YES | | NULL | | | emp_gender | enum('male','female','others') | YES | | male | | | dep_id | int(11) | YES | MUL | NULL | | +------------+--------------------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) # 如果先插入員工表的資料，會報錯！！！ insert into emp(emp_name,dep_id) values('egon',1); mysql> insert into emp(emp_name,dep_id) values('egon',1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`day46`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`)) # 所以需要 1.先給被關聯的資料表插入資料 insert into dep(dep_name,dep_desc) values('外交部','漂泊流浪'),('教學部','教書育人'),('技術部','技術有限公司'); mysql> select * from dep; +----+-----------+--------------------+ | id | dep_name | dep_desc | +----+-----------+--------------------+ | 1 | 外交部 | 漂泊流浪 | | 2 | 教學部 | 教書育人 | | 3 | 技術部 | 技術有限公司 | +----+-----------+--------------------+ 3 rows in set (0.00 sec) 2.再給員工表插入資料 insert into emp(name,dep_id) values ('jason',1),('egon',2),('tank',2),('kevin',3),('lili',3); mysql> select * from emp; +----+----------+------------+--------+ | id | emp_name | emp_gender | dep_id | +----+----------+------------+--------+ | 7 | jason | male | 1 | | 8 | egon | male | 2 | | 9 | tank | male | 2 | | 10 | kevin | male | 3 | | 11 | lili | male | 3 | +----+----------+------------+--------+ 5 rows in set (0.00 sec) 問題： 1.修改emp里面的dep_id 欄位，或者dep里面的id欄位 update dep set id = 200 where id = 2; # 不行 2.洗掉dep表里的資料 delete fro dep # 不行 #解決方案 1.先洗掉教學部對應的員工資料，之后再洗掉部門 操作太過繁瑣 2.真正做到資料之間有關系 更新就同步更新 洗掉就同步洗掉 級聯更新 級聯洗掉 如何實作？？？？ create table dep( id int primary key auto_increment, dep_name char(16), dep_desc char(32) ); create table emp( id int primary key auto_increment, emp_name char(16), emp_gender enum('male','female','others') default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade #同步更新 on delete cascade #同步洗掉 ); insert into dep(dep_name,dep_desc) values('外交部','漂泊流浪'),('教學部','教書育人'),('技術部','技術有限公司'); insert into emp(emp_name,dep_id) values('jason',1),('egon',2),('tank',2),('kevin',3),('lili',3); 這時候再去修改，對應的資料就會自動改變 mysql> update dep set id=200 where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from dep; +-----+-----------+--------------------+ | id | dep_name | dep_desc | +-----+-----------+--------------------+ | 1 | 外交部 | 漂泊流浪 | | 3 | 技術部 | 技術有限公司 | | 200 | 教學部 | 教書育人 | +-----+-----------+--------------------+ 3 rows in set (0.00 sec) mysql> select * from emp; +----+----------+------------+--------+ | id | emp_name | emp_gender | dep_id | +----+----------+------------+--------+ | 1 | jason | male | 1 | | 2 | egon | male | 200 | | 3 | tank | male | 200 | | 4 | kevin | male | 3 | | 5 | lili | male | 3 | +----+----------+------------+--------+ 5 rows in set (0.00 sec) """
多對多表關系
""" 圖書表和作者表 book id title price 1 python入門到放棄 21000 2 葵花寶典 6666 3 前端基礎 9999 4 水滸傳 123.23 author id name age 1 jason 18 2 egon 84 確定圖書表和作者表的關系 先站在圖書表的角度： 一本書可不可以有多個作者 可以！！！！ 再站在作者表的角度： 一個作者能不能寫多本書 可以！！！！ 結論： 圖書表和作者表是雙向的一對多 那么他們的表關系就是“多對多” 的表關系 知道了以上兩個表的關系之后，我們前面學過，要確定表關系，需要用到外鍵，因此在book表中加一個外鍵auto_id ,
同樣的在author表中也要加一個外加book_id 各自加了外鍵之后，原來的表變成下面的樣子 book id title price author_id 1 python入門到放棄 21000 1,2 2 葵花寶典 6666 1 3 前端基礎 9999 2 4 水滸傳 123.23 1 author id name age book_id 1 jason 18 1,2 2 egon 84 1,2 確定了外鍵之后，我們嘗試用sql陳述句創建這兩張表 create table book( id int primary key auto_increment, title char(32), price int, author_id int, foreign key(author_id) references author(id) on update cascade on delete cascade ); create table author( id int primary key auto_increment, name varchar(32), age int, book_id int, foreign key(book_id) references book(id) on update cascade on delete cascade ); 以上創建表的陳述句寫好之后，我們分析一波，建立外鍵的時候，有幾個要注意的問題 1.一對多表關系，外鍵建在多的一方 2.在創建表的時候，需要先創建被關聯的表 3.在錄入資料的時候，需要先錄入被關聯表的資料 這里我們看第2點，站在book的角度，被關聯表是author 站在author表的角度，被關聯表是book 問題就來了，到底先創建哪個表，陷入了“死回圈” 按照上述的方式創建，一個都別想成功 其實我們只是想記錄書籍與作者的關系 針對多對多欄位表關系，不能在兩張原有的表中建立外鍵 需要你單獨再開設一張表，專門用來存盤兩張表資料之間的關系 這時候的表如下： book id title price 1 python入門到放棄 21000 2 葵花寶典 6666 3 前端基礎 9999 4 水滸傳 123.23 author id name age 1 jason 18 2 egon 84 book2author id book_id author_id 1 1 1 2 1 2 3 2 1 4 3 2 5 4 1 接下來再去創建三張表 creata table book( id int primary key auto_increment, title varchar(32), price int ); create table author( id int primary key auto_increment, name varchar(32), age int ); create book2author( id int primary key auto_increment, book_id int, author_id int, foreign key(book_id) references book(id) on update cascade on delete cascade; foreign key(author_id) references author(id) on update cascade on delete cascade ); win7命令視窗復制代碼：右鍵標記，選中代碼，按enter鍵，即可復制 mysql> desc book; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(32) | YES | | NULL | | | price | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.03 sec) mysql> desc author; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> desc book2author; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | book_id | int(11) | YES | MUL | NULL | | | author_id | int(11) | YES | MUL | NULL | | +-----------+---------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) 建好表之后，開始插入資料 insert into book(title,price) values('python入門到放棄',21000),('葵花寶典',6666),('前端基礎',9999),('水滸傳',123); insert into author(name,age) values('jason',18),('egon',73),('tank',25),('oscar',45); mysql> select * from book; +----+-----------------------+-------+ | id | title | price | +----+-----------------------+-------+ | 1 | python入門到放棄 | 21000 | | 2 | 葵花寶典 | 6666 | | 3 | 前端基礎 | 9999 | | 4 | 水滸傳 | 123 | +----+-----------------------+-------+ 4 rows in set (0.00 sec) mysql> select * from author; +----+-------+------+ | id | name | age | +----+-------+------+ | 1 | jason | 18 | | 2 | egon | 73 | | 3 | tank | 25 | | 4 | oscar | 45 | +----+-------+------+ 4 rows in set (0.00 sec) 接下來插入資料到book2author表中 insert into book2author(book_id,author_id) values(1,1),(1,2),(2,1),(3,2),(4,1); mysql> select * from book2author; +----+---------+-----------+ | id | book_id | author_id | +----+---------+-----------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 1 | | 4 | 3 | 2 | | 5 | 4 | 1 | +----+---------+-----------+ 5 rows in set (0.00 sec) 現在我們建立好了關系，也插入了資料，我們去更新和洗掉書籍表中和作者表中的一條資料，看看與其關聯的表是否有變化 update book set id = 100 where id = 1; mysql> select * from book2author; +----+---------+-----------+ | id | book_id | author_id | +----+---------+-----------+ | 1 | 100 | 1 | | 2 | 100 | 2 | | 3 | 2 | 1 | | 4 | 3 | 2 | | 5 | 4 | 1 | +----+---------+-----------+ 5 rows in set (0.00 sec) delete from book where id = 100; """
一對一表關系
""" id name age addr phone bobby email... 如果一個表里的欄位特別多，每次查詢又不是所有的欄位都需要查詢到 這時候就要將表一份為二： 用戶表 id name age 用戶詳情表 id addr phone hobby email.... 站在用戶表的角度 一個用戶能否對用多個用戶詳情，不能 再站在用戶詳情表的角度 一個用戶詳情表能否對應多個用戶，不能 結論：單向的一對多都不成立，那么這個時候，兩張表之間的關系 要么一對一 要么沒有關系 客戶表和學生表 在報名之前是客戶 報名之后是學生（期間有一些客戶不會報名） author id name age 1 jason 18 2 egon 73 authororderdetail id phone addr 1 110 安徽蕪湖 2 120 山東菏澤 客戶表和學生表只能一對一 一對一外鍵建立在任意一方都可以，但是建議建在查詢頻率較高的那張表中 create table authordetail( id int primary key auto_increment, phone int , addr varchar(64) ); create table author1( id int primary key auto_increment, name varchar(32), age_int, authordetail_id int unique, foreign key(authordetail_id) references authordetail(id) on update cascade on delete cascade ); 插入資料 insert into authordetail(phone,addr) values(110,'安徽蕪湖'),(120,'折江杭州'); 由于表關系是一對一，因此外鍵authordetail_id 必須加unique約束，才可以 在插入資料的時候，就不能插入相同的authordetail_id 比如下面陳述句就會報錯 insert into author1(name,age,authordetail_id) values('egon',18,1),('jason',25,1); mysql> insert into author1(name,age,authordetail_id) values('egon',18,1),('jason ',25,1); ERROR 1062 (23000): Duplicate entry '1' for key 'authordetail_id' 如果改成這樣就可以了： insert into author1(name,age,authordetail_id) values('egon',18,1),('jason',25,2); 插播一個解決mysql終端輸入沒法執行的方法： mysql> insert into author1(name,age,authordetail) values('\c '> \c '> ; '> insert into authordetail(phone,addr) values(110,'安徽蕪湖'),(120,'折江杭 州'); '> ); '> quit '> /sfadsas '> f '> ga '> sfg '> \g '> '\c mysql> 在 '>后面輸入 '\c 即可 """
總結
表關系的建立需要用到foreign key
一對多
外鍵欄位建在多的一方
多對多
外鍵欄位建在中間表
一對一
建在任意一方都可以，建議建在查詢頻率高的表中
判斷表之間的關系
采用換位思考法
修改表（了解）
""" MySQL對大小寫是不敏感的 1.修改表名 alter table 表名 rename 新表名; 2.增加欄位 alter table 表名 add 欄位名 欄位型別(寬度) 約束條件; alter table 表名 add 欄位名 欄位型別(寬度) 約束條件 first; alter table 表名 add 欄位名 欄位型別(寬度) 約束條件 after 欄位名; 3.洗掉欄位 alter table 表名 drop 欄位名; 4.修改欄位 alter table 表名 modify 欄位名 欄位型別(寬度) 約束條件; alter table 表名 change 舊欄位名 新欄位名 欄位型別(寬度) 約束條件; """
復制表（了解）
""" 我們SQL陳述句的查詢結果其實也是一張虛擬表 """ create table 新表名 select * from 就表名; #不能復制主鍵、外鍵 create table new_table select * from old_table where id > 3;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/297714.html
標籤：Python
上一篇：四十五、MySQL資料庫2
下一篇：四十七、MySQL資料庫4