python大数据之MySQL学习总结——day03 查询-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_46010015/article/details/134051255

单表查询

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('花花公子','香奈儿');

模糊查询

```mysql
# 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 '香__';

非空判断

```mysql
# 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 , 排序字段2名 asc|desc;
			注意: 如果order by后跟多个排序字段,先按照前面的字段排序,如果有相同值的情况再按照后面的排序规则排序

示例:

```mysql
# 排序查询
# 需求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值

示例:

```mysql
# 注意: 别名不建议用中文,以下仅仅为了演示
# 需求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语句;

示例:

```mysql
# 方式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;