SQL面试题思维导图+常考题型SQL面试题结构拆解思维导图 # SQL初级查询面试题 # SQL中级查询面试题 join

SQL面试题结构拆解思维导图

# SQL初级查询面试题

1 员工姓名是中文，如何按照姓名拼音排序

select emp_name
from employee
order by convert(emp_name using GBK)

-- 中文可以按照拼音或者偏旁部首排序，MYSQL中的GBK编码支持拼音排序

2 有的员工奖金没有奖金，BOUNS字段为空，此时对BOUNS字段排序，没有奖金的员工排在前面还是后面


-- MYSQL，排序时NULL比0还小，升序时NULL排在最前面

3 薪水最高的三位员工

select emp_name,salary

from employee

order by salary desc

limit 3

-- mysql中是limit，其他数据库语言可能是别的，比如FETCH

4 如果两个人都是第三名，怎么都返回，也就是返回四条数据

在 MySQL 8.0 中不直接支持标准 SQL 中的 FETCH WITH TIES 语句。

不过可以使用窗口函数等方式来模拟类似的功能，就像前面提到的使用 DENSE_RANK() 窗口函数来实现获取相同排名的数据。

5 返回第11名到第15名，也就是实现分页效果

select emp_name,salary

from employee

order by salary desc

limit 5 offset 10

-- offset表示偏移量，表示跳过多少行，这里的意义是行数而不是索引或者第几行数

6 字符串函数：如何得知员工邮箱长度

select emp_name,length(email)

from employee

-- length函数用于返回字符长度，MySQL是按照字节数量计算

-- char_length函数返回的是字符数量计算

-- 在MySQL中，字符的编码不同，字节的数量也不同

-- GBK编码中，一个汉字 = 2个字节 一个英文 = 一个字节

-- UTF-8编码中，一个汉字 = 3个字节 一个英文 = 一个字节

-- 对于这道题来说，邮箱一般都是英文，所以都是用length或者char_length都可以

7 如何屈认准谁的邮箱是“DJAKS@DJA.COM”

select emp_name

from employee

where upper(email) = '<DJAKS@DJA.COM>'

-- upper函数全转为大写

-- lower函数全传为小写

8 以CSV格式(逗号分隔符)显示员工的姓名、性别、薪水等

select concat_ws(','emp_name,sex,salary)

from employee

9 如何获取员工邮箱中的名字部分（即@之前的字符串）

select emp_name,substr(email,1,instr(email,'@') -1)

from employee

-- instr函数查找‘@’的位置，substr函数获取该位置之前的子串（将email字段从第一位取到@之前的一位）

10 将员工邮箱中的.com替换成.net

-- 这是我自己想得

select emp_name,concat(left(email,1,instr(email,'@')-1),'.net')

-- 下面是replace函数直接实现

select emp_name,replace(email,'.com','.net')

  


-- trim函数虽然一般用于去除空格，但也能用于去除字符串中多余的字符

select emp_name,trim(both '.com',email)

-- trim函数仅去除字符串两侧的字符，不会去除字符串内部的空格或指定字符

11 ceiling、floor和round有什么区别

ceiling 向上取整 1.1 ——> 2

floor 向下取整 1.1——> 1

round 四舍五入 1.1 1

12 下面是学生成绩表，如何知道每个学生的最高分（每个学生的横向对比）

select student_id,greatest(chainese,math,english,history)

13 如何知道每个员工的工作年限？

select emp_name,extract(year from current_date)- extract(year from hire_date)

from employee

-- current_date函数返回当前日期，extract可以提取日期中的各个部分

14 工资信息比较敏感，不宜直接显示。按照范围显示收入水平，小于 10000 显示为“低收入”，大于等于 10000 并且小于 20000 显示为“中等收入”，大于 20000 显示为“高收入”

select emp_name,

case when salary < 10000 then '低收入'

when salary < 20000 then ‘中收入’

when salary >= 20000 then '高收入'

end

from employee

15 统计员工的数量、平均月薪、最高月薪、最低月薪、月薪总和

select count(*),avg(salary),max(salary),min(salary),sum(salary)

16 count(*),count(1)和count(字段)的区别

- count(1)，其实就是计算一共有多少符合条件的行。1并不是表示第一个字段，而是表示一个固定值。我们可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.。同理，count(2)、count(3)，得到的值完全一样，count('x')，count('y')都是可以的。一样的理解方式。在你这个语句理都可以使用，返回的值完全是一样的。就是计数。

 
- count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。
若字段为主键则count(主键)效率最高，否则少量数据时使用count(1)

 
-- count(1)和count(*)对null计数，count(字段)不计数

-- 效率上：

-- 小数据量 count(1)>count(字段)>count(*)

-- 大数据量 count(字段)>count(*)>count(1)

-- 若是count(字段)字段为主键，那么此时效率最快

17 群发邮件时，多个邮件地址使用分号进行分隔。如何获取所有员工的群发邮件地址

select group_concat(email separator ';')

from employee

-- 使用字符串的聚合函数将多个字符串合并成一个

18 如何获取每个部门的统计信息，比如员工数量，平均月薪

select dept_id,count(*),avg(salary)

from employee

order by dept_id

19 执行顺序：以下语句能否正常运行？

SELECT dept_id, COUNT(*), emp_name

FROM employee

GROUP BY dept_id;

-- 不能，select关键词之后只能出现group by之后的字段名

20 如果只想查看平均月薪大于10000的部门怎么实现

select dept_id,avg(salary)

from employee

group by dept_id

having avg(salary) > 10000

21 如果想要知道哪些部门月薪超过 5000 的员工数量大于 5

select dept_id,count(*)

from employee

where salary > 5000

group by dept_id

having count(*) > 5

22 MYSQL执行顺序

from

on

join

where

group by

agg_func

having

select

distinct

order by

limit/offset

23 NOW() 和 CURRENT_DATE()有什么区别？

NOW（）结果为年 月 日 小时 分钟 秒

CURRENT_DATE() 结果为 年 月 日

# SQL中级查询面试题

1 什么是连接查询？SQL中有哪些连接查询？

join是基于两个表中的关联字段将数据行拼接到一起，可以同时返回两个表中的数据

MySQL支持：

内连接 inner join / join：用于返回两个表中满足连接条件的数据行

左外连接 left join：返回左表中所有的数据行；对于右表中的数据，如果没有匹配的值，返回空值

右外连接 right join：

全外连接 full join 不支持

笛卡尔积 cross join：两个表的笛卡尔积相当于一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘

自连接 self join

2 如何通过内连接返回员工所在的部门名称

SELECT d.dept_id,

d.dept_name,

e.emp_name

FROM employee e

JOIN department d ON (e.dept_id = d.dept_id);

  


SELECT d.dept_id,

d.dept_name,

e.emp_name

FROM employee e, department d

WHERE e.dept_id = d.dept_id;

3 统计每个部门的员工数量，同时显示部门名称信息

select dept_name，count(dept_name)

from department as d

left join employee as e

on d.dept_id = e.dept_id

group by dept_name

-- 因为可能有的部门还没有员工，所以不能用内连接

4 如何知道每个员工的经理名字

select e.emp_name as ‘员工姓名’，emp_name as ”经理姓名“

from employee as e

left join employee as m

on e.emp_id = m,emp_id

-- 使用左连接是因为，有一个员工没有上级，他就是公司最高领导

5 SQL支持哪些集合运算

并集(UNION/UNION ALL) 纵向合并

交集(INTERSECT) 返回查询结果中的共同部分 MySQL不支持

差集(EXCEPT) 返回出现在第一个查询结果中，但不在第二个查询结果中的数据

6 假设存在以下两个表，下列查询的结果分别是什么？

CREATE TABLE t1(id int);

INSERT INTO t1 VALUES (1);

INSERT INTO t1 VALUES (2);

  


CREATE TABLE t2(id int);

INSERT INTO t1 VALUES (1);

INSERT INTO t1 VALUES (3);

  


-- Oracle 实现

SELECT id FROM t1 UNION SELECT id FROM t2;

SELECT id FROM t1 UNION ALL SELECT id FROM t2;

SELECT id FROM t1 INTERSECT SELECT id FROM t2;

SELECT id FROM t1 EXCEPT SELECT id FROM t2;

  


-- 1 2 3 1 1 2 3 1 2

7 对于 MySQL 而言，如何实现上题中的交集运算和差集运算效果

-- 使用连接查询实现交集运算

SELECT t1.id FROM t1 JOIN t2 ON (t1.id = t2.id);

  


-- 使用左连接查询实现差集运算

SELECT t1.id FROM t1

LEFT JOIN t2 ON (t1.id = t2.id)

WHERE t2.id IS NULL;

8 什么是子查询？子查询有哪些类型？

子查询（subquery）是指嵌套在其他语句（SELECT、INSERT、UPDATE、DELETE、MERGE）中的 SELECT 语句。子查询中也可以嵌套另外一个子查询，即多层子查询

  


子查询根据返回的结果分为以下类型：

标量子查询：返回单个值（一行一列，就是一个数据格）

行子查询：返回包含一个或多个值的单行结果（一行多列，一行数据），标量子查询是行子查询的特例

表子查询：返回一个虚拟的表（多行多列），行子查询是表子查询的特例

  


基于子查询和外部查询的关系，也可以分为以下两类：关联子查询（correlated subqueries）和非关联子查询（non-correlated subqueries）。关联子查询会引用外部查询中的列，因而与外部查询产生关联；非关联子查询与外部查询没有关联

9 如何找出月薪大于平均月薪的员工？

select emp_name,salary

from employee

where salary > (select avg(salary) from employee)

-- 这是非关联的标量子查询

10 以下查询的返回结果是什么？

select *

from employee

where dept_id = (select dept_id from department)

  


-- 执行出错

-- 因为非关联的表子查询返回的是表，一个字段 ！= 一个表

-- 不能用 = ，需要用in

11 哪些员工的月薪高于本部门的平均值？

select emp_name,salary

from employee e

where salary >

(select avg(salary) from employee where dept_id = e.dept_id)

12 显示员工信息时，增加一列，用于显示该员工所在部门的人数

select emp_name,(select count(*) from employee where dept_id = e.dept_id) as dept_count

from employee as e

13 以上问题能否使用下面的查询实现？

SELECT emp_name,

dept_count

FROM employee e

JOIN (SELECT COUNT(*) AS dept_count

FROM employee

WHERE dept_id = e.dept_id) d

ON (1=1)

-- 执行出错

-- from子句中不能直接使用关联子查询，因为子查询和查询处于相同的层级，不能引用前表 e 中的数据

14 找出哪些部门中有女性员工？

select *

from department d

where exists (select 1 from employee e where e.sex = '女' and e.dept_id = d.dept_id)

-- EXISTS 运算符用于检查子查询中结果的存在性。针对外部查询中的每条记录，如果子查询存在结果（部门中存在女性员工），外部查询即返回结果。NOT EXISTS 执行相反的操作

15 按照部门和职位统计员工的数量，同时统计部门所有职位的员工数据，再加上整个公司的员工数量,如何用一个查询实现效果？

SELECT dept_id, job_id, COUNT(*)

FROM employee

GROUP BY dept_id, job_id WITH ROLLUP

-- GROUP BY 支持扩展的 ROLLUP 选项，可以生成按照层级进行汇总的结果，类似于财务报表中的小计、合计和总计

16 使用扩展分组时，会产生一些 NULL 值，如何确认这些 NULL 值代表的意义？

SELECT CASE GROUPING(dept_id) WHEN 1 THEN '所有部门' ELSE dept_id END,

CASE GROUPING(job_id) WHEN 1 THEN '所有职位' ELSE job_id END,

COUNT(*)

FROM employee

GROUP BY dept_id, job_id WITH ROLLUP

-- 使用grouping函数，会在结果表中将null显示为具体意义

17 如何使用SQL查询生成一列连续的数字序列

WITH RECURSIVE cte (n) AS

(

SELECT 1 FROM dual

UNION ALL

SELECT n + 1 FROM cte WHERE n < 10

)

SELECT * FROM cte

-- 通用表表达式（with子句）是一个子在语句级别的临时结果集

-- 定义之后，相当于有了一个表变量，可以在语句中多次引用该通用表表达式

-- 递归 RECURSIVE 形式的通用表表达式可以用于生成序列，遍历层次数据或树状结构的数据

18 如何获取员工在公司组织结构中的结构图，也就是从最高领导到员工的管理路径？

WITH RECURSIVE employee_paths (emp_id, emp_name, path) AS

(

SELECT emp_id, emp_name, CAST(emp_name AS CHAR(200))

FROM employee

WHERE manager IS NULL

UNION ALL

SELECT e.emp_id, e.emp_name, CONCAT(ep.path, '->', e.emp_name)

FROM employee_paths ep

JOIN employee e

ON ep.emp_id = e.manager

)

SELECT * FROM employee_paths ORDER BY path

| emp_id | emp_name | path                   |
| ------ | -------- | ---------------------- |
| 1      | 刘备       | 刘备                     |
| 2      | 关羽       | 刘备->关羽                 |
| 18     | 法正       | 刘备->关羽->法正             |
| 25     | 孙乾       | 刘备->关羽->法正->孙乾         |
| 19     | 庞统       | 刘备->关羽->法正->孙乾->庞统     |
| 24     | 简雍       | 刘备->关羽->法正->孙乾->庞统->简雍 |

19 什么是窗口函数？有哪些常见的窗口函数？

窗口函数（Window function）也称为分析函数，与聚合函数类似，也是基于一组数据进行分析；但是，窗口函数针对每一行数据都会返回一个结果。窗口函数为 SQL 提供了强大的数据分析功能

  


-- 常见的窗口函数

-- 聚合函数窗口函数：count avg sum min max

-- 排序函数窗口函数：row_number dense_rank rank

-- 偏移函数窗口函数：lead lag first_value last_value

20 查询员工的月薪，同时返回该员工所在部门的平均月薪。如何使用聚合函数实现？

SELECT emp_name, salary, AVG(salary) OVER (PARTITION BY dept_id)

FROM employee

21 查询员工的月薪，同时计算其月薪在部门内的排名？

SELECT emp_name, dept_id, salary,

ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC),

RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC),

DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)

FROM employee

-- 12345

-- 12245

-- 12234

22 查询员工的入职日期，同时计算其部门内在该员工之前一个和之后一个入职的员工？

SELECT emp_name, dept_id, hire_date,

LAG(emp_name, 1) OVER (PARTITION BY dept_id ORDER BY hire_date),

LEAD(emp_name, 1) OVER (PARTITION BY dept_id ORDER BY hire_date)

FROM employee

-- lag 窗口下移

-- lead 窗口上移

23 查询员工的月薪，同时计算其部门内到该员工为止的累计总月薪？

SELECT emp_name, dept_id, salary,

SUM(salary) OVER (PARTITION BY dept_id ORDER BY NULL ROWS UNBOUNDED PRECEDING)

FROM employee

-- 窗口函数支持定义窗口范围，UNBOUNDED PRECEDING 表示从分组内的第一行到当前行，可以用于计算累计值

-- first_value：查看分区排序后的第一个值

-- last_value：查看分区排序后的最后一个值

--

> sum() over() 的一些疑惑
>
> 1.  sum(xx) over(order by xx)as sum1,此时的sum1的结果是累加，比如xx字段下的数据为 30 40 50 60 那么sum1的四行分别为 30 70 120 180
> 1.  sum(xx) over(partition by yy order by xx) as sum2，那么会分区累加，比如yy字段中相同数据的分在了一个区，加入yy为 aa aa aa bb 对应的xx字段数据依旧为 30 40 50 60 ，那么sum2的结果为 30 70 120 60
> 1.  sum(xx) over(partition by yy) as sum3,那么sum3为 180 180 180 60,没有累加过程，但是会分组，相同的key进行回填处理
> 1.  如果在这个累加窗口函数外面套一层select，但后对其进行distinct ，那么只会剩下，aa 180 bb 60
> 1.  总结：
> 1.  1.  只有order by就累加
>     1.  只有partition by就回填累加后的最大值，并且分区
>     1.  两个都有，就是又分区又累加

24 查询员工的月薪，同时计算其部门内按照月薪排序后，前一个员工、当前员工以及后一个员工的平均月薪？

SELECT emp_name, dept_id, salary,

AVG(salary) OVER (PARTITION BY dept_id ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

FROM employee

> over（partition by XX order by XX rows between unbounded preceding（代表分区第一行） and current row（当前行））数据窗口从分区第一行到当前行
>
> rows between

# 设计与开发（底层原理）面试题

1 什么是数据库（DATABASE）？什么是数据库管理系统（DBMS）

数据库（DATABASE）是各种数据的集合，按照一定的数据结构进行存储和管理

数据库管理系统(DATABASE Management System)是用于管理数据库的软件，负责数据库的创建、查询、修改等操作。

这两者共同构成了数据库系统（DataSystem）

应用程序或者终端用户通过DBMS访问和管理数据库

2 什么是关系数据库

关系数据路是基于关系模型的数据库，在关系模型中，用于存储数据的逻辑结构就是二维表（Table）。

表由行和列组成，行被称为记录，代表了单个实体；列被称为字段，代表了实体的某些属性。

关系数据库使用SQL作为标准语言，执行数据的增删查改以及各种管理操作，关系数据库还定义了三种约束完整性：实体完整性、参照完整性以及用户定义完整性。

大多数主流数据库都属于关系数据库，例如：Oracle、MySQL、SQLSever以及PostgreSQL等。另外，数据库领域还存在着一些非关系型数据库（NoSQL），例如：MongoDB、Redis、Cassandra等 。

3 关系数据库有哪些完整性约束？

-   非空约束（NOT NULL）：用于限制字段不会出现空值，比如员工姓名不能为空
-   唯一约束（UNIQUE）：用于确保字段中的值不会重复，例如，每个员工的电子邮箱不能重复。每个表可以有多个唯一约束。
-   主键约束（Primary Key），主键是唯一标识表中每一行的字段。例如员工编号，部门编号等。主键字段必须唯一且非空，每个表可以有且只能有一个主键。
-   外键约束（FOREIGN KEY），用于表示两个表之间的引用关系。例如，员工属于部门，因此员工表中的部门编号字段可以定义为外键，它引用了部门信息表中的主键。
-   检查约束（CHECK），可以定义更多用户自定义的业务规则。例如，薪水必须大于 0 ，性别只能是男和女等。
-   默认值（DEFAULT），用于向字段中插入默认数据。

4 OLTP和OLAP有什么区别？

| OLTP                      | OLAP                       |
| ------------------------- | -------------------------- |
| 在线事务处理系统                  | 在线分析处理系统                   |
| 专注事务增删改，事务相对简单但繁琐，要求响应时间快 | 专注于决策数据分析，查询通常比较复杂，处理时间长   |
| 数据来源于在线业务                 | 数据来源于各种 OLTP               |
| 通常采用规范化的设计，需要保证数据的完整性     | 不需要太多规范化，可以存储冗余信息，采用多维数据模型 |
| 常见应用包括银行 ATM、在线订票系统、网上商城  | 常见应用包括数据仓库、报表分析、商务智能       |

5 什么是数据库规范化，有哪些常见的数据库范式？

数据库规范化是一种数据库设计的方法，用于有效地组织数据，减少数据的冗余和相互之间的依赖，增加数据的一致性。由于非规范化的数据库存在冗余，可能导致数据的插入、删除、修改异常等问题，因此引入了规范化过程。

数据库规范化的程度被称为范式（Normal Form），目前已经存在第一范式到第六范式，每个范式都是基于前面范式的增强。

-   第一范式（First Normal Form），表中的每个属性都是单值属性，每个记录都唯一，也就是需要主键。举例来说，如果员工存在工作邮箱和个人邮箱，不能都放到一个字段，而需要拆分成两个字段；
-   第二范式（Second Normal Form），首先需要满足第一范式，且不包含任何部分依赖关系。举例来说，如果将学生信息和选课信息放在一起，学号和课程编号可以作为复合主键；但此时学生的其他信息依赖于学号，即主键的一部分。通常使用单列主键可以解决部分依赖问题；
-   第三范式（Third Normal Form），首先需要满足第二范式，并且不存在传递依赖关系。举例来说，如果将部门信息存储在每个员工记录的后面，那么部门名称依赖部门编号，部门编号又依赖员工编号，这就是传递依赖。解决的方法就是将部门信息单独存储到一个表中

另外，反规范化（Denormalization）是在完成规范化之后执行的相反过程。反规范化通过增加冗余信息，减少 SQL 连接查询的次数，从而减少磁盘 IO 来提高查询时的性能。但是反规范化会导致数据的重复，需要更多的磁盘空间，并且增加了数据维护的复杂性。

数据库的设计是一个复杂的权衡过程，需要综合考虑各方面的因素。

6 什么是实体关系图（ERD）？

实体关系图是一种用于数据库设计的结构图，它描述了数据库中的实体，以及这些实体之间的相互关系。实体代表了一种对象或者概念。例如，员工、部门和职位可以称为实体。每个实体都有一些属性，例如员工拥有姓名、性别、工资等属性。

关系用于表示两个实体之间的关联。例如，员工属于部门。三种主要的关系是一对一、一对多和多对多关系。例如，一个员工只能属于一个部门，一个部门可以有多个员工，部门和员工是一对多的关系。

7 数据库常见对象有哪些？

表（Table）、视图（View）、序列（Sequence）、索引（Index）、存储过程（Stored Procedure）、触发器（Trigger）、用户（User）以及同义词（Synonym）等等。其中，表是关系数据库中存储数据的主要形式。

8 常见 SQL 数据类型有哪些？

最常见的类型包括字符类型、数字类型、日期时间类型和二进制数据类型。

9 CHAR 和 VARCHAR 类型的区别？

CAHR 是固定长度的字符串，如果输入的内容不够使用空格进行填充，通常用于存储固定长度的编码；

VARCHAR 是可变长度的字符串，通常用于存储姓名等长度不一致的数据。

10 如何创建一个表？

CREATE TABLE employee

( emp_id INTEGER NOT NULL PRIMARY KEY

, emp_name VARCHAR(50) NOT NULL

, sex VARCHAR(10) NOT NULL

, dept_id INTEGER NOT NULL

, manager INTEGER

, hire_date DATE NOT NULL

, job_id INTEGER NOT NULL

, salary NUMERIC(8,2) NOT NULL

, bonus NUMERIC(8,2)

, email VARCHAR(100) NOT NULL

, CONSTRAINT ck_emp_sex CHECK (sex IN ('男', '女'))

, CONSTRAINT ck_emp_salary CHECK (salary > 0)

, CONSTRAINT uk_emp_email UNIQUE (email)

, CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)

, CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id)

, CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)

) ;

11 如何基于已有的表复制一个表？

CREATE TABLE emp_new

AS

SELECT *

FROM employee

WHERE 1=0

-- 在where后面加一个永不为真的条件可以复制一个只有结构的空表，不加where就会复制一个表

  


-- 复制一个空表还可以：

CREATE TABLE emp_copy

LIKE employee

12 如何创建自增列？

自增列（auto increment），也称为标识列（identity column），用于生成一个自动增长的数字。它的主要用途就是为主键提供唯一值。Oracle 使用标准 SQL 中的 GENERATED ALWAYS AS IDENTITY 表示自增列，MySQL 使用关键字 AUTO_INCREMENT 表示自增列。

CREATE TABLE emp_identity(

emp_id INT AUTO_INCREMENT,

emp_name VARCHAR(50) NOT NULL,

PRIMARY KEY (emp_id)

);

  


INSERT INTO emp_identity(emp_name) VALUES ('张三');

INSERT INTO emp_identity(emp_name) VALUES ('李四');

INSERT INTO emp_identity(emp_name) VALUES ('王五');

  


SELECT * FROM emp_identity;

emp_id |emp_name

------------------

1 |张三

2 |李四

3 |王五

13 如何修改表的结构？

-- 常见的操作包括增加列，修改列，删除列；增加约束，修改约束，删除约束等

ALTER TABLE emp_new

ADD weight NUMERIC(4,2) DEFAULT 60 NOT NULL

注意添加多个列的写法是 alter table XX add XX 类型，add XX 类型，add XX 类型（每个都要写关键字）

14 如何删除一个表？

drop table table_name

15 SQL中的drop、delete、truncate都表示删除，但是三者有一些差别?

相同点：drop、delete、truncate 都是删除表的内容
不同点：
- delete是dml：删除表的内容，表的结构还存在，不释放空间，可以回滚恢复；
- - 这个操作会放到rollback segement中，事务提交之后才生效，如果有相应的trigger,执行的时候将被触发，在之后需要时可以回滚数据
  - 还可以加一些其它的where条件，比如删除确定的记录
- drop是ddl：删除表内容和结构，释放空间，没有备份表之前要慎用；
- truncate是ddl：删除表的内容，表的结构存在，可以释放空间,没有备份表之前要慎用；
速度：一般来说drop> truncate > delete

16 什么是数据库事务？

在数据库中，事务（Transaction）是指一个或一组相关的操作（SQL 语句），它们在业务逻辑上是一个原子单元。一个最常见的数据库事务就是银行账户之间的转账操作。比如从 A 账户转出 1000 元到 B 账户，其中就包含了多个操作：

查询 A 账户的余额是否足够；
从 A 账户减去 1000 元；
往 B 账户增加 1000 元；
记录本次转账流水。

数据库必须保证所有的操作要么全部成功，要么全部失败。如果从 A 账户减去 1000 元成功执行，但是没有往 B 账户增加 1000 元，意味着客户将会损失 1000 元。用数据库中的术语来说，这种情况导致了数据库的不一致性。

数据库事务拥有以下 4 个特性：原子性、一致性、隔离性以及持久性（ACID）。

Atomic，原子性。一个事务包含的所有 SQL 语句要么全部成功，要么全部失败。例如，某个事务需要更新 100 条记录，但是在更新到一半时，系统出现故障，数据库必须保证能够回滚已经修改过的数据，就像没有执行过该事务一样。
Consistency，一致性。事务开始之前，数据库位于一致性的状态；事务完成之后，数据库仍然位于一致性的状态。例如，银行转账事务中，如果一个账户扣款成功，但是另一个账户加钱失败，那么就会出现数据不一致（此时需要回滚已经执行的扣款操作）。另外，数据库还必须保证满足完整性约束，比如账户扣款之后不能出现余额为负数（可以在余额字段上添加检查约束）。
Isolation，隔离性。隔离性与并发事务有关，一个事务的影响在其完成之前对其他事务不可见，多个并发的事务之间相互隔离。例如，账户 A 向账户 B 转账的过程中，账户 B 查询的余额应该是转账之前的数目；如果多人同时向账户 B 转账，结果也应该保持一致性，就像依次转账的结果一样。
Durability，持久性。已经提交的事务必须永久生效，即使发生断电、系统崩溃等故障，数据库都不会丢失数据。

17 数据库事务支持哪些隔离级别？

18 如何插入数据？

方式一：

INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部');

  


-- 一次插入多行是MySQL特有

INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部'), (2, '人力资源部'), (3, '财务部');

方式二：

INSERT INTO emp_new

SELECT * FROM employee

19 如何修改数据？

UPDATE table_name

SET column1 = expr1,

column2 = expr2,

...

[WHERE condition]

UPDATE emp_new

SET salary = salary + 1000

WHERE emp_name = '赵云'

20 如何删除数据？

SQL 中用于删除数据的命令主要是 DELETE 语句

  


-- 清空所有数据

DELETE FROM emp_new

  


-- 根据条件清空

DELETE FROM table_name

[WHERE conditions]

21 MySQL中的UPSERT操作？

MERGE 是 SQL:2003 标准中引入的一个新的数据操作命令，它可以同时完成 INSERT 和 UPDATE 的操作，甚至 DELETE

UPSERT与其作用相同

INSERT INTO target_table (column1, column2, ...)

SELECT col1, col2, ...

FROM source_table s

ON DUPLICATE KEY UPDATE

column1 = s.col1,

column2 = s.col2,

...;

22 什么是索引？有哪些类型的索引？

索引（Index）是一种数据结构，主要用于提高查询的性能。

索引类似于书籍最后的索引，它指向了数据的实际存储位置；索引需要占用额外的存储空间，在进行数据的操作时需要额外的维护。

另外，索引也用于实现约束，例如唯一索引用于实现唯一约束和主键约束。

不同数据库的索引类型不同，以下为通用索引类型：

B/B+树索引：使用平衡树或者扩展的平衡树结构创建索引。这是最常见的一种索引，几乎所有的数据库都支持。这种索引通常用于优化 =、<、<=、>、BETWEEN、IN 以及字符串的前向匹配查询。
Hash索引：使用数据的哈希值进行索引。主要用于等值（=）查询。
聚集索引：将表中的数据按照索引的结构（通常是主键）进行存储。MySQL 中称为聚集索引，Oracle 中称为索引组织表（IOT）。
非聚集索引：也称为辅助索引。索引与数据相互独立，MySQL 中的 InnoDB 存储的是主键值，Oracle 中存储的时物理地址。
全文索引：用于支持全文搜索。
唯一索引与非唯一索引：唯一索引可以确保被索引的数据不会重复，可以实现数据的唯一性约束。非唯一索引仅仅用于提高查询的性能。
单列索引与多列索引：基于多个字段创建的索引称为多列索引，也叫复合索引。
函数索引：基于函数或者表达式的值创建的索引。

索引是优化 SQL 查询的一个有效方法，但是索引本身也需要付出一定的代价，过渡的索引可能给系统带来负面的影响。

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

查询计划是数据库执行 SQL 的具体方式。包括读取表的方式，使用全表扫描还是使用索引；表的连接方式；预计占用的 CPU、IO 等资源。查看查询计划是进行 SQL 性能诊断和优化的基础。所有主流的数据库都提供了类似的查看执行计划的方式：EXPLAIN 命令。

EXPLAIN

SELECT *

FROM employee e

WHERE emp_id = 5;

--|-----------|-----|----------|-----|-------------|-------|-------|-----|----|--------|-----|

1|SIMPLE |e | |const|PRIMARY |PRIMARY|4 |const| 1| 100| |

-- 由于 emp_id 是主键，执行计划显示通过主键索引（PRIMARY）进行查询。

24 以下查询语句会不会使用索引？

CREATE INDEX idx ON test (col);

SELECT COUNT(*)

FROM test

WHERE col * 12 = 2400

不会，针对索引字段进行运算或者使用函数之后，会导致无法使用索引，可以将运算改到操作符的右边

CREATE INDEX idx ON test (col);

SELECT COUNT(*)

FROM test

WHERE col = 2400 / 12

25 针对以下查询，如何创建索引？

SELECT *

FROM test

WHERE col1 = 100

AND col2 = 'SQL'

SELECT *

FROM test

WHERE col2 = 'NoSQL

创建一个复合索引，并且将col2放在前面

CREATE INDEX idx ON test (col2, col1)

创建复合索引时需要注意字段的顺序，当查询条件使用索引左侧的字段时，可以有效的利用索引。

26 员工表的 email 字段上存在唯一索引，以下查询会不会使用该索引？

SELECT *

FROM employee e

WHERE email LIKE 'zhang%'

会，对于LIKE运算符，如果通配符不在最左侧，可以使用索引，但是如果 '%zhang%'或者 '%zhang'无法使用索引

27 多表连接查询有哪三种执行方式？

数据库在实际执行连接查询时，可以采用以下三种物理方式：

嵌套循环连接（Nested Loop Join），针对驱动表中的每条记录，遍历另一个表找到匹配的数据，相当于两层循环。Nested Loop Join 适用于驱动表数据比较少，并且连接的表中有索引的时候。
排序合并连接（ Sort Merge Join），先将两个表中的数据基于连接字段进行排序，然后合并。Sort Merge Join 通常用于没有索引，并且数据已经排序的情况，比较少见。
哈希连接（Hash Join），将一个表的连接字段计算出一个哈希表，然后从另一个表中一次获取记录并计算哈希值，根据两个哈希值来匹配符合条件的记录。Hash Join 对于数据量大，且没有索引的情况下可能性能更好。

MySQL 目前只支持 Nested Loop Join，不建议使用多个表的连接查询，因为多层循环嵌套会导致查询性能的急剧下降。

28 什么是视图？

视图（View）是一个存储在数据库中的 SELECT 语句。视图也被称为虚表，在许多情况下可以当作表来使用。视图与表最大的区别在于它自身不包含数据，数据库中存储的只是视图的定义语句。

视图具有以下优点：

替代复杂查询，减少复杂性；
提供一致性接口，实现业务规则；
控制对于表的访问，提高安全性。

但是，使用视图也需要注意以下问题：

不当使用可能会导致查询的性能问题；
可更新视图（Updatable View）需要满足许多限制条件。

29 创建一个视图，包含员工所在部门，所属职位，姓名、性别以及邮箱信息？

create or replace view emp_info as

SELECT d.dept_name,j.job_title, e.emp_name, e.sex, e.email

FROM employee e

JOIN department d ON (d.dept_id = e.dept_id)

JOIN job j ON (j.job_id = e.job_id);

  


SELECT *

FROM emp_info

WHERE emp_name = '法正

30 什么是可更新视图？

可更新视图是指可以通过对视图的 INSERT、UPDATE、DELETE 等操作，实现对视图对应的基础表的数据修改。通常来说，可更新视图必须是简单的查询语句，不能包含以下内容：

聚合函数，例如 SUM、AVG 以及 COUNT 等；
DISTINCT 关键字；
GROUP BY 或者 HAVING 子句；
集合操作符 UNION 等；
不同的数据库特定的限制。

简单来说，可能导致无法通过视图找到对应基础表中的数据的操作都不允许。以下语句创建了一个简单的视图，只包含了开发部门的员工信息，并且隐藏了工资等敏感信息：

CREATE OR REPLACE VIEW emp_devp

SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email

FROM employee

WHERE dept_id = 4

WITH CHECK OPTION

其中的 WITH CHECK OPTION 确保无法通过视图修改超出其可见范围之外的数据。以下是通过该视图修改员工信息的操作：

UPDATE emp_devp

SET email = 'zhaoyun@sanguo.net'

WHERE emp_name = '赵云'

如如果尝试更新非开发部门员工，不会更新到任何数据

UPDATE emp_devp

SET email = 'zhangfei@sanguo.net'

WHERE emp_name = '张飞'

-- 不行

31 什么是存储过程？

存储过程（Stored Procedure）是存储在数据库中的程序，它是数据库对 SQL 语句的扩展，提供了许多过程语言的功能，例如变量定义、条件控制语句、循环语句、游标以及异常处理等等。一旦创建之后，应用程序（Java、C++ 等）可以通过名称调用存储过程。

存储过程的优点包括：

提高应用的执行效率。存储过程经过编译之后存储在数据库中，执行时可以进行缓存，可以提高执行的速度；
减少了应用与数据库之间的数据传递。调用存储过程时，只需要传递参数，业务代码已经存在数据中；
存储过程可以实现代码的重用。不同的应用可以共享相同的存储过程；
存储过程可以提高安全性。存储过程实现了代码的封装，应用程序通过存储过程进行数据访问，而不需要之间操作数据表。

另一方面，存储过程也存在一些缺点：

不同数据库的实现不同，Oracle 中称为 PL/SQL，MySQL 中称为 PSM，其他数据库也都有各自的实现；
存储过程需要占用数据库服务器的资源，包括 CPU、内存等，而数据库的扩展性不如应用；
存储过程的开发和维护需要专业的技能。

是否使用存储过程需要考虑具体的应用场景。对于业务变化快的互联网应用，通常倾向于将业务逻辑放在应用层，便于扩展；而对于传统行业的应用，或者复杂的报表分析，合理使用存储过程可以提高效率。

32 如何创建存储过程？

使用 CREATE PROCEDURE 语句创建存储过程，不同的数据库存在一些实现上的差异。以下语句创建了一个为员工表增加员工的存储过程：

-- MySQL 实现

DELIMITER $$

CREATE PROCEDURE insert_employee(IN pi_emp_id INT,

IN pi_emp_name VARCHAR(50),

IN pi_sex VARCHAR(10),

IN pi_dept_id INT,

IN pi_manager INT,

IN pi_hire_date DATE,

IN pi_job_id INT,

IN pi_salary NUMERIC,

IN pi_bonus NUMERIC,

IN pi_email VARCHAR(100))

BEGIN

DECLARE EXIT HANDLER FOR 1062

SELECT CONCAT('Duplicate employee: ', pi_emp_id);

INSERT INTO employee(emp_id, emp_name, sex, dept_id, manager,

hire_date, job_id, salary, bonus, email)

VALUES(pi_emp_id, pi_emp_name, pi_sex, pi_dept_id, pi_manager,

pi_hire_date, pi_job_id, pi_salary, pi_bonus, pi_email);

  


END$$

DELIMITER

33 如何删除存储过程？

使用 DROP PROCEDURE 命令删除存储过程，使用 DROP FUNCTION 命令删除存储函数。以下语句删除存储过程 insert_employee：

DROP PROCEDURE insert_employee;

34 什么是触发器？

触发器（Trigger）是一种特殊的存储过程，当某个事件发生的时候自动执行触发器中的操作。最常见的触发器是基于表的触发器，包括 INSERT、UPDATE 和 DELETE 语句触发器。根据触发的时间，又可以分为 BEFORE 和 AFTER 触发器。另外，根据触发的粒度，又可以分为行级触发器和语句级触发器。

触发器典型的应用场景包括：

审计表的数据修改。某些表中可能包含敏感信息，比如员工的薪水，要求记录所有的修改历史。这种需求可以通过创建针对员工表的语句级 UPDATE 触发器实现。
实现复杂的业务约束。在触发器中增加业务检查和数据验证，阻止非法的业务操作。

不过，触发器也可能带来一些问题。比如增加数据库服务器的压力；逻辑隐藏在数据库内部，应用端无法进行控制。

触发器的管理主要包括创建和删除：

CREATE TRIGGER 用于创建触发器。
DROP TRIGGER 用于删除触发器。

35 为员工表创建一个审计表和审计触发器，记录每次修改员工月薪的操作

CREATE TABLE employee_audit

( emp_id INTEGER NOT NULL

, salary_old NUMERIC(8,2) NOT NULL

, salary_new NUMERIC(8,2) NOT NULL

, update_ts TIMESTAMP NOT NULL

);

  


-- MySQL 实现

DELIMITER $$

CREATE TRIGGER employee_audit

BEFORE UPDATE ON employee

FOR EACH ROW

BEGIN

IF OLD.salary <> NEW.salary THEN

INSERT INTO employee_audit(emp_id, salary_old, salary_new, update_ts)

VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);

END IF;

END$$

DELIMITER ;

UPDATE employee

SET salary = salary + 1000

WHERE emp_name = '张飞';

  


SELECT * FROM employee_audit

36 主键索引和唯一索引的区别是什么？

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为空值和唯一索引了。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
主键可以被其他表引用为外键，而唯一索引不能。

37 MySQL中有哪些表类型？

MyISAM
Heap
Merge
INNODB
ISAM

但是实际上基本只有INNODB和MyISAM（MYSQL默认类型）两种

INNODB是事务安全型表类型

38 SQL的生命周期？

应用服务器与数据库服务器建立一个连接
数据库进程拿到请求SQL
解析并生成执行计划，执行
读取数据到内存并进行逻辑处理
通过步骤一的连接，发送结果到客户端
关掉链接，释放资源

39 如何查看为表格定义的所有索引？

show index from <tablename>

40 数据库为什么使用B+树而不是使用B树？

B树只适合随机检索，而B+树同时支持随机检索和顺序检索
B+树的空间利用率更高，可以减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素
B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

41 怎么优化SQL语句？

1.  避免全表扫描
1.  1.  在where 和order by 涉及的列上建立索引
    1.  避免在where子句中对字段进行is null值判断，否则引擎会放弃索引进行全表扫描，使用is not null
    1.  where子句中也尽量不要用or，这样也会使放弃索引进行全表扫描，尽量用in 替换or
    1.  in 和not in慎用，会进行全表扫描
    1.  避免在where子句后面写函数，也会导致强制全表扫描

42 覆盖索引，回表了解吗？

覆盖索引：查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

SQL面试题思维导图+常考题型

SQL面试题结构拆解思维导图

# SQL初级查询面试题

# SQL中级查询面试题

# 设计与开发（底层原理）面试题

注意添加多个列的写法是 alter table XX add XX 类型，add XX 类型 ，add XX 类型（每个都要写关键字）

注意添加多个列的写法是 alter table XX add XX 类型，add XX 类型，add XX 类型（每个都要写关键字）