文章目录
一、什么是子查询?
子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果,可以作为外层查询语句提供条件。
二、引发子查询的情况?
1.使用[NOT]IN的子查询
- 测试IN
SELECT id FROM department;
SELECT id,username FROM employee WHERE depId IN(1,2,3,4);
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);
- 测试 NOT IN
INSERT employee(username,depId) VALUES('testtest',8);
SELECT id,username FROM employee WHERE depId NOT IN(SELECT id FROM department);
2.使用比较运算符的子查询
- =、 >、<、>=、<=、<>、!=、<=>
- 创建学员表student:id username score
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);
测试:查询获得1等奖学金的学员
SELECT level FROM scholarship WHERE id=1;
SELECT id,username,score FROM student WHERE score>=90;
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);
3.使用[NOT]EXISTS的子查询
[NOT]EXISTS类似于判断,返回真或者假
- 查询部门表中
SELECT * FROM department WHERE id=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=41);
4.使用ANY|SOME或者ALL的子查询
(1)测试ANY|SOME、ALL
- 查询所有获得奖学金的学员
mysql> SELECT * FROM scholarship;
+----+-------+
| id | level |
+----+-------+
| 1 | 90 |
| 2 | 80 |
| 3 | 70 |
+----+-------+
#大于等于scholarship中的最小值
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);
(2)’=ANY’ 相当于IN
score>=SOME(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 IN(SELECT level FROM scholarship);
score>=SOME(SELECT level FROM scholarship);
(3)’<> ALL’相当于NOT IN
score>=SOME(SELECT level FROM scholarship);
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);
三、将查询结果写入到数据表
INSERT [INTO] tbl_name [(col nam...] SELECT ...
CREATE TABLE test1 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);
INSERT test1(id,num)
SELECT id,score FROM student;
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;
四、建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition...] select_statement