MySQL数据类型以及元数据的使用

搜ijsio

第1章 Mysql数据类型
1.1 四中主要类别:
　　1.      数值类型
　　2.      字符类型
　　3.      时间类型
　　4.      二进制类型
1.2 数据类型三要素:
　　简介-完整-适当
1.3 数值数据类型:
　　类
　　类型
　　说明
　　整数
　　TINYINT
　　极小整数数据类型（0-255）
　　整数
　　SMALLINT
　　较小整数数据类型（-2^15 到2^15-1）
　　整数
　　MEDIUMINT
　　中型整数数据类型
　　整数
　　INT
　　常规（平均）大小的整数数据类型（-2^31 到2^31-1）
　　整数
　　BIGINT
　　较大整数数据类型（-2^63到2^63-1）
　　浮点数
　　FLOAT
　　小型单精度（四个字节）浮点数
　　浮点数
　　DOUBLE
　　常规双精度（八个字节）浮点数
　　定点数
　　DECIMAL
　　包含整数部分、小数部分或同时包括二者的精确值数值
　　BIT
　　BIT
　　位字段值
1.4 字符串数据类型:
　　类
　　类型
　　说明
　　文本
　　CHAR
　　固定长度字符串，最多为255 个字符
　　文本
　　VARCHAR
　　可变长度字符串，最多为65,535 个字符
　　文本
　　TINYTEXT
　　可变长度字符串，最多为255 个字符
　　文本
　　TEXT
　　可变长度字符串，最多为65,535 个字符
　　文本
　　MEDIUMTEXT
　　可变长度字符串，最多为16,777,215 个字符
　　文本
　　LONGTEXT
　　可变长度字符串，最多为4,294,967,295 个字符
　　整数
　　ENUM
　　由一组固定的合法值组成的枚举
　　整数
　　SET
　　由一组固定的合法值组成的集
1.5 二进制字符串数据类型:
　　类
　　类型
　　说明
　　二进制
　　BINARY
　　类似于 CHAR（固定长度）类型，但存储的是二进制字节字符串，而不是非二进制字符串
　　二进制
　　VARBINARY
　　类似于 VARCHAR（可变长度）类型，但存储的是二进制字节字符串，而不是非二进制字符串
　　BLOB
　　TINYBLOB
　　最大长度为255 个字节的 BLOB 列
　　BLOB
　　BLOB
　　最大长度为65,535 个字节的 BLOB 列
　　BLOB
　　MEDIUDMBLOB
　　最大长度为16,777,215 个字节的 BLOB 列
　　BLOB
　　LONGBLOB
　　最大长度为4,294,967,295 个字节的 BLOB 列
1.6 时间数据类型:
　　类型
　　格式
　　示例
　　DATE
　　YYYY-MM-DD
　　2017-12-16
　　TIME
　　hh:mm:ss[.uuuuuu]
　　12:59:02.123456
　　DATETIME
　　YYYY-MM-DD   hh:mm:ss[.uuuuuu]
　　2017-12-16   12:59:02.123
　　TIMESTAMP
　　YYYY-MM-DD   hh:mm:ss[.uuuuuu]
　　2017-12-16   12:59:02.12
　　YEAR
　　YYYY
　　2017
1.7 列属性:就是对数据类型进行规范的
1.7.1 列属性的类别:
　　数值:适用于数值数据类型
　　字符串:使用与非二进制字符串数据类型
　　常规:适用于所有数据类型
　　数据类型
　　属性
　　说明
　　数  值
　　UNSIGNED
　　禁止使用负值
　　仅整数
　　AUTO_INCREMENT
　　生成包含连续唯一整数值的序列
　　字符串
　　CHARACTER SET
　　指定要使用的字符集
　　字符串
　　COLLATE
　　指定字符集整理
　　字符串
　　BINARY
　　指定二进制整理
　　全 部*
　　NULL 或 NOT NULL
　　指示列是否可以包含 NULL 值
　　全  部
　　DEFAULT
　　如果未为新记录指定值，则为其提供默认值
　　设置主键,一个表只能有一个,非空并唯一的列可以设置为主键
　　如果业务中没有非空和唯一条件的列,我们可以定义一个无关列,最好是数字列
mysql> create table test (pid int not null primary key,pname varchar(10),page tinyint,pgen enum('W','M'));　　非负:
mysql> create table test_1 (pid int not null primary key AUTO_INCREMENT,pnianling int UNSIGNED not null);　　唯一:
mysql> create table people (id int not null primary key,name varchar(10) not null unique);　　非空,且不输入此行的话默认填写值为Y
mysql> create table jins (id int not null,name enum('n','y') default 'y');　　
mysql> insert into jins values (1,'n');
　　
Query OK, 1 row affected (0.26 sec)
mysql> select * from jins;　　
+----+------+
　　
| id | name |
　　
+----+------+
　　
|  1 | n    |
　　
+----+------+
　　
mysql> insert into jins(id) values (2);
　　
mysql> select * from jins;
　　
+----+------+
　　
| id | name |
　　
+----+------+
　　
|  1 | n    |
　　
|  2 | y    |
　　
+----+------+
1.7.2 数据类型小结:
　　1.      使用固定长度数据类型:
　　如果存储的所有字符串值的长度相同
　　2.      使用可变长度数据类型:
　　如果存储的字符串值不同,对于多字节字符集
　　3.      对于频繁使用的字符,使用占用空间较少的多字节字符集
　　使用基本多文种平面之外的其他字符集
第2章 元数据:
2.1 什么是元数据:
　　1.      数据库;表对象的一些定义信息都以可以称之为元数据
　　2.      数据库的一些状态统计
2.2 元数据访问方法:
　　1.      查询information_schema数据库表,期中包含Mysql数据库服务器所管理的所有对象的相关数据
　　2.      使用show语句,用于获取数据库和表信息的mysql专用语句
　　3.      使用desc语句,查看表结构和列属性的快捷方式
　　4.      使用msyqlshow客户端程序,show语法的命令行程序
2.3 information_schema数据库优点介绍:
　　1.      充当数据库元数据的中央系统信息库,模式和模式对象,服务器统计信息(状态变量,设置,连接)
　　2.      采用表格式以实现灵活访问,使用任意select语句,是虚拟数据库,表并非真实的表,而是系统视图,根据当前用户的特权动态填充表
　　3.      列出当前数据库中那个所有的表:
mysql> use information_schema;　　
Database changed
　　
mysql> show tables;
2.4 元数据的使用:
2.4.1 查找引擎是innodb的表

1.1.1 查找数据类型是set的表

1.1.1 查看每个数据库下表的个数:

1.1 Mysql全库级别分表批量备份:
1.1.1 指定安全文件的位置
vim /etc/my.cnf　　
secure-file-priv=/tmp
1.1.2 生成批量备份的文件
select concat("mysqldump ", "-uroot", " -p123 ",TABLE_SCHEMA," ",TABLE_NAME," >/bak/",TABLE_SCHEMA,"_",TABLE_NAME,".sql")from information_schema.tables into outfile '/tmp/tabbak.sh';1.2 mysql中show语句总结:
　　show databases : 列出所有数据库
　　show tables : 列出默认所有数据库
　　show tables from  : 列出指定数据库中的表
　　show columns from  : 显示表的列结构
　　show index from  : 显示表中有关索引和索引列的信息
　　show charaater set : 显示可用的字符集机器默认整理
　　show status : 列出当前数据库的状态
　　show variables : 列出数据库中的参数定义值
　　show processlist : 查看当前的连接数量
1.3 如何统计数据库中所有表的占用量
SELECT　　
CONCAT(table_schema, '.', table_name) AS "Name"
　　
,CONCAT(ROUND(table_rows / 1000, 2), 'K') AS "Rows"
　　
,CONCAT(ROUND(data_length / ( 1024 * 1024  ), 2), 'M') AS "Row Size"
　　
,CONCAT(ROUND(index_length / ( 1024 * 1024  ), 2), 'M') AS "Index Size"
　　
,CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024  ), 2), 'M') AS "Total"
　　
,ROUND(index_length / data_length, 2) "Row / Index Ratio"
　　
FROM information_schema.TABLES
　　
ORDER BY data_length + index_length DESC
　　
LIMIT 10;
1.4 等待时间查看:
SELECT　　
r.trx_id waiting_trx_id
　　
,r.trx_mysql_thread_id waiting_thread
　　
,r.trx_query waiting_query
　　
,b.trx_id blocking_trx_id
　　
,b.trx_mysql_thread_id blocking_thread
　　
,b.trx_query blocking_query
　　
FROM information_schema.innodb_lock_waits w
　　
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
　　
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;