kingbase 单表查询

[TOC]
## 一、SQL语言概述
### 1. SQL语言分类
```
SQL语言共分为四大类：
DQL 数据查询语言
DML 数据操纵语言
DDL 数据定义语言
DCL 数据控制语言
```

SQL类别 | 主要动作
---|---
DQL（Date Query Language）| select（通常与from、where、group by、having、order by等组合使用），数据查询。
DML（Data Manipulation Language） | insert、update和delete，定义数据库记录（数据）。
DDL（Data Definition Language） | create、alter、drop、truncate，定义数据库对象：库、表、列等。
DCL（Data Control Language） | grant、revoke，定义访问权限和安全级别。
TCL（Transaction Control Language） | commit、rollback、savapoint、set transaction，事务控制。
CCL（Cursor Control Language） | declare cursor、fetch into和update where current，指针控制。

```
说明：
Manipulation /məˌnɪpjuˈleɪʃn/ n.操纵
Definition /ˌdefɪˈnɪʃn/ n.定义
```
## 二、DQL语言的语法
```
test=# \h select
Command: SELECT
Description: 从数据表或视图中读取数据
Syntax:
[ WITH [ RECURSIVE ] with查询语句(with_query) [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( 表达式 [, ...] ) ] ]
[ * | 表达式 [ [ AS ] 输出名称 ] [, ...] ]
[ FROM from列表中项 [, ...] ]
[ WHERE 条件 ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING 条件 [, ...] ]
[ WINDOW 窗口名称 AS ( 窗口定义 ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] 查询 ]
[ ORDER BY 表达式 [ ASC | DESC | USING 运算子 ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { 查询所用返回记录的最大数量 | ALL } ]
[ OFFSET 起始值 [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ 查询所用返回记录的最大数量 ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF 表名 [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

from 列表中的项可以是下列内容之一

[ ONLY ] 表名 [ * ] [ [ AS ] 别名 [ ( 列的别名 [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( 参数 [, ...] ) [ REPEATABLE ( 种子 ) ] ]
[ LATERAL ] ( 查询 ) [ AS ] 别名 [ ( 列的别名 [, ...] ) ]
WITH查询语句名称(with_query_name) [ [ AS ] 别名 [ ( 列的别名 [, ...] ) ] ]
[ LATERAL ] 函数名称 ( [ 参数 [, ...] ] )
[ WITH ORDINALITY ] [ [ AS ] 别名 [ ( 列的别名 [, ...] ) ] ]
[ LATERAL ] 函数名称 ( [ 参数 [, ...] ] ) [ AS ] 别名 ( 列定义 [, ...] )
[ LATERAL ] 函数名称 ( [ 参数 [, ...] ] ) AS ( 列定义 [, ...] )
[ LATERAL ] ROWS FROM( 函数名称 ( [ 参数 [, ...] ] ) [ AS ( 列定义 [, ...] ) ] [, ...] )
[ WITH ORDINALITY ] [ [ AS ] 别名 [ ( 列的别名 [, ...] ) ] ]
from列表中项 [ NATURAL ] 连接操作的类型 from列表中项 [ ON 用连接操作的条件 | USING ( 用于连接操作的列 [, ...] ) ]

并且grouping_element可以是下列之一：

( )
表达式
( 表达式 [, ...] )
ROLLUP ( { 表达式 | ( 表达式 [, ...] ) } [, ...] )
CUBE ( { 表达式 | ( 表达式 [, ...] ) } [, ...] )
GROUPING SETS ( grouping_element [, ...] )

with查询语句是：

WITH查询语句名称(with_query_name) [ ( 列名称 [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( 查询 | 值 | insert | update | delete )

TABLE [ ONLY ] 表名 [ * ]



参数说明：
select 主动作关键字，可以对表执行投影和选择操作。
distinct 用于对结果集去掉重复记录。
* 代表查询表中的所有列。
where 指定查询条件，只返回条件为true的记录。
条件表达式用于各种数据类型的字段值的相关比较。
常用的条件表达式操作符有>、<、>=、<=、<>(!=)、between and、exists、like、is null。
逻辑表达式用于结合或者限制一个或多个条件表达式。
常用的逻辑表达式操作符有not、and、or。
group by 用于对满足条件的记录按指定列执行分组聚合运算。
having 用于对组聚合后的结果集进行筛选，只返回条件为true的记录。
order by 用于对集合进行排序。
支持升序（ASC）、降序（DESC）、多列组合排序。
limit 用于返回结果集的前N行（TOP N查询）。
offset .. fetch 用于分页查询。
for 用于独占方式查询表。


distinct /dɪˈstɪŋkt/ adj.有区别的
例如：
#查询sys_tables表中涉及到的所有模式并去重。
test=# select distinct schemaname from sys_tables;
```
### 1. select子句
#### 1.1 查询所有行和所有列
```
1. 使用select * 查看course表中所有的数据。
test=# select * from exam.course;
cno | cname | clevel | pass_mark
-----+-------+----------+-----------
10 | KCA | junior | 70
20 | KCP | middle | 70
30 | KCM | advanced | 70
(3 行记录)
```
#### 1.2 查询部分列
```
针对列较多的表，在实际应用中往往只需要取部分列，同时为输入的列设置有意义的别名，让结果更直观。为列取别名可使用as，也可以省略as关键字。

1. 从exam.student表中查询学号和姓名信息。
test=# select sno as "学号",sname as "姓名" from exam.student;
学号 | 姓名
------+----------------
1001 | Zhang San
1002 | Yang Yang
1003 | Liu Wei
1004 | Sun Juan
1005 | Li Xiaofeng
1006 | Zhang Xiaotian
1007 | Feng Xiaoyue
1008 | Qin Shanshan
(8 行记录)
```
#### 1.3 单引号与双引号
```
1. 针对有空格、特殊字符、中文、以数字开头的【别名】必须加双引号，英文可不加引号，此处说的是别名。
例如：
test=# select 1+1 as "1+1=?" from dual;
1+1=?
-------
2
(1 行记录)

2. 针对标量字符串表达式必须加单引号，此处说的是字段，即字符串型的【字段】要使用单引号。
例如：
test=# select 'hello,kingbase' as hello from dual;
hello
----------------
hello,kingbase
(1 行记录)
```
#### 1.4 连接运算
##### 1.4.1 字符串的拼接运算
```
1. 例如，将多列拼接成一个长的字符串、将字符串与列拼接成一个长的字符串。

test=# select iname || '的职称是' || title || '.' as "讲师信息" from exam.instructor;
讲师信息
----------------------------------------------
Li Nan的职称是Senior Instructor.
Zhang San的职称是Intermediate Instructor.
Liu Wei的职称是Intermediate Instructor.
Ma Tao的职称是Intermediate Instructor.
Yang Juan的职称是Intermediate Instructor.
Sun Yang的职称是Intermediate Instructor.
Deng Feilong的职称是Intermediate Instructor.
(7 行记录)

```
##### 1.4.2 字符串拼接经常用于生成SQL脚本
```
1. 比如，要删除exam模式下的所有表，可以通过拼接生成如下批量的SQL语句。

test=# select 'drop table ' || schemaname || '.' || tablename || ';' as batch_sql from sys_tables where schemaname='exam';
batch_sql
-------------------------------------
drop table exam.instructor_history;
drop table exam.course;
drop table exam.score;
drop table exam.student;
drop table exam.instructor;
(5 行记录)

说明：在Kingbase中，sys_tables表是一个系统表，用于储存数据库中所有表的相关信息。
```
#### 1.5 算数运算符
##### 1.5.1 数值的算数运算
```
要对某一列进行算数运算时，可以直接将相应的列明和数值进行运算。
要对某两列或多列进行算数运算时，可直接将相应的列名与相应的运算符连接。

说明：
字段可以直接使用"+"、"-"、"*"、"/" 进行加减乘除的算数运算。

1. 单列运算
test=# select sno,score,score+1 as "new_score" from exam.score ;
sno | score | new_score
------+-------+-----------
1001 | 85 | 86
1001 | 88 | 89
1001 | 83 | 84
(3 行记录)

2. 两列运算
test=# select sno,score,score+sno as "sum_two" from exam.score ;
sno | score | sum_two
------+-------+-----------
1001 | 85 | 1086
1001 | 88 | 1089
1001 | 83 | 1084
(3 行记录)

3. 多列运算
test=# select sno,cno,ino,sno+cno+ino as "sum_three" from exam.score ;
sno | cno | ino | sum_three
------+-----+-----+-----------
1001 | 10 | 101 | 1112
1001 | 20 | 101 | 1122
1001 | 30 | 101 | 1132
(3 行记录)
```
##### 1.5.2 日期的算数运算
```
test=# select current_date()+7 as "next_week" from dual;
next_week
------------
2023-06-16
(1 行记录)
```
#### 1.6 使用条件表达式
```
case具有两种格式：简单case函数和case搜索函数。

case n.具体情况,事例
when adv.当...时候
```
1. 简单case函数
```
case 列名
when '值1' then '结果1'
when '值2' then '结果2'
...
else '其他' end

说明：此时显示的列名为case后的列名。

举例说明：
select population,
case country
when '中国' then '亚洲'
when '美国' then '美洲'
when '加拿大' then '北美洲'
else '其他' end
from exam.countrys;
```
2. case搜索函数
```
case
when 条件1 then 结果1
when 条件2 then 结果2
...
else 其他 end

说明：若不为整个case when语句写个别名的话，则显示的列名为整个case when语句。

举例说明：
输出成绩信息，当成绩>=90时输出“优秀”，当成绩>=70且<90时输出“良好”，大于等于60且小于70输出“及格”，其它则输出“不及格”。

test=#
select
sno,cno,ino,
case
when score>=90 then '优秀'
when score>=70 and score<90 then '良好'
when score>=60 and score<70 then '及格'
else '不及格' end as "score_level"
from exam.score;

sno | cno | ino | score_level
------+-----+-----+-------------
1001 | 10 | 101 | 良好
1002 | 10 | 101 | 优秀
1004 | 10 | 102 | 良好
1004 | 20 | 102 | 优秀
1005 | 10 | 105 | 及格
(5 行记录)
```
### 2. where子句
#### 2.1 等值查询
##### 2.1.1 数值型字段条件匹配
```
1. 查找分数大于80的学员信息。
test=# select sno,cno,ino from exam.score where score >80;
sno | cno | ino
------+-----+-----
1001 | 10 | 101
1001 | 20 | 101
1001 | 30 | 101
(3 行记录)
```
##### 2.1.2 字符型字段条件匹配（等值匹配）
```
1. 查看所在城市为Beijing的学员。
test=# select sno,sname,city from exam.student where city='Beijing';
sno | sname | city
------+-----------+---------
1002 | Yang Yang | Beijing
1003 | Liu Wei | Beijing
(2 行记录)
```
##### 2.1.3 字符型字段条件匹配（列表匹配）
```
1. 查看所在城市为Beijing或者Chongqing的学员。
test=# select sno,sname,city from exam.student where city in('Beijing','Chongqing');
sno | sname | city
------+-----------+-----------
1001 | Zhang San | Chongqing
1002 | Yang Yang | Beijing
1003 | Liu Wei | Beijing
1004 | Sun Juan | Chongqing
```
#### 2.2 多条件匹配
```
1. 查找所在城市为Beijing，工作岗位是Database Engineer的学员信息。
test=# select sno,sname,city from exam.student where city='Beijing' and job='Database Engineer';
sno | sname | city
------+-----------+---------
1002 | Yang Yang | Beijing
1003 | Liu Wei | Beijing
(2 行记录)
```
#### 2.3 逻辑运算
```
1. 逻辑运算包含与、或、非，分别使用逻辑运算符and、or、not表示。
2. 逻辑运算的优先级为：not > and > or，但可以使用括号改变优先级。
```
#### 2.4 模糊查询
```
在查询条件中无法确认完整的条件时，可以使用模糊查询，模糊查询的操作符是like，
在匹配值域中可使用通配符%和_，其中“%”代表匹配任意多个字符，“_”代表匹配一个字符。
```
```
1. 查询姓名以S字符开头的学员。
test=# select sno,sname from exam.student where sname like 'S%';
sno | sname
------+----------
1004 | Sun Juan
(1 行记录)

2. 查询姓名中第二个字符必须为i的学员信息。
test=# select sno,sname from exam.student where sname like '_i%';
sno | sname
------+--------------
1003 | Liu Wei
1005 | Li Xiaofeng
1008 | Qin Shanshan
(3 行记录)
```
#### 2.5 范围查询
```
在kingbase中可以使用 between...and... 进行范围查询。
between操作符用于指定一个范围的初始值，接着使用and连接这个范围的末尾值。
表示要在这两个值范围中查询，包含临界值。
```
```
1. 查找在2021年1月10日到7月10日参加培训的学员信息。
test=# select sname,reg_date from exam.student where reg_date between '2021-01-10' and '2021-07-10';
sname | reg_date
-----------+---------------------
Zhang San | 2021-01-10 00:00:00
Yang Yang | 2021-01-23 00:00:00
Liu Wei | 2021-07-10 00:00:00
(3 行记录)
```
#### 2.6 空值查询
```
在kingbase中，可以通过 is null 或者 is not null 进行控制查询。
```
```
1. 查询工作单位为空的学员信息。
test=# select sno,sname,company from exam.student where company is null;
sno | sname | company
------+--------------+---------
1007 | Feng Xiaoyue |
(1 行记录)
```
### 3. order by子句
```
在kingbase中，可以使用 order by 子句对查询结果进行排序，order by子句必须紧跟在select子句之后，并且可以通过一个或多个列名来指定排序的依据。

1. 使用 asc 表示升序排列（从小到大），缺省时asc为默认选项；
2. 使用 desc 表示降序排列（从大到小）；
descend /dɪˈsend/ n.降序排列

如果按照多个列进行排序，则可以在order by子句中指定多个列名，多个列明之间使用逗号分隔，并且按照指定的列顺序进行排序。
```
#### 3.1 升序排列使用 asc
```
说明：使用 asc 表示升序排列（从小到大），缺省时asc为默认选项；

1. 按照日期升序排列查询。
1.1 缺省时，asc为默认选项。
test=# select sno,sname,reg_date from exam.student order by reg_date;
sno | sname | reg_date
------+----------------+---------------------
1001 | Zhang San | 2021-01-10 00:00:00
1002 | Yang Yang | 2021-01-23 00:00:00
1003 | Liu Wei | 2021-07-10 00:00:00
(3 行记录)

1.2 使用asc关键字
test=# select sno,sname,reg_date from exam.student order by reg_date asc;
sno | sname | reg_date
------+----------------+---------------------
1001 | Zhang San | 2021-01-10 00:00:00
1002 | Yang Yang | 2021-01-23 00:00:00
1003 | Liu Wei | 2021-07-10 00:00:00
(3 行记录)
```
#### 3.2 降序排列使用 desc
```
说明：使用 desc 表示降序排列（从大到小）

1. 按照姓名降序排列
test=# select sno,sname from exam.student order by sname desc;
sno | sname
------+----------------
1006 | Zhang Xiaotian
1002 | Yang Yang
1004 | Sun Juan
1008 | Qin Shanshan
1005 | Li Xiaofeng
1007 | Feng Xiaoyue
(6 行记录)
```
#### 3.3 引用字段别名排序
```
1. 按照姓名降序排序
test=# select sno,sname as name from exam.student order by name desc;
sno | name
------+----------------
1001 | Zhang San
1002 | Yang Yang
1004 | Sun Juan
1008 | Qin Shanshan
1005 | Li Xiaofeng
1007 | Feng Xiaoyue
(6 行记录)
```
#### 3.4 引用字段的顺序号排序
```
说明：即按照查询的字段的排列顺序号进行排序（以下reg_date是第三个）。

1. 按照注册日期降序排列
test=# select sno,sname,reg_date from exam.student order by 3 desc;
sno | sname | reg_date
------+----------------+---------------------
1008 | Qin Shanshan | 2021-10-13 00:00:00
1004 | Sun Juan | 2021-09-06 00:00:00
1003 | Liu Wei | 2021-07-10 00:00:00
1001 | Zhang San | 2021-01-10 00:00:00
(4 行记录)
```
#### 3.5 多列组合排序
```
在kingbase中，可以使用order by语句对多列进行组合排序，列之间使用逗号隔开。

范例：在一个学生表中，先按照年龄升序排列，再按照姓名降序排列。
test=# select * from exam.users order by age asc,name desc;
可以理解为，先按照年龄升序排列，当年龄相同时则再按照姓名降序排列。
```
```
1. 先按照城市升序排列，同一城市的再按照注册日期降序排列。
test=# select sno,sname,city,reg_date from exam.student order by city asc,reg_date desc;
sno | sname | city | reg_date
------+----------------+-----------+---------------------
1003 | Liu Wei | Beijing | 2021-07-10 00:00:00
1002 | Yang Yang | Beijing | 2021-01-23 00:00:00
1006 | Zhang Xiaotian | Chengdu | 2021-10-10 00:00:00
1004 | Sun Juan | Chongqing | 2021-09-06 00:00:00
1001 | Zhang San | Chongqing | 2021-01-10 00:00:00
1005 | Li Xiaofeng | Guangzhou | 2021-09-01 00:00:00
1007 | Feng Xiaoyue | Tianjin | 2021-10-13 00:00:00
1008 | Qin Shanshan | Xian | 2021-10-13 00:00:00
(8 行记录)
```
### 4. 知识补充
#### 4.1 限制返回的行数
##### 4.1.1 使用limit子句
```
1. 输出查询结果的前三行
test=# select sno,sname,city from exam.student limit 3;
sno | sname | city
------+-----------+-----------
1001 | Zhang San | Chongqing
1002 | Yang Yang | Beijing
1003 | Liu Wei | Beijing
(3 行记录)
```
##### 4.1.2 使用子查询
```
1. 输出查询结果的前三行
test=# select * from (select rownum rn,sno,sname,city from exam.student) where rn<=3;
rn | sno | sname | city
----+------+-----------+-----------
1 | 1001 | Zhang San | Chongqing
2 | 1002 | Yang Yang | Beijing
3 | 1003 | Liu Wei | Beijing
(3 行记录)

说明：rn<=3 等同于 limit 3，其中rn 即 row number 行数。
```
#### 4.2 分页查询
```
当查询结果输出较多时，需要显示部分结果，可以使用以下方法：
1. 使用offset fetch来实现
ofset 表示跳过前多少行
fetch 表示显示接下来的多少行

2. 使用limit来实现
limit x y
x 表示跳过前多少行
y 表示显示接下来多少行
```
```
1. 跳过前5行，显示接下来的3行内容。
test=# select sno,sname from exam.student offset 5 rows fetch next 3 rows only;
sno | sname
------+----------------
1006 | Zhang Xiaotian
1007 | Feng Xiaoyue
1008 | Qin Shanshan
(3 行记录)
2. 跳过前5行，显示接下来的3行内容。
test=# select sno,sname from exam.student limit 5,3;
sno | sname
------+----------------
1006 | Zhang Xiaotian
1007 | Feng Xiaoyue
1008 | Qin Shanshan
(3 行记录)
```
#### 4.3 distinct关键字
```
在kingbase中，distinct用于对查询结果进行去重，distinct和select语句一起使用。
```
```
1. 查看student表中的城市数量（含重复值）。
test=# select count(city) from exam.student;
count
-------
8
(1 行记录)

2. 查看student表中的城市数量（去掉重复值）。
test=# select count(distinct city) from exam.student;
count
-------
6
(1 行记录)

3. 查看student表中的所有城市，并按照城市升序排列（去掉重复值）。
test=# select distinct city from exam.student order by city asc;
city
-----------
Beijing
Chengdu
Chongqing
Guangzhou
Tianjin
Xian
(6 行记录)
```