實(shí)驗(yàn)要求

1.基本表操作

• 按下圖創(chuàng)建四個(gè)表：teacher、student、course和SC，為屬性選擇合適的域、合適的主碼和外鍵約束，并為他們插入所列出數(shù)據(jù)；

• 在student表中，為姓名為’Zhang’且系信息錯(cuò)填為’Physics’的同學(xué)修改信息，將其系信息修改為’CS’;
• 刪除teacher表中，屬于Finance學(xué)院的教師信息;
• 在teacher表中，為工資低于或等于70000的教師增長(zhǎng)10%的工資，為工資高于70000的教師增長(zhǎng)5%的工資。

2.基本數(shù)據(jù)查詢(xún)

1. 基于teacher表，找出“物理系Physics和生物系Biology”所有教師的名字和工資；
2. 基于teacher表，列出教師所在的各個(gè)系名，要求每個(gè)系名僅出現(xiàn)一次；
3. 基于teacher表，希望查看“若將每位教師的工資提高20%后”各位教師的姓名和工資是多少；
4. 基于表student、SC和course，查看到計(jì)算機(jī)系CS的每位學(xué)生所選課程的所有信息，包括學(xué)生姓名、所在系、課程名稱(chēng)、課程學(xué)分的情況。

3.復(fù)雜數(shù)據(jù)查詢(xún)

1. 查詢(xún)?nèi)w學(xué)生的姓名、年齡
2. 查詢(xún)所有選修過(guò)課的學(xué)生的學(xué)號(hào)
3. 查詢(xún)考試成績(jī)低于60分的學(xué)生的學(xué)號(hào)
4. 查詢(xún)年齡在20至23之間的學(xué)生姓名、性別和年齡
5. 查詢(xún)所有姓l(shuí)iu的學(xué)生的學(xué)號(hào)、姓名和年齡
6. 查詢(xún)學(xué)習(xí)C1課程的學(xué)生最高分?jǐn)?shù)
7. 查詢(xún)各個(gè)課程號(hào)與相應(yīng)的選課人數(shù)
8. 查詢(xún)選修C3課程的學(xué)生的姓名
9. 查詢(xún)每一門(mén)課程的平均成績(jī)。

（選做題）

在必做題的數(shù)據(jù)庫(kù)中完成以下要求：
學(xué)生只能選擇自己學(xué)院開(kāi)設(shè)的課程。發(fā)現(xiàn)CS學(xué)院有的同學(xué)選擇了其他學(xué)院開(kāi)設(shè)的課程。在SC表中刪除這些錯(cuò)選的記錄。

實(shí)驗(yàn)過(guò)程

1. 基本表操作

1.1 建表、設(shè)置主鍵外鍵

1.1.1 登錄數(shù)據(jù)庫(kù)

• 在數(shù)據(jù)庫(kù)主節(jié)點(diǎn)服務(wù)器上，切換至 omm 操作系統(tǒng)用戶(hù)環(huán)境
  su - omm
• 查看服務(wù)是否啟動(dòng)
  gs_ctl status -D $GAUSSHOME/data/single_node -Z single_node
  • 啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)（可選操作，如未啟動(dòng)，請(qǐng)按此步驟啟動(dòng)）
    gs_ctl start -D $GAUSSHOME/data/single_node -Z single_node
• 連接數(shù)據(jù)庫(kù)
  gsql -d postgres -p 26000 -r
• 查看數(shù)據(jù)庫(kù)列表
  \l
• 用用戶(hù)和密碼登錄數(shù)據(jù)庫(kù)
  gsql -d db_tpcc -p 26000 -U joe -W DataBase@123 -r

1.1.2. 建表

primary key為主鍵，可以唯一標(biāo)識(shí)一條記錄。

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
);

查看表的結(jié)構(gòu)
\d sc

1.1.3. 向表中插入數(shù)據(jù)

向teacher中添加一行數(shù)據(jù)

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

查看teacher中的所有數(shù)據(jù)

SELECT * FROM teacher;

teacher中的數(shù)據(jù)如下：

添加完數(shù)據(jù)后如圖：

1.1.4. 設(shè)置主鍵、外鍵

主鍵前面已經(jīng)設(shè)置過(guò)了，由表sc可知，sid和cid是表student和表course的有聯(lián)系的，所以將其設(shè)為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 更新表中數(shù)據(jù)

在student表中，為姓名為’Zhang’且系信息錯(cuò)填為’Physics’的同學(xué)修改信息，將其系信息修改為’CS’

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

更新前：

更新后：

1.3 刪除表中數(shù)據(jù)

刪除teacher表中，屬于Finance學(xué)院的教師信息

DELETE FROM teacher WHERE dept = 'Finance';

刪除前：

刪除后：

1.4 查找修改表中數(shù)據(jù)

在teacher表中，為工資低于或等于70000的教師增長(zhǎng)10%的工資，為工資高于70000的教師增長(zhǎng)5%的工資。

查找用WHERE，修改用UPDATE：

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

如圖：

2. 基本數(shù)據(jù)查詢(xún)

2.1

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

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

2.2

2）基于teacher表，列出教師所在的各個(gè)系名，要求每個(gè)系名僅出現(xiàn)一次

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，查看到計(jì)算機(jī)系CS的每位學(xué)生所選課程的所有信息，包括學(xué)生姓名、所在系、課程名稱(chēng)、課程學(xué)分的情況。

左外連接：保留左邊表所有的元組以及兩張表中相同屬性下取值相同的元組
使用A left join B using(sid)指定連接項(xiàng)
.或者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));

第一個(gè)select：最后我們要列出學(xué)生姓名sname、所在系dept、課程名稱(chēng)cname、課程學(xué)分credit
第二個(gè)select：選出所在系dept為CS的學(xué)生姓名和sid ，姓名用于第一個(gè)select的展示，sid用于在sc表中對(duì)應(yīng)到課程信息
第一個(gè)left join：用第二個(gè)select選出的學(xué)生的sid將sc表對(duì)應(yīng)著鏈接過(guò)去，此時(shí)的表有sid、cid、sname
第二個(gè)left join：接著用cid，將course表的cname和credits跟上面得到的連接起來(lái)

綜上，即可得到CS學(xué)生的sname、dept、cname、credits信息。

-----------------------------------------2022/4/6 更新-----------------------------------------------
上面的dept顯示的是course的dept，如果要顯示student的dept的話，可以在第二行用一個(gè)as stu，同時(shí)select加一個(gè)dept，然后第一行的dept改為stu.dept
----------------------------------------2022/4/6 更新------------------------------------------------

3. 復(fù)雜數(shù)據(jù)查詢(xún)

3.1

1）查詢(xún)?nèi)w學(xué)生的姓名、年齡；

select sname, age from student;

3.2

2）查詢(xún)所有選修過(guò)課的學(xué)生的學(xué)號(hào)；

select sid from sc;

3.3

3）查詢(xún)考試成績(jī)低于60分的學(xué)生的學(xué)號(hào)；

select sid from sc where grade<60;

3.4

4）查詢(xún)年齡在20至23之間的學(xué)生姓名、性別和年齡；

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

3.5

5）查詢(xún)所有姓l(shuí)iu的學(xué)生的學(xué)號(hào)、姓名和年齡；

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

3.6

6）查詢(xún)學(xué)習(xí)C1課程的學(xué)生最高分?jǐn)?shù)；

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

3.7

7）查詢(xún)各個(gè)課程號(hào)與相應(yīng)的選課人數(shù)；

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

根據(jù)cid進(jìn)行分組，用count計(jì)算選課人數(shù)

3.8

8）查詢(xún)選修C3課程的學(xué)生的姓名；

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

用sid找cid，用where篩選cid為’C3’的同學(xué)，最后只顯示sname

3.9

9）查詢(xún)每一門(mén)課程的平均成績(jī)。

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

（選做題）

在必做題的數(shù)據(jù)庫(kù)中完成以下要求：
學(xué)生只能選擇自己學(xué)院開(kāi)設(shè)的課程。發(fā)現(xiàn)CS學(xué)院有的同學(xué)選擇了其他學(xué)院開(kāi)設(shè)的課程。在SC表中刪除這些錯(cuò)選的記錄。

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-427958.html

到了這里，關(guān)于數(shù)據(jù)庫(kù)實(shí)驗(yàn)一：基本表操作、基本數(shù)據(jù)查詢(xún)和復(fù)雜數(shù)據(jù)查詢(xún)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！