目录
十、开窗函数查询(只有mysql8版本以上支持,5版本没有这个功能)
本次分享到此结束,觉得有用的朋友给博主点点关注!!!欢迎留言或者私信我一起讨论!!!
一、简单查询
格式:select [distinct] 字段名(*) from 表名;
注:distinct实现去重,可以不写。如果想查询全部信息,可以用 * ,* 表示全部字段。
二、条件查询(关键字:where)
1.比较运算符查询
比较运算符:< ,>,>=,<=,=,!=(不等于),<>(不等于)
格式:select [distinct] 字段名(*) from 表名 where 条件;
注:这里条件可以是字段名 = 值,比如 age = 18等一系列用了比较运算符的条件
2.逻辑查询
逻辑符号:and,or,not
格式:select * from 表名 where 条件;
注:逻辑运算符一般和比较运算符一起使用,这里的条件可以是 条件1 and/or 条件2,条件1和2都是比较运算符条件,或者not 条件1,比如 age =18 and name = '张三',age = 18 or age = 20,not age < 18。
3.范围查询
关键字:between ...and...(是一个整体),in()
格式:select * from 表名 where 字段名 between 值1 and 值2;
select * from 表名 where 字段名 in (值1,值2);
注:in里面的两个值意思是字段名满足值1或者满足2,相当于or,比如 age in(18,20)等于 age = 18 or age = 20
4.模糊查询
关键字:like
格式:select * from 表名 where 字段名 like 值;
注:这个值可以是: '海%'意思是以海字开头,'%海'意思是以海字结尾,'%海%'意思是有海字,'海_'意思是以海字开头且只有两个字,'_海'意思是以海字结尾且只有两个字,'_ _ _'意思是三个字。这里一般用于名字的查询。
5.非空判断查询
关键字:is null(是空值),is not null (不是空值)
格式:select * from 表名 where 字段名 is null;
select * from 表名 where 字段名 is not null;
注:第一个格式是返回字段名是空值的数据,第二个格式是返回字段名不是空值的数据。
三、排序查询
关键字:ord by , asc / desc
格式:select * from 表名 order by 字段名 asc/desc;
注:asc是升序,desc是降序,默认是asc不写,写了会报警告,说你这里的asc是多余的,因为系统默认给你升序就是asc了,要是想降序需要写desc。
四、聚合查询
五大聚合函数:count():计数,sum():求和,avg():求平均,max():最大值,min():最小值,聚合函数又叫统计函数或者分组函数(整体看成一个大组)
格式:select 聚合函数(字段名) from 表名;
如 select sum / count / avg / max / min(字段名) from 表名;
注:聚合函数(字段名)会自动忽略null值,如果不想忽略null值,可以用聚合函数(*)或者聚合函数(常量),这里聚合函数前面不能字段名,否则报错,则么才能有字段名下面会有讲解。
五、分组查询
关键字:group by ,having
格式:select * from 表名 group by 字段名 having 聚合条件;
where和having的区别:1.where在分组前面,having在分组后面。
2.where后面不能接聚合条件,having后面可以接聚合条件和非聚合条件,但是建议having只接聚合条件,where接非聚合条件,因为having要是同时加了会使得效率变低。有聚合函数的条件就是聚合条件。
3.where后面不能加别名,having后面可以加别名。
select 字段名 聚合函数(*) from 表名 group by 字段名 having 聚合条件;
注:用group by分组后聚合函数前面就可以接group by后面的字段名了,注意,只能加group by后面的字段名,其他字段名不能加。要是想在聚合函数前面加任意字段名,后面的开窗函数可以解决这个问题。
六、分页查询
关键字:limit M,N M(起止索引)= ( 当前页数-1) * N,为什么-1可以参考列表的索引,第一页的索引是从0开始的。N是每一页查询的数据条数。
格式:select * from 表名 limit M,N;
注:分页查询可以跟排序查询混合使用,先根据年龄降序排序,然后limit 0,1 就可以筛选处年龄最大的那个人。
limit 0,1就是第一页,且一页就只有一个数据。
七、多表查询(连接查询)
主表:被引用的表
从表:引用了其他表的表
主键:主表的键
外键:从表的键
外键约束:
添加:alter table 从表名 add foreign key(外键字段名)reference 主表名 (主键字段名); #reference是关系的意思
作用:限制主表不能删除从表引用了的数据,限制从表不能插入主表没有的数据。
优点:保证数据的准确性和完整性。
1.交叉连接查询
格式:
隐式:select * from 表名1,表名2;
显式:select * from 表名1 cross join 表名2;
注:了解即可,不用该查询,因为该查询会交叉相乘,得到很多没用的数据,甚至数据多了以后相乘会使服务器崩溃,因此不建议使用!!!
2.内连接查询
关键字:inner join...on(inner可以省略不写)
格式:
隐式:select * from 表名1,表名2 where 表关联条件;
显式:select * from 表名1 inner join 表名2 on 表关联条件;
注:内连接不加条件就变成交叉连接了
3.左外连接查询
关键字:left outer join on(outer可以省略不写)
格式:select * from 表名1 left outer join 表名2 on 表关联条件;
特点:左表所有数据固定展示,右表只展示和左表关联上的数据,其他用null补全
4.右外连接查询
关键字:right outer join on(outer可以省略不写)
格式:select * from 表名1 right outer join 表名2 on 表关联条件;
特点:右表所有数据固定展示,左表只展示和右表关联上的数据,其他用null补全
八、子查询
概念:一条查询语句作为另一条查询语句的条件或者表
格式:
子查询语句作为主查询的条件:select * from 表名 where 字段名 = (select * from 表名 where 条件);
子查询语句作为主查询的表:select * from (select * from 表名 where 条件) join 表名 on 表连接条件;
特点:用子查询会提高运行效率,也称为sql调优。
九、自连接查询
1.一个表作为两种功能表自连接
格式:select * from 表名 as 别名1 join 表名 as 别名2 on 表关联条件 [where 条件]; #[]里面的可以省略不写,as也可以省略不写。
注:自连接应用场景很少,一般用于查找一个表中每个省下面的每个市,每个市下面的每个县。
2.一个表作为三种功能表自
连接
格式:select * from 表名 as 别名1 join 表名 as 别名2 on 表关联条件1
join 表名 as 别名3 on 表关联条件2 [where 条件];
十、开窗函数查询(只有mysql8版本以上支持,5版本没有这个功能)
关键字:over()
格式:select 字段名,聚合函数(字段名)over ([partition by 分组字段],[order by 排序字段 asc/desc])from 表名 ; #[]里的内容可以省略不写
五大聚合函数开窗:count(),sun(),avg(),max(),min()
注:这里聚合函数前面就可以随便加任意字段了!!!
三大排序函数:row_number(),rand(),dense_rand()
注:这三个函数都会返回编号
特点:
row_number():唯一且连续
rank():并列不连续
dense_rank():并列且连续
十一、完整代码
# 1.数据准备
use day02_db;
# 创建商品表
CREATE TABLE product
(
pid INT PRIMARY KEY,
pname VARCHAR(20),
price DOUBLE,
category_id VARCHAR(32)
);
# 插入数据
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'海澜之家',1,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(14,'小米',1999,'');
INSERT INTO product(pid,pname,price,category_id) VALUES(15,'华为',6999,'null');
INSERT INTO product(pid,pname,price,category_id) VALUES(16,'蜜雪冰城',1,null);
#简单查询:
# 查看所有的商品信息
select pid,pname,price,category_id from day02_db.product;
select * from day02_db.product;
#查询商品名称和商品价格
select pname,price from day02_db.product;
#查询商品价格,要求去重展示
select distinct price from day02_db.product;
# 要求所有商品价格上涨10元展示
select distinct pname,price+10 as price from day02_db.product;
#条件查询:
#比较运算符
#查询价格大于800的商品信息
select * from product where price > 1000;
#查询价格小于800的商品信息
select * from product where price < 1000;
#查询价格大于1000,并且小于3000的商品信息
select * from product where price >= 1000 and price <= 3000;
#查询价格不等于1000的商品信息
select * from product where price <> 1000;
select * from product where price != 1000;
select * from product where not(price = 1000);
#查询价格大于3000或者价格小于1000的所有商品信息
select * from product where price >= 3000 or price <= 1000;
select * from product where not(price >= 1000 and price <= 3000);
#范围查询
select * from product where price between 800 and 3000;
select * from product where price not between 800 and 3000;
select * from product where price in(200,800,3000);
select * from product where price not in(200,800,3000);
select * from product where pname in('联想','小米','海尔');
select * from product where pname not in('联想','小米','海尔');
#模糊查询
#查找名称以‘海’开头的商品信息
select * from day02_db.product where pname like '海%';
#查找名称带‘想’字的商品信息
select * from day02_db.product where pname like '%想%';
#查找名称以‘斯’字结尾的商品信息
select * from day02_db.product where pname like '%斯';
#查找名称以‘海’开头并且名称是两个字的商品信息
select * from day02_db.product where pname like '海_';
#查找名称第二个字是‘想’字的商品信息
select * from day02_db.product where pname like '_想%';
#查找名称以‘斯’字结尾,并且名称是三个字的商品信息
select * from day02_db.product where pname like '__斯';
#查找名称是三个字的所有商品信息
select * from day02_db.product where pname like '___';
#非空判断
#查询没有分类id的商品信息
select * from day02_db.product where category_id is null;
#查询有分类id的商品信息
select * from day02_db.product where category_id is not null;
#排序查询
#查询所有的商品信息,要求按照价格升序排序
select * from day02_db.product order by price ;
#查询所有的商品信息,要求先按照价格升序排序,价格一样,按照分类id升序排序
select * from day02_db.product order by price , category_id ;
#查询所有的商品信息,要求按照价格升序排序
select * from day02_db.product order by price desc;
#查询分类id为c002的商品信息,并且价格按照降序排序
select * from day02_db.product where category_id = 'c002'order by price desc;
#查询名称是两个字并且价格在800-3000的商品信息,并且价格在800-3000按照降序排序
select * from day02_db.product where price between 800 and 3000 and pname like '__' order by price desc;
#创建库
CREATE DATABASE day03_db;
#使用库
USE day03_db;
#复制表
#方式一:复制表结构再快速插入数据
CREATE TABLE product LIKE day02_db.product;
INSERT INTO
product
SELECT *
FROM
day02_db.product;
#方式二:合二为一
CREATE TABLE product
SELECT *
FROM
day02_db.product;
#聚合查询
#聚合函数又叫统计函数,也叫分组函数
#sum(),count(),max(),min(),avg()
# 聚合函数(字段名):会自动忽略null值
#有的统计个数场景不要忽略null值,那就可以用count(*)或者count(常量)
#如果字段加了主键也可以count(主键字段名)
#统计商品的总个数
SELECT
COUNT(*)
FROM
product;
#统计category_id不为空的商品总个数
SELECT
COUNT(*)
FROM
product
WHERE
category_id IS NOT NULL;
SELECT
COUNT(category_id)
FROM
product;
#统计商品的价格总和,最大价格,最小价格,平均价格
# select sum(price) from product;
# select max(price) from product;
# select min(price) from product;
# select avg(price) from product;
SELECT
SUM(price) zongjia,
MAX(price) zuida,
MIN(price) zuixiao,
AVG(price) pingjun
FROM
product;
#查询c001分类的价格总和,最大价格,最小价格,平均价格
SELECT
SUM(price) zongjia,
MAX(price) zuida,
MIN(price) zuixiao,
AVG(price) pingjun
FROM
product
WHERE
category_id = 'c001';
#上述基础上,要求平均价格保留两位小数
#round(数据,保留位数)
SELECT
SUM(price) zongjia,
MAX(price) zuida,
MIN(price) zuixiao,
ROUND(AVG(price), 2) pingjun
FROM
product
WHERE
category_id = 'c001';
#分组查询
#统计每个分类的商品总价:group by
SELECT
category_id,
COUNT(*)
FROM
product
GROUP BY
category_id;
#统计每个分类的商品的个数,最终只展示个数大于1的信息
SELECT
category_id,
COUNT(*)
FROM
product
GROUP BY
category_id
HAVING
COUNT(*) > 1;
#先筛选分类ID不为null的商品,然后统计各个分类的商品个数,最终只展示商品个数大于1的信息
#sql_mode=only_full_group_by 分组聚合查询的时候,select后字段名要么再groupby后出现,要么再聚合函数内出现
#非聚合条件用where,聚合条件用having,having虽然也可以包含非聚合条件,但是效率低,不建议用。
SELECT
category_id,
COUNT(*)
FROM
product
WHERE
category_id IS NOT NULL
GROUP BY
category_id
HAVING
COUNT(*) > 1;
#分页查询:SELECT * from product LIMIT x,y
#x:起始索引 y:本次查询多少数据
#一共16条数据,每页4条,请分别查询出每一页的数据
#第一页
SELECT * from product LIMIT 0,4;
#第二页
SELECT * from product LIMIT 4,4;
#第三页
SELECT * from product LIMIT 8,4;
# x=(当前页数-1)*y
#求商品价格最高的1个的商品信息
SELECT * FROM product ORDER BY price desc limit 0,1;
#求商品价格最低的5个商品信息
SELECT * FROM product ORDER BY price limit 0,5;
#先筛选分类ID不为null的商品,然后统计各个分类的商品个数,最终只展示商品个数大于1的信息,按照个数降序排序,最终只展示前两条数据
SELECT
category_id,
COUNT(*)
FROM
product
WHERE
category_id IS NOT NULL
GROUP BY
category_id
HAVING
COUNT(*) > 1
ORDER BY
count(*) desc
LIMIT
0,2;
#where后不能使用别名
# 查询关键字书写顺序: select [distinct]-> from -> where -> group by -> having -> order by -> limit
#查询关键字执行顺序:from -> Where -> group by -> having -> select [distinct]-> order by -> limit
# 外键约束
# 如果之前已经存在就先删除
drop table if exists product;
drop table if exists products;
drop table if exists category;
# 分类表
CREATE TABLE category
(
cid VARCHAR(32) PRIMARY KEY,
cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
pid varchar(32) PRIMARY KEY,
pname VARCHAR(40),
price DOUBLE,
category_id varchar(32)
);
# 添加外键约束
alter table products add foreign key(category_id) references category(cid);
/*上述代码执行后,主从表之间如果没有成功添加外键约束,原因是存储引擎的问题!!!
mysgl中最常见的存储引警有myisam和innodb
myisam不支持外键和事务,innodb 支持外键租事务!!!
*/
show create table category; -- ENGINE=MyISAM ...
show create table products; -- ENGINE=MyISAM ...
# mysql安装路径的my.ini文件改为innodb 存储引警后,重新建表,再次查看
show create table category; -- 结果是innodb
show create table products; -- 结果是innodb
# 查看表关系: 右键从表products->选diagrams-> show visualization
/*外键约束关键字: foreign key
外键约束作用:
限制从表插入数据: 如果从表插入的外键值,在主表中不存在,就插入失败
限制主表删除数据: 如果主表的主键值已经被从表引用,在主表删除该数据的时候,就删除失败
外键约束好处: 保证数据的准确性和完整性*/
# 演示外键约束的限制作用:
# 演示往从表中插入主表不存在的数据
insert into products values('p001','联想笔记本',4999,'c001'); # 插入失败,因为有约束,限制从表插入外键值必须是主表存在的!!!
# 往主表中插入数据
insert into category values('c001','电脑');
# 再次往从表插入刚刚的数据
insert into products values('p001','联想笔记本',4999,'c001'); # 插入成功,因为主表已经有了c001记录
insert into products values('p002','小米笔记本',4999,'c001'); # 插入成功,因为主表已经有了c001记录
insert into products values('p003','小米笔记本',4999,null); # 插入成功,不指定分类直接插入null
# 演示主表删除从表已经引用的数据
delete from category where cid = 'c001'; # 删除失败,因为有约束,限制主表删除的主键值不能是从表已经引用的!!!
# 如何才能删除c001数据? 思路1: 直接把从表引用c001的数据删除 思路2: 把引用c001的外键值改为null
delete from products where category_id = 'c001';
# update products set category_id = null where category_id = 'c001';
# 再次执行主表删除操作
delete from category where cid = 'c001'; # 删除成功,因为从表已经没有引用了
# 多表查询数据准备
# 创建hero表
CREATE TABLE hero
(
hid INT PRIMARY KEY,
hname VARCHAR(255),
kongfu_id INT
);
# 创建kongfu表
CREATE TABLE kongfu
(
kid INT PRIMARY KEY,
kname VARCHAR(255)
);
# 插入hero数据
INSERT INTO hero VALUES(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);
# 插入kongfu数据
INSERT INTO kongfu VALUES(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');
# 1.交叉连接(慎用)
# 显式
select * from hero cross join kongfu;
# 隐式
select * from hero , kongfu;
# 2.内连接(常用)
# 隐式
select * from hero , kongfu where hero.kongfu_id=kongfu.kid;
# 显式
select * from hero inner join kongfu on hero.kongfu_id=kongfu.kid;
# 3.左外连接
# 需求2: 获取所有的英雄名称和对应的功夫,没有功夫的null补全
# 左表所有数据展示,右表只展示和左表关联上的数据,其他用null补全
select * from hero left outer join kongfu on hero.kongfu_id=kongfu.kid;
# 4.右外连接
# 需求3: 获取所有的功夫和对应的英雄,没有英雄的null补全
# 右表所有数据展示,左表只展示和右表关联上的数据,其他用null补全
select * from hero right outer join kongfu on hero.kongfu_id=kongfu.kid;
# 创建数据库
create database day04_db;
# 使用数据库
use day04_db;
# 回顾多表查询
/*
交叉连接:select 字段名 from 左表 cross join 右表;
内连接:select 字段名 from 左表 inner join 右表 on 关联条件;
左外连接:select 字段名 from 左表 left outer join 右表 on 关联条件;
右外连接:select 字段名 from 左表 right outer join 右表 on 关联条件;
注意: 左表和右表只是位置关系,在前面是左表,在后面的是右表
*/
# 准备数据
create table category (
cid varchar(32) primary key ,
cname varchar(50)
);
create table products(
pid varchar(32) primary key ,
pname varchar(50),
price int,
flag varchar(2), #是否上架标记为:1表示上架、0表示下架
category_id varchar(32),
constraint products_fk foreign key (category_id) references category (cid)
);
# 分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
# 商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
#多表查询
#交叉连接查询
SELECT * FROM category CROSS JOIN products ; #显式
SELECT * FROM category , products ; #隐式
#内连接
SELECT * FROM category c inner JOIN products p ON c.cid = p.category_id; #显式
SELECT * FROM category c , products p WHERE c.cid = p.category_id; #隐式
#左外连接
SELECT * FROM category c left outer JOIN products p ON c.cid = p.category_id;
#右外连接
SELECT * FROM category c right outer JOIN products p ON c.cid = p.category_id;
#统计每个分类下的商品个数,如果分类下没有商品也要展示个数为0
SELECT category_id,cname,count(category_id) FROM category c left outer JOIN products p ON c.cid = p.category_id GROUP BY p.category_id,cname ;
#统计哪些分类的商品已经上架,要求展示分类名称
SELECT pname,cname,flag FROM category JOIN products p ON category.cid = p.category_id WHERE flag = '1';
#查询'化妆品'和'服饰'分类的商品详情
SELECT products.* FROM category JOIN products ON category.cid = products.category_id WHERE cname in('化妆品','服饰');
#子查询
#查询'化妆品'分类的商品详情
SELECT cid FROM category WHERE cname = '化妆品';#c003
SELECT * FROM products WHERE category_id = 'c003';
#子查询作为主查询的条件
SELECT cid FROM category WHERE cname = '化妆品';
SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品');
#子查询作为主查询的表
SELECT * FROM category WHERE cname = '化妆品';
SELECT * FROM (SELECT * FROM category WHERE cname = '化妆品') as c JOIN products ON products.category_id = c.cid;
#sql调优:能提前过滤的旧提前过滤,这样能减少join的次数,从而提升效率
#自连接查询
#查询广东省下的所有城市
SELECT city.title,province.title FROM areas as city join areas as province on city.pid = province.id WHERE province.title ='广东省';
#查询湛江市下的所有县
SELECT xian.id,xian.title FROM areas as xian join areas as city on xian.pid = city.id WHERE city.title ='湛江市';
# SELECT
# province.id,
# province.title,
# city.id,
# city.title,
# xian.id,
# xian.title
# FROM
# areas AS xian
# JOIN areas AS city ON xian.pid = city.id
# JOIN areas AS province ON city.pid = province.id;
#三表自连接
CREATE TABLE areas_distribute(
id int PRIMARY KEY AUTO_INCREMENT,
province_code varchar(30),
province_name varchar(30),
city_code varchar(30),
city_name varchar(30),
county_code varchar(30),
county_name varchar(30)
);
INSERT INTO
areas_distribute
SELECT
NULL,
province.id province_code,
province.title province_name,
city.id city_code,
city.title city_name,
county.id county_code,
county.title county_name
FROM
areas AS county
JOIN areas AS city ON county.pid = city.id
JOIN areas AS province ON city.pid = province.id;
#开窗函数
#开窗函数本质就是在结果末尾新增一列
#特点:保证了输出的数据和输入的数据条数一致
SELECT pname,price,min(price) from products;
SELECT pname,price,min(price) OVER () from products;
#求每个商品价格占总价格的占比
SELECT pname,price,sum(price) OVER () ,price/sum(price) OVER() FROM products;
#求每个商品价格和平均价格的差额
SELECT pname,price,avg(price) OVER () ,price-avg(price) OVER () FROM products;
#求每个商品价格和最大价格的差额
SELECT pname,price,max(price) OVER () ,price-max(price) OVER () FROM products;
#求每个商品价格和最小价格的差额
SELECT pname,price,min(price) OVER () ,price-min(price) OVER () FROM products;
# 需求6: 经计每个分类的商品个数
# 方式1: group by
SELECT
category_id,
COUNT(*) AS cnt
FROM
products
GROUP BY
category_id;
# 方式2: partition by
SELECT DISTINCT
category_id,
COUNT(*) OVER (PARTITION BY category_id) AS cnt
FROM
products;
#需求7: 统计每个分类商个数点商品个数的占比
SELECT DISTINCT
category_id,
count(*) OVER (PARTITION BY category_id) AS cnt,
count(*) OVER() AS total_cnt,
count(*) OVER(PARTITION BY category_id)/ COUNT(*) OVER() AS 占比
from products;
create table employee (
empid int,
ename varchar(20) ,
deptid int ,
salary decimal(10,2)
);
insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(3,'张飞',10,3500.00);
insert into employee values(4,'关羽',10,4500.00);
insert into employee values(5,'曹操',20,1900.00);
insert into employee values(6,'许褚',20,4800.00);
insert into employee values(7,'张辽',20,6500.00);
insert into employee values(8,'徐晃',20,14500.00);
insert into employee values(9,'孙权',30,44500.00);
insert into employee values(10,'周瑜',30,6500.00);
insert into employee values(11,'陆逊',30,7500.00);
select
*
from employee
order by salary desc;
# 开窗函数排序
select
*,
row_number() OVER (order by salary desc) r1,
rank() over(order by salary desc) r2,
dense_rank() over(order by salary desc) r3
from employee;
# 需求2: 使用三个函数对每个分类里的所有商品价格降序排序生成对应序号
select
*,
row_number() OVER (PARTITION BY deptid order by salary desc) r1,
rank() over(PARTITION BY deptid order by salary desc) r2,
dense_rank() over(PARTITION BY deptid order by salary desc) r3
from employee;