【Mysql】记录一些特殊的select语句

一时失言乱红尘 2023-09-23 17:54 82阅读 0赞

#### 目录 ####

*   *  一.分页查询
     *  二.多表查询
     *  三.自连接
     *  四.子查询
     *   *  4.1单行子查询
         *  4.2多行子查询
         *  4.3临时表子查询
         *  4.4多行子查询
         *  4.5多列子查询
     *  企业开发中遇到的复杂SQL

### 一.分页查询 ###

`select...limit start,rows`  
表示从start+1行开始取，取出rows行，start从0开始算  
公式：limit 每页显示记录数\*（第几页-1），每页显示记录数

SELECT
    	* 
    FROM
    	emp 
    WHERE
    	deptno = 20 
    	LIMIT 0,4

### 二.多表查询 ###

多表查询的条件不能少于表的个数-1, 否则会出现笛卡尔集  
![在这里插入图片描述][76325d29cc80488cbe68547b165e9053.png]  
查询一：显示雇员名,雇员工资及所在部门的名字  
雇员名,雇员工资 来自 emp 表  
部门的名字 来自 dept 表

SELECT
    	ename,
    	sal,
    	dname,
    	emp.deptno 
    FROM
    	emp,
    	dept 
    WHERE
    	emp.deptno = dept.deptno

查询二：显示部门号为 10 的部门名、员工名和工资

SELECT
    	ename,
    	sal,
    	dname,
    	emp.deptno 
    FROM
    	emp,
    	dept 
    WHERE
    	emp.deptno = dept.deptno 
    	AND emp.deptno = 10

查询三：显示各个员工的姓名，工资，及其工资的级别  
姓名，工资 来自 emp  
工资级别 salgrade

SELECT
    	ename,
    	sal,
    	grade 
    FROM
    	emp,
    	salgrade 
    WHERE
    	sal BETWEEN losal 
    	AND hisal;

### 三.自连接 ###

在同一张表的连接查询（元素之间通过表中的某一列关联，把一张表都当做两张表来使用  
），在进行查询时一般要起别名！  
![在这里插入图片描述][dc04f7485c6949c298cabba1f7321e1b.png]  
查询一：显示公司员工名字和他的上级的名字  
员工名字 在 emp, 上级的名字的名字 emp  
员工和上级是通过 emp 表的 mgr 列关联

SELECT
    	worker.ename AS '职员名',
    	boss.ename AS '上级名' 
    FROM
    	emp worker,
    	emp boss 
    WHERE
    	worker.mgr = boss.empno;

在自连接的基础上一般进行条件筛查，完成查询  
查询二：  
![在这里插入图片描述][1d230408cc22443698baf49e65106751.png]  
像这样的自连接场景也可以使用join关键字来实现，本质就是将两张表通过列来连接起来

select a.name AS 'Employee' 
    from 
    Employee AS a,
    Employee AS b 
    where 
    a.managerId=b.id 
    AND 
    a.salary>b.salary
    # 使用join关键字实现
    select a.name AS 'Employee' 
    from 
    Employee AS a join Employee AS b 
    where 
    a.managerId=b.id 
    AND 
    a.salary > b.salary

### 四.子查询 ###

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询

#### 4.1单行子查询 ####

单行子查询是指只返回一行数据的子查询语句  
查询一:如何显示与SMITH同一部门的所有员工?  
先查出Smith的部门号，再根据查到的部门号来查询其他信息

SELECT DISTINCT
    	ename,
    	job,
    	sal,
    	deptno 
    FROM
    	emp 
    WHERE
    	deptno IN (
    	SELECT
    		deptno 
    	FROM
    		emp 
    WHERE
    	ename = 'SMITH')

#### 4.2多行子查询 ####

多行子查询指返回多行数据的子查询使用关键字in  
查询二：查询和10号部门工作相同的员工，但不包括10号部门  
查询10号部门有哪些工作，再把查询结果当作子查询使用

SELECT
    	ename,
    	deptno 
    FROM
    	emp 
    WHERE
    	job IN 
    	( SELECT 
    	job 
    	FROM 
    	emp 
    	WHERE 
    	deptno = 10 ) 
    	AND deptno != 10

#### 4.3临时表子查询 ####

将子查询当作一张临时表来使用  
查询一：查询表中各个类别里，价格最高的商品  
先得到各个类别中，价格最高的商品当作临时表，在针对临时表查询其他字段  
![在这里插入图片描述][697d892452b2499484ce10e9ef8f457a.png]

SELECT
    	goods_id,
    	ecs_goods.cat_id,
    	goods_name,
    	shop_price 
    FROM
    	( SELECT cat_id, MAX( shop_price ) 
    	AS max_price 
    	FROM ecs_goods 
    	GROUP BY cat_id ) 
    	temp,ecs_goods 
    WHERE
    	temp.cat_id = ecs_goods.cat_id 
    	AND temp.max_price = ecs_goods.shop_price

注意针对临时表和原始表共存的字段要做声明，比如使用ecs\_goods.cat\_id来作为字段名  
查询二：  
查询每个部门的信息（部门名，编号，地址）和人数  
![在这里插入图片描述][c28c9c72af2040b188a91a147e1575e9.png]

SELECT
    	COUNT(*) AS per_num,
    	deptno 
    FROM
    	emp 
    GROUP BY
    	deptno 
    	--临时表
    
    SELECT
    	dname,
    	dept.deptno,
    	loc,
    	per_num 
    FROM
    	dept,(
    	SELECT
    		COUNT(*) AS per_num,
    		deptno 
    	FROM
    		emp 
    	GROUP BY
    		deptno 
    	) temp 
    WHERE
    	dept.deptno = temp.deptno

#### 4.4多行子查询 ####

查询一：查询工资比30号部门的所有员工工资高的员工的姓名、工资和部门号  
使用all操作符实现  
还可以使用MAX（）

SELECT
    	ename,
    	sal,
    	deptno 
    FROM
    	emp 
    WHERE
    	sal > ALL (
    	SELECT
    		sal 
    	FROM
    		emp 
    	WHERE
    	deptno = 30)

查询二：查询工资比30号部门其中一个员工工资高的员工的姓名、工资、部门编号  
使用ANY操作符实现  
也可以使用MIN（）

SELECT
    	ename,
    	sal,
    	deptno 
    FROM
    	emp 
    WHERE
    	sal > ANY ( 
    	SELECT 
    	sal 
    	FROM 
    	emp 
    	WHERE 
    	deptno = 30 )

#### 4.5多列子查询 ####

（字段1，字段2…）=（select 字段1，字段2 from…）  
查询一：查询和Allen部门和岗位相同的元素

SELECT * FROM emp 
    WHERE 
    ( deptno, job )=( 
    SELECT deptno, job 
    FROM emp 
    WHERE ename = 'ALLEN' 
    ) 
    AND ename != 'ALLEN'

### 企业开发中遇到的复杂SQL ###

分组统计的场景

SELECT
    	t1.publisher_id,
    	t2.charging_pile_name,
    	t2.charging_pile_id,
    	t1.max_count,
    	t2.create_time 
    FROM
    	(
    	SELECT
    		publisher_id,
    		MAX( count ) AS max_count 
    	FROM
    		(
    		SELECT
    			publisher_id,
    			charging_pile_id,
    			COUNT(*) AS count 
    		FROM
    			task_item 
    		WHERE
    			type = 3 
    			AND publisher_id IN (423, 7097, 6119, 17509, 6025, 423, 7097, 6119, 17509, 6025) 
    		GROUP BY
    			charging_pile_id,
    			publisher_id 
    		) AS subquery 
    	GROUP BY
    		publisher_id 
    	) AS t1
    	JOIN (
    	SELECT
    		publisher_id,
    		charging_pile_name,
    		charging_pile_id,
    		COUNT(*) AS count,
    		MAX( create_time ) AS create_time 
    	FROM
    		task_item 
    	WHERE
    		type = 3 
    		AND publisher_id IN (423, 7097, 6119, 17509, 6025, 423, 7097, 6119, 17509, 6025) 
    	GROUP BY
    		publisher_id,
    		charging_pile_id 
    	) AS t2 ON t1.publisher_id = t2.publisher_id 
    	AND t1.max_count = t2.count 
    	AND t2.charging_pile_id IS NOT NULL 
    ORDER BY
    	t1.publisher_id ASC,
    	t2.create_time DESC

2.当聚合函数里的字段为where子句条件字段时，要用子表来解耦，把查询该字段的地点与聚合函数抽离开

SELECT *  -- 通过临时表来判断状态字段，从而达到不影响聚合函数使用的目的
    FROM (
        SELECT  -- 拿到原生的聚合函数统计的数据，用if来通过子表的数据判断分组整体的状态
            id,
            building_id,
            unit_id,
            room_id,
            total_unpaid_amount,
            building_name,
            unit_name,
            room_name,
            bill_start_time,
            total_unpaid_bills,
            total_unRemind_bills,
            IF(total_unpaid_bills > 0, 0, 1) AS STATUS,
            IF(total_unRemind_bills > 0, 1, 0) AS remind_status
        FROM
            (
                SELECT -- 统计精确分组下的总金额，此时total_unpaid_amount还没有状态过滤，聚合函数中的判断字段没有受影响
                    id,
                    building_id,
                    unit_id,
                    room_id,
                    SUM(total_money) AS total_unpaid_amount,
                    building_name,
                    unit_name,
                    room_name,
                    bill_start_time,
                    SUM(CASE WHEN `status` = 0 THEN 1 ELSE 0 END) AS total_unpaid_bills,
                    SUM(CASE WHEN `remind_status` = 1 THEN 1 ELSE 0 END) AS total_unRemind_bills
                FROM
                    payment_bill
                WHERE
                    1 = 1 
                    AND community_id = 2 
                    AND type = 0 
                    AND room_id IN (13, 14, 15, 16) 
                    AND building_id IN (4) 
                    AND unit_id IN (7, 8)
                GROUP BY
                    building_id,
                    unit_id,
                    room_id
            ) AS subquery
    ) AS result
    WHERE STATUS = 0; -- 这里是 status 字段的条件查询，筛选出 STATUS 值为 1 的结果

[76325d29cc80488cbe68547b165e9053.png]: https://img-blog.csdnimg.cn/76325d29cc80488cbe68547b165e9053.png
[dc04f7485c6949c298cabba1f7321e1b.png]: https://img-blog.csdnimg.cn/dc04f7485c6949c298cabba1f7321e1b.png
[1d230408cc22443698baf49e65106751.png]: https://img-blog.csdnimg.cn/1d230408cc22443698baf49e65106751.png
[697d892452b2499484ce10e9ef8f457a.png]: https://img-blog.csdnimg.cn/697d892452b2499484ce10e9ef8f457a.png
[c28c9c72af2040b188a91a147e1575e9.png]: https://img-blog.csdnimg.cn/c28c9c72af2040b188a91a147e1575e9.png