数据库知识点小结

1、数据库的存储过程：

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，通过用户指定存储过程的名字和参数来执行它。

存储过程和函数的区别：

1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强
2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象
3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用

存储过程的优点：

1. 执行速度更快-在数据库中保存的存储过程语句都是编译过的
2. 允许模块化程序设计
3. 提高系统安全性-防止SQL注入

2、not in 和not exists:

如果查询语句使用了not in 那么内外表都会进行全表扫描，没有用到索引

not exists的子查询依然能用到表上的索引。所以无论表大小，用not exists都比not in 要快。

3、编写SQL语句：

学生表：

课程表： 

成绩表： 

 教师表：

 

1）查询“001”课程比“002”课程成绩高的所有学生的学号：

select a.s_id from (select s_id, score from SC where C_ID = '001') a,(select s_id,score from SC where C_ID='002') b where a.score>b.score and a.s_id = b.s_id;

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

select S_ID, avg(score) from SC group by S_ID having avg(score) > 60;

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

select Student.S_ID, Student.Sname, count(SC.C_ID), sum(score) from Student left Outer join SC on Student.S_ID = SC.S_ID group by Student.S_ID, Sname;

4）查询姓“李”的老师的个数：

select count(distinct(Tname)) from Teacher where Tname like '李%';

5）查询没学过“叶平”老师课的同学的学号、姓名：

select Student.S_ID, Student.Sname from Student where S_ID not in (select distinct(SC.S_ID) from SC, Course, Teacher where SC.C_ID = Course.C_ID and Teacher.t_id = Course.t_id and Teacher.t_name = '叶萍';

6）查询学过"001"并且也学过编号"002"课程的同学的学号、姓名：

select Student.S_ID, Student.Sname from Student, SC where Studnet.S_ID = SC.S_ID and SC.C_ID = '001' and exists (select * from SC as SC_2 where SC_2.S_ID = SC.S_ID and SC_2.C_ID = '002');

7）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：

Select S_ID,Sname from (select Student.S_ID,Student.Sname,score ,(select score 
from SC SC_2 where SC_2.S_ID=Student.S_ID and SC_2.C_ID='002') score2 
 from Student,SC where Student.S_ID=SC.S_ID and C_ID='001') S_2 where score2 
<score;

8）查询所有课程成绩小于60分的同学的学号、姓名：

select S_ID,Sname 
 from Student 
 where S_ID not in (select S.S_ID from Student AS S,SC where S.S_ID=SC.S_ID and
score>60);

9）查询没有学全所有课程的同学的学号、姓名：

select Student.S_ID,Student.Sname 
 from Student,SC 
 where Student.S_ID=SC.S_ID group by Student.S_ID,Student.Sname having
count(C_ID) <(select count(C_ID) from Course);

10）查询至少有一门课程与学号为"1001"的同学所学相同的同学的学号和姓名：

select distinct S_ID,Sname from Student,SC where Student.S_ID=SC.S_ID and 
SC.C_ID in (select C_ID from SC where S_ID='1001');

11）查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名：

select distinct SC.S_ID,Sname 
 from Student,SC 
 where Student.S_ID=SC.S_ID and C_ID in (select C_ID from SC where
S_ID='001');