Mysql 语法 -- 复习-CSDN博客

本文链接：https://blog.csdn.net/weixin_43712120/article/details/116534907

根据菜鸟教程，进行相关知识总结，后续会进行重新梳理

Mysql语法

一、数据库知识
二、Mysql 数据类型
三、Mysql数据库语法
四、数据表语法
五、增查改删记录语法
六、进阶语法
七、Mysql 事务
八、Mysql 索引
九、Mysql函数及运算符
附录 - 关键词优先级

一、数据库知识

Mysql是在关系型数据库管理系统（RDBMS：Relational Database Management System）之一。

1. RDBMS 特点

数据以表格的形式出现
一行称为一条记录
一列为记录名称所对应的数据域
许多的行和列组成一张表单
若干的表单组成database

2. RDBMS 术语

数据库: 数据库是一些关联表的集合；
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格；
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据；
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据；
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性；
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据；
外键：外键用于关联两个表；
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引；
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录；
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

在这里插入图片描述

表头(header): 每一列的名称;
列(col): 具有相同数据类型的数据的集合;
行(row): 每一行用来描述某条记录的具体信息;
值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
键(key): 键的值在当前列中具有唯一性。

3. MySQL数据库

MySQL 是开源的，目前隶属于 Oracle 旗下产品。
MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL 使用标准的 SQL 数据语言形式。
MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

4. DQL、DML、DDL、DCL

DQL：数据查询语言，由SELECT子句，FROM子句，WHERE
子句等组成。

DML：数据操纵语言，由INSERT、UPDATE、DELETE组成

DDL：数据定义语言，CREATE，DROP、TRUNCATE等

DCL：数据控制语言，GRANT：授权、ROLLBACK 回滚、COMMIT 提交。

只有DML操作可以执行回滚操作，设置自动提交：

SET AUTOCOMMIT ON；

5. 元数据

命令	解释
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量
SELECT LAST_INSERT_ID()	查询当前自增列值

二、Mysql 数据类型

MySQL支持多种类型，大致分为三类：数值、日期/时间和字符串(字符)类型。下面将列举常用类型。

1. 数值类型

注：带正负号的数值，都是负值包括，正值不包括。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	[-128,127]	[0,255]	小整数值
SMALLINT	2 bytes	[-32768,32767]	[0,65535]	大整数值
MEDIUMINT	3 bytes	± 8 388 608	0~16 777 215	大整数值
INT(或INTEGER)	4 bytes	± 2 147 483 648	0~4 294 967 295	大整数值
BIGINT	8 bytes	± 2⁶³	0~2⁶⁴-1	极大整数值
FLOAT	4 bytes	约± 3.4E38	约0~3.4E38	单精度浮点数值
DOUBLE	8 bytes	约± 1.8E308	约0~1.8E308	双精度浮点数值
DECIMAL	两个参数较大者+2	依赖M和D的值	依赖M和D的值	小数值

DECIMAL(6,2) # 表示该数保留6位有效数字，小数点后两位

2. 日期和时间类型

类型	大小	范围	格式	用途
DATE	3 bytes	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3 bytes	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1 byte	1901/2155	YYYY	年份值
DATETIME	8 bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4 bytes	1970-01-01 00:00:00/2038-1-19 11:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3. 字符串类型 (含char、varchar 与 text 比较)

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，比如 VARCHAR (255) 表示其可存储 255 个字符。

char、varchar 与 text

char：定长格式，长度范围是 0~255。当存储的数据不足设定的长度，会以空格替代。查询时，返回的数据尾部没有空格。
varchar：可变长度格式，可存放65535 bytes，与text范围一致
text：定长格式，不可有默认值，能用varchar，不用text。

三、Mysql数据库语法

# 创建数据库
CREATE DATABASE 数据库名;

# 如果不存在就创建指定数据库，并指定编码为UTF-8
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

# 删除数据库
drop database 数据库名;

# 选择数据库
use 数据库名;

四、数据表语法

1. 数据表层语法

# 创建数据表
CREATE TABLE table_name (column_name column_type);

# 删除数据表
DROP TABLE table_name;

# 创建数据表举例
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE DEFAULT '2021-05-09',
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

说明：

NOT NULL：表示值不能为空
UNSIGNED：表示无符号
AUTO_INCREMENT：表示自增，但只能用于整型，多用于主键、
DEFAULT ：设置默认值
PRIMARY KEY：定义列为主键，可指定多个，逗号分隔
ENGINE：设置存储引擎，InnoDB 是 Mysql 第一个提供外键约束的数据存储引擎
CHARSET：设置编码

注意：

表名和字段名外面的符号 ` 不是单引号，是反引号
除非必须使用 NULL 值，否则将字段设置为 NOT NULL。一方面是：含有空值的列很难进行查询优化（索引不会存储NUll值，可使用特殊值取代null值），另一方面：联表查询时（left join），如果某一字段为空，很难区分其是没有关联记录，还是其他情况。

2. 数据字段语法（Alter）

# 查看表结构
SHOW COLUMNS

# 删除表字段，当只剩一个表字段时，不可删除
ALTER TABLE table_name DROP column_name;

# 新增表字段
ALTER TABLE table_name ADD column_name column_type;  # 新增字段在最后一列
ALTER TABLE table_name ADD column_name column_type FIRST; # 新增字段在最后一列
ALTER TABLE table_name ADD column_name column_type AFTER column_name1 ; # 新增字段在column_name1 后

# 修改字段类型
ALTER TABLE table_name MODIFY column_name new_column_type ;
# 修改表字段名及类型
ALTER TABLE table_name CHANGE old_column_name  new_column_name  new_column_type;

3. Mysql 临时表

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

# 临时表创建语法与创建表一致，只不过多个关键词 TEMPORARY
CREATE TEMPORARY TABLE SalesSummary (
	product_name VARCHAR(50) NOT NULL, 
	total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
	avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
	total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
); 

# 将查询结果直接导入临时表（常用）
CREATE TEMPORARY TABLE jobss AS
(
    SELECT *  FROM jobs
);

# 临时表添加数据和删除 与 添加与删除完全一致
INSERT INTO SalesSummary(product_name, total_sales,avg_unit_price, total_units_sold)
VALUES ('cucumber', 100.25, 90, 2);

DROP TABLE SalesSummary;

# 注意：1. SHOW TABLES 命令是无法看到临时表的
# 注意：2. 关闭连接时，临时表会自动销毁。

五、增查改删记录语法

1. 增加记录语法

# 增加记录
INSERT INTO table_name (field1, field2,...fieldN) 
	VALUES (value1, value2,...valueN);

# 增加多条记录,逗号分隔
INSERT INTO table_name (field1, field2,...fieldN)  
	VALUES  (valueA1,valueA2,...valueAN),
		(valueB1,valueB2,...valueBN),
		(valueC1,valueC2,...valueCN)......;

# 增加记录举例
INSERT INTO runoob_tbl (runoob_id,runoob_title, runoob_author, submission_date)
	VALUES (null,"学习 PHP", "菜鸟教程", NOW());

注意：

field1, field2,…fieldN，可省略，表示按照的表列的顺序依次插入值，但是最好不要省略，防止增加记录报错或记录不对的情况（表列的顺序在不同环境下可能发生改变）。
举例中的null，并不是将runoob_id设置为null，因为runoob_id列设置了主键自增，此处 null 表示缺省，实际数据是会自增的。

2. 查询记录语法

# 查询记录 ,可在列名前加关键字：DISTINCT  表示去除重复记录
SELECT column_name,column_name FROM table_name
[WHERE condition1 [AND [OR]] condition2.....]
[LIMIT N][ OFFSET M]

# 关联查询,多张表连接查询
SELECT column_name,column_name FROM table_name1,table_name2
[WHERE condition1 [AND [OR]] condition2.....]  # where 条件一般为两个表的关联条件
[LIMIT N][ OFFSET M]

# 查询记录举例
select * from runoob_tbl where runoob_id = 1;

说明：

查询语句中可使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件；
SELECT 命令可以读取一条或者多条记录；
可使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
WHERE 用来筛选记录，可使用 AND 或者 OR 指定一个或多个条件，支持操作符：=、<>（不等于）、!= （不等于）、>、>=、<、<=， WHERE 也可用于 DELETE 或 UPDATE 命令。
LIMIT N：返回 N 条记录
OFFSET M：设定偏移量，初始值为0，单独使用无效
LIMIT N,M：相当于 LIMIT M OFFSET N , 从第 N 条记录开始, 返回 M 条记录，常用于分页功能。

3. 更新记录语法

# 更新记录
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

# 更新记录举例
select runoob_tbl set runoob_title = "Mysql数据库语法" 
where runoob_id = 1;

说明：

可同时更新一个或多个字段，逗号分隔
可在 WHERE 子句中指定任何条件

4. 删除记录语法（含truncate、drop与delete 区别）

# 删除记录
DELETE FROM table_name [WHERE Clause]

# truncate 删除表记录，与 DELETE 注意区分。
truncate table_name;

注意：

DELETE 如果没有WHERE 子句，表中全部记录都会被删除

truncate、drop、delete 区别

truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。
truncate只能作用于表；delete，drop可作用于表、视图等。
truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除表的结构及其所依赖的约束、索引等。
truncate会重置表的自增值；delete不会。
truncate不会激活与表有关的删除触发器；delete可以。
truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。
执行速度：drop > truncate > delete

六、进阶语法

1. WHERE LIKE 子句

LIKE是用来模糊匹配的，可在WHERE中同时使用LIKE 和 =

# WHERE LIKE 语法
SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

# 模糊匹配举例
'%a'     # 以a结尾的数据
'a%'     # 以a开头的数据
'%a%'    # 含有a的数据
'_a_'    # 三位且中间字母是a的
'_a'     #两位且结尾字母是a的

LIKE有两种匹配方式：

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符。中文建议使用 %%。
_ : 表示任意单个字符。匹配单个任意字符。

2. MySQL UNION 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果，将其组合到一个结果集合中。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

说明：

UNION DISTINCT：删除结果集中重复的数据，默认即是如此
UNION ALL：返回所有结果集，包含重复数据
UNION 要求多个SELECT的结果集必须拥有相同数量的列、列必须拥有相似的数据类型、列的顺序必须相同。

3. 排序

# 排序语法，关键词：ORDER BY

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE conditions]
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

说明：

可使用任何字段来作为排序的条件
可设定多个字段来排序
可使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认升序。

4. 分组

# 分组语法，关键字group by

SELECT column_name, function(column_name)
FROM table_name
GROUP BY column_name
HAVING function(column_name) > 100;

说明：

分组后，SELETCT后的字段必须在一个组中数据一致
在分组的列上可使用 COUNT, SUM, AVG 等函数
HAVING 可对组进行筛选

5. 内/左/右连接

Mysql 不支持 full join。但支持以下连接

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

SELECT column_name,column_name table_name1 (left|right|inner) join table_name2
[on condition1 [AND [OR]] condition2.....]  # on 条件一般为两个表的关联条件
[LIMIT N][ OFFSET M]

在这里插入图片描述

6. NULL值处理

任何数跟 NULL 进行运算都是 NULL，Mysql 提供了三大运算符，进行NULL值比较。

IS NULL: 当列的值是 NULL,此运算符返回 true
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true

7. 正则表达式

字符	含义
^	匹配字符串开始位置
$	匹配字符串结尾位置
.	匹配任意字符，除换行
[…]	字符集合。匹配所包含的任意一个字符
[^…]	反字符集合。匹配未包含的任意一个字符
p1	p2
*	匹配前面的子表达式零次或多次
+	匹配前面的子表达式一次或多次
{n}	n 是一个非负整数。匹配确定的 n 次
{n,m}	m 和 n 均为非负整数，最少匹配 n 次且最多匹配 m 次，m可不写

# 正则表达式，关键字: REGEXP 
SELECT name FROM runoob_tbl WHERE runoob_author REGEXP 'mar';

七、Mysql 事务

1. 事务特点

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务用来管理 insert,update,delete 语句
默认情况下，事务是自动提交的

2. 事务四大特性

事务必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3. 事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT（ COMMIT WORK），二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK （ ROLLBACK WORK），二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ（Mysql 默认级别）和 SERIALIZABLE。

# 例：
begin
INSERT INTO table_name (field1, field2,...fieldN) 
	VALUES (value1, value2,...valueN);
commit;

八、Mysql 索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。组合索引，即一个索引包含多个列。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

1. 优缺点

优点：索引可以大大提高MySQL的检索速度
缺点：会降低更新表的速度；建立索引会占用磁盘空间的索引文件。

2. 普通索引

# 显式索引
SHOW INDEX FROM table_name;

# 创建索引,若列类型为char或varchar,length可以小于字段实际长度,也可省略length
CREATE INDEX index_name ON table_name (column_name(length))

# 修改表结构(添加索引)
ALTER table table_name ADD INDEX index_name(columnName(length))

# 删除索引
DROP INDEX index_name ON table_name; 

# 创建表时，直接指定索引
CREATE TABLE table_name (  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	INDEX [index_name] (columnName(length))
);

3. 唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

# 显式索引
SHOW INDEX FROM table_name;

# 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(column_name(length))

# 添加索引（更改表结构）
ALTER table table_name ADD UNIQUE [index_name] (column_name(length)) 

# 创建表时直接指定
CREATE TABLE table_name (  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	UNIQUE [index_name] (username(length))   
);

4. 添加/删除主键

# 增加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

# 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;

九、Mysql函数及运算符

Mysql函数

Mysql运算符

附录 - 关键词优先级

FROM, JOIN
WHERE
GROUP BY
HAVING
WINDOW functions
SELECT
DISTINCT
UNION
ORDER BY
LIMIT and OFFSET