DQL

目录：

基础语法：

SELECT 
    [DISTINCT] 查询项 [[as] 别名], 查询项...
[FROM 
    表名]
[JOIN_TYPE JOIN 
    表2
ON 连接条件]
[WHERE
    筛选条件]
[GROUP BY
    分组项
[HAVING
    分组后的条件筛选]]
[ORDER BY 
    排序的字段|表达式|函数|别名 [ASC|DESC]]
[LIMIT [offset,] size];

注：

  • SQL索引都是从1开始，与编程语言不同（原因很简单，查出来3条数据，不能说第0条是xxx吧）
    但limit的offset是从0开始的，offset表示要显示条目的起始索引，size表示要显示的条目个数
  • 通过select查询完的结果集（其实每个子句执行后的结果集都是），是一个虚拟的表，不是真实存在
  • 执行顺序：from表 -> join连接表 -> on连接条件 -> where筛选条件 -> group by分组 -> having分组后筛选 -> select查询 -> order排序 -> limit
    即先看表存不存在，再看查询条件，然后查询，最后排序
  • 查询项
    • 结果去重 distinct
    • 查询项可以是字段，也可以是常量值、表达式、函数
    • 查询项可以用``括起来，以区分字段名和关键字，也可以不用
    • 查询项可以用 AS 或 空格 起别名, 别名中若包含特殊符号，如空格，#等，需要用引号括起来
  • where 条件：
    • 按条件： > , < ,=(注意等于是单个=), >= , <= , !=, <>（表示不等于）
    • 按逻辑条件：and(&&), or(||), not(!)
    • 模糊查询： like, between and(包含边界), in, is null, is not null
      • like 通常与通配符搭配使用：
        % 匹配任意多个字符,包含0个字符
        _ 匹配任意单个字符
        like支持转义： LIKE '_\_%' 表示匹配第二个字符为_的字符串
        另一种转义方式：LIKE '_$_%' ESCAPE '$';
      • in
        in列表的值类型必须一致或兼容
        in列表中不支持通配符
  • order by 排序：
    • order by后可以有多个排序字段，按先后顺序：
      order by salary desc, age asc; //按工资降序排序，若工资一样，则按年龄升序排序
    • order by 语句通常是放在最后面，（limit子句前）

函数

1.单行函数

        ifnull(字段) 如果字段为null，就返回1， 否则返回0
        ifnull(字段， val) 如果字段为null，则转换成val

	1、字符函数
        concat(str, ... , str)     字符串拼接
                （注意：mysql中的 + 不能做拼接，只能做运算:
                    ①两个操作数都为数值型，则做加法运算；
                    ②只要其中一方为字符型，试图将字符型转换成数值型，如果转换成功，则继续做加法运算，如果转换失败，则将字符型转换成0,在做加法运算；
                    ③只要其中一方为null，则结果肯定为null）
        length(str)     获取字节个数
                （注意：一个汉字占2个字节(gbk)或3个字节(utf8)）
        substr(str, startIndex [, len]);    截取子串，注意：startIndex从1开始 
        instr(str, substr)     返回子串第一次出现的索引,没有返回0
        replace(str, srcstr, dststr)    替换,将str中所有的srcstr替换成dststr
        upper(str)     转换成大写
        lower(str)     转换成小写
        trim(str)    去前后指定的空格和字符
                    //select trim('a' FROM 'aaa张三丰aaa') as 'out';去除字符串前后的a，输出为“张三丰”
        ltrim()     去左边空格
        rtrim()     去右边空格
        lpad(str, len, padchar)     左填充,若str长度小于len，则用padchar进行填充，若小于len，则只显示len长度的str
        rpad()     右填充
						
	2、数学函数
        mod()     取余
            //mod(a, b) = a - a/b*b;
        rand()     随机数
        floor()     向下取整
        ceil()     向上取整
        round() 四舍五入
            //round(1.567) //结果是2
            //round(1.567, 2) //小数点后保留2位，结果是1.57
        truncate()     截断,小数点后保留几位
            //truncate(1.567, 2) //结果是1.56  

	3、日期函数
		now()     当前系统日期+时间
		curdate()     当前系统日期
		curtime()    当前系统时间
		str_to_date(str, format)     将字符转换成日期
                    //正常解析 str_to_date('1999-9-1')
                    //按指定格式解析：str_to_date('9-1-1999'， '%m-%d-%Y');结果是1999-09-01
		date_format(date, format)     将日期转换成字符
                    //date_format(now(), '%Y年%m月%d日');结果是2019年11月1日
                //获取指定部分
                //eg: YEAR(NOW()); 即可获取年
                datediff(date1, date2) 可以获取两个日期相差的天数
                    //eg: datediff('2018-12-01', now()); 查询从2018年12月1日到现在有多少天

	4、流程控制函数
		if(statement, expr1, expr2)     处理双分支,
                        等效于 statement ? expr1 : expr2;

		case语句     处理多分支
			情况1：处理等值判断
                            case 字段
                            when 值1 then expr1
                            when 值2 then expr2
                            ...
                            else expr_default
                            end as 别名
			情况2：处理条件判断
                            case
                            when 条件1 then expr1
                            when 条件2 then expr2
                            ...
                            else expr_default
                            end as 别名
		
	5、其他函数
		version()    版本
		database()    当前库
		user()    当前连接用户

2.分组函数（聚合函数，用于统计）
    count()    计数
    sum()     求和
    max()     最大值
    min()     最小值
    avg()     平均值

注：

  • count的参数可以支持：
    字段、*、常量值（一般是1）
    count(字段)： 统计的是该字段不为null的行数
    count(*)：统计的是所有行数，（因为*表示所有列，而一行里面不可能所有字段都是null）
    count(1)： 相当于加了一列，该列全为1，因此统计的也是所有行数，在innodb引擎下，与count(*)的效率差不多。
  • 以上五个分组函数计算时都不计null值，除了count(*)
  • sum和avg一般用于处理数值型
    max、min、count可以处理任何数据类型
  • 都可以搭配distinct使用，用于统计去重后的结果, distinct写在函数内
    eg: count(distinct age); 统计不同年龄的个数
  • 和聚合函数一起查询的字段都是group by 后的字段

查询

分组查询

分组查询一般与分组函数一起使用

SELECT 
    分组函数，字段（这里的字段是group by后的字段）
FROM 
    表名
[WHERE
    分组前的条件筛选]
GROUP BY
    字段 （按什么进行分组）
[HAVING
    分组后的条件筛选]
[ORDER BY 
    排序的字段|表达式|函数|别名 [ASC|DESC]]

注：

  • 分组函数后面不能跟普通字段，可以是group by后的字段
  • 为性能应尽量用分组前筛选，即where，但有的必须用分组后筛选
条件筛选分类 数据源 位置 关键字
分组前筛选 原始表 group by子句前 where
分组后筛选 分组查询后的结果集 group by子句后 having
  • 不仅可以按字段进行分组，还可以是 表达式、函数、别名

连接查询（又叫多表查询或多表连接）

  • 笛卡尔积：表1 有m行，表2有n行，结果=m*n行
    发生原因：没有有效的连接条件
    如何避免：添加有效的连接条件
  • 分类：
    • 按标准分类：
      sql92标准:仅仅支持内连接
select 查询列表
from 表1 别名 [连接类型], 表2 别名 
where 连接条件 and 筛选条件
[group by 分组]
[having 筛选条件]
[order by 排序列表]

sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

select 查询列表
from 表1 别名 
[连接类型] join 表2 别名 
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]

- 按功能分类：
    - 1.内连接：[inner] 找交集，即两张表都有的记录
		1.1等值连接
		1.2非等值连接
		1.3自连接
    - 2.外连接： 
        - 应用场景：用于查询一个表中有，另一个表没有的记录
        - 外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
        外连接的查询结果为主表中的所有记录：
        如果从表中有和它匹配的，则显示匹配的值
        如果从表中没有和它匹配的，则显示null

		2.1左外连接 left [outer] 左边的是主表
		2.2右外连接 right [outer] 右边的是主表
                        左外和右外交换两个表的顺序，可以实现同样的效果
		2.3全外连接 full [outer] 【MySQL不支持全外连接】
                        全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的
	
    - 3.交叉连接 cross
        交叉连接查询结果是笛卡尔积的结果

1.1 等值连接

  • 连接条件用 = ，n表连接，至少需要n-1个连接条件
  • 多表等值连接的结果为多表的交集部分
  • 多表的顺序没有要求
  • 一般需要为表起别名（简洁性和区分多个表中重名的字段），注意起了别名就只能用别名了
#查询员工名、工种号、工种名
//sql92
SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;
//sql99
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=  j.`job_id`;

1.2 非等值连接

  • 与等值连接不同的地方在于连接条件中不是用 = 作为两个表的连接条件，比如用 between and
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

1.3 自连接
即自己连接自己，即一张表连续查询两次

#案例：查询 员工名和上级的名称
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`;

子查询

  • 概念：
    出现在其他语句中的select语句，称为子查询或内查询
    外部的查询语句，称为主查询或外查询
  • 分类：

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

    • 按子查询出现的位置：

      • select后面：
        仅仅支持标量子查询
      • from后面：
        支持表子查询
      • where或having后面：
        标量子查询（单行） √
        列子查询 （多行） √
        行子查询
      • exists后面（相关子查询）
        标量子查询
        列子查询
        行子查询
        表子查询
  • 特点：
    ①子查询放在小括号内
    ②子查询一般放在条件的右侧
    ③标量子查询，一般搭配着单行操作符使用
    > < >= <= = <>
    列子查询，一般搭配着多行操作符使用
    in、any/some、all
    ④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

一、where或having后面的子查询

  1. 标量子查询
#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
);
  1. 列子查询
#返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MIN( salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';
  1. 行子查询
#查询员工编号最小并且工资最高的员工信息
#标量子查询
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);
#行子查询
SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

二、from后面的子查询

#查询每个部门的平均工资的工资等级
SELECT  ag_dep.*,g.`grade_level`
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;

三、exists后面的子查询（相关子查询）

#查询有员工的部门名

#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
)

#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

四、案例

# 查询平均工资最低的部门信息

#方式一：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
#③查询哪个部门的平均工资=②
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep

);

#④查询部门信息

SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep
	)
);

#方式二：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

#③查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
);

分页查询

格式：

limit [offset,] size;

注：

  • limit语句放在查询语句的最后，也是最后执行的
  • offset要显示条目的起始索引（起始索引从0开始）, offset = (page-1)*size
  • size 要显示的条目个数

联合查询

概念：
将多条查询语句的结果合并成一个结果
语法：

查询语句1
union
查询语句2

应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：

  • 要求多条查询语句的查询列数是一致的！
  • 要求多条查询语句的查询的每一列的类型和顺序最好一致
  • union默认去重，
    如果使用union all,则可以包含重复项
posted @ 2019-11-05 17:08  Ez_real  阅读(188)  评论(0)  编辑  收藏  举报