SQL经典练习题（openGauss数据库）下

前言

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

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

一、 初始化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' , '女');

课程表 Course

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

教师表 Teacher

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

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

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

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

成绩表 SC

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 人数：课程编号，课程名称， 人数及所占百分比

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

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

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

5、查询男生女生人数

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

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

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

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

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

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

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

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

14、查询不及格的课程

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

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

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

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

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

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

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

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

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

24、按照出生日期来算

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的数值。

示例：

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编写

• 使用limit对查询结果的记录进行限定

• 避免select *，将需要查找的字段列出来

• 使用连接（join）来代替子查询

• 拆分大的delete或insert语句

• 可通过开启慢查询日志来找出较慢的SQL

• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，

• sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；

• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

• 不用函数和触发器，在应用程序实现

• 避免%xxx式查询

• 使用同类型进行比较

• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

• 对于连续数值，使用BETWEEN不用IN：

• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

3.补充：

• 合理使用"分区、分表等，根据实际情况结合数据库自身技术进行。

• 从业务角度出发给数据库“瘦身”，如分析并下线没有实际业务意义的job；归档历史数据等。

7、中文表名及字段名的优缺点

openGauss数据库虽然支持定义中文表、字段名，但是不建议使用中文（sql server也支持中文）。

• 中文场景：

1、国人的习惯，在不熟悉的领域（机械，医疗等专业）里，英文往往并不能准确的翻译，而使用拼音或缩写也不直观。

2、在简单的、快速开法的（低成本仅供演示的）项目中，往往对数据字典没什么特别的要求，也无需考虑稳定性、迁移性等。使用中文可以方便修改逻辑结构模型，方便SQL语句设计。

3、报表展示等

• 优点：

1、不必考虑命名规范

2、很清晰的知道我们要操作的字段，看起来一目了然。（当然如果数据库被其他人进入也是一目了然，安全性差一点）。

3、页面显示的时候无需转换等。

• 缺点：

1、编写时中文的全角半角等切换太麻烦，很容易出错。

2、中文储存与英文储存有差异，一些系统视图中可能会有一些性能或者维护上的影响。

4、兼容性差，如多语言环境下、多种数据库情况下，兼容性差等。

四、 在线学习平台（推荐）

SQL 的学习是基于数据库而言的，针对不同的数据库，如hivesql、mysql、sqlserver、oracle等，除了拥有自己的私有的扩展外， 大部分都是符合SQL的标准。如下是一些SQL学习的平台和资料，建议收藏。

本文是在openGauss数据库环境上运行的，且大多数sql题有多种写法，需要根据实际业务表的情况进行最优编写，大家练习的同时可以在openGauss数据库环境上测试，同时欢迎经验分享。

1、SQL语法手册，随用随查

1）openGauss 3.0版本， 开发者指南-SQL参考

https://opengauss.org/zh/docs/3.0.0/docs/Developerguide/SQL%E5%8F%82%E8%80%83.html

2）gitbook http://gitbook.net/sql/index.html

3）1keydata https://www.1keydata.com/sql/sql.html

2、SQL边学边练

1）sqlzoo： https://sqlzoo.net/

2）CodeCademy： https://www.codecademy.com/learn/learn-sql

3）sqlbolt：https://sqlbolt.com/

4）w3school ：http://www.w3school.com.cn/sql/index.asp

5）w3schools ：https://www.w3schools.com/sql/default.asp

6）sqlteaching：https://www.sqlteaching.com/

以上就是本期《SQL经典练习题（openGauss数据库）下》 相关分享， 下期再见！

作者：酷哥，来源Gauss松鼠会