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称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

外键特点：

1. 从表外键的值是对主表主键的引用。

2. 从表外键类型，必须与主表主键类型一致。

• 创建外键约束定义：

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';

• 外键约束报错信息。

外键约束总结：

1. 外键约束主要是为了保证数据的完整性；

2. 外键是在从表中建的，关联上主表的主键；

3. 如果主表主键中不存在，从表中不允许插入不存在的数据的；

4. 如果从表中还有指定的外键数据，主表中不能删除对应的记录。

 

多对多关系（可不掌握）

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

• 案例

  # 产品和订单之间的关系
  ### 商品表[已存在]
  ### 订单表
  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;

  

完毕。