数据库实验二--学生选课数据库（OpenGuess）

实验二要求：

实验二 实验内容：

创建数据库、表和索引

（一）实验目的：

1. 掌握如何在OpenGauss中创建用户、数据库、模式、表和索引等。
2. 灵活运用SQL语句建立上述概念。
3. 理解数据库用户、数据库、schema、表、索引之间的关系。

（二）实验内容：

1. 创建数数据库、模式、表和索引。
2. 修改基本表的结构。 在OpenGauss数据库系统中使用SQL语句（按教材上所示的属性）建立基本表，如STUDENT表、COURSE表、SC表等，并向表中填充数据。熟悉使用虚拟机进行OpenGauss中SQL语句的代码输入方式。
3. 创建和删除索引 使用SQL语句在上述表中创建索引，熟悉相应的SQL语句，请参考教材P73-79页的SQL语句，请注意标准SQL和openGauss中相应SQL语句相同与不同之处。
4. 操作系统：Windows系统或者openEuler。
5. 数据库管理系统：OpenGauss。

学生选课数据库的数据：

（书中数据有不完善的地方，在输入数据时部分有修改）

过程

打开数据库

首先切换到omm用户进行操作（参照了华为实验指导书，使用omm用户操作数据库）

su - omm

使用gs_om启动服务

gs_om -t start

使用gsql连接数据库

gsql -d postgres -p 26000 -r

其中，postgres为openGauss安装完成后默认生成的数据库。初始可以连接到此数据库进行新数据库的创建。26000为数据库主节点的端口号，需根据openGauss的实际情况做替换，请确认连接信息获取。

引申信息：
使用数据库前，需先使用客户端程序或工具连接到数据库，然后就可以通过客户端程序或工具执行SQL来使用数据库了。
gsql是openGauss数据库提供的命令行方式的数据库连接工具。

连接数据库之后我们正式开始实验。

新建数据库

我们首先创建选课系统的数据库

创建数据库之前我们为即将要创建的数据库创建一个表空间``

CREATE TABLESPACE c_c RELATIVE LOCATION 'tablespace/c_c';

我们创建了一个叫做c_c的表空间，我们可以使用

SELECT spcname FROM pg_tablespace;

来查看表空间

接着我们去创建数据库，并让这个数据库使用c_c这个表空间：

CREATE DATABASE db_cc WITH TABLESPACE = c_c;

之后使用

\c db_cc

来进入到db_cc数据库

创建模式和数据表

我们接着可以创建模式了，本实验我们要创建模式S_C,并且该模式下有六个关系（详见上图）
Departments、Teachers、Students、Courses、Teaches、SC
模式允许多个用户
创建模式的语句为：

CREATE SCHEMA＜模式名＞ ［＜模式元素＞］

或

CREATE SCHEMA[＜模式名＞］AUTHORIZATION ＜用户名＞

我们也可以在创建模式的同时创建该模式中的对象。

我们这里先直接新建模式：

CREATE SCHEMA S_C;

接着我们创建该模式下的信息表；
由实验要求得，我们共计要创建六个表，我们在创建的时候暂时忽略表约束条件，创建完成之后再进行修改。

CREATE TABLE Teachers 
( 
    Tno                 char(7) PRIMARY KEY, 
    Thame               char(10) NOT NULL,
    Sex                 char(2) CHECK(Sex = '男' OR Sex = '女'),
	Birthday            DATE,
    Title               char(6),
	Dno                 CHAR(4)
); 

CREATE TABLE Departments 
( 
    Dno                 char(4) PRIMARY KEY, 
    Dhame               char(10),
    Dheadno             char(7)
); 

CREATE TABLE Students
( 
    Sno                 char(9) PRIMARY KEY, 
    Shame               char(10) NOT NULL,
	Birthday            DATE,
    Enrollyear          char(4),
	Speciality          char(20),
	Dno                 char(4)；
	 Sex                 char(2)
); 

注：本处我把性别的约束删除了，与课本上有了一些出入，倘若加上性别约束也不影响结果，但是请注意各列的顺序。

CREATE TABLE Courses 
( 
    Cno                 char(5) PRIMARY KEY, 
    Cname               char(20) NOT NULL,
    Period              SMALLINT,
	Credit              SMALLINT
); 

CREATE TABLE Teaches 
( 
    Tno                 char(7) , 
    Cno               char(5),
    TCsores             SMALLINT,
	PRIMARY KEY (Tno, Cno)
); 

CREATE TABLE SC 
( 
    Sno                 char(9), 
    Cno               char(5),
    Grade              SMALLINT CHECK(Grade >0 AND Grade <=100),
	PRIMARY KEY (Sno, Cno),
	FOREIGN KEY (Sno) REFERENCES Students (Sno),
	FOREIGN KEY (Cno) REFERENCES Courses (Cno)
); 

创建完成后我们可以使用\dt来查看刚刚创建的数据表：

现在，我们已经新建了六个数据表并为其添加了合适的约束条件。

接着我们按照实验要求为其添加数据库数据。

再插入数据的时候发现之前定义的字符长度有些不够，会出现报错，例如：

我们可以更改字段长度来避免这个问题：

 ALTER table 表名
 modify 需要修改列名 char(100);

之后我们添加数据：

INSERT INTO teachers VALUES 
(05001, '张海峰','1969-02-21','教授','CS', '男'),
(05002, '刘晓明','1978-10-06','副教授','CS','男'),
(05003, '王梅','1972-02-25','副教授','CS','女'),
(05004, '赵青山','1981-05-18','讲师','CS','男'),
(06001, '朱杰','1963-01-30','教授','MA','男'),
(06002, '欧阳梅','1970-08-11','副教授','MA','女');


insert into courses values
('cs101' ,'计算科学导论',2,1),
('cs102','C语言',4,3),
('cs201','数据结构',4,3),
('cs202','离散数学',4,3),
('cs301','数据库系统原理',4,3);

insert into departments values
('CS' ,'计算机系',05001),
('MA','数学系',06001),
('PH','物理系',07001),
('CH','化学系',08001);



 insert into students values
(201705001,'张华' ,'男','1999_12_16','计算机','cs'),
(201705002,'李玉','女','1998_10_17','计算机','cs'),
(201705003,'欧阳山','男','1999_05_24','计算机','cs'),
(201706001,'林艳','女','1998_09_20','数学','ma'),
(201706002,'高山','男','1997_08_16','数学','ma'),
(201706003,'王海洋','男','1997_01_30','数学','ma');


insert into sc values
(201705001 ,'cs101',89),
(201705002,'cs101',91),
(201705003,'cs101',80),
(201705001,'cs102',90),
(201705002,'cs102',87),
(201705003,'cs102',82),
(201705001 ,'cs201',88),
(201705002,'cs201',89),
(201705003,'cs201',90),
(201705001,'cs202',86),
(201705002,'cs202',85),
(201705001,'cs301',92);


insert into teaches values
(05001 ,'cs101',95),
(05002,'cs101',92),
(05003,'cs101',90),
(05004,'cs101',95);

还需要注意的是我们刚刚还没有添加表约束条件，我们在这里添加约束条件：

我们现在通过修改表来添加表级约束。

ALTER TABLE departments
ADD FOREIGN KEY (Dheadno) REFERENCES Teachers(Tno);

ALTER TABLE teachers
ADD FOREIGN KEY (Dno) REFERENCES departments(Dno);

ALTER TABLE students
ADD FOREIGN KEY (Dno) REFERENCES departments(Dno);

ALTER TABLE teaches
ADD FOREIGN KEY (Cno) REFERENCES Courses(Cno);

ALTER TABLE teaches
ADD FOREIGN KEY (Tno) REFERENCES teachers(Tno);

查询

全部完成后我们可以使用selecr语句来查询

建立索引

CREATE INDEX 索引名 ON 表名 (列名);

CREATE INDEX Student_Dept ON Students (Dno);

使用\di来查看索引；
DBMS会自动为主码创建索引。

---

---

更新：
做完试验后发现几个表的列名name被打成了hame，一不小心就打错了，可以再改一下。

openGauss修改列名的指令和mysql的指令不太一样：

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
    RENAME [ COLUMN ] column_name TO new_column_name;

例如：

alter table students rename shame to sname;