实验要求
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.基本数据查询
- 基于teacher表，找出“物理系Physics和生物系Biology”所有教师的名字和工资；
- 基于teacher表，列出教师所在的各个系名，要求每个系名仅出现一次；
- 基于teacher表，希望查看“若将每位教师的工资提高20%后”各位教师的姓名和工资是多少；
- 基于表student、SC和course，查看到计算机系CS的每位学生所选课程的所有信息，包括学生姓名、所在系、课程名称、课程学分的情况。
3.复杂数据查询
- 查询全体学生的姓名、年龄
- 查询所有选修过课的学生的学号
- 查询考试成绩低于60分的学生的学号
- 查询年龄在20至23之间的学生姓名、性别和年龄
- 查询所有姓liu的学生的学号、姓名和年龄
- 查询学习C1课程的学生最高分数
- 查询各个课程号与相应的选课人数
- 查询选修C3课程的学生的姓名
- 查询每一门课程的平均成绩。
（选做题）
在必做题的数据库中完成以下要求：
学生只能选择自己学院开设的课程。发现CS学院有的同学选择了其他学院开设的课程。在SC表中删除这些错选的记录。文章来源：https://www.toymoban.com/news/detail-427958.html
实验过程
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表中删除这些错选的记录。
文章来源地址https://www.toymoban.com/news/detail-427958.html
到了这里，关于数据库实验一：基本表操作、基本数据查询和复杂数据查询的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
