关系型数据库（SQL）相关练习题目

一、简答题

1.MySQL常见的三种存储引擎(InnDB、MyISAM、MEMORY)的区别(至少5点)？

答：引擎(engine)，中文百度百科定义为汽车发动机的主要核心组件，用来驱动汽车发动、前进、加速的一种组件，是汽车动力的来源。当引擎用在游戏中时，游戏引擎指的是控制所有游戏功能的主程序。
存储引擎： 是MySQL数据库特有的，不同的存储引擎提供不同的数据存储方式、数据检索方式以及使存储的数据具有一些特有的性能。因此我们可以根据要存储的数据类型来选择存储引擎。
事务：一般指要做的或所作的事情，通常由高级数据库操纵语言或编程语言书写的用户程序的执行引起的，由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

MySQL中有多种存储引擎供不同类型的数据使用，可以通过‘show engines’语句查询所有的存储引擎。
1).InnoDB存储引擎

• InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。MySQL5.5版本以后，默认存储引擎就是InnoDB，是MySQL上第一个提供外键约束的表引擎。
• InnoDB存储引擎支持AUTO_INCREMENT 。自动增长列的值不能为空，并且值必须唯一。MySQL中规定自增列必须为主键。在插入值得时候，如果自动增长列不输入值，则输入的值为自动增长后的值；如果自动增长列输入的值为0或空(NULL)，则插入的值也是自动增长后的值；如果插入某个确定的值，且该值在前面没有出现过，则可以直接插入。
• InnoDB支持外键（FOREIGN KEY）。外键所在的表叫做子表，外键所依赖(References)的表叫做父表.父表中被子表外键关联的字段必须为主键。当删除、更新父表中某条信息时，子表也必须有相应的改变，这是数据库的参照完整性规则。
• InnoDB中，创建的表的表结构存储在.frm文件中。数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。

总结： InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点时读写效率较差，占用的数据空间相对较大。

2).MyISAM存储引擎

• MyISAM是MySQL中常见的存储引擎，曾经是MySQL的默认存储引擎。MySQL是基于ISAM引擎发展起来的，增加了许多有用的扩展。
• MyISAM的表存储为3个文件。文件的名字与表名相同。扩展名为frm、MYD、MYI。其中，frm文件存储表的结构，MYD文件存储数据，MYI文件存储索引。
• 基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度是不固定的；压缩型需要用到myisampack工具，占用磁盘空间较小。

总结： MyISAM的优势在于占用空间小、处理速度快。缺点是不支持事务的完整性和并发性。

3).MEMORY存储引擎

• MEMORY是MySQL种一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。
• 每个基于MEMORY存储引擎的表实际对应于一个类型为frm的磁盘文件，该文件的文件名与表名相同，只存储表的结构。而其数据文件，存储在内存中，这样使数据快速处理，提高整个表的效率。因此，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用，如果不再使用可以释放内存，甚至删除不需要的表。

总结： MEMORY默认使用哈希索引，速度比使用B型树索引快，如果想用B型树索引，可以在创建索引时指定。

注意： MEMORY很少用到，因为它把数据存储在内存中，如果内存出现异常就会使数据出现异常，当计算机重启或者关机，数据就会消失，因此，基于MEMORY的表的生命周期很短，一般都是一次性的。

我们应该如何选择合适的存储引擎？

特性	InnoDB	MyISAM	MEMORY
事务安全	支持	无	无
存储限制	64TB	有	有
空间使用	高	低	低
内存使用	高	低	高
插入数据的速度	低	高	高
对外见的支持	支持	无	无

InnoDB: 支持事务处理、支持外键、支持崩溃修复能力和并发控制。 如果对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），需要频繁的更新、删除操作（因为支持事务的提交commit和回滚rollback）。

MyISAM:插入数据快，空间和内存使用比较低。如果表用于插入新记录和读出记录。那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但安全性不高。如果要求告诉的读写速度，且对数据的安全性要求较低，可以选择MEMORY。它不能建立太大的表，所以这类数据库只使用在相对较小的数据库表。

注意： 同一个数据库也可以使用多种存储引擎的表。

2.数据库事务的四个特性及含义？

答：
ACID表示事务的特性：原子性、一致性、隔离性和持久性。

• 原子性（Atomic）:事务是一个不可分割的工作单位，事务中包括的操作，要么都做，要么都不做。任何一项操作的失败都会导致整个事务的失败。
• 一致性（Consistent）:事务必须是使数据库从一个一致性状态转换到另一个一致性状态。
• 隔离性（Isolated）:一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（Durable）:持久性也被称为永久性，即一个事务一旦提交，对数据库中数据的改变是永久性的。

3.数据库的三大范式是什么？

答：
通过研究和使用中问题的总结，对设计数据库提出了一些规范，这些规范被称为范式。

• 第一范式（1NF）：数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性存在传递函数依赖关系。即列不可拆分，即无重复的域。
• 第二范式（2NF）：完全依赖于主键，消除非主属性对主码的部分函数依赖。即唯一标识，拥有实体的唯一标识（身份证、学号等）。
• 第三范式（3NF）：每个分关键字列都独立于其他非关键子列，并依赖于关键字，第三范式指数据库中不能。即引用主键，每列数据都与主键直接相关。

注意： 关系型数据库有六种范式。一般情况下，我们不会精确到最高级别范式，只需满足第三范式。

4.数据库支持的SQL数据类型常用的有哪些？

答：
常用的数据库支持的SQL数据类型有：

1. 字符型数据： char\varchar\text, 这几种数据类型都是用来装字符串的。
   char: 固定长度存储数据
   varchar: 按变长存储数据
   text: 当你需要存储数量非常大的字符串时使用
2. 日期时间型数据： datetime、smalldatetime,都是用于存储日期和时间信息。
   datetime: 存放1/1/1753–12/31/9999期间的时间数据，精确到0.001s
   smalldatetime: 存放1/1/1900–6/6/2079期间的时间，精确到s
3. 整数型数据： bigint、int、smallint、tinyint，用于存放整数。
   bigint: 从-2^63 (-9223372036854775808) 到 2 ^63-1(9223372036854775808)的整型数据（所有数字）
   int: 从-2 ^ 31(-2147483648) 到 2 ^31-1(2147483647) 的整型数据（所有数字）
   smallint: 从-2 ^ 15(-32768) 到 2^15-1(32767)的整数数据
   tinyint: 从0到255的整数数据
4. 精确小数型数据： decimal、numeric,用于存放小数。
5. 近似数值类型： float、real,用于存放近似数
6. 位类型数据： bit

5.SQL数据类型varchar和char的区别？

答：

• varchar: 可变长的字符串，需要在声明字段时指定能存储的最大字符数，真实占用的空间取决于存入的字符数，存入的越多占用空间越多。适合保存内容长度不定的字符类型数据。存储数据的大小，MySQL5.0以前0 ~ 255字节，MySQL5.0以后0 ~ 65535字节。
• char: 定长字符串，需要在声明字段时指定固定字符串。即使存入的字符串少于该长度，该字段也会占用该固定长度，适合存储长度不变的字符类型数据。存储数据的大小 0 ~ 255字节。

6.SQL约束有哪几种并解释含义（eg:NOT NULL,UNIQUE等）？

答：

• not null 非空
• unique 唯一
• primary key 非空，唯一，只能有一个主键
• foreign 外键，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值（在必要时可包括复合主键值）。此时，第二个表中保存这些值的属性称为外键（foreign key）。建立外键的前提是目标关系的主键和参照关系的外键必须定义在一个或同一组的域上。
• check 强制数据满足要求，用于限制列中值得范围。

7.数据库内连表、左连表、右连表有什么区别？

答：

• 内连表：table1 inner join table2,表1和表2为相同等级，相匹配得行出现在结果中
• 左连表: table1 left join table2,表1和表2 联结时，以表1为主表
• 右连表：table1 ringht join table2 ,表1和表2联结时，以表2为主表

8.SQL语句查询时如何实现分页？

#表示从第m条数据开始，获取count条数据，默认情况下索引从0开始
select * from table limit m,count;

9.什么是SQL注入？

1）概念

SQL注入（SQLi)是一种注入攻击，可以执行恶意SQL语句。它通过将任意SQL代码插入数据库查询，使攻击能够完全控制web应用程序后面得数据库服务器。攻击者可以使用SQL注入漏洞绕过应用程序安全措施；可以绕过网页或web应用程序得身份验证和授权，并检索整个SQL数据库得内容；还可以使用SQL注入来添加、修改和删除数据库中得记录。
SQL注入漏洞可能会影响使用SQL数据库（如MySQL，Oracle，SQL server 或其他）得任何网站或web应用程序。犯罪分子可能会利用它来未经授权访问用户得敏感数据：客户信息，个人数据，商业机密，知识产权等。SQL注入攻击是最古老，最流行，最危险得web应用程序漏洞之一。

2）SQL注入攻击的类型

1. 带内注入：典型攻击，攻击者可以通过相同的通信通道发起攻击并获得结果。通过两种带内技术完成—1.基于错误的SQL注入：从显示的错误消息中获取有关数据库的信息；2.基于联合的SQL注入：依赖于攻击者能够将被盗信息的结果与合法结果连接起来。
   这两种技术都依赖于攻击者修改应用程序发送的SQL，以及浏览器中显示的错误和返回的信息。如果应用程序开发人员或数据库开发人员无法正确的参数化他们在查询中使用的值，那么它会成功。两者都是试错法，可以检测到错误。
2. 盲注入： 也成为推理SQL注入，盲注入攻击不会直接从目标数据库中显示数据，相反，攻击者会仔细检查行为中的间接线索。HTTP响应中的详细信息，某些用户输入的空白网页以及数据库响应某些用户输入需要多长时间这些都可以是线索，具体取决于攻击者的目标。他们还可以指向攻击者尝试的另一个SQLi攻击途径。
3. 带外注入： 当攻击者无法在单个直接查询-响应攻击中实现其目标时，攻击者可能会使用此攻击。通常，攻击者会制作SQL语句，这些语句在呈现给数据库时会触发数据库系统创建与攻击者控制的外部服务器的连接。以这种方式，攻击者可以收集数据或可能控制数据库的行为。
   二阶注入就是一种带外注入， 在这种情况下，攻击者将提供SQL注入，该注入将由数据库系统的单独行为存储和执行。当二级系统行为发生时（它可能类似于基于时间的作业或由其他典型管理员或用户使用数据库触发的某些事）并且执行攻击者的SQL注入，那就是当”伸出”到系统时攻击者控制发生了。

3）SQL注入的产生条件

1. 有参数传递
2. 参数值带入数据库查询并执行

4）如何防止SQL注入攻击？

1. 使用预编译语句，绑定变量
2. 使用安全的存储过程对抗SQL注入
3. 检查数据类型
4. 使用安全函数

10.数据库怎么优化查询效率（参考解释）？

1. 储存引擎选择：如果数据表需要事务处理，应该考虑使用InnoDB，因为它完全符合ACID特性。如果不需要事务处理，使用MyISAM
2. 对查询进行优化，要尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引
3. 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃索引而进行全表扫描
4. 应尽量避免在where子句中使用!=或><操作符，否则将导致引擎放弃索引而进行全表扫描
5. 应尽量避免在where子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
6. update语句，如果只更改1、2个字段，不要update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志
7. 对于多张大数据量的表join，要先分页再join，否则逻辑读会很高，性能很差

二、SQL语句查询操作

1.创建数据库表employees，包含的信息有：

属性名	属性含义	属性类型
emp_no	员工编号	整型、主键、自增
birth_date	出生年月	日期、非空
name	姓名	字符串、非空
gender	性别	整型、非空、1-男 2-女
hire_date	入职日期	日期、非空

create table employees(
    emp_no int primary key auto_increment comment '员工编号',
    bitrh_date date not null comment '出生年月',
    name varchar(20) not null comment '姓名',
    gender int not null comment '性别，1-男，2-女',
    hire_date date not null comment '入职日期'
);
desc employees；

结果

2.批量插入如下数据：

emp_no	birth_date	name	gender	hire_date
1	1996-10-10	高崎	女	2019-10-10
2	1995-10-10	刘欢	女	2019-6-10
3	1993-10-10	王佗	男	2019-1-10
4	1995-10-10	洪笙宁	男	2018-10-10
5	1991-2-1	张三	男	2017-10-10

import pymysql
#创建连接
conn=pymysql.connect(host='localhost',user='root',password='anning',database='Blog',port=3306,autocommit=True,charset='utf8')
#获取游标
cur=conn.cursor()
#编写sql语句并执行
insert_sql="insert into employees values(1,'1996-10-10','高崎',2,'2019-10-10')," \
           "(2,'1995-10-10','刘欢',2,'2019-6-10')," \
           "(3,'1993-10-10','王佗',1,'2019-1-10')," \
           "(4,'1995-10-10','洪笙宁',1,'2018-10-10')," \
           "(5,'1991-2-1','张三',1,'2017-10-10')"
cur.execute(insert_sql)
#关闭游标
cur.close()
#关闭连接
conn.close()

结果显示

3.更新高崎的出生日期为1996-12-12

import pymysql
#创建连接
conn=pymysql.connect(host='localhost',user='root',password='anning',database='Blog',port=3306,autocommit=True,charset='utf8')
#获取游标
cur=conn.cursor()
#编写sql语句并执行
update_sql="update employees set bitrh_date='1996-12-12' where name='高崎';"
cur.execute(update_sql)
query_sql="select * from employees;"
cur.execute(query_sql)
#关闭游标
cur.close()
#关闭连接
conn.close()

结果显示

4.统计员工总人数

select count(*) as emp_num from employees ;

结果

5.统计入职时间超过2年的员工姓名（扩展知识，查阅资料）

#利用函数curdate()获取当前日期，并保存在@now中
set @now=curdate();
select @now;
#使用datediff函数进行计算
select * from employees where datediff(@now,hire_date)>730;

结果显示：
当前日期

符合条件的员工姓名：

6.查找最晚入职员工的所有信息

select * from employees where hire_date=(select max(hire_date) from employees);

结果显示

7.查询最早入职员工的所有信息

select * from employees where hire_date=(select min(hire_date) from employees);

结果显示

8.查询成绩在85到90分之间的学生姓名、课程名和成绩

select name as '姓名',course_name as '课程名',scores as '成绩' from courses_info where scores between 85 and 90;

结果显示

9.查询高等数学成绩最高的学生名和学生分数

#1. 查看得知最高分的学生为 | 103  | 3-105 |   92.0 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
MariaDB [StuProject]> select * from scores right join students on students.sno=scores.sno;
+------+-------+--------+-----+--------+------+---------------------+-------+
| sno  | cno   | degree | sno | sname  | ssex | sbirthday           | class |
+------+-------+--------+-----+--------+------+---------------------+-------+
| 103  | 3-245 |   86.0 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105  | 3-245 |   75.0 | 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 109  | 3-245 |   68.0 | 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 103  | 3-105 |   92.0 | 103 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105  | 3-105 |   88.0 | 105 | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 109  | 3-105 |   76.0 | 109 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 101  | 3-105 |   64.0 | 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 107  | 3-105 |   91.0 | 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108  | 3-105 |   78.0 | 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 101  | 6-166 |   85.0 | 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 107  | 6-106 |   79.0 | 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108  | 6-166 |   81.0 | 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
+------+-------+--------+-----+--------+------+---------------------+-------+
12 rows in set (0.003 sec)
 
# 2. 获取
MariaDB [StuProject]> select sname as '姓名',cname as '课程', degree as '成绩' from scores
    -> right join students on students.sno=scores.sno
    -> right join courses on scores.cno=courses.cno
    -> where degree=(select max(degree) from scores);
+--------+-----------------+--------+
| 姓名   | 课程            | 成绩   |
+--------+-----------------+--------+
| 陆君   | 计算机导论      |   92.0 |
+--------+-----------------+--------+
1 row in set (0.001 sec)

10.查询李军选修的课程名称

MariaDB [StuProject]> select sname as '姓名',cname as '课程' from scores 
    -> right join students on students.sno=scores.sno
    -> right join courses on scores.cno=courses.cno
    -> where sname='李军';
+--------+-----------------+
| 姓名   | 课程            |
+--------+-----------------+
| 李军   | 计算机导论      |
| 李军   | 数据电路        |
+--------+-----------------+
2 rows in set (0.001 sec)
``