SQL

Eric（讨论 | 贡献）2021年4月5日 (一) 09:37的版本

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，管理数据库的软件，常见有：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的预定义数据类型如下：

类别 子类 格式 描述 示例
字符类型 字符 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可以自动转化为适当的数据类型存储。

数据定义 DDL

创建表

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数据库的关键特性。它们提供对数据的快速访问，但也会降低插入（INSERT）和更新（UPDATE）数据的速度。几乎所有的实现都支持CREATE INDEX语句。

可以在单列或多列上创建索引，多列索引一般用于多列一起查询情况，例如姓氏列和名字列创建一个多列索引可能是有用的。使用CREATE INDEX创建普通索引，语法如下： 

CREATE INDEX 索引名称
ON 表名 (列名1, 列名2, ...);

唯一索引是索引键不含重复值，因此表中的每一行应该是唯一的。使用CREATE UNIQUE INDEX创建唯一索引，语法如下： 

CREATE UNIQUE INDEX 索引名称
ON 表名 (列名1, 列名2, ...);

使用DROP INDEX删除索引，语法如下： 

DROP INDEX 索引名称;

删除表

DROP TABLE命令用于数据库中删除表。该命令会删除表的所有数据和定义，它将表作为一个整体处理，所以速度非常快。语法如下： 

DROP TABLE 表名;


数据操作 DML

插入数据

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）功能将没有用处。

数据查询 DQL

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子句
分类 命令 描述
内连接 SELECT 字段名
FROM 左表
[INNER] JOIN 右表 ON 条件; 		按条件取两表交集，INNER可以省略。
隐式内链接 SELECT 字段名
FROM 左表, 右表
WHERE 连接条件; 		结果与内连接查询一样。
左外连接 SELECT 字段名
FROM 左表
LEFT [OUTER] JOIN 右表 ON 条件; 		以左表为基准，左边所有数据，以及右表与左表有交集部分。对于左表有而右表没有的数据，右边显示NULL。OUTER可省略。
右外连接 SELECT 字段名
FROM 左表
RIGHT [OUTER ]JOIN 右表 ON 条件 		以右表为基准，右边所有数据，以及左表与右表有交集部分。对于右表有而左表没有的数据，左边显示NULL。OUTER可省略。与左外连接相似，一般使用左外连接即可。
交叉连接查询 SELECT 字段名
FROM 表1, 表2; 		交叉连接（Cross Join），左边全部行与右边的全部行组合，返回大量行，两表行数的乘积（笛卡尔积）。注意：使用这种一定要添加限制条件。

数据控制 DCL

资源

网站

相关网站

书籍

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

参考文献

取自“https://zxdd.com/index.php?title=SQL&oldid=508”