MySql数据库---单表查询，高级查询，外键约束，多表关系，建表原则

思维导图

模糊查询

select * from 表名 where 列名 like '匹配符';
符号: _ 表示一个任意字符
符号: % 表示0或者多个任意字符
# （1）查询商品名称含有"香"字的所有商品信息；
select * from product where pname like '%香%';
# （2）查询商品名称为三个字的商品信息；
select * from product where pname like '___';
# （3）查询商品名称以"斯"结尾，并且是三个字的商品信息；
select * from product where pname like '__斯';
# （4）思考1：查询以"香"开头，且是三个字的商品信息；
select * from product where pname like '香__';
# （5）思考2：查询以"香"开头的所有商品信息。
select * from product where pname like '香%';

非空查询

# 例如，使用非空查询来完成：
# （1）将商品名称为"香奈儿"的分类category_id修改为null；
update product set category_id = null where pname='香奈儿';
# （2）查询分类为空的商品信息；
# select * from product where category_id = null; -- 错误写法
select * from product where category_id is null; -- 正确写法
# （3）查询分类不为空的所有商品信息。
select * from product where category_id is not null; --

排序查询

（1）asc从小到大排列，即升序；
（2）desc从排序，即降序；
（3）默认按照列值从小到大进行排序（即asc升序）。
select * from product order by price asc;
select * from product order by price ;-- 如果是升序可以省略asc
# （2）按价格进行降序排序查询所有的商品信息；
select * from product order by price desc;
# （3）按照价格升序排序查询名称中有"想"字的所有商品信息。
select * from product where pname like '%想%' order by price;
# 扩展 : (4) 先按照价格排序,如果价格一样按照id降序
select * from product order by price desc,pid desc;

聚合查询[纵向计算]

-- max,min,avg,sum,count
# 例如，使用命令完成：
# （1）查询商品的总条数；
select count(pid) from product; -- 推荐
select count(1) from product;-- 推荐--但是所有列都为空也算一条记录.
select count(*) from product;-- 不推荐--效率低
select count(category_id) from product;-- 可以为空的列-- 不推荐
# （2）查询商品价格的最大值；
select max(price) from product;
# （3）查询商品价格的最小值；
select min(product.price) from product;
# （4）加入where条件后，查询价格大于200的商品总条数；
select count(pid) from product where price > 200;
# （5）查询分类c001中所有商品的总和；
select count(1) from product where category_id = 'c001';
# （6）查询分类为c002所有商品的平均价格。
select avg(price) from product where category_id = 'c002';

分页查询

（1）limit是分页查询的关键字；
（2）M为整数，表示从第几条索引开始，默认值是0；
（3）N为整数，表示查询多少条数据。
-- todo -----分页查询------------
# 例如，使用命令完成：
# （1）从商品信息中开始处查询5条数据；
select * from  product limit 0,5;
select * from  product limit 5;-- m可以省略,默认是0
# （2）从商品信息中索引值为5开始，共查询10条数据；
select * from product limit 5,10;
# （3）获取当前商品中价格最低的2件商品；
select * from product order by price limit 0,2;
# （4）思考：每页显示5条数据，要显示第3页的所有数据，该怎么做？
-- 计算: m = (3-1)*n = 10  n=5
select * from product limit 10,5;

分组查询

（1）分组字段名：表示按照指定字段的值进行分组；
（2）group by的作用是实现分组，默认可以实现去重操作；
（3）having分组条件是用来过滤分组后的数据。(4) 分组查询的结果只能显示分组字段和聚合函数.

-- todo 分组+聚合函数
# 例如，使用命令完成：
# （1）计算平均年龄值；
select avg(age) from person;
# （2）计算总人物个数；
select count(1) from person;
# （3）请统计出不同性别的人平均年龄值；
select gender,avg(age) from person group by gender;
# （4）请统计出不同性别的人总个数。
select gender,count(1) from person group by gender;

图解分组过程:

分组后过滤

# （3）请统计各个性别下人物的总个数，且只显示总个数大于3的信息。
selectgender,count(1)
from person group by gender having count(1)>3 ;
-----------------------------------------------
selectgender,count(1) ct
from person group by gender having ct > 3 ;-- ct 是别名,方便使用

where 和 having的区别

where: 是对表中的列进行条件过滤的.
having:可以对select后边计算后的列进行过滤的.注意:having不能单独使用.必须搭配group by
总结: where 和 having 不能互换.
建议: 能放在where中过滤的进行放在where中.如果有计算列的值则必须放在having中进行过滤.

外键约束

-- todo 验证 外键关联效果-----一对多表关系------一个分类下有多个商品
create table tb_category(cid int primary key auto_increment,cname varchar(20)
);

create table tb_product(id int primary key auto_increment,name varchar(29),price int,cid int,foreign key (cid) references tb_category(cid)
);

insert into tb_category values (1,'手机'),(2,'电脑');
insert into tb_product values  (1,'iphone16','5999',1);
insert into tb_product values  (2,'拯救者','9999',2);

-- todo 商品表不可以随意添加商品.--必须是符合分类的商品才行
insert into tb_product values  (3,'格力','2999',3); -- 添加失败...
-- todo 分类表不可以随意删除分类.只能删除没有产品的分类.
delete  from tb_category where cid = 1; -- 删除失败

多表建表原则

一对一

1:主键对应
2:在任意一张表中创建一个外键执行另一张表的主键.但是外键要加一个唯一约束.

-- todo 1.主键对应
create table user(id int primary key auto_increment,name varchar(20)
);
create table user_detail(id int primary key auto_increment,addr varchar(29),hobby varchar(20),foreign key(id) references user(id)
);

-- todo 2.在任意一张表中创建一个外键执行另一张表的主键.但是外键要加一个唯一约束.
create table user2(id int primary key auto_increment,name varchar(20)
);
create table user_detail2(id int primary key auto_increment,addr varchar(29),hobby varchar(20),uid int unique ,-- 必须唯一foreign key (uid) references user(id)
);

一对多

在多的一方建立外键列,指向1的一方的主键列

-- 场景: 一个分类下有多个商品.
create table tb_category( -- 分类表 --- 一 cid int primary key auto_increment,cname varchar(20)
);

create table tb_product( -- 商品表--- 多id int primary key auto_increment,name varchar(29),price int,cid int,foreign key (cid) references tb_category(cid)
);

多对多

创建第三张表.该表至少有两个外键列,分别指向两张表的主键.

-- 场景: 一个订单包含多个商品.多个商品可以存在于不同的订单中. -- 多对多场景.
create table goods(  -- 商品表goodsid int primary key auto_increment,gname varchar(20),gbrand varchar(20),gprice double
);
create table orders( -- 订单表orderid int primary key auto_increment,money varchar(20)
);

-- todo 中间表至少有两个外键列,分别指向两张表的主键.
create table orderitem(-- 订单条目表-- 用于记录订单和商品的关系.id int primary key auto_increment,item_goodsid int,-- 外键列指向商品表item_orderid int,-- 外键列指向订单表foreign key (item_goodsid) references goods(goodsid),foreign key (item_orderid) references orders(orderid)
);