SQL语句操作_MySQL核心查询2(多表查询)


文章目录

  • SQL语句操作_MySQL核心查询2(多表查询)
  • 1 外键约束
  • 1.1 外键
  • 1.2 级联删除
  • 2 笛卡尔积
  • 3 内连接
  • 3.1 隐式内连接
  • 3.2 显式内连接
  • 4 外连接
  • 4.1 左外连接
  • 4.2 右外连接
  • 5 SQL JOINS 总结


1 外键约束

1.1 外键

  • 外键指的是在从表中与主表的主键对应的那个字段（就是说，数据表A中有一列，这列指向了另一张数据表B的主键，数据表A中的这列就是外键）
  • 使用外键约束可以让两张表之间产生一个对应关系，保证主从表的引用的完整性
  • 添加/删除外键仅针对从表进行
  • 关键字：FOREIGN KEY
  • 添加外键
/*
语法格式：
1. 新建表时添加外键
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字 段名)
2. 已有表添加外键
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
*/
-- 创建主表
create table department(
	id INT PRIMARY KEY auto_increment,
	dep_name VARCHAR(20),
	dep_location VARCHAR(80)
); 
-- 方式一，创建employee,添加外键约束
create table employee(
	eid INT PRIMARY KEY auto_increment,
	ename VARCHAR(20),
	sex CHAR(1),
	dept_id INT, -- 外键字段类型要与主表的主键字段类型保持一致
	-- 添加外键约束，外键指向部门表的主键
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
); 
-- 方式二，已有表添加外键
alter table emp3 add constraint emp3_dept_fk foreign key(dept_id) references department(id);
  • 删除外键
-- 删除外键
alter table emp3 drop foreign key emp3_dept_fk;
  • 注意
  • 从表外键类型必须与主表主键类型一致 否则创建失败
  • 添加数据时，应该先添加主表中的数据
  • 删除数据时，应该先删除从表中的数据，再删除主表中的数据

1.2 级联删除

  • 删除主表的同时删除从表
  • 级联删除：ON DELETE CASCADE
  • 示例
-- 创建employee,添加级联删除
create table employee(
	eid INT PRIMARY KEY auto_increment,
	ename VARCHAR(20),
	sex CHAR(1),
	dept_id INT, 
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
	-- 添加级联删除
	ON DELETE CASCADE
); 
-- 删除部门编号为2的数据
delete from department where id = 2;

2 笛卡尔积

  • 交叉连接查询,因为会产生笛卡尔积，所以基本不会使用
  • 语法格式
SELECT 字段名 FROM  表1, 表2;
  • 笛卡尔积

3 内连接

通过指定的条件去匹配两张表中的数据，匹配上就显示,匹配不上就不显示 （左右表都有的数据显示）

3.1 隐式内连接

  • 笛卡尔积 + where条件过滤
  • 语法格式
SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
  • 示例
-- 查询格力空调是属于哪一分类下的商品
SELECT
	p.pname,
	c.cname 
FROM
	products p,
	category c 
WHERE
	p.category_id = c.cid AND p.pname = '格力空调';

3.2 显式内连接

  • 使用 inner join …on 这种方式, 就是显式内连接
  • 语法格式
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
-- inner 可以省略
  • 示例
-- 查询所有商品信息和对应的分类信息
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
-- 查询鞋服分类下,价格大于500的商品名称和价格
SELECT
	c.cname,
	p.pname,
	p.price 
FROM
	products p
	INNER JOIN category c ON p.category_id = c.cid 
WHERE
	c.cname = '鞋服' AND p.price > 500;

4 外连接

4.1 左外连接

  • 使用 LEFT OUTER JOIN ，OUTER 可以省略
  • 特点
  • 以左表为基准，匹配右边表中的数据，如果匹配的上，就展示匹配到的数据
  • 如果匹配不到，左表中的数据正常展示，右边的展示为null
  • 语法格式
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
  • 示例
-- 查询所有商品信息和对应的分类信息
SELECT * FROM category c LEFT JOIN products p ON c.cid = p.category_id ;
-- 查询每个分类下的商品数量信息（分组查询）
SELECT
	c.cname,
	COUNT( p.pname) 
FROM
	category c
	LEFT JOIN products p ON c.cid = p.category_id 
GROUP BY
	c.cname;

4.2 右外连接

  • 使用RIGHT OUTER JOIN ，OUTER 可以省略
  • 特点
  • 以右表为基准，匹配左边表中的数据，如果匹配的上，就展示匹配到的数据
  • 如果匹配不到，右表中的数据正常展示，左边的展示为null
  • 语法格式
SELECT 字段名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 条件
  • 示例
-- 查询所有商品信息和对应的分类信息
SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

5 SQL JOINS 总结

sqlite数据库外键和索引区别 sql数据库外键语句_mysql