SQL（2）DQL语言 条件查询+排序查询+常见函数+分组查询+连接查询

感谢以下链接的教学分享，很好的视频和资源，笔记记录在这里，便于日后查看

新i版-链接: https://pan.baidu.com/s/1GDhRzV_3lUQaETsH4CM8zQ 提取码: rpte

DQL : Data query language

基础查询

#基础查询
/*
语法：
select 查询列表
from 表名;

特点：
1.查询列表可以是：表中的字段，常量值，表达式，函数
2.查询的结果是一个临时性的虚拟表格
*/

USE myemployees;
# 1. 查询表中的单个字段
SELECT last_name FROM employees;
# 2. 查询表中多个字段
SELECT last_name,salary,email FROM employees;
# 3. 查询表中的所有字段
#(可以单击所有左边的字段名或者select * from 表名，但是字段顺序固定)
SELECT 
  `first_name`,
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct`,
  `manager_id`,
  `department_id`,
  `hiredate` 
FROM
  employees ;
#当关键字和字段名字一样的时候，可以着重号``，将字段名标记· 如上所示  
  
 # 方式二
  SELECT * FROM employees;
#4.查询常量值
SELECT 100;
SELECT 'john';
#5.查询表达式
SELECT 100*98;
#6.查询函数
SELECT VERSION();
#7.为字段起名 
/*
①提高可读性，便于理解
②如果有重复的字段名，使用别名可以区分
*/
# 方式一 SELECT xx AS 别名;  
SELECT 100%98 AS result;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
# 方式二 省略AS
SELECT last_name 姓, first_name 名 FROM employees;

# 注意 当别名中有特殊符号（空格，＃等）的时候，将别名加“”或者‘’

#8.去重 SELECT DISTINCT 

# 案例： 查询员工表中涉及的部门编号
SELECT DISTINCT department_id FROM employees;

#9.+作用
/*
select 100+90; 两个都是数值型，则作加法
select ‘123’+90 一个是字符型，试图转为数值型，成功则相加
select ‘john’+90 不成功则将字符型变为0
*/
#案例：员工名和姓连接成一个字段并显示为姓名
SELECT 
      CONCAT(last_name,first_name) AS 姓名 
FROM 
      employees;
 

条件查询

#进阶2 条件查询
/*
语法：
    select 
        查询列表 
    from
        表名
    where
        筛选条件;
分类：
    1. 按照条件表达式筛选
    条件运算符：> < = != <> >= <=
    2. 按逻辑表达式筛选
    逻辑运算符：and  or  not
    3. 模糊查询
    like  
    between and 
    in 
    is null
*/

# 1. 按照条件表达式筛选
# 案例一 员工工资大于一万二的员工信息
SELECT 
    *
FROM 
    employees
WHERE
        salary > 12000;
#  案例二：查询部门编号不等于90号的员工名和部门编号
SELECT
    last_name,department_id
FROM
    employees
WHERE
    department_id <> 90;
#2. 按逻辑表达式筛选
#案例一：工资在10000到20000之间的员工名，工资以及奖金
SELECT
    `first_name`,
    `salary`,
    `commission_pct`
FROM 
    employees
WHERE
    `salary`>=1000 AND `salary`<=20000;

#3. 模糊查询 %通配符
/*
like 一般和通配符一起使用
    通配符： 
    %  任意多个字符,不能匹配null
    _  任意单个字符

*/
# 案例一 员工名包含字符a的员工信息
SELECT 
    *
FROM 
    employees
WHERE
    `last_name` LIKE '%a%';       或者 利用正则表达式 REGEXP  'a'   

like 和RGEXP区别：

LIKE匹配整个列，LIKE 'a' 表示只有完全一样的a才能返回

REGEXP

（1）在列值内进行匹配，如果被匹配的文本在列值出现即可，可以利用^和$定位符使其匹配整个列

（2）不区分大小写，可以通过REGEXP BINARY区分大小写
# 案例二 员工名第二个为u，第五个为t的员工信息
SELECT 
    *
FROM 
    employees
WHERE
    `last_name` LIKE '_u_t%';  
# 案例三 员工名第二个为_的员工信息  用转义字符\
SELECT 
    *
FROM 
    employees
WHERE
    `last_name` LIKE '_\_%';
# 用转义字符  也可以自己指定，就是用 ESCAPE '$'
SELECT 
    *
FROM 
    employees
WHERE
    `last_name` LIKE '_$_%' ESCAPE '$';
# between and 
/*
可以提高语句简洁度
包含左右临界值
不可以颠倒顺序
*/
#案例 员工编号在100到120之间的所有信息
SELECT 
    *
FROM 
    employees
WHERE
    `employee_id` BETWEEN 100 AND 120;
    
    
#  in
/*
判断是否是in列表中的某一项
in列表值必须兼容
不可以使用通配符
*/
#案例 员工工种编号是 IT_PROG，AD_VP的所有信息
SELECT
    * 
FROM
    employees
WHERE
    `job_id` IN ('IT_PROG','AD_VP');

# is not 
/*
=不能用于null

*/
#案例 没有奖金的员工名
SELECT
    last_name
FROM
    employees
WHERE
    `commission_pct` IS NULL;
    
# 安全等于 <=> 可以判断null和普通类型
#案例 没有奖金的员工名
SELECT
    last_name
FROM
    employees
WHERE
    `commission_pct` <=> NULL;
#案例 工资12000的员工名
SELECT
    last_name,`job_id`,`salary`
FROM
    employees
WHERE
    `salary` <=> 12000;

##  is null 和 <=>
# is null 仅仅判断NULL,<=>都可以，但是可读性差

排序查询

## 进阶3  排序查询
/*

语法：
    select 
        查询列表
    from  
        表名
    where 
        条件
    order by 
        排列列表【asc|desc】
特点： 默认 asc 升序
*/
# 案例1： 查询员工信息，工资从高到低,默认升序
SELECT
    *
FROM
    employees
ORDER BY 
    `salary` DESC;
    
# 案例2： 查询部门编号>=90 的员工信息，按入职时间升序

SELECT
    *
FROM
    employees
WHERE
    `department_id`>=90
ORDER BY 
    `hiredate` ASC;
    
    
# 案例3：[表达式排序] 按年薪高低显示员工信息

SELECT
    *,
    `salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
    employees

ORDER BY 
    年薪 ASC;    
    
# 案例4：[函数排序] 按照姓名的长度显示员工的姓名和工资
SELECT
    CONCAT(`last_name`,`first_name`) AS 姓名,
    `salary`
    
FROM
    employees

ORDER BY 
    LENGTH(姓名)ASC;
    
# 案例5：先按员工工资排序，再按员工编号(工资相同的时候，按员工编号排)
SELECT
    *    
FROM
    employees

ORDER BY 
    `salary` ASC,`employee_id` DESC;    

常见函数

# 进阶4： 常见函数
/*
调用： select 函数名(实参列)
分类：
    一.单行函数  concat,length,ifnull,upper,lower
    二.分组/统计/聚合函数
    
*/
#一.单行函数
# 字符函数  utf8 一个汉字3个字节，一个字母一个字节

SELECT LENGTH('john');
SELECT CONCAT(`last_name`,'_',`first_name`) FROM `employees`;
SELECT UPPER('john');
# 案例  将姓大写，名字小写后拼接
SELECT CONCAT(UPPER(`last_name`),'_',LOWER(`first_name`)) FROM `employees`;

#substr,substring  索引从1开始
#指定索引处后面的字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
#指定索引处后一定长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
#案例：姓名中首字符大写，其他小写，然后拼接
SELECT CONCAT(UPPER(SUBSTR(`last_name`,1,1)),'_',LOWER(SUBSTR(`last_name`,2)))FROM employees;

# instr  返回后面的在前面出现第一个的索引，找不到返回0

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
SELECT INSTR('杨不悔爱上了殷六侠','杨不悔') AS out_put;

## trim 仅仅去掉前后的字符，默认是空格
SELECT TRIM('   啦啦啦   ');

SELECT TRIM('a' FROM 'aaaa啦aaa啦啦aaaa')AS out_put;

# lpad left  用指定字符实现总长度的左填充  右填充 rpad
SELECT LPAD('殷素素',10,'***')

# replace 将所有周芷若更换 赵敏
SELECT REPLACE('张无忌爱上周芷若','周芷若','赵敏');

# 二数学函数
#1. ROUND 四舍五入 小数点后保留XX位数
SELECT ROUND(1.65);

SELECT ROUND(1.567,2);

#2. ceil 向上取整  floor 下取整
SELECT CEIL(1.65);

#3.truncate 截断
SELECT TRUNCATE(1.6533,1);

#4.mod 取余  %
SELECT MOD(7,2);

# 三.日期函数

#1. now 返回当前系统日期

SELECT NOW();
#2.  curdate 返回当前系统日期,没有时间

SELECT CURDATE();

#3.  curtime 返回当前系统时间，没有日期

SELECT CURTIME();

#4. 获取指定部分
SELECT YEAR(`hiredate`) FROM employees;

SELECT MONTHNAME(`hiredate`) FROM employees;

#5.str_to_date  将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('9-4-2020','%m-%d-%Y');

# 查询入职日期为1992-4-3号员工日期
SELECT * FROM employees WHERE `hiredate` = STR_TO_DATE('4-3 1992','%c-%d %Y');

#6.date_formate  日期变成符号

# 查询入职日期为1992-4-3号员工日期
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')

# 四.其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

# 五. 流程控制
#1.if
SELECT IF(10>5,'大','小');
#2.case 使用一 switch case
/*
case 要判断的字母
when 常量1 then 要显示的值或者语句;
when 常量2 then 要显示的值或者语句;
...
else 要显示的值或者语句
end
}
*/
# 案例 查询员工工资，要求
/*
部门号=30，显示1.1倍工资
部门号=40，显示1.2倍工资
部门号=50，显示1.3倍工资
部门号=其他，显示原有工资
*/
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;

#使用二  类似多重if
/*
case
when 条件1 then 值1或 语句1;
when 条件2 then 值2或 语句2;
...
else 值n或者语句n
end

*/
# 案例： 查询员工的工资
/**
如果工资大于20000，显示A级别
如果工资大于15000，显示B级别
如果工资大于10000，显示C级别
否则，显示D级别
*/
SELECT `salary` 原始工资,
CASE 
WHEN `salary`>20000 THEN 'A'
WHEN `salary`>20000 THEN 'B'
WHEN `salary`>20000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

# 二.分组函数
/*
功能：用作统计使用

分类：
sum   avg, max,min,count
特点
1. sum avg 一半处理数值型
   max,min,count可以处理任何字段
2. 以上分组函数都忽略null
3. 可以和distinct搭配去重
4. count(*)统计行数
5. 和分组函数一起使用的要求是groub by 后的字段
*/
#1. 简单使用
SELECT SUM(`salary`) FROM employees;
SELECT AVG(`salary`) FROM employees;
SELECT SUM(`salary`) 和,AVG(`salary`) 平均 FROM employees;
#2.忽略null

SELECT SUM(`commission_pct`) 和,AVG(`commission_pct`) 平均 FROM employees;
#3. 可以和distinct搭配使用
SELECT SUM(DISTINCT salary) FROM employees;

#4.count函数详细介绍
SELECT COUNT(`commission_pct`) FROM employees;

SELECT COUNT(*) FROM employees;
# COUNT(1) 中间常量值什么都可以
SELECT COUNT(1) FROM employees;
 

分组函数

# 进阶5：分组查询
/*
语法
    select 分组函数，列（要求出现在group by的后面分组列表中）
    from   表
    where  筛选条件 
    group by 分组列表
    orfer by 
注意：    
    查询列表比较有特殊，要求是分组函数和group by 后出现的字段

特点：
    1.分组前筛选和分组后筛选
    2.group by 支持单个字段分组，多个字段分组，没有顺序要求，用,隔开
    3.也可以添加排序
*/
# 简单分组查询
# 案例1 查询每个工种的最高工资

SELECT MAX(`salary`),`job_id`
FROM `employees`
GROUP BY `job_id`;

# 案例2：查询每个位置上的部门个数

SELECT COUNT(*),`location_id`
FROM `departments`
GROUP BY `location_id`;

# 添加筛选条件
# 案例1 查询邮箱中包含a字符的，每个部门的平均工资

SELECT AVG(`salary`),`department_id`
FROM `employees`
WHERE `email` LIKE '%a%'
GROUP BY `department_id`;

# 案例2 有奖金的每个领导手下员工的最高工资

SELECT MAX(`salary`),`manager_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`;

# 添加复杂筛选条件
# 案例1【分组后筛选】 哪个部门的员工个数大于2
# 查询每个部门的员工个数

SELECT COUNT(*),`department_id`
FROM `employees`
GROUP BY `department_id`;
# 根据上一步结果筛选，再查询大于2的部门

SELECT COUNT(*),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING COUNT(*)>2;

# 案例2【分组后筛选】查询每个工种的有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT MAX(`salary`),`job_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `job_id`
HAVING MAX(`salary`)>12000;

# 案例3【分组后筛选】查询领导编号大于102的每个领导手下的最低工资大于5000的领带编号以及最低工资
SELECT MIN(`salary`),`manager_id`
FROM `employees`
WHERE `manager_id` >102
GROUP BY `manager_id`
HAVING MIN(`salary`)>5000;

#按表达式或函数分组

# 案例1 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(`last_name`) len_name
FROM `employees`
GROUP BY LENGTH(`last_name`)
HAVING COUNT(*)>5;

# 按多个字段分组

# 案例1 查询每个部门，每个工种的平均工资

SELECT AVG(`salary`),`department_id`,`job_id`
FROM `employees`
GROUP BY `department_id`,`job_id`;

连接查询

#进阶6 连接查询
/*
含义：多表查询，当查询的字段来自多个表
分类
    按年代分类：
        sql1992标准：仅仅支持内连接
        sql1999标准【推荐】 不支持全外连接
    
    按功能分类：
        内连接：等值连接，非等值连接，自连接
        外连接：左外，右外，全外
        交叉连接
    
*/
SELECT * FROM beauty; 
SELECT * FROM boys; 

SELECT NAME,boyname FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id;

#一，sql1992标准
/*
多表等值连接的结果为多表的交集
表的顺序没有要求
一般给表起名
*/
# 1. 等值连接
# 案例1：查询女神名和对应的男神名

SELECT NAME,boyname 
FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id;

# 案例2：员工名和对应的部门名

SELECT `last_name`,`department_name`
FROM `employees`,`departments`
WHERE `employees`.`department_id` = `departments`.`department_id`;

#2.为表起别名
/*
提高语句的简洁度
起别名之后，原始名字不起作用
*/
# 案例1. 查询员工名，工种号，工种名

SELECT `last_name`,e.`job_id`,`job_title`
FROM  `employees`AS e,`jobs` AS j
WHERE e.`job_id` = j.`job_id`;

#3.两个表的顺序可以调换

#4.可以加筛选

#案例1： 查询有奖金的员工名和部门名
SELECT `first_name`,`department_name`,commission_pct
FROM `employees`,`departments`
WHERE `employees`.`department_id`= `departments`.`department_id` AND `commission_pct` IS NOT NULL;

#案例2： 城市中第二个字符为o的部门名和城市名
SELECT `department_name`,`city`
FROM `locations` l,`departments` d
WHERE l.`location_id` = d.`location_id`;
AND city likr '_o%'

#5.可以加分组

# 案例1： 查询每个城市的部门个数

SELECT COUNT(*),`city`
FROM `locations` l,`departments` d
WHERE d.`location_id` = l.`location_id`
GROUP BY `city`;

# 案例2： 查询有奖金的每个部门的部门名和部门的领导标号和该部门的最低工资

SELECT `department_name`,d.`manager_id`,MIN(`salary`)
FROM `departments` d,`employees` e
WHERE d.`department_id` = e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.`department_name`,d.`manager_id`

# 自链接
# 查找员工名和上级名称

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`;

#二. sql199语法
/*
语法：
    Select 查询列表
    from 表1 别名【连接类型】
    join 表2 别名 
    on 连接条件
    【where 筛选条件】
    【group by】
    【having】

内连接:inner
外连接:left [outer] right[outer] full
交叉连接:cross 
*/
#1.内连接
/*
select 查询列表
from 表一 别名
inner join 表2 别名
On 连接条件
*/
# 1.1 等值连接
# 案例1： 查询员工名和部门名

SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`;

# 案例2【添加筛选】： 查询名字中包含e的员工名和工种名

SELECT `last_name`,`job_title`
FROM `jobs` j
INNER JOIN `employees` e
ON j.`job_id` = e.`job_id`
WHERE e.`last_name` LIKE '%e%';

# 案例3【添加分组+筛选】： 查询部门个数》3的城市名和部门个数

SELECT l.`city`,COUNT(`department_name`)
FROM`locations` l
INNER JOIN `departments` d
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`
HAVING COUNT(`department_name`)>=3;

# 案例4【添加排序】： 查询哪个部门的员工个数>3的部门名和员工个数，并按照个数降序

SELECT `department_name`,COUNT(*)
FROM `employees` e
INNER JOIN `departments` d
ON d.`department_id` = e.`department_id`
GROUP BY `department_name`
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

# 案例5【多表】： 查询员工名，部门名，和工种名，并按部门排序
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 j.`job_id` = e.`job_id`
ORDER BY `department_name` DESC;

#  1.2.非等值连接
#查询员工级别

SELECT salary,garde_level
FROM employees e 
INNER JOIN jeb_garde g
ON e.`salary` BETWEEN g.lowest AND g.highest;

#1.3 自连接
# 查找员工名和上级名称

SELECT e.last_name,m.last_name
FROM `employees` e
JOIN `employees` m
ON e.`manager_id` = m.`employee_id`;

#2.外连接 
/*
用于查询一个表中有一个表没有的记录
1. 会显示主表的所有内容，当从表没有的时候用null填充
2. 左连接中left 左边是主表
   右连接中right 右边是主表

*/

# 查询男朋友不在Boys表的女神
SELECT b.`name`
FROM `beauty` b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

#案例1 查询哪个部门没有员工
SELECT `department_name`
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

# 交叉连接:99语法标准的笛卡尔连接

# 92 pk 99
/*
99支持较多，
可读性：连接条件和筛选条件分离，可读性高
*/