文章目录
九、连接查询
1.什么是连接查询?
连接查询是将两个或两个以上的表按按某个条件连接起来,从中选取需要的数据。连接查询时同时查询两个或两个以上的表时使用的。当不用的表中存在相同意义的字段时,可以通过该字段连接这个几个表。
2.内连接查询
-
JOIN | CROSS JOIN INNER JOIN
-
通过ON连接条件
-
显示两个表中符合连接条件的记录
-- cms_user的proId对应省份表中的id SELECT cms_user.id,username,proName FROM cms_user,provinces WHERE cms_user.proId=provinces.id; -- 查询cms_user表中id,username,email,sex -- 查询provinces表proName -- 使用INNER JOIN 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; -- 使用CROSS JOIN SELECT u.id,u.username,u.email,u.sex,p.proName FROM cms_user AS u CROSS JOIN provinces AS P ON u.proId=p.id; -- 查询cms_user表中的id,username,sex -- 查询provinces表中的proName -- 条件是cms_user的性别为男的用户 SELECT u.id,u.username,u.sex,p.proName FROM cms_user AS u JOIN provinces AS p ON u.proId =p.id WHERE u.sex='男'; -- 根据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; -- 对分组结果进行筛选,选出组中用户人数>=2的用户 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(*) >=2; -- 按照id升序排列 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; -- 限制显示条数,前两条 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; -- 查询cms_news表中的id,title -- 查询cms_cate表中的cateName SELECT n.id,n.title,c.cateName FROM cms_news AS n JOIN cms_cate AS c ON n.cid=c.id; -- 查询cms_news表中的id,title -- 查询cms_admin表中的username,role SELECT n.id,n.title,a.username,a.role FROM cms_news AS n JOIN cms_admin AS a ON n.aid=a.id; -- 查询cms_news表中的id,title -- 查询cms_cate表中的cateName -- 查询cms_admin表中的username,role 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;
3.外连接查询
-
左外连接:LEFT [OUTER] JOIN 显示左表的全部记录及右表符合连接条件的记录
-
右外连接:RIGHT [OUTER] JOIN 显示右表的全部记录以及左表符合条件的记录
-- 插叙一条不属于provinces表中id的记录 INSERT cms_user(username,password,regTime,proId) VALUES('TEST2','TEST2','1381203974',20);
-- 左外连接,以左表为主
-- 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;
-- 右外连接,以右表为主
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;
4.外键
- 外键时表的一个特殊字段。被参照的表是主表,外键所在的字段的表为子表。设置外键的原则需要记住,就是依赖与数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时,子表中与之对应的信息也应有相应的改变。
- 外键的作用保持数据的一致性和完整性
- 可以实现一对一或一对多的关系
- 注意
- 父表和子表必须使用相同的存储引擎,而且禁止使用临时表
- 数据表的存储引擎只能为InnoDB
- 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
- 外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引
- 外键约束的参照操作
- CASCADE:从父表删除或者更新且自动删除或更新子表中匹配的行
- SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL
- RESTRICT:拒绝对父表的删除或更新操作
- NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
-- 创建部门表department(主表)
-- 先有主表,再有子表,在本例中体现为现有部门,再有员工
-- 增加外键
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(子表)
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;
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('knight',3),
('saber',4),
('archer',1);
-- 增加了外键的约束后,无法直接从主表中删除部门
DELETE FROM department WHERE id=1;
-- 报错:Cannot delete or update a parent row: a foreign key constraint fails (`spike`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))
-- 要先删除想要删除的部门中的员工
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1;
-- 受外键约束,当往employee表中添加department表中不存在的部门id时,系统报错
INSERT employee(username,depId) VALUES('TEST',11);
-- 报错:Cannot add or update a child row: a foreign key constraint fails
-- 报错的原因为
-- 1、添加的外键列与另一个表的唯一索引列(一般是主键)的数据类型不同
-- 2、要添加外键的表类型与另一个表的存储引擎是不是都为innodb引擎
-- 3、设置的外键与另一个表中的唯一索引列(一般是主键)中的值不匹配
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
depName VARCHAR(20)
)ENGINE=INNODB;
INSERT department(depName) VALUES('市场部'),
('教学部'),
('研发部'),
('客服部');
-- 通过CONSTRAINT命名外键名称
CREATE TABLE IF NOT EXISTS employee(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
emp_name VARCHAR(20) NOT NULL UNIQUE,
dep_id TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(dep_id) REFERENCES department(id)
)ENGINE=INNODB;
INSERT employee(emp_name,dep_id) VALUES('KING',1),
('QUEEN',2),
('KNIGHT',3),
('ARCHER',4),
('ASSASIN',1);
- 增加外键后,向子表(employee)中添加主表REFERENCES department(id)之外的值时,系统报错;
- 报错:Cannot add or update a child row: a foreign key constraint fails
INSERT employee(emp_name,dep_id) VALUES('saber',5);
-- 删除外键后,才可插入
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
INSERT employee(emp_name,dep_id) VALUES('saber',10);
-- 删除外键后,方可删除主表的值(部门)
DELETE FROM department WHERE id BETWEEN 5 and 9;
-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(dep_id) REFERENCES department(id);
-- 无法添加外键,因为子表(employee)中仍包含在主表id范围外的dep_id值
-- 删除条记录后,则可添加外键
DELETE FROM employee WHERE id BETWEEN 6 AND 9;
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(dep_id) REFERENCES department(id);
-- CASCADE:从父表删除或者更新且自动删除或更新子表中匹配的行
-- ON DELETE
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
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
)ENGINE=INNODB;
INSERT employee(username,depid) VALUES('king',1),
('queen',2),
('knight',3),
('saber',4),
('archer',1);
-- 从主表中删除id为1的值,同时子表中depId为1的员工信息也自动被删除
DELETE FROM department WHERE id=1;
-- ON UPDATE
DROP TABLE employee;
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 UPDATE CASCADE
)ENGINE=INNODB;
INSERT employee(username,depid) VALUES('king',2),
('queen',2),
('knight',3),
('saber',4),
('archer',4);
-- 主表id和子表depId同时+10
UPDATE department SET id=id+10;
-- ------------------------------------------------------------------------------
DROP TABLE department,employee;
-- SET NULL:从父表删除或者更新且自动删除或更新子表中匹配的行
-- ON DELETE ON UPDATE
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
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),
('knight',3),
('saber',4),
('archer',1);
-- 从主表中删除id为1的值
DELETE FROM department WHERE id=1;
5.联合查询
- UNION
- UNION ALL
- UNION和UNION ALL区别是UNION去掉相同记录,UNION ALL是单纯的合并到一起
-- 联合查询
SELECT username FROM cms_user UNION SELECT username FROM employee;
-- 联合的数据要有管联系,不推荐如下骚操作
SELECT id,username FROM cms_user UNION SELECT username,id FROM employee;
十、子查询
1.什么是子查询
子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果,可以作为外层查询语句的条件。
2.引发子查询的情况?
-
使用[NOT] IN的子查询
-- 子查询 DROP TABLE department,,employee; CREATE TABLE IF NOT EXISTS department( id TINYINT UNSIGNED AUTO_INCREMENT KEY, depName VARCHAR(20) NOT NULL UNIQUE )ENGINE=INNODB; INSERT department(depName) VALUES('教学部'), ('市场部'), ('运营部'), ('督导部'); 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), ('knight',3), ('saber',4), ('archer',1); -- 查询department表中的id SELECT id FROM department; -- 根据如上操作,得到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); SELECT id,username FROM employee WHERE depid NOT IN(SELECT id FROM department);
-
使用比较运算符的子查询
-- 创建学院表student 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',25), ('KING2',35), ('KING3',45), ('KING4',55), ('KING5',65), ('KING6',75), ('KING7',85), ('KING8',95); -- 奖学金表scholarship CREATE TABLE IF NOT EXISTS scholarship( id TINYINT UNSIGNED KEY AUTO_INCREMENT, level TINYINT UNSIGNED ); INSERT scholarship(level) VALUES(90),(80),(70); -- 查询获得1等奖学金的学员有那些 SELECT id,username FROM student WHERE score >= (SELECT level FROM scholarship WHERE id=1);
-
使用[NOT] EXISTS的子查询
- EXISTS + True | NOT EXISTS + False,则执行前面的SELECT语句
-- 查询部门表中部门5的员工情况 -- 由于部门5,不存在,所有查询结果为空 SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5); SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=4); SELECT id,username FROM employee WHERE NOT EXISTS(SELECT * FROM department WHERE id=5);
-
使用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 >=ALL(SELECT level FROM scholarship); -- 查询学员表中没有获得奖学金的学员 SELECT id,username,score FROM student WHERE score <=ALL(SELECT level FROM scholarship); -- =ANY | SOME相当于IN SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship); 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);
3.将查询结果写入到数据表
INSERT [INTO] tbl_name [(col_name)] SELECT
CREATE TABLE test1(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);
-- 先获得查询结果,再将查询结果插入test1中
INSERT test1(id,num)
SELECT id,score FROM student;
4.创建数据表同时将查询结果写入到数据表
CREATE TABLE IF [ IF NOT EXISTS ] tbl_name [(create_definition, …)] select_statement
-- 创建的字段最好与插入的信息字段名称一致,否则会在表内产生新的字段
CREATE TABLE IF NOT EXISTS test2(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED
)SELECT id, score FROM student;
SHOW CREATE TABLE employee;
DESC employee;
SELECT * FROM test2;
SELECT * FROM scholarship;
十一、正则表达式查询
1. REGEXP’匹配方式’
2.常用匹配方式
模式字符 | 含义 |
---|---|
^ | 匹配字符开始的部分 |
$ | 匹配字符串结尾的部分 |
. | 代表字符串中的任意一个字符,包括回车和换行 |
[字符合集] | 匹配字符集合中的任何一个字符 |
[^字符合集] | 匹配除了字符集合以外的任意字符 |
S1 | S2 | S3 | 匹配S1、S2、S3中的任意一个字符 |
* | 代表0个1个或者多个其前的字符 |
+ | 代表1个或者多个其前的字符 |
String{N} | 字符串出现N次 |
字符串{M,N} | 字符串至少出现N次,最多N次 |
-- ^匹配字符开始的部分
-- 查询用户名以t开始的用户,不区分大小写
SELECT * FROM cms_user WHERE username REGEXP'^T';
-- ¥匹配字符开始的部分
-- 查询用户名以g结尾的用户,不区分大小写
SELECT * FROM cms_user WHERE username REGEXP'g$';
-- . 代表任意字符
SELECT * FROM cms_user WHERE username REGEXP 'R..G';
SELECT * FROM cms_user WHERE username LIKE 'R__G';
-- [字符集合] 匹配用户名中含有[lto]的用户信息
SELECT * FROM cms_user WHERE username REGEXP '[LTO]';
-- [^字符集合] 除了字符集合中的内容
-- 如果字符串中还包含[^]意外的字母,则依然可以被筛选出来,lily不会被筛选出来
SELECT * FROM cms_user WHERE username REGEXP '[^lily]';
-- ttt不会被晒出出来
INSERT cms_user(username,password,regTime,proId) VALUES('ttt','111',20200717,2);
SELECT * FROM cms_user WHERE username REGEXP '[^LTO]';
-- 匹配ng、qu、te中的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP 'NG|QU|TE';
-- 代表0个1个或者多个其前的字符
SELECT * FROM cms_user WHERE username REGEXP 'QUE*';
-- 代表1个或者多个其前的字符
SELECT * FROM cms_user WHERE username REGEXP 'QUE+';
-- 代表QUE出现1次到3次
SELECT * FROM cms_user WHERE username REGEXP 'QUE{1,3}';