数据库系统概述--关系数据库标准语言SQL

第三章 关系数据库标准语言SQL

1.SQL功能

数00据查询select

数据定义create drop alter

数据操纵 insert update delete

数据控制 grant revoke

 

2.数据定义

a.模式的定义和删除

为用户zhang创建一个模式test

create scheme test authorization ZHANG

删除模式test

drop scheme test cascade（级联）

drop scheme test restrict（限制）

 

b.基本表的定义、删除与修改

建立一个学生表Student

Create table student(

  Sno char(9) primary key,

  Sname char(20) unique,

  Ssex char(2),

  Sage smallint,

  Sdept char(20)

);

建立一个课程表Course

Create table course(

  Cno char(4) primary key,

  Cname char(40) not null,

  Cpno char(4),

  Ccredit smallint,

  Foreign key(cpno) preferences course(Cno)

);

建立学生选课表SC

Create table sc(

  Sno char(9),

  Cno char(4),

  Grade smallint,

  Primary key(Sno,Cno),

  Foreign key(Sno) references student(Sno),

  Foreign ket(Cno) references course(Cno)

);

删除student表

Drop table student cascade

 

c.索引建立、修改与删除

为学生-课程数据库中的student，course和sc三个表建立索引。其中student表按学号升序建唯一索引，course表按课程号升序建唯一索引，sc表按照学号升序和课程号降序建唯一索引。

Create unique index stusno on student(sno);

Create unique index coucno on course(cno);

Create unique inde  scno on sc(sno asc,cno desc)

将SC表的scno索引名改为scsno

Alter index scno rename to scsno

删除student表的stusname索引

Drop index stusname

 

3.数据查询

查询全体学生的学号与姓名

Select sno,sname from student;

查询全体学生的姓名、学号、所在系

Select sname,sno,sdept from student;

查询全体学生的详细记录

Select * from student;

查询全体学生的姓名及其出生年份

Select sname,sbirth from student;

查询全体学生的姓名、出生年份和所在的院系

Select sname,sbirth,lower(sdept) from student;

查询选修了课程的学生学号

Select discinct sno from sc;

查询计算机科学系全体学生的名单

Select sname from student where sdept=’CS’

查询所有年龄在20岁以下的学生姓名及其年龄

Select sname,sage from student where sage<20;

查询考试成绩不及格的学生的学号

Select distinct sno from sc where grade<60

查询年龄在20-23岁之间的学生姓名、系别和年龄

Select sname,sdept,sage from student where sage between 20 and 23

查询年龄不在20-23岁之间的学生姓名、系别和年龄

Select sname,sdept,sage from student where sage not between 20 and 23

查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别

Select sname,ssex from student where sdept in(‘CS’,’MA’,’IS’);

查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别

Select sname,ssex from student where sdept not in(‘CS’,’MA’,’IS’);

查询学号为201215121的学生的详细情况

Select * from student where sno like ‘201215121’

查询所有姓刘的学生的姓名、学号和性别

Select sname,sno,ssex from student where sname like ‘刘%’

查询姓欧阳且全名为三个汉字的学生的姓名

Select sname from student where sname like ’欧阳_’

查询名字中第二个字为阳的学生的姓名和学号

Select sname,sno from student where sname like ’_阳%’

查询所有不姓刘的学生的姓名、学号和性别

Select sname,sno,ssex from student where sname not like ‘刘%’

查询DB_Design课程的课程号和学分

Select cno,ccredit from course where cname like ‘DB\_Design’ escape ‘\’

查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况

Select * from course where cname like ‘DB\_%i__’ escape ‘\’

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩，查询缺少成绩的学生的学号和相应的课程号

Select sno,cno from sc where grade is null

查所有有成绩的学生学号和课程号

Select sno,cno from sc where grade is not null

查询计算机科学系年龄在20岁以下的学生姓名

Select sname from student where sdept=’cs’ and sage<20

查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

Select sno,grade from sc where cno=’3’ order by grade desc

查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

Select * from student order by sdept,sage desc;

查询学生总人数

Select count(*) from student

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

Select count(distinct sno) from sc

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

Select avg(grade) from sc where cno=’1’

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

Select max(grade) from sc where cno=’1’

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

Select sum(credit) from sc,course where sno=’201215012’ and sc.cno=course.cno

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

Select cno,count(sno) from sc group by cno

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

Select sno from sc group by sno having count(*)>3

查询平均成绩大于等于90分的学生学号和平均成绩

Select sno,avg(grade) from sc group by sno having avg(grade)>=90

查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

Select student,sno,sname from student,sc where student.sno=sc.sno and sc.cno=’2’ and sc.grade>90

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

Select sno,sname,sdept from student where sdept in( select sdept from student where sname=’刘晨’)

Select s1.sno,s1.sname,s1.sdept from student s1,student s2 where s1.sdept=s2.sdept and s2.sname=’刘晨’

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

Select student.sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.cname=’信息系统’

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

Select sno,cno from sc x where grade>=(select avg(grade) from sc y  where y.sno=x.sno)

查询选修了课程1或者选修了课程2的学生

Select sno from sc where cno=’1’ union select sno from sc where cno=’2’

查询计算机科学系的学生与年龄不大于19岁的学生的交集

Select * from student where sdept=’cs’ intersect select * from student where sage<=19

查询计算机科学系的学生与年龄不大于19岁的学生的差集

Select * from student where sdept=’cs’ except select * from student where sage<=19

 

4.数据更新

插入操作：

将一个新学生元组（学号:201215128,姓名：陈东，性别:男，所在系：IS，年龄：18岁）插入到student表中

Insert into student(sno,sname,ssex,sdept,sage) values(‘201215128’,’陈东’,’男’,’IS’,18)

将学生张成民的信息插入到student表中

Insert into student values(‘201215126’,’张成民’,’男’,18,’CS’)

插入一条选课记录(‘201215128’,’1’)

Insert into sc(sno,cno) values(‘201215128’,’1’)

对每一个系，求学生的平均成绩，并把结果存入数据库

create table dept_age( sdept char(15) avg_age smallint);

insert into dept_age(sdept,avg_age) selct sdept,avg(sage) from student group by sdept;

 

修改数据

将学生201215121的年龄修改为22岁

update student set sage=22 where sno=’201215121’

将所有学生的年龄都增加1岁

update student set sage=sage+1;

将计算机科学系全体学生的成绩置0

update sc set grade=0 where sno in (select sno from student where sdept=’cs’)

 

删除记录

删除学号为201215128的学生记录

delete from student where sno=’201215128’

删除所有学生的选课记录

delete from sc;

删除计算机科学系所有学生的选课记录

delete from sc where sno in(select sno from student where sdept=’cs’)

 

5.视图的建立、修改和删除

建立视图

建立信息系学生的视图

create view is_student as select sno,sname,sage from student where sdept=’is’

建立信息系学生的视图，并要求进行修改和删除操作时仍需保证该视图只有信息系的学生

create view is_student as select sno,sname,sage from student where sdept=’is’ with

 check_option

建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

create view is_si(sno,sname,grade) as select student.sno,sname,grade from student,sc where sdept=’is’ and student.sno=sc.sno and sc.cno=’1’

建立信息系选修了1号课程且成绩在90分以上的学生的视图

create view is_s2 as select sno,sname,grade from is_s1 where grade>90

将学生的学号及平均成绩定义为一个视图

create view s_g(sno,gavg) as select sno,avg(grade) from sc group by sno;

 

删除视图

删除视图BT_S和视图IS_S1

drop view bt_s

drop view is_s1 cascade