2.DQL数据查询语言

DQL数据查询语言

一. SQL概述

1. 什么是SQL

​ **SQL（Structured Query Language）**是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。

​ 虽然SQL可以用在所有关系型数据库中，但很多数据库都有自己的一套语法。例如MySQL中的LIMIT分页语句就是MySQL独有的方言，其它数据库都不支持。当然，Oracle或SQL Server也都有自己的语法。

2. SQL语法要求

1). SQL语句可以单行或多行书写，以分号结尾；

2). 可以用空格和缩进来来增强语句的可读性；

3). 关键字不区别大小写，建议使用大写；

3. SQL分类

1). **DQL（Data Query Language）：数据查询语言，用来查询记录（数据） select **

2). DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据） insert / update / delete

3). DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等 create / drop / alter/desc

4). DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别

5). TCL（Transaction Control Language) ：事务控制语言 事务提交commit，事务回滚rollback，保存点savepoint

二. DQL数据查询语言

2.1 概念

DQL（Data Query Language）：数据查询语言，用来查询记录（数据） 执行**select操作**

DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

2.2 语法

SELECT 

selection_list  /*要查询的列名称*/

FROM 

table_list   /*要查询的表名称*/

WHERE 

condition /*行条件*/

GROUP BY 

grouping_columns /*对结果分组*/

HAVING 

condition /*分组后的行条件*/

ORDER BY

sorting_columns /*对结果排序*/

LIMIT 

offset_start, row_count /*结果限定（分页）*/

2.3 准备工作 - 创建表结构

执行初始化脚本 employee.sql，创建并插入以下数据。

员工表：employees

字段名称	字段类型	说明
employee_id	int(6)	员工编号 ，非空，主键，自增长
first_name	varchar(20)	员工姓
last_name	varchar(25)	员工名
email	varchar(25)	邮箱
phone_number	varchar(20)	电话
job_id	varchar(10) 字符串类型	工种编号，外键关联工种表中的主键
salary	double(10,2) 浮点类型	月薪
commission_pct	double(4,2) 有效位数4位，小数点2位	奖金率
manager_id	int(6)	领导编号，来源于本表中的employee_id
department_id	int(4)	部门编号，外键关联部门表中的主键
hiredate	datetime 日期时间类型	入职时间

部门表：departments

字段名称	字段类型	说明
department_id	int(4) 整型	部门编号，非空，主键，自增长
department_name	varchar(3) 字符串类型	部门名称
manager_id	int(6)	部门领导编号
location_id	int(4)	位置编号，外键关联区域表中的主键

工种表：jobs

字段名称	字段类型	说明
job_id	varchar(10) 字符串类型	工种编号，非空，主键
job_title	varchar(35)	工种名称
min_salary	int(6) 整型	最低工资
max_salary	int(6)	最高工资

位置表：locations

字段名称	字段类型	说明
location_id	int(11) 整型	位置编号，非空，主键，自增长
street_address	varchar(40) 字符串类型	街道地址
postal_code	varchar(12)	邮编
city	varchar(30)	城市
state_province	varchar(25)	州/省
country_id	varchar(2)	国家编号

三. 基础查询

3.1 语法

select 查询列表 from 表名;

其中select后面跟的查询列表，可以有多个部分组成，中间用逗号隔开，例如：
select 字段1,字段2,表达式 from 表;	

3.2 执行顺序

from子句
select子句

3.3 查询列表

字段、表达式、常量、函数等

3.4 案例

-- 一、查询常量
select 12;
-- 二、查询表达式
select null+1;
select null+null;
-- 三、查询单个字段
select first_name from employees;
-- 四、查询多个字段
select first_name , last_name , department_id from employees;
-- 五、查询所有字段
select * from employees;
-- 六、查询函数（调用函数，获取返回值）
select concat(first_name , ' # ' , last_name) from employees;
-- 七、给查询字段起别名
#方式一：使用as关键字
select concat(first_name , ' & ' , last_name) as name from employees;
#方式二：使用空格
select concat(first_name , ' $ ' , last_name) '姓名' from employees;
-- 八、+的作用
-- 1.两个操作数都是数值型，执行加法操作
-- 2.其中一个操作数为字符型，一个操作数为数值，则将字符型数据强制转换成数值型,如果无法转换，则直接当做0处理
-- 3.其中一个操作数为null，结果为null
SELECT salary '薪资',commission_pct '提成率', salary*commission_pct '提成' from employees;
-- 九、distinct的使用，去除重复记录
#需求：查询员工涉及到的部门编号
SELECT distinct department_id from employees;
-- 十、查看表的结构
desc	employees;

四. 条件查询

4.1 语法

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

4.2 执行顺序

from子句
where子句
select子句

4.3 案例

-- 一、按关系表达式筛选  关系运算符：>  <  >=  <=  =  <>    补充：也可以使用!=,但不建议
#案例1：查询部门编号不是100的员工信息
select employee_id , first_name , salary ,department_id from employees where department_id <> 100;

#案例2：查询工资<15000的姓名、工资
select  first_name,salary 
from employees
where salary < 15000;
-- 二、按逻辑表达式筛选 逻辑运算符：and  or   not   补充：也可以使用&&  ||   !  ，但不建议
#案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
select  first_name,department_id ,email
from	employees
where department_id < 50 or department_id >100;

#案例2：查询奖金率>0.03 或者 员工编号在60-110之间的员工信息
select first_name, department_id , employee_id ,commission_pct
from employees
where (commission_pct>0.03) or (employee_id >=60 and employee_id <=110);
-- 三、模糊查询 like/not like 
#案例1：查询姓名中包含字符a的员工信息
select first_name , salary from employees where first_name like '%a%';
#案例2：查询姓名中包含最后一个字符为e的员工信息
select first_name  from employees where first_name like '%e';
#案例3：查询姓名中包含第一个字符为e的员工信息
select first_name  from employees where first_name like 'e%';
#案例4：查询姓名中包含第三个字符为x的员工信息
select last_name  from employees where last_name like '__x%';
#案例5：查询姓名中包含第二个字符为_的员工信息
-- \斜杠可以转义后面的_下划线 ， 代表此处_就是一个普通的下划线
select last_name  from employees where last_name like '_\_%';
-- escape 后面的字符， 代表此sql中使用的转义符号,代表此处_就是一个普通的下划线
select last_name  from employees where last_name like '_#_%' escape '#';


-- 四、查询某字段的值是否属于指定的列表之内  in/not in
#案例1：查询部门编号是30/50/90的员工名、部门编号
select first_name , department_id
from employees
where department_id in(30,50,90);

#案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息
select * from employees where job_id <> 'SH_CLERK' and job_id <> 'IT_PROG';
select * from employees where job_id not in('SH_CLERK' , 'IT_PROG');

-- 五、判断某个字段的值是否介于指定的区间范围  between and/not between and
#案例1：查询部门编号是30-90之间的部门编号、员工姓名
select first_name , department_id
from employees
where department_id BETWEEN 30 and 90;

#案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪
select first_name , commission_pct from employees;
select first_name , IFNULL(commission_pct,0) from employees;

select first_name , salary , salary*12 '年薪'
from employees
where salary*(1+IFNULL(commission_pct,0))*12 not BETWEEN 100000 and 200000;

-- 六、查询是null字段  is null ， 查询不是null字段  is not null
#案例1：查询没有奖金的员工信息
select first_name  from employees where commission_pct is null;

#案例2：查询有奖金的员工信息
select first_name  from employees where commission_pct is not null;

select * from employees where salary <> 9000.0;

4.4 特殊用法 之 IFNULL函数

因为salary和commission_pct两列的类型都是数值类型，所以可以做计算。如果salary或commission_pct中有一个字段不是数值类型，那么会出错。

​ 其中commission_pct列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。

需求：下面使用函数IFNULL() 把NULL转换成数值0：

#ifnull(表达式1，表达式2)
/*
表达式1：可能为null的字段或表达式
表达式2：如果表达式1为null，则最终结果显示的值

功能：如果表达式1为null，则显示表达式2，否则显示表达式1
*/
SELECT commission_pct,IFNULL(commission_pct,0) FROM employees;

五. 排序查询 order by

5.1 语法

select   查询列表  from 表名  where 筛选条件  order by 排序列表

5.2 执行顺序

from子句
where子句
select子句
order by 子句

5.3 用法

1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合

2、升序 ，通过 asc ，默认行为
降序 ，通过 desc

5.4 案例

-- 一、按单个字段排序
#案例1：将员工编号>120的员工信息进行工资的升序
select *
from employees
where  employee_id>120
order by salary;

#案例2：将员工编号>120的员工信息进行工资的降序
select *
from employees
where  employee_id>120
order by salary desc;
-- 二、按表达式排序
#案例：对有奖金的员工，按年薪降序
select first_name , salary , commission_pct ,salary*12 '年薪'
from employees
where commission_pct is not null
order by salary*12 desc;


#案例：对有奖金的员工，按总收入降序
select first_name , salary , commission_pct , salary*(1+IFNULL(commission_pct,0))*12 '总收入'
from employees
where commission_pct is not null
order by salary*(1+IFNULL(commission_pct,0))*12 desc;

-- 三、按别名排序
#案例：对有奖金的员工，按年薪降序
select first_name , salary , commission_pct , salary*12 '年薪'
from employees
where commission_pct is not null
order by '年薪' desc;


-- 四、按函数的结果排序
#案例：按姓名的字符数长度进行升序
select first_name , char_length(first_name) 
from employees
order by char_length(first_name) ;

-- 五、按多个字段排序
#案例：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
select first_name , salary ,department_id 
from employees
order by salary , department_id desc;

-- 六、按列数排序(不做要求)
#案例：按第二列排序
select * from employees order by 2; 

六. 常见函数

​ 函数：类似于java中学过的“方法”，为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露函数名，供外部调用。

6.1 字符串函数

1). 常用函数

字符串函数名	函数说明
CONCAT(S1,S2,…,Sn)	连接S1,S2,…,Sn为一个字符串
CONCAT(s, S1,S2,…,Sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上s
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

2). 案例

#一、字符函数
-- 1、CONCAT 拼接字符
#CONCAT(S1,S2,......,Sn)连接S1,S2,......,Sn为一个字符串
select concat(first_name ,'&' ,last_name) as name from employees;
-- 2、LENGTH 获取字节长度
select length('abc');-- 3
select length("你好");-- 6
-- 3、CHAR_LENGTH 获取字符个数
select CHAR_LENGTH('abc');-- 3
select CHAR_LENGTH('你好'); -- 2

-- 4、SUBSTRING 截取子串  注意：起始索引从1开始
#substring(str,pos ,len)str 将要截取的字符串，pos起始下标，默认截取至最后
select first_name , substring(first_name,2,3) from employees;
#substring(str,pos ,len)str 将要截取的字符串，pos起始下标，默认截取至最后
select first_name , substring(first_name,2) from employees;

-- 5、INSTR获取字符第一次出现的索引
#INSERT(str,substr) 查询子串sunstr在字符串str出现的索引位置
select INSERT(first_name,'a') from employees;

-- 6、TRIM去前后指定的字符，默认是去空格
#Trim(s) 去掉字符串s开始与结尾的空格
select concat('###','	aaa		','###'),concat('###',trim('	aaa '),'###'); 

-- 7、LPAD/RPAD  左填充/右填充
select lpad('aaa',10,'#');

-- 8、UPPER/LOWER  变大写/变小写
#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”
select upper(first_name) from employees;
select lower(first_name) from employees;

select first_name ,last_name,concat(upper(substring(last_name,1,1)),lower(substring(last_name,2)),'_',upper(first_name))'OUTPUT' from employees;

-- 9、STRCMP 比较两个字符大小
select strcmp('aaa','bbb');

-- 10、LEFT/RIGHT  截取子串
select left('aaabbb',3);
select right('aaabbb',3);

6.2 数学函数

1). 常用函数

数学函数名	函数说明
ABS（x）	返回x的绝对值
CEIL（x）	返回大于x的最小整数值
FLOOR（x）	返回大于x的最大整数值
MOD(x,y)	返回x/y的模
RAND(x)	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

2). 案例

#二、数学函数
-- 1、ABS 绝对值
select abs(-12),abs(0),abs(10);
-- 2、CEIL 向上取整  返回>=该参数的最小整数
select ceil(12.3),ceil(12.7),ceil(-12.5);

-- 3、FLOOR 向下取整，返回<=该参数的最大整数
select floor(12.3),floor(12.7),floor(-12.5);

-- 4、ROUND 四舍五入
select round(12.2),round(12.5),round(12.45,1);

-- 5、TRUNCATE 截断
select truncate(12.2,0),truncate(12.5,0),truncate(12.45,1);

-- 6、MOD 取余
select mod(10.3);

6.3 日期时间函数

1). 常用函数

日期时间函数	函数说明
CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW()	返回当前系统日期时间
SYSDATE()
CURRENT_TIMESTAMP()
LOCALTIME()
LOCALTIMESTAMP()
YEAR(date)	返回指定时间的年
MONTH(date)	返回指定时间的月
DAY(date)	返回指定时间的日
HOUR(time)	返回指定时间的小时
MINUTE(time)	返回指定时间的分钟
SECOND(time)	返回指定时间的秒
WEEK(date)	返回一年中的第几周
WEEKOFYEAR(date)
DAYOFWEEK()	返回当前是周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回指定的时间是周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回指定的时间是星期几：MONDAY,TUESDAY…SUNDAY
MONTHNAME(date)	返回指定的时间的月份：January，。。。。。
DATEDIFF(date1,date2)	返回date1 - date2的日期间隔
TIMEDIFF(time1, time2)	返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVALE expr , type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

2). 案例

#三、日期函数
-- 1、NOW  系统当前时间
select SYSDATE();
select now();

select YEAR(now()) as 年; 
select MONTH(now()) as 月;
select DAY(now()) as 日;
select HOUR(now()) as 小时; 
select MINUTE(now()) as 分钟; 
select SECOND(now()) as 秒; 


-- WEEKDAY(date)返回指定的时间是周几，注意，周1是0，周2是1，。。。周日是6
select WEEKDAY(now()) as 星期; 
-- DAYOFWEEK()返回当前是周几，注意：周日是1，周一是2，。。。周六是7
select DAYOFWEEK(now()) as 星期;
-- DAYNAME(date)返回指定的时间是星期几：MONDAY,TUESDAY.....SUNDAY
select DAYNAME(now());
-- MONTHNAME(date)返回指定的时间的月份：January，。。。。。
select MONTHNAME(NOW());

-- WEEK(date)返回一年中的第几周 WEEKOFYEAR(date)
select WEEK(now()) , WEEKOFYEAR(now());


-- 2、CURDATE  系统当前日期
-- 3、CURTIME  系统当前时间
select CURDATE();
select CURTIME();
select CURRENT_DATE;
select CURRENT_TIME;
select CURRENT_TIMESTAMP;



-- 4、DATEDIFF(date1,date2)  返回date1 - date2的日期间隔
-- TIMEDIFF(time1, time2)返回time1 - time2的时间间隔
select DATEDIFF('2020-10-10',now());
select TIMEDIFF('16:24:01','16:24:24');
select TIMEDIFF('16:24:01',CURTIME());

-- 5、DATE_FORMAT(datetime ,fmt)  按照字符串fmt格式化日期datetime值
#案例：查看100号员工入职日期
select employee_id , hiredate ,DATE_FORMAT(hiredate,'%y-%m-%d %H:%i:%s 星期%w')from employees where employee_id = 100;


-- 6、STR_TO_DATE 按指定格式解析字符串为日期类型
#案例：查看1998年6月以前入职的员工信息
select employee_id,hiredate 
from employees
where hiredate < STR_TO_DATE('1998年6月','%Y年-%m月');



-- 7、DATE_ADD(datetime, INTERVALE  expr  type)  返回与给定日期时间相差INTERVAL时间段的日期时间
/*
	使用 DATE_ADD(NOW(),INTERVAL 1 MONTH) 这个函数来进行修改时间
	第一个参数是要修改的时间;
	第二个参数固定写法;
	第三个参数的修改的值 : 如果正数就是加,负数就是减;
	第四个参数可填YEAR,MONTH,DAY,HOUR,MINUTE,SECOND;
*/
select now() , DATE_ADD(now(),INTERVAL 1 MONTH);
select now() , DATE_ADD(now(),INTERVAL -1 DAY);
select now() , DATE_ADD(now(),INTERVAL 1 HOUR);
SELECT NOW() , DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);

（1）DATE_ADD(datetime,INTERVAL expr type) 返回与给定日期时间相差INTERVAL时间段的日期时间

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);   

SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);  #可以是负数

SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);  #需要单引号

表达式类型
YEAR	以年为单位追加
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR_MONTH	以年和月为单位追加
DAY_HOUR
DAY_MINUTE
DAY_SECOND
HOUR_MINUTE
HOUR_SECOND
MINUTE_SECOND

（2）DATE_FORMAT(datetime ,fmt) 和 STR_TO_DATE(str, fmt)

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,…）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，…）	%c	数字表示月份（1,2,3,…）
%D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示月中的天数(01,02…)
%e	数字形式表示月中的天数（1,2,3,4,5…）
%H	两位数字表示小数，24小时制（01,02…）	%h和%I	两位数字表示小时，12小时制（01,02…）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4…）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02…)
%W	一周中的星期名称（Sunday…）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，…）
%w	以数字表示周中的天数(0=Sunday,1=Monday…)
%j	以3位数字表示年中的天数(001,002…)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

6.4 流程控制函数

1). 常用函数

流程控制函数	函数说明
IF(value,t ,f)	如果value是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2…[ELSE resultn] END	相当于Java的if…else if…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1…[ELSE 值n] END	相当于Java的switch

#例如
SELECT oid,`status`, 
CASE `status` 
WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单'
END
FROM t_order;

2). 案例

#四、流程控制函数
-- 1、IF函数
#需求：如果有奖金，则显示最终奖金，如果没有，则显示0

-- 2、CASE函数
/*
   情况1 ：类似于switch语句，可以实现等值判断
   CASE 表达式
   WHEN 值1 THEN 结果1
   WHEN 值2 THEN 结果2
   ...
   ELSE 结果n
   END
*/
#案例：部门编号是30，工资显示为2倍；部门编号是50，工资显示为3倍；部门编号是60，工资显示为4倍；否则不变；
#显示部门编号，新工资，旧工资
select department_id '部门编号',salary '旧工资',
   case department_id
   	when 30 then salary*2
   	when 50 then salary*3
   	when 60 then salary*4
   	else salary
   end as 'newSalary'
   from employees;


/*
   情况2：类似于多重IF语句，实现区间判断
   CASE 
   WHEN 条件1 THEN 结果1
   WHEN 条件2 THEN 结果2
   ...
   ELSE 结果n
   END
*/
#案例：如果工资>20000,显示级别A；工资>15000,显示级别B；工资>10000,显示级别C；否则，显示D
select 
   salary as '工资',
   case
   	when salary>20000 then '级别A' 
   	when salary>15000 then '级别B' 
   	when salary>10000 then '级别C' 
   	else '级别D'
   end as 'level' 
from employees;

6.5 分组（聚合）函数

​ 分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数，聚合函数是用来做纵向运算的函数。

1). 常用函数

COUNT()：统计指定列不为NULL的记录行数；

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

2). 案例

#五、分组（聚合）函数
-- 案例：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
select sum(salary) '工资和' , avg(salary) '工资平均值' ,
min(salary) '最低工资'  , max(salary) '最高工资' , count(salary)
from employees;

-- count() 函数
#添加筛选条件
-- 案例1:查询emp表中记录数
select count(*) from employees;

-- 案例2:查询emp表中有佣金的人数
select count(commission_pct) from employees;

-- 案例3:查询emp表中月薪大于2500的人数
select count(first_name) from employees where salary>2500;

-- 案例4:查询有领导的人数
select count(manager_id) from employees;

#其它用途
-- 案例5:统计结果集的行数，推荐使用count(*)。需求：查询员工表中30号部门的人数
select count(*) from employees where department_id=30;

-- 案例6:搭配distinct实现去重的统计。需求：查询有员工的部门个数
select count(distinct department_id) from employees;

-- SUM() 求和函数
-- 案例7:查询所有员工月薪和
select sum(salary) from employees;

-- AVG() 平均值函数
-- 案例8:统计所有员工平均工资
select AVG(salary) from employees;

-- MAX() 和 MIN()  最大值和最小值函数
-- 案例9:查询最高工资和最低工资
select MAX(salary) from employees;
select AVG(salary) from employees;

七. 分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的平均工资，这说明要使用部门来分组。

7.1 语法

select 查询列表

from 表名

where 筛选条件

group by 分组列表

having 分组后筛选

order by 排序列表;

7.2 执行顺序

from  子句

where 子句

group by 子句

having 子句

select 子句

order by 子句

7.3 用法

查询列表往往是，分组函数和被分组的字段

分组查询中的筛选分为两类：

	筛选的基表	使用的关键词	位置
分组前筛选	原始表	where	group by的前面
分组后筛选	分组后的结果集	having	group by的后面

顺序：where——group by ——having

总结：1.分组函数做条件只可能放在having后面

​ 2.在select列表中所有未包含在组函数中的列都应该包含在 group by子句中。

7.4 案例

-- 1）简单的分组
#案例1：查询每个工种的员工平均工资
select job_id ,avg(salary)
from employees
group by job_id;

#案例2：查询每个领导的手下人数
select manager_id ,count(*)
from employees
group by manager_id; 



-- 2）可以实现分组前的筛选
#案例1：查询邮箱中包含a字符的每个部门的最高工资
select department_id,max(salary)
from employees
where email like '%a%'
group by department_id;

#案例2：查询每个领导手下有奖金的员工的平均工资
select manager_id,avg(salary)
from employees
where commission_pct is not null
group by manager_id;



-- 3）可以实现分组后的筛选
#案例1：查询哪个部门的员工个数>5
select	department_id,count(*)
from employees
group by department_id
having count(*)>5;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select job_id,max(salary)
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;

#案例3：领导编号>102的每个领导手下的最低工资大于5000的最低工资
select manager_id,min(salary)
from employees
where manager_id>102 
group by manager_id
having min(salary)>5000;


-- 4）可以实现排序
#案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
select job_id,max(salary) max
from employees
where commission_pct is not null
group by job_id
having max(salary)>6000
order by max;

-- 5）按多个字段分组
#案例：查询每个工种每个部门的最低工资,并按最低工资降序。提示：工种和部门都一样，才是一组
select job_id,department_id,min(salary) min
from employees
group by job_id ,department_id
order by min desc;

八. 连接查询

8.1 准备工作 - 创建表结构

执行初始化脚本 person.sql，创建并插入以下数据。

女神表：girl

字段名称	字段类型	说明
id	int(11)	女生编号 ，主键，非空，自增长
name	varchar(50)	姓名，非空
sex	char(1)	性别，默认女
borndate	datetime	生日
phone	varchar(11)	手机号，非空
photo	blob	头像
boyfriend_id	int(11)	男朋友的编号，外键关联boy表中的主键

男神表：boy

字段名称	字段类型	说明
id	int(11)	男生编号 ，主键，非空，自增长
name	varchar(20)	姓名

8.2 概念

连接查询又称多表查询，当查询语句涉及到的字段来自于多个表时，就会用到连接查询。

直接多表查询，会出现问题： 笛卡尔乘积现象，记录数是 表1 有m行，表2有n行，结果=m*n行

#查看新建的表数据
select count(*) from girl; -- 12
select count(*) from boy;  -- 6

#出现笛卡尔乘积现象
select girl.name '女' , boy.name '男' from girl , boy;  -- 72

发生原因：没有有效的连接条件，所有表中的所有行互相连接

如何避免：为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件

#解决方案：添加有效的连接条件
select girl.name '女' , boy.name '男' from girl , boy where girl.boyfriend_id = boy.id;

分析：

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

8.3 分类

按年代分类：

1). sql92标准:仅仅支持内连接. 内连接： 等值连接 非等值连接 自连接

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

按功能分类：

​ 内连接： 等值连接 非等值连接 自连接

​ 外连接： 左外连接 右外连接 全外连接（不支持）

​ 交叉连接：笛卡尔积

​ 自然连接

8.4 SQL92标准 内连接

1). 语法

select 查询列表

from 表1 别名 , 表2 别名

where 连接条件 and 筛选条件

group by 分组列表

having 分组后筛选

order by 排序列表

2). 执行顺序

from子句

where子句

and子句

group by子句

having子句

select子句

order by子句

3). 等值连接

用法

1）多表等值连接的结果为多表的交集部分

2）n表连接，至少需要n-1个连接条件

3）多表的顺序没有要求

4）一般需要为表起别名

5）可以搭配前面介绍的所有子句使用，比如排序、分组、筛选等

案例

-- 1、多表等值查询
#案例1：查询女神名和对应的男神名
select g.name '女神' ,b.name '男神'
from girl g , boy b
where g.boyfriend_id = b.id;


#案例2：查询员工名和对应的部门名
select e.first_name '员工名' ,d.department_name '部门名'
from employees e , departments d
where e.department_id = d.department_id;


-- 2、为表起别名
#查询员工名、工种号、工种名
select e.first_name '员工表' , j.job_id '工种号' ,j.job_title '公众名'
from employees e, jobs j
where e.job_id = j.job_id;


-- 3、两个表的顺序可以调换
#查询员工名、工种号、工种名
select  e.first_name '员工名' ,e.job_id '工种号' ,j.job_title '公众名'
from jobs j , employees e
where e.job_id = j.job_id;

-- 4、可以加筛选
#案例1：查询有奖金的员工名、部门名
select e.first_name '员工名' , d.department_name '部门名'
from employees e , departments d
where e.department_id = d.department_id and e.commission_pct is not null;

#案例2：查询城市名中第二个字符为o的部门名和城市名
select d.department_name '部门名', l.city '城市名'
from departments d , locations l
where d.location_id = l.location_id and l.city like '_o%';

-- 5、可以加分组
#案例1：查询每个城市的部门个数
select l.city , count(d.department_id)
from departments d , locations l
where d.location_id = l.location_id 
group by l.city;

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select d.department_name '部门名' , d.manager_id '领导编号' , min(salary) '最低工资'
from employees e , departments d
where e.department_id = d.department_id and e.commission_pct is not null
group by d.department_name;


-- 6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
select j.job_title '工种名' , count(*) '员工个数'
from employees e , jobs j
where e.job_id = j.job_id
group by j.job_title
order by count(*) desc;

-- 7、可以实现三表连接？
#案例1：查询员工名、部门名和所在的城市
select e.first_name '员工名', d.department_name '部门名', l.city '所在城市'
from employees e , departments d , locations l
where e.department_id = d.department_id and d.location_id = l.location_id;

#案例2：查询员工名、部门名和所在的城市，要求城市名称以s开头，并且按照部门名称降序。
select e.first_name '员工名', d.department_name '部门名', l.city '所在城市'
from employees e , departments d , locations l
where e.department_id = d.department_id and d.location_id = l.location_id and l.city like 's%'
order by d.department_name desc;

4). 非等值连接

执行初始化脚本 job_grades.sql，创建并插入以下数据。

#案例：查询员工的工资和工资级别
select e.first_name ,e.salary , g.grade_level
from employees e, job_grades g
where e.salary BETWEEN g.lowest_sal and g.highest_sal;

5). 自连接

#案例：查询员工名和上级的名称
select	e.first_name '员工' , m.first_name '领导'
from employees e,employees m
where e.manager_id = m.employee_id;

8.5 SQL99语法

SQL92和SQL99的区别：

​ SQL99，使用 JOIN关键字 代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性。

8.5.1 内连接

1). 语法

SELECT 查询列表

FROM 表名1 别名 [INNER] JOIN  表名2 别名

ON 连接条件

WHERE 筛选条件

GROUP BY 分组列表

HAVING 分组后筛选

ORDER BY 排序列表;

2). 等值连接

-- 1.简单连接
#案例：查询员工名和部门名
select e.first_name '员工',d.department_name '部门名'
from employees e inner join departments d
on e.department_id = d.department_id;

-- 2.添加筛选条件
#案例：查询部门编号>100的部门名和所在的城市名
select d.department_name '部门名' , l.city '所在城市名'
from departments d JOIN locations l
on d.location_id = l.location_id;
where d.department_id >100;

-- 3.添加分组
#案例：查询每个城市的部门个数
select l.city '城市' , count(d.department_id) '部门个数'
from departments d join locations l
on d.location_id = location_id
group by l.city;

-- 4.添加分组+排序
#案例：查询部门中员工个数>10的部门名，并按员工个数降序
select d.department_name '部门名' , count(e.employee_id) '员工个数'
from employees e join departments d
on e.department_id = d.department_id
group by d.department_name
having count(e.employee_id)>10
order by count(e.employee_id) desc;

3). 非等值连接

#案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
select e.salary ,e.department_id ,g.grade_level '工资级别'
from employees e join job_grades g
on e.salary BETWEEN g.lowest_sal and g.highest_sal
where e.department_id BETWEEN 10 and 90;

4). 自连接

#案例：查询员工名和对应的领导名
select e.first_name'员工' , m.first_name '领导'
from employees e join employees m
on e.manager_id = m.employee_id;

8.5.2 外连接

1). 说明

查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null

2). 应用场景

一般用于查询主表中有但从表没有的记录

3). 用法

左连接的话，left join左边为主表；

右连接的话，right join右边为主表；

左/右外连接 = 内连接查询的结果 + 主表中有而从表中没有的记录

全外连接 = 内连接查询的结果 + 表1中有但表2中没有的记录 + 表2中有但表1中没有的记录

4). 语法

select 查询列表

from 表1 别名

left | right | full [outer] join 表2 别名

on 连接条件

where 筛选条件;

5). 左外连接 left outer join

SELECT  FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

注意：OUTER可以省略

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

6). 右外连接 right outer join

SELECT  FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

左/右外连接 = 内连接查询的结果 + 主表中有而从表中没有的记录

#案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null
-- 左连接
select g.name '女神' , b.name '男神'
from girl g left join boy b
on g.boyfriend_id = b.id;

-- 右连接
select  g.name '女神' , b.name '男神'
from boy b right join girl g
on b.id = g.boyfriend_id;


#案例2：查哪个女神没有男朋友
-- 左连接
select g.name '女神' , b.name '男神'
from girl g left join boy b
on g.boyfriend_id = b.id
where b.name is null;

-- 右连接
select  g.name '女神' , b.name '男神'
from boy b right join girl g
on b.id = g.boyfriend_id;


#案例3：查哪个男神没有女朋友
-- 左连接
select b.name '男神' ,g.name '女神'
from boy b left join girl g
on b.id = g.boyfriend_id
where g.name is null;

-- 右连接
select  b.name '男神' ,g.name '女神'
from girl g right join boy b
on  g.boyfriend_id = b.id
where g.name is null;


#案例4：查询哪个部门没有员工，并显示其部门编号和部门名
select d.department_id , d.department_name , e.first_name 
from employees e right join departments d
on e.department_id = d.department_id
where e.first_name is null;


#案例5：查询城市名包含a字符的哪个城市没有部门，并按城市名降序
select l.city
from locations l left join departments d
on l.location_id = d.location_id 
where l.city like '%a%' and d.department_name is null
order by l.city desc;

7). 全外连接 full outer join

全连接就是将表中所有记录都查询出来，且不允许重复。注：此处不支持。

全外连接 = 内连接查询的结果 + 表1中有但表2中没有的记录 + 表2中有但表1中没有的记录

#全外连接（不支持）
SELECT g.`name` , b.name 
FROM boy b 
FULL  OUTER JOIN girl  g  ON g.`boyfriend_id` = b.`id`;

8.5.3 交叉连接（笛卡尔乘积） CROSS JOIN

#交叉连接
SELECT g.`name` , b.name 
FROM boy b 
CROSS JOIN girl  g ;

8.5.4 自然连接（不常用） NATURAL JOIN

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。

而自然连接无需你去给出主外键等式，它会自动找到这一等式：

两张连接的表中字段名称和类型完全一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

SELECT  FROM emp NATURAL JOIN dept;  -- 内

SELECT  FROM emp NATURAL LEFT JOIN dept; -- 左外

SELECT  FROM emp NATURAL RIGHT JOIN dept; -- 右外

8.5.5 连接查询图分析

九. 子查询

9.1 概念

出现在其他语句的内部的select语句，称为子查询或内查询

里面嵌套其他select语句的查询语句，称为主查询或外查询

子查询不一定必须出现在select语句内部，只是出现在select语句内部的时候较多！

9.2 示例

select first_name from employees where department_id  > (

   	 select department_id from departments   where  location_id=1700  ) ;

9.3 分类

按子查询出现的位置进行分类：

​ 1、select后面
​ 要求：子查询的结果为单行单列（标量子查询）

​ 2、from后面
​ 要求：子查询的结果可以为多行多列

​ 3、where或having后面
​ 要求：子查询的结果必须为单列；单行子查询；多行子查询

​ 4、exists后面
​ 要求：子查询结果必须为单列（相关子查询）

其中where或having后面是重点：

​ 单行子查询

​ 特点：子查询的结果集只有一行一列

​ 多行子查询

​ 特点：子查询的结果集有多行一列

9.4 特点

1、子查询语句需要放在小括号内，提高代码的阅读性

2、子查询先于主查询执行，一般来讲，主查询会用到子查询的结果

3、如果子查询放在条件中，一般来讲，子查询需要放在条件的右侧

​ 示例：where job_id>(子查询)

​ 不能写成：where (子查询)<job_id

4、单行子查询对应的使用单行操作符： > < >= <= = <>

​ 多行子查询对应的使用多行操作符: in 、any 、all 、not in

​ 例如：

​ in : 判断某字段是否在指定列表内 x in(10,30,50)

​ any : 判断某字段的值是否满足其中任意一个 x>any(10,30,50) x>min() x=any(10,30,50) x in(10,30,50)

​ all : 判断某字段的值是否满足里面所有的 x >all(10,30,50) x >max()

9.5 案例

-- 1.单行子查询
#案例1：谁的工资比 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');


#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
#第一步
select job_id from employees where employee_id = 141;
#第二步
select salary from employees where employee_id = 143;
#第三步
select first_name ,employee_id ,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);


#案例3：返回公司工资最少的员工的last_name,job_id和salary
#第一步
select min(salary) from employees;
#第二步
select last_name ,job_id ,salary
from employees
where salary = (select min(salary) from employees);


#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#第一步
select min(salary) from employees where department_id = 50;
#第二步
select department_id , min(salary)
from employees
group by department_id
having min(salary) > (select min(salary) from employees where department_id = 50);

-- 2.多行子查询
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#第一步
select department_id from departments where location_id in(1400,1700);
#第二步
select first_name 
from employees e 
where department_id in(select department_id from departments where location_id in(1400,1700));

#案例2：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
#第一步
select salary from employees where job_id = 'IT_PROG';
#第二步
select	employee_id ,first_name , job_id ,salary  
from	employees 
where	salary < any(select salary from employees where job_id = 'IT_PROG')


#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
#第一步
select salary from employees where job_id = 'IT_PROG';
#第二步
select	employee_id ,first_name , job_id ,salary  
from	employees 
where	salary < all(select salary from employees where job_id = 'IT_PROG')



#案例4：使用子查询实现城市为Toroto的，且工资>10000的员工姓名
#第一步
select location_id from locations where city='Toronto'; 
#第二步
select department_id  
from	departments
where location_id =(select location_id from locations where city='Toronto');
#第三步
select first_name,salary
from employees
where department_id 
in(select department_id  
from	departments
where location_id =(select location_id from locations where city='Toronto'))
and salary >10000;

-- 二、放在select后面
#案例：查询部门编号是50的员工个数
#第一步
select count(*) from employees where department_id = 50;
#第二步
select(select count(*) from employees where department_id = 50);

-- 三、放在from后面
#案例：查询每个部门的平均工资的工资级别
#第一步
select department_id ,avg(salary) from employees group by department_id;
#第二步
select s.deptId , s.avg , g.grade_level
from 
	(select department_id deptId ,avg(salary) avg  from employees group by department_id) s,
	job_grades g
where s.avg between g.lowest_sal and g,highest_sal;


-- 四、放在exists后面
#案例1 ：查询有无名字叫“Abel”的员工信息
select * from employees where last_name='Abel';
-- 返回1代表值存在，返回0代表值不存在
select EXISTS(select * from employees where last_name='Abel') '是否有Abel员工';


#案例2：查询没有女朋友的男神信息
select boyfriend_id from girl where boyfriend_id is not null;
select id from boy where id not in(select boyfriend_id from girl where boyfriend_id is not null);

-- exists
select id 
from boy 
where EXISTS(select boyfriend_id from girl where boyfriend_id is not null)

十. 分页查询

10.1 应用场景

当页面上的数据，一页显示不全，则需要分页显示

分页查询的sql命令请求数据库服务器——>服务器响应查询到的多条数据——>前台页面

10.2 语法

		select 查询列表
		
		from 表1 别名
		
		join 表2 别名
		
		on 连接条件
		
		where 筛选条件
		
		group by 分组
		
		having 分组后筛选
		
		order by 排序列表
		
		limit 起始条目索引,显示的条目数

其中 LIMIT 用来限定查询结果的起始行，以及每页显示行数。

MySQL limit关键字

Oracle rownum关键字

10.3 执行顺序

		from子句
		
		join子句
		
		on子句
		
		where子句
		
		group by子句
		
		having子句
		
		select子句
		
		order by子句
		
		limit子句

10.4 特点

​ ①起始条目索引如果不写，默认是0
​ ②limit后面支持两个参数
​ 参数1：显示的起始条目索引
​ 参数2：条目数

10.5 公式

假如要显示的页数是page，每页显示的条目数为size

	select *
	from employees
	limit (page-1)*size , size;

例如：
	page	     size=10
	 1      	 limit 0,10
	 2			 limit 10,10
	 3			 limit 20,10
	 4			 limit 30,10

10.6 案例

#案例1：查询员工信息表的前5条
select * from employees limit 0,5;
select * from employees limit 5;


#案例2：查询第11条——第20条的员工信息
select * from employees limit 10,10;

#案例3：查询有奖金的工资最高的前三名员工名、工资、奖金、部门编号
select first_name ,salary ,commission_pct ,department_id
from employees
order by salary desc 
limit 3;



#案例4：查询年薪最高的前10名
select first_name ,salary ,commission_pct ,department_id
from employees
order by salary*12 desc 
limit 10;



#案例5：使用分页查询实现，查询员工信息表中部门为50号的工资最低的5名员工信息
select *
from employees
where department_id = 50
order by salary
limit 5;

十一. 联合查询

11.1 应用场景

当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询

11.2 语法

select 查询列表 from 表1  where 筛选条件  
union
select 查询列表 from 表2  where 筛选条件  

11.3 特点

1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2、union实现去重查询
union all 实现全部查询，包含重复项

11.4 案例

#案例1：查询所有国家的年龄>20岁的用户信息
select * from chinese where age >20
union
select * from japanese where jage >20
union
select * from usa where uage >20;

#案例2：查询所有国家的用户姓名和年龄
select name , age  from chinese 
union
select jname , jage  from japanese
union
select uname , uage  from usa ;



#案例3：union自动去重/union all 可以支持重复项
select name , age  from chinese 
union all
select jname , jage  from japanese
union	all
select uname , uage  from usa ;

总结：SQL的执行顺序

手写的顺序：

SELECT 

selection_list  /*要查询的列名称*/

FROM  

table1   /*表名称，取别名*/

JOIN  

table2   /*表名称，取别名*/

ON 

condition   /*连接条件*/

WHERE 

condition /*筛选条件*/

GROUP BY 

grouping_columns /*对结果分组*/

HAVING 

condition /*分组条件*/

ORDER BY

sorting_columns /*对结果排序*/

LIMIT 

offset_start, row_count /*结果限定（分页）起始条目索引 , 显示的条目数  */

真正执行的顺序： 随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。

下面是经常出现的查询顺序：

		from  子句
		
		join  子句
		
		on  子句
		
		where  子句
		
		group by  子句
		
		having  子句
		
		select  子句
		
		distinct  子句
		
		order by  子句
		
		limit  子句