Mysql相关操作与命令

￡神魔★判官ぃ 2024-04-19 11:02 89阅读 0赞

### 文章目录 ###

*   *   *  引用
         *  APT
         *  初始化与启动
         *   *  开机设置
             *  启动与停止
         *  库操作
         *   *  用户相关
             *  环境相关：
             *   *  防火墙-linux
             *  创建数据库
             *  登录同时进入数据库
             *  查看版本和时间
             *  退出和取消命令
             *  查看表结构
             *  切换结束符 DELIMITER
             *  通配与正则
             *   *  通配
                 *  正则匹配
                 *  正则大小写
                 *  正则匹配多个
             *  增删改以及常用语法
             *   *  Insert
                 *  Delete
                 *  Update
                 *  Alert Table 更新表
                 *  Create Index 创建索引
                 *  Create procedure 创建储存过程
                 *  create table 创建表
                 *  create user 添加用户
                 *  create view 创建视图
                 *  drop 永久删除数据库对象：表、视图、索引等
                 *  insert select 插入select的结果到一个表
                 *  distinct 去重
             *  事务
             *   *   *  commit 提交事务
                     *  rollback 撤销事务处理块
                     *  savepoint 为rollback语句设立保留点
                     *  start transaction 设置新事务处理快的开始点
         *  函数与查询
         *   *  查看当前连接
             *  最大连接数
             *  显示当前用户的连接信息
             *  显示连接状态
         *  常用文本处理函数
         *   *   *  拼接 Concant()
                 *  去空格 Trim()
                 *  别名 As
             *  常用文本处理函数
             *   *  Upper() Lower() 转换大小写函数
                 *   *  Left() Right()返回串左边和返回串右边指定位数的字符
                     *  Length() 返回串长度
                     *  Locate() 返回串的子串第一次出现的位置
                     *  SubString() 返回子串的字符(截取子串)
                     *  SOUNDEX() 返回串的SOUNDEX值
                     *  SOUNDEX() 发音相近匹配
         *  日期处理函数
         *   *  当前日期
             *  获取日期偏移
             *   *   *  当前时间
                     *  比较日期(计算日期差值)
                     *  给指定的日期增加指定的天数
                     *  向OrderDate增加2天
                     *  高精度增加时间
                     *  使用指定格式格式化日期
                     *  根据日期筛选
                     *  更可靠的根据日期筛选
                     *  筛选一个日期范围内的结果
                     *  更可靠的日期范围筛选
                     *  返回0-1之间不包括1的随机数
                 *  数学函数
         *  聚集函数 汇总数据
         *   *   *  回指定列的平均值
                 *  AVG() 返回指定列符合条件的行的平均值
                 *  count(\*) 计算指定表的行数(包括null值行)
                 *  count(col) 计算指定列的行数(不包括null值行)
                 *  MAX() 返回指定列的最大值（忽略null值）
                 *  MIN() 返回指定列的最小值（忽略null值）
                 *  SUM() 对指定列求合
                 *  SUM(A\*B) 在求全函数中计算
                 *  去重（列出不同的值）- 在函数中使用DISTINCT
                 *  使用多个函数
         *  分组数据： Group by 和 Having
         *   *   *  Group By 分组
                 *  过滤分组Having 分组的where
                 *  分组和排序 GROUP BY 和 ORDER BY
             *  SELECT子句的顺序
         *  子查询
         *   *   *  查询使用关键字 IN
                 *  计算字段中使用子查询
         *  联结Join
         *   *  笛卡尔乘积
             *  高级联结
             *  外联 （左联右联)
             *  带函数的联结
         *  组合查询 并 union
         *   *  全文本搜索
         *  视图
         *   *  创建：
             *  查询修改删除
             *  视图结构查看修改与删除视图等
         *  储存过程
         *   *  创建储存过程
             *   *  创建
                 *  检查
                 *  调用
                 *  删除
                 *  变量
         *  游标
         *  触发器
         *  事务处理
         *  数据库维护
         *  优化、改善性能
         *   *  配置

### 引用 ###

[Mysql必知必会-下载][Mysql_-]  
[Mysql必知必会-简书][Mysql_- 1]

### APT ###

[mysql 5.51 API][]

### 初始化与启动 ###

#### 开机设置 ####

cp support-files/mysql.server /etc/init.d/mysql
    chmod +x /etc/init.d/mysql
    #把mysql注册为开机启动的服务
    chkconfig --add mysql 
     
    #查看是否添加成功
    chkconfig --list mysql

#### 启动与停止 ####

Mysql 服务
    根据安装方式使用
    
    yum安装mysql使用：
    service mysqld start 或者/etc/init.d/mysqld start
    service mysqld restart
    service mysqld stop
    
    或者
    systemctl start  mysqld.service
    systemctl stop  mysqld.service
    systemctl restart  mysqld.service
    systemctl status mysqld.service

设置mysql的开机启动

chkconfig --add mysqld
    chkconfig mysqld on
    
    systemctl enable mysqld
    systemctl daemon-reload

### 库操作 ###

#### 用户相关 ####

远程用户

GRANT 【权限内容】 ON 【库名】.【表名】 TO 【用户名】@'【IP地址】' IDENTIFIED BY '【密码】' WITH GRANT OPTION;
    
    GRANT ALL PRIVILEGES ON *.*  TO ghost@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON *.*  TO qulv@'%' IDENTIFIED BY 'Qulv789.' WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON *.*  TO php@'%' IDENTIFIED BY 'Qulv789.' WITH GRANT OPTION;
    GRANT ALL PRIVILEGES ON *.*  TO php@'%' IDENTIFIED BY 'Qulv789.' WITH GRANT OPTION;

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>权限</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>all privileges</td> 
   <td>增删改查所有权限</td> 
  </tr> 
  <tr> 
   <td>insert</td> 
   <td>增</td> 
  </tr> 
  <tr> 
   <td>delete</td> 
   <td>删</td> 
  </tr> 
  <tr> 
   <td>update</td> 
   <td>改</td> 
  </tr> 
  <tr> 
   <td>select</td> 
   <td>查</td> 
  </tr> 
 </tbody> 
</table>

使命令立刻执行

FLUSH PRIVILEGES;

查询数据库用户：

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

#### 环境相关： ####

##### 防火墙-linux #####

1:查看防火状态
    systemctl status firewalld
    service  iptables status
    
    2:暂时关闭防火墙
    systemctl stop firewalld
    service  iptables stop
    
    3:永久关闭防火墙
    systemctl disable firewalld
    chkconfig iptables off
    
    4:重启防火墙
    systemctl enable firewalld
    service iptables restart  
    
    5:永久关闭后重启//暂时还没有试过chkconfig iptables on
    
    #开启8011端口 
    /sbin/iptables -I INPUT -p tcp --dport 8011 -j ACCEPT 
    #保存配置 
    /etc/rc.d/init.d/iptables save 
    #重启服务 
    /etc/rc.d/init.d/iptables restart 
    #查看端口是否已经开放
    /etc/init.d/iptables status 
    
    1) 重启后生效 
    开启： chkconfig iptables on 
    关闭： chkconfig iptables off 
    2) 即时生效，重启后失效 
    开启： service iptables start 
    关闭： service iptables stop

#### 创建数据库 ####

CREATE DATABASE `ultrax` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci

#### 登录同时进入数据库 ####

> \>mysql -h host -u user -p databasesname 
    > Enter password: *******

#### 查看版本和时间 ####

`mysql > select version(),current_date; select now();`

#### 退出和取消命令 ####

mysql> \c
    mysql> quit

#### 查看表结构 ####

mysql> describe table;
    mysql> show columns from table;
    mysql> desc table;

#### 切换结束符 DELIMITER ####

（主要创建储存过程使用）

DELIMITER $$
    或
    DELIMITER //
    
    mysql> delimiter $$
    mysql> delimiter ;

#### 通配与正则 ####

##### 通配 #####

# 通配符 % 在like之后： '%anvil%'
    mysql> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
    
    mysql> SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

##### 正则匹配 #####

# 在子句中使用正则匹配 REGEXP
    mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

##### 正则大小写 #####

# 区分大小写
    mysql> SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'Jet' ORDER BY prod_name;

##### 正则匹配多个 #####

# 匹配两个串(类似OR的功能)
    mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
    
    # 匹配一组字符
    mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

#### 增删改以及常用语法 ####

##### Insert #####

mysql> insert into table ([column],[column],[column],[column]) values ([value],[value],[value],[value],);
    或者：
    mysql> insert into table set col_name = value ,
    
    insert...select 用于插入从其它表中选出的行
    
    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name [(col_name,...)]    VALUES ({expr | DEFAULT},...),(...),...    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
    或：
    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name    SET col_name={expr | DEFAULT}, ...    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
    或：
    INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name [(col_name,...)]    SELECT ...    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
    
    
    # 一次插入多行数据  (多行数用括号包裹，逗号分隔)
    # 此技术可以提高数据库处理的性能,因为MySQL用单条 INSERT 语句处理多个插入比使用多条 INSERT语句快
    
    mysql> INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country )
    VALUES
        ( 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA' ),
        ( 'M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA' );

##### Delete #####

mysql> insert into table values ( [column],[column],[column],[colume]);
    
    mysql> delete 
    
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition]    [ORDER BY ...]    [LIMIT row_count]
    
    多表语法：
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]  tbl_name[.*] [, tbl_name[.*] ...]    
    FROM table_references   [WHERE where_definition]
    或：
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]  FROM tbl_name[.*] [, tbl_name[.*] ...]    USING table_references    [WHERE where_definition]
    
    注释：当引用表名称时，您必须使用别名（如果已给定）：
    DELETE t1 FROM test AS t1, test2 WHERE ...
    进行多表删除时支持跨数据库删除，但是在此情况下，您在引用表时不能使用别名。
    举例说明：DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...
    目前，您不能从一个表中删除，同时又在子查询中从同一个表中选择

##### Update #####

– 更新和删除的指导原则  
– 前一节中使用的 UPDATE 和 DELETE 语句全都具有 WHERE 子句，这样做的  
– 理由很充分。如果省略了 WHERE 子句，则 UPDATE 或 DELETE 将被应用到表中  
– 所有的行。换句话说，如果执行 UPDATE 而不带 WHERE 子句，则表中每个行  
– 都将用新值更新。类似地，如果执行 DELETE 语句而不带 WHERE 子句，表的  
– 所有数据都将被删除。  
– 下面是许多SQL程序员使用 UPDATE 或 DELETE 时所遵循的习惯。  
–  除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE  
– 子句的 UPDATE 或 DELETE 语句。  
–  保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能  
– 像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。  
–  在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进  
– 行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不  
– 正确。  
–  使用强制实施引用完整性的数据库（关于这个内容，请参阅第15  
– 章），这样MySQL将不允许删除具有与其他表相关联的数据的行  
– 不要省略 WHERE 子句 在使用 UPDATE 时一定要注意细心, 因为稍不注意，就会更新表中所有行.

# 更新符合筛选条件的行中的指定列
    mysql> UPDATE customers SET cust_email = 'elemer@fudd.com' WHERE cust_id = 10005;
    
    # 更新多列
    mysql> UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
    
    # 更新多行时, 使用IGNORE忽略错误, 继续更新下去
    mysql> UPDATE IGNORE customers SET cust_email = 'elemer1@fudd.com' WHERE cust_id = '10005';
    
    
    # 可以用来删除指定列(可以为NULL的情况)
    mysql> UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

##### Alert Table 更新表 #####

##### Create Index 创建索引 #####

##### Create procedure 创建储存过程 #####

##### create table 创建表 #####

##### create user 添加用户 #####

##### create view 创建视图 #####

##### drop 永久删除数据库对象：表、视图、索引等 #####

##### insert select 插入select的结果到一个表 #####

##### distinct 去重 #####

> select distinct job from emp;

#### 事务 ####

###### commit 提交事务 ######

###### rollback 撤销事务处理块 ######

###### savepoint 为rollback语句设立保留点 ######

###### start transaction 设置新事务处理快的开始点 ######

mysql>

### 函数与查询 ###

#### 查看当前连接 ####

mysql> show status like ‘Threads%’;

<table> 
 <thead> 
  <tr> 
   <th>Variable_name</th> 
   <th>Value</th> 
   <th>info</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Threads_cached</td> 
   <td>58</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>Threads_connected</td> 
   <td>57</td> 
   <td>###这个数值指的是打开的连接数</td> 
  </tr> 
  <tr> 
   <td>Threads_created</td> 
   <td>3676</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>Threads_running</td> 
   <td>4</td> 
   <td>###这个数值指的是激活的连接数，这个数值一般远低于connected数值</td> 
  </tr> 
 </tbody> 
</table>

Threads\_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads\_running是代表当前并发数

#### 最大连接数 ####

查询数据库当前设置的最大连接数

mysql> show variables like ‘%max\_connections%’;

#### 显示当前用户的连接信息 ####

#### 显示连接状态 ####

mysql> SHOW STATUS LIKE ‘%connect%’;

### 常用文本处理函数 ###

##### 拼接 Concant() #####

##### 去空格 Trim() #####

Rtrim() 右边空格
    LTrim() 左边空格
    Trim() 两端空格
    
    mysql> select concat(RTrim(vend_name),'(',RTrim(vend_country),')') from vendors order by vend_name;
    +------------------------------------------------------+
    | concat(RTrim(vend_name),'(',RTrim(vend_country),')') |
    +------------------------------------------------------+
    | ACME(USA)                                            |
    | Anvils R Us(USA)                                     |
    | Furball Inc.(USA)                                    |
    | Jet Set(England)                                     |
    | Jouets Et Ours(France)                               |
    | LT Supplies(USA)                                     |
    +------------------------------------------------------+
    6 rows in set (0.00 sec)
    
    mysql> select concat(RTrim(vend_name),'(',RTrim(vend_country),')') as title from vendors order by vend_name;
    +------------------------+
    | title                  |
    +------------------------+
    | ACME(USA)              |
    | Anvils R Us(USA)       |
    | Furball Inc.(USA)      |
    | Jet Set(England)       |
    | Jouets Et Ours(France) |
    | LT Supplies(USA)       |
    +------------------------+
    6 rows in set (0.00 sec)

##### 别名 As #####

其它查询可以直接使用别名代替计算、查询等操作

mysql> select concat(sno, ' - ',sname) as title from student order by sno desc;
    
    mysql> select prod_id, quantity, item_price, quantity*item_price AS expanded_price from orderitems where order_num = 20005;

#### 常用文本处理函数 ####

##### Upper() Lower() 转换大小写函数 #####

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
    SELECT vend_name, LOWER(vend_name) AS vend_name_lower FROM vendors ORDER BY vend_name;

###### Left() Right()返回串左边和返回串右边指定位数的字符 ######

SELECT vend_name, LEFT(vend_name, 5) AS vend_name_left FROM vendors ORDER BY vend_name;
    SELECT vend_name, RIGHT(vend_name, 5) AS vend_name_right FROM vendors ORDER BY vend_name;

###### Length() 返回串长度 ######

SELECT vend_name, LENGTH(vend_name) AS vend_name_length FROM vendors ORDER BY vend_name;

###### Locate() 返回串的子串第一次出现的位置 ######

SELECT vend_name, LOCATE('A', vend_name) AS vend_name_locate FROM vendors ORDER BY vend_name;

###### SubString() 返回子串的字符(截取子串) ######

SELECT vend_name, SUBSTRING(vend_name, 1, 5) AS vend_name_locate FROM vendors ORDER BY vend_name;

###### SOUNDEX() 返回串的SOUNDEX值 ######

SELECT vend_name, SOUNDEX(vend_name) AS vend_name_locate FROM vendors ORDER BY vend_name;

###### SOUNDEX() 发音相近匹配 ######

SELECT cust_name, cust_contact FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Y. Lie');

### 日期处理函数 ###

#### 当前日期 ####

> SELECT CURDATE();  
> CURDATE() CURRENT\_DATE()  
> CURRENT\_DATE和CURRENT\_DATE()是CURDATE()的同义词。

mysql> SELECT CURRENT_DATE(), CURRENT_DATE, CURDATE();
    +----------------+--------------+------------+
    | CURRENT_DATE() | CURRENT_DATE | CURDATE() |
    +----------------+--------------+------------+
    | 2017-08-10   | 2017-08-10  | 2017-08-10 |
    +----------------+--------------+------------+
    1 row in set

NOW() 返回当前日期时间

mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2019-09-10 17:26:26 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT DATE(NOW());
    +-------------+
    | DATE(NOW()) |
    +-------------+
    | 2017-08-10 |
    +-------------+
    1 row in set

#### 获取日期偏移 ####

date\_sub()

mysql> select date_sub(DATE(now()),interval 1 day) ;
    +--------------------------------------+
    | date_sub(DATE(now()),interval 1 day) |
    +--------------------------------------+
    | 2019-09-09                           |
    +--------------------------------------+
    1 row in set (0.00 sec)

当前时2019-09-17  
表示90天前：

mysql> select date_sub(DATE(now()), interval 90 day);
    2019-06-19
    date_sub(curdate(),interval 1 day) 表示 2019-06-19
    date_sub(curdate(),interval -1 day) 表示 2019-09-18
    date_sub(curdate(),interval 1 month) 表示 2019-10-17
    date_sub(curdate(),interval -1 month) 表示 2019-8-17
    date_sub(curdate(),interval 1 year) 表示 2018-09-17
    date_sub(curdate(),interval -1 year) 表示 2020-09-17
    或
    date_sub('2019-06-19',interval 1 day) 表示 2019-06-19

TO\_DAYS()

1、利用to_days函数查询今天的数据：
    select * from 表名 where to_days(时间字段名) = to_days(now());
    to_days函数：返回从0000年（公元1年）至当前日期的总天数。
    2、昨天
    SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) <= 1
    3.7天
    SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
    4.近30天
    SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
    5.本月
    SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m' ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m' )
    6.上一月
    SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m' ) , date_format( 时间字段名, ‘%Y%m' ) ) =1
    #查询本季度数据
    select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
    #查询上季度数据
    select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
    #查询本年数据
    select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
    #查询上年数据
    select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

###### 当前时间 ######

SELECT CURTIME();

###### 比较日期(计算日期差值) ######

SELECT DATEDIFF(‘2018-10-10’, “2018-10-11”);

###### 给指定的日期增加指定的天数 ######

> SELECT ADDDATE(NOW(), 10);

###### 向OrderDate增加2天 ######

> SELECT OrderId,DATEADD(day,2,OrderDate) AS OrderPayDate FROM Orders

###### 高精度增加时间 ######

> SELECT DATE\_ADD(NOW(), INTERVAL 60 SECOND)

###### 使用指定格式格式化日期 ######

> SELECT DATE\_FORMAT(NOW(),’%Y-%m-%d %H:%i:%S’);  
> SELECT DATE(‘2018-10-10 12:12’); \# 截取日期部分  
> SELECT TIME(‘2018-10-10 12:12:12’); \#返回日期的时间部分  
> SELECT YEAR(‘2018-10-10 12:12:12’); \#返回日期的年份部分  
> SELECT MONTH(‘2018-10-10 12:12:12’); \#返回日期的月份部分  
> SELECT DAY(‘2018-10-10 12:12:12’); \#返回日期的天数部分  
> SELECT HOUR(‘2018-10-10 12:12:12’); \#返回日期的时针部分  
> SELECT MINUTE(‘2018-10-10 12:12:12’); \#返回日期的分针部分  
> SELECT SECOND(‘2018-10-10 12:12:12’); \#返回日期的秒部分

###### 根据日期筛选 ######

> SELECT cust\_id, order\_num FROM orders WHERE order\_date = ‘2005-09-01’;

###### 更可靠的根据日期筛选 ######

> SELECT cust\_id, order\_num FROM orders WHERE DATE(order\_date) = ‘2005-09-01’;

###### 筛选一个日期范围内的结果 ######

> SELECT cust\_id, order\_num FROM orders WHERE DATE(order\_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’;

<select id="queryEndDateLtByCompanyId" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"/>    
    from company where stop_date <= date_sub(DATE(now()), interval -5 day) and stop_date >= date_sub(DATE(now()), interval 15 day) and company_id = #{companyId,jdbcType=INTEGER}
    </select>

###### 更可靠的日期范围筛选 ######

> SELECT cust\_id, order\_num FROM orders WHERE Year(order\_date) = 2005 AND Month(order\_date) = 9;

###### 返回0-1之间不包括1的随机数 ######

> SELECT RAND();

##### 数学函数 #####

Abs()       返回一个数绝对值
    Exp()       指数值
    Mod()     除操作的余数
    Pi()          圆周率
    Rand()    随机数
    Sqrt()      平方根
    Sin()       角度的正弦
    Cos()      角度的余弦
    Tan()      角度的正切

### 聚集函数 汇总数据 ###

##### 回指定列的平均值 #####

SELECT AVG(prod_price) AS avg_price FROM products;

##### AVG() 返回指定列符合条件的行的平均值 #####

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

##### count(\*) 计算指定表的行数(包括null值行) #####

SELECT COUNT(*) AS num_cust FROM customers;

##### count(col) 计算指定列的行数(不包括null值行) #####

SELECT COUNT(cust_email) AS num_cust FROM customers;

##### MAX() 返回指定列的最大值（忽略null值） #####

SELECT MAX(prod_price) AS max_price FROM products;

##### MIN() 返回指定列的最小值（忽略null值） #####

SELECT MIN(prod_price) AS min_price FROM products;

##### SUM() 对指定列求合 #####

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

##### SUM(A\*B) 在求全函数中计算 #####

SELECT SUM(quantity\*item_price) AS total_price FROM orderitems WHERE order_num = 20005;

##### 去重（列出不同的值）- 在函数中使用DISTINCT #####

SELECT DISTINCT 列名称 FROM 表名称
    
    SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

##### 使用多个函数 #####

SELECT
        COUNT( * ) AS num_items,
        MIN( prod_price ) AS price_min,
        MAX( prod_price ) AS price_max,
        AVG( prod_price ) AS price_avg 
    FROM
        products;

### 分组数据： Group by 和 Having ###

##### Group By 分组 #####

> – 在具体使用 GROUP BY 子句前，需要知道一些重要的规定。  
> – \* GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套，  
> – 为数据分组提供更细致的控制。  
> – \* 如果在 GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上  
> – 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算  
> – （所以不能从个别的列取回数据）。  
> – \* GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式  
> – （但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在  
> – GROUP BY 子句中指定相同的表达式。不能使用别名。  
> – \* 除聚集计算语句外， SELECT 语句中的每个列都必须在 GROUP BY 子  
> – 句中给出。  
> – \* 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列  
> – 中有多行 NULL 值，它们将分为一组。  
> – \* GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前

mysql> select vend_id,count(*) as num_prods from products;
    +---------+-----------+
    | vend_id | num_prods |
    +---------+-----------+
    |    1001 |        14 |
    +---------+-----------+
    1 row in set (0.00 sec)
    
    # 分组显示每个id下有多少产品
    mysql> select vend_id,count(*) as num_prods from products group by vend_id;
    +---------+-----------+
    | vend_id | num_prods |
    +---------+-----------+
    |    1001 |         3 |
    |    1002 |         2 |
    |    1003 |         7 |
    |    1005 |         2 |
    +---------+-----------+
    4 rows in set (0.00 sec)

# 对分组汇总
    SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

##### 过滤分组Having 分组的where #####

*  where过滤行，Having过滤分组。Where在分组前过滤，Having在分组后过滤

# 使用HAVING过滤分组
    mysql> SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
    +---------+--------+
    | cust_id | orders |
    +---------+--------+
    |   10001 |      2 |
    +---------+--------+
    1 row in set (0.00 sec)
    
    
    # 查询出具有2个或以上，价格为10或以上产品的供应商
    mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
    +---------+-----------+
    | vend_id | num_prods |
    +---------+-----------+
    |    1003 |         4 |
    |    1005 |         2 |
    +---------+-----------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;

##### 分组和排序 GROUP BY 和 ORDER BY #####

<table> 
 <thead> 
  <tr> 
   <th>Order By</th> 
   <th>Group By</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>排序产生的输出</td> 
   <td>分组行。但输出不一定是分组的排序</td> 
  </tr> 
  <tr> 
   <td>任意列可使用</td> 
   <td>只能使用选择列或者表达式列，而且必须使用每个选择列表达式</td> 
  </tr> 
  <tr> 
   <td>非必须</td> 
   <td>如果与聚集函数（比如统计conut、计算等）则必须使用</td> 
  </tr> 
 </tbody> 
</table>

# GROUP BY 和 ORDER BY 
    mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;
    +-----------+------------+
    | order_num | ordertotal |
    +-----------+------------+
    |     20006 |      55.00 |
    |     20008 |     125.00 |
    |     20005 |     149.87 |
    |     20007 |    1000.00 |
    +-----------+------------+
    4 rows in set (0.00 sec)

#### SELECT子句的顺序 ####

**SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT;**

### 子查询 ###

##### 查询使用关键字 IN #####

# 列出订购物品 TNT2 的所有客户
    -- 方式一：分条查询
    
    mysql> SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
    +-----------+
    | order_num |
    +-----------+
    |     20005 |
    |     20007 |
    +-----------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);
    +---------+
    | cust_id |
    +---------+
    |   10001 |
    |   10004 |
    +---------+
    2 rows in set (0.00 sec)
    
    -- 方法二：子查询
    SELECT cust_name, cust_contact FROM customers WHERE cust_id IN ( SELECT cust_id FROM orders WHERE    order_num IN (  SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
    
    mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (
        ->         SELECT cust_id FROM orders WHERE    order_num IN (
        ->         SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
    +----------------+--------------+
    | cust_name      | cust_contact |
    +----------------+--------------+
    | Coyote Inc.    | Y Lee        |
    | Yosemite Place | Y Sam        |
    +----------------+--------------+
    2 rows in set (0.00 sec)

##### 计算字段中使用子查询 #####

# 计算字段中使用子查询
    SELECT
        cust_name,
        cust_state,
        ( SELECT COUNT( * ) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders 
    FROM
        customers 
    ORDER BY
        cust_name;

### 联结Join ###

# 联结查询（等值联结果或者内部连接）
    SELECT
        vend_name,
        prod_name,
        prod_price 
    FROM
        vendors,
        products 
    WHERE
        vendors.vend_id = products.vend_id 
    ORDER BY
        vend_name,
        prod_name;
        
    # 内部连接的另一种更明确的写法
    SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
    
    
    # 同时连接多个表
    SELECT
        prod_name,
        vend_name,
        prod_price,
        quantity 
    FROM
        orderitems,
        products,
        vendors 
    WHERE
        products.vend_id = vendors.vend_id 
        AND orderitems.prod_id = products.prod_id 
        AND order_num = 20005;

#### 笛卡尔乘积 ####

笛卡尔积即是查询结果的相乘，即两个集合X和Y的乘积，表示为X\*Y。  
第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 。  
通俗点讲就是检索出的数目行数可能是第一个表的行数乘以第二个表中的行数。

#### 高级联结 ####

左联右联 内联外联  
inner join  
left join  
right join

# 在连接查询中使用别名
    SELECT
        cust_name,
        cust_contact 
    FROM
        customers AS c,
        orders AS o,
        orderitems AS oi 
    WHERE
        c.cust_id = o.cust_id 
        AND oi.order_num = o.order_num 
        AND prod_id = 'TNT2';
    
    # 自连接
    SELECT p1.prod_id, p1.prod_name
    FROM products AS p1, products AS p2
    WHERE p1.vend_id = p2.vend_id
    AND p2.prod_id = 'DTNTR';

> 使用自联结而不是子查询，有时候处理紫联结比使用子查询快的多，应该尝试两种方法那种性能更好

#### 外联 （左联右联) ####

> LEFT OUTER JOIN 简写 left join  
> 如果是左联，则保留左表所有的行，即使在右表中没有匹配的行。  
> 右联同理

# 外部连接(左外和右外)
    
    SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
    +---------+-----------+
    | cust_id | order_num |
    +---------+-----------+
    |   10001 |     20005 |
    |   10001 |     20009 |
    |   10002 |      NULL |
    |   10003 |     20006 |
    |   10004 |     20007 |
    |   10005 |     20008 |
    +---------+-----------+
    6 rows in set (0.00 sec)
    
    SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON orders.cust_id = customers.cust_id;
    +---------+-----------+
    | cust_id | order_num |
    +---------+-----------+
    |   10001 |     20005 |
    |   10001 |     20009 |
    |   10003 |     20006 |
    |   10004 |     20007 |
    |   10005 |     20008 |
    +---------+-----------+
    5 rows in set (0.00 sec)

#### 带函数的联结 ####

# 带聚集函数的联结
    SELECT
        customers.cust_name,
        customers.cust_id,
        COUNT(orders.order_num) AS num_ord 
    FROM
        customers
        INNER JOIN orders ON customers.cust_id = orders.cust_id 
    GROUP BY
        customers.cust_id;
    
    +----------------+---------+---------+
    | cust_name      | cust_id | num_ord |
    +----------------+---------+---------+
    | Coyote Inc.    |   10001 |       2 |
    | Wascals        |   10003 |       1 |
    | Yosemite Place |   10004 |       1 |
    | E Fudd         |   10005 |       1 |
    +----------------+---------+---------+
    4 rows in set (0.00 sec)
    
    
    SELECT
        customers.cust_name,
        customers.cust_id,
        COUNT( orders.order_num ) AS num_ord 
    FROM
        customers
        LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id 
    GROUP BY
        customers.cust_id;
    
    +----------------+---------+---------+
    | cust_name      | cust_id | num_ord |
    +----------------+---------+---------+
    | Coyote Inc.    |   10001 |       2 |
    | Mouse House    |   10002 |       0 |
    | Wascals        |   10003 |       1 |
    | Yosemite Place |   10004 |       1 |
    | E Fudd         |   10005 |       1 |
    +----------------+---------+---------+
    5 rows in set (0.00 sec)

### 组合查询 并 union ###

# 使用UNION组合多条语句查询
    SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
    UNION
    SElECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
    
    +---------+---------+------------+
    | vend_id | prod_id | prod_price |
    +---------+---------+------------+
    |    1003 | FC      |       2.50 |
    |    1002 | FU1     |       3.42 |
    |    1003 | SLING   |       4.49 |
    |    1003 | TNT1    |       2.50 |
    |    1001 | ANV01   |       5.99 |
    |    1001 | ANV02   |       9.99 |
    |    1001 | ANV03   |      14.99 |
    |    1002 | OL1     |       8.99 |
    +---------+---------+------------+
    8 rows in set (0.00 sec)
    
    # 不自动取消重复行
    SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
    UNION ALL
    SElECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
    
    +---------+---------+------------+
    | vend_id | prod_id | prod_price |
    +---------+---------+------------+
    |    1003 | FC      |       2.50 |
    |    1002 | FU1     |       3.42 |
    |    1003 | SLING   |       4.49 |
    |    1003 | TNT1    |       2.50 |
    |    1001 | ANV01   |       5.99 |
    |    1001 | ANV02   |       9.99 |
    |    1001 | ANV03   |      14.99 |
    |    1002 | OL1     |       8.99 |
    +---------+---------+------------+
    8 rows in set (0.00 sec)
    
    # 只能在最后一条SQL后写排序
    SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
    UNION ALL
    SElECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
    ORDER BY vend_id, prod_price;

#### 全文本搜索 ####

### 视图 ###

*  数据库中的虚拟表，修改等同于修改实际表。同时修改实际表的时候视图也会自动修改
 *  简单化，安全控制权限在视图内，逻辑数据独立性
 *  可单表或者多表建立视图

#### 创建： ####

create vuew vuew\_name (column\_list, colume\_list, …)  
as select select\_column\_list, select\_column\_list …  
from table\_name, table\_name  
where …

CREATE VIEW user_company_view ( user_id, company_id, user_name, real_name, position, start_date, stop_date, version ) AS SELECT USER
    .user_id,
    USER.company_id,
    USER.user_name,
    USER.real_name,
    USER.position,
    company.start_date,
    company.stop_date,
    company.version 
    FROM
    	USER,
    	company 
    WHERE
    	USER.company_id = company.company_id

#### 查询修改删除 ####

同正常使用  
select update delete

#### 视图结构查看修改与删除视图等 ####

查看基本信息 describe view\_name  
等同 desc view\_name  
或 show table status like ‘view\_name’  
`mysql> show table status like ''user_company_view`  
或在视图表中查看：  
`select * from information_schema.views`

修改：  
修改和创建时同一条语句，如果当前视图已存在则修改，不存在则创建  
create or update view view\_name as sleect \* from name;

CREATE  [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW  view_name  [(column_list)]
    AS SELECT_statement
    [WITH [CASCADED \ LOCAL] CHECK OPTION]

或者 使用alert  
alert view view\_name as select column\_name from table\_name \[or view\_name\]

删除  
drop view if exists view\_name

### 储存过程 ###

> 单独编写每条语句，并根据结果有条件的执行另外的语句。即类似脚本、批处理文件  
> 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

简单、安全、高性能

Sql中的存储过程及相关介绍：
    CREATE PROCEDURE [拥有者.]存储过程名[;程序编号]
    [(参数#1,…参数#1024)]
    [WITH
    {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
    ]
    [FOR REPLICATION]
    AS 程序行
    其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数

#### 创建储存过程 ####

*  由于储存过程中sql语句需要使用分号作为结束符，所以需要使用delimiter设置自定义结束符。创建完储存过程后后再使用delimiter ;重设结束符为分号；

##### 创建 #####

CREATE 
        [DEFINER = { user | CURRENT_USER }] 　
    PROCEDURE sp_name ([proc_parameter[,...]]) 
        [characteristic ...] routine_body 
    proc_parameter: 
        [ IN | OUT | INOUT ] param_name type 
    characteristic: 
        COMMENT 'string' 
        | LANGUAGE SQL 
        | [NOT] DETERMINISTIC 
        | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
        | SQL SECURITY { DEFINER | INVOKER } 
    routine_body: 　　
        Valid SQL routine statement 
        
    [begin_label:] BEGIN 　　
        [statement_list] 　　　　
        …… 
    END [end_label]
    
    # P:
    CREATE PROCEDURE demo_in_parameter(IN p_in int)

**MYSQL 存储过程中的关键语法**  
声明语句结束符，可以自定义:

> DELIMITER $$  
> 或  
> DELIMITER //

声明存储过程:

> CREATE PROCEDURE demo\_in\_parameter(IN p\_in int)

存储过程开始和结束符号:

> BEGIN … END

变量赋值:

> SET @p\_in=1

变量定义:

> DECLARE l\_int int unsigned default 4000000;

创建mysql存储过程、存储函数:

> create procedure name()

存储过程名(参数)存储过程体:

> create function 存储函数名(参数)

##### 检查 #####

> show cretate procedure name()

##### 调用 #####

> call productpricing()

##### 删除 #####

##### 变量 #####

mysql> delimiter //
    mysql> create procedure productpricing()
        -> begin
        -> select avg(prod_price) as priceaverage
        -> from products;
        -> end //
        
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter ;
    mysql> call productpricing()
        -> ;
    +--------------+
    | priceaverage |
    +--------------+
    |    16.133571 |
    +--------------+
    1 row in set (0.01 sec)
    
    Query OK, 0 rows affected (0.02 sec)

> CALL productpricing(@pricelow, @pricehigh, @priceaverage);

### 游标 ###

### 触发器 ###

### 事务处理 ###

### 数据库维护 ###

### 优化、改善性能 ###

#### 配置 ####

[client]
    port = 3306
    socket = /tmp/mysql.sock
     
    [mysqld]
     
    ###############################基础设置#####################################
     
    #Mysql服务的唯一编号 每个mysql服务Id需唯一
    server-id = 1
     
    #服务端口号 默认3306
    port = 3306
     
    #mysql安装根目录
    basedir = /usr/local/mysql
     
    #mysql数据文件所在位置
    datadir = /usr/local/mysql/data
     
    #临时目录 比如load data infile会用到
    tmpdir  = /tmp
     
    #设置socke文件所在目录
    socket  = /tmp/mysql.sock
     
    #主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
    skip-external-locking
     
    #只能用IP地址检查客户端的登录，不用主机名
    skip_name_resolve = 1
     
    #事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
    transaction_isolation = READ-COMMITTED
     
    #数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
    character-set-server = utf8mb4
     
    #数据库字符集对应一些排序等规则，注意要和character-set-server对应
    collation-server = utf8mb4_general_ci
     
    #设置client连接mysql时的字符集,防止乱码
    init_connect=‘SET NAMES utf8mb4‘
     
    #是否对sql语句大小写敏感，1表示不敏感
    lower_case_table_names = 1
     
    #最大连接数
    max_connections = 400
     
    #最大错误连接数
    max_connect_errors = 1000
     
    #TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
    explicit_defaults_for_timestamp = true
     
    #SQL数据包发送的大小，如果有BLOB对象建议修改成1G
    max_allowed_packet = 128M
     
    #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
    #MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
    interactive_timeout = 1800
    wait_timeout = 1800
     
    #内部内存临时表的最大值 ，设置成128M。
    #比如大数据量的group by ,order by时可能用到临时表，
    #超过了这个值将写入磁盘，系统IO压力增大
    tmp_table_size = 134217728
    max_heap_table_size = 134217728
     
    #禁用mysql的缓存查询结果集功能
    #后期根据业务情况测试决定是否开启
    #大部分情况下关闭下面两项
    query_cache_size = 0
    query_cache_type = 0
     
    #####################用户进程分配到的内存设置BEGIN#############################
     
    ##每个session将会分配参数设置的内存大小
    #用于表的顺序扫描，读出的数据暂存于read_buffer_size中，当buff满时或读完，将数据返回上层调用者
    #一般在128kb ~ 256kb,用于MyISAM
    #read_buffer_size = 131072
    #用于表的随机读取，当按照一个非索引字段排序读取时会用到，
    #一般在128kb ~ 256kb,用于MyISAM
    #read_rnd_buffer_size = 262144
    #order by或group by时用到
     
    #建议先调整为2M，后期观察调整
    sort_buffer_size = 2097152
     
    #一般数据库中没什么大的事务，设成1~2M，默认32kb
    binlog_cache_size = 524288
     
    ########################用户进程分配到的内存设置END############################
     
    #在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中
    #官方建议back_log = 50 + (max_connections / 5),封顶数为900
    back_log = 130
     
    ############################日志设置##########################################
     
    #数据库错误日志文件
    log_error = error.log
     
    #慢查询sql日志设置
    slow_query_log = 1
    slow_query_log_file = slow.log
     
    #检查未使用到索引的sql
    log_queries_not_using_indexes = 1
     
    #针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
    log_throttle_queries_not_using_indexes = 5
     
    #作为从库时生效,从库复制中如何有慢sql也将被记录
    log_slow_slave_statements = 1
     
    #慢查询执行的秒数，必须达到此值可被记录
    long_query_time = 8
     
    #检索的行数必须达到此值才可被记为慢查询
    min_examined_row_limit = 100
     
    #mysql binlog日志文件保存的过期时间，过期后自动删除
    expire_logs_days = 5
     
    ############################主从复制设置#####################################
     
    #开启mysql binlog功能
    log-bin=mysql-bin
     
    #binlog记录内容的方式，记录被操作的每一行
    binlog_format = ROW
     
    #对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列
    binlog_row_image = minimal
     
    #master status and connection information输出到表mysql.slave_master_info中
    master_info_repository = TABLE
     
    #the slave‘s position in the relay logs输出到表mysql.slave_relay_log_info中
    relay_log_info_repository = TABLE
     
    #作为从库时生效,想进行级联复制，则需要此参数
    log_slave_updates
     
    #作为从库时生效,中继日志relay-log可以自我修复
    relay_log_recovery = 1
     
    #作为从库时生效,主从复制时忽略的错误
    slave_skip_errors = ddl_exist_errors
     
    #####################redo log和binlog的关系设置BEGIN#########################
     
    #(步骤1) prepare dml相关的SQL操作，然后将redo log buff中的缓存持久化到磁盘
    #(步骤2)如果前面prepare成功，那么再继续将事务日志持久化到binlog
    #(步骤3)如果前面成功，那么在redo log里面写上一个commit记录
    #当innodb_flush_log_at_trx_commit和sync_binlog都为1时是最安全的，
    #在mysqld服务崩溃或者服务器主机crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。
    #但是都设置为1时会导致频繁的io操作，因此该模式也是最慢的一种方式。
    #当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
    #当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
     
    #commit事务时,控制redo log buff持久化磁盘的模式 默认为1
    innodb_flush_log_at_trx_commit = 2
     
    #commit事务时,控制写入mysql binlog日志的模式 默认为0
    #innodb_flush_log_at_trx_commit和sync_binlog都为1时，mysql最为安全但性能上压力也是最大
    sync_binlog = 1
     
    ####################redo log和binlog的关系设置END############################
     
    ############################Innodb设置#####################################
     
    #数据块的单位8k，默认是16k，16kCPU压力稍小，8k对select的吞吐量大
    #innodb_page_size的参数值也影响最大索引长度，8k比16k的最大索引长度小
    #innodb_page_size = 8192
     
    #一般设置物理存储的60% ~ 70%
    innodb_buffer_pool_size = 1G
     
    #5.7.6之后默认16M
    #innodb_log_buffer_size = 16777216
     
    #该参数针对unix、linux，window上直接注释该参数.默认值为NULL
    #O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
    innodb_flush_method = O_DIRECT
     
    #此格式支持压缩, 5.7.7之后为默认值
    innodb_file_format = Barracuda
     
    #CPU多核处理能力设置，假设CPU是2颗4核的，设置如下
    #读多，写少可以设成2:6的比例
    innodb_write_io_threads = 4
    innodb_read_io_threads = 4
     
    #提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力
    #默认值200（单位：页）
    #可根据磁盘近期的IOPS确定该值
    innodb_io_capacity = 500
     
    #为了获取被锁定的资源最大等待时间，默认50秒，超过该时间会报如下错误:
    # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    innodb_lock_wait_timeout = 30
     
    #调整buffer pool中最近使用的页读取并dump的百分比,通过设置该参数可以减少转储的page数
    innodb_buffer_pool_dump_pct = 40
     
    #设置redoLog文件所在目录, redoLog记录事务具体操作内容
    innodb_log_group_home_dir = /usr/local/mysql/redolog/
     
    #设置undoLog文件所在目录, undoLog用于事务回滚操作
    innodb_undo_directory = /usr/local/mysql/undolog/
     
    #在innodb_log_group_home_dir中的redoLog文件数, redoLog文件内容是循环覆盖写入。
    innodb_log_files_in_group = 3
     
    #MySql5.7官方建议尽量设置的大些，可以接近innodb_buffer_pool_size的大小
    #之前设置该值较大时可能导致mysql宕机恢复时间过长，现在恢复已经加快很多了
    #该值减少脏数据刷新到磁盘的频次
    #最大值innodb_log_file_size * innodb_log_files_in_group <= 512GB,单文件<=256GB
    innodb_log_file_size = 1024M
     
    #设置undoLog文件所占空间可以回收
    #5.7之前的MySql的undoLog文件一直增大无法回收
    innodb_undo_log_truncate = 1
    innodb_undo_tablespaces = 3
    innodb_undo_logs = 128
     
    #5.7.7默认开启该参数 控制单列索引长度最大达到3072
    #innodb_large_prefix = 1
     
    #5.7.8默认为4个, Inodb后台清理工作的线程数
    #innodb_purge_threads = 4
     
    #通过设置配置参数innodb_thread_concurrency来限制并发线程的数量，
    #一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒，
    #可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间
    #该值默认为0,在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议:
    #(1)如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
    #(2)如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128,
    ###并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数
    #innodb_thread_concurrency = 0
     
    #强所有发生的死锁错误信息记录到error.log中，之前通过命令行只能查看最近一次死锁信息
    innodb_print_all_deadlocks = 1
     
    ############################其他设置########################################
     
    [mysqldump]
    quick
    max_allowed_packet = 128M
     
    [mysql]
    no-auto-rehash
     
    [myisamchk]
    key_buffer_size = 20M
    sort_buffer_size = 256k
    read_buffer = 2M
    write_buffer = 2M
     
    [mysqlhotcopy]
    interactive-timeout
     
    [mysqld_safe]
    #增加每个进程的可打开文件数量
    open-files-limit = 28192

[Mysql_-]: http://vdisk.weibo.com/s/dbMpuweRAEEb3
[Mysql_- 1]: https://www.jianshu.com/p/294502893128
[mysql 5.51 API]: http://tool.oschina.net/apidocs/apidoc?api=mysql-5.1-zh