mysql数据库添加字段实战_MySQL数据库从入门到实战（四）

——————————————————————

第一部分：字符集

——————————————————————

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 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;