.NET面试题解析(11)-SQL语言基础及数据库基本原理

转自:http://www.cnblogs.com/anding/p/5281558.html

常见面试题目:

0. 基本SQL语法题目，在 正文“基础SQL语法”中有13道题，这里就略过了。

1. 索引的作用？她的优点缺点是什么？

2. 介绍存储过程基本概念和 她的优缺点？

3. 使用索引有哪些需要注意的地方？

4. 索引碎片是如何产生的？有什么危害？又该如何处理？

5. 锁的目的是什么？

6. 锁的粒度有哪些？

7. 什么是事务？什么是锁？

8. 视图的作用，视图可以更改么？

9. 什么是触发器(trigger)? 触发器有什么作用?

10. SQL里面IN比较快还是EXISTS比较快?

11. 维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑?为什么?

  基础SQL语法

以下SQL所使用的实例数据库为Sqlite（因为相当轻量），数据库文件（下载链接,test.db,6KB）,SQLite数据库管理工具推荐SQLite Expert Personal。

0. 创建表

定义如下表结构，后面的题目都以此表结构为依据。

Student(ID,Name,Age,Sex) 学生表   
Course(ID,Name,TeacherID) 课程表   
Score(StudentID,CourseID,Score) 成绩表   
Teacher(ID,Name) 教师表

创建表的语法很简单，SQL语句：

CREATE TABLE [Student] (
  [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [Name] NVARCHAR(20), 
  [Age] INT, 
  [Sex] INT);

CREATE TABLE [Course] (
  [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [Name] NVARCHAR(20), 
  [TeacherID] INT) 

CREATE TABLE [Score] (
  [Score] double,   
  [StudentID] INT,
  [CourseID] INT) 

CREATE TABLE [Teacher] (
  [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,  
  [Name] NVARCHAR(20))

1. 查询语文“1”比数学“2”课程成绩高的所有学生的姓名

这是一个嵌套查询的题目，考察对子查询的使用，子查询结果作为一个集合可以当做一个独立的表来看待，子查询必须用括号括起来：

select st.[Name],c1.Score,c2.Score from
(select sc.[Score], sc.StudentID from Score sc  where sc.[CourseID]=1)c1,
(select sc.[Score],sc.StudentID from Score sc where sc.[CourseID]=2)c2
join Student st on st.[ID]= c1.[StudentID]
where c1.[Score]>c2.[Score] and c1.[StudentID]==c2.[StudentID]

2. 查询平均成绩大于60分的同学的学号和平均成绩

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。GROUP BY子句在SELECT语句的WHERE子句之后并ORDER BY子句之前。WHERE 关键字无法与合计函数一起使用，GROUP BY后面不能接WHERE条件，使用HAVING代替。

select sc.[StudentID],avg(sc.Score) from Score sc
group by sc.[CourseID] having avg(sc.Score)>60

3. 查询所有同学的学号、姓名、选课数、总成绩；

select st.[ID],st.Name,count(sc.CourseID),sum(sc.Score) from Student st
left outer join Score sc on sc.[StudentID]=st.[ID]
group by st.[ID]

外连接的三种形式如下表，其中outer可以省略。与外连接对应的就是内连接inner join ，要两个表同时满足指定条件。

4. 查询姓“张”的老师的个数；

select count(t.ID) from Teacher t where t.Name like '张%'

SQL LIKE子句使用通配符运算符比较相似的值。符合LIKE操作符配合使用2个通配符：

• 百分号 (%)：百分号代表零个，一个或多个字符

• 下划线 (_)：下划线表示单个数字或字符

5. 找出教师表中姓名重复的数据，然后删除多余重复的记录，只留ID小的那个。

select t.Name,count(t.Name) from Teacher t group by t.[Name] having count(t.Name)>1

删除多余的记录，写这种稍微复杂一点的sql的时候，要学会拆解，此题可以拆解为三个部分（删除+重复数据+重复数据中ID最小的数据），先分别把3个部分的sql写了，然后再一步步合并，这样就轻松多了。

delete from Teacher where Name in
       (select t2.Name from Teacher t2 group by t2.[Name] having count(t2.Name)>1)
and ID not in
       (select min(t3.ID) from Teacher t3 group by t3.Name having count(t3.Name)>1)

6. 按照成绩分段标示（<60不及格，60-80良，>80优），输出所有学生姓名、课程名、成绩、成绩分段标示。

<