openGauss数据库-SQL练习题下

原创

qwerqw001 2022-09-29 18:57:42 博主文章分类：数据库 ©著作权

文章标签 sql 数据库 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者qwerqw001的原创作品，请联系作者获取转载授权，否则将追究法律责任

前言

数据库方向的研究和开发大致可以分为三个方向：一是数据库内核开发（自研等）、二是数据库系统管理（类似DBA的角色）、三是数据库应用开发（业务+SQL）。内核开发可能需要有钻研创新的能力，比如一些数据库产品本身的自研工作等；DBA可能需要有系统架构、实施经验、以及整体管理的解决方案能力；应用开发则需要具有将业务快速转换成SQL的实现能力。所以说，以上三点纵贯“数据库的整个生命周期” 。

本文将在上一篇《SQL经典练习题（openGauss数据库）上》的基础上继续练习。因为SQL的学习途径之一就是练习，俗话说，熟能生巧嘛！

1、统计各科成绩>=70 de 人数：课程编号，课程名称，人数及所占百分比 5

2、查询各科成绩前三名的记录 5

3、查询每门课程被选修的学生数 6

4、查询出只选修两门课程的学生学号和姓名 6

9、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 8

10、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 8

11、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 8

12、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） 9

13、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 10

14、查询不及格的课程 10

15、查询课程编号为 01 且课程成绩在 80(含80) 分以上的学生的学号和姓名 11

16、求每门课程的学生人数 11

17、查询选修「张老师」所授课程的学生中，成绩最高的学生信息及其成绩 11

18、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩 12

19、查询每门功课成绩最好的前两名 12

20、统计每门课程的学生选修人数（超过 5 人的课程才统计） 13

21、检索至少选修三门课程的学生的学生信息 13

22、查询选修了全部课程的学生信息 13

23、查询各学生的年龄，只按年份来算 14

24、按照出生日期来算 14

25、查询本周过生日的学生 15

三、实验小结 15

1、to_char(datetime/interval [, fmt]) 函数 15

2、获取系统当前的时间（日期） 16

3、TIMESTAMPDIFF 函数 16

一、初始化4张基础表

1、数据表介绍

--学生表：Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--课程表：Course(CId,Cname,TeId)
--CId 课程编号,Cname 课程名称,TId 教师编号

--教师表Teacher(TeId,Tname)
--TId 教师编号,Tname 教师姓名

--成绩表：SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数

2、初始化（创建表并插入测试数据）

学生表Student

create table Student(SId varchar(10),Sname varchar(10),Sbirthday date,Ssex varchar(10));

insert into Student values('01' , '赵雷' , date '1990-01-01' , '男');

insert into Student values('02' , '钱电' , date '1990-12-21' , '男');

insert into Student values('03' , '孙风' , date '1990-12-20' , '男');

insert into Student values('04' , '李云' , date '1990-12-06' , '男');

insert into Student values('05' , '周梅' , date '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , date '1992-01-01' , '女');

insert into Student values('07' , '郑竹' , date '1989-01-01' , '女');

insert into Student values('09' , '张三' , date '2017-12-20' , '女');

insert into Student values('10' , '李四' , date '2017-12-25' , '女');

insert into Student values('11' , '李四' , date '2012-06-06' , '女');

insert into Student values('12' , '赵六' , date '2013-06-13' , '女');

insert into Student values('13' , '孙七' , date '2014-06-01' , '女');

课程表

create table Course(CId varchar(10),Cname varchar(10),TeId varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

教师表

create table Teacher(Teid varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张老师');

insert into Teacher values('02' , '李老师');

insert into Teacher values('03' , '王老师');

成绩表

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

二、SQL习题

1、统计各科成绩>=70 de 人数：课程编号，课程名称，人数及所占百分比

openGauss数据库-SQL练习题下_sql

2、查询各科成绩前三名的记录

openGauss数据库-SQL练习题下_sql_02

3、查询每门课程被选修的学生数

openGauss数据库-SQL练习题下_数据库_03

4、查询出只选修两门课程的学生学号和姓名

openGauss数据库-SQL练习题下_数据库_04

5、查询男生女生人数

openGauss数据库-SQL练习题下_数据库_05

6、查询名字中含有「风」字的学生信息

openGauss数据库-SQL练习题下_sql_06

7、查询同名学生名单，并统计同名人数

openGauss数据库-SQL练习题下_数据库_07

8、查询年出生的学生名单1990

openGauss数据库-SQL练习题下_数据库_08

9、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

openGauss数据库-SQL练习题下_数据库_09

10、查询平均成绩大于等于

openGauss数据库-SQL练习题下_sql_10

11、查询课程名称为「数学」，且分数低于

openGauss数据库-SQL练习题下_sql_11

12、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

openGauss数据库-SQL练习题下_数据库_12

13、查询任何一门课程成绩在

openGauss数据库-SQL练习题下_数据库_13

14、查询不及格的课程

openGauss数据库-SQL练习题下_数据库_14

15、查询课程编号为

openGauss数据库-SQL练习题下_sql_15

16、求每门课程的学生人数

openGauss数据库-SQL练习题下_数据库_16

17、查询选修「张老师」所授课程的学生中，成绩最高的学生信息及其成绩

openGauss数据库-SQL练习题下_数据库_17

18、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩

openGauss数据库-SQL练习题下_sql_18

19、查询每门功课成绩最好的前两名

openGauss数据库-SQL练习题下_sql_19

20、统计每门课程的学生选修人数（超过 5 人的课程才统计）

openGauss数据库-SQL练习题下_sql_20

21、检索至少选修三门课程的学生的学生信息

openGauss数据库-SQL练习题下_sql_21

22、查询选修了全部课程的学生信息

openGauss数据库-SQL练习题下_数据库_22

23、查询各学生的年龄，只按年份来算

openGauss数据库-SQL练习题下_sql_23

24、按照出生日期来算

openGauss数据库-SQL练习题下_sql_24

25、查询本周过生日的学生

openGauss数据库-SQL练习题下_数据库_25

三、实验小结

根据上文的测试过程，如下整理了一些openGauss数据库相关的函数和经验：

1、to_char(datetime/interval [, fmt]) 函数

描述：将一个DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE或者TIMESTAMP WITH LOCAL TIME ZONE类型的DATETIME或者INTERVAL值按照fmt指定的格式转换为VARCHAR类型。

可选参数fmt可以为以下几类：日期、时间、星期、季度和世纪。每类都可以有不同的模板，模板之间可以合理组合，常见的模板有：HH、MI、SS、YYYY、MM、DD。
模板可以有修饰词，常用的修饰词是FM，可以用来抑制前导的零或尾随的空白。

返回值类型：varchar

2、获取系统当前的时间（日期）

clock_timestamp() ，描述：实时时钟的当前时间戳。返回值类型：timestamp with time zone
current_date，描述：当前日期。返回值类型：date
current_time，描述：当前时间。返回值类型：time with time zone
current_timestamp ，描述：当前日期及时间。返回值类型：timestamp with time zone
pg_systimestamp() , 描述：当前日期及时间。返回值类型：timestamp with time zone
localtime,描述：当前时间。返回值类型：time
localtimestamp,描述：当前日期及时间。返回值类型：timestamp
now(),描述：当前日期及时间。返回值类型：timestamp with time zone
timenow,描述：当前日期及时间。返回值类型：timestamp with time zone

3、TIMESTAMPDIFF 函数

TIMESTAMPDIFF(unit , timestamp_expr1, timestamp_expr2)

timestampdiff函数是计算两个日期时间之间(timestamp_expr2-timestamp_expr1)的差值，并以unit形式返回结果。timestamp_expr1，timestamp_expr2必须是一个timestamp、timestamptz、date类型的值表达式。unit表示的是两个日期差的单位。

说明：该函数仅在openGauss兼容MY类型时（即dbcompatibility = 'B'）有效，其他类型不支持该函数。（dbcompatibility的取值： A、B、C、PG ，具体对应是Oracle、MySql、Teradata、PostgreSql 数据库）

4、age函数

age(timestamp, timestamp)，描述：将两个参数相减，并以年、月、日作为返回值。若相减值为负，则函数返回亦为负。返回值类型：interval
age(timestamp)，描述：当前时间和参数相减。返回值类型：interval

5、EXTRACT函数

EXTRACT(field_ _FROM source)，extract函数从日期或时间的数值里抽取子域，比如年、小时等。source必须是一个timestamp、time或interval类型的值表达式（类型为date的表达式转换为timestamp，因此也可以用）。field是一个标识符或者字符串，它指定从源数据中抽取的域。extract函数返回类型为double precision的数值。

示例：

openGauss数据库-SQL练习题下_sql_26

6、SQL优化

为什么需要进行SQL优化？在进行多表连接查询、子查询等操作的时候，由于写的SQL语句欠佳，导致的服务器执行时间太长，等待结果的时间太长。SQL的性能不仅仅需要对SQL语句本身进行，同时也要考虑在建库、建表、建索引等阶段进行。基于此，所以需要学习怎么优化SQL。

1.数据库表设计

1）选择合适的数据类型

使用可存下数据的最小的数据类型。
使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。
使用合理的字段属性长度，固定长度的表会更快。
尽可能使用not null定义字段。
尽量少用text，非用不可最好分表。

2）索引

索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。索引可以用来提高数据库查询性能，但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引：

经常执行查询的字段。
在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引。例如，select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b，可以在t1表上的a，b字段上建立组合索引。
WHERE子句的过滤条件字段上（尤其是范围条件）。
在经常出现在ORDER BY、GROUP BY和DISTINCT后的字段。

2.sql编写