MySQL 笔记

本文是以前学习 MySQL 时记录的内容,现在将笔记整理归档。

参考课程:尚硅谷 MySQL 数据库全套完整版

1. MySQL 概述

1.1 MySQL 基本概念

MySQL 中,包含以下概念:

  • DB:数据库,保存一组有组织的数据的容器。

  • DBMS:数据库管理系统,数据库软件,管理数据库中的数据。

  • SQL:结构化查询语言,实现与数据库管理系统的通信。

1.2 MySQL 存储特点

MySQL 等关系数据库具有以下特点:

  • 数据存储在表中,表存储在数据库中。
  • 一个数据库有多个表。表中具有特性,定义了数据在表中如何存储,类似于 Java 的类。
  • 表的列又叫字段,类似于 Java 的属性。
  • 表的行类似于 Java 的对象。

1.3 MySQL 常用操作

MySQL 中,启动和停止服务的内容:

  • net start [服务名]

  • net stop [服务名]

MySQL 中,服务的登入和退出:

  • mysql [-h 主机名] [-p 端口号] -u [用户名] -p [密码]
  • exit

MySQL 中,常用操作指令:

  • show database:查看当前所有数据库。
  • use [库名]:打开指定库。
  • show tables:查看当前库所有表。
  • show tables from [库名]
  • create table [表名] {列名 列类型 ...};
  • desc [表名]:查看表结构。
  • select version():已登录查看版本。
  • mysql --version (or --v):未登录查看版本。

1.4 MySQL 语法规范

MySQL 定义了一些语法规范,包括:

  • 不区分大小写。但建议:关键字大写,表名、列名小写。
  • 建议命令分号结尾。
  • 建议按行缩进。

1.5 MySQL 命令分类

MySQL 中,包含如下命令:

  • DML:数据操纵语言。用于增删改查数据库记录,及检查数据完整性。
  • DDL:数据定义语言。用于库和表的增删改查。
  • DCL:数据控制语言。用于定义用户的访问权限和安全级别。
  • TCL:事务控制语言。

也可以将 DML 中的 SELECT 语句拿出来,作为 DQL 数据查询语言。

DML 内容:

  • INSERT
  • UPDATE
  • DELETE
  • SELECT

DDL 内容:

  • CREATE TABLE
  • ALTER TABLE:更改表结构,增删改列长度。
  • DROP TABLE
  • CREATE INDEX:表上建立索引。
  • DROP INDEX

DCLTCL 内容:

  • GRANT:分配权限。
  • REVOKE:撤销权限。
  • COMMIT:提交事务处理。
  • ROLLBACK:回退事务处理。
  • SAVEPOINT:设置保存点。
  • LOCK:部分锁定数据库。

2. DQL 命令

介绍DQL命令前,首先介绍单行函数和分组函数,之后介绍条件表达式,最后介绍DQL命令以及相应的问题。

2.1 单行函数

单行函数包括以下内容:

  • 字符函数:
    • concat:拼接函数。
    • substr:截取子串。
    • upperlower:切换大小写。
    • trimltrimrtrim:去除指定的空格和字符。
    • replace:替换。
    • lpadrpad:填充。
    • instr:返回子串第一次出现的索引。
    • length:获取字节数。
  • 数学函数:
    • roundfloorceil:取整。
    • rand:随机数。
    • mod:取余数。
    • truncate:截断。
  • 日期函数:
    • now:当前系统日期和时间。
    • curdate:当前系统日期。
    • curtime:当前系统时间。
    • str_to_date:字符转日期。
    • date_format:日期转字符。
  • 流程控制函数:
    • if:双分支。
    • case:多分支。
  • 其他函数:
    • version:版本。
    • database:当前数据库。
    • user:当前连接用户。

2.2 分组函数

分组函数包括以下内容:

  • summaxminavgcount
  • 可以搭配distinct使用,统计去重后结果。
  • count参数一般放*,即count(*)

2.3 条件表达式

WHERE内容涉及到四种表达式:

  • 条件表达式:> < >= <= = != <>
  • 逻辑表达式:&& || !
  • 模糊查询:like "%"
  • 多行查询:any all in not in

2.4 DQL命令

DQL命令包括:

  • SELETE 内容;

  • SELETE 内容 FROM 表名 WHERE 条件;

  • SELECT 内容 FROM 表名 WHERE 条件 ORDER BY 表达式 [asc|desc];:排序查询。

  • SELECT 内容 FROM 表名 GROUP BY 分组字段;:分组查询。

  • SELECT 内容 FROM 表名 [INNER|LEFT OUTER|RIGHT OUTER|CROSS] JOIN 表名 ON 连接条件 [,WHERE 条件] [GROUP BY 分组字段] [HAVING 条件] [ORDER BY 排序字段];:连接查询。

  • 子查询。

  • SELECT 内容 FROM 表名 [WHERE 条件] [GROUP BY 分组字段] [HAVING 条件] [ORDER BY 排序字段] LIMIT [起始条目索引] 条目数;:分页查询。

  • SELECT 内容 [FROM 表名] [WHERE 条件] UNION [ALL] ...;:联合查询。

2.5 DQL相关问题

分组查询相关问题:

  • 分组前筛选用WHERE
  • 分组后筛选用HAVING
  • 可以多个字段分组,字段间用逗号隔开。

连接查询相关问题:

  • [...] JOIN 表名 ON 连接条件;:这句话可以多次出现,用于实现多个表的连接。
  • 连接操作和筛选条件清晰分离,简洁明了。

子查询相关问题:

  • 多行子查询通常搭配 any, all, in, not in 关键字。

分页查询相关问题:

  • 分页查询用于 web 应用查询,与用户提交请求相对应。
  • LIMIT一般放在查询最后。
  • LIMIT对应的两个参数分别是起始条目索引和查询返回的条目数。

联合查询相关问题:

  • 多条联合查询对应的列数必须一致。
  • UNION代表去重,UNION ALL代表不去重。

3. DML 命令

首先介绍DML相关的命令,随后介绍相关的问题。

3.1 DML命令

DML命令包括:

  • INSERT INTO 表名 VALUES 值;:插入数据。
  • UPDATE 表名 [,表名] SET [字段 = 新值] [WHERE [条件]] [AND [条件]];:更新数据。
  • DELETE FROM 表名 [,表名] [WHERE [条件]] [AND [条件]];:删除数据。
  • TRUNCATE TABLE 表名;:删除数据。

3.2 DML相关问题

插入数据相关问题:

  • 可为空的字段可以不填内容,或者填入NULL
  • 字段要和值类型兼容且顺序一致,不能空的必须对应有值。

修改数据相关问题:

删除数据相关问题:

  • TRUNCATE不支持WHERE,不支持回滚,DELETE支持。
  • TRUNCATE效率相对较高。

4. DDL语句

首先DDL常见约束,随后介绍DDL相关的命令。

4.1 DDL常见约束

DDL常见约束包括:

  • NOT NULL, DEFAULT, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY

4.2 DDL命令

DDL命令包括:

  • CREATE DATABASE 数据库名;
  • DROP DATABASE 数据库名;
  • CREATE TABLE [IF NOT EXISTS] 表名(列名 列类型 ...);
  • ALTER TABLE ADD|MODIFY|DROP|CHANGE COLUMN 字段名 字段类型;
  • DROP TABLE [IF EXISTS] 表名;

5. TCL语句

5.1 事务基本概念

数据库事务相关的内容包括:

  • 数据库事务是通过一组逻辑操作单元(一组DML语句),将数据进行状态转变。

  • 数据库事务的ACID特点:原子性,一致性,隔离性,持久性。

  • 数据库事务的相关步骤:

    • 开启事务。
    • 编写一组逻辑操作单元。
    • 提交事务或回滚事务。
  • 事务的分类:

    • 隐式事务:没有明显开始、结束的事务,如 INSERT, UPDATE, DELETE。
    • 显式事务:有明显开启和提交的事务。

5.2 事务关键词

数据库事务关键词包括:

  • SET:设置初始值。
  • START:开启事务。
  • COMMIT:提交事务。
  • ROLLBACK:回滚事务。
  • SAVEPOINT:存储断点。

5.3 事务并发

事务并发可能会存在的问题包括:

  • 脏读:一个事务读到了另一个事务更新但还没有提交的数据。
  • 不可重复读:同一个事务中,多次读取到的数据不一致。
  • 幻读:一个事务读时,另一个事务更新了值。

对此,可以设置事务隔离解决。

5.4 事务隔离

事务隔离相关的设置包括:

  • READ UNCOMMITTED
  • READ COMMITTED:基础事务隔离,避免脏读。
  • REPEATABLE READ:较强事务隔离,避免脏读、不可重复读和部分幻读。
  • SERIALIZABLE:最强的事务隔离,避免上述三种问题。

事务隔离相关命令包括:

  • SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL [隔离级别名];:设置隔离级别。
  • SELECT @@tx_isolation:查看当前事务的隔离级别。

6. 视图

首先介绍视图基本概念,随后介绍视图相关命令。

6.1 视图基本概念

视图基本概念包括:

  • 视图与表区别在于,视图不占用内存空间。
  • 视图和表分离开,提高了操作安全性。

6.2 视图相关命令

视图相关命令包括:

  • CREATE VIEW [视图名] AS [查询语句];
  • INSERT INTO [视图名(内容)] VALUES(值);
  • UPDATE [视图名] SET [内容] WHERE [条件];
  • DELETE FROM [视图名];
  • DROP VIEW [视图名];:删除视图。
  • SHOW CREATE VIEW [视图名];:查看视图。

7. 存储过程

首先介绍存储过程基本概念,随后介绍存储过程命令。

7.1 存储过程基本概念

存储过程基本概念包括:

  • 存储过程是一组预编译的 SQL 语句集合。
  • 存储过程可以带 IN, OUT, INOUT 类型,表示无返回有参数,有返回无参数,有返回有参数。
  • 存储过程与函数的区别:存储过程可以有多个返回值,一般用于更新操作。函数只能有一个返回值,一般用于查询操作。

7.2 存储过程相关命令

存储过程相关命令包括:

  • CREATE PROCEDURE [名称](IN|OUT|INOUT 参数名 参数类型 ...) BEGIN [存储过程体] END;
  • CALL [名称](参数);

7.3 函数相关命令

函数相关命令包括:

  • CREATE FUNCTION [名称](参数名 参数类型) RETURNS [返回类型] BEGIN [函数体] END;

  • SELECT [名称](参数);

8. 其他内容

其他内容部分,主要是变量和分支控制。

用户变量和局部变量的区别:

  • 用户变量的范围是会话域,需要加@符号,不用指定变量类型。
  • 局部变量范围是当前,不用加@,要指定变量类型。

8.1 全局变量相关命令

全局变量相关命令包括:

  • SHOW GLOBAL VARIABLES;:显示所有全局变量。
  • SHOW GLOBAL VARIABLES LIKE '%CHAR%';:查看满足条件的全局变量。
  • SELECT @@global.autocommit;:查看指定的系统变量的值。
  • SET @@global.autocommit = 0;:为某个系统变量赋值。
  • SET GLOBAL autocommit = 0:为某个系统变量赋值。

8.2 会话变量相关命令

会话变量相关命令:将全局变量的GLOBAL换成SESSION即可。

8.3 用户变量相关命令

用户变量相关命令包括:

  • SET @变量名 = 值;
  • SET @变量名 = 值;
  • SELECT @变量名 = 值;
  • SELECT @变量名;
  • SELECT 表达式 INTO 变量名 FROM 表;:赋值表中元素。
  • 上面三种是赋值并初始化,只要去掉@,就是赋值操作。

8.4 局部变量相关命令

局部变量相关命令包括:

  • DECLARE 变量名 类型 [DEFAULT 值];:声明变量。
  • 其他内容和用户变量相关一样。

8.5 分支相关命令

CASE相关命令包括:

  • CASE 表达式 WHEN 1 THEN 1 WHEN 2 THEN 2 ... ELSE 3 END;

循环相关命令包括:

  • WHILE 循环条件 DO [循环体] END WHILE;
  • 可以搭配LEAVE跳出循环。