MySQL特有的命令 - 2

MySQL事务并发访问

MySQL支持的事务隔离级别

1、read UNCOMMITTED -- 读未提交，最低的隔离级别，脏读、不可重复读、幻读都会出现
2、read COMMITTED -- 读已提交，二级隔离级别，解决脏读，但不可重复读、幻读仍会出现 --->Oracle默认隔离级别
3、REPEATABLE read -- 可重复读，三级隔离级别，解决脏读、不可重复读，但幻读仍会出现 --->MySQL默认隔离级别
4、serializable -- 可串行话，四级隔离级别，解决脏读、不可重复读、幻读

设置MySQL的事务隔离级别

set transaction ISOLATION LEVEL 隔离级别名称  -- 会话隔离级别的修改
set GLOBAL transaction ISOLATION LEVEL 隔离级别名称 -- 全局隔离级别的修改

• 设置事务的提交方式：set auto_commit = 0 | 1;

• 显式开启事务：BEGIN 或 START TRANSACTION;

• 显式事务提交： COMMIT;

• 显式事务回滚： ROLLBACK;

查看MySQL的安装路径

show VARIABLES like ‘base%’;

例如我自己的是C:\Program Files\MySQL\MySQL Server 5.7\

查看MySQL的数据目录

MySQL的全局配置文件及数据库中的表文件都在数据目录下

show VARIABLES like ‘data%’;

C:\ProgramData\MySQL\MySQL Server 5.7\Data\

MySQL的数据类型

主要包括五大类：整数类型、小数类型、日期和时间类型、字符串类型、二进制数据类型
使用：表的结构定义、存贮过程中的局部变量

一、整数类型

TINYINT | SMALLINT | MEDIUMINT | int(integer) | BIGINT （INTEGER、SMALLINT标准SQL语言）

类型	字节数
TINYINT	1Byte ：128 ~ 127，无符号0~255
SMALLINT	2Byte ：32768 ~ 32767
MEDIUMINT	3Byte：8开头的7位数
INT、INTEGER	4Byte ：2开头的10位数
BIGINT	8Byte

查看int类型：HELP int; (只能在专属客户端使用)

二、小数类型

• 浮点数类型: FLOAT | DOUBLE
• 定点数类型: DECIMAL

类型	字节数
FLOAT	4Byte
DOUBLE	8Byte
DECIMAL(M,D)、DEC(M,D)	M+2 Byte （MySQL中M的长度不包括小数点的长度 ）

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值(以字符串存贮)。

设一个字段定义为float(6,3)，如果插入一个数 123.45678,实际数据库里存的是 123.457,但总个数还以实际为准，即6位。
整数部分最大是3位，如果插入数 12.123456,存储的是 12.1234,如果插入 12.12,存储的是 12.1200(整数位+小数位=6)。

三、日期和时间类型:

YEAR | DATE | TIME | DateTime | TimeStamp

YEAR	年，1Byte， 1901~2155 年
DATE	日期，4Byte
TIME	时间，3Byte
DateTime	日期时间，8Byte
TimeStamp	日期时间，4Byte

	TimeStamp:默认值非空,时间数据会随其他字段修改的时候自动刷新,可以存放这条记录最后被修改的时间。

MySQL的日期时间函数

-- 获得当前日期+时间（date + time）函数：now()
	SELECT NOW(); -- 2020-03-24 12:06:49
	
	SELECT YEAR(NOW()); -- 2020
	SELECT MONTH(NOW()); -- 3
	SELECT DAY(NOW()); -- 24

• MySQL 为日期增加一个时间间隔：date_add()

• MySQL 为日期减去一个时间间隔：date_sub()

• MySQL 日期、时间相减函数：datediff(date1,date2)、timediff(time1,time2)

四、字符串类型

CHAR(定长) | VARCHAR(变长) | TINYTEXT | TEXT(大文本型) | enum(枚举型) | set(集合型)

char 和 varchar 类型类似,但它们保存和检索的方式不同.它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
char 型数据存储时按指定宽度储存，如果实际数据不足，则存储时后面补足空格，但读取时自动将空格去掉，因此无法保留尾部空格。

char: 0~255, char(5)    ---占5个字符对应的字节数, 处理速度比 varchar 快。
varchar: 0~65535, varchar(5)   ---占实际字符数+1个字节 

以上定义的5不是表示字节数，而是字符数

在MySQL5.0以上版本：中文：utf-8—3字符 GBK—2字符

• LENGTH(str)：返回字符串str的长度，以字节为单位。
• CHAR_LENGTH(str)：返回字符串str的长度，以字符为单位。

*思考：存贮内容、存贮字符数、读取内容

• TEXT型：没有默认值

    TINYTEXT： 0~255，占用空间 ：字符长度+2个字节
    TEXT： 0~65535，占用空间 ：字符长度+2个字节
    MEDIUMTEXT ： 0~(2^24-1)，占用空间 ：字符长度+3个字节
    LONGTEXT ： 0~(2^32-1)，占用空间 ：字符长度+4个字节
  

• enum型：取值范围由定义时列举的值的范围决定，且只能取列举值中的一个，枚举值只能为char型。每一个枚举值都有一个顺序编号，数据库存入的是编号，而不是列举的值，第一个枚举值为1，以此类推。

	drop table if exists test;
	CREATE TABLE test(id int auto_increment PRIMARY KEY ,name VARCHAR(6), gender enum('男','女') DEFAULT '男');

	SELECT * FROM test;
	INSERT INTO test(name,gender) VALUES('aaa','男');
	INSERT INTO test(name,gender) VALUES('aaa','女');
	INSERT INTO test(name,gender) VALUES('aaa','中'); -- 出错
	SELECT * FROM test WHERE gender=1; -- 使用编号查询

• set集合型：集合的元素只能是字符，元素末尾的空格会自动截掉，取一个或多个元素，以 , 间隔。

	drop table if exists test;
	CREATE TABLE test(id int auto_increment PRIMARY KEY ,cno VARCHAR(6),tNo set('t01','t02','t03'));
	INSERT INTO test VALUES(1,'c01',('t01'));
	INSERT INTO test VALUES(2,'c02','t01,t03'); -- 注意插入集合中多个元素的表示方法
	SELECT * FROM test;

五、二进制数据类型

BINARY | VARBINARY | bit | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB

• *思考：存贮时、读取时、是否区分大小写

    BINARY(M): 字节数为 M ,0~M长度的定长二进制字符串，补全用'\0'---空字符
    VARBINARY(M): 0~M长度的变长二进制字符串，字节数为实际值的字节数+1
  

• BINARY | VARBINARY 与 CHAR | VARCHAR 相似，都是字符型数据的存储方式，但储存的是字符串的二进制数据。

MySQL中有对应的字符型二进制的存储集合与排序规则，排序是基于字节的值进行的。

BINARY	CHAR
二进制字符串	字符
不足末尾加’\0’	不足末尾加空格
读取时保留’\0’	读取时截掉尾部空格

• CHAR 与 VARCHAR

SELECT ‘a’=‘a’,‘a’='a ',‘a’=‘A’,‘a’='A ’ from dual;

结果全为1，CHAR与VARCHAR不区分大小写，读取时截掉尾部空格

	drop table if exists test;
	CREATE TABLE test(name CHAR(6),col2 VARCHAR(6) DEFAULT 'abc' not NULL);
	SELECT * FROM test;
	INSERT INTO test(name) VALUES('mary');
	SELECT name='mary',name='mary  ',name='MARY',name='MARY  ' from test;

-- binary
	drop table if exists test;
	CREATE TABLE test(id int, col BINARY(3));
	
	INSERT INTO test VALUES(1,'abc');
	INSERT INTO test VALUES(1,'中');  -- 汉字的二进制编码(看起来像是乱码，但是存储进去了)
	INSERT INTO test VALUES(1,'a'); -- 实际存储了'a\0\0'
	INSERT INTO test VALUES(1,'中国人'); -- 出错，超过3个字节，共9个字节，'\0'(值为0)不是空格字符' '
	SELECT col='a', col='a  ', col='a\0\0' FROM test; -- 0  0  0
	
	SELECT LENGTH(col) from test;  -- 3 ,length()测binary类型数据时测得的是字节数
	
	SELECT * from test;
	SELECT * from test WHERE col='a\0\0'; -- 可以找到
	SELECT * from test WHERE col='a'; -- 找不到
	SELECT * from test WHERE col='A\0\0'; -- 找不到，binary和varbinary区分大小写
	SELECT * from test WHERE col='中'; -- 可以找到

• bit(M)：M位二进制数，0~64 B
  bit(4):：0000 ~ 1111 即 0 ~ 15 ，存放的数据范围：0 ~ (2^M) - 1的数

• 以下blob存放的都是二进制数据(较大的二进制数据)，如图片、声音、视频
  TINYBLOB： 变长二进制数据，0~255 B
  BLOB：变长二进制数据，0~65535 B 即 64 K
  MEDIUMBLOB： 变长二进制数据，0 ~ (2²⁴-1)B 即 16 M
  LONGBLOB： 变长二进制数据，0 ~ (2³²-1)B 即 4 G

	drop table if exists test;
	CREATE TABLE test(id int,pic MEDIUMBLOB);
	show variables like 'datadir%';
	INSERT into test VALUES(1,LOAD_FILE('D:/1.jpg'));
	SELECT * FROM test;
	SELECT pic FROM test;

MySQL关键字及含义

关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集