SQL语句应用
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) + 价格 去重.
条件查询:
格式:
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
排序查询:
# 格式: 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降序.
聚合查询:
/*
聚合函数:
1. count(): 统计数量
count()高级用法:
count(case when 条件 then 1 end) 别名
count(if (条件, 条件为真的值, 条件为假的值))
2. max(): 找出最大值
3. min(): 找出最小值
4. sum(): 数值列求和
5. avg(): 数值列求平均值
6. round(小数或结果是小数的聚合函数等, 保留小数位数)
*/
use day02;
show tables;
select
max(price) 最大值, min(price) 最小值, sum(price) 总价格, round(avg(price),3) 均价
from product;
select count(1) from product; # 13 为每条数据增加一个字段数据值1,只统计该列的数据值
select count(*) from product; # 13 统计每条数据的所有数据列
select count(category_id) from product; # 12 包含空值的普通列
select count(pid) from product; # 13 主键列
分组查询:
概述:按照分组字段,将字段值一样的数据,放到一起,然后做某些操作,一般是聚合操作
格式:
select 列1, 列2, 聚合函数....
from 表名
where 组前筛选
group by 分组字段1, 分组字段2...
having 组后筛选
order by 排序字段 asc/desc
limit 起始索引, 数据条数;
with rollup 分组后统计所有组满足聚合函数的值,并在输出的最后增加一行显示
细节:
-
分组查询,查询列只能出现 分组字段 和 聚合函数
(除非某列值和分组字段一一对应,分组查询可以查询该列)
-
分组查询一般要结合 聚合函数 一起使用,否则 无意义
select
category_id, sum(price) totle_price
from
product
where
price >= 500
group by
category_id
having
totle_price >= 1000
order by
totle_price
limit
0, 2;
# 大量数据用 分组方法
select category_id
from
product
group by
category_id;
# 少量数据(几十万条)用distinct
select distinct category_id from product ;
分页查询:
分页查询4要素;
总页数:方式1: select ceil(总条数 / 每页数据条数)
方式2: (数据总条数 + 每页数据条数 - 1) / 每页数据条数 '/'代表整除
每页的起始索引: (当前页数 - 1) * 每页数据条数
每页多少条数据: 产品经理,决策者
数据的总条数: select count(id) from 表名
多表查询:
连接查询:
1. 内连接:
# 格式1, 显式内连接.
# select * from A inner join B on 关联条件 where ......;
select kname, hname from hero h inner join kongfu k on hid = kid;
select hname, kname from kongfu k join hero h on kid = hid;
# 效果同上, 语法糖, inner可以省略不写.
# 格式2, 隐式内连接.
# select * from A, B where 关联条件......;
select hname, kname from hero h, kongfu k where hid = kid;
2.外连接:
左外和右外掌握一个就可以,左右互换位置就可以实现左右外连接
# 格式: 左外连接.
# select * from A left outer join B on 关联条件 where ......;
select * from hero h left outer join kongfu k on kid = hid;
# 格式: 右外连接.
# select * from A right outer join B on 关联条件 where ......;
# outer可以省略.
select * from hero h right join kongfu k on kid = hid;
子查询:
一个SQL语句的查询条件, 需要依赖于另一个SQL语句的查询结果, 里边的查询叫 子查询, 外表的查询叫 父查询(主查询)
select * from 表名 where 列名 in (select 列名 from 表名 ....);
# 方式1: 分解版
# step1: 找到降龙十八掌的kid
select * from kongfu where kname = '降龙十八掌';
# step2: 找到kongfu_id = 1 的英雄信息
select * from hero where kongfu_id = 1;
# 方式2: 合并版
select * from hero where kongfu_id = (select kid from kongfu where kname = '降龙十八掌');
# 方式3: 内连接
select *
from kongfu
join hero on kid = kongfu_id
where kname = '降龙十八掌';
自关联查询:
图解:
select
province.title, # 省名称
city.title, # 市名称
county.title # 县区名称
from
areas province
join
areas city on province.id = city.pid
join
areas county on county.pid = city.id
where county.id = '110110';