/*
DQL语句介绍:
概述:
数据查询语言, 主要是操作 表数据的, 进行 查询操作.
关键字: select, from, where
常用的查询:
简单查询:
条件查询:
聚合查询:
排序查询:
分组查询:
分页查询:
一个完整的单表查询语法格式如下:
select
[distinct] 列名1, 列名2 as 别名, 列名3...
from
数据表名
where
组前筛选
group by
分组字段1, 分组字段2...
having
组后筛选
order by
排序的列1, 列2 [asc | desc]
limit
起始索引, 数据条数;
*/
# -------------- 案例1: 准备数据 --------------
-- 1. 切库
use day02;
-- 2. 查表
show tables;
# 3. 建表.
# 创建商品表：
create table product (
pid int primary key auto_increment, # 商品id, 主键, 自增
pname varchar(20), # 商品名
price double, # 商品价格
category_id varchar(32) # 商品所属的分类ID
);
# 4. 添加表数据.
insert into product(pid,pname,price,category_id) values(null,'联想',5000,'c001');
insert into product(pid,pname,price,category_id) values(null,'海尔',3000, null);
insert into product(pid,pname,price,category_id) values(null,'雷神',5000,'c001');
insert into product(pid,pname,price,category_id) values(null,'杰克琼斯',800,'c002');
insert into product(pid,pname,price,category_id) values(null,'真维斯',200,'c002');
insert into product(pid,pname,price,category_id) values(null,'花花公子',440,'c002');
insert into product(pid,pname,price,category_id) values(null,'劲霸',2000,'c002');
insert into product(pid,pname,price,category_id) values(null,'香奈儿',800,'c003');
insert into product(pid,pname,price,category_id) values(null,'相宜本草',200,'c003');
insert into product(pid,pname,price,category_id) values(null,'面霸',5,'c003');
insert into product(pid,pname,price,category_id) values(null,'好想你枣',56,'c004');
insert into product(pid,pname,price,category_id) values(null,'香飘飘奶茶',1,'c005');
insert into product(pid,pname,price,category_id) values(null,'海澜之家',1,'c002');
# 5. 查看表数据.
select * from product;
# -------------- 案例2: DQL语句 单表查询-简单查询 --------------
# 1. 查看商品表中, 所有的数据.
select pid, pname, price, category_id from product;
select * from product; # 效果同上, 即: *代表全列名.
# 2. 查询商品名 和 商品价格.
select pname, price from product;
# 3. 起别名, 查询.
select pname as 商品名称, price as 商品价格 from product;
select pname 商品名称, price 商品价格 from product; # as可以省略不写.
# 4. 查询结果是表达式(即: 算术运算), 即: 将所有商品的价格 + 10进行显示.
select *, '夯哥' from product;
select *, price + 10 as new_price from product;
# 5. 去重查询.
# 需求: 查询所有的分类.
select distinct category_id from product; # 按照 商品所属的分类ID(category_id)去重.
select distinct category_id, price from product; # 按照 商品所属的分类ID(category_id) + 价格 去重.
# -------------- 案例3: DQL语句 单表查询-条件查询 --------------
/*
格式:
select * from 数据表名 where 条件;
where后的条件可以写什么:
比较运算符: >, >=, <, <=, =, !=, <>
范围筛选:
in (值1, 值2...);
not in (值1, 值2...);
between 值1 and 值2; # 包左包右
模糊查询:
like '_值'; _ 只能代表任意的1个字符
like '%值'; % 可以代表任意的多个字符.
非空查询:
is null; # 是否为空
is not null; # 是否不为空.
逻辑运算符:
and 逻辑与, 并且的意思, 要求条件都要满足, 有False则整体为False.
or 逻辑或, 或者的意思, 只要满足任意1个条件即可, 有True则整体为True.
not 逻辑非, 取反的意思, True => False, False => True
*/
# 1.查询商品名称为“花花公子”的商品所有信息：
select * from product where pname='花花公子';
# 2.查询价格为800商品
select * from product where price = 800;
select * from product where price in (800);
# 3.查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where price not in (800);
# 4.查询商品价格大于60元的所有商品信息
select * from product where price > 60;
select * from product where not price <= 60;
# 5.查询商品价格小于等于800元的所有商品信息
select * from product where price <= 800;
# 6. 查询商品价格在200到800之间所有商品
select * from product where price between 200 and 800; # 包左包右.
select * from product where price >= 200 and price <= 800;
# 7. 查询商品价格是200或800的所有商品
select * from product where price = 200 or price = 800;
select * from product where price in (200, 800);
# 8.查询以'香'开头的所有商品
select * from product where pname like '香_'; # _代表任意的1个字符
select * from product where pname like '香__'; # _代表任意的1个字符, 这里有2个_
select * from product where pname like '香%'; # %代表任意的多个字符, 至少0个, 至多无所谓.
# 9.查询第二个字为'想'的所有商品
select * from product where pname like '_想%';
# 10.查询没有分类的商品
# 细节: 空表示什么都没有, 所以无法直接通过 = 来判断.
select * from product where category_id = null; # 不报错, 但是结果可能不是你想要的.
select * from product where category_id is null; # is null 和 is not null 才是判断空的格式.
# 11.查询有分类的商品
select * from product where category_id is not null;
# 12. 查询所有的分类.
select distinct category_id from product;
# -------------- 案例4: DQL语句 单表查询-排序查询 --------------
# 格式: select * from 数据表名 order by 要排序的列1 [asc | desc], 列2 [asc | desc];
# 其中, asc(ascending, 升序)是默认的, 可以省略不写, 如果降序, 必须手动写 desc(descending)
# 1.使用价格排序(降序)
select * from product order by price; # 默认是: 价格 升序
select * from product order by price asc; # 效果同上
select * from product order by price desc; # 降序, 手动指定 desc
# 2.在价格排序(降序)的基础上，以分类排序(降序)
# 先按照价格降序排列, 如果价格一致, 则按照 分类id 降序排列.
select * from product order by price desc, category_id desc;
select * from product order by price, category_id desc; # 价格升序, 价格一样, 分类id降序.
# -------------- 案例5: DQL语句 单表查询-聚合查询 --------------
# 1. 切库, 查表, 查看表数据.
use day02;
show tables;
select * from product; # 商品表, 数据.
# 2. 演示 聚合查询.
/*
聚合查询介绍:
概述:
之前我们学的查询都是 "横向"查询, 那如果我们要对某列值做 纵向查询, 这个时候, 就需要用到: 聚合(Aggregate)函数了.
分类:
max() 某列的 最大值
min() 某列的 最小值
sum() 某列的 和
avg() 某列的 平均值
count() 统计表的数据总条数, 或者某列的非空值的个数.
*/
# 需求1: 或者价格的最大值, 最小值.
select max(price) from product;
select min(price) from product;
select
max(price) as max_price,
min(price) as 最低价格, # 起别名的时候, 可以不写单引号.
sum(price) 价格总和,
avg(price) 均价,
count(price) 数据总条数
from product;
# 需求2: 如果保留指定的小数位数. 1346.3846153846155 => 1346.385
select round(1346.3846153846155, 3); # 保留3位小数, round()的作用: 四舍五入.
select
max(price) as max_price,
min(price) as 最低价格, # 起别名的时候, 可以不写单引号.
sum(price) 价格总和,
round(avg(price), 3) 均价,
count(price) 数据总条数
from product;
# 需求3: 面试题, count(1), count(列), count(*)的区别是什么?
/*
区别1: 是否统计null值.
count(1), count(*): 会统计null值.
count(列): 不会统计null值, 即: 只统计非空值的个数.
区别2: 效率问题.
count(主键列) > count(1) > count(*) > count(列)
*/
select count(1) from product; # 13
select count(pid) from product; # 13, 主键列
select count(category_id) from product; # 12, 普通列
select count(*) from product; # 13
# 需求4: 可以对 非数值列做 聚合操作, 但是结果可能不是你想要的.
select count(pname) from product; # 13
select max(pname) from product; # 香飘飘奶茶
select avg(pname) from product; # 0, 无意义.
# -------------- 案例6: DQL语句 单表查询-分组查询 --------------
/*
分组查询介绍:
分组查询 = 按照分组字段, 把整表数据 分成n个组, 然后在各个组内做 聚合操作等.
格式:
select 分组字段, 聚合函数 from 数据表名 where 组前筛选 group by 分组的列 having 组后筛选;
细节:
1. 根据谁分组, 就根据谁查询. 即: 分组查询的查询列只能出现 分组字段 + 聚合函数.
2. 扩展: 如果某列值 和 分组字段是一对一的关系(一一对应的), 则: 分组的时候即使没写这个字段, 也可以加上.
例如: 公司id 和 公司名一一对应, 我们按照公司id分组, 但是查询的时候, 可以把公司名这一列也加上.
3. 面试题: having 和 where的区别是什么?
where: 组前筛选, 后边不能跟聚合函数.
having: 组后筛选, 后边可以跟聚合函数.
*/
# 需求1: 统计各个分类商品的个数.
select * from product;
select count(pid) from product; # 13: 数据总条数
select category_id, count(pid) from product group by category_id; # 各个类别 商品总数
# 需求2: 如果要对某列值进行去重, 分组也是可以实现的. 例如: 查看所有的分类.
select distinct category_id from product;
select category_id from product group by category_id;
# 需求3: 统计各个分类商品的个数, 且只显示个数大于1的信息.
select
category_id, count(pid) total_cnt
from
product
group by
category_id
having
total_cnt > 1; # 组后筛选
# 需求4: 统计各个分类商品的总价格, 只统计价格在500(及其)以上的, 且只显示分组总金额在1000以上的信息, 按照总金额升序排列, 且只要前2条数据.
select
category_id, # 分组字段, 商品的分类id
sum(price) total_price # (各分类)商品总价格
from
product
where
price >= 500 # 组前筛选
group by # 具体的分组动作
category_id
having # 组后筛选
total_price > 1000
order by # 排序的
total_price
limit # 做分页的, 即: 筛选出指定条数的数据.
0, 2;
# -------------- 案例7: DQL语句 单表查询-分页查询 --------------
/*
分页查询介绍:
概述:
就是一次性的从数据表中, 获取指定条数的数据, 就叫: 分页查询.
好处:
1. 降低服务器的压力.
2. 降低浏览器端的压力.
3. 提高用户体验.
格式:
limit 起始索引, 数据条数;
细节:
1. SQL中, 表的每条数据都是有自己的编号(也叫: 下标, 角标, 索引)的, 且编号是从 0 开始的.
2. 要搞明白分页, 核心是理解如下的4个名词:
数据的总条数: count(主键列)
每页的数据条数: 产品经理, 项目经理, 你定.
每页的起始索引: (当前页数 - 1) * 每页的数据条数
总页数: (总条数 + 每页的数据条数 - 1) / 每页的数据条数 注意: 这里是整除, 即: 不要余数或者小数位.
(23 + 5 - 1) / 5 = 5
(25 + 5 - 1) / 5 = 5
(26 + 5 - 1) / 5 = 6
*/
# 1. 3条/页, 获取指定页.
select * from product limit 0, 3; # 第 1 页
select * from product limit 3, 3; # 第 2 页
select * from product limit 6, 3; # 第 3 页
# 2. 5条/页, 获取指定页.
select * from product limit 5; # 第 1 页, 语法糖, 不写起始索引, 默认从0开始.
select * from product limit 0, 5; # 第 1 页
select * from product limit 5, 5; # 第 2 页
select * from product limit 10, 5; # 第 3 页
# 天花板数, 向上取整, 即: 比这个数字大的所有数字中, 最小的那个整数.
select ceil(3.0); # 3
select ceil(3.1); # 4
扫一扫
769
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
