Mysql Day Eight

最新推荐文章于 2024-08-14 18:59:31 发布

原创最新推荐文章于 2024-08-14 18:59:31 发布 · 153 阅读

0 ·

CC 4.0 BY-SA版权

MYSQL 专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了SQL中的联合查询和子查询技术，包括基本语法、实例演示以及如何使用ORDER BY和LIMIT配合使用。通过具体例子展示了联合查询在结构相同多表查询中的应用，以及子查询在不同场景下的灵活运用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

联合查询

作用：将多次查询在记录上进行拼接，不会增加字段。常用结构相同的多表查询，和同一张表需求不同

基本语法：多条select语句构成，每一条语句获取的字段数必须严格一致，字段类型不要求。

select 语句1 union [union选项] select 语句2，语句3；

union选项：all ：保留所有数据

destinct：去重，（默认）；

-- 实例一：
select * from m_primary
union all
select * from m_primary;
+-------+----+
| name  | id |
+-------+----+
| name1 |  1 |
| name2 |  2 |
| name1 |  1 |
| name2 |  2 |
+-------+----+
select * from m_primary
union 
select * from m_primary;
+-------+----+
| name  | id |
+-------+----+
| name1 |  1 |
| name2 |  2 |
+-------+----+

-- 实例二：
select * from m_foreign;
+------+--------+------+
| id   | child  | p_id |
+------+--------+------+
|    1 | lili   |    1 |
|    2 | huahua |    1 |
|    4 | jiji   |    2 |
|    5 | keke   |    2 |
+------+--------+------+
select * from m_primary
union
select id,child from m_foreign;
+-------+--------+
| name  | id     |
+-------+--------+
| name1 | 1      |
| name2 | 2      |
| 1     | lili   |
| 2     | huahua |
| 4     | jiji   |
| 5     | keke   |
+-------+--------+

-- 联合查询在使用order by时必须使用limit搭配，才能生效

子查询


+-------+----+
| name  | id |
+-------+----+
| name1 |  1 |
| name2 |  2 |
+-------+----+
select * from m_copy;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    1 | name1 | women |     4 |
|    2 | name2 | man   |     4 |
|    3 | name3 | women |     2 |
|    4 | name4 | man   |     2 |
|    5 | name5 | women |     1 |
|    6 | name6 | women |     1 |
|    7 | name7 | women |     3 |
|    8 | name8 | women |     3 |
|    9 | name1 | man   |     4 |
|   10 | name2 | man   |     4 |
|   11 | name3 | women |     2 |
|   12 | name4 | man   |     2 |
|   13 | name5 | man   |     1 |
|   14 | name6 | man   |     1 |
|   15 | name7 | man   |     3 |
|   16 | name8 | women |     3 |
+------+-------+-------+-------+
-- 标量子查询 查询班级name1 为1 的所有学生
select * from m_copy where class=(select id from m_primary where name="name1");
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    5 | name5 | women |     1 |
|    6 | name6 | women |     1 |
|   13 | name5 | man   |     1 |
|   14 | name6 | man   |     1 |
+------+-------+-------+-------+
-- 列子查询
-- 查询m_primary 表中所有班级的学生
select * from m_copy where class in (select id from m_primary);
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    3 | name3 | women |     2 |
|    4 | name4 | man   |     2 |
|    5 | name5 | women |     1 |
|    6 | name6 | women |     1 |
|   11 | name3 | women |     2 |
|   12 | name4 | man   |     2 |
|   13 | name5 | man   |     1 |
|   14 | name6 | man   |     1 |
+------+-------+-------+-------+

--行子查询
select * from m_copy where (id,sex)=(select max(id),max(sex) from m_copy);
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|   16 | name8 | women |     3 |
+------+-------+-------+-------+

-- 表子查询
-- 找出每个班中id做大的学生
select * from (select * from m_copy order by id desc) as student group by class;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    1 | name1 | women |     4 |
|    3 | name3 | women |     2 |
|    5 | name5 | women |     1 |
|    7 | name7 | women |     3 |
+------+-------+-------+-------+

-- exists 子查询
-- exists 判断后面条件为真还是为假
select * from m_copy where exists (select * from m_primary where id =1 );