MySQL初级

2021-04-13

关系型数据库

服务器：

硬件：指的就是一台计算机

软件：指的是需要在这台电脑上安装数据库服务器

一台电脑如果安装了MySQL，这台电脑称为是MySQL数据库服务器

MySQL数据库服务器存储的方式：

一台数据库服务器中会创建很多数据库（一个项目，会创建一个数据库）。在数据库中会创建很多张表（一个实体会创建一个表）。在表中会有很多记录（一个对象的实例会添加一条新的记录）。

SQL：结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

分类：

DDL：数据定义语言：create，drop，alter..

DCL：数据控制语言：grant，if…

DML：数据操纵语言：insert，update，delete…

DQL：数据查询语言：select （不区分大小写）

MySQL 刚创建完成的时候会生成四个系统数据库：information_schema、performance_schema、mysql、test、

information_schema：虚拟库，不占用磁盘内存，存储的是数据库启动后的参数
performance_schema：用于收集数据库服务器性能的参数，记录处理查询请求时发生的各种事件、锁等现象
mysql：授权库，存储系统用户的权限信息
test：MySQL数据库系统自动创建的测试数据库

对数据库进行CRUD的操作

创建数据库：

语法：

create database 数据库名称 [character set 字符集 collate 字符集校对规则];

utf-8的字符集写的时候是utf8

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode，比如emoji表情

为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR

字符集校对规则可以在查询手册中查到，用来数据库排序展示

查看数据库：

语法：

查看数据库服务器中所有的数据库：show databases;

查看某个数据库的定义信息： show create database 数据库名称;

修改数据库：

语法：

alter database 数据库名称 character set 字符集 collate 校对规则;

删除数据库：

语法:

drop database 数据库名称;

其他数据库操作：

切换数据库：use 数据库名称;

查看当前正在使用的数据库：select database();

对数据库表进行操作

Java中的类型	MySQL中的类型
byte/short/int/long	tinyint/smallint/int/bigint
float	float
double	double
boolean	bit
char/String	char和varchar类型
Date	date/time/datetime/timestamp
File	BLOB/TEXT

tinyint 型的字段如果设置为unsigned类型，只能存储从0到255的整数,不能用来储存负数。
tinyint 型的字段如果不设置unsigned类型,存储-128到127的整数。
1个tinyint型数据只占用一个字节;一个INT型数据占用四个字节。
这看起来似乎差别不大，但是在比较大的表中，字节数的增长是很快的。
tinyint(1)与tinyint(2)的区别可以从下面看出来:

CREATE TABLE `test` (                                  
   `id` int(11) NOT NULL AUTO_INCREMENT,                
   `str` varchar(255) NOT NULL,                                     
   `state`  tinyint(1) unsigned zerofill DEFAULT NULL,   
   `state2` tinyint(2) unsigned zerofill DEFAULT NULL,  
   `state3` tinyint(3) unsigned zerofill DEFAULT NULL,  
   `state4` tinyint(4) unsigned zerofill DEFAULT NULL,  
   PRIMARY KEY (`id`)                                   
 ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8  
         
insert into test (str,state,state2,state3,state4) values('csdn',4,4,4,4);
select * from test;

结果：
id   str   state   state2   state3   state4 
1    csdn  4        04       004     0004 

于是在dll里面channel` tinyint(2) unsigned zerofill DEFAULT ‘00’

问题来了，2指的是存储宽度，不表示存储长度。如果列制定了zerofill 就会用0填充显示，例如tinyint(2)指定后2就会显示为02，自动左边补零

char和varchar的区别：

char代表是固定长度的字符或字符串。定义类型char(8),向这个字段存入字符串hello，那么数据库使用三个空格将其补全。

varchar代表的是可变长度的字符串。定义类型varchar(8), 向这个字段存入字符串hello,那么存入到数据库的就是hello。

datetime和timestamp区别：

datetime就是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用null存入到数据库中。

timestamp也是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用当前的系统时间存入到数据库中。

存储空间：TIMESTAMP占用4个字节 DATETIME占用8个字节
时区：TIMESTAMP实际记录的是1970-01-01 00:00:01到现在的数数，受时区影响 DATETIME不受时区影响
时间范围：TIMESTAMP ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC DATETIME ‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
存储方式：对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回(中国属于东八区，所以应该是UTC+8）而对于DATETIME，不做任何改变，基本上是原样输入和输出。

int类型占用4字节，datetime占用8字节，timestamp占用4字节；通常情况下在选择表中列的数据类型时我们要选择能满足存储需要的，最小的数据类型，在使用MySQL数据库时有很多常见的误解，其中使用int类型来保存日期数据会提高数据读取的效率就是比较常见的一个误解。显然INT要比datetime类型小很多，同时MySQL又提供了两个非常好用的函数FROM_UNIXTIME() 和UNIX_TIMESTAMP()，使用这两个函数可以方便的在INT和DATETIME类型之间进行转换，但是使用INT类型存储时间也给我们带来了不少的麻烦：

1.数据的可读性比较差，我们在查看数据时不能直观的看出时间列中记录的一串整数所代表的时间
2.每次进行显示时都要通过函数进行转换，增加了数据使用的复杂成度。
那有没有什么更好的方法来存储日期数据呢？这就要用到我们标题中所说到的timestamp类型了，timestamp类型的特点如下：
1.存储占用 4个字节，以年月日小时分秒的日期型式显示
2.存储范围’1970-01-01 00:00:01’ to ‘2038-01-19 03:14:07’.
3.以UTC时区进行存储，但是以系统当前时间进行显示
4.可以在insert和update时把值自动更新为当前时间

timestamp存储占用的空间和INT类型相同，实际上timestamp类型的数据在存储时就是被保存成INT类型的数据来存储的，这和我们使用INT来存储日期时间数据可以说是完全一样的。由于同样是使用INT类型来保存数据，所以和INT类型一样其存储的时间范围也是有限制的，
这一点大家一定要注意，超过了这个范围的日期数据建议大家使用datetime类型来保存。另外timestamp数据存储时是以UTC时区来保存的，在显示时MySQL会自动的把数据转换为当前连接所对应时间来显示。

可见，使用timestamp来存储日期时间数据不但保证了数据类型的大小同INT类型一样，同时可以显示为日期时间格式，这在给我们使用数据带来了很多的方便。所以强烈建议大家，使用timestamp类型来存储日期数据而不要再使用INT类型了。

BLOB是二进制的文件，TEXT是文本类型的文件（都很少使用）

TINYBLOB: maximum length of 255 bytes
BLOB: maximum length of 65,535 bytes（63.99kb）
MEDIUMBLOB: maximum length of 16,777,215 bytes(16383.99kb)
LONGBLOB: maximum length of 4,294,967,295 bytes(4194303.99kb)

mysql数据库与java类型对应关系表

在这里插入图片描述

约束：

约束作用：保证数据的完整性

单表约束分类：

主键约束：primary key 主键约束默认就是唯一非空的

唯一约束：unique

非空约束：not null

创建表：

语法：create table 表名称(字段名称字段类型(长度) 约束,字段名称字段类型(长度) 约束…);

字段类型：一个实体对应一个表，一个实体属性对应表的一个字段。

create database web_test1;

use web_test1;

create table user(

	id int primary key auto_increment,

	username varchar(20) unique,

	password varchar(20) not null,

	age int,

	birthday date

);

查看表：

查看某个数据库下的所有的表：（必须先切换到这个数据库）

语法：show tables;

查看某个表的结构信息：

语法：desc 表名; （只是结构信息）

删除表：

语法：drop table 表名;

修改表：

添加列：alter table 表名 add 列名类型(长度) 约束;

修改列类型，长度和约束：alter table 表名 modify 列名类型(长度) 约束;

删除列：alter table 表名 drop 列名;

修改列名称：alter table 表名 change 旧列名新列名类型(长度) 约束;

修改表名：rename table 表名 to 新的表名;

修改表的字符集：alter table 表名 character set 字符集;

对数据库表的记录进行操作

添加表的记录：

语法：

向表中插入某些列：insert into 表名 (列名1,列名2,列名3…) values (值1,值2,值3…)

向表中插入所有列：insert into 表名 values (值1,值2,值3…);

//id一般是自动增长的，所以可以把值写为null

注意事项

1.值的类型与数据库中表列的类型一致。

2.值的顺序与数据库中表列的顺序一致。

3.值的最大长度不能超过列设置最大长度。

4.值的类型是字符串或者是日期类型，使用单引号引起来。

直接向数据库中插入中文记录会出现错误！

解决方法：

show variables like '%character%'; –查看数据库中与字符集相关参数（数据库中都是utf8，而命令行中的是gbk）所以需要将MySQL数据库服务器中的客户端部分的字符集改为gbk。MySQL数据库服务器中的服务端还是utf8，但是MySQL数据库服务器已经可以识别了。

2.找到MySQL的安装路径：my.ini文件，修改文件中[client]下的字符集，重新启动MySQL数据库服务器（在搜索程序和文件中输入 services.msc 打开服务界面，找到MySQL停止，再启动），再show variables like '%character%'; 就可以看到改成功了。

修改表的记录：

语法:update 表名 set 列名=值,列名=值 [where 条件];

注意事项

1.值的类型与列的类型一致。

2.值的最大长度不能超过列设置的最大长度。

3.字符串类型和日期类型添加单引号。

删除表的记录：

语法：delete from 表名 [where 条件];

注意事项

1.删除表的记录，指的是删除表中的一行记录。

2.删除如果没有条件，默认是删除表中的所有记录。

删除表中的记录有两种做法:

delete from user; 删除所有记录，属于DML语句，一条记录一条记录删除。事务可以作用在DML语句上的（可以回滚）

truncate table user;删除所有记录，属于DDL语句，将表删除，然后重新创建一个结构一样的表。事务不能控制DDL的（真的删除掉了）

查看表的记录：

基本查询：

语法:

select [distinct] *|列名 from 表 [条件];

[]代表可选，就是可加可不加。distinct表示不重复的

*|列名中的列名可以进行运算的。select name,english+chinese+math from exam;

也可以取个别名进行查询：

select name,english+chinese+math as sum from exam; （as有没有都可以）

条件查询：使用where子句：

> , < , >= , <= , <>（不等于） ,=

like:模糊查询（like可以进行模糊查询,在like子句中可以使用_或者%作为占位符。_只能代表一个字符，而%可以代表任意个字符。）

in:范围查询（in后面跟的是范围）

条件关联:and , or ,not （多个条件时可以使用）

排序查询：

使用order by 字段名称 asc/desc;

asc 升序 desc 降序（不写的话，默认是asc升序）

查询学生信息，先按照语文成绩进行倒序排序，如果成绩相同再按照英语成绩升序排序：select * from exam order by chinese desc,english asc;

分组统计查询：

聚合函数使用：（）括号中加对象

sum();

select sum(english)+sum(chinese)+sum(math) from exam;和select sum(english+chinese+math) from exam;的区别：

第一个语句是按照列的方式统计，英语成绩总和+语文成绩总和+数学成绩总和。

第二个语句先计算英语+数学+语文然后再求和。

//如果其中有一个值为null时，select sum(english+chinese+math) from exam;语句不会把那一行数据加上（null加上任意数还是null），select sum(english)+sum(chinese)+sum(math) from exam;语句就会正确相加。

如果某一列有null值，可以使用ifnull的函数：

select sum(ifnull(english,0)+chinese+math) from exam;如果english是null，赋值为0

count(); 统计个数 select count(*) from exam where name like ‘李%’;

max();

min();

avg();

count(*)和count(1)执行的效率是完全一样的。
count(*)的执行效率比count(col)高，因此可以用count(*)的时候就不要去用count(col)。
count(col)的执行效率比count(distinct col)高，不过这个结论的意义不大，这两种方法也是看需要去用。
如果是对特定的列做count的话建立这个列的非聚集索引能对count有很大的帮助。
如果经常count(*)的话则可以找一个最小的col建立非聚集索引以避免全表扫描而影响整体性能。
在不加WHERE限制条件的情况下，COUNT(*)与COUNT(COL)基本可以认为是等价的；
但是在有WHERE限制条件的情况下，COUNT(*)会比COUNT(COL)快非常多；
count(0)=count(1)=count(*)
\1. count(指定的有效值)–执行计划都会转化为count(*)

\2. 如果指定的是列名，会判断是否有null，null不计算

效率：count（*） > count（1） > count（id） > count（字段）

分组查询：

语法：使用group by 字段名称;

where的子句后面不能跟着聚合函数。如果现在使用带有聚合函数的条件过滤（分组后条件过滤）需要使用一个关键字having

select product,sum(price) from orderitem group by product having sum(price) > 5000;

总结

S(select)… F(from)…W(where)…G(group by)…H(having)…O(order by);

顺序不能颠倒，可以省略

#if分支；将查出来的多个结果拼接字符串；里外保持一致
SELECT
           	n.node_num,
            n.helmet_color,
            u.name,
            u.role,
		    n.user_id AS `type`,
       if(n.series=0,
           (select g.gateway_name from node n1 left join gateway g on g.id = n1.gateway_id WHERE n1.id=n.id ),
           (select group_concat(g.gateway_name)
            from node n1 left join bind_gateway bg on n1.id = bg.node_id left join gateway g on bg.gateway_id = g.id group by n1.id having n1.id=n.id)
           ) as gateway_name
        FROM node n
        LEFT JOIN user u ON u.id=n.user_id
        LEFT JOIN gateway g ON g.id=n.gateway_id
        WHERE n.company_id = 17
        AND n.user_id IS NOT NULL;

有更新，无添加

在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。另外，ON DUPLICATE KEY UPDATE不能写where条件

如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2，如果更新的数据和已有的数据一模一样，则受影响的行数是0

批量更新

1.批量update，一条记录update一次，性能很差

1	update test_tbl set dr='2' where id=1;

2.replace into 或者insert into …on duplicate key update

1	replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

或者使用

1	insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

3.创建临时表，先更新临时表，然后从临时表中update

这种方法需要用户有temporary 表的create 权限

1
2
3

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

MySQL的密码重置（设置）

也可以解决 Access denied for user ''@'localhost' to database 'mysql' 的问题：

windows：

停止MySQL的服务：在搜索程序和文件中输入 services.msc 打开服务界面，找到MySQL停止
在cmd下启动服务：mysqld –skip-grant-tables
重新开启cmd的命令行（前面那个不要关掉）：mysql -u root -p 直接登入，不需要密码

修改root密码：

1
2
3

use mysql； 

update user set password = password('sunweixuan') where user = 'root';

结束mysqld的进程:在任务管理器中的进程结束mysqld.exe
重新启动mysql的服务：在搜索程序和文件中输入 services.msc 打开服务界面，找到MySQL启动

linux：

1.关闭mysql数据库

service mysql stop

2.屏蔽权限

mysqld_safe –skip-grant-table

会显示Starting demo from ….. ，如果是一直在运行一个东西的话，先ctrl+c停止，在输入一遍命令。

停止mysql服务，查找含有mysqld的线程：ps -ef | grep mysqld 保证屏蔽权限的线程存在

3.新开起一个终端输入

mysql -u root mysql

mysql> update user set password=password('newpassword') where user='root';
mysql> flush privileges;//记得要这句话，否则如果关闭先前的终端，又会出现原来的错误
mysql> \q

如果还出现Access denied for user ''@'localhost' to database 'mysql'的问题，可能是是因为mysql数据库的user表里，存在用户名为空的账户即匿名账户，导致登录的时候是虽然用的是root，但实际是匿名登录的

1.关闭mysql
   # service mysqld stop
2.屏蔽权限
   # mysqld_safe --skip-grant-table
   屏幕出现： Starting demo from .....
3.新开起一个终端输入
   # mysql -u root mysql
   mysql> delete from user where USER='';
   mysql> FLUSH PRIVILEGES;//记得要这句话，否则如果关闭先前的终端，又会出现原来的错误
   mysql> \q

数据库的备份和还原

数据库的备份

mysqldump的所有参数：https://www.cnblogs.com/qq78292959/p/3637135.html

Mysql中数据备份使用的命令是:mysqldump命令来将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

–opt是quick，add-drop-table，add-locks，extended-insert，lock-tables几个参数的合称，一般都要使用

windows下备份数据库

1.打开cmd的命令行窗口（以管理员身份运行）

2.输入mysqldump -u root -p web_test1 >C:/web_test1.sql （这样c盘中就有了web_test1.sql ）（>和<是数据流向）

备份的语句mysqldump的基本语法: mysqldump -u username -p dbname table1 table2... >BackupName.sql; 接下来输入密码

dbname：要备份数据库的名称；

table1和table2：参数表示的是需要备份的数据库表的名称，假如为空则表示需要备份整个数据库；

BackupName.sql：表示的是将数据库备份到指定的这个以后缀名为.sql的文件中，这个文件的前面可以执行一个详细的绝对路径下；

数据库备份时进行的操作：

1.（如果存在表的话，将这个表删除）创建表结构

2.锁表

3.向表中插入备份的数据

4.解锁表

注意：文件的开头会记录MySQL的版本、备份的主机名和数据库名。文件中以“–”开头的都是SQL语言的注释，以”/*!40101”等形式开头的是与MySQL

有关的注释。40101是MySQL数据库的版本号，如果MySQL的版本比4.11高，则/*!40101和*/之间的内容就被当做SQL命令来执行，如果比4.1.1低就

会被当做注释

例子：

1 2	linux下的命令： /usr/bin/mysqldump --opt -h数据库地址 -u用户名 -p密码数据库名字 \| gzip > 备份的地址/备份的文件名.sql.zip

备份多个数据库

mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql

–databases用于指定多个数据库

备份所有的数据库操作

mysqldump -u username -p --all-databases > BackupName.sql

这里相对于之前的一个和多个数据库备份多添加了一个–all命令用于标识这是备份多个数据库的操作

直接复制整个数据库项目

MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。

不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造

成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。

注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。

使用mysqlhotcopy工具快速备份

热备份：mysqlhotcopy支持不停止MySQL服务器备份。而且，mysqlhotcopy的备份方式比mysqldump快。mysqlhotcopy是一个perl脚本，主要在Linux系统下使用。其使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份

原理：先将需要备份的数据库加上一个读锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。

Linux下的操作命令如下:

1	[root@localhost ~]# mysqlhotcopy [option] dbname1 dbname2 backupDir/

dbname：数据库名称；
backupDir：备份到哪个文件夹下；

mysqlhotcopy并非mysql自带，需要安装Perl的数据库接口包；下载地址为:http://dev.mysql.com/downloads/dbi.html

目前，该工具也仅仅能够备份MyISAM类型的表

数据库的还原

第一种：

1.在数据库服务器内部创建数据库：creat

2.在命令行窗口输入：mysql -u root -p web_test1 < C:/web_test1.sql

通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制

第二种：

1.在数据库服务器内部创建数据库：creat

2.切换到该数据库：use

3.使用source命令还原：source C:/web_test1.sql

数据库开启binlog日志

SHOW VARIABLES LIKE 'LOG_BIN%';
查询到数据库的log_bin是off状态

去服务器的/etc/my.conf文件里添加了：
#mysql5.7之后的版本要加一个id标识，因为是一个机器，所以随便设置即可（但是必须要有）
server-id=123454
# 启用binlog并设置binlog日志的存储目录
log_bin = /var/lib/mysql/bin-log
# 设置binlog索引存储目录
log_bin_index = /var/lib/mysql/mysql-bin.index
# 30天之前的日志自动删除
expire_logs_days = 30
# 设置binlog日志模式，共有3种模式：STATMENT、ROW、MIXED 推荐使用row模式
 binlog_format = row
 
修改后需要重启mysql（一定要重开命令行重启服务，不然会报错）：
service mysql restart
service mysqld restart

查看服务状态：systemctl status mysqld.service

MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:
其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。
二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件

查看mysql中的binlog日志内容

1、mysql查看binlog

mysql> show binlog events;   #只查看第一个binlog文件的内容
mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容
mysql> show binary logs;  #获取binlog文件列表
mysql> show master status; #查看当前正在写入的binlog文件

　2、使用mysqlbinlog工具

mysqlbinlog是一个查看mysql二进制日志的工具，可以把mysql上面的所有操作记录从日志里导出，这个工具默认的安装路径为：/usr/local/mysql/bin/mysqlbinlog

可以通过find / -name “mysqlbinlog”命令查找mysqlbinlog的工具路径。

基于开始/结束时间：

1	/usr/local/mysql/bin/mysqlbinlog --start-datetime="2013-03-01 00:00:00" --stop-datetime="2014-03-21 23:59:59" /usr/local/mysql/var/mysql-bin.000007 -r test2.sql

怎么查看binlog二进制文件

binlog本身是一类二进制文件。二进制文件更省空间，写入速度更快，是无法直接打开来查看的。
因此mysql提供了命令mysqlbinlog进行查看。
一般的statement格式的二进制文件，用下面命令就可以

1	mysqlbinlog mysql-bin.000001

如果是row格式，加上-v或者-vv参数就行，如

1	mysqlbinlog -vv mysql-bin.000001

将二进制文件转到文本中

1	mysqlbinlog -vv bin-log.000002 > mysql.txt

解析binlog格式

位置
位于文件中的位置，“at 294”说明“事件”的起点，是以第294字节开始；“end_log_pos 388 ”说明以第388 字节结束

时间戳
事件发生的时间戳：“120330 17:54:46”

事件执行时间
事件执行花费的时间:”exec_time=28”

错误码
错误码为：“error_code=0”

服务器的标识
服务器的标识id：“server id 1”

删除binlog

删binlog的方法很多，有三种是常见的
(1) 使用reset master,该命令将会删除所有日志，并让日志文件重新从000001开始。
(2) 使用命令

1	PURGE { BINARY \| MASTER } LOGS { TO 'log_name' \| BEFORE datetime_expr }

例如

1	purge master logs to "binlog_name.00000X"

将会清空00000X之前的所有日志文件.
(3) 使用–expire_logs_days=N选项指定过了多少天日志自动过期清空。

binlog常见参数

可视化工具

SQLyog、navicat等

MySQL的可视化工具有很多，基本上功能和内容都一样。

可以增删查改，可以执行SQL语句。

多表设计

约束是用来保证数据的完整性。

单表：主键约束、非空约束、唯一约束。

多表：外键约束：用来保证数据完整性（多表之间）

让两个表之间产生联系：

在员工表上添加外键：alter table employee add foreign key (dno) references dept(did); （添加外键dno指向dept表中的did列）

对外键进行操作类似于对表进行修改：设置外键为非空：alter table employee modify dno int not null;

表与表之间的关系：

\1. 一对多的关系（员工和部门）：

建表原则：在多的一方创建外键指向一的一方的主键

\2. 多对多的关系（学生和课程）：

建表原则：需要创建中间表，中间表中至少两个字段，分别作为外键指向多对多双方的主键

\3. 一对一的关系（公司和注册地址）：（很少见）

两种方式：

（1）唯一外键对应：假设是一对多，在多的一方创建外键指向一的一方的主键，将外键设置为unique。

（2）主键对应：将两个表的主键建立对应关系即可。

多表设计：

\1. 多表分析：分析每个类之间的关系

\2. 多表创建：创建各个表，添加外键使其建立关系，对于多对多的关系，需要再建立中间表。

\3. 多表查询：2类

（1）连接查询

l 交叉连接（了解）：查询到的是两个表的笛卡尔积（表1*表2=总记录条数）

语法：select * from 表1 cross join 表2;

select * from 表1,表2;

l 内连接：inner join （inner可以省略）

显示内连接：在SQL中显示的调用inner join关键字

语法：select * from 表1 inner join 表2 on 关联条件;

（select * from classes c inner join student s on c.cid = s.cno;）

隐式内连接：在SQL中没有调用inner join关键字

语法：select * from 表1,表2 where 关联条件;

（SELECT * FROM classes c,student s WHERE c.cid = s.cno;）

l 外连接：outer join （outer可以省略）

左外连接：语法：select * from 表1 left outer join 表2 on 关联条件;

左边表的全部和左右边共有的部分（左边表为基准）

右外连接：语法：select * from 表1 right outer join 表2 on 关联条件;

左边表的全部和左右边共有的部分（右边表为基准）

（2）子查询：一个查询语句条件需要依赖另一个查询语句的结果。（查询可以嵌套）

带in的子查询：select * from classes where cid in (SELECT cno FROM student WHERE birthday > ‘1991-01-01’); （先查询出后面的范围，再在后面的范围中查找前面的信息并输出）

带exists的子查询：select * from classes where exists (SELECT cno FROM student WHERE birthday > ‘1991-01-01’); （先查询后面的范围，如果存在，就对前面的语句进行查询；如果不存在，不执行前面的查询语句）

带any的子查询：SELECT * FROM classes WHERE cid > ANY (SELECT cno FROM student )；（大于后面中最小值的）

带all的子查询：SELECT * FROM classes WHERE cid > ALL (SELECT cno FROM student)；（大于后面中最大值的）

内连接与外连接的区别：左外连接和右外连接的交集就是内连接。

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值

Case具有两种格式。

简单Case函数

格式说明

　　　　case 列名

　　　　when 条件值1 then 选项1

　　　　when 条件值2 then 选项2…….

　　　　else 默认值 end

Case搜索函数

格式说明

　　　　case

　　　　when 列名= 条件值1 then 选项1

　　　　when 列名=条件值2 then 选项2…….

　　　　else 默认值 end

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

not in

对于第二个表中的某个字段不想要，只想取第一个表中这个字段值时，可以在查询时利用where条件限定id字段not in 第一个表的id字段。这样第二个表的查询结果会自动把第一个表中已经存在的id字段自动去除。

外键

MySQL支持外键的存储引擎只有InnoDB，子表的外键，必须是主表的主键,外键必须建立索引

设置了外键的表是从表，它关联的表被称为主表；给从表插入数据时，插入数据必须是主表的被关联字段出现的数据；先建主表再建从表；先删从表再删主表。

级联动作

1、cascade

数据级联删除、更新(参考字段)

2、restrict(默认)

从表有相关联记录,不允许主表操作。要删除主表必须先删除子表，要删除主表的记录必须先删除子表关联的记录，不能更新主表主键字段的值

3、set null

主表删除、更新,从表相关联记录字段值为NULL。有一个要求：设计从表时，外键（关联的主表）不能使用NOT NULL约束，否则会自动换为no action

4、no action

修改或删除主表，从表的数据不发生变化，此种策略需要存储引擎支持，如果存储引擎不支持，会自动换为RESTRICT

总结：

CASCADE用得最多，其次是RESTRICT，未设置外键关联策略时默认为RESTRICT(为了数据安全)

1.在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要求高，安全自己控制），不用外键；小系统随便，最好用外键。

2.用外键要适当，不能过分追求

3.不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库。

4.不管是否加外键，一定要索引。

严格模式

MySQL的严格模式，简单来说就是MySQL自身对数据进行严格的校验（格式、长度、类型等），比如一个整型字段我们写入一个字符串类型的数据，在非严格模式下MySQL不会报错，同样如果定义了char或varchar类型的字段，当写入或更新的数据超过了定义的长度也不会报错。

这个对于编程来说没有任何好处，虽然我们尽量在代码中做数据校验。MySQL开启了严格模式从一定程序上来讲师对我们代码的一种测试，如果我们的开发环境没有开启严格模式在开发过程中也没有遇到错误，那么在上线或代码移植的时候将有可能出现不兼容的情况，因此在开发过程做最好开启MySQL的严格模式

1 通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。　　

2 通过设置sql model 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql 进行较大的修改。　　

3 在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中。

查看当前是严格还是非严格模式

select @@sql_mode;
SHOW VARIABLES LIKE 'sql_mode';  

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

开启严格模式

方式一：先执行select @@sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，
然后执行set sql_mode = '修改后的值'或者set session sql_mode='修改后的值';，例如：set session sql_mode='STRICT_TRANS_TABLES';
改为严格模式此方法只在当前会话中生效，关闭当前会话就不生效了。

方式二：先执行select @@global.sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，
然后执行set global sql_mode = '修改后的值'。此方法在当前服务中生效，重新MySQL服务后失效

方法三：在mysql的安装目录下，或my.cnf文件(windows系统是my.ini文件)
新增 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
或者 sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
添加my.cnf如下：
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
然后重启mysql。
此方法永久生效.当然生产环境上是禁止重启MySQL服务的，所以采用方式二加方式三来解决线上的问题，那么即便是有一天真的重启了MySQL服务，也会永久生效了。

sql_mode常用值

ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中.

NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了

STRICT_TRANS_TABLES
在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制

NO_ZERO_IN_DATE
在严格模式下,不允许日期和月份为零

NO_ZERO_DATE
设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告

ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

PIPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

ANSI_QUOTES
启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

ORACLE的sql_mode设置

ORACLE的sql_mode设置等同:PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, 
NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

如果使用mysql,为了继续保留大家使用oracle的习惯,可以对mysql的sql_mode设置如下
在my.cnf添加如下配置
[mysqld]
sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PIPES_AS_CONCAT,ANSI_QUOTES'

MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的,5.6的mode是NO_ENGINE_SUBSTITUTION，其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。5.7的mode是STRICT_TRANS_TABLES，也就是严格模式

数据字典

数据字典存储有关数据的来源、说明、与其他数据的关系、用途和格式等信息，它本身就是一个数据库，存储“关于数据项的数据”

数据字典通常是指数据库中数据定义的一种记录，类似一个数据库的数据结构，但其内容要比数据库的数据结构描述丰富得多。在收集有关数据信息，建立数据库的初始阶段，必须建立数据项的命名约定，必须统一不同部门、不同个人之间对共同关心的数据的内涵、来源和命名的观念。这个过程要涉及数据监管人、用户和数据库开发人员，是一个需要反复多次的过程。这个统一的命名约定，及其附带的说明，就是数据字典。

数据库的重要部分是数据字典。它存放有数据库所用的有关信息，对用户来说是一组只读的表。

数据库数据字典是一组表和视图结构。它们存放在SYSTEM表空间中。

目的：

1)提高开发效率，降低研制成本。数据字典是数据库开发者、数据监管人和用户之间的共同约定，是系统说明书的一个重要组成部分。一个统一的数据字典有助于开发者建立数据模型以及程序和数据库之间的数据转换接口，为规范化设计和实施数据管理系统铺平了道路。
2)促进数据共享，提高数据的使用效率。通过数据字典，用户可以方便地知道每项数据的意义，了解数据的来源和使用方法，从而帮助用户迅速地找到所需的信息，并按照正确的方法使用数据。
3)控制数据的使用。在某些特定的场合，可以通过对数据字典的控制达到控制数据使用的目的。

数据字典（Data dictionary）是一种用户可以访问的记录数据库和应用程序元数据的目录

主动数据字典是指在对数据库或应用程序结构进行修改时，其内容可以由数据库管理系统自动更新的数据字典。被动数据字典是指修改时必须手工更新其内容的数据字典

两种形式：

第一种：《主体表》里包含主体和属性代码，《属性表》里包含属性代码和属性Value,不同属性分别建表

由于属性id是存储在主体表里的，属性的数量是不变的，而属性取值的数量可以是变化的。但是如果该主体的属性非常多的话，就需要建很多的属性表，在开发中还要设计很多属性类，那当想要取得一条主体的完全数据时，那将进行几十个表的联接（join）操作。性能耗损严重。当属性的数量不多时，用第一种数据字典即可

第二种：《主体表》里仅包含主体，《系统代码分类表》里存储属性标识和属性名称，《系统代码表》里包含所有属性代码、属性标识和属性Value，《属性表》是《主体》和《系统代码表》的关系表，包含属性id，主体id,属性代码

由于这种设计方式属性和主题表是分开的，所以属性的数量是可变的，而属性取值的数量可以是变化的。引入《系统代码分类表》和《系统代码表》，也解决了第一种设计方式的局限性

优点：

一、在一定程度上，通过系统维护人员即可改变系统的行为（功能），不需要开发人员的介入。使得系统的变化更快，能及时响应客户和市场的需求。

二、提高了系统的灵活性、通用性，减少了主体和属性的耦合度

三、简化了主体类的业务逻辑

四、能减少对系统程序的改动，使数据库、程序和页面更稳定。特别是数据量大的时候，能大幅减少开发工作量

五、使数据库表结构和程序结构条理上更清楚，更容易理解，在可开发性、可扩展性、可维护性、系统强壮性上都有优势。

缺点：

一、数据字典是通用的设计，在系统效率上会低一些。

二、程序算法相对复杂一些。