1、数据表介绍 3

4age函数 16

6SQL优化 17

## 一、初始化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、初始化（创建表并插入测试数据）

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

## 三、实验小结

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

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

### 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表示的是两个日期差的单位。

### 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优化

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学习的平台和资料建议收藏

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

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

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

31keydata 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/