1 DQL语言
DQL基本语法:
select [distinct]|列名|计算列 from 表名 [where 条件]
distinct: 对指定列进行去重操作
where 条件: 不写是查询表中所有行的数据, 写的话是查询满足条件的行数据 select和from之间是查询表中的指定字段
1.1 简单查询
/*
DQL基本语法:
select [distinct]|列名|计算列 from 表名 [where 条件]
distinct: 对指定列进行去重操作
where 条件: 不写是查询表中所有行的数据, 写的话是查询满足条件的行数据
select和from之间是查询表中的指定字段
*/
# 简单查询
# 1.查询所有的商品.
# *: 代表所有列名
SELECT *
FROM
product;
# 2.查询商品名和商品价格.
# 查询指定的列, 先写哪列就先展示哪列
# 最后的列名后不需要添加逗号
SELECT
price,
pname
FROM
product;
# 3.查询结果是表达式(运算查询):将所有商品的价格+10元进行显示.
# 列名/表名重命名 as 新名称
# 数值列和常数/数值列之间进行运算操作
SELECT
pname,
price + 10 AS new_price,
price + pid
FROM
product;
# 4. 去重查询 distinct
# distinct 前边不能有列名, 列名只能放在distinct后边(对指定的列进行去重)
SELECT
DISTINCT category_id
FROM
product;
# 对price, category_id列相同的行数据进行去重
SELECT
DISTINCT price, category_id
FROM
product;
1.2 条件查询
where 后边写的就是条件, 对表中的行数据进行过滤
-
比较查询
# 条件查询
# where 后边写的就是条件, 对表中的行数据进行过滤
# 比较查询
# > < >= <= = !=/<>(不等于)
# 数值列使用最多, 非数值使用 = !=
# 查询商品名称为"花花公子"的商品所有信息
SELECT *
FROM
product as p
WHERE
p.pname = '花花公子';
# 查询价格为800商品
SELECT
pname,
price
FROM
product
WHERE
price = 800;
# 查询价格不是800的所有商品
SELECT *
FROM
product
WHERE
price != 800;
SELECT *
FROM
product
WHERE
price <> 800;
# 查询商品价格大于60元的所有商品信息
SELECT *
FROM
product
WHERE price > 60;
# 查询商品价格小于等于800元的所有商品信息
SELECT *
FROM
product
WHERE price <= 800;
1.3 范围查询
# 列名 between 起始值 and 结束值
# 包含起始值和结束值
# 查询的数据是在一个连续的范围内
# 列名 in (值1, 值2, ...)
# 查询的数据是在列表中, 间断的范围
# 查询商品价格在200到1000之间所有商品
SELECT *
FROM
product
WHERE
price BETWEEN 200 AND 1000;
# 查询商品价格是200或800的所有商品
SELECT *
FROM
product
WHERE price in (200, 800);
1.4 逻辑查询
# 逻辑查询 关联多个条件
# and 多个条件同时成立
# or 多个条件一个成立
# not 取反 =800 not就是!=800
# () > not > and > or
# 查询商品价格在200到1000之间所有商品, 不包含200和1000
SELECT *
FROM
product
WHERE
price > 200
AND price < 1000;
# 查询商品价格是200或800的所有商品
SELECT *
FROM
product
WHERE
price = 200
OR price = 800;
# 查询价格不是800的所有商品
SELECT *
FROM
product
WHERE
NOT (price = 800);
1.5 非空查询
# 非空查询
# 判断是否是NULL值 is / is not
# 不允许使用 = / != 来判断是否是null值
INSERT INTO
product (pid, pname, price)
VALUES
(14, '华为', 6899);
# 查询没有分类的商品
SELECT *
FROM
product
WHERE
category_id is NULL;
# 查询有分类的商品
SELECT *
FROM
product
WHERE category_id is not null;
1.6 排序查询
# 排序查询
# order by 排序字段1 排序方式, 排序字段2 排序方式, ...
# 排序方式: asc 升序(默认,可以忽略不写); desc 降序
# 排序规则: 先以第一个字段进行排序, 第一个字段值相同的再以第二个字段进行排序
# 1.使用价格排序(降序)
SELECT *
FROM
product
ORDER BY price DESC ;
# 2.在价格排序(降序)的基础上,以分类排序(升序)
SELECT *
FROM
product
ORDER BY price DESC, category_id ASC;
1.7 聚合查询
# 聚合查询
# 使用聚合函数进行查询操作
# 聚合函数: 对列数据通过聚合操作实现计算得到一个值
# count(1|*|[distinct]列名): 统计条目数,个数, *:会统计null值 列名:不会统计null值 1:统计时不走索引
# sum(列名): 统计求和
# avg(列名): 统计平均值
# max(列名): 统计最大值
# min(列名): 统计最小值
# 1、查询商品的总条数
SELECT
COUNT(*) as product_num
FROM
product;
SELECT
count(category_id)
FROM
product;
SELECT
count(1)
FROM
product;
# 去重再计算
SELECT
count(DISTINCT category_id)
FROM
product;
# 2、查询价格大于200商品的总条数
SELECT
count(*)
FROM
product
WHERE price > 200;
# 3、查询分类为'c001'的所有商品的总和
SELECT
sum(price)
FROM
product
WHERE category_id = 'c001';
# 4、查询分类为'c002'所有商品的平均价格
SELECT
avg(price),
round(avg(price)), # 保留整数
round(avg(price), 2), # 保留x位小数
floor(avg(price))
FROM
product
WHERE category_id='c002';
# 5、查询商品的最大价格和最小价格
SELECT
max(price),
min(price)
FROM
product;
2.8 分组查询 ※※※
# 分组查询
# group by 字段1,字段2,... [having 条件]
# 如果有多个分组字段,所有分组字段的值都相同时才属于同一组 1 1 2 / 1 2 3 / 1 1 2
# having 条件: 对分组之后的结果表进行过滤
/*
注意点:
select和from之间只能出现分组字段,或者对某字段进行聚合结果(分组聚合操作)
having 后边的条件是 聚合结果的值
一般情况下分组和聚合函数一起使用, 分组聚合操作
分组查询没有和聚合函数使用, 对分组字段进行去重操作
有多少个分组,计算结果就是有多少条数据
*/
/*
where和having区别:
where是对数据表先进行过滤,然后再进行分组聚合操作, 条件中是不能出现聚合函数
hving是跟着group by后边, 对分组之后的结果进行过滤, 条件中是可以出现聚合函数
*/
/*
什么时候使用分组聚合操作?
需求中出现 各组/各商品, 每天/每个班级, 不同性别/不同班级 字眼 需要使用分组聚合
一定要找到分组字段和聚合字段
*/
#1 统计各个分类商品的个数
SELECT
category_id,
count(*)
FROM
product
GROUP BY category_id;
#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT
category_id,
count(*)
FROM
product
# WHERE category_id != 'c001' # 先对product进行where过滤,过滤子表再进行分组聚合操作
GROUP BY category_id
HAVING count(*) > 1;
SELECT
category_id,
count(*) as cnt
FROM
product
GROUP BY category_id
HAVING cnt > 1;
1.9 分页查询
# 分页查询
# limit m,n
# m: 行数据的索引值, 从0开始: 第1行数据的索引值是0, 如果m=0,可以省略 当前页的m=(当前页-1)*n
# n: 当前页的行数
# 每页有5条数据
# 查询第一页数据
SELECT *
FROM
product
LIMIT 0, 5;
SELECT *
FROM
product
LIMIT 5;
# 查询第二页数据
# m = (2-1)*5=5
SELECT *
FROM
product LIMIT 5,5;
# 查询第三页数据
# m = (3-1)*5=10
SELECT *
FROM
product LIMIT 10,5;
2 多表操作
-
概念
多张表之间进行关联,然后进行查询操作
-
表之间的关系
-
一对一关系 最简单
-
一对多关系 最常用
-
主表->一 主键字段(关联字段)
-
从表->多 外键字段(关键字段)
-
一对多就可以创建一个外键约束
-
表之间进行关联是可以没有外键约束的
-
通过表之间关联字段值相同进行关联
-
-
-
多对多关系 最复杂 交叉连接
-
-
外键约束
创建外键约束注意点:
创建从表时添加 CONSTRAINT FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名)
从表中的外键字段类型要和主表中的主键字段类型相同
# 外键约束 # 创建主表 一 主键 # 创建分类表 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), # category_id 从表外键字段 # category (cid) 主表名 (主键字段) CONSTRAINT FOREIGN KEY (category_id) REFERENCES category (cid) # 添加外键约束 ); # 在插入数据时,保证了数据的准确性。 # 向分类表中插入数据 INSERT INTO category (cid, cname) VALUES ('c001', '服装'); # 向商品表中插入数据, 没有类别id值 INSERT INTO products (pid, pname) VALUES ('p001', '商品名称'); # 向商品表中插入数据, 有类别id值, c001 INSERT INTO products (pid, pname, category_id) VALUES ('p002', '商品名称2', 'c001'); # 向商品表中插入数据, 有类别id值, c002 # 会发生报错 Cannot add or update a child row: a foreign key constraint fails (`bigdata_db`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`cid`)) # 触发外键约束, 插入数据的类别id在category表中不存在,不允许插入 INSERT INTO products (pid, pname, category_id) VALUES ('p003', '商品名称3', 'c002'); # 在删除数据时,保证了数据的完整性。 # 删除category表中c001类别 # 发生报错 annot delete or update a parent row: a foreign key constraint fails (`bigdata_db`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`cid`)) # 会触发外键约束, 从表中有c001的数据,是不允许删除主表中的c001数据 # 先删除从表中所有为c001的数据,再删除主表中c001的数据 # 删除从表c001数据 DELETE FROM products WHERE category_id = 'c001'; # 再删除主表数据 DELETE FROM category WHERE cid = 'c001';
3 多表查询
注意点:
内连接和外连接一定要有关联条件
关联条件可以有多个 and or 连接
关联条件是通过关联字段的值相等进行关联的, 两张表之间的关联字段名可以不同
多表之间如果相同的字段名, 查询此字段名一定要通过 表名.字段名 获取
-
创建表和插入数据
# 多表查询
# 创建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, '天山折梅手');
交叉连接
# 多表查询 关联条件字段是根据字段的值相同
# [了解]交叉连接 不需要考虑关联字段 笛卡尔积 2 * 2 = 4
SELECT *
FROM
hero,
kongfu;
SELECT *
FROM
hero
CROSS JOIN kongfu;
内连接
# 内连接 (inner) join 保留两表之间共有的数据
SELECT *
FROM
hero
JOIN kongfu ON hero.kongfu_id = kongfu.kid;
SELECT
hero.hid,
hero.hname,
kongfu.kname
FROM
hero
INNER JOIN kongfu ON hero.kongfu_id = kongfu.kid;
SELECT *
FROM
hero,kongfu
WHERE
hero.kongfu_id = kongfu.kid;
左连接和右连接
# 外连接 -> 左外连接 和 右外连接
# 左表: join前边的表 右表:join后边的表
# 左连接(left [outer] join):保留左表所有数据,右表能关联上的数据保留,关联不上的用null填充
SELECT *
FROM
hero LEFT JOIN kongfu ON kongfu_id = kid;
# 右连接(right [outer] join):保留右表所有数据,左表能关联上的数据保留,关联不上的用null填充
SELECT *
FROM
hero RIGHT JOIN kongfu ON kongfu_id = kid;
-
子查询
一个select的查询结果作为另一个select的一部分(条件, 表)
一个sql语句中出现了多个select语句,就是一个子查询语句
# 子查询
# 一个select的查询结果是作为另一个select的一部分(查询条件,表)
# 查询条件
# 查询“化妆品”分类上架商品详情
# 查询类别名称为化妆品的类别id
SELECT
cid
FROM
category
WHERE
cname = '化妆品';
# 查询类别id为c003的上架商品信息
SELECT *
FROM
products
WHERE
flag = 1
AND category_id = 'c003';
SELECT *
FROM
products
WHERE
flag = 1
AND category_id IN (SELECT
cid
FROM
category
WHERE
cname = '化妆品');
SELECT *
FROM
products
WHERE
flag = 1
AND category_id IN (SELECT
cid
FROM
category
WHERE
cname IN ('化妆品', '服饰'));
# 表
# 查询类别名称为化妆品的信息
SELECT *
FROM
category
WHERE
cname = '化妆品';
# 查询类别名称为化妆品的信息 和 商品表关联
SELECT
p.pid,
p.pname,
c.cname
FROM
products p
JOIN (SELECT *
FROM
category
WHERE
cname = '化妆品') c ON c.cid = p.category_id;
-
自查询
通过datagrip导入sql文件数据
# 自查询 -> 表中的字段存储一个层级关系(上下级关系, 省份id,城市id,区县id) # 左表和右表是同一张表, 需要给左表和右表起不同的别名 # 创建tb_areas CREATE TABLE tb_areas ( id VARCHAR(30) NOT NULL PRIMARY KEY, title VARCHAR(30), pid VARCHAR(30) ); # 查询省名为山西省的所有城市, 查询字段:城市id,城市名称,省份id,省份名称 # 查询山西省的省份id SELECT id FROM tb_areas WHERE title = '山西省'; SELECT c.*, p.title FROM (SELECT * FROM tb_areas WHERE pid IN (SELECT id FROM tb_areas WHERE title = '山西省')) c JOIN tb_areas p ON c.pid = p.id; # 自查询方式实现 SELECT c.id as c_id, c.title as c_name, c.pid, -- 省份id p.id, -- 省份id p.title as p_name FROM tb_areas c JOIN tb_areas p ON c.pid = p.id WHERE p.title = '山西省';