下面我们来看一些特殊形式的查询。
特殊形式的查询:
1.子查询:指将一个查询语句嵌套在另一个查询语句中,子语句是嵌套在父语句中间的。当父语句需要用到子语句查询结果时,可以使用这种查询语句。
select 字段名称 from tb_name where col_name=(select col_name from tb_name);
这就是一个子查询的语句。你可以看到有两条查询结果:外层和内层。内层查询语句的一个查询结果可以作为内层查询语句的一个条件。这就是我们的子查询。
这种查询形式就是我们的子查询。
由in引发的子查询。不用子查询我们需要用到两个查询语句,当用了子查询我们就只需要一条查询语句就可以了。
#加入我们要查询一下员工表中部门depId在部门表中的
SELECT depIp FROM emp;
SELECT *FROM emp WHERE depId IN(1,2,3,4);
由比较运算符引出的子查询
#新建一个学员表stu
CREATE TABLE stu(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20)NOT NULL UNIQUE,
score TINYINT UNSIGNED NOT NULL
);
INSERT stu(username,score)VALUES('king',95),
#插入一些记录作为一个我们的测试
('queen',75),
('zhangsan',69),
('lisi',78),
('wangwu',87),
('zhaoliu',88),
('tianqi',99),
('tiancai',50);
#学员的分数级别表
CREATE TABLE `level`(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED COMMENT'分数'
);
INSERT `level`(score)VALUES(90),(80),(70);
#查询出成绩优秀的学员。
SELECT score FROM LEVEL WHERE id=1;
SELECT id,username,score FROM stu
WHERE score>=(SELECT score FROM LEVEL WHERE id=1);
#注意我们的子查询必须要放到括号里面
#查询出没有评级的学员
SELECT id,username,score FROM stu
WHERE score<=(SELECT score FROM LEVEL WHERE id=3);
由exists引发的子查询:内层查询语句返回的是一个bool值,如果为真,则执行外层查询语句,否则不执行。
#内层语句作为是否会执行到外层语句的一个条件。
SELECT *FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10);
如果内层查到结果,就相当于真,前面的语句就可以执行。
any,some,all引发的一个子查询:用这些代表最大值、最小值、任意值。
#带有any、some、all关键字的子查询
#显示所有可以获得评级的学员
SELECT *FROM stu
WHERE score>=ANY(SELECT score FROM LEVEL);
>=any表示大于等于里面的最小值。
同样的你把换成all、some也是可以的。
#带有any、some、all关键字的子查询
#显示所有可以优秀评级的学员
SELECT *FROM stu
WHERE score>=ALL(SELECT score FROM LEVEL);
all表示大于这个里面的最大值。
#带有any、some、all关键字的子查询
#显示一下不能获得评级的学员
SELECT *FROM stu
WHERE score<ALL(SELECT score FROM LEVEL);
=any表示等于等级表中的任意值都可以。
INSERT stu(username,score)VALUES('aaaa',90),
('bbbb',80),
('cccc',70);
#实验=any
#想要查询出成绩刚好为70、80、90的学员
SELECT *FROM stu
WHERE score=ANY(SELECT score FROM LEVEL);
#实验=any
#想要查询出成绩不为70、80、90的学员
SELECT *FROM stu
WHERE score!=ALL(SELECT score FROM LEVEL);
这是带有关键字的一个子查询。
有了这个子查询以后,我们还有很多查询是基于这个子查询的,我们来看一下。
insert ...select
create...select
把查询结果写到另一个表中。
我们想要创建一个新表,把员工表中的用户名插入到新表中。
#创建一个user1表,id username
#没有子查询之前,需要两步,先把emp查出来,在写入user1
#而有了子查询我们这个操作就可以一步搞定
CREATE TABLE user1(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20)
)SELECT id,username FROM emp;
在这里完成了两步操作,先把这个表建成功,在把查询结果插入。
#将user表中的用户名写入user1表中
INSERT user1(username)SELECT username FROM USER;
#创建一个数据表user2,它的表结构和user1相同
#但是它里面是没有数据的,接下来我们再将user1中的数据导入user2
CREATE TABLE user2 LIKE user1;
INSERT user2 SELECT id,username FROM user1;
#将stu表中的tiancai用户名添加到user2中
INSERT user2 SET username=(SELECT username FROM stu WHERE username='tiancai');
#去掉字段的重复值
SELECT DISTINCT(username)FROM user2;
除了联合查询
#将user1和user2两个数据表的数据合并到一起
SELECT *FROM user1
UNION
SELECT *FROM user2;
union会去掉两个表中的重复值。
union all只是简单的将查询结果合并。
select 字段名称,...from tb_name1
union
select 字段名称,...from tb_name2;
select 字段名称,...from tb_name1
union all
select 字段名称,...from tb_name2;