[数据库管理系统]利用SQL语言建立数据库的实例-----SCT(1)

01ADH

已于 2024-10-30 17:15:19 修改

阅读量978

点赞数 13

分类专栏：数据库管理文章标签：数据库 SQL postgresql

于 2024-10-29 17:05:07 首次发布

本文链接：https://blog.youkuaiyun.com/jasonlu888/article/details/143305056

版权

数据库管理专栏收录该内容

1 篇文章

订阅专栏

前言：

今天早上终于成功安装pgAdmin4并且成功连接账户，本文将以哈尔滨工业大学的MOOC课程：《数据库系统》中提及的学生选课系统SCT为例出发基于postgresql实现这个数据库。相信基于这个数据库的实现的实例可以掌握数据库操作的基本技术以及一些细节

一、对数据库SCT的介绍：

SCT就是学生选课系统：在现实中比较常见，下面是他包含的一些常见的关系：

1.Course关系：

Course(C# char(3),Cname char(12),Chours integer,Credit float(1),T#char(3))

在这个表中的列名有：课号C#，课程名Cname，教师编号T#，学时Chours，学分Credit。

2.Teacher关系：

Teacher(T# char(3),Tname char(10),D# char(2),Salary float(2))

在这个表中的列名有：教师编号T#，教师名Tname,所属院系D#,以及工资Salary

3.SC关系（选课关系）：

SC(s# char(8),C#char(3),Score float(1))

在这个表的列名含有学号S#，课号C#，成绩Score。

4.Student关系：

Student(S# char(8),Sname char(10),Ssex char(2),Sage integer,D# char(2),Sclass char(6))

在这个表中的列名有：学号S#，姓名Sname，性别Ssex，年龄Sage，所属系别D#，以及班级Sclass。

5.Dept(院系):

Dept(D# char(2),Dname char(10),Dean char(10))

在这个表的列名有：系别D#，系名Dname，系主任Dean。

我们的数据库 SCT围绕这五个表来实现。

二、建立数据库：

一句话来讲解:定义数据库和表，再向表中追加元组，随后就可进行一系列的操作。

1.建立数据库：

（1).在pgAdmin4中实现:

我们选择在pgAdmin4中创建数据库：

第一步在左侧栏选择数据库并且右键：

第二步：选择新建数据库：

第三步：为数据库命名并且保存：

(2).在SQL shell中创建：

首先连接服务器：

利用创建数据库的语句：CREATE DATDABASE "数据库名字";

返回显示：CREATE DATABASS;

最后我们在pgAdmin4界面看见我们新创建的两个数据库:

2.创建表：

数据库创建表的基本格式主要包括表名、列名、数据类型以及可选的约束条件和默认值等。以下是对数据库创建表的基本格式的介绍：

表名：表名是表的唯一标识符，用于在数据库中区分不同的表。在命名时，应遵循数据库的命名规则，避免使用保留字或特殊字符。
列名：列名是表中每列的名称，用于描述该列的数据内容。列名同样需要遵循数据库的命名规则，并确保在同一表中唯一。
数据类型：数据类型定义了表中每列可以存储的数据的种类，如整型（INT）、浮点型（FLOAT）、字符串（VARCHAR）等。选择合适的数据类型对于保证数据的完整性和查询效率至关重要。
约束条件：约束条件用于限制表中数据的有效性和一致性。例如，主键（PRIMARY KEY）用于唯一标识表中的每一行记录；外键（FOREIGN KEY）用于建立表之间的关联关系；非空（NOT NULL）约束确保列中的值不能为NULL。
默认值：默认值是在插入记录时，如果未指定某列的值，则自动赋予该列的值。默认值可以是常量值，也可以是表达式或函数的返回值。
存储引擎：存储引擎定义了表的物理存储方式和数据存取方法。不同的存储引擎具有不同的特点和性能表现。
字符集：字符集定义了表中字符数据的编码方式，如UTF-8、GBK等。选择合适的字符集对于支持多语言和符号的存储至关重要。

下面给出在数据库SCT中的主键和外键的设计：

Course 表: C# 作为主键，T# 作为外键引用 Teacher 表。

Teacher 表: T# 作为主键，D# 作为外键引用 Dept 表。

SC 表: (S#, C#) 作为复合主键，分别引用 Student 表和 Course 表。

Student 表: S# 作为主键，D# 作为外键引用 Dept 表。

Dept 表: D# 作为主键。

(1)在pgAdmin4中实现：

第一步在左侧栏选中：架构->public->右键创建->选择表

第二步设计表名（以Student为例），选择是否为空？是否为主键？

在左侧可以看见

接下来依次将其余的表创建：

(2).在SQL shell中实现：

以创建表Student为例

CREATE TABLE IF NOT EXISTS public."Student"
(
    "S#" "char" NOT NULL,
    "Sname" "char" NOT NULL,
    "Ssex" "char" NOT NULL,
    "Sage" "char" NOT NULL,
    "D#" "char" NOT NULL,
    "Sclass" "char",
    CONSTRAINT "Student_pkey" PRIMARY KEY ("S#")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Student"
    OWNER to postgres;

3.向表中追加元组：

由于在pgAdmin4中也可以使用SQL语句来追加元组，所以本小节只在pgAdmin4中展示：

(1).使用SQL语句追加元组：

第一步先找到目标数据库，再右键选择查询工具：如下图所示。

第二步在查询工具中编写SQL语句，随后进行执行即可。下面介绍insert into简单语法

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
#注意的是值要与列名一一对应；

INSERT INTO table_name (column1, column2, column3, ...)
VALUES

(value1, value2, value3, ...)，

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

(value1, value2, value3, ...);

#也可以一次添加多个行

INSERT INTO tablebB(columnb1,columnb2, columnb3, columnb4)
SELECTcolumna1,columna2, columna3, columna4 FROM tableA;

#还可以从另一个表中选择数据并插入到目标表中

下面给出一个实例：

我在插入的过程中遇到问题：明明在左侧栏可以看到自己创建好的Table，但是在插入数据的时候总显示Table不存在？解决方法：在被查询的表加一对双引号即可！！！

在左侧栏查看关系Crouse如下图所示：

(2)使用UI追加元组：

①打开数据编辑视图：在 pgAdmin4 中，导航到目标数据库和表。右键点击表名，选择“查看和编辑数据”->所有行：

②添加新记录：在打开的数据编辑视图中，你会看到一个表格，其中列出了表中的所有现有记录。在表格底部，你会看到一个空白行，可以在这里输入新的数据：

点击左上角的添加行操作：

选中对应位置后回车即可编辑

③保存更改：输入完新记录后，点击工具栏上的“Save”按钮（通常是一个磁盘图标），以保存更改；

4.利用SQL语言进行简单查询：

(1).简单查询 :

简单查询方法：

SELECT * FROM table_name;#对表的所有列进行查询

SELECT column1, column2 FROM table_name;#对表的指定的行进行查询

SELECT * FROM table_name WHERE condition;#条件筛选

SELECT * FROM table_name ORDER BY column1 [ASC|DESC];

#选择列并且排序ASC是升序，DESC是降序。

(1).使用SQL在pgAdmin4进行查询：

对表的所有列进行查询：

对表的指定行进行选中查询：

条件筛选查询：

排序查询：

升序（其中ASC可以省略）：

降序查询：

(2).高级查询：

①模糊查询：

在SQL中，模糊查询通常使用 LIKE 运算符来实现。LIKE 运算符允许你通过模式匹配来查找符合特定条件的记录。常用的通配符包括：

%：表示零个或多个字符。

_：表示单个字符。

其格式为：SELECT * FROM table_name
WHERE column LIKE 通配符语句;

1. 查找以特定字符串开头的记录：'特定字符%';

2. 查找以特定字符串结尾的记录: '%特定字符’；

3. 查找包含特定字符串的记录：'%特定字符%';

4. 查找特定位置的字符: :'_特定字符’;#这里是查找特定字符在第二个位置。

②多表联合查询：

在SQL中，多表联合查询（也称为联接查询）用于从多个表中检索数据。常见的联接类型包括内联接（INNER JOIN）、左外联接（LEFT JOIN）、右外联接（RIGHT JOIN）和全外联接（FULL OUTER JOIN）

1.内连接：内联接返回两个表中满足联接条件的记录。如果某行在任一表中没有匹配的行，则该行不会出现在结果集中。

2.左外连接：左外联接返回左表中的所有记录以及右表中满足联接条件的记录。如果右表中没有匹配的行，则结果集中的右表列将包含NULL。

3.右外连接：右外连接返回右表中的所有记录以及左表中满足联接条件的记录。如果左表中没有匹配的行，则结果集中的左表列将包含NULL。

4.全外连接：全外连接返回两个表中所有记录。当某一行在任一表中没有匹配的行时，结果集中的相应列将包含NULL。

内连接：

语法格式：

SELECT 列名#注意一些细节：

FROM 表名

INNER JOIN 表名 ON 连接条件

左外连接：

SELECT 列名
FROM 表名#第一个表为左表，
LEFT JOIN 表名 ON 连接条件;

更进一步，我们可以加入简单查询的语句，例如只选择课程号为001的元组。

右外连接，全外连接的SQL语句仅仅将上述的left分别修改为：right、full outer即可。

或者采用更古老的形式（在MOOC所使用的方法）

select 列名1,列名2......

from 表名1，表名2.....

where 检索条件：

这里的检索条件可以实现等值连接，不等值连接。

练习题目：（所有的解决均使用JOIN显示连接方法）

按”001“课程号成绩由高到低的顺序显示所有学生的姓名以及成绩？

SELECT "Sname","Score"
FROM "Student"
INNER JOIN "SC" ON "Student"."S" = "SC"."S"
WHERE "SC"."C" = '001'
ORDER BY "Score" DESC;

我们来从关系代数来分析这个过程：

按照数据库课程成绩由高到低显示所有学生的姓名和成绩？

SELECT "Student"."Sname", "SC"."Score"
FROM "Student"
JOIN "SC" ON "Student"."S" = "SC"."S"
JOIN "Course" ON "SC"."C" = "Course"."C"
WHERE "Course"."Cname" = '数据库'
ORDER BY "SC"."Score" DESC;