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

一、约束

1. 约束的概念

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

2. 约束的分类

3. 非空约束

• 添加约束

-- 创建表时添加非空约束
CREATE TABLE 表名(
	列名 数据类型 NOT NULL,
	...
);

或

-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

• 删除约束

ALTER TABLE 表名 MODIFY 字段名 数据类型;

4. 唯一约束

• 添加约束

-- 创建表时添加唯一约束
CREATE TABLE 表名(
	列名 数据类型 UNIQUE [AUTO_INCREMENT],
	-- [AUTO_INCREMENT]:当不指定值时自动增长
	...
);

或

-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

• 删除约束

ALTER TABLE 表名 DROP INDEX 字段名;

5. 主键约束

• 添加约束

-- 创建表时添加主键约束
CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
	...
);

或

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

• 删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

6. 默认约束

• 添加约束

-- 创建表时添加默认约束
CREATE TABLE 表名(
	列名 数据类型 DEFAULT 默认值,
	...
);

或

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

• 删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

7. 外键约束

• 添加约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
	列名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);

或

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

• 删除约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

8. 约束案例

-- 员工表
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
);

二、数据库设计

1. 表关系之一对多

2. 表关系之多对多

3. 表关系之一对一

三、多表查询

• 笛卡尔积：取 A，B 集合所有组合情况
• 多表查询：从多张表查询数据
  

1. 连接查询

a. 内连接

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2... WHERE 条件;

-- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

b. 外连接

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

2. 子查询

子查询概念：查询中嵌套查询，称嵌套查询为子查询

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

• 单行单列：作为条件值，使用 =、!=、>、< 等进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

• 多行单列：作为条件值，使用 in 等关键字进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

• 多行多列：作为虚拟表

SELECT 字段列表 FROM (子查询) WHERE 条件;

3. 案例

现有 4 张表如下：

-- 部门表
create table dept (
	id int primary key, -- 部门id
	dname varchar(50), -- 部门名称
	loc varchar(50) -- 部门所在地
);

-- 职务表
create table job (
	id int primary key, -- 职务id
	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 -- 最高工资
);

1. 查询员工编号，员工姓名，工资，职务名称，职务描述

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

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

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 emp.dept_id = dept.id;

3. 查询员工姓名，工资，工资等级

SELECT
	emp.ename,
	emp.salary,
	salarygrade.grade 
FROM
	emp,
	salarygrade 
WHERE
	emp.salary BETWEEN salarygrade.losalary 
	AND salarygrade.hisalary; -- 隐式内连接

4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

SELECT
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	salarygrade.grade 
FROM
	emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON emp.dept_id = dept.id
INNER JOIN salarygrade t1 ON emp.salary BETWEEN t1.losalary AND t1.hisalary;
-- 显式内连接

5. 查询部门编号，部门名称，部门位置，部门人数

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;

四、事务

1. 事务简介

2. 事务四大特征