主要是考试的题目和代码,参考学习!!!
新的一年里,祝愿我们学业顺利,在所擅长的领域里共同进步。路很远,请慢慢走。
一、关系运算
/*选择2号课程的学生学号*/
∏Sno (σcno = '2'(SC))
/*至少选修了一门先行课为5的学生*/
∏Sname(σCpno = '5'(C) )▷◁SC ▷◁ ∏Sno,Sname(S)
二、SQL语句
规范化写SQL语句,关键字全部用大写。
1、基本表的建立
1.建立一个SC表
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade INT,
PRIMARY KEY(SNO,CNO), /*主码*/
FORRIGN KEY(Sno) REFFERENCES S(Sno),
FORRIGN KEY(Cno) REFFERENCES C(Cno)
/*表级完整性约束条件*/
);
/*Check子句*/
CREATE TABLE A(
name CHAR(20),
age INT,
CHECK(age>0 AND age <100)
);
2.建立索引
CREATE UNIQUE INDEX Stuno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno AESC);
3.查询语句
SELECT Sno,Sname
FROM S;
/*查询全体学生的信息*/
SELECT *
FROM S;
SELECT DISTINCT Sno
FROM SC;
SELECT Sname,Sage
WHERE Sage<20;
SELECT Sname,Ssex
FROM S
WHERE Sdept IN('CS','MA','IS')
SELECT Sname,Sno
FROM S
WHERE Sname LIKE'何%';
SELECT Sno,Sname
FROM S,SC
WHERE S.Sno = SC.Sno AND SC.Grade > 80;
3.对查询结果排序
SELECT Sno,Grade
FROM SC
WHERE Cno = '3'
GROUP BY Grade DESC;
- 连接查询
SELECT S.Sno,Sname
FROM S,SC
WHERE S.Sno = SC.Sno AND
SC.Cno ='2' AND SC.Grade > 90;
5.嵌套查询
SELECT Sname,sno,Sdept
FROM S
WHERE Sdept IN
(
SELECT Sdept
FROM S
WHERE Sname = '小明'
)
6.插入数据
INSERT
INTO S(Sno,Sname,Ssex,Sdept,Sage)
VALUES('2020140083' ,'女','CS','18');
7.修改数据
UPDATE S
SET Sage = '22'
WHERE Sno = '2020140011';
/*所有学生年龄加一*/
UPDATE S
SET Sage += 1;
8.建立视图
CREATE VIEW IS_S1(Sno, Sname,Grade)
AS
SELECT S.Sno,Sname,Grade
FROM S,SC
WHERE
Sdept = 'IS' AND
S.Sno = SC.Sno AND
SC.Cno = '1'
CREATE VIEW F_Student(F_sno,name,sex,age,dpet)
as
SELECT *
FROM S
WHERE Ssex = '女';
9.删除视图
DROP VIEW BT_S
DROP VIEW BT_S1 CASCADE /*级联删除*/
三、数据库的安全性
1.授权
GRANT SELECT
ON TABLE S
TO U1;
/*把S,C表的全部操作权限授予给U2,U3*/
GRANT ALL PRIVILEGES
ON TABLLE Studen,Course
TO U1,U3;
/*把查询权限授予给所有用户*/
GRANT SELECT
ON TABLE SC
TO PUBLIC;
GRANT SELECT,UPDATE(Sno)
ON TABLE S
TO U4;
/*U5允许将此权限在授予给其他用户*/
GRANT INSERT
ON TABLE SC
TO U5
WHERE GRANT OPTION;
2.角色
/*创建角色*/
CREATE ROLE R1;
/*给角色R1授权*/
GRANT SELECT,UPADTE,INSERT
ON TABLE Student
TO R1;
/*将角色授予用户*/
GRANT R1
TO 小何,小明,小可;
/*收回权限*/
REVOKE R1
FROM 小明;
四、数据库完整性
1.触发器 (必考!!!)
CREATE TRIGGER SC_T
AFTER UPDATE OF GRADE ON SC
REFERENING
OLDROW AS OLDTuple
NEWROW AS NewTuple
FOR EACH ROW
WHERE(NewTuple.Grade >=1.1*OLDTuple.Grade)
INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
VLUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)
CREATE TRIGGER INSERT_Or_Update_Sal
BEFORE INSERT OR UPDATE ON TEACHER
RFFERENCING NEW row AS newTuple
For EACH ROW
/*定义一个触发动作体*/
BEGIN
IF(newTuple.JOB = '教授')AND(newTuple.Sal < 4000)
THEN newTuple := 4000;
END IF
END;
五、规范化
1、范式
第一范式:属性不可再分
第二范式:每个非主属性完全依赖于任何一个候选码。消除了非主属性对吗码的依赖。
第三范式:在第2NF上,消除了非主属性对码的传递函数依赖。
BC范式:在第3NF上,在消除了主属性的部分函数依赖和传递函数依赖。
六、数据库设计
学校管理中涉及到的实体有:
学校,系,班级,教研室,教员,教授,副教授,研究生,学生,课程;
把E-R图转换成关系模式
学校(校名)
系(系编号,系名)
班级(班号,系别,班主任,班级人数)
学生(学号,姓名,性别,年龄,班号)
教研室(教研室编号,教研类别,教研室负责人,可容纳人数)
例子2:
该工厂管理系统中实体有:工厂产品、零件、材料、仓库;
作出E-R图如下:
实体属性为:
工厂产品:产品号,产品名
零件:零件号,零件名
材料:材料名,材料号,材料类别
仓库:仓库号,仓库名,仓库大小
各联系属性为:
产品组成:零件数
零件制成:材料需求量
零件存储:零件存储量
材料存放:材料存放量
E-R图转换成关系模型为::
工厂产品()、零件()、材料(材料名,材料号,材料类别,材料存放量)、仓库(仓库号,仓库名,仓库大小)
产品组成:零件号,产品号,零件数
零件制成:零件号,材料号,材料需求量
零件存储:零件号,仓库号,零件存储量
七、数据库的编译
1.存储过程
/*定义存储过程 TRANSFER*/
CREATE OR REPLACE PROCEDURE TRANSFER(inAcount INT,outAcount INT,amout FlOAT)
/*使用CALL或者PERFORM激活存储过程的执行*/
CALL PROCEDURE TRANSFER(12345689,87546321,10000);
/*修改存储过程*/
/*使用ALERT PROCEDURE 重命名一个存储过程*/
ALERT PROCEDURE ARENAME TO B;
/*重新编译*/
ALERT PROCEDURE A COMPILE;
/*删除存储过程*/
DROP PROCEDURE A();
八、并发控制
1、不一致性
并发控制带来的数据不一致性包括 丢失修改、不可重复读、读“脏”数据。
2、封锁协议
一级封锁协议:在事务T修改数据R之前必须加上X锁(排它锁,只允许T读取和修改R,其他事物不可加锁),直到事务结束才释放。 解决:丢失修改
二级封锁协议:在一级封锁协议基础上增加事务T在读取数据R之前加S锁(共享锁,事务T可以读R但不能修改),读取后可以释放S锁。 解决:丢失修改,读脏数据
二级封锁协议:在一级封锁协议基础上增加事务T在读取数据R之前加S锁(共享锁,事务T可以读R但不能修改),直到事务结束才释放。 解决:丢失修改,读脏数据,不可重复读。