MySQL高级查询

MySQL

前言

文本源自微博客 (www.microblog.store),且已获授权.

一. mysql基础知识

1. mysql常用系统命令

• 启动命令 net start mysql
• 停止命令 net stop mysql
• 登录命令 mysql -h ip -P 端口 -u 用户名 -p

​ 本机可以省略 ip mysql -u 用户名 -p

• 查看数据库版本 mysql --version / select version();(已登录)
• 显示所有数据库 show databases;
  • 进⼊指定的库 use 库名;
  • 显示当前库中所有表 show tables;
  • 查看其他库中所有的表 show tables from 库名
  • 查看表的创建语句 show create table 表名;
  • 查看表结构 desc 表名;
  • 查看当前所在库 select database();
• 查看当前mysql支持的存储引擎 show engines;
• 查看系统变量及其值 show variables;
• 查看某个系统变量 show VARIABLES like '变量名';

2. mysql语法规范

1. 不区分大小写,但建议关键字大写,表明、列明小写
2. 每条语句最好用英文分号结尾
3. 每条命令根据需要，可以进行缩进或换行
4. 注释
   • 单行注释： #注释文字
   • 单行注释： – 注释文字，注意这里需要加空格
   • 多行注释 /*注释文字*/

3. SQL的语言分类

• DQL（Data Query Language）：数据查询语⾔ select 相关语句
• DML（Data Manipulate Language）：数据操作语⾔ insert 、update、delete 语句
• DDL（Data Define Language）：数据定义语⾔ create、drop、alter 语句
• TCL（Transaction Control Language）：事务控制语⾔ set autocommit=0、start
  transaction、savepoint、commit、rollback

二. MySQL中数据类型介绍

1. MySQL的数据类型

主要包括以下五大类

• 整形数据： bit、bool、tinyint、smallint、mediumint、int、bigint
• **浮点数类型：**float、double、decimal、decimal
• **字符串类型：**char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext
• **日期类型：**Date、DateTime、TimeStamp、Time、Year
• **其他数据类型：**xxxx

2. 数据类型选择的一些建议

• **选小不选大：**⼀般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通
  常更快，占用磁盘，内存和CPU缓存更小。
• **简单就好：**简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作
  代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
• **尽量避免NULL：**尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的
  列值会使得索引、索引统计和值比较更加复杂。
• 浮点类型的建议统⼀选择decimal
• 记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为
  秒、毫秒，进行存储，方便走索引

九. 分组查询（group by、having）

1. 前期准备

1. 语法

   SELECT column, group_function,... FROM table
   [WHERE condition]
   GROUP BY group_by_expression
   [HAVING group_condition];
   

2. 说明

   group_function：聚合函数。
   group_by_expression：分组表达式，多个之间用逗号隔开。
   group_condition：分组之后对数据进行过滤。

   分组中，select后面只能有两种类型的列：

3. 分组中，select后面只能有两种类型的列

4. 出现在group by后的列

5. 或者使用聚合函数的列

6. 聚合函数

   函数名称	作用
   max	查询指定列的最大值
   min	查询指定列的最小值
   count
   sum
   avg

   分组时，可以使用使用上面的聚合函数。

2. 查询

1. 单字段分组

   查询每个用户下单数量，输出：用户id、下单数量

   SELECTuser_id,COUNT( user_id ) 
   FROMt_order 
   GROUP BYuser_id
   

2. 查询每个用户每年下单数量，输出字段：用户id、年份、下单数量，如下

   SELECTuser_id,the_year,COUNT( user_id ) 
   FROMt_order 
   GROUP BYuser_id,the_year;
   

3. 分组前筛选数据

   分组前对数据进行筛选，使用where关键字

   需要查询2018年每个用户下单数量，输出：用户id、下单数量

   
   SELECTuser_id,COUNT( user_id ) 
   FROMt_order WHERE the_year = 2018
   GROUP BYuser_id;
   

4. 分组后筛选数据

   分组后对数据筛选，使用having关键字

   查询2018年订单数量大于1的用户，输出：用户id，下单数量

   SELECTuser_id,COUNT( user_id ) AS total
   FROMt_order WHERE the_year = 2018
   GROUP BYuser_idHAVING total>1;
   

5. where和having的区别

   where是在分组（聚合）前对记录进行筛选，而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果。
   可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后面的聚集函数相同。

十二. 子查询

子查询:

​ 出现在select语句中的select语句，称为子查询或内查询。

​ 外部的select查询语句，称为主查询或外查询。

1. 子查询分类

• 按结果集的行列数不同分为4种
  • 标量子查询（结果集只有⼀行⼀列）
  • 列子查询（结果集只有⼀列多行）
  • 行子查询（结果集有⼀行多列）
  • 表子查询（结果集⼀般为多行多列）
• 按子查询出现在主查询中的不同位置分
  • select后面：仅仅支持标量子查询。
  • from后面：支持表子查询。
  • where或having后面：支持标量子查询（单列单行）、列子查询（单列多行）、行子
    查询（多列多行）
  • exists后面（即相关子查询）：表子查询（多行、多列)

​ (数据准备:javacode2018_employees.sql)

2. select后面的子查询

子查询位于select后⾯的，仅仅支持标量子查询。

• 实例1 查询每个部门员工个数

  -- 查询每个部门员工个数
  SELECTa.*,( SELECT count(*) FROM employees b WHERE b.department_id = a.department_id ) AS 员工个数 
  FROMdepartments a;
  

• 查询员工号=102的部门名称

  -- 菜鸟版本
  /*SELECT* 
  FROMdepartments dep 
  WHEREdep.department_id = ( SELECT emp.department_id FROM employees emp WHERE emp.employee_id = 102 );*/ 
  -- 高级版SELECT
  ( SELECT a.department_name FROM departments a, employees b WHERE a.department_id = b.department_id AND b.employee_id = 102 ) AS 部门名;
  

3. from后面的子查询

将子查询的结果集充当⼀张表，要求必须起别名，否者这个表找不到。
然后将真实的表和子查询结果表进行连接查询。

• 查询每个部门的平均工资

  SELECTemp.department_id,avg( emp.salary ) 
  FROMemployees AS emp 
  GROUP BYemp.department_id;
  

• 薪资等级表

  select * from job_grades;
  

• 联合查询: 将上面2个结果连接查询,筛选条件: 平均工资 between lowest_sal and highest_sal

  SELECTemp.department_id,emp.department_name,sa AS '平均工资',job.grade_level 
  FROM( SELECTemp.department_id,dep.department_name,avg( emp.salary ) AS saFROMemployees AS emp ,departments AS dep where  dep.department_id = emp.department_idGROUP BYemp.department_id) emp,job_grades job 
  WHEREemp.sa BETWEEN job.lowest_sal AND job.highest_sal;
  

4. where和having后面的子查询

1. where和having使用情况

   • 标量子查询(单行单列子查询)

   • 列子查询(单行多列子查询)

   • 行子查询(一行多列子查询)

2. 特点

• 子查询放在小括号内。

• 子查询⼀般放在条件的右侧。

• 标子查询，⼀般搭配着单行单列操作符使用 >、<、>=、<=、=、<>、!=

• 列子查询，⼀般搭配着多行操作符使用

• in(not in)：列表中的“任意⼀个”

  • any或者some：和子查询返回的“某⼀个值”比较，比如a>some(10,20,30)，a大于
    子查询中任意⼀个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。
  • all：和子查询返回的“所有值”比较，比如a>all(10,20,30)，大于子查询中所有
    值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30);

• 子的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。

3. mysql中的in、any、some、all

   in，any，some，all分别是子查询关键词之⼀。

• in：in常用于where表达式中，其作用是查询某个范围内的数据
• any和some⼀样： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、
  大于等于、小于、小于等于、不等于其中的任何⼀个数据。
• all：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小
  于、小于等于、不等于其中的其中的所有数据。

1. 标量子查询

1. ⼀般标量子查询

查询谁的工资比Abel的⾼？

1. 查询abel的工资【改查询是标量子查询】

    SELECTemp.salary FROMemployees AS emp WHEREemp.last_name = 'Abel'
   

2. 查询员工信息，满足salary>①的结果

    SELECT* FROMemployees emp WHEREemp.salary > ( SELECT emp.salary FROM employees AS emp WHERE emp.last_name = 'Abel' );
   

2. 多个标量子查询

返回jobid与141号员工相同，salary比143号员工多的员工、姓名、jobid和工资

1. 查询141号员工的job_id

   -- 1. 查询141号员工的job_idSELECTemp.job_id 
   FROMemployees emp 
   WHEREemp.employee_id = 141;
   

2. 查询143号员工的salary

   SELECTemp.salary 
   FROMemployees emp 
   WHEREemp.employee_id = 143;
   

3. 查询员工的姓名、job_id、工资，要求job_id=① and salary>②

   SELECT* 
   FROMemployees emp 
   WHEREemp.job_id = ( SELECT emp.job_id FROM employees emp WHERE emp.employee_id = 141 ) AND emp.salary > ( SELECT emp.salary FROM employees emp WHERE emp.employee_id = 143 );
   

3. 子查询+分组函数

查询最低工资大于50号部门最低工资的部门id和其最低工资【having】

1. 查询50号部门的最低工资

   SELECTmin( salary ) 
   FROMemployees 
   WHEREdepartment_id = 50;
   

2. 查询每个部门的最低工资

   SELECTmin( salary ) 
   FROMemployees 
   GROUP BYdepartment_id;
   

3. 在②的基础上筛选，满足min(salary)>①

   	
   SELECTemp.department_id,min( emp.salary ) minSalary 
   FROMemployees emp 
   GROUP BYemp.department_id 
   HAVINGMIN( emp.salary )>(SELECTmin( salary ) FROMemployees WHEREdepartment_id = 50 );

2. 列子查询(子查询结果集⼀列多行)

列子查询需要搭配多行操作符使用：in(not in)、any/some、all。
为了提升效率，最好去重⼀下distinct关键字。

实例1

返回location_id是1400或1700的部门中的所有员工姓名:

1. 方式一:

   a. 查询location_id是1400或1700的部门编号

   select DISTINCT department_id from departments WHERE location_id in(1400,1700);
   

   b. 查询员工姓名，要求部门是①列表中的某⼀个

   SELECTlast_name 
   FROMemployees 
   WHEREdepartment_id IN ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );
   

2. 方式二:使用any实现

   SELECTlast_name 
   FROMemployees 
   WHEREdepartment_id = ANY ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );
   

3. 拓展，下面与not in等价

   SELECTlast_name 
   FROMemployees 
   WHEREdepartment_id <>ALL ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );
   

实例2

返回其他工种中比jobid为’ITPROG’工种任意工资低的员工的员工号、姓名、job_id、salary

1. 查询job_id为’IT_PROG’部门任-工资

   SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' 
   

2. 查询员⼯号、姓名、job_id、salary，slary<①的任意⼀个

   SELECT* 
   FROMemployees 
   WHEREsalary < ANY ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id != 'IT_PROG';
   

3. 或者

   SELECT* 
   FROMemployees 
   WHEREsalary < ( SELECT MAX(salary ) FROM employees WHERE job_id = 'IT_PROG' ) AND job_id != 'IT_PROG';
   

实例3

返回其他工种中比jobid为’ITPROG’部门所有⼯资低的员⼯的员⼯号、姓名、
job_id、salary

1. 使用ALL实现:

   SELECTemp.employee_id,emp.first_name,emp.job_id,emp.salary 
   FROMemployees emp 
   WHEREemp.salary < ALL ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND emp.job_id != 'IT_PROG';
   

2. 使用min:

   	SELECTemp.employee_id,emp.first_name,emp.job_id,emp.salary 
   FROMemployees emp 
   WHEREemp.salary < ( SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG' ) AND emp.job_id != 'IT_PROG';
   

3. 行子查询(子查询结果集一行多列)

查询员工编号最小并且工资最高的员工信息，3种方式:

a. 查询最⼩的员⼯编号

SELECTmin( employee_id ) 
FROMemployees;

b. 查询最⾼⼯资

SELECTMAX( salary ) 
FROMemployees;

方式1 分开使用查询条件

SELECT* 
FROMemployees 
WHEREemployee_id = ( SELECT min( employee_id ) FROM employees ) AND salary = ( SELECT MAX( salary ) FROM employees );

方式2 使用联合查询条件

SELECT* 
FROMemployees 
WHERE( employee_id, salary ) = (SELECTmin( employee_id ),MAX( salary ) FROMemployees )

方式3 使用联合查询条件

SELECT* 
FROMemployees 
WHERE( employee_id, salary ) IN (SELECTmin( employee_id ),MAX( salary ) FROMemployees );

4. exists后面（也叫做相关子查询)

1. 语法：exists(完整的查询语句)。
2. exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。
3. ⼀般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。
4. 和前面的查询不同，这先执行主查询，然后主查询查询的结果，再根据子查询进行过
   滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。

实例1 简单实例

SELECT EXISTS( SELECT employee_id FROM employees WHERE salary = 300000 ) AS 'exists返回1或者0';

+----------------------+
| exists返回1或者0 |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)

实例2 查询所有员⼯的部门名称

1. 查询所有员⼯的部门名称

   SELECTa.department_name 
   FROMdepartments a 
   WHEREEXISTS (SELECT1 FROMemployees b WHEREa.department_id = b.department_id);
   

2. 使用in实现

   SELECTa.department_name 
   FROMdepartments a 
   WHEREa.department_id IN ( SELECT department_id FROM employees );
   

实例3 查询所有员⼯的部门名称

1. exists实现

   SELECTa.department_name 
   FROMdepartments a 
   WHERENOT EXISTS ( SELECT 1 FROM employees b WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL );
   

2. in 实现

   SELECTa.department_name 
   FROMdepartments a 
   WHEREa.department_id NOT IN ( SELECT 1 FROM employees b WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL );