MySql-进阶查询

HFwas约 1739 字大约 6 分钟

MySql-进阶查询

子查询

说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询外面的select语句称为主查询或外查询。
分类：
- 按子查询出现的位置进行分类：
  - select后面要求：子查询的结果为单行单列（标量子查询）
  - from后面要求：子查询的结果可以为多行多列
  - where或having后面 ★ 要求：子查询的结果必须为单列单行子查询多行子查询
  - exists后面要求：子查询结果必须为单列（相关子查询）
- 特点：
  - 子查询放在条件中，要求必须放在条件的右侧
  - 子查询一般放在小括号中
  - 子查询的执行优先于主查询
  - 单行子查询对应了单行操作符：> < >= <= = <>
  - 多行子查询对应了多行操作符：any/some all in

基本用法

谁的工资比 Abel 高?

查询Abel的工资

SELECT salary
FROM employees
WHERE last_name  = 'Abel'

查询salary>①的员工信息

SELECT last_name,salary
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name  <> 'Abel'

);

返回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=① and 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

);

返回公司工资最少的员工的last_name,job_id和salary

查询最低工资

SELECT MIN(salary)
FROM employees

查询salary=①的员工的last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

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

查询50号部门的最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id = 50

查询各部门的最低工资，筛选看哪个部门的最低工资>①

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(

    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);

多行子查询

介绍

in:判断某字段是否在指定列表内
- x in(10,30,50)
any/some:判断某字段的值是否满足其中任意一个
- 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()

基本用法

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

查询location_id是1400或1700的部门

SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)

查询department_id = ①的姓名

SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)
);

返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

查询job_id为‘IT_PROG’部门的工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

查询其他部门的工资<任意一个①的结果

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

等价于：

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
);

返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

查询job_id为‘IT_PROG’部门的工资

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

查询其他部门的工资<所有①的结果

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

等驾驭

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(

    SELECT MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'


);

放在select后面

查询部门编号是50的员工个数

SELECT 
(
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 50
)  个数;

放在from后面

查询每个部门的平均工资的工资级别

查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

将①和sal_grade两表连接查询

SELECT dep_ag.department_id,dep_ag.ag,g.grade
FROM sal_grade g
JOIN (

    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id

) dep_ag ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

放在exists后面

查询有无名字叫“张三丰”的员工信息

SELECT EXISTS(
    SELECT * 
    FROM employees
    WHERE last_name = 'Abel'
) 有无Abel;

查询没有女朋友的男神信息

USE girls;

SELECT bo.*
FROM boys bo
WHERE bo.`id` NOT IN(
    SELECT boyfriend_id
    FROM beauty b
)

SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty b
    WHERE bo.id = b.boyfriend_id
);

查询和 Zlotkey 相同部门的员工姓名和工资

查询Zlotkey的部门编号

SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'

查询department_id = ①的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
);

查询工资比公司平均工资高的员工的员工号，姓名和工资。

查询平均工资

SELECT AVG(salary)
FROM employees

查询salary>①的信息

SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
    SELECT AVG(salary)
    FROM employees
);


## 分页查询

- 应用场景
  - 当页面上的数据，一页显示不全，则需要分页显示
  - 分页查询的sql命令请求数据库服务器——>服务器响应查询到的多条数据——>前台页面
- 语法
  - select 查询列表
  - from 表1 别名
  - join 表2 别名
  - on 连接条件
  - where 筛选条件
  - group by 分组
  - having 分组后筛选
  - order by 排序列表
  - limit 起始条目索引,显示的条目数
- 执行顺序：
  - 1》from子句
  - 2》join子句
  - 3》on子句
  - 4》where子句
  - 5》group by子句
  - 6》having子句
  - 7》select子句
  - 8》order by子句
  - 9》limit子句
- 特点：
  - ①起始条目索引如果不写，默认是0
  - ②limit后面支持两个参数
  - 参数1：显示的起始条目索引
  - 参数2：条目数
- 公式：
  - 假如要显示的页数是page，每页显示的条目数为size
  - select *. from employees     limit (page-1)size,size;

### 示例

- 查询员工信息表的前5条

```sql
SELECT * FROM employees LIMIT 0,5;
#完全等价于
SELECT * FROM employees LIMIT 5;

查询有奖金的，且工资较高的第11名到第20名

SELECT 
    * 
FROM
    employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10,10 ;

查询年薪最高的前10名

SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC
LIMIT 0,10;

联合查询

说明
- 当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询
语法：
- select 查询列表 from 表1 where 筛选条件 union select 查询列表 from 表2 where 筛选条件
特点：
- 多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
- union实现去重查询，union all 实现全部查询，包含重复项

示例

查询所有国家的年龄>20岁的用户信息

SELECT * FROM usa WHERE uage >20 UNION
SELECT * FROM chinese WHERE age >20 ;

查询所有国家的用户姓名和年龄

SELECT uname,uage FROM usa
UNION
SELECT age,`name` FROM chinese;

union自动去重/union all 可以支持重复项

SELECT 1,'范冰冰' 
UNION ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' ;

昵称

邮箱(可选)

按正序
按倒序
按热度

MySql-进阶查询

预览: