【数据库系统概论】第三章关系数据库标准语言SQL_数据库s_t中创建学生表student、课程表course和选修表sc(参照实验3), 对学生关系s

选择题会考：

1.数据查询：

SELECT：用于选择需要查询的列和行。

FROM：用于指定要查询的表。

WHERE：用于指定查询条件。

GROUP BY：用于按照指定的列对结果进行分组。

HAVING：用于指定分组条件。

ORDER BY：用于指定查询结果的排序方式。

2.数据操纵：

INSERT INTO：用于将数据插入表中。

UPDATE：用于更新表中的数据。

DELETE FROM：用于删除表中的数据。

3.数据定义：

CREATE TABLE：用于创建新表。

ALTER TABLE：用于修改现有表的结构。

DROP TABLE：用于删除表。

4.数据控制：

GRANT：用于向用户或用户组授予特定的数据库权限。

REVOKE：用于撤销已授予的数据库权限。

3.1SQL概述

SQL语句特点：

3.3数据定义

建立三个表

1. 建立学生表Student

create table Student
(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
);

2. 建立一个课程表Course

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

3. 创建一个SC表

create table SC
(
Sno char(9),
Cno char(9),
Grade smallint,
primary key (Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
);

3.4数据查询

一般格式：select [all或distinct]<目标列表达式>[,<目标列表达式>]...

from<表名字或视图名>[,<表名或视图名...>]或(<select 语句>)[as]<别名>

[where<条件表达式>]

[group by<列名1>[having <条件表达式>]]

[order by<列名2>[asc或desc]];

例一：查询全体学生的学号与姓名

select Sno,Sname
from student;

例二：查询全体学生的详细记录

select *
from student;

例三：查询全体学生的姓名、出生年份、所在院系（小写）

select Sname,2023-Sage as Birthyear,
lower(Sdept)as Department 
from student;
/*注意列名的丢失*/

例四：查询考试成绩合格学生的成绩

select distinct Sno/*毕竟在SC表中 学号是重复出现的元素*/
from SC
where Grade>60;

例五：查询年龄在20~23岁之间学生的姓名，系名，年龄

select Sname,Sdept,Sage
from student
where Sage between 20 and 30;

例六：查询CS MA IS的学生姓名和学号

select Sname,Sage
from student 
where Sdept in('CS','MA','IS');
/*in 查找属性值属于指定集合的元组*/

例七：查询第二个名字为立的同学

select Sno
from Student
where Sname like '_立%';/*一个汉字这里是俩个斜杠*/
/*但是在sql server中一个汉字代表的是一个斜杠*/

例八：查询DB_Design课程的课程号和学分

select Cno,Ccredit
from Course
where Cname like 'DB\_Design' escape '\';
/*由于_是占位符 但是在这里_是具体符号，所以得用escape关键字*/

Order by子句

例九：查询全体学生情况，系号升序年龄降序

以上为错误示范

select *
from Student
order by Sdept , Sage desc;/*默认升序*/

聚集函数

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

select count(distinct Sno)
from sc;

例十一：查询选修1号课程的学生平均成绩

select avg(Grade)
from SC;
where Cno='1';

例十二：查询学生202102810224选修课程的总学分数

select sum(Ccreidt)
from SC,Course
where Sno='202102810224' and SC.Cno=Course.Cno;/*必须连接俩表 否则关系无法建立起来*/

group by 子句

往往表头元素和数字有关系的时候 就会用group by语句 比如：查询每个学生的选课门数，选修三门以上学生的名字等等

目的：细化聚集函数的作用对象，分组后，聚集函数将作用于每一个组，每一个组都有一个函数值

将表头元素和聚集函数值生成表建立联系

不可以用where 只可以用having

group by 后面加聚集函数之前的所有列表属性

having后面只可以加聚集函数

例一：查询各个课程号及相应的选课人数

select Cno,count(Sno)
from SC
group by Cno;

例二：查询每个学生的选课门数

select Sno,count(Cno)
from SC
group by Sno;

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

select Sno
from SC
group by Sno
having count(Cno)>3;

例四：查询选修课程中至多一门>70分的学生学号

SC的Sno投影-Sno的投影(选择Grade>70(SC)) 所有成绩都<=70的

并(union)上

有且仅有一门是70分的（和数量有关 得用到group by）

where后面不可以加聚集函数

/*所有成绩<=70的*/
select Sno 
from SC 
where Sno not in
	(select Sno from SC where Grade >70)
union 
/*有且仅有一门是70分的*/
select Sno
from SC
where Grade>70
group by Sno
having  count(Cno)=1 
/*对于课本的表 这个代码是没有数据的*/

为什么这段代码没有数据？？

分组之后 Cno已经没有=1的了

例五：查询平均成绩>=90分的学生学号和平均成绩

select Sno,avg(Grade)
from SC
group by Sno
having avg(Grade)>=90;

连接查询

例一：查询每个学生及其选修课程的情况

select Student.*,SC.*
from Student,SC
where SC.Sno=Student.Sno;

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

select Student.Sno,Sname
from Student,SC
where SC.Sno=Student.Sno and SC.Cno='2' and SC.Grade>90;

例三：查询每一门课的间接先修课

select C1.Cno,C2.Cpno
from Course as C1 ,Course as C2
where C1.Cpno=C2.Cno;

例四：查询每个学生的学号，姓名，选修的课程，成绩

select Sno,Sname,Cno,Grade
from SC,Course,Student
where Student.Sno=SC.Sno and SC.Cno=Course.Cno /*虽然是三表连接 但是用一个and就可以解决问题*/

例五：

注意，SC连接的时候用的是Sno，不是Cno

嵌套查询

在 SQL 语言中, 一个select-from-where 语句称为一个查询块,将一个查询块嵌套在另一个和查询块的 where 子句或者 having 短语的条件中的查询称为 嵌套查询

1. 无关子查询，先写子查询再写父查询
2. 能用in的绝对不要用=
3. 子查询前面一定是属性+in 不要只写in

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

select Sno,Sname,Sdept
from Student 
where Sdept in(select Sdept from Student where Sname='刘晨');

例二：查询既选修1号又选修2号课程的学生

select Sno
from Student
where Cno='1' and Sno in
(select Sno from Student where Cno='2');

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

select distinct Student.Sno,Sname
from SC,Student,Course
where SC.Sno=Student.Sno and Course.Cno=SC.Cno and Course.Cname='信息系统';

例四：查询成绩都大于70分学生的成绩

/*查询所选课程成绩全部大于70分的学生的学号*/
select distinct Sno
from SC 
where Grade not in(select Grade from SC where Grade<=70);

相关子查询

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

select Sno,Cno
from SC as X
where Grade>=(select avg(grade) from SC Y where Y.Sno=X.Sno);

// 1. 从外层查询中取出 SC 的一个元组 x, 将元组 x 的 Sno 值(201215121) 传送给内层查询

select avg(Grade)

form SC y

where y.Sno='201215121'

// 2. 执行内层查询,得到值 88(平均成绩的近似值), 用该值代替内层查询, 得到外层查询

select Sno,Cno

from SC x

where Grade>=88

// 3. 执行这个查询,得到

select Sno,Cno

from SC x

where Grade>=88;

然后从外层查询取出下一个元组重复上述 1 ~ 3 步骤的处理, 知道外层的 SC 元组全部处理完毕,结果为

// 学号 和 课程号

(201215121,1)

(201215121,3)

(201215122,2)

带有ANY和ALL谓词的子查询

例一：查询非计算机科学系某一个学生年龄小的学生姓名和年龄

select Sname,Sage
from student
where Sage<any(select Sage from Student where Sdept='CS')
and Sdept <>'CS';

例二：查询非计算机科学系中比计算机学科系所有学生年龄都小的学生姓名和年龄

select Sname,Sage
from Student
where Sage<all(select Sage from student where Sdept='CS') and Sdept <>'CS';

带有EXISTS谓词的子查询

例一：查询选修了1号课程的学生姓名

select Sname
from Student
where exists(select * from SC where SC.Sno=Student.Sno and Cno='1');

相关子查询的过程分析：

1.取外层查询中Student表的第一个元组，根据与内层查询相关的属性值处理内层查询

2.与SC.Sno判断条件，如果满足，则记录下来

3.取Student表的下一个元组，重复这一过程

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

select Sname
from Student
where not exists
  (select* from Course where not exists
   			(select* from SC where Sno=Student.Sno and Cno=Course.Cno));

理解：如果同时满足俩个条件，那么双重否定表肯定就是想要的结果

只要有一个结果不满足，那么就返回真，之后还有一个NOT EXISTS，就是假

例三：查询至少选修了20215122选秀的全部课程的学生号码

select distinct Sno
from SC X
where not exists
	(select * from SC Y where y.Sno='20215122' and not exists
       (select * from SC Z where Z.Sno=X.Sno and Z.Sno=Y.Cno));

集合查询

例一：查询计算机科学系的学生及年龄不大于19岁的学生

select *
from Student
where Sdept='CS'
union 
select *
from Student
where Sage<=19;

例二：查询选修1号课程与2号课程的学生

select Sno
from SC
where Cno='1'
intersect
select Sno
from SC
where Cno='2';

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

select *
from Student
where Sdept='CS'
except--除去的意思
select *
from Student
where Sage<=19;

基于派生表的查询

意义：可以将一些相关子查询改写为便于理解的非相关子查询

前情背景： 我们的子查询语句一般都是在where语句中，当然也可以出现在from语句中，这时候可以利用子查询生成的临时派生表成为主查询的查询对象

例一：找出每个学生超过自己选修课程平均成绩的

select Sno,Cno
from SC x
where Grade>=(select avg(Grade) from SC y where y.Sno=x.Sno);

改写：

select Sno,Cno
from SC,(select Sno,avg(Grade) from SC group by Sno) as avgsc(avgsno,avggrade);
where SC.Sno=avgsc.avgsno and SC.Cno>avgsc.avggrade;

注：如果子查询没有聚集函数，派生类可以不指定属性列

例二：查询所有选修1号课程的学生名字

select Sname
from Student,(select Sno from SC where Cno='1') as SC1
where Student.Sno=SC1.Sno;

3.5数据更新

1.插入数据

insert

into 表名 （表头名1，表头名2...）

values （常量1，常量2）

注意：如果into中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值

插入元组

• INTO子句中没有出现的属性列，新元组在这些列上将会取NULL
• 若INTO子句中没有指明任何属性列名，则新插入的元祖必须在每个属性列上均有值

例一：将一个新学生元组(学号:201215128，姓名:陈冬，性别:男，所在系:年龄:18岁)插入到Student 表中

INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES ('201215128 陈冬，男，IS18);

• 注意顺序可以和表不一致（因为student后面的属性已经完全指出了）

例二：插入学生张三

INSERT
INTO student
VALUES('201215126','张成民','男',18,'CS');

• 注意由于没有指定顺序，所以按照必须按照表中属性列的顺序插入，否则会导致插入错误

插入子查询

例三：对每一个系，求学生的平均年龄，并把结果存入数据库

CREATE TABLE dept_age(
	Sdept char(15),
	Age SMALLINT
);
 
INSERT 
INTO dept_age(Sdept,Age)
SELECT Sdept,AVG(Sage)
FROM student
GROUP BY Sdept;

2.修改数据（update）

例四：将所有学生的年龄增加1岁

UPDATE student
set Sage=Sage+1;

例五：将CS所有学生成绩为0

UPDATE sc
SET Grade=0
WHERE Sno IN
(
	SELECT Sno FROM student WHERE Sdept='CS'
);

3.删除数据（delete）

• DELETE删除的是表的数据，而不是表的定义
• 如果省略WHERE子句，那么就表示删除全部元组

例六：删除计算机科学系所有学生选课记录

DELETE FROM sc
WHERE Sno IN
	(SELECT Sno FROM student WHERE Sdept='CS');

3.6触发器

插入：inserted表中有，deleted无

删除：deleted有，inserted无

CREATE TRIGGER trigger_name 
ON table_name
INSTEAD OF DELETE--前触发器
AS
BEGIN
-- 触发器内容
END

例一：当在学生表S中删除某一个学生时，在学生选课表SC中的选课记录也全部被删除。

例二：当对表sc的grade进行修改时，若分数增加了10%，则将此次操作记录到另一个表中sc_u（sno,cno,oldgrade,newgrade）

--则将此次操作记录到另一个表中sc_u（sno,cno,oldgrade,newgrade）

create trigger sb
on sc
after update 
as
begin
 set nocount on;
  if (1.1<=(select inserted.grade/deleted.grade
            from inserted,deleted
   where  inserted.sno=deleted.sno and inserted.cno=deleted.cno))
 begin 
insert into sc_u
select deleted.* ,inserted.grade
from inserted,deleted
where inserted.sno=deleted.sno and inserted.cno=deleted.cno
 end
end

例三：创建触发器,该触发器能够保证在学生选课表SC表中添加新的记录时，学生的学号SNO必须已经存在于学生基本信息表S中

例三：如果学生成绩小于60，改为60

create TRIGGER scl_grade
on SC 
instead of INSERT
as
BEGIN
SET NOCOUNT ON;
if 60> (select grade--50 --70 
        from  inserted)
 
begin
insert into SC
select Sno,Cno,60
from inserted
end
 
if 60<= (select grade from  inserted)
begin
insert into SC
select Sno,Cno,inserted.Grade
from inserted
end
 
 
END;
 
insert into SC  values('001','10',50);

ALTER TABLE Orders

ADD CONSTRAINT FK_CustomerOrder

FOREIGN KEY (CustomerID)

REFERENCES Customers(CustomerID)

ON DELETE CASCADE;

3.7视图（见课本121页吧 实在写不动了）

写实验报告遇到的错误点

1.插入错误

表和into的位置写反了

2.查询选修了全部课程的学生的学号和姓名

第二个表是course 写成了SC 因为要的是全部课程 所以就得从Course中选择

3.删除C1的记录

更改：

4.创建视图