MySQL数据库从入门到实战（四）

ifuleyou

　　——————————————————————
　　第一部分：字符集
　　——————————————————————
　　show charset;   ---查看mysql支持字符集
　　1、服务器端
　　(1)实例级别
　　vim my.cnf
　　character-set-server=utf8
　　(2)库级别
　　create database oldboy charset UTF8;
　　create database oldboy1 charset gbk;
　　alter database oldboy charset utf8mb4;
　　show create database oldboy;
　　(3)表级别
　　create table t1 (id int ,name varchar(20)) default charset utf8;
　　show create table  t1;
　　2、客户端
　　alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;
　　alter table t1 CHARACTER SET latin1;
　　官方定义，修改后的字符集必须是之前字符集的严格超集。
　　————————————————————————————————
　　第二部分：列属性
　　————————————————————————————————
　　1）约束类：
　　主键 ：
　　一个表只能有一个，非空并唯一的列可以设置为主键
　　create table test (id int not null primary key,name varchar(20));
　　或者
　　CREATE TABLE test (
　　id int(11) NOT NULL,
　　name varchar(20) DEFAULT NULL,
　　PRIMARY KEY (id)
　　);
　　

    如果业务中没有，非空和唯一条件的列。我们可以人为定义一个无关列，最   好是数字列。　　create table ptab (
　　pid int not null primary key AUTO_INCREMENT,
　　pname varchar(20),
　　page tinyint,
　　pgen enum('M','F'));
　　

　　非空：
　　create table teacher (
　　id int not null primary key ,
　　name varchar(20) not null ,
　　state enum('y','n') not null default 'y');
　　 唯一：
　　create table people (
　　p_id char(20) not null primary key,
　　name varchar(20) not null unique ,
　　age tinyint not null);
　　非负数
　　（2）其他
　　自增长  --- AUTO_INCREMENT
　　默认值  ---default 'y'
　　utf8   字符     ---->3字节
　　utf8mb4  字符   ---->4个字节
　　——————————————————————————————
　　第三部分：元数据获取---Informatica_schema
　　——————————————————————————————
　　什么是元数据？
　　1、数据库、表对象的一些定义信息都可以把它称之为元数据
　　2、数据库的一些状态统计
　　元数据是存放到数据库的系统表（基表）
　　利用视图批量操作实例：
　　1.批量备份脚本拼接
　　vim /etc/my.cnf
　　secure-file-priv=/tmp
　　mkdir /bak
　　select concat("mysqldump ", "-uroot", " -p123 ",TABLE_SCHEMA," ",TABLE_NAME," >/bak/",TABLESCHEMA,"",TABLE_NAME,".sql")from information_schema.tables into outfile '/tmp/tabbak.sh';
　　2.批量创建world 表结构相同的表
　　创建语句：
　　CREATE TABLE world.city_0 LIKE world.city;
　　USE world
　　SHOW TABLES;
　　批量操作：
　　SELECT CONCAT("create ","table ",TABLE_SCHEMA,"."
　　,TABLE_NAME," "
　　,"like ",TABLE_SCHEMA,".",TABLE_NAME,"_0")
　　FROM  information_schema.tables
　　WHERE TABLE_SCHEMA='world';
　　3.information_schema 企业需求案例
　　统计数据量
　　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>

　　,CONCAT(ROUND(index_length / ( 1024  1024  ), 2), 'M') AS "Index>　　,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;
　　等待事件查看
　　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;