SQL常用操作

1.表结构

以oracle为例，下面定义三个表：

学生信息表Student，课程表Course和选课表SC：

create table Student (
    Sno     varchar2(10) primary key,
    Sname   varchar2(20) not null,
    Ssex    char(2) check (Ssex in('男', '女')),
    Sage    integer check (Sage > 0),
    Sdept   varchar2(20)
);

create table Course (
    Cno     char(4) primary key,
    Cname   varchar2(40) not null,
    Cpno    char(4),
    Ccredit integer,
    foreign key (Cpno) references Course(Cno)
);

create table SC (
    Sno     varchar2(10),
    Cno     char(4),                                                                           
    Grade   integer check(Grade >= 0 and Grade <= 100),
    primary key (Sno, Cno),
    foreign key (Sno) references Student(Sno),
    foreign key (Cno) references Course(Cno)
);

表中数据暂时为：

Student：

SNO	SNAME	SSEX	SAGE	SDEPT
200215121	李勇	男	20	CS
200215122	刘晨	女	19	CS
200215123	王敏	女	18	MA
200215125	张立	男	19	IS

Course：

CNO	CNAME	CPNO	CCREDIT
2	数学		2
6	数据处理		2
7	C语言	6	4
4	操作系统	6	3
5	数据结构	7	4
1	数据库	5	4
3	信息系统	1	4

SC：

SNO	CNO	GRADE
200215121	1	92
200215121	2	85
200215121	3	88
200215122	2	90
200215122	3	80

2. 聚集函数

count(*)   或   count(列名)

sum(列名)

avg(列名)

max(列名)

min(列名)

查询选修了课程的学生人数：

select count(distinct sno) from sc;

计算1号课程的学生平均成绩：

select avg(grade) from sc where cno = '1';

查询选修1号课程的学生最高分数：

select max(grade) from sc where cno = '1';

查询学生200215121选修课程的总的学分数：

select sum(Ccredit) from sc, course where sno = '200215121' and sc.cno = course.cno;

3. group by 

group by 将查询结果按某一列或多列的值分组，值相等的为一组。与聚集函数一起用。

求各个课程号及相应的选课人数：

select cno, count(sno) from sc group by cno;

输出为：

CNO	COUNT(SNO)
2	2
3	2
1	1

查询选修了2门以上课程的学生学号：

select sno from sc group by sno having count(*) > 2;

having 表达式          此表达式一般为聚集函数，即having一般作用与聚集函数

4. 连接查询

有等值与非等值连接查询、自身连接、外连接、复合条件连接等

（1）自身连接

通常将同一个表起多个别名，供使用。

查询每一门课程间接先修课（即先修课的先修课）：

select first.cno, second.cpno from course first, course second
where first.cpno = second.cno;

5. 嵌套查询

（1）带有IN谓词的子查询

查询与刘晨在同一个系学习的学生：

select sno, sname, sdept from student 
where sdept in (
    select sdept from student where sname = '刘晨'
);

查询选修了课程名为“信息系统”的学生学号和姓名：

select sno, sname from student    --有里向外， 父查询，不相关子查询
where sno in (
    select sno from sc where cno in (
        select cno from course where cname = '信息系统'  
    )
);

（2）带有比较运算符的子查询

找出每个学生超过他选修课程平均成绩的课程号：

select sno, cno from sc x     --相关子查询
where grade >= (
    select avg(grade) from sc y
    where y.sno = x.sno
);

6. 带有EXISTS或NOT EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真或假。

查询所有选修了1号课程的学生姓名：

select sname from student 
where exists (
    select * from sc where sno = student.sno and cno = '1' 
);

查询没有选修1号课程的学生姓名：

select sname from student 
where not exists (
    select * from sc where sno = student.sno and cno = '1' 
);

查询选修了全部课程的学生姓名：

查询这样的学生，没有一门课程是他不选修的

select sname from student
where not exists (
    select * from course 
    where not exists (
        select * from sc
        where sno = student.sno and cno = course.cno
    )
);

查询至少选修了学生200215122选修的全部课程的学生号码：

不存在这样的课程y，学生200215122选修了y而学生x没有选

select distinct sno from sc scx
where not exists (
    select * from sc scy
    where scy.sno = '200215122' and not exists (
        select * from sc scz 
        where scz.sno = scx.sno and scz.cno = scy.cno 
    )
);