SQL基本语句及用法

一.基本SQL语句用法及概述1.常用MySQL命令# 查看所有数据库SHOW DATABASES;# 切换指定数据库USE nsd2021;# 查看当前库中所有的表SHOW TABLES;# 查看表结构DESC departments;# 查看当前所处的数据库SELECT DATABASE();# 查看当前登陆用户SELECT USER();# 查看版本SELECT VERSION();[root

kali_yao

11575人浏览 · 2021-09-10 09:10:54

kali_yao · 2021-09-10 09:10:54 发布

1）查询的字段列表可以是字段、常量、表达式、函数等

2）使用别名，字段名和别名之间可以用空格或关键字AS与as指定别名

一.基本SQL语句用法及概述

1.常用MySQL命令

# 查看所有数据库
SHOW DATABASES;
# 切换指定数据库
USE nsd2021;
# 查看当前库中所有的表
SHOW TABLES;
# 查看表结构
DESC departments;
# 查看当前所处的数据库
SELECT DATABASE();
# 查看当前登陆用户
SELECT USER();
# 查看版本
SELECT VERSION();
[root@mysql1 ~]# mysql --version #不进库查看
[root@mysql1 ~]# mysql -V

2.语法规范

关键字不区分大小写，但建议关键字大写

表名、列名建议小写

每条命令最好用分号结尾，当然，你用`\G结尾也可以

每条命令根据需要，可以进行缩进或换行(最好是关键字单独占一行)，如：
mysql> SELECT
    -> name, email
    -> FROM
    -> employees;
注释

单行注释
mysql> # select * from departments
mysql> -- select * from departments
多行注释
mysql> /*
   /*> SELECT
   /*> *
   /*> FROM
   /*> departments;
   /*> */

3.SQL语句分类

数据查询语言（Data Query Language, ）DQL

负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。

数据定义语言 (Data Definition Language,）DDL

负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成

数据操纵语言（Data Manipulation Language,）DML

负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

数据控制语言 (Data Control Language)

它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。

二.数据查询语言

1.基础查询

SELECT 查询的字段列表 FROM 表;

1）查询的字段列表可以是字段、常量、表达式、函数等

# 查单个字段
select dept_name from departments;
# 查多个字段
select name, email from employees;
# 查所有字段
select * from departments;
# 使用表达式
select date, employee_id, basic+bonus from salary;
# 查询常量
select 100;
# 查询表达式
select 10+5;
# 查询函数
select version();
# 查询函数，统计salary共有多少行记录
select count(*) from salary;

2）使用别名，字段名和别名之间可以用空格或关键字AS与as指定别名

mysql> select dept_id 部门编号, dept_name AS 部门名 from departments;

3）去重 distinct

select dept_id from employees;
select distinct dept_id from employees;

4）使用concat函数进行字符串拼接

select concat(name, '-', phone_number) from employees;

2.条件查询

SELECT 查询的字段列表 FROM 表 WHERE 条件;

1）件运算符

>: 大于

<: 小于

=: 等于

>=: 大于等于

<=: 小于等于

!=: 不等于

select * from departments where dept_id>3; 
select * from departments where dept_id<3;
select * from departments where dept_id=3;
select * from departments where dept_id!=3;
select * from departments where dept_id>=3;
select * from departments where dept_id<=3;

2）逻辑运算符

and(&&)、or(||)、not(!)

select * from departments where dept_id>1 and dept_id<5;
select * from departments where dept_id<3 or dept_id>6;
select * from departments where not dept_id<=6;

3.模糊查询

- like: 包含
- between xxx and yyy : 在xxx和yyy之间的
- in：在列表中的
- is null：为空，相当于python的None
- is not null：非空

%匹配0到多个任意字符

_匹配一个字符
# %匹配0到多个任意字符
select name, email from employees where name like '张%';
# _匹配一个字符
select name, email from employees where name like '张_';
select * from departments where dept_id between 3 and 5;
select * from departments where dept_id in (1, 3, 5, 8);
# 匹配部门名为空的记录
select * from departments where dept_name is null;
# 查询部门名不为空的记录
select * from departments where dept_name is not null;

4.排序 order by

SELECT 查询的字段列表 FROM 表 ORDER BY 排序列表 [asc|desc];

排序：默认升序

select name, birth_date from employees where birth_date>'19980101';
# 默认升序排列
select name, birth_date from employees where birth_date>'19980101' order by birth_date;
# 降序排列
select name, birth_date from employees where birth_date>'19980101' order by birth_date desc;

三.数据查询语言之函数应用

1.常用函数分类

1）按使用方式分为：

单行函数

分组函数

2）按用途分为

字符函数

数学函数

日期函数

流程控制函数

SELECT 函数(参数) FROM 表;

2.函数应用

1）字符函数实例：

LENGTH(str)：返字符串长度，以(字节)为单位
select length('abc');
select length('你好');
select name, email, length(email) from employees where name='李平';
CHAR_LENGTH(str): 返回字符串长度，以(字符)为单位
select char_length('abc');
select char_length('你好');
CONCAT(s1,s2，...): 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL
# 拼接字符串
mysql> select concat(dept_id, '-', dept_name) from departments;
UPPER(str)和UCASE(str): 将字符串中的字母全部转换成大写
select name, upper(email) from employees where name like '李%';
LOWER(str)和LCASE(str):将str中的字母全部转换成小写
# 转小写
select lower('HelloWorld');
SUBSTR(s, start, length): 从子符串s的start位置开始，取出length长度的子串，位置(从1)开始计算
select substr('hello world', 7);
# 取子串，下标从7开始取出3个
select substr('hello world', 7, 3);
INSTR(str,str1)：返回str1参数，在str参数内的位置
# 子串在字符串中的位置
select instr('hello world', 'or');
select instr('hello world', 'ol');
TRIM(s): 返回字符串(s删除了两边空格之后的字符串)
select trim('  hello world.  ');
数学函数实例

ABS(x)：返回x的绝对值
select abs(-10);（负—>正）
PI(): 返回圆周率π，默认显示6位小数
select pi();
MOD(x,y): 返回x被y除后的余数
select mod(10, 3);
CEIL(x)、CEILING(x): 返回不小于x的最小整数
select ceil(10.1);
FLOOR(x): 返回不大于x的最大整数
select floor(10.9);
ROUND(x)、ROUND(x,y): 前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位
select round(10.6666);返回最接近于x的整数，即对x进行四舍五入
select round(10.6666, 2);返回最接近x的数，其值保留到小数点后面y位

2）日期和时间函数实例

CURDATE()、CURRENT_DATE(): 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定
select curdate();当前日期按照"YYYY-MM-DD"
select curdate() + 0;格式根据函数用在字符串或是数字语境中而定
NOW(): 返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定
select now();式为"YYYY_MM-DD HH:MM:SS"
select now() + 0;具体格式根据函数用在字符串或数字语境中而定
UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date): 前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数
select unix_timestamp();
FROM_UNIXTIME(date): 和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间
select from_unixtime(0);
MONTH(date)和MONTHNAME(date):前者返回指定日期中的月份，后者返回指定日期中的月份的名称
select month('20211001120000');返回指定日期中的月份
select monthname('20211001120000');返回指定日期中的月份的名称
DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d): DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二
select dayname('20211001120000');返回星期*
select dayname('20211001');
WEEK(d): 计算日期d是一年中的第几周
select week('20211001');
DAYOFYEAR(d)、DAYOFMONTH(d)：前者返回d是一年中的第几天，后者返回d是一月中的第几天
select dayofyear('20211001');
YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time): YEAR(date)返回指定日期对应的年份，范围是1970到2069；QUARTER(date)返回date对应一年中的季度，范围是1到4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值
select year('20211001');返回指定日期对应的年份
select quarter('20211001');回date对应一年中的季度
流程控制函数实例

IF(expr,v1,v2): 如果expr是TRUE则返回v1，否则返回v2
select if(3>0, 'yes', 'no');
select name, dept_id, if(dept_id=1, '人事部', '非人事部')  from employees where name='张亮';
IFNULL(v1,v2): 如果v1不为NULL，则返回v1，否则返回v2
select dept_id, dept_name, ifnull(dept_name, '未设置') from departments;
insert into departments(dept_id) values(9);
select dept_id, dept_name, ifnull(dept_name, '未设置') from departments; 
CASE expr (WHEN v1)( THEN r1) [WHEN v2 THEN v2] [ELSE rn] END: 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn
mysql> select dept_id, dept_name,
    -> case dept_nam
    -> when '运维部' then '技术部门'
    -> when '开发部' then '技术部门'
    -> when '测试部' then '技术部门'
    -> when null then '未设置'
    -> else '非技术部门'
    -> end as '部门类型'
    -> from departments;
# 查看字段，名字。匹配 名字是 当是v1就是r1 ，是v2 就是v2，否则就返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的值
select dept_id, dept_name,
    -> case 
    -> when dept_name='运维部' then '技术部门'
    -> when dept_name='开发部' then '技术部门'
    -> when dept_name='测试部' then '技术部门'
    -> when dept_name is null then '未设置'
    -> else '非技术部门'
    -> end as '部门类型'
    -> from departments;

3）分组函数

用于统计，又称为聚合函数或统计函数

sum() ：求和

select employee_id, sum(basic+bonus) from salary where employee_id=10 and year(date)=2018;

avg() ：求平均值

select employee_id, avg(basic+bonus) from salary where employee_id=10 and year(date)=2018;

max() ：求最大值

select employee_id, max(basic+bonus) from salary where employee_id=10 and year(date)=2018;

min() ：求最小值

select employee_id, min(basic+bonus) from salary where employee_id=10 and year(date)=2018;

count() ：计算个数

select count(*) from departments;

四.数据查询语言之分组查询

1.分组查询的概述

在对数据表中数据进行统计时，可能需要按照一定的类别分别进行统计。比如查询每个部门的员工数。

使用GROUP BY按某个字段，或者多个字段中的值，进行分组，字段中值相同的为一组

语法格式

查询列表必须是分组函数和出现在（GROUP BY）后面的字段

通常而言，分组前的数据筛选放在where子句中，分组后的数据筛选放在having子句中
SELECT 字段名1(要求出现在group by后面)，分组函数(),……
FROM 表名
WHERE 条件
GROUP BY 字段名1，字段名2
HAVING 过滤条件
ORDER BY 字段;
应用实例

查询每个部门的人数
select dept_id, count(*) from employees group by dept_id;
查询每个部门中年龄最大的员工
select dept_id, min(birth_date) from employees group by dept_id;
查询每个部门入职最晚员工的入职时间
select dept_id, max(hire_date) from employees group by dept_id;
统计各部门使用tedu.cn邮箱的员工人数
mysql> select dept_id, count(*) from employees where email like '%@tedu.cn' group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        5 |
|       2 |        2 |
|       3 |        4 |
|       4 |       32 |
|       5 |        7 |
|       6 |        5 |
|       7 |       15 |
|       8 |        1 |
+---------+----------+
8 rows in set (0.00 sec)
查看员工2018年工资总收入，按总收入进行降序排列
mysql> select employee_id, sum(basic+bonus) as total from salary where year(date)=2018 group by employee_id order by total desc;
查询部门人数少于10人
mysql> select dept_id, count(*) from employees where count(*)<10 group by dept_id;
ERROR 1111 (HY000): Invalid use of group function

mysql> select dept_id, count(*) from employees group by dept_id having count(*)<10;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        8 |
|       2 |        5 |
|       3 |        6 |
|       6 |        9 |
|       8 |        3 |
+---------+----------+
5 rows in set (0.00 sec)

五.数据查询语言之连接查询

1.连接查询的概述

也叫多表查询。常用于查询字段来自于多张表

如果直接查询两张表，将会得到笛卡尔积
select name, dept_name from employees, departments;
通过添加有效的条件可以进行查询结果的限定
select name, dept_name from employees, departments where employees.dept_id=departments.dept_id;

2.连接分类

按功能分类

内连接(重要)

等值连接

非等值连接

自连接

外连接

左外连接(重要)

右外连接(重要)

全外连接(mysql不支持，可以使用UNION实现相同的效果)

交叉连接

按年代分类

SQL92标准：仅支持内连接

SQL99标准：支持所有功能的连接

3.SQL99标准多表查询

语法格式
SELECT 字段... 
FROM 表1 [AS] 别名 [连接类型]
JOIN 表2 [AS] 别名
ON 连接条件
WHERE 分组前筛选条件
GROUP BY 分组
HAVING 分组后筛选条件
ORDER BY 排序字段
1）内连接

语法格式
select 查询列表
from 表1 别名
inner join 表2 别名 on 连接条件
inner join 表3 别名 on 连接条件
[where 筛选条件]
[group by 分组]
[having 分组后筛选]
[order by 排序列表]
2）等值连接

查询每个员工所在的部门名
select name, dept_name
    -> from employees
    -> inner join departments
    -> on employees.dept_id=departments.dept_id;
查询每个员工所在的部门名，使用别名
select name, dept_name
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id;
查询每个员工所在的部门名，使用别名。两个表中的同名字段，必须指定表名
select name, d.dept_id, dept_name
    -> from employees as e
    -> inner join departments as d
    -> on e.dept_id=d.dept_id;
查询11号员工的名字及2018年每个月工资
select name, date, basic+bonus as total
    -> from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018 and e.employee_id=11;
查询2018年每个员工的总工资
select name, sum(basic+bonus) from employees
    -> inner join salary
    -> on employees.employee_id=salary.employee_id
    -> where year(salary.date)=2018
    -> group by name;
查询2018年每个员工的总工资，按工资升序排列
select name, sum(basic+bonus) as total from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018
    -> group by name
    -> order by total;
查询2018年总工资大于30万的员工，按工资降序排列
select name, sum(basic+bonus) as total from employees as e
    -> inner join salary as s
    -> on e.employee_id=s.employee_id
    -> where year(s.date)=2018
    -> group by name
    -> having total>300000
    -> order by total desc;
3）非等值连接 between
附：创建工资级别表

创建表语法：
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
创建工资级别表：

id：主键。仅作为表的行号

grade：工资级别，共ABCDE五类

low：该级别最低工资

high：该级别最高工资
mysql> use nsd2021;
mysql> create table wage_grade
    -> (
    -> id int,
    -> grade char(1),
    -> low int,
    -> high int,
    -> primary key (id));
向表中插入数据：

语法:
INSERT INTO 表名称 VALUES (值1, 值2,....);
向wage_grade表中插入五行数据：
mysql> insert into wage_grade values
    -> (1, 'A', 5000, 8000),
    -> (2, 'B', 8001, 10000),
    -> (3, 'C', 10001, 15000),
    -> (4, 'D', 15001, 20000),
    -> (5, 'E', 20001, 1000000);
查询2018年12月员工基本工资级别
select employee_id, date, basic, grade
    -> from salary as s
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date)=2018 and month(date)=12;
查询2018年12月员工各基本工资级别的人数
select grade, count(*)
    -> from salary as s
    -> inner join wage_grade as g
    -> on s.basic between g.low and g.high
    -> where year(date)=2018 and month(date)=12
    -> group by grade;
查询2018年12月员工基本工资级别，员工需要显示姓名
select name, date, basic, grade
    -> from salary as s
    -> inner join employees as e
    -> on s.employee_id=e.employee_id
    -> inner join wage_grade
    -> on basic between low and high
    -> where date='20181210'
    -> order by grade, basic;
4）自连接

要点：

将一张表作为两张使用

每张表起一个别名

查看哪些员的生日月份与入职月份相同
select e.name, e.hire_date, em.birth_date
    -> from employees as e
    -> inner join employees as em
    -> on month(e.hire_date)=month(em.birth_date)
    -> and e.employee_id=em.employee_id;
5）外连接的概述

常用于查询一个表中有，另一个表中没有的记录

如果从表中有和它匹配的，则显示匹配的值

如要从表中没有和它匹配的，则显示NULL

外连接查询结果=内连接查询结果+主表中有而从表中没有的记录

左外连接中，left join左边的是主表left outer join

右外连接中，right join右边的是主表right outer join

左外连接和右外连接可互换，实现相同的目标

6）左外连接

语法
SELECT tb1.字段..., tb2.字段
FROM table1 AS tb1
LEFT OUTER JOIN table2 AS tb2 
ON tb1.字段=tb2.字段
查询所有部门的人员以及没有员工的部门
select d.*, e.name
    -> from departments as d
    -> left outer join employees as e
    -> on d.dept_id=e.dept_id;
7）右外连接

语法
SELECT tb1.字段..., tb2.字段
FROM table1 AS tb1
RIGHT OUTER JOIN table2 AS tb2 
ON tb1.字段=tb2.字段
查询所有部门的人员以及没有员工的部门
select d.*, e.name
    -> from employees as e
    -> right outer join departments as d
    -> on d.dept_id=e.dept_id;
8）交叉连接 cross join

返回笛卡尔积

语法：
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
查询员工表和部门表的笛卡尔积
select name, dept_name
    -> from employees
    -> cross join departments;
附：授予管理员root可以通过任意地址访问数据库，密码是NSD2021@tedu.cn。默认情况下，root只允许在本机访问
grant all on *.* to root@'%' identified by 'NSD2021@tedu.cn';
向部门表中插入数据：
insert into departments(dept_name) values('采购部');

六.数据查询语言之子查询

1.子查询的概述

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

子查询返回的数据分类
        单行单列：返回的是一个具体列的内容，可以理解为一个单值数据

        单行多列：返回一行数据中多个列的内容

        多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围

        多行多列：查询返回的结果是一张临时表

子查询常出现的位置
        select之后：仅支持单行单列

        from之后：支持多行多列

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

2.子查询实例

1）单行单列

查询运维部所有员工信息

分析：
首先从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)

2）多行单列

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

分析：

查询人事部和财务部编号

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)

3）单行多列

查找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)

4）多行多列

查询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)

5）分页查询

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

6）联合查询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)

七.插入语句DDL

1.不指定列名的插入

语法格式：

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

2.指定列名的插入

语法格式：

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

3.使用set语句

语法格式：

INSERT INTO 表名 SET 列名1=列值1, 列名2=列值2, ...
示例：
mysql> insert into departments set dept_name='采购部';
Query OK, 1 row affected (0.00 sec)

4.修改语句

修改单表记录
语法：

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='李四';

5.删除记录

删除单表记录
语法：
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)

6.删除多表记录

语法：

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)

7.清空表

语法：
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)