mysql 增删改查

mysql实时监控工具：
//create database xixi_demo default charset utf8;

//alter table game modify `image` varchar(100) not null default "" after `index`;

//create unique index uni_test6 on test6(object_id);

//alter table game add `index` int not null default 0 after name;

//alter table game character set utf8;

//alter table game modify id bigint not null primary key auto_increment;

//alter table game default charset utf8;

//show create table game;

//alter table game engine=innodb;

//创建表
CREATE TABLE `user` (
  `user_id` bigint(20) unsigned NOT NULL,
  `flyme` varchar(50) NOT NULL DEFAULT '',
  `phone` varchar(50) NOT NULL DEFAULT '',
  `email` varchar(50) NOT NULL DEFAULT '',
  `nickname` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`),
  KEY `INX_FLYME_NORMAL` (`flyme`) USING BTREE,
  KEY `INX_EMAIL_NORMAL` (`email`) USING BTREE,
  KEY `INX_NICKNAME_NORMAL` (`nickname`) USING BTREE,
  KEY `INX_PHONE_NORMAL` (`phone`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

###########################################################
mysqladmin password 123456

mysql -uroot -p123456 -e "use mysql;select Host,Password,User from user;"
mysql -uroot -p123456 -e "use mysql;delete from user where Password <> Password('123456')"
mysql -uroot -p123456 -e "use mysql;update user set Host='%' where User='root'"
mysql -uroot -p123456 -e "use mysql;select Host,Password,User from user;"


mytop
orztop

1、进去指定schema 数据库（存放了其他的数据库的信息）
  www.2cto.com 
1
use information_schema
2、查询所有数据的大小

1
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES
3、查看指定数据库的大小
  www.2cto.com 
比如说 数据库apoyl

1
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='apoyl';

4、查看指定数据库的表的大小

比如说 数据库apoyl 中apoyl_test表

1
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='apoyl' and table_name='apoyl_test';