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            INT                              PRIMARY KEY,
   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优点

基础知识

数据库基本概念

名称 英文 描述
数据库管理系统 Database Management System 简称DBMS,管理数据库的软件,常见有:MySQLSQLitePostgreSQLMicrosoft SQL Server等。
关系型数据库管理系统 Relational Database Management System 简称RDBMS ,管理关系型数据库的软件,常见有:MySQLSQLitePostgreSQLMicrosoft SQL Server等。
数据库 database
table
列或字段 column 属性Attribute
row
主键
外键


SQL语法

SQL语句以关键字开始,如SELECTDELETECREATE,并以分号;结束。它们不区分大小写,除了固定的字符串值,如'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>;

除了SELECTFROM元素的其他都是可选的。 语言元素的顺序是强制性的。 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个逻辑运算符:NOTANDOR。其中ANDOR可用于连接多个“筛选条件”。

逻辑运算符 描述 示例
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 索引名称;

数据控制

资源

网站

相关网站

书籍

  • 《SQL必知必会》第四版-Ben Forta

参考文献