数据库实验三--OpenGauss(查询和更新数据)

实验内容

  1. 查询操作
    根据教材P79-94页所有查询实例,完成各类查询操作。
  2. 更新操作
    根据教材P94-96上所有更新实例,完成各类更新操作(插入、修改和删除数据等),请注意标准SQLopenGauss中相应SQL语句相同与不同之处。

实验过程

首先启动并连接数据库:

su - omm
gs_om -t start
gsql -d postgres -p 26000 -r

进入到我们实验二创建的数据库db_cc

\c db_cc

在这里插入图片描述

查询

不带where的简单查询

查询所有课程的信息

select * from courses;

在这里插入图片描述

显示每位学生的年龄

select sname,2017-extract(year from birthday) as age from students ;

在这里插入图片描述
注意这里openGauss中不支持year()等函数,这里要用extract代替

显示所有学生的不同年龄

select distinct 2017-extract(year from birthday) as age from students;

在这里插入图片描述

带where的查询

查询职称(Title)为教师的全体教师的姓名和性别

select tname, sex from teachers where title = '副教授';

在这里插入图片描述
查询考试成绩低于90分的学生的学号

select distinct sno from sc where grade < 90;

在这里插入图片描述
查询出生年月在1997~1998的学生的姓名和专业

select sno,sname from students where extract(year from birthday) between 1997 and 1998;

在这里插入图片描述

LIKE表达式

查询所有以“数据”开头的课程名

select cname from courses where cname like '数据%';

在这里插入图片描述

在这里插入图片描述

排序和分组

排序

ORDER BY可以进行查询结果的排序

ORDEF BY <排序列> [ASC|DESC] {,<排序列> {ASC|DESC}}

查询每位学生cs202课程的成绩,并将查询结果按照成绩降序排序

SELECT * FROM sc WHERE cno='cs202' ORDER BY Grade DESC;

在这里插入图片描述

聚集函数

查询选修了cs202课程的学生人数

SELECT COUNT(*) FEOM SC WHERE cno = 'cs202';

在这里插入图片描述

查询cs102课程的最低分,平均分和最高分

select MIN(GRADE),AVG(Grade),MAX(Grade) from sc where cno = 'cs102';

在这里插入图片描述

分组

GROUP BY子句可用来进行分组

GROUP BY <分组列> {,<分组列>} {<分组选择条件>}

查询每个学生的平均成绩,输出学生的学号和平均成绩

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

在这里插入图片描述
查询每个学生的平均成绩,并输出平均成绩大于85分的学生学号和平时成绩
需要注意的是:我们要去掉平均成绩小于或等于85的分组,需要是能够用到HAVING短语

select sno,avg(grade) from sc group by sno having avg(grade) >85;

在这里插入图片描述

连接查询

查询学号为201705001的学生的各科成绩,对每们课程显示名称和成绩。

select cname,grade 
from sc,courses 
where sc.cno = courses.cno and sno = '201705001';

在这里插入图片描述
注意:cno既是sc的属性,也是courses的属性。为了避免二义性,我们必须要在前加上前缀。

查询每个学生的平均成绩,并输出平均成绩大于85分的学生学号,姓名和平均成绩

select students.sno, sname,avg(grade) 
from sc,students
where students.sno = sc.sno
group by students.sno,sname
having avg(grade) > 85 ;

在这里插入图片描述
自身连接
查询和张华专业相同的学生

select s2.sname
from students s1,students s2
where s1.speciality = s2.speciality and
	  s1.sname = '张华' and
	  s2.sname<>'张华' ;

在这里插入图片描述
注意这里students出现了两次,我们用s1和s2来区分他们。

嵌套查询

IN-子查询

查询和张华专业相同的男学生,显示学号和姓名

select speciality
from students
where sname = '张华;

我们将上面作为子查询,我们得到改查询的sql语句为:

select sno, sname
from students
where sex = '男' and speciality in 
	(select speciality
	 from students
	 where sname = '张华');

在这里插入图片描述
系统会先进性子查询,得到张华的专业。一般的,这是一个集合。(比如叫张华的不止一个)
然后,外层查询选择专业一致的男学生。

集合比较-子查询

sql允许将一个元素与子查询的结构集进行比较,这种量化比较表达式的常用形式是

<> 0 ALL | SOME | ANY <子查询>  

查询比数学专业所有学生都小的其他专业的学号、姓名、专业和出生日期;

select birthday
from students
where speciality = '数学';

将它作为子查询

select sno,sname,speciality,year(birthday)
from students
where speciality<>'数学'and
	year(birthday)>all(select year(birthday)
				from students
				where speciality = '数学');

存在量词--子查询

查询所有选修了cs201课程的学生的学号和姓名

select sno,sname
from students s
where exists
	(select * 
	from sc
	where sno = s.sno and cno='cs201');

在这里插入图片描述

查询选修了全部课程的学生的学号和姓名

select sno, sname
from students s
where not exists
	(select * 
	from courses c
	where not exists
		(select *
		from sc
		where sc.sno = s.sno and sc.cno = c.cno));

在这里插入图片描述

查询子查询结果中的重复元组

unique <子查询>

集合运算

sql支持集合运算
包括UNION, INTERSECT, EXCEPT
查询选修了cs201号课程或者选修了cs202号课程的学生的学号

select sno
from sc
where cno = 'cs201'
union
select sno
from sc
where cno = 'cs201';

在这里插入图片描述

数据更新

数据更新包括插入、删除和修改。
对应的sql语句分别为INSERTDELETEUPDATE

插入

插入单个元组

插入单个元组的INSERT格式为

INSERT INTO T [(A1,···,Ak)]
VALUES(C1,···,ck)

T通常是基本表,也可以是视图

将学号为201716010、姓名为司马相如、性别为男、生日为1997-01-28,专业为数学,入学年份为2017,专业代码为ma的学生元组插入到表格中。

INSERT INTO students
VALUES ('201716010','司马相如','男','1997-01-28','2017','数学','ma');

在这里插入图片描述
在这里插入图片描述

向表sc中插入一个选课记录,登记为一个学号为201716010的学生选修了cs301的课程

INSERT INTO sc(sno,cno)
VALUES ('201716010','cs301');

在这里插入图片描述
在这里插入图片描述
(这里在插入时没有插入成绩)

插入查询结果

INSERT INTO T[(A1,···.Ak)]
<查询表达式>

删除

可以使用DELETE来进行删除

DELETE FROM T
	[WHERE <删除条件>]

删除学号为201716010的学生记录

delete from students
where sno = '201716010';

需要注意的是,201716010的记录在sc表中也有,并且sno在sc表中是外码,所以上面的指令直接执行会违反完整性约束。删除该同学的记录需要在sc表和students表中一块删除。本部分会在之后的实验中详细操作。

修改

使用update语句可以修改指定元组指定属性上的值

UPDATE T
SET A1 = e1,···,Ak = ek
	[WHERE <修改条件>]

将学号为201716010的学生的专业入学时间修改为2018

update students 
set enrollyear = '2018'
where sno = '201716010';

在这里插入图片描述
在这里插入图片描述

posted @ 2022-04-07 15:02  CK_0ff  阅读(527)  评论(0编辑  收藏  举报