第一节 约束
1、概念
A、约束是什么
约束是作用于表中列上的规则，用于限制加入表的数据
约束的存在保证了数据库中数据的正确性、有效性和完整性
添加约束可以在添加数据的时候就限制不正确的数据，比如年龄是3000，数学成绩是-5分这样无效的数据，鸡儿保障数据的完整性
2、约束分类
A、非空约束：关键字是NOT NULL
保证列中所有的数据不能有null值
例如：id列在添加马花疼这条数据时就不能添加成功
B、唯一约束：关键字是UNIQUE
保证列中所有数据各不相同
例如：id列中三条数据的值都是1，这样的数据在添加时是绝对不允许的
C、主键约束：关键字是PRIMARY KEY
主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给每张表添加一个主键列用来唯一标识数据
例如：上图表中id就可以作为主键，来标识每条数据。那么这样就要求数据中id的值不能重复，不能为null值
D、检查约束：关键字是CHECK
保证列中值满足某一条件。
例如：我们可以给age列添加一个范围，最低年龄可以设置为1，最大年龄就可以设置为300，这样的数据才更合理些。
注意：MySQL不支持检查约束，这样是不是就没办法保证年龄在指定的范围内了？从数据库层面不能保证，以后可以在java代码中进行限制，一样也可以实现要求。
E、默认约束：关键字是DEFAULT
保存数据时，未指定值则采用默认值。
例如：我们在给english列添加该约束，指定默认值是0，这样在添加数据是没有指定具体值时就会采用默认给定的0。
F、外键约束：关键字是FOREIGN KEY
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。
外键约束现在可能还不太好理解，后面我们会重点进行详解。
3、非空约束
概念：非空约束用于保证列中多有数据不能有NULL值
语法：添加约束
语法：删除约束
4、唯一约束
概念：唯一约束用于保证列中所有数据各不相同
语法：添加约束
语法：删除约束
5、主键约束
概念：主键是一行数据的唯一标识，要求非空且唯一，一张表只能有一个主键
语法：添加约束
语法：删除约束
tips：主键约束一般和auto_increment一起用，自动增长
6、默认约束
概念：保存数据时，未指定值则采用默认值
语法：添加约束
语法：删除约束
注意：默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。
7、外键约束
概述：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。
语法：添加外键约束
语法：删除外键约束
第二节 数据库设计
1、数据库设计简介
A、软件研发步骤
B、数据库设计概念
数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型
建立数据库中的表结构以及表与表之间的关联关系的过程
有哪些表？表里有哪些字段？表和表之间有什么关系？
C、数据库设计步骤
需求分析：入局数据是什么？数据具有哪些属性？数据与属性的特点是什么？
逻辑分析：通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统，如下图就是ER（Entity/Relation）图：
物理设计：根据数据库自身的特点把逻辑设计转换为物理设计
维护设计：一是对新的需求进行建表，二是对表进行优化
D、表关系
一对一
一对多
多对多
2、表关系之一对多
一对多：如部门和员工，一个部门对应多个员工，一个员工对应一个部门
实现方式：在多的一方建立外键，指向一的一方的主键
案例
建表语句如下
-- 删除表 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) );
表结构模型图：
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 ); -- 建完表后，添加外键 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);
查看表结构模型
4、表关系之一对一
一对一：如用户和用户详情，一对一关系多用于表拆7分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提示查询性能
实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）
案例
建表语句如下：
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、多表查询有哪些？
连接查询
内连接查询：相当于查询AB交集数据
外连接查询：
左外连接查询：相当于查询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 , dept; -- 从emp和dept表中查询所有的字段数据，这里最后出的结果是笛卡尔积的结果，一共有（emp的条数*dept的条数）这么多条
select * from emp , dept where emp.dep_id = dept.did; -- 这条语句的查询猜是我们所需要的
2、内连接查询
语法：
-- 隐式内连接 SELECT 字段列表 FROM 表1,表2… WHERE 条件; -- 显示内连接，INNER可以省略 SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
内连接相当于查询AB交集数据
3、外连接查询
语法：
-- 左外连接 SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件; -- 右外连接 SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
解释说明：
4、子查询
概念：查询中嵌套查询，称嵌套查询为子查询
子查询根据查询结果不同，作用不同：单行单列 多行单列 多行多列
第四节 事务
1、概述
数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
事务是一个不可分割的工作逻辑单元
这些概念不好理解，接下来举例说明，如下图有一张表
张三和李四账户中各有100块钱，现李四需要转500元给张三，具体转账操作为：
第一步：查询李四账户余额
第二步：从李四账户金额-500
第三步：给张三账户金额+500
2、语法
开启事务
1 START TRANSACTION; 2 或者 3 BEGIN;
提交事务
1 commit;
回滚事务
1 rollback;
3、事务四大特征（常见面试题）
