2020-12-17

基本SQL语法

1.创建表
定义如下表结构，后面的题目都以此表结构为依据。
Student(ID,Name,Age,Sex)学生表
Course(ID,Name,TeacherID)课程表
Source(StudentID,CourserID,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))

2.查询语文“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 where sc.CourseID=2)c2 join Student st on st.ID=c1.StudentID where c1.Score > c2.Score and c1.StudentID = c2.StudentID

3.查询平均成绩大于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

4.查询所有同学的学号、姓名、选课数、总成绩；
select st.ID,st.Name,count(sc.CourseID),sum(sc.Score) from Student st left join Score sc on sc.StudentID = st.ID group by st.ID

5.查询姓“张”的老师的个数；
select count(t.ID) from Teacher t where t.Name like ‘%张%’

6.找出教师表中姓名重复的数据，然后删除多余重复的记录，只留ID小的那个。
select t.Name,count(t.Name) from Teacher t group by t.Name having count(t.Name)>1
删除多余的记录，写这种稍微复杂一点的SQL的时候，要学会拆分，此题可以拆解为三个部分（删除+重复数据+重复数据中ID最小的数据）
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)

7.按照成绩分段标示（<60不及格，60-80良，>80优），输出所有学生姓名、课程名、成绩、成绩分段标示。
select st.Name,c.Name,sc.Score,(case when sc.Score>80 then ‘优’ when sc.Score<60 then ‘不及格·’ else ‘良’ end) as ‘Remark’
from Score sc
inner join Student st on st.ID = sc.StudentID
inner join Course c on c.ID = sc.CourseID

8.查询所有课程成绩小于60分的同学的学号、姓名信息
select distinct st.* from Student st left join Score sc on sc.StudentID = st.ID where sc.Score < 60

9.查询各科成绩最高和最低的分：以如下形式显示：课程名称，最高分，最低分
select c.Name as 课程，max(sc.Score) as 最高分，min(sc.Score) as 最低分 from Score sc left join Coure c on c.ID = sc.CourseID group by sc.CourseID

10.查询不同老师所教不同课程平均分从高到底显示
select t.Name,c.Name,avg(sc.Score) from Score sc,Teacher t,Course c where sc.CourseID = c.ID and c.TeacherID = t.ID group by sc.CourseID order by avg(sc.Score) desc

11. 查询和“1”号的同学学习的课程完全相同的其他学生学号和姓名
    select s1.StudentID,c.Name from Score s1 where s1.CourseID in(select s2.Course from Score s2 where s2.StudentID = 1) group by s1.StudentID having count() = (select count() from Course c where c.ID = s1.Student)

12. 查询选修“张老师”所授课程的学生中，成绩最高的学生姓名及其成绩
    select t.Name,c.Name,s1.Score from Score s1,Teacher t,Course c where t.ID = c.TeacherID and s1.CourseID = c.ID and t.Name = “张老师” and s1.Score = (select max(Score) from Score where CourseID = c.ID)

13. 查询所有成绩第二名到第四名的成绩
    select s.StudentID,s.Score from Score s order by s.Score desc limit 2 offset 2
    –SQL 2005/2008中的分页函数是ROW_NUMBER() Over (Order by 列…)–
    select t.StudentID,t.Scorce from(select s2.StudentID,s2.Scorce,ROW_NUMBER() OVER (ORDER BY s2.Score) AS rn from Score s2)t where t.rn>=2 and t.rn<=4

14. 查询各科成绩前两名的记录
    select * from Score s1 where s1.Score in (select s2.Score from Score s2 where s1.CourseID = s2.CourseID order by s2.Scorce desc limit 2 offset 0)
    – 上面是sqlite中的语法，sqlite中没有top，使用limit代替，效果是一样的 –
    select * from Score s1 where s1.Score
    in(select Top 2 s2.Score from Score s2 where s1.[CourseID]=s2.[CourseID] order by s2.Score desc)
    order by s1.[CourseID],s1.[Score] desc

题目解析：
1.索引的作用？和它的优点缺点是什么？
索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。索引很类似于现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

2.介绍存储过程基本概念和它的优缺点
存储过程是一个预编译的SQL语句，它的优点是允许模块化的设计，也就是说只需要创建一次，在该程序中就可以调用多次。例如某次操作需要执行多次SQL，就可以把这个SQL做一个存储过程，因为存储过程是预编译的，所以使用存储过程比单纯SQL语句执行要快。缺点是可移植性差，交互性差。

3.使用索引有哪些需要注意的地方？
*创建索引的字段尽量小，最好是数值，比如整形int等；
*对于频繁修改的字段，尽量不要创建索引，维护索引的成本很高，而且更容易产生索引碎片；
*定期的索引维护，如索引碎片的修复等；
*不要建立或维护不必要的重复索引，会增加修改数据（新增、修改、删除数据）的成本；
*使用唯一性高的字段创建索引，切不可在性别这样的低唯一性的字段上创建索引；
*在SQL语句中，尽量不要在where条件中使用函数、运算符或表达式计算，会造成索引无法正常使用；
*应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；
*应尽量避免在where子句中使用！=或<>操作符，否则将导致引擎放弃使用索引而进行全表扫描；

4.索引碎片是如何产生的？有什么危害？又该如何处理？
索引在使用一段时间后（主要是新增、修改、删除数据，如果该页已经存储满了，就要进行页的拆分，频繁的拆分，会产生较多的索引碎片）会产生索引碎片。
索引碎片会严重影响数据的查询效率，如果碎片过多，索引可能不会被使用。
碎片的处理方式主要有两种：
第一种是预防：设置页的填充因子
意思就是在页上设置一段空白区域，在新增数据的时候，可以使用这段空白区域，可以一定的避免页的拆分，从而减少索引碎片的产生。
第二种是索引修复：定期对索引进行检查、维护，写一段SQL检查索引的碎片比列，如果碎片过多，进行碎片修复或重建，定期执行即可。

5.锁的目的是什么？
主要解决多个用户同时对数据库的并发操作时会带来以下数据不一致的问题：
*丢失更新，同时修改一条数据
*脏读，A修改了数据后，B读取后A又取消了修改，B脏读
*不可重复读，A用户读取数据，随后B用户读取该数据并修改，此时A用户在读取数据时发现前后两次的值不一致
*还有一种是幻读。

并发控制的主要方法是封锁，锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致

6.锁的粒度有哪些？
*数据库锁：锁定整个数据库，这通常发生在整个数据库模式改变的时候。
*表锁：锁定整个表，这包含了与该表相关联的所有数据相关的对象，包括实际的数据行以及与该表相关的所有索引中的键。
*区段锁：锁定整个区段，因为一个区段由8页组成，所以区段锁定是指锁定控制了区段、控制了该区段内8个数据或索引页以及这8页中的所有数据行。
*页锁：锁定该页中的所有数据或索引键。
*行或行标识符：虽然从技术上讲，锁是放在行标识符上的，但是本质上，它锁定了整个数据行。

7.什么是事务？什么是锁？
事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就会被失败，以后操作就会回滚到操作前的状态，或是上个节点。为了确保要么执行，要么不执行，就可以使用事务。要将所有组语句作为事务考虑，就需要通过ACID测试，即原子性、一致性、隔离性和持久性。
锁是实现事务的关键，锁可以保证事务的完整性和并发性。

8.视图的作用，视图可以更改吗？
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。

视图的目的在于简化检索，保护数据，并不用于更新。

9.什么是触发器（trigger）？触发器有什么作用？
触发器是数据库中由一定时间触发的特殊的存储过程，它不是由程序调用也不是手动启动的。触发器的执行可以由对一个表的insert、delete、update等操作来触发，触发器经常用于加强数据的完整性约束和业务规则等。

10.SQL里面IN比较快还是EXISTS比较快？
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
如果查询语句使用了not in,那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists 都比not in要快。

11.维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？
尽可能使用约束，如check、主键、外键、非空字段等来约束。这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整性和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。