科普文：软件架构数据库系列之【MySQL数据库SQL命令和SQL标准梳理】

概叙

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

SQL 是用于访问和处理数据库的标准的计算机语言。

SQL历史

History of SQL

​SQL语言1974年由Boyce和Chamberlin提出，并首先在IBM公司研制的关系数据库系统SystemR上实现。由于它具有功能丰富、使用方便灵活、语言简洁易学等突出的优点，深受计算机工业界和计算机用户的欢迎。1980年10月，经美国国家标准局（ANSI）的数据库委员会X3H2批准，将SQL作为关系数据库语言的美国标准，同年公布了标准SQL，此后不久，国际标准化组织（ISO）也作出了同样的决定。

SQL从功能上可以分为3部分：数据定义、数据操纵和数据控制。 

SQL的核心部分相当于关系代数，但又具有关系代数所没有的许多特点，如聚集、数据库更新等。它是一个综合的、通用的、功能极强的关系数据库语言。

其特点是： 

1、数据描述、操纵、控制等功能一体化。 

2、两种使用方式，统一的语法结构。SQL有两种使用方式。一是联机交互使用，这种方式下的SQL实际上是作为自含型语言使用的。另一种方式是嵌入到某种高级程序设计语言（如C语音等）中去使用。前一种方式适合于非计算机专业人员使用，后一种方式适合于专业计算机人员使用。尽管使用方式不向，但所用语言的语法结构基本上是一致的。 

3、高度非过程化。SQL是一种第四代语言（4GL），用户只需要提出“干什么”，无须具体指明“怎么干”，像存取路径选择和具体处理操作等均由系统自动完成。 

4、语言简洁，易学易用。尽管SQL的功能很强，但语言十分简洁，核心功能只用了9个动词。SQL的语法接近英语口语，所以，用户很容易学习和使用。

SQL功能

SQL具有数据定义、数据操纵、和数据控制的功能。

1、SQL数据定义功能：能够定义数据库的三级模式结构，即外模式、全局模式和内模式结构。在SQL中，外模式又叫做视图（View），全局模式简称模式（Schema），内模式由系统根据数据库模式自动实现，一般无需用户过问。

2、SQL数据操纵功能：包括对基本表和视图的数据插入、删除和修改，特别是具有很强的数据查询功能。

3、SQL的数据控制功能：主要是对用户的访问权限加以控制，以保证系统的安全性。

SQL分类

SQL功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词：CREATE、ALTER、DROP、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。

可以分成6种SQL：

1、数据查询语言（DQL: Data Query Language）

其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。

2、数据操作语言（DML：Data Manipulation Language）

其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。

3、事务控制语言（TCL：Transaction Control Language）

事务控制语句，用于控制事务，它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

4、数据控制语言（DCL：Data Control Language）

即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。

它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

5、数据定义语言（DDL：Data Definition Languages）

即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。

在数据库中创建新表或修改、删除表（CREATE TABLE 或 DROP TABLE）；为表加入索引等。

6、指针控制语言（CCL：Cursor Control Language）

规定了 SQL语句在宿主语言的程序中的使用的规则，是SQL语言的分类之一。CCL包含了DECLARE CURSOR(声明游标)、FETCH INTO(进入)和UPDATE WHERE CURRENT(更新当前位置)等语句，主要用于用于对一个或多个表单独行的操作。

它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

使用基于CCL的数据库：

1. MySQL - 是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。
2. Oracle - 是是甲骨文公司的一款关系数据库管理系统。
3. Hive – 是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载。
4. HBase –是一个分布式的、面向列的开源数据库。

MySQL对SQL的支持

以下是MySQL的一些主要特点和功能：

1.开源性：MySQL是开源软件，可以免费使用和修改，具有强大的社区支持。

2.可扩展性：MySQL支持高度可扩展的架构，适用于小型应用到大型企业级应用。

3.跨平台支持：MySQL可以在多个操作系统上运行，包括Windows、Linux、macOS等。

4.高性能：MySQL具有出色的性能和处理能力，能够处理大量的并发请求，并提供高效的查询和数据操作。

5.安全性：MySQL提供了多种安全机制来确保数据的机密性和完整性，包括用户身份验证、访问控制等。

6.支持标准SQL语言：MySQL遵循SQL标准，并提供了广泛的SQL功能，包括数据查询、更新、事务处理等。

7.多种存储引擎：MySQL支持多种存储引擎，如InnoDB、MyISAM等，每种引擎都有不同的特点和适用场景。

8.数据复制和高可用性：MySQL提供了复制功能，可以将数据复制到多个服务器以实现高可用性和数据备份。

9.数据库管理工具：MySQL提供了命令行工具和图形化管理工具（如Navicat、phpMyAdmin等），方便用户管理和监控数据库。

MySQL 8.0 版本前，有不少技术伙伴会吐槽 MySQL 对于 SQL 标准的支持的程度。但是在当前 8.0 版本下，MySQL 对于 SQL 语法的支持度已经越来越好，甚至在某些方面超过了商业数据库 Oracle。

MySQL完全兼容SQL92/SQL99，MySQl8.0开始支持窗口函数，也就部分支持SQL2003标准。

科普文：软件架构数据库系列之【MySQL的sql_mode参数】-CSDN博客

从MySQL 5.6开始，就有默认的SQL模式sql_mode（即严格的模式）。再到后面的版本MySQL5.7，8.0，8.4对sql_mode限制的越来越严格和规范。

动态改变SQL模式：SET [SESSION|GLOBAL] sql_mode='modes'

设置GLOBAL变量时需要拥有SUPER权限，并且会影响从那时起连接的所有客户端的操作。设置SESSION变量只影响当前的客户端。任何客户端可以随时更改自己的会话sql_mode值。

非严格模式：

mysql> set @@sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tbl_kenyon values(99,'123456');

Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_kenyon values(1,'1234567');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into tbl_kenyon values('','123456');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;

+---------+------+------------------------------------------------------+

| Level | Code | Message |

+---------+------+------------------------------------------------------+

| Warning | 1366 | Incorrect integer value: '' for column 'id' at row 1 |

+---------+------+------------------------------------------------------+

1 row in set (0.00 sec)

两种模式各有利弊，通常生产上会设置严格模式，可以保证数据的完整性。非严格模式并不一定就是差的，在数据一致性要求不严的场景下某些忽略警告操作可能更方便，比如不同版本数据库的非关键数据导入：

mysql> load data infile '/data/source_data/trace/tbl_msg.sql' into table tbl_msg;

ERROR 1366 (HY000): Incorrect integer value: 'NULL' for column 'pid' at row 1

换个模式导入：

mysql> set @@session.sql_mode='';

Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/data/source_data/trace/ tbl_msg.sql' into table tbl_msg;

Query OK, 23525 rows affected, 65535 warnings (2.80 sec)

Records: 23525 Deleted: 0 Skipped: 0 Warnings: 185707

了解一下关于SQL语法的一些注意事项：

1. SQL 语句可以单行或多行书写，以分号结尾。

2. 可使用空格和缩进来增强语句的可读性。

3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。

4. 3 种注释

① 单行注释:  -- 注释内容 或 # 注释内容(mysql 特有)

② 多行注释:  /* 注释 */

DDL（数据定义语言）

DDL语言：全面数据定义语言（Data Define Language），是用来定义和管理数据对象，如数据库，数据表等。DDL命令有CREATE（创建）、DROP（删除）、ALTER（修改）。

下面用代码给大家举例子：

-- SQL语法不区分大小写
-- 每一句结束的时候都要用一个分号;
# 库的操作
-- 显示所有的库
show databases;
-- 创建一个库
-- create database 库名;
create database ku;
-- 删除一个库
-- drop database 库名;
drop database ku;
-- 使用库
-- use 库名;
use ku;
# 表的操作
-- 查看库中所有的表
show tables;
-- 建表
create table 表名(
	字段名  类型  属性,
	字段名  类型  属性,
	....
	字段名  类型 属性
);
create table tab_teacher(
	tea_name varchar(10),
	tea_sex char(1),
	tea_birthday datetime,
	tea_money decimal(20,1)
);
show tables;
-- 查看表结构
desc tab_teacher;
show create table tab_teacher;
-- ` 反引号 让关键词失效
CREATE TABLE `tab_teacher` (
  `tea_name` varchar(10) DEFAULT NULL,
  `tea_sex` char(1) DEFAULT NULL,
  `tea_birthday` datetime DEFAULT NULL,
  `tea_money` decimal(20,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
    COLLATE=utf8mb4_0900_ai_ci

DML（数据操作语言）

DML语言：数据操作语言（Data Manipulation Language），是用于操作数据库对象中所包含的数据。DML命令有INSERT（增加）、DELETE（删除）、UPDATE（修改）。

下面用代码给大家举例子：

# DML 语句
-- 新增
-- 语法
-- insert into 表名(字段名,字段名...字段名) values(值,值...值);
-- 日期用字符串的形式表示
insert into student(sid,sname,birthday,ssex,classid) values(9,'张三','1999-1-1','男',3);
insert into student(sid,ssex,classid) values(11,'男',2);
-- 让主键自增
insert into student(sname) values("王桑");
insert into student values(default,'老王','1970-6-1','男',2);
insert into student values(null,'老王','1970-6-1','男',2);
-- 一次性插入多条数据
insert into student(sname,ssex) values('王帅帅','男'),('王靓靓','男'),('王妹妹','女');
-- 不常用的新增方式
-- 表都要存在
create table stu1(
	xingming varchar(10),
	ssex varchar(2)
)
-- insert into select 
insert into stu1 select sname,ssex from student;
-- 新建表的时候插入数据
-- 新表不能存在
create table newstu select sname,birthday,ssex from student;
-- 修改
-- 语法
-- update 表名 set 字段名=值,字段名=值... where 子句
update stu1 set xingming = '赵雷雷';
update newstu set ssex= '女' where sname='老王';
-- 范围
update student set ssex = '女',classid = 10 where sid >= 10 and sid <= 15;
-- between 小数据 and 大数据
update student set ssex='呵呵',classid = 20 where sid between 10 and 15;
-- 删除
-- delete from 表名  where 子句
delete from stu1;
delete from student  where sname = '老王';
-- 清空表
truncate 表名
truncate student;

DQL（数据查询语言）

DQL语言：数据查询语言（Data Query Language），是用于查询数据库数据。DQL命令有SELECT（查询）。

下面用代码给大家举例子：

# DQL
-- 查询
-- 查询表所有行和列的数据(得到的是一张虚拟表)
-- select * from 表名;
select * from student;
-- 查询指定字段
-- select 字段名1，字段名2... from 表名;
select sid,sname,birthday,ssex,classid from student;
-- 字段起别名
-- select 旧字段名 as '新字段名';
select sname as '姓名', birthday '生日',ssex 性别 from student;
-- 去除重复 distinct
-- select distinct 字段名... from 表名; 
select distinct ssex,classid,sid from student;
-- 带条件的查询  WHERE 子句
select * from student where ssex = '男' and classid = 1;
-- 生日 大于 1990-1-1 的学生
select * from student where birthday < '1990-1-1';
-- 模糊查询 like
insert into student(sname) 
values('张三丰'),('张三'),('张三三');
-- 张字有关的数据
-- 模糊符号 % 任意多的任意字符
select * from student where sname like '%张%';
-- 姓张的人
select * from student where sname like '张%';
-- 模糊符号_ 一个任意字符
select * from student where sname like '张__';
-- 学生编号是 2,5,6,8,9,20,300,4000
-- in 在特定的范围内查找
select * from student  where sid in (2,5,6,8,9,20,300,4000);
-- 没有生日的学生  is 是对null的判断
select * from student where birthday is null;
select * from student where birthday is not null;
# 分组
-- group by 字段
select count(1) from student where ssex = '男';
select count(1) from student where ssex = '女';
select ssex,count(sid) from student group by ssex;
-- 每个班有多少学生
select classid,count(sid) from student group by classid;
-- sc  每个学生的平均分
select sid,avg(score) 平均分, sum(score) 总成绩,max(score) 最高分, min(score) 最低分, count(*) 次数 from sc group by sid;


语法：之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。

eg: select count(*) 总个数, sum(score) 总成绩, avg(score) 平均分, max(score) 最高分, min(score) 最低分 from sc;


# 聚合函数
count(字段) -- 统计个数
-- 数字
avg(字段) -- 平均值
sum(字段) -- 总和
max(字段) -- 最大值
min(字段) -- 最小值
-- count 统计个数
select count(*) from student where ssex = '男';
select count(sname) from student where ssex = '男';
select count(1) from student where ssex = '男';
select count('a') from student where ssex = '男';
-- count() 不统计null
select count(birthday) from student;
-- avg 平均值
-- 所有学生的平均分
select avg(score) from sc;
-- sum 总成绩
select sum(score) from sc;
select count(*) 次数, sum(score) 总成绩, avg(score) 平均分, max(score) 最高分,min(score)最低分 from sc;

TCL（事务控制语句）

　　它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
　　SAVEPOINT：保存点
　　ROLLBACK：回退到某点
　　COMMIT：提交事务

-- 开始一个新事务
START TRANSACTION;
 
-- 提交事务
COMMIT;

DCL(数据控制语言)

​ 它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

　　1) GRANT：授权

　　2) REVOKE ：回收权限

-- 授权用户访问数据库的权限
GRANT SELECT, INSERT ON mydb.* TO 'user'@'localhost';

CCL（指针控制语言）

　　它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

-- 在存储过程中使用条件逻辑
CREATE PROCEDURE my_procedure()
BEGIN
  IF (SELECT COUNT(*) FROM my_table) > 0 THEN
    -- 执行一些操作
  ELSE
    -- 执行其他操作
  END IF;
END;

SQL标准 ISO/IEC 9075 - SQL Standard

从SQL86开始到SQL2023，历经四五十年总共推出了10款SQL标准。

ISO SQL标准经历了SQL:2016, SQL:2011, SQL:2008, SQL:2006, SQL:2003（这个版本开始，根据主题拆分）, SQL:1999, and SQL-92。

 国际标准化组织ISO于2023年6月1日发布了SQL:2023标准，新增第16部分SQL/PGQ，支持图形查询语言GQL，增强了SQL对图形数据处理的能力。

此外，标准还强化了SQL语言和JSON功能，PostgreSQL16已开始支持这些新特性。

SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。SQL 语句用于取回和更新数据库中的数据。SQL 可与数据库程序协同工作，比如 MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 以及其他数据库系统。目前的RDBMS关系型数据库系统都遵循SQL99的标准。

不幸地是，存在着很多不同版本的 SQL 语言，但是为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的关键词（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。

注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的私有扩展！

SQL-86 (or SQL-87)

SQL-87 is the ISO 9075:1987 standard of 1987

Some defined features of SQL-87 are

• transactions
• create
• read
• update
• delete

This standard is available as FIPS 127 / ANSI X3.135-1986 on archive.org (the link was posted on this dba.stackexchange.com answer).

This standard was based on System R.

SQL-89

SQL-89 is the ISO/IEC 9075:1989 standard of 1989

SQL-89 added foreign and primary key constraints.

This standard had about 120 pages.

SQL-92

SQL-92 is the ISO/IEC 9075:1992 standard of 1992.

SQL-92 added

• subqueries in place of named tables.
• Internationalization.
• Embedded SQL in programming languages

SQL-92 mentions the four isolation levels (Read uncomitted, read comitted, repeatable read and serializable).

This standard had 628 pages.

In MS access, ANSI 92 syntax can be enabled under the menu File -> Options -> Object Designers -> Query Design -> SQL Server Compatible Syntax

SQL:1999

SQL:1999 is the ISO/IEC 9075:1999 standard of 1999.

SQL:1999 introduced the first of two collection types: the array. (These types apparently are also referred to as User Defined Types).

An amendment to SQL:1999 in 2000 added window functions. These were then incorporated into the SQL:2003 standard.

The full standard had about 2200 pages.

SQL:2003

SQL:2003 is the ISO/IEC 9075:2003 standard of 2003

SQL:2003 introduced the merge statement and the second of two collection types: the multiset.

Data types

Because of lack of support in existing database products, the bit and bit varying data types were removed from the standard. (However, SQL Server still has the bit datatype).

On the other hand, three new data types were added:

• bigint
• multiset (which is a collection type).
• XML

Columns

SQL 2003 also introduced

• identity columns, and
• generated columns

SQL:2006

SQL:2006 is the ISO/IEC 9075:2006 standard of 2006

SQL:2008

SQL:2008 is the ISO/IEC 9075:2008 standard of 2008

Many corrections.

SQL:2008 enhanced the merge statement.

SQL:2008 specifies 164 mandatory and 280 optional features.

SQL:2011

SQL:2011 is the ISO/IEC 9075:2011 standard of 2011

SQL:2011 added 34 new features to the SQL/Foundation part (all of which are optional). This brought the total of features in SQL/Foundation to 314.

This standard came with 4063 pages.

The possibly most important feature of SQL:2011 is the support for temporal databases. Other important features that come with SQL:2011 are

• delete in the merge statement.
• Pipelined DML
• Enhanced call statement (named arguments and default values for arguments)
• Ability to limit fetches (See Oracle SQL: Select first n rows / rows between n and m (TOP n/LIMIT queries))
• Enhanced collection types
• Non-enforced table constraints
• Window enhancement (ntile, navigation within a window (lag, lead, nth_value, first and last), nested navigation within a window, groups option)

See Temporal features in SQL:2011 (by Krishna Kulkarni and Jan-Eike Michels covers the most important new functionality that is part of SQL:2011: the ability to create and manipulate temporal tables and Fred Zemke: What's new in SQL:2011.

See also: Final Draft (not to be used for reference purposes)

SQL:2016

SQL:2016 is the ISO/IEC 9075:2016 standard of 2016

With the exception of part 2, SQL:2016 did not significantly change from the previous version.

The most important new features in SQL:2016 are

• Support for JSON data (of the 44 new optional features of SQL:2016, 22 are related to JSON).
• Polymorphic table functions (PTF)
• Row pattern recognition (RPR): apply regular expressions across rows.
• Trigonometric and logarithmic functions

Support for JSON

Unlike SQL/XML (Part 14), JSON was not put in its own part - yet, it is still referred to as SQL/JSON.

SQL:2016 does not define a new data type for JSON - it is stored in an SQL string type (for example varchar or clob).

See also the technical report ISO/IEC TR 19075-6:2017

Row pattern recognition (RPR)

Row pattern recognition is kind of applying regular expressions across sequences of rows.

See also the technical report ISO/IEC TR 19075-5:2016 and features R010 and R020.

Polymorphic table functions (PTF)

Parameters and function return values can be tables whose shape is not known until query time.

The feature ID for PTFs is B200.

See also the technical report ISO/IEC TR 19075-7:2017

SQL:2019

SQL:2019 added a part 15: Multidimensional arrays (MDA)

SQL:2020

JTC 1/SC 32/WG 3 identified the following areas of interest for the next (as of 2020-02) SQL standard:

• Better support for Big Data applications.
• Graph queries
• Approximate queries/aggregates and uncertain data.
• Integration of statistical packages (for example R).
• Support for map-reduce algorithms
• Streaming/continuous queries
• Support for blockchains
• BASE transactions

SQL:2023

SQL:2023 has some improvements in the following three areas:

• Various smaller changes to the existing SQL language
• New features related to JSON
• A new part for property graph queries (SQL/PGQ)

参考

GitHub - ronsavage/SQL: BNF Grammars for SQL-92, SQL-99 and SQL-2003

ISO/IEC 9075 - SQL Standard

基础篇：数据库 SQL 入门教程-CSDN博客