一、SQL （Structured Query Language）相关的知识

1、SQL的概念：结构化查询语言，是关系数据库的标准语言，是一个通用的、功能极强的关系数据库语言。

2、几种常见的SQL模型：

ansi quotes 双引号相当于反引号只能用来引用字段名称、表名，单引号只能用来标识字符串

ignore_space 在内键函数中忽略多余的空白字符

strict_all_tables 只要违反了数据规则的都不允许填入并会返回一个错误的

strict_trans_tables 向一个支持事务的表当中插入非法数据的时候是不允许的

traditional  

3、MySQL服务器变量的分类

1）按作用域，分为两类：

全局变量（对每一个局部都生效）

show global variables

会话变量

show [session] variables

2）按生效时间，分为两类：

动态：可即时修改,能及时生效

静态：写在配置文件中或通过参数传递给mysqld

4、动态调整参数的生效方式：

全局：对当前会话无效，只对新建立会话有效；

会话：即时生效，但只对当前会话有效；

服务器变量：@@变量名

显示：select

设定：set global|session 变量名=‘value’

eg：select @@global.sql_mode;

eg: select @@session.sql_mode;

eg: set global sql_mode='strict_all_tables';

5. 程序语言连接数据的方式：

动态SQL：通过函数或方法与数据库服务器建立连接，将查询语句直接发往服务器端

嵌入式SQL：通过API直接连到服务器上，连接之前还要先编译

二、数据库查询相关的知识

1、创建和删除数据库

创建数据库用法：create database|schema [if not exists] db_name [character set=] [collate=]

删除数据库用法：drop {database | schema} [if exista] db_name

例如：连接到数据库执行以下命令

eg:mysql

>select @@global.sql_mode; 选择数据库模块

> help create database; 创建数据库

>show character set; 查看中文字符集

>show collation; 查看排序规则

>create schema if not exists students character set 'gbk' collate 'gbk_chinese_ci'; 创建一个数据库指定字符集的策略

2、表的创建有三种方式:

1)直接定义一张空表

格式：create table [if not exists] tb_name (col_name col_defination,constraint)

(col_defination最核心的是数据类型,constraint定义约束)

eg：create table tb1 (id int unsigned not null auto_increment,Name char(20) not null,Age tinyint not null)

eg:create table tb2 (id int unsignet not auto_increment,Name char(20) not null,Age tinyint not null,primary key(id),uniqce key(name),index(age)) unique把name定义成惟一键,把年龄定义为索引

2)从其它表中查询出数据，并以之创建新表

格式：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

3)以其它表为模板创建一个新表；

格式：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name{ LIKE old_tbl_name | (LIKE old_tbl_name) }

3、表的选项：

engine [=] engin_name 指定表的存储引擎,若不指定直接从数据库中那里直接继承

auto_increment [=] value 自动增长从哪个值开始

avg_row_length [=] value 平均行的长度

[default] character set [=] charset_name 字符集

checksum [=] {0|1} 是否启用它的校验和

[default] collate [=] collation_name 排序规则

comment [=] 'string' 表的注释信息

data directory [=] 'absolute path to directory' 数据目录

delay key_write [=] {0|1} 是否延迟键写入

index directory [=] 'absolute path to directory' 索引目录

insert_method 插入方法

key_block_size [=] value 键块的大小

max_rows [=] value 最多允许存储多少行

min rows [=] value 最少允许存储多少行

pack_keys [=] {0|1|default} 

password [=] 'string' 密码

row_format [=] {default|dynamic|fixed|compressed|redundant|compact} 行格式

tablespace tabespace_name [storage {disk|memory|default}] 表空间

union [=] (tbl_name[,tbl_name]...)

4）修改表定义：

alter table

添加、删除、修改字段

添加、删除、修改索引

改表名

修改表属性

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name 修改表指定表名

DROP [COLUMN] col_name 删除字段

DROP {INDEX|KEY} index_name 删除索引

RENAME [TO|AS] new_tbl_name 重命名表名

MAX_ROWS = rows 修改表的最大行

DISCARD TABLESPACE 是否禁用所有索引

IMPORT TABLESPACE 是否启用所有索引

5、常见表的查询

1）表的查询类型分为

a) 简单查询：select select-list from tb where qualification

b) 多表查询：

多表查询的连接类型：

<1> 交叉连接：笛卡尔乘积(指定表)

<2>自然连接：where tb1.field=tab2.filed （将两张表上相同字段的两个值逐一作比较，只将那些等值关系保留下来）

<3> 外连接：

左外连接（写在前面的表）from tb1 left join tb2 on condition

右外连接 from tb1 right join tb2 on condition

自连接 （自连接时必须需要别名）

select * from tb_name; 选择显示所有表中的所有行（*是通配符，表示对应表中的所有字段）

select filed1,filed2 from tb_name;投影 （只挑选有效的字段）

select [distinct] * from tb_name where qualification;选择

(distinct表示独有的，相同的值只显示一次)

select后面跟的是from(from子句：要查询的关系)， from后面跟的是表、多个表、其它select语句

c) 子查询（嵌套查询）：

比较操作符中进行子查询：子查询只能返回一个字段的单个值；

IN():列表(某字段的多个值)

在from中使用子查询；

2)where子句逻辑关系：

and 与

or 或

no 非

xor 异或

between ... and ...

3)常用的一些查询命令

like ''

%:任意长度的任意字符

_:任意单个字符

regexp ,rlike 作比较的时候支持正则表达式

eg:select name from student where name rlike '^[XY].*$';

in 在...内

is null 判断是否为空

is not null 判断是否不空

ORDER BY field_name,...{ASC|DESC} 排序:asc升序，desc降序

给字段取别名：select Name AS Student_Name FROM students;(字段别名使用：as)

limit子句：limit [offset,]count limit表示后的offset,表示略过多少个，count表示取多少个

聚合计算：sum(), min(),max(), avg(),平均 count()个数之和

eg: select avg(age) from students; 平均年龄

group by :分组，主要目的做聚合计算

group by filed1,...

having 只能跟group by一起用，用来将group by的结果再次进行过滤

eg：select avg(age) gender from students group by Gender;

having qualification：过滤

三、事务和日志

1、事务的特性：

1）原子性[Automaticty]: 事务所引起的数据库操作，要么都完成，要么都不执行；

2）一致性[Consistency]: 从一个状态转换为另一个状态，这个状态是平稳转换的;

3）隔离性[Isolation]: 把事务隔离开，彼此间不干扰

隔离级别：

read uncommitted：读未提交

read committed：读提交

repatable read:可重读

seriablizable:可串行

4）持久性[Durability]:一旦失误成功完成，系统必须保证任何故障都不会引起事务表示出不一致性；

a) 事务提交之前就已经写出数据至持久性存储

b) 结合事务日志完成

事务日志：顺序IO

数据文件：随机IO

2、事务的状态：

活动的：active

部分提交的：最后一条语句执行后

失败的：没执行完成的

中止的：不执行的

提交的：已完成并提交（事务一旦提交就无法撤销）

补充：

事务并发执行的好处：提高吞吐量和资源利用率、减少等待时间。

事务调度的分类：可恢复调度、无级联调度。

并发控制依赖的技术手段：锁、时间戳、多版本和快照隔离

(锁：读锁（共享锁）、写锁（独占锁、排查锁）

锁粒度：从大到小，Mysql服务器只支持表级锁，行锁需要有存储引擎完成；）

3、日志类型有六类：(日志中记录的是操作过程，不是数据)

1）错误日志:主要用于记录服务运行当中的错误信息，服务器启动或关闭过程中显示的信息还可记录警告信息，从服务器上启动中继服务进程管理到的信息，还有事件调度器产生的信息，还可记录警告信息

2）一般查询日志：

（任何一个查询语句所产生的日志信息都要记录下来，一般查询日志通常是不开启的）

general_log

general_log_file

log

log_output

3）慢查询日志：查询日志的查询评估是根据它从开始启动这个查询一直到查询执行结束这个时间，而不是其真正在CPU上执行的时间，无论是一般查询日志还是慢查询日志它们都可以记录到文件中

log_output {table|file|none} 定义日志信息输出到什么位置(table和file可同时指定一般用逗号隔开)

long_query_time

log_slow_queries={YES|NO}

slow_query_log

slow_query_log_file

4）二进制日志：任何引起或可能引起数据库变化的操作；

复制、即时点恢复；

mysqlbinlog

重放

二进制日志的格式（事件）

基于语句：statement

基于行：row

混合方式：mixed

（二进制日志自己是会做滚动的，默认情况下存放至数据目录中，一般建议一定不要跟数据放在同一个存储设备上，因为每一次存储设备的IO操作[数据的写操作]都会引起二进制日志的写操作，两者都会产生IO，也就意味着在同一块磁盘上两者会产生竞争的，所以本身就会带来性能低下的状况，而且为了避免由于数据文件损坏也导致二进制日志损坏，所以就尤其的建议应该分开存储，基于性能和数据可靠性的原因）

二进制日志时间：

产生的时间

相对位置

二进制日志文件

索引文件

二进制日志文件

查看当前正在使用的二进制日志文件

show master status; 查看当前这段时间的日志

show binary logs; 查看当前系统上正在使用的二进制日志

show binlog events in '二进制文件名' [from position]; 查看当前日志中的某个文件

purge binary logs to'日志文件'; 实现某个文件备份之后清除某个日志之前的日志

flush logs; 实现二进制日志的滚动

mysqlbinlog 显示详细查看输出二进制文件内容的日志

--start-position 指定起始位置

--stop-position 指定结束位置

--start-datetime 'yyyy-mm-dd hh:mm:ss' 以时间为上下限指定起始位置

--stop-datetime ''

可以将输出的信息一并的保存至文本文件中，导入到数据库里面可以实现恢复数据，因为它里面记录的都是具体的操作

5）中继日志：

从主服务器的二进制日志文件中复制而来的事件，并保存为的日志文件

6）事务日志：ACID，将随机IO转换为顺序IO；

事务性存储引擎用于保证原子性、一致性、隔离性和持久性；

（>show global variables like '%log%';执行此命令可显示以下内容）

log_flush_log_at_trx_commit;

0：每秒同步，并执行磁盘flush操作；

1：每事务同步，并执行磁盘flush操作；

2：每事务同步，但不执行磁盘flush操作；

innodb_log_buffer_size：内存缓存大小

binlog_stmt_cache_size 二进制语句缓存的大小

sql_log_bin={on|off} 用于控制二进制日志信息是否记录进日志文件

innodb_log_group_home_dir 日志组放在当前目录

innodb_mirrored_log_groups 是否对文件组做镜像，若做的话要指定位置

expire_logs_days 日志的过期天数，若后面的数字是30也就意味着30天后里面的二进制日志将被清除

补充：mysqld产生的错误日志

1）服务器启动和关闭过程中的信息

2）服务器运行过程中的错误信息

3）时间调度器运行一个事件产生的错误信息

4）在从服务器上启动从服务器进程时产生的信息

四、MySQL的备份和还原

1、备份：将数据存放一个副本，提供一个副本在出现任何可能性故障的时候能够将数据还原回来的这么一个副本。（若把数据保存在RAID1、RAID10上也需要备份，因为RAID1、RAID10主要作用：保证硬件损坏而不会业务中止，一定程度上也保证数据的稳定性；）

备份用法：

select * from outfile '/path/to/somefile.txt' from tb_name [where clause];

还原用法：

load data infile '/path/to/somefile.tct' into table tb_name;

2、备份类型：

1） 热备份、温备份和冷备份（hot,warm,cold）

热备份：读、写不受影响；在线备份

温备份：仅可以执行读操作；

冷备份：离线备份；读、写操作均中止

2）物理备份和逻辑备份

物理备份：复制数据文件

逻辑备份：将数据导出至文本文件中；

3） 完全备份、增量备份和差异备份：

完全备份：备份全部数据 （full）

增量备份：仅备份上次完全备份或增量备份以后变化的数据；（incremental）

差异备份：仅备份上次完全备份以来变化的数据 （differential）

（在线：物理完全备份，它们之间完全可以自由的进行组合）

3、备份什么：数据、配置文件、二进制日志(做即时点还原)、事务日志(物理备份要备份事务日志)

4、MySQL备份工具：

1）mysqldump:逻辑备份工具、MyISAM(温备份)、InnoDB（热备份）

mysqlhotcopy：物理备份工具、温备份

MyISAM引擎：可以使用几乎热备，但要借助于逻辑卷进行备份，使用快照进行备份；最好的方式是温备份，以共享方式锁定MyISAM中的所有表。

InnoDB引擎：xtrabackup(可以实现热备，而且是物理备份),mysqldump(可以实现对InnoDB进行热备份)

MySQL -->从服务器：（把mysql在线服务做一个从服务器，需要备份的时候把从服务器停下来，备份好后再重新启动起来，它会自动从上一次提供服务器的那里停用的那一刻再同步）

2）备份的特点：

a)物理备份：速度快（可直接复制数据文件）

b)逻辑备份：速度慢、丢失浮点数精度；方便使用文本工具直接对其处理、可移植能力强；

1)浮点数据丢失精度

2)备份出的数据更占用存储空间；压缩后可大大节省空间

3)不适合对大数据库做完全备份

使用逻辑备份的前提：

1）数据文件要在逻辑卷上；

2）此逻辑卷所在卷组必须有足够空间使用快照卷

3）数据文件和事务日志要在同一个逻辑卷上

步骤：

1>打开会话，施加读锁，锁定所有表

mysql> flush tables with read lock;

mysql> flush logs;

2>通过另一个终端，保存二进制日志文件及相关位置信息

mysql -uroot -p -e 'show master status\G' > /path/to/master.info

3>创建快照卷

lvcreate -L # -s -p -r -n LV_NAME /path/to/source_lv

4>释放锁

mysql > unlock tables;

5>挂载快照卷，备份

mount

cp

6>删除快照卷

7>增量备份二进制日志

补充：

备份策略：完全+增量；完全+差异（完全备份是物理方式还是逻辑方式是取决你的需要，增量方式是以物理方式还是逻辑方式进行也取决你的需要）

完全备份：mysqldump 

增量备份：备份二进制日志文件

5、文件系统工具（第二组工具）：

1)cp:冷备

2)lv:逻辑卷的快照功能，可以实现几乎热备；

mysql> flush tables;刷新表

mysql> lock tables;锁表

然后创建快照：释放锁，而后复制数据

（对MyISAM引擎来讲刷新表并锁表过程很简单，也没有什么难题；但对InnoDB引擎来说，刷新表锁表后，这并不意味着它背后没有写操作，因为有些事务可能仍然在日志当中，它从日志文件中正在同步到数据文件中，此外已经提交的数据可能仍在内存当中，从内存中往日志文件中提交，所以刷新表锁表后不能立即创建快照，还要监控着InnoDB的存储引擎确保它的缓存驱动中的内容都已经同步到磁盘上才行，这可能需要大量的表）

3)mysqldump：逻辑备份

mysqldump（完全备份）+ 二进制日志

完全+增量

mysqldump支持所有引擎，温备份，备份速度中等，还原速度最慢，还原时的控制能力是最具有弹性的。mysqldump对InnoDB引擎来讲，热备，备份速度中等，还原速度最慢，还原时的控制能力是最具有弹性的select intc outfile:只能够对应每张表中的某些个对应级,保存的时候对应某些文件级，但是它并不某些表结构。

4)mk-parallel-backup:这是一个perl脚本，依赖于很多perl模块进行备份，速度非常快，但服务器本身必须是多CPU或多核才有意义。

5)ibbackup:商业工具，因为它是物理备份，所以速度非常快，对MyISAM引擎温备，对InnoDB温备

6)mysqlhotcopy：MyISAM引擎，几乎是冷备，速度较快，还原也比较快

6、备份单个数据库，或库中的特定表

mysqldump DB_NAME [tb1] [tb2] 备份指定数据库

flush tables with read lock; 刷新所有表并执行读锁

lock tables;

show binary lock;

--master-data={0|1|2}

0: 不记录二进制日志文件及路位置；

1：以change master to 的方式记录位置，可用于恢复后直接启动从服务器

2: 以change master to 的方式记录位置，但默认为被注释；

--lock-all-tables:锁定所有表（备份之前锁定所有表）

--flush-logs:执行日志flush；（锁完表执行日志滚动）

如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备；

备份多个库：

--all-databases:备份所有库

--databases DB_NAME,DB_NAME,...:备份指定库

--events 备份事件调度器

--routines 备份存储过程和存储函数

--triggers 备份触发器