day04 mysql

day04_mysql

今日目标

排序查询
聚合查询
分组、分页查询
克隆表与数据
多表操作

DQL 查询语句

排序查询

# 语法格式
SELECT * FROM 表名 ORDER BY 排序字段 ASC 或 DESC;
 ASC 升序 (默认)
 DESC 降序
#根据价格降序排列
select * from product order by price desc;
#根据价格降序排列，根据分类降序排列
select * from product order by price desc,category_id desc;
#根据价格去重之后的降序排列
select distinct price from product order by price desc;

distinct * 去重所有字段内容一样的 比如3个去重后剩2个  其中2个重复

聚合函数

介绍的常用的五种聚合函数
- 案例 count 记录行数的案例

#3 查询分类为'c001'的所有商品价格的总和
select sum(price) as 总额 from product where category_id='c001';
#4 查询分类为'c002'所有商品的平均价格
select avg(price) as 平均价格 from product where category_id='c002';
#5 查询分类为'c002' 的商品最高的价格
select max(price) as 最高价格 from product where category_id='c002';
#6 查询分类为'c002' 的商品最小的价格
select min(price) as 最小价格 from product where category_id='c002';

分组查询

分组查询的定义

分组查询是指使用group by字句对查询信息进行分组。

案例

#1 统计各个分类商品的个数
select p.category_id,count(1) as 分类个数
from product p
group by p.category_id;
#2 统计各个分类商品的个数,且只显示个数大于1的信息
select p.category_id,count(1) as 分类个数
from product p
group by p.category_id
having count(1)>1

where 和 having 的区别
1. where 需要在from之后，group by 之前，不能用于分组条件，而having 需要在group by 之后
2. where 不能和聚合函数一起使用，having 搭配聚合函数使用

分页查询

定义

limit 分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。

# 例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。
select * from product limit 0,5;
# 第二页
select * from product limit 5,5;
# 第三页
select * from product limit 10,5;

（页数-1）*每页的个数,每页的个数

#查询product表的前5条记录
SELECT *  FROM product LIMIT 0,5
SELECT * FROM product limit 5

总结：基础查询关键字的执行顺序
克隆表和数据

定义：克隆表结构及表数据

案例_ like insert

# 创建一张和 t_customer 相同表结构的表，目标表和源表的约束不会丢失，也会被克隆。
CREATE TABLE t_customer_bak LIKE t_customer;
# 创建一张和 product 相同表结构的表信息
create table t_product like product;

# 将数据插入到 t_customer_bak 表中
INSERT INTO t_customer_bak SELECT * FROM t_customer;
# 克隆表数据
insert into t_product(pid,pname) select pid,pname from product;

案例_ create table 表名 as select

# 备份 product 中的数据到 t_product_bak
# 会丢失表约束
create table t_product_bak as select * from product;

多表操作

表与表之间的关系

一对一关系

人和身份证关系、和档案的关系

一对多关系

分类表和商品表之间的关系

此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

外键特点：

从表外键的值是对主表主键的引用。
从表外键类型，必须与主表主键类型一致。

创建外键约束定义：

alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

创建外键约束的目的：

保证数据的完整性。
案例：

# 创建数据表 分类和商品表
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100)  #分类名称
);

# 商品表
CREATE TABLE products (
  pid varchar(32) PRIMARY KEY,
  name VARCHAR(40) ,
  price DOUBLE ,
  category_id varchar(32)
);
# 添加外键约束
alter table products add constraint c_products_category_cid foreign key (category_id) references category(cid);

#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');

#2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');

#3 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');

#4 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) -- 失败,异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';

#6 删除外键约束
alter table products drop foreign key c_products_category_cid;

#7 如果外键约束名称忘记怎么办
## 查询一下外键约束的元数据表
select CONSTRAINT_NAME from information_schema.key_column_usage where TABLE_NAME='products';

外键约束报错信息。

外键约束总结：

外键约束主要是为了保证数据的完整性；
外键是在从表中建的，关联上主表的主键；
如果主表主键中不存在，从表中不允许插入不存在的数据的；
如果从表中还有指定的外键数据，主表中不能删除对应的记录。

多对多关系（可不掌握）

学生和课程之间的关系，一个学生可以选多门课，一门课可以被多个学生选择。

案例

# 产品和订单之间的关系
### 商品表[已存在]
### 订单表
create table `orders`(
  `oid` varchar(32) PRIMARY KEY ,
  `totalprice` double  #总计
);
### 订单项表
create table orderitem(
rid varchar(50),
  oid varchar(50),-- 订单id
  pid varchar(50)-- 商品id
);

alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);
###---- 商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);

#1 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES('p003','商品名称');
#2 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');
#3向中间表添加数据(数据存在)
INSERT INTO orderitem(pid,oid) VALUES('p001','x001');
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');
#4删除中间表的数据
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';
#5向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderitem(pid,oid) VALUES('p002','x003');
#6删除商品表的数据 -- 执行异常
DELETE FROM products WHERE pid = 'p001';

问题集锦

select distinct * from product 过滤是根据第一个字段还是所有字段

group by 加深理解

# 统计每个分类的个数 product
select category_id,count(1) from product group by category_id;

完毕。

posted @ 2020-09-21 17:16 十一vs十一阅读(132) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部