引语

备考两天不到的数据库考试终于落下了帷幕，备考期间系统的回顾复习了所学的知识，现归纳如下（其中有一些谬误之处，因为我懒的一个一个找出来，太浪费时间，就不修改了）；

高超老师的课堂教案

数据库的组成

1)数据类文件：管理数据

1、主数据文件有且唯一 .mdf

2、辅助数据文件可以有，可以没有，可以有多个 .ndf

2)日志类文件：备份数据至少一个 .ldf

每一个文件有五个属性

1 name:逻辑名称,字符型数据 name=’aaaa111’

标准化命名：数据库名字+_+data(log)+序号（类型）

2 filename:路径

标准化命名：盘符（例如d:\）+逻辑名称+文件类型名

3 size:文件初始大小 size=5mb,

4 maxsize:最大大小 maxsize=20mb或maxsize=unlimited

5 filegrowth：文件增长方式filegrowth=5%或filegrowth=2mb

作业1

数据库叫xslg假设他由3个文件组成1个主数据1个辅助文件1个日志文件create database xslg

create database xslg
on primary
(name='xslg_dat1',
filename='d:\xslg_dat1.mdf',
size=5mb,
maxsize=50mb,
filegrowth=10%),
(name='xslg_dat2',
filename='d:\xslg_dat2.ndf',
size=5mb,
maxsize=50mb,
filegrowth=10%)
log on
(name='xslg_log1',
filename='d:\xslg_log1.ldf',
size=2mb,
maxsize=20mb,
filegrowth=1mb)
go

drop database test1

alter database xslg
 add file
 (name='xslg_dat3',
filename='d:\xslg_dat3.ndf',
size=5mb,
maxsize=50mb,
filegrowth=10%)

 alter database xslg
 add log file
 (name='xslg_log2',
filename='d:\xslg_log2.ldf',
size=2mb,
maxsize=20mb,
filegrowth=1mb)

alter database xslg
remove file xslg_dat3

alter database xslg
remove file xslg_log2

数据库是容器

数据可不可以直接放入容器？不可以

加入对象概念

一、定义基本表结构

CREATE TABLE <表名>

（<列名> <数据类型>[ <列级完整性约束条件> ]

[，<列名> <数据类型>[ <列级完整性约束条件>] ] …

[，<表级完整性约束条件> ] ）；

列级完整性约束条件：只依赖于一个属性 Sno CHAR(9) PRIMARY KEY，

表级完整性约束条件：依赖于多个属性 PRIMARY KEY (Sno，Cno)，

1定义表名字，属性名称，数据类型，（数据长度）

CHAR(N)：适用于上下限区别不大的字符数据学号，身份证号，姓名

VARCHAR(N)：适用于上下限区别较大的字符数据姓名

......

2定义合理的约束 constraint

Primary key=unique+ not null 主码约束

Unique 唯一约束

tno char(6) primary key,

pno char(6) unique,

Not null 非空约束

Foreign key 外码约束

1表间外码FOREIGN KEY (Sno) REFERENCES Student(Sno)，

2 表内外码 FOREIGN KEY (Cpno) REFERENCES Course(Cno)

Check约束*

课间作业;写student,course,sc表结构代码

修改表结构举例

alter table student

add sbirthday date

* 不要加not null 约束

alter table student

drop column sbirthday



alter table student

alter column sage int



alter table student

alter column sname char(30)  改大不改小



drop table student 删除表结构 慎用

约束对数据输入的限制

1 主码约束

2 外码约束*（重难点）先加主表（码），先删从表（码）

3 insert语句的使用

insert into Student
values('201230402101','张三','男',20,'计算机')

insert into Student
values('201230402102','张五','男',21,'艺术系'),('201230402103','刘丽','女',20,'艺术系')

单表查询

查询的结果和条件都在同一表中

Select :输出列或属性值

1 指定列 select sno,sname

select sno,sname,sage

from Student

2 全部列

select *

from Student

3* SELECT Sname,出生年份=2020-Sage   计算列

FROM Student

等价为

4* Select sname,2020-Sage as  ‘出生年份’  换名称

FROM Student

5 指定显示多少元组

Distinct  top n  top n percent

6 带where 限制条件的查询

pre class="code">SELECT Sname,Sdept,sage

FROM Student

WHERE Sage<21

确定范围:要求有明显的上限和下限

年龄在20岁和21岁之间

select *

from Student

where Sage between 20 and 21

确定集合:没有明显的上限和下限

select *

from Student

where Sdept in ('艺术系','机械制造')

*空值不是值，是一种状态

匹配串为含通配符的字符串

1 %：匹配任意个（可以为0）任意字符 *适合大范围匹配

模糊值：姓张的同学

select *

from Student

where Sname like '张%'

:一个匹配一个任意字符* 用于准确定位

使用换码字符将通配符转义为普通字符

取消匹配符定义，使其恢复为普通字符*

假设有一个同学就叫张%

换码字符: \ # *

SELECT Cno，Ccredit

FROM Course

WHERE Cname LIKE 'DB\_Design' ESCAPE '\‘

ESCAPE '\‘:只取消\后面第一个字符的匹配功能

Order by 排序

select *

from Student

order by sage desc,Sno desc

聚集函数：对原始列（属性）进行简单的数理统计

select COUNT(distinct sdept) as 'xxx'

from Student



select max(grade) as '最大成绩',min(grade) as '最小成绩'

from SC

*聚集函数在使用时推荐和换名输出合用

COUNT(*) ：统计表中元组的数量

select COUNT(*) as '课程总门数'

from sc

Group by 分组，常见的按性别，所在系分组且和select语句原始列保持一致

select ssex

from student

group by ssex

select ssex,sname

from Student

group by ssex,sname

select COUNT(*) as '课程总门数'

from sc

只有聚集函数时，可以没有group by语句

*3高级用法：当select语句中既有普通原始列又有聚集函数时，必须和group by合用。其中group by后接普通列

同时聚集函数的含义会发生变化：

既有普通列又有聚集函数时：必须有group by且聚集函数的含义会发生变化

select sno,COUNT(*) as '每位学生选课的门数'

from sc

group by sno

select cno,COUNT(*) as '每门课程所选的人数'

from sc

group by cno

HAVING筛选短语:对聚集函数做进一步筛选

Where语句：对原始列进行筛选

select sno,COUNT(*) as '每位学生选课的门数'

from sc

group by sno

Having COUNT(*)>2  / 查询选修课程数大于2门同学的学号

连接查询：查询的结果或条件同时涉及多个表的查询

连接条件或连接谓词：用来连接两个表的条件

连接谓词法用where(自然连接=要求有公共属性,要求属性输出不重复的等值连接)适用于初学者*

表1.公共属性=表2.公共属性

Where student.sno=sc.sno

查询选修了3号课程的同学的学号和姓名

select student.sno,sname

from SC,student

where student.sno=sc.sno and Cno='3'

查询选修了3号课程的同学的成绩和课程名称

select grade,cname

from sc,course

where sc.cno=course.cno and course.cno='3'

查询选修了数据结构课程的同学学号和姓名

select student.sno,sname

from SC,student,course

Where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Cname='数据结构'

2 内连接：表连接条件有on语句(一个ON语句负责一个条件)负责;from中表和表连接用 join

缺点：讲语法

from中中间表sc要在中间*

B.注意嵌套结构* 先内后外

查询选修了3号课程的同学的学号和姓名

select student.sno,sname

from SC join student

On student.sno=sc.sno

where Cno='3'

查询选修了数据结构课程的同学学号和姓名(要求用内连接完成)

select student.sno,sname

from  student join SC join course

on Course.Cno=SC.cno

On student.sno=sc.sno

where Cname='数据结构'

自身连接：一个表与其自己进行连接

1 需要给表起别名以示区别

由于所有属性名都是同名属性，因此必须使用别名前缀

1 求先修课的先修课

select a.cno,b.cpno,a.Cname,b.cname

from Course a,Course b

where a.Cpno=b.cno

2 查询和张三在同一个系的同学学号和性别

select b.sname,b.ssex

from student a,student b

where a.sdept=b.sdept and a.sname='张三' and b.sname!='张三'

作业：查询和张三不在同一个系的同学学号和性别

外连接与普通连接的区别

普通连接操作只输出满足连接条件的元组

外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出，但只能两个表进行连接*

1 内连接

select *

from Student join SC

on Student.Sno=SC.sno

2 左外，右外，完全

1 select *

from Student left join SC

on Student.Sno=SC.sno

2 select *

from Student right join SC

on Student.Sno=SC.sno

3 交叉连接，没有连接条件(只有两行)

select *

from Student cross join SC

嵌套查询概述（拆分*）

一个SELECT-FROM-WHERE语句称为一个查询块

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

1 用in 或=做查询的连接*

Where 公共列 in (select 公共列

In 可以无限替代=，反之则不成立

子查询的结果有且唯一， in可以换成=，反之则不成立

1 选修了2号课程同学姓名

select sname

from student

where sno  in

(select sno

from sc

where cno='2')

2 选修了数据库课程同学姓名

select sname

from student

where sno  in

(select sno

from sc

where cno in

(select cno

from Course

where Cname='数据库'))

3 没有选修数据库课程同学姓名

select sname

from student

where sno not in

(select sno

from sc

where cno  in

(select cno

from Course

where Cname='数据库'))

4 至少选修了一门不是数据库课程的同学

select sname

from student

where sno in

(select sno

from sc

where cno not in

(select cno

from Course

where Cname='数据库'))

带有ANY或ALL谓词的比较类子查询

选修1号课程且比选修3号课程最低分成绩要高的同学学号和姓名

select Student.Sno,sname

from SC join student

on Student.Sno=SC.sno

where Cno='1' and Grade>any

(select grade

from SC

where Cno='3')

查询比计算系年龄都大的艺术系同学的学号和姓名

select sno ,sname

from student

where sdept = '艺术系' and sage >all

(

select sage

from student

where sdept  = '计算机')

EXISTS谓词：行（元组）的连接存在量词

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

若内层查询结果非空，则外层的WHERE子句返回真值

若内层查询结果为空，则外层的WHERE子句返回假值

由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

NOT EXISTS谓词

若内层查询结果非空，则外层的WHERE子句返回假值

若内层查询结果为空，则外层的WHERE子句返回真值

Exists 连接：1不找公共列,2在最下层子查询块中写表之间的连接条件;3中间表要放最下层子查询块中

Where (not) exists ( select *

1选修了1号课程的同学学号和姓名

select sno,sname

from Student

where exists

(select *

from SC

where SC.Sno=Student.Sno and Cno='1')

2选修了数据库课程的同学学号和姓名

select sno,sname

from Student

where  exists

(select *

from Course

where exists

(select *

from SC

where SC.Sno=Student.Sno and SC.Cno=Course.Cno and  Cname='数据库'))

3 和张三同学在同一个系的同学学号和性别

select sname,ssex

from student a

where exists

(select *

from Student b

where a.sdept=b.sdept and b.sname='张三' and a.sname!='张三')

4 选修了全部课程的同学学号和姓名

select sno,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 ))

或

select student.sno,student.sname ,count(cno) as"选修课程总数"

from student  join  sc

on sc.sno=student.sno

group by student.sname,student.sno

having count(cno)=(select count(distinct cno)

from course)

其中红颜色代码是求一共有几门课，在老师环境下就等于7，having语句做进一步筛选，必须选满7门课即全部课程

索引

建立索引的目的，加快查询速度
谁可以建立索引
- DBA或表的属主（即建立表的人）
- DBMS一般会自动建立以下列上的索引
  - Primary key
  - UNIQUE
谁维护索引
- DBMS自动完成
使用索引
- DBMS自动选择是否使用索引以及使用哪些索引

语句格式

CREATE [UNIQUE] [CLUSTER] [NONCLUSTER]INDEX <索引名>

ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；

CLUSTER INDEX 一个表中有且唯一；必须现建立聚集后建立非聚集；一般在主码上

nonCLUSTER INDEX 一个表里可以建立多个

[例14]为学生-课程数据库中的Student，Course，SC三个表建立唯一索引。

CREATE UNIQUE INDEX  Stusno ON Student(Sno)；

CREATE UNIQUE INDEX  Coucno ON Course(Cno)；

CREATE UNIQUE INDEX  SCno ON SC(Sno ASC，Cno DESC)；

Student表按学号升序建唯一索引

Course表按课程号升序建唯一索引

SC表按学号升序和课程号降序建唯一索引

视图view的特点

虚表，是从一个或几个基本表（或视图）导出的表

只存放视图的定义，不存放视图对应的数据

基表中的数据发生变化，从视图中查询出的数据自动也随之改变。反之，若视图数据发生变化且该视图是可更新视图，则其来源的基本表也会自动变动。如果是不可更新视图，则其来源的基本表不会自动变动，但可通过触发器技术来实现变动。

语句格式

CREATE VIEW

<视图名> [(<列名> [，<列名>]…)]

AS <子查询>

[WITH CHECK OPTION]；

组成视图的属性列名：全部省略或全部指定

子查询不允许含有ORDER BY子句和DISTINCT短语

[例1] 建立计算机学生的视图。

CREATE VIEW CS_Student

AS

SELECT *

FROM    Student

WHERE  Sdept= '计算机'

[例2]建立计算机学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

CREATE VIEW CS2_Student

AS

SELECT *

FROM    Student

WHERE  Sdept= '计算机'

with check option  //  二次检查

例1例2区别在于是否二次检查

1 先往基本表内加入一个计算机系同学，看基本表和两个视图内是否有他的数据

insert into  student

values('201230402120','刘芬','女',18,'计算机')

select *

from CS_Student

select *

from CS2_Student

select *

from Student

结果为:三者完全一样，都有刘芬

2再往基本表内加入一个非计算机系同学，看基本表和两个视图内是否有他的数据

 

insert into  student

values('201230402120','姜小丽','女',18,'艺术')

select *

from CS_Student 没有姜小丽

select *

from CS2_Student 也没有姜小丽

select *

from Student  有姜小丽

结果

3 分别往两个视图内添加非计算机学生

insert into  cs_student

values('201230402122','高新华','男',18,'艺术')

结果：成功

insert into  cs2_student

values('201230402123','谢峰','男',18,'艺术')

结果：失败

分析：加了二次检查的视图必须满足WHERE语句的限制条件

存储过程的增删改查（注意形式参数和EXEC调用）

1 查

create proc select_student @sno char(12)

as

select *

from Student

where Sno=@sno

exec select_student @sno='201230402101'

2 增

create proc insert_sc @sno char(12),@cno char(4),@grade smallint

as

insert into SC

values(@sno,@cno,@grade)




exec insert_sc @sno='201230402101',@cno='7',@grade=100

3 删

create proc delete_sc @sno char(12)

as

delete from sc

where Sno=@sno

exec delete_sc @sno='201230402109'

4 改

create proc update_sc @sno char(12)

as

update  sc

set

Grade=Grade+10

where Sno=@sno

exec update_sc @sno='201230402108'

触发器（没有形参没有EXEC调用，满足条件自动触发）

独有临时缓存表，只在触发器中其作用

1 deleted 执行删除操作或修改前值保存在该表

2 inserted 执行插入操作或修改后值保存在该表

例1：当在SC表中删除某个同学数据时，自动删除他在STUDENT表中数据

删

create trigger sc_delete

on sc after delete

as

declare @sno char(12)

select @sno=sno from deleted

delete from student where sno=@sno

delete from sc

where sno='201230402107'

红色触发将删除的数据送入 deleted 表中，select @sno=sno fromdeleted语句再从deleted 表获取@sno的值

通用格式 select @xxx=xxx from deleted(inserted)

最后delete from student where sno=@sno自动完成操作

2 修改sc中学号时，自动修改Student表相应数据

改

注意改比删复杂，因为如果将03学号改为93 那就有两个值 03是改前属于deleted 表，93是改后属于

Inserted

创建一个合适的instead of触发器，实现在一个不可更新视图中插入数据时，所依赖的多个基本表会自动更新。

create view student_sc

as

select student.sno,sname,grade

from student,sc

where student.sno=sc.sno

insert into student_sc

values('200215168','张三阿',90)

create  trigger alter_student

on student_sc instead of insert

as

declare @sno char(10),@sname char(8),@grade smallint

select @sno=sno, @sname=sname,@grade=grade from inserted

insert into student values(@sno,@sname,23,'男','cs','2002-8-9')

insert into sc values(@sno,'1',@grade)

第一次数据库工程实践考试题目与代码

--创建数据库

create database Company

on primary

(

name='Company_Data1',

filename='d:\Company_Data1.mdf',

size=5mb,

maxsize=50mb,

filegrowth=20%

),

(

name='Company_Data2',

filename='d:\Company_Data2.ndf',

size=5mb,

maxsize=50mb,

filegrowth=20%

),

filegroup Company_Group

(

name='Company_Data3',

filename='d:\Company_Data3.ndf',

size=5mb,

maxsize=50mb,

filegrowth=20%

)

log on

(

name='Company_Log',

filename='d:\Company_Log.ldf',

size=5mb,

maxsize=50mb,

filegrowth=20%

)

--创建数据库

create database University

on primary

(

name='University_Data',

filename='d:\University_Data.mdf',

size=5mb,

maxsize=50mb,

filegrowth=20%

)

log on

(

name='University_Log',

filename='d:\University_Log.ldf',

size=5mb,

maxsize=50mb,

filegrowth=20%

)




--使用数据库

use readbook_01







alter database readbook_01

add file

(

name='readbook_01_data03',

filename='d:\readbook_01_data03.ndf',

size=5mb,

maxsize=50mb,

filegrowth=20%

)

--删除数据库

drop database ZZQ

--增加辅数据文件

alter database readbook_01

add file

(

name='readbook_01_data4',

filename='d:\readbook_01_data4.ndf',

size=5mb,

maxsize=50mb,

filegrowth=20%

)

--添加日志文件

alter database readbook_01

add log file

(

name='readbook_01_log2',

filename='d:\readbook_01_log2.ldf',

size=5mb,

maxsize=50mb,

filegrowth=20%

)

--删除辅数据文件

alter database readbook_01

remove file readbook_01_data03

--删除日志文件

alter database ZZQ

remove file ZZQ_log3

--使用数据库

use University

--创建表

--添加唯一约束

create table Students

(

Sno char(3) primary key,

Sname char(8) not null unique,

Ssex nvarchar(4) ,

Sage int,

Sdept nvarchar(16) 

)




create table Courses

(

Cno char(3) primary key,

Cname char(8) not null unique,

Pre_Cno char(3) ,

Credits int 

)




create table Reports

(

Sno char(3) foreign key references Students(Sno),

Cno char(3) foreign key references Courses(Cno),

Grade int,

primary key(Sno,Cno)

)




create table Reader

(

ReaderId char(4) primary key,

firm char(10) not null,

ReaderName char(6) ,

gender char(2),

grade char(6),

ReaderAddress char(16) 

)




create table Borrow

(

ReaderId char(4) foreign key references Reader(ReaderId),

BookId char(6) foreign key references Book(BookId),

BorrowDate DATETIME ,

primary key(ReaderId,BookId)

)

--删除表

drop table Borrow




drop table Students

--给表增加一个属性

alter table Reader

add ReaderBirthday datetime




alter table Students

add Sentrancedate datetime

--修改表中的一个属性

alter table Book

alter column publicfirm char(20)




alter table Students

alter column Sage smallint

--删除表中的一个属性

alter table Reader

drop column ReaderBirthday




alter table Students 

drop column  Sentrancedate

--增加一个约束

pre class="code">alter table Reader

add constraint DF_Reader_Gender DEFAULT('女')

for gender

alter table Book

add constraint CK_Book_price CHECK(price>0 and price<2000)

alter table Borrow

add constraint FK_Borrow_ReaderId

foreign key(ReaderId) references Reader(ReaderId)

alter table Reader

add constraint UQ_Rader_ReaderName UNIQUE(ReaderName)

alter table Book

drop constraint CK_Book_price

alter table Book

add constraint CK_Book_price CHECK(price>0 and price<3000)

--删除一个约束

alter table Student

drop constraint CK_Student_Ssex




alter table Students 

drop constraint UQ__Students__52723D27D6F5CFB8

--增加数据

insert into Book 

values('080001','C语言程序设计','谭浩强','清华大学',36)




insert into Book 

values('080002','数据结构','严蔚敏','清华大学',40)




insert into Book 

values('080003','Photoshop设计','李佳','合肥工业大学',30)




go 

use University

insert into Students 

values('S01','王建平','男',21,'计算机')




insert into Students 

values('S02','刘华','女',19,'自动化')




insert into Students 

values('S03','范林军','女',18,'计算机')




insert into Students 

values('S04','李伟','男',19,'数学')




insert into Students 

values('S05','黄河','男',18,'数学')




insert into Students 

values('S06','长江','男',20,'数学')




insert into Courses

values('C01','英语','',4)




insert into Courses

values('C02','数据结构','C05',2)




insert into Courses

values('C03','数据库','C02',2)




insert into Courses

values('C04','DB_设计','C03',3)




insert into Courses

values('C05','C++','',3)




insert into Courses

values('C06','网络原理','C07',3)




insert into Courses

values('C07','操作系统','C05',3)




insert into Reports(Sno,Cno)

values('S01','C01')













insert into Reader 

values('0001','信息工程','Lucy','女','副教授','东区')







insert into Reader 

values('0002','信息工程','Lily','女','讲师','西区')







insert into Reader 

values('0003','建筑工程','Jim','男','副教授','北区')
















insert into Borrow 

values('0001','080001','2008-10-10')







insert into Borrow 

values('0001','080002','2008-10-20')










insert into Borrow 

values('0002','080003','2008-10-30')

--查询数据

select * from Student

--更改数据

update Student 

set sno=0001,Sname='zzq',Sage=Sage+1

--删除表

drop table SC,Course,Student

--统计全体学生中的男同学的人数，女同学的人数，以及男女比(若只有男生或只有女生则显示0)

select '男同学人数'=COUNT(*),

'女同学人数'=(select count(*) from Student)-count(*),

'男女比'=

case

when  (select count(*) from Student)-count(*)=0 then 0

else COUNT(*)*1.0/((select count(*) from Student)-count(*))

end

from Student

where Ssex='1'

--统计每个学生的总分并按降序排序显示前3名

select top 3 

stu.Sno,

'姓名'=max(stu.Sname),

'总分'=sum(sc.Grade)

from Student as stu

join SC  as sc

on stu.Sno=sc.Sno

group by stu.Sno

order by '总分' desc

--查询所有选修了hh课程的同学姓名与其成绩分数

select stu.Sname,

SC.Grade

from Student as stu

join SC as sc

on stu.Sno= sc.Sno

join Course as c

on c.Cno = sc.Cno

where c.Cname='hh'

--请查询总分大于150分的学生

select '姓名'=max(stu.Sname),

'学号'=stu.Sno,

'总分'=sum(sc.Grade)

from Student as stu

join SC as sc

on Stu.Sno=SC.Sno

group by Stu.Sno

having sum(sc.Grade)>150

--查询姓名中带有'k'的学生的姓名与总分

select '姓名'=max(stu.Sname),

 '总分'=sum(sc.Grade)

from Student as stu

join SC as sc

on stu.Sno=sc.Sno

where stu.Sname

like '%k%'

group by stu.Sno

--统计每个学生jj课程的成绩，列出学生姓名，成绩，级别（>90 优秀,>60 及格 ,否则不及格）并按成绩降序排序

select stu.Sname,

sc.Grade,

'级别' = case

when sc.Grade>90 then '优秀'

when sc.Grade>60 then '及格'

else '不及格'

end

from Student as stu

join SC as sc

on stu.Sno=sc.Sno

join Course as c

on c.Cno=sc.Cno

where c.cname='jj'

order by sc.Grade desc

use ZZQ

select  *

from Reader

where  gender='女'

select *

from Reader

where ReaderName 

like '%l%'

select  * 

from Book

order by price desc

select top 10 percent *

from Book

order by  BookId desc

select '男生人数'=count(*)

from  Reader

where gender='男'

select * 

from Student

--创建视图

go

create view my_view

as 

select *

from Student

where Sname like '小_'

go

--使用视图

select * 

from my_view

--打开一个事务(no)

begin tran

declare @sum int=0

select *

from Student

where Sname like '小_'

set @sum=@sum+@@ERROR

if @sum>0

begin

rollback

end

else 

begin

commit

end

--创建一个简单存储过程

create proc usp_say_hello

as 

begin 

print 'HELLO WORLD'

end

--使用存储过程

exec usp_say_hello

--创建一个带参数的存储过程

create proc usp_add_number

@n1 int,

@n2 int

as 

begin

select @n1+@n2

end

--使用带参数的存储过程

exec usp_add_number 

@n1=2,@n2=3

--创建一个带输出参数的存储过程

create proc sup_add2_nubmer

@n1 int output,

@n2 int 

as 

begin




end







go

1-- 查询全体学生的详细记录。

select *

from Students

2-- 查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。

use University

select Sname,Sno,Sdept

from students

3-- 查询选修了课程的学生学号。

select Sno

from Reports

4-- 查询数学系全体学生的学号(Sno)和姓名 (Sname)。

select Sno ,Sname

from students

where Sdept='数学'

5-- 查询所有年龄在19~22岁(包括19岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。

select Sname,Sage

from students

where sage

between 19 and 22

6-- 查询年龄不在19-22岁之间的学生姓名(Sname)及年龄(Sage)。

select Sname,Sage

from students

where sage

not between 19 and 22

7-- 查询自动化系和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。

select Sno,Sname,Ssex

from students

where Sdept='自动化' 

or Sdept='计算机'

8-- 查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。

select *

from students

where Sname like '刘%'

9-- 查询课程名为“DB_设计”的课程号(Cno)和学分(Credits)。

select Cno,Credits

from Courses

where Cname ='DB_设计'

10-- 查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。

select *

from Courses

where Cname like 'DB&_%设_'  escape '&'

11-- 假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。

select Sno,Cno

from 选课表

except

select Sno,Cno

from 成绩表

12-- 查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。

use University




select *

from students

order by Sdept asc,Sage  desc

13-- 求学生的总人数

select count(*) 学生总人数

from students

14-- 求选修了课程的学生人数

select COUNT(*)

from Reports

group by sno




select count(distinct sno)

from Reports

15-- 求课程和选修了该课程的学生人数

select cno,count(*)

from Reports

group by cno

16-- 求选修超过2门课的学生学号

use xslg




select *

from student 

where sno in(

select sno 

from sc

group by sno

having count(*)>1

)

--17 查询计算机科学系的学生及年龄不大于19岁的学生

select *

from students

where Sage<=19

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

select *

from students

where Sdept='计算机'

intersect 

select * 

from students

where Sage<=19

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

select *

from students

where Sdept='计算机'

except

select * 

from students

where Sage<=19

--20 查询选修课程‘C01’的学生集合与选修课程‘C02’的学生集合的交集

select *

from Reports

where Cno ='C01'

intersect

select *

from Reports

where Cno='C02'

-- 21查询选修课程‘C01’的学生集合与选修课程‘C02’的学生集合的差集

select *

from Reports

where Cno ='C01'

except

select *

from Reports

where Cno='C02'

--1 求学生号以及其选修课程的课程号和成绩，但查询结果中只能有一个SNO字段。

--2 求选修了课程”c01”且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

--3 求选修了课程的学生的学生姓名、课程号和成绩。

--4 求年龄大于 ’李伟’ 的所有学生的姓名、系和年龄。

--5 求选修了课程’C02’或’C03’的学生的学号、课程号、课程名和成绩。

--6 求与 ‘李伟’ 年龄相同的学生的姓名和系。

--7 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。

--8 求比数学系中某一学生年龄大的学生的姓名和系。

--9 求比数学系中全体学生年龄大的学生的姓名和系。

--10 求选修了课程C04的学生的姓名和系。

--11 求与 ‘黄河’ 同系且同龄的学生的姓名和系。

--12 求与 ‘黄河’ 同系，且年龄大于 ‘黄河’ 的学生的信息。

--13 求数学系中年龄相同的学生的姓名和年龄。

--14 检索至少选修所有授课程中一门课程的男学生姓名。

--15 检索选修某课程的学生人数多于2人的课程名称。

--16 查询被一个以上的学生选修的课程号。

--17 求未选修课程’C04’的学生的姓名。

--18 求选修了全部课程的学生的姓名。

--19 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。

--20 查询所有未选课程的学生姓名和所在系。

--查询SC表汇总至少有2名学生选修的并以‘数’开头的课程的平均分数

select 

avg(sc.grade),sc.cno,min(cname)

from course join sc

on course.cno=sc.cno

where left(cname,1) like '数%'

group by sc.cno

having count(*)>1

第二次数据库工程实践考试题目与代码

use xs
--（4）查询Score表中成绩在60到80之间的所有记录。
select *
from Score
where Degree between 60 and 80

--（5）查询Score表中成绩为85，86或88的记录。
select *
from Score
where Degree in (85,86,88)

--（6）查询Student表中“95031”班或性别为“女”的同学记录。
select *
from Student 
where Class='2'
and Ssex='女'
--（7）查询Score表同时选修了1号课程和2号课程的同学学号 。
select Sno
from Score 
where Cno='002' 
and Sno in
(
select Sno
from Score
where Cno='003'
)

--（8）以Cno升序、Degree降序查询Score表的所有记录。

select *
from Score
order by Cno asc,Degree desc

（9）查询“95031”班的学生人数。
select Class,count(*) 班级人数
from Student
where Class='2'
group by Class

--（10）查询Score表中成绩大于平均成绩的学生学号和课程号。（要求排序显示）

select Sno,Cno
from Score a
where exists
(select *
from Score b
where a.Cno=b.Cno
group by Cno
having a.Degree>avg(Degree)
)
order by Sno

--（11）查询学生选课情况，没有选课的同学也要显示出来。 
select *
from Student 
left join Score
on Student.Sno=Score.Sno

--（12）查询Score表中至少有2名学生选修的并以3开头的课程的平均分数。
select avg(Degree)
from Score 
where Cno='003'
and Cno
in(
select Cno
from Score 
group by Cno
having count(*)>1
)

--（13）查询所有学生的Sname、Cno和Degree列。（谓词连接）
select Student.Sname,Score.Cno,Score.Degree
from Student ,Score
where Student.Sno=Score.Sno

--（14）查询所有学生的Sno、Cname和Degree列。（内连接）

select Student.Sname,Score.Cno,Score.Degree
from Student join Score
on Student.Sno=Score.Sno

--（15）查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select *
from Score
where Cno='001'
and Sno<>'202001'
and Degree>(
select Degree
from Score
where Sno='202001'
and Cno='001'
)

--（16）查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有同学记录。

select *
from Score
where Sno<>'202001'
and Cno='001'
and Degree>(
select Degree
from Score
where Sno='202001'
and Cno='001'
)

--（17）查询和学号为108的同学同年出生的学生的Sno、Sname和Sbirthday。
select Sno,Sname,Sbirthday
from Student 
where Sbirthday in
(
select Sbirthday
from Student
where Sno='202001'
)
and Sno<>'202001'

--（18）查询选修某课程的同学人数多于2人的教师姓名。

select Tname
from Teacher
where Tno in 
(
select Tno
from Course
where Cno in
(
select Score.Cno
from Teacher join Course
on Teacher.Tno=Course.Tno
join Score
on Score.Cno=Course.Cno
group by Score.Cno
having count(*)>1
)
)

（19）查询出“计算机系”教师所教课程的成绩表。
select Sno,Course.Cno,Degree
from Teacher join Course
on Teacher.Tno=Course.Tno
join Score
on Score.Cno=Course.Cno
where Depart='cs'

pre class="code">--（20）查询成绩比该课程平均成绩低的同学的成绩表。
select Sno,Cno,Degree
from Score a
where exists
(select *
from Score b
where a.Cno=b.Cno
group by Cno
having a.Degree<avg(Degree)
)
order by Sno

--（21）查询所有任课教师的Tname和Depart.

select Tname,Depart
from Teacher

--（22）查询所有未讲课的教师的Tname和Depart. 
select Tname,Depart
from Teacher
where Tno in
(
select Tno
from Teacher
except
select Tno
from Course
)

（23）查询至少有2名男生的班号。 
select Class
from Student
where Ssex='男'
group by Class
having Count(*)>1

（24）查询Student表中不姓“王”的同学记录。
select *
from Student
where Sname not like '张%'

（25）查询和“李军”同性别的所有同学的Sname.（嵌套子查询）
select Sname
from Student 
where Sno not in
(
select sno
from Student 
where Sname='张山'
)
and Ssex =(
select Ssex
from Student 
where Sname='张山'
)

（26）查询和“李军”同性别并同班的同学Sname.(自身连接)
select *
from Student a,Student b
where a.Sno<>b.Sno
and b.Sname='张山'
and a.Ssex=(
select Ssex
from Student 
where Sname='张山'
)

--（27）查询所有选修“计算机导论”课程的“男”同学的成绩表。
select Student.Sno,Score.Cno,Score.Degree
from Course join Score 
on Course.Cno=Score.Cno
join Student 
on Score.Sno=Score.Sno
where Course.Cname='C'
and Student.Ssex='男'

--（28）查询选修了全部课程的男同学的情况


select *
from Student
where Sno in
(
select Sno
from Score
group by Sno
having count(*)=(select count(*) 
from Course )
)
and Ssex='男'

第三次数据库工程实践考试题目与代码

编码环境为SQL Server。

请把答案写在本文档中，考试完成后，请提交本文档。

（答题文档命名为：学号+姓名+专业班级+数据库工程实践答题）

1、按模板要求，将下面某职工工作管理信息系统的需求描述补充完整：

图一数据库系统需求分析模板

工厂（工厂编号，工厂名，地址）

职工（职工编号，姓名，性别，技术等级，工厂编号，聘期，工资）

产品（产品号，产品名，规格，单价）

生产（工厂编号，产品号，销售数量）

某职工工作管理信息系统的数据需求如下：

工厂编号（FactoryID、工厂名（FactoryName）、地址（FactoryAddress）、职工编号（StaffID）、姓名（StaffName）、性别（StaffSex）、技术等级（StaffGrade）、聘期（StaffPeriod）、工资（StaffWage）、销售数量（SalesMessage）、产品号（ProductID）、产品名（ProductName）、规格（ProductSize）、单价（ProductPrice）

实体型与实体型之间的联系如下：

一名职工属于一个工厂，一个工厂有多名职工。
一种产品可以被多个工厂生产。
一个工厂可以生产多种产品。

2、请根据系统的数据需求，进行系统的概念结构设计，并给出系统的E-R图和属性列表(要求至少5个表)。

属性列表

数据表名			Factory
数据表中文含义			工厂
中文名称		字段名称		字段类型及长度	非空约束	主码约束	外码约束
工厂编号		FactoryID		varchar(5)	TRUE	TRUE	TRUE
工厂名		FactoryName		varchar(20)	TRUE	TRUE	FALSE
地址		FactoryAddress		varchar(50)	TRUE	TRUE	FALSE
注释	无

数据表名			Staff
数据表中文含义			职工
中文名称		字段名称		字段类型及长度	非空约束	主码约束	外码约束
职工编号		StaffID		varchar(5)	TRUE	TRUE	TRUE
姓名		StaffName		varchar(20)	TRUE	TRUE	FALSE
性别		StaffSex		varchar(2)	TRUE	TRUE	FALSE
技术等级		StaffGrade		varchar(10)	TRUE	TRUE	FALSE
注释	无

数据表名			聘用
数据表中文含义			Engage
中文名称		字段名称		字段类型及长度	非空约束	主码约束	外码约束
工厂编号		FactoryID		varchar(5)	TRUE	TRUE	TRUE
职工编号		StaffID		varchar(5)	TRUE	TRUE	TRUE
聘期		StaffPeriod		varchar(10)	TRUE	FALSE	FALSE
工资		StaffWage		varchar(20)	TRUE	FALSE	FALSE
注释	主码为（FactoryID，StaffID）

数据表名			Yield
数据表中文含义			生产
中文名称		字段名称		字段类型及长度	非空约束	主码约束	外码约束
工厂编号		FactoryID		varchar(5)	TRUE	TRUE	TRUE
产品号		ProductID		varchar(5)	TRUE	TRUE	TRUE
生产数量		ProductCount		varchar(10)	TRUE	FALSE	FALSE
注释	主码为（FactoryID，ProductID）

数据表名			Product
数据表中文含义			产品
中文名称		字段名称		字段类型及长度	非空约束	主码约束	外码约束
产品号		ProductID		varchar(5)	TRUE	TRUE	FALSE
产品名		ProductName		varchar(20)	TRUE	FALSE	FALSE
规格		ProductSize		varchar(10)	TRUE	FALSE	FALSE
单价		ProductPrice		varchar(10)	TRUE	FALSE	FALSE
注释	无

3、进行系统的逻辑结构设计，给出系统所有的关系模式，并对关系模式进行优化，使得每一个关系模式达到3NF要求。并选取任一关系模式说明理由。

工厂（工厂编号，工厂名，地址）

职工（职工编号，姓名，性别，技术等级）

产品（产品号，产品名，规格，单价）

生产（工厂编号，产品号，生产数量）

聘用（工厂编号，职工编号，聘期，工资）

4请给出创建任意两个关系模式的代码，并输入不少于5条元组。

create table Factory (

FactoryID            varchar(5)           not null,

FactoryName          varchar(20)          not null,

FactoryAddress       varchar(50)          not null,

constraint PK_FACTORY primary key nonclustered (FactoryID)

)

create table Staff (

StaffID              varchar(5)           not null,

StaffName            varchar(20)          not null,

StaffSex             varchar(2)           not null,

StaffGrade           varchar(10)          not null,

constraint PK_STAFF primary key nonclustered (StaffID)

)

insert into Factory

values(00001,'一号工厂','成都')

insert into Factory

values(00002,'二号工厂','广西')

insert into Factory

values(00003,'三号工厂','山东')

insert into Factory

values(00004,'四号工厂','郑州')

insert into Factory

values(00005,'五号工厂','浙江')

insert into Staff

values(00001,'张三','男','技术一级')

insert into Staff

values(00002,'李四','女','技术二级')

insert into Staff

values(00003,'王五','男','技术一级')

insert into Staff

values(00004,'刘东','男','技术一级')

insert into Staff

values(00005,'张飞','男','技术二级')

5请在任意两个表创建合适索引。

Create unique index UK_sname on Staff(StaffName)

Create unique index UK_fname on Factory(FactoryName)

6构建合理的视图（2个），请给出代码。

go

create view Male_Staff

as

select *

from Staff

where StaffSex='男'

go

create view G1_Staff

as

select *

from Staff

where StaffGrade='技术一级'

go

7创建触发器，实现表的数据增、删、改功能。请给出代码。

--增

go

create trigger addToStaff_bak

on Staff after insert

as

declare @StaffId varchar(5),@StaffName varchar(20),@StaffSex varchar(2),@StaffGrade varchar(10)

select StaffID,StaffName,StaffSex,StaffGrade

from deleted

insert into Staff_bak

values(@StaffID,@StaffName,@StaffSex,@StaffGrade)

insert into Staff

values('00008','张飞号','男','技术二级')

--删

go

create trigger tri_delete_Staff

on Staff

after delete

as

insert into

Staff_bak(StaffID,StaffName,StaffSex,StaffGrade)

select StaffID,StaffName,StaffSex,StaffGrade

from deleted

 

 

delete from Staff

where StaffName='张飞号'

--改

go

create trigger tri_update_Staff

on Staff

after update

as

insert into

Staff_bak(StaffID,StaffName,StaffSex,StaffGrade)

select StaffID,StaffName,StaffSex,StaffGrade

from deleted

 

 

 

update Staff

set StaffName='张三'

where StaffName='王五'

8编写合理的增、删、改、查四个存储过程(要求两个参数或以上)。请给出实现的代码。

--查

go

create proc select_MaleG1 @StaffGrade varchar(10)

,@StaffSex varchar(2)

as

select *

from Staff

where StaffGrade=@StaffGrade

and StaffSex=@StaffSex

exec select_MaleG1 @staffGrade='技术一级',

@staffsex='男'

--改

go

create proc upStaffSex @StaffID varchar(5)

,@StaffSex varchar(2)

as

update Staff

set StaffSex=@StaffSex

where StaffID=@StaffID

exec upStaffSex @staffID='1',

@staffsex='女'

--删

go

create proc deStaffSexG1 @StaffGrade varchar(10)

,@StaffSex varchar(2)

as

delete from Staff

where StaffGrade=@StaffGrade

and StaffSex=@StaffSex

exec deStaffSexG1 @staffGrade='技术一级',

@staffsex='女'

--增

go

create proc addStaffSexG1 @StaffGrade varchar(10)

,@StaffSex varchar(2),@StaffID varchar(5),@StaffName varchar(20)

as

insert into Staff

values(@StaffID,@StaffName,@StaffSex,@StaffGrade)

exec addStaffSexG1 @StaffID='00006', @staffGrade='技术一级',

@staffsex='女',@StaffName='新李四'

9创建一个合适的instead of触发器，实现在一个不可更新视图（聚集函数类型）中插入数据时，所依赖的多个基本表会自动更新。

go

create view  IOview1

as

select  Factory.FactoryName , MAX(StaffWage) '各工厂最高工资'

from Factory join Engage

on Factory.FactoryID=Engage.FactoryID

join Staff

on Engage.StaffID=Staff.StaffID

Group by Factory.FactoryName

go



go

create trigger IOtri

on IOview1  instead of insert

as

declare @FactoryName varchar(20),@StaffWage varchar(20), @OldFactoryName varchar(20),@OldStaffWagevarchar(20)

select @FactoryName=FactoryName,@StaffWage='各工厂最高工资'

from inserted

select @FactoryName=FactoryName,@StaffWage='各工厂最高工资'

from deleted

update Factory

set FactoryName=@FactoryName

where FactoryName=@OldFactoryName

update Engage

set StaffWage=@StaffWage

where StaffWage=@StaffWage

insert into IOview1

values('工厂一号','8000')

数据库权限操作相关知识

on student

6.1

GRANT ALL PRIVILEGES

ON 学生班级

TO U1;

6.2

GRANT SELECT ,UPDATE(家庭住址)

ON 学生

TO U2;

6.3

GRANT SELECT 

ON 班级

TO PUBLIC;

6.4

GRANT SELECT ,UPDATE

ON 学生

TO R1;

6.5

GRANT R1

TO U1

WITH ADMIN OPTION;

7.1

GRANT SELECT 

ON 职工,部门

TO 李勇;

7.3

CREATE VIEW 职工视图 

AS 

SELECT *

FROM 职工

WHERE COCAT(姓名,'@localhost')=USER();



GRANT SELECT 

ON 职工视图

TO PUBLIC;

7.4

GRANT SELECT ,UPDATE(工资)

ON 职工

TO 张新

7.6

GRANT ALL PRIVILEGES

ON 职工,部门

TO 周平

7.7

CREATE VIEW MMA(部门号,最高工资,最低工资,平均工资)

AS

SELECT 部门号,MAX(工资),MIN(工资),AVG(工资)

FROM 职工 JOIN 部门

ON 职工.部门号=部门.部门号

GROUP BY 部门.部门号



GRANT SELECT 

ON MMA

TO 杨兰

候选码练习题

分别求出下面5组关系的候选码并判断该模式是第几范式，试说明理由并对其进行优化，至少达到BCNF模式：

第一题

设一个职工关系为Employees（职工号，姓名，性别，年龄，职务）

候选码？

候选码为：职工号

该模式为第几范式？

第四范式（4NF）

理由？

每一个属性都不可再分，属于第一范式

非主属性完全函数依赖于候选码，属于第二范式

没有传递函数依赖，属于第三范式

非主属性之间没有函数依赖，属于BCNF

属性之间没有非平凡且非函数依赖的多值依赖，属于第四范式

如何优化？

不用优化

第二题

设一个学生关系为Student（学号，姓名，性别，系号，系名，系主任名），通常每个学生只属于一个系，每个系有许多学生，每个系都对应唯一的系名和系主任名。

候选码？

候选码为（学号）

该模式为第几范式？

第二范式（2NF）

理由？

有传递函数依赖，所以不是第三范式。

如何优化？

将Student（学号，姓名，性别，系号，系名，系主任名）关系模式分解为：

Student（学号，姓名，性别，系号）

Department（系号，系名，系主任名）

分解后的关系模式Student和Department，没有传递函数依赖，为第三范式。

关系模式Student非主属性之间没有函数依赖，为BCNF。

关系模式Department没有非主属性，所以也就没有非主属性之间的函数依赖，为BCNF

关系模式Student和Department，属性之间没有非平凡且非函数依赖的多值依赖，为第四范式

第三题

设一个教师任课关系为Teachers（教工号，姓名，职称，课程号，课程名，课时数，课时费），该关系给出某个学校每个教师在一个学期内任课安排的情况，假定每个教师可以讲授多门课程，每门课程可以由不同教师来讲授。

候选码？

候选码为：（教工号，课程号）

由于任一个单属性都不能函数决定关系中的所有属性，所以都不是候选码，若选取一个属性子集（教工号，课程号），由于它能够函数决定所有属性，所以它是该关系的一个候选码，并且是唯一的候选码。

该模式为第几范式？

第一范式（1NF）

理由？

存在非主属性部分函数依赖于候选码，所以不是第二范式。

如何优化？

将关系模式Teachers（教工号，姓名，职称，课程号，课程名，课时数，课时费）分解为：

Teachers（教工号，姓名，职称）

Course（课程号，课程名，课时数）

Fee(教工号，课程号，课时费)

分解后的关系模式Teachers、Course和Fee，非主属性完全函数依赖于候选码，为第二范式。

分解后的关系模式Teachers、Course和Fee，没有传递函数依赖，为第三范式。

分解后的关系模式Teachers、Course和Fee，非主属性之间没有函数依赖，为BCNF。

分解后的关系模式Teachers、Course和Fee，属性之间没有非平凡且非函数依赖的多值依赖，为第四范式。

第四题

设有关系模式study（教工名，项目名，项目经费，部门名，部门领导），如果规定每个教工可参与多个项目，分别获得一笔不等的项目经费；每个项目只属于一个部门管理；每个部门只有一个部门领导。

候选码？

候选码为（教工名，项目名）

该模式为第几范式？

第一范式（1NF）

理由？

存在非主属性部分函数依赖于候选码，所以不是第二范式。

如何优化？

将系模式study（教工名，项目名，项目经费，部门名，部门领导）分解为：

study（教工名，项目名，项目经费）

project（项目名，部门名）

department（部门名，部门领导）

分解后的关系模式study、project和department，非主属性完全函数依赖于候选码，为第二范式。

分解后的关系模式study、project和department，没有传递函数依赖，所以为第三范式。

分解后的关系模式study、project和department，非主属性之间没有函数依赖，所以为BCNF。

分解后的关系模式study、project和department，属性之间没有非平凡且非函数依赖的多值依赖，所以为第四范式。

第五题

设一个教学关系为Teaching（教师号，姓名，课程号，课程名，课程学分，专业号，专业名，教学等级分），假定每个教师有一个唯一的教师号，每门课程有一个唯一的课程号，每个专业有一个唯一的专业号，每个教师号对应一个姓名，每个课程号对应一个课程名和一个课程学分，每个专业号对应一个专业名，教学等级分是根据某个教师给某个专业上某门课程的教学评价效果而得到的分数，每个教师可以给不同的专业上不同的课程，请通过函数依赖分析，求出该关系的候选码。

候选码？

候选码为（教师号，课程号，专业号）

该模式为第几范式？

第一范式

理由？

存在非主属性部分函数依赖于候选码，所以不是第二范式。

如何优化？

将关系模式Teaching（教师号，姓名，课程号，课程名，课程学分，专业号，专业名，教学等级分）分解为：

Teaching（教师号，姓名）

Course（课程号，课程名，课程学分）

Faculty（专业号，专业名）

TeachingGrade（教师号，课程号，专业号，教学等级分）

分解后的关系模式Teaching、Course、Faculty和TeachingGrade，非主属性完全函数依赖于候选码，为第二范式。

分解后的关系模式Teaching、Course、Faculty和TeachingGrade，没有传递函数依赖，为第三范式。

分解后的关系模式Teaching、Course、Faculty和TeachingGrade，非主属性之间没有函数依赖，为BCNF。

分解后的关系模式Teaching、Course、Faculty和TeachingGrade，属性之间没有非平凡且非函数依赖的多值依赖，为第四范式。

参考资料：

关系数据库设计中范式理论的教学方法探讨李俊,罗勇胜顺德职业技术学院广东佛山528300 文章编号:1005-1228(2016)02-0068-04

函数依赖（理论及举例）

参考资料文档下载：

关系数据库设计中范式理论的教学方法探讨

函数依赖(理论及举例)

去年15分大题

某商业集团的销售管理系统：数据库中有三个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品号、商品名、规格、单价等；三是“职工”实体集，属性有职工编号、姓名、性别、业绩等。

商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店销售一种商品，有月销售量；商店与职工间存在着“聘用”联系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。

1、试画出ER图，并在图上注明属性、联系的类型。

2、将ER图转换成关系模式，并注明主键和外键。

商店（商店编号，商店名，地址）

主键：商店编号

商品（商品号，商品名，规格，单价）

主键：商品号

职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪）

主键：职工编号外键：商店编号

销售（商店编号，商品号，月销售量）

外键：商店编号，商品号

其他随笔

随笔嘛，就是随便写点东西，那我就随便列一些标志性的知识吧。

主数据文件 .mdf

辅数据文件 .ndf

日志文件 .ldf

文件五属性

name

filename

size

maxsize

filegrowth

用 = 给它们赋值!

其中filename要带盘符！

添加辅数据文件和日志文件，一个是add file ，一个是add log file

但是删除辅数据文件和日志文件的时候，情况发生了变化，两个用的都是 remove file !!!!!这一点一定要注意！！！

删除或者修改一个属性的时候，结构为：drop column 属性名 /alter column 属性名，它是有column这个单词的

但是在添加一个属性的时候，结构为：add 属性名，没有column这个单词！！！！注意区别！

起别名的方式有三种，分别是 = 、 as 、空格

DISTINCT

TOP N

TOP N PERCENT

BETWEEN ..... AND ......

% 任意个任意字符

_ 单个任意字符

转义字符 ESCAPE ' 单个自定义字符'

ORDER BY 默认ASC升序排列，DESC 降序排列

聚合函数

AVG()

MAX()

MIN()

COUNT()

SUM()

GROUP BY 后可以使用HAVING ， HAVING可以使用聚合函数对分组后的表进行筛选

谓词连接

WHERE 表1.属性=表2.属性

内连接

select *

form A 表 join B 表

on A.属性=B.属性

自身连接

A as a join A as b

on a.属性 =b.属性

左外连接

LEFT JOIN

右外连接

RIGHT JOIN

交叉连接（笛卡尔积）

CROSS JOIN

差集

EXCEPT

交集

INTERSECT

至少有一个

ANY

全部

ALL

存在量词

EXISTS

结果集有一条以上的查询结果，就返回真

NOT EXISTS

结果集有一条没查出来就返回真

索引

UNIQUE

CLUSTER

NONCLUSTER

CREATE 索引种类 INDEX 索引名 ON 表名（列名）

视图

CREATE VIEW 视图名 [列名]

...............................

[WITH CHECK OPTION]

(二次检查)

加了二次检查的视图在向该视图内插入数据时，插入的数据必须满足该视图WHERE条件的限制

存储过程

CREATE PROC 存储过程名 @ 变量名变量数据类型

................................

调用存储过程

EXEC 存储过程名 @ 变量名

触发器

CREATE TRIGGER 触发器名

ON 表名 AFTER/INSTEAD OF 操作

DECLARE @ 变量名变量类型

SELECT @ 变量名 = 属性名 FORM DELETED/INSERTED

.................................

当题目中有修改有修改约束的字眼时，要注意，约束不能修改，自能先把该约束删除，然后再添加一个新的约束！！！！

YEAR() 函数，取年份非常好用

一对多

多的会抢一的主码当外码

多对多

中间会多个表

第一范式

每一个属性都不可再分

第二范式

非主属性完全函数依赖于候选码

第三范式

没有传递函数依赖

BCNF

非主属性之间没有函数依赖

第四范式

属性之间没有非平凡且非函数依赖的多值依赖

2021年 11月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

引语

高超老师的课堂教案

数据库的组成

每一个文件有五个属性

作业1

一、定义基本表结构

1定义 表名字，属性名称，数据类型，（数据长度）

2定义合理的约束 constraint

修改表结构举例

单表查询

1 指定列 select sno,sname

2 全部列

5 指定显示多少元组

6 带where 限制条件的查询

确定范围:要求有明显的上限和下限

确定集合:没有明显的上限和下限

匹配串为含通配符的字符串

使用换码字符将通配符转义为普通字符

Order by 排序

聚集函数：对原始列（属性）进行简单的数理统计

Group by 分组，常见的按性别，所在系分组且和select语句原始列保持一致

HAVING筛选短语:对聚集函数做进一步筛选

连接查询：查询的结果或条件同时涉及多个表的查询

连接条件或连接谓词：用来连接两个表的条件

2 内连接：表连接条件有on语句(一个ON语句负责一个条件)负责;from中表和表连接用 join

自身连接：一个表与其自己进行连接

1 内连接

2 左外，右外，完全

3 交叉连接 ，没有连接条件(只有两行)

嵌套查询概述（拆分*）

1 用in 或=做查询的连接*

带有ANY或ALL谓词的比较类子查询

EXISTS谓词：行（元组）的连接存在量词

索引

视图view的特点

存储过程的增删改查（注意形式参数和EXEC调用）

触发器（没有形参没有EXEC调用，满足条件自动触发）

创建一个合适的instead of触发器，实现在一个不可更新视图中插入数据时，所依赖的多个基本表会自动更新。

第一次数据库工程实践考试题目与代码

第二次数据库工程实践考试题目与代码

第三次数据库工程实践考试题目与代码

数据库权限操作相关知识

候选码练习题

第一题

候选码？

该模式为第几范式？

理由？

如何优化？

第二题

候选码？

该模式为第几范式？

理由？

如何优化？

第三题

候选码？

该模式为第几范式？

理由？

如何优化？

第四题

候选码？

该模式为第几范式？

理由？

如何优化？

第五题

候选码？

该模式为第几范式？

理由？

如何优化？

去年15分大题

其他随笔

发送评论 编辑评论

推荐文章

1定义表名字，属性名称，数据类型，（数据长度）

3 交叉连接，没有连接条件(只有两行)

发送评论编辑评论