1数据准备
# 1.数据准备
create database day03;
use day03;
# 创建商品表
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);
2.基础查询
基础查询关键字: select:查什么 from: 从哪儿查
基础查询格式: select [distinct] 字段名 | * from 表名;
[]:可以省略
|: 或者
*: 对应表的所有字段名
distinct: 去除重复内容
as : 可以给表或者字段起别名
# 需求1: 查询所有的商品信息
select pid,pname,price,category_id from product;
# 注意:*代表所有字段
select * from product;
# 需求2: 只查询商品名称和对应价格
select pname,price from product;
# 需求3: 查询商品分类id,要求去重
select DISTINCT category_id from product;
# 需求4: 查询商品名称和对应价格,要求最终展示字段名为商品名称和商品价格
# as 起别名 ,实际工作中不建议用中文,此处仅仅为了演示
select pname as 商品名称,price as 商品价格 from product;
# 注意: as 关键字可以省略
select pname 商品名称,price 商品价格 from product;
# 需求5: 所有的商品价格要求加100元展示
select pname,price+100 from product;
# 可以起别名展示
select pname,(price+100) as price from product;
3.条件查询
条件关键字:where
条件查询基础格式:select 字段名 from where 条件:
比较运算符:> < <= >= != <>
逻辑运算符:and or not
范围查询:连续范围:between x and y 非连续范围:in(x,y,....)
模糊查询:关键字:like %:0个或者多个字符 _:一个字符
非空判断:为空:is null 不为空:is not null
比较查询
# 1.比较运算符: > < >= <= != <>
# 需求1: 查询商品价格大于800的商品信息
select * from product where price > 800;
# 需求2: 查询商品价格小于800的商品信息
select * from product where price < 800;
# 需求3: 查询商品价格大于等于800的商品信息
select * from product where price >= 800;
# 需求4: 查询商品价格小于等于800的商品信息
select * from product where price <= 800;
# 需求5: 查询商品价格不等于800的商品信息
select * from product where price != 800;
select * from product where price <> 800;
逻辑查询
# 2.逻辑运算符: and(并且) or(或者) not(取反)
# 需求1: 查询商品价格介于200-2000之间(含200,2000)的商品信息
select * from product where price >=200 and price <=2000;
# 需求2: 查询商品价格等于200或者等于2000的商品信息
select * from product where price = 200 or price = 2000;
# 需求3:查询商品价格不在 200-2000之间(含200,2000)的商品信息
select * from product where not (price >=200 and price <=2000);
select * from product where price < 200 or price > 2000;
select * from product where not price >=200 or not price <=2000;
范围查询
# 3.范围查询: 连续范围:between x and y 非连续范围: in(x,y)
# 需求1: 查询商品价格介于200-2000之间(含200,2000)的商品信息
select * from product where price between 200 and 2000;
# 需求2: 查询商品价格等于200或者等于2000的商品信息
select * from product where price in(200,2000);
# 需求3: 查询商品价格 不 在200-2000之间(含200,2000)的商品信息
select * from product where price not between 200 and 2000;
# 需求4: 查询商品价格不等于200和2000的其他商品信息
select * from product where price not in(200,2000);
# 需求: 查询商品名称是'花花公子'或者'香奈儿'的商品信息
select * from product where pname in('花花公子','香奈儿');
迷糊查询
# 4.模糊 查询: 关键字:like %:0个或者多个字符 _:一个字符
# 需求1: 查询商品名称以'香'开头的所有商品信息
select * from product where pname like '香%';
# 需求2: 查询商品名称包含'想'字的所有商品信息
select * from product where pname like '%想%';
# 需求3: 查询商品名称以'斯'字结尾的所有商品信息
select * from product where pname like '%斯';
# 需求4: 查询商品名称第三个字是'斯'的商品信息
select * from product where pname like '__斯%';
# 需求5: 查询商品名称以'香'开头并且是三个字的所有商品信息
select * from product where pname like '香__';
非空查询
# 5.非空 判断: 为空: is null 不为空:is not null
# sql中null代表空的无意义的意思,和空字符串''以及字符串'null'不是一回事!!!
# 需求1: 查询商品分类id为空的商品信息
select * from product where category_id is null;
# 需求2: 查询商品分类id不为空的商品信息
select * from product where category_id is not null;
# 需求3: 查询商品分类id是空字符串的商品信息
select * from product where category_id = '';
# 需求4: 查询商品分类id是名为'null'的商品信息
select * from product where category_id = 'null';
4.排序查询
排序查询关键字:order by
排序查询基础格式:select 字段名 from 表名 order by 排序字段名 asc|desc;
asc:升序(默认)
desc:降序
排序查询进阶格式:select 字段名 from order by 排序字段名1 asc|desc,排序字段名2asc|desc;
注意:如果order by后跟多个排序字段名,先按照前面的字段排序,如果有相同值的情况再按照后面的排序规则排序
# 排序查询
# 需求1: 查询所有商品信息,按照价格升序排序
select * from product order by price asc; # 报黄警告,因为底层默认升序,asc可以省略
select * from product order by price;
# 需求2: 查询所有商品信息,按照价格降序排序
select * from product ORDER BY price desc;
# 需求3: 查询所有商品信息,先按照价格降序排序,价格相同再按照分类id升序排序
select * from product ORDER BY price desc,category_id;
# 需求4: 查询所有商品信息,先按照价格降序排序,价格相同再按照分类id降序排序
select * from product ORDER BY price desc,category_id desc;
# 需求5: 查询c002分类的所有商品,并且按照价格降序排序
select * from product where category_id = 'c002' order by price desc;
5.集合函数
聚合函数:又叫做统计函数,也叫分组函数
常用聚合函数: sum() count() avg() max() min()
聚合函数基础查询格式:select 聚合函数(字段名) from 表名; 注意:此处没有分组默认整个表就是一个大的分组
注意:集合函数(字段名)会自动忽略null值,以后统计个数一般用count(*)统计,因为它不会忽略null值
# 注意: 别名不建议用中文,以下仅仅为了演示
# 需求1.1: 统计所有商品的个数
select count(pid) as 总个数 from product;
select count(1) as 总个数 from product;
select count(*) as 总个数 from product;
# 需求1.2: 统计商品分类不为空的一共有多少条记录
select count(*) from product where category_id is not null;
# 利用count(字段名)会自动忽略null值的特点统计
select count(category_id) from product;
# 需求2: 统计所有商品的总价格
select sum(price) from product;
# 需求3: 统计所有商品中的最大价格
select max(price) from product;
# 需求4: 统计所有商品中的最小价格
select min(price) from product;
# 需求5: 统计所有商品的平均价格
select avg(price) from product;
# 需求6: 查询c002分类的所有商品中最大价格
select max(price) from product where category_id = 'c002';
# 需求7: 查询商品价格大于2000的有几个
select count(*) from product where price > 2000;
# 需求8: 查询c001分类的的商品个数
select count(price) from product where category_id = 'c001';
# 需求9: 统计c001商品的平均价格要求保留2位小数
# 如何保留2位小数? round(数据,保留位数)
select round(avg(price),2) from product where category_id = 'c001';
6.分组查询
分组查询关键字: group by
分组查询基础格式: select 分组字段名,聚合函数(字段名) from 表名 group by 分组字段名;
注意: select后的字段名要么在group by后面出现过,要么写到聚合函数中,否则报错...sql_mode=only_full_group_by
分组查询进阶格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];
where和having的区别?
书写顺序: where在group by 前,having在group by后
执行顺序: where在group by 前,having在group by后
分组函数: where后不能跟聚合条件,只能跟非聚合条件,having后可以使用聚合条件,也可以使用非聚合条件(不建议)
应用场景: 建议大多数过滤数据都采用where,只有当遇到聚合条件的时候再使用having
使用别名: where后条件不能使用别名,having后可以使用别名
# 需求1: 统计各个分类的商品个数
select category_id,count(*) from product GROUP BY category_id;
# 需求2: 统计各个分类的商品个数,筛选个数大于1的分类信息
select category_id,count(*) from product GROUP BY category_id having count(*) > 1;
# 需求3: 先过滤分类id是null的商品,然后统计各个分类的商品个数
select category_id,count(*) FROM product where category_id is not null group by category_id;
# 注意: having后也可以跟非聚合条件,但是不建议,因为效率低下
select category_id,count(*) FROM product group by category_id having category_id is not null;
# 需求4: 先过滤分类id是null的商品,然后统计各个分类的商品个数,最后筛选个数大于1的分类信息
select category_id,count(*) from product where category_id is not null GROUP BY category_id HAVING count(*)>1;
# 注意: having后也可以跟非聚合条件,但是不建议,因为效率低下
select category_id,count(*) from product GROUP BY category_id HAVING category_id is not null and count(*)>1;
7.分页查询
分页查询关键字: limit
分页查询基础格式: select 字段名 from 表名 limit x,y;
x: 起始索引,默认从0开始
y: 本次查询的条数
注意: limit能完成topN需求,但是不能考虑到并列情况,此问题可以使用后期学习的开窗函数解决
/*
需求1:一共16条数据,每页展示4条,依次求出每页数据
分析: limit x,4
起始索引x: x= (页数-1)*每页展示条数
*/
# 第1页 起始索引: (1-1)*4
select * from product limit 0,4;
# 第2页 起始索引: (2-1)*4
select * from product limit 4,4;
# 第3页 起始索引: (3-1)*4
select * from product limit 8,4;
# 第4页 起始索引: (4-1)*4
select * from product limit 12,4;
# 需求2: 查询最大价格的商品信息
# 分析: 先按照价格降序排序,只取第一条
select * from product ORDER BY price desc limit 0,1;
# 注意: 如果起始索引是0,那么可以省略
select * from product ORDER BY price desc limit 1;
# 需求3: 查询最小价格的3个商品信息
select * from product ORDER BY price limit 3;
复制表[拓展]
快速查看建表语句: ctrl+Q
快速复制表结构(含约束): create table 新表名 like 旧表名;
查询结果快速插入到表中: insert into 表名 select语句;快速复制表结构和数据(丢失约束): create table 新表名 [as] select语句;
# 方式1: 快速查看建表语句: ctrl+Q
CREATE TABLE product1
(
pid INT NOT NULL
PRIMARY KEY,
pname VARCHAR(20) NULL,
price DOUBLE NULL,
category_id VARCHAR(32) NULL
);
# 笨方法按照旧表一条条插入数据
# 插入数据
INSERT INTO product1(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product1(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product1(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product1(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO product1(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product1(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product1(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product1(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product1(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product1(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product1(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product1(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product1(pid,pname,price,category_id) VALUES(13,'海澜之家',1,'c002');
INSERT INTO product1(pid,pname,price,category_id) VALUES(14,'小米',1999,'');
INSERT INTO product1(pid,pname,price,category_id) VALUES(15,'华为',6999,'null');
INSERT INTO product1(pid,pname,price,category_id) VALUES(16,'蜜雪冰城',1,null);
# 方式2: 快速复制表结构(含约束): create table 新表名 like 旧表名;
create table product2 like product;
# 查询结果快速插入到表中: insert into 表名 select语句;
insert into product2 select * from product;
# 方式3: 快速复制表结构和数据(丢失约束): create table 新表名 [as] select语句;
create table product3 select * from product;