JavaWeb - Day2 - MySQL高级
约束
约束的概念
- 约束是作用于表中列上的规则，用于限制加入表的数据。
- 约束的存在保证了数据库中数据的正确性、有效性和完整性。
约束的分类
- Tips：MySQL不支持检查约束。
约束案例
实例
DROP TABLE IF EXISTS emp;
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
joindate DATE NOT NULL , -- 入职日期，非空
salary DOUBLE(7,2) NOT NULL , -- 工资，非空
bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);
-- 演示主键约束：非空且唯一
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);
-- 演示非空约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);
-- 演示唯一约束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);
-- 演示默认约束
INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);
-- 演示自动增长：auto_increment：当列是数字类型并且 唯一约束
INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);
SELECT * from emp;
非空约束
非空约束的概念
- 非空约束用于保证列中所有数据不能有NULL值。
非空约束的语法
唯一约束
唯一约束的概念
- 唯一约束用于保证列中所有数据各不相同。
唯一约束的语法
主键约束
主键约束的概念
- 主键是一行数据的唯一标识，要求非空且唯一.
- 一张表只能有一个主键.
主键约束的语法
默认约束
默认约束的概念
- 保存数据时，未指定值则采用默认值。
默认约束的语法
外键约束
外键约束的概念
- 外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性。
外键约束的语法
实例（创建员工表和部门表，并添加上外键约束）
/*
外键约束:
* 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性
-- 创建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-- 删除约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
*/
-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
-- 部门表
CREATE TABLE dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE 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 dept(id)
);
-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);
-- ------------------
select * from emp;
-- 删除外键
alter table emp drop FOREIGN key fk_emp_dept;
-- 建完表后，添加外键
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);
数据库设计
数据库设计简介
数据库设计概念
- 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
- 建立数据库中的表结构以及表与表之间的关联关系的过程。
数据库设计的步骤
- 需求分析
- 逻辑分析
- 物理设计
- 维护设计
数据库设计设计什么？
- 有哪些表
- 表里有哪些字段
- 表和表之间是什么关系
表关系有哪几种？
- 一对一
- 一对多
- 多对多
表关系之一对多
- 如：部门和员工
- 一个部门对应多个员工，一个员工对应一个部门
- 实现方式：在多的一方建立外键，指向一的一方的主键。
表关系之多对多
- 如：商品和订单
- 一个商品对应多个订单，一个订单包含多个商品
- 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。
模型（下方实例）
实例
/*
多对多：
* 如：订单 和 商品
* 一个商品对应多个订单，一个订单包含多个商品
实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
*/
-- 删除表
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
);
-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);
表关系之一对一
- 如：用户和用户详情
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。
- 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)。
数据库设计案例
专辑表曲目表短评表用户表4张表.- 一个专辑可以有多个曲目，一个曲目只能属于某一张专辑，所以专辑表和曲目表的关系是一对多。
- 一个专辑可以被多个用户进行评论，一个用户可以对多个专辑进行评论，所以专辑表和用户表的关系是多对多。
- 一个用户可以发多个短评，一个短评只能是某一个人发的，所以用户表和短评表的关系是一对多。
多表查询
笛卡尔积
- 取A，B集合所有组合情况
多表查询：从多张表查询数据
连接查询
内连接：相当于查询A B交集数据。 外连接: 左外连接：相当于查询A表所有数据和交集部分数据。 右外连接：相当于查询B表所有数据和交集部分数据。- 子查询
内连接
内连接查询语法
- 内连接相当于查询A B交集数据。
实例
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;
-- 多表查询
SELECT
*
FROM
emp,
dept;
-- 笛卡尔积 ： 有 A ,B两个集合 取 A,B所有的组合情况
-- 消除无效数据
-- 查询emp 和 dept 的数据 ，emp.dep_id = dept.did
-- 隐式内连接
SELECT
*
FROM
emp,
dept
WHERE
emp.dep_id = dept.did;
-- 查询 emp的 name， gender，dept表的dname
SELECT
emp. NAME,
emp.gender,
dept.dname
FROM
emp,
dept
WHERE
emp.dep_id = dept.did;
-- 给表 起别名
SELECT
t1. NAME,
t1.gender,
t2.dname
FROM
emp t1,
dept t2
WHERE
t1.dep_id = t2.did;
-- 显式内连接
select * from emp inner join dept on emp.dep_id = dept.did;
select * from emp join dept on emp.dep_id = dept.did;
外连接
外连接查询语法
- 左外连接：相当于查询A表所有数据和交集部分数据。
- 右外连接：相当于查询B表所有数据和交集部分数据。
实例
-- 左外连接
-- 查询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;
select * from dept left join emp on emp.dep_id = dept.did;
子查询
子查询概念
- 查询中嵌套查询，称嵌套查询为子查询
子查询根据查询结果不同，作用不同
- 单行单列
- 多行单列
- 多行多列
实例
-- 查询工资高于猪八戒的员工信息
select * from emp;
-- 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 = '猪八戒');
-- 查询 '财务部' 和 '市场部' 所有的员工信息
-- 查询 '财务部' 所有的员工信息
select did from dept where dname = '财务部' or dname = '市场部';
select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');
-- 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息
-- 查询入职日期是 '2011-11-11' 之后的员工信息
select * from emp where join_date > '2011-11-11' ;
select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;
多表查询案例
-- 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
*
FROM
emp;
SELECT
*
FROM
job;
-- 显式内连接
SELECT
emp.id,
emp.ename,
emp.salary,
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
;
-- 显式内连接
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
;
-- 3.查询员工姓名，工资，工资等级
/*
分析：
1. 员工姓名，工资 信息在emp 员工表中
2. 工资等级 信息在 salarygrade 工资等级表中
3. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
*/
SELECT
emp.ename,
emp.salary,
t2.*
FROM
emp,
salarygrade t2
WHERE
emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
分析：
1. 员工编号，员工姓名，工资 信息在emp 员工表中
2. 职务名称，职务描述 信息在 job 职务表中
3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
4. 部门名称，部门位置 来自于 部门表 dept
5. dept 和 emp 一对多关系 dept.id = emp.dept_id
6. 工资等级 信息在 salarygrade 工资等级表中
7. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
*/
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc,
t2.grade
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;
-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*
分析：
1. 部门编号、部门名称、部门位置 来自于部门 dept 表
2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
3. 使用子查询，让部门表和分组后的表进行内连接
*/
select * from dept;
select dept_id, count(*) from emp group by dept_id;
SELECT
dept.id,
dept.dname,
dept.loc,
t1.count
FROM
dept,
(
SELECT
dept_id,
count(*) count
FROM
emp
GROUP BY
dept_id
) t1
WHERE
dept.id = t1.dept_id
事务
事务简介
- 数据库的事务(Transaction)是一种机制、一个操作序列，包含了一组数据库操作命令。
- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。
- 事务是一个不可分割的工作逻辑单元。
事务操作
DROP TABLE IF EXISTS account;
-- 创建账户表
CREATE TABLE account(
id int PRIMARY KEY auto_increment,
name varchar(10),
money double(10,2)
);
-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);
UPDATE account set money = 1000;
select * from account;
-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500
-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';
-- 出现异常了...
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 查看事务的默认提交方式
SELECT @@autocommit;
-- 1 自动提交 0 手动提交
-- 修改事务提交方式
set @@autocommit = 0;
事务四大特征（A,C,I,D）
- 原子性(Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败。
- 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性(Isolation)：多个事务之间，操作的可见性。
- 持久性（Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
Comments | NOTHING