1. 基础查询

1.1 语法

select 要查询的字段
from 表名;
  • 查询列表可以使：表中的字段、常量、表达式、函数
  • 查询的结果是一个虚拟的表格

1.2 示例

  • 查询表中的单个字段：
select last_name
from employees;
  • 查询表中的多个字段：
select last_name, salary, email
from employees;
  • 查询表中的所有字段
select * 
from employees;
查询表中所有字段时，如果对顺序没有要求，可以直接使用*代替所有，如果需要对字段排序，则需要依次指定所有的字段
  • 查询常量
select 100;
select 'hello';
select 100 * 98;
  • 查询函数
select version();
......

1.3 别名

  • 使用as关键字
select 100 * 99 as 结果;

select last_name as 姓, first_name as 名
from employees;
  • 省略as关键字
select last_name 姓, first_name 名
from employees;
一般常用的是第二种省略as关键字的写法

1.4 去重

如果查询的结果中有多个相同行，使用distinct关键字即可去除重复行并保留其中一个

select distinct department_id
from employees;

1.5 拼接

select concat(last_name,first_name) as 姓名 from employees;
连接多个字段为一个时需要使用concat()函数，不能使用+进行连接。+在SQL中只代表运算符。如果使用+进行运算，会尝试将非数值类型转换为数值类型进行计算，如果转换失败，则将其转换为数值0计算。需要注意的是，null值与任何值进行计算结果都是null，如果要解决这一问题，可以使用ifnull(null值字段名, 默认值)函数设定默认值。

2. 条件查询

2.1 语法

select 查询列表
from 表明
where 筛选条件;

即在基础查询的基础上使用where关键字，对结果进行筛选，只显示符合条件的数据

2.2 分类

  • 按条件表达式筛选：>, <, =, !=, <>, >=, <=
  • 按逻辑表达式筛选：or, and, not, &&, ||, !
  • 模糊查询：like, between and, in, is null

2.3 示例

  • 按条件表达式筛选
# 查询工资大于12000的员工
select *
from employees
where salary > 12000;
  • 按逻辑表达式筛选
# 查询工资在10000到20000之间的员工的员工名以及奖金
select last_name, salary
from employees
where salary >= 10000
  and salary <= 20000;
# 查询部门编号不是在90-110之间的或者工资高于15000的员工的信息
select *
from employees
where not (department_id >= 90 and department_id <= 110)
   or salary > 1500;
  • 模糊查询：like '条件'：可以使用通配符%和_，%表示任意数量的任意字符，_表示单个任意字符
# 查询员工名中包含字符a的员工信息
select *
from employees
where last_name like '%a%';

# 查询员工名中第三个字符为e，第五个字符为a的员工名和工资
select last_name, salary
from employees
where last_name like '__n_l%';

如果需要查询包含这两个通配符的字段值，则需要使用通配符：

# 查询员工名中第二个字符为下划线的
# 可以使用escape指定一个转义字符
select last_name, salary
from employees
where last_name like '_$_%' escape '$';

其中，escape关键字可以指定某个符号为转义字符，如果不使用该关键字，也可以用\代替

# 查询员工编号在100-120之间的员工信息
select *
from employees
where employee_id between 100 and 120;
这种方式包含两个临界值，并且临界值不能交换顺序
# 查询员工编号在100-120之间的员工信息
select *
from employees
where employee_id between 100 and 120;
# 查询没有奖金的员工和奖金率
select last_name, commission_pct
from employees
where commission_pct is null;

此外，还有一个运算符<=>可以用来判断是否为空，也可以用来判断数值类型

# 安全等于 <=> 可以判断null值，也可以判断普通值
select last_name, commission_pct
from employees
where commission_pct <=> null;

3. 排序查询

3.1 语法

select 查询列表
from 表名
[where 筛选条件]
order by 排序列表 [asc | desc];

  • 特点：

    • asc表示升序，desc表示降序
    • 可以支持单个字段、多个字段、表达式、函数、别名等
    • 一般放在查询语句的最后面，limit子句除外

3.2 示例

  • 按字段排序
# 查询部门编号大于等于90的员工信息，要求按入职时间先后进行排序 【按字段排序】
select *
from employees
where department_id >= 90
order by hiredate;

# 查询员工信息，按年薪高低排序 【按表达式排序】
select *, salary * 12 * (1 + ifnull(commission_pct, 0)) 年薪
from employees
  • 按函数值排序
# 按姓名的长度显示员工的姓名和工资 【按函数值排序】
select length(last_name) 字段长度, last_name, salary
from employees
order by 字段长度 desc;
  • 按多个字段排序
# 查询员工信息，先按工资排序，再按员工编号排序
select *
from employees
order by salary desc, employee_id;

4. 常用函数

4.1 字符函数

  • length：计算字符长度
select length('hxuanyu');

select length('数据库');
一个中文字符在utf8模式下占用3个字节
  • concat：拼接字符
select concat(first_name, '_', last_name)
from employees;
  • substr/substring：字符串裁切
select substr('hxuanyu', 2, 5);
  • upper/lower：将字母转换为大写或小写
select upper('hxuanyu');
select lower('HXUANYU');
  • instr：查找指定子串的索引
select instr('hxuanyu', 'xuan');
  • trim：去除字符串两端的指定字符，默认为空格
select trim('   hxuanyu     ');
select trim('a' from 'aaaaaaaahxuanyuaaaaaaaa') as output;
上述示例中即去除字符串两端的字符a，同时也可以将a写为aa，此时将aa看做一个整体，从两端依次去除
  • lpad：左侧填充指定字符或字符串至指定长度
select lpad('hxy', 10, '*');
如果原字符串长度已经超过指定长度，则从右侧进行截断
  • rpad：从右侧填充指定字符至指定长度
  • replace：替换所有指定子串为新的值
select replace('hello world', 'world', 'sql');

4.2 数学函数

  • round ：取绝对值
# round 取绝对值，负数按绝对值运算
select round(1.65);
select round(-1.65);
# 保留小数
select round(1.324, 2);
  • ceil：向上取整，返回大于等于该参数的最小参数
# ceil 向上取整 返回大于等于该参数的最小整数
select ceil(1.02);
  • floor：向下取整，返回小于等于该参数的最大整数
# floor 向下取整，返回小于等于该参数的最大整数
select floor(9.99);
select floor(-9.99);
  • truncate：截断，不进行四舍五入
# truncate 截断 截断，不四舍五入
select truncate(1.65, 1);
  • mod：取余
# mod 取余  a-a/b*b
select mod(10, 3);

4.3 日期函数

  • now：返回当前系统日期+时间
# now 返回当前系统日期+时间
select now();
  • curdate：返回当前系统日期，不包含时间
# curdata 返回当前系统日期，不包含时间
select curdate();
  • curtime()：返回当前系统时间，不包含日期
# curtime 返回当前时间，不包含日期
select curtime();
  • 获取指定的部分
# 获取指定的部分：年月日时分秒
select year(now()) 年;
select year('1998-1-1') 年;
select month(now()) 月;
select monthname(now()) 月;
  • str_to_date()：将日期格式的字符串转换成指定格式的日期
# str_to_date 将日期格式的字符转换成指定格式的日期
select str_to_date('9-13-1999', '%c-%d-%y');

# 查询入职日期为4.3的员工
select *
from employees
where hiredate = str_to_date('4-3 1992', '%c-%d %Y');
  • date_format()：将日期转换成字符
# date_format：将日期转换成字符
select date_format(now(), '%y年%m月%d日');
# 查询有奖金的员工的入职日期，格式：xx月/
select last_name, DATE_FORMAT(hiredate, '%m月/%d日 %y年') 入职日期
from employees
where commission_pct is not null;

日期转义符格式

4.4 其他函数

select version();
select database();
select user();
还有很多内置的函数，只不过开发过程中不常用

4.5 流程控制函数

  • if：实现流程控制
# if : 实现类似于if-else的效果
select if(10 > 5, '大', '小');
select last_name, commission_pct, if(commission_pct is null, '没奖金', '有奖金') 备注
from employees;
  • case：实现分支结构
# 查询员工的工资
select salary     原始工资,
       department_id,
       case department_id
           when 30 then salary * 1.1
           when 40 then salary * 1.2
           when 50 then salary * 1.3
           else salary
           end as 新工资
from employees;

# case函数第二种使用方法
/*
 case
 when 条件1 then 要显示的值1或语句
 when 条件2 then 要显示的值2或语句
 ...
 else 默认
 end
 */

select salary,
       case
           when salary > 20000 then 'A'
           when salary > 15000 then 'B'
           when salary > 10000 then 'C'
           else 'D'
           end as 工资级别
from employees;

4.6 分组函数

功能：用于统计使用，又称为聚合函数或统计函数、组函数

分类：sum求和、avg平均值、max最大值、min最小值、count计数

select sum(salary)
from employees;
select avg(salary)
from employees;
select min(salary)
from employees;
select max(salary)
from employees;
select count(salary)
from employees;

select sum(salary) 和, round(avg(salary), 2) 平均, max(salary) 最大, min(salary) 最小, count(salary) 个数
from employees;
  • sum()和avg()只支持处理数值类型，min()、max()支持可以比较类型
  • 五个分组函数均忽略null值
  • 可以与distinct搭配使用
select sum(distinct salary), sum(salary)
from employees;
  • count(字段)用于统计某一字段非空的行数
  • count(*)可以统计整个表的总行数

  • count(1)或count(常量)也可以统计总行数

    • 在myIsam引擎中，count(*)效率最高
    • 在innoDB引擎中，count(1)和count(*)效率差不多
和分组函数一同查询的字段必须是group by后的字段

5. 分组查询

5.1 语法

select 分组函数， 列（要求出现在group by 后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 后置筛选条件]
[order by 子句];
注意点：查询列表必须是分组函数group by后出现的字段

5.2 特点

  • 分组查询中的筛选条件分为两类
数据源位置关键字
分组前筛选原始表group by子句之前where
分组后筛选分组后的结果集group by子句之后having
  • 分组函数做条件，肯定放在having子句中，因为分组函数在原始表中一定不存在。能用分组前筛选条件的字段尽量放在分组前筛选中，可以提高性能

5.3 示例

  • 简单分组查询
# 查询每个部门的最高工资
select max(salary), job_id
from employees
group by job_id;

# 查询每个位置上的部门个数
select count(*), location_id
from departments
group by location_id;
  • 添加筛选条件
# 添加筛选条件
# 查询邮箱中包含a字符的，每个部门的平均工资
select avg(salary), department_id
from employees
where email like '%a%'
group by department_id;

# 查询有奖金的每个领导手下员工的最高工资
select max(salary), manager_id
from employees
where commission_pct is not null
group by manager_id;
  • 添加分组后筛选条件
# 添加分组后筛选条件
# 查询哪个部门的员工个数大于2
/*
 分步1：查询每个部门的员工的个数
 分步2：根据分组查询后的结果集进行筛选
 */

select count(*), department_id
from employees
group by department_id
having count(*) > 2;

# 查询每个工种有奖金的员工的最高工资，且最高工资大于12000的工种和最高工资
/*
 1.查询每个工种有奖金员工的最高工资
 2.根据1的结果筛选最高工资大于12000
 */

select max(salary), job_id
from employees
where commission_pct is not null
group by job_id
having max(salary) > 12000;

# 查询领导编号大于102的每个领导手下的最低工资且大于5000的领导以及其最低工资
select min(salary), manager_id
from employees
where manager_id > 102
group by manager_id
having min(salary) > 5000;
  • 按表达式或函数分组
# 按表达式或函数分组
# 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些
select count(*), length(last_name) len_name
from employees
group by length(last_name)
having count(*) > 5;
  • 按多个字段进行分组
# 按多个字段进行分组
# 查询每个部门，每个工种的员工的平均工资
select avg(salary), department_id, job_id
from employees
group by department_id, job_id;
  • 分组后排序
# 添加排序
# 查询每个部门，每个工种的员工的平均工资，并且按平均工资的高低显示
select avg(salary), department_id, job_id
from employees
where department_id is not null
group by department_id, job_id
having avg(salary) > 10000
order by avg(salary)
desc;

6. 连接查询

6.1 分类

  • 按年代分：

    • sql92：仅仅支持内连接
    • sql99：支持所有的内连接、外连接、交叉连接。外连接在MySQL中只支持左外连接和右外连接

  • 按功能分：

    • 内连接

      • 等值连接
      • 非等值连接
      • 自连接

    • 外连接

      • 左外连接
      • 右外连接
      • 全外连接
    • 交叉连接

6.2 SQL92标准等值连接

  • 多表等值连接的结果为多表的交集部分
  • n表连接时，至少需要n - 1个连接条件
  • 多表的顺序没有要求
  • 为了防止冲突，一般需要为表起别名
  • 可以搭配之前介绍的所有查询子句，如排序、分组、筛选等

举例：

  • 查询员工的部门名
select last_name, department_name
from employees,
     departments
where employees.department_id = departments.department_id;
  • 查询员工名、工种号、工种名
# 查询员工名、工种号、工种名 为表起别名：表名后加as,可以提高语句简洁度，两个表的顺序可以调换
select last_name, e.job_id, job_title
from employees as e,
     jobs as j
where e.job_id = j.job_id;
  • 查询有奖金的员工名、部门名（加筛选）
# 可以加筛选
# 查询有奖金的员工名、部门名
select last_name, department_name, commission_pct
from employees e,
     departments d
where e.department_id = d.department_id
  and e.commission_pct is not null;
  • 查询城市中第二个字符为o的对应部门名和城市名
# 查询城市中第二个字符为o的对应的部门名和城市名
select department_name, city
from departments d,
     locations l
where d.location_id = l.location_id
  and city like '_o%';
  • 查询每个城市的部门个数（加分组）
# 可以加分组
# 查询每个城市的部门个数
select count(*) 个数, city
from departments d,
     locations l
where d.location_id = l.location_id
group by city;
  • 查询有奖金的每个部门名和部门领导编号以及该部门的最低工资
# 查询有奖金的每个部门名和部门领导编号以及该部门的最低工资
select department_name, d.manager_id, min(salary)
from departments d,
     employees e
where d.department_id = e.department_id
  and commission_pct is not null
group by department_name, d.manager_id;
  • 查询每个工种的工种名、员工的个数，并且按照员工的个数降序排列（加排序）
select job_title, count(*)
from employees e,
     jobs j
where e.job_id = j.job_id
group by job_title
order by count(*) desc;
  • 查询员工名、部门名和所在城市（多表连接）
# 查询员工名、部门名和所在的城市
select last_name, department_name, city
from employees e,
     departments d,
     locations l
where e.department_id = d.department_id
  and d.location_id = l.location_id;

6.3 SQL92标准非等值连接

  • 查询员工的薪资等级
select salary, grade_level
from employees e,
     job_grades g
where salary >= g.lowest_sal
  and salary < g.highest_sal;

6.4 SQL92自连接

  • 查询员工名以及上级名
# 查询员工名以及上级的名称
select e.employee_id, e.last_name, m.employee_id, m.last_name
from employees e,
     employees m
where e.manager_id = m.employee_id;

6.5 SQL99语法

select 查询列表
from 表 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组条件]
[having 筛选条件]
[order by 排序条件];
更加具有可读性

支持：

  • 内连接inner

  • 外连接

    • 左外：left outer join
    • 右外：right outer join
    • 全外：full outer join
  • 交叉连接：cross

6.6 SQL99 内连接

  • 分类

    • 等值连接
    • 非等值连接
    • 自连接

  • 特点：

    • 可以添加排序、分组、筛选
    • inner可以省略
    • 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
    • 等值连接与SQL92的效果相同

示例：

  • 查询员工名、部门名
# 查询员工名、部门名
select last_name, department_name
from employees e
         inner join departments d
                    on e.department_id = d.department_id;
  • 查询名字中包含e的员工名和工种名
# 查询名字中包含e的员工名和工种名
select last_name, job_title
from employees e
         inner join jobs j
                    on e.job_id = j.job_id
where e.last_name like '%e%';
  • 查询部门个数>3的城市名和部门个数
# 查询部门个数>3的城市名和部门个数
select city, count(*) '部门个数'
from departments d
         inner join locations l on d.location_id = l.location_id
group by city
having 部门个数 > 3;
  • 查询每个部门的员工个数大于3的部门名和员工个数，并按员工个数降序
# 查询每个部门的员工个数大于3的部门名和员工个数，并按个数降序
select department_name, count(*) 员工个数
from departments d
         inner join employees e on d.department_id = e.department_id
group by department_name
having 员工个数 > 3
order by 员工个数 desc;
  • 查询员工名、部门名、工种名
# 查询员工名、部门名、工种名
select last_name, department_name, job_title
from employees e
         inner join departments d on e.department_id = d.department_id
         inner join jobs j on e.job_id = j.job_id
order by department_name desc;

非等值查询

  • 查询员工的工资级别
# 查询员工的工资级别
select salary, grade_level
from employees e
         join job_grades g
              on e.salary between g.lowest_sal and g.highest_sal;

自连接

  • 查询员工的名字、上级的名字
# 三 自连接 查询员工的名字、上级的名字
select e.last_name, m.last_name
from employees e
         inner join employees m on e.manager_id = m.employee_id;

6.7 SQL99外连接

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

特点：

  • 外连接的查询结果为主表中的所有记录，如果表中有和它匹配的值，则显示匹配的值，如果没有匹配的值，则显示nulll，外连接查询 = 内连接查询结果 + 主表中有而从表中没有的记录
  • 左外连接中，left join左边的是主表，右外连接中，right join右边的是主表
  • 左外和右外，如果交换两个表的顺序，可以实现同样的效果
  • 全外连接 = 内连接的结果 + 表一中没有的 + 表二中没有的 + 表二中有但表一中没有的

示例

  • 查询哪个部门没有员工
# 查询哪个部门没有员工
select d.*, e.employee_id
from departments d
         left join employees e on d.department_id = e.department_id
where e.employee_id is null;

6.8 交叉连接

交叉连接就是笛卡尔积，查询结果的总行数为参与查询的表的行数之积

7. 子查询

7.1 概述

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

  • 分类：

    • 按子查询出现的位置

      • select之后：只支持标量
      • from之后：只支持表子查询
      • where或having之后：支持标量、列子查询、行子查询
      • exist之后：支持表子查询

    • 按功能或结果集行列数不同：

      • 标量子查询：结果一行一列
      • 列子查询：结果多行一列（多行子查询）
      • 行子查询：结果多行多列（一行多列居多）
      • 表子查询：一般为多行多列

7.2 where或having后

  • 特点：

    • 必须放在小括号内
    • 放在条件的右侧
    • 如果使用标量子查询，一般使用单行操作符> < >= <= <>
    • 如果使用列子查询，一般使用多行操作符：IN、ANY、SOME、ALL
    • 先进行子查询，主查询再使用子查询的结果

7.2.1 标量子查询示例

  • 查询谁的工资比Abel高，先查Abel的工资，再查比他高的
select salary
from employees
where last_name = 'Abel';

select last_name, salary
from employees
where salary > (
    select salary
    from employees
    where last_name = 'Abel'
);
  • 返回job_id与141号员工南向通，salary比143号员工多的员工姓名、job_id和工资
# 先查询141号员工的jobid
select job_id
from employees
where employee_id = 141;

# 查询143号员工的salary
select salary
from employees
where employee_id = 143;

# 查询符合条件的员工
select last_name, job_id, salary
from employees
where job_id = (
    select job_id
    from employees
    where employee_id = 141
)
  and salary > (
    select salary
    from employees
    where employee_id = 143
);
  • 查询公司工资最少的员工的信息
# 查询公司的最少工资
select min(salary)
from employees;

# 查询员工的信息，要求工资等于最低工资
select last_name, job_id, salary
from employees
where salary = (
    select min(salary)
    from employees
);
  • 查询最低工资大于50号部门的最低工资的部门id和其最低工资
# 先查询50号部门的最低工资
select min(salary)
from employees
where department_id = 50;

# 查询每个部门的最低工资
select min(salary), department_id
from employees
group by department_id;

# 筛选，
select department_id, min(salary)
from employees
group by department_id
having min(salary) > (
    select min(salary)
    from employees
    where department_id = 50
);

7.2.2 列子查询示例

  • 返回location_id是1400或1700的部门中所有员工的姓名
# 查询location_id是1400或1700的部门编号
select distinct department_id
from departments
where location_id = 1400
   or location_id = 1700;

# 查询员工姓名，要求部门号是上述查询中的某一个
select last_name
from employees
where department_id in (
    select distinct department_id
    from departments
    where location_id = 1400
       or location_id = 1700
);
  • 返回其他部门中比job_id为IT_PROG部门任一工资低的员工号、姓名、job_id以及salary
# 查询job_id为IT_prog部门的任一工资
select distinct salary
from employees
where job_id = 'IT_PROG';

# 查询员工号信息，要求salary小于任意一个上述值
select last_name, employee_id, job_id, salary
from employees
where salary < any (
    select distinct salary
    from employees
    where job_id = 'IT_PROG'
);
  • 返回其他部门中比job_id为IT_PROG部门所有员工工资都低的员工的员工号、姓名和工资
select last_name, employee_id, job_id, salary
from employees
where salary < all (
    select distinct salary
    from employees
    where job_id = 'IT_PROG'
);

7.2.3 行子查询

  • 查询员工编号最小并且工资最高的员工信息
# 案例：查询员工编号最小并且工资最高的员工信息
select min(employee_id)
from employees;

select max(salary)
from employees;

select *
from employees
where employee_id = (
    select min(employee_id)
    from employees
)
  and (
    select max(salary)
    from employees
);

# 使用行子查询
select *
from employees
where (employee_id, salary) = (
    select min(employee_id), max(salary)
    from employees
);

7.3 select之后

  • 查询每个部门的员工个数
select d.*,
       (
           select count(*)
           from employees e
           where e.department_id = d.department_id
       ) 个数
from departments d;
  • 查询员工号=102的部门名
select (select department_name

        from departments d
                 inner join employees e on d.department_id = e.department_id
        where e.employee_id = 102
       ) 部门名;

7.4 from 之后

  • 查询每个部门的平均工资的工资等级
# 先查每个部门的平均工资
select avg(salary), department_id
from employees
group by department_id;

# 查询工资等级
select ag_dep.*, j.grade_level
from (
         select avg(salary) ag, department_id
         from employees
         group by department_id
     ) ag_dep
         inner join job_grades j
                    on ag_dep.ag between lowest_sal and highest_sal;

7.5 exist之后

  • 查询员工名和有员工的部门名
select department_name
from departments d
where exists(select *
             from employees e
             where d.department_id = e.department_id
          );

8. 分页查询

8.1 概述

  • 应用场景：要显示的场景一页显示不全，需要分页提交sql请求
  • 语法
select 查询列表 
from
[join type] join 表2
    on 连接条件
where 筛选条件
group by 分组条件
having 分组后的筛选
order by 排序后的字段
limit offset, size
offset为要显示条目的起始索引，从0开始，size表示显示的条目个数

  • 特点：

    • limit语句放在查询语句的最后
    • 公式：假设要显示的页数为page，每页的条目数为size，则select 查询列表 from 表 limit (page - 1) * size, size

8.2 举例

  • 查询前五条员工的信息
select *
from employees
limit 0, 5;
  • 查询第11条到第25条的员工信息
select *
from employees
limit 10, 15;
  • 查询有奖金的员工信息，并且工资较高的前10名显示出来
select *
from employees
where commission_pct is not null
order by salary desc
limit 10;

9. 联合查询

使用关键字union将多条语句的查询结果合并为一个结果。

语法：

 查询语句1
 union
 查询语句2
 ......
  • 应用场景：查询的结果来自多个表，但是这些表没有直接的连接关系，仅仅是含以上相似

  • 特点：

    • 多条查询语句的列数必须一致
    • 多条语句的每一列的类型和顺序最好一致，不一致也不会报错，但是没意义
    • 使用union合并时会自动进行去重操作，如果要避免去重，可以使用union all进行联合查询
  • 示例：
# 查询部门编号大于90或邮箱中包含a的员工信息
select *
from employees
where email like '%a%'
union
select *
from employees
where department_id > 90;


# 查询中国用户男性的信息以及外国用户男性的信息
select id, cname, csex from t_ca where csex = '男'
union
select t_id, t_Name, t_Gender from t_ua where tGender = 'mail';
最后修改：2023 年 11 月 14 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏