Mysql 基础知识（详细）

 

 

目录

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.log 

2、登陆

--本地登陆
mysql -uroot -proot

--远程登陆，需要有远程登陆权限
mysql -uroot -h127.0.0.1 -P 3306 -proot

3、建库建表

---编码
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 |
+-----------------+-----+------------+-----------+-------------+-----------------------+

 

 

总结比较详细的mysql脑图：https://blog.csdn.net/weixin_36986228/article/details/89091649