mysql 操作合集(二)
6.1.3
刪除unique約束
alter table persons drop index uc_PersonID;Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id | int(11) | NO | | NULL | || lastname | varchar(255) | NO | | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+
6.2 not null約束
6.2.1
not null 約束強(qiáng)制列不接受null值。
再拿6.1.3的表persons做說(shuō)明,可以看到id和lastname字段是存在not null約束的,所以在insert時(shí)必須要傳入這兩個(gè)字段的值
insert into persons(id,lastname) values (3,'q');Query OK, 1 row affected (0.00 sec)
插入成功,下面試一下insert時(shí)不給id賦值的情況:
insert into persons(lastname) values ('q');ERROR 1364 (HY000): Field 'id' doesn't have a default value
成功的報(bào)錯(cuò)了,除非你有給id定一個(gè)default(默認(rèn)值),否則在insert時(shí)不給id賦值一定會(huì)報(bào)錯(cuò)。
select * from persons;+----+----------+-----------+------+| id | lastname | firstName | city |+----+----------+-----------+------+| 1 | z | q | NULL || 2 | z | q | NULL || 2 | zz | q | NULL || 3 | q | NULL | NULL |+----+----------+-----------+------+
總結(jié):有not null約束的字段一定要賦值,沒(méi)有not null約束的比如city字段在insert時(shí)不賦值會(huì)自動(dòng)為null
6.2.2
刪除not null 約束
alter table persons modify id int null;desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || lastname | varchar(255) | NO | | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+
6.3 check 約束
6.3.1
ALTER TABLE Persons ADD CHECK (id>0);insert into persons(id,lastname) values (-2,'qq');Query OK, 1 row affected (0.00 sec)
非常奇怪,check約束明明加進(jìn)去了,但是id=-2還可以被插入;
select * from persons;+------+----------+-----------+------+| id | lastname | firstName | city |+------+----------+-----------+------+| 1 | z | q | NULL || 2 | z | q | NULL || 2 | zz | q | NULL || 3 | q | NULL | NULL || -2 | qq | NULL | NULL |+------+----------+-----------+------+
剛剛insert的數(shù)據(jù)已經(jīng)進(jìn)入表中了。。。
6.3.2
百度以后,發(fā)現(xiàn)這個(gè)mysql本身的問(wèn)題,想要達(dá)到約束效果可以使用type=enum();具體方法如下:
在原表基礎(chǔ)上加入gender字段,規(guī)定gender只能為male或者female;
alter table persons add gender enum('male' ,'female');desc persons;+-----------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+-----------------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || lastname | varchar(255) | NO | | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | NULL | || gender | enum('male','female') | YES | | NULL | |+-----------+-----------------------+------+-----+---------+-------+
表persons的gender成功變?yōu)閠ype為enum('male' ,'female');
insert into persons1(id,lastname,gender) values (3,'qqq','male1');ERROR 1265 (01000): Data truncated for column 'gender' at row 1insert into persons1(id,lastname,gender) values (3,'qqq','male');Query OK, 1 row affected (0.00 sec)
6.3.3
刪除enum('male' ,'female')約束
alter table persons modify gender varchar(255);desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || lastname | varchar(255) | NO | | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | NULL | || gender | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+
6.4 default 約束
6.4.1
創(chuàng)建default 約束
alter table persons alter city set default '上海';Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || lastname | varchar(255) | NO | | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | 上海 | || gender | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+
6.4.2
刪除default 約束
alter table persons alter city drop default;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || lastname | varchar(255) | NO | | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | NULL | || gender | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+
6.5 primary key
primary key約束:
用來(lái)唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)表中的每條記錄;主鍵必須包含唯一的值且不能含有null值。與6.1unique約束不同的是,unique約束能同時(shí)對(duì)好幾個(gè)字段增加唯一約束,而primary約束只能對(duì)某一列創(chuàng)建唯一約束。在一個(gè)表中只能有一個(gè)primary約束。
6.5.1創(chuàng)建primary 約束
alter table persons add primary key(id);ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
原因:給表persons的id字段增加primary約束,這需要id字段的內(nèi)容保持唯一性,而現(xiàn)在id=2有好幾條數(shù)據(jù),所以報(bào)錯(cuò)。
先將表中id=2的字段刪除,再給id增加primary約束
delete from persons where id=2;alter table persons add primary key(id);desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || lastname | varchar(255) | NO | | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | NULL | || gender | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+
6.5.2 刪除primary 約束
alter table persons drop primary key;desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id | int(11) | NO | | NULL | || lastname | varchar(255) | NO | | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | NULL | || gender | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+
6.6 foreign key
6.6.1
ALTER TABLE a ADD FOREIGN KEY (P_Id) REFERENCES b(P_Id)
給表a增加外鍵P_Id;且此外鍵的參照為表b的P_Id列;關(guān)聯(lián)的操作為刪除和更新;
注意:表b為為主表,表a為從表,表b的更新和刪除時(shí)將會(huì)聯(lián)動(dòng)表a中外鍵與其關(guān)聯(lián)對(duì)應(yīng)的記錄做更新或刪除操作
6.6.2
在做這個(gè)操作時(shí),表newstudent和表test都是有數(shù)據(jù)的,所以就一直報(bào)錯(cuò)
alter table newstudent add foreign key (id) references testdatabase.test(id);ERROR 3734 (HY000): Failed to add the foreign key constraint. Missing column 'userid' for constraint 'newstudent_ibfk_1' in the referenced table 'test'
通過(guò)這兩步,將表中的數(shù)據(jù)都刪除;
delete from newstudent;Query OK, 7 rows affected (0.01 sec)delete from test;Query OK, 7 rows affected (0.01 sec)
6.6.3
alter table newstudent add foreign key (id) references testdatabase.test(id);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0
此時(shí),給表newstudent增加外鍵id;且此外鍵的參照為表test的id項(xiàng);這時(shí)表test為主表;表newstudent為從表
重點(diǎn):主表和從表一定要分清楚,因?yàn)橹鞅淼母潞蛣h除時(shí)將會(huì)聯(lián)動(dòng)從表中外鍵與其關(guān)聯(lián)對(duì)應(yīng)的記錄做更新或刪除操作
6.6.4
之前沒(méi)有注意,在兩個(gè)表都為空表時(shí),先給從表newstudent賦值插入,結(jié)果就是報(bào)錯(cuò)
insert into newstudent(userid,id,name,gender,score) values(100,2,'張三我','男',85);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdatabase`.`newstudent`, CONSTRAINT `newstudent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`))
先給test賦值,id=2;再給newstudent賦值id=2的數(shù)據(jù),就可以成功插入
insert into test(id,name,score,course) values(2,'王五的',90,'語(yǔ)文');Query OK, 1 row affected (0.00 sec)insert into newstudent(userid,id,name,gender,score) values(100,2,'張三我','男',85);Query OK, 1 row affected (0.00 sec)
6.6.5
如果給newstudent賦值id=3會(huì)報(bào)錯(cuò),因?yàn)檫@個(gè)時(shí)候test表沒(méi)有id=3的數(shù)據(jù)
insert into newstudent(userid,id,name,gender,score) values(100,3,'張三我','男',85);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdatabase`.`newstudent`, CONSTRAINT `newstudent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`))
那在重新給newstudent賦值id=1的數(shù)據(jù)呢?結(jié)果也是報(bào)錯(cuò),因?yàn)閕d有primary約束,不能重復(fù)插入
insert into newstudent(userid,id,name,gender,score) values(100,1,'張三我','男',85);ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
6.6.6
外鍵起到的作用就是保持連接的數(shù)據(jù)表的一致性,當(dāng)對(duì)主表進(jìn)行刪除或更新操作時(shí),從表也會(huì)一起刪除或更新;
update test set id=3;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdatabase`.`newstudent`, CONSTRAINT `newstudent_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test` (`id`))
不能刪除或更新這一行,存在外鍵約束
我們?cè)诟屡c刪除時(shí)遇到的外鍵約束解決方案分別對(duì)應(yīng)設(shè)置Update rule與Delete rule。有如下四個(gè)選項(xiàng):
1.CASCADE:從父表刪除或更新且自動(dòng)刪除或更新子表中匹配的行。
2.SET NULL:從父表刪除或更新行,并設(shè)置子表中的外鍵列為NULL。如果使用該選項(xiàng),必須保證子表列沒(méi)有指定NOT NULL。
3.RESTRICT:拒絕對(duì)父表的刪除或更新操作。
4.NO ACTION:標(biāo)準(zhǔn)SQL的關(guān)鍵字,在MySQL中與RESTRICT相同。
6.6.7
所以將foreign約束刪除后重新構(gòu)建:
alter table newstudent drop foreign key newstudent_ibfk_1;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0
要點(diǎn):newstudent_ibfk_1是在之前的報(bào)錯(cuò)中找到的CONSTRAINT `newstudent_ibfk_1`
delete from test;Query OK, 0 rows affected (0.00 sec)delete from newstudent;Query OK, 2 rows affected (0.00 sec)
將表中的內(nèi)容刪除干凈
6.6.8
alter table newstudent add foreign key (id) references testdatabase.test(id) on update cascade on delete cascade;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0
on update cascade表示更新關(guān)聯(lián);on delete cascade表示刪除關(guān)聯(lián)。
6.6.9
將表test的id=2更新為id=3,看看表newstudent和表test有什么變化
update test set id=3 where id=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
select * from test;+----+-----------+--------+-------+| id | name | course | score |+----+-----------+--------+-------+| 1 | 王五 | 語(yǔ)文 | 90 || 3 | 王五的 | 語(yǔ)文 | 90 |+----+-----------+--------+-------+
test表的id從2變成了3;
select * from newstudent;+--------+----+-----------+--------+-------+| userid | id | name | gender | score |+--------+----+-----------+--------+-------+| 100 | 1 | 張三 | 男 | 65 || 100 | 3 | 張三我 | 男 | 85 |+--------+----+-----------+--------+-------+
此時(shí)newstudent表的id也從2變成了3;(此時(shí)沒(méi)有直接對(duì)newstudent表有過(guò)任何操作)
6.6.10
delete from test where id=3;Query OK, 1 row affected (0.00 sec)
將主表test中id=3的刪除
select * from test;+----+--------+--------+-------+| id | name | course | score |+----+--------+--------+-------+| 1 | 王五 | 語(yǔ)文 | 90 |+----+--------+--------+-------+
select * from newstudent;+--------+----+--------+--------+-------+| userid | id | name | gender | score |+--------+----+--------+--------+-------+| 100 | 1 | 張三 | 男 | 65 |+--------+----+--------+--------+-------+
這時(shí)表newstudent的id=3的數(shù)據(jù)也刪除了
發(fā)表評(píng)論
請(qǐng)輸入評(píng)論內(nèi)容...
請(qǐng)輸入評(píng)論/評(píng)論長(zhǎng)度6~500個(gè)字
最新活動(dòng)更多
-
10月31日立即下載>> 【限時(shí)免費(fèi)下載】TE暖通空調(diào)系統(tǒng)高效可靠的組件解決方案
-
即日-11.13立即報(bào)名>>> 【在線會(huì)議】多物理場(chǎng)仿真助跑新能源汽車(chē)
-
11月28日立即報(bào)名>>> 2024工程師系列—工業(yè)電子技術(shù)在線會(huì)議
-
12月19日立即報(bào)名>> 【線下會(huì)議】OFweek 2024(第九屆)物聯(lián)網(wǎng)產(chǎn)業(yè)大會(huì)
-
即日-12.26火熱報(bào)名中>> OFweek2024中國(guó)智造CIO在線峰會(huì)
-
即日-2025.8.1立即下載>> 《2024智能制造產(chǎn)業(yè)高端化、智能化、綠色化發(fā)展藍(lán)皮書(shū)》
推薦專題
- 1 【一周車(chē)話】沒(méi)有方向盤(pán)和踏板的車(chē),你敢坐嗎?
- 2 特斯拉發(fā)布無(wú)人駕駛車(chē),還未迎來(lái)“Chatgpt時(shí)刻”
- 3 特斯拉股價(jià)大跌15%:Robotaxi離落地還差一個(gè)蘿卜快跑
- 4 馬斯克給的“驚喜”夠嗎?
- 5 海信給AI電視打樣,12大AI智能體全面升級(jí)大屏體驗(yàn)
- 6 打完“價(jià)格戰(zhàn)”,大模型還要比什么?
- 7 馬斯克致敬“國(guó)產(chǎn)蘿卜”?
- 8 神經(jīng)網(wǎng)絡(luò),誰(shuí)是盈利最強(qiáng)企業(yè)?
- 9 比蘋(píng)果偉大100倍!真正改寫(xiě)人類歷史的智能產(chǎn)品降臨
- 10 諾獎(jiǎng)進(jìn)入“AI時(shí)代”,人類何去何從?
- 高級(jí)軟件工程師 廣東省/深圳市
- 自動(dòng)化高級(jí)工程師 廣東省/深圳市
- 光器件研發(fā)工程師 福建省/福州市
- 銷售總監(jiān)(光器件) 北京市/海淀區(qū)
- 激光器高級(jí)銷售經(jīng)理 上海市/虹口區(qū)
- 光器件物理工程師 北京市/海淀區(qū)
- 激光研發(fā)工程師 北京市/昌平區(qū)
- 技術(shù)專家 廣東省/江門(mén)市
- 封裝工程師 北京市/海淀區(qū)
- 結(jié)構(gòu)工程師 廣東省/深圳市