非空:
- 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 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;
等待事件查看
- 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;
来源: http://www.bubuko.com/infodetail-2549531.html