Java：37-MySQL多表和外键和数据库设计

MySQL多表和外键和数据库设计

代码-------------------------------

对于一个表来说，可以有多个重复数据，且可以有多列

我们可以将他分开，用一个字段表示，这个字段称为外键

主表有主键，从表有外键

-- 创建部门表
-- 一方,主表
CREATE TABLE department(
	 id INT PRIMARY KEY AUTO_INCREMENT,   
	 dep_name VARCHAR(30),	
	 dep_location VARCHAR(30)
);

-- 创建员工表
-- 多方 ,从表
CREATE TABLE employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	age INT,
	dept_id INT
);


-- 添加2个部门 
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); 
SELECT * FROM department; 

-- 添加员工,dep_id表示员工所在的部门 
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); 

SELECT * FROM employee;

-- 插入一条 不存在部门的数据
INSERT INTO employee (ename,age,dept_id) VALUES('无名',35,3);
-- 明显可以插入，但是外键的那个表，并没有对应的3，所以就有了外键约束



/*
	外键约束
		作用: 外键约束可以让两张表之间产生有一个对应的关联,从而保证了主从表引用的完整性
	
	外键
		外键指的是在从表中与主表的主键对应的字段
		
	主表和从表
		主表 主键id所在的表 ,一的一方，一个部门有多个员工，只有一个主键，没有外键
		从表 外键字段所在的表,多的一方，多个员工同属于一个部门，可以有多个键
		如主键和多个外键，两者也可同时有
		
	
	添加外键约束的语法格式
		1.创建表的时候添加外键
		create table 表名(
			字段...
			[constraint] [外键约束名] foreign key(外键字段名) references 主表(主键字段)
			可以理解为[约束]外键的字段[去]参考主表的主键，即从表的该字段值，在主表的外键找不到，就会报错
			[constraint] [外键约束名]可以省略，即有[]都可以省略
		);
	
*/

-- 创建员工表 添加外键
CREATE TABLE employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	age INT,
	dept_id INT, -- 外键字段 指向了主表的主键
	-- 添加外键约束
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

-- 正常添加数据 (从表外键 对应主表主键)
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); 

-- 插入一条错误的数据
-- 添加外键约束之后 就会产生一个强制的外键约束检查 保证数据的完整性和一致性
INSERT INTO employee (ename, age, dept_id) VALUES ('错误', 18, 3); 


/*
	删除外键约束
	语法格式
		alter table 从表 drop foreign key 外键约束的名称
		若没有名称，那么就会通过主表和从表的联系，给你个名称

*/

-- 删除 employee表中 外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 创建表之后添加外键
-- 语法格式 alter table 从表 add CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES     
-- department(id)

-- 简写 不写外键约束名 自动生成的外键约束 employee_ibfk_1
ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id)

/*
	外键约束的注意事项
		1. 从表的外键类型必须与主表的主键类型一致
		2. 添加数据时,应该先添加主表的数据
		3. 删除数据的时候 要先删除从表中的数据
*/

-- 添加一个新的部门
INSERT INTO department(dep_name,dep_location) VALUES('市场部','北京');

-- 添加一个属于市场部的员工
INSERT INTO employee(ename,age,dept_id) VALUES('老胡',24,3);

/*
	级联删除
		指的是在删除主表的数据的同时,可以删除与之相关的从表中的数据
	
	级联删除
		on delete cascade
*/
-- 重新创建添加级联操作
CREATE TABLE employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	age INT,
	dept_id INT,
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
	-- 添加级联删除
	ON DELETE CASCADE
);

-- 添加数据
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); 

-- 删除部门编号为 2 的数据
DELETE FROM department WHERE id = 2;

/*
	表与表之间的三种关系
		一对多关系(1:n 常见): 班级和学生 部门和员工
		多对多关系(n:n 常见): 学生与课程 演员和角色
		一对一关系(1:1 了解): 身份证 和 人
*/

-- 一对多关系 省表与市表

-- 创建省表 主表 一的一方
CREATE TABLE province(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	description VARCHAR(20)
);

-- 创建市表 从表 中 外键字段指向 主表的主键
CREATE TABLE city(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	description VARCHAR(20),
	
	-- 创建外键 添加外键约束
	pid INT,
	FOREIGN KEY(pid) REFERENCES province(id)
);


-- 多对多关系 演员与角色

-- 演员表
CREATE TABLE actor(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);

-- 角色表
CREATE TABLE role(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);

-- 创建中间表
CREATE TABLE actor_role(
	-- 中间表的主键
	id INT PRIMARY KEY AUTO_INCREMENT,
	
	-- aid 字段 指向 actor表的主键
	aid INT,
	
	-- rid 指向 role表的主键
	rid INT
);


-- 添加外键约束

-- aid字段添加外键约束
ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(id);

-- rid字段添加外键约束
ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);
-- constraint 英文意思：约束
-- foreign key 英文意思：外键
-- references 英文意思：参考
-- [constraint] [外键约束名] foreign key(外键字段名) references 主表(主键字段)
-- 可以理解为[约束]外键的字段[去]参考主表的主键，即从表的该字段值，在主表的外键找不到，就会报错
-- [constraint] [外键约束名]可以省略，即有[]都可以省略
-- 主表主键，从表多键(可以有主键和多个外键，或者两者都有)
-- 当主表的从表在时，删除主表会报联系错误，因为是与从表有关联的
-- 但是删除从表就没事，因为从表可以看作参考主表的
-- 而主表删除必须要先删除从表，因为从表需要主表，若删除主表，则没什么参考，即报错
-- 若不想删掉从表而去删掉主表的话，可以设置不检查该联系，set foreign_key_checks = 0，但删除后最好改成1
-- 因为删除主表的话，从表就没有参考了，在从表添加数据时，主表必须有对应字段数据，否则就是参考空的，即也会报错
-- 当然了，若从表有数据，那么主表也不可以删除对应从表的数据，否则也会报错，除非你设置no delete cascade
-- 那么就可以在删除对应主表数据时，对应的从表数据也会删除，但是该从表必须设置no delete cascade
-- 若有些从表没有设置，有些设置了，且对应数据一样，那么删除主表对应数据时
-- 因为有些从表没有设置，那么就会报错，因为在没设置前，是不可以这样删除的，若不想有报错，则必须都设置
-- no delete cascade(英文意思与实际操作不服，即编程不要依赖与英文意思)写在设置外键的格式的后面
-- 并没有自占一行，即不用逗号隔开
-- 注意：这里的报错都是因为检查联系的原因
-- 若set foreign_key_checks = 0，那么上述的报错原因都会没有
-- check(checks) 英文意思：检查
-- 删除外键的方式alter table 表名 drop foreign key 外键名，必须指定外键名，因为可以有多个外键
-- 而alter table 表名 drop primary key，后面不用加什么名，除了没有名字外，更重要的是主键只有一个
-- 但是，若有自增修饰的话，就不可以删除主键，若删除，那么自增就无意义了，因为他是修饰主键的
-- 所以有自增修饰时，就不可以删除主键，除非先去掉自增，但要去掉自增的话，其实只能修改字段
-- 如用modify和change来对字段进行修改
-- 修改时可以添加一些修饰，或者直接不写自增修饰，那么就相当于删除了自增修饰了
-- 但是若有了主键的话，那么就不可以在修改时，顺便添加主键了(主键唯一)
-- 这时不添加主键，相当于没有写自增修饰了，那么就删除自增修饰了
-- 否则可以，因为对于创建表或者add添加的主键
-- 只有not null作用在字段那里，因为主键本身就占一行，如primary key(字段名)，自占一行
-- 因为唯一(只能有一个主键，而unique可以一直加，因为不唯一，可以有多个unique)
-- 那么就不可以再增加(add)主键修饰或者修改(modify和change)后面加修饰
-- 因为修改(modify和change)后面加修饰时，也会算作加上修饰
-- 外键一般在alter中写在drop后面，且drop不可操作多条删除，即不能同时删除主键和外键或者其他东西
-- 所以drop是单向删除的，并且后面也不可以接drop(如drop，drop)
-- 添加外键的方式，与添加主键的方式差不多，一般在alter中写(创建表时添加外键的方式)在add后面
-- 其中add是单向添加，即不可同时添加主键和外键或者其他东西，与drop类似
-- 但添加时可以接add(如add，add)，实现多个添加，即可以得出add和drop实际上是操作一行的(即操作不了自增修饰)
-- 但drop对于主键的删除，却不用指定主键名，因为主键唯一，当然也可以添加和删除字段，因为可以操作一行
-- cascade 英文意思：大量
-- 对于表来说，主键和外键都可以不用，这两者只是用来方便表操作的，即可以只设置外键，而不用主键
-- 但通常都会设置主键，因为代表了这个表的行信息的主体
-- 且主键不能为外键，若强行设置的话，就会报错
-- 记住，在创建外键时，外键类型必须与对应的主键类型一样，如int只对应int，而不会对应同样是整数的tinyint
-- 对于sqlyog可视化界面，有一个架构设计器，可以很直观的看出有联系的表，直接拖过去就可以了
-- 像什么一对多和多对多和一对一，中一是主表，多是从表，即可以理解为一个主键数据可以对应多个从表数据
-- 比如市场部(1是主键值)有很多人(2，3，4，从表的数据)，即1-2，1-3，1-4

将一方相同的浓缩成一个字段来对应

将两方相同的分别浓缩成字段来对应

不用浓缩，可以直接对应（主键），但也可以浓缩（外键）对应

/*
	多表查询的语法
		select 字列表段 from 表名列表;

*/

CREATE DATABASE db3_2 CHARACTER SET utf8;

#分类表 (一方 主表)
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);

#商品表 (多方 从表)
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2),		#是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  -- 添加外键约束
  FOREIGN KEY (category_id) REFERENCES category (cid)
);

#分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','汽车');


#商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视机',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空调',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰箱',4500,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动裤',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋衣',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大宝',200,'1','c003');


/*
	笛卡尔积
	
*/

-- 多表查询 交叉连接查询 的查询结果会产生 笛卡尔积 是不能够使用的.
SELECT * FROM products ,category;

/*
	1.内连接查询
	2.外连接查询
*/

/*
	内连接查询
		特点 通过指定的条件 去匹配俩张表中的内容, 匹配不上的就不显示
		
		隐式内连接
			语法格式: select 字段名... from 左表,右表 where 连接条件
			
		显式内连接
			语法格式: select 字段名... from 左表 [inner] join 右表 on 连接条件
			inner 可以省略
			他们与外连接不同的是，作用基本一样，并没有偏向于一方，只是隐藏和显示的区别而已，即单词的多和写的区别，但是显示比较好看一点以及好观察，所以建议使用显示
	
*/

-- 1.查询所有商品信息和对应的分类信息
-- 隐式内连接
SELECT * FROM products , category WHERE category_id = cid;

-- 2.查询商品表的商品名称 和 价格,以及商品的分类信息
-- 多表查询中 可以使用给表起别名的方式 简化查询
SELECT  
	p.`pname`,
	p.`price`,
	c.`cname`
FROM products p,category c WHERE p.`category_id` = c.`cid`;

--  查询 格力空调是属于哪一分类下的商品 
SELECT 
	p.`pname`,
	c.`cname`
FROM products p, category c WHERE p.`category_id` = c.`cid` AND p.`pid` = 'p002';


-- 1.查询所有商品信息和对应的分类信息
-- 显式内连接
SELECT 
* 
FROM products p 
INNER JOIN category c ON p.`category_id` = c.`cid`;

-- 2.查询鞋服分类下,价格大于500的商品名称和价格
/*
	查询之前要确定几件事情
		1.查询几张表 products &  category
		2.表的连接条件 p.`category_id` = c.`cid`; 从表.外键 = 主表.主键
		3.查询所用到的字段  商品名称  价格
		4.查询的条件 分类 = 鞋服,  价格 > 500
*/

SELECT 
	p.`pname`,
	p.`price`
FROM products p 
INNER JOIN category c ON p.`category_id` = c.`cid`
WHERE p.`price` > 500 AND c.`cname` = '鞋服';

/*
一般来说，在操作主键和外键时，我们通常是以主键为主体的（在左边）
	外连接查询
		左外连接
			语法格式 关键字 left [outer] join 
				select 字段名 from 左表 left join 右表 on 连接条件
			左外连接的特点
				以左表为基准 匹配右表中的数据 如果能匹配上就显示
				如果匹配不上, 左表中的数据正常显示,右表数据显示为null
		
		右外连接
			语法格式 关键字 right [outer]  join
				select 字段名 from 左表 right join 右表 on 条件
			右外连接的特点
				以右表为基准 匹配左表中的数据 如果能够匹配上 就显示
				如果匹配不到 右表中的数据就正常显示 左表显示null
由于外连接通常需要条件作为匹配基准，而不是单纯操作笛卡尔积，所以必须存在条件，如on，或者where，否则报错，可以自己测试一下
		
*/

-- 左外连接查询
SELECT 
* 
FROM category c 
LEFT JOIN products p ON c.`cid` = p.`category_id`;

--  查询每个分类下的商品个数
/*
	1.查询的表
	2.查询的条件 分组 统计
	3.查询的字段 分类 分类下商品个数信息
	4.表的连接条件
*/

SELECT 
	c.`cname`,
	COUNT(p.`pid`)
FROM
-- 表连接
category c  LEFT JOIN products p ON c.`cid` = p.`category_id`
-- 分组
GROUP BY c.`cname`;


-- 右外连接查询
SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;
-- 在之前我们查询表时，都是一个表，如select * from 表名
-- 但是可以用逗号隔开(与select后面查询的字段一样，可以用逗号隔开)
-- 如select * from 表1,表2，查询两个表，但是这样的查询
-- 按照意思来说，应该必须都显示出来，且放在一个整体表里
-- 于是就会考虑两表的数据，那么写在前面的如表1，会先考虑
-- 即表1的所有数据在查询时会靠左边，即表2会在右边，多个表查询时，也按照这样的顺序排列
-- 但是若表1有两个数据(1,2)，表2有两个数据(3,4)，那么就会出现(1,3)，(1,4)，(2,3)，(2,4)
-- 由此看出，表2的数据虽然会靠右边，但是会先将表的数据查询出来，其中会结合其他表数据
-- 以此类推，多个表也是一样的顺序
-- 虽然上面的是查询*(所有的)
-- 但其实我们指定字段时，还是会显示4条语句，导致出现只查表1的第一个字段的值时，出现两个1和2
-- 因为使用这个时，虽然没查表2的数据，但是还是会根据他数据的量来进行查询，即是这样的(1,)，(1,)，(2,)，(2,)
-- 还是查了四个
-- 而我们将上面的这样的顺序叫做笛卡尔积，即两表的相乘(2*2=4)，即必查4个数据
-- 之所以需要多表查询，是因为需要一个联系的表
-- 且可以得到表的字段值(由于表多了，就需要"表名.字段"了，当然可以用as来设置表名，设置后，只能用设置的表名了)
-- 即相当于得到表的字段(变量)
-- 而不让出现笛卡尔积，则需要where来进行条件的查询(有对应的字段可以操作了，如id相等，即连接起来id相等的)
-- 或者其他可以来让数据的数量进行过滤的操作都可以，如分组等等
-- 但分组是最后分组的，先进行表连接，就如先进行where条件判断一样
-- 由于where条件是先判断(内存里进行过滤)再查询数据(对于想象来说可以理解为最后一次查询)
-- 可以想象成先将数据查询好，再过滤，然后将过滤的查询给你看
-- 而加上and或者or，则可以想象成一层一层的过滤，即查询过滤，再查询过滤等等
-- 那么原来不相同id的，都会过滤掉，当然也可以减少数量，如对于表2来个查询3的数据，那么就出现了(1,3)，(2,3)
-- 就两个数据了，即不是3的都过滤掉了
-- 当然在进行多表查询时，表名可以用as来设置，若各表的字段有相同的，那么查字段时
-- 就需要表名.字段(多表最好都这样)，否则的话，就会报错，不知道查哪个表的字段
-- 若字段名在其他表中没有，那么可以省略"表名."，单表基本省略
-- 多表查询与是否添加外键无关，即没有外键的也可以进行多表查询，就与单表查询一样
-- 但是有外键的基本上都要多表查询，因为这样查询的结果可以更加知道联系
-- 在mysql里最好用单引号，因为mysql的一些东西，会将双引号解释成其他符号(如ANSI_QUOTES)，而单引号不会
-- 对于多表，有如下方式查询
-- 内连接查询-----------------------
-- 隐式内连接
-- 语法格式: select 字段名... from 左表,右表 where 连接条件
-- 显式内连接
-- 语法格式: select 字段名... from 左表 [inner] join 右表 on 连接条件
-- 可以理解为左表结合右表在条件上的查询，其中on可以不写，相当于查询两表时，没进行任何过滤一样
-- 因为有无条件都没关系，且也可以加上where条件语句
-- 通常写在on后面，若on没有，则写在右表后面
-- inner 可以省略
-- inner 英文意思：里面的
-- join 英文意思：结合
-- on 英文意思：在...上，后面也可以写上where条件语句，近一步加条件
-- 外连接查询-----------------------
-- 左外连接
-- 语法格式 关键字 left [outer] join 
-- select 字段名 from 左表 left join 右表 on 连接条件
-- 左外连接的特点
-- 以左表为基准，匹配右表中的数据，如果能匹配上就显示
-- 如果匹配不上(根据条件来匹配)，左表中的数据正常显示，右表数据显示为null
-- 上述的匹配是根据条件来匹配的，可以理解为，先让条件进行过滤，然后将左表没操作的数据显示出来
-- 并且对应没有与之匹配的右表数据都为null
-- 右外连接
-- 语法格式 关键字 right [outer]  join
-- select 字段名 from 左表 right join 右表 on 条件
-- 右外连接的特点
-- 以右表为基准，匹配左表中的数据，如果能够匹配上就显示
-- 如果匹配不上，右表中的数据就正常显示，左表显示null
-- 上述的匹配是根据条件来匹配的，可以理解为，先让条件进行过滤，然后将右表没操作的数据显示出来
-- 并且对应没有与之匹配的左表数据都为null
-- 由于必须要有条件匹配，即on必须写（inner不用，left和right需要），也可加上where条件来加更多条件，分组操作也可加
-- 先连接后分组，因为分组通常写在最后面
-- outer 英文意思：外面的

-- on虽然与where类似，但是他代表的含义是连接表的信息关联，可以说成是语义化的where，只是可以与where共存

/*
	子查询 subQuery
		一条select语句的结果，作为另外一条select语句的一部分
	
	子查询的特点
		子查询必须要放在 小括号中
		子查询作为父查询的条件使用(更多的时候)
*/

-- 查询价格最高的商品信息
-- 1.查询出最高的价格
SELECT MAX(price) FROM products ; -- 5000

-- 2.根据最高价格 查出商品信息
SELECT * FROM products WHERE price = 5000;

-- 使用一条SQL完成 子查询方式
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products );

/*
	子查询分类
		where型子查询: 将子查询的结果 作为父查询的 比较条件使用.
		from型子查询: 将子查询的查询结果作为一张表使用
		exists 型子查询: 查询结果是单列多行的情况,可以将子查询的结果作为父查询的 in函数中的条件使用
*/

-- 子查询作为查询条件

-- 1. 查询化妆品分类下的 商品名称 商品价格
-- 查询出化妆品分类的 id
SELECT cid FROM category WHERE cname = '化妆品'; -- c003

-- 2.根据化妆品id 查询对应商品信息
SELECT 
	p.`pname`,
	p.`price`
FROM products p
WHERE p.`category_id` = (SELECT cid FROM category WHERE cname = '化妆品');

-- 查询小于平均价格的商品信息
-- 1.求出平均价格
SELECT AVG(price) FROM products; -- 1866

-- 2.获取小于平均价格的商品信息
SELECT 
* 
FROM products
WHERE price < (SELECT AVG(price) FROM products);


-- from型子查询方式 

-- 查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称
SELECT * FROM category;

SELECT 
	p.`pname`,
	p.`price`,
	c.cname
FROM products p 
-- 注意 子查询的结果作为一张表时,要起一个别名 否则无法访问表中的字段
INNER JOIN (SELECT * FROM category) c ON p.`category_id` = c.cid 
WHERE p.`price` > 500;

/*
	子查询的结果是单列多行, 作为父查询的 in 函数中的条件使用
	语法格式
		select 字段名 from 表名 where 字段 in(子查询);
*/
-- 查询价格小于两千的商品,来自于哪些分类(名称)

-- 1.查询小于两千的商品的 分类id
SELECT DISTINCT category_id FROM products WHERE price < 2000;

-- 2.根据分类的id 查询 分类的信息
SELECT * FROM category 
WHERE cid IN 
(SELECT DISTINCT category_id FROM products WHERE price < 2000);


-- 查询家电类 与 鞋服类下面的全部商品信息
-- 1.首先要获取 家电类和鞋服类 分类id
SELECT cid FROM category WHERE cname IN('家电','鞋服');

-- 2.根据 分类id 查找商品信息
SELECT 
	* 
FROM products WHERE category_id IN
(SELECT cid FROM category WHERE cname IN('家电','鞋服'));

-- 子查询的总结
-- 1.子查询如果是一个字段(单列) ,那么就在where后面做条件
-- 2.如果是多个字段(多列) 就当做一张表使用 (要起别名，否则执行一般会报错)
-- 子查询分类
-- where型子查询：将子查询的结果作为父查询的比较条件使用
-- 可以将结果用括号括起来，如select * fron 表 where id = (select id from 表 where id = 4);
-- 其中括号里的查询要返回一条数据，因为id只能有一个数据，否则会报错
-- from型子查询：将子查询的查询结果作为一张表使用
-- 如select * from 表1，select * from 表2 inner join (select * from 表) c on 表2.id = c.id
-- 其中括号里的看成一个虚拟的表，即必须要设置名字，如c，否则无法调用该表字段，但是打出这个c时，不会给你提示
-- 因为这始终是一个虚拟的表
-- 但还是要记住，无论是什么情况下的条件(如on，where，having，group by等等)
-- 若表有字段相同的，则不可以单独使用
-- 需要表名.字段名，因为必须要确定是谁的字段，否则报错
-- 若一个表的话，可以直接用字段名，当然也可以用表名.字段名，且设置了表名的话，必须用设置的表名
-- exists型子查询：查询结果是单列多行的情况，可以将子查询的结果作为父查询的in函数中的条件使用
-- 如select * from 表1 where 表1.id in (select 表2.id from 表2);
-- 其中若对表名进行设置，那么只能用该名进行字段的访问了，且括号里的查询的数据，只能是一列多行
-- 多行的可以有相同的，因为就如条件一样的重合了而已，如a>4 或者(且) a>4，那么实际上就是a>4的结果
-- 因为是过滤的，过滤过了，再次过滤一样的，当然什么操作都没进行
-- exists 英文意思：存在

数据库三范式

三范式指的就是数据库设计的一个规则

作用 就是为了创建 冗余较小 结构合理的数据库

范式 就是设计数据库的要求(规范)

第一范式(1NF)：满足最低要求的范式

第二范式(2NF)：在满足第一范式的基础之上，进一步满足更多的规范

第三范式(3NF)：以此类推

反三范式---------------------

指的是通过增加冗余或者重复数据来提高数据库的读性能

浪费存储空间，节省查询时间(以空间换时间)

冗余字段-----------------------------

某一个字段属于一张表，但是他又在多张表中都有出现

第一范式----------------------------------

第二范式----------------------------------

第三范式----------------------------------

其中100和900可以被推导出来（20 * 50 = 100，30 * 30 = 900）

反三范式----------------------------------

其中若订单表没有关于名字的字段，那么当要查询对应名字的信息时，需要连表查询

影响效率，用时间换空间，即节省了磁盘空间，但查询（效率）速度变慢

而加上有名字的字段时，虽然冗余（重复，造成磁盘空间变大，在以前磁盘空间是非常值钱的，而现在没有之前值钱了）

但是提高了效率（速度）

一般来说，几乎任何的直接操作表的地方，都可以使用（select …）来进行操作，比如显示字段中，就可以这样使用