常用的SQL例子这是一些常用的SQL例子，不同类型的SQL都列举了一下，方便自己忘记时查看😊子查询、窗口函数等使用

这是一些常用的SQL例子，不同类型的SQL都列举了一下，方便自己忘记时查看😊

准备SQL表：

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `product_id` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `product_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `product_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sale_price` int(0) NULL DEFAULT NULL,
  `purchase_price` int(0) NULL DEFAULT NULL,
  `regist_date` date NULL DEFAULT NULL,
  PRIMARY KEY (`product_id`) USING BTREE
);

INSERT INTO `product` VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO `product` VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO `product` VALUES ('0003', '运动T恤', '衣服', 4000, 2800, '2009-09-11');
INSERT INTO `product` VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO `product` VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO `product` VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO `product` VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2009-01-15');
INSERT INTO `product` VALUES ('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

DROP TABLE IF EXISTS `my_course`;
CREATE TABLE `my_course`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `course` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
insert into my_course VALUES (1,'张三','语文',74);
insert into my_course VALUES (2,'张三','数学',83);
insert into my_course VALUES (3,'张三','英语',93);
insert into my_course VALUES (4,'李四','语文',74);
insert into my_course VALUES (5,'李四','数学',84);
insert into my_course VALUES (6,'李四','英语',94);

DROP TABLE IF EXISTS `my_city`;
CREATE TABLE `my_city`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `my_city` VALUES (1, '张三', '北京', '上海', '广州');
INSERT INTO `my_city` VALUES (2, '李四', '纽约', '东京', '首尔');

全部科目及格(>=60)的学生:

select name,min(score) from my_course
 GROUP BY name
 HAVING min(score) >=60;

全部科目平均分>=80的学生:

select name,avg(score) from my_course
 GROUP BY name
 HAVING avg(score) >=80;

查询重复的数据：

select * from my_tb tb where EXISTS
( SELECT 1 FROM 
    (select min(id) as mid from my_tb 
		group by name,course,score) as min_ids
 	WHERE tb.id=min_ids.mid
);

查询商品的每月上架的数量：

select month(regist_date),count(*) from product group by month(regist_date);

查询商品的价格区间的数量

select scope,count(*) 
from  (select  
				CASE 
					WHEN sale_price <1000
						THEN '价格[0-1000)'
					WHEN sale_price <5000
						THEN '价格[1000-5000)'
					WHEN sale_price>=5000
						THEN '价格[5000+)'
					ELSE '[未知]'
				END 'scope'
				from product
			) tmp
group by scope ;

连接查询

CROSS JOIN：

-- CROSS JOIN
select * FROM
join_student s
CROSS join 
join_school sc
order by id;
打印：
id	name	school_id	sh_id	sh_name
1001	张三	1	3	中心学校
1001	张三	1	2	南方学校
1001	张三	1	1	北方学校
1002	李四	2	3	中心学校
1002	李四	2	2	南方学校
1002	李四	2	1	北方学校
1003	王五	2	3	中心学校
1003	王五	2	2	南方学校
1003	王五	2	1	北方学校
1005	赵六	99	3	中心学校
1005	赵六	99	2	南方学校
1005	赵六	99	1	北方学校

-- CROSS JOIN，带条件时，等价于inner join
select * FROM
join_student s
CROSS join 
join_school sc
on s.school_id=sc.sh_id
order by id;
打印：
id	name	school_id	sh_id	sh_name
1001	张三	1	1	北方学校
1002	李四	2	2	南方学校
1003	王五	2	2	南方学校

INNER JOIN：

-- INNER JOIN
select * FROM
join_student s
inner join 
join_school sc
on s.school_id=sc.sh_id
order by id;
打印：
id	name	school_id	sh_id	sh_name
1001	张三	1	1	北方学校
1002	李四	2	2	南方学校
1003	王五	2	2	南方学校

图片.png 加外键索引

LEFT JOIN：

-- LEFT JOIN
select * FROM
join_student s
LEFT join 
join_school sc
on s.school_id=sc.sh_id
order by id;
打印：
id	    name	school_id	sh_id	sh_name
id	name	school_id	sh_id	sh_name
1001	张三	1	1	北方学校
1002	李四	2	2	南方学校
1003	王五	2	2	南方学校
1005	赵六	99	null	null

图片.png

RIGHT JOIN：

-- RIGHT JOIN
select * FROM
join_student s
RIGHT join 
join_school sc
on s.school_id=sc.sh_id
order by id;
打印：
id		name	school_id	sh_id	sh_name
null	null	null	3	 中心学校
1001	张三  	1		1	北方学校
1002	李四		2		2	南方学校
1003	王五		2		2	南方学校

子查询

子查询：（select引发的子查询）

SELECT name,(select city.city_name from city WHERE city.id=addr_id) addr_name FROM person; -- 查询person的name和addr_name

WHERE 引发的子查询：（子句结果当条件）

    SELECT * FROM product WHERE sale_price>(SELECT AVG(sale_price) FROM product); -- 查询sale_price大于平均price的product。

    SELECT * FROM product WHERE sale_price IN (SELECT MAX(sale_price) FROM product GROUP BY product_type); -- 查询每个类别中price最大的product。

EXISTS引发的子查询：（将主句的结果拿到子句中比较，返回符合的记录）

    SELECT * FROM product WHERE EXISTS (SELECT MAX(sale_price) max_price FROM product GROUP BY product_type HAVING sale_price=max_price ); -- 查询每个类别中price最大的product。

FROM引发的子查询：（将子句结果当临时表）

    SELECT * FROM (SELECT * FROM product) tp GROUP BY product_type;

Update子查询：

update product 
set stock=(
	select quantity from store where product_id=product.id 
);

WITH AS

递归查询 name

with recursive name_path (id, name, path) as (
    select id, name, cast(name as char(200))
    from employees_mgr
    where manager_id=0
    union all
    select e.id, e.name, concat(ep.path, '/', e.name)
    from name_path as ep
    inner join employees_mgr as e
    on ep.id = e.manager_id
    )
select path from name_path;

with recursive name_path (id, menu_name, path,level) as (
    select e.id, e.menu_name, cast(menu_name as char(255)), 1
    from sys_menu e
    where parent_id=0
    union all
    select e.id, e.menu_name, concat(ep.path, '/', e.menu_name), level+1
    from name_path as ep
    inner join sys_menu as e
    on ep.id = e.parent_id
    )
select * from name_path;

行转列、列转行

listagg：拼接值

select *, listagg(score) within group(order by course) as scores 
from my_course group by name;

PIVOT：行转列

select * from my_course 
pivot(sum(score) for course in ('语文','数学','英语') );

UNPIVOT：列转行

select * from my_city
unpivot(city for c_seq in (city1,city2,city3) ) up;

窗口函数

语法： <窗口函数> OVER ([PARTITION BY <列>] ORDER BY <排序的列>)
- partition by ：用于分组（非必须）,分组后的记录相当于一个窗口
- order by：用于排序，对窗口内的记录进行排序
- 注意： 窗口函数只能在Select子句中。

排名函数：

特点：是对窗口内的记录进行排名

rank()：排名，如 1、1、3
dense_rank()：排名，如 1、1、2
row_number()：行数，如 1、2、3

-- 分类下的售价排名
SELECT product_name, product_type, sale_price,
 RANK() OVER (PARTITION BY product_type
 ORDER BY sale_price) AS ranking
 FROM product;
-- 全局的售价排名
SELECT product_name, product_type, sale_price, 
 RANK() OVER (ORDER BY sale_price) AS ranking
 FROM product;
-- 全局的售价排名（各种排名）
SELECT product_name, sale_price,
    RANK() OVER (ORDER BY sale_price) AS ranking,
    DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking,
    ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
    FROM product;

聚合函数：

特点：是对窗口内的记录进行累计，而且只累计当前记录之前的（窗口0行~当前行）

sum(column)：
avg(column)：
count(column)：
注意： 是对窗口的聚合

-- 计算分类下的累计的价格（窗口0行~当前行）
 SELECT product_id,product_type, product_name, sale_price,
 SUM(sale_price) OVER (PARTITION BY product_type ORDER BY sale_price) AS current_sum
 FROM product;

-- 计算累计的价格（窗口0行~当前行）
 SELECT product_id,product_type, product_name, sale_price,
 SUM(sale_price) OVER (ORDER BY sale_price desc) AS current_sum
 FROM product;

 -- 计算平均的价格（窗口0行~当前行）
SELECT product_id, product_name, sale_price,
    SUM(sale_price) OVER (ORDER BY product_id) AS curr_sum,
AVG(sale_price) OVER (ORDER BY product_id) AS curr_avg
    FROM product;
 
 -- 计算平均的价格，
 -- 'ROWS 2 preceding'表示只累计当前记录之前的前2+1行
 -- 'ROWS 2 following'表示只累计当前记录之后的后2+1行
 -- 'ROWS BETWEEN 1 preceding AND 1 following'表示只累计当前记录前后的1+1+1行
SELECT product_id, product_name, sale_price,
 AVG (sale_price) OVER (ORDER BY product_id
 ROWS 2 preceding) AS moving_avg
 FROM product;


-- 根据分类 — 上架时间 分级合计价格。（注，在mysql中写法为 GROUP BY product_type WITH ROLLUP）
SELECT product_type, shelf_time, SUM(sale_price) AS sum_price
    FROM product GROUP BY ROLLUP(product_type,shelf_time);

GROUPING运算：

rollup：rollup 是“卷起”的意思，形象地说明了该操作能够得到像从小计到合计这样。rollup可以同时得出合计和小计。

-- 分类 计算价格总和（注，在mysql中写法为 GROUP BY product_type WITH rollup）
SELECT product_type, SUM(sale_price) AS sum_price
 FROM product GROUP BY rollup(product_type);
 
-- 分类、上架时间 计算价格总和（注，在mysql中写法为 GROUP BY product_type,regist_date WITH rollup）
 SELECT product_type, regist_date, SUM(sale_price) AS sum_price
    FROM product GROUP BY rollup(product_type,regist_date);

-- grouping(column)：该函数在其参数列的值为超级分组记录所产生的null 时返回 1（即小结的记录标记为1）

-- rollup的使用
-- rollup分了层级：一级分组product_type，二级分组：regist_date。（即groupBy()、groupBy(product_type)、groupBy(product_type,regist_date)）
SELECT 
		CASE WHEN GROUPING(product_type) = 1 
		 THEN '商品种类 合计'
		 ELSE product_type END AS product_type,
		 CASE WHEN GROUPING(regist_date) = 1 
		 THEN '登记日期 合计'
		 ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
		 SUM(sale_price) AS sum_price
 FROM Product GROUP BY rollup(product_type, regist_date);

cube：用数据来搭积木

 -- cube的使用
 -- cube相比rollup多了 直接以regist_date分组的记录。（即groupBy()、groupBy(product_type)、groupBy(product_type,regist_date)、groupBy(registe_date)）
SELECT 
		CASE WHEN GROUPING(product_type) = 1 
		 THEN '商品种类 合计'
		 ELSE product_type END AS product_type,
		 CASE WHEN GROUPING(regist_date) = 1 
		 THEN '登记日期 合计'
		 ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
		 SUM(sale_price) AS sum_price
 FROM Product GROUP BY cube(product_type, regist_date);

grouping sets：相当从 ROLLUP 或者 CUBE 的结果中取出部分记录

-- grouping sets的使用
-- cube中的记录的子集。（即groupBy(product_type)、groupBy(registe_date)）
SELECT 
		CASE WHEN GROUPING(product_type) = 1 
		 THEN '商品种类 合计'
		 ELSE product_type END AS product_type,
		 CASE WHEN GROUPING(regist_date) = 1 
		 THEN '登记日期 合计'
		 ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
		 SUM(sale_price) AS sum_price
 FROM Product GROUP BY grouping sets(product_type, regist_date);

JSON操作

-- json数组，追加元素（基本值）
UPDATE ch_chat_msg 
            SET reads_sign = JSON_ARRAY_APPEND(reads_sign,'$',:userId ) 
            WHERE msg_id=:msgId
-- json数组，追加元素（对象值）
UPDATE ch_group_relation 
            SET users = JSON_ARRAY_APPEND(users,'$',CONVERT(:member,JSON) ) 
            WHERE group_no=:groupNo

-- json数组，移除元素
UPDATE ch_group_relation SET users = JSON_REMOVE(users, CONCAT('$[',:index,']') ) where group_no=:groupNo