数据库系统概论复习

alittlemc
学习:数据库概论

发布于：2019年12月27日

题型

考试题目范围:

  1. 填空->10分
  2. 选择->20分
  3. 设计(ER图转关系模式)->10分
  4. 建表->10分
  5. 查表,更新表
  6. 视图
  7. 触发器
  8. 存储过程

文件命名:

  • 学号-姓名-班级.zip
    • .doc
    • .sql

知识点

1. 数据模型

  • 概念:数据模型就是对于现实世界数据特征的抽象(对于现实世界的模拟).

    • 第一类: 概念模型
    • 第二类: 逻辑模型(解释概念模型为)和物理模型(底层实现)

  • E-R图

    • 实体(处理的对象:人,物,事)->矩形
    • 属性(实体具有的某一特性)->

SQL语句

1. 查询

select [distinct] <列名1> [as 别名],<列名2>[as 别名] from <表名1> [as 别名],<表名2> [as 别名]
[where <条件表达式>] --这里不能用聚集函数
[group by <列名> [having <条件>]]
[order by <列名> [asc|desc]]

(1.)(自然连接查询) 求学生学号以及其选修课程的课程号和成绩。

use jwglxt
go
select student.sno,cno,score
from student,sc 
where student.sno=sc.sno;

(2.)(连接查询)求选修了课程01001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

use jwglxt
go
select student.sname,course.cname,sc.score 
from student,course,sc
where student.sno=sc.sno 
and course.cno=sc.cno
and sc.cno='1001'
and sc.score  not between 70 and 90;
--and (sc.score <=70 or sc.score>=90)

(3.)(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。

use jwglxt
go
select st.sname,s.cno,s.score
from student as st,sc as s
where st.sno=s.sno

(4.)(自身连接查询)求年龄大于 ’王红’ 的所有学生的学号、姓名和年龄。

use jwglxt
go
Select s.sno,s.sname,s.sage 
from student as f,student as s
where f.sname='王红'and s.sage>f.sage;

(5.)(外部连接查询)求选修了课程的学生的学号、课程号、课程名和成绩。

use jwglxt
go
select distinct course.cname, sc.*
from course right outer join sc on(course.cno=sc.cno);
  • 另一种解决方案
use jwglxt
go
select distinct s.sno,s.cno,cname,score
from Student ,Course,SC as s
where s.Sno=Student.Sno 
and s.cno=Course.cno

(6.)(子查询) 求与 ‘张维明’ 年龄相同的学生的姓名和出生年月。

use jwglxt
go
select sname,Sbir from student where Sage =
(select Sage from student where Sname='张维明') and sname <>'张维明';

(7.)(子查询)求选修了课程名为 ’ 计算机网络’ 的学生的学号和姓名。

use jwglxt
go
select sno,sname from student where sno in (select sno from sc 
where cno =(select cno from course where cname='计算机网络'));
  • in用于多返回值判断等于

(8.)(子查询any) 求比‘200801’班级中某一学生年龄大的学生的姓名和学号。

use jwglxt
go
select Sno,sname from student 
where sage >any(select sage from student where Clno='200801') and Clno <> '200801';
x < any(子查询)

(9.)求比‘200801’班级中全体学生年龄大的学生的姓名和学号。

use jwglxt
go
select Sno,sname from student 
where sage > all(select sage from student where Clno='200801') and Clno <> '200801';

(10.)(子查询exists)求选修了课程02002的学生的姓名和学号。

use jwglxt
go
select sno,sname from student 
where exists (select * from sc where sc.sno=student.sno and cno='02002');
  • exists(表达式)存在则输出
  • 另一种解决方案
use jwglxt
go
select distinct student.sno,sname 
from sc,student 
where sc.sno=student.sno and cno='2002';

(11.)(返回多列的子查询或集合查询) 求与 ‘李静’ 同班且同龄的学生的姓名和学号。

use jwglxt
go

-- 方法一:

select sno,sname from student

where sage= (select sage from student where sname='李静')

and clno= (select clno from student where sname='李静')

and sname <>'李静';

go

--方法二:

 select sno,sname from student

  where sage= (select sage from student where sname='李静')

  intersect 

select sno,sname from student

  where clno= (select clno from student where sname='李静') 

and sname <>'李静';

(12)(多个子查询或集合查询)求与 ‘王一夫’ 同班，且年龄大于 ‘赵良明’ 的学生的信息。

use jwglxt

go

--方法一:

select * from student

where Clno=(select Clno from student 

where sname='王一夫')

and sage>(select sage from student 

where sname='赵良明') and sname<>'王一夫';

go

--方法二: 

select * from student

  where Clno=(select Clno from student where sname='王一夫')

   intersect

 select *from student where sage>(select sage from student where sname='赵良明');

(13)(嵌套与分组查询)检索选修某课程的学生人数多于3人的这门课的课程名。

use jwglxt

go

select cname from Course where Cno in(

select cno 

from sc 

group by cno

having COUNT(*)>3);

(14)(集合查询)查询选修了课程01001或者选修了课程02002的学生学号。

use jwglxt

go

select sno from sc

where cno='01001'

union 

select sno from sc

where cno='02002'

(15)(相关子查询或集合查询)求选修了课程，但未选修课程02003的学生的姓名、学号。

use jwglxt

go

--方法一:

select sname,student.sno

from student

where sno in

(

  select distinct sc.sno

  from sc

  where sc.sno=student.sno

  and sc.sno not in

(

 select sc.sno

  from sc

  where cno='02003'

) 

)

 

go

--方法二: 

select sname,student.sno from

student where 

Sno in(

select distinct sno from sc

except

select sno from SC where Cno='02003');

(16)(相关子查询) 求选修了全部课程的学生的姓名。

use jwglxt

go

select sname from student 

where not exists

(select * from Course 

  where not exists 

​      (select * from SC 

where SC.Sno=student.Sno and SC.Cno=Course.cno));

(17)(相关子查询)求至少选修了学生 ‘20080102’ 所选修的全部课程的学生的学号，姓名。

use jwglxt

go

select distinct sno from 

sc scx where not exists

(select * from sc scy where scy.sno='20080102' 

 and not exists

​    (select * from sc scz 

where scz.sno=scx.sno and scz.cno=scy.cno)) and 

scx.sno<>'20080102';

(18)(相关子查询)求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。

use jwglxt

go

select * from sc x

where Score>(select avg(Score) from sc y

where y.sno=x.sno)

(19)(相关子查询或集合查询) 查询所有未选课程的学生学号、姓名。

use jwglxt
go
--方法一:
select sname,Sno from student where
 not exists(select * from sc where sno=student.sno )
go 
--方法二: 
select sname,sno from student where Sno in(
select distinct sno from student
except 
select distinct sno from sc );

2.视图

  • 删除
If exists (select name
            from   sysobjects
            where  name = '<视图名>' and type = 'v')
   drop view <视图名>
go
  • 视图把查询存起来
create view <视图名>
as
--
<这里一般写查询语句>
--
go
  • 调用视图
select * from <视图名>

信息表student上创建一个名为Stu_view，输出Sno、Clno、Sname，然后通过视图Stu_view查询Student表里的数据。

use jwglxt
go
create view Stu_view
as
select Sno,Clno,Sname
from student
go
select * from Stu_view

3. 触发器

  • 存在时删除触发器
If exists (select name
            from   sysobjects
            where  name = '<触发器名>' and type = 'tr')
   drop trigger <触发器名>
go

建立一个触发器，当向Student表中添加数据时，如果添加的学生的年龄不在12岁～60岁之间，则将禁止插入此学生。

create trigger Trigger_Insert
on Student
for insert
as

  -- 申明局部变量

  declare @Sage tinyint,

     @Sno varchar(8)

-- 检索插入的数据行的信息

  select  @Sage = i.Sage,@Sno=i.Sno

  from   Student Stu

      join inserted i on  Stu.Sno=i.Sno

  -- 现在检测禁止插入新数据行的规则，如果必要的话返回错误

  if @Sage not between 12 and 60

  begin

   -- 禁止插入新数据行时，必须先回滚

rollback tran

   raiserror ( '你输入的学生年龄不在（~60）范围之内：', 16, 10)

  end

go

2. 数据更新

  • 插入
insert into <表名> [<属性列1>,<属性列1>...]
values (<常量1>,<常量2>...)
  • 修改数据
update <表名>
set <列名>=<表达式>[,<列名>=<表达式>...]
[where <条件>]
  • 删除数据
delete
from <表名>
[where <条件>]

(1)(单行插入数据)将一新学生记录(学号：20090201；班级号：200901；姓名：陈冬；性别：男；出生年月：1991-2-26)插入学生表中。

insert into Student(Sno,Clno,Sname,Ssex,Sbir) 
values('20090201','200901','陈冬','男',1991-2-26);

存储过程

  • 存在删除
If exists(select name
      from sysobjects
 where  name='<存储过程名>'and type='p')
drop  procedure <存储过程名>
go
  • 存储过程
create  proc  <存储过程名>
[变量
 @<name> <type> [ = <val> [output] ];...
]
  as
--
<代码>
--
go
  • 调用
exec <存储过程名> [[@<name>]<val>,...]

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：https://alittlemc.github.io/Academic/SJK_SQL_Server/

更新于：2020年5月3日

数据库