day02 约束、数据库设计、多表查询、事务

代码示例

CREATE TABLE DEPT(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);


CREATE TABLE EMP(
ID INT PRIMARY KEY AUTO_CREMENT,
NAME VARCHAR(32),
AGE INT,
EMP_ID,
CONSTRAINT fk_emp_dept FOREIGN KEY(EMP_ID) REFERENCES DEPT(ID)
);

二、数据库设计

2.1 数据库设计简介

1. 软件的研发步骤

2. 表关系

一对一, 如用户和用户详情

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

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

2.2 一对多

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

案例:

经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）

代码示例

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;

-- 部门表
CREATE TABLE tb_dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);

-- 员工表
CREATE TABLE tb_emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,

-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
);

查看表结构模型图：

2.3 多对多

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

案例

经过分析发现，订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键指向两张表的主键

代码示例

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;

-- 订单表
CREATE TABLE tb_order(
id int primary key auto_increment,
payment double(10,2),
payment_type TINYINT,
status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
id int primary key auto_increment,
title varchar(100),
price double(10,2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
id int primary key auto_increment,
order_id int,
goods_id int,
count int,
CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id),
CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id),

);

查看表结构模型图：

2.4 一对一

实现方式在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

案例

使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。

代码示例

create table tb_user_desc (
id int primary key auto_increment,
city varchar(20),
edu varchar(10),
income int,
status char(2),
des varchar(100)
);


create table tb_user (
id int primary key auto_increment,
photo varchar(100),
nickname varchar(50),
age int,
gender char(1),
desc_id int unique,

-- 添加外键
CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);

查看表结构模型图：

三、多表查询

查询步骤

1. 确定要查询的列 select 列1,列2...

2. 这些列属于哪些表 from 表1,表2,...

3. 判断条件 where 条件

合起来: select 列1,列2... from 表1,表2,...where 条件;

3.1 分类

连接查询: 内连接查询、外连接查询

        内连接查询：相当于查询AB交集数据

        外连接查询：

                左外连接查询：相当于查询A表所有数据和交集部门数据

                右外连接查询：相当于查询B表所有数据和交集部分数据

子查询

3.2 案例

代码示例

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);

# 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);

-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');

-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null
);

执行下面的多表查询语句

select * from emp , dept; -- 从emp和dept表中查询所有的字段数据

从上面的结果我们看到有一些无效的数据，如孙悟空这个员工属于1号部门，但也同时关联的2、3、4号部门。所以我们要通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据，

select * from emp , dept where emp.dep_id = dept.did;

上面语句就是连接查询

3.3 连接查询

3.3.1 内连接查询

隐式内连接（常用）

SELECT 字段列表 FROM 表1,表2… WHERE 条件;

显示内连接

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件;

3.3.2 外连接查询

1. 左外连接

左外连接查询：相当于查询A表所有数据和交集部门数据

SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 条件;

查询表1 所有数据和对应表2中的信息

2.右外连接

右外连接查询：相当于查询B表所有数据和交集部分数据

SELECT 字段列表 FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;

查询表2 所有数据和对应表1中的信息

案例

1. 查询emp表所有数据和对应的部门信息（左外连接）

select * from emp left join dept on emp.dep_id = dept.did;

2. 查询dept表所有数据和对应的员工信息（右外连接）

select * from emp right join dept on emp.dep_id = dept.did;

3.3.3 自连接

mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。

案例

列出所有员工的姓名及其直接上级的名字?

select a.ename,b.ename from emp a left outer join emp b on a.empno = b.mgr;

3.4 子查询

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

案例: 查询工资高于猪八戒的员工信息。

第一步：先查询出来猪八戒的工资3600

select salary from emp where name = '猪八戒';

第二步：查询工资高于猪八戒的员工信息

select * from emp where salary > 3600;

第三步: 替换

select * from emp where salary > (select salary from emp where name = '猪八戒');

3.5 案例

代码示例

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;
-- 部门表
CREATE TABLE dept (
did INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

分析：

1. 员工编号，员工姓名，工资信息在emp 员工表中

2. 职务名称，职务描述信息在 job 职务表中

3. job 职务表和 emp 员工表是一对多的关系 emp.job_id = job.id

方式一：隐式内连接

SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description
FROM
emp,
job
WHERE
emp.job_id = job.id;

方拾二: 显示内连接

select
emp.id,
emp.ename,
job.jname,
job.description,
from
emp
inner
join
job
on
emp.job_id = job.id;

2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

分析：

1. 员工编号，员工姓名，工资信息在emp 员工表中

2. 职务名称，职务描述信息在 job 职务表中

3. job 职务表和 emp 员工表是一对多的关系 emp.job_id = job.id

4. 部门名称，部门位置来自于部门表 dept

5. dept 和 emp 一对多关系 dept.id = emp.dept_id

select
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc
from
emp,job,dept
where
emp.job_id = job.id and dept.id = emp.dept_id;

1. emp

2. dept

3. job

4. salarygrade

四、事务

4.1 概述

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。

事务是一个不可分割的工作逻辑单元。

4.2 事务的四大特征

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

隔离性（Isolation） :多个事务之间，操作的可见性

持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的