第十章 创建计算字段
将表中不同列中的数据字段通过拼接、加减乘除运算计算出结果并单独显示出来。
10.2拼接字段
将不同字段的值连接在一起并构成单个值。使用函数concat（），如果要将此值独立出来需要使用AS来进行别名定义。
mysql> select concat(user_name,':',password) from user;
+-----------------------------------------+
| concat(user_name,':',password) |
+-----------------------------------------+
| system: |
| admin:286c39c4042493717b7e916eab48eab6 |
| master:cfbf20d7d12069ed501c10cc47f6b788 |
| dtedu:261a07079db097c15150d9a07ba066f8 |
+-----------------------------------------+
4 rows in set (0.00 sec)
将计算字段进行别名设置，使其可以独立出来便于调用。
mysql> select concat(user_name,'(',password,')') as user_passwd from user;
+------------------------------------------+
| user_passwd |
+------------------------------------------+
| system() |
| admin(286c39c4042493717b7e916eab48eab6) |
| master(cfbf20d7d12069ed501c10cc47f6b788) |
| dtedu(261a07079db097c15150d9a07ba066f8) |
+------------------------------------------+
4 rows in set (0.00 sec)
如果字段中包含空格，可以删除指定字段中的空格，再进行计算。RTrim：删除右边空格，LTrim：删除左边空格，Trim：删除左右两边空格。
mysql> select concat (rtrim(user_name),'(',ltrim(password),')',trim(status)) as new_user from user;
+------------------------------------------------+
| new_user |
+------------------------------------------------+
| system()disabled |
| admin(286c39c4042493717b7e916eab48eab6)active |
| master(cfbf20d7d12069ed501c10cc47f6b788)active |
| dtedu(261a07079db097c15150d9a07ba066f8)active |
+------------------------------------------------+
4 rows in set (0.00 sec)
select同样提供了用于测试计算语句是否正确的方法。
mysql> select 4+5
-> ;
+-----+
| 4+5 |
+-----+
| 9 |
+-----+
1 row in set (0.00 sec)
mysql> select rtrim( gong );
ERROR 1054 (42S22): Unknown column 'gong' in 'field list'
mysql> select rtrim(' gong ');
+--------------------+
| rtrim(' gong ') |
+--------------------+
| gong |
+--------------------+
1 row in set (0.00 sec)
算术计算字段之间的数值。常用的有加减乘除。
mysql> select user_name+password as new from user;
+-----+
| new |
+-----+
| 0 |
| 286 |
| 0 |
| 261 |
+-----+
4 rows in set, 7 warnings (0.00 sec)
第十一章 使用数据处理函数
函数可以处理很多复杂的计算问题，但是函数的可移植性差，不同DBMS之间的函数差异很大，所以建议在编写脚本的时候对函数的功能进行注释说明。
函数类型：
1、文本处理函数
用于处理文本内容的删除、编辑、转换大小写等功能。
常用的文本处理函数
| 函数 |
说明 |
| left（）right（） | 返回串左边的字符 |
| length（） | 返回串的长度 |
| locate（） | 找出串的一个子串 |
| lower（）upper() | 将串转化为小写 |
| sounded（） | 返回串的soundex值 |
| substring（） | 返回子串的字符 |
| Trim、LTrim、RTrim() | 删除串旁边的空格 |
2、算术处理函数
处理数值数据的算术操作，比如返回绝对值、代数运算等功能。
3、处理日期和时间的函数
对日期或者时间进行特定内容的提取。
常用日期处理函数
| 函数 | 说明 |
| adddate() | 增加一个日期 |
| additive() | 增加一个时间 |
| curate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| date() | 返回日期时间的日期部分 |
| datediff() | 计算两个日期的差 |
| date_add() date_format（） |
高度灵活的日期运算函数 返回一个格式化的日期或时间串 |
| day() | 返回一个日期的天数部分 |
| day0fweek() | 返回星期几 |
| hour（） | 返回小时部分 |
| minute（） | 返回分钟部分 |
| now() | 返回当前时间和日期 |
| second() | 返回秒数 |
| time() | 返回一个时间部分 |
| year() | 返回年份部分 |
数值处理函数
| 函数 |
说明 |
| abs() |
返回一个数的绝对值 |
| cos() |
返回一个角的余弦 |
| exp() |
返回一个数的指数值 |
| mod() |
返回操作数的余数 |
| pi() |
返回圆周率 |
| rand() | 返回一个 随机数 |
| sin() |
返回一个角的正弦 |
| sqrt() |
返回一个数的平方根 |
| tan() |
返回一个角度的正切 |
4、DBMS系统信息提取函数。
第十二章 汇总数据
应用场景：
1、确定表中的行数，或者满足某一个条件的特定行数。
2、获得表中行组的和。
3、找出表列的最大值、最小值、平均值。
12.1聚集函数
| 函数 | 说明 |
| avg() | 返回某列的平均值 |
| count() | 返回某列的行数 |
| max() | 返回某列的最大值 |
| min() | 返回某列的最小值 |
| sum() | 返回某列值的和 |
count()函数的两种用法：
1、count（*）：对表中行的数目进行计数（不管表列中是否有空值）
2、count（column）对特定的列中具有值的行进行计数，忽略null值。
第十三章分组数据
应用场合：常规检索方式为全部表内容、通过where来指定具体值、范围的方法。而分组容许把数据分为多个逻辑组，以便能够对每个组进行聚集计算（对行求平均、最大、最小）
比如：mysql> select vend_id, count(*) as num_prods from products group by vend_id;
二、group by
重要规定：
1、group by子句可以包括任意多个列，实现嵌套功能。
mysql> select user_name,count(*) from user group by status;
+-----------+----------+
| user_name | count(*) |
+-----------+----------+
| gongbing | 6 |
| system | 1 |
+-----------+----------+
2 rows in set (0.00 sec)
2、实现嵌套后，数据会在规定的最后一个分组实现汇总。
mysql> select user_name,status from user group by status,user_name;
3、group by子句中列出的可以是表达式、检索列，但不能是聚集函数，也不能使用别名。
4、null在分组中单独存在。
5、group by出现位置是在order by之前，where之后。
三、过滤分组having
第十四章 子查询语句
第十五章 联接表
一、什么是联接
利用sql的select查询语句将多个表的不同列连起来并输出。
关系表
依据某种关系存在的多个表，称为关系表。
外键
一个表中的主键在另一个表中出现，它定义了两个表之间的关系。
联接
就是将不同表中的列抽取出来，并通过select查询语句整理成一个完整的信息。此操作依据的就是关系型数据库中的表的关系完成的。
MySQL事务日志
1、特性
事务日志的并发性
为了实现事务日志并发性，需要对数据进行保护，保证数据不会混乱，这里就使用到了“锁”这个功能。
锁的类型：表锁、页锁、行锁，共享锁（读锁）、独占锁（写锁）。
myISAM引擎支持表锁
innoDB引擎执行行锁
根据锁的施加方式：
显示锁（通常不使用此方法，只有在备份的时候使用会非常有用。）
lock tables tbname （read|write）
设置读锁：
mysql> lock tables students read;
Query OK, 0 rows affected (0.00 sec)
解除锁：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
隐式锁
事务仅在innoDB引擎中支持，在myISAM引擎并不支持，所以要修改引擎才支持事务功能。
默认情况下，mysql将每一条语句都当成一个事务，并在语句执行后提交，可以通过修改变量“autocommit”来改变。
1、查看状态（当前为自动提交）
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
2、通常在使用innoDB的事务功能时，关闭autocommit的功能，并手动实现提交。可以提供数据库性能。
启动事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
提交事务，提交后是不可以在回滚回去。
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
回滚，就是撤销上一条操作
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
创建回滚点（类似windows的还原点），必要时恢复到还原点之前的状态。
mysql> savepoint del1;
Query OK, 0 rows affected (0.00 sec)
滚回到以前的某一个回滚点：rollback to pointname
mysql的隔离性（变量名：tx_isolation）
通常是为了事务的并发性而存在的。
隔离级别：
1级、read-uncommitted（读取未提交内容，所有事务都可以看到其他未提交事务的执行结果）
2级、read-committed（读取提交内容，一个事务只能看到已经提交的结果。大多数数据库默认级别）
3级、 repeatable-read （可重读，会产生“幻读”，mysql默认级别）
4级、serializable（可串行化）
1、隔离级别只能在会话模式下进行设置。
mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)
将mysql的隔离级别设置为readcommited可以提高数据库的并发能力。
数据库存储引擎篇
由于mysql被oracle收购，存在收费及被oracle冷处理的可能，mysql原创始人有开发了一个新的数据库maria，其实这个数据库在使用上和mysql非常相似，甚至命令很多都是一样的。
maria数据库的引擎自身集成并优化了innoDB。
存储引擎本身是属于表级别的，就是说是建立在表的基础上的。
查看表的存储引擎，实际就是查看表的状态信息。
show table status
Name: toc 名称
Engine: MyISAM 引擎
Version: 10 版本
Row_format: Fixed 行格式
Rows: 0 表中已有行数
Avg_row_length: 0 平均行长度
Data_length: 0 已经存储的数据量
Max_data_length: 2533274790395903 最大存储数据量
Index_length: 1024 索引长度
Data_free: 0 已经分配出去并使用然后又删除的存储空间
Auto_increment: 1 自动增长步长值
Create_time: 2016-07-28 15:59:16 表的创建时间
Update_time: 2016-07-28 15:59:16 上次修改记录的时间
Check_time: NULL 上次使用check命令检查表的时间
Collation: latin1_swedish_ci 排序规则
Checksum: NULL 表的校验和，默认未启用
Create_options: 创建表时使用的其他选项
Comment: 注释，对于myISAM表，存储的是创建表时的commment选项指定的信息。对于innoDB来讲，存储对应的表空间剩余的表空间信息。
早起mysql的默认存储引擎是myISAM,但是他不支持事务，所以出现了innoDB引擎，后来又出现了BDB引擎，innoDB的出现完成了mysql成为完整的关系型数据库。
innoDB:
事务：事务日志
外键：
mvcc：多版本并发控制，类似快照。
支持聚簇索引
支持辅助索引
支持自适应hash索引
支持热备：需要myql enterprise backup，percona xtrabackup
行级锁
myISAM:
全文索引
压缩
空间索引
表级锁
不支持事务和行级锁
崩溃后无法安全恢复
其他引擎介绍：
archive：
仅仅执行insert和select，支持压缩功能。
适用于存储日志信息，或其他按时间序列实现的数据采集类应用
csv：
将数据保存为.csv格式，不支持索引，适用于数据交换。
blackhole：
没有存储机制，任何数据都会被丢失，但会记录二进制日志
federated：
访问远程服务器上的数据的存储引擎：federatedx
memory：
内存存储引擎，比ISAM至少快一个数量级，适用于实现临时表。
mrg myISAM：
可以将多个myISAM表合并成一个ISAM表，实际自身并不存储数据。
第三方存储引擎：
OLTP类：
xtraDB:是innoDB的增强版
PBXT:已经不再继续有人开发维护了。
TokuDB：开源、支持图数据，分形树、执行索引结构。
列式存储引擎：
infobright：最著名的列式存储。
monetDB:
infiniDB:
lucidDB:
社区存储引擎：
aria：
groona：全文索引引擎
qqgraph：支持图，有open query研发
sphinx：
spider：支持分片
VPforMYsql：支持垂直分区
查看服务器中mysql数据库支持的引擎有哪些？
[root@localhost ~]# ll /usr/local/dtedu/mysql/lib/plugin/
总用量 292
-rwxr-xr-x. 1 mysql mysql 7734 6月 28 2013 adt_null.so
-rwxr-xr-x. 1 mysql mysql 10123 6月 28 2013 auth.so
-rwxr-xr-x. 1 mysql mysql 7420 6月 28 2013 auth_socket.so
-rwxr-xr-x. 1 mysql mysql 9545 6月 28 2013 auth_test_plugin.so
-rw-r--r--. 1 mysql mysql 227 7月 2 2012 daemon_example.ini
drwxr-xr-x. 2 mysql mysql 4096 6月 28 2013 debug
-rwxr-xr-x. 1 mysql mysql 46737 6月 28 2013 ha_example.so
-rwxr-xr-x. 1 mysql mysql 75366 6月 28 2013 ha_federated.so
-rwxr-xr-x. 1 mysql mysql 10836 6月 28 2013 libdaemon_example.so
-rwxr-xr-x. 1 mysql mysql 10163 6月 28 2013 mypluglib.so
-rwxr-xr-x. 1 mysql mysql 6663 6月 28 2013 qa_auth_client.so
-rwxr-xr-x. 1 mysql mysql 9334 6月 28 2013 qa_auth_interface.so
-rwxr-xr-x. 1 mysql mysql 7078 6月 28 2013 qa_auth_server.so
-rwxr-xr-x. 1 mysql mysql 45592 6月 28 2013 semisync_master.so
-rwxr-xr-x. 1 mysql mysql 17754 6月 28 2013 semisync_slave.so
选择数据引擎的方法和标准有哪些？
1、是否需要事务
2、备份
3、崩溃恢复
1、查看数据库支持的存储引擎有哪些？
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
日志、备份和恢复篇
日志种类
错误日志
查询日志
二进制日志
事务日志
中继日志
慢查询日志
一、错误日志
默认存放位置：--datadir 选项设置的目录，也可通过my.cnf来进行设置。其中
1、log-error
服务器启动和关闭过程中的信息
服务器运行中的错误信息
事件调度器运行一个事件时产生的信息
启动从服务器进程时产生的信息
查看详细错误日志
mysql> show global variables like '%log%';
+-----------------------------------------+---------------------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| expire_logs_days | 0 |
| general_log | OFF |是否开启查询日志功能
| general_log_file | /data/log/mysql/mysqld.log |查询日志以文件形式输出的位置
| innodb_flush_log_at_trx_commit | 1 |是否缓存中有内容就同步到磁盘上，
0:每秒同步，并执行磁盘flush操作
1：每事务同步，并执行磁盘flush操作
2：每事务同步，并不执行磁盘flush操作
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |缓存大小
| innodb_log_file_size | 5242880 |事务日志文件大小
| innodb_log_files_in_group | 2 |缓存文件个数
| innodb_log_group_home_dir | ./ |事务日志组的位置
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /data/log/mysql/mysqld.log |
| log_output | FILE |查询日志输出的=位置
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |是否开启慢查询
| slow_query_log_file | /data/log/mysql/mysqld-slow.log |定义日志位置
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sync_binlog | 0 |
+-----------------------------------------+---------------------------------+
38 rows in set (0.00 sec)
2、log-warning（0|1）
二、查询日志
默认情况下，查询日志是关闭的，为了是提高IO效率。
默认情况下，日志的输出位置是到general_log_file=指定的位置。如果是以table的形式输出的话将存放在mysql中的mysql数据库中的general_log表。
三、慢查询日志
建议开启，对性能影响很小。
参数：slow_query_log
slow_query_log_file
慢查询的时长通常是10s，long_query_time设置。
四、事务日志
事务日志的缓存空间一般有2个，当一个填满后就写入下一个，并报写满的缓存导入的磁盘上的文件中。
| general_log | OFF |是否开启查询日志功能
| general_log_file | /data/log/mysql/mysqld.log |查询日志以文件形式输出的位置
| innodb_flush_log_at_trx_commit | 1 |是否缓存中有内容就同步到磁盘上，建议设置为2
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |缓存大小
| innodb_log_file_size | 5242880 |事务日志文件大小
| innodb_log_files_in_group | 2 |缓存文件个数
五、二进制日志
用途：记录修改数据或者有可能改变数据的mysql语句。在主从mysql的数据库进行同步时，使用的同步方法也就是读取二进制日志，所以对于同步来说，二进制日志很重要。
常用参数：
log_bin 设置是否启用二进制日志，默认存储位置/data/mysql/，文件名称通常是mysql-bin.0001的形式，可以自己直接设置存储位置，注意这里的文件名不要加后缀，系统自动添加比如：/path/to/filename
binlog_cache_size 32768 二进制日志的缓存大小
binlog_direct_non_transactional_updates OFF
binlog_format MIXED 二进制格式
bin_stmt_cache_size 523121 基于语句格式的缓存大小（三种格式之一）
expire_logs_days 0 日志过期时间，0表示不过期。
sql_log_bin on 会话级别的开启、关闭二进制日志，备份时重要
sync_binlog 0 是否立即同步缓存的日志到磁盘上。
max_binlog_cache_size 13123141312 最大缓存大小
max_binlog_size 141231 日志文件的大小
max_stmt_cache_size 131314 基于语句格式的缓存大小
日志格式：
sql语句（statement）
修改行的数据内容（row）：数据量相对大，但是会比较精确。
混合格式（交替使用语句和行）mixed
查看二进制日志命令
show binary logs：显示二进制文件数量和大小
查看当前使用的二进制日志文件
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000016 | 106 （所处位置为多少字节数）| | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看二进制日志文件中记录的事件内容。
mysql> show binlog events in 'mysql-bin.000001' from 24238 limit 2;
+------------------+-------+------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-------+------------+-----------+-------------+----------------------------------------------------+
| mysql-bin.000001 | 24238 | Query | 1 | 24339 | use `mysql`; CREATE TEMPORARY TABLE tmp_db LIKE db |
| mysql-bin.000001 | 24339 | Query | 1 | 24408 | BEGIN |
+------------------+-------+------------+-----------+-------------+----------------------------------------------------+
2 rows in set (0.00 sec)
mysqlbinlog
linux自身也有有一个命令，可以查看二进制日志文件内容cd /data/mysql/ mysqlbinlog mysql-bin.00001
这个命令也可以用来恢复数据库内容，能够找回丢失的数据。
1、导出二进制日志文件
mysqlbinlog --star-position=123 -stop-position=241 mysql-bin.00003 >123.sql
2、恢复数据
mysql -u root -p testdb<123.sql
删除二进制日志文件的安全方法：
1、建议在做完备份之后删除
2、建议确定后删除
删除：
mysql> purge binary logs to 'mysql-bin.000003'; //删除000003之前的日志文件
Query OK, 0 rows affected (0.00 sec)
日志文件滚动，意义就是使用新的二进制日志文件比如现在使用的是mysql-bin.0000011，flush logs 滚动后变成mysql-bin.0000012了。
六、中继日志
主要用于同步场景。
本文转自 blackstome 51CTO博客，原文链接：http://blog.51cto.com/137783/1968778，如需转载请自行联系原作者
