数据库实验一：基本表操作、基本数据查询和复杂数据查询

实验要求

1.基本表操作

• 按下图创建四个表：teacher、student、course和SC，为属性选择合适的域、合适的主码和外键约束，并为他们插入所列出数据；

	中文语义
teacher (TID,TNAME,DEPT,SALARY)	教师（教工号，姓名，系，薪水）
student (SID,SNAME,DEPT,AGE,GENDER)	学生（学号，姓名，系，年龄，性别）
course (CID,CNAME,DEPT,CREDITS,TEACHER)	课程（课程号，课程名，系，学分，任课教师）
SC (SID,CID,GRADE)	学习（学号，课程号，成绩）

• 在student表中，为姓名为’Zhang’且系信息错填为’Physics’的同学修改信息，将其系信息修改为’CS’;
• 删除teacher表中，属于Finance学院的教师信息;
• 在teacher表中，为工资低于或等于70000的教师增长10%的工资，为工资高于70000的教师增长5%的工资。

2.基本数据查询

1. 基于teacher表，找出“物理系Physics和生物系Biology”所有教师的名字和工资；
2. 基于teacher表，列出教师所在的各个系名，要求每个系名仅出现一次；
3. 基于teacher表，希望查看“若将每位教师的工资提高20%后”各位教师的姓名和工资是多少；
4. 基于表student、SC和course，查看到计算机系CS的每位学生所选课程的所有信息，包括学生姓名、所在系、课程名称、课程学分的情况。

3.复杂数据查询

1. 查询全体学生的姓名、年龄
2. 查询所有选修过课的学生的学号
3. 查询考试成绩低于60分的学生的学号
4. 查询年龄在20至23之间的学生姓名、性别和年龄
5. 查询所有姓liu的学生的学号、姓名和年龄
6. 查询学习C1课程的学生最高分数
7. 查询各个课程号与相应的选课人数
8. 查询选修C3课程的学生的姓名
9. 查询每一门课程的平均成绩。

（选做题）

在必做题的数据库中完成以下要求：
学生只能选择自己学院开设的课程。发现CS学院有的同学选择了其他学院开设的课程。在SC表中删除这些错选的记录。

实验过程

1. 基本表操作

1.1 建表、设置主键外键

1.1.1 登录数据库

• 在数据库主节点服务器上，切换至 omm 操作系统用户环境
  su - omm
• 查看服务是否启动
  gs_ctl status -D $GAUSSHOME/data/single_node -Z single_node
  • 启动数据库服务（可选操作，如未启动，请按此步骤启动）
    gs_ctl start -D $GAUSSHOME/data/single_node -Z single_node
• 连接数据库
  gsql -d postgres -p 26000 -r
• 查看数据库列表
  \l
• 用用户和密码登录数据库
  gsql -d db_tpcc -p 26000 -U joe -W DataBase@123 -r

1.1.2. 建表

primary key为主键，可以唯一标识一条记录。

CREATE TABLE Teacher
(
TCID int primary key,
TNAME varchar(20),
DEPT varchar(20),
SALARY int
);

CREATE TABLE Student
(
SID varchar(255) primary key,
SNAME varchar(20),
DEPT varchar(20),
AGE int,
GENDER char(1)
);

CREATE TABLE Course
(
CID varchar(255) primary key,
CNAME varchar(20),
DEPT varchar(20),
CREDITS float4,
TEACHER varchar(20)
);

CREATE TABLE SC
(
SID varchar(255),
CID varchar(255),
GRADE int
);

查看表的结构
\d sc

1.1.3. 向表中插入数据

向teacher中添加一行数据

INSERT INTO teacher VALUES (14001, 'Katz', 'CS', 75000);

查看teacher中的所有数据

SELECT * FROM teacher;

teacher中的数据如下：

添加完数据后如图：

1.1.4. 设置主键、外键

主键前面已经设置过了，由表sc可知，sid和cid是表student和表course的有联系的，所以将其设为sc的外键。
使用alter table 含外键的表 add foreign key(xxx)references 别的表(xxx)

alter table sc add foreign key(cid) references course(cid);
alter table sc add foreign key(sid) references student(sid);

1.2 更新表中数据

在student表中，为姓名为’Zhang’且系信息错填为’Physics’的同学修改信息，将其系信息修改为’CS’

UPDATE student SET dept = 'CS' WHERE sname = 'Zhang';

更新前：

更新后：

1.3 删除表中数据

删除teacher表中，属于Finance学院的教师信息

DELETE FROM teacher WHERE dept = 'Finance';

删除前：

删除后：

1.4 查找修改表中数据

在teacher表中，为工资低于或等于70000的教师增长10%的工资，为工资高于70000的教师增长5%的工资。

查找用WHERE，修改用UPDATE：

UPDATE teacher SET salary = salary*1.1 WHERE salary <= 70000;
UPDATE teacher SET salary = salary*1.05 WHERE salary > 70000;

如图：

2. 基本数据查询

2.1

1）基于teacher表，找出“物理系Physics和生物系Biology”所有教师的名字和工资；

SELECT * FROM teacher WHERE dept IN('Physics','Biology');

2.2

2）基于teacher表，列出教师所在的各个系名，要求每个系名仅出现一次

SELECT DISTINCT(dept) FROM teacher;

2.3

3）基于teacher表，希望查看“若将每位教师的工资提高20%后”各位教师的姓名和工资是多少；

UPDATE teacher SET salary = salary*1.2;
SELECT tname, salary FROM teacher ;

2.4

4）基于表student、SC和course，查看到计算机系CS的每位学生所选课程的所有信息，包括学生姓名、所在系、课程名称、课程学分的情况。

左外连接：保留左边表所有的元组以及两张表中相同属性下取值相同的元组
使用A left join B using(sid)指定连接项
.或者A naural left join B

select sname, dept, cname, credits from(
    (select sid, sname from student where dept='CS')
    left join sc using(sid)
    left join course using(cid));

第一个select：最后我们要列出学生姓名sname、所在系dept、课程名称cname、课程学分credit
第二个select：选出所在系dept为CS的学生姓名和sid ，姓名用于第一个select的展示，sid用于在sc表中对应到课程信息
第一个left join：用第二个select选出的学生的sid将sc表对应着链接过去，此时的表有sid、cid、sname
第二个left join：接着用cid，将course表的cname和credits跟上面得到的连接起来

综上，即可得到CS学生的sname、dept、cname、credits信息。

-----------------------------------------2022/4/6 更新-----------------------------------------------
上面的dept显示的是course的dept，如果要显示student的dept的话，可以在第二行用一个as stu，同时select加一个dept，然后第一行的dept改为stu.dept
----------------------------------------2022/4/6 更新------------------------------------------------

3. 复杂数据查询

3.1

1）查询全体学生的姓名、年龄；

select sname, age from student;

3.2

2）查询所有选修过课的学生的学号；

select sid from sc;

3.3

3）查询考试成绩低于60分的学生的学号；

select sid from sc where grade<60;

3.4

4）查询年龄在20至23之间的学生姓名、性别和年龄；

select sname, gender, age from student where age IN(20,21,22,23);

3.5

5）查询所有姓liu的学生的学号、姓名和年龄；

select sid, sname, age from student where left(sname,3)='Liu';

3.6

6）查询学习C1课程的学生最高分数；

select max(grade) from sc where cid='C1';

3.7

7）查询各个课程号与相应的选课人数；

 select cid, count(cid) from sc group by cid;

根据cid进行分组，用count计算选课人数

3.8

8）查询选修C3课程的学生的姓名；

select sname from(
	(select sid, sname from student)
	left join sc using(sid)) where cid='C3';

用sid找cid，用where筛选cid为’C3’的同学，最后只显示sname

3.9

9）查询每一门课程的平均成绩。

select cid, avg(grade) from sc group by cid;

（选做题）

在必做题的数据库中完成以下要求：
学生只能选择自己学院开设的课程。发现CS学院有的同学选择了其他学院开设的课程。在SC表中删除这些错选的记录。