人大金仓数据库KingbaseES基于学习流程的SQL基础入门操作

关键字：

SQL、SELECT、UPDATE、DELETE、INSERT

1.什么是SQL？

SQL是“Structured Query Language”（结构化查询语言）的简称。是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

2.学前必备的SQL语法树说明

语法树：根据语法树的规则可以帮你看懂语句的意思，明白语句参数的含义。

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

• 大写形式表示SQL关键字;小写形式表示SQL语法中需要用户提供的参数
• |(竖线)分隔括号内的语法项目，只能选择一个项目
• ()(小括号)必选语法项目，必须键入小括号
• 可选语法项目，不必键入方括号
• {}（大括号）必选语法项目，不必键入大括号
• [,...]表示前面的项可重复，每一项由逗号分隔;[...]表示前面的项可重复，每一项由空格分隔

小贴士：这里面的大写指的是SQL的关键字，但是在实际的执行语句中，小写也不影响语句的执行操作。

3.常用数据类型的整理

（1） 数值类型

	对应别名	描述	范围
Smallint	Int2	带符号的2字节整数	-32768到+32767
integer	Int,Int4	带符号的4字节整数	-2147483648到+2147483647
Bigint	Int8	带符号的8字节整数	-9223372036854775808到+9223372036854775807
Numeric	Decimal	用户指定精度，精确	小数点前131072位；小数点后16383位
Real	Float4	4字节可变精度，不精确	6位十进制数字精度
Double precision	Float8	8字节可变精度，不精确	15位十进制数字精度
Smallserial		自增2字节整数	1到32767
Serial	Serial4	自增4字节整数	1到2147483647
Bigserial	Serial8	自增8字节整数	1到9223372036854775807

小贴士：以上的数值类型在使用时不需要为其规定长度。

（2）字符类型

	对应别名	描述	范围
Character varying[(n)]	Varchar[(n)]	变长字符串	有长度限制
Character[(n)]	Char[(n)]	定长字符串	不足补空白
text		变长字符串	无长度限制

小贴士：以上的字符类型在使用可以规定长度，也可以不规定长度。

（3）日期时间类型

	对应别名	描述	范围
Date		4字节只用于日期	4713BC到5874897AD
Timestamp[(p)][without time zone]		8字节日期和时间，无时区	4713BC到294276AD
Timestamp[(p)][with time zone]	Timestamptz	8字节日期和时间，有时区	4713BC到294276AD
Time[(p)][without time zone]	--	8字节只用于一日内时间，无时区	00:00:00到24:00:00
Time[(p)][with time zone]	Timetz	8字节只用于一日内时间，有时区	00:00:00+1459到24:00:00-1459
Interval[fields][(p)]	--	12字节存储时间间隔	-178000000年到178000000年

（4）布尔类型

	对应别名	描述	范围
Boolean	Bool	逻辑布尔量	True/false

（5）二进制类型 Bytea 存储二进制字符串

（6）特殊字符类型

	对应别名	描述	范围
“char”		1字节	单字节内部类型
name		64字节	对象名的内部类型

4.数据库的基本操作以及所遇问题的解决

（1）数据库表的创建

test=# create table students (num int(10),name varchar(225),age int(3)); 遇到的报错：

ERROR: syntax error at or near "("

LINE 1: create table students (num int(10),name varchar(225),age int...

报错原因：

在我们的数据库里面int类型是不可以定长的。

正确的创建语句：

test=# create table students (num int,name varchar(225),age int);

（2）查询上面创建的表： \d

（3）向表中插入数据

插入数据方法1：

test=# insert into students (num,name,age) values(1,'张三',12);

插入数据方法2：

test=# insert into students values(1,'李四',82);

（4）删除表的三种方法

• drop table + 表名字； test=# drop table qq2;

该方法删除整个表结构，包括表的内容，整个表都不见了。

• truncate table + 表名； test=# truncate table student2;

该方法是清空表内容，表还在，表的约束索引都在。

• delete from + 表名 where 列名 = 值； test=# delete from students where age = 82;

该方法删除表中被选中的某一条数据，每次删除表中的一行。

（5）修改表中某个值

test=# update students set num = 4 where age = 78;

（6）查询表内容

• 查询刚才创建的表的全部内容 test=# select * from students;

• 简单查询表的某个字段内容 SELECT * FROM employee;

SELECT empid,name FROM employee;

• 等值查询 SELECT empid,name FROM employee WHERE empid=100;

• 范围查询 SELECT empid,name FROM employee WHERE empid BETWEEN 100 AND 500;

还有一些其他的范围运算符：<>或！=、<、>、<=、>=

• 去重 SELECT DISTINCT empid FROM employee;

• 排序 SELECT empid,name FROM employee WHERE empid=100 ORDER BY cardno;

• 分组 SELECT empid,count(*) as num FROM employee GROUP BY empid;

test=# select num,count(*) as id from students group by num;

结果：

num | id
-----+----
       3 |  1
       4 |  1
      2 |  1
      1 |  2
(4 rows)

解析：选择要分组的字段，统计每个字段的个数，重命名为id出来。

错误语句：

test=# select age.count(*) from students group by num;

错误原因：将age后面应该是逗号，不是点。

• 分页查询 SELECT empid,name FROM employee ORDER BY empid LIMIT 5 OFFSET 0;

（7）下面将进行比较复杂的查询操作

（a）多表查询：

• 带ON的INNER连接 使用：test=# select * from students inner join class on students.num = class.num;

• 带USING的INNER连接-- --去掉重复的num字段 实践：test=# select * from students inner join class using(num);

• 带ON的左外连接

• 带USING的左外连接

• 带ON的右外连接

• 带USING的右外连接

• 全连接。

小贴士：以上是在查询时的多表查询，语句近似，所得内容根据关键字的变化，而变化。

• CROSS连接：假设有T1和T2两个表，生成的连接表来自T1和T2表行的每一种可能的组合(迪卡儿积)；如果·两个表分别为N行和M行，则连接表会有N*M行

实践：test=# select * from students cross join class ;

小贴士：该查询方法主打一个重组，将两个表进行重组显示。

(b) 子查询

将一个查询的结果作为另一个查询的数据来源或判断条件的查询。子查询指定了一个派生表，它必须被包围在圆括号里。

• IN/NOT IN子查询 语法格式如下：

Expression IN （subquery）
Expression NOT IN （subquery）

IN 和 NOT IN的右侧是圆括号起来的子查询，它必须返回一个列。Expression将被计算，其结果与subquery结果逐行进行比较

• 如果找到任何等于子查询行的值，则IN的结果为true
• 如果没有找到任何等于子查询的行的值，则IN的结果为false（包含子查询没有返回任何行的情况）

示例：

SELECT … FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
SELECT … FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2=fdt.c1+10 )

实践：test=# select name from students where num in (select num from class );

理解：该查询方法就是以一个表的查询结果作为这个表的查询条件，进行查询。

• EXISTS/NOT EXISTS子查询

EXISTS在SQL中的作用是检验查询是否返回数据

语法格式如下：

EXISTS(subquery)
EXISTS的参数是一个任意的SELECT语句或子查询。系统对于查询进行计算以判断它是否返回行。

• 如果子查询至少返回一行，那么EXISTS的结果为true
• 如果子查询没有返回行，那么EXISTS的结果为false

这个子查询通常只是运行到能判断它是否可以返回至少一行为止，而不是等到全部结束。因为结果只取决于是否返回行，而不取决于这些行的内容，所以这个子查询的输出列表通常是无关紧要的。

示例：

SELECT … FROM fdt WHERE EXISTS (SELECT 1 FROM t2)

实践：test=# select name from students where exists (select num from class);

没有搞明白这个查询方法的具体作用。

（c） LIKE查询

通配符：KES提供了两个通配符

1、“%”用于匹配任何字符序列

SELECT * FROM employee WHERE name like ‘%A%’; --返回姓名包含A的员工信息

SELECT * FROM employee WHERE name like ‘A%’; --返回所有以A开头的姓名

SELECT * FROM employee WHERE name like ‘%A’; --返回所有以A结尾的姓名

2、“_”用于匹配任意单个字符

SELECT * FROM employee WHERE name like ‘A_’; --返回所有以A开头，且只有两个字符的姓名。

实践：test=# select * from students where name like '%赵%';

小贴士：KES对LIKE进行了拓展，提供了和LIKE行为相似的ILIKE预算符。不同的是ILIKE在模式匹配时会忽视大小写。

写作总结：

为了更好的学习SQL基本操作，本文基于《KingbaseES SQL功能入门》PPT，首先，介绍了SQL的定义，其次，完成了SQL语法树的学习以及常用数据类型的整理，然后，学习了数据库的基础增、删、改、查操作。最后，在终端上对所学基础命令进行实践，整理报错和解决办法。

更多信息，参见https://help.kingbase.com.cn/v8/index.html