目录
- 数据查询
- 简单查询
- 去重复查询
- 多列查询
- 别名
- 条件查询
- join连接查询
- 内连接
- 外连接
- 左外连接
- 右外连接
- 全外连接
- 半连接
- 反连接
- 子查询
- 合并结果集
- 差异结果集
- 数据分组
- 数据排序
- 数据限制
- 数据更新
- 数据插入
- 数据修改
- 数据删除
数据查询
数据查询语言是数据库最基本的应用,其语法较为复杂,包括简单查询、带条件查询、连接查询、子查询、集合运算、数据分组、排序和限制等。
简单查询
日常查询中,最常用的是通过FROM子句实现的查询。
语法格式:
SELECT表示想要查询的列,FROM关键字后跟想要查询的表,如果想要查询所有的列则在SELECT后跟*即‘SELECT * FROM 表 ’,如果要查询特定的列,则在SELECT后跟上相应的列名,用逗号隔开。
去重复查询
通过使用DISTINCT关键字,从SELECT查询的结果集中删除所有重复的行,使结果集中每一行唯一,根据字段名或表达式进行去重复。
语法格式:
如果在DISTINCT关键字后只有一个列,则使用该列来计算重复,如果有两列或者多列,则将使用这些列的组合来进行重复检查。
多列查询
在选择查询列时,列名可以用下面几种形式表达:
手动输入列名,多个列之间用英文逗号(,)分隔。
其中,列a、b是表t1中的列,f1、f2是表t2中的列。
可以是计算出来的字段。
如果某两个或某几个表正好有一些共同的列名,推荐使用表名限定列名。不限定列名可以得到查询结果,但使用完全限定的表和列名称,可以减少数据库内部的处理工作量,从而提升查询的返回性能。例如:
别名
通过使用子句AS name,可以把表名称或者列名称指定为另一个标题名称显示,一般创建别名是为了让列名称的可读性更强。
语法格式:
条件查询
在SELECT查询中,可以设置条件以达到更精细的查询,条件由表达式与操作符共同指定,且条件返回的值是TRUE,FALSE或UNKNOWN。查询条件可以应用于WHERE子句,HAVING子句。
语法格式:
查询training表中所有课程名为SQL majorization的行,且显示所有列。
比较操作符“>, <,>=,<=, !=, <>,=”指定的比较查询条件。当查询条件中和数字比较,可以使用单引号引起,也可以不用,当和字符及日期类型的数据比较,则必须用单引号引起。还可以与逻辑操作符AND和OR搭配使用,来满足多个条件,或满足多个条件之一。
逻辑操作符的运算规则:
a | b | a ANDb | a OR b | NOT a |
TRUE | TRUE | TRUE | TRUE | FALSE |
TRUE | FALSE | FALSE | TRUE | FALSE |
TRUE | NULL | NULL | TRUE | FALSE |
FALSE | FALSE | FALSE | FALSE | TRUE |
FALSE | NULL | FALSE | NULL | TRUE |
NULL | NULL | NULL | NULL | NULL |
GaussDB 100支持如下表的测试运算符:
运算符 | 描述 |
IN/NOT IN | 元素在/不在指定的集合中。 |
EXISTS/NOT EXISTS | 存在/不存在符合条件的元素。 |
ANY/SOME | 存在一个值满足条件。SOME是ANY的同义词。 |
ALL | 全部值满足条件。 |
BETWEEN…AND… | 在两者之间,例如a BETWEEN x AND y等效于a>= x and a <= y。 |
IS NULL/IS NOT NULL | 等于/不等于NULL。 |
LIKE/NOT LIKE | 字符串模式匹配/不匹配。 |
REGEXP | 字符串与正则表达式相匹配,仅支持STRING类型。 |
REGEXP_LIKE | 字符串与正则表达式相匹配,支持STRING类型和NUMBER类型。 |
join连接查询
实际应用中所需要的数据,经常会需要查询两个或两个以上的表。这种查询两个或两个以上数据表或视图的查询叫做连接查询。连接查询通常建立在存在相互关系的父子表之间。
语法格式:
当FROM子句后跟多个表时,数据库就会执行连接操作:
内连接
内连接的关键字为inner join,其中inner可以省略,连接执行顺序必然遵循语句中所写的表的顺序。
例如:
两张表,一张trainning,一张education,将两张表通过内连接方式进行连接查询,并且连接时根据STAFF_ID字段相同的进行内连接,然后查询连接后的表中的staff_id,highest_degree和score。
外连接
内连接所指定的两个数据源处于平等的地位。而外连接不同,外连接以一个数据源为基础,将另外一个数据源与之进行条件匹配。
内连接返回两个表中所有满足连接条件的数据记录。外连接不仅返回满足连接条件的记录,还将返回不满足连接条件的记录。
外连接又分为左外连接、右外连接和全外连接。
左外连接
又称左连接,关键字为LEFT JOIN,如图所示,是指以左边的表为基础表进行查询。
根据指定连接条件关联右表,获取基础表以及和条件匹配的右表数据,未匹配条件的右表对应的字段位置填上NULL。
右外连接
又称右连接,关键字为RIGHT JOIN,是指以右边的表为基础表,在内连接的基础上也查询右边表中有记录,而左边的表中没有记录的数据(左边用NULL值填充)。
全外连接
又称全连接,关键字FULL JOIN,是指除了返回两个表中满足连接条件的记录,还会返回两个表中不满足连接条件的所有其它行(不匹配的另外一边用NULL值填充)。
半连接
半连接(Semi Join)是一种特殊的连接类型,在SQL中没有指定的关键字,通过在WHERE后面使用IN或EXISTS子查询实现。当IN/EXISTS右侧的多行满足子查询的条件时,主查询也只返回一行与IN/EXISTS子查询匹配的行,而不是复制左侧的行。
反连接
反连接(Anti Join)是一种特殊的连接类型,在SQL中没有指定的关键字,通过在WHERE后面使用NOT IN或NOT EXISTS子查询实现。返回所有不满足条件的行。这个关系的概念跟半连接相反。
子查询
子查询是指在查询、建表或插入语句的内部嵌入查询,以获得临时结果集。子查询可以出现在FROM子句、WHERE子句、以及WITH AS子句中。FROM子句中的子查询也称为内联视图。WHERE子句中的子查询也称为嵌套子查询。
WITH AS子句
定义一个SQL片断,该SQL片断会被整个SQL语句用到。
语法格式:
例:
上述SQL语句中Sl条件判断依据是另外一个查询中返回的数值,形成了一个嵌套的WHERE查询。
合并结果集
除子查询外,还可以使用集合运算符处理多个查询的结果集,输出最终结果。
Union运算符:将多个查询块的结果集合并为一个结果集输出。
使用注意:
每个查询块的查询列数目必须相同。
每个查询块对应的查询列必须为相同数据类型或同一数据类型组。
关键字ALL的意思是保持所有重复数据,而没有ALL的情况下表示删除所有重复数据。
差异结果集
对查询结果集的减法,计算存在于左边查询语句的输出、而不存在于右边查询语句输出的结果。A minus B C就意味着将结果集A去除结果集B和结果集C中所包含的所有记录后的结果,即在A中存在,而在B、 C中不存在的记录。
MINUS/ EXCEPT运算符:
数据分组
数据库查询中,分组是一个非常重要的应用。分组是指将数据表中的记录以某个或者某些列为标准,值相等的划分为一组。
语法格式:
使用注意:
GROUP BY子句中的表达式可以包含FROM子句中表,视图的任何列,无论这些列是否出现在SELECT列表中。
GROUP BY子句对行进行分组,但不保证结果集的顺序。 要对分组进行排序,请使用ORDER BY子句。
GROUP BY后的表达式可以使用括号,如: group by (expr1, expr2),或者 group by(expr1), (expr2)。 但不支持 group by (expr1, expr2), expr3 格式。HAVING子句
与GROUP BY子句配合用来选择特殊的组。HAVING子句将组的一些属性与一个常数值比较,只有满足HAVING子句中条件的组才会被提取出来。
语法格式:
数据排序
使用ORDER BY子句对查询语句返回的行根据指定的列进行排序。如果没有ORDER BY子句,则多次执行的同一查询将不一定以相同的顺序进行行的检索。
语法格式:
使用注意:
ORDER BY语句默认按照升序对记录进行排序。如果希望按照降序对记录进行排序,请使用DESC关键字。
NULLS FIRST | NULLS LAST 关键字指定ORDER BY列中NULL值的排序位置,FIRST表示将NULL值排在最前面,LAST表示将NULL值排在最后面,若不指定该选项, ASC默认为NULLS LAST, DESC默认为NULLS FIRST。
数据限制
数据限制功能包括两个独立的子句,LIMIT子句和OFFSET子句。
LIMIT子句允许限制查询返回的行。 可以指定偏移量,以及要返回的行数或行百分比。 可以使用此子句实现top-N报表。要获得一致的结果,请指定ORDER BY子句以确保确定性排序顺序。
OFFSET子句设置开始返回的位置。
使用注意:
start:指定在返回行之前要跳过的行数。
count:指定要返回的最大行数。
start和count都被指定时,在开始计算要返回的count行之前会跳过start行。
LIMIT 5,20与LIMIT 20 OFFSET 5及OFFSET 5 LIMIT 20等效。
数据更新
数据插入
INSERT语句用于在表中插入数据
语法格式:
例:
往training1表中插入数据,每个字段对应VALUE值,并且VALUE值的数据类型要与设置的字段数据类型对应。
注意:
只有拥有表INSERT权限的用户,才可以向表中插入数据。
如果使用RETURNING子句,用户必须要有该表的SELECT权限。
如果使用query子句插入来自查询里的数据行,用户还需要拥有在查询里使用的表的SELECT权限。
INSERT事务提交是默认关闭的。会话退出时,需要显式COMMIT,否则记录将丢失。
数据修改
UPDATE语句用于更新表中的值
语法格式:
例:
更新表training中staff_id和表education中staff_id相同的记录的first_name字段。
注意:
UPDATE事务提交是默认关闭的。会话退出时,需要显式COMMIT,否则记录将丢失。
执行该语句的用户需要有表的UPDATE权限或者UPDATE ANY TABLE的系统权限。
普通用户不允许UPDATE系统SYS用户对象。不支持临时表的多表更新。
数据删除
DELETE语句用于从表中删除行
语法格式:
例:
删除表training中同时匹配course_name='INFORMATION SAFETY’和staff_id=10的记录。
注意:
执行该语句的用户需要有表的DELETE权限或者DELETE ANY TABLE的系统权限。
DELETE事务提交是默认关闭的。会话退出时,需要显式COMMIT,否则记录将丢失。