[TOC]

0x00 MySQL语法

描述:学习MySQL除了一些基本的SQL语句以外,我们还需要学习MySQL扩展部分的知识;

语法定义说明:

  • 1.SQL关键字不区分大小写,可以用任何字母大写(建议大写),每条语句后加上’;’结束;
  • 2.在语法描述中,方括号 [ 关键字 ]表示可选的关键字参数;
  • 3.当语法元素由多个替代项组成时,替代项由竖线[ 关键字 | 关键字 ]分隔,表示里面的参数关键字赛可以被选择使用的;如:TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
  • 4.当必须从一组选项中选择一个成员时在用 { 关键字 }中列出,如{DESCRIBE | DESC} tbl_name [col_name | wild];
  • 5.省略号(…)表示省略了语句的一部分,通常是为了提供较短版本的更复杂的语法例如:SELECT ... INTO OUTFILE是SELECT语句形式的简写,该语句的INTO OUTFILE后面还有一个 子句。
  • 6.省略号还可以指示可以重复执行语句的前面的语法元素;比如reset_option可以给出多个值,每个值后面的第一个值前都带有逗号:RESET reset_option [,reset_option] ...
  • 7.注释采用-- 或者 /****/进行SQL语句注释


通过前面的学习我们知道SQL大致分为四类,在后面的学习采用这四类来入门了解使用MySQL 8.0;


1.DQL 数据查询语句

SELECT 语句

基础语法:

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[into_option]
[FOR {UPDATE | SHARE}
[OF tbl_name [, tbl_name] ...]
[NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]
[into_option]

into_option: {
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...
}

-- 简单说明
SELECT [DISTINCT] [*] [列名1,列名2] FROM 表名 [WHERE 条件] -- 去重
SELECT name AS '字段别名', grade AS '字段别名' FROM 表名 [WHERE 条件] -- 别名
SELECT [列名1,列名2] FROM 表名 [WHERE 条件] ORDER BY 列名1 [DESC | ASC] --排序
SELECT [列名1,列名2] FROM 表名1 AS 表别名1,表名2 AS 表别名2 [WHERE 条件] GROUP BY 组名 HAVING 条件 -- 表别名和分组过滤
SELECT 聚合函数(自字段) FROM 表名 [WHERE 条件] --聚合函数
SELECT 字段1,聚合函数(字段) FROMGROUP BY 需要分组的字段名称 HAVING [聚合函数(字段)] 分组显示条件 -- 分组

基础示例:

-- SELECT 运算查询
SELECT product_name,price,(price * 80) AS discount WHERE product;

-- 聚合函数
SELECT avg(price) FROM product; --平均价格
SELECT * FROM product WHERE price > (SELECT avg(price) FROM product) --子查询

-- 分组(对某个字段进行分组,统计显示),比如商品分类及其数量
SELECT product_type,count(*) FROM product GROUP BY product_type;
-- 需求:根据商品类型分组,统计每组商品的平均价格,并且显示商品平均价格 > 60的产品分类;
SELECT product_type,avg(price) FROM product GROUP BY product_type HAVING avg(price)

注意事项:

  • 注:WHERE 子句后不能直接接聚合函数,而分组中的HAVING可以直接接聚合函数;
  • 注:总结出查询的SQL语句的编写顺序:SELECT . FROM . WHERE . GROUP BY . HAVING . ORDER BY;
  • 注:总结出执行顺序FROM . WHERE . GROUP BY . HAVING . SELECT . ORDEY BY;


2.DDL 数据定义语句

描述:原子数据定义语句支持也叫原子DDL语句,通过在MySQL 8.0中引入MySQL数据字典,可以实现原子DDL。
原子DDL语句将数据字典更新,存储引擎操作以及与DDL操作关联的二进制日志写入操作组合到单个原子事务中。
即使事务在操作期间停止,事务也可以提交,并在数据字典,存储引擎和二进制日志中保留适用的更改,或者回滚。

  • 支持的DDL语句
    • 1.原子DDL功能支持表DDL语句和非表DDL语句(当前仅InnoDB存储引擎支持原子DDL),与表相关的DDL操作需要存储引擎支持,而非表DDL操作则不需要;
    • 2.表DDL语句:CREATE,ALTER和 DROP 数据库,表空间,表和索引以及TRUNCATE TABLE语句;
    • 3.非表DDL语句: CREATE和DROP 语句,以及(如果适用)ALTER 存储程序,触发器,视图和用户定义函数(UDF)的语句, 账户管理语句: CREATE,ALTER, DROP,如果适用RENAME报表用户和角色,以及GRANT 和REVOKE报表。
  • 原子DDL特性
    • 1.在DDL操作期间SQL层没有中间提交, 元数据更新,二进制日志写入和存储引擎操作(如果适用)组合为一个事务。
    • 2.DDL操作的可见行为是原子的,这会更改某些DDL语句的行为。注意:DDL语句(原子的或其他方式)隐式结束当前会话中处于活动状态的任何事务,就好像您COMMIT在执行该语句之前已执行了。
  • DDL语句行为的变化
  • 存储引擎支持
  • 查看DDL日志


CREATE 语句

描述:CREATE 语句主要是用来创建数据库、表、视图、

  • CREATE DATABASE
  • CREATE TABLE
  • CREATE VIEW


CREATE DATABASE 语句

基础语法:

-- 创建数据库以及指定字符集语法
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
-- create_specification
[DEFAULT] CHARACTER SET [=] charset_name -- 指定默认的数据库字符集
| [DEFAULT] COLLATE [=] collation_name -- 指定默认的数据库排序校对规则(查询判断的时候)
| DEFAULT ENCRYPTION [=] {'Y' | 'N'} -- 定义默认的数据库加密

-- 简单说明
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] 数据库名称 DEFAULT CHARACTER SET = 字符集 COLLATE = 校队规则 ENCRYPTION = 'Y'

基础实例:

-- 查看支持的字符集以及校对规则
SHOW CHARACTER SET;
SHOW COLLATION LIKE 'UTF8%';

-- 创建数据库并且指定字符编码和校队规则
CREATE DATABASE IF NOT EXISTS black_hack DEFAULT CHARACTER SET = 'utf8mb4' COLLATE = 'utf8mb4_bin';

-- 查看创建的数据库信息
SHOW CREATE DATABASE black_hack;

注意事项:

  • 注1:如果数据库存在并且您未指定IF NOT EXISTS则会发生错误
  • 注2:在LOCK TABLES语句的会话中不允许使用CREATE DATABASES;
  • 注3:如果不指定校队规则就默认采用 utf8mb4_0900_ai_ci


CREATE TABLE 语句

基础语法:

-- 基础语法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
col_name column_definition
| {INDEX|KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition

column_definition:
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY}]
[reference_definition]
[check_constraint_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
[check_constraint_definition]

data_type:
(see Chapter 11, Data Types)

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
USING {BTREE | HASH}

index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}

check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
table_option [[,] table_option] ...

table_option:
AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT|0|1}
| STATS_PERSISTENT [=] {DEFAULT|0|1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)

partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]

partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]

query_expression:
SELECT ... (Some valid select or union statement)

-- 简单说明
CREATE TABLE [IF NOT EXISTS] 数据库名称 (
列名1 类型(长度) 约束 COMMENT '描述1',
列名2 类型(长度) 约束 COMMENT '描述2',
PRIMARY KEY(列名1) INDEX(列名1)
) ENGINE=引擎 AUTO_INCREMENT=自增起始 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

基础示例:

-- 创建表示例
CREATE TABLE `Persons` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID序号',
`LastName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '名称',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 查看表以及创建的表创建信息
mysql> SHOW TABLES;
mysql> SHOW CREATE TABLE Persons;
-- 查看表结构
mysql> DESC Persons;

注意事项:

  • 1.默认情况下表是使用InnoDB存储引擎在默认数据库中创建的;
  • 2.如果该表存在没有默认数据库或该数据库不存在则会发生错误。
  • 3.MySQL对表的数量没有限制。基础文件系统可能会对表示表的文件数量有所限制。


CREATE VIEW 语句

描述:它可以创建或者替换现有的视图它将会始时的刷新拉取数据,视图定义受以下限制:

  • 1.SELECT语句不能引用系统变量或用户定义的变量
  • 2.存储的程序SELECT语句不能引用程序参数或局部变量
  • 3.SELECT语句不能引用准备好的语句参数
  • 4.定义中引用的任何表或视图都必须存在
  • 5.不能引用TEMPORARY 表,也不能创建TEMPORARY 视图;
  • 6.无法将触发器与视图关联。
  • 7.SELECT语句中列名的别名将根据最大列长度64个字符(而不是最大别名长度256个字符)进行检查。

基础语法:

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

-- 简单说明
CREATE VIEW 视图名称 AS SELECT 字段1,字段2 FROM 数据库;

基础示例:

-- 建立视图
CREATE VIEW login_log AS
4SELECT id,userLogin,loginTime
4FROM user_log
4ORDER BY id DESC;
4
-- 查看创建的视图信息和数据
SHOW CREATE TABLE login_log;
SELECT * FROM login_log;
| View | Create View | character_set_client | collation_connection |
| -------- | ------------------------------------------------------------ | -------------------- | -------------------- |
| login_lo | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `login_log` AS select `user_log`.`id` AS `id`,`user_log`.`userLogin` AS `userLogin`,`user_log`.`loginTime` AS `loginTime` from `user_log` order by `user_log`.`id` desc | utf8mb4 | utf8mb4_0900_ai_ci |


CREATE USER 语句

描述:

基础语法:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 参数说明
username -- 你将创建的用户名
host -- 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost , 任意远程主机登陆使用通配符%
password -- 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器


ALTER 语句

描述:修改数据库,修改表和列(修改表的字符集)添加add列,修改modify列,修改change列名,删除drop列,

  • 1.ALLTER DATABASE
  • 2.ALLTER TABLE
ALLTER DATABASE 语句

基础语法:

-- 基础语法
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| DEFAULT ENCRYPTION [=] {'Y' | 'N'}


ALLTER TABLE 语句

基础语法:

ALTER TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]

alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (key_part,...) [index_option] ...
| ADD {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name]
(key_part,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
| DROP {CHECK|CONSTRAINT} symbol
| ALTER {CHECK|CONSTRAINT} symbol [NOT] ENFORCED
| ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
| ALTER [COLUMN] col_name
{SET DEFAULT {literal | (expr)} | DROP DEFAULT}
| ALTER INDEX index_name {VISIBLE | INVISIBLE}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| {DISABLE|ENABLE} KEYS
| {DISCARD|IMPORT} TABLESPACE
| DROP [COLUMN] col_name
| DROP {INDEX|KEY} index_name
| DROP PRIMARY KEY
| DROP FOREIGN KEY fk_symbol
| FORCE
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ORDER BY col_name [, col_name] ...
| RENAME COLUMN old_col_name TO new_col_name
| RENAME {INDEX|KEY} old_index_name TO new_index_name
| RENAME [TO|AS] new_tbl_name
| {WITHOUT|WITH} VALIDATION

partition_options:
partition_option [partition_option] ...

partition_option:
ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| DISCARD PARTITION {partition_names | ALL} TABLESPACE
| IMPORT PARTITION {partition_names | ALL} TABLESPACE
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
USING {BTREE | HASH}

index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}

table_options:
table_option [[,] table_option] ...

table_option:
AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT|0|1}
| STATS_PERSISTENT [=] {DEFAULT|0|1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)

partition_options:
(see CREATE TABLE options)

-- 简单说明
ALTER TABLE 表名 ADD [COLUMN] 列名 类型 约束;
ALTER TABLE 表名 MODIFY [COLUMN] 列名 类型 约束;
ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 类型 约束;
ALTER TABLE 表名 DROP [COLUMN] 列名;
ALTER TABLE 表名 CHARACTER SET 字符集;
ALTER TABLE 旧表名 RENAME 新表名;

基础示例:

mysql> ALTER TABLE Persons CHARACTER SET = 'gbk';



RENAME TABLE 语句

描述:RENAME TABLE重命名一个或多个表,您必须具有ALTER与 DROP原始表的权限以及CREATE与INSERT新表的权限

-- 基础语法
RENAME TABLE
tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...

-- 加单说明
RENAME TABLE 旧表名1 TO 新表名1,旧表名2 TO 新表名2;

基础示例:

-- MySQL 8.X 新特性:可以重命名使用LOCK TABLES语句锁定的表,前提是它们已被WRITE锁锁定
LOCK TABLE old_table1 WRITE;
RENAME TABLE old_table1 TO new_table1,new_table1 TO new_table2;

-- 用来将表从一个数据库移动到另一个数据库
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;


DROP 语句

描述:删除数据库或者表,注意删除后无法通过日志恢复;

基础语法

-- 删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] 数据库名称

-- 删除表
DROP [TEMPORARY] TABLE [IF EXISTS] 数据库表名称1 [, 数据库表名称2] ...
[RESTRICT | CASCADE]

-- 删除索引
DROP INDEX index_name ON tbl_name
[algorithm_option | lock_option] ...
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

-- 删除试图
DROP VIEW [IF EXISTS] view_name [, view_name] ...
[RESTRICT | CASCADE]


TRUNCATE 语句

描述:TRUNCATE TABLE完全清空一张表它需要DROP 特权,从逻辑上讲它类似于DELETE删除所有行的语句或DROP TABLE 语句的序列,但是其中实现过程以及性能耗费的时间是不同的;

基础语法:

TRUNCATE [TABLE] tbl_name

补充知识点:

  • 它与DELETE语句块的异同(前者先删除表再重建表,而后者一条一条删除表中的数据):执行速度,隐式提交因此无法回滚,只要表定义有效即使数据或索引文件已损坏也能重新创建空白表,将AUTO_INCREMENT值重置为其初始值,不调用ON DELETE触发器。
    • 如果数据少DELETE效率高,否则数据多的话建议采用TRUNCATE;

3.DML 数据操纵语句

INSERT 语句

描述:批量插入比单条插入效率高,如果数据处理不好容易出现BUG;

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
|
VALUES row_constructor_list
}
[AS row_alias[(col_alias [, col_alias] ...)]]
[ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[AS row_alias[(col_alias [, col_alias] ...)]]
SET assignment_list
[ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
[AS row_alias[(col_alias [, col_alias] ...)]]
{SELECT ... | TABLE table_name}
[ON DUPLICATE KEY UPDATE assignment_list]

value:
{expr | DEFAULT}

value_list:
value [, value] ...

row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]

assignment:
col_name = [row_alias.]value

assignment_list:
assignment [, assignment] ...

-- 简单说明
INSERT INTO 表名 VALUES (value1,value2,value3,...);

基础示例:

-- 指定字段插入
INSERT INTO new_log(userLogin,userPass) VALUES ('Apache',MD5('123556'));

-- 顺序插入(注意插入顺序以及非NULL值的字段)
INSERT INTO new_log VALUES (null,'Apache',NOW(),MD5('123556'));

WeiyiGeek.INSERT INTO


INSERT…SELECT 语句

描述:从SELECT 语句的结果中快速将许多行插入到表中,该语句的结果可以从一个或多个表中进行选择。

基础语法:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{SELECT ... | TABLE table_name}
[ON DUPLICATE KEY UPDATE assignment_list]

value:
{expr | DEFAULT}

assignment:
col_name = value

assignment_list:
assignment [, assignment] ...

-- 简单说明
INSERT INTO 表名 (字段)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

基础示例:

-- 查询插入(需要存在相同类型的字段)
INSERT INTO Persons(lastName) SELECT userLogin FROM user_log
查询执行完毕,3 行受影响。

-- 复制表整表插入
CREATE TABLE new_log LIKE user_log;
INSERT INTO new_log SELECT * FROM user_log;

注意事项:

  • 从MySQL 8.0.19开始,您可以使用 TABLE语句代替 SELECT,如下所示:

    INSERT INTO ta TABLE tb;  -- TABLE tb 等同于 SELECT * FROM tb
  • 要求数据库,表,存在并且,字段类型是一致的


UPDATE 语句

描述:UPDATE 是DML语句,用于修改表中的行用于更新表记录

-- 单表语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

value:
{expr | DEFAULT}

assignment:
col_name = value

assignment_list:
assignment [, assignment] ..

-- 多表语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]


-- 简单说明
UPDATE 表名 SET 列名=列值,列名2=列值 [WHERE 条件]

基础案例:




DELETE 语句

描述:删除表中的数据会在日志中记录,并且需要注意如果不加上WHERE条件默认是整表数据库删除删除;
基础语法:

-- 单表语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]


-- 多表语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]

-- 简单说明
DELETE FROM 表名 [WHERE 条件]

基础示例:

-- 多表删除:删除两个表中相同id的数据
DELETE new_log,user_log FROM user_log LEFT JOIN new_log ON new_log.id = user_log.id WHERE user_log.id = new_log.id;


4.DCL 数据控制语句

-- 系统字符编码
mysql> show CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| ascii | US ASCII | ascii_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |


-- 系统字符编码校对规则
mysql> SHOW COLLATION LIKE 'UTF8%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |

-- 查看创建数据库的信息
mysql> SHOW CREATE DATABASE black_hack;
| Database | Create Database
| black_hack | CREATE DATABASE `black_hack` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */


mysql> SHOW TABLES;
+----------------+
| Tables_in_Demo |
+----------------+
| Aggregate |

mysql> SHOW CREATE TABLE Persons;
| Persons | CREATE TABLE `Persons` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID序号',
`LastName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '名称',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |