MySQL数据库子查询 sub query_Mysql_

-- 子查询得到的结果只有一个值 select * from 数据源 where 条件判断 =/<> (select 字段名 form 数据源 where 条件判断);

-- 知道学生的id，查询所在班级名字 -- 主查询：班级，子查询：班级id mysql> select * from my_student; +----+--------+----------+------+--------+ | id | name | class_id | age | gender | +----+--------+----------+------+--------+ | 1 | 刘备 | 1 | 18 | 2 | | 2 | 李四 | 1 | 19 | 1 | | 3 | 王五 | 2 | 20 | 2 | | 4 | 张飞 | 2 | 21 | 1 | | 5 | 关羽 | 1 | 22 | 2 | | 6 | 曹操 | 1 | 20 | NULL | +----+--------+----------+------+--------+ mysql> select * from my_class; +----+--------+ | id | name | +----+--------+ | 1 | 一班 | | 3 | 三班 | | 2 | 二班 | +----+--------+ select class_id from my_student where id = 1; +----------+ | class_id | +----------+ | 1 | +----------+ select * from my_class where id = ( select class_id from my_student where id = 1 ); +----+--------+ | id | name | +----+--------+ | 1 | 一班 | +----+--------+ 

-- 获取有学生的班级名字 -- 1、找到学生表中的所有班级id -- 2、找出班级表中对应的名字 select distinct(class_id) from my_student; +----------+ | class_id | +----------+ | 1 | | 2 | +----------+ select name from my_class where id in ( select distinct(class_id) from my_student ); +--------+ | name | +--------+ | 一班 | | 二班 | +--------+ 

-- 错误示例 select * from my_student having age = max(age) and class_id = max(class_id); -- 1、having在group by之后，代表group by执行了一次，聚合函数使用 -- 2、group by 一旦执行，结果就是只返回一行记录，第一行 select max(age), max(class_id) from my_student; +----------+---------------+ | max(age) | max(class_id) | +----------+---------------+ | 22 | 2 | +----------+---------------+ select * from my_student where (age, class_id) = ( select max(age), max(class_id) from my_student ); Empty set (0.01 sec) select * from my_student where (age, class_id) = ( select max(age), min(class_id) from my_student ); +----+--------+----------+------+--------+ | id | name | class_id | age | gender | +----+--------+----------+------+--------+ | 5 | 关羽 | 1 | 22 | 2 | +----+--------+----------+------+--------+

select 字段表 from (表子查询) as 别名 [where] [group by] [having] [order by] [limit]

-- 错误示例 select * from my_student group by class_id having age = max(age); 将每个班年龄最大的学生排在最前面 order by 针对结果进行group by 保留每组第一条数据 -- select * from ( select * from my_student order by age desc ) as t group by t.class_id; +----+--------+----------+------+--------+ | id | name | class_id | age | gender | +----+--------+----------+------+--------+ | 1 | 刘备 | 1 | 18 | 2 | | 3 | 王五 | 2 | 20 | 2 | +----+--------+----------+------+--------+ 

where exists (查询语句) -- 永远为真 where 1;

-- 查询有学生的所有班级 select * from my_class as c where exists ( select id from my_student as s where s.class_id = c.id ); +----+--------+ | id | name | +----+--------+ | 1 | 一班 | | 2 | 二班 | +----+--------+

mysql> select * from my_student; +----+--------+----------+------+--------+ | id | name | class_id | age | gender | +----+--------+----------+------+--------+ | 1 | 刘备 | 1 | 18 | 2 | | 2 | 李四 | 1 | 19 | 1 | | 3 | 王五 | 2 | 20 | 2 | | 4 | 张飞 | 2 | 21 | 1 | | 5 | 关羽 | 1 | 22 | 2 | | 6 | 曹操 | 1 | 20 | NULL | +----+--------+----------+------+--------+ mysql> select id from my_class; +----+ | id | +----+ | 1 | | 3 | | 2 | +----+ 

select * from my_student where class_id in (select id from my_class); +----+--------+----------+------+--------+ | id | name | class_id | age | gender | +----+--------+----------+------+--------+ | 1 | 刘备 | 1 | 18 | 2 | | 2 | 李四 | 1 | 19 | 1 | | 3 | 王五 | 2 | 20 | 2 | | 4 | 张飞 | 2 | 21 | 1 | | 5 | 关羽 | 1 | 22 | 2 | | 6 | 曹操 | 1 | 20 | NULL | +----+--------+----------+------+--------+ 

-- 查询结果中有任意一个匹配即可，等价于in 主查询 where 条件 any (列子查询) select * from my_student where class_id = any (select id from my_class); +----+--------+----------+------+--------+ | id | name | class_id | age | gender | +----+--------+----------+------+--------+ | 1 | 刘备 | 1 | 18 | 2 | | 2 | 李四 | 1 | 19 | 1 | | 3 | 王五 | 2 | 20 | 2 | | 4 | 张飞 | 2 | 21 | 1 | | 5 | 关羽 | 1 | 22 | 2 | | 6 | 曹操 | 1 | 20 | NULL | +----+--------+----------+------+--------+ -- 不等于任意一个 主查询 where 条件 any <> (列子查询) select * from my_student where class_id <> any (select id from my_class); +----+--------+----------+------+--------+ | id | name | class_id | age | gender | +----+--------+----------+------+--------+ | 1 | 刘备 | 1 | 18 | 2 | | 2 | 李四 | 1 | 19 | 1 | | 3 | 王五 | 2 | 20 | 2 | | 4 | 张飞 | 2 | 21 | 1 | | 5 | 关羽 | 1 | 22 | 2 | | 6 | 曹操 | 1 | 20 | NULL | +----+--------+----------+------+--------+

-- 等于其中所有 =all(列子查询) select * from my_student where class_id = all (select id from my_class); Empty set (0.00 sec) select * from my_class where id = all (select class_id from my_student); Empty set (0.00 sec) -- 不等于其中所有 <>all(列子查询) select * from my_student where class_id <> all (select id from my_class); Empty set (0.00 sec) select * from my_class where id <> all (select class_id from my_student); +----+--------+ | id | name | +----+--------+ | 3 | 三班 | +----+--------+

mysql> select * from my_student; +----+--------+----------+------+--------+ | id | name | class_id | age | gender | +----+--------+----------+------+--------+ | 1 | 刘备 | 1 | 1
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：MySQL数据类型enum 枚举类型_Mysql_
                                                                
下一篇：MySQL数据库的备份与还原操作方法_Mysql_