数据库_days3_子查询

子查询

子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以作为外层查询语句提供条件。

子查询的方式

使用[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);

使用比较运算符的子查询

• =、 >、<、>=、<=、<>、!=、<=>
  创建学员表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);

使用[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);

使用ANY|SOME或者ALL的子查询

测试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);

’=ANY’ 相当于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);
score>=SOME(SELECT level FROM scholarship);

’<> ALL’相当于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);

将查询结果写入到数据表

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