MySQL整理

登录
命令：mysql -h [IP 地址/域名] -P [端口] -u [用户名] -p[密码]

端口默认是3306

mysql -hlocalhost -uroot -p

忘记root登录密码的解决方法：
https://blog.csdn.net/hey_yf/article/details/80024706

断开

1. exit;
2. \q; 或quit;

创建一个数据库，就会产生对应存储数据的的文件夹，每个文件夹下都有opt文件，保存对应的数据库选项。数据库选项有字符集和校对集。

显示数据库
系统数据库：information_schema :保存数据库所有的结构信息。
mysql：保存权限关系
performance_schema:效率库

显示部分数据库：
show databases like "匹配模式"
_:当前位置匹配单个字符
%：匹配多个字符

数据表
表选项：engine：存储引擎；
charset：字符集
collate：校对集
create database.table(
字段名 字段类型，……
）engine= innodb charset=utf8;

复制一个表结构：create table newtable like database.table1
每创建一个数据表，在数据库文件夹下会创建一些文件(如.frm)
查看表结构：desc 表名
查看数据表创建语句：show create table 表名;
修改表结构：alter table 表名 add 字段 属性 first/after 字段;
修改表字段：alter table 表名 change 旧字段名 新字段名 字段类型;
修改字段属性：alter table 表名 modify 字段名 新类型；
删除字段：alter table 表名 drop 字段;
删除表：drop table 表名;

插入数据：
insert into 表名 (字段列表) values (对应字段列表的值); 值列表只需与字段列表对应即可，无需与表结构相同。

查询数据：
查询所有数据：select * from 表;
查询部分数据：select 字段1，字段2，字段3 from 表；
条件查询： select 字段/* from 表 where 条件;
删除数据：delete from 表名 [条件];
更新数据：update 表名 set 字段=新值 where 条件;

显示长度：类型后边的数字指显示长度，指最长可以显示的长度。
zerofill：从左侧填充0.
mysql中小数分浮点型（float、double）和定点型（decimal），定点型可以保证整数部分的精确，浮点型只有前7位可能精确。
定点数应用：涉及钱的时候用定点数

时间日期类型：
date：3个字节，格式为YYYY-mm-dd
time：3个字节，格式为HH:ii:ss
datetime:8个字节，格式为date和time的合并
timestamp：时间戳，格式与datetime相同，默认为当前时间，不能为空。timestamp的记录如果被更改，自己会自动更新。
year：1个字节 范围为1900-2155年。
通常时间的处理在编程中处理，mysql中只保存时间戳，用整数存储。

列属性
NULL，default，comment ，主键，自增长，唯一键。
增加主键：create table 表名(字段 类型 primary key，……) charset utf8;
create table 表名(字段 类型，……，primary key(字段))charset utf8;
alter table 表名 add primary key(字段);
删除主键：alter table 表名 drop primary key;
主键约束：当前字段不能为空，字段的数据不能有重复。
唯一键，创建方式与主键类似。

新增数据：
主键冲突解决方案：

1. 主键冲突更新：如果插入的过程中主键冲突，采用更新方法
   insert into 表名 values(值列表) on duplicate key update 字段=新值;
2. 主键冲突替换：主键冲突后，删除原来的数据，重新插入新数据。
   replace into [字段列表] values(值列表);

蠕虫复制：从已有的数据中获取数据，并将获取到的数据插入到数据表中。
insert into 表名(字段) select */字段 from 表名;
应用：通常复制的是重复数据，可以在短期内快速增加表的数据量，从而测试表的压力，还可以测试表的索引。

更新数据：

1. update 表名 set 字段名 = 新值 where 判断条件
2. 如果没有条件，是全表更新，可以使用limit 来显示更新的数量。
   update 表名 set 字段名 = 新值 [where 判断条件] limit 数量;

查询数据：
select 选项 字段列表 from 数据源 where 条件 group by 条件 having 条件 order by 条件 limit 限制；
选项：all(默认)，distinct
别名：字段 [as] 别名
数据源：from 表1，表2……
动态数据：from [子查询] as 别名;
select * from (select * from table) as alias;
group by : 是为了分组后进行数据统计，将数据按照字段分组后，只会保留每组的第一条记录。count()统计每组中的数量;avg();group_concat(字段)：将指定的字段合并。
group by多分组：group by 字段1，字段2;
group by分组排序：group by 字段1 [asc|desc],字段[asc|desc];
h回溯统计：group by 字段[asc|desc] with rollup;

having:
1.用于筛选条件，与where的区别是，having 用在group by后边，用于对分组的数据进行条件筛选，可以使用聚合函数，where是从表中取数据，将数据从磁盘装进内存。

order by:
order by 字段1，字段2……;先按字段1排序，后按字段2排序

limit:
应用：1、用来限制记录获取的数量
2、分页，用来获取指定区间的数据：limit offset,length;

联合查询
union:select 字段 union select 子句;默认去重。
使用order by时，两个select要括起来。且必须加limit才能生效。

行子查询：
主查询 where 条件 (构造行元素)=(行子查询);在行子查询中就可以使用聚合函数。
列子查询：
主查询 where 条件 字段 in (列子查询);
行子查询和列子查询都属于where子查询；
表子查询：
返回多行多列；用于from数据源，属于from子查询。
select 字段表 from (表子查询) as 别名……;
exists子查询：
where exists(查询语句);

整库数据备份(SQL数据备份)
不只备份数据，还备份sql指令。
产生的备份结构很大，不适合特大型数据备份，也不适合数据变换频繁型的数据库备份。
语法：mysqldump -hPup 数据库名 > 备份地址
整库备份
单表备份
多表备份

数据还原：
1、使用mysql客户端：mysql -hPup 数据库<文件位置
2、导入sql指令
source SQL文件位置

mysql用户管理
mysql中所有的用户信息保存在mysql库中的user表中
创建用户：create user '用户名'@'' identified by '密码';
删除用户：drop user '用户名'@'IP主机';
更改密码：set password for 用户=password();
权限管理：
数据权限：增删改查
结构管理(create\drop)
管理权限(create user\grant\revoke)
授予权限：
grant 权限列表 on 数据库.表 to 用户;
取消权限：
revoke 权限列表 on 数据库.表 from 用户;
刷新权限：
flush privileges;

事务

自动事务：
客户端发送一条sql指令后，服务器不用等待用户反馈，自动将结果同步到数据表。
系统通过autocommit变量来控制，可通过show variables like "autocommit"查看。
关闭自动事务：set autocommit =off；
自动事务关闭后，使用commit命令提交同步到数据表，使用rollback命令回滚，清空之前的操作。提交命令后，在当前客户端可以看到提交后的数据。
手动事务：
需要用户手动发送事务操作指令实现。
对应的命令：
1、start transaction;//这条语句后面的所有语句都不会直接写入数据表
2、事务处理；
3、事务提交：commit、rollback;
回滚点：savepoint，事务中前面一部分事务执行成功，无需重新执行则在该点设置一个记号，如果后面的事务执行失败，则回到这个位置。
增加回滚点：savepoint 回滚点名字；
回到回滚点：rollback to 回滚点。