MySQL-基础（三）

5.4 数据库事务

数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成

事务指的是数据库一种保护数据的方式

事务一般由增删改操作自动调用，事务根据数据库不同提交的时机也是不同的

​ mysql数据库默认执行增删改就会提交事务

​ 我们可以设置为 手动提交 begin 或者 start transaction;

事务的特征

​ ACID原则：

​ 原子性

​ 事务是操作数据的最小单元，不可以再分

​ 一致性

​ 事务提交之后，整个数据库所看到数据都是最新的数据

​ 所有人看到的数据都是一致的

​ 隔离性

​ 别人无法访问到我们未提交的数据，而且一旦这个数据被我修改，别人也无法进行操作

​ 持久性

​ 事务一旦被提交，数据库就进入到一个全新的状态

​ 数据在也不能返回到上一个状态

事务如何开启和提交？

​ 开启

​ 当我们执行增删改操作的时候就会默认开启一个事务

​ 这个事务和当前操作的窗口有关，别人是无法共享这个事务的

​ 提交

​ 手动

​ 显示

​ commit; 提交

​ rollback; 回滚

​ 隐式

​ 执行DDL操作，会默认提交当前的事务

​ 用户退出，事务统一进行回滚（Mysql）

​ 自动

​ mysql数据库执行DML操作之后会自动的提交事务

​ 好处：

​ 方便

​ 坏处：

​ 不能将多个SQL纳入到一个事务，不便于管理

​ 当我们大批量插入数据的时候，数据库会频繁的开启关闭事务影响插入效率

5.5 事务的隔离级别

根据数据库的不同用途，我们可以对数据库的事务进行级别的设置

级别设置的越高，数据越安全，但是效率越低

​ 读未提交

​ 我们可以读取到别人未提交的数据

​ 有可能产生脏读的问题

​ 读已提交

​ 只能读取别人提交后的数据

​ 不能达到可重复读，但是可以避免脏读

​ 有可能产生虚读或者幻读的情况

​ 可重复读

​ 当数据被我查询之后，别人就不能修改这个数据了

​ 说明在我查询的时候已经有事务操作到这些数据，查询都会开启事务

​ 但是不能防止别人查询别的数据

​ 序列化

​ 当前数据库只能存在一个事务，但我操作数据库的时候，别人是不能访问数据库的

​ 这时对于用户来讲数据相当安全，一般在倒库的时候才会开启这种级别

​ 脏读

​ 读取别人未提交的数据，这个数据是不安全的

​ 虚读

​ 第一次读取的数据，第二次在读取的时候有可能被被人修改了

​ 幻读

​ 第一次读取的数据，第二次多了一条或者少了一条

六、SQL-DDL

常见组成

​ 库的操作

​ 表

​ 视图

​ 存储过程

​ 事件

​ 索引

​ 触发器

5.1 数据库的操作

# 数据库创建
create database 数据库名 charset utf8;

# 查看数据库
show databases;
show create database db;
desc db;
select database();

# 选择数据库
use 数据库名;

# 删除数据库
drop database 数据库名;

# 修改数据库
alter database db1 charset utf8;

5.2 数据类型

4.3 表 table的创建

-- 我们首先要对你操作的数据有一个基础型的了解
-- 学号 姓名 性别 出生日期 入学时间 专业 院系 创建时间
-- 学号 int 姓名 varchar 性别 char 出生日期 date 入学时间 date 专业 varchar 院系 varchar 创建时间 timestamp


create table t_student(
   sno int,
   sname varchar(40),
   gender char(1),
   birthday date,
   schooltime date,
   major varchar(255),
   department varchar(255),
   createtime timestamp
);

4.4 表 table的修改

--根据查询语句创建表
CREATE TABLE STU01 AS SELECT * FROM t_student;
--添加一列
alter table t_student add updatetime timestamp default now();
--删除一列
alter table t_student drop column email;
--修改一列
alter table t_student modify major varchar(20);
--修改列名
alter table t_student rename COLUMN birthday to birth;
--修改表名
rename t_student to t_s;
--删除一张表
drop table t_s;

4.5 表 table的约束

约束指的是我们创建的表 别人在插入数据的时候，对数据的约束,而不是对创建人的约束

主键约束 PRIMARY KEY

​ a: 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。

​ b: 主键分为单字段主键和多字段联合主键

​ c: 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

CREATE TABLE t_pk01(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);

CREATE TABLE t_pk03(
tid INT(11),
cid INT(11),
salary FLOAT,
PRIMARY KEY(tid,cid)
);


CREATE TABLE t_pk02(
id VARCHAR(40),
name VARCHAR(25),
salary FLOAT
);

-- ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);
ALTER TABLE t_pk02 ADD PRIMARY KEY(id);

-- ALTER TABLE <数据表名> DROP PRIMARY KEY;
ALTER TABLE t_pk04 DROP PRIMARY KEY;

唯一性约束 unique

列中的值可以为空但是不能相同

CREATE TABLE tb_unique01(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
phonenum VARCHAR(11) UNIQUE,
location VARCHAR(50)
);

-- ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
ALTER TABLE tb_unique01 ADD CONSTRAINT t_unique01_unique_location UNIQUE(location);

-- ALTER TABLE <表名> DROP INDEX <唯一约束名>;
ALTER TABLE tb_unique01 DROP INDEX unique_t_unique01_location;

非空约束 not null

列中的值不能为null

CREATE TABLE tb_null01(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);

-- ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;
ALTER TABLE tb_null01 CHANGE COLUMN location location VARCHAR(50) NOT NULL;

-- ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
ALTER TABLE tb_null01 CHANGE COLUMN location location VARCHAR(50) NULL;

外键约束

定义外键时，需要遵守下列规则：

​ 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

​ 必须为主表定义主键。

​ 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主表中，这个外键的内容就是正确的。

​ 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或唯一性键。

​ 外键中列的数目必须和主表的主键中列的数目相同。

​ 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

外键的删除

​ 级联删除

​ 设置为NULL

​ 阻止删除

​ 删除主表数据的时候，要保证这个ID没有被字表所使用

--1位老师对应N个学生 teacher表为主表，student表为外键表 tid为外键
create table teacher(
   tid varchar(40) primary key,
   name varchar(40)
);

create table student(
   sid varchar(40) primary key,
   name varchar(40),
   tid varchar(40),
   CONSTRAINT fk_teacher_student_tid FOREIGN KEY(tid) REFERENCES 
teacher(tid) on delete cascade
);

insert into teacher values('a','岳不群');
insert into teacher values('b','定闲师太');
insert into teacher values('c','无崖子');
insert into student values('1','令狐冲','a');
insert into student values('2','岳灵珊','a');
insert into student values('3','依琳','b');
insert into student values('4','星宿大仙','c');

--错误数据，因为主表主键没有d
insert into student values('5','任我行','d');

-- 修改表
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

约束回顾

-- 查看表中的约束
SHOW CREATE TABLE <数据表名>;

create table table_name(
列名1 数据类型 (int) primary key auto_increment,
列名2 数据类型  not null,
列名3 数据类型   unique,
列名4 数据类型 default '值',
constraint 索引名 foreign key(外键列) references 主键表(主键列) on delete
cascade | on delete set null
)

1.主键约束
添加:alter table table_name add primary key (字段)
删除:alter table table_name drop primary key

2.非空约束
添加:alter  table table_name modify 列名 数据类型  not null
删除:alter table table_name modify 列名 数据类型 null

3.唯一约束
添加:alter table table_name add unique 约束名（字段）
删除:alter table table_name drop key 约束名

4.自动增长
添加:alter table table_name modify 列名 int auto_increment
删除:alter table table_name modify 列名 int  

5.外键约束
添加:alter table table_name add constraint 约束名 foreign key(外键列) 
references 主键表（主键列）

删除:
第一步:删除外键
alter table table_name drop foreign key 约束名

6.默认值
添加:alter table table_name alter 列名  set default '值'
删除:alter table table_name alter 列名  drop default
数加科技

索引

假如我们有一张表有1000万条记录，现在查询ename = ‘zs’的员工信息

​ 原始的数据遍历

​ 让我们查询的数据有序：可以使用折半查找法

案例

​ 拼音

​ 偏旁部首

简介

​ 在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。

​ 索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。

​ 索引优化应该是对查询性能优化最有效的手段。

-- CREATE INDEX indexName ON mytable(username); 
Create Index index_dept_dname on dept(dname);
Drop Index index_dept_dname on dept;

索引的分类:

​ 常规索引

​ 常规索引，也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。

​ 主键索引

​ 主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

​ 唯一索引

​ 唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

​ 外键索引

​ 外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

=======================================================================================================================

索引是数据库中一块独立的空间，专门存储索引值的一棵B-树

​ 我们可以通过B-树快速的定位到要查找的数据

​ 尽量不要对重复数据的列添加索引

​ 性别

​ 优点

​ 索引可以让MySQL快速地查找到我们所需要的数据，但这并不是索引的唯一作用。

​ 索引大大减少了MySQL服务器需要扫描的数据量。

​ 索引可以帮助服务器避免排序和临时表。

​ 索引可以将随机I/O变为顺序I/O。

​ 缺点

​ 影响数据库的增删改速度

视图

简介

​ MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。

​ 行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图是动态生成的。

优点

​ 1) 定制用户数据，聚焦特定的数据

​ 2) 简化数据操作

​ 3) 提高数据的安全性

​ 4) 共享所需数据

​ 5) 更改数据格式

​ 6) 重用 SQL 语句

创建 查看

-- CREATE VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
CREATE VIEW v_swordsman AS
( SELECT tid, NAME FROM teacher ) UNION ALL
( SELECT sid, NAME FROM student );

SELECT
 * 
FROM
 v_swordsman
 
 
-- DESCRIBE 视图名；
DESCRIBE v_swordsman


-- SHOW CREATE VIEW 视图名;
SHOW CREATE VIEW v_swordsman


-- ALTER VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
-- DROP VIEW IF EXISTS <视图名1> [ , <视图名2> …]

三范式

我们创建表需要遵循的规范

第一范式

数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组

ID	name	Address
1	李毅	浙江-杭州
2	小虎	安徽-合肥
3	黄仙	广东-深圳

ID	name	province	city
1	李毅	浙江	杭州
2	小虎	安徽	合肥
3	黄仙	安徽	合肥

第二范式

数据库中每一行数据必须依赖于主键，每一个行数据都要有主键

主键是一行数据的唯一性标识

ID	name	province	city	good
1	李毅	浙江	杭州	龙井茶
2	小虎	安徽	合肥	小龙虾
3	黄仙	安徽	合肥	长丰草莓

ID	sname	tid	tname
1	张三	3	笑哥
2	李四	1	小虎
3	王五	2	火火

第三范式

表中非主键的列要完全依赖于主键，不能出现部分属性依赖于其他属性

当出现传递依赖的时候要将非依赖于主键的列专门创建一张表进行管理

ID	sname	tid
1	张三	3
2	李四	1
3	王五	2

tid	tname
3	笑哥
1	小虎
2	火火

表与表的关系

为了维护两张表的关系，然后根据两张表的对应关系可以分为：

1:1--> 一对一

​ 两张表中的数据 1条只和1条对应

​ 关联方案

​ 让关联数据主键值相同

​ 在一张表中存放另外一张表的主键

1:N--> 一对多

​ A表中的一条数据有可能对应B表中的多条记录

​ 1老师：N学生

​ 关联方案

​ 在N方的表中设计一个1方的主键列，也称之为外键关联

N:N--> 多对多

​ A表中的一条数据有可能对应B表中的多条记录

​ 同时B表中的一条数据有可能对应A表中的多条记录

​ 1学生：N课程

​ 1课程：N学生

​ 关联方案

​ s学生表 c课程表

​ 一般都会创建第三章表专门管理学生与课程的关系

​ sid,cid 作为联合主键管理信息

​ id ,sid ,cid 以ID作为这张表的单独主键