Java Web之MySQL: 约束、数据库设计、多表查询、事务

这里我们来学习一下MySQL的几个知识点：约束、数据库设计、多表查询、事务。

1.约束

1.1 其他约束

我们来通过实例了解一下约束的用法。由于外键约束较为重要，我们等会单独拿出来说。这里先展示一下其他约束：

DROP TABLE
IF
	EXISTS emp;-- 如果有emp表，先将其删除
-- 创建如下的emp表:	
create table emp (
	id int primary key auto_increment,
	ename varchar(50) not null unique,
	joindate DATE not null,
	salary double(7,2) not null,
	bonus double(7,2) default 0
);
select * from emp;

insert into emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8888,5000);

-- 演示主键约束：非空且唯一
insert into emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8888,5000);

insert into emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8888,5000);

-- 演示默认约束
insert into emp(id,ename,joindate,salary) values(2,'李四','1993-10-20',8000);
-- 默认奖金为0,但如果写成null就不行
insert into emp(id,ename,joindate,salary,bonus) values(3,'王五','1993-10-20',8000,null);

-- 演示默认自动增长
insert into emp(ename,joindate,salary,bonus) values('赵六','1993-10-20',8000,null);
insert into emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1993-10-20',8000,null);
insert into emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1993-10-20',8000,null);

如果不按照相应的约束操作数据，就会弹出相应的提醒，并且操作不会成功。

1.2 外键约束

实例：

-- 1.创建dept部门表
CREATE TABLE dept ( id INT PRIMARY KEY auto_increment, dep_name VARCHAR ( 20 ), addr VARCHAR ( 20 ) );
-- 2.创建emp员工表
CREATE TABLE emp (
id INT PRIMARY KEY auto_increment,
NAME VARCHAR ( 20 ),
age INT,
dep_id INT,
-- 将员工表里的dep_id 作为外键 关联 部门表的id主键(fk_demp_dept为外键名)
CONSTRAINT fk_demp_dept FOREIGN KEY ( dep_id ) REFERENCES dept ( id ) 
);
INSERT INTO dept ( dep_name, addr )
VALUES
	( '研发部', '广州' ),
	( '销售部', '深圳' );
INSERT INTO emp ( NAME, age, dep_id )
VALUES
	( '研1', 20, 1 ),
	( '研2', 20, 2 ),
	( '研3', 20, 1 ),
	( '销1', 20, 2 ),
	( '销2', 20, 2 ),
	( '销3', 20, 2 );
SELECT
	* 
FROM
	dept;
SELECT
	* 
FROM
	demp;
-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_demp_dept;
-- 建完表后!!!!添加外键
ALTER TABLE emp ADD CONSTRAINT fk_demp_dept FOREIGN KEY ( dep_id ) REFERENCES dept ( id );

如下就是建立起外键约束的两个表，中间有线连接:

2.数据库设计

2.1 数据库设计的概念

2.2 数据库设计什么？

有哪些表？、表里有哪些字段？这些是较为容易根据用户需求自己设计的。我们来着重讲一下表与表之间的关系：这是需要我们自己进行逻辑的判断来设计的。

2.3 表与表之间的关系

2.3.1 一对一

相亲app中的用户表和用户详情表：

2.3.2 一对多(多对一)

员工表和部门表：

员工表和部门表我们前面在讲外键约束的时候已经讲过了，这里不在赘述。

2.3.3 多对多

订单表和商品表:

/*
	多对多：
		* 如：订单 和 商品
		* 一个商品对应多个订单，一个订单包含多个商品

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

-- 删除表
DROP TABLE IF EXISTS tb_orders_goods;
DROP TABLE IF EXISTS tb_orders;
DROP TABLE IF EXISTS tb_goods;


-- 订单表
CREATE TABLE tb_orders(
	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_orders_goods(
	id int primary key auto_increment,
	orders_id int,
	goods_id int,
	count int
);

-- 查询表
select * from tb_orders;
select * from tb_goods;
select * from tb_orders_goods;
-- 建完表后，添加外键
alter table tb_orders_goods add constraint fk_orders_id foreign key(orders_id) references tb_orders(id);
alter table tb_orders_goods add constraint fk_goods_id foreign key(goods_id) references tb_goods(id);

我们建立了第三方表: 订单商品中间表。并且添加了两个外键，分别关联商品表和订单表的主键。

总结:三种表关系的实现方式

3.多表查询

这里我们已经建好了如下的两张表：
员工表：

部门表：

如果我们直接查询两个表 ，

SELECT
	* 
FROM
	emp,
	dept;

会有笛卡尔积个情况 （笛卡尔积：A,B两个集合所有的组合）。
比如这里会出现6*4=24种情况。这就需要我们清除重复数据了：

--  查询emp和dept的数据，清除无效数据: emp.dep_id=dept.did
SELECT
	* 
FROM
	emp,
	dept 
WHERE
	emp.dep_id = dept.did;

3.1 内连接查询

3.1.1 隐式内连接

我们上面提到的清除重复数据的方法，其实就是隐式内连接:

3.1.2 显示内连接

-- 显示内连接(一般inner 省略不写)
select * from emp inner join dept on emp.dep_id=dept.did;
select * from emp join dept on emp.dep_id=dept.did;

3.2 外连接查询

-- 左外连接
-- 查询emp表所有数据和对应的部门信息
SELECT
	* 
FROM
	emp
	LEFT JOIN dept ON emp.dep_id = dept.did;
-- 右外连接
-- 查询dept表所有数据和对应的员工信息
SELECT
	* 
FROM
	emp
	RIGHT JOIN dept ON emp.dep_id = dept.did;
-- 相当于dept表的左外连接
SELECT
	* 
FROM
	dept
LEFT JOIN emp ON emp.dep_id = dept.did;

可以看出左外连接和右外连接的区别不大：不论是左外连接还是右外连接，只要把两个表的位置调换就能实现另一种。因此我们一般习惯用左外连接。

3.3 子查询

子查询的含义：
是指在查询中嵌套查询，我们就称嵌套的查询为子查询。
子查询分类：
实例：
这里的表还是我们刚开始讲多表查询时的emp表和dept表。

-- 1.查询猪八戒的工资(一步步查询)
SELECT
	salary 
FROM
	emp 
WHERE
	NAME = '猪八戒';
-- 2.查询工资高于猪八戒的员工信息(一步步查询)
SELECT
	* 
FROM
	emp 
WHERE
	salary > 3600;
-- 嵌套变为子查询:
SELECT
	* 
FROM
	emp 
WHERE
	salary > ( SELECT salary FROM emp WHERE NAME = '猪八戒' );
-- 查询财务部和市场部所有员工的信息
-- 1.(一步步查询)
SELECT
	did 
FROM
	dept 
WHERE
	dname = '财务部' 
	OR dname = '市场部';
-- 2.(一步步查询)
SELECT
	* 
FROM
	emp 
WHERE
	dep_id IN ( 2, 3 );
-- 嵌套变为子查询:
SELECT
	* 
FROM
	emp 
WHERE
	dep_id IN ( SELECT did FROM dept WHERE dname = '财务部' OR dname = '市场部' );
-- 查询入职日期是'2011-11-11'之后的员工信息和部门信息
-- 1.(一步步查询)
SELECT
	* 
FROM
	emp 
WHERE
	join_date > '2011-11-11';
-- 2.(一步步查询)
SELECT
	* 
FROM
	emp,
	dept 
WHERE
	emp.dep_id = dept.did;
-- 嵌套变为子查询:
SELECT
	* 
FROM
	( SELECT * FROM emp WHERE join_date > '2011-11-11' ) t1,
	dept 
WHERE
	t1.dep_id = dept.did;

4.事务

4.1 事务简介及实例

我们先来创建这样的一张account表：

我们先让李四给张三转500，但是在李四转钱和张三赚钱之间写个错误(就写:出错了…)

-- 转账操作
-- 1.查询李四的金额
-- 2.李四金额-500
UPDATE account 
SET money = money - 500 
WHERE
	NAME = '李四';
出错了...
-- 3.张三金额+500
UPDATE account 
SET money = money + 500 
WHERE
	NAME = '张三';

这时所有用户查询的都会是：只有李四少了500块钱，而张三没多。

但是当我们开启了事务之后：

-- 转账操作
-- 开启事务
BEGIN;
-- 1.查询李四的金额
-- 2.李四金额-500
UPDATE account 
SET money = money - 500 
WHERE
	NAME = '李四';
--出错了...-- 3.张三金额+500
UPDATE account 
SET money = money + 500 
WHERE
	NAME = '张三';

开启事务后，修改将变成临时性的。如果不提交或回滚，其他用户查询不到修改。也就是说，如果再出现错误，只有我们这里的查询是这个，并且会为我们提醒来让我们修正错误。

而其它用户的查询都是这个：

不会再让李四凭空少500块钱了！！！
了解了事务开启后，我们再来看一下事务的全过程:

-- 转账操作
-- 开启事务(开启事务后，修改将变成临时性的。如果不提交或回滚，其他用户查询不到修改)
BEGIN;
-- 1.查询李四的金额
-- 2.李四金额-500
UPDATE account 
SET money = money - 500 
WHERE
	NAME = '李四';
--出错了...-- 3.张三金额+500
UPDATE account 
SET money = money + 500 
WHERE
	NAME = '张三';
COMMIT;-- 提交事务(确认无误后提交事务)
ROLLBACK;-- 回滚事务(回滚到开启事务的时候)

4.2 事务的四大特征

4.3 MySQL的事务提交机制