本文围绕MySQL数据库展开，涵盖数据库的创建、查看、删除、备份恢复等操作，介绍了表的创建、修改、数据增删改查，以及索引、事务等知识。还讲解了合计、字符串、数学等各类函数，同时提及常用数据类型，为MySQL学习提供全面指导。
目录
2.12.7 group by,having,order by,limit排序方式
3.1.4 MAX/MIN:返回满足WHERE条件的一列的最大/最小值
3.1.5 GROUP BY和HAVING（二者一般是一起使用）
一、数据库相关
1.1 创建数据库
create database db_name CHARACTER SET ... COLLATE ...
注：如果不指定字符集，默认utf8；如不指定校对规则，默认是不区分大小写
1.2 查看数据库
SHOW DATABASES
1.3 显示当时创建数据库的语句
SHOW CREATE DATABASE db_name
1.4 删除数据库
DROP DATABASE db_name
1.5 备份及恢复数据库
方法一：
备份（在DOS执行）：mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
恢复（进去MySQL命令行再执行）： Source 文件名.sql
方法二：
直接将文件名.sql的内容放到查询编辑器中，执行
1.6 事务隔离级别
1.6.1 查看事务隔离级别
脏读（dirty read）：当一个事务读取另一个事务尚未提交的改变时，产生脏读
不可重复读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读
幻读（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读
1.6.2 事务隔离级别
| MySQL隔离级别（4种） | 脏读 | 不可重复读 | 幻读 | 加锁读 |
| 读未提交（Read uncommitted） | √ | √ | √ | 不加锁 |
| 读已提交（Read committed） | × | √ | √ | 不加锁 |
| 可重复读（Repeatable read） | × | × | × | 不加锁 |
| 可串行化（Serializable） | × | × | × | 加锁 |
1.6.3 设置事务隔离级别
1.查看当前会话隔离级别
SELECT @@tx_isolation
2. 查看系统当前隔离级别
SELECT @@global.tx_isolation
3. 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL ...
4. 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL ...
1.7 用户管理
1.7.1 创建用户
CREATE USER `用户名`@ `允许登录的位置` IDENTIFIED BY `密码`
1.7.2 删除用户
DROP USER `用户名`@`允许登录位置`
1.7.3 修改密码
SET PASSWORD = PASSWORD(`密码`) --修改自己的密码
SET PASSWORD FOR `用户名`@`允许登录位置 = PASSWORD(`密码`)
1.7.4 MySQL中的权限
| 权限 | 意义 |
| ALL[PRIVILEGES] | 设置除GRANT OPTION之外的所有的简单权限 |
| ALTER | 允许使用ALTER TABLE |
| ALTER ROUTINE | 更改或取消已存储的子程序 |
| CREATE | 允许使用CREATE TABLE |
| CREATE ROUTINE | 创建已存储的子程序 |
| CREATE TEPORARY TABLES | 允许使用CREATE TEPORARY TABLE |
| CREATE USER | 允许使用CREATE USER, DROP USER, RENAME USER 和 REVOKE ALL PRIVILEGES |
| CREATE VIEW | 允许使用CREATE VIEW |
| DELETE | 允许使用DELETE |
| DROP | 允许使用DROP |
| EXECUTE | 允许用户允许以存储的子程序 |
| FILE | 允许使用SELECT...INTO OUTFILES 和LOAD DATA INFILE |
| INDEX | 允许使用CREATE INDEX 和 DROP INDEX |
| INSERT | 允许使用INSERT |
| LOCK TABLES | 允许您对拥有SELECT权限的表使用LOCK TABLES |
| PROCESS | 允许使用SHOW FULL PROCESSLIST |
| RELOAD | 允许使用FLUSH |
| REPLICATION CLIENT | 允许用户询问从属服务器或主服务器的地址 |
| REPLICATION SLAVE | 用于复制型从属服务器（从主服务器中读取二进制日志事件) |
| SELECT | 允许使用SELECT |
| SHOW DATABASES | SHOWDATABASES显示所有数据库 |
| SHOW YIEW | 允许使用SHOW CREATE VIEW |
| SHUTDOWN | 允许使用mysqladmin shutdow |
| SUPER | 允许使用CHANGE MASTER，KILL，PURGE MASTER LOGS和SET GLOBAL语句，nysgladmin debug命令;允许您连接（一次），即使已达到max connectionso |
| UPDATE | 允许使用UPDATE |
| USAGE | “无权限”的同义词 |
| GRANT OPTION | 允许授予权限 |
1.7.6 给用户授权
基本语法为：
GRANT 权限列表 ON 库.对象名 TO `用户名`@`登录位置` IDENTIFIED BY `密码`
细节：
1. GRANT ALL ON...表示赋予用户在该对象上的所有权限
2. *.*代表本系统中的所有数据库的所有对象
3. 库.*表示某个数据库中的所有数据对象
4. IDENTIFIED BY可以省略，也可以写出。如果用户存在，则为修改用户密码；若不存在，则创建用户
1.7.7 回收用户授权
REVOKE 权限列表 ON 库.对象名 FROM `用户名`@`登录位置`
1.7.8 权限生效指令
如果权限没有生效，可以执行以下指令
FLUSH PRIVILEGES
1.7.9 细节说明
1. 在创建用户的时候，如果不指定Host, 则为%，%表示表示所有IP都有连接权限，create user xxx
2. 你也可以这样指定create userxxx@'192.168.1.%'表示xxx用户在192.168.1.的ip可以登录mysql
3. 在删除用户的时候，如果 host 不是 %,需要明确指定用户@'host值
二、表相关
2.1 创建表
CREATE TABLE table_name (
field datatype,
field datatype) character set 字符集 collate 校对规则 engine 存储引擎
注意：如果不指定字符集和校对规则，则默认与数据库相同
2.2 查看表的结构
DESC tablename
2.3 修改表名
RENAME TABLE 旧表名 to 新表名
2.4 修改字符集
ALTER TABLE tablename CHARATER SET 字符集
2.5 添加列
ALTER TABLE tablename
ADD `name` datatype
AFTER `想要添加到某一列后对应的列名`
2.6 修改列
ALTER TABLE tablename
MODIFY `colum`
2.7 删除列
ALTER TABLE tablename
drop （colum）
2.8 修改列名
ALTER TABLE tablename
CHANGE '旧名字' '新名字'
2.9 使用INSETRT语句向表中插入数据
INSERT INTO tablename(column1,colunmn2)
VALUES(value1,value2)
注意：如果是给表中所有的字段添加数据，可以不写column1和column2
2.10 使用UPDATE语句修改表中数据
UPDATE tablename
SET column_name = ...
WHERE ...
注意：WHERE子句指定应更新哪些行，如果没有WHERE子句，则跟新这一列中的所有数据
2.11 使用DELETE语句删除表中某一行的数据
DELETE FROM table_name
WHERE where_definition
注意：如果没有WHERE语句，则删除表中所有记录
2.12 SELECT语句在表中的应用
2.12.1 基本语法
SELECT [DISTINCT] * | {column1, column2, column3..}
FROM table_name;
注意：*表示查询所有列；DISTINCT可选，指显示结果时，是否去掉重复数据
2.12.2 使用表达式对查询的列进行运算
SELECT [DISTINCT] * | {column1|expression, column2|expression..}
FROM table_name;
2.12.3 SELECT语句中可使用as语句
SELECT column_name as 别名 from table_name
2.12.4 使用ORDER BY子句排序查询结果
SELECT column1, column2, column3..
FROM table_name
ORDER BY column asc|desc
2.12.5 like操作符相关
%：表示从0到多个字符；_:表示多个字符
2.12.6 分页查询
SELECT... LIMIT start, rows
表示：从start+1行开始取，取出rows行，start从0开始计算
有一个公式为：LIMIT 每页显示记录数 * （第几页 - 1），每页显示记录数
2.12.7 group by,having,order by,limit排序方式
如果select语句同时包含有 group by,having,order by和limit，那么他们的顺序是 group by,having,order by,limit。（顺序错误会报错）
2.13 多表查询
定义：多表查询是指基于两个和两个以上的表查询，在实际应用中，查询单个表可能不能满足需求。
韩顺平老师的小技巧：多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集
自连接：自连接指的是在同一张表的连接查询[将同一张表看做两张表]
2.14 mysql表子查询
子查询定义：子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询
2.14.1 单行子查询
定义：单行子查询是指只返回一行数据的子查询语句
2.14.2 多行子查询
定义：多行子查询指返回多行数据的子查询，使用关键字in
all和any的使用：all指的是所有，any值得是其中一个
2.14.3 子查询当做临时表使用
2.14.4 多列子查询
2.15 表自我复制（蠕虫复制）
有的时候，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。
2.16 表的合并查询（union/union all）
union all：用于取得两个结果集的并集，当使用该操作符时，不会取消重复行
union：该操作符与union all类似，但是会自动去掉结果集中的重复行
2.17 表外连接
A LEFT JOIN B：左连接A，即左侧的表完全显示
A RIGHT JOIN B：右连接B,即右侧的表完全显示
2.18 约束
约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：NOT NUL、UNIQUE、PRIMARRY KEY、FOREIGN KEY和check五种。
2.18.1 PRIMARY KRY（主键）
1.作用：用于唯一的标示表行的数据，当定义主键约束后，该列不能重复
2.指定方式：
· 直接在字段名后指定：字段名 PRIMARY KEY
· 在表定义最后写 PRIMARY KEY（列名）
3.细节
①PRIMARY KEY不能重复且不能为NULL
②一张表最多只能有一个主键，但可以是复合主键
③使用 DESC 表名，可以看到PRIMARY KEY的情况
④韩顺平老师提醒：在实际开发中，每个表往往都会设计一个主键
2.18.2 NUT NULL
作用：如果在列上定义了NOT NULL，那么当插入数据时，必须为列提供数据
2.18.3 UNIQUE
1. 作用：当定义了唯一约束后，该列值是不能重复的
2.细节：
① 如果没有指定NOT NULL，则UNIQUE字段可以有多个NULL
② 一张表可以有多个UNIQUE字段
2.18.4 外键
1. 作用：用于定义主表和从表之间的关系，外键约束要定义在从表上，主表必须具有主键约束或者是UNIQUE约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或为null
2. 细节
① 外键指向的表的字段，要求是PRIMARY KEY或者是UNIQUE
② 表的类型是innodb，这样的表才支持外键
③ 外键字段的类型要和主键字段的类型一致（长度可以不同）
④ 外键字段的值，必须在主键字段中出现过，或者为NULL（前提是外键字段允许为NULL）
⑤ 一旦建立了外键的关系，数据就不能随意的删除了
2.18.5 check
1. 作用：用于强制行数据必须满足条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在这区间范围内，就会提示出错
2. 注意：Oracle和sql server均支持check，但是mysql 5.7目前还不支持check，只做语法校验，但不会生效
2.19 自增长（ AUTO_INCREMENT）
字段名 整形 PRIMARY KEY AUTO_INCREMENT
1.添加自增长的方式（字段1为设定的自增长）
INSERT INTO xxx（字段1，字段2）values （NULL,'值'）
INSERT INTO xxx（字段2） values（'值'）
INSERT INTO xxx values（NULL,'值'）
2. 细节
① 一般来说，自增长是和PRIMARY KEY配合使用的
② 自增长也可以单独使用（但是需要配合一个UNIQUE）
③ 自增长修饰的字段为整数型（小数也可以，但是很少这样使用）
④ 自增长默认是从1开始，但是也可以通过如下命令修改
ALTER TABLE '表名' AUTO_INCREMENT = 新的开始值
⑤ 如果添加数据时，给自增长指定的有值，则以指定的值为准
2.20 索引
2.20.1 索引类型
索引主要有一下四种类型：主键索引、唯一索引、普通索引和全文索引
2.20.1.1 主键索引
主键自动地为主索引（PRIMARY KEY）
创建方式有两种，第一种为在创建表的时候添加主键，第二种方式如下
ALTER TABLE 表名 ADD PRIMARY KEY（列名）
2.20.1.2 普通索引
创建普通索引也有两种方式，分别如下
CREATE INDEX 索引名 ON 表名 (列名)
ALTER TABLE 表名 ADD INDEX 索引名（列名）
2.20.1.3 唯一索引
创建方式如下
CREATE UNIQUE INDEX 索引名 ON （列名）
如果某列的值，是不会重复的，则优先考虑使用 unique 索引, 否则使用普通索引
2.20.1.4 全文索引
一般开发，不适用mysql自带的全文索引，而是使用：全文搜索Solt和ElasticSearch
2.20.2 删除索引
2.20.2.1 删除普通索引
DROP INDEX 索引名 ON 表名
2.20.2.2 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY
2.20.3 修改索引
修改索引比较简单，即先删除，再添加新索引
2.20.4 查询索引
有以下四种方式可以查询索引
SHOW INDEX FROM 表名
SHOW INDEXES FROM 表名
SHOW KEYS FROM 表名
desc 表名
2.20.5 哪些列上适合使用索引
1. 较频繁的作为查询条件字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，例如性别一列
3. 更新非常频繁的字段不适合索引
4. 不会出现在where子句中的字段不用创建索引
2.21 事务
事务用于保证数据的一致性，它有一系列相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性
2.21.1 mysql数据库控制台事务的几个重要操作
START TRANSSACTION --开始一个事务
SAVEPOINT 保存点名 -- 设置保存点
ROLLBACK TO 保存点名 --回退事务
ROLLBACK -- 回退全部事务
COMMUT --提交事务，所有的操作生效，不能回退
2.21.2 事务细节
1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2. 如果开始一个事务且没有创建保存点，则执行rollback，默认就是回退到事务开始的状态
3. mysql的事务机制需要innodeb的存储引擎才可以使用，myisam不能使用
4. 开始一个事务有两种语句，分别如下：
START TRANSACTION
SET AUTOCOMMIT=OFF
2.21.3 事务ACID
1. 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么不发生
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态
3. 隔离性（Isolation）：事务的隔离性是指多个用户并发访问数据库时，数据库为每一个用户开启的事务，不被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
4. 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久的，接下来即使数据库发生故障也不应该对其有任何影响
2.22 表的类型和存储引擎
2.22.1 基本介绍
1. MySQL的表类型有存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等
2. MySQL的数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB
3. 这六种又分为两类，一类是“事务安全型”（transaction-safe），如：InnoDB；其余都属于第二类，称为“非事务安全型”
2.22.2 主要的存储引擎/表类型特点
| 特点 | MyISAM | InnoDB | Memory | Aechive |
| 批量插入的速度 | 高 | 低 | 高 | 非常高 |
| 事务安全 | 支持 | |||
| 全文索引 | 支持 | |||
| 锁机制 | 表锁 | 行锁 | 表锁 | 行锁 |
| 存储限制 | 没有 | 64TB | 有 | 没有 |
| B树索引 | 支持 | 支持 | 支持 | |
| 哈希索引 | 支持 | 支持 | ||
| 集群索引 | 支持 | |||
| 数据缓存 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | |
| 数据可压缩 | 支持 | 支持 | ||
| 空间使用 | 低 | 高 | N/A | 非常低 |
| 内存使用 | 低 | 高 | 中等 | 低 |
| 支持外键 | 支持 |
2.22.3 细节说明
1. MuISAM不支持事务也不支持外键，但其访问速度快，对事务完整性没有要求
2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认是使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在
2.22.4 如何选择表的存储引擎
1. 如果应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快
2. 如果需要支持事务，选择InnoDB
3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快。但是由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态（））
2.23 视图（view）
1. 定义：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表
2. 细节：通过视图可以修改基表的数据；基表的改变，也会影响到视图的数据
3. 基本语句
CREATE VIEW 视图名 AS SELECT... --创建视图
ALTER VIEW 视图名 AS SELECT -- 更新视图
SHOW CREATE VIEW 视图名 --显示当时创建视图的语句
DROP VIEW 视图名1，视图名2 --删除视图
三、函数
3.1 合计/统计函数
3.1.1 COUNT：返回行的总数
SELECT count(*) | count(列名) from table_name
WHERE where_definition
注意：count(*) 返回满足条件的记录的行数
count(列名)统计满足条件的某行有多少个，但是会排除为null的情况
3.1.2 SUM：返回满足where条件的行的和
SELECT SUM(列名） FROM table_name
WHERE...
3.1.3 AVG:返回满足WHERE条件的一列的平均值
SELECT AVG(列名) from table_name
WHERE...
3.1.4 MAX/MIN:返回满足WHERE条件的一列的最大/最小值
SELECT MAX(列名) | MIN(列名) from table_name
WHERE...
3.1.5 GROUP BY和HAVING（二者一般是一起使用）
SELECT column1, column2...
FROM table_name
GROUP BY column HAVING...
注意：GROUP BY：用于对查询结果分组统计；HAVING子句用于限制分组显示结果
3.2 字符串相关函数
| 函数 | 作用 |
| CHARSET(str) | 返回字符串字符集 |
| CONCAT(str1,str2) | 连接字符串 |
| INSTR（string，substring） | 返回substring在string中出现的位置，没有则返回0 |
| UCASE(string） | 转换成大写 |
| LCASE(string) | 转换成小写 |
| LEFT(string,length) | 从string中的左边起取length个字符 |
| LENGTH(string) | string长度 |
| REPLACE(str,sear_str,replace_str) | 在str中用replace_str替换search_str |
| STRCMP(string1,string2) | 逐字符比较两字符串大小 |
| SUBSTRING(str,position,length) | 从str的position开始（从1开始计算），取length个字符 |
| LTRIM(str) | 去除前端空格 |
| RTRIM(str) | 去除后端空格 |
| TRIM(str） | 去除两端空格 |
3.3 数学相关函数
| 函数 | 作用 |
| ABS(num) | 绝对值 |
| BIN(dicimal_number) | 向上取整 |
| CONV(number,from_base,to_base) | 进制转换 |
| FLOOR(number) | 向下取整 |
| FORMAT(number,decimal_places) | 保留小数位数 |
| HEX(decimal_number) | 转十六进制 |
| LEAST(number1,number2) | 求最小值 |
| MOD(numetator,denominator) | 求余 |
| RAND([seed]) | 求随机数，范围是[0,1] |
3.4 日期相关函数
| 函数 | 作用 |
| CURRENT_DATE() | 当前日期 |
| CURRENT_TIME() | 当前时间 |
| CURRENT_TIMESTAMP() | 当前时间戳 |
| YEAR|MONTH|DATE（datetime） | 返回datetime的年|月|日部分 |
| DATE_ADD(data,INTERVAL d_value) | 在data中加上日期或时间 |
| DATE_SUB(data,INTERVAL d_value) | 在data中减去日期或时间 |
| DATEDIFF(data1,data2) | 两个日期差 |
| TIMEDIFF(time1,time2) | 两个时间差 |
| NOW() | 当前时间 |
注意：在实际开发中，我们也经常使用int来保存一个unix时间戳，然后使用from—_unixtime()进行转换，还是非常有实用价值的
3.5 加密和系统函数
| 函数 | 作用 |
| USER() | 查询用户 |
| DATABASE() | 数据库名称 |
| MD5(str） | 为字符串算出一个MD5 32的字符串，（用户密码）加密 |
| PASSWORD(str) | 从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密 |
3.6 流程控制函数
| 函数 | 作用 |
| IF(expr1,expr2,expr3) | 如果expr1为True，则返回expr2，否则返回expr3 |
| IFNULL(expr1,expr2) | 如果expr1为空，则返回expr2，否则返回expr1 |
| SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END | 如果expr1为True，则返回expr2，如果expr3为True，则返回expr4，否则返回expr5 |
四、MySQL常用数据类型（列类型）
| 分类 | 数据类型 | 说明 |
| 数值类型 | BIT(M) TINYINT [UNSIGNED] 占1个字节 SMALLINT [UNSIGNED] 2个字节 MEDIUMINT [UNSIGNED] 3个字节 INT [UNSIGNED] 4个字节 BIGINT [UNSIGNED] 8个字节
DOUBLE [UNSIGNED] DECIMAL (M,D)[UNSIGNED] | 位类型。M指定位数，默认值1，范围1-64 带符号的范围是-128到127。无符号0到255。默认是有符号 带符号是 负的2^15 到2^15-1,无符号0到216 -1 带符号是 负的 2^23 到 2^23-1,无符号0 到 2^24 -1 带符号是负的 2^31 到 2^31-1 ,无符号 0 到 2^32 -1 带符号是负的 2^63 到2^63-1,无符号 0到2^64 -1 占用空间4个字节 表示比float精度更大的小数,占用空间8个字节 定点数 M指定长度，D表示小数点的位数 |
| 文本、二进制类型 | CHAR(size) char(20) | 固定长度字符串 最大255 |
| 时间日期 | DATE/DATETIME/TimeStamp | 日期类型(YYYY-MM-DD)(YYYY-MM-DD HH:MM:SS) TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间 |
参考资料
本文所有内容均为韩顺平老师课上所讲知识，感谢韩顺平老师。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
