深入浅出MySQL语法：从基础到进阶实战解析

持续更新中….

前言

在当今这个数据驱动的时代,数据库管理系统作为信息技术领域的基石,其重要性不言而喻。而MySQL,作为全球最受欢迎的开源关系型数据库之一,凭借其强大的性能、高度的灵活性以及广泛的应用场景,长期以来一直是开发者和数据管理员的首选工具。随着技术的飞速发展,MySQL不断进化,引入了诸多新特性与优化,以满足日益增长的数据处理需求和挑战。

本文旨在深入探讨MySQL语法的持续更新,带领读者踏上一场从基础到进阶的探索之旅。我们不仅会回顾MySQL的核心语法及其发展历程,更重要的是,将聚焦于近年来MySQL引入的重大更新与改进。这包括但不限于SQL标准的兼容性增强、性能优化、安全性的提升、以及为应对大数据和云计算环境所做的调整。

通过实例解析和最佳实践

不论是对于MySQL的老用户,还是刚刚踏入这个领域的新人,本文都力求成为一份宝贵的参考资料,帮助大家紧跟技术潮流,解锁MySQL的全部潜力。让我们一起启程,深入探索MySQL语法的广阔天地,携手应对数据管理的新挑战

锁

参考文章:看一遍就理解:MySQL中的锁

1
2
3
4
5
6
7
8
9
10
11
#创建共享锁和排他锁
select ... for share;/lock in share mode;
select ... for update;
#创建全局锁
flush tables with read lock;
#创建表级锁
lock tables tablename write;/read;
#释放锁
unlock tables;
#查看进程中是否有锁等待
show processlist;

索引

参考文章:一篇文章让你了解MySQL中的索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#创建普通索引 辅助,联合,覆盖
alter table tablename add index indxname(columnname1,columnname2);
#创建主键索引
alter table tablename add primary key(columnname);
#创建唯一索引
alter table tablename add UNIQUE(columnname);
#删除索引
drop index indexname on tablename;
#查看一张表中的索引
show index from tablename;
#查看select语句执行的时候是否使用了索引
explain select ...;
#以树状的方式查看sql语句的执行计划
expalin format tree select...
#在执行一条查询语句的时候指定索引
select * from table_name force/use index (index_name) where condition;
#添加约束
alter table table_name add constraint check_name (condition);

全文索引

参考文章:全文索引&前缀索引

1
2
3
4
5
6
7
8
#在创建表的时候创建
create table ... ,fulltext key idexname(columnname1,columnname2);
#在变更表结构的时候创建
alter table tablename add fulltext indexname(columnname);
#在创建好表后在添加
create fulltext index indexname on tablename(columnname);
#使用方法
select * from tablename where match(columnname) against('search keywords');

前缀索引

 参考文章:全文索引&前缀索引

1
2
#创建前缀索引
create index index_name on table_name(column_name(length));

事务隔离级别

参考文章:ACID模型

1
2
3
4
5
6
7
8
9
10
#开启事务,提交事务/回滚事务
begin;/start transaction;
commit;
rollback;
#查看当前隔离级别
select transaction_isolation;
#设置隔离级别/这里隔离级别的设置是在一个session中,如果session中断隔离级别设置也将重置
set global/session transaction isolation level (READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE) ;
#查看正在执行中的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_STATE 'RUNNING'\G

Online DDL

参考文章:你知道Online DDL吗?

1
2
3
4
5
ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM INPLACE, LOCK NONE;
#其中的ALGORITHM有如下选择
copy、inplace、instant、default
#其中的LOCK有如下选择
SHARE、NONE、EXCLUSIVE、DEFAULT

表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#查看一个表的创建语法
show create table tablename\G
#修改表的引擎
alter table tablename engine enginename;
#查看表的结构
show columns from tablename;/describe tablename;
#复制表(创建新表并复旧表的数据,索引不会跟着复制)
create table newtable_name engine engine_name as select * from oldtable_name;
#查看一个表的具体信息 包括存储引擎,创建时间,索引大小...
show table status like 'table_name'\G
#删除表中的全部数据
truncate table user;
#优化表的性能和空间利用,回收空间碎片
optimize table table_name;
#检查和修复表
check table table_name;
#分析表的索引和统计信息,并根据这些信息优化查询执行计划
analyze table table_name;
#查看表的完整字段信息
show full fields/columns from table_name;
#创建会话临时表
create temporary table .....;

CHECK TABLE

MySQL :: MySQL 5.7 Reference Manual :: 13.7.2.2 CHECK TABLE Statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#用于检查表是否有错误,比如数据损坏或者表结构有问题,执行期间会锁表
CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option: {
FOR UPGRADE
| QUICK
| FAST
| MEDIUM
| EXTENDED
| CHANGED
}
--FOR UPGRADE: 在升级MySQL服务器之前,检查表是否兼容新版本。
--QUICK: 执行快速检查,这种方式不会检查表中的每一行数据,主要用于检查表结构。
--FAST: 在MyISAM存储引擎中等同于QUICK,在InnoDB中没有效果。
--EXTENDED: 执行更详细的检查,比默认检查更彻底,可能更慢。
--CHANGED: 仅检查自最后一次检查以来被修改过的记录。

InnoDB

参考文章:InnoDB存储引擎

1
2
3
4
5
6
7
8
#查看InnoDB存储引擎状态
show engine innodb status\G
#修改表的存储引擎
alter table table_name engine engine_name;
#创建表的时候设置引擎
create ... engine engine_name;
#查看当前数据库支持的存储引擎
show engines\G

查看变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#查询和修改 MySQL 服务器的配置变量
show variables like 'variables_name';
#查看服务器的状态变量以及对应的值
show global status like 'variables_name';
#查看查询分析器是否开启
show variables like 'profiling';
#开启查询分析器
set profiling 1;
#查询sql分析结果
show profiles;
#查询特定语句执行详细信息
show profile for query query_id;
#查看数据库的版本
select version();

查看二进制日志

参考文章:MySQL数据库日志详解

1
2
3
4
#查看二进制日志
show binlog events in 'log_name'\G
#显示二进制日志文件列表,可以查看log_name和log_size
show binary logs;

单位的转换

1
2
3
4
#将字节转换为KB
select byte_size /1024;
#将KB转换为字节
select KB * 1024;

函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#随机生成一个uuid
select uuid();
#随机生成一个0~1之间的浮点数
select rand();
select floor(rand() * 100);--返回0~100之间的随机整数
#向下取整并返回不大于给定参数的最大参数
select floor(3,4);--返回3
#获取最后插入的自增ID值的SQL查询语句
select last_insert_id();
#将多个字符串连接成一个字符串
select concat(column_?) as info from table;
#多维数据聚合扩展语法,通常在group by子句中创建超级汇总行
select ... group by rollup(column_?);
#窗口函数/分析函数 窗口函数的标志是over)
function (expression)
OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list
[ frame_clause ]
]
)
#frame_clause语法
ROWS
{ UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } |
{BETWEEN
{ UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } |
CURRENT ROW}
AND
{ UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } |
CURRENT ROW }}

外键

1
2
3
4
#创建表的时候创建外键
create ... constraint constraint_name foreign key(外键列名) references 关联表名(关联列名);
#为已有的表添加外键
alter table table_name add constraint constraint_name foreign key(外键列名) references 关联表名(关联列名);

CTE语法

 参考文章:Common Table Expressions - CTE

1
2
3
4
5
6
7
with cte_name (column1, column2, ..., columnN) as (
select expression1, expression2, ..., expressionN
from tables
where conditions
)
select *
from cte_name;

特殊的DML语法

1
2
3
4
5
#在发生重复键冲突时,不会报错或拒绝插入,而是执行 UPDATE 操作来更新已存在的记录
insert into ... on duplicate key update;
#如果表中不存在具有相同主键或唯一索引的记录,则执行插入操作,类似于 INSERT INTO 语句;如果表中已经存在具有相同主键或唯一索引的记录,则先删除该记录,然后再插入新的记录,相当于先执行 DELETE 操作,再执行 INSERT 操作
replace into table_name (column1, column2, ...) values (value1, value2, ...);

视图

参考文章:物化视图 Materialized view 详解

1
2
3
4
5
6
7
8
9
#普通视图创建语法
create view view_name as select columns1,columns2 from table_name where condition;
#创建物化视图语法
create materialized view view_name
[refresh {fast | complete | force } {start with date_exp]
[next expr] | on demand}]
[enable query rewrite]
as select select columns1,columns2 from table_name where condition
[with [cascaded | local] check option];

存储过程

参考文章:https://blog.51cto.com/u_13341/7517619

1
2
3
4
5
6
7
8
9
10
#查看某个存储过程的详细创建语句
show create procedure procedure_name;
#查看所有的存储过程
show procedure status;
#查看某个特定数据库中的存储过程
show procedure status where db 'db_name';
#调用创建的存储过程
call procedure;
#调用存储过程中传递参数
call procedure('parameter1','parameter2');

触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#创建触发器的语法,可以说触发器是实际是存储过程
delimiter //
create trigger trigger_name
trigger_time trigger_event
on table_name
for each row
begin
-- 触发器要执行的SQL语句
-- 注意:这里可以有多个SQL语句,需要使用分号隔开
end//
delimiter ;
trigger_name:触发器的名称,需要在数据库中唯一。
trigger_time:触发时机,指定触发器何时执行。可选值为:
--BEFORE:在操作执行前触发。
--AFTER:在操作执行后触发。
trigger_event:触发事件,指明哪种数据库操作会激活触发器。可选值为:
--INSERT:插入操作。
--UPDATE:更新操作。
--DELETE:删除操作。
table_name:表名,即触发器监视的表。
FOR EACH ROW:表示对于受触发事件影响的每一行数据都会执行一次触发器中的代码。
BEGIN ... END;:包裹触发器要执行的具体SQL语句。
#查看数据库中所有的触发器
show trigger;
#查看指定数据库中的触发器
show trigger from table_nmae;
#根据触发器的名字直接查询
show trigger like 'trigger_name_pattern';

如果有什么命令不对的话望指出…..