数据库进阶约束

-- 创建day02数据库
CREATE DATABASE day02;

-- 准备数据
CREATE TABLE goods (
  NAME VARCHAR(10),
  price DOUBLE,
  sales_volume INT,
  produced_date DATE,
  category VARCHAR(20)
);

INSERT INTO goods VALUES 
('华为P40',5999,1000,'2020-08-20','手机'),
('小米11',4999,5000,'2020-12-28','手机'),
('红米K30',2999,22000,'2020-03-11','手机'),
('糯米',8.99,200,'2016-06-08','食物'),
('米糊',7.99,30,'2013-11-22','食物'),
('iPhone 12',6799,12000,'2020-10-28','手机'),
('DELL 7590',8799,300,'2019-06-18','电脑'),
('立白洗衣粉',12.9,39000,'2018-02-13','日用品'),
(NULL,88,666,NULL,NULL),
('联想电脑',8799,700,'2017-03-13','电脑'),
('惠普电脑',8799,50,'2008-12-13','电脑');

-- /* DQL：聚合函数 */
-- 注意：聚合函数不能用在where 条件里面
-- SELECT 聚合函数(字段) FROM 表名;

-- 查询商品个数, COUNT统计时会忽略NULL值
-- 演示：统计price个数
SELECT COUNT(price) FROM goods

-- 演示：统计name个数，求出price的最大最小值、平均值，还有总和

SELECT COUNT(NAME),MAX(price) '最大值',MIN(price) '最小值',AVG(price) '平均值',SUM(price) FROM goods -- 因为有null，所以不计个数

-- COUNT(*) 表示统计所有行数，包含NULL的行
SELECT COUNT(*) FROM goods

-- 扩展用法：统计price大于1000的总个数
SELECT COUNT(*) FROM goods WHERE price > 1000

-- 扩展用法：统计手机 和 食物 的总个数？
SELECT COUNT(*) FROM goods WHERE category IN('手机','食物')

/*五个聚合函数分别是？*/
/*
count  求个数
sum    求总和
avg	求平均值
max	最大值
min	最小值
*/  

/* DQL：分组查询  */
-- 演示；按商品类型分组，查询所有字段
-- 注意：分组后会返回每组的第一条数据
SELECT * FROM goods GROUP BY category
-- 分组推荐：通常只查询分组字段

-- 注意：分组后通常是为了统计,分组后聚合函数操作每一组的数据
-- 演示：查询每种类型的商品的记录数
SELECT category,COUNT(*) FROM goods GROUP BY category

-- 演示：查询销量大于100的商品,按商品类型分组,统计每组的数量
SELECT category,COUNT(*) FROM goods WHERE sales_volume > 100 GROUP BY category

-- 演示：查询销量大于100的商品,按商品类型分组,统计每组的数量,并只显商品类型数量大于2的数据
SELECT category,COUNT(*) FROM goods WHERE sales_volume > 100 GROUP BY category HAVING COUNT(category) > 2 

/*
## having与where的区别

| 关键字             | 功能                                                         |
| ------------------ | ------------------------------------------------------------ |
| **where 子句** 	 1. 先过滤，再进行分组<br />
			2. where后面不能使用聚合函数      |
| **having 子句**     | 1. 先分组，再进行过滤<br />
			2. having后面可以使用聚合函数，having用在group by后面 |
*/

/* DQL：LIMIT分页查询 */
-- 查询商品表中数据，跳过前面2条，显示3条
SELECT * FROM goods LIMIT 2 , 3

-- 假设我们一每页显示3条记录的方式来分页，SQL语句如下：
-- 第一页: 跳过0条, 获取3条
SELECT * FROM goods LIMIT 3 -- 0可以忽略
-- 如果跳过的条数是0,可以省略

-- 第二页: 跳过3条, 获取3条
SELECT * FROM goods LIMIT 3 , 3

-- 第三页: 跳过6条, 获取3条
SELECT * FROM goods LIMIT 6 , 3

-- 第四页: 跳过9条, 获取3条
SELECT * FROM goods LIMIT 9 , 3

/* DCL：用户权限操作 */
-- 创建user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@localhost IDENTIFIED BY 'user1'

-- 创建user2用户可以在任何电脑上登录mysql服务器

-- 给user1用户分配对db1这个数据库的goods表,INSERT,UPDATE,DELETE,SELECT的权限
GRANT INSERT,UPDATE,DELETE,SELECT ON db1.`goods3` TO 'user1'@localhost

-- 查看权限
SHOW GRANTS FOR 'user1'@localhost

-- 撤销user1用户对db1的goods的select操作权限
REVOKE INSERT,UPDATE,DELETE,SELECT ON db1.`goods3` FROM 'user1'@localhost

-- 离职了删除'user1'@'localhost'
DROP USER 'user1'@localhost

/*
约束使用方式：
	一、可以在建完表之后再添加
	二、一般都是在创建表的时候直接加约束
*/

/* 约束：主键约束 */
-- 创建表学生表st5, 包含字段(id, name, age)将id做为主键
-- 创建表时添加主键
CREATE TABLE st5 (
	id INT PRIMARY KEY,  -- 演示1：这里需要设置主键约束
	NAME VARCHAR(20),
	age INT
);

INSERT INTO st5 VALUES (1, '迪丽热巴', 28);
INSERT INTO st5 VALUES (2, '古力娜扎', 38);
INSERT INTO st5 VALUES (3, '马尔扎哈', 18);
INSERT INTO st5 VALUES (4, '摩托罗拉', 26);

-- 演示2：添加重复主键数据观察报错效果，插入数据(4, 'xx罗拉', 26)
INSERT INTO st5 VALUES (4, 'xx罗拉', 26);-- 主键是唯一的，

-- 演示3：给主键设置为NULL会报错
INSERT INTO st5 VALUES (NULL, 'xx罗拉', 26);-- 主键不能为空的

-- 演示4：删除主键约束(了解)
ALTER TABLE st5 DROP PRIMARY KEY

-- 演示5：在已有表中添加主键约束(了解)
ALTER TABLE st5 ADD PRIMARY KEY(id)

/* 主键自增 */
-- 创建学生表st6, 包含字段(id, name, age)
CREATE TABLE st6 (
	id INT PRIMARY KEY AUTO_INCREMENT, -- 演示1：将id做为主键并自动增长
	NAME VARCHAR(20),
	age INT
);

-- 观察效果主键默认从1开始自动增长
INSERT INTO st6 (NAME, age) VALUES 
('刘德华', 60),
('张学友', 58),
('黎明', 52);


-- 演示2：修改自动增长的开始值(面试题)，修改为从100开始
ALTER TABLE st6 AUTO_INCREMENT = 100

-- 插入测试数据观察递增值是否从100开始
INSERT INTO st6 (NAME, age) VALUES ('郭富城', 60);


/* 约束：唯一约束 */
-- 创建学生表st7, 包含字段(id, name), 
CREATE TABLE st7 (
	id INT PRIMARY KEY,
	NAME VARCHAR(3) UNIQUE -- 演示1：设置name这一列唯一约束, 不能出现同名的学生
);

INSERT INTO st7 VALUES (1, '杨玉环');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '貂蝉');

-- 演示2：添加重复的数据 (4, '杨玉环')观察报错效果
INSERT INTO st7 VALUES (4, '杨玉环');-- Duplicate entry '杨玉环' for key 'NAME' 不能重复

/* 约束：非空约束 */
-- 创建表学生表st8, 包含字段(id,name,gender)
CREATE TABLE st8 (
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(10) UNIQUE NOT NULL, -- 演示3：设置name不能为NULL
	gender VARCHAR(2)
);

INSERT INTO st8 VALUES (1, '马云', '男');
INSERT INTO st8 VALUES (2, '马化腾', '男');
INSERT INTO st8 VALUES (3, '马蓉', '女');

-- 演示4：添加一条name为null的数据
INSERT INTO st8(NAME,gender) VALUES (NULL,'女')-- Column 'NAME' cannot be null

-- 扩展:一个字段可以同时添加多个约束，如添加非空和唯一约束，表stt(name)


-- 扩展: 主键与唯一约束的区别是什么?
答：主键是一定要int类型的，而且要唯一，不能为null
唯一，不能重复，但是null可以重复

/* 约束：默认值约束 */
-- 创建一个学生表st5，包含字段(id,name,address)， 
CREATE TABLE st9 (
	id INT,
	NAME VARCHAR(10),
	address VARCHAR(20) DEFAULT '广州'-- 演示5：设置address默认值是广州
);

-- 演示6：添加一条记录,使用默认值
INSERT INTO st9(id,NAME) VALUES (1,'林总')

-- 演示7：添加一条记录,不使用默认值
INSERT INTO st9 VALUES (1,'陈总','湛江')


-- ---------------------------------------------------------
/* 约束：外键约束 */
-- 准备数据
-- 创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 创建员工表
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);

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

-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES 
('张三', 20, 1), 
('李四', 21, 1), 
('王五', 20, 1), 
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);


-- 删除从表 employee
DROP TABLE employee;

-- 创建 employee 并添加外键约束
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
    -- 演示1：添加dep_id外键约束
    CONSTRAINT emp_id FOREIGN KEY(dep_id) REFERENCES department(id)
);


-- 正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES 
('张三', 20, 1), 
('李四', 21, 1), 
('王五', 20, 1), 
('老王', 20, 5),
('大王', 22, 5),
('小王', 18, 5);

-- 演示2：添加不正常的数据，例如：('王中王', 20, 6)
INSERT INTO employee (NAME, age, dep_id) VALUES 
('王中王', 20, 6) 

-- 演示3：删除外键约束(了解)
ALTER TABLE employee DROP FOREIGN KEY emp_id

-- 演示4：在已有表添加外键约束, 外键约束可以省略: CONSTRAINT 外键约束名 (了解)
ALTER TABLE employee ADD FOREIGN KEY(dep_id) REFERENCES department(id)

-- 演示5：删除外键约束，省略CONSTRAINT外键约束名 数据库会自动设置外键约束的名字,我们要到 `3信息` 中查找
ALTER TABLE employee DROP FOREIGN KEY `employee_ibfk_1`

-- ----------------------------------------------------

/* 约束：外键级联操作 */
-- 添加外键约束
ALTER TABLE employee ADD FOREIGN KEY(dep_id) REFERENCES department(id);

-- 疑问1：要把部门表中的id值2，改成5，能不能直接修改呢？
UPDATE department SET id=5 WHERE id=2;    -- 不能修改，外键没有级联操作

-- 疑问2：要删除部门id等于1的部门, 能不能直接删除呢？
DELETE FROM department WHERE id=1;  -- 不能删除

-- 删除表
DROP TABLE employee;

-- 添加表employee钙片【；‘
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
    -- 演示：创建表，添加dep_id外键约束，并设置级联更新和级联删除
    -- 注意：级联操作必须在这个位置设置，不支持在其他位置添加级联操作
	CONSTRAINT emp_id FOREIGN KEY(dep_id) REFERENCES department(id)
	ON UPDATE CASCADE ON DELETE CASCADE
);

-- 测试1：外键有级联操作后,修改主键将id=2改成id=5
UPDATE department SET id=5 WHERE id=2; -- 加了级联更新删除后

-- 测试2：外键有级联操作后,删除主键id=1
DELETE FROM department WHERE id=1;

-- 注意：一般在企业中不建议使用外键约束.当数据量大的时候外键约束效率低.阿里巴巴规范中规定"不得使用外键与级联，一切外键概念必须在应用层解决", 建议在Java层面控制外键的值
-- ----- - - -- - - - -  - -  ----   - --  --  -  - - - - - - - - -
数据库三大范式
第一范式：确保列的原子性，表中每一列都不可以再分割成更小的列。
（相当于能拆就拆了，每一列都有每一列的属性，原子性）

第二范式：在第一范式的基础上更进一层，要求表中的每列都和主键相关，保证实体的唯一性。如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。
（一张表只能描述一件事情（一个实体）比如：人->姓名、年龄 ）
（表中的每一个字段都依赖于主键（每个表都有一个主键））

第三范式：在第二范式的基础上更进一层，第三范式是确保每一列都和主键列直接相关，而不是间接相关，即限制列冗余性
（表与表之间的关系通过外键建立）

-- ----- - - -- - - - -  - -  ----   - --  --  -  - - - - - - - - -

/* 表关系：1对多创建 */
-- 删除表
DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
-- 创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);
-- 创建员工表
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	-- 演示：1对多表关系创建
	-- 创建原则：建立外键约束，在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键
	CONSTRAINT emp_id FOREIGN KEY(dep_id) REFERENCES department(id)
);
-- ----- - - -- - - - -  - -  ----   - --  --  -  - - - - - - - - -
-- ----- - - -- - - - -  - -  ----   - --  --  -  - - - - - - - - -

/* 表关系：多对多创建 */
-- 学生表
CREATE TABLE student (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL
);

-- 课程表
CREATE TABLE course (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO student VALUES (NULL, '张三'), (NULL, '李四'), (NULL, '王五');
INSERT INTO course VALUES (NULL, '语文'), (NULL, '数学'), (NULL, '英语');


-- 演示1：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
--       表名：stu_cou， 字段1：sid ， 字段2：cid， 创建sid和cid的复合主键，sid外键，cid外键
-- 数据库表名命名规则: 多个单词使用_连接
-- 复合主键: 多个字段合起来作为主键, 作为主键的多个字段的值合起来不能重复
CREATE TABLE stu_cou(
	sid INT,
	cid INT,
	PRIMARY KEY(sid,cid),
	FOREIGN KEY(sid) REFERENCES student(id),
	FOREIGN KEY(cid) REFERENCES course(id)
);

-- 演示2：插入符合业务的
-- 多对多表业务关系描述：一个学生可以选择多门课程，每门课程课可以被多个学生选择
-- 插入数据1：张三选择语文和数学
INSERT INTO stu_cou VALUES (1,1),(1,2)

-- 插入数据2：李四选择数学和英语
INSERT INTO stu_cou VALUES (2,2),(2,3)


/* 1对1关系：外键+主键 */
-- 主表
CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自动赠一
	NAME VARCHAR(20)
	
);
-- 从表
CREATE TABLE info(
	id INT PRIMARY AUTO_INCREMENT,
	address VARCHAR(20),
	use_name VARCHAR(10),
	werght DOUBLE,
	-- 直接把主键约束成外键。 主键也可以是外键。
	FOREIGN KEY(id) REFERENCES stu(id)
	  
);

/* 1对1关系：外键+唯一 */
-- 主表
CREATE TABLE stu(
   id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键
   NAME VARCHAR(20),
   info_id INT UNIQUE, -- 简历号  
   FOREIGN KEY(info_id) REFERENCES info(id)
);
-- 从表
CREATE TABLE info(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键
    address VARCHAR(20),
    use_name VARCHAR(10),
    weight DOUBLE
    
);