mysql数据库入门、进阶和提升（续二）

2022-05-20 23:51:34

第十章创建计算字段

将表中不同列中的数据字段通过拼接、加减乘除运算计算出结果并单独显示出来。

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;

+------------+---------+------------------------------------------------------------+--------------+------+------------+

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| 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;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

查看二进制日志文件中记录的事件内容。

mysql> show binlog events in 'mysql-bin.000001' from 24238 limit 2;

+------------------+-------+------------+-----------+-------------+----------------------------------------------------+

+------------------+-------+------------+-----------+-------------+----------------------------------------------------+

| 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，如需转载请自行联系原作者

码农公寓

相关文章