路飞学城Python-Day40（第四模块复习题）

数据库

一、简答题

1、说说你所知道的MySQL数据库存储引擎，InnoDB存储引擎和MyISM存储引擎的区别？

1.InnoDB存储引擎(MySQL默认存储引擎)，支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁

2.MyISAM 存储引擎 不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数

3.NDB 存储引擎 NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型

4.Memory 存储引擎 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失

5.Infobright 存储引擎 存储是按照列而非行的

6.NTSE 存储引擎 提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持

7.BLACKHOLE 黑洞存储引擎，可以应用于主备复制中的分发主库

---

2、MySQL中char和varchar的区别，varchar(50)和char(50)分别代表什么意思？

char和varchar都是字符串类型，

varchar(50)是一次最多可以存储50个字符

char(50)是一次可以最多存储50个字符

char是定长字符类型，就是无论存储多少的字符，都会变成定长的填充字符，存储方式简单暴力，存储提取速度快，但是不够节省空间，适用于常用查询数据

varchar是变长字符类型，存储的字符包含一个数据头(统计存入的该条数据的字符长度)+数据，节省空间，但是存储速度和提取速度慢，适用于固定、不常用数据存储

注意：一定条件下，设计表格数据格式先设计char再设计varchar有利于提高数据的操作效率。

---

3、MySQL中int类型存储多少个字节？

int的存储宽度是4个字节，即32位，位数就是2^32

当有符号的时候 -2^31~2^31-1

没有符号的时候  0~2^32

---

4、主键具有什么特征？

主键是唯一的且不为空值

---

5、简述你对inner join、left join、right join、full join的理解；

inner join 内连接 两个表连接显示公共部分

left join   左连接 优先显示左表的匹配内容，左表不匹配的也显示出来

right join 右连接 优先显示右表的匹配内容，右表不匹配的也显示出来

full join   外连接 先匹配两表的共有部分，然后把两表的非共有部分显示出来(MySQL不支持full join 可以用union)

---

6、concat, group_concat函数的作用是什么？

concat可以用来连接字符串

group_concat可以和group by一起使用，查看分组后的信息

---

7、请介绍事务的实现原理；

将各种sql语句(原子型语句)封装成一个事务状态，只有当这个事务全部完成的时候才能结束，否则就会调用roll back函数对之前的语句进行回滚，返回原来的状态

---

8、索引的本质是什么？索引有什么优点，缺点是什么？

索引的本质就是对某些字段加键的约束关系，使得查询的时候依据索引查找减少查询的范围，让查询的时间损耗尽量小

优点：提高了查询速度和查询效率

缺点：1.加索引本身很麻烦，索引也会占存储空间，索引多对存储空间有影响

         2.对表记录加索引后，后续想再对表进行修改，索引也要随着修改，会加长修改表的时间

---

9、哪些情况下需要创建索引，哪些情况下不需要创建索引？

1.对于数据量大，且查询频繁的，表数据结构不容易发生表更的，可以采用创建索引的方式提高效率

2.数据量小，表数据内容和数据结构都不断更新修改的，不利于创建索引

---

10、请分别介绍ACID代表的意思，什么业务场景需要支持事务，什么业务场景不需要支持事务？

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

银行的交易系统机制、重要的数据计算(涉及多个终端的交互)类似的场景都需要支持事务

简单的查询语句，不关系到数据修改的等都不需要支持事务

---

11、什么是触发器，请简述触发器的使用场景？

触发器就是在一定条件下执行的语句，可以定制用户对表进行增删改后的操作，但是查询的时候不能触发

---

12、什么是存储过程，存储过程的作用是什么？

存储过程内采用了一系列的sql语句对数据库进行操作，可以采用别名的方式就对数据库进行调用

将程序和数据的操作进行解耦，程序员只要关注程序的执行就可以了，实际数据库的操作可以直接掉用别名

以别名的方式进行网络传输，传输的数据量小，传输效率更高

缺点：由于全是别名封装的数据功能，对于程序员来说扩展的功能不易实现，需要经过DBA认同后才能对数据进行功能扩展。

---

13、什么是视图，简单介绍视图的作用和使用场景？

视图是基于数据库的一张临时表，这张临时表的数据结果可以直接调用，不用再重写数据库的查询语句

视图的作用：视图可以实现对数据的多次查询，直接调用视图名称就能实现

视图的使用场景：需要对数据进行多次查询，且查询语句复杂时，不建议多次创建视图，创建视图后，将不再能对视图数据就行修改，对视图数据的修改将会影响到原表的数据

---

14、如何查看SQL语句的执行计划？

MySQL的执行计划(explain)是对于SQL语句的优化和建立索引的重要依据

eg: explain select * from table 

附件：explain内的参数说明

table 
显示这一行的数据是关于哪张表的

type 
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
说明：不同连接类型的解释（按照效率高低的顺序排序）
system：表只有一行：system表。这是const连接类型的特殊情况。
const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。
eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

possible_keys 
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key 
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len 
使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref 
显示索引的哪一列被使用了，如果可能的话，是一个常数

rows 
MYSQL认为必须检查的用来返回请求数据的行数

Extra 
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
说明：extra列返回的描述的意义
Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

---

15、在你本地数据库中查看*select * from student*的执行计划，并解释每个字段分别代表什么意思？

id  查询到的数据的顺序
select_type 查询到的语句的类型
table  查询的表名称
partitions  分区表的分区名，对于非分区表，为NULL
type 类型
possible_keys 可能的索引关系 
key 是否建立了索引
key_len 索引的长度
ref 显示索引的哪一列被使用了，如果可能的话，是一个常数
rows 返回查询的列数
filtered  被条件过滤掉的百分比
Extra  额外说明

16、数据备份分为哪几种类型？增量备份和差异备份的区别是什么？

1.完整备份  备份系统中的所有数据。特点：占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快

2.增量备份   只备份上次备份以后有变化的数据因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，

            所以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时间长

3.差异备份   只备份上次完全备份以后有变化的数据；占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完

整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间

---

17、请介绍*select*语句的执行顺序；

from  先找到表位置
where 按条件查找表记录
group by 根据字段进行分组
having  根据条件过滤表内容
select  选择表字段
distinct 去除重复值
order by 按某一个或多个字段排序
limit  提取查询到的记录条数

---

18、请问存储引擎MyISM和InnoDB的适合什么样的使用场景？

MyISM 不支持事务，但是查询速度快，适用于计算、查询量大的场景

InnoDB 支持事务查询，数据可靠性要求高，适用于查询、插入数据频繁的场景

---

19、请举出MySQL中常用的几种数据类型；

1.数字类型

整型 int 不用指定字节大小

浮点型 float 可用于资薪处理

2.字符串类型

char 定长

varchar 变长

3.日期类型

datetime

4.枚举类型和集合类型

单选类型 enum

多选类型 set

---

20、什么情况下会产生笛卡尔乘积，如何避免？

多表查询的时候就会产生笛卡尔乘积

比如select * from table1,table2

尽量不要直接使用笛卡尔乘积

使用内连接、外连接、左连接或右连接的方法或者直接在多表连接的查询后增加条件过滤数据

---

21、请列举MySQL中常用的函数；

1.数学函数 round(x,y) 返回参数x的四舍五入的有y位小数的值

2.聚合函数

count()统计数字

avg()求平均值

max()求最大值

min()求最小值

sun()求和

3.字符串函数

concat()字符串拼接

4.日期函数

current_time()返回当前的时间

5.控制函数

if else

case then

loop

---

22、请说明group by的使用场景；

group by 是在where之后执行的，需要在表后对记录进行字段上的分组，分组后的查询需要用到聚合函数

---

23、请介绍hash索引和B+树索引的实现原理；

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接

---

二、编程题

1、创建一个表student，包含ID(学生学号)，sname(学生姓名)，gender(性别)，credit(信用卡号),四个字段，要求：ID是主键，且值自动递增，sname是可变长字符类型，gender是枚举类型, credit是可变长字符类型；

CREATE TABLE student (
ID INT PRIMARY KEY auto_increment not NULL ,
sname VARCHAR (10),
gender enum('男','女') DEFAULT '男',
credit VARCHAR (20)
);

---

2、在上面的student表中增加一个名为class_id的外键，外键引用class表的cid字段；

CREATE table class(
cid int NOT NULL PRIMARY KEY auto_increment,
cname VARCHAR (5)
);

ALTER table student ADD class_id INT UNIQUE ;
ALTER table student ADD FOREIGN KEY (class_id)
REFERENCES class(cid)
ON DELETE CASCADE
ON UPDATE CASCADE ;

---

3、向该表新增一条数据，ID为1，学生姓名为alex，性别女，修改ID为1的学生姓名为wupeiqi，删除该数据；

insert into class (cname) values (
('一班')
);
INSERT INTO student(sname,gender) VALUES (
'alex','女'
);
UPDATE student
SET sname = 'wupeiqi'
where ID = 1;

DELETE from student
where ID = 1;

---

4、查询student表中，每个班级的学生数；

SELECT c.cname,count(ID) from student s
LEFT JOIN class c
ON c.cid = s.class_id
GROUP BY ID;

---

5、修改credit字段为unique属性；

alter TABLE student modify credit VARCHAR (18) UNIQUE ;

---

6、请使用命令在你本地数据库中增加一个用户，并给该用户授予创建表的权限；

grant CREATE ON *.* TO 'panda@localhost' identified by'123';

---

7、请使用pymysql模块连接你本地数据库，并向student表中插入一条数据；

import pymysql

conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
db='db6',
charset='utf8'
)
cursor = conn.cursor()
sql = "insert into student values(13,'park','男','123456',1)"
rows = cursor.execute(sql)

conn.commit()
cursor.close()
conn.close()

---

8、请使用mysqldump命令备份student表；

cd C:Program FilesMySQLMySQL Server 5.7in
mysqldump -uroot -p123456 db6 student > student1.sql

---

9、创建一张名为student_insert_log的表，要求每次插入一条新数据到student表时，都向student_insert_log表中插入一条记录，记录student_id, insert_time;

delimiter //
CREATE TRIGGER tri_student_insert_log AFTER INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO student_insert_log(student_id, insert_time) VALUES(NEW.ID, now()) ;
END//
delimiter ;