连接查询与子查询
1 内连接查询
连接查询是将两个或两个以上的表按某个条件连接起来,从中选取需要的数据。
连接查询是同时查询两个或两个以上的表时使用的,当不同的表中存在相同意义的字段时,可以通过该字段连接这几个表。
JOIN|CROSS JOIN |INNER JOIN
(1)WHERE 实现内连接
现有cms_user,其内容如下(没有编号7的记录)
表的详细定义如下
还有一张provinces表,内容如下
详细定义如下
现要查询id,username,所在的省份(cms_user表中proId对应provinces表中的id,利用这一层关键将proName取出来)
-- cms_user的proId对应省份表中的id
SELECT cms_user.id,username,proName FROM cms_user,provinces
WHERE cms_user.proId=provinces.id;
因为两张表都有id,因此需要在id前面加上表名
如果不加WHERE条件,那么就生成cms_user记录与provinces的笛卡尔积,最后会有55条记录,因为成cms_user有11条记录,provinces有5条,11×5=55,WHERE的作用是从笛卡尔积中筛选。
(2)join实现内连接
上面是使用WHERE从笛卡尔积中筛选实现内连接,也可以使用关键字JOIN实现内连接。
CROSS JOIN |INNER JOIN | JOIN
CROSS、INNER都可以省略。
-- 查询cms_user表中id,username,email,sex
-- 查询provinces表proName
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proId=p.id; -- 两张表连接的条件
如果使用WHERE条件实现相同的效果,则是以下命令
-- 下面的查询命令与上面等效
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u,provinces AS p
WHERE u.proId=p.id;
(3)JOIN与其他关键字一起使用
JOIN内连接还可以与分组、筛选、HAVING子句、排序、LIMIT等一起使用
分组
-- 根据proName分组
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName;
先连接,再筛选,后分组
HAVING子句
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1;
排序
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC;
与LIMIT一起使用
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC
LIMIT 0,2;
(4)三张表的链接
三张或三张以上的表,也能实现连接
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_cate AS c
JOIN
cms_news AS n
ON n.cId=c.id
JOIN
cms_admin AS a
ON n.aId=a.id;
表n和表c有关联,表n和表a有关联,但表c和表a未必有关。
2 外连接查询
以一张表为主表,另外一张表为副表,显示主表中的所有记录,以及副表中符合条件的记录,如果主表中的某条记录在副表中找不到对应的,则用NULL替代。
内连接是同时符合两张表的,外连接是只需符合主表
(1)左外连接
左连接是以左表为主,哪个是左表,哪个是右表?
FROM后的左表,LEFT JOIN后的为右表,和最后的显示没有关系
下面的命令是以cms_user为左表
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proId=p.id;
也可以以provinces为左表
-- 以provinces为主表
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
LEFT JOIN cms_user AS u
ON u.proId=p.id;
副表cms_user中没有记录与“重庆”对应,索引显示NULL
(2)右外连接
右外连接是以右表为主,与左外连接类似,只需要把LIFT改成RIGHT就行
FROM后的左表,RIGHT JOIN后的为右表
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proId=p.id;
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
RIGHT JOIN provinces AS p
ON u.proId=p.id;
3 外键
(1)数据的一致性和完整性
先来看看没有外键时的样子
创建一张部门表,并插入数据
-- 创建部门表department(主表)
-- id depName
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
再创建一张员工表,并插入数据
-- 创建员工表employee(子表)
-- id ,username ,depId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);
内连接查询
SELECT e.id,e.username,d.depName FROM
employee AS e
JOIN
department AS d
ON e.depId=d.id;
此时删除督导部
此时以员工为左表,进行左连接查询
可以发现,李四所在行的部门变成了NULL
如果现在往员工数据表插入一条记录,并进行左连接查询
INSERT employee VALUES(DEFAULT,'刘六',3);
SELECT e.id,e.username,d.depName
FROM employee AS e
LEFT JOIN department AS d
ON e.depId=d.id;
刘六属于督导部,在没有督导部的情况下,这就是垃圾数据,居然还能插入成功。
在没有删除“督导部”员工的情况下,删除了督导部,导致员工所属部门变成了NULL;没有督导部,居然还能插入督导部的员工。
这种现象的发生,就是因为数据缺乏完整性和一致性。
(2)外键简介
外键的作用就是防止上述情况的发生,保证数据的完整性和一致性。
(3)如何设置外键
先把employee删除
DROP TABLE employee;
重新建立员工表
-- 创建员工表employee(子表)
-- id ,username ,depId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;
在定义中指定外键
以employee的depId字段为外键列,参考department表的id字段,即department表的id参考列,这里department为父表。
插入员工数据
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('王五',1);
以employee为左表,进行左连接查询
SELECT e.id,e.username,d.depName
FROM employee AS e
LEFT JOIN department AS d
ON e.depId=d.id;
现在如果想删除主表中id为1(即教学部)的记录,则会报错,因为教学部下面还有员工
-- 删除父表中的记录
DELETE FROM department WHERE id=1;
必须先删除教学部里的员工,才能删除父表
-- 删除employee中的属于1部门的人
DELETE FROM employee WHERE depId=1;
-- 删除部门1中的人之后,就能删除部门1了
DELETE FROM department WHERE id=1;
删除成功!
设置外键后,同样可以避免插入垃圾数据
-- 下面这条语句也会报错,因为有外键约束,把部门11当成了垃圾数据
INSERT employee(username,depId) VALUES('test',11);
部门表中,没有编号为11的部门,因此插入的记录是垃圾数据
删除部门表也无法操作
(3)外键索引
在定义employee的时候,没有指定唯一索引,但查看employee的定义和结构,就会发现,MySQL自动给外键列创建了索引
4 外键的添加和删除
删除员工表,创建一张新的员工表
-- 指定外键名称 CONSTRAINT emp_fk_dep
-- 指定外键名称,可以方便删除
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;
插入数据
INSERT employee(username,depId) VALUES('king',3),
('queen',2),
('张三',3),
('王五',2);
(1)删除外键
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
删除之后查看employee的定义
可以看到,外键被删除,但给外键列添加的索引还存在。
(2)添加外键
-- 要先保证数据是完整的数据,比如删除了部门2,但部门2的员工未删除,这就不完整,无法添加
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
再看表的定义
5 外键约束的参照操作
(1)CASCADE
如果我想这样,我从部门表中删除部门的时候,想把该部门的员工从员工表中一起删除,该如何做?如果我想修改department中部门的编号,同时让员工表中的编号一起更新,又该如何做?
先删除员工表,再删除部门表,顺序不能颠倒
重新创建部门表和员工表
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB;
CASCADE ON UPDATE CASCADE表示
插入数据
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);
两张表连接
删除编号为1的部门(教学部)
-- 删除部门表中的第一个部门
DELETE FROM department WHERE id=1;
可以看到,顺带着把教学部下的两个员工都删了。
同样的,因为有关键字 ON UPDATE CASCADE,所以当更新department中各个部门的编号时,员工表也能一块更新
-- 更新
UPDATE department SET id=id+10;
(2)SET NULL
SET NULL的用法与CASCADE的用法基本一致,不同的是,SET NULL从父表删除或更新行,则会把子表中的外键列为NULL。
如果使用该选项,必须保证子表列没有指定NOT NULL。
删除原来的员工表和部门表,重新创建
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;
插入数据
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);
删除编号为1的部门(教学部)
-- 删除部门表中的第一个部门
DELETE FROM department WHERE id=1;
更新部门编号
-- 更新
UPDATE department SET id=id+10;
6 联合查询
SELECT username FROM employee UNION SELECT username FROM cms_user;
UNION是将SELECT username FROM employee和SELECT username FROM cms_user两条命令的结果纵向拼接起来,去掉重复的
如果是UNION ALL,则不进行去重处理
SELECT username FROM employee UNION ALL SELECT username FROM cms_user;
可以看到,有两个queen
也可以将两张表中无关联的字段进行拼接,只要两张表显示的字段书目一样就行
只显示第一张表的字段,id和username
7 子查询
删除员工表和部门表,重新创建并插入数据
-- 创建部门表
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
-- 创建员工表employee(子表)
-- id ,username ,depId
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);
(1)IN 和 NOT IN子查询
如果要查询employee中,部门编号在部门表的记录,需要使用两条语句
-- 由[NOT] IN引发的子查询
SELECT id FROM department;
-- 从上条命令的结果可以看到id的值是1,2,3,4
SELECT id,username FROM employee WHERE depId IN(1,2,3,4);;
使用子查询可以将上述两条语句合并
-- 将上述两条命令结合,子查询先执行的是括号中的内容
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);
IN前加NOT表示取反
(2)比较运算符子查询
新建一张学员表,并插入数据
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED
);
INSERT student(username,score) VALUES('king',95),
('king1',35),
('king2',45),
('king3',55),
('king4',65),
('king5',75),
('king6',80),
('king7',90),
('king8',25);
新建一张奖学金表,并插入数据
-- 创建奖学金scholarship
-- id ,level
CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
level TINYINT UNSIGNED
);
INSERT scholarship(level) VALUES(90),(80),(70);
如果要查询获得一等奖学金的学员,如果不用子查询,得先查一等奖学金的得分,然后再根据得分查学员
如果使用子查询语句
-- 利用子查询
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);
(3)使用[NOT]EXISTS的子查询
-- 子查询如果为空,EXISTS返回的就是FALSE,那么外层查询语句不执行
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);
-- 只有当EXISTS返回TRUE时,外层的查询语句才会执行,此时相当于SELECT id,username FROM employee
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=4);
可以看到,图片中的两条命令等效。
同样的,也有NOT EXISTS
-- 子查询如果为空,NOT EXISTS返回的就是TRUE,那么外层查询语句会执行
SELECT id,username FROM employee WHERE NOT EXISTS(SELECT * FROM department WHERE id=41);
(4)使用ANY|SOME或者ALL的子查询
可以这么记,ANY和SOME完全等效,小于小于表存在,等于时表任意,ALL任何时候都为任意。
练习以下命令
-- 查询所有获得奖学金的学员
-- 成绩只要高于奖学金等级的最低值就行
SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score>=SOME(SELECT level FROM scholarship);
-- 查询所有学员中获得一等奖学金的学员
SELECT id,username,score FROM student WHERE score>=ALL(SELECT level FROM scholarship);
-- 查询学员表中没有获得奖学金的学员
SELECT id,username,score FROM student WHERE score<ALL(SELECT level FROM scholarship);
-- 查询所有学员中没有获得一等奖学金的学员
SELECT id,username,score FROM student WHERE score<ANY(SELECT level FROM scholarship);
-- 刚好踩到奖学金分数线的学员
SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);
-- 相当于IN,即下面这条语句与上面的等效
SELECT id,username,score FROM student WHERE score IN(SELECT level FROM scholarship);
-- 相当于NOT IN,下面两条语句等效
SELECT id,username,score FROM student WHERE score NOT IN(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score <> ALL(SELECT level FROM scholarship);
8 将查询结果插入到数据表
CREATE TABLE test1 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);
INSERT test1(id,num) SELECT id,score FROM student;
9 创建数据表时,将查询结果插入数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement
-- 会得到三个字段,多出score,num那一列全为NULL
CREATE TABLE test2 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
)SELECT id,score FROM student;
-- 只有字段名称相同时,才能正确插入
CREATE TABLE test3 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED
)SELECT id,score FROM student;
10 正则查询
利用正则表达式筛选字符,从而筛选记录。
REGEXP后跟正则表达式
先对cms_user插入一些值
INSERT cms_user(username,password,regTime,proId)
VALUES('lll','lll',138212349,2),
('ttt','lll',138212349,2),
('ooo','lll',138212349,2);
尝试一下命令
-- ^匹配字符开始的部分
-- 查询用户名以t开始的用户,不区分大小写
SELECT * FROM cms_user WHERE username REGEXP '^t';
-- $匹配字符串结尾的部分
SELECT * FROM cms_user WHERE username REGEXP 'g$';
-- .代表任意字符,和没加条件效果一样
SELECT * FROM cms_user WHERE username REGEXP '.';
-- r与g之间有两个任意字符
SELECT * FROM cms_user WHERE username REGEXP 'r..g';
-- 也可以用模糊查询,通过两个下划线实现同样的功能
SELECT * FROM cms_user WHERE username LIKE 'r__g';
-- [字符集合] [lto]
-- 只要出现 l、t、o三个字符中的任意一个,就能成功匹配到
SELECT * FROM cms_user WHERE username REGEXP '[lto]';
-- [^字符集合] 除了字符集合中的内容
-- 只要不是全由l组成、全由t组成、全由o组成,就都能查询到
SELECT * FROM cms_user WHERE username REGEXP '[^lto]';
-- 下面的语句,用户名为lll的无法查询到,其他都可以,ooo和ttt也可以
SELECT * FROM cms_user WHERE username REGEXP '[^l]';
-- 下面的语句,lll、ttt、ooo都看不到
SELECT * FROM cms_user WHERE username REGEXP '[^lto]';
-- 只要出现a-k中任意一个字母,都会被查询到
SELECT * FROM cms_user WHERE username REGEXP '[a-k]';
-- 只有全部字母都在a-k中时,才查询不到
SELECT * FROM cms_user WHERE username REGEXP '[^a-m]';
-- 所有用户名包含ng或qu的,都能被查询到
SELECT * FROM cms_user WHERE username REGEXP 'ng|qu';
-- 想多查几个字符串,可以用竖线连接
SELECT * FROM cms_user WHERE username REGEXP 'ng|qu|te';
-- qu,que,quee...*前面是e,e可以出现,也可以不出现,可以出现多次
SELECT * FROM cms_user WHERE username REGEXP 'que*';
-- 字符t至少要出现一次
SELECT * FROM cms_user WHERE username REGEXP 't+';
-- +前是e,表示e至少要一次,加上前面的qu,即que,quee,...
SELECT * FROM cms_user WHERE username REGEXP 'que+';
-- 花括号前是e,表示要出现2次,加上前面的qu,表示quee
SELECT * FROM cms_user WHERE username REGEXP 'que{2}';
-- 大于等于1,小于等于3
SELECT * FROM cms_user WHERE username REGEXP 'que{1,3}';