扩展
upsert语法
原始数据
create table stu(id int primary key auto_increment,name varchar(20), #学生姓名gender varchar(10), #学生性别age int #学生姓名
) default charset=utf8;insert into stu values(null,'乔峰', '男' ,28),(null,'虚竹', '男' ,25),(null,'段誉', '男' ,23),(null,'灭绝', '女' ,38);
方式一：replace into table_name value(val1,val2,...);
replace into stu value(1,'萧峰','男',28); #如果id存在则直接修改
replace into stu value(9,'张无忌','男',26)
方式二：on duplicate key update col=val；
insert into stu value(10,'灵鹫宫宫主', '保密' , 30) on duplicate key update name ='小和尚' ,age=31; #id不存在则添加，存在则修改
执行第一次，添加
执行第二次，修改
多表关系
注意：多对多关系中一定有一个中间关系表，记录两张表之间的数据联系
多表约束之外键约束
外键（从键）
概念：假设有两张表A和B，B表的某列的值引用了A表的主键列的值，则B表的这一列称为B表的外键列 (Foreign Key)，其中A表称为主表，B表称为从表。
外键约束
外键约束的作用：
保证插入数据的准确性：从表中外键的值在主表主键中必须有对应的值
如果从表插入的外键值,在主表中不存在,就插入失败
保证删除数据的完整性：主表的主键值被从表外键引用之后，主表中对应的记录不能被删除
外键约束的缺点：
过分强调或者说使用主键／外键会平添开发难度
添加外键，也会降低数据增删改的性能
注意：实际开发，很少使用外键约束，而是从代码层面保持表之间的关系！
创建外键约束
写法一：建表的时候直接指定外键约束
语法：[constraint 外键约束名] foreign key (外键列) references 主表名(主键列名)
举例：
示例1：新建分类表category和商品表product
#创建分类表CREATE TABLE categorycid VARCHAR(32) PRIMARY KEY,#分类idcname VARCHAR(1OG)#分类名称DESC category;
#商品表CREATE TABLE products
(pid VARCHAR(32) PRIMARY KEY,pname VARCHAR(40),price
DOUBLEcategory_id VARCHAR(32),--商品分类id,外键#CONSTRAINT约束#REFERENCES引用CONSTRAINT fk_category_products FOREIGN KEY
(category_id) REFERENCES category(cid) #添加外键约束,category_id是外键列，cid是主键列-- [constraint 外键约束名] foreign key (外键列) references 主表名(主键列名)
)
写法二：建表后添加外键约束
-- alter table 外表名 add [constraint 外键约束名] foreign key (外键列) references 主表名(主键列名)
alter table 外表名 add constraint fk_category_products foreign key
(category_id) references category(cid)删除外键约束：
alter table table_name drop foreign key 外键约束名;
多表查询
测试数据
CREATE TABLE products
(id INT PRIMARY KEY AUTO_INCREMENT, -- 商品IDname VARCHAR(24) NOT NULL, -- 商品名称price DECIMAL(10, 2) NOT NULL, -- 商品价格score DECIMAL(5, 2), -- 商品评分，可以为空is_self VARCHAR(8), -- 是否自营category_id INT -- 商品类别ID
);
drop table category
CREATE TABLE category
(id INT PRIMARY KEY AUTO_INCREMENT, -- 商品类别IDname VARCHAR(24) NOT NULL -- 类别名称
);# 添加测试数据
INSERT INTO category
VALUES (1, '手机'),(2, '电脑'),(3, '美妆'),(4, '家居');INSERT INTO products
VALUES (1, '华为Mate50', 5499.00, 9.70, '自营', 1),(2, '荣耀80', 2399.00, 9.50, '自营', 1),(3, '荣耀80', 2199.00, 9.30, '非自营', 1),(4, '红米note 11', 999.00, 9.00, '非自营', 1),(5, '联想小新14', 4199.00, 9.20, '自营', 2),(6, '惠普战66', 4499.90, 9.30, '自营', 2),(7, '苹果Air13', 6198.00, 9.10, '非自营', 2),(8, '华为MateBook14', 5599.00, 9.30, '非自营', 2),(9, '兰蔻小黑瓶', 1100.00, 9.60, '自营', 3),(10, '雅诗兰黛粉底液', 920.00, 9.40, '自营', 3),(11, '阿玛尼红管405', 350.00, NULL, '非自营', 3),(12, '迪奥996', 330.00, 9.70, '非自营', 3);交叉查询（笛卡尔积，了解，慎用）
交叉连接关键字: cross join
显式交叉连接格式: select * from 左表 cross join 右表;
隐式交叉连接格式: select * from 左表,右表;
注意: 交叉连接了解即可,因为它本质就是一个错误,又叫笛卡尔积(两个表记录数的乘积)
注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表
SELECT * FROM products, category; -- 左表的没一行都会对应右表的没一行，基本上都是脏数据，总行数是两个表的行数乘积
内连接
内连接: 两个表的交集
内连接关键字: [inner] join ... on显式内连接格式: select * from 左表 [inner] join 右表 on 关联条件;隐式内连接格式: select * from 左表 , 右表 where 关联条件;注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表
# 显式内连接格式: select 字段名 from 左表 inner join右表 on 关联条件;
SELECTc.id cid,c.name cname,p.id pid,p.name pname
FROMproducts pINNER JOIN category c ON p.category_id = c.id;
# 隐式内连接格式: select 字段名 from 左表,右表 where 条件;
SELECTc.id cid,c.name cname,p.id pid,p.name pname
FROMproducts p,category c
WHEREp.category_id = c.id;
左外连接、右外连接
左外连接 内连接关键字: left [outer] join ... on
左外连接格式: select * from 左表 left [outer] join 右表 on 关联条件;
右外连接 内连接关键字: right [outer] join ... on
左外连接格式: select * from 左表 right outer join 右表 on 关联条件;
SELECT * FROM category c left JOIN products p ON p.category_id = c.id;
#这里有个注意的地方，左表是类别，只要右表跟左表有对应就全部对应，所以下面美妆等类别会出现多个对应的行，如果右边没有跟左表对应的，则会用null填充
SELECT * FROM products p left JOIN category c ON p.category_id = c.id;
全连接
两个表合上下(垂直)合并成一个表，如下图
union : 默认去重
union all: 不去重
注意：两个表的列要一一致，否则没发合并
(SELECT category_id,name FROM products LIMIT 0,1)
UNION
(SELECT category_id,name FROM products LIMIT 0,1);
(SELECT category_id,name FROM products LIMIT 0,1)
UNION ALL
(SELECT category_id,name FROM products LIMIT 0,1);
扩展：自关联（自连接查询）
解释: 两个表进行关联时，如果左表和右边是同一张表，这就是自关联。
注意: 自连接必须起别名!
-- 查询'江苏省'下所有城市
SELECTshi.id,shi.title,sheng.id,sheng.title
FROMareas shengJOIN areas shi ON shi.pid = sheng.id
WHEREsheng.title = '江苏省';-- 求每个月和上月的差额
SELECTc.month,c.revenue,c.revenue-u.revenue as diff
FROMsales cJOIN sales u ON c.month = u.month + 1;注意：该语句涉及到多表关联，如果数据量大的话会特别耗时，在实际使用中无法应用
子查询
子查询:在一个 SELECT 语句中，嵌入了另外一个 SELECT 语句，那么被嵌入的 SELECT 语句称之为子查询语句，外部那个SELECT 语句则称为主查询。
作用: 子查询是辅助主查询的。
子查询的结果充当主查询的条件
子查询的结果充当主查询的数据源(临时表)
子查询的结果充当主查询的查询字段
-- 需求: 求商品价格大于平均价的商品信息
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
-- 查询'河北省'下所有城市
select * from areas where pid = (select id from areas where title = '河北省');注意:子查询作为表使用必须加括号,同时起别名!!!
-- 需求: 查询'电脑'分类中商品的平均价格,要求结果中包含分类名称
思路1: 先合并商品表和分类表,再分组聚合
select c.name,avg(p.price) from products p JOIN category c ON c.id = p.category_id group by c.name having c.name = '电脑';思路2: 子查询作为表使用,调优角度: join之前能提前过滤就提前过滤
select c.name,avg(p.price) from products p JOIN (select * from category where name = '电脑') c ON c.id = p.category_id group by c.name ;
