C#.NET面试题汇总系列八：数据库

1. 什么是SQL？

SQL（结构化查询语言）是一种设计用于检索和操作数据的数据库。它属于美国国家标准协会（ANSI）的一种标准，可用于执行Select（选择）、Update（更新）、Delete（删除）和Insert（插入）等数据任务

2. 有哪几种类型的SQL命令？

SQL命令分为以下类型：

DDL（数据定义语言） - 用于定义数据库的结构

DCL（数据控制语言） - 用于为用户提供权限

DML（数据操作语言） - 用于管理数据

DQL（数据查询语言） - 所有命令都在SQL中，用于检索DQL中的数据

TCL（事务控制语言） - 用于管理DML所做的更改

3. 什么是Index（索引）？

数据库索引的本质是一个排序的数据结构，以协助快速查询、更新数据库表中的数据，可以理解为一种特殊的目录，实现方式通常采用B树和B+树

索引用于加速查询的性能，它可以更快地从表中检索数据，可以在一组列上创建索引

4. 索引的分类有哪些？

唯一索引：唯一索引不允许两行具有相同的索引值

主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

5. Clustered（群集）和Non-Clustered Index（非群集）索引之间有什么区别？

聚集索引，有助于轻松检索数据，并且只有一个聚集索引与一个表一起分配，会更改记录在数据库中的保存方式

非聚集索引，与聚集索引相比，非聚集索引很慢，并且在非集群索引的情况下，该表可以具有多个索引，为表创建一个对象，该表是搜索后指向表的一个点

6. 索引应该建在那些列上？

在经常需要搜索的列上，可以加快搜索的速度

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

7. 为什么要一定要设置主键?

其实这个不是一定的，有些场景下，小系统或者没什么用的表，不设置主键也没关系

主键作用，唯一标识一条记录，不能有重复的，不允许为空，默认主键为主键索引

8. 主键，外键

主键，唯一标识一条记录，不能有重复的，不允许为空

外键，表的外键是另一表的主键, 外键可以有重复的, 可以是空值

9. CHAR 和 VARCHAR 的区别？

char和varchar类型声明长度表示用户想保存的最大字符数，其中char(M)定义的列的长度为固定的，M的取值可以0-255之间，当保存char值时，在它们的右边填充空格以达到指定的长度。

当检索到char值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。char存储定长数据很方便，char字段上的索引效率很高。

varchar(M)定义的列的长度是可变长度字符串，在MySQL5.0以上的版本中，varchar的数据类型长度支持到了65535，因为起始位和结束位占去了3个字节，所以其整体最大长度为65532字节（varchar的最大有效长度由最大行大小和使用的字符集确定）。

同样在char和varchar尾部加空格，检索时char类型后的被删掉，而varchar类型的保存

10. 什么是存储过程？有哪些优缺点？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快

存储过程的优点

● 能够将代码封装起来
● 保存在数据库之中
● 让编程语言进行调用
● 存储过程是一个预编译的代码块，执行效率比较高
● 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率

存储过程的缺点：

● 每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
● 业务逻辑放在数据库上，难以迭代

11. 说说存储过程与函数的区别

• 存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据
• 存储过程声明用procedure，函数用function
• 存储过程不需要返回类型，函数必须要返回类型
• 存储过程可作为独立的pl-sql执行，函数不能作为独立的plsql执行，必须作为表达式的一部分
• 存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值
• sql语句中不可用调用存储过程，而函数可以

12. 什么是事务？

事务简单来说：一个Session中所进行所有的操作，要么同时成功，要么同时失败

13. 游标是什么？

是对查询出来的结果集作为一个单元来有效的处理

游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行，可以对结果集当前行做修改

一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要

14. 触发器的作用？

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发

15. 字段为什么要定义为NOT NULL?

null对索引性能不好

如果某列存在null的情况，可能导致count() 等函数执行不对的情况

sql 语句写着也麻烦，既要判断是否为空，又要判断是否为null等

16. 什么是最左前缀原则？

最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到

17. 查询语句的执行先后顺序？

查询中用到的关键词主要包含六个，并且他们的顺序依次为 select--from--where--group by--having--order by

执行顺序：from join on where group by avg,sum.... having select distinct order by limit

18. 什么情况下会造成死锁？

所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁

这些永远在互相等待的进程称为死锁进程，表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

19. 死锁如何解决？

查出的线程杀死 kill

设置锁的超时时间

20. 在数据库中查询语句速度很慢，如何优化？

1.建索引

2.减少表之间的关联

3.优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面

4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据

21. 乐观锁与悲观锁

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁；传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁

乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现

查看学生每门课程分数大于80的学生姓名

表字段，name，kecheng，fenshu

答案1：
select name from tableA group by name having min(fenshu)>80

答案2：
select name from tableA group by name having count(kecheng)>=3 and min(fenshu)>=80

查询每个日期胜负数量（行转列）

如果要生成下列结果, 该如何写sql语句?

日期	胜	负
2005-05-10	2	3
2005-05-11	3	1

select 
	rq, 
	sum(case when shengfu='胜' then 1 else 0 end)'胜',
	sum(case when shengfu='负' then 1 else 0 end)'负' 
from tableb group by rq

显示所有可能的比赛组合

一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合

select a.name, b.name
from team a, team b 
where a.name < b.name

关于年度、季度SQL统计查询的面试题

A表 - 销售数据表，其中有id，adddate是销售时间，company是客户公司，money是销售金额，sales是销售员

B表 - 产品销售表，其中有id，aid对应A表中的id，pid对应是C表中产品表

C表 - 产品类型表，其中有id，type类型名称

请使用SQL语句统计2011年至2013年三年中每个季度的销售金额?

SELECT  
YEAR(`adddate`) AS yer, 
QUARTER(`adddate`) AS jd, 
SUM(money) 
FROM a  
WHERE YEAR(`adddate`) IN ('2011','2012','2013')  
GROUP BY QUARTER(`adddate`),YEAR(`adddate`)

员工，部门

部门表：部门编号，部门名称，地址；

员工表：员工编号，员工名字，职务，管理编号，入职日期，薪资，奖金，部门编号；

列出至少有一个雇员的所有部门

select 
	dname,
	count(empno) as 部门人数 
from dept,emp where dept.deptno=emp.deptno
group by dname 
having count(empno)>=1;

列出薪金比"SMITH"多的所有雇员

select * from emp where sal>(select sal from emp where ename='SMITH');

列出入职日期早于其直接上级的所有雇员

select * from emp e1 where hiredate <(select hiredate from emp e2 where e2.empno=e1.mgp);

找员工姓名和直接上级的名字

select e1.ename 员工姓名,e2.ename 直接上级 from emp e1,emp e2 where e1.mgp=e2.empno;

显示部门名称和人数

select dname,count(empno) as 部门人数 
from dept,emp 
where dept.deptno=emp.deptno 
group by dname;

显示每个部门的最高工资的员工

SELECT ename,sal,e1.deptno FROM emp e1
WHERE sal = 
(SELECT MAX(sal) FROM emp e2 WHERE e2.`deptno`=e1.`deptno`
GROUP BY e2.`deptno`);

显示每个部门的工资前2名的员工

select deptno, ename, sal      
from emp e1     
where      
   (  
    select count(1)     
    from emp e2     
    where e2.deptno=e1.deptno and e2.sal>=e1.sal  
   ) <=2  
order by deptno, sal desc;