MySQL
MySQL的练习题
https://www.cnblogs.com/pythonzwd/p/10820151.html
sql 功能语句
-
DCL(Data Control Language)数据控制语言:
grant(授权),revoke(取消权限)… -
DDL(Data Definition Language)数据定义语言:
create,alter,drop,truncate,comment on,rename to… -
DML(Data Manipulating Language)数据操纵语言:
insert,update,delete补充 - 凡是可以进行事务回滚(撤销操作)的话 - DML操作.
-
DQL(Date Query Language)数据查询语句:
select -
DTL(Data Transaction Language)数据事务语言:
commit(提交),rollback,savepoint
关系型数据库和非关系型数据库
- 关系型数据库
- 表和表、表和字段、数据和数据存在着关系
- 优点：
- 数据之间有关系，进行数据的增删改查的时候是非常方便的
- 关系型数据库是有事务操作的，保证数据的完整性和一致性。
- 缺点：
- 因为数据和数据是有关系的，底层是运行了大量的算法
大量算法会降低系统的效率，会降低性能 - 面对海量数据的增删改查的时候会显的无能为力
- 海量数据对数据进行维护变得非常的无力
- 因为数据和数据是有关系的，底层是运行了大量的算法
- 举例: oracle数据库,mysql数据库 ,SQL Server数据库
- 非关系型数据库
- 为了处理海量数据，非关系数据库设计之初就是为了替代关系型数据库的关系
- 优点：
- 海量数据的增删改查是可以的
- 海量数据的维护和处理非常轻松
- 缺点：
- 数据和数据没有关系，他们之间就是单独存在的
- 非关系数据库没有关系，没有强大的事务关系，没有保证数据的完整性和安全性
- 举例 : redis（持久化缓存),
mongodb(文档的数据库)
存储引擎
- Innodb:
- MySQL 5.5版本后默认的存储引擎为InnoDB。
- 支持事务，支持外键，行锁，查表总行数时，全表扫描
- MYISAM:
- MyISAM是MySQL的默认数据库引擎（5.5版之前）
- 不支持事务，不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描
- 由于不支持事务和行级锁，最大的缺陷就是崩溃后无法安全恢复
- MEMORY:
- 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
- Merge：
- 将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用
- Archive：
- 非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差
存储过程
定义
MySQL 5.0 以后才支持存储过程
存储过程，又称存储程序，是在数据库存储复杂程序，以便外部程序调用的数据库对象，可以视为数据库的一种函数或子程序。是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
优点
- 增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
- 标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
- 较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
- 减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
- 作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
存储过程和函数的区别
- 函数必须指定返回类型,但是存储过程不需要
- 函数必须使用return+返回值,但是存储过程可以没有返回结果.
drop和truncate和delete三者之间的区别
- delete属于DML语句，drop和truncate属于DDL语句
- drop删除的是整张表，delete和truncate只会删除表中的数据，但会保留表结构
- 从速度上来看，drop>truncate>delete
- 当不需要一张表的时候使用drop，当要删除表的部分记录的时候使用delete，当删除所有的数据只保留表结构的时候使用truncate。
- truncate不能和where结合起来使用,进行删除操作时不会进行存储,不会被回滚，delete可以和where结合起来使用,操作时原数据会被放到rollback segment中,可以被回滚
三大范式
1NF:原子性,也就是说表中任何一个列都是唯一的,不可再拆分的总结:列不可再分
2NF:在1NF的基础上,不存在非关键列部分依赖于关键列,也就是说所有的非关键列都必须完全依赖于关键列.
3NF:在2NF的基础上,不存在非关键列传递依赖于关键列,也就是说,所有的非关键列都必须直接依赖于关键列
事务
定义
就是指一组相关的SQL操作,我们所有的操作都是处在事务中的.
事务控制语句 （DTL）
-
BEGIN或START TRANSACTION；显式地开启一个事务；
-
**COMMIT；**也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
-
**ROLLBACK；**有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
-
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
-
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
-
ROLLBACK TO identifier；把事务回滚到标记点；
事务的四大特性(ACID):
-
atomic,原子性,事务是不可再分割的,要么同时成功,要么同时失败.
转账业务(账户+money,账户-money - 一组sql - 至于一个事务当中,并且要保证原子性.)
-
consistency,一致性,事务一旦结束,内存中的数据和数据库中的数据是保持一致的
example：A用户和B用户进行转账1000元，A账户减少1000和B账户增加1000的sql语句必须全部执行成功，不存在一般成功，一般失败。
但是无论如何执行，两个账户金额的改变加起来一定1000元，这就是一致性(完整性)。 -
isolation,隔离性,事务之间互不干扰,一多个用户并发访问数据库，比如同时操作一张表。数据库为每一个用户开启事务。每个事务相互独立，互不干扰。
-
duration,持久性,事务一旦提交,则数据持久化到数据库中,永久保存
[涉及到数据库的备份和恢复的一些技术,冷备份,热备份…]
多事务并发处理机制
原因:多个事务同时操作一个表中的同一行数据(争抢临界资源),如果这些操作是.修改操作的话,就会产生并发问题,如果不处理,则会造成数据不一致的情况.
数据库可能产生的并发问题包括:
脏读简介:一个事务读取到了另外一个事务中尚未提交的数据.
是指一个事务正在访问数据,并且对这个数据进行修改,而这种修改
还没有提交到数据库中,而另一个事务也访问了这个数据,并且使用了这个数据.
解决方法:一个事务在修改数据时,该数据不能被其他事务访问
不可重复读是指一个事务多次读取同一条记录,如果此时另一个事务也访问并且
修改了该数据,则就会出现多次读取出现数据不一致的情况,原来的
数据变成了不可重复读取的数据
解决方法:只有在修改事务完全提交过后才可以读取到数据
幻读是指一个事务修改表中的多行记录,但是此时另一个事务对该表格进行
了插入数据的操作,则第一个事务会发现表格中会出现没有被修改的行,
就像发生了幻觉一样.
解决方法:在一个事务提交数据之前,其他事务不能添加数据
不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了幻读的重点在于新增或者删除
事务隔离级别
- READ_UNCOMMITTED 读未提交
这是事务最低的隔离级别，它充许另外一个事务可以看到这个事务未提交的数据。
解决第一类丢失更新的问题，但是会出现脏读、不可重复读.
- READ_COMMITTED 读已提交
保证一个事务修改的数据提交后才能被另外一个事务读取，即另外一个事务不能读取该事务未提交的数据。
解决第一类丢失更新和脏读的问题，但会出现不可重复读.
- REPEATABLE_READ 可重复度
简介:mysql默认的事务的隔离级别.
保证一个事务相同条件下前后两次获取的数据是一致的
解决第一类丢失更新，脏读、不可重复读.
- SERIALIZABLE 可序列化
事务被处理为顺序执行。解决所有问题 - 有点类似于锁表.
性能及其低下.
索引
底层原理 数据结构： B+ Tree
-
MyISAM
-
有单独的索引文件的,索引过多 - 索引文件变大(占空间的)
-
叶子节点中保存的是索引+物理行地址的
索引的本质:键值对(索引列值,物理行地址)
-
先判断查询是否走了索引,先查询索引文件,找到物理行地址
再由地址直接定位到数据表.
-
索引是单独的文件 - 非聚簇索引
-
-
InnoDB
- 索引文件不是一个单独的文件,它和数据文件是合二为一的.
- 索引和数据->数据文件中 -> 聚簇索引
索引类型
-
普通索引(index)：仅仅是加快了查询速度
-
**唯一索引(unique)：**行上的值不能重复
-
**主键索引(primary key)：**不能重复
-
全文索引(fulltext):仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
-
组合索引[覆盖索引]：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。
所谓最左前列，指的是查询从索引的最左前列开始，并且不跳过索引中的列
优缺点：
- 优点[作用]：
- 加快数据库的检索速度
- 降低了增删改等操作的效率
- 加速了表与表之间的连接
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
- 缺点:
- 创建和维护索引需要耗费时间，
- 索引需要耗费物理空间
- 对表中的数据进程增删改操作时，索引也要进行维护，这样就降低了数据的维护速度。
建立索引的规则
- 表的主键、外键必须有索引；
- 数据量超过300的表应该有索引；
- 经常与其他表进行连接的表，在连接字段上应该建立索引；
- 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
- 引应该建在选择性高的字段上；
- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
- 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替
- 频繁进行数据操作的表，不要建立太多的索引；
- 删除无用的索引，避免对执行计划造成负面影响；
索引失效的场景：
- 索引列进行了计算
- 查询的数量是表中大部分的数据,超过了30%.
- 索引列放入到mysql函数中了,也会失效
- 模糊查询也会导致索引列失效.
视图
定义
计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。
但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
使用视图原因
- 视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
- 关系型数据库中的数据是由一张一张的二维关系表所组成，简单的单表查询只需要遍历一个表，而复杂的多表查询需要将多个表连接起来进行查询任务。对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图（view）功能。
-- 视图是一张"虚拟"表
create view student_view
as select sno,sname,sgender from student;
-- 查询视图
mysql>select * from student_view;
-- 删除视图
mysql>drop view student_view;
视图的作用
- 当需要进程多表查询时提供了SQL语句的重用性
- 对象数据库进程重构，但不影响程序的运行
- 提高了安全性能，可以对不同的用户设定不同的视图
视图的WITH CHECK OPTION
- 如果视图在创建时指定了"WITH CHECK OPTION",那么更新数据时不能插入或者更新不符合视图限制条件的记录。
视图分类
-
简单视图
简单视图来源于单张表，修改简单视图会影响到数据库中的表
-
复杂视图
复杂视图来源于多张不同的表，修改视图不会影响表中数据
数据库相关题目
已知教学数据库包含三个关系：
学生S（SND,SNAME,SA,SD）
课程C（CNO,CN,TNAME）,
选课SC（SNO,CNO,G）
其中SNO代表学号，SNAME代表学生姓名，SA代表学生年龄，SD代表学生所在系，CNO代表课程号，CN代表课程名，TNAME代表任课老师姓名，G代表成绩，
请用SQL语句实现：
1. 创建表
create table S(
sno int(10) primary key,-- 学号
sname varchar(20) not null,-- 姓名
sa int(3),--年龄
sd varchar(10)-- 所在系
)
create table C(
cno int(10) primary key,,-- 课程号
cn varchar (20) unique , -- 课程名
tname int(3) not null--任课老师
)
create table SC(
sno int(10),-- 学号
cno varchar(20), -- 课程号
g int(3),--成绩
constraint fk_sc_s foreign key(sno) references s(sno)
constraint fk_sc_c foreign key(cno) references c(cno)
primary key (sno,cno)
)
将下列学生信息插入学生关系中：李丹，18岁，电信系，学号：20070206
INSERT into S(SNO,SNAME,SD) VALUES(20070206,'李丹','电信系');
找出选修了课程为“112002”的学生学号和姓名
SELECT S.SNO,S.SNAME FROM S S,SC C where S.SNO=C.SNO AND C.CNO='112002';
修改学号为“20070206”的学生所在的系为计算机
UPDATE S SET SD='计算机' WHERE SNO='20070206'
查询选修了数据库系统原理这门课的学生的姓名和成绩
SELECT S.SNAME,C.G FROM S S,SC C where S.SNO=C.SNO;
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
