MySQL深入学习总结

MySQL

MySQL是瑞典的MySQL AB公司开发的典型的开源关系型数据库管理系统，其中维护的都是二维表，类似于Excel表格；关系型数据库除了MySQL，还有Oracle数据库（甲骨文公司，闭源）、SQL Server（微软，闭源）；后来MySQLab被Sun公司收购，Sun公司又被甲骨文公司收购，所以现在MySQL是Oracle旗下产品。因害怕Oracle今后不再开放MySQL源代码，其创始人重写了一套关系型数据库命名为MariaDB，其功能与MySQL基本没有差别。

关系型数据库：MySQL、MariaDB、SQL Server、Oracle等

非关系型数据库（NOSQL）：Redis、MongoDB、Hbase等

安装MySQL数据库（5.5版本）：yum -y install mariadb mariadb-server

启动MySQL：systemctl start mariadb.server

开机自启动：systemctl enable mariadb.server

设置用户名密码：mysqladmin -uroot password 123456

登录数据库：mysql -uroot -p123456

SQL语句（92标准版SQL语句，适用于大部分关系型数据库）

DDL 数据定义语言 create drop

DML 数据操纵语言 update delete insert

DQL 数据查询语言 select

DCL 数据控制语言 grant

（一）查看数据库内容

1.查看数据库：show database；

进入数据库：use 库名；例：use mysql；

进入后，查看库中的表：show tables；

（二）数据库及表的操作

创建数据库：create database 库名；例：create database abc；

删除数据库：drop database 库名；例：drop database abc；

创建表：create table 表名（字段1 数据类型1，字段2 数据类型2......）；创建一个名为info的表，内含字段id和name，类型为整数和可变长字符串，（）内是规定有多少位。

例：create table info（id int(3),name varchar(10)）；

查看表结构：desc 表名；例：desc info；

show create table 表名；

数据表内容的增删改查

增加（insert）

插入一行数据：insert into 表名 values（值1，值2）

查看刚刚插入表中的数据：select * from 表名；例：select * from info；

一次性插入多行数据（中间的，可以分开多行数据）：例：insert into info values(3,”xiaozhao”),(4,”xiaoqiang”);

删除（delete）

删除表中所有内容：delete from 表名；例：delete from info；

删除某一条数据：delete from 表名 where 字段=值；例：如果删除上表4号数据，则 delete from info where id=4；

修改（update）

修改表中name字段所有内容：update info set name=“xiaoming”

修改符合条件的值，update 表名 set 需要修改的字段=“新名称” where 字段=值；例:修改表中的xiaozhao为xiaogang，则：update info set name=”xiaogang”where id=3;

查看（select）

查看表中内容：select * from 表名；例：select * from info；

表结构（字段）相关操作

查看表结构

desc 表名；或者show creat table 表名；

修改表结构

增加一个字段：alter table 表名 add 字段名 字段类型；例，为info表增加一个年龄字段：alter table info add age int（3）；

注：如果想指定位置，则可在int（3）后写first、after id、before

删除一个字段：alter table 表名 drop 字段名；例，删除info表中age1字段：alter table info drop age；

修改字段数据类型：alter table info modify 字段 更改的字段类型；例，修改age的类型为int（5）：alter table info modify age int（5）；

修改字段顺序，例，将name字段移到第一行：alter table info modify name varchar（10）first；

修改已有字段的名称，例，修改age为性别（sex）：alter table info change age sex varchar（5）；

修改表名，例，将info修改为info1：alter table info rename info1；

创建两张表，第一张包含id、name、subject字段，第二张表包含id、source字段

为表t1插入数据：

insert into t1 values(1,"xiaoming","English"),(2,"xiaohong","English"),(3,"xiaogang","English");

为表t2插入数据：

为表2source字段插入数据：

（五）创建新一个新库

创建一个新库，指定字符编码为utf8：create database abc charset=utf8;

进入abc库，创建名为学生的新表，内含名字、编码字段：create table student(name varchar(10),code int(3) zerofill);

创建名为info的新表，内含code、subject、score：create table info(code int(3) zerofill,subject varchar(10),score int(3));

向student表中插入数据：insert into student values("张三",001),("李四",002),("王五",003),("甲六",004);

向info表中插入数据：insert into info values(001,"数学",80),(002,"数学",75),(001,"语文",90),(002,"语文",80),(001,"英语",90),(002,"英语",85),(003,"英语",80),(004,"英语",70);

order by排序（内含limit分页）

排序：以成绩为标准对info表内数据升序排列：select * from info order by score;

降序排列：select * from info order by score desc;

查看info表中前两条数据：select * from info limit 2；

只显示某一行：select * from info limit 偏移量，行数；例：偏移量从0开始，即第一行为0。

根据表info中的数据按降序排列，并显示出第一行数据：

select * from order by score desc limit 1；

注意：limit是用于分页查看，偏移量是指从第几行开始，第一行表示为0。

group by分组

根据表info对科目进行排序：select * from info group by subject；

聚合函数

聚合函数sum（）求和，max（）最大值，min（）最小值，count（）统计，avg（）平均数。

格式：select 函数（值）from 表名；

根据score排序并列出最小值：select min（score） from info；

去重

distinct去重：格式，select distinct 字段 from 表名；

select distinct subject from info；

聚合

问题1：查询出所有学生信息，SQL语句怎么编写：select a.code,a.name,b.subject,b.score from student a,info b where a.code=b.code;

问题2：新学生小明，学号为005，需要将信息写入学生信息表，SQL语句怎么编写：insert into student values（“小明”，5）；

问题3：李四语文成绩被登记错误，成绩实际为85分，更新到考试信息表中，SQL语句怎么编写：update student a ,info b set score=85 where a.name="李四" and b.subject="语文"and a.code=b.code;

问题4：查询出各科成绩的平均成绩avg（），显示字段为：学科、平均分，请问SQL语句如何编写：select subject,avg(score) from info group by subject;

问题5：查询出所有学生的各科成绩，显示字段为：姓名、学号、学科、成绩，并以学号与学科排序，没有成绩的学生也需要列出，SQL怎么编写：select a.name,a.code,b.subject,b.score from student a,info b where a.code=b.code order by name,subject;

问题6：查询出单科成绩最高的，显示字段为：姓名、学号、学科、成绩，SQL语句如何编写：select a.name,a.code,b.subject,max(b.score) from student a,info b group by subject;

问题7：列出每位学生的各科成绩，要求输出格式：姓名、学号、语文成绩、数学成绩、英语成绩，SQL怎么写：select student.name,student.code,sum(if(info.subject="语文",info.score,0)) 语文成绩,sum(if(info.subject="数学",info.score,0)) 数学成绩,sum(if(info.subject="英语",info.score,0)) 英语成绩 from student,info where student student.code=info.code group by student.name;

在abc库中创建一个新表：create table worker(salay int(10),id int(3));

将student表和worker表进行全连接：select * from student union all select * from  worker；

全连接: union或union all

1.两张表的数据数量相同

2.全连接内使用order by 没有效果，可以对连接后的结果进行排序；

3.union会合并相同的数据；

授权与撤销授权

授权：grant 权限[all,insert,delete,update,select] on 库.表 to '用户'@'地址' identified by '密码';

撤销权限：revoke 权限[all,insert,delete,update,select] on 库.表 from '用户'@'地址' ;

查看用户授权：show grants for '用户'@'地址';

数据类型

数字--> tinyint（一个字节） smallint（两个字节） mediumint（三个字节） int（四个字节） bigint（八个字节）float（浮点数，也就是小数）double  bit  zerofill

auto_increment：自增

字符串  char  varchar（可变长字符串）

时间    datetime（年月日时分秒） date（年月日）  time（时分秒） year（年） now()  length() year() lpad(str,n,pad)

生成随机数（0-1范围）：rand（）

if(值,t,f)

ifnull(值1,值2)

case when [值1] then [结果] ... else [默认值] end

MySQL密码

忘记mysql密码如何解决

修改MySQL的root密码:

1.关闭MySQL ：systemctl stop mysqld

2.执行语句，安全登录：mysqld_safe --skip-grant-table &

3.免密登录数据库后，使用update语句修改密码

update mysql.user set password=password（“密码”）where=”root”;

杀死MySQL进程:kill -9 MySQL的pid（通过ps -aux查询得到）

重新启动

MySQL的命令选项：mysql  -u  指定用户  -p密码 -h 指定主机地址 -P 端口号  -e  SQL语句

例：mysql -uroot -p123123 -e ”use mysql;select user from user;”

同理，在mysql中也可以执行系统命令，例：system ls

设置字符编码，在mysql配置文件/etc/my.cnf中[mysqld]下添加character_set_server=utf8

MySQL内输入：show variables like “character%”;查看字符编码。

索引（相当于目录，可以快速找到数据）

运维人员一般用不上，属于开发要了解的

索引的分类:

普通索引：    index

唯一性索引：unique

主键：          primary key

全文索引：   fulltext

创建索引

创建表时，创建索引：create table 表名（字段 数据类型（长度），.....，index 索引名称（字段名[length]））；

例：create table test(id int(3),name varchar(10),index idx_id(id));

查看索引：show index from test\G;

创建表后，创建索引(两种方法）

（1）create index 索引名 on 表名（字段[length]）;

（2）alter table 表名 add index 索引名称（字段[length]）;

例：alter table info add index idx_code(code); （索引名称规范idx_索引的字段名）此处不写长度

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

前缀索引：在创建索引时使用length指定使用该字段数据的前几个字符做索引，通常用于数据比较大时；

删除索引

删除索引drop index 索引名称 on 表名；

例：删除info表的索引idx_code：drop index idx_code on info；

主键

创建表时，添加主键：create table 表名(字段 数据类型,....,primary key(字段));

创建表后，添加主键：alter table 表名 add primary key(字段);

删除主键：alter table 表名 drop primary key;

全文索引 fulltext

主要用于字段数据类型为text（文本文件）或varchar（可边长字符串）。

索引作用

索引的作用

（1）加快查询速度

（2）降低磁盘IO成本

（3）加快表与表之间的连接

（4）减少分组排序的时间

索引的使用

对于经常更新的数据字段，不要使用索引；

表特别小的时候，不需要使用索引-->全表扫描（select语句）比使用索引查询快时，不创建索引

字段中数据唯一性比较差的，不适合创建索引

索引应该尽量建立在字段中数据比较小的，如果指定数据比较长应该创建前缀索引。

创建索引的字段尽量在where中使用。

表100万行数据，code=001 select * from where code=001；

正常查询：全表扫描

使用索引查询：name使用索引，只扫描索引列

组合索引：

假设有a b 两个字段，创建索引 where a=1 and b=1

MySQL查询时，只会使用1个索引，所以先查询a=1再查询b=1，此时应该是用组合索引（a，b）

索引失效：

条件中有or

使用Like加% 模糊查询

全表扫描比使用索引查询快

索引是表达式的一部分

字段类型是字符串，数据没有加“ ”

事务

是一种机制，其作用是保持数据一致性

MySQL的事务主要用来处理操作量大，复杂度高的数据；innodb存储引擎支持事务，MyISAM不支持事务

事务的ACID特点：原子性、一致性、隔离性、持久性

原子性：事务是一个整体，是不可分割的，事物中的所有操作要不都执行成功，要不都失败；rollback（回滚）可以撤销事务中已经执行的SQL语句；Innodb存储引擎中通过undo log实现回滚

一致性：当事务完成时，数据必须处于一致状态，在事务开始之前数据处于一致状态；当事务再进行时，数据可能处于不一致状态；当事务执行完成后，数据必须处于一致状态；

隔离性：对数据进行修改的所有并发事务都是隔离的；以防止多个食物并发执行时由于交叉执行导致数据不一致；

持久性：事务处理的结果都是永久的；

手动开启事务：begin；（开始）commit；（提交）rollback；（回滚）

查看自动提交：show variables like “autocommit”;

开启/关闭自动提交：set antocommit=1/0；

存储引擎

查看MySQL默认支持的搜索引擎：show engines\G;

主要学习innodb和myisam，默认使用存储引擎innodb,通过命令查看：show variables like “default_storage_engine”;

创建表时指定存储引擎：create table 表名(字段1 字段类型,字段2 字段类型,...) engine="存储引擎";

修改默认存储引擎：set global default_storage_engine="存储引擎";

Innodb 支持事务 行级锁 64TB；

MyISAM不支持事务 表级锁 256TB；

MyISAM 存储引擎表存储为三个文件：

.frm  存储表格式

.MYD  存储数据

.MYI  存储索引

MyISAM 以读为主，不频繁更新的表，对数据一致性要求不高

Innodb 以写为主，对数据一致性要求比较高

MySQL日志（重点）

MySQL中包含6种日志：错误日志、查询日志、慢查询日志、二进制日志、中继日志、元数据日志

错误日志：和MySQL启动相关的信息，mariadb默认路径/var/log/mariadb/mariadb.log

二进制日志（重点）：记录跟修改相关的SQL语句，例如updata insert delect create drop alter

二进制日志有三种格式：

语句

行

混合

将log-bin=mysql-bin写入MySQL配置文件中，默认位置在/etc/my.cnf 开启二进制日志，并指定日志文件名

查看二进制日志文件：ls /var/lib/mysql/  标黑处

查看二进制文件内容：mysqlbinlog mysql-bin.000001

MySQL内查看二进制状态：show master status；

MySQL查看所有二进制日志：show master logs；

MySQL内清除二进制日志：reset master；

删除指定二进制日志：purge master logs to “mysql-bin.000001”;

删除指定二进制日志之前：purge master logs before “日期 时间”;

查看二进制日志过期时间：show variables like “expire_logs_days”;

设置二进制日志过期时间：set global expire_logs_days=3;

查看是否开启二进制日志：show variables like "log_bin";

查看二进制日志的缓存大小：show variables  like "binlog_cache_size";

查看二进制日志格式：show variables like "binlog_format";

查询日志与慢查询日志

查询日志：记录客户端所有的语句，包括查询语句（通常不开启）

开启/关闭查询日志：set global general_log=1/0

慢查询日志：记录执行时间较长，超过long_query_time设定的值并且扫描数据行数不小于min_examined_row_limit的SQL语句。

show variable like “long%”;默认单位是秒，可以精确到微秒

show variables like “%row_limit”;

开启/关闭 慢查询日志：set global slow_query_log=1/0

设置慢查询日志时间：set long_query_time=数值（默认单位是秒）

配置文件/etc/my.cnf中的mysqld段指定 slow_query_log_file=路径文件名

数据库备份

备份策略：全备份、增量备份、差异备份

热备份 ：数据库在运行过程中进行备份，不需要对数据库额外操作温备份 ：对表进行锁定，表中数据不可以变化，此时数据库还在运行过程中的备份  冷备份 ：停止数据库之后进行备份

Mysql备份方法1（导出）：select * from 表名 into outfile "路径文件名";例：备份表student到根下的test：select * from student into outfile “/var/lib/mysql/student.txt”;

注意：保存文件的目录一定有mysql用户权限

导入表：load data infile “路径文件名”into table 表名;

将表导出为Excel文件：test：select * from student into outfile “/var/lib/mysql/student.csv”;

安装lrzsz：yum -y install lrzsz

sz student.csv传到桌面上

MySQL自带的备份工具 mysqldump

mysqldump选项

-u 指定用户

-P指定密码

-p指定端口

-h指定主机地址

在linux命令行，备份数据库abc，导出sql文件，格式：mysqldump -u用户 -p密码 库名 > 文件名.sql

例：mysqldump abc > abc.sql

在linux命令行，备份abc库中的表，同样也是导出sql文件：mysqldump -u用户 -p密码 库名 表名 > 文件名.sql

例：mysqldump abc student > student.sql

备份表结构

恢复数据库,在数据库中:source /路径/备份文件.sql

恢复在数据库外:cat 备份文件.sql |mysql -u用户 -p密码 库名               mysql -u用户 -p密码 库名 < 备份文件.sql

mysqldumper 多线程并发执行备份，为每一个表创建一个备份文件

基于二进制节点位置进行数据恢复

mysqlbinlog --start-position=起始节点 --stop-position=结束节点 二进制名称|mysql -uroot -p123123

基于二进制日志时间进行数据恢复

mysqlbinlog --start-datetime="起始时间" --stop-datetime="终止时间" 二进制日志名称|mysql -uroot -p123123

MySQL客户端工具

mysql

mysqladmin 管理工具 mysqladmin -uroot password 123123

mysqldump 数据导出工具

mysqlpump 并行导出数据工具（5.7版本之后才有）

mysqlcheck 表维护工具

mysqlimport 数据导入工具 mysqlimport -uroot -p密码 库名 备份文件名

MySQL5.7二进制包安装

yum安装lrzsz，然后上传MySQL二进制包：yum -y install lrzsz

解包到usr下的src：tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/src

为了更好实现版本控制，我们为解包好的文件创建个软连接：ln -s /usr/src/mysql-5.7.22-linux-glibc2.12-x86_64/ /usr/local/mysql

创建MySQL程序用户：useradd -M -s /sbin/nologin mysql

进入MySQL目录：cd /usr/local/mysql

做一个环境变量：echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

执行source命令使其立即生效：source /etc/profile

初始化MySQL配置：mysqld --user=mysql --initialize --datadir=/usr/local/mysql/data

记录密码：TaKm#R5lb8ms（每个生成的不同）

将mysql启动文件复制到/etc/init.d下：cp support-files/mysql.server /etc/init.d/mysqld

修改cnf配置文件，默认应该在support-files中有my_default.cnf文件，如果没有，自己上传

如果启动不成功，大概率可能是，本机已经安装过mysql或者mariadb，有残留文件，yum -y remove卸载一下

启动MySQL：/etc/init.d/mysqld start

使用刚刚生成的密码登录：mysql -uroot -pTaKm#R5lb8ms

登录后首先修改初始化密码：set password=password（“123123”）;

安装成功

附:MySQL配置文件my.cnf

由于在7.18开始，二进制包不再包含示例文件my-default.cnf，所以我从5.7.17版本中提取了样例，但是发现里面也没有太多项配置，my-default.cnf内容如下：

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html# *** DO NOT EDIT THIS FILE. It‘s a template which will be copied to the# *** default location during install, and will be replaced if you# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

主从复制（数据库热备份）

主从复制主要通过二进制日志实现，在主库上开启二进制日志（修改相关的SQL语句），主库将二进制日志发送到从库，并写入从库的中继日志中，从库根据中继日志的内容进行重做（重新执行一遍），从而达到主从数据一致。

部署两台MySQL主机：一台名为mysql-master，另一台为mysql-slave。上传mysql5.7二进制包，并将其解压移动到usr/local下，命名为mysql5.7

主从复制的优势:

1.如果主库出现问题，可以切换到从库上提供服务；

2.从库提供读操作，可以减轻主库I/O压力；

3.可以在从库上做数据备份，避免备份期间影响主库的性能；

MySQL主从复制默认是异步复制；5.5版本开始支持半同步复制；

二进制日志格式:语句 行 混合；7.6之前默认是语句模式复制；5.7.7之后默认是行模式复制；

Mysql复制架构: 一主一从；一主多从；双主复制；级联复制；同源复制(多主一从)；

首先，主库开启二进制日志（配置文件中）：log-bin=mysql-bin

主库更改配置文件中的server id=10，从库等于11

主库需要对从库进行授权，在主库中执行SQL语句：grant replication slave on *.* to "repl"@"%" identified by "123123";

主库执行flush logs；再确定二进制日志文件：show master stautus；

从库指向主库，在从库中执行SQL语句：change master to master_user='repl',master_password='123123',master_host='192.168.1.100',master_log_file='mysql-bin.000004',master_log_pos=154;

开启从库，在从库执行SQL语句：start slave；

查看从库状态：show slave status\G；

上图IO和SQ显示yes，则主从复制就成功了。

删除二进制日志，首先在从库停掉slave：stop slave；然后reset slave；就重置主从复制配置了。

一台从库运行了一个月，增加一台从库，如何操作？

在主库上锁表，只可以读：flush tables with read lock；

全量备份,在从库上导入；开始配置主从；

主库解锁: unlock tables;

多源复制

Innodb MyISAM

sync_binlog -->将binlog写入磁盘

=1时  表示开启

=0时  表示关闭，mysql不控制二进制日志写入磁盘，由操作系统控制

=N  延迟binlog写入磁盘

MySQL多线程复制

保证从库并发应用relay log能力，2核32核的CPU 128G内存，硬盘HDD TPS（每秒事务处理能力：3万次）

slave_paralel_type 指定并发复制的类型

首先开启主从复制，然后在从库上指定多少个工作线程：set global slave_parallel_workers=4;

从库设置类型，此步设置前请stop slave；然后执行：set global master_info_repository=“table”;

从库执行设置：set global relay_log_info_repository=“table”;

查看表结构：show creat 表名；如果搜索引擎不是InnoDB或MyISAM，可以修改：alter table slave_master_info engine="InnoDB/MyISAM ";

开启从库：start slave；

半同步复制

主库安装插件

install plugin rpl_semi_sync_master soname "semisync_master.so";

set global rpl_semi_sync_master_enabled=1;

从库安装插件

install plugin rpl_semi_sync_slave soname "semisync_slave.so";

set global rpl_semi_sync_slave_enabled=1;

stop slave io_thread;

start slave io_thread;

show variables like "%semi_sync%"; 查看半同步复制的值

show status like "rpl%"; 查看半同步复制的相关信息

基于GTID复制

GTID=source_id:事务id

show variables like "gtid_mode";  查看gtid状态

set global gtid_mode=ON_PERMISSIVE;

select * from mysql.gtid_executed；

show master status;

gtid_executed  保存的是上一个binlog日志中GTID

reset slave; 清除从库配置后，配置信息保存在内存中，使用show slave status;还可以查看到信息；

reset slave all;

清除从库配置后，使用show slave status; 查不到信息；

change master to master_user="repl",master_password="123123",master_host="192.168.2.10",master_auto_position=1;

不允许主从库临时开始gtid

在配置文件/etc/my.cnf中[mysqld]加入

gtid_mode=ON

enforce_gtid_consistency=ON

GTID复制可以在主从恢复时，自动根据GTID进行恢复；

读写分离-->缓解主库IO压力,避免从库写数据

基于代码:

基于代理中间件:

mysql-proxy -->Atlas

mycat

MySQL数据库压测与性能

MySQL数据库性能指标：

吞吐量：TPS/QPS

补充说明：磁盘 IOPS-->每秒IO请求数

磁盘吞吐量：每次IO操作产生的流量

show status like "%Questions%";

QPS：mysqladmin -uroot -p123123 -h 127.0.0.1 -i 1 -r  extended|grep -i questions

响应时间：

1.例如现有一个订单业务，要求5秒内响应

2.当有50并发时，TPM为2000事务，95%

3.100并发时，TPM为3500，55%

并发性

同时500在线，WEB 100并发请求，数据库DB 50并发请求。

查看最大连接数，默认值为151：show variables like "%max_connection%";

附件： MySQL.docx 1.06MB 下载次数：0次

CentOS MySQL SQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。