- 1关于ROS中对moveit和gazebo联动出现的问题进行解决_rosnoetic让moveit和gazebo联动
- 2数据结构-线性表_线性表的顺序存储结构和链式存储结构分别是
- 3了解哈希冲突_hash冲突产生的原因
- 4『App自动化测试之Appium应用篇』| uiautomator + accessibility_id定位方法完全使用攻略_appium中bounds [915,1941][1023,2049]右下角是那部分
- 5nlp的四大任务什么_nlp四大基本任务
- 6【动手学深度学习】使用块的网络（VGG）的研究详情
- 7人脸识别4-百度商用方案调研_人脸识别有源和无源的区别
- 8基于SpringBoot+Vue的酒店管理系统设计与实现_基于springboot+vue 酒店客
- 9MySQL8.0窗口函数_mysql窗口函数lead
- 10应用层协议：VPN协议
数据库系统概论完整笔记_创建数据库owndb,owndb1使其分别不带location指令和带location指令
赞
踩
1.数据库的操作
1.数据库的创建删除修改查看
#一、创建数据库
#(方式一)
CREATE DATABASE mydb1;
#(方式二)
CREATE DATABASE mydb2 CHARACTER SET "gbk"
#(方式三)
#如果要创建的数据库不存在则创建成功
CREATE DATABASE IF NOT EXISTS mydb
CHARACTER SET "utf8"
#如果要创建的数据库存在，则创建成功，但是不会报错
CREATE DATABASE IF NOT EXISTS mydb2
CHARACTER SET "jbk"
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
#二、管理数据库
#1.查看数据库 SHOW DATABASES; #2.查看数据库结构 SHOW CREATE DATABASE mydb; #3.切换数据库 USE mysql; #4.查看数据库中表的信息 SHOW TABLES; #5.查看当前使用的数据库 SELECT DATABASE() FROM DUAL; #6.查看指定数据库下保存的数据表 SHOW TABLES FROM mysql;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
#三、修改数据库
ALTER DATABASE mydb2 CHARACTER SET "utf8";
#查看一下
SHOW CREATE DATABASE mydb2;
- 1
- 2
- 3
#四、删除数据库
#如果数据库存在则删除成功，否则默默结束，不会报错
DROP DATABASE IF EXISTS mydb1;
#在查看一下
SHOW DATABASES;
- 1
- 2
- 3
- 4
2.表的创建删除查看修改
#五、创建表
#先查看一下 USE mydb; SHOW CREATE DATABASE mydb; SHOW TABLEs; #方式1： CREATE TABLE IF NOT EXISTS myemp( id INT, emp_name VARCHAR(50), hire_data DATE ); #查看表结构 DESC myemp; SHOW CREATE TABLE myemp; #查看表信息 SELECT * FROM myemp; #方式2,注意要在用一个数据库下 CREATE TABLE myemp2 AS SELECT employee_id,last_name,salary FROM employees;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
#六、修改表
#1.添加一个字段 ALTER TABLE myemp ADD salary DOUBLE(10,2) #一共有10位，小数点有两位 ALTER TABLE myemp ADD phone VARCHAR(15) FIRST; #FIRST 代表出现在字段的第一个 ALTER TABLE myemp ADD mail VARCHAR(30) AFTER phone; #after phone 代表添加到phone字段后面 #查看 SHOW CREATE TABLE myemp; DESC myemp; #2.修改一个字段 #修改类型大小 ALTER TABLE myemp MODIFY mail VARCHAR(40) #默认名称位liping ALTER TABLE myemp MODIFY emp_name VARCHAR(50) DEFAULT 'liping' #修改字段名称用change 旧名称 新名称 ALTER TABLE myemp CHANGE salary mon_salary DOUBLE(10,2); #在修改名称的同时还可以修改类型大小 ALTER TABLE myemp CHANGE mail my_mail VARCHAR(50); #查看 DESC myemp; #删除一个字段DrOP COLUMN ALTER TABLE myemp DROP COLUMN my_mail; #3.重命名一个字段rename table 表名 to 新表名 RENAME TABLE myemp TO emp DESC emp;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
3.数据的查询
1.单表的查询
2.选择表中的若干元组：
（1）消除取值重复的行 :DISTINCT
SELECT DISTINCT department FROM EMPLOYEE
- 1
（2）查询满足条件的元组
-
比较运算符：=, >, <, >=, <=, !>, !<
-
确定范围：between and,not between and
-
字符匹配：like,not like;
%号：代表任意长度
—：代表单个字符。如：a_b,代表以a开头，以b结尾长度为3的任意字符
-
多重条件：and， or , not
-
空值查询：is null, is not null
（3）order by查询结果按照一个或多个属性列升序（asc）或者降序（desc）
#按照工资降序排列 SELECT employee_id as 员工号,last_name as 名字,salary as 工资 FROM employees ORDER BY salary DESC;- 1
- 2
- 3
- 4
（4）聚集函数
COUNT(); SUM(); AVG(); MAX(); MIN()
（5）group by()
（6）连接查询
4.清空表和删除表
1.清空表是清空表中所有数据，但是表的结构任然保留
DCL中的COMMIT 和ROLLBACK
commit:提交数据，一旦执行commit，则数据就被永久报讯在数据库中，意味着数据不可以回滚
rollback回滚数据，一旦执行rollback，则可以实现数据的回滚，回滚到最近一次commit之后
5.对比TRUNCK TABLE 和DELETE FROM
相同点：都可以实现对表中所有数据的删除，同时保留表的结构
不同点：TRUNCATE TABLE 一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的
DELETE FROM 一旦执行该操作，则数据可以全部清除也可以部分清除（用where条件过滤）。同时，数据是可以实现回滚的
6.DDL和DML的说明
DDL的操作一旦执行，就不可以回滚
DML的操作默认情况下，一旦执行，也是不可以回滚的。但是如果在执行DML之前，执行了SET autocommit = false ,则执行的DML操作就可以实现回滚
2.数据库的完整性和安全性
掌握：DBMS完整性控制机制的三个方面，完整性约束条件的定义、完整性约束条件的检查和违约处理，使用触发器实现数据库完整性的方法
难点：RDBMS如何实现参照完整性的策略。当操作违反实体完整性、参照完整性和用户定义的完整性约束条时，RDBMS应该如何处理以确保数据的正确与有效。
1.数据库的完整性
1.数据库的完整性包括：
（1）实体的完整性：关系的主属性不能取空值，执行插入修改操作时看是否触发实体完整性
（2）参照完整性：检查时机：参照表（插入、修改操作）、被参照表（删除、修改）拒绝执行或者级联操作或者设置为空值
（3）用户定义完整性：检查时机：执行插入修改操作时，违约处理方法是拒绝执行
2.数据库的完整性是指数据库的正确性和相容性。
（1）.数据的正确性：是指数据是符合现实世界语义，反映了当前实际就状况
（2）.数据的相容性：数据库的同一对象在不同关系表中的数据是符合逻辑的
3.数据库的完整性和安全性概念有什么区别和联系？
（1）数据完整性：防止数据库中存在不符合语义的数据，也就是防止数据库存在不正确的数据
防范对象：不合法的语义、不正确的数据
也就是说无效操作和错误结果（垃圾进垃圾出）
（2）数据的安全性：保护数据库防止恶意的破坏和非法的存取
防范对象：非法用户和非法操作
完整性是阻止合法用户通过合法操作向数据库中加入不正确的数据
安全性防范的是非法用户和非法操作存取数据库中的正确数据
2.数据库的安全性
掌握：（1）TCSEC和CC的标准的主要内容
（2）DBMS的安全措施（用户身份鉴别、自主存取控制、强制存取控制技术、视图技术和审计技术、数据加密和加密传输）
难点： 强制存取控制机制中确定主体能否存取客体的存取规则
1.试述信息安全标准的发展史，CC评估保证级划分的基本内容
发展史：TCSEC(Tursted Computer System Evaluation Criteria)是指1985美国国防部（Department of Defense，DoD）正式颁布的可信计算机系统评估准则（Tursted Computer System Evaluation Criteria，TCSEC或DoD85）,为了满足全球IT市场互认标准化安全评估结果的需要，CTCPE、ITSEC、FC、TSCES于1993年发起联合行动解决原标准中概念和技术上的差异，将各自独立的准则集合成一组单一的、能被广泛使用的IT安全准则，这一行动被称为通用准则项目（Common Criteria,CC）。进过多次的讨论和修订，CC V2.1 版本于1999年被IOS采用为国际标准，2001年被我国采用为国家标准。
CTCPE（CanadianTrusted Computer Product Evaluation Criteria）加拿大可信计算机产品评估准则、ITSEC（Information Technology Security Evaluuation Criteria）欧洲信息技术安全评估准则、FC（Federal Criterial ,FC）
1991年4月，美国国家计算机安全中心（National Computer Security Center，NCSC）颁布了《可信计算机系统评估准则关于可信数据库系统的解释TCSEC/TDI(Trusted Database Interpretation),即紫皮书》（TCSEC又称桔皮书），将TCSEC扩展到数据库管理系系统中。从四个方面进行了评估：安全策略、责任和保证文档。
D级:该级是最低级别。保留D级的目的是为了将一切不符合更高标准的系统统统归于D组。如 DOS就是操作系统中安全标准为D级的典型例子，它具有操作系统的基本功
C1级:该级只提供了非常初级的自主安全保护，能够实现对用户和数据的分离，进行自主存取控制（DAC)，保护或限制用户权限的传播。现有的商业系统往往稍作改进即可满足要求。
C2级:该级实际上是安全产品的最低档，提供受控的存取保护，即将C1级的 DAC进一步细化，以个人身份注册负责，并实施审计和资源隔离。达到C2级的产品在其名称中往往不突出“安全”(security)这一特色，如操作系统中的 Windows 2000、数据库产品中的 Oracle 7等。
B1级:标记安全保护。对系统的数据加以标记，并对标记的主体和客体实施强制存取控制（MAC) 以及审计等安全机制。Bl 级别的产品才被认为是真正意义上的安全产品，满足此级别的产品前一般多冠以“安全”( security)或“可信的”( trusted）字样，作为区别于普通产品的安全产品出售。
B2级:结构化保护。建立形式化的安全策略模型，并对系统内的所有主体和客体实施DAC 和 MAC。
B3级:安全域。该级的TCB (Trusted Computing Base）必须满足访问监控器的要求，审计跟踪能力更强，并提供系统恢复过程。
A1级:验证设计，即提供B3级保护的同时给出系统的形式化设计说明和验证，以确信各安全保护真正实现。
CC是在上述各评估准则及具体实践的基础上通过相互总结和互补发展而来的。和早期的评估准则相比，CC具有结构开放、表达方式通用等特点。CC提出了目前国际上公认的表述信息技术安全性的结构，即把对信息产品的安全要求分为安全功能要求和安全保证要求。安全功能要求用以规范产品和系统的安全行为，安全保证要求解决如何正确有效地实施这些功能。安全功能要求和安全保证要求都以“类-子类-组件”的结构表述，组件是安全要求的最小构件块。
CC的文本由三部分组成，三个部分相互依存,缺一不可。
第一部分是简介和一般模型，介绍CC中的有关术语、基本概念和一般模型以及与评估有关的一些框架。
第二部分是安全功能要求，列出了一系列类、子类和组件。由11大类、66个子类和135个组件构成。
第三部分是安全保证要求，列出了一系列保证类、子类和组件，包括7大类、26个子类和74个组件。根据系统对安全保证要求的支持情况提出了评估保证级（EvaluationAssurance Level，EAL)，从EAL1至EAL7共分为7级，按保证程度逐渐增高。如表4.2所示
2.数据库安全性控制常用的方法和技术
（1）用户身份鉴别：提供多种方式让用户标识自己的名字或身份，用户要使用数据库系统时由数据库系统进行校对，通过鉴定后方可使用数据库。
（2）多层存取控制：系统提供用户权限定义和合法权限查询功能，用户只能获得某种权限才能访问数据库。
（3）视图机制：为不同的用户提供不同的视图，通过视图机制把要保密的对无权存取的用户隐藏起来，从而对数据提供一定的安全保护。
（4）审计：建立审计日志，把用户对数据库的所有操作自动记录下来放入审计日志当中，审计员可以利用审计信息重现找出非法村求数据库的人、时间和内容。
数据库的审计功能指：数据库的审计模块在用户对数据库执行操作的同时，把所有操作都自动记录到系统的审计日志中。
（5）数据加密：对存和传输的数据进行加密处理，从而使不知道解密算法的人无法获知数据的内容。
3.什么是数据库中自助存取控制方法和强制存取方法？
自助存取控制方法：定义个用户对不同数据对象的存取权限。当用户对数据库访问时首先检查其权限，防止不合法用户对数据库的存取。自助存取控制中的“自主”的含义是：用户可以将自己拥有的存取权限“自主”地授予别人
强制存取控制方法：每个数据对象强制地标以一定的密级，每个用户被强制地授予某一个级别的许可证。系统规定只有具有某一许可证的用户才能存取某一个密级的数据对象。
3.约束
1.什么是数据库的完整性约束
完整性约束条件是指数据库中的数据应该满足的语义约束条件
2.为什么需要约束
为了保证数据的完整性
3.什么叫约束
对表中字段的限制
4.约束的分类：
（1）列级约束：
（2）表级约束
5.约束的作用：（有待完善）
保证数据的准确性
（1）not null（非空值约束）：没有表级约束，只有列级约束
#1.先创建主表 CREATE TABLE dept( dept_id INT, dept_name VARCHAR(20) ); #2.在创建从表 CREATE TABLE emp( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(15), department_id INT, #添加表的约束 CONSTRAINT fk_emp_dept_id FOREIGN KEY(department_id) REFERENCES dept(dept_id) ) #上述探作报错，因为主表中的dept_id上没有主键约束或唯一性约束。 ALTER TABLE dept ADD PRIMARY KEY (dept_id); DESC dept; #查看表的约束 SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE table_name="dept" #现在主表中插入部门 INSERT INTO dept VALUES(10,'IT') #再在子表中插入员工信息 INSERT INTO emp VALUES(1001,'liping',10) SELECT * FROM dept,emp; #要想先删除部门信息，也是不可以的,得先删除员工信息 DELETE FROM dept WHERE dept_id = 10 DELETE FROM emp WHERE department_id = 10 #3 not null (非空约束) #3.1-在CREATE TABLE时添加约束 CREATE TABLE testl( id INT NOT NULL,l last _name VARCHAR (15)NOT NULL, email VARCHAR(25), salary DECIMAL (10,2) );
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
（2）unique:（唯一约束）:可以向声明为unique的字段上添加null值。而且可以多次添加null
#4.1 在CREATE TABLE时添加约束 CREATE TABLE test2( id INT UNIQUE, last_name VARCHAR(15) , emaii VARCHAR(25)UNIQUE, salary DECIMAL ( 10,2) ) ; #4.1 在CREATE TABLE时添加约束 CREATE TABLE test2( id INT UNIQUE，#列级约束 last_name VARCHAR(15) , emaii VARCHAR (25), salary DECIMAL ( 10,2), #表级约束 CONSTRAINT uk_test2_email UNIQUE ( email)) ; #4.2在ALTER TABLE时添加约束DEsc test2 ; UPDATE test2 SET salary = 5000 WHERE id = 3 ; #方式1: ALTER TABLE test2 ADD CONSTRAINT uk_test2_sal UNIQUE(salary); #方式2: ALTER TABLE test2 MODIFY last_name VARCHAR(15) UNIQUE;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
（3）primary key（主键约束）:可以再列级别约束、也可以在表级别约束；
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
（4）foreign key (外键约束)
（6）check (检查约束)
（7）default (默认约束)
6.如何添加/删除约束
删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除
（1）creat table时添加约束
（2）alter table 时增加约束、删除约束
7.总结:约束关系是针对双方的
1.添加了外键约束后，主表的修改和删除数据受约束。
2.添加了外键约束后，从表的添加和修改数据受约束
3.在从表上建立外键，要求主表必须存在
8.约束等级
- Cascade方式︰在父表上update/delete记录时，同步update/delete掉子表的匹配记录
- Set null方式∶在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
- No action方式:如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
- Restrict方式:同no action，都是立即检查外键约束
- Set default方式(在可视化工具sQLyog中可能显示空白)︰父表有变更时，子表将外键列设置成一个默认的值，但lnnodb不能识别
问题1:那么建和不建外键约束和查询有没有关系?
答:没有，外键与级联更新适用于单机低并发，不适合分布式、高并发集群;级联更新是强阻塞，存在数据库更新风暴的风险;外键影响数据库的插入速度。
阿里开发中【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
在MySQL里，外键约束是有成本的，需要消耗系统资源。对于大并发的sQL操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
问题2.带AUTO_INCREMENT约束的字段值是从1开始的吗?
在MysQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性(AUTo_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。
4.DBMS的完整性控制机制应具有哪三个方面的功能？
（1）定义功能：提供定义完整性约束条件的机制，完整性约束条件也称为完整性规则，是数据库中必须满足的予以约束条件
（2）检查功能：检查用户发出的操作（Insert、Update 、delete）请求是否违背了完整性约束条件
（3）违约处理功能：用户操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性（NO ACTION），拒绝或者级联执行其他操作（CASCADE）
DBMS进行检查的好处
用DBMS管理的好处：不必由应用程序来完成，从而减轻了应用程序员的负担。
4.视图
1.视图
一个或者多个数据表里的数据的逻辑显示，视图并不存储数据，视图是一种虚拟的表，本身不具有数据，占用很少的空间。
2.视图的本质：
可以看做是存储起来的select语句
视图中select语句涉及到的表，称为基表
视图本身的删除不会导致数据的删除
视图的应用场景：针对于大型项目
3.视图的优点：
简化查询,减少数据的冗余，数据安全（设置访问权限）
CREATE DATABASE dbtest; USE dbtest; CREATE TABLE emps AS SELECT * FROM atguigudb.employees; CREATE TABLE depts AS SELECT * FROM atguigudb.departments; SELECT * FROM emps; SELECT * FROM depts; DESC emps; DESC atguigudb.employees; #1.针对于单标 CREATE VIEW vu_emp1 AS SELECT employee_id,last_name,salary FROM emps; SELECT * FROM vu_emp1; #查询语句中的字段的别名会坐作为视图的中的字段出现呢 CREATE VIEW vu_emmp2 AS SELECT employee_id,last_name,salary FROM emps WHERE salary>8000; SELECT * FROM vu_emmp2; #小括号内字段个数与select中字段的个数相同 CREATE VIEW vu_emp3(emp_id,NAME,monthly_sal) AS SELECT employ_id,last_name,salary FROM emps WHERE salary>8000 #情况二：视图中的字段在基表中可能没有对应的字段 CREATE VIEW vu_emp_sal AS SELECT department_id,AVG(salary) as avg_sal FROM emps WHERE department_id IS NOT NULL GROUP BY department_id; SELECT * FROM vu_emp_sal #针对多表 CREATE VIEW vu_emp_dep AS SELECT e.employee_id,d.department_id,d.department_name FROM emps e JOIN depts d ON e.department_id=d.department_id SELECT * FROM vu_emp_dep; #基于视图创建视图 CREATE VIEW vu_emp4 AS SELECT employee_id,last_name FROM vu_emp1; SELECT * FROM vu_emp4; #查看视图 SHOW TABLEs; DESCRIBE vu_emp4; SHOW TABLE STATUS LIKE 'vu_emp1'; SELECT employee_id,last_name,salary FROM emps; #更新视图的数据，会导致基表中的数据的修改 UPDATE vu_emp1 SET salary = 20000 WHERE employee_id=101; #同理，更新表中的数据，也会导致途中的数据的修改 UPDATE emps SET salary = 10000 WHERE employee_id = 101; SELECT * FROM emps; #删除视图中的数据，也会导致表中的数据的删除 #修改视图 DESC vu_emp1; CREATE OR REPLACE VIEW vu_emp1 AS SELECT employee_id,last_name,salary,email FROM emps WHERE salary>7000 SELECT * FROM vu_emp1; #删除视图 DROP VIEW vu_emp4; SHOW TABLES; DROP VIEW IF EXISTS vu_emp1;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
6.触发DBMS进行参照完整性的检查的操作是什么？
（1）插入元组。拒绝执行
（2）修改外码的值。拒绝操作
（3）删除元组。级联操作：删除所关联的信息
（4）修改主码值。设置为空值
7.断言（create assertion）
断言是用来指定更具有一般性的约束。断言创建后，任何对断言中所涉及的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真的操作都会被拒绝执行。
8.触发器（create trigger）
1.概念：
触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操件。
触发器：相当于事件的监听器，当数据库发生特定事件后，触发器就会被触发，完成相应的处理
数据库设计应该具备的条件/良好的数据库设计应该有以下优点：冗余度小，结构合理
（1）节省数据的存储空间
（2）能够保证数据的完整性
（3）方便进行数据库应用系统的开发
2.触发器的相关操作
#举例1:定义触发器"“salary_check_trigger”，基于员工表"“employees""的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为'HYo0o'的错误，从而使得添加失败。 DELIMITER // CREATE TRIGGER salary_check_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN DECLARE mgrsalary DOUBLE; SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id; IF NEW .salary > mgrsalary THEN SIGNAL SQLSTATE 'HY800' SET MESSAGE_TEXT ='薪资高于领导薪资错误'; END IF; END // DELIMITER ; INSERT INTO employees(employee_id,last_name,email,hire_date,jod_id,salary,manager_id) VALUES(300,'tom','tom@163.com',CURDATE(),8000,103); SELECT *FROM employees; #2．查看触发器 #查看当前数据库的所有触发器的定义 SHOW TRIGGERS; #方式2:查看当前数据库中某个触发器的定义 SHOW CREATE TRIGGER salary_check_trigger; #方式3:从系统库information schema的TRIGGERS表中查询"salary_check_trigger"触发器的信 SELECT * FROM information_schema.TRIGGERS; #3.册除触发器 DROP TRIGGER IF EXISTs after_insert_test_tri; /* 触发器的练习 */ CREATE DATABASE test3_trigger; USE test3_trigger; CREATE TABLE emps AS SELECT employee_id,last_name,salary FROM atguigudb.employees; SHOW TABLES; SELECT * FROM emps; DESC emps; #1．复制一张emps表的空表emps_back，只有表结构，不包含任何数据 CREATE TABLE emps_back AS SELECT * FROM emps WHERE 1 = 2;#相当于始终是假，不执行，空表 #2．查询emps_back表中的数据 SELECT * FROM emps_back; #3．创建触发器emps_insert_trigger，每当向emps表中添加一条记录时，同步将这条记录添加到emps_back2 DELIMITER // CREATE TRIGGER emp_insert_trigger AFTER INSERT ON emps FOR EACH ROW BEGIN INSERT INTO emps_back(employee_id,last_name,salary)VALUES(NEW.employee_id,New.last_name,New.salary); END // DELIMITER; #4.验证触发器是否起作用 SELECT *FROM emps_back; INSERT INTO emps(employee_id,last_name,salary) VALUES(0001,'ping',18000); SELECT * FROM emps; #练习2:使用练习1中的emps表 #1．复制一张emps表的空表emps_backl，只有表结构，不包含任何数据 SELECT * FROM emps; CREATE TABLE emps_back1 AS SELECT employee_id,last_name,salary FROM emps WHERE 1 = 2; #该句是创建空表 #2．查询emps_backl表中的数据 SELECT * FROM emps_back1; #3，创建触发器emps_del_trigger，每当向emps表中删除一条记录时，同步将删除的这条记录添加到emps_ba DELIMITER // CREATE TRIGGER emps_del_trigger BEFORE DELETE ON emps FOR EACH ROW BEGIN INSERT INTO emps_back1(employee_id,last_name,salary) VALUES(OLD.employee_id,OLD.last_name,OLD.salary); END // DELIMITER; #4．验证触发器是否起作用 SELECT *FROM emps; SELECT *FROM emps_back1; DELETE FROM emps WHERE employee_id=1;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
3.触发器的优缺点：
优点：（1）确保数据的完整性
（2） 可以帮助我们记录操作日志
（3）触发器哈可以在操作数前，对数据进行合法检查
缺点：（1）触发器的可读性差
（2）相关数据的变更，可能会导致触发器出错
9.六大范式
（1）第一范式（1NF）:确保每列的原子性
只要是确保数据表中每个字段的值必须具有原子性，也就是说数据表中的每个字段的值为不可再拆分的最小数据单元
例如家庭住址：省市区，看具体需求是否需要拆分，不需要拆分就具有原子性
（2）第二范式(2NF)：确保每列和主键的完整依赖
在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。
例如：成绩表：学号、课程号、成绩；其中学号和课程号都是主键，不能说这门课的成绩是多少，要说这门课对应的某个学生的成绩是对少
（3）第三范式(3NF)：确保每列和主键列直接相关，而不是间接相关
要求数据表中的所有非主键字段不能依赖于其他得主键字段，既不传递依赖也不部分依赖
例如非主属性A依赖于非主属性B,非主属性B依赖于主属性C的情况是不可以的(A-B->C)，只能A依赖C,B依赖C
（4）巴斯-科德范式(BCNF优化后的第三范式)：修正的第三范式或者扩充的第三范式,，它在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖
（5）第四范式（4NF）
多值依赖的概念：多值依赖即属性之间的一对多关系，记为K→→A。
函数依赖：事实上是单值依赖，所以不能表达属性值之间的一对多关系。
平凡的多值依赖∶全集U=K+A，一个K可以对应于多个A，即K→→A。此时整个表就是一组一对多关系。
非平凡的多值依赖︰全集U=K+A+B，一个K可以对应于多个A，也可以对应于多个B，A与B互相独立，即K→一A，K→一B。整个表有多组一对多关系，且有:“一"部分是相同的属性集合，“多""部分是互相独立的属性集合。
第四范式即在满足巴斯-科德范式(BCNF）的基础上，消除非平凡且非函数依赖的多值依赖（即把同一表内的多对多关系删除)。
（6）第五范式（5NF 又称完美范式）
越往下，冗余度越低
2.范式的优缺点
范式的优点：
数据标准化有助于消除数据库中的数据冗余，第三范式通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。
范式的缺点：
范式的使用可能降低查询效率，因为等级越高，设计的数据表就越多、越精细、数据的冗余度就越低，进行数据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效。
范式只是提出了设计的标准，实际上设计数据表时，未必一定要符合这些标准。开发中，我们会出现为了性能和读取效率违反范式化的原则，通过增加少量的冗余或重复的数据来提高数据库的读性能，减少关联查询，join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。
10.反范式化
1.应用场景：
当冗余的信息有价值或者能大幅度提高查询效率的时候，我们才会采取反范式的优化。
2.反范式化的优缺点
优点：增加数据表中的冗余字段来提高数据库的读性能
缺点：
（1）存储空间变大了
（2）一个表中字段做了修改，另一个表中冗余字段也需要做同步修改，否则数据不一致
（3）若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁会非常消耗系统资源
（4）在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂
规范化vs性能
1.为满足某种商业目标,数据库性能比规范化数据库更重要
2.在数据规范化的同时,要综合考虑数据库的性能
3.通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
4.通过在给定的表中插入计算列，以方便查询
10.键和相关属性的概念
(1)超键:能唯一标识元组的属性集叫做超键。
(2)候选键:如果超键不包括多余的属性，那么这个超键就是候选键。主键:用户可以从候选键中选择一个作为主键。
(3)外键∶如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键。
(4)主属性:包含在任一候选键中的属性称为主属性。
(5)非主属性:与主属性相对，指的是不包含在任何一个候选键中的属性。
通常，我们也将候选键称之为“码”，把主键也称为“主码”。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分。
#子查询 SELECT employee_id,last_name FROM employees WHERE salary In( SELECT MIN(salary) FROM employees GROUP BY department_id ) #exists和not exists #题目:查询公司管理者的employee_id， #last_name，job_id，department_id信息 SELECT DISTINCT mag.employee_id,mag.last_name,mag.job_id,mag.department_id FROM employees emp JOIN employees mag WHERE emp.manager_id = mag.employee_id #子查询 SELECT e.employee_id,e.last_name,e.job_id,e.department_id FROM employees e WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees ) SELECT employee_id,last_name,job_id,department_id FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees ) #exists SELECT employee_id,last_name,job_id,department_id FROM employees e1 WHERE EXISTS ( SELECT * FROM employees e2 WHERE e1.employee_id = e2.manager_id ) #题目:查询departments表中，不存在于employees表中的 #部门的department_id和department_name SELECT d.department_id ,d.department_name FROM employees e JOIN departments d ; ON e.department_id = d.department_id WHERE e.departments_id IS NULL -/-. - #1.查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name,salary FROM employees WHERE department_id=( SELECT department_id from employees WHERE last_name="Zlotkey" ) #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。 SELECT avg(salary) FROM employees SELECT employee_id,last_name,salary From employees WHERE salary > ( SELECT avg(salary) FROM employees ) #3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name，job_id,salary SELECT salary FROM employees WHERE job_id="SA_MAN" SELECT last_name,job_id,salary FROM employees WHERE salary > ALL( SELECT salary FROM employees WHERE job_id="SA_MAN" ) #4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 SELECT DISTINCT department_id from employees WHERE last_name LIKE "%u%" SELECT employee_id,last_name FROM employees WHERE department_id IN ( SELECT DISTINCT department_id From employees WHERE last_name LIKE "%u%" ) #5.查询在部门的location_id为1700的部门工作的员工的员工号 SELECT department_id FROM departments WHERE location_id = "1700" SELECT employee_id FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id = "1700" ) #6.查询管理者是King的员工姓名和工资 SELECT last_name,salary FROM employees WHERE manager_id IN ( SELECT employee_id FROM employees WHERE last_name = "King" ) #7 .查询工资最低的员工信息: last_name,salary SELECT last_name,salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees ) #8.查询平均工资最低的部门信息 SELECT MIN(avg_salary) FROM ( SELECT avg(salary) avg_salary FROM employees GROUP BY department_id )table_minSalary SELECT DISTINCT department_id FROM employees WHERE salary < ( SELECT MIN(avg_salary) FROM ( SELECT avg(salary) avg_salary FROM employees GROUP BY department_id )table_minSalary ); #错误 SELECT * FROM departments WHERE department_id IN ( SELECT DISTINCT department_id FROM employees WHERE salary < ( SELECT MIN(avg_salary) FROM ( SELECT avg(salary) avg_salary FROM employees GROUP BY department_id )table_minSalary ) ) #正确1 SELECT * FROM departments WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(avg_salary) FROM ( SELECT AVG(salary) avg_salary FROM employees GROUP BY department_id )table_minsalary ) ); #正确2 SELECT * FROM departments WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL( SELECT AVG(salary) FROM employees GROUP BY department_id ) ); #9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询) SELECT MIN(avg_salary) FROM( SELECT AVG(salary) avg_salary FROM employees GROUP BY department_id )table_minSalary SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)= ( SELECT MIN(avg_salary) FROM( SELECT AVG(salary) avg_salary FROM employees GROUP BY department_id )table_minSalary ) SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id=d.department_id) FROM departments d WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(avg_salary) FROM ( SELECT AVG(salary) avg_salary FROM employees GROUP BY department_id )table_minsalary ) ); #除了groupby和limmit不能写子查询，其余地方都可以写，包括select #10.查询平均工资最高的job信息 SELECT department_id FROM employees GROUP BY department_id HAVING avg(salary)>=ALL( SELECT AVG(salary) FROM employees GROUP BY department_id ); SELECT * FROM jobs WHERE job_id = ( SELECT job_id FROM employees GROUP BY job_id HAVING AVG(salary) = ( SELECT MAX(m_s) FROM( SELECT AVG(salary) m_s FROM employees GROUP BY job_id ) table_maxsalary ) );
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
综合案例
#综合案例 #1、创建数据库test0l_library CREATE DATABASE IF NOT EXISTS tlibrary CHARACTER SET 'utf8'; USE tlibrary; #2.创建book表 CREATE TABLE IF NOT EXISTS books( id INT, `name` VARCHAR(50), `authors` VARCHAR(100), price FLOAT, pubdate YEAR, note VARCHAR(100), num INT ) SELECT * FROM books; #3、向books表中插入记录 #1)不指定字段名称，插入第一条记录 INSERT INTO books VALUES (1,'自然语言','liping',10000,'2022','science',10000); #2)指定所有字段名称，插入第二记录 INSERT INTO books(id,name,authors,price,pubdate,note,num) VALUES(8,'C++','liping',365,2022,'study',0); #3)同时插入多条记录（剩下的所有记录) INSERT INTO books(id,name,authors,price,pubdate,note,num) VALUES (4,'love story','xiaozhu',200,2022,'love',20), (5,'rose','xiaoli',200,2021,'story',6), (6,'C','谭浩强',200,2022,'study',20); INSERT INTO books(id,name,authors,price,pubdate,note,num) VALUES (9,'java','xiaozhu',200,2022,'study',20), (10,'HTML','xiaoli',200,2021,'study',6), (11,'Go','谭浩强',200,2022,'study',20); ALTER TABLE books SET COLUMN 'history' 'study' WHERE id = 7 #4.将历史学科的书的价格+5元 UPDATE books SET price = price + 5 WHERE note='history'; #5、将名称为简和爱的书的价格改为40，并将说明改为drama。 UPDATE books SET price=40,note='drama' WHERE name='简和爱' #6、删除库存为0的记录。 DELETE FROM books WHERE num = 0 #7、统计书名中包含o字母的书 SELECT name as 含有o的书 FROM books WHERE name like '%o%' #8、统计书名中包含a字母的书的数量和库存总量 SELECT COUNT(name),sum(num) FROM books WHERE name like'%o%' SELECT * FROM books; # 9、找出"histroy"”类型的书，按照价格降序排列 SELECT * FROM books WHERE note='study' ORDER BY price DESC; #10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列 SELECT * FROM books ORDER BY num DESC,note ASC; # 11、按照note分类统计书的数量 SELECT note,COUNT(*) FROM books GROUP BY note #12、按照note分类统计书的库存量，显示库存量超过30本的书名 SELECT note,sum(num) FROM books GROUP BY note HAVING SUM(num) > 30 #13、查询所有图书，每页显示5本，显示第二页 #显示第一页 SELECT * FROM books LIMIT 5 #显示第二页，5(每页显示的数量)，5（偏移量，第几页） SELECT * FROM books LIMIT 5,5 # 14、按照note分类统计书的库存量，显示库存量最多的 SELECT note,SUM(num) FROM books GROUP BY note LIMIT 0,1 #15、查询书名达到10个字符的书，不包括里面的空格 SELECT CHAR_LENGTH(replace(name,' ','')) FROM books #replace(name,' ',''),第一个参数是列名、第二个参数是要处理的，第三个参数是要替换的 SELECT name FROM books WHERE CHAR_LENGTH(replace(name,' ','')) > 6 #16、查询书名和类型，其中note值为science显示科学，study显示学习，dram显示戏剧，love显示爱情，history显示历史 SELECT name '书名',note ,CASE note WHEN 'science' THEN '科学' WHEN 'study' THEN '学习' WHEN 'dram' THEN '戏剧' WHEN 'love' THEN '爱情 ' WHEN 'history' THEN '历史' ELSE '其他' END '类型' FROM books #17、查询书名、库存，其中num值超过30本的，显示滞销，大于o并低于10的，显示畅销，为o的显示需要尹 SELECT name AS "书名" , num AS"库存",CASE WHEN num > 30 THEN'滞销' WHEN num > 0 AND num <10 THEN '畅销' WHEN num = 0 THEN '无货' ELSE"正常" END"显示状态" FROM books; #18、找出最早出版的一本书 SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1; #19、找出study中价格最高的一本书 SELECT * FROM books WHERE note = 'study' ORDER BY price DESC LIMIT 0,1; #20、找出书名中字数最多的一本书，不含空格 SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC LIMIT 0,1
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
在定义数据类型时
如果确定是整数，就用INT;
如果是小数，一定用定点数类型DECIMAL(N,D);
如果是日期与时间，就用DATETIME。
11.存储过程与函数
1.存储过程的定义：
存储过程（stored procedure），是一组经过预先编译的SQL语句的封装
2.存储过程和存储函数的区别
存储过程和存储函数的区别：存储过程是没有返回值的，存储函数是有返回值的
3.好处：
（1）简化操作，起高了sql语句的重用性，减少了开发程序员的压力
（2）减少操作过程中的失误，提高了效率
（3）减少网络传输量
（4）减少sql语句暴露网络上的风险
4.视图和函数的对比
视图是虚拟表，通常不对底层数据表直接操作，而且存储过程是程序化的sql，可以直接操作底层数据，相比于面向集合的操作方式，能够实现一些更复杂的数据处理
5.视图的操作
#0.准备工作 CREATE DATABASE dbtest1; USE dbtest1; #复制一份数据 CREATE TABLE employees AS SELECT * FROM atguigudb.employees; CREATE TABLE departments AS SELECT * FROM atguigudb.departments; #查看 SELECT * FROM employees; SELECT * FROM departments; #1.创建存储过程 #类型1，无参数、无返回值 DELIMITER $ CREATE PROCEDURE select_all_data() BEGIN SELECT * FROM employees; END $ DELIMITER; #2.存储过程的调用 CALL select_all_data(); #举例1:创建存储过程avg_employee_salary()，返回所有员工的 #平均工资 DELIMITER $ CREATE PROCEDURE avg_employee_salary() BEGIN SELECT AVG(salary) FROM employees; END $ DELIMITER; #调用存储过程 CALL avg_employee_salary(); #举例3:创建存储过程show_max_salary()，用来查看"employee"表的最高薪资值。 DELIMITER // CREATE PROCEDURE show_max_salary() BEGIN SELECT MAX(salary) FROM employees; END // DELIMITER; CALL show_max_salary(); #类型2，带out #举例：创建存储过程show_min_salary(),查看employees表最低的薪资，并将最低薪资通过out参数ms输出 DESC employees; DELIMITER // CREATE PROCEDURE show_min_salary(OUT ms DOUBLE) BEGIN SELECT MIN(salary) INTO ms FROM employees; END // DELIMITER; #调用 CALL show_min_salary(@ms); #查看值 SELECT @ms; #举例5:创建存储过程show_someone_salary()，查看"employees"表的某个员工的薪资，并用IN参数empname输入员工姓名。 DELIMITER // CREATE PROCEDURE show_someone_salary(IN ename VARCHAR(20)) BEGIN SELECT salary FROM employees WHERE last_name = ename; END // DELIMITER; #调用方式一 CALL show_someone_salary('Abel'); #调用方式二 SET @ename = 'Abel'; CALL show_someone_salary(@ename); #举例6:创建存储过程show_someone_salary2()，查看"employees"表的某个员工的薪资， #并用IN参数empname输入员工姓名，用ouT参数empsalary输出员工薪资 desc employees; DELIMITER // CREATE PROCEDURE show_someone_salary2(IN ename VARCHAR(25),OUT esalary DOUBLE) BEGIN SELECT salary INTO esalary FROM employees WHERE last_name=ename; END // DELIMITER; #调用 SET @ename='Abel'; CALL show_someone_salary2(@ename,@esalary); SELECT @esalary; #4.存储过程、函数的修改 ALTER PROCEDURE show_max_salary SQL SECURITY INVOKER COMMENT'查询最高工资'; #5．存储过程、函数的删除 DROP FUNCTION IFEXISTs count_by_id; DROP PROCEDURE IFEXISTs show_min_salary; CREATE DATABASE test2; USE test2; #1．创建存储过程insert_user()，实现传入用户名和密码，插入到admin表中 CREATE TABLE admin( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(15) NOT NULL, pwd VARCHAR(25) NOT NULL ); DELIMITER $ CREATE PROCEDURE insert_user(IN uname VARCHAR(15),IN pwd VARCHAR(25)) BEGIN INSERT INTO admin(user_name,pwd) VALUES(uname,pwd); END $ DELIMITER; SELECT * FROM admin; CALL insert_user('tom','pwd'); #2．创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话 CREATE TABLE beauty( id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(15) NOT NULL, phone VARCHAR(15) UNIQUE, birth DATE ); INSERT INTO beauty(`name`,phone,birth) VALUES ('liping','123456','1998-02-14'), ('li','1234567','1998-02-14'), ('lipingping','12345','1998-02-14'), ('ping','1234566','1998-02-14'); SELECT * FROM beauty; DELIMITER $ CREATE PROCEDURE get_phone(IN id INT ,OUT name VARCHAR(15),OUT phone varchar(15)) BEGIN SELECT b.`name`,b.phone into `name`,phone FROM beauty b WHERE b.id = id; END $ DELIMITER; CALL get_phone(16,@name,@phone); SELECT @name,@phone; #3．创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回 DELIMITER $ CREATE PROCEDURE format_date(IN my_date DATE,OUT str_date VARCHAR(25)) BEGIN SELECT DATE_FORMAT(my_date,'%y年%m月%d日') into str_date; END $ DELIMITER; CALL format_date(CURDATE(),@str); SELECT @str; #4．创建存储过程date_diff( )，实现传入两个女神生日，返回日期间隔大小 DELIMITER $ CREATE PROCEDURE date_diff(IN birth1 DATE,IN birth2 DATE,OUT sum_date INT) BEGIN SELECT DATEDIFF(birth1,birth2) INTO sum_date; END $ DELIMITER; SET @birth1='1998-02-14'; SET @birth2='1999-10-30'; CALL date_diff(@birth1,@birth2,@sum_date); SELECT @sum_date;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
6.存储函数
相关操作
#创建存储函数，名称为email_by_name(),参数定义为空 #该函数查询Abel的email,并返回，数据类型为字符串型 DELIMITER // CREATE FUNCTION email_by_name() RETURNS VARCHAR(25) DETERMINISTIC CONTAINS SQL READS SQL DATA BEGIN RETURN (SELECT email FROM employees WHERE last_name='Abel'); END // DELIMITER; SELECT email_by_name(); #无参有返回 #1．创建函数get_count (),返回公司的员工个数 DELIMITER $ CREATE FUNCTION get_count() RETURNS INT BEGIN RETURN(SELECT COUNT(*) FROM employees); END $ DELIMITER; SELECT get_count(); #举例2:创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型万整型。 DELIMITER // CREATE FUNCTION count_by_id(dept_id INT) RETURNS INT BEGIN RETURN(SELECT COUNT(*) FROM employees WHERE department_id=dept_id); END // DELIMITER; SET @dept_id:=30; SELECT count_by_id(@dept_id); SET GLOBAL log_bin_trust_function_creators = 1; #有参有返回 #2创建函数ename_salary(),根据员工姓名，返回它的工资 DELIMITER $ CREATE FUNCTION ename_salary(emp_name VARCHAR(25)) RETURNS DOUBLE BEGIN RETURN (SELECT salary FROM employees WHERE last_name = emp_name ); END $ DELIMITER; SELECT ename_salary('Abel'); #创建函数dept_sal(),根据部门名，返回该部门的平均工资 DELIMITER // CREATE FUNCTION dept_sal(dept_name VARCHAR(15)) RETURNS DOUBLE BEGIN RETURN(SELECT AVG(salary) FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name = dept_name ); END // DELIMITER; SELECT dept_sal('Marketing'); #4．创建函数add_float ()，实现传入两个float，返回二者之和 DELIMITER // CREATE FUNCTION add_float(num1 FLOAT,num2 FLOAT) RETURNS FLOAT BEGIN RETURN(SELECT num1+num2); END // DELIMITER; SET @num1 := 12; SET @num2 := 13; SELECT add_float(@num1,@num2);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
7.存储函数优点
(1)、存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了SQL的执行效率。
(2)、可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。
(3)、存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。
8.存储函数的缺点
（1）、可移植性差。存储过程不能跨数据库移植，比如在MySQL、Oracle和sQL Server里编写的存储过程，在换成其他数据库时都需要重新编写。
（2）、调试困难。只有少数DBMS支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。|
（3）、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
（4）、它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。
12.游标
1.定义
游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让sQL这种面向集合的语言有了面向过程开发的能力。
在select语句查询的结构是多条记录，因此需要用游标机制将多条记录一次一条地送给主程序处理，从而把集合操作转换为对单个记录的处理。
在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。
这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。
MysQL中游标可以在存储过程和函数中使用。
2.游标的相关操作
/*游标的使用 #声明游标 #打开游标 #使用游标（从游标中获取资源） #关闭游标，游标会占用系统资源，如果不及时关闭，则会一直在存储过程中 */ #举例:创建存储过程get_count_by_limit_total_salary()"，声明IN参数limit_total_salary，DOUBLE类型; #声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值， #直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。 DELIMITER // CREATE PROCEDURE ts(IN limt_total_salary DOUBLE, OUT total_count INT) BEGIN #先声明变量 DECLARE sum_sal DOUBLE DEFAULT 0.0; DECLARE esal DOUBLE; DECLARE ecount INT DEFAULT 0; #声明游标 DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary desc; #使用游标 OPEN emp_cursor; #使用游标 REPEAT FETCH emp_cursor INTO esal; SET sum_sal = sum_sal + esal; SET ecount = ecount + 1; UNTIL sum_sal >= limit_total_salary END REPEAT; SET total_count = ecount; #关闭游标 CLOSE emp_cursor; END // DELIMITER; #调用 CALL ts(100000,@total_count); SELECT @total_count;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
3.游标的优缺点
游标是MysQL的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候不仅会影响业务之间的效率, 还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。
建议:养成用完之后就关闭的习惯，这样才能提高系统的整体效率。
13.缓冲池
1.数据库缓冲池(buffer pool)
InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面〈包括读页面、写页面、创建新页面等操作)。而磁盘I/O需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。.
这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行工/O 的时间。要知道，这种策略对提升sQL语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。
2.缓冲池(Buffer Pool)
InnoDB缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应Hash和数据字典信息等。
3.缓存原则:
“位置*频次”这个原则，可以帮我们对IO访问效率进行优化。
首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有200G，但是内存只有16G，缓冲池大小只有1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会`优先对使用频次高的热数据进行加载
14.索引
1.索引
1.定义：
索引是帮助MYSQL高效获取数据的数据结构。通过索引查找相关数据，不需要全表扫描，相当于书的目录。
2.本质：
索引的本质是数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级的查找算法
3.索引的优缺点
优点
（1）提高检索效率，降低数据的IO成本
（2）创建唯一索引，可以保证数据库表中每一行数据的唯一性
（3）在实现数据 的参考完整性方面，可以加速表和表之间的链接
（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗
缺点
（1）创建和维护索引要耗费时间，并且随着数量的增加，所耗费的时间也会增加
（2）索引需要占用磁盘空间，除了数据表占数据空间之外 ，每一个索引还要占用一定的物理空间，存储在磁盘上，索引文件可能更快的达到最大问价
（3）降低了更新表的速度，当对表中的数据进行增删改时，索引也要动态的维护，这样就降低了索引的维护速度
2.聚簇索引
1.聚簇索引是一种数据的存储方式
2.特点：
（1）页内的记录是按主键的大小顺序排成一个单链表
（2）页也是根据也中用户记录的主键大小顺序排成一个双向链表
（3）存放目录项记录的页分为不同的层次，在同一层次中的页也是根据目录项记录的主键大小排序成一个双向链表
（4）B+树的叶子结点存储的是完整的用户记录
3.聚簇索引优缺点：
优点：
（1）数据访问速度快
（2）聚簇索引对主键的排序查找和范围查找速度非常快
（3）节省了大量的IO操作，不需要全部提取，只需要按照一定的范围提取
缺点
（1）插入速度严重依赖插入顺序
（2）更新主键的代价高
（3）二级索引访问需要两次索引查找
3.原理：B树和B+树
1.B+树和B树的差异在于以下几点:
1.有k个孩子的节点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字数+1。
2．非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大(或最小)。
3，非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既
保存索引，也保存数据记录。
4.所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大
顺序链接。
2.Hash索引与B+树索引的区别
1、Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
2、Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，Hash索引在计算 Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
3、Hash索引不支持ORDER BY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面后模糊查询（比如%结尾）的话就可
4.操作
#隐式的方式创建索引。在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引 CREATE DATABASE dbtest2; USE dbtest2; CREATE TABLE dept ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(20) ) ; CREATE TABLE emp( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(20) UNIQUE, dept_id INT, CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)) ; #显示的方式创建 #创建普通的索引 CREATE TABLE book( book_id INT, book_name VARCHAR(100), authours Varchar(100), info varchar(100), comment VARCHAR(100), year_public YEAR, #声明索引 INDEX idx_bname(book_name) ); #通过命令方式查看索引 #方式一 SHOW CREATE TABLE book; #方式二 SHOW INDEX FROM book; #创建唯一索引 CREATE TABLE book1( book_id INT, book_name VARCHAR(100), authours Varchar(100), info varchar(100), comment VARCHAR(100), year_public YEAR, #声明索引 UNIQUE INDEX uk_idx_cmt(comment) ); INSERT INTO book1(book_id,book_name,authours,info,comment,year_public) VALUES (01,'人生','luyao','xiaoshuo','好看','2015'), (02,'活着','luyao','xiaoshuo','好看看','2015'); SELECT * FROM book1; CREATE TABLE book3( book_id INT, book_name VARCHAR(100), authours Varchar(100), info varchar(100), comment VARCHAR(100), year_public YEAR, #声明索引 INDEX mul_bid_bname_info(book_id,book_name,info) ); #索引按照最左原则，底层是B+树，不可能跳过第一个关键值，从第二个开始 SHOW INDEX FROM book3; CREATE TABLE book4( book_id INT, book_name VARCHAR(100), authours Varchar(100), info varchar(100), comment VARCHAR(100), year_public YEAR ); SHOW INDEX FROM book4; ALTER TABLE book4 ADD INDEX idx_cmt(comment); ALTER TABLE book5 ADD UNIQUE uk_idex_bname(book_name); #创建全文索引 CREATE TABLE test4( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX fultxt_idx_info(info(50)) ) #info(50)只用前50个作为索引 SHOW INDEX FROM test4;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
16.事务
1.事务的基本概念
一组逻辑操作单元，使数据从一个状态变换到另一种状态。
事务是用户定义一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位
2.事务的特性：ACID
（1）原子性（atomicity）：要么全部成功，要么全部失败回滚，原子性是基础
（2）一致性(consistency)：数据从一个合法的状态变换到另一个状态，一致性是约束条件
（3） 隔离性（isolation）：并发执行的各个事务之间不能相互干扰，隔离性是手段
（4）持久性（durablity）:事务一旦提交哦，它对数据库的改变就是永久性的，持久性是通过事务日志来保证的。日志包括重做日志和回滚日志，持久性是目的
3.事务的状态
（1）活动的：事务对应数据库操作正在执行
（2）部分提交：当事务的最后一个操作执行完成时，但与有操作都在内存中执行，所造成的影响并没有刷新到磁盘中，此时的事务啤酒处在部分提交状态。
（3）提交的：当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。
（4）失败的：当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。
显式事务
隐式事务
4.事务的四个隔离级别
1.读未提交
2.读已提交
3.可重复读
4.串行化
并发调度的可串行化：多个事务的并发执行是正确的，当且仅当其结果与按某一次串行地执行这些事务的的结果相同
冲突可串行化调度：
冲突操作：不同的事务对同一个数据的读写操作和写写操作
17.MVCC
1.MVCc(Multiversion Concurrency Control)，多版本并发控制。MVCC是通过数据行的多个版本管理来实现数据库的并发控制。
**事务是并发控制的基本单位，保证事务的ACID特性是事务处理的重要任务，而事务的ACID特性可能会被破坏，原因是多个事务并发操作造成的。**
- 1
2.并发操作带来的数据不一致性包括
1.丢失数据
两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失
例如: 甲售票点T1对某航班机票余额为16，乙售票点T2对该航班售票余额也为16
甲出售一张机票还剩15，写会数据库，乙出售一张机票还剩15，写会数据库
结果卖了两张票却只减少了一张票
这种情况这是由于并发控制导致数据的不一致性，是由于T2写会后覆盖了T1的修改
2.不可重复读
事务T1读取数据后，事务T2对该数据执行了更新操作，使得T1无法再现前一次的读取结果
不可重复度包括三种情况：
（1）事务T1读取数据后，事务T2对该数据执行了修改操作，当事务T1在此读取数据时与前一次读取的结果不一样
（2）事务T1按一定的条件从数据库中读取某些数据后，事务T2删除了其中部分数据记录，当T1按相同条件在此读取数据时，发现数据消失了
（3）事务T1按一定的条件从数据库中读取某些数据后，事务T2插入了一些数据记录，当T1按相同条件在此读取数据时，发现数据多了
3.读脏数据
事务T1修改某一数据并将其写会磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这时T1修改过的数据恢复原值，T2读到的数据与数据库中的数据不一致，则T2读到的数据就是在脏数据。
MVCC可以不采用锁的机制
并发及控制机制就是要用正确的方式来调度并发操作，使一个用户的执行不收其他事务的干扰，从而避免数据的不一致性。并发控制的机制有封锁、时间截、乐观控制法、多版本并发控制。
3.快照读与当前读
快照读又叫一致性读，读取的是快照数据。**不加锁**的简单的SELECT都属于快照读，即不加锁的非阻塞读
- 1
当前读读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的SELECT，或者对数据进行增删改都会进行当前读。
4.MVCC实现原理之ReadView
在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView了，它帮我们解决了行的可见性问题。
ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID("活跃"指的就是，启动了但还没提交)。
5.通过MVCC我们可以解决:
1.读写之间阻塞的问题。通过MVCC可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。
2.降低了死锁的概率。这是因为MVCC采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁
定必要的行。
3.解决快照读的问题。当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结
果，而不能看到这个时间点之后事务提交的更新结果。
18.锁
脏读：读到未提交的数据
幻读：前后读的数据量不同
1.锁的定义
锁是计算机协调多个进程或线程并发访问某一资源的机制。在程序开发中会存在多线程同步的问题，当多个线程并发访问某个数据的时候，尤其是针对一些敏感的数据（比如订单、金额等)，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。在开发过程中加锁是为了保证数据的一致性，这个思想在数据库领域中同样很重要。
在数据库中，除传统的计算资源〈如CPU、RAM、I/o等）的争用以外，数据也是一种供许多用户共享的资源。**为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MysQL的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。**所以锁对数据库而言显得尤其重要，也更加复杂。
2.锁使用的情况：
1.写–写情况：即并发事务相继对相同的记录做出改动
在这种情况下会发生脏写的情况，所以多个事务相继对同一条记录做该改动时，需要让他们排队执行，这个排队过程就是通过锁来实现的
三种情况：
（1）不加锁：不需要在内存中生成对应的锁结构，可以直接进行操作
（2）获取锁成功或者加锁成功：在内存中生成了相应的锁，而且锁结构的is_waiting是false，也就是事务可以继续执行操作
（3）获取锁失败，或者加锁失败，或者没有获到锁：
2.读–写或者写–读
读写或者写读，即一个事物进行读取操作，一个事务进行改动操作，这种情况下可能发生脏读、不可重复读、幻读的问题
解决方法：
（1）读操作利用多版本并发控制（MVCC）,写操作进行加锁
所谓的WVCC，就是生成一个ReadView，通过ReadView找到符合条件的记录版本（历史版本由undo日志构建)。查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。
（2）读和写都采用加锁的方式
采用MVCC方式的话，读-写操作彼此并不冲突，性能更高。
采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。
3.对锁操作类型的分类
1.封锁类型
-
读锁/共享锁（s锁）
- 针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，不会互相阻塞
-
写锁/排它锁（X锁）
- 当前写操作没有完成，它会阻断其他写锁和读锁，这样就确保在给定时间内，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源
2.封锁协议
-
一级封锁协议
- 事务T1在修改数据R之前必须对其加上X锁，直到事物结束才释放
-
二级封锁协议
- 在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加S锁，读完后可以释放S锁
-
三级封锁协议
- 在一级封锁协议上增加事务T在读取数据R之前必须先对其加S锁，直到事务结束后才释放
3.封锁方法带来的问题
4.锁的粒度：封锁对象的大小
1.表级锁，可以避免死锁
- 表级锁的S锁、X锁
- 意向锁：如果一个结点被加锁，则该结点的下层正在被加锁
- 定义
- 协调行锁和表锁的关系，支持多粒度的锁并存
- 意向锁是一种不与行级别冲突的锁
- 如果对一个节点加上意向锁，则说明该结点的下层结点正在被加锁，对任意一节点加锁时，必须先对它的上层结点加意向锁
- 分类
- 意向共享锁
- 事务有意向对表的某些数据加共享锁
- select colum from table…lock in share mode
- 事务有意向对表的某些数据加共享锁
- 意向排它锁
- 事务有意向对表中的某些行加排它锁
- 意向共享锁
- 解决的问题
- 在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。
- 定义
- 自增锁
- 对某个列添加AUTO_INCREMENT
- 一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞
- 元数据锁 （MDL锁）
2.行级锁
- 记录锁 Record Locks
- 间隙锁 Gap Locks
- gap锁的提出仅仅是为了防止插入幻影记录而提出的。虽然有共享gap锁和独占gap锁这样的说法，但是它们起到的作用是相同的。而且如果对一条记录加了gap锁(不论是共享gap锁还是独占gap锁)，并不会限制其他事务对这条记录加记录锁或者继续加gap锁。
- 临间锁 Next-Key Locks
- 插入意向锁
3.页级锁
- 页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
多粒度封锁协议：
- 允许多粒度树中的每个节点被独立地加锁，对一个结点加锁，则该结点的后裔也被加上同类型的锁
5.对待锁的态度划分
悲观锁
乐观锁
6.加锁方式
隐式封锁
- 该数据对象没有被独立加锁，是由于其上级节点加锁而是该数据对象加上了锁
显示封锁
- 应事务的要求直接加到数据对象上的锁
7.其他
全局锁
死锁
19.数据库设计
1.定义
是对于一个给定的应用环境，构造优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，是指能够有效的管理和存储数据，满足各种用户的应用需求
2.设计规律
三分技术，七分管理，十二分基础数据。十二分基础数据强调了数据的收集、整理、组织和不断更新数据库建设中的重要环节
3.数据库设计的基本步骤
（1）需求分析：调查、收集与分析
（2）概念结构设计：对用户需求进行综合、归纳与抽象，形成一个独立于具体数据库管理系统的概念模型，E-R图（实体用矩形、属性用椭圆形、联系用菱形）、UML(实体类型：用类表示；实体码：类图的属性后面加上PK；联系)
（3）逻辑结构设计阶段：将概念模型转换为数据库管理系统所支持的数据模型，并对其进行优化
（4）物理结构设计阶段
（5）数据库实施阶段
（6）数据库运行和维护阶段
