MySQL基础篇-（二）DQL语言(查询)

我就是我 2021-06-10 20:40 419阅读 0赞

# DQL语言 #

## 基础查询 ##

### 语法 ###

select 查询列表 from 表名;

### 特点 ###

1、查询列表可以是字段、常量、表达式、函数，也可以是多个  
2、查询结果是一个虚拟表

（类似于：System.out.println(打印东西)）

### 示例 ###

USE myemployees;
    
    #1.查询表中的单个字段
    
    SELECT last_name FROM employees;
    
    #2.查询表中的多个字段
    SELECT last_name,salary,email FROM employees;
    
    #3.查询表中的所有字段
    
    #方式一：
    SELECT 
        `employee_id`,
        `first_name`,
        `last_name`,
        `phone_number`,
        `last_name`,
        `job_id`,
        `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.起别名
     /* ①便于理解 ②如果要查询的字段有重名的情况，使用别名可以区分开来 */
     #方式一：使用as
    SELECT 100%98 AS 结果;
    SELECT last_name AS 姓,first_name AS 名 FROM employees;
    
    #方式二：使用空格
    SELECT last_name 姓,first_name 名 FROM employees;
    
    
    #案例：查询salary，显示结果为 out put
    SELECT salary AS "out put" FROM employees;
    
    
    #8.去重
    #DISTINCT
    
    #案例：查询员工表中涉及到的所有的部门编号
    SELECT DISTINCT department_id FROM employees;
    
    
    #9.+号的作用
    
    /* java中的+号： ①运算符，两个操作数都为数值型 ②连接符，只要有一个操作数为字符串 mysql中的+号： 仅仅只有一个功能：运算符 select 100+90; 两个操作数都为数值型，则做加法运算 select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型 如果转换成功，则继续做加法运算 select 'john'+90; 如果转换失败，则将字符型数值转换成0 select null+10; 只要其中一方为null，则结果肯定为null */
    
    #案例：查询员工名和姓连接成一个字段，并显示为 姓名
    
    
    SELECT CONCAT('a','b','c') AS 结果;
    
    SELECT 
    	CONCAT(last_name,first_name) AS 姓名
    FROM
    	employees;
    	
    	
    	
    #案例
    #显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
    
    #IFNULL(a,b) -> 如果a为null则值为b否则为a
    
    
    SELECT
    	CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
    FROM
    	employees;

## 条件查询 ##

### 语法 ###

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

### 分类 ###

一、按条件表达式筛选  
简单条件运算符：> < = != <> >= <=

二、按逻辑表达式筛选  
逻辑运算符：  
作用：用于连接条件表达式  
&& || !  
and or not

&&和and：两个条件都为true，结果为true，反之为false  
||或or： 只要有一个条件为true，结果为true，反之为false  
!或not： 如果连接的条件本身为false，结果为true，反之为false

三、模糊查询  
like  
between and  
in  
is null

#### 按条件表达式筛选 ####

#案例1：查询工资>12000的员工信息
    
    SELECT 
    	*
    FROM
    	employees
    WHERE
    	salary>12000;
    	
    	
    #案例2：查询部门编号不等于90号的员工名和部门编号
    SELECT 
    	last_name,
    	department_id
    FROM
    	employees
    WHERE
    	department_id<>90;

#### 按逻辑表达式筛选 ####

#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
    SELECT
    	last_name,
    	salary,
    	commission_pct
    FROM
    	employees
    WHERE
    	salary>=10000 AND salary<=20000;
    #案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
    SELECT
    	*
    FROM
    	employees
    WHERE
    	NOT(department_id>=90 AND  department_id<=110) OR salary>15000;

#### 模糊查询 ####

between and  
in  
is null|is not null

##### like #####

特点：  
①一般和通配符搭配使用  
通配符：  
% 任意多个字符,包含0个字符  
\_ 任意单个字符

#案例1：查询员工名中包含字符a的员工信息
    
    select 
    	*
    from
    	employees
    where
    	last_name like '%a%';#abc
    #案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
    select
    	last_name,
    	salary
    FROM
    	employees
    WHERE
    	last_name LIKE '__n_l%';
    
    
    #案例3：查询员工名中第二个字符为_的员工名
    
    SELECT
    	last_name
    FROM
    	employees
    WHERE
    	last_name LIKE '_$_%' ESCAPE '$';

##### between and #####

①使用between and 可以提高语句的简洁度  
②包含临界值  
③两个临界值不要调换顺序

#案例1：查询员工编号在100到120之间的员工信息
    
    SELECT
    	*
    FROM
    	employees
    WHERE
    	employee_id >= 120 AND employee_id<=100;
    #----------------------
    SELECT
    	*
    FROM
    	employees
    WHERE
    	employee_id BETWEEN 120 AND 100;

##### in #####

含义：判断某字段的值是否属于in列表中的某一项  
特点：  
①使用in提高语句简洁度  
②in列表的值类型必须一致或兼容  
③in列表中不支持通配符

#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
    
    SELECT
    	last_name,
    	job_id
    FROM
    	employees
    WHERE
    	job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
    
    
    SELECT
    	last_name,
    	job_id
    FROM
    	employees
    WHERE
    	job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
    
    #4、is null

=或<>不能用于判断null值  
is null或is not null 可以判断null值

#案例1：查询没有奖金的员工名和奖金率
    SELECT
    	last_name,
    	commission_pct
    FROM
    	employees
    WHERE
    	commission_pct IS NULL;
    
    
    #案例1：查询有奖金的员工名和奖金率
    SELECT
    	last_name,
    	commission_pct
    FROM
    	employees
    WHERE
    	commission_pct IS NOT NULL;
    
    #----------以下为×
    SELECT
    	last_name,
    	commission_pct
    FROM
    	employees
    
    WHERE 
    	salary IS 12000;

##### 安全等于 <=> #####

#案例1：查询没有奖金的员工名和奖金率
    SELECT
    	last_name,
    	commission_pct
    FROM
    	employees
    WHERE
    	commission_pct <=>NULL;
    	
    	
    #案例2：查询工资为12000的员工信息
    SELECT
    	last_name,
    	salary
    FROM
    	employees
    
    WHERE 
    	salary <=> 12000;

is null pk <=>

IS NULL:仅仅可以判断NULL值，可读性较高，建议使用  
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70]

## 排序查询 ##

### 语法 ###

select 查询列表 from 表名【where 筛选条件】 order by 排序的字段或表达式;

### 特点 ###

1、asc代表的是升序，可以省略  
desc代表的是降序

2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段

3、order by子句在查询语句的最后面，除了limit子句

### 代码示例 ###

#1、按单个字段排序
    SELECT * FROM employees ORDER BY salary DESC;
    
    #2、添加筛选条件再排序
    
    #案例：查询部门编号>=90的员工信息，并按员工编号降序
    
    SELECT *
    FROM employees
    WHERE department_id>=90
    ORDER BY employee_id DESC;
    
    
    #3、按表达式排序
    #案例：查询员工信息 按年薪降序
    
    
    SELECT *,salary*12*(1+IFNULL(commission_pct,0))
    FROM employees
    ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
    
    
    #4、按别名排序
    #案例：查询员工信息 按年薪升序
    
    SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
    FROM employees
    ORDER BY 年薪 ASC;
    
    #5、按函数排序
    #案例：查询员工名，并且按名字的长度降序
    
    SELECT LENGTH(last_name),last_name 
    FROM employees
    ORDER BY LENGTH(last_name) DESC;
    
    #6、按多个字段排序
    
    #案例：查询员工信息，要求先按工资降序，再按employee_id升序
    SELECT *
    FROM employees
    ORDER BY salary DESC,employee_id ASC;
    
    
    #1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
    
    SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
    FROM employees
    ORDER BY 年薪 DESC,last_name ASC;
    
    
    #2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
    SELECT last_name,salary
    FROM employees
    
    WHERE salary NOT BETWEEN 8000 AND 17000
    ORDER BY salary DESC;
    
    #3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
    
    SELECT *,LENGTH(email)
    FROM employees
    WHERE email LIKE '%e%'
    ORDER BY LENGTH(email) DESC,department_id ASC;

## 常见函数 ##

### 概述 ###

功能：类似于java中的方法  
好处：提高重用性和隐藏实现细节  
调用：select 函数名(实参列表);

### 单行函数 ###

#### 字符函数 ####

- length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
    - concat 拼接字符串
    - substr：截取从指定索引处后面所有字符/截取从指定索引处指定字符长度的字符
    - instr 返回子串第一次出现的索引，如果找不到返回0
    - trim ：去前后空格或指定字符
    - upper 字母变大写
    - lower 字母变小写
    - lpad 用指定的字符实现左填充指定长度
    - rpad 用指定的字符实现右填充指定长度
    - replace 替换

#1.length 获取参数值的字节个数
    SELECT LENGTH('john');
    SELECT LENGTH('张三丰hahaha');
    
    SHOW VARIABLES LIKE '%char%'
    
    #2.concat 拼接字符串
    
    SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
    
    #3.upper、lower
    SELECT UPPER('john');
    SELECT LOWER('joHn');
    #示例：将姓变大写，名变小写，然后拼接
    SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;
    
    #4.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)))  out_put
    FROM employees;
    
    #5.instr 返回子串第一次出现的索引，如果找不到返回0
    
    SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
    
    #6.trim
    
    SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
    
    SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;
    
    #7.lpad 用指定的字符实现左填充指定长度
    
    SELECT LPAD('殷素素',2,'*') AS out_put;
    
    #8.rpad 用指定的字符实现右填充指定长度
    
    SELECT RPAD('殷素素',12,'ab') AS out_put;
    
    
    #9.replace 替换
    
    SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

#### 数学函数 ####

- round：四舍五入
    - ceil：向上取整
    - floor：向下取整
    - truncate：截断(保留小数)
    - mod：取余

#round 四舍五入
    SELECT ROUND(-1.55);
    SELECT ROUND(1.567,2);
    
    
    #ceil 向上取整,返回>=该参数的最小整数
    
    SELECT CEIL(-1.02);
    
    #floor 向下取整，返回<=该参数的最大整数
    SELECT FLOOR(-9.99);
    
    #truncate 截断
    
    SELECT TRUNCATE(1.69999,1);
    
    #mod取余
    /* mod(a,b) ： a-a/b*b mod(-10,-3):-10- (-10)/(-3)*（-3）=-1 */
    SELECT MOD(10,-3);
    SELECT 10%3;

#### 日期函数 ####

- now：返回当前系统日期+时间
    - curdate：返回当前系统日期，不包含时间
    - curtime:返回当前时间，不包含日期
    - year
    - month
    - monthname：英文的月
    - day
    - hour
    - minute
    - second
    - str_to_date: 将字符通过指定的格式转换成日期
    - date_format: 将日期转换成字符

#now 返回当前系统日期+时间
    SELECT NOW();
    
    #curdate 返回当前系统日期，不包含时间
    SELECT CURDATE();
    
    #curtime 返回当前时间，不包含日期
    SELECT CURTIME();
    
    
    #可以获取指定的部分，年、月、日、小时、分钟、秒
    SELECT YEAR(NOW()) 年;
    SELECT YEAR('1998-1-1') 年;
    
    SELECT  YEAR(hiredate) 年 FROM employees;
    
    SELECT MONTH(NOW()) 月;
    SELECT MONTHNAME(NOW()) 月;
    
    
    #str_to_date 将字符通过指定的格式转换成日期
    
    SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
    
    #查询入职日期为1992--4-3的员工信息
    SELECT * FROM employees WHERE hiredate = '1992-4-3';
    
    SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
    
    
    #date_format 将日期转换成字符
    
    SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
    
    #查询有奖金的员工名和入职日期(xx月/xx日 xx年)
    SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
    FROM employees
    WHERE commission_pct IS NOT NULL;

#### 其他函数 ####

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

#### 流程控制函数 ####

#1.if函数： if else 的效果
    
    SELECT IF(10<5,'大','小');
    
    SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
    FROM employees;
    
    
    
    
    #2.case函数的使用一： switch case 的效果
    
    /* java中 switch(变量或表达式){ case 常量1：语句1;break; ... default:语句n;break; } mysql中 case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; when 常量2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; 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;
    
    
    
    #3.case 函数的使用二：类似于 多重if
    /* java中： if(条件1){ 语句1； }else if(条件2){ 语句2； } ... else{ 语句n; } mysql中： 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>15000 THEN 'B'
    WHEN salary>10000 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函数的单独介绍  
一般使用count(\*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

#### 代码示例: ####

#1、简单 的使用
    SELECT SUM(salary) FROM employees;
    SELECT AVG(salary) FROM employees;
    SELECT MIN(salary) FROM employees;
    SELECT MAX(salary) FROM employees;
    SELECT COUNT(salary) FROM employees;
    
    
    SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
    FROM employees;
    
    SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
    FROM employees;
    
    #2、参数支持哪些类型
    
    SELECT SUM(last_name) ,AVG(last_name) FROM employees;
    SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;
    
    SELECT MAX(last_name),MIN(last_name) FROM employees;
    
    SELECT MAX(hiredate),MIN(hiredate) FROM employees;
    
    SELECT COUNT(commission_pct) FROM employees;
    SELECT COUNT(last_name) FROM employees;
    
    #3、是否忽略null
    
    SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees;
    
    SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;
    
    SELECT COUNT(commission_pct) FROM employees;
    SELECT commission_pct FROM employees;
    
    
    #4、和distinct搭配
    
    SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
    
    SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
    
    
    
    #5、count函数的详细介绍
    
    SELECT COUNT(salary) FROM employees;
    
    
    SELECT COUNT(*) FROM employees;
    
    SELECT COUNT(1) FROM employees;
    
    效率：
    MYISAM存储引擎下  ，COUNT(*)的效率高
    INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
    
    
    #6、和分组函数一同查询的字段有限制
    
    SELECT AVG(salary),employee_id  FROM employees;

## 分组查询 ##

### 语法 ###

select 查询列表 from 表【where 筛选条件】 group by 分组的字段【order by 排序的字段】;

### 特点 ###

1、和分组函数一同查询的字段必须是group by后出现的字段  
2、筛选分为两类：分组前筛选和分组后筛选  
针对的表 位置 连接的关键字  
分组前筛选 原始表 group by前 where

分组后筛选 group by后的结果集 group by后 having

问题1：分组函数做筛选能不能放在where后面  
答：不能

问题2：where——group by——having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、分组可以按单个字段也可以按多个字段  
4、可以搭配着排序使

### 代码示例 ###

#引入：查询每个部门的员工个数
    
    SELECT COUNT(*) FROM employees WHERE department_id=90;
    #1.简单的分组
    
    #案例1：查询每个工种的员工平均工资
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id;
    
    #案例2：查询每个位置的部门个数
    
    SELECT COUNT(*),location_id
    FROM departments
    GROUP BY location_id;
    
    
    #2、可以实现分组前的筛选
    
    #案例1：查询邮箱中包含a字符的 每个部门的最高工资
    
    SELECT MAX(salary),department_id
    FROM employees
    WHERE email LIKE '%a%'
    GROUP BY department_id;
    
    
    #案例2：查询有奖金的每个领导手下员工的平均工资
    
    SELECT AVG(salary),manager_id
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY manager_id;
    
    
    
    #3、分组后筛选
    
    #案例：查询哪个部门的员工个数>5
    
    #①查询每个部门的员工个数
    SELECT COUNT(*),department_id
    FROM employees
    GROUP BY department_id;
    
    #② 筛选刚才①结果
    
    SELECT COUNT(*),department_id
    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的领导编号和最低工资
    
    manager_id>102
    
    SELECT manager_id,MIN(salary)
    FROM employees
    GROUP BY manager_id
    HAVING MIN(salary)>5000;
    
    
    #4.添加排序
    
    #案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
    
    SELECT job_id,MAX(salary) m
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY job_id
    HAVING m>6000
    ORDER BY m ;
    
    
    #5.按多个字段分组
    
    #案例：查询每个工种每个部门的最低工资,并按最低工资降序
    
    SELECT MIN(salary),job_id,department_id
    FROM employees
    GROUP BY department_id,job_id
    ORDER BY MIN(salary) DESC;
    
    
    #案例
    #1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
    
    SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
    FROM employees
    GROUP BY job_id
    ORDER BY job_id;
    
    
    #2.查询员工最高工资和最低工资的差距（DIFFERENCE）
    SELECT MAX(salary)-MIN(salary) DIFFRENCE
    FROM employees;
    #3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
    SELECT MIN(salary),manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
    HAVING MIN(salary)>=6000;
    
    
    
    #4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
    SELECT department_id,COUNT(*),AVG(salary) a
    FROM employees
    GROUP BY department_id
    ORDER BY a DESC;
    #5.选择具有各个job_id的员工人数
    SELECT COUNT(*) 个数,job_id
    FROM employees
    GROUP BY job_id;

## 连接查询 ##

### 概念 ###

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m\*n行

发生原因：没有有效的连接条件  
如何避免：添加有效的连接条件

分类：

按年代分类：
    sql92标准:仅仅支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
    
    按功能分类：
    	内连接：
    		等值连接
    		非等值连接
    		自连接
    	外连接：
    		左外连接
    		右外连接
    		全外连接
    	
    	交叉连接

### sql92标准 ###

#### 等值连接 ####

① 多表等值连接的结果为多表的交集部分  
②n表连接，至少需要n-1个连接条件  
③ 多表的顺序没有要求  
④一般需要为表起别名  
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

#案例1：查询女神名和对应的男神名
    SELECT NAME,boyName 
    FROM boys,beauty
    WHERE beauty.boyfriend_id= boys.id;
    
    #案例2：查询员工名和对应的部门名
    
    SELECT last_name,department_name
    FROM employees,departments
    WHERE employees.`department_id`=departments.`department_id`;
    
    
    
    #2、为表起别名
    /* ①提高语句的简洁度 ②区分多个重名的字段 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定 */
    #查询员工名、工种号、工种名
    
    SELECT e.last_name,e.job_id,j.job_title
    FROM employees  e,jobs j
    WHERE e.`job_id`=j.`job_id`;
    
    
    #3、两个表的顺序是否可以调换
    
    #查询员工名、工种号、工种名
    
    SELECT e.last_name,e.job_id,j.job_title
    FROM jobs j,employees e
    WHERE e.`job_id`=j.`job_id`;
    
    
    #4、可以加筛选
    
    
    #案例：查询有奖金的员工名、部门名
    
    SELECT last_name,department_name,commission_pct
    
    FROM employees e,departments d
    WHERE e.`department_id`=d.`department_id`
    AND e.`commission_pct` IS NOT NULL;
    
    #案例2：查询城市名中第二个字符为o的部门名和城市名
    
    SELECT department_name,city
    FROM departments d,locations l
    WHERE d.`location_id` = l.`location_id`
    AND city LIKE '_o%';
    
    #5、可以加分组
    
    
    #案例1：查询每个城市的部门个数
    
    SELECT COUNT(*) 个数,city
    FROM departments d,locations l
    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 commission_pct IS NOT NULL
    GROUP BY department_name,d.`manager_id`;
    #6、可以加排序
    
    
    #案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
    
    SELECT job_title,COUNT(*)
    FROM employees e,jobs j
    WHERE e.`job_id`=j.`job_id`
    GROUP BY job_title
    ORDER BY COUNT(*) DESC;
    
    
    
    
    #7、可以实现三表连接？
    
    #案例：查询员工名、部门名和所在的城市
    
    SELECT last_name,department_name,city
    FROM employees e,departments d,locations l
    WHERE e.`department_id`=d.`department_id`
    AND d.`location_id`=l.`location_id`
    AND city LIKE 's%'
    
    ORDER BY department_name DESC;

#### 非等值连接 ####

#案例1：查询员工的工资和工资级别
    
    SELECT salary,grade_level
    FROM employees e,job_grades g
    WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
    AND g.`grade_level`='A';

#### 自连接 ####

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

### sql99语法 ###

#### 概念 ####

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

分类：  
内连接（★）：inner  
外连接  
左外(★):left 【outer】  
右外(★)：right 【outer】  
全外：full【outer】  
交叉连接：cross

#### 内连接 ####

语法：

select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;

分类：  
等值  
非等值  
自连接

特点：  
①添加排序、分组、筛选  
②inner可以省略  
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读  
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

##### 等值连接 #####

#案例1.查询员工名、部门名
    
    SELECT last_name,department_name
    FROM departments d
     JOIN  employees e
    ON e.`department_id` = d.`department_id`;
    
    
    
    #案例2.查询名字中包含e的员工名和工种名（添加筛选）
    SELECT last_name,job_title
    FROM employees e
    INNER JOIN jobs j
    ON e.`job_id`=  j.`job_id`
    WHERE e.`last_name` LIKE '%e%';
    
    
    
    #3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
    
    #①查询每个城市的部门个数
    #②在①结果上筛选满足条件的
    SELECT city,COUNT(*) 部门个数
    FROM departments d
    INNER JOIN locations l
    ON d.`location_id`=l.`location_id`
    GROUP BY city
    HAVING COUNT(*)>3;
    
    
    
    
    #案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
    
    #①查询每个部门的员工个数
    SELECT COUNT(*),department_name
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id`=d.`department_id`
    GROUP BY department_name
    
    #② 在①结果上筛选员工个数>3的记录，并排序
    
    SELECT COUNT(*) 个数,department_name
    FROM employees e
    INNER JOIN departments d
    ON e.`department_id`=d.`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 e.`job_id` = j.`job_id`
    
    ORDER BY department_name DESC;

##### 非等值连接 #####

#查询员工的工资级别
    
    SELECT salary,grade_level
    FROM employees e
     JOIN job_grades g
     ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
     
     
     #查询工资级别的个数>20的个数，并且按工资级别降序
     SELECT COUNT(*),grade_level
    FROM employees e
     JOIN job_grades g
     ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
     GROUP BY grade_level
     HAVING COUNT(*)>20
     ORDER BY grade_level DESC;

##### 自连接 #####

#查询员工的名字、上级的名字
     SELECT e.last_name,m.last_name
     FROM employees e
     JOIN employees m
     ON e.`manager_id`= m.`employee_id`;
     
      #查询姓名中包含字符k的员工的名字、上级的名字
     SELECT e.last_name,m.last_name
     FROM employees e
     JOIN employees m
     ON e.`manager_id`= m.`employee_id`
     WHERE e.`last_name` LIKE '%k%';

#### 外连接 ####

应用场景：用于查询一个表中有，另一个表没有的记录

特点：  
1、外连接的查询结果为主表中的所有记录  
如果从表中有和它匹配的，则显示匹配的值  
如果从表中没有和它匹配的，则显示null  
外连接查询结果=内连接结果+主表中有而从表没有的记录  
2、左外连接，left join左边的是主表  
右外连接，right join右边的是主表  
3、左外和右外交换两个表的顺序，可以实现同样的效果  
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

##### 代码示例 #####

#引入：查询男朋友 不在男神表的的女神名
     
     SELECT * FROM beauty;
     SELECT * FROM boys;
     
     #左外连接
     SELECT b.*,bo.*
     FROM boys bo
     LEFT OUTER JOIN beauty b
     ON b.`boyfriend_id` = bo.`id`
     WHERE b.`id` IS NULL;
     
     
     #案例1：查询哪个部门没有员工
     #左外
     SELECT d.*,e.employee_id
     FROM departments d
     LEFT OUTER JOIN employees e
     ON d.`department_id` = e.`department_id`
     WHERE e.`employee_id` IS NULL;
     
     
     #右外
     
      SELECT d.*,e.employee_id
     FROM employees e
     RIGHT OUTER JOIN departments d
     ON d.`department_id` = e.`department_id`
     WHERE e.`employee_id` IS NULL;
     
     
     #全外
     
     
     USE girls;
     SELECT b.*,bo.*
     FROM beauty b
     FULL OUTER JOIN boys bo
     ON b.`boyfriend_id` = bo.id;
     
    
     #交叉连接
     
     SELECT b.*,bo.*
     FROM beauty b
     CROSS JOIN boys bo;
     
     
     
     #sql92和 sql99pk
     /* 功能：sql99支持的较多 可读性：sql99实现连接条件和筛选条件的分离，可读性较高 */
     
     
     #案例
     #一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
    
    
    SELECT b.id,b.name,bo.*
    FROM beauty b
    LEFT OUTER JOIN boys bo
    ON b.`boyfriend_id` = bo.`id`
    WHERE b.`id`>3;
    #二、查询哪个城市没有部门
    
    SELECT city
    FROM departments d
    RIGHT OUTER JOIN locations l 
    ON d.`location_id`=l.`location_id`
    WHERE  d.`department_id` IS NULL;
    
    #三、查询部门名为SAL或IT的员工信息
    
    SELECT e.*,d.department_name,d.`department_id`
    FROM departments  d
    LEFT JOIN employees e
    ON d.`department_id` = e.`department_id`
    WHERE d.`department_name` IN('SAL','IT');
    
    
    SELECT * FROM departments
    WHERE `department_name` IN('SAL','IT');

## 子查询 ##

含义：  
出现在其他语句中的select语句，称为子查询或内查询  
外部的查询语句，称为主查询或外查询

分类：  
按子查询出现的位置：  
select后面：  
仅仅支持标量子查询  
from后面：  
支持表子查询  
where或having后面：★  
标量子查询（单行） √  
列子查询 （多行） √  
行子查询

exists后面（相关子查询）  
表子查询

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

#### where或having后面 ####

/* 1、标量子查询（单行子查询） 2、列子查询（多行子查询） 3、行子查询（多列多行） 特点： ①子查询放在小括号内 ②子查询一般放在条件的右侧 ③标量子查询，一般搭配着单行操作符使用 > < >= <= = <> 列子查询，一般搭配着多行操作符使用 in、any/some、all ④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果 */
    #1.标量子查询★
    
    #案例1：谁的工资比 Abel 高?
    
    #①查询Abel的工资
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
    
    #②查询员工的信息，满足 salary>①结果
    SELECT *
    FROM employees
    WHERE salary>(
    
    	SELECT salary
    	FROM employees
    	WHERE last_name = 'Abel'
    
    );
    
    #案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
    
    #①查询141号员工的job_id
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
    
    #②查询143号员工的salary
    SELECT salary
    FROM employees
    WHERE employee_id = 143
    
    #③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
    
    SELECT last_name,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
    
    #②查询last_name,job_id和salary，要求salary=①
    SELECT last_name,job_id,salary
    FROM employees
    WHERE salary=(
    	SELECT MIN(salary)
    	FROM employees
    );
    
    
    #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
    
    #①查询50号部门的最低工资
    SELECT  MIN(salary)
    FROM employees
    WHERE department_id = 50
    
    #②查询每个部门的最低工资
    
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    
    #③ 在②基础上筛选，满足min(salary)>①
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary)>(
    	SELECT  MIN(salary)
    	FROM employees
    	WHERE department_id = 50
    
    
    );
    
    #非法使用标量子查询
    
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary)>(
    	SELECT  salary
    	FROM employees
    	WHERE department_id = 250
    
    
    );
    
    
    
    #2.列子查询（多行子查询）★
    #案例1：返回location_id是1400或1700的部门中的所有员工姓名
    
    #①查询location_id是1400或1700的部门编号
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)
    
    #②查询员工姓名，要求部门号是①列表中的某一个
    
    SELECT last_name
    FROM employees
    WHERE department_id  <>ALL(
    	SELECT DISTINCT department_id
    	FROM departments
    	WHERE location_id IN(1400,1700)
    
    
    );
    
    
    #案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
    
    #①查询job_id为‘IT_PROG’部门任一工资
    
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
    
    #②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
    SELECT last_name,employee_id,job_id,salary
    FROM employees
    WHERE salary<ANY(
    	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 MAX(salary)
    	FROM employees
    	WHERE job_id = 'IT_PROG'
    
    ) AND job_id<>'IT_PROG';
    
    
    #案例3：返回其它部门中比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';
    
    
    
    #3、行子查询（结果集一行多列或多行多列）
    
    #案例：查询员工编号最小并且工资最高的员工信息
    
    
    
    SELECT * 
    FROM employees
    WHERE (employee_id,salary)=(
    	SELECT MIN(employee_id),MAX(salary)
    	FROM employees
    );
    
    #①查询最小的员工编号
    SELECT MIN(employee_id)
    FROM employees
    
    
    #②查询最高工资
    SELECT MAX(salary)
    FROM employees
    
    
    #③查询员工信息
    SELECT *
    FROM employees
    WHERE employee_id=(
    	SELECT MIN(employee_id)
    	FROM employees
    
    
    )AND salary=(
    	SELECT MAX(salary)
    	FROM employees
    );

#### select后面 ####

/* 仅仅支持标量子查询 */
    
    #案例：查询每个部门的员工个数
    
    
    SELECT d.*,(
    
    	SELECT COUNT(*)
    	FROM employees e
    	WHERE e.department_id = d.`department_id`
     ) 个数
     FROM departments d;
     
     
     #案例2：查询员工号=102的部门名
     
    SELECT (
    	SELECT department_name,e.department_id
    	FROM departments d
    	INNER JOIN employees e
    	ON d.department_id=e.department_id
    	WHERE e.employee_id=102
    	
    ) 部门名;

#### from后面 ####

/* 将子查询结果充当一张表，要求必须起别名 */
    
    #案例：查询每个部门的平均工资的工资等级
    #①查询每个部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    
    
    SELECT * FROM job_grades;
    
    
    #②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
    
    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后面（相关子查询） ####

/* 语法： exists(完整的查询语句) 结果： 1或0 */
    
    SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
    
    #案例1：查询有员工的部门名
    
    #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`
    
    
    );
    
    
    #案例2：查询没有女朋友的男神信息
    
    #in
    
    SELECT bo.*
    FROM boys bo
    WHERE bo.id NOT IN(
    	SELECT boyfriend_id
    	FROM beauty
    )
    
    #exists
    SELECT bo.*
    FROM boys bo
    WHERE NOT EXISTS(
    	SELECT boyfriend_id
    	FROM beauty b
    	WHERE bo.`id`=b.`boyfriend_id`
    
    );

#### 案例 ####

#1. 查询和Zlotkey相同部门的员工姓名和工资
    
    #①查询Zlotkey的部门
    SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
    
    #②查询部门号=①的姓名和工资
    SELECT last_name,salary
    FROM employees
    WHERE department_id = (
    	SELECT department_id
    	FROM employees
    	WHERE last_name = 'Zlotkey'
    
    )
    
    #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
    
    #①查询平均工资
    SELECT AVG(salary)
    FROM employees
    
    #②查询工资>①的员工号，姓名和工资。
    
    SELECT last_name,employee_id,salary
    FROM employees
    WHERE salary>(
    
    	SELECT AVG(salary)
    	FROM employees
    );
    
    
    
    #3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
    #①查询各部门的平均工资
    SELECT AVG(salary),department_id
    FROM employees
    GROUP BY department_id
    
    #②连接①结果集和employees表，进行筛选
    SELECT employee_id,last_name,salary,e.department_id
    FROM employees e
    INNER JOIN (
    	SELECT AVG(salary) ag,department_id
    	FROM employees
    	GROUP BY department_id
    
    
    ) ag_dep
    ON e.department_id = ag_dep.department_id
    WHERE salary>ag_dep.ag ;
    
    
    
    #4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
    #①查询姓名中包含字母u的员工的部门
    
    SELECT  DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
    
    #②查询部门号=①中的任意一个的员工号和姓名
    SELECT last_name,employee_id
    FROM employees
    WHERE department_id IN(
    	SELECT  DISTINCT department_id
    	FROM employees
    	WHERE last_name LIKE '%u%'
    );
    
    
    #5. 查询在部门的location_id为1700的部门工作的员工的员工号
    
    #①查询location_id为1700的部门
    
    SELECT DISTINCT department_id
    FROM departments 
    WHERE location_id  = 1700
    
    
    #②查询部门号=①中的任意一个的员工号
    SELECT employee_id
    FROM employees
    WHERE department_id =ANY(
    	SELECT DISTINCT department_id
    	FROM departments 
    	WHERE location_id  = 1700
    
    );
    #6.查询管理者是King的员工姓名和工资
    
    #①查询姓名为king的员工编号
    SELECT employee_id
    FROM employees
    WHERE last_name  = 'K_ing'
    
    #②查询哪个员工的manager_id = ①
    SELECT last_name,salary
    FROM employees
    WHERE manager_id IN(
    	SELECT employee_id
    	FROM employees
    	WHERE last_name  = 'K_ing'
    
    );
    
    #7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
    
    
    #①查询最高工资
    SELECT MAX(salary)
    FROM employees
    
    #②查询工资=①的姓.名
    
    SELECT CONCAT(first_name,last_name) "姓.名"
    FROM employees
    WHERE salary=(
    	SELECT MAX(salary)
    	FROM employees
    
    );

## 分页查询 ##

### 概述 ###

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求  
语法：  
select 查询列表  
from 表  
【join type join 表2  
on 连接条件  
where 筛选条件  
group by 分组字段  
having 分组后的筛选  
order by 排序的字段】  
limit 【offset,】size;  
offset要显示条目的起始索引（起始索引从0开始）  
size 要显示的条目个数

特点：  
①limit语句放在查询语句的最后  
②公式  
要显示的页数 page，每页的条目数size  
select 查询列表  
from 表  
limit (page-1)\*size,size;

size=10  
 page  
 1 0  
 2 10  
 3 20

### 代码案例 ###

#案例1：查询前五条员工信息
    
    
    SELECT * FROM  employees LIMIT 0,5;
    SELECT * FROM  employees LIMIT 5;
    
    
    #案例2：查询第11条——第25条
    SELECT * FROM  employees LIMIT 10,15;
    
    
    #案例3：有奖金的员工信息，并且工资较高的前10名显示出来
    SELECT 
        * 
    FROM
        employees 
    WHERE commission_pct IS NOT NULL 
    ORDER BY salary DESC 
    LIMIT 10 ;

## 联合查询 ##

### 概述 ###

union 联合 合并：将多条查询语句的结果合并成一个结果

语法：  
查询语句1  
union  
查询语句2  
union  
…

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

特点：★  
1、要求多条查询语句的查询列数是一致的！  
2、要求多条查询语句的查询的每一列的类型和顺序最好一致  
3、union关键字默认去重，如果使用union all 可以包含重复项

### 代码演示 ###

#引入的案例：查询部门编号>90或邮箱包含a的员工信息
    
    SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;;
    
    SELECT * FROM employees  WHERE email LIKE '%a%'
    UNION
    SELECT * FROM employees  WHERE department_id>90;
    
    
    #案例：查询中国用户中男性的信息以及外国用户中年男性的用户信息
    
    SELECT id,cname FROM t_ca WHERE csex='男'
    UNION ALL
    SELECT t_id,tname FROM t_ua WHERE tGender='male';

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NDA4Mzkw_size_16_color_FFFFFF_t_70]: /images/20210516/4958e7e5afdc48ceab57fe169bc22c83.png