第二篇：数据查询语言DQL

Part1: 基础查询

核心的关键查询词：select
它可以用于查询表中的字段、常量、表达式、函数等，查询结果是一个虚拟表格。

查询常量的方法：
select 常量；
例1：查询”jerry” 

  1. select "jerry";

备注：查询的常量，可以是数字，字符串等常量格式。

查询表格中1个字段的语法：
select 字段名 from 表格名;
例2：从员工表employees中查询员工的姓名： 

  1. select name from employees;

查询表格中多个字段的语法：
select 字段名1， 字段名2， …, 字段名n from 表名；
例3：从员工表employees中查询员工的名字和工号： 

  1. select name, id from employees;

给查询的字段起别名：
select 字段 as 别名 from 表名;
例4：从员工表employees中查询员工的姓名，并命名为”姓名”显示： 

  1. select name "姓名" from employees;

备注: as可加可不加；

查询函数：
select 函数；
例5：查询当前mysql的版本号: 

  1. select version();

Part2: 条件查询

基本语法如下：
select 字段 from 表名 where 筛选条件;
执行顺序：

  1. 首先检查from 表名中的表格是否存在；
  2. 然后检查where中的筛选条件;
  3. 最后将结果显示select出来；

按照条件表达式筛选：
补充：条件预运算符有 > < = <> >= <=
例6：查询员工表employees中工资>10000元的员工的姓名：
select name from employees where salary>10000;

按照逻辑表达式筛选：
补充：逻辑运算符有 & | ！ and or not
例7：查询员工表employees中工资>10000元且工号小于100的员工的姓名: 

  1. select name    
  2. from employees    
  3. where salary > 10000 and id < 100;

按照模糊查询的方式筛选：
补充：模糊查询方法有 like, between and, in, is null
例8：查询员工表employees中邮箱名字包含字母e的员工的姓名: 

  1. select name    
  2. from employees    
  3. where email like "%e%";

例9：查询员工表employees中邮箱的第3个字母是e的员工的姓名: 

  1. select name   
  2. from employees    
  3. where email like "__e%";

备注：%表示字符型通配符，可以表示0个或者多个；
_也表示字符型通配符，但是只能表示一个；
例10：查询员工表employees中工资在8000-10000的员工的姓名： 

  1. select name    
  2. from employees    
  3. where salary between 8000 and 10000;

备注：between and 的结构中，第一个数字要比第二个小；
例11：查询员工表employees中工种是”IT”,”VP”,”PRSE”的员工姓名： 

  1. select name    
  2. from employees    
  3. where job_title in ("IT", "VP", "PRES");

Part3: 排序查询

排序查询的语法如下：
select 字段名 from 表名 where 筛选条件 order by 排序条件 排序方法；

备注：排序方法有两种 asc代表升序排列，可省略； desc代表降序排列，不可省略。
其中语法执行的顺序为：

  1. from 表名
  2. where 筛选条件
  3. select 字段名
  4. order by 排序条件 排序方法；

单次排序：
例12：将员工表employees中的员工姓名按照工资的降序排列： 

  1. select name     
  2. from employees    
  3. order by salary desc;

多次排序：
多次排序是指在主排序中出现排序相同的时候的次排序方案。
例13：将员工表employees中的员工先按照工资降序排序，再按照工号升序排序： 

  1. select name    
  2. from employees    
  3. order by salary desc, id asc;

Part4: 常见函数

A. 字符函数

1.length();输出括号内有几个字节；备注：1个汉字3个字节，1个字母1个字节（utf8)
2.contact();拼接字符，例如：contact(last_name,’-‘,first_name);
3.upper();小写字母变大写字母；
4.lower();大写字母变小写字母；
5.substr();输出字符中的一部分，使用方法：
substr(string, a, b)表示从字符串string中的第a个字符开始，到第b个字符，
如果只有一个字母，则默认到最后；

备注：这里是字符，并不是字节；
备注：mysql中的字符是从1开始，而非0；

6.instr();返回目标出现的第一个位置，使用方法：
instr(string, a)输出a在string中第一次出现的位置;
7.trim();去掉前后的字符，使用方法：
例14：trim(‘ 张翠山 ‘)输出’张翠山’，前后的’ ‘被去掉了；
例15：trim(‘a’ from ‘aaa张翠山aaa’）输出’张翠山’，前后的’a’被全掉了;
8.lpad()；字符串的左边用指定字符进行填充，使用方法：
例16：lpad(‘张翠山’,10,’a’)输出’aaaaaaa张翠山’，其中10表示输出总长度；
9.rpad()；字符串的右边用指定字符进行填充，使用方法：
例17：rpad(‘张翠山’,10,’a’)输出’张翠山aaaaaaa’，其中10表示输出总长度；

备注：如果本身字符串的长度就大于输出的总长度的话，则输出保持不变；

10.repalce();替换字符串，用法如下：
replace(string, a, b)将字符串中的a用b替换；

B. 数学函数

1.round();四舍五入函数；
2.ceil();向上取整函数，也就是返回大于等于当前这个输入数据的最小整数；
3.floor();向下取整函数，也就是返回小于等于当前这个输入数据的最大整数；
4.truncate();截断函数，干掉小数点后面的数字，用法如下：
例18：truncate(1.699,1)输出1.6
5.mod();取余函数,用法如下：
例19：mod(10,3)输出1

备注：公式为mod(a,b) = a-a/b*b

C. 日期函数

1.now();返回当前的系统日期+时间，格式为：’2020-02-09 14:37:18’;
2.curdate(); 返回当前的系统日期,格式为：’2020-02-09’;
3.curtime(); 返回当前的系统时间,格式为：’14:37:18’;
4.分别获取系统的年月日：
select year(now());
select month(now());
select day(now());
5.str_to_date();将日期格式转换为指定格式：
例20：str_to_date(‘9-13-1999’,’%m-%d-%y’);输出为’2019-09-13’，其中‘%m-%d-%y’是通知这个函数，你输入的时间格式中，哪个是年月日，然后再按照标准化输出；
6.date_fromat();将日期转换为字符：
例21：date_format(now(),’%y年%m月%d日’)；输出为’20年02月09日’;
备注：因为输出写的是y，所以是20年；如果写为Y，就是2020年；
备注：看清楚函数，是date_format而不是date_formate

D. 流程控制函数

  1. if函数
    语法结构为：if(exp1, exp2, exp3)如果exp1真，那么执行exp2；否则执行exp3；
  2. case函数
    语法1：switch (变量)
    {
    case 常量： 语句， break
    …
    default 语句
    }
    语法2：
    case 字段或者表达式
    when 常量1 then 语句1
    …
    else 语句n（默认情况)

Part5: 分组函数

  1. sum(); 求和
  2. avg(); 均值；
  3. max(); 最大值;
  4. min(); 最小值;
  5. count(); 统计函数;
    例22：求员工表employees中所有员工的平均工资： 
    1. select avg(salary) from employees;
    例23：求员工表employees中所有员工的工资之和： 
    1. select sum(salary) from employees;
    例24：统计员工表employees中共有多少名员工: 
    1. select count(*) from employees;

Part6: 分组查询

分组查询的语法如下：
select 字段名
from 表名
where 筛选条件
group by 分组条件
order by 排序条件 排序方法
执行的顺序如下：

  1. from 表名
  2. where 筛选条件
  3. group by 分组条件
  4. select 字段名
  5. order by 排序条件 排序方法

例25：查询员工表employees中的每个工种的最高工资： 

  1. select max(salary)    
  2. from employees  
  3. group by job_id;

例26：查询员工表employees中哪个部门的员工数目>2： 

  1. select count(*)   
  2. from employees        
  3. group by department_id        
  4. having count(*)>2;

备注：having 表示在上述的表格的结果中进行筛选，从某个表+筛选条件的角度来说，生成的是虚拟表格，having 就是在这个基础上进行筛选的。
备注：加了having之后的执行顺序是：

  1. from 表名;
  2. where 筛选条件；
  3. group by 分组条件；
  4. having 筛选条件；
  5. select 字段；
  6. order by 排序条件 排序方法；

联合查询：
例27：查询员工表employees中每个部门每个工种的平均工资：
首先要理解题意，不同部门是存在不同的工种的，所以要分类执行，输出结果按照笛卡尔排序的方法；

  1. select avg(salary)    
  2. from employees    
  3. group by depatrtment_id, job_id;

备注：这里的department_id， job_id的形式，已经将两者搭配在一起了，形成了想要的结果。

Part7: 连接查询

连接查询，相较于上述的查询方式的主要区别如下：
上述查询方式是只用一张表的信息就足够了
连接查询希望借助不同表格的信息，对查询的数据进行优化

连接查询的语法如下：
select 字段名
from 表格1
[type] join 表格2
on 连接条件
where 筛选条件
group by 分组条件
having 筛选条件
order by 排序条件 排序方法
执行顺序记忆方法如下：
表格1 [type] join 表格2 on 连接条件
形成了一个新的表格，其余方法参考之前的内容

type的类型1：等值连接
这种方法是指相互连接的两个表格之间的关系是平等的，此时type=inner，可以省略
例28：根据员工表employees和部门表department，查询工资>10000的员工所在的部门的名字： 

  1. select departmnent_name    
  2. from employees  e
  3. join departments  d   
  4. on e.department_id = d.department_id
  5. where salary>10000;

备注：如果需要去重，请使用函数dinstinct();

实现三表连接：
例29：查询员工名，部门名和所在的城市： 

  1. select name, department_name, city        
  2. from departments d    
  3. join employees e    
  4. on e.department_id = d.department_id    
  5. join locations l      
  6. on d.location_id = l.location_id;

备注：这里是按照多重的join的方式，将不同的表格联合在一起；要做到能够和另外两个表格都连接的表格放在最前面，这样方便进行连接；

type的类型2：非等值连接
非等值连接，意味着值之间的连接方式不是由等号确定的，也就是其他的关系。
例30：查询员工的姓名和工资的等级: 

  1. select name, level    
  2. from employees
  3. join salary_grade    
  4. on employees.salary between salary_grade.lowest_sal and salary_grade.highest_sal;

type的类型3：自连接
顾名思义，自连接就是自己和自己连接，也就是表格中的两个字段之间产生了关联，然后形成一个新的表格了。
例31：查询员工表employees中员工名和他的领导的名字: 

  1. select e.name, m.name
  2. from employees e
  3. join employees m    
  4. on e.manager_id = m.id;

备注：这个例子中，表格中的一些行，既是领导，也是员工，所以要自己与自己连接，注意上述例子中通过不同的别名实现了对同一个表格的区分；

type的类型4：左外连接
左外连接的意思是，将第一个表格作为关键表，第二个表格作为不关键表；左外连接与内连接的区别在于第一个表格的所有内容将会被保留，第二个表格的内容，如果与第一个表格有关系，则被保留，否则不保留。
例32：找出女生表格beauty中男朋友不出现在男生表格boy中的女生的姓名： 

  1. select b.name    
  2. from beauty b    
  3. left outer join boy bo    
  4. on b.bf_id = bo.id    
  5. where bo.id is null;

备注：这里使用where表示，from 后面的一串形式，就是一个表格；

type的类型5：右外连接
右外连接的意思是，将第一个表格作为不关键表，第二个表格作为关键表；右外连接与内连接的区别在于第二个表格的所有内容将会被保留，第一个表格的内容，如果与第一个表格有关系，则被保留，否则不保留。
例33：找出女生表格beauty中男朋友不出现在男生表格boy中的女生的姓名： 

  1. select b.name    
  2. from boy bo   
  3. right outer join beauty b  
  4. on b.bf_id = bo.id    
  5. where bo.id is null;

type的类型6：全外连接
全外连接的意思是，将所有的都显示出来，但是是按照一定的连接方式。与前述的连接方式的区别在于，两个表内连接的部分将会被保留，一个表有，另一个表没有的部分也将被保留。
例34：显示出男生表boy和女生表beauty的全部内容，并将互为男女朋友的放在一起： 

  1. select bo.*, b.*    
  2. from beauty b    
  3. full outer join boy bo    
  4. on bo.id = b.bf_id;

type的类型7：交叉连接
交叉连接的意思是，将两个表格的内容互相连，每种可能的情况都遍历一遍
例35：显示出男生表boy和女生表beauty中的男女可能凑成的情侣的情况： 

  1. select bo.name, b.name    
  2. from beauty b
  3. cross join boy bo;

备注：以上几种连接方式的区别总结如下：
内连接表示两个表格之间的交集；
左外和右外连接表示使用一个表的全部和另一个表中有用的信息，相当于交集加上一个集合；
全外连接，表示两个表格的信息全部按照一定的规则使用，相当于全集；
交叉连接，表示两个表格所有可能出现的组合情况，也叫笛卡尔连接。

Part8: 子查询

子查询的意思是，将一个查询过程的结果作为另一个查询过程的条件。一般情况下，可以在from后的表格中加入子查询，where/having后的筛选条件中加入子查询.
备注：子查询的优先级高于主查询的；

类型1：单行子查询
单行子查询的意思是，子查询的结果是一个值
例36：查询员工表employees中工资比sobel高的员工的姓名： 

  1. select name
  2. from employees
  3. where salary>(
  4. select salary
  5. from employees
  6. where name = 'sboel'
  7. );

备注：这里的查询结果就是一个值，也就是1个工资的结果；

类型2：多行子查询
多行子查询的意思是，子查询的结果是很多值
例37：查询平均工资最低的部门编号： 

  1. select department_id, avg(salary)      
  2. from employees  
  3. group by department_id  
  4. having avg(salary) = (
  5. select min(avg(salary))
  6. from (
  7. select avg(salary)
  8. from employees
  9. group by department_id
  10. )   
  11. );

备注：其中，表征每个部门的平均工资的表格就是输出的多行子查询；
另外，还准备了一个多行子查询的经典例题：
例38：查询各个部门中，工资比本部门平均工资高的员工的姓名： 

  1. select name
  2. from employees e   
  3. join (
  4. select avg(salary) ag, department_id
  5. from employees
  6. group by department_id    
  7. ) f
  8. on e.department_id = f.department_id
  9. where e.salary > f.ag    
  10. group by e.department_id;

备注：如果多行子查询结果输出的话，需要将子查询的结果与目标相关联，才方便进行筛选；

Part9: 分页查询

分页查询表示查询出的结果长度。语法如下：
select 字段名
from 表名1
[type] join 表名2
on 连接条件
where 筛选条件
group by 分组条件
having 筛选条件
order by 排序条件 排序方式
limit offset, size
执行顺序如下：

  1. from 表名1
  2. [type] join 表名2
  3. on 连接条件
  4. where 筛选条件
  5. group by 分组条件
  6. having 分组条件
  7. select 字段名
  8. order by 排序条件 排序方式
  9. limit offset, size

    备注:其中offset表示分页开始的地方，size表示表格的长度，这里offset从0开始；

例39：查询员工表employees中工资最高的10个人的名字： 

  1. select name   
  2. from employees    
  3. order by salary desc
  4. limit 0, 10;

Part10: 联合查询

联合查询的意义在于，能够将多个查询语句分开写。
语法如下：
select 字段名1 from 表格1 where 筛选条件1
union
select 字段名2 from 表格2 where 筛选条件2；

备注：为了能够生成结果，需要保证筛选的字段名1和字段名2之间需要相互兼容；

例40：查询员工表employees中工资<3000和工资>10000的员工的姓名： 

  1. select name
  2. from employees 
  3. where salary > 10000 or salary < 3000;
  1. select name from employees where salary<3000
  2. union
  3. select name from employees where salary>10000;

备注：两者的输出结果不一致，因为查询的方式不一致；
备注：union本身自带去重效果，如果要保留重复，要使用union all；


本博客所有文章除特别声明外，均采用 CC BY-SA 3.0协议 。转载请注明出处！