MySQL

这篇笔记记录深入学习sql的笔记

数据库操作

连接

mysql -u root -p

查询数据库

show databases;

选择数据库

use xxx;

创建数据表

CREATE TABLE table_name (column_name column_type);

删除数据表

DROP TABLE table_name ;

WHERE

• 查询语句中可以使用一个或多个表，表之间用逗号分隔，用WHERE设置查询条件

• 可以使用AND和OR指定一个或多个条件

• =，检测两个值是否相等

UPDATE

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

LIKE

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

• 在WHERE中使用LIKE

• 通常与%一同使用，类似于一个元字符的搜索

UNION

union用于连接两个以上的select语句的结果组合到一个结果集合中，多个select语句会删除重复数据

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

• tables，要检索的表

• DISTINCT，可选，删除结果集中重复的数据，默认情况下已删除，加DISTINCT修饰符对结果没影响

• ALL，可选，返回所有结果集，包含重复数据

排序

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

• 可以使用多个字段排序

分组

使用 group by 语句根据一个或多个列对结果集进行分组

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

使用 with rollup 实现分组统计数据的基础上在进行相同的统计

连接

多表查询的时候使用join连表查询

• INNER JOIN，内连接，或等值连接，获取两个表中字段匹配关系的记录，使用时可以省略INNER

• LEFT JOIN，左连接，获取左表所有记录，即使右表中没有对应匹配的记录

• RIGHT JOIN，右连接，与左连接相反，用于获取右表中的所有记录，即使左表中没有对应匹配的记录

具体使用可以参考MySQL 连接的使用 | 菜鸟教程 (runoob.com)

NULL处理

• IS NULL，当列的值时NULL，返回true

• IS NOT NULL，当列的值不为NULL，返回true

• <=>，比较操作符，不同于=运算符，当比较的两个值相等或都为NULL时返回true

• 关于NULL的条件比较，不能使用 =NULL 或 !=NULL 在列中查找NULL，在MySQL中，NULL值与任何其他值比较（即使是NULL）永远返回NULL，NULL = NULL 返回NULL

MySQL正则

具体规则参考MySQL 正则表达式 | 菜鸟教程 (runoob.com)

事务

• 使用Innodb数据库引擎才支持事务

• 事务用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行

事务必须满足的四个条件：

• 原子性：一个事务中的所有操作，要么全部完成要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，就会回滚，到事务开始前的状态，就像这个事务从来没有执行过一样

• 一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时，由于交叉执行而导致数据的不一致。事务隔离分为不通级别，包括读未提交、读提交、可重复读和串行化

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

事务控制语句：

• 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 和 SERIALIZABLE

MySQL事务处理主要有两种方法：

1. 用 BEGIN，ROLLBACK，COMMIT 来实现

• BEGIN 开始一个事务

• ROLLBACK 事务回滚

• COMMIT 事务确认

2. 直接用 SET 来改变MySQL的自动提交模式

• SET AUTOCOMMIT=0 禁止自动提交

• SET AUTOCOMMIT=1 开启自动提交

使用示例参考：MySQL 事务 | 菜鸟教程 (runoob.com)

ALTER

ALTER命令用于修改数据库、表和索引等对象的结构。ALTER命令允许添加、修改或删除数据库对象，并且可以用于更改表的列定义、添加约束、创建和删除索引等操作。具体使用可以参考：MySQL ALTER命令 | 菜鸟教程 (runoob.com)

索引

索引可以提高MySQL的检索速度。

索引分为单列索引和组合索引：

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引

• 组合索引，即一个索引包含多个列

创建索引时，要确保索引是应用来查询语句的条件部分，实际上，索引也是一张表，该表保存了主键与索引字段，并只想实体表的记录。

索引虽然能提高查询性能，但需要注意以下几点：

• 索引需要占用额外的存储空间

• 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能

• 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引

普通索引

创建索引

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

添加索引

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

创建表的时候直接指定

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

删除索引

DROP INDEX index_name ON table_name;

ALTER TABLE table_name
DROP INDEX index_name;

唯一索引

唯一索引确保索引中的值是唯一的，不允许有重复值，使用 CREATE UNIQUE INDEX 来创建唯一索引

相关操作类似普通索引，具体可参考：MySQL 索引 | 菜鸟教程 (runoob.com)

显示索引信息

使用 SHOW INDEX 命令来列出表中的相关索引信息

mysql> SHOW INDEX FROM table_name\G
........

临时表

临时表可以保存一些临时数据，临时表只在当前连接可见，当关闭连接时，会自动删除表并释放所有空间，如果使用MySQL客户端程序连接数据库创建临时表，在关闭客户端之后才会销毁临时表，当然也可以手动销毁。

复制表

• 使用SHOW CREATE TABLE来获取创建数据表语句，该语句包含源数据表的结构、索引等

• 复制创建表的SQL语句，修改表名，执行SQL，完全复制表结构

• 通过 INSERT INTO SELECT 语句实现复制表内容

其他

其他不重要的可参考：MySQL 教程 | 菜鸟教程 (runoob.com)