mysql 操作合集(二)
这里接着mysql 操作合集(一)写。主要有两部分,join和约束。
这里接着mysql 操作合集(一)写。
主要有两部分,join和约束。
5.两个表间的操作
创建表
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))
插入数据
insert into test(name,course,score)values('张三','语文',80),('李四','语文',90),('王五','语文',93),('张三','数学',77),('李四','数学',68),('王五','数学',99),('张三','英语',90),('李四','英语',50),('王五','英语',89);
现在有两个表newstudent和表test
select * from test;+----+--------+--------+-------+| id | name | course | score |+----+--------+--------+-------+| 1 | 张三 | 语文 | 80 || 2 | 李四 | 语文 | 90 || 3 | 王五 | 语文 | 93 || 4 | 张三 | 数学 | 77 || 5 | 李四 | 数学 | 68 || 6 | 王五 | 数学 | 99 || 7 | 张三 | 英语 | 90 || 8 | 李四 | 英语 | 50 || 9 | 王五 | 英语 | 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 内连接
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 | 张三 | 语文 | 80 || 10012 | 2 | 李四 | 女 | 87 | 2 | 李四 | 语文 | 90 || 1001 | 5 | 王五 | NULL | 97 | 3 | 王五 | 语文 | 93 || 10011 | 1 | 张三 | 男 | 83 | 4 | 张三 | 数学 | 77 || 10012 | 2 | 李四 | 女 | 87 | 5 | 李四 | 数学 | 68 || 1001 | 5 | 王五 | NULL | 97 | 6 | 王五 | 数学 | 99 || 10011 | 1 | 张三 | 男 | 83 | 7 | 张三 | 英语 | 90 || 10012 | 2 | 李四 | 女 | 87 | 8 | 李四 | 英语 | 50 || 1001 | 5 | 王五 | NULL | 97 | 9 | 王五 | 英语 | 89 |+--------+----+--------+--------+-------+-----+--------+--------+-------+select * from test inner join newstudent on test.name=newstudent.name;+-----+--------+--------+-------+--------+----+--------+--------+-------+| cid | name | course | score | userid | id | name | gender | score |+-----+--------+--------+-------+--------+----+--------+--------+-------+| 1 | 张三 | 语文 | 80 | 10011 | 1 | 张三 | 男 | 83 || 2 | 李四 | 语文 | 90 | 10012 | 2 | 李四 | 女 | 87 || 3 | 王五 | 语文 | 93 | 1001 | 5 | 王五 | NULL | 97 || 4 | 张三 | 数学 | 77 | 10011 | 1 | 张三 | 男 | 83 || 5 | 李四 | 数学 | 68 | 10012 | 2 | 李四 | 女 | 87 || 6 | 王五 | 数学 | 99 | 1001 | 5 | 王五 | NULL | 97 || 7 | 张三 | 英语 | 90 | 10011 | 1 | 张三 | 男 | 83 || 8 | 李四 | 英语 | 50 | 10012 | 2 | 李四 | 女 | 87 || 9 | 王五 | 英语 | 89 | 1001 | 5 | 王五 | NULL | 97 |+-----+--------+--------+-------+--------+----+--------+--------+-------+
5.3 left join 左连接
select * from a left join b on a.name=b.name;
左连接从左表产生一套完整的记录,再与匹配的记录(右表b)进行匹配,生成的新表格顺序以匹配的记录为准,没有匹配则为null
select * from test left join newstudent on test.name=newstudent.name;+-----+--------+--------+-------+--------+------+--------+--------+-------+| cid | name | course | score | userid | id | name | gender | score |+-----+--------+--------+-------+--------+------+--------+--------+-------+| 1 | 张三 | 语文 | 80 | 10011 | 1 | 张三 | 男 | 83 || 4 | 张三 | 数学 | 77 | 10011 | 1 | 张三 | 男 | 83 || 7 | 张三 | 英语 | 90 | 10011 | 1 | 张三 | 男 | 83 || 2 | 李四 | 语文 | 90 | 10012 | 2 | 李四 | 女 | 87 || 5 | 李四 | 数学 | 68 | 10012 | 2 | 李四 | 女 | 87 || 8 | 李四 | 英语 | 50 | 10012 | 2 | 李四 | 女 | 87 || 3 | 王五 | 语文 | 93 | 1001 | 5 | 王五 | NULL | 97 || 6 | 王五 | 数学 | 99 | 1001 | 5 | 王五 | NULL | 97 || 9 | 王五 | 英语 | 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 | 张三 | 语文 | 80 || 10012 | 2 | 李四 | 女 | 87 | 2 | 李四 | 语文 | 90 || 1001 | 5 | 王五 | NULL | 97 | 3 | 王五 | 语文 | 93 || 10011 | 1 | 张三 | 男 | 83 | 4 | 张三 | 数学 | 77 || 10012 | 2 | 李四 | 女 | 87 | 5 | 李四 | 数学 | 68 || 1001 | 5 | 王五 | NULL | 97 | 6 | 王五 | 数学 | 99 || 10011 | 1 | 张三 | 男 | 83 | 7 | 张三 | 英语 | 90 || 10012 | 2 | 李四 | 女 | 87 | 8 | 李四 | 英语 | 50 || 1001 | 5 | 王五 | NULL | 97 | 9 | 王五 | 英语 | 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的部分,同时将表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表为基准,将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表为基准,但test表的name字段“张三”,“李四”,“王五”在newstudent表都存在,所以去掉相同部分后test为空
desc下这个表结构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为基准,将表a匹配项(a.key=b.key)右连接,与表b匹配,生成内容以匹配的记录为准,没有匹配则为null
select * from test right join newstudent on test.name=newstudent.name;+------+--------+--------+-------+--------+----+--------+--------+-------+| cid | name | course | score | userid | id | name | gender | score |+------+--------+--------+-------+--------+----+--------+--------+-------+| 1 | 张三 | 语文 | 80 | 10011 | 1 | 张三 | 男 | 83 || 2 | 李四 | 语文 | 90 | 10012 | 2 | 李四 | 女 | 87 || 3 | 王五 | 语文 | 93 | 1001 | 5 | 王五 | NULL | 97 || 4 | 张三 | 数学 | 77 | 10011 | 1 | 张三 | 男 | 83 || 5 | 李四 | 数学 | 68 | 10012 | 2 | 李四 | 女 | 87 || 6 | 王五 | 数学 | 99 | 1001 | 5 | 王五 | NULL | 97 || 7 | 张三 | 英语 | 90 | 10011 | 1 | 张三 | 男 | 83 || 8 | 李四 | 英语 | 50 | 10012 | 2 | 李四 | 女 | 87 || 9 | 王五 | 英语 | 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 | 张三 | 语文 | 80 || 10011 | 1 | 张三 | 男 | 83 | 4 | 张三 | 数学 | 77 || 10011 | 1 | 张三 | 男 | 83 | 7 | 张三 | 英语 | 90 || 10012 | 2 | 李四 | 女 | 87 | 2 | 李四 | 语文 | 90 || 10012 | 2 | 李四 | 女 | 87 | 5 | 李四 | 数学 | 68 || 10012 | 2 | 李四 | 女 | 87 | 8 | 李四 | 英语 | 50 || 1001 | 5 | 王五 | NULL | 97 | 3 | 王五 | 语文 | 93 || 1001 | 5 | 王五 | NULL | 97 | 6 | 王五 | 数学 | 99 || 1001 | 5 | 王五 | NULL | 97 | 9 | 王五 | 英语 | 89 |+--------+------+--------+--------+-------+-----+--------+--------+-------+
5.6 right join 右连接
select * from a right join b on a.key=b.key where a.key is null;
和5.5右连接不同的是:以表b为基准,先把a.key=b.key的部分从表b去除;再把表a的字段粘贴过来
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;
不知道为什么这个语法一直报错,网上找了一种替代法: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 | 张三 | 语文 | 80 || 10012 | 2 | 李四 | 女 | 87 | 2 | 李四 | 语文 | 90 || 1001 | 5 | 王五 | NULL | 97 | 3 | 王五 | 语文 | 93 || 10011 | 1 | 张三 | 男 | 83 | 4 | 张三 | 数学 | 77 || 10012 | 2 | 李四 | 女 | 87 | 5 | 李四 | 数学 | 68 || 1001 | 5 | 王五 | NULL | 97 | 6 | 王五 | 数学 | 99 || 10011 | 1 | 张三 | 男 | 83 | 7 | 张三 | 英语 | 90 || 10012 | 2 | 李四 | 女 | 87 | 8 | 李四 | 英语 | 50 || 1001 | 5 | 王五 | NULL | 97 | 9 | 王五 | 英语 | 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 求两个表格的非相同部分
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约束用来标识数据库表中每条记录;
在一个表中,可以使某一列有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));
这个表将id,lastname共同作为表persons的约束,确保id+lastname不重复
要注意的是uc_PersonID是虚拟的,不属于表真实存在的字段。
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)
此时虽然lastname重复了'z',但是id+lastname分别为1+'z'和2+'z';所以不算重复,因此插入成功
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)插入的数据相比,虽然id重复了都为2,但lastname分别为"z"和'zz';所以id+lastname分别为2+'z'和2+'zz';所以不算重复,因此插入成功
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'
在第四次操作时,由于id+lastname重复为2+'zz',约束项uc_PersonID重复,所以报错。