目录
DBA之禅
0、SQL概要
1、环境配置
2、登陆
3、建库建表
如何选择 MySQL 存储引擎
4、简单查询
5、聚合、分组查询
6、数据更新
7、视图
8、函数，谓词，case表达式
9、联结表、组合查询
10、全文本搜素
11、存储过程
12、容灾备份
13、mysql日志
DBA之禅
一名合格的DBA所应遵循的四个生存守则（转）
1.备份重于一切
我们必需知道,系统总是要崩溃的，没有有效的备份只是等哪一天死!我经常开玩笑的说,唯一会使DBA在梦中惊醒的就是,没有有效的备份.
2.三思而后行
任何时候都要清楚你所做的一切，否则宁可不做!有时候一个回车,一条命令就会造成不可恢复的灾难,所以,你必需清楚确认你所做的一切,并且在必要时保护现场.
3.rm是危险的
要知道在UNIX/Linux下，这个操作意味着你可能将永远失去后面的东西，所以，确认你的操作!!!
太多的人在 "rm -rf" 上悲痛欲绝,当年写下这条守则时,是一个凌晨被一个朋友吵醒,他说误操作rm -rf删除掉了200G的数据库,并且没有备份.
我当时能告诉他的只有一句话:要保持冷静.
4.你来制定规范
良好的规范是减少故障的基础。所以,做为一个DBA,你需要来制订规范,规范开发甚至系统人员,这样甚至可以规避有意或是无意的误操作.减少数据库的风险.
我们知道,在管理良好的数据库服务器上,rm -rf甚至可能是不允许使用的.
也许我们需要遵守的可能更多,所以我一直强调DBA一定要严谨专注,当然我也非常喜欢另外一句话:坚韧卓绝之人，必能成就万事。
0、SQL概要
数据定义语言（Data Definition Language,DDL） CAD; create、alter、drop
数据操作语言（Data Manipulation Language,DML） DUSI; delete、update、select、insert --commit
数据控制语言（Data Control Language,DCL） CRGR; commit、rollback、grant、revoke(取消赋权)
1、环境配置
--系统环境，阿里云主机
[root@oracle /]# cat /etc/redhat-release
CentOS release 6.9 (Final)
--查看是否安装
yum list installed | grep mysql
--安装mysql
yum install mysql mysql-server mysql-devel -y
--安装后，查看安装信息
rpm -qi mysql-server
--启动停止，重启
service mysqld start/stop/restart
--开机重启
chkconfig mysqld on
--Linux下面mysql 清屏
system clear;
--mysql变量分为系统变量（环境属性）与状态变量（运行中属性）
--查看版本相关属性
show global variables like'%version%' \G;
--查看字符设置
show global variables where variable_name like 'character%';
--查看日志是否开启
mysql> show global variables where variable_name like '%log%' and value='off';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_direct_non_transactional_updates | OFF |
| binlog_rows_query_log_events | OFF |
| general_log | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_undo_log_truncate | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_syslog | OFF |
| relay_log_recovery | OFF |
| slow_query_log | OFF |
| sql_log_off | OFF |
+-----------------------------------------+-------+
--发下mysql Linux不好安装，安装5.7地址
http://www.mamicode.com/info-detail-1166036.html
查看初始密码：grep 'temporary password' /var/log/mysqld.log2、登陆
--本地登陆
mysql -uroot -proot
--远程登陆，需要有远程登陆权限
mysql -uroot -h127.0.0.1 -P 3306 -proot3、建库建表
---编码
MySQL服务器可以支持多种字符集，在同一台服务器，同一个数据库，甚至同一个表的不同字段都可以指定使用不同的字符集，相比oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，MySQL明显存在更大的灵活性。
character set可以简写为char set和charset
建表 engine=innodb default charset=utf8;
建库 create database dbking charset=utf8;
show create database db_name;命令查看数据库创建语句：
查看当前数据库的字符集
mysql> show variables like 'character%';
名词解释：
character_set_client：客户端请求数据的字符集
character_set_connection：客户机/服务器连接的字符集
character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。
character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的
character_set_results：结果集，返回给客户端的字符集
character_set_server：数据库服务器的默认字符集
character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。
查看当前数据库的校对规则
mysql> show variables like 'collation%';
名词解释：
collation_connection 当前连接的字符集。
collation_database 当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候，这个变量的值就会改变。如果没有当前数据库，这个变量的值就是collation_server变量的值。
collation_server 服务器的默认校对。
创建对象的默认值
字符集合校对规则有4个级别的默认设置：
1）服务器级别；
2）数据库级别；
3）表级别、列级别；
4）连接级别。
更低级别的设置会集成高级别的设置。
这里有一个通用的规则：先为服务器或者数据库选择一个合理的字符集，然后根据不同的实际情况，让某个列选择自己的字符集。
附送几个数据库中文编码的经验教训：
1. 基于可维护的角度，虽然latin1没什么问题，但是还是尽量换成utf8或者gb系列
2. 出现乱码时：
SHOW VARIABLES LIKE 'character%'
SHOW VARIABLES LIKE 'collation_%';
a、要保证数据库中存的数据与数据库编码一致，即数据编码与character_set_database一致；
b、要保证通讯的字符集与数据库的字符集一致，即character_set_client, character_set_connection与character_set_database一致；
c、要保证SELECT的返回与程序的编码一致，即character_set_results与程序编码一致；
d、要保证程序编码与浏览器、终端编码一致
3. 要想简单一点的话，就将各个字符集都设为一致的，写入mysql的配置文件，每次用客户端都设置一下字符集(set names 'xxx')，写入和读取时要记得确保字节流的编码是ok的
----------------------------------------------------------------------------------------
--查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
--查看编码
show variables like '%character%';
--创建数据库
create database shop;
判断原来不存在，则创建数据库，并指定编码格式和校验集：
create database if not exists hehe default character set utf8 collate utf8_general_ci;
--使用数据库
use shop;
--查看表
show tables；
mysql> use shop;
Database changed
mysql> show tables;
Empty set (0.00 sec)
--创建表
drop table if exists shohin;
create table shohin
(
shohin_id char(4) primary key not null,
shohin_name varchar(100) not null,
shohin_leibie varchar(32),
shohin_number integer,
shohin_price integer,
shohin_time date
)engine=InnoDB default charset=utf8;
--删除表
drop table shohin;
--描述表
desc shohin
--增加一栏/删除一栏
alter table shohin add column shohin_add int;
alter table shohin drop column shohin_add int;
mysql> desc shohin;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| shohin_id | char(4) | NO | PRI | NULL | |
| shohin_name | varchar(100) | NO | | NULL | |
| shohin_leibie | varchar(32) | YES | | NULL | |
| shohin_number | int(11) | YES | | NULL | |
| shohin_price | int(11) | YES | | NULL | |
| shohin_time | date | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table shohin add column shohin_add int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc shohin;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| shohin_id | char(4) | NO | PRI | NULL | |
| shohin_name | varchar(100) | NO | | NULL | |
| shohin_leibie | varchar(32) | YES | | NULL | |
| shohin_number | int(11) | YES | | NULL | |
| shohin_price | int(11) | YES | | NULL | |
| shohin_time | date | YES | | NULL | |
| shohin_add | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
--查看建表语句
show create table shohin;
--插入数据 start transaction或begin语句可以开始一项新的事务。commit可以提交当前事务，是变更成为永久变更。
start transaction;
insert into shohin values
('0001','T恤','衣服',1000,500,'2009-09-20'),
('0002','打孔器','办公用品',500,320,'2009-09-11'),
('0003','运动T恤','衣服',4000,2800,NULL),
('0004','菜刀','厨房用具',3000,2800,'2009-09-20'),
('0005','高压锅','厨房用具',6800,5000,'2009-01-15'),
('0006','叉子','厨房用具',500,NULL,'2009-09-20'),
('0007','擦菜板','厨房用具',880,790,'2008-04-28'),
('0008','圆珠笔','办公用品',100,NULL,'2009-11-11');
commit;
--重命名表
alter table shohin rename to xixi;如何选择 MySQL 存储引擎
不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。
功能 | MylSAM | MEMORY | InnoDB | Archive |
存储限制 | 256TB | RAM | 64TB | None |
支持事务 | No | No | Yes | No |
支持全文索引 | Yes | No | No | No |
支持树索引 | Yes | Yes | Yes | No |
支持哈希索引 | No | Yes | No | No |
支持数据缓存 | No | N/A | Yes | No |
支持外键 | No | No | Yes | No |
可以根据以下的原则来选择 MySQL 存储引擎：
- 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
- 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
- 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
- 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。
4、简单查询
--表数据
mysql> desc xixi;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| shohin_id | char(4) | NO | PRI | NULL | |
| shohin_name | varchar(100) | NO | | NULL | |
| shohin_leibie | varchar(32) | YES | | NULL | |
| shohin_number | int(11) | YES | | NULL | |
| shohin_price | int(11) | YES | | NULL | |
| shohin_time | date | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
mysql> select * from xixi;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+-----------+-------------+---------------+---------------+--------------+-------------+
--限制条数
ysql> select * from xixi limit 2;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0001 | T恤 | 衣服 | 1600 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
+-----------+-------------+---------------+---------------+--------------+-------------+
mysql> select * from xixi limit 2,2;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
+-----------+-------------+---------------+---------------+--------------+-------------+
--查询名字
select shohin_name from xixi;
select shohin_name as shangping_name from xixi;
--常数查询
select '666' as test,66 as test1,shohin_name from xixi;
--不要重复的，distinct
mysql> select shohin_leibie, shohin_name from xixi;
+---------------+-------------+
| shohin_leibie | shohin_name |
+---------------+-------------+
| 衣服 | T恤 |
| 办公用品 | 打孔器 |
| 衣服 | 运动T恤 |
| 厨房用具 | 菜刀 |
| 厨房用具 | 高压锅 |
| 厨房用具 | 叉子 |
| 厨房用具 | 擦菜板 |
| 办公用品 | 圆珠笔 |
+---------------+-------------+
--在多个列之前使用，此时会将多个列的数据进行组合，将重复的数据结合为一条
mysql> select distinct shohin_leibie, shohin_name from xixi;
+---------------+-------------+
| shohin_leibie | shohin_name |
+---------------+-------------+
| 衣服 | T恤 |
| 办公用品 | 打孔器 |
| 衣服 | 运动T恤 |
| 厨房用具 | 菜刀 |
| 厨房用具 | 高压锅 |
| 厨房用具 | 叉子 |
| 厨房用具 | 擦菜板 |
| 办公用品 | 圆珠笔 |
+---------------+-------------+
mysql> select distinct shohin_leibie from xixi;
+---------------+
| shohin_leibie |
+---------------+
| 衣服 |
| 办公用品 |
| 厨房用具 |
+---------------+
--where语句
mysql> select shohin_name from xixi where shohin_price >=5000;
+-------------+
| shohin_name |
+-------------+
| 高压锅 |
+-------------+
--between语句
mysql> select * from xixi where shohin_number between 100 and 500;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+-----------+-------------+---------------+---------------+--------------+-------------+
--不含from查询语句
mysql> select (1+2) as haha;
+------+
| haha |
+------+
| 3 |
+------+
--时间大小比较
mysql> select * from xixi where shohin_time < '2009-09-20' limit 1;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
+-----------+-------------+---------------+---------------+--------------+-------------+
--多添加查询
mysql> select * from xixi where shohin_leibie='厨房用具';
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
+-----------+-------------+---------------+---------------+--------------+-------------+
--当与NULL进行逻辑运算时，其结果为不确定。
mysql> select * from xixi where shohin_leibie='厨房用具' and (shohin_price=null or shohin_time='2008-04-28');
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
+-----------+-------------+---------------+---------------+--------------+-------------+
mysql> select * from xixi where shohin_leibie='厨房用具' and (shohin_price='2800' or shohin_time='2009-09-20');
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
+-----------+-------------+---------------+---------------+--------------+-------------+
--正则查询
mysql> select * from xixi where shohin_leibie regexp '用品|用具';
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+-----------+-------------+---------------+---------------+--------------+-------------+
--concat连接
mysql> select * from xixi limit 1;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0001 | T恤 | 衣服 | 1600 | 500 | 2009-09-20 |
+-----------+-------------+---------------+---------------+--------------+-------------+
mysql> select concat (shohin_id,'(',shohin_name,')') as id_name from xixi limit 1;
+------------+
| id_name |
+------------+
| 0001(T恤) |
+------------+5、聚合、分组查询
--count（*），包含空值null
mysql> select count(*) from xixi;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
--统计不含null的行数
mysql> select count(shohin_price) from xixi;
+---------------------+
| count(shohin_price) |
+---------------------+
| 6 |
+---------------------+
--sum，avg，max，min。※MAX和MIN函数几乎适用于所有数据类型的列，SUM和AVG函数只适用于数值类型的列
mysql> select sum(shohin_price) '不含null总价' from xixi;
+------------------+
| 不含null总价 |
+------------------+
| 12210 |
+------------------+
mysql> select max(shohin_time) '最大时间',min(shohin_time) '最小时间' from xixi;
+--------------+--------------+
| 最大时间 | 最小时间 |
+--------------+--------------+
| 2009-11-11 | 2008-04-28 |
+--------------+--------------+
--统计不重复值
mysql> select count(distinct shohin_leibie) from xixi;
+-------------------------------+
| count(distinct shohin_leibie) |
+-------------------------------+
| 3 |
+-------------------------------+
mysql> select * from xixi;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+-----------+-------------+---------------+---------------+--------------+-------------+
mysql> select shohin_leibie,count(*) from xixi group by shohin_leibie;
+---------------+----------+
| shohin_leibie | count(*) |
+---------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
| 厨房用具 | 4 |
+---------------+----------+
--过滤分组
mysql> select shohin_leibie,count(*) from xixi group by shohin_leibie having count(*)=2;
+---------------+----------+
| shohin_leibie | count(*) |
+---------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
+---------------+----------+
--子句的执行顺序：SELECT->FROM->WHERE->GROUP BY->HAVING
SELECT 列名1,列名2,列名3, ... FROM 表名
GROUP BY 列名1,列名2,列名3, ...
HAVING 分组结果对应的条件;
--降序排列
select shohin_id from xixi order by shohin_id desc;
--别名省略as
mysql> select shohin_name othername from xixi order by othername;
+------------+
| othername |
+------------+
| 菜刀 |
| 高压锅 |
| 运动T恤 |
| T恤 |
| 叉子 |
| 圆珠笔 |
| 打孔器 |
| 擦菜板 |
+------------+6、数据更新
--更新数据
insert into xixi(shohin_name) values('haha');
--删除一条数据
delete from xixi where shohin_name='haha';
--删除所有数据
delete from xixi
--其他表中复制
create table xixi1(select shohin_id,shohin_name from xixi);
--更新数据
UPDATE 表名 SET 列名=NULL WHERE 条件;
--多列
UPDATE 表名 SET 列名=NULL,列名=NULL WHERE 条件;
update xixi set shohin_number=1100 where shohin_name='T恤';
--事务：需要在同一个处理单元中执行的一系列更新处理的集合
创建事务：
事物开始语句(start transtion);
DML语句1;
DML语句2;
DML语句3;
...
事物结束语句（commit或者rollback）;
start transtion;
begin;
update xixi set shohin_number=1200 where shohin_name='T恤';
update xixi set shohin_number=1300 where shohin_name='T恤';
update xixi set shohin_number=1600 where shohin_name='T恤';
commit;7、视图
视图与表的唯一区别：是否保存了实际的数据。数据库中的数据实际上会被保存到计算机的存储设备中，但使用视图时并不会将数据保存到存储设备之中，也不会将数据保存到其他任何地方。实际上是他保存的是select语句。视图就是从存在的表里面挑选出部分字段或者数据，本质可以理解为一条查询语句。
--创建视图，as关键字不可省略
create view xixi_view(id,name) as select shohin_id,shohin_name from xixi;
--删除视图
drop view xixi_view;
--使用视图的查询：通常需要执行2条以上的SELECT语句
a.首先执行定义视图的select语句；
b.根据得到的结果，再执行在from子句中使用视图的SELECT语句。
--视图的限制：通过聚合得到的视图无法进行更新
a.定义视图时不能使用ORDER BY子句（视图和表一样，数据行都是没有顺序的）
b.当定义视图的SELECT语句满足以下某些条件时，可以对视图进行更新（更新包括：INSERT、DELETE、UPDATE）。条件如下：SELECT语句中未使用DISTINCT；FROM只有一张表；未使用GROUP BY子句；未使用HAVING子句
--子查询,一张一次性视图
select shohin_id from (select shohin_id,shohin_name from xixi) as haha;
mysql> select shohin_id from xixi where shohin_leibie in
-> (select shohin_leibie from xixi where shohin_leibie='厨房用具');8、函数，谓词，case表达式
--算术函数、字符串函数、日期函数、转换函数、聚合函数。
大小写，去空格，对齐，子串
--日期函数
select current_date;
select current_time
select current_timestamp;
--谓词：满足返回值是真值的函数，‘_’代表任意一个字符，而‘%’则代表一个或多个字符
select * from xixi where shohin_leibie like'%厨房%';
--CASE表达式：一种表示（条件）分歧的函数
mysql> select * from xixi;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0001 | T恤 | 衣服 | 1600 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+-----------+-------------+---------------+---------------+--------------+-------------+
mysql> select case when shohin_name in ('T恤','运动T恤') then '衣服'
-> when shohin_name in ('打孔器','圆珠笔') then '办公用品'
-> else '厨房用具'
-> end as shohin_lb
-> from xixi;
+--------------+
| shohin_lb |
+--------------+
| 衣服 |
| 办公用品 |
| 衣服 |
| 厨房用具 |
| 厨房用具 |
| 厨房用具 |
| 厨房用具 |
| 办公用品 |
+--------------+9、联结表、组合查询
--先创建表
mysql> create table jh1
-> (id int,
-> bh int);
mysql> create table jh2
-> (id2 int,
-> bh2 int);
--insert into jh1 values(1,2),(3,4),(5,6);
mysql> select * from jh1;
+------+------+
| id | bh |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
+------+------+
mysql> select * from jh2;
+------+------+
| id2 | bh2 |
+------+------+
| 1 | 2 |
| 7 | 8 |
+------+------+
--m*n,交叉连接
mysql> select * from jh1,jh2;
+------+------+------+------+
| id | bh | id2 | bh2 |
+------+------+------+------+
| 1 | 2 | 1 | 2 |
| 1 | 2 | 7 | 8 |
| 3 | 4 | 1 | 2 |
| 3 | 4 | 7 | 8 |
| 5 | 6 | 1 | 2 |
| 5 | 6 | 7 | 8 |
+------+------+------+------+
--内连接
mysql> select * from jh1,jh2 where jh1.id=jh2.id2;
+------+------+------+------+
| id | bh | id2 | bh2 |
+------+------+------+------+
| 1 | 2 | 1 | 2 |
+------+------+------+------+
mysql> select * from jh1,jh2 where jh1.id>jh2.id2;
+------+------+------+------+
| id | bh | id2 | bh2 |
+------+------+------+------+
| 3 | 4 | 1 | 2 |
| 5 | 6 | 1 | 2 |
+------+------+------+------+
mysql> select * from jh1,jh2 where jh1.id<jh2.id2;
+------+------+------+------+
| id | bh | id2 | bh2 |
+------+------+------+------+
| 1 | 2 | 7 | 8 |
| 3 | 4 | 7 | 8 |
| 5 | 6 | 7 | 8 |
+------+------+------+------+
--union与union all，union会去除重复着，union all会打印重复值
mysql> select * from jh1 union select * from jh2;
+------+------+
| id | bh |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 7 | 8 |
+------+------+
mysql> select * from jh1 union all select * from jh2;
+------+------+
| id | bh |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 1 | 2 |
| 7 | 8 |
+------+------+10、全文本搜素
-- 类似like语句，MyISAM支持全文本搜索，InnoDB不支持。
为了进行全文本搜索，必须在被搜索的列建立索引，在建立索引后，使用match()与against()完成搜索，match指定被搜索的列，against指定要使用的搜索表达式。传递给match()的值必须与fulltext()定义中的相同，如果指定多个列，则必须列出他们，次序也需正确。
select shohin_price from xixi where match(shohin_price) against('500');11、存储过程
qu tmd 这个总是出问题，……
--多条语句结合处理一个任务
存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等
过程体格式：以begin开始，以end结束(可嵌套)
----------创建存储过程名字为cunchuway
delimiter //
create procedure cunchuway()
begin
select sum(shohin_id) as totalnum
from xixi;
end //
--将语句的结束符号恢复为分号，一定要有空格哈 delimiter 分隔符
delimiter ;
--调用
call cunchuway();
--删除
drop procedure cunchuway;
----------创建带参数存储过程名字为cunchuname
delimiter //
create procedure cunchuname(in idd int)
begin
select * from xixi where shohin_id=idd;
end //
--将语句的结束符号恢复为分号，一定要有空格哈
delimiter ;
--调用
call cunchuname(0001);
--删除
drop procedure cunchuname;
----触发器
在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器。
BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。
-- NEW用来表示将要(before)或已经(after)插入的新数据。
-- old用来表示将要或已经被删除的原数据。
--查看触发器状态
show triggers;
show triggers from databaseName;
--创建触发器
drop trigger if exists haha.sumid;
delimiter //
create trigger sumid
after insert on xixi
for each row
begin
set @sum=@sum+new.shohin_number;
end
//
delimiter ;
set @sum=(select sum(shohin_number) from xixi);
insert into xixi(shohin_id,shohin_name,shohin_number) values(0009,'吼吼',1000);
select @sum;
触发器创建的四个要素
（1）监视地点（table）
（2）监视事件（insert/update/delete）
（3）触发时间（after/before）
（4）触发事件（insert/update/delete）
--触发器进阶
-- 变量直接赋值
set @num=999;
-- 使用select语句查询出来的数据方式赋值，需要加括号：
set @name =(select name from table);
-- 简单的if语句：
set sex = if (new.sex=1, '男', '女');
-- 多条件if语句：
if old.type=1 then
update table ...;
elseif old.type=2 then
update table ...;
end if;12、容灾备份
不同容灾备份对比
差异备份，恢复差一备份 增量备份，恢复增2备份
热备：读写 温备：只读 冷备
---------------------------------------------------------------------------------
备份方式1： 只导出数据，无表结构,小心乱码
--可以使用 SELECT INTO OUTFILE 语句把表数据导出到一个文本文件中进行备份。
*************注意：这种方法只能导出或导入数据的内容，而不包括表的结构。若表的结构文件损坏，则必须先设法恢复原来表的结构。
select * from hehe.shohininto outfile '/var/mysql.txt';
load data infile '/var/mysql.txt' into table hehe.shohin;
将数据库 haha 的表 xixi 的全部数据备份到 /var/lib/mysql-files/mysql.txt 的文件中，要求每个字段用逗号分开，并且字符用双引号标注，每行以问号结束。
show variables like "secure_file_priv";
start transaction;
insert into xixi values
('0001','T','fg',1000,500,'2009-09-20'),
('0008','gg','gf',100,NULL,'2009-11-11');
commit;
mysql>
select * from hehe.shohin
into outfile '/var/lib/mysql-files/mysql.txt'
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n';
用记事本查看 MySQL 备份文件夹下的 file.txt 文件
delete from xixi;
--恢复----乱码
mysql>
load data infile '/var/lib/mysql-files/mysql.txt'
into table hehe.shohin
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n';
--恢复乱码
show variables like "%char%";
此时可以使用set names utf8;
再次使用show variables like "%char%";
mysqldump 和xtrabackup
保持一致性方法：快照，只读锁
-------------------------------------------------------------------------------
备份方式2：
mysqldump备份数据是insert语句，可以文本处理，缺点是效率低，不能很多数据
innodb支持热备，支持事务，可以基于事务热备。
myisam只支持温备，表备份，请求锁住
----master-data记录binlog的编号
----lock-all-tables myisam的锁表参数
------default-character-set=utf8 编码设置
mysqldump -uroot -h127.0.0.1 --all-databases --master-data=2 -p > bakmysql.sql
mysqldump -uroot -h127.0.0.1 --all-databases --master-data=2 -p|gzip > bakmysql.sql.gz
后者路径改成 /root/backmysql/bak_$(date +%F).sql.gz
-----------------------------------------------------------------------------恢复
恢复关闭binlog
set sql_log_bin=off;
--获取全路径
[root@oracle ~]# readlink -f bakmysq1l.sql
/root/bakmysq1l.sql
--恢复数据有空格
mysql> \. /root/bakmysq1l.sql
----------------------------------------------------------------------------------
xtrabackup：物理备份工具 对innodb可以热备，增量，差异备份 对myisan温备，增加读锁，只能全量备份。
----------------innodb如何保证一致性
--xtrabackup备份会运行2线程，一个复制innodb的page，一个负责innodb的事务日志（redo），事务日志会被xtrabackup记录到自己的日志文件当中，备份结束后有2份，一份不可用（未确定的在事务中），一份备份的事务日志，通过应用事务日志变成一份可用的，提交的replayed，未提交的roolback，类似崩溃后的恢复，
这个过程被称为‘prepare’；prepare操作保证了备份出的数据一致性。
---表引擎有innodb又有myisam时，先热备innodb，之后备份myisam加锁，温备，最后对innodb库prepare，保证所有引擎的世界点是一致的。
--innodb逻辑存储结构看，所有的数据都被逻辑的存放在一个空间中，为表空间（tablespace）。表空间又由段（segment）、区（extend）、页（page）组成，页也可以别成为块（block）。 备份读取页备份，LSN记录，LSN类似seq实现增量备份，有变化的页就记录实现增量，恢复就LSN覆盖旧的页。
xtrabackup安装
yum list | grep percona-xtrabackup
查看版本：
xtrabackup -version
2.4的xtrabackup=2.3的innobackupex+2.3的xtrabackup
2.3的：
1).xtrabackup只能备份InnoDB和XtraDB 两种数据表
2).innobackupex则封装了xtrabackup,同时可以备份MyISAM数据表13、mysql日志
mysql日志有错误 、查询 、慢日志、 二进制、 中继 、innodb存储引擎事务这6种类型
--错误日志： 错误信息，警告信息
mysql> show variables like '%log_error%';
+---------------------+---------------------+
| Variable_name | Value |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
+---------------------+---------------------+
修改位置永久生效 my.cnf修改log_error
--查询日志，记录了数据库执行的命令，不管正确与否
show variables where variable_name like '%general_log%' or variable_name='log_output';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/oracle.log |
| log_output | FILE |
+------------------+---------------------------+
默认关闭，暂时打开
set global general_log=on;
--慢查询日志 响应比较慢的日志，dml超过指定时间，都叫‘慢查询’
show variables where variable_name like '%slow_query%' or variable_name='log_output';
临时开启
set global slow_query_log=on;
查看开机以来的所有慢查询
show global status like '%slow_queries%';
--二进制日志，binlog，查询不记录，修改才记录，用户备份恢复，重新执行操作的语句
show variables like '%log_bin%';
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mybinlog |
| log_bin_index | /var/lib/mysql/mybinlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------------------------------+
去对应目录 ll mybin* 查看
三种模式
statuement 记录语句
row模式，插入1000行，记录1000行，空间，io大 --生产端环境，保险，一般row
mixed模式：混合
二进制：记录的事件字节和位置字节，事件 events ，位置 position
-----------------------------------------
row模式只会记录修改的
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mybinlog.000001 | 154 |
+-----------------+-----------+
--类似切归档
flush logs;
--查看记录到哪里了
show master status;
mysql> select * from shohin;
+-----------+-------------+---------------+---------------+--------------+-------------+
| shohin_id | shohin_name | shohin_leibie | shohin_number | shohin_price | shohin_time |
+-----------+-------------+---------------+---------------+--------------+-------------+
| 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
+-----------+-------------+---------------+---------------+--------------+-------------+
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mybinlog.000001 | 154 |
+-----------------+-----------+
mysql> insert into shohin(shohin_id,shohin_name) values(100,'擦菜');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mybinlog.000001 | 430 |
+-----------------+-----------+
1 row in set (0.00 sec)
--查看事件
show binlog events;
mysql> show binlog events in 'mybinlog.000001';
+-----------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+----------------+-----------+-------------+---------------------------------------+
| mybinlog.000001 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mybinlog.000001 | 123 | Previous_gtids | 10 | 154 | |
| mybinlog.000001 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mybinlog.000001 | 219 | Query | 10 | 291 | BEGIN |
| mybinlog.000001 | 291 | Table_map | 10 | 351 | table_id: 108 (hehe.shohin) |
| mybinlog.000001 | 351 | Write_rows | 10 | 399 | table_id: 108 flags: STMT_END_F |
| mybinlog.000001 | 399 | Xid | 10 | 430 | COMMIT /* xid=11 */ |
+-----------------+-----+----------------+-----------+-------------+---------------------------------------+
--从指定字节查看
mysql> show binlog events in 'mybinlog.000001' from 154;
mysql> show binlog events in 'mybinlog.000001' from 430;
+-----------------+-----+------------+-----------+-------------+-----------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+------------+-----------+-------------+-----------------------+
| mybinlog.000001 | 430 | Rotate | 10 | 476 | mybinlog.000002;pos=4 |
+-----------------+-----+------------+-----------+-------------+-----------------------+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
