sql查询语法

• 单表查询

• 格式
  select 字段列表 from 表明列表 where 条件列表 group by 分组字段列表 having 分组后的条件列表 limit 起始索引 每一页的数据量

• 基本查询
  select 字段1，字段2，字段3 from 表名；
  select * from 表名；
  select 字段1 as 别名1，字段2 as 别名2 from 表名；
  select distinct 字段列表 from 表名；（去除重复记录）

• 条件查询
  select 字段列表 from 表名 where 条件列表；
  比较运算符：> >= < <= = !=(<>) between…and… in(…)in之后的列表中的值多选一 like（模糊匹配 单个字符_，任意个字符%）
  逻辑运算符：and(&&) or(||) not(!)
  注意：查询为null的数据，不能使用 =null，而是is null/is not null
  例子：
  查询 入职日期 在 ‘2000-01-01’ (包含) 到 ‘2010-01-01’(包含) 之间的员工信息
  select * from tb_emp where entry_date between ‘2020-01-01’ and’2010-01-01’；
  select * from tb_emp where entry_date >= ‘2020-01-01’ and entry_date<=‘2010-01-01’；
  查询 入职时间 在 ‘2000-01-01’ (包含) 到 ‘2010-01-01’(包含) 之间 且 性别为女 的员工信息
  select * from tb_emp where entry_date between ‘2000-01-01’ and ‘2010-01-01’ and gender = 2；

• 聚合函数
  select 聚合函数（字段列表）from 表名；
  聚合函数会忽略null，对null值不进行统计
  聚合函数：count(*) max min avg sum
  注意：使用了聚合函数，select后面应该跟的是查询字段名和聚合函数

• 分组查询
  group by 分组列表 having 过滤条件
  根据性别分组 , 统计男性和女性员工的数量
  select gender，count(*) from tb_emp group by gender;

  查询入职时间在 ‘2015-01-01’ (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
  select job,count() from tb_emp where entry_date <= ‘2015-01-01’ group by job having count()>=2;
  因为先用条件查询找到入职时间的员工，然后根据职位分组，分组之后再用having过滤符合job数量大于等于2的数据，同时用到了聚合函数，select后面应该跟着分组的字段和聚合函数。
  注意：where和having区别
  执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  判断条件不同：where不能对聚合函数进行判断，而having可以。

• 排序查询
  order by 字段列表；
  升序 ASC（默认）
  降序 DESC
  根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序
  select * from tb_emp group by entry_date ASC，update_time DESC；
  如果是多字段排序，只有前一个字段相同时，才会根据第二个字段排序

• 分页查询
  limit 起始索引，每页的数量（起始索引从0开始）
  查询 第2页 员工数据, 每页展示5条记录
  select * from tb_emp limit 5，5；

• 查询时的表达式
  if(条件表达式，true取值，false取值)
  select if(gender=1,‘男性员工’,‘女性员工’) AS 性别, count() AS 人数
  from tb_emp
  group by gender;
  if(表达式, tvalue, fvalue) ：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue
  as 后面是别名
  case 表达式 when 值1 then 结果1 when 值2 then 结果2 … else result end
  select (case job
  when 1 then ‘班主任’
  when 2 then ‘讲师’
  when 3 then ‘学工主管’
  when 4 then ‘教研主管’
  else ‘未分配职位’
  end) AS 职位 ,
  count() AS 人数
  from tb_emp
  group by job;

• 多表查询

• 内连接（显示/隐式）查询两个表的交集
  隐式：select 字段列表 from 表1，表2 where 条件…；
  显示：select 字段列表 from 表1 inner join 表2 on 连接条件；
  查询员工的姓名及所属的部门名称
  隐式：
  select tb_emp.name , tb_dept.name – 分别查询两张表中的数据
  from tb_emp , tb_dept – 关联两张表
  where tb_emp.dept_id = tb_dept.id; – 消除笛卡尔积

  显示：
  select tb_emp.name , tb_dept.name
  from tb_emp inner join tb_dept
  on tb_emp.dept_id = tb_dept.id;
  可以起别名
  select emp.name , dept.name
  from tb_emp emp inner join tb_dept dept
  on emp.dept_id = dept.id;
  注意：如果指定了别名，就不能再使用表名来指定对应字段，只能用别名

• 外连接
  左外连接：查询左边集合（包括两个集合的交集）
  右外连接：查询右边集合（包括两个集合的交集）
  select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 … ;
  select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 … ;
  查询员工表中所有员工的姓名, 和对应的部门名称
  左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表匹配的右边表中的数据
  select emp.name , dept.name
  from tb_emp AS emp left join tb_dept AS dept
  on emp.dept_id = dept.id;
  左外连接和右外连接是可以相互替换的，只需要调整连接查询时SQL语句中表的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

• 子查询
  SQL语句中嵌套select语句，称为嵌套查询，又称子查询
  SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 … );
  子查询可以书写的位置
  where之后
  from之后
  select之后

• 标量子查询（子查询结果返回的是单个值）

查询"教研部"的所有员工信息

可以将需求分解为两步：

1. 查询 “教研部” 部门ID
2. 根据 “教研部” 部门ID，查询员工信息

– 1.查询"教研部"部门ID
select id from tb_dept where name = ‘教研部’; #查询结果：2
– 2.根据"教研部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 2;

– 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where name = ‘教研部’);

查询在 “方东白” 入职之后的员工信息

可以将需求分解为两步：

1. 查询 方东白 的入职日期
2. 查询 指定入职日期之后入职的员工信息

– 1.查询"方东白"的入职日期
select entrydate from tb_emp where name = ‘方东白’; #查询结果：2012-11-01
– 2.查询指定入职日期之后入职的员工信息
select * from tb_emp where entrydate > ‘2012-11-01’;

– 合并以上两条SQL语句
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = ‘方东白’);

• 列子查询
  子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询
  in 在指定集合范围内多选一
  not in 不在指定集合范围内
  查询"教研部"和"咨询部"的所有员工信息

分解为以下两步：

1. 查询 “销售部” 和 “市场部” 的部门ID
2. 根据部门ID, 查询员工信息

– 1.查询"销售部"和"市场部"的部门ID
select id from tb_dept where name = ‘教研部’ or name = ‘咨询部’; #查询结果：3,2
– 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);

– 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where name = ‘教研部’ or name = ‘咨询部’);

• 行子查询
  子查询返回的结果是一行(可以是多列)
  常用的操作符：= 、<> 、IN 、NOT IN
  查询与"韦一笑"的入职日期及职位都相同的员工信息

可以拆解为两步进行：

1. 查询 “韦一笑” 的入职日期 及 职位
2. 查询与"韦一笑"的入职日期及职位相同的员工信息

– 查询"韦一笑"的入职日期 及 职位
select entrydate , job from tb_emp where name = ‘韦一笑’; #查询结果： 2007-01-01 , 2
– 查询与"韦一笑"的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) = (‘2007-01-01’,2);

– 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = ‘韦一笑’);

• 表子查询
  子查询返回的结果是多行多列，常作为临时表
  查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

分解为两步执行：

1. 查询入职日期是 “2006-01-01” 之后的员工信息
2. 基于查询到的员工信息，在查询对应的部门信息

select * from emp where entrydate > ‘2006-01-01’;

select e.*, d.* from (select * from emp where entrydate > ‘2006-01-01’) e left join dept d on e.dept_id = d.id