第二阶段 MySql 02

约束

（1）约束的概念

- 约束是作用于表中列上的规则，用于限制加入表的数据
- 约束的存在保证了数据库中数据的正确性、有效性和完整性

（2）约束的分类

案例：

CREATE TABLE if not exists emp(
id INT PRIMARY KEY auto_increment, -- 员工id,主键且自增长
ename VARCHAR(10) NOT NULL UNIQUE, -- 非空且唯一
joindate DATE NOT NULL, -- 非空
salary DOUBLE(7,2) NOT NULL, -- 非空
bonus DOUBLE (7,2) DEFAULT 0 -- 没有奖金默认为0
);
-- 删除表
DROP TABLE IF EXISTS emp;

INSERT INTO emp(id,ename,joindate,salary,bonus)values(1,'小红','2022-1-5',8000,5000);

-- 验证 auto_inceament 自增长
INSERT INTO emp(ename,joindate,salary,bonus)values('小花','2022-1-5',9000,3000);

SELECT id,ename,joindate,salary,bonus FROM emp;

SELECT id,ename,salary from emp WHERE ename LIKE '%红';

外键约束

-- 外键
-- 创建一个员工表，一个部门表
SELECT id,name,age,dep_id FROM emp1;
SELECT id,dep_name,addr FROM demp;

DROP TABLE IF EXISTS emp1;
DROP TABLE IF EXISTS demp;

CREATE TABLE IF NOT EXISTS emp1(
id INT PRIMARY KEY auto_increment,
name varchar(5) NOT NULL UNIQUE,
age INT NOT NULL,
dep_id int NOT NULL,

-- 添加外键，dep_id,关联 dept 表的id主键 constraint
CONSTRAINT fk_emp1_dept FOREIGN KEY(dep_id) REFERENCES demp(id)
);

CREATE TABLE IF NOT EXISTS demp(
id INT PRIMARY KEY auto_increment,
dep_name varchar(5) NOT NULL UNIQUE,
addr varchar(10)
);
-- 添加外键约束后，插入数据的顺序也需要注意
INSERT INTO emp1(name,age,dep_id) VALUES('张三',18,1);
INSERT INTO emp1(name,age,dep_id) VALUES
('李四',20,1),
('王五',19,1),
('赵六',21,1),
('孙七',20,2),
('周八',20,2);
INSERT INTO demp(dep_name,addr) VALUES('研发部','广州');
INSERT INTO demp(dep_name,addr) VALUES('销售部','深圳');

-- 删除外键
alter table emp1 drop FOREIGN KEY fk_emp1_dept;
-- 建完表后添加外键
alter table emp1 ADD CONSTRAINT fk_emp1_demp FOREIGN KEY(dep_id) REFERENCES demp(id);

 数据库设计

表关系 

一对一：

如：用户 和 与用户详情

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询功能

实现方式：在任意一方加入外键，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

一对多（多对一）：

如：部门 和 员工

一个部门对应多个员工，一个员工对应一个部门

实现方式：在多的一方建立外键，指向一的一方的主键

多对多：

如：商品和订单

一个商品对应多个订单，一个订单包含多个商品

实现方式：建立第三方中间表，中间表至少包含两个外键，分别关联两方主键

多表查询

内连接：

内连接查询语法：

  -- 内连接查询语法
  -- 隐式内连接
  -- SELECT 字段列表 FROM 表1,表2 WHERE 条件; 
  -- 显示内连接
  -- SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

  SELECT emp1.id,emp1.name,emp1.age,emp1.dep_id,demp.id,demp.dep_name FROM emp1,demp where emp1.dep_id = demp.id;
  -- 给表起别名
  SELECT t1.id,t1.name,t2.dep_name FROM emp1 t1,demp t2 where t1.dep_id = t2.id;
  
  -- 显示内连接
  SELECT t1.id,t1.name,t2.dep_name FROM emp1 t1 inner join demp t2 on t1.dep_id = t2.id;

   外连接查询语法：

sql
  -- 外连接查询语法
  -- 左外连接
  -- SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
  -- 右外连接
  -- SELECT 字段列表 FROM 表2 RIGHT [OUTER] JOIN 表2 ON 条件
  
  -- 左外连接
  SELECT emp1.id,emp1.name,emp1.age,demp.dep_name,demp.addr from emp1 left JOIN demp ON emp1.dep_id = demp.id;
  
  -- 右外连接
  SELECT emp1.id,emp1.name,emp1.age,demp.dep_name,demp.addr from emp1 RIGHT JOIN demp ON emp1.dep_id = demp.id

子查询

子查询概念：

查询中嵌套查询，称嵌套查询为子查询

子查询根据查询结果不同，作用不同：

  单行单列
  多行单列
  多行多列

-- 子查询
-- 查询emp1表中年龄大于王五人的信息
select emp1.id,emp1.name,emp1.age FROM emp1 WHERE emp1.age > (select emp1.age FROM emp1 WHERE name = '王五');

-- 查询emp1表中年龄大于王五的员工信息和部门信息
SELECT t1.id,t1.name,t1.age,t2.dep_name,t2.addr FROM (select * FROM emp1 t1 WHERE age > (SELECT emp1.age from emp1 where name = '王五')) t1,demp t2 WHERE t1.dep_id = t2.id ;