子查询、分页查询、联合查询、插入语句、修改语句、删除语句

2021-08-05 本文已影响0人秋天丢了李姑娘

子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式

子查询返回的数据分类

单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
单行多列：返回一行数据中多个列的内容
多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
多行多列：查询返回的结果是一张临时表

子查询常出现的位置

select之后：仅支持单行单列
from之后：支持多行多列
where或having之后：支持单行单列、单行多列、多行单列

子查询实例

单行单列

查询运维部所有员工信息

分析：

首先从departments表中查出运维部的编号

mysql> select dept_id from departments where dept_name='运维部';
+---------+
| dept_id |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

再从employees表中查找该部门编号和运维部编号相同的员工

mysql> select *
    -> from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='运维部'
    -> );

查询2018年12月所有比100号员工基本工资高的工资信息

分析：

首先查到2018年12月100号员工的基本工资

mysql> select basic from salary
    -> where year(date)=2018 and month(date)=12 and employee_id=100;
+-------+
| basic |
+-------+
| 14585 |
+-------+
1 row in set (0.00 sec)

再查询2018年12月所有比100号员工基本工资高的工资信息

mysql> select * from salary
    -> where year(date)=2018 and month(date)=12 and basic>(
    ->   select basic from salary
    ->   where year(date)=2018 and month(date)=12 and employee_id=100
    -> );

查询部门员工人数比开发部人数少的部门

分析：

查询开发部部门编号

mysql> select dept_id from departments where dept_name='开发部';
+---------+
| dept_id |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

查询开发部人数

mysql> select count(*) from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='开发部'
    -> );
+----------+
| count(*) |
+----------+
|       55 |
+----------+
1 row in set (0.00 sec)

分组查询各部门人数

mysql> select count(*), dept_id from employees group by dept_id;
+----------+---------+
| count(*) | dept_id |
+----------+---------+
|        8 |       1 |
|        5 |       2 |
|        6 |       3 |
|       55 |       4 |
|       12 |       5 |
|        9 |       6 |
|       35 |       7 |
|        3 |       8 |
+----------+---------+
8 rows in set (0.01 sec)

查询部门员工人数比开发部人数少的部门

mysql> select count(*), dept_id from employees group by dept_id
    -> having count(*)<(
    ->   select count(*) from employees
    ->   where dept_id=(
    ->     select dept_id from departments where dept_name='开发部'
    ->   )
    -> );
+----------+---------+
| count(*) | dept_id |
+----------+---------+
|        8 |       1 |
|        5 |       2 |
|        6 |       3 |
|       12 |       5 |
|        9 |       6 |
|       35 |       7 |
|        3 |       8 |
+----------+---------+
7 rows in set (0.00 sec)

查询每个部门的人数

分析：

查询所有部门的信息

mysql> select d.* from departments as d;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
|       9 | NULL      |
+---------+-----------+
9 rows in set (0.00 sec)

查询每个部门的人数

mysql> select d.*, (
    ->  select count(*) from employees as e
    ->   where d.dept_id=e.dept_id
    -> ) as amount
    -> from departments as d;
+---------+-----------+--------+
| dept_id | dept_name | amount |
+---------+-----------+--------+
|       1 | 人事部    |      8 |
|       2 | 财务部    |      5 |
|       3 | 运维部    |      6 |
|       4 | 开发部    |     55 |
|       5 | 测试部    |     12 |
|       6 | 市场部    |      9 |
|       7 | 销售部    |     35 |
|       8 | 法务部    |      3 |
|       9 | NULL      |      0 |
+---------+-----------+--------+
9 rows in set (0.00 sec)

多行单列

查询人事部和财务部员工信息

分析：

查询人事部和财务部编号

mysql> select dept_id from departments
    -> where dept_name in ('人事部', '财务部');
+---------+
| dept_id |
+---------+
|       1 |
|       2 |
+---------+
2 rows in set (0.00 sec)

查询部门编号是两个部门编号的员工信息

mysql> select * from employees
    -> where dept_id in (
    ->   select dept_id from departments
    ->   where dept_name in ('人事部', '财务部')
    -> );

查询人事部2018年12月所有员工工资

分析：

查询人事部部门编号

mysql> select dept_id from departments where dept_name='人事部';
+---------+
| dept_id |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

查询人事部员的编号

mysql> select employee_id from employees
    -> where dept_id=(
    ->   select dept_id from departments where dept_name='人事部'
    -> );
+-------------+
| employee_id |
+-------------+
|           1 |
|           2 |
|           3 |
|           4 |
|           5 |
|           6 |
|           7 |
|           8 |
+-------------+
8 rows in set (0.00 sec)

查询2018年12月人事部所有员工工资

mysql> select * from salary
    -> where year(date)=2018 and month(date)=12 and employee_id in (
    ->   select employee_id from employees
    ->   where dept_id=(
    ->     select dept_id from departments where dept_name='人事部'
    ->   )
    -> );
+------+------------+-------------+-------+-------+
| id   | date       | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6252 | 2018-12-10 |           1 | 17016 |  7000 |
| 6253 | 2018-12-10 |           2 | 20662 |  9000 |
| 6254 | 2018-12-10 |           3 |  9724 |  8000 |
| 6255 | 2018-12-10 |           4 | 17016 |  2000 |
| 6256 | 2018-12-10 |           5 | 17016 |  3000 |
| 6257 | 2018-12-10 |           6 | 17016 |  1000 |
| 6258 | 2018-12-10 |           7 | 23093 |  4000 |
| 6259 | 2018-12-10 |           8 | 23093 |  2000 |
+------+------------+-------------+-------+-------+
8 rows in set (0.00 sec)

单行多列

查找2018年12月基本工资和奖金都是最高的工资信息

分析：

查询2018年12月最高的基本工资

mysql> select max(basic) from salary
    -> where year(date)=2018 and month(date)=12;
+------------+
| max(basic) |
+------------+
|      25524 |
+------------+
1 row in set (0.00 sec)

查询2018年12月最高的奖金

mysql> select max(bonus) from salary
    -> where year(date)=2018 and month(date)=12;
+------------+
| max(bonus) |
+------------+
|      11000 |
+------------+
1 row in set (0.00 sec)

查询

mysql> select * from salary
    -> where year(date)=2018 and month(date)=12 and basic=(
    ->   select max(basic) from salary
    ->   where year(date)=2018 and month(date)=12
    -> ) and bonus=(
    ->   select max(bonus) from salary
    ->   where year(date)=2018 and month(date)=12
    -> );
+------+------------+-------------+-------+-------+
| id   | date       | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6368 | 2018-12-10 |         117 | 25524 | 11000 |
+------+------------+-------------+-------+-------+
1 row in set (0.01 sec)

多行多列

查询3号部门及其部门内员工的编号、名字和email

分析

查询3号部门和员工的所有信息

mysql> select d.dept_name, e.*
    -> from departments as d
    -> inner join employees as e
    -> on d.dept_id=e.dept_id;

将上述结果当成一张临时表，必须为其起别名。再从该临时表中查询

mysql> select dept_id, dept_name, employee_id, name, email
    -> from (
    ->   select d.dept_name, e.*
    ->   from departments as d
    ->   inner join employees as e
    ->   on d.dept_id=e.dept_id
    -> ) as tmp_table
    -> where dept_id=3;
+---------+-----------+-------------+-----------+--------------------+
| dept_id | dept_name | employee_id | name      | email              |
+---------+-----------+-------------+-----------+--------------------+
|       3 | 运维部    |          14 | 廖娜      | liaona@tarena.com  |
|       3 | 运维部    |          15 | 窦红梅    | douhongmei@tedu.cn |
|       3 | 运维部    |          16 | 聂想      | niexiang@tedu.cn   |
|       3 | 运维部    |          17 | 陈阳      | chenyang@tedu.cn   |
|       3 | 运维部    |          18 | 戴璐      | dailu@tedu.cn      |
|       3 | 运维部    |          19 | 陈斌      | chenbin@tarena.com |
+---------+-----------+-------------+-----------+--------------------+
6 rows in set (0.00 sec)

分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条
要实现分页功能，实际上就是从结果集中显示第1至100条记录作为第1页，显示第101至200条记录作为第2页，以此类推
分页实际上就是从结果集中“截取”出从M开始，偏移到N的记录。这个查询可以通过LIMIT <M>, <N>子句实现
起始索引从0开始
每页显示内容速算：LIMIT (PAGE-1)*SIZE, SIZE
示例：

// 按employee_id排序，取出前5位员姓名
mysql> select employee_id, name from employees
    -> order by employee_id
    -> limit 0, 5;
+-------------+-----------+
| employee_id | name      |
+-------------+-----------+
|           1 | 梁伟      |
|           2 | 郭岩      |
|           3 | 李玉英    |
|           4 | 张健      |
|           5 | 郑静      |
+-------------+-----------+
5 rows in set (0.00 sec)


// 按employee_id排序，取出前15至20号员姓名
mysql> select employee_id, name from employees
    -> order by employee_id
    -> limit 15, 5;
+-------------+--------+
| employee_id | name   |
+-------------+--------+
|          16 | 聂想   |
|          17 | 陈阳   |
|          18 | 戴璐   |
|          19 | 陈斌   |
|          20 | 蒋红   |
+-------------+--------+
5 rows in set (0.00 sec)

联合查询UNION

作用：将多条select语句的结果，合并到一起，称之为联合操作。
语法：( ) UNION ( )
要求查询时，多个select语句的检索到的字段数量必须一致
每一条记录的各字段类型和顺序最好是一致的
UNION关键字默认去重，可以使用UNION ALL包含重复项

mysql> (select 'yes') union (select 'yes');
+-----+
| yes |
+-----+
| yes |
+-----+
1 row in set (0.00 sec)


mysql> (select 'yes') union all (select 'yes');
+-----+
| yes |
+-----+
| yes |
| yes |
+-----+
2 rows in set (0.00 sec)

例，某生产商有一张原材料表和一张商品表，需要把原材料价格和商品价格一起输出
查询1972年前或2000年后出生的员工

// 普通方法
mysql> select name, birth_date from employees
    -> where year(birth_date)<1972 or year(birth_date)>2000;
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 梁伟      | 1971-08-19 |
| 张建平    | 1971-11-02 |
| 窦红梅    | 1971-09-09 |
| 温兰英    | 1971-08-14 |
| 朱文      | 1971-08-15 |
| 和林      | 1971-12-10 |
+-----------+------------+
6 rows in set (0.01 sec)


// 联合查询的方法
mysql> (
    -> select name, birth_date from employees
    ->   where year(birth_date)<1972
    -> )
    -> union
    -> (
    ->   select name, birth_date from employees
    ->   where year(birth_date)>=2000
    -> );
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 梁伟      | 1971-08-19 |
| 张建平    | 1971-11-02 |
| 窦红梅    | 1971-09-09 |
| 温兰英    | 1971-08-14 |
| 朱文      | 1971-08-15 |
| 和林      | 1971-12-10 |
+-----------+------------+
6 rows in set (0.00 sec)

插入语句

不指定列名的插入

语法格式：

INSERT INTO 表名称 VALUES (值1, 值2,....)

需要为所有列指定值
值的顺序必须与表中列的顺序一致
示例：

# 如果表中已有1号部门，则出错。因为dept_id是主键，不允许重复
mysql> insert into departments values(1, '行政部');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into departments values(10, '行政部');
Query OK, 1 row affected (0.01 sec)

支持多行插入

mysql> insert into employees values
    -> (134, '张三', '2019-5-10', '2000-10-12', 'zhangsan@tedu.cn', '15088772354', 9),
    -> (135, '李四', '2020-8-20', '1999-6-23', 'lisi@tedu.cn', '13323458734', 9);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

指定列名的插入

语法格式：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

列和值的顺序要一致
列名先后顺序不重要
示例：

mysql> insert into departments (dept_name, dept_id) values ('售后部', 11);
Query OK, 1 row affected (0.00 sec)

主键由于是自动增长的，也可以不指定主键的值

mysql> insert into departments (dept_name) values ('咨询部');
Query OK, 1 row affected (0.00 sec)

支持子查询

mysql> insert into employees
    -> (name, hire_date, birth_date, email, phone_number, dept_id)
    -> (
    ->   select name, hire_date, birth_date, email, phone_number, dept_id
    ->   from employees
    ->   where name='张三'
    -> );
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

使用set语句

语法格式：

INSERT INTO 表名 SET 列名1=列值1, 列名2=列值2, ...

示例：

mysql> insert into departments set dept_name='采购部';
Query OK, 1 row affected (0.00 sec)

修改语句

修改单表记录

语法：

UPDATE 表名称 SET 列名称=新值, 列名称=新值, ... WHERE 筛选条件

示例：

# 修改人事部的名称为人力资源部
mysql> update departments set dept_name='人力资源部'
    -> where dept_name='人事部';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

修改多表记录

语法：

UPDATE 表1 AS 表1别名
INNER | LEFT | RIGHT JOIN 表2 AS 表2别名
ON 连接条件
SET 列=值, 列=值, ...
WHERE 连接条件

示例：

# 修改李四所在部门为企划部
mysql> update departments as d
    -> inner join employees as e
    -> on d.dept_id=e.dept_id
    -> set d.dept_name='企划部'
    -> where e.name='李四';

删除记录

删除单表记录

语法：

DELETE FROM 表名 WHERE 筛选条件;

删除的是满足条件的整行记录，而不是某个字段
示例：

# 删除重复的员工张三，只保留一个张三的信息
# 查询张三信息
mysql> select * from employees where name='张三';

# 根据员工编号删除重复的张三
mysql> delete from employees where employee_id=136;
Query OK, 1 row affected (0.00 sec)

删除多表记录

语法：

DELETE 表1别名, 表2别名
FROM 表1 AS 表1别名
INNER | LEFT | RIGHT JOIN 表2 AS 表2别名
ON 连接条件
WHERE 筛选条件

示例：

# 删除9号部门中所有的员工
mysql> delete e
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id
    -> where d.dept_id=9;
Query OK, 2 rows affected (0.00 sec)

清空表

语法：

TRUNCATE TABLE 表名

TRUNCATE不支持WHERE条件
自增长列，TRUNCATE后从1开始；DELETE继续编号
TRUNCATE不能回滚，DELETE可以
效率略高于DELETE
示例：

# 清空wage_grade表
mysql> truncate table wage_grade;
Query OK, 0 rows affected (0.01 sec)