一、sql概述
1.sql（Structured Query Language）结构化查询语言，是关系数据库的标准语言
2.sql的特点：综合统一、高度非过程化、面向集合的操作方式、同一种语法结构提供多种使用方法、语言简洁，易学易用

二、sql的应用
学生-课程数据库为例

• 学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
• 课程表：Course(Cno,Cname,Cnpo,Ccredit)
• 学生选课表：SC(Sno,Cno,Grade)

1.数据定义
（1）模式定义
create schema <模式名> authorization <用户名>
未定义模式名 -> 模式名隐含为用户名（调用该命令的用户必须拥有DBA权限）
（2）模式删除
drop schema <用户名> <cascade（级联） | restrict（限制）>
如果模式中定义了数据库对象（表、视图等）：
采用cascade，删除模式+数据库对象
采用restrict，拒绝该删除语句的执行（无下属对象方可执行）

例1.为用户WANG定义一个学生-课程模式S-T
create schema S-T authorization WANG;

（3）定义基本表
create table <表名>(<列名> <数据类型> [列级完整性约束条件] …);
（4）修改基本表
alter table <表名>
[add <新列名> <数据类型> [完整性约束]]
[drop [完整性约束名]]
[alter column <列名> <数据类型>]
（5）删除基本表
drop table <表名> [restrict | cascade];

例2.建立一个“学生”表Student
create table Student
(Sno char(9) primary key,	//Sno主码Sname char(20) unique,	    //Sname唯一Ssex char(2),Sage smallint,Sdept char(20));例3.建立一个“课程”表Course
create table Course
(Cno char(4) primary key,	//Cno主码Cname char(20) ,Ccredit smallint,foreign key (Cpno) references Course(Cno));	//Cpno先修课，外码例4.建立一个“选课”表SC
create table SC
(Sno char(9);Cno char(4);Grade smallint;primary key(Sno,Cno),	//主码Sno，Cnoforeign key (Sno) references Student(Sno),foreign key (Cno) references Course(Cno));例5.向Student表增加“入学时间”列，其数据类型为日期型
alter table Student add Etime date;例6.将年龄的数据类型由字符型改为整型
alter table Student alter column Sage int;例7.增加课程名必须唯一值的约束条件
alter table Course add unique(Cname);例8.删除Student表
drop table Student cascade;

（6）建立索引
create [unique | cluster] index <索引名> on <表名>(<列名> [<次序>]…);
unique唯一索引
cluster聚簇索引，在一个基本表上最多只能建立一个聚簇索引（经常更新的表不宜建立聚簇索引）
次序asc上升、desc下降（缺省自动判别为asc）
（7）删除索引
drop index <索引名>；

例9.为学生-课程数据库中的Student，Course，SC三个表建立索引。
Student表按学号升序建唯一索引；
Course表按课程号升序建唯一索引；
SC表按学号升序和课程号降序建唯一索引。
create unique index on Stusno Student(Sno);
create unique index on Coucno Course(Cno);
create unique index on SCno SC(Sno asc,Cno desc);例10.删除Student表的Stusname索引
drop index Stusname;

2.数据查询
select [all | distinct] <目标列表达式>…
from <表名或视图名>…
[where <条件表达式>]
[group by <列名1> [Having <条件表达式>]]
[order by <列名2> [asc | desc]];
（1）单表查询

例1 查询全体学生的学号与姓名。
select Sno,Sname
from Student;例2 查询全体学生的姓名、学号、所在系。
select Sname,Sno,Sdept
from Student;例3 查询全体学生的详细记录。
select *
from Student;例4 查全体学生的姓名及其出生年份。
select Sname,2020-Sage
from Student;例5 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名
select Sname,2020-Sage,Lower(Sdept)
from Student;例6 查询选修了课程的学生学号。
select distinct Sno
from SC;例7 查询计算机科学系全体学生的名单。
select Sname
from Student
where Sdept='CS';例8 查询所有年龄在20岁以下的学生姓名及其年龄。
select Sname,Sage
from Student
where Sage<20;例9 查询考试成绩有不及格的学生的学号。
select distinct Sno
from SC
where Grade<60;例10 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
select Sname,Sdept,Sage
from Student
where Sage between 20 and 23;例11 查询年龄不在20~23岁之间的学生姓名、系别和年龄
select Sname,Sdept,Sage
from Student
where Sage not between 20 and 23;例12 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。
select Sname,Ssex
from Student
where Sdept in('IS','MA','CS');例13 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
select Sname,Ssex
from Student
where Sdept not in('IS','MA','CS');例14 查询学号为200215121的学生的详细情况。
select *
from Student
where Sno='200215121';例15 查询所有姓刘学生的姓名、学号和性别。
select Sname,Sno,Ssex
from Student
where Sname='刘%';例16 查询姓"欧阳"且全名为三个汉字的学生的姓名。
select Sname
from Student
where Sname='欧阳_';例17 查询名字中第2个字为"阳"字的学生的姓名和学号。
select Sname,Sno
from Student
where Sname='_阳%';例18 查询所有不姓刘的学生姓名。
select Sname
from Student
where Sname not like '刘%';例19 查询DB_Design课程的课程号和学分。
select Cno,Ccredit
from Course
where Cname='DB\_Design' escape'\';例20 查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。
select *
from Course
where Cname='DB\_%i__' eacape '\';例21 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
select Sno,Cno
from SC
where Grade is null;例22 查所有有成绩的学生学号和课程号。
select Sno,Cno
from SC
where Grade is not null;例23 查询计算机系年龄在20岁以下的学生姓名。
select Sname
from Student 
where Sdept='CS' and Sage<20;例24 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
select Sno,Grade
from SC
where Cno='3'
order by Grade desc;例25 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
select *
from Student
order by Sdept,Sage desc;例26 查询学生总人数。
select count(*)
from Student;例27 查询选修了课程的学生人数。
select count(distinct Sno)
from SC;例28 计算1号课程的学生平均成绩。
select avg(Grade)
from SC;
where Cno='1';例29 查询选修1号课程的学生最高分数。
select max(Grade)
from SC
where Cno='1';例30 查询学生200215012选修课程的总学分数。
select sum(Ccredit)
from SC,Course
where Sno='200215012' and SC.Cno=Course.Cno;例31 求各个课程号及相应的选课人数。
select Cno,count(Sno)
from SC
group by Cno;例32 查询选修了3门以上课程的学生学号。
select Sno
from SC
group by Sno
having count(*)>3;

（2）连接查询

例33 查询每个学生及其选修课程的情况
select Student.Sno,Sname,Ssex,Sage,Sdept,SC.Sno,Cno,Grade
from Student,SC
where Student.Sno=SC.Sno;例34 对例33用自然连接完成。
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student,SC
where Student.Sno=SC.Sno;例35 查询每一门课的间接先修课（即先修课的先修课）
select first.Cno,second.Cpno
from Course first,Course second
where first.Cpno=second.Cno;例36 例33左外连接
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left out join SC on(Student.Sno=SC.Sno);例37 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名		
select Student.Sno,Sname
from Student,SC
where Student.Sno=SC.Sno and Cno='2' and Grade>90;例38 查询每个学生的学号、姓名、选修的课程名及成绩
select Student.Sno,Sname,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and SC.Cno=Course.Cno;

（3）嵌套查询

例39 查询与“刘晨”在同一个系学习的学生。
select *
from Student
where Sdept in(select Sdeptfrom Studentwhere Sname='刘晨');例40 查询选修了课程名为“信息系统”的学生学号和姓名
select Sno,Sname
from Student
where Sno in(select Snofrom SCwhere Cno in(select Cnofrom Coursewhere Cname='信息系统'));例41 找出每个学生超过他选修课程平均成绩的课程号。
select Cno
from SC x
where Grade>(select avg(Grade)from SC ywhere x.Sno=y.Sno);例42 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
select Sname,Sage
from Student
where Sage<any(select Sagefrom Studentwhere Sdept='CS') and Sdept<>'CS';例43 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
select Sname,Sage
from Student
where Sage<all(select Sagefrom Studentwhere Sdept='CS') and Sdept<>'CS';例44 查询所有选修了1号课程的学生姓名。
select Sname
from Student
where exists(select *from SCwhere SC.Sno=Student.Sno and Cno='1');例45 查询没有选修1号课程的学生姓名。
select Sname
from Student
where not exists(select *from SCwhere SC.Sno=Student.Sno and Cno='1');例46 查询选修了全部课程的学生姓名。
select Sname
from Student
where not exists(select *from Coursewhere not exists(select *from SCwhere Course.Cno=SC.Cno and SC.Sno=Student.Sno);例47 查询至少选修了学生200215122选修的全部课程的学生号码。
select distinct Sno
from SC x
where not exists(select *from SC ywhere y.Sno='200215122' and not exists(select *from SC zwhere z.Sno=x.Sno and z.Cno=y.Cno);

（4）集合查询

例48 查询计算机科学系的学生及年龄不大于19岁的学生。
select *
from Student
where Sdept='CS' 
union
select *
from Student
where Sage<=19;例49 查询选修了课程1或者选修了课程2的学生。
select *
from SC
where Cno='1'
union
select * 
from SC
where Cno='2';例50 查询计算机科学系的学生与年龄不大于19岁的学生的交集
select *
from Student
where Sdept='CS'
intersect
select *
from Student
where Sage<=19;例51 查询选修课程1的学生集合与选修课程2的学生集合的交集
select *
from SC
where Cno='1'
intersect
select *
from SC
where Cno='2';例52 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
select *
from Student
where Sdept='CS'
except
select *
from Student
where Sage<=19;

3.数据更新
（1）插入元组
insert into <表名> … values…
（2）插入子查询结果
insert into <表名> 子查询；
（3）修改数据
update <表名>
set <列名> = <表达式>
where <条件>
（4）删除数据
delete
from <表名>
where <条件>

例1 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。
insert into Student(Sno,Sname,Ssex,Sdept,Sage) values('200215128','陈冬','男','IS',18);例2 插入一条选课记录( '200215128'，'1')。
insert into SC(Sno,Cno) values('200215128','1');例3 对每一个系，求学生的平均年龄，并把结果存入数据库。
create table Dept_age
(Sdept char(15),Avg_age smallint);
insert into Dept_age(Sdept,Avg_age)
select Sdept,avg(Sage)
from Student
group by Sdept;例4 将学生200215121的年龄改为22岁
update Student
set Sage=22
where Sno='200215121';例5 将所有学生的年龄增加1岁
update Student
set Sage=Sage+1;例6 将计算机科学系全体学生的成绩置零。
update SC
set Grade=0
where 'CS'=(select Sdeptfrom Studentwhere Student.Sno=SC.Sno);例7 删除学号为200215128的学生记录。
delete
from Student
where Sno='200215128';例8 删除所有的学生选课记录。
delete 
from SC;例9 删除计算机科学系所有学生的选课记录。
delete
from SC
where 'CS'=(select Sdeptfrom Studentwhere Student.Sno=SC.Sno);

4.视图
（1）建立视图
create view <视图名>
as <子查询>
[with check option];
（2）删除视图
drop view <视图名> [cascade];
（3）查询视图

例1 建立信息系学生的视图。
create view IS_Student
as
select Sno,Sname,Sage
from Student
where Sdept='IS';例2 建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。
create view IS_Student
as
select Sno,Sname,Sage
from Student
where Sdept='IS'
with check option;例3 建立信息系选修了1号课程的学生视图。
create view IS_S1(Sno,Sname,Grade)
as
select Student.Sno,Sname,Grade
from Student,SC
where Sdept='IS' and Student.Sno=SC.Sno and Cno='1';例4 建立信息系选修了1号课程且成绩在90分以上的学生的视图。
create view IS_S2
as
select Sno,Sname,Grade
from IS_S1
where Grade>90;例5 定义一个反映学生出生年份的视图。
create view Birth_Student(Sname,Birth)
as
select Sname,2020-Sage
from Student;例6 将学生的学号及他的平均成绩定义为一个视图。假设SC表中“成绩”列Grade为数字型。
create view S_G(Sno,Avg_Grade)
as
select Sno,avg(Grade)
from SC
group by Sno;例7 将Student表中所有女生记录定义为一个视图
create view W_Student(Sno,Sname,Sage)
as
select Sno,Sname,Sage
from Student
where Ssex='女';例8 删除视图IS_S1：
由于视图IS_S1导出了IS_S2视图，所以只能用级联删除
drop view IS_S1 cascade;例9 在信息系学生的视图中找出年龄小于20岁的学生。
select *
from IS_Student
where Sage<20;例10 查询选修了1号课程的信息系学生
select *
from IS_S1;例11 在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩
select *
from S_G
where Avg_Grade>90;例12 将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。
update IS_Student
set Sname='刘辰'
where Sno='200215122';例13 向信息系学生视图IS_S中插入一个新的学生记录：200215129，赵新，20岁
insert into IS_S(Sno,Sname,Sage) values('200215129','赵新',20);例14 删除信息系学生视图IS_Student中学号为200215129的记录
delete 
from IS_Student
where Sno='200215129';