[大数据]数据库系统概论的一些知识点

前言

因为最近好像没什么要紧的事干，复习一下数据库吧。

数据库的四个基本概念

数据

数据库

数据库管理系统

数据库系统

数据库系统可如上图所示。

数据模型

概念模型

逻辑模型

层次模型

网状模型

关系模型

数据库系统的三级模式结构

外模式

模式

内模式

关系代数

例子

关系元组

例子

事务

定义

性质

数据库的不一致性

丢失修改

不可重复读

读“脏”数据

小结

封锁

一级封锁协议

二级封锁协议

三级封锁协议

小结

SQL语句第一次作业

[1] 创建数据库Studentdb

CREATE DATABASE Studentdb;

[2] 在数据库Studentdb中创建学生表Student

CREATE TABLE Student(
Sno CHAR(4) NOT NULL,
Sname VARCHAR(8) NOT NULL,
Gender CHAR(2),
Birthday DATE,
Haddress VARCHAR(50),
Height DECIMAL(3,2),
Remark text,
PRIMARY KEY(Sno)
);

[3] 在数据库Studentdb中创建课程表Course

CREATE TABLE Course(
Cno CHAR(4) NOT NULL,
Cname VARCHAR(20) NOT NULL,
Credit int,
PRIMARY KEY(Cno)
);

[4] 在数据库Studentdb中使用创建成绩表Score

CREATE TABLE Score(
Sno char(4) NOT NULL,
Cno char(4),
Grade DECIMAL(3,1),
PRIMARY KEY(Sno, Cno)
);

[5] 将下列数据插入Student表中

INSERT INTO student(Sno,Sname,Gender,Birthday,Haddress,Height)
VALUES
('0001','刘一平','男','1990-10-1','温州市环城西路201号',1.78),
('0002','张得民','男','1990-12-2','杭州市下沙路22号',1.65),
('0003','马东','男','1990-7-4','宁波市中山北道20号',1.71),
('0004','肖海燕','女','1990-3-15','温州市越秀北路43号',1.65),
('0005','张民华','女','1991-5-13','宁波市艮山路7号',1.63);

[6] 将下列数据插入course表中

INSERT INTO course(Cno,Cname,Credit) 
VALUES
('0001','计算机基础',2),
('0002','管理学原理',3),
('0003','数据库原理及应用',3),
('0004','项目管理',2),
('0005','毕业论文',10);

[7] 将下列数据插入Student表中

INSERT INTO score(Sno,Cno,Grade)
VALUES
('0001','0001',80.0),
('0001','0002',90.0),
('0001','0003',70.0),
('0001','0004',85.0),
('0001','0005',92.0),
('0002','0001',78.0),
('0002','0002',NULL),
('0002','0003',77.0),
('0002','0004',67.0),
('0003','0001',66.0),
('0003','0002',76.0),
('0003','0003',NULL),
('0003','0004',73.0)
;

[8] 查询全体学生的详细记录(不包括选课信息)

SELECT * FROM student;

[9] 查询学生表中学生的姓名和地址信息

SELECT Sname,Haddress FROM student;

[10]查询学生表中“刘”姓学生的信息

SELECT * FROM student
WHERE Sname LIKE "刘%";

[11]查询学生表中姓名包含“民”的学生的信息

SELECT * FROM student
WHERE Sname LIKE "%民%";

[12]查询所有身高1.75以上的男学生的学号和姓名

SELECT Sno,Sname FROM student
WHERE Gender='男' AND Height>1.75;

[13]查询所有来自“宁波”的学生姓名、性别和年龄

SELECT Sname,Gender,TIMESTAMPDIFF(YEAR, Birthday, CURDATE()) AS Age
FROM student
WHERE Haddress LIKE "宁波%";

[14]查询没有考试成绩的学生学号和课程编号

SELECT DISTINCT Sno,Cno FROM Score
WHERE Grade <=> NULL;

[15]查询所有参加过考试的学生学号

SELECT DISTINCT Sno FROM score
WHERE Grade IS NOT NULL;

[16]查询所有学分不小于3的课程名

SELECT Cname FROM course
WHERE Credit>=3;

[17]查询学分在1~5范围内的课程编号和课程名称

SELECT Cno,Cname
FROM course
WHERE Credit>=1 AND Credit<=5;

[18]查询“数据库原理及应用”课程的信息

SELECT * FROM course
WHERE Cname="数据库原理及应用";

[19]查询每门(被选修)课程的课程号以及选修该课程的学生信息，并按课程号升序进行排列

SELECT score.Cno,student.*
FROM student INNER JOIN score 
ON score.Sno = student.Sno ORDER BY Cno;

SQL语句第二次作业

[1] 查询选修“0002”号课程的学生的成绩最高分

SELECT MAX(Grade) 
FROM score
WHERE Cno='0002';

[2] 统计参加“0001”号课程考试的学生人数

SELECT COUNT(DISTINCT Sno)
FROM score
WHERE Cno='0001';

[3] 计算“0003”号课程中所有参加过考试的学生平均分

SELECT AVG(DISTINCT Grade)
FROM score
WHERE Cno='0003';

[4] 查询选修了3门课以上[包括3门]的学生姓名

SELECT student.Sname
FROM student INNER JOIN score
ON score.Sno = student.Sno 
GROUP BY score.Sno
HAVING COUNT(*)>=3;

[5] 统计各个课程号及相应的选课人数

SELECT Cno,COUNT(DISTINCT Sno)
FROM score
GROUP BY Cno;

[6] 统计没有参加考试的学生学号和姓名

SELECT student.Sno,student.Sname
FROM student,score
WHERE student.Sno = score.Sno AND
score.Grade IS NULL;

[7] 查询选修了所有课程的学生学号和姓名

SELECT student.Sno,student.Sname
FROM student,score
WHERE student.Sno = score.Sno
HAVING COUNT(*)=(SELECT COUNT(Cno)FROM score);

[8] 统计选修人数在3人以上[包括3人]的课程号和课程名

SELECT score.Cno,course.Cname
FROM score,course
WHERE score.Cno = course.Cno
GROUP BY score.Cno
HAVING COUNT(*)>=3;

[9] 查询每个学生的学号、姓名、选修的课程名及成绩

SELECT student.Sno,student.Sname,course.Cname,score.Grade
FROM student,course,score
WHERE student.Sno = score.Sno AND score.Cno = course.Cno;

[10] 查询选修了课程号为“0003”的学生姓名和住址

SELECT student.Sname, student.Haddress
FROM student, score
WHERE student.Sno = score.Sno AND
score.Cno='0003';

[11] 查询有两门及以上课程成绩大于等于90分的学生学号和姓名

SELECT student.Sno,student.Sname
FROM student,score
WHERE student.Sno = score.Sno AND
Grade >= 90
GROUP BY student.Sno
HAVING COUNT(*)>=2;

[12] 查询比“0003”号课程平均分高的其它课程信息

SELECT DISTINCT course.*
FROM course, score
WHERE course.Cno = score.Cno
GROUP BY score.Cno
HAVING AVG(score.Grade)>
(SELECT AVG(Grade) FROM score WHERE Cno='0003');

[13] 查询每个学生及其选修课程的情况[包括没有选修课程的学生]

SELECT student.*,course.*
FROM student,course,score
WHERE student.Sno = score.Sno AND
course.Cno = score.Cno;

[14] 将选修了课程号为0002的学生的成绩减去2分

UPDATE score
SET Grade = Grade-2
WHERE Cno='0002';

[15] 删除0002号课程的所有选课记录

DELETE FROM score
WHERE Cno='0002';

[16] 删除所有的学生选课记录

DELETE FROM score;

SQL语句第三次作业

[1] 使用SQL语句删除学生信息表(Student)中“备注”字段

alter table student drop Remark;

[2] 使用SQL语句修改学生信息表(Student)中字段属性如下

alter table student modify column Sname VARCHAR(20) not null ;

[3] 为Student表添加“系名”字段，存储数据如：“信息系”，“数学系”，“计算机系”等，具体数据可自行添加到Student表中

UPDATE student set 系名 = '信息系'
WHERE Sno="0001";
UPDATE student set 系名 = '数学系'
WHERE Sno="0002";
UPDATE student set 系名 = '计算机系'
WHERE Sno="0003";
UPDATE student set 系名 = '信息系'
WHERE Sno="0004";
UPDATE student set 系名 = '计算机系'
WHERE Sno="0005";

[4] 将信息系所有学生的身高增加5cm

UPDATE student 
SET Height = Height + 0.05
WHERE 系名="信息系";

[5] 将计算机系全体学生的成绩清零

UPDATE student,score
SET score.Grade = 0
WHERE student.Sno = score.Sno AND
student.系名="计算机系";

[6] 删除信息系所有学生的选课记录

Delete From Score
Where Sno in( Select b.* FROM 
(SELECT score.Sno FROM student,score 
WHERE student.Sno = score.Sno AND student.系名 = '信息系') b
);

[7] 查询与“刘一平”来自同一个系的学生姓名

SELECT Sname
FROM student
WHERE student.系名 = 
(SELECT 系名
FROM student
WHERE Sname="刘一平");

[8] 查询其它系中’0002’ 课程比信息系所有学生分数高的学生学号和姓名

SELECT student.Sno, student.Sname
FROM student,score
WHERE score.Sno=student.Sno AND score.Cno="0002" AND
student.系名<>"信息系" AND
score.Grade >(SELECT MAX(score.Grade)
FROM score,student
WHERE score.Sno = student.Sno AND student.系名 = "信息系");

[9] 查询其它系中比信息系所有学生年龄大的学生姓名和性别

SELECT Sname, Gender
FROM student
WHERE 系名<>"信息系" AND Birthday<(
SELECT MAX(Birthday)
FROM student
WHERE 系名="信息系");

[10] 查询“信息系”中选课最多的学生学号

SELECT student.Sno
FROM student, score
WHERE student.Sno = score.Sno AND 系名="信息系"
GROUP BY student.Sno
HAVING COUNT(score.Cno) = 
MAX( (SELECT COUNT(*)
FROM student,score
WHERE student.Sno = score.Sno and 系名="信息系"
GROUP BY student.Sno) );

[11] 查询每门课程中低于该课程平均成绩的学生学号和姓名

SELECT DISTINCT student.Sno, student.Sname
FROM student,score s1
WHERE student.Sno = s1.Sno AND s1.Grade<
(SELECT AVG(s2.Grade)
FROM score s2
WHERE s1.Cno=s2.Cno);

[12] 统计每个系学生的平均年龄，并创建新表，同时把统计结果存入新表中

CREATE TABLE stu_age(
Sdept VARCHAR(20),
Sage INT
);

INSERT INTO stu_age (Sdept, Sage) SELECT	
`系名`,
AVG(TIMESTAMPDIFF(YEAR, Birthday, CURDATE()))
FROM	
student
GROUP BY
`系名`;

SQL语句第四次作业

[1] 定义视图

(1) 定义信息系学生基本情况视图 V_IS

CREATE VIEW V_IS
as 
SELECT *
FROM student
WHERE 系名="信息系";

(2) 将 Student，Course 和 SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图 V_S_C_G

CREATE VIEW V_S_C_G
AS
SELECT student.Sno, student.Sname, 
course.Cno, course.Cname, score.Grade
FROM student,score,course
WHERE student.Sno = score.Sno 
AND course.Cno = score.Cno;

(3) 将各系学生人数，平均身高定义为视图 V_NUM_AVG

CREATE VIEW V_NUM_AVG
AS
SELECT COUNT(系名), AVG(Height)
FROM student
GROUP BY 系名;

(4) 定义一个反映学生出生年份的视图 V_YEAR [即出生不同年份的学生人数总数]

CREATE VIEW V_YEAR
AS
SELECT COUNT(Birthday)
FROM student
GROUP BY YEAR(Birthday);

(5) 将各位学生选修课程的门数及平均成绩定义为视图 V_AVG_S_G

CREATE VIEW V_AVG_S_G
AS
SELECT COUNT(score.Cno), AVG(score.Grade)
FROM student,score
WHERE student.Sno = score.Sno
GROUP BY student.Sno;

(6) 将各门课程的选修人数及平均成绩定义为视图 V_AVG_C_G

CREATE VIEW V_AVG_C_G
AS
SELECT COUNT(score.Sno),AVG(score.Grade)
FROM score
GROUP BY Cno;

(7) 将各位学生学号、姓名，已选修课程的名称、学分及其已取得的绩点定义为视图V_S_GPA

现已知成绩绩点的计算方式如下：

create view V_S_GPA
as
(select student.sno,sname,Course.Cno, Cname, credit,(case 
when(Grade>=90) then 4 
when(Grade >=85 and Grade <90) then 3.7
when(Grade >=82 and Grade <85) then 3.3 
when(Grade >=78 and Grade <82) then 3
when(Grade >=75 and Grade <78) then 2.7
when(Grade >=71 and Grade <75) then 2.0
when(Grade >=66 and Grade <71) then 1.7
when(Grade >=62 and Grade <65) then 1.3
when(Grade >=60 and Grade <61) then 1.0
else 0
end) as gpa from Score,Student,Course
where Score.Cno=Course.Cno and Student.Sno= Score.Sno);

[2] 使用视图

(1) 查询以上所建的视图结果

SELECT *
FROM v_avg_c_g, v_avg_s_g,v_is,v_num_avg,v_s_c_g,v_s_gpa,v_year;

(2) 查询平均成绩为 90 分以上的学生学号、姓名和平均成绩

SELECT student.Sno,student.Sname,v_avg_s_g.`AVG(score.Grade)`
FROM student, v_avg_s_g
WHERE student.Sno = v_avg_s_g.Sno AND
v_avg_s_g.`AVG(score.Grade)`>90 ;

(3) 查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩

SELECT student.Sno, student.Sname, course.Cname,score.Grade
FROM student,score,course,v_avg_c_g
WHERE student.Sno = score.Sno AND score.Cno=course.Cno
AND course.Cno = v_avg_c_g.Cno AND
score.Grade>v_avg_c_g.`AVG(score.Grade)`
GROUP BY student.Sno
HAVING COUNT(score.Sno)=(SELECT COUNT(Cno) FROM course);

(4) 按系统计各系平均成绩在 80 分以上的人数，结果按降序排列

SELECT 系名,COUNT(student.Sno)
FROM student,v_avg_s_g
WHERE student.Sno = v_avg_s_g.Sno 
AND student.Sno = v_avg_s_g.Sno AND v_avg_s_g.`AVG(score.Grade)`>80
GROUP BY student.系名
ORDER BY COUNT(*) DESC;

(5) 查询平均绩点在 2.5 分以上的学生信息，结果按系排列显示

注：平均绩点GPA是国际通用的学生学习质量评定标准，其计算公式如下：课程学分1绩点+课程学分2绩点+…+课程学分n*绩点)/(课程学分1+课程学分2+…+课程学分n)

SELECT student.*
FROM student,v_s_gpa
WHERE student.Sno = v_s_gpa.sno
GROUP BY student.Sno
HAVING AVG(v_s_gpa.gpa)>2.5
ORDER BY `系名`;

[3] 修改视图

(1) 通过视图V_IS，分别将学号为“0001”和“0004”的学生姓名更改为“刘二平”和“马西”，并查询结果

UPDATE v_is
set Sname="刘二平"
WHERE Sno="0001";
UPDATE v_is
SET Sname="马西"
WHERE Sno="0004";
SELECT * 
FROM v_is;

(2) 通过视图 V_IS，新增加一个学生记录 (‘1001’,‘韩磊’,“男”,1992-01-23, ‘IS’)，并查询结果

INSERT
INTO v_is
VALUES('1001','韩磊',"男","1992-01-23", 'IS', 1.75, "信息系");
SELECT *
FROM v_is;

(3) 通过视图 V_IS，删除学号为 “0003”的学生信息，并查询结果

DELETE 
FROM v_is
WHERE Sno="0003";
SELECT *
FROM v_is;

(4) 要通过视图V_S_C_G，将学号为“S12”的姓名改为“S12_MMM”，是否可以实现？并说明原因

UPDATE v_s_c_g
set Sno="0005_2"
WHERE Sno="0001";

在这里不可以，因为原表中，Sno的长度只有4，将姓名修改为“S12_MMM“其长度已经超过此数据类型的范围，因此运行程序也会报错，因此不可以。

(5) 要通过视图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，是否可以实现？并说明原因

不行，无法进行更新因为系统无法修改各科成绩以使平均成绩为90分。

参考

数据库系统概论（第5版）——王珊 萨师煊
大二上数据库自己做的作业（SQL自己做的难免可能有错，欢迎批评指正，希望直接代码砸脸上！）