一、前言
此处记录一下，平时用到的一些MySQL命令，方便下次查阅。
本章不足之处会在有时间的时候不断更新。
二、操作命令
数据库相关操作
登录
全写：
mysql -h 127.0.0.1 -P 3306 -u root -proot
注意：-h 表示主机IP 如果不写代表本机
-P 表示端口号 如果不些默认3306 端口可修改 防止被恶意攻击
-u 表示用户名 -u和root之间可以有空格 也可以没有
-p 表示密码 之间不能有空格！！！
简写:推荐这种登录方式，不建议使用明文登录。
mysql -u root -p
输入密码
修改密码
新安装MySQL时，密码是随机生成的，此时我们需要重新设置密码，下面的语句是修改root账户的密码为root
update mysql.user set authentication_string=password('root') where user='root';
这是正常的修改方式
mysqladmin -u用户名 -p旧密码 password新密码
备份与恢复数据库
备份：注意该命令是在dos命令行下执行。
多个数据库用空格隔开，文件名要带上地址。
mysqldump -u root -p -B 数据库1 数据库2 [数据库3...] > 文件名.sql
恢复：进入MySQL命令行执行
source 文件名.sql
显示数据库字符集
若要修改，修改配置文件my.ini
show variables like 'character_$'
## 比较规则
show variables like 'collation_$'
显示所有数据库
show databases;
创建数据库
通常的创建方式。
CREATE DATABASE database_name;
全命令为：
CREATE DATABASE [IF NOT EXISTS] database_name[create_specification [,create_specification ] ...]
create_specification 参数为：
CHARACTER SET : 数据库采用的字符集 ，默认utf8
COLLATE:数据库字符集的校对规则（常用的utf8_bin , utf8_general_ci ,默认为uft8_general_ci（不区分大小写））
显示数据库创建语句
show create database database_name
该命令会将数据库的创建语句返回。
删除数据库
drop database name;
drop命令可以使用if exits选项判断数据库是否存在，存在即删除，不存在也不会报错。
drop database if exists name;
使用数据库
database_name为数据库实例名
use database_name;
查看当前数据库
返回当前数据库名
select database();
显示当前数据库版本
select version();
显示当前日期
select now();
退出
以下两种都可以。
exit;
quit;
命令导入表和表数据
resource d://mydate.sql
表相关操作
查看数据库所有表
show tables;
创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype unsigned，
field3 timestamp not null
default current_timestamp
on update current_timestamp
)
CHARACTER SET utf8
COLLATE uft8_general_ci
ENGINE innodb
CHARACTER SET 字符集，不写默认当前数据库字符集
COLLATE 校对规则，不写默认当前数据库校对规则
ENGINE 存储引擎，不写默认innodb
unsigned 表示定义一个无符号的整数，此时datatype一定要为整形才可以
not null
default current_timestamp
on update current_timestamp
这是对时间进行限制，表示不为空、默认当前时间戳、更新时使用当前时间戳。可以自定义设置所需。
修改表结构
添加字段
ALTER TABLE table_name
ADD (column datatype[,column datatype]...)
例：ALTER TABLE t1 ADD interest VARCHAR(255) NOT NULL DEFAULT '' AFTER name
修改字段数据类型
ALTER TABLE table_name
MODIFY(column datatype[,column datatype]...)
修改字段名
ALTER TABLE table_name
CHANGE column new_column datatype
删除字段
ALTER TABLE table_name
DROP (column)
表的重命名
RENAME TABLE table_name TO new_table_name
修改表的字符集
utf8代表字符集
ALTER TABLE table_name CHARACTER SET utf8
删除表
DROP TABLE table_name
查看表结构
DESC table_name
表数据相关操作
新增
INSERT INTO t1(column1,column2)VALUES(value1,value2)
修改
不加where是对全表操作，慎用！
UPDATE table_name SET column1=value1,column2=value2 WHERE column3=value3
在原值基础上增加值
UPDATE table_name SET column1=column1+1000 WHERE column3=value3
删除
不加where是对全表操作，慎用！
DELETE FROM table_name WHERE column1=value1
查询
查询及去重
SELECT [DISTINCT] *|{column1,column2,column3} FROM table_name
查询的字段还可以配合表达式运算和 加as 写别名
排序 order by asc/desc
分组 group by … having …
函数
合计函数/统计函数
count()
- 表示统计满足条件的所有数据的列的个数，1是将该列的值设为1，进行统计，稍微缩短查询时间，column是查询满足条件的所有非null数据的列的个数。
SELECT COUNT(*) FROM table_name
SELECT COUNT(1) FROM table_name
SELECT COUNT(column) FROM table_name
其他合计函数
sum() 求和
avg() 求平均值
max() 最大值
min() 最小值
字符串函数
CHARSET(str) 返回字符串的字符集
CONCAT(str1,str2) 拼接字符串
UCASE(str) 转为大写
LCASE(str) 转为小写
LEFT(str,length) 从左边截取几个字符
RIGHT(str,length) 从右边截取几个字符
LENGTH(str) 返回字符串长度（字节!!!）
REPLACE(str,oldstr,newstr) 替换
SUBSTRING(str,index[,length]) 截取字串 index从1开始
LTRIM(str) 去除左边空格
RTRIM(str) 去除右边空格
TRIM(str) 去除两边空格
数字函数
此处只记录常用的数字函数，比较太多了，不常用的使用到了查阅资料吧
ABS(num) 绝对值
CEILING(num) 向上取整
FLOOR(num) 向下取整
FORMAT(num,小数位数) 四舍五入 保留小数位
LEAST(num1,num2[,..]) 求最小值
MOD(num) 求余
RAND(seed) 随机数，范围0<=n<=1.0
日期函数
日期是开发中不可缺少的部分，需要着重记忆！
CURRENT_DATE() 当前日期
CURRENT_TIME() 当前时间
CURRENT_TIMESTAMP() 当前时间戳 年月日 时分秒
NOW() 当前时间
YEAR() MONTH() DAY() 返回时间的部分信息
DATE(datetime) 返回datetime的日期部分
DATE_ADD(date,INTERVAL value TYPE) date上加上一个日期或时间 单位可以为year、day、hour、minute、second等
DATE_SUB(date,INTERVAL value TYPE) date上减去一个日期或时间
DATEDIFF(date1,date2) 两个日期相差多少天
unix_timestamp() 返回1970-1-1到现在的秒数
from_unixtime(s,'%Y-%M-%D %H-%i-%s') 可以将unix_timestamp秒数 转成指定格式 类似simpledateformat
last_day(date) 返回当前月的最后一天
加密函数
# 当前用户，返回用户名@ip
user()
# 加密算法
md5(str)
# MySQL默认加密算法
password(str)
流程控制函数
# if 当exp1为真时 返回exp2 否者返回exp3 类似三目运算符
IF(exp1,exp2,exp3)
# ifnull 当exp1不为空时返回exp1，否者返回exp2
IFNULL(exp1,exp2)
# 类似 switch case,相当于子查询了~
select case
when exp1 then exp2
when exp3 then exp4
...
else exp5 end;
表复制和去重
创建一张已存在的表，即将其他表的数据复制过来。
CREATE TABLE table_name AS SELECT * FROM source_table_name
CREATE TABLE table_name(col1 type1,col2 type2) AS SELECT col3,col4 FROM source_table_name;
实现自身表指数倍复制（大量重复）
INSERT INTO table_name AS SELECT * FROM source_table_name
删除表中重复数据
此处提供一种想法。
1、创建相同结构的表
2、将现有表的数据查出来并使用distinct去重
3、将查处的数据存入新表
4、要么直接删除原有表，并将新表重命名为原表
或者 删除原表数据，再将新表的数据添加到原表，并删除新表
这只是一个想法，有其他更好的也可以的，sql本就是很灵活的。
索引
如果表没有索引，它是进行全表扫描，表中数据量大时查询速度很慢，但是加了索引，查询速度会飞速提升，它会形成类似与二叉树等的结构，提升查询速度，但是索引本身也是有缺点的，1、它会占用比较大的磁盘空间。2、它会对inser、update、delete的效率有所影响。但是大家都知道，一个系统中查询的需求是远大于增删改的。一定程度上的牺牲是有必要的。
索引大致分为：主键索引、唯一索引、普通索引、全文索引
主键索引其实就是主键约束（primary key），建表时添加了该约束，自动为主键索引。也可以后期添加的。
唯一索引其实就是唯一约束（unique），建表时添加了该约束，自动为唯一索引。也可以后期添加的。
普通索引就是为了提升查询速度的索引，可为NULL。
全文索引MySQL有自带的，但是非常不好用，实际开发中会选择第三方产品，如ES等。
# 查询表是否有索引（三种）
show index from table_name
show indexes from table_name
show keys from table_name
# 添加索引
# 添加唯一索引
create unique index index_name on table_name(column_name);
# 添加普通索引
create index index_name on table_name(column_name);
# 添加普通索引方式2
alter table table_name
add index index_gname(column_name);
# 添加主键索引
alter table table_name
add primary key(column_name);
# 删除索引方式一
drop index index_name from table_name
# 删除索引方式二
alter table table_name drop index index_name
# 删除主键索引
alter table table_name drop primary key
修改索引：就是先删除索引，再添加索引~~
事务
事务由一组dml语句组成，用于保证数据的一致性，该组语句要么全部成功，要么全部失败。
事务操作
# 开启一个事务
start TRANSACTION;
# 设置保存点 a为保持点名，可根据需要自行设定
SAVEPOINT a;
# 事务回滚 返回到a保存点时的状态
ROLLBACK to a;
# 事务回滚 回滚到事务起始状态
ROLLBACK;
# 事务提交
COMMIT;
注意：
1、如果不开启事务，事务是自动提交的，不能回滚，因此如果要回滚，需要开启事务，或者执行set autocommit=off即关闭自动提交
2、MySQL的事务机制必须是在InnoDB的存储引擎才生效，myisam是不好使滴
3、开启事务时不用设置保存点，它也默认算是有一个保存点，可通过rollback回到起始状态，并且一个事务可以有多个保存点
事务的隔离级别
事务与事务的隔离程度。
当多个用户开启各自事务操作数据库时，数据库要负责隔离操作，保证各自操作数据的准确性。如果不考虑隔离级别会出现脏读、不可重复读、幻读等现象。
隔离级别分为四种，分别为：读未提交、读已提交、可重复读、可串行化。默认是可重复读。
| MySQL隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
|---|---|---|---|---|
| 读未提交 【read uncommitted】 | √ | √ | √ | 不加锁 |
| 读已提交 【read committed】 | × | √ | √ | 不加锁 |
| 可重复读 【repeatable read】 | × | × | × | 不加锁 |
| 可串行化 【serializable】 | × | × | × | 加锁 |
| √表示出现 ×表示不出现 |
事务隔离级别操作
理论上来说，我们不太需要去操作隔离级别。默认的就足够了，列举出来是方便以后真用到了，可以查看回顾。
# 查看当前会话隔离级别
select @@transaction_isolation;
# 查看当前系统隔离级别
select @@global.transaction_isolation;
# 设置当前会话隔离级别
set session transaction isolation level repeatable read;
# 设置当前系统隔离级别
set global transaction isolation level repeatable read;
事务的ACID特性
MySQL的ACID特性是一个面试高频考点。
ACID表示事务的原子性、一致性、隔离性、持久性。
**原子性：**事务是一个不可分割的工作单位，事务中的操作，要么全部成功，要么全部失败。
**一致性：**事务必须使数据库从一个一致性变换到另一个一致性状态。
**隔离性：**事务的隔离性是多个用户发生并发访问数据库时，数据库为每一个用户开启事务，保证事务之间相互隔离，操作的数据不受其他事务干扰。
**持久性：**事务一旦被提交，它对数据库的改变就是永久的。
存储引擎
MySQL的存储引擎很多，可以通过命令show engines查询。
常用到的存储引擎大致为InnoDB、MyISAM、MEMORY。
修改存储引擎的命令为alter table table_name engine InnoDB
不同的业务可以选择不同的存储引擎，每一种都有其特别的优势。默认情况下时MySQL的引擎为InnoDB，其他的引擎介绍此处暂时不介绍。
视图
视图就是一张虚拟表，它基于基表，数据根据查询条件所得。
视图不仅可以比一张基表小，还可以比它大，因为它可以基于多张基表。
他的操作和表得操作非常类似。
# 创建视图
create view view_name as select column_name1,column_name2[, ...] from table_name;
# 删除视图
drop view view_name1,view_name2;
# 修改视图，可以看成是重新创建的感觉
alter view view_name as select column_name1,column_name2[, ...] from table_name;
# 查询视图创建语句
show create view view_name;
# 查询、修改数据等与表的数据操作一样，比如查询
select * from view_name
注意：
1、向视图里新增数据时必须保证新增语句能够在基表中执行成功，否者会新增失败
2、数据库中，视图只有一个结构文件，没有数据文件，它的数据是基于基表的
3、视图数据的改变会影响基表，基表数据的改变也会影响视图
4、视图也可以基于其他视图
用户管理
# 创建用户
create user 'user_name'@'host' identified by 'password';
# 创建的用户所以IP都可生效
create user 用户名
# 指定ip段，表示如下ip段都可以登录
create user 'user_name'@'192.168.1.%' identified by 'password';
# 删除用户
drop user 'user_name' @ 'host';
存入MySQL user表的密码事通过password（）函数加密后的
# 修改自己的密码
set password = password('123456');
# 修改其他用户的密码，需要有修改用户密码的权限
set password for 'user_name'@'localhost' = password('123456');
权限管理
# 给用户授权
grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']
如果事所以权限就使用all
如果事多个权限，用逗号分割
如果是所以库的所有对象使用*.*
如果是某个库的所有对象使用库.*
对象表示数据库的表、试图、存储过程等
identified by 密码 可以不写，写上了，如果用户存在，则表示授权时一并修改了密码，不存在则表示授权时创建了用户
# 回收权限
revoke 权限列表 on 库.对象 from '用户名'@'登录位置'
部分朋友的MySQL版本比较低，授权后不生效的，使用如下命令刷新权限即可。
flush privileges;
参考地址
非常感谢韩顺平老师的java课程，让我巩固了很多知识点，这是老师的b站地址，有兴趣的可以看一看哦。
https://www.bilibili.com/video/BV1fh411y7R8
三、结束语
MySQL还是非常常见的数据库，因为其开源且体积小的特性很受大家喜欢，它的命令还是比较多，但是都比较的容易，需要花费一些时间来记忆，其实最好的记忆就是多使用，使用的多了，很多东西自然就会了。
本章就到这里了，回见！
扫一扫
1891
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
