mysql 操作合集(二)

這里接著mysql 操作合集(一)寫(xiě)。

主要有兩部分,join和約束。

5.兩個(gè)表間的操作

創(chuàng)建表

CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, course varchar(20) DEFAULT NULL, score int(11) DEFAULT NULL, PRIMARY KEY (id))

插入數(shù)據(jù)

insert into test(name,course,score)values('張三','語(yǔ)文',80),('李四','語(yǔ)文',90),('王五','語(yǔ)文',93),('張三','數(shù)學(xué)',77),('李四','數(shù)學(xué)',68),('王五','數(shù)學(xué)',99),('張三','英語(yǔ)',90),('李四','英語(yǔ)',50),('王五','英語(yǔ)',89);

現(xiàn)在有兩個(gè)表newstudent和表test

select * from test;+----+--------+--------+-------+| id | name | course | score |+----+--------+--------+-------+| 1 | 張三 | 語(yǔ)文 | 80 || 2 | 李四 | 語(yǔ)文 | 90 || 3 | 王五 | 語(yǔ)文 | 93 || 4 | 張三 | 數(shù)學(xué) | 77 || 5 | 李四 | 數(shù)學(xué) | 68 || 6 | 王五 | 數(shù)學(xué) | 99 || 7 | 張三 | 英語(yǔ) | 90 || 8 | 李四 | 英語(yǔ) | 50 || 9 | 王五 | 英語(yǔ) | 89 |+----+--------+--------+-------+select * from newstudent;+--------+----+--------+--------+-------+| userid | id | name | gender | score |+--------+----+--------+--------+-------+| 10011 | 1 | 張三 | 男 | 83 || 10012 | 2 | 李四 | 女 | 87 || 1001 | 3 | NULL | 男 | 90 || 1001 | 4 | NULL | 女 | 97 || 1001 | 5 | 王五 | NULL | 97 || 1001 | 6 | 打人 | NULL | 97 |+--------+----+--------+--------+-------+

5.1

select newstudent.id,newstudent.name,test.cid,test.name from newstudent,test where newstudent.id=test.cid;+----+--------+-----+--------+| id | name | cid | name |+----+--------+-----+--------+| 1 | 張三 | 1 | 張三 || 2 | 李四 | 2 | 李四 || 3 | NULL | 3 | 王五 || 4 | NULL | 4 | 張三 || 5 | 王五 | 5 | 李四 || 6 | 打人 | 6 | 王五 |+----+--------+-----+--------+

5.2 inner join 內(nèi)連接

select * from a inner join b on a.id=b.id

a為主表,將b表中符合a.id=b.id的部分join到表a

select * from newstudent inner join test on newstudent.name=test.name; +--------+----+--------+--------+-------+-----+--------+--------+-------+| userid | id | name | gender | score | cid | name | course | score |+--------+----+--------+--------+-------+-----+--------+--------+-------+| 10011 | 1 | 張三 | 男 | 83 | 1 | 張三 | 語(yǔ)文 | 80 || 10012 | 2 | 李四 | 女 | 87 | 2 | 李四 | 語(yǔ)文 | 90 || 1001 | 5 | 王五 | NULL | 97 | 3 | 王五 | 語(yǔ)文 | 93 || 10011 | 1 | 張三 | 男 | 83 | 4 | 張三 | 數(shù)學(xué) | 77 || 10012 | 2 | 李四 | 女 | 87 | 5 | 李四 | 數(shù)學(xué) | 68 || 1001 | 5 | 王五 | NULL | 97 | 6 | 王五 | 數(shù)學(xué) | 99 || 10011 | 1 | 張三 | 男 | 83 | 7 | 張三 | 英語(yǔ) | 90 || 10012 | 2 | 李四 | 女 | 87 | 8 | 李四 | 英語(yǔ) | 50 || 1001 | 5 | 王五 | NULL | 97 | 9 | 王五 | 英語(yǔ) | 89 |+--------+----+--------+--------+-------+-----+--------+--------+-------+select * from test inner join newstudent on test.name=newstudent.name;+-----+--------+--------+-------+--------+----+--------+--------+-------+| cid | name | course | score | userid | id | name | gender | score |+-----+--------+--------+-------+--------+----+--------+--------+-------+| 1 | 張三 | 語(yǔ)文 | 80 | 10011 | 1 | 張三 | 男 | 83 || 2 | 李四 | 語(yǔ)文 | 90 | 10012 | 2 | 李四 | 女 | 87 || 3 | 王五 | 語(yǔ)文 | 93 | 1001 | 5 | 王五 | NULL | 97 || 4 | 張三 | 數(shù)學(xué) | 77 | 10011 | 1 | 張三 | 男 | 83 || 5 | 李四 | 數(shù)學(xué) | 68 | 10012 | 2 | 李四 | 女 | 87 || 6 | 王五 | 數(shù)學(xué) | 99 | 1001 | 5 | 王五 | NULL | 97 || 7 | 張三 | 英語(yǔ) | 90 | 10011 | 1 | 張三 | 男 | 83 || 8 | 李四 | 英語(yǔ) | 50 | 10012 | 2 | 李四 | 女 | 87 || 9 | 王五 | 英語(yǔ) | 89 | 1001 | 5 | 王五 | NULL | 97 |+-----+--------+--------+-------+--------+----+--------+--------+-------+

5.3 left join 左連接

select * from a left join b on a.name=b.name;

左連接從左表產(chǎn)生一套完整的記錄,再與匹配的記錄(右表b)進(jìn)行匹配,生成的新表格順序以匹配的記錄為準(zhǔn),沒(méi)有匹配則為null

select * from test left join newstudent on test.name=newstudent.name;+-----+--------+--------+-------+--------+------+--------+--------+-------+| cid | name | course | score | userid | id | name | gender | score |+-----+--------+--------+-------+--------+------+--------+--------+-------+| 1 | 張三 | 語(yǔ)文 | 80 | 10011 | 1 | 張三 | 男 | 83 || 4 | 張三 | 數(shù)學(xué) | 77 | 10011 | 1 | 張三 | 男 | 83 || 7 | 張三 | 英語(yǔ) | 90 | 10011 | 1 | 張三 | 男 | 83 || 2 | 李四 | 語(yǔ)文 | 90 | 10012 | 2 | 李四 | 女 | 87 || 5 | 李四 | 數(shù)學(xué) | 68 | 10012 | 2 | 李四 | 女 | 87 || 8 | 李四 | 英語(yǔ) | 50 | 10012 | 2 | 李四 | 女 | 87 || 3 | 王五 | 語(yǔ)文 | 93 | 1001 | 5 | 王五 | NULL | 97 || 6 | 王五 | 數(shù)學(xué) | 99 | 1001 | 5 | 王五 | NULL | 97 || 9 | 王五 | 英語(yǔ) | 89 | 1001 | 5 | 王五 | NULL | 97 |+-----+--------+--------+-------+--------+------+--------+--------+-------+select * from newstudent left join test on test.name=newstudent.name;+--------+----+--------+--------+-------+------+--------+--------+-------+| userid | id | name | gender | score | cid | name | course | score |+--------+----+--------+--------+-------+------+--------+--------+-------+| 10011 | 1 | 張三 | 男 | 83 | 1 | 張三 | 語(yǔ)文 | 80 || 10012 | 2 | 李四 | 女 | 87 | 2 | 李四 | 語(yǔ)文 | 90 || 1001 | 5 | 王五 | NULL | 97 | 3 | 王五 | 語(yǔ)文 | 93 || 10011 | 1 | 張三 | 男 | 83 | 4 | 張三 | 數(shù)學(xué) | 77 || 10012 | 2 | 李四 | 女 | 87 | 5 | 李四 | 數(shù)學(xué) | 68 || 1001 | 5 | 王五 | NULL | 97 | 6 | 王五 | 數(shù)學(xué) | 99 || 10011 | 1 | 張三 | 男 | 83 | 7 | 張三 | 英語(yǔ) | 90 || 10012 | 2 | 李四 | 女 | 87 | 8 | 李四 | 英語(yǔ) | 50 || 1001 | 5 | 王五 | NULL | 97 | 9 | 王五 | 英語(yǔ) | 89 || 1001 | 3 | NULL | 男 | 90 | NULL | NULL | NULL | NULL || 1001 | 4 | NULL | 女 | 97 | NULL | NULL | NULL | NULL || 1001 | 6 | 打人 | NULL | 97 | NULL | NULL | NULL | NULL |+--------+----+--------+--------+-------+------+--------+--------+-------+

5.4 left join 左連接

selece * from a left join b on a.key=b.key where b.key is null;

與5.3的左連接不同的是,僅保留a表a.key!=b.key的部分,同時(shí)將表b的字段左連接到表a

select * from newstudent left join test on newstudent.name=test.name where test.name is null;+--------+----+--------+--------+-------+------+------+--------+-------+| userid | id | name | gender | score | cid | name | course | score |+--------+----+--------+--------+-------+------+------+--------+-------+| 1001 | 3 | NULL | 男 | 90 | NULL | NULL | NULL | NULL || 1001 | 4 | NULL | 女 | 97 | NULL | NULL | NULL | NULL || 1001 | 6 | 打人 | NULL | 97 | NULL | NULL | NULL | NULL |+--------+----+--------+--------+-------+------+------+--------+-------+以newstudent表為基準(zhǔn),將newstudent.name=test.name的部分去掉,再把test表的字段左連接到newstudent表select * from test left join newstudent on newstudent.name=test.name where newstudent.name is null;Empty set (0.00 sec)返回空值的原因:以test表為基準(zhǔn),但test表的name字段“張三”,“李四”,“王五”在newstudent表都存在,所以去掉相同部分后test為空desc下這個(gè)表結(jié)構(gòu)desc select * from test left join newstudent on newstudent.name=test.name where newstudent.name is null;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | NULL || 1 | SIMPLE | newstudent | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

5.5 right join 右連接

select * from a right join b on a.key=b.key;

以表b為基準(zhǔn),將表a匹配項(xiàng)(a.key=b.key)右連接,與表b匹配,生成內(nèi)容以匹配的記錄為準(zhǔn),沒(méi)有匹配則為null

select * from test right join newstudent on test.name=newstudent.name;+------+--------+--------+-------+--------+----+--------+--------+-------+| cid | name | course | score | userid | id | name | gender | score |+------+--------+--------+-------+--------+----+--------+--------+-------+| 1 | 張三 | 語(yǔ)文 | 80 | 10011 | 1 | 張三 | 男 | 83 || 2 | 李四 | 語(yǔ)文 | 90 | 10012 | 2 | 李四 | 女 | 87 || 3 | 王五 | 語(yǔ)文 | 93 | 1001 | 5 | 王五 | NULL | 97 || 4 | 張三 | 數(shù)學(xué) | 77 | 10011 | 1 | 張三 | 男 | 83 || 5 | 李四 | 數(shù)學(xué) | 68 | 10012 | 2 | 李四 | 女 | 87 || 6 | 王五 | 數(shù)學(xué) | 99 | 1001 | 5 | 王五 | NULL | 97 || 7 | 張三 | 英語(yǔ) | 90 | 10011 | 1 | 張三 | 男 | 83 || 8 | 李四 | 英語(yǔ) | 50 | 10012 | 2 | 李四 | 女 | 87 || 9 | 王五 | 英語(yǔ) | 89 | 1001 | 5 | 王五 | NULL | 97 || NULL | NULL | NULL | NULL | 1001 | 3 | NULL | 男 | 90 || NULL | NULL | NULL | NULL | 1001 | 4 | NULL | 女 | 97 || NULL | NULL | NULL | NULL | 1001 | 6 | 打人 | NULL | 97 |+------+--------+--------+-------+--------+----+--------+--------+-------+select * from newstudent right join test on newstudent.name=test.name;+--------+------+--------+--------+-------+-----+--------+--------+-------+| userid | id | name | gender | score | cid | name | course | score |+--------+------+--------+--------+-------+-----+--------+--------+-------+| 10011 | 1 | 張三 | 男 | 83 | 1 | 張三 | 語(yǔ)文 | 80 || 10011 | 1 | 張三 | 男 | 83 | 4 | 張三 | 數(shù)學(xué) | 77 || 10011 | 1 | 張三 | 男 | 83 | 7 | 張三 | 英語(yǔ) | 90 || 10012 | 2 | 李四 | 女 | 87 | 2 | 李四 | 語(yǔ)文 | 90 || 10012 | 2 | 李四 | 女 | 87 | 5 | 李四 | 數(shù)學(xué) | 68 || 10012 | 2 | 李四 | 女 | 87 | 8 | 李四 | 英語(yǔ) | 50 || 1001 | 5 | 王五 | NULL | 97 | 3 | 王五 | 語(yǔ)文 | 93 || 1001 | 5 | 王五 | NULL | 97 | 6 | 王五 | 數(shù)學(xué) | 99 || 1001 | 5 | 王五 | NULL | 97 | 9 | 王五 | 英語(yǔ) | 89 |+--------+------+--------+--------+-------+-----+--------+--------+-------+

5.6 right join 右連接

select * from a right join b on a.key=b.key where a.key is null;

和5.5右連接不同的是:以表b為基準(zhǔn),先把a(bǔ).key=b.key的部分從表b去除;再把表a的字段粘貼過(guò)來(lái)

select * from test right join newstudent on test.name=newstudent.name where test.name is null;+------+------+--------+-------+--------+----+--------+--------+-------+| cid | name | course | score | userid | id | name | gender | score |+------+------+--------+-------+--------+----+--------+--------+-------+| NULL | NULL | NULL | NULL | 1001 | 3 | NULL | 男 | 90 || NULL | NULL | NULL | NULL | 1001 | 4 | NULL | 女 | 97 || NULL | NULL | NULL | NULL | 1001 | 6 | 打人 | NULL | 97 |+------+------+--------+-------+--------+----+--------+--------+-------+select * from newstudent right join test on newstudent.name=test.name where newstudent.name is null;Empty set (0.00 sec)返回空值原因同上5.4

5.7 full outer join 全連接

select * from a full join b on a.key=b.key;

不知道為什么這個(gè)語(yǔ)法一直報(bào)錯(cuò),網(wǎng)上找了一種替代法:join + union(去重)

select * from A left join B on A.id = B.id union select * from A right join B on A.id = B.id;

select * from newstudent left join test on newstudent.name=test.name union select * from newstudent right join test on newstudent.name=test.name;+--------+------+--------+--------+-------+------+--------+--------+-------+| userid | id | name | gender | score | cid | name | course | score |+--------+------+--------+--------+-------+------+--------+--------+-------+| 10011 | 1 | 張三 | 男 | 83 | 1 | 張三 | 語(yǔ)文 | 80 || 10012 | 2 | 李四 | 女 | 87 | 2 | 李四 | 語(yǔ)文 | 90 || 1001 | 5 | 王五 | NULL | 97 | 3 | 王五 | 語(yǔ)文 | 93 || 10011 | 1 | 張三 | 男 | 83 | 4 | 張三 | 數(shù)學(xué) | 77 || 10012 | 2 | 李四 | 女 | 87 | 5 | 李四 | 數(shù)學(xué) | 68 || 1001 | 5 | 王五 | NULL | 97 | 6 | 王五 | 數(shù)學(xué) | 99 || 10011 | 1 | 張三 | 男 | 83 | 7 | 張三 | 英語(yǔ) | 90 || 10012 | 2 | 李四 | 女 | 87 | 8 | 李四 | 英語(yǔ) | 50 || 1001 | 5 | 王五 | NULL | 97 | 9 | 王五 | 英語(yǔ) | 89 || 1001 | 3 | NULL | 男 | 90 | NULL | NULL | NULL | NULL || 1001 | 4 | NULL | 女 | 97 | NULL | NULL | NULL | NULL || 1001 | 6 | 打人 | NULL | 97 | NULL | NULL | NULL | NULL |+--------+------+--------+--------+-------+------+--------+--------+-------+

5.8 union

select key1 from a union select key2 from b;

select name,id,score from newstudent union select name,cid,score from test;+--------+----+-------+| name | id | score |+--------+----+-------+| 張三 | 1 | 83 || 李四 | 2 | 87 || NULL | 3 | 90 || NULL | 4 | 97 || 王五 | 5 | 97 || 打人 | 6 | 97 || 張三 | 1 | 80 || 李四 | 2 | 90 || 王五 | 3 | 93 || 張三 | 4 | 77 || 李四 | 5 | 68 || 王五 | 6 | 99 || 張三 | 7 | 90 || 李四 | 8 | 50 || 王五 | 9 | 89 |+--------+----+-------+注意:union 后的表字段是引用表a的字段名

5.9 union all

select key1 from a union all select key2 from b;

select name from test union all select userid from newstudent;+--------+| name |+--------+| 張三 || 李四 || 王五 || 張三 || 李四 || 王五 || 張三 || 李四 || 王五 || 10011 || 10012 || 1001 || 1001 || 1001 || 1001 |+--------+

5.10 求兩個(gè)表格的非相同部分

SELECT * FROM A LEFT JOIN B ON A.name = B.name

WHERE B.id IS NULL

union

SELECT * FROM A right JOIN B ON A.name = B.name

WHERE A.id IS NULL;

select * from test right join newstudent on test.name=newstudent.name where test.name is null union select * from test left join newstudent on newstudent.name=test.name where newstudent.name is null;+------+------+--------+-------+--------+------+--------+--------+-------+| cid | name | course | score | userid | id | name | gender | score |+------+------+--------+-------+--------+------+--------+--------+-------+| NULL | NULL | NULL | NULL | 1001 | 3 | NULL | 男 | 90 || NULL | NULL | NULL | NULL | 1001 | 4 | NULL | 女 | 97 || NULL | NULL | NULL | NULL | 1001 | 6 | 打人 | NULL | 97 |+------+------+--------+-------+--------+------+--------+--------+-------+

6.約束

6.1 unique

unique約束用來(lái)標(biāo)識(shí)數(shù)據(jù)庫(kù)表中每條記錄;

在一個(gè)表中,可以使某一列有unique約束,也可以多列一起unique約束

6.1.1

CREATE TABLE persons(id int NOT NULL,lastname varchar(255) NOT NULL,firstName varchar(255),city varchar(255),CONSTRAINT uc_PersonID UNIQUE (id,lastname));

這個(gè)表將id,lastname共同作為表persons的約束,確保id+lastname不重復(fù)

要注意的是uc_PersonID是虛擬的,不屬于表真實(shí)存在的字段。

desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || lastname | varchar(255) | NO | PRI | NULL | || firstName | varchar(255) | YES | | NULL | || city | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+

6.1.2-1

insert into persons(id,lastname,firstname) values (1,'z','q');Query OK, 1 row affected (0.00 sec)

6.1.2-2

insert into persons(id,lastname,firstname) values (2,'z','q');Query OK, 1 row affected (0.01 sec)

此時(shí)雖然lastname重復(fù)了'z',但是id+lastname分別為1+'z'和2+'z';所以不算重復(fù),因此插入成功

6.1.2-3

insert into persons(id,lastname,firstname) values (2,'zz','q');Query OK, 1 row affected (0.01 sec)

和第二次(6.1.2-2)插入的數(shù)據(jù)相比,雖然id重復(fù)了都為2,但lastname分別為"z"和'zz';所以id+lastname分別為2+'z'和2+'zz';所以不算重復(fù),因此插入成功

6.1.2-4

insert into persons(id,lastname,firstname) values (2,'zz','q');ERROR 1062 (23000): Duplicate entry '2-zz' for key 'uc_PersonID'

在第四次操作時(shí),由于id+lastname重復(fù)為2+'zz',約束項(xiàng)uc_PersonID重復(fù),所以報(bào)錯(cuò)。

12下一頁(yè)>

(免責(zé)聲明:本網(wǎng)站內(nèi)容主要來(lái)自原創(chuàng)、合作伙伴供稿和第三方自媒體作者投稿,凡在本網(wǎng)站出現(xiàn)的信息,均僅供參考。本網(wǎng)站將盡力確保所提供信息的準(zhǔn)確性及可靠性,但不保證有關(guān)資料的準(zhǔn)確性及可靠性,讀者在使用前請(qǐng)進(jìn)一步核實(shí),并對(duì)任何自主決定的行為負(fù)責(zé)。本網(wǎng)站對(duì)有關(guān)資料所引致的錯(cuò)誤、不確或遺漏,概不負(fù)任何法律責(zé)任。
任何單位或個(gè)人認(rèn)為本網(wǎng)站中的網(wǎng)頁(yè)或鏈接內(nèi)容可能涉嫌侵犯其知識(shí)產(chǎn)權(quán)或存在不實(shí)內(nèi)容時(shí),應(yīng)及時(shí)向本網(wǎng)站提出書(shū)面權(quán)利通知或不實(shí)情況說(shuō)明,并提供身份證明、權(quán)屬證明及詳細(xì)侵權(quán)或不實(shí)情況證明。本網(wǎng)站在收到上述法律文件后,將會(huì)依法盡快聯(lián)系相關(guān)文章源頭核實(shí),溝通刪除相關(guān)內(nèi)容或斷開(kāi)相關(guān)鏈接。 )

贊助商
2019-03-08
mysql 操作合集(二)
這里接著mysql 操作合集(一)寫(xiě)。主要有兩部分,join和約束。

長(zhǎng)按掃碼 閱讀全文