【MySQL】MySQL小结

MySQL数据库的基本信息

数据：记录事物的信息
表：数据的集合，由行和列组成；将多条数据组合在一起
数据库：是表的集合，是存储以统一格式且相互有关数据的仓库

DBMS的主要功能：1.数据库的建立和维护；2.数据定义功能；3.数据操纵功能；4.数据库的运行管理；5.通信功能

DBMS的工作模式：1.接受应用程序的数据和处理请求；2.将数据请求(高级指令)转化为复杂的机器代码(底层指令)；3.从而实现对数据库的操作；4.从数据库的操作中接受查询结果；5.然后将结果转化为高级指令；6.最后返回给用户

关系型数据库和非关系型数据库

关系型数据库

结构为二维表

常见的有：MySQL、Oracle、Postgresql、DB2、微软、Microsoft Access

非关系型数据库

数据结构化存储方式的集合，可以为文档、键值对等

高并发数据库，可以进行海量的并发读写

NoSql、Cloudant、MongoDB、redis K-V键值对、HBase

时序数据库-mariadb

随时间不断产生一系列的数据，具有时间标记，无法查看前面的数据

关系和非关系数据库的区别

关系：

优点：表结构格式一致，易于维护；支持多表查询；

缺点：读写性能差，尤其是高并发时；结构固定，缺乏灵活；

非关系：

优点：格式灵活；速度快；扩展性高；成本低；

缺点：不提供sql支持，学习成本高；无事务结构；多表、复杂查询较差；数据存储在内存中容易丢失

MySQL的安装

安装依赖包和环境
创建运行用户
编译安装、安装
修改配置文件
更改MySQL安装目录和配置文件的属主和属组
设置路径环境变量、刷新环境变量
初始化数据库
添加MySQL系统服务
修改mysql登录密码
授权远程登录、刷新权限

免密登录

mysql 数据库的基本操作

注意本文中的字段名和列名是指同一个东西，两个都写只是我的个人习惯；

查看数据库信息

查看数据库        show databases;
切换数据库        use 库名;
查看当前数据库下的表        show tables;
                           show tables in mysql;

查看数据表的结构    describe 库名.表名;
                   describe 表名;    简写：desc 表名;

表结构的含义

Field    为字段名称
Type     为数据类型
Null     是否为空
Key      键的类型
Default    为默认值
Extra     为扩展属性；例：标志符列（标识了种子，增量/步长）

数据类型

int         整型；用于定义证书类型的数据；
float       单精度浮点型；4字节32位，准确到小数点后六位，后续不准确
double      双精度浮点型；8字节64位，有效位数15位，后续不准确
char        固定长度的字符类型；可以放字符串（'英文'和'汉字'）
varchar     可变长度的字符类型；
text        文本
imge        图片
decimal(5,2)    表示5个有效长度数字，小数点后面显示两位；指定长度数组
int(4) zerofill     不满四位，自动补0到指定长度
auto_increment      自增长字段，从1开始每次自增1;该字段必须具有唯一性
primary key    主键
unique key     唯一键，可以有多个，但每个的值唯一
not null    不可为空
default '值'    设置默认值

#自增和补零不能一起用

SQL分类

DDL：数据定义语言；用于创建数据库对象

建库    create database 库名;
建表    create table 表名 (字段1 数据类型,字段2 数据类型,...,PRIMARY KEY (主键名));
create table if not exists 表名........
#创建表时判断表是否存在

创建临时表    create temporary table 表名 (字段1 数据类型,字段2 数据类型,...,PRIMARY KEY (主键名));
创建克隆表    create table 新表名 like 旧表名;
导入数据      insert into 新表名 select * from 旧表名;
直接备份数据，但不会导入主键和唯一键
        create table 新表名 (select * from 旧表名);

DML：数据操纵语言；用于插入、删除和修改数据库中的的数据

插入数据
insert into 表名 (字段1,字段2,...) values(值1,值2,...);
insert into 表名 values(值1,值2,...,值x);

修改数据
update 表名 set 字段='新值' where 列名='值';

删除数据
删除所有内容    delete from 表名;
删除指定行      delete from 表名 where 列名='值';
               truncate table 表名;

高级操作

删除表
drop table 表名;
删除数据库
drop database 库名;

drop、delete、truncate对比
drop    直接把表删掉，速度最快，不可回滚，什么都不剩；不能用where
delete    逐行删除数据，速度慢，可回滚，可带where
truncate    删表重建结构，速度较快，不可回滚，不可带where

DQL：数据查询语言；用于从数据表中查询符合条件的数据

查询数据
所有        select * from 表名;
指定列      select 字段1,字段2 from 表名;
指定行      select * from 表名 where 列名='值';
模糊查询    select * from 表名 like '%值%';
竖向显示    select * from 表名\G;

显示头两行    select * from 表名 limit 2;
显示第三行后的两行    select * from 表名 limit 3,2;


查看索引信息
show create table 表名\G;

DCL：数据控制语言；用于设置或更改数据库中用户的权限

修改表名    alter table 旧表名 rename;

添加字段    alter table 表名 add 新字段 数据类型;
删除字段    alter table 表名 drop 字段名;
修改字段    alter table 表名 change 旧字段 新字段 数据类型;
修改类型    alter table 表名 modify column 字段名 数据类型;
#字段类型不建议修改，容易导致数据丢失

数据库管理

用户管理

新建用户
create user '用户名'@'来源地址' identified by '密码';
select password('密码');
create user '用户名'@'来源地址' identified by password '密文'; 

查看用户信息
use mysql;
select user,authentication_string,host from user;
select * from user;    查看用户表的所有信息，可以通过指定字段来方便查看

重命名用户
rename user '旧名'@'来源地址' to '新名'@'来源地址';
update mysql.user set user='新名' where user='旧名';

修改指定用户密码
set password for '用户名'@'来源地址' = password('新密码');

修改当前用户密码
set password = password('新密码');

删除用户
drop user '用户名'@'本机地址';

以上指令执行后都需要flush privileges; 刷新数据库才能生效

忘记root密码的解决办法

修改/etc/my.cnf 文件，设置免密登录
vim /etc/my.cnf
[mysqld]
skip-grant-tables
保存并重启mysqld
然后直接登录root用户
mysql -uroot

修改root用户密码
update mysql.user set authentication_string = password'新密码' where user='root';

最后删除/注释掉免密登录的代码，重启mysqld即可

数据库用户授权

授权远程登录
grant 权限列表 on 库名.表名 to '用户名'@'来源地址' identified by '密码';

权限列表：select、inset、update、delete、all privileges


创建用户并授予权限
grant select,insert on test.* to '新用户名'@'来源地址' identified by '密码';

查看指定用户拥有的权限
show grants for 用户名@来源地址;

撤销权限
revoke 权限列表 on 库名.表名 from 用户名@来源地址;

#USAGE权限只能用于数据库登陆，不能执行任何操作;无法被赋予和撤销

 MySQL索引的介绍

索引的概念

作用：加快数据库查询速度；降低数据库IO成本；减少分组和排序的时间

副作用：需要额外占用磁盘空间；在修改数据时需要花费更多的时间

应用场景

适合建立索引：小字段；唯一性强的字段(主键、唯一键、外键)；数据超过五百条；平时不改动但经常访问的字段；经常与其他表进行连接的表；

索引的建立

创建普通索引
create index 索引名 on 表名(列名);
create index 索引名 on 表名(列名(length));
alter table 表名 add index 索引名(列名);
create table 表名 (字段1,字段2,...,index 索引名(列名));

创建唯一索引
create unique index 索引名 on 表名(列名);
alter table 表名 add unique 索引名(列名);
create table 表名 (字段1,字段2,...,unique 索引名(列名));

创建主键索引
alter table 表名 add primary key(列名);
create table 表名 (字段1,字段2,...,primary key(列名));

创建组合索引
create table 表名 (字段1,字段2,...,index 索引名(字段1,字段2));

创建全文索引
create fulltext index 索引名 on 表名(列名);
alter table 表名 add fulltext 索引名(列名);
create table 表名 (字段1,字段2,...,fulltext 索引名(列名));

索引的使用

使用全文索引查询
select * from 表名 where match(列名) against('查询内容');

查看索引
show index from 表名;
show index from 表名\G;
show keys from 表名;
show keys from 表名\G;

删除索引

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
alter table 表名 drop primary key;

各字段的含义

Table	    表的名称
Non_unique	如果索引内容唯一，则为 0；如果可以不唯一，则为 1。
Key_name	索引的名称。
Seq_in_index	索引中的列序号，从 1 开始。 limit 2,3
Column_name	    列名称。
Collation	    列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。
Cardinality	    索引中唯一值数目的估计值。
Sub_part	    如果列只是被部分地编入索引，则为被编入索引的字符的数目(zhangsan)。
                如果整列被编入索引，则为 NULL 
Packed	    指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null	    如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment	    备注。

MySQL事务

事物的概念

事务就是一个操作序列；这些操作要么都执行，要么都不执行，是一个不可分割的工作单位

事务ACID的特点

事物的四个特性

原子性：事务是一个不可分割的工作单位，一个事务中的所有操作要没都执行，要么都不执行；
一致性：事务开始之前和事务结束以后，数据处于一致状态，数据库的完整性约束不会被破坏；
隔离性：事物之间相互隔离，互不影响；
持久性：事务完成后，该事务对数据库所做的更改就会永久保留在数据库；

事务的相互干扰

脏读：读取到了未提交的数据
不可重复读：前后多次读取，返回的数据内容不一致
幻读：前后多次读取，数据总量不一致
丢失更新：后来的事务覆盖掉了前事务的修改结果

MySQL事务的4种隔离级别

read uncommitted 未提交读：读取了尚未提交的数据
read committed 提交读：读取已提交的数据
repeatable read 重复读取：解决脏读和不可重复读
serializable 可串行化；一个一个来，解决脏读、不可重复读和幻读

查询事务隔离级别

查看事务隔离级别
全局：
show global variables like '%isolation%';
select @@global.tx_isolation;
会话：
show session variables like '%isolation%';
select @@session.tx_isolation;
select @@tx_isolation;

设置事务隔离等级
全局：
set global transaction isolation level 隔离等级;
会话：
set session transaction isolation level 隔离等级;

事务控制语句

开始        begin;
设置回滚点    savepoint s1;
设置回滚点    savepoint s2;
回滚        rollback to s1;
            rollback;
提交事务    commit;

使用set设置控制事务
set autocommit=0;
set autocommit=1;
查看当前的autocommit值
show variables like 'autocommit';

MySQL存储引擎

mysql系统中数据的流向

存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

数据——》存储引擎——》数据文件

常用的存储引擎

myisam和innodb

存储引擎负责执行实际的数据读写

MyISAM和InnoDB的区别

MyISAM：表级锁定；清空重建；不支持事务；硬件要求低；并发量低；
innodb：行级锁定；一行行删除；支持事务；硬件要求高；高并发；

MyISAM三个存储文件

.frm        存储表结构
.MYD        存储数据文件
.MYI        存储索引文件

MyISAM表支持三种存储形式

静态表：myisam默认的存储形式；记录长度固定char；存储快，故障易恢复；占用空间多

动态表：可变字段长度varchar；空间占用少，但会产生碎片，需定期清理；故障恢复较难

压缩表：压缩记录，占用空间最少，减少访问开支

存储引擎

查看表使用的存储引擎
show table status from 库名 where name='表名'\G;

user 库名;
show create from 表名;
show create from 表名\G;

修改存储引擎
alter table engine='引擎名';
修改my.cnf配置文件中的默认存储引擎，重启后新建的表生效；