数据库应用

约束

概念：

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的正确性、完整性、有效性、唯一性。

非空约束：not null

听名字就能理解，被非空约束的列，在插入值时必须非空。

创建表时添加约束

CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name不能为空
);

INSERT INTO stu (id, name) VALUES (1,"zs")

INSERT INTO stu (id, name) VALUES (2,NULL)

ALTER TABLE stu MODIFY NAME VARCHAR(20);

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL

UPDATE stu SET name = "lisi" WHERE id = 2

创建表完后，添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;-- -- 如果表中含有null 则添加失败

删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

唯一约束：unique

当为字段指定唯一约束后，那么字段的值必须是唯一的。

唯一约束可以有NULL值，但是只能有一条记录为null. 有待研究！

在创建表时，添加唯一约束

CREATE TABLE stu1(
	id INT,
	phone_number VARCHAR(20) UNIQUE -- 手机号
);


INSERT INTO stu1 (id,phone_number) VALUES (1,"123")

INSERT INTO stu1 (id,phone_number) VALUES (2,"123")

ALTER TABLE stu1 DROP INDEX phone_number;

ALTER TABLE stu1 MODIFY phone_number VARCHAR(20) UNIQUE; 

UPDATE stu1 set  phone_number = "456" WHERE id = 2

删除唯一约束

ALTER TABLE stu1 DROP INDEX phone_number;
在表创建完后，添加唯一约束
ALTER TABLE stu1 MODIFY phone_number VARCHAR(20) UNIQUE; ---- 如果表中含有相同数据则添加失败

主键约束：primary key

主键(PRIMARY KEY)是用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。

主键要求这一行的数据不能有重复且不能为空。

注意：
- 含义：非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
在创建表时，添加主键约束

create table stu2(
	id int primary key,-- 给id添加主键约束
	name varchar(20)
);

INSERT INTO stu2 (id,name)  VALUES (1,"zs")

INSERT INTO stu2 (id,name) VALUES (NULL,"lisi")

ALTER TABLE stu2 DROP PRIMARY KEY; 

ALTER TABLE stu2 MODIFY id INT PRIMARY KEY;

INSERT INTO stu2 (id,name)  VALUES (2,"lisi")

删除主键

ALTER TABLE stu DROP PRIMARY KEY; --注意：如果id有null值则删除失败
创建完表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

自动增长：

MySQL提供了主键自动增长的功能，这样用户就不用再为是否有主键是否重复而烦恼了。

当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。

使用 auto_increment 可以来完成值得自动增长，自动增长的列必须是主键。

创建表时，添加主键约束，并且完成主键自增长

create table stu3(
id int primary key auto_increment,-- 给id添加主键约束和自动增长
name varchar(20)
);
INSERT INTO stu3 (id,name)  VALUES (1,"zs")

INSERT INTO stu3 (id,name)  VALUES (2,"lisi")

INSERT INTO stu3 (id,name)  VALUES (NULL ,"wangwu")

DELETE FROM stu3 WHERE id = 3

ALTER TABLE stu3 MODIFY id INT;

ALTER TABLE stu3 MODIFY id INT AUTO_INCREMENT;

删除自动增长

ALTER TABLE stu MODIFY id INT;
添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束：foreign key

一个表的一列（a列）指向另一个表的b列（主键），那么这一列（a列）就是外键约束。

比如人和车，把两者保存在一张表里显然是不合理的；

而保存成两张表，则需要想办法表示出二者的关系，即人拥有车，而车属于人。

create table person (
    id int primary key,
    pname varchar(30)
);

create table car(
    id int primary key,
    cname varchar(30),
    pid int ,
    foreign key(pid) references person(id)
);

-- 如果person表中没有人，我们是无法直接添加car表的
INSERT INTO car  VALUES (1,"奔驰",1),(2,"宝马",2),(3,"奥迪",2)

INSERT INTO person  VALUES (1,"zs"),(2,"lisi"),(3,"ww")

-- 如果在car表中添加一个persion表中不存在的人，也是不能添加的，这就是外键约束的作用
INSERT INTO car  VALUES (4,"五菱",4)

SELECT * from person 

SELECT * from car

DROP TABLE person 

ALTER TABLE car DROP FOREIGN KEY car_ibfk_1;

就是让表与表产生关系，从而保证数据的正确性。

定义外键所在的表叫从表,从表所依赖的数据表是主表

外键可以为null

从表依赖于主表，我是否可以直接删除主表？

SELECT * from person 

SELECT * from car

DROP TABLE person

解决方法：先删除从表，在删除主表，让主表不在被引用即可
删除外键

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

创建表时，可以添加外键

create table 表名(
外键列,
-- constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 定义外健名称是为了 后期方便删除外健约束

创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

级联操作. 外键的建立是多的一方指向一的一方。

添加级联操作语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;

1. 分类：
1. 级联更新：ON UPDATE CASCADE

2. 级联删除：ON DELETE CASCADE

3. 级联操作慎用

数据库的设计

多表之间的关系

一对一(了解)：
- 如：人和身份证
- 分析：一个人只有一个身份证，一个身份证只能对应一个人。
一对多(多对一)：
- 如：部门和员工
- 分析：一个部门有多个员工，一个员工只能对应一个部门
多对多：
- 如：学生和课程
- 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

实现关系

一对多(多对一)：

如：部门和员工

一个员工只能选一个部门，一个部门可以被多个员工选择。

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

多对多：

如：学生和课程

实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，

这两个字段作为第三张表的外键，分别指向两张表的主键

3. 一对一(了解)：

* 如：人和身份证

实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。也可以两张表拥有共同主键，主键表示相同含义。

数据库设计的范式

概念：

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，

这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：

第一范式（1NF）、
第二范式（2NF）、
第三范式（3NF）、
巴斯-科德范式（BCNF）、
第四范式(4NF）
第五范式（5NF，又称完美范式）。

一般都遵循前三个，我们只学习前三个。

多表查询：

定义：

多张表的查询，语法和单表查询差不太多。

查询语法：

select
		列名列表
from
		表名列表
where....

多表查询概述：

-- 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

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

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

-- 多表查询
SELECT * FROM emp,dept;
/*如果按照上述方式查询，其实会把两个表的所有可能性都列举出来，查询出两个表的结果集，
这个结果集我们称之为笛卡尔积 */

笛卡尔积：

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，

第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

比如：集合A={a,b} , 集合B={0,1,2}

两个集合的笛卡尔积为：

{ (a,0)，(a,1)，(a,2)，(b,0)，(b,1)，(b,2) }

说白了，就是有A集合，有B集合，我们取AB集合的所有组合情况。那么AB组合情况就是2*3共6种情况。

如下图是笛卡尔积的结果：

会发现有好多无用的数据，比如红线框起来的部分。

多表查询分类：

内连接查询 --- A表和B表相交的部分
- 隐式内连接
- 显式内连接
外连接查询
- 左外连接
- 右外连接
子查询

隐式内连接查询：

使用where条件清除无用数据即可。

sql语句;

-- 内连接查询
-- 隐式内连接   显式内连接

-- 隐式内连接：使用where条件清除无用数据。
-- 1.查询所有员工信息和对应部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;-- 提示出来的引号 可加可不加

-- 2.1 查询员工表名称，性别，部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 2.2 简化写法 给表名起别名  t1 是别名
SELECT 
	t1.`name`,
	t1.`gender`,
	t2.`name` 
FROM 
	emp t1,
	dept t2 
WHERE
	t1.`dept_id` = t2.`id`;

显式内连接查询：

语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
[inner] 可选关键字
显式内连接sql语句：

-- 1 查询所有员工信息和对应的部门信息
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
-- 1.1 INNER 可以省略
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.id;
-- 1.2 也可以起别名
SELECT * FROM emp t1 JOIN dept t2 ON t1.`dept_id` = t2.`id`;

内连接查询注意事项：
1.从哪些表中查询数据，

2.条件是什么，判断什么是有效数据，什么是无效数据

3.查询哪些字段，我们会选择我们需要的字段来查询

外连接查询：

概念：

若干个表中，查询某个表的全部信息的同时在查询另一张表的交集信息。

左外连接：

语法：
select 字段列表 from 表1 left [outer] join 表2 on 条件；-- 表1是左表，查询的是左表所有数据以及和表2其交集部分。
[OUTER]关键字可选
SELECT * FROM emp LEFT JOIN dept ON emp.`dept_id` = dept.`id`;

右外连接：

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；-- 表2是右表查询的是右表所有数据以及和表1其交集部分。

子查询：

概念：
- 查询中嵌套查询，称嵌套查询为子查询
- 一个select中嵌套另一个select，也属于子查询

子查询基本使用

查询最高工资的员工信息
- 第一步：先查询最高工资是多少 9000
- SELECT MAX(salary) FROM emp;-- 先查询最高工资是多少 9000
- 第二步：再查询员工信息并且薪资=9000的
- SELECT * FROM emp WHERE emp.`salary` = 9000;--
子查询完成上述操作
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

事务

事务基本介绍：

概念：
事务（transaction）指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部不成功
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。没有事务前会遇到的问题：张三和李四各有1000元，张三给李四转500，那么张三应该剩余500，李四剩余1500。但是如果在张三给李四刚转完钱之后，程序出问题了，会导致李四收不到这笔500元的款项造成张三损失了500，不翼而飞了，而李四余额还是1000，不变

这次操作涉及2条sql语句操作

给张三账户减少500
给李四账户增加500

-- 举例：
CREATE TABLE account (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (10),
  balance DOUBLE
) ;

-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);

SELECT * FROM account;

-- 张三给李四转500
-- 1.张三账户-500
UPDATE account SET balance = balance -500 WHERE NAME ='zhangsan';

出错啦...

-- 2. 李四账户+500
UPDATE account SET balance = balance + 500 WHERE NAME ='lisi';

UPDATE account SET balance = 1000; -- 数据还原到1000

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。
如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。
- 开启事务：start transaction
- 结束事务：commit（提交）或 rollback（回滚）
在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务。

commit表示提交，即：事务中的多条SQL语句所做出的影响会持久化到数据库中；或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了。
操作：

-- 开启事务
START TRANSACTION;
-- 张三给李四转500
-- 1. 张三账户-500
UPDATE account SET balance = balance -500 WHERE NAME ='zhangsan';
-- 出错啦...
-- 2. 李四账户+500
UPDATE account SET balance = balance + 500 WHERE NAME ='lisi';

-- 如果没有问题 那么就提交
COMMIT;
-- 发现问题了  就回滚事务
ROLLBACK;
UPDATE account SET balance = 1000;-- 数据还原到1000
SELECT * FROM account;

事务提交的方式：

事务提交的两种方式：
- 自动提交：
  - mysql就是自动提交的，你用mysql写完sql语句执行就自动被提交了
    - 一条DML(增删改)语句会自动提交一次事务。
- 手动提交：(oracle)
  - 修改事务的默认提交方式：
    - 查看事务的默认提交方式：SELECT @@autocommit; 1代表自动提交 0代表手动提交
    - 修改默认提交方式： set@@autocommit = 0; 如果你设置了手动提交，那么每次执行完sql语句都需要commit手动提交一次，否则不会持久化存储如果你设置了自动提交，那么每次执行一些sql语句，就不需要手动特意去提交一次。自动会持久化存储

事务的四大特性（ACID）

原子性（Atomicity）

事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。
一致性（Consistency）

事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。（其他特性都是为一致性这个特性服务的）
隔离性（Isolation）

隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。
持久性（Durability）

一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

事务的隔离级别 (隔离性)

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
数据库事务的隔离级别有4种，由低到高分别为Read uncommitted、Read committed、Repeatable read、Serializable。序列化
存在问题：
1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 虚读：(不可重复读)，在同一个事务中，两次读取到的数据不一样。
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
  
  更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，
  
  · 无法插入，此时就发生了幻读。 (幽灵问题)
隔离级别：
1. read uncommitted：读未提交(未提交的可以读到)
  - 产生的问题：脏读、虚读、幻读
2. read committed：读已提交（Oracle）
  - 产生的问题：虚读、幻读
3. repeatable read：可重复读（MySQL默认）
  - 产生的问题：幻读
4. serializable：串行(xing)化,序列化
  - 可以解决所有的问题
    
    当前事务可以将表锁上，其它事务访问不到该表，只有当前事务提交或回滚了，其它事务才可以访问该表。效率极低。
    
    注意：隔离级别从小到大安全性越来越高，但是效率越来越低

事务隔离级别	脏读	不可重复读	幻读
读未提交	可能发生	可能发生	可能发生
读已提交	×	可能发生	可能发生
可重复读	×	×	可能发生
串行化	×	×	×

数据库查询隔离级别：

select @@tx_isolation; -- mysql5.7之前使用这种方式查询
select @@transaction_isolation; -- mysql5.7以后使用这种方式查询

数据库设置隔离级别：
set global transaction isolation level 级别字符串;
set global transaction isolation level repeatable read;
事务级别演示：
set global transaction isolation level read uncommitted;

转账操作

start transaction; 两个事务窗口都开启事务

update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

一般不会修改隔离级别，了解即可，我们使用mysql默认的隔离级别即可。

DCL

概念：

SQL分类：
1. DDL：数据定义语言，操作数据库和表,列
2. DML：数据操作语言，增删改表中数据
3. DQL：数据查询语言，查询表中数据
4. DCL：数据控制语言，管理用户，授权
DCL了解即可，因为公司有数据库管理员，他们专门管理数据库的，一般你刚进入公司他们会给你创建一个数据库用户他会给你授权，你到时候用找个用户登入数据库即可。
DBA：(DataBase Administrator) 数据库管理员

管理用户,授权

DCL：管理用户，授权
	一. 管理用户
		1. 查询用户：
				-- 1. 切换到mysql数据库
					USE myql;
				-- 2. 查询user表
					SELECT * FROM USER;
					通配符： % 表示可以在任意主机远程使用用户登录数据库
			
		2. 添加用户：
			   语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
					CREATE USER 'zs'@'localhost'  IDENTIFIED BY  '123';
					
		3. 删除用户：
			   语法：DROP USER '用户名'@'主机名';
			   DROP USER 'zs'@'localhost'
			
		4. 修改用户密码：
			#SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
			 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
			
			#UPDATE user SET PASSWORD = PASSWORD('新密码') WHERE USER='用户名' 
			 update user set password = password('1234') where user='zs'

数据库的备份和还原

命令行：
- 语法：
- -- 备份： mysqldump -u用户名 -p密码数据库名称 > 保存的路径
  mysqldump -uroot -proot t3 > C://t3.sql
- - 还原：
- 图形化工具：
  - 转储t3.sql文件
  - 执行sql文件，前提是事先创建好数据库才可以导入成功

ER图表

概述：

ER图也被称为实体-联系图，提供了表示实体类型、属性和联系的方法。

Entity-relation

三个概念

实体定义：现实世界中任何可以被认知、区分的事物示例：

1、学校：学生、教师、课程、班主任 … 2、企业：职工、产品
属性定义：实体所具有的特性示例 1、学生属性：学号、姓名、年龄、性别、专业 … 2、产品属性：产品编号、产品名称、规格 …
关系定义：实体之间的关系分类一对一关系(1:1) 班级和班长一对多关系(1:n) 公司和职工、班级和学生多对多关系(m:n) 学生和课程、商店和顾客
ER图的绘制矩形框代表实体

菱形框代表关系

椭圆形代表属性
示例学生选课系统

车队-司机-车辆

sql语句优化技巧

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中使用!=或< >操作符，否则引擎将放弃使用索引而进行全表扫描。

(索引扫描效率会优于全表扫描)
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

解释说明：UNION 操作符用于合并两个或多个 SELECT 语句的结果集。默认地，UNION 操作符选取不同的值。

如果允许重复的值，请使用 UNION ALL。

SELECT * FROM s1 UNION SELECT * FROM s2; SELECT * FROM s1 UNION ALL SELECT * FROM s2;
in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3,4)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 4；
应尽量避免在 where 子句中对字段进行表达式运算操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3) = 'abc' -- name以abc开头的id

应改为:

select id from t where name like 'abc%'
很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
避免频繁创建和删除临时表，以减少系统表资源的消耗。

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。

创建临时表的语法与创建表语法类似，不同之处是增加关键字TEMPORARY，如：

CREATE TEMPORARY TABLE 表名 (…. )
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
尽量避免大事务操作，提高系统并发能力。
尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

如下：

SELECT * FROM t WHERE username LIKE '%li%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE 'li%'