数据查询语句（DQL）

1、基础查询

语法： select 查询列表 from 表名
特点：

查询列表可以是：表中的字段、常量值、表达式、函数
查询的结果是一个虚拟的表格，即显示的结果为保存。

查询单个字段
select 字段 from 表名
查询多个字段
select 字段1,字段2,...,字段n from 表名
查询所有字段
select * from 表名
查询常量值
select 常量值 常量值不来源与表，故无需指定表。
eg: select 100;
查询表达式
select 表达式
eg: select 100*98;
查询函数
select 函数
eg: select version();
起别名
select 字段 as 别名 from 表名
select 常量值/表达式/函数 as 别名

as替换为空格效果一样
常量/表达式/函数的字段是本身
当别名中含有关键字的时候，用引号将别名包围
使用别名的好处：
1. 使用别名能更好地理解数据
2. 如果要查询的字段有重名的情况，可以使用别名区分开

去重
select distinct 字段 from 表名
+号的作用
mysql中的+号只有运算符的作用：
- 两个操作数都为数值型，则做加法运算
- 其中一个为字符型，尝试将字符型转为数值型在进行加法运算，若转化失败，则结果为0。使用ifnull函数可以将判断为null的情况赋值一个数值。
- 只要有一项为null，则结果为null。
字段拼接
将两个字段的信息拼接在一起，需要使用函数concat()连接。
select concat(字段1,...,字段n) as 别名 from 表名

对于字段与关键字冲突的情况，可使用着重号包围字段,注意不是单引号。

2.条件查询

语法：select 查询列表 from 表名 where 筛选条件

分类：
1. 按条件表达式查询：
" <" " >" " = " "<= " " >=" "<>"
2. 按逻辑表达式查询:
" &&" " ||" " !" " and" " or" "not"
3. 模糊查询:
" like" "between and" " in" " is null"

模糊查询

like：一般与统配符配合使用
- 通配符：% 任意多个字符，包含0个字符
  _ 任意单个字符
  支持通过进行转义，也可通过escape指定转义字符

案例1：查询员工名中包含字符a的员工信息
select * from employees where last_name like '%a%'

案例2：查询员工名中第三个字符为'a'第5个字符为‘b’的员工信息
select * from employees where last_name like '_ _a_b%'

between and：
- 包含临界值
- 两个临界值的顺序不能调换
  eg:select * from employees where employee_id between 100 and 120
in:
- 判断某字段的值是否在in列表中
- in列表的值类型必须一致或兼容（可转化）
  eg:select * from employees where job_id in('it_prot','ad_vp','ad_pres');
is null:
- 判断是否为null不能用=，使用is null，is null也只能用来判断null值
- 安全等于：<=>既可以判断null值，也可以判断数值

3.排序查询

语法：
select 查询列表 from 表名 order by 排序列表 asc升序排序，默认
select 查询列表 from 表名 order by 排序列表 desc降序排序

排序列表可为单个字段，多个字段，表达式，函数，别名
例：
select * from employees order by salary desc,employee_id asc;
order一般位于查询语句的最后，limit子句除外

4.常见函数

语法：
select 函数名(实参列表) (from 表）
实参在表中时需要加表名

分类：

单行函数：concat、length、ifnull等
分组函数

1.单行函数

1.字符函数

length()：获取参数值的字节数
concat()：拼接字符串
upper()、lower()：将字母变为大写或小写
substr()、substring()：根据索引获取子字符串。sql中索引从1开始
instr()：返回子字符串在主串中的第一次出现的索引，找不到则返回0
trim()：去除字符串左右空格，也可指定去除左右的字符，但只局限于首尾。
lapd()、rpad()：用指定的字符向左或右填充指定的长度
replace()：将主串中的指定子串替换成另一个指定的字符串。

2. 数学函数

round()：四舍五入，可指定保留的小数位数
ceil()：向上取整
floor()：向下取整
truncate()：截断。从小数点后指定位截断
mod()：取余

3. 日期函数

now()：返回当前系统日期+时间
curdate():返回当前日期，不包含时间
curtime():返回当前时间，不包含日期
year():获取日期时间中的年
month():获取日期时间中的月。其他类似
str_to_data():将字符串通过指定的格式转为日期
data_format():将日期转为字符

4. 其他函数

version():查看版本号
database()：查看当前数据库
user()：查看当前用户

5. 流程控制函数

if(): if-else效果
case():

switch-case效果：

case 判断的字段或表达式
when 常量1 then 语句1
when 常量2 then 语句2
...
else 语句
end case;

if-elseif-elseif-else效果：

case
when 条件1 then 语句1
when 条件2 then 语句2
...
else 语句
end case;

2. 分组函数

用作统计使用，又称为聚合函数或组函数。与单行函数的区别在于，单行函数是对每一行都进行操作并且每一行都返回一个值。而分组函数，是将一组中的值进行操作返回只一个值

sum：求和
avg：平均值
min：最小值
max：最大值
count：计算非空个数

注：

sum,avg支持数值型，min，max，count支持所有类型
忽略null值
可以和distinct搭配
count函数的详细介绍：
- count（*）计算行数，只要一行中有一列的值不为null就统计。统计行数最常用的方式
- count(常量值)：加入一列值为常量的列并统计数量
- count（字段）：统计该字段不为null 的行数
和分组函数一同查询的字段要求是group by后的字段

5. 分组查询

语法：

select 分组函数,列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组列表
【order by子句】

筛选包括分组前的筛选和分组后的筛选，分组前的筛选在group by关键字前使用where即可，分组后的筛选在group by后使用having关键字。能在分组前筛选的现在分组前筛选
可使用多个字段作为分组的条件

6. 连接查询

又称多表查询，当查询的字段来自多个表时，就会用到连接查询。
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
根据相对应的连接条件将两个表的数据对应连接起来

1. 等值连接

语法

select 查询列表
from 表列表
where 等值条件

对于在两个表中都有的字段，在查询时就需指定表名，可通过起别名来简写。如果为表起了别名，则查询的字段就不能使用原来的表名。

多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表取别名
可以与其他所有子句搭配使用

2.非等值连接

即将等值连接中的连接条件中的等于号换成大于小于或不等于等。

select salary,grade_level
from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal
and g.grade_level = 'A';

3.自连接

自己与自己相连，即在同一张表中重复查询。通过起不同的别名来使表具有不同的意义。

7.子查询

出现在其他语句中select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：

按子查询出现的位置
select后面：
仅支持标量子查询

from后面：
表子查询

where或having后面：
标量子查询、列子查询、行子查询

exists后面（相关子查询）：
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
行子查询（结果集有一行多列）
列子查询（结果集有一列多行）
表子查询（结果集一般为多行多列）

特点：

子查询都放在小括号内
子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
子查询优先于主查询执行，主查询使用了子查询的执行结果
子查询根据查询结果的行数不同分为以下两类：
1. 单行子查询
  - 结果集只有一行
  - 一般搭配单行操作符使用：> < = <> >= <=
  - 非法使用子查询的情况：
    a、子查询的结果为一组值
    b、子查询的结果为空
2. 多行子查询
  - 结果集有多行
  - 一般搭配多行操作符使用：any、all、in、not in
  - in：属于子查询结果中的任意一个就行
  - any和all往往可以用其他查询代替

1. where或having后面

1.标量子查询（单行子查询）

案例：查询最低工资的员工姓名和工资
①最低工资
select min(salary) from employees
②查询员工的姓名和工资，要求工资=①

select last_name,salary
from employees
where salary=(
   select min(salary) from employees
);

其中小括号内的查询结果为一行一列即标量子查询，并作为另一个查询的条件

2.列子查询（多行子查询）

案例：查询所有是领导的员工姓名
①查询所有员工的 manager_id

select manager_id
from employees

②查询姓名，employee_id属于①列表的一个

select last_name
from employees
where employee_id in(
   select manager_id
   from employees
);

select后面

仅支持标量子查询
案例：查询每个部门的员工个数

select d.*,(
        select count(*)
        from employees e
        where e.department_id = d.department_id
    ) 个数
from department_id d;

from后面

子查询结果作为一张表，必须起别名
案例：查询每个部门的平均工资的工资等级
①查询每个部门的平均工资

select avg(salary),department_id
from employees
group by department_id

②连接①的结果和job_grade表，筛选条件平均工资between lowest and higheset

select 
from (
    select avg(salary) ag,department_id
    from employees
    group by department_id
) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal;

exist后面（相关子查询）

可用in代替
语法：

exists(完整的查询语句)

子查询是否有值

select exists(select employee_id from employees where salary= 300000);

8.分页查询

一、应用场景：

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
当要查询的条目数太多，一页显示不全

二、语法

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

注意：
offset代表的是起始的条目索引，默认从0卡死
limit子句放在查询语句的最后
size代表的是显示的条目数
公式：
假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)* size,size;

9.联合查询

语法：

查询语句1
union
查询语句2
union

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接，但查询的信息一致
将多条查询语句的结果合并成一个结果。
特点：
- 多条查询语句的查询列数是一致的
- 多条查询语句的查询的每一列的类型和顺序一致
- union关键字默认去重，使用union all可以包含重复项
  案例：查询部门编号>90或邮箱包含a的员工信息

select *
from employees
where email like '%a%' or department_id>90;

可写成
select *from employees where email like '%a%'
union 
select * from employees where department_id>90;