sql_mode

sql_mode是一组mysql的语法校验规则，定义了mysql应该支持的sql语法、数据校验等。

查看SQL_MODE

• select @@sql_mode

配置SQL_MODE

• 临时设置
  • SET GLOBAL sql_mode='修改后的值'
  • 需要注意应当先查出来原值，然后在其基础上进行参数的移除或添加。
• 永久设置
  • 在配置文件中添加sql_mode='参数'后保存，然后重启数据库即可。

only_full_group_by参数

• 该参数规定，在带有GROUP BY子句的SELECT语句中，SELECT后跟着的展示列(非主键)，要么是用于GROUP BY分组的列，要么需要使用函数将其包裹。

视图

视图(view) 是一个虚拟表，非真实存放数据的表，视图表中的数据是根据视图定义的DQL语句来动态获取的。

视图相当于别名，将长的命令封装成一个视图，用户使用时只需使用视图名称查询，即可获取DQL语句的查询结果集，并可以将其当作表来使用。

定义视图

• CREATE VIEW [视图名] AS [查询语句];
• 例如：CREATE VIEW had_username AS SELECT username FROM user WHERE username IS NOT NULL;

查询视图

• 查询视图的方法和查询普通表一样。
• SELECT * FROM [视图表名];

元数据

除了数据行之外的数据，都是元数据(属性)，也就是数据库的整体信息。例如数据库中有什么库、有什么表、库的属性、表的属性、记录行数、表的大小、表的引擎等。

元数据存储在"基表"中，需要通过专用的DDL语句、DCL语句进行修改，查询也需要通过专用视图来进行元数据的查询。

查询方法

1. 通过SELECT语句查询information_schema库，该表提供了大量的元数据查询的视图。

2. 通过SHOW语句查询，SHOW语句的功能是封装好的，提供元数据的基础查询功能。

information_schema查询方法

• information_schema库存放了大量数据库元数据的视图，我们可以通过查询该库的表，来获取数据库的元数据信息，查询该库实际就和查询普通库一样，没有什么太大区别。
  • use information_schema; - 进入information_schema库。
  • show tables; - 查看information_schema的视图功能。
  • desc [视图名]; - 查看视图的表信息。
• 常用元数据表：tables、innodb_locks、innodb_trx、innodb_lock_waits、innodb_buffer_pool_stats

tables视图的应用

• tables列字段

  • table_schema - 表所在的库名
  • table_name - 表名
  • engine - 存储引擎
  • table_rows - 数据行
  • avg_row_length - 平均行长度
  • index_length - 索引长度

• 使用例子：

  # 查询所有的库和表的信息
  select table_schema,group_concat(table_name)
  from information_schema.tables
  group by table_schema;
  
  # 查询所以innodb引擎的表
  select table_name,engine
  from information_schema.tables
  where engine='innodb';
  
  # 统计world库city表的占用大小,单位Bytes,所以如果要MB需要除两次1024
  # 平均行长度 * 行数 + 索引长度
  select table_name,(avg_row_length*table_rows+index_length)/1024
  from information_schema.tables
  where table_schema='world' and table_name='city';
  
  # 统计world库总大小
  select table_schema,
  sum(table_rows*avg_row_length+index_length)/1024
  from information_schema.tables
  where table_schema='world';
  
  # 显示所以库大小并降序排序
  select table_schema,
  sum(table_rows*avg_row_length+index_length)/1024 as totalsize
  from information_schema.tables
  group by table_schema
  order by totalsize desc;
  

SHOW语句查询方法

库

• show databases; - 查看所有数据库名
• show create database [数据库名]; - 查看建库语句

表

• show tables; - 在库中查看所有表名
• show create table [表名]; - 在库中查看建表语句
• show index form [表]; - 查看表的索引信息

属性

• show charset; - 查看所有支持的字符集
• show collation; - 查看所有支持的校对规则
• show engines; - 查看所有支持的存储引擎类型
• show engine innodb status\G; - 查看InnoDB引擎详细状态信息

二进制

• show binary logs; - 查看二进制日志的列表信息
• show binlog events in '[事件]'; - 查看二进制日志的事件信息
• show master status; - 查看MySQL当前使用的二进制日志信息

从库

• show slave status\G; - 查看从库状态信息
• show relaylog events in '[事件]'; - 查看回放日志的事件信息

用户

• show processlist; - 查看所有用户连 接情况
• show grants for [用户]; - 查看用户的权限信息

系统

• show variables like '%xx%'; - 查看变量参数信息
• show status like '状态变量'; - 查看数据库整体状态信息