SQL:修订间差异
(→数据定义) |
无编辑摘要 |
||
第310行: | 第310行: | ||
===提交与回滚=== | ===提交与回滚=== | ||
插入(INSERT )、更新(UPDATE )、和删除(DELETE )操作在提交(COMMIT)保存前,这些数据都写入临时空间,可以通过回滚(ROLLBACK)来撤销一个或多个连续的操作。 | |||
语法如下,其中“WORK”关键字是可选的: | |||
<syntaxhighlight lang="sql"> | |||
COMMIT WORK; -- 提交保存, | |||
-- 从上次一COMMIT或ROLLBACK开始的所有INSERT、 UPDATE 和 DELETE 操作。 | |||
ROLLBACK WORK; -- 撤销, | |||
-- 从上次一COMMIT或ROLLBACK开始的所有INSERT、 UPDATE 和 DELETE 操作。 | |||
</syntaxhighlight> | |||
简单示例如下: | |||
<syntaxhighlight lang="sql"> | |||
DELETE FROM person WHERE id = 99; | |||
-- 该id搜索不到,但数据现在还并没有真正删除,可以打开另一个连接查找该表可以看到。 | |||
SELECT * FROM person WHERE id = 99; | |||
-- 回滚,将撤销删除id = 99这条数据。 | |||
ROLLBACK; | |||
DELETE FROM person WHERE id = 99; | |||
DELETE FROM person WHERE id = 88; | |||
-- 提交保存,将id = 88 和 id = 99这两条数据真正从表中删除。 | |||
COMMIT; | |||
</syntaxhighlight> | |||
自动提交保存(AUTOCOMMIT)功能不是标准的SQL一部分。一些数据库管理系统支持该功能,在INSERT、UPDATE 或 DELETE操作后自动保存,但回滚(ROLLBACK)功能将没有用处。 | |||
==数据定义== | ==数据定义== | ||
第330行: | 第358行: | ||
CREATE TABLE test_table ( | CREATE TABLE test_table ( | ||
-- 定义列(名称 / 类型 / 默认值 / 列约束) | -- 定义列(名称 / 类型 / 默认值 / 列约束) | ||
id | id INTEGER PRIMARY KEY, | ||
part_number CHAR(10) DEFAULT 'n/a' NOT NULL, | part_number CHAR(10) DEFAULT 'n/a' NOT NULL, | ||
part_name VARCHAR(500), | part_name VARCHAR(500), | ||
part_state DECIMAL DEFAULT -1 | part_state DECIMAL DEFAULT -1 | ||
); | ); | ||
</syntaxhighlight> | |||
===数据类型=== | |||
标准SQL的预定义数据类型如下: | |||
{| class="wikitable" style="width: 100%; | |||
! 类别 | |||
! 子类 | |||
! 格式 | |||
! 描述 | |||
! 示例 | |||
|- | |||
| rowspan="3"|字符类型 | |||
| 字符 | |||
| CHAR(n) | |||
| 固定n长度的字符 | |||
| CHAR(10) | |||
|- | |||
| 可变长度字符 | |||
| VARCHAR(n) | |||
| 可变长度字符,最长为n | |||
| VARCHAR(150) | |||
|- | |||
| 字符大对象 | |||
| CLOB | |||
| | |||
| CLOB | |||
|- | |||
| rowspan="3"|二进制类型 | |||
| 二进制 | |||
| BINARY(n) | |||
| | |||
| BINARY(10) | |||
|- | |||
| 可变长度二进制 | |||
| VARBINARY(n) | |||
| | |||
| VARBINARY(200) | |||
|- | |||
| 二进制大对象 | |||
| BLOB | |||
| | |||
| BLOB | |||
|- | |||
| rowspan="8"|数值类型 | |||
| 带固定精度和小数位数的数值 | |||
| NUMERIC(p,s) | |||
| p(precision)表示整数和小数的总位数,s(scale)表示小数位数,p和s都可以省略。 | |||
| NUMERIC(5,2) | |||
|- | |||
| 带固定精度和小数位数的数值 | |||
| DECIMAL(p,s) | |||
| p(precision)表示整数和小数的总位数,s(scale)表示小数位数,p和s都可以省略。 | |||
| DECIMAL | |||
|- | |||
| 小型整数 | |||
| SMALLINT | |||
| 标准SQL没有定义大小,范围大小会比INTEGER类型小。 | |||
| SMALLINT | |||
|- | |||
| 整数 | |||
| INTEGER | |||
| | |||
| INTEGER | |||
|- | |||
| 大型整数 | |||
| BIGINT | |||
| 标准SQL没有定义大小,范围大小会比INTEGER类型大。 | |||
| BIGINT | |||
|- | |||
| 浮点数 | |||
| FLOAT(p) | |||
| | |||
| FLOAT(3) | |||
|- | |||
| 单精度浮点数 | |||
| REAL | |||
| | |||
| REAL | |||
|- | |||
| 双精度 | |||
| DOUBLE PRECISION | |||
| | |||
| DOUBLE PRECISION | |||
|- | |||
| rowspan="4"|日期时间类型 | |||
| 日期 | |||
| DATE(p) | |||
| | |||
| DATE | |||
|- | |||
| 时间 | |||
| TIME(p) | |||
| | |||
| TIME | |||
|- | |||
| 时间戳 | |||
| TIMESTAMP(p) | |||
| | |||
| TIMESTAMP(9) | |||
|- | |||
| 时间间隔 | |||
| INTERVAL | |||
| | |||
| INTERVAL YEAR TO MONTH | |||
|- | |||
| rowspan="1"|布尔类型 | |||
| 布尔类型 | |||
| BOOLEAN | |||
| true 或 false | |||
| BOOLEAN | |||
|- | |||
| rowspan="1"|XML类型 | |||
| XML类型 | |||
| XML | |||
| | |||
| XML | |||
|- | |||
| rowspan="1"|JSON类型 | |||
| JSON类型 | |||
| JSON | |||
| 在[https://en.wikipedia.org/wiki/SQL:2016 SQL:2016]中新加 | |||
| JSON | |||
|- | |||
|} | |||
但是不同的数据库管理系统定义的数据类型不同,可以在各自帮助文档查看。 | |||
下表为[[SQLite]]定义的数据类型,所有存入SQLite数据库的值都被保存为下表5中类型之一: | |||
{{SQLite数据类型表}} | |||
布尔值在SQLite没有单独的存储类,使用INTEGER类的0 (false) 和 1 (true)存储。 | |||
SQLite也没有单独的日期时间数据类型,而是通过内置的时间日期函数,使用TEXT, REAL, 或INTEGER数据类型保存值。 | |||
为了最大化与和其他SQL数据库引擎之间的兼容性,SQLite可以自动转化为适当的数据类型存储。 | |||
===索引=== | |||
索引是所有SQL数据库的关键特性。它们提供对数据的快速访问,但也会降低插入(INSERT)和更新(UPDATE)数据的速度。几乎所有的实现都支持'''CREATE INDEX'''语句。 | |||
可以在单列或多列上创建索引,多列索引一般用于多列一起查询情况,例如姓氏列和名字列创建一个多列索引可能是有用的。使用'''CREATE INDEX'''创建普通索引,语法如下: | |||
<syntaxhighlight lang="sql"> | |||
CREATE INDEX 索引名称 | |||
ON 表名 (列名1, 列名2, ...); | |||
</syntaxhighlight> | |||
唯一索引是索引键不含重复值,因此表中的每一行应该是唯一的。使用'''CREATE UNIQUE INDEX'''创建唯一索引,语法如下: | |||
<syntaxhighlight lang="sql"> | |||
CREATE UNIQUE INDEX 索引名称 | |||
ON 表名 (列名1, 列名2, ...); | |||
</syntaxhighlight> | |||
使用'''DROP INDEX'''删除索引,语法如下: | |||
<syntaxhighlight lang="sql"> | |||
DROP INDEX 索引名称; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
2020年10月24日 (六) 07:01的版本
SQL(Structured Query Language,结构化查询语言),是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。
SQL基于关系代数和元组关系演算。SQL的范围包括数据插入、查询、更新和删除,数据库模式创建和修改,以及数据访问控制。
简介
时间轴
- 1970年代初,由IBM公司San Jose,California研究实验室的埃德加·科德发表将数据组成表格的应用原则(Codd's Relational Algebra)。
- 1974年,同一实验室的D.D.Chamberlin和R.F. Boyce对Codd's Relational Algebra在研制关系数据库管理系统System R中,研制出一套规范语言-SEQUEL(Structured English Query Language)
- 1976年11月,IBM Journal of R&D上公布新版本的SQL(叫SEQUEL/2)
- 1979年,ORACLE公司首先提供商用的SQL,IBM公司在DB2和SQL/DS数据库系统中也实现了SQL。
- 1980年,改名为SQL。
- 1986年10月,美国国家标准学会(ANSI)采用SQL作为关系数据库管理系统的标准语言(ANSI X3. 135-1986)。
- 1987年,国际标准化组织(ISO)采纳这一标准为国际标准。
- 1989年,美国ANSI采纳在ANSI X3.135-1989报告中定义的关系数据库管理系统的SQL标准语言,称为ANSI SQL 89,该标准替代ANSI X3.135-1986版本。
SQL优点
- 几乎所有重要的DBMS如:MySQL、SQLite、PostgreSQL及Microsoft SQL Server等都支持SQL。
- SQL简单易学
基础知识
数据库基本概念
名称 | 英文 | 描述 |
---|---|---|
数据库管理系统 | Database Management System | 简称DBMS,管理数据库的软件,常见有:MySQL、SQLite、PostgreSQL及Microsoft SQL Server等。 |
关系型数据库管理系统 | Relational Database Management System | 简称RDBMS ,管理关系型数据库的软件,常见有:MySQL、SQLite、PostgreSQL及Microsoft SQL Server等。 |
数据库 | database | |
表 | table | |
列或字段 | column | 属性Attribute |
行 | row | |
主键 | ||
外键 |
SQL语法
SQL语句以关键字开始,如SELECT
、DELETE
或CREATE
,并以分号;
结束。它们不区分大小写,除了固定的字符串值,如'Mr. Brown'
了解更多 >> 维基教科书:SQL - 语言元素
数据查询
SQL中最常见的操作是查询,它是通过SELECT
语句执行的。SELECT
使用一个或多个表或表达式来检索数据。标准的SELECT
语句不会对数据库有持久影响。SELECT
的一些非标准的实现可以有持久影响,如一些数据库中有SELECT INTO
语法。
SELECT
语句从一个或多个的表或视图中检索数据。 它通常由下列语言元素构成:
SELECT 显示内容 -- 大部分是列名的列表
FROM 表名 -- 表或视图名称及其别名
WHERE 筛选条件
GROUP BY <group_by_clause>
HAVING <having_clause>
ORDER BY <order_by_clause>
OFFSET <offset_clause>
FETCH <fetch_first_or_next_clause>;
除了SELECT
和FROM
元素的其他都是可选的。 语言元素的顺序是强制性的。 SELECT
语句可以嵌套,以递归方式查询。
了解更多 >> 维基教科书:SQL - SELECT 基本知识
基本查询
选取数据 | 语句示例 | 描述 |
---|---|---|
选取单表所有数据 | SELECT * FROM 表名; | * 号为通配符
|
选取单列数据 | SELECT 列名 FROM 表名; | |
选取多列数据 | SELECT 列名1, 列名2, 列名3 FROM 表名; | 列名用逗号隔开,最后一个列名不加逗号。 |
别名 | SELECT 列名 AS 别名 FROM 表名; SELECT 列名1 别名1, 列名2 别名2, FROM 表名; |
使用AS 关键词可以设置别名,可以显示更清楚,方便引用。通常AS可以省略,但不建议,使用AS增加了可读性。 |
去除重复值 | SELECT DISTINCT 列名 FROM 表名; | 将该列中重复值去除 |
选取部分行 | SELECT * FROM 表名 LIMIT 开始行数,结束行数; SELECT * FROM 表名 LIMIT 行数; |
LIMIT接收2个整数参数,其中开始行数默认为0,第二个语句是选取前几行的简写。 |
从某行开始选取部分行 | SELECT * FROM 表名 LIMIT 选取行数 OFFSET 偏移行数; | 如LIMIT 10 OFFSET 30 表示从第30行开始选取10行。
|
算数计算 | SELECT 列名+100 FROM 表名; SELECT 列名1-列名2 AS 别名 FROM 表名; SELECT 列名1, 列名2*3.14 AS 别名 FROM 表名; SELECT 列名/2.5 AS 别名 FROM 表名; |
四种算术符:+ 加、- 减、* 乘和/ 除。生成的计算字段(列)可以通过 AS 关键字设置别名,用于显示和调用。
|
排序
ORDER BY子句用于对一列或多列进行排序,两种排序方式:
- 升序,使用
ASC
关键字,默认方式,关键字可以省略 - 降序,使用
DESC
关键字
选取数据 | 语句 | 描述 |
---|---|---|
升序排序 | SELECT * FROM 表名 ORDER BY 列名; | 选取所有数据,按某列升序排序显示。 |
降序排序 | SELECT * FROM 表名 ORDER BY 列名 DESC; | 选取所有数据,按某列降序排序显示。 |
多列排序 | SELECT * FROM 表名 ORDER BY 列名1 DESC , 列名2; | 选取所有数据,先按列名1的列降序排序,再按列名2的列升序排序。 |
条件筛选
WHERER子句用于按条件筛选数据。
运算符 | 描述 | 示例 |
---|---|---|
= | 相等 | SELECT * FROM 表名 WHERE 列名 = 值; |
<> | 不相等,这个是ISO标准写法,大部分也支持!= 表示不相等 |
SELECT 列名1,列名2 FROM 表名 WHERE 列名1 <> 值; |
> | 大于 | SELECT * FROM 表名 WHERE 列名 > 值; |
< | 小于 | SELECT * FROM 表名 WHERE 列名 < 值; |
>= | 大于等于 | SELECT * FROM 表名 WHERE 列名 >= 值; |
<= | 小于等于 | SELECT * FROM 表名 WHERE 列名 <= 值; |
BETWEEN AND | 在某个范围内 | SELECT * FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2; |
LIKE | 使用通配符匹配 通配符 % 表示0个或多个字符 通配符 _ 表示单个字符 |
SELECT * FROM 表名 WHERE 列名 LIKE 'CF%'; 筛选该列中以CF开头的值 |
IN | 与一组值进行比较 | SELECT * FROM 表名 WHERE 列名 IN (值1, 值2, 值3); |
注意:值为字符串需要使用'
或"
包裹。
WHERER子句可以使用3个逻辑运算符:NOT
、AND
和 OR
。其中AND
和OR
可用于连接多个“筛选条件”。
逻辑运算符 | 描述 | 示例 |
---|---|---|
AND | 与,所有结果都为真才是真。 | |
OR | 或,有一个结果为真便是真。 | |
NOT | 取反,取与结果相反的值。 |
分组
GROUP BY子句可以对一列或多列进行分组,与聚合函数一起使用可以实现分组统计。
聚合函数 | 描述 | 示例 |
---|---|---|
COUNT(列名 或 *) | 行数。COUNT(列名) 统计该列行数,存在空值不计数。COUNT(*) 统计行数,一行中存在空值也计数。
|
SELECT 列名, COUNT(列名2) FROM 表名 GROUP BY 列名; |
MIN(列名) | 最小值。值为字符串时,按字符序列进行比较。 | SELECT 列名, MIN(列名2) AS 列2最小值 FROM 表名 GROUP BY 列名; |
MAX(列名) | 最大值。值为字符串时,按字符序列进行比较。 | SELECT 列名, MAX(列名2) FROM 表名 GROUP BY 列名 ORDER BY 列名; |
SUM(列名) | 所有值的和。 | SELECT 列名, SUM(列名2) , MAX(列名2) FROM 表名 GROUP BY 列名; |
AVG(列名) | 算术平均值。 | SELECT 列名, AVG(列名2) , SUM(列名2) FROM 表名 GROUP BY 列名; |
使用函数处理
多表查询
多表查询,一般通过JOIN关键字将表建立连接,再查询。
基本语法:
SELECT 显示的列
FROM 表1 表1别名 -- 设置表别名,方便引用
JOIN 表2 表2别名 ON 连接条件
... --其他可选SELECT子句
连接类型 | 描述 | 示例 |
---|---|---|
JOIN / INNER JOIN | 内连接,两个表的交集,最常用的一种。 | SELECT 表1.列名, 表2.列名2 FROM 表1 JOIN 表2 ON 表1.列名 = 表2.列名; |
LEFT JOIN / LEFT OUTER JOIN | 左连接,左表的全部行,连接右表中两个表的交集行,左表有右表无使用空(NULL)或其他值替换。 | SELECT 表1.列名, 表2.列名2 FROM 表1 LEFT JOIN 表2 ON 表1.列名 = 表2.列名; |
RIGHT JOIN / RIGHT OUTER JOIN | 右连接,右表的全部行,连接左表中两个表的交集行,右表有左表无使用空(NULL)或其他值替换。 | SELECT 表1.列名, 表2.列名2 FROM 表1 RIGHT JOIN 表2 ON 表1.列名 = 表2.列名; |
FULL JOIN / FULL OUTER JOIN | 完全连接,左表的全部行,连接右表的全部行,不存在交集的行使用空(NULL)或其他值替换。 | SELECT 表1.列名, 表2.列名2 FROM 表1 FULL JOIN 表2 ON 表1.列名 = 表2.列名; |
Cross Join | 交叉连接 ,左边全部行与右边的全部行组合,返回大量行(两表行数的乘积)。注意:使用这种一定要添加限制条件 | SELECT 表1.列名, 表2.列名2 FROM 表1 JOIN 表2 SELECT 表1.列名, 表2.列名2 FROM 表1, 表2 |
数据操作
插入数据
INSERT INTO语句用于将一行或多行插入到表中。
基本语法:
INSERT INTO 表名 (列名1,列名2,列名3,...)
VALUES (值1,值2,值3,...),
(值a,值b,值c,...),
...;
-- 当一行值的个数和顺序与表的相等,可以省略列名
INSERT INTO 表名
VALUES (值1,值2,值3,...),
(值a,值b,值c,...),
...;
更新数据
UPDATE 语句用于更新表中已存在的数据。
基本语法:
UPDATE 表名
SET 列名1=值1,
列名2=值2,
...
WHERE 筛选条件;
WHERE子句筛选出的行将按SET子句修改。注意:如果WHERE子句省略,所有行将被修改。 当WHERE子句没有筛选出行,没有行被更新,不会发生任何错误或异常。
删除数据
DELETE 语句用于删除表中的数据。 基本语法:
DELETE 表名
WHERE 筛选条件;
WHERE子句筛选出的零个、一个或多个行将被删除。注意:如果WHERE子句省略,所有行将被删除。
提交与回滚
插入(INSERT )、更新(UPDATE )、和删除(DELETE )操作在提交(COMMIT)保存前,这些数据都写入临时空间,可以通过回滚(ROLLBACK)来撤销一个或多个连续的操作。
语法如下,其中“WORK”关键字是可选的:
COMMIT WORK; -- 提交保存,
-- 从上次一COMMIT或ROLLBACK开始的所有INSERT、 UPDATE 和 DELETE 操作。
ROLLBACK WORK; -- 撤销,
-- 从上次一COMMIT或ROLLBACK开始的所有INSERT、 UPDATE 和 DELETE 操作。
简单示例如下:
DELETE FROM person WHERE id = 99;
-- 该id搜索不到,但数据现在还并没有真正删除,可以打开另一个连接查找该表可以看到。
SELECT * FROM person WHERE id = 99;
-- 回滚,将撤销删除id = 99这条数据。
ROLLBACK;
DELETE FROM person WHERE id = 99;
DELETE FROM person WHERE id = 88;
-- 提交保存,将id = 88 和 id = 99这两条数据真正从表中删除。
COMMIT;
自动提交保存(AUTOCOMMIT)功能不是标准的SQL一部分。一些数据库管理系统支持该功能,在INSERT、UPDATE 或 DELETE操作后自动保存,但回滚(ROLLBACK)功能将没有用处。
数据定义
创建表
CREATE TABLE语句用于创建表。
CREATE TABLE 表名 (
列名1 数据类型 默认值 列约束,
列名1 数据类型 默认值 列约束,
...,
表约束,
表约束,
...
);
语句中默认值、列约束和表约束为可选。一个简单示例如下:
CREATE TABLE test_table (
-- 定义列(名称 / 类型 / 默认值 / 列约束)
id INTEGER PRIMARY KEY,
part_number CHAR(10) DEFAULT 'n/a' NOT NULL,
part_name VARCHAR(500),
part_state DECIMAL DEFAULT -1
);
数据类型
标准SQL的预定义数据类型如下:
类别 | 子类 | 格式 | 描述 | 示例 |
---|---|---|---|---|
字符类型 | 字符 | CHAR(n) | 固定n长度的字符 | CHAR(10) |
可变长度字符 | VARCHAR(n) | 可变长度字符,最长为n | VARCHAR(150) | |
字符大对象 | CLOB | CLOB | ||
二进制类型 | 二进制 | BINARY(n) | BINARY(10) | |
可变长度二进制 | VARBINARY(n) | VARBINARY(200) | ||
二进制大对象 | BLOB | BLOB | ||
数值类型 | 带固定精度和小数位数的数值 | NUMERIC(p,s) | p(precision)表示整数和小数的总位数,s(scale)表示小数位数,p和s都可以省略。 | NUMERIC(5,2) |
带固定精度和小数位数的数值 | DECIMAL(p,s) | p(precision)表示整数和小数的总位数,s(scale)表示小数位数,p和s都可以省略。 | DECIMAL | |
小型整数 | SMALLINT | 标准SQL没有定义大小,范围大小会比INTEGER类型小。 | SMALLINT | |
整数 | INTEGER | INTEGER | ||
大型整数 | BIGINT | 标准SQL没有定义大小,范围大小会比INTEGER类型大。 | BIGINT | |
浮点数 | FLOAT(p) | FLOAT(3) | ||
单精度浮点数 | REAL | REAL | ||
双精度 | DOUBLE PRECISION | DOUBLE PRECISION | ||
日期时间类型 | 日期 | DATE(p) | DATE | |
时间 | TIME(p) | TIME | ||
时间戳 | TIMESTAMP(p) | TIMESTAMP(9) | ||
时间间隔 | INTERVAL | INTERVAL YEAR TO MONTH | ||
布尔类型 | 布尔类型 | BOOLEAN | true 或 false | BOOLEAN |
XML类型 | XML类型 | XML | XML | |
JSON类型 | JSON类型 | JSON | 在SQL:2016中新加 | JSON |
但是不同的数据库管理系统定义的数据类型不同,可以在各自帮助文档查看。
下表为SQLite定义的数据类型,所有存入SQLite数据库的值都被保存为下表5中类型之一:
SQLite类型 | 描述 |
---|---|
NULL | 空值 |
INTEGER | 整数,根据值的大小存储,最大8字节,即最大2^63-1=9223372036854775807。 |
REAL | 浮点数, 存储为 8 字节的 IEEE 浮点数字。 |
TEXT | 文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。 |
BLOB | 二进制值,直接存储,不经过转换,如存储图片等。 |
布尔值在SQLite没有单独的存储类,使用INTEGER类的0 (false) 和 1 (true)存储。
SQLite也没有单独的日期时间数据类型,而是通过内置的时间日期函数,使用TEXT, REAL, 或INTEGER数据类型保存值。
为了最大化与和其他SQL数据库引擎之间的兼容性,SQLite可以自动转化为适当的数据类型存储。
索引
索引是所有SQL数据库的关键特性。它们提供对数据的快速访问,但也会降低插入(INSERT)和更新(UPDATE)数据的速度。几乎所有的实现都支持CREATE INDEX语句。
可以在单列或多列上创建索引,多列索引一般用于多列一起查询情况,例如姓氏列和名字列创建一个多列索引可能是有用的。使用CREATE INDEX创建普通索引,语法如下:
CREATE INDEX 索引名称
ON 表名 (列名1, 列名2, ...);
唯一索引是索引键不含重复值,因此表中的每一行应该是唯一的。使用CREATE UNIQUE INDEX创建唯一索引,语法如下:
CREATE UNIQUE INDEX 索引名称
ON 表名 (列名1, 列名2, ...);
使用DROP INDEX删除索引,语法如下:
DROP INDEX 索引名称;
数据控制
资源
网站
- ISO:ISO/IEC 9075-1:2016 或SQL:2016,是ISO发布的第八版标准SQL。
相关网站
书籍
- 《SQL必知必会》第四版-Ben Forta