期末复习-数据库原理

关系代数

概念

关系代数是一种数学工具和查询语言，用于处理和操作关系型数据库中的关系数据。它提供了一组操作符，用于对关系进行操作和组合，以获取所需的数据结果。

关系代数的基本操作符包括：

选择（Selection）：根据指定的条件从关系中选择满足条件的元组。
投影（Projection）：从关系中选择指定的属性列，生成一个新的关系。
连接（Join）：将两个关系根据共享的属性列合并成一个新的关系。
交（Intersection）：获取两个关系的交集，即共有的元组。
并（Union）：获取两个关系的并集，即合并两个关系的元组，去除重复项。
差（Difference）：获取一个关系中有而另一个关系中没有的元组。
除（Division）：根据指定条件从一个关系中选择满足条件的元组，然后从另一个关系中选择与前一步结果关系中的所有元组匹配的元组。

通过组合和嵌套这些基本操作符，可以进行更复杂的查询和操作，从而实现对关系数据库中的数据的有效管理和检索。关系代数是关系数据库理论的基础之一，它提供了一种形式化的方式来描述和操作关系数据库中的数据。

以下是关系代数基本操作的常用符号表示：

选择（Selection）：用σ（sigma）符号表示。例如，σ<条件>(关系) 表示选择满足指定条件的元组。
投影（Projection）：用π（pi）符号表示。例如，π<属性列表>(关系) 表示从关系中选择指定的属性列。
连接（Join）：用⨝（join）符号表示。例如，关系1 ⨝ 关系2 表示根据共享的属性列将两个关系进行连接。
交（Intersection）：用∩（intersection）符号表示。例如，关系1 ∩ 关系2 表示获取两个关系的交集，即共有的元组。
并（Union）：用∪（union）符号表示。例如，关系1 ∪ 关系2 表示获取两个关系的并集，即合并两个关系的元组，去除重复项。
差（Difference）：用-（minus）符号表示。例如，关系1 - 关系2 表示获取一个关系中有而另一个关系中没有的元组。
除（Division）：用÷（division）符号表示。例如，关系1 ÷ 关系2 表示根据指定条件从一个关系中选择满足条件的元组，然后从另一个关系中选择与前一步结果关系中的所有元组匹配的元组。

这些符号表示了关系代数中的基本操作，可以根据需要进行组合和嵌套，以实现更复杂的查询和操作。

训练场

1 2	Emp(E#,Ename,Age,Salary,D#); Depa(D#,Dname,Mgr#);

检索每个部门经理的工资（显示部门编号，部门名称，经理编号，经理姓名，经理工资）：
1
π D#, Dname, Mgr#, Ename, Salary (σ Emp.E# = Depa.Mgr# ∧ Emp.D# = Depa.D# (Emp ∞ Depa))
检索部门名称为“销售科”的职工工号，姓名，工资：
1
π E#, Ename, Salary (σ Dname = "销售科" (Emp ∞ Depa))

已知一关系数据库的模式如下：
供应商关系：

1	S(Sno,Sname,City);

零件关系：

1	P(Pno,Pname,Color,Weight);

工程关系：

1	J(Jno,Jname,City);

供货关系：

1	SPJ(Sno,Pno,Jno,Qty);

用关系代数完成下列查询：

查询为工程’J1’供货的供应商名：

SELECT Sname
FROM S
WHERE Sno IN (
    SELECT Sno
    FROM SPJ
    WHERE Jno = 'J1'
)

查询供应’螺母’零件的供应商名和所在城市：

SELECT Sname, City
FROM S
WHERE Sno IN (
    SELECT Sno
    FROM SPJ
    WHERE Pno IN (
        SELECT Pno
        FROM P
        WHERE Pname = '螺母'
    )
)

查询工程’J2’不使用的零件名和颜色：

SELECT Pname, Color
FROM P
WHERE Pno NOT IN (
    SELECT Pno
    FROM SPJ
    WHERE Jno = 'J2'
)

SQL语言

概念

SQL（Structured Query Language）是一种用于管理关系型数据库系统的编程语言。它是一个标准化的语言，用于存储、检索、操作和管理数据库中的数据。

SQL提供了一组用于执行各种操作的命令和语句，包括以下主要方面：

数据定义语言（Data Definition Language，DDL）：用于创建、修改和删除数据库对象（例如表、视图、索引等）的语句。DDL语句包括CREATE、ALTER和DROP等。
数据操作语言（Data Manipulation Language，DML）：用于操作和处理数据库中的数据的语句。DML语句包括SELECT、INSERT、UPDATE和DELETE等。
数据控制语言（Data Control Language，DCL）：用于控制数据库的安全性和访问权限的语句。DCL语句包括GRANT和REVOKE等。
数据查询语言（Data Query Language，DQL）：用于从数据库中检索数据的语句。DQL语句主要是SELECT语句，用于查询满足指定条件的数据。

SQL语言的特点包括：

面向集合：SQL处理数据的基本单位是集合，即表中的行。
声明式语言：SQL是一种声明式语言，用户只需描述所需的结果，而不需要指定具体的操作步骤。
高度可扩展：SQL支持在单个查询中使用多个表进行联接，执行复杂的数据操作和聚合运算。
广泛应用：SQL是关系型数据库系统的标准查询语言，几乎所有主流的关系型数据库管理系统（如MySQL、Oracle、SQL Server等）都支持SQL语言。

通过SQL语言，用户可以方便地管理和操作数据库中的数据，执行各种查询、更新和管理任务。

训练场

这里存在数据库，结构为

Student(No,name,sex,age,class);
Course(Cno,cname,Tno);
Teacher(Tno,name,sex,prof,dept);
Score(No,Cno,Grade);

查询选修课程3-105且成绩在60到80之间的所有记录：

1
2
3

SELECT *
FROM Score
WHERE Cno = '3-105' AND Grade BETWEEN 60 AND 80;

查询成绩为85,86或88的记录：

1
2
3

SELECT *
FROM Score
WHERE Grade IN (85, 86, 88);

查询95031班的学生人数：

1
2
3

SELECT COUNT(*)
FROM Student
WHERE Class = '95031';

查询至少有5名同学选修的并以3开头的课程的平均成绩：

SELECT AVG(Grade)
FROM Score
WHERE Cno LIKE '3%'
GROUP BY Cno
HAVING COUNT(*) >= 5;

查询最低分大于70，最高分小于90的NO：

SELECT No
FROM Score
GROUP BY No
HAVING MIN(Grade) > 70 AND MAX(Grade) < 90;

查询95033班学生所选课程的平均分：

SELECT AVG(Grade)
FROM Score, Student
WHERE Score.No = Student.No AND Class = '95033'
GROUP BY No;

查询选修3-105课程的成绩高于109号同学成绩的所有同学的记录：

1
2
3

SELECT *
FROM Score
WHERE Cno = '3-105' AND Grade > (SELECT Grade FROM Score WHERE Cno = '3-105' AND No = '109');

查询与学号为108的同学同岁的所有学生的NO, Name, Age：

1
2
3

SELECT No, Name, Age
FROM Student
WHERE Age = (SELECT Age FROM Student WHERE No = '108');

查询张旭教师任课的课程号及选修其课程的学生的学号和成绩：

1
2
3

SELECT No, Grade
FROM Score
WHERE Cno IN (SELECT Cno FROM Course WHERE Tno = (SELECT Tno FROM Teacher WHERE name = '张旭'));

1
2
3

SELECT No, Grade
FROM Score, Teacher, Course
WHERE Teacher.Tno = Course.Tno AND Course.Cno = Score.Cno AND Teacher.name = '张旭';

查询选修某课程的学生人数多于30人的教师姓名：

SELECT Name
FROM Teahcer
WHERE Tno IN (
   SELECT Tno
   FROM Course
   WHERE Cno IN (
       SELECT Cno
       FROM Score
       GROUP BY Cno
       HAVING COUNT(No) > 30
   )
);

SELECT Name
FROM Techaer, Score, Course
WHERE Techaer.Tno = Course.Tno
   AND Course.Cno = Score.Cno
GROUP BY Cno
HAVING COUNT(*) > 30;

查询计算机系与电子工程系不同职称的教师姓名和职称：

SELECT name, prof
FROM Teacher
WHERE Dept = '电子工程系' AND Prof NOT IN (
   SELECT Prof
   FROM Teacher
   WHERE Dept = '计算机系'
);

查询选修编号为3-105课程且成绩至少高于选修编号为3-234课程的同学的课号、学号、成绩，并按成绩降序排列：

SELECT No, grade
FROM Score
WHERE Cno = '3-105' AND Grade > ANY (
   SELECT grade
   FROM Score
   WHERE Cno = '3-234'
)
ORDER BY grade DESC;

查询选修编号为3-105课程且成绩高于选修编号为3-234课程的同学的课号、学号、成绩，并按成绩降序排列：

SELECT No, grade
FROM Score
WHERE Cno = '3-105' AND Grade > ALL (
   SELECT grade
   FROM Score
   WHERE Cno = '3-234'
)
ORDER BY grade DESC;

列出所有老师和同学的姓名、性别和年龄：

SELECT Name, Sex, Age
FROM teacher
UNION
SELECT Name, Sex, Age
FROM student;

查询成绩比该课平均成绩低的学生的成绩表：

SELECT No, Grade
FROM Score
WHERE Grade < (
   SELECT AVG(Grade)
   FROM Score AS y
   WHERE Score.Cno = y.Cno
   GROUP BY Cno
);

列出所有任课教师的Name和Dept：

SELECT Name, Dept
FROM Teacher
WHERE Tno IN (
   SELECT Tno
   FROM Course
);

SELECT Name, Dept
FROM Teahcer AS A
WHERE EXISTS (
   SELECT *
   FROM Course AS B
   WHERE A.Tno = B.Tno
);

列出所有未讲课教师的Name和Dept：

SELECT Name, Dept
FROM Teacher
WHERE Tno NOT IN (
   SELECT Tno
   FROM Course
);

SELECT Name, Dept
FROM Teahcer AS A
WHERE NOT EXISTS (
   SELECT *
   FROM Course AS B
   WHERE A.Tno = B.Tno
);

列出至少有2名男生的班级编号：

SELECT Class
FROM student
WHERE sex = '男'
GROUP BY Class
HAVING COUNT(*) >= 2;

查询不姓王的学生记录：

1
2
3

SELECT *
FROM student
WHERE Name NOT LIKE '王%';

查询每门课最高分的同学的No, Cno, Grade：

SELECT No, Cno, Grade
FROM Score A
WHERE Grade = (
   SELECT MAX(Grade)
   FROM Score B
   WHERE A.Cno = B.Cno
);

SELECT No, Cno, Grade
FROM Score A
WHERE A.Cno = B.Cno AND Grade = (
   SELECT MAX(Grade)
   FROM Score B
   GROUP BY Cno
);

查询与“李军”同性别并同班的同学姓名：

SELECT Name
FROM Student
WHERE Class = (
   SELECT Class
   FROM Student
   WHERE Name = '李军'
)
AND sex = (
   SELECT sex
   FROM Student
   WHERE Name = '李军'
);

查询男教师及其所任的课程：

1
2
3

SELECT A.Name, B.Cname
FROM Teacher A, Course B
WHERE A.Tno = B.Tno AND A.sex = '男';

查询选修计算机导论课程的男同学的成绩表：

SELECT No, Cno, Grade
FROM Score
WHERE Cno IN (
   SELECT Cno
   FROM Course
   WHERE Cname = '计算机导论'
)
AND No IN (
   SELECT No
   FROM Student
   WHERE Sex = '男'
);

SELECT No, Cno, Grade
FROM Score A, Student B, Course C
WHERE A.No = B.No
AND A.Cno = C.Cno
AND C.Cname = '计算机导论'
AND B.Sex = '男';

在数据库中要求每个经理的工资大于2000元。试在Depa表的定义时，加一个Check子句，以满足上述要求。
1
ALTER TABLE Depa ADD CHECK (Mgr# IN (SELECT E# FROM Emp WHERE Salary > 2000))
更新Depa表，将经理编号（Mgr#）设置为null，满足条件：Depa表中的Mgr#等于Emp表中的E#，并且Emp表中的Age大于50。
1
UPDATE Depa SET Mgr# = NULL WHERE Mgr# IN (SELECT E# FROM Emp WHERE Age > 50)

创建一个年龄大于50岁的职工视图（D#, Dname, E#, Ename, Age, Salary）：

CREATE VIEW wyy AS
SELECT D#, Dname, E#, Ename, Age, Salary
FROM Emp, Depa
WHERE Emp.E# = Depa.Mgr# AND Emp.Age > 50;

用Create table 命令定义Depa表（需要定义主键和外键）

Create table Depa (
    D# char(10) primary key,
    Dname char(8),
    Mgr# char(15),
    Foreign key (Mgr#) references Emp(E#)
);

查询每个部门的职工人数和平均工资，要求显示（部门号，部门名称，职工人数，平均工资）：

SELECT D#, Dname, COUNT(*) as 职工人数, AVG(Salary) as 平均工资
FROM Depa, Emp
WHERE Depa.D# = Emp.D#
GROUP BY D#

在Depa表中删除人数少于10人的部门元组

DELETE FROM Depa
WHERE D# IN (
    SELECT D#
    FROM Emp
    GROUP BY D#
    HAVING COUNT(*) < 10
)

对于上述关系代数与SQL的示例可能存在不合理的部分，请跟据实际情况修改，毕竟，只有生产环境才是有真正明确需求的与真实的环境。

万用模版

查询模版

SELECT [列名]
FROM [表名]
[JOIN 子句]
WHERE [条件]
GROUP BY [列名]
HAVING [条件]
ORDER BY [列名] [ASC|DESC]
LIMIT [数量];

插入模版

1 2	INSERT INTO [表名] ([列1], [列2], ...) VALUES ([值1], [值2], ...);

修改模版

1
2
3

UPDATE [表名]
SET [列1] = [新值1], [列2] = [新值2], ...
WHERE [条件];

删除模版
1
2
DELETE FROM [表名]
WHERE [条件];
定义视图模版
1
2
3
4
CREATE VIEW [视图名] AS
SELECT [列1], [列2], ...
FROM [表名]
WHERE [条件];
- 解释说明：
  - [视图名]：指定要创建的视图的名称。
  - [列1], [列2], ...：指定要包含在视图中的列名。
  - [表名]：指定要从中提取数据的表名。
  - [条件]：可选项，用于筛选特定的数据行。
- 注意事项：
  - 方括号 [ ] 中的内容是需要替换为具体的值或表达式。
  - 视图是从一个或多个基本表派生出来的虚拟表，其内容是基于查询的结果集。因此，在定义视图时，需要使用合适的 SELECT
    语句来指定视图的内容。
建表模版
1
2
3
4
5
6
CREATE TABLE [表名] (
[列1] [数据类型] [约束],
[列2] [数据类型] [约束],
...
[列N] [数据类型] [约束]
);
- 解释说明：
  - [表名]：指定要创建的表的名称。
  - [列1], [列2], ..., [列N]：指定要创建的列名。
  - [数据类型]：指定每个列的数据类型，如 INT、VARCHAR、DATE 等。
  - [约束]：可选项，用于指定列级别的约束条件，如 PRIMARY KEY、NOT NULL、UNIQUE 等。
- 注意事项：
  - 方括号 [ ] 中的内容是需要替换为具体的值或表达式。
  - 每个列定义需要包含列名和数据类型。
  - 可以在每个列定义后面添加约束来进一步定义列的行为和限制。
  - 可以根据需要在表定义的末尾添加其他选项，如存储引擎、字符集等。
创建角色模版
1
CREATE ROLE '角色名';
- 解释说明：
  - '角色名'：指定要创建的角色名称，角色名称用单引号括起来。
- 注意事项：
  - 角色名称应遵循 MySQL 的命名规则，如不能包含特殊字符、空格等。
  - 创建角色需要具有适当的权限，通常是 CREATE ROLE 权限。
角色赋权模版
1
GRANT 权限列表 TO '角色名';
- 解释说明：
  - 权限列表：指定要授予给角色的权限，可以是单个权限或多个权限，多个权限之间使用逗号分隔。
  - '角色名'：指定要授予权限的角色名称，角色名称用单引号括起来。
- 注意事项：
  - 角色和权限都应存在于数据库中。
  - 授予权限需要具有适当的权限，通常是 GRANT 权限。
- 使用上述模板，你可以为角色赋予指定的权限。将 '角色名' 替换为实际的角色名称，将 权限列表
  替换为要授予的具体权限即可。如果需要授予多个权限，可以在 权限列表 中使用逗号分隔不同的权限项。
  示例：
  1
  GRANT SELECT, INSERT, UPDATE ON database.table TO '角色名';
  上述示例将 SELECT、INSERT 和 UPDATE 权限授予给名为 '角色名' 的角色。你可以根据实际情况调整权限列表和角色名称。
授权模版
1
GRANT [权限列表] ON [数据库].[表] TO '[用户名]'@'[主机]' [WITH GRANT OPTION];
- 解释说明：
  - [权限列表]：指定要授予的权限，可以是多个权限，用逗号分隔，如 SELECT, INSERT, UPDATE。
  - [数据库]：指定要授予权限的数据库名称。如果要授予全局权限，可以使用通配符 *。
  - [表]：可选项，指定要授予权限的表名。如果要授予数据库级别的权限，可以省略该部分。
  - '[用户名]'@'[主机]'
    ：指定要授予权限的用户和对应的主机。用户名和主机分别用单引号括起来。如果要授予全局权限，可以使用通配符 %。
  - WITH GRANT OPTION：可选项，允许被授权的用户进一步授予该权限给其他用户。
- 注意事项：
  - 方括号 [ ] 中的内容是需要替换为具体的值或表达式。
  - 权限可以是诸如 SELECT、INSERT、UPDATE、DELETE 等数据库操作的关键字。
  - 可以根据需要指定具体的数据库和表，或者使用通配符 * 表示所有数据库或表。
  - 授权操作需要具有适当的权限，通常是 GRANT OPTION 权限。

E-R图

概念

E-R图（实体-关系图）是一种用于数据建模的图形工具，用于描述现实世界中的实体、实体之间的关系以及实体属性的结构化图形化表示。它基于实体-关系模型，是一种高层次、抽象的表示方式，用于在数据库设计和开发过程中描述实体、关系和属性之间的概念和关联。

E-R图由实体、关系和属性组成，其中：

实体（Entity）代表现实世界中具有独立存在和可识别的事物，如人、物、地点或概念。每个实体都有一个唯一的标识符（主键），并具有属性描述实体的特征。
关系（Relationship）表示实体之间的联系或关联。关系可以是一对一、一对多或多对多的关系，用于描述实体之间的交互和依赖。
属性（Attribute）是实体的特征或描述，用于存储实体的具体数据。属性可以是简单属性（如姓名、年龄）或复合属性（如地址，由街道、城市和邮编组成）。

通过使用符号和连接线，E-R图提供了一种直观的方式来可视化和表示实体、关系和属性之间的结构和相互作用。它是数据库设计和规划的重要工具，有助于理解和沟通数据模型的概念，以便更好地设计和管理数据库系统。

例子

Student(id, name, gender)

Class(cid, name)

Teacher(tid, gender, name)

我们使用_来表述一张数据表中的主键字段。

图中连接表与表的线条，应该是干净的，不应该有类似于分支的部分，应该与连接表与字段的线条一致。

Orlando

相关

关系代数

概念

训练场

SQL语言

概念

训练场

万用模版

E-R图

概念

例子

你的赏识是我前进的动力