1、数据库的存储过程:
存储过程是在大型数据库系统中,一组为了完成特定功能的SQL语句集,它存储在数据库中,一次编译后永久有效,通过用户指定存储过程的名字和参数来执行它。
存储过程和函数的区别:
- 一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强
- 对于存储过程来说可以返回参数,而函数只能返回值或者表对象
- 存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句的一个部分来调用
存储过程的优点:
- 执行速度更快-在数据库中保存的存储过程语句都是编译过的
- 允许模块化程序设计
- 提高系统安全性-防止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');