Mysql常用命令

登录

mysql -u <user> -p<pwd>
mysql --default-character-set=utf8 -u <user> -p<pwd>

查看数据库的数据类型

SELECT * FROM information_schema.SCHEMATA where schema_name = "schemaname";

查看数据表的数据类型

SELECT CCSA.character_set_name 
FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA 
WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "schemaname" AND T.table_name = "table_name";

查看表列的数据类型

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.`COLUMNS` WHERE table_schema = "schemaname" AND table_name = "table_name";

查看所有视图

show table status where comment='view'; 

创建用户

CREATE USER 'root'@'localhost' IDENTIFIED BY '******';

删除用户

delete user
use mysql;
delete from user where user='t1';
FLUSH PRIVILEGES;

重新设置密码

SET PASSWORD FOR 'root'@'localhost' = PASSWORD( '123' );

默认编码utf8

vi /etc/my.cnf
5.1
[mysqld]
default-character-set=utf8

5.5
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
SHOW VARIABLES LIKE 'character_set_%'

重设自增ID

alter table regions AUTO_INCREMENT=1;

创建数据库

CREATE DATABASE `dbname` CHARACTER SET UTF8 COLLATE utf8_general_ci;

分配权限

grant all on dbname.* to root@localhost
grant all on *.* to 'root'@'%' identifiied by '***'

取消权限

revoke all on dbname.* to root@localhost

查看表的定义

desc users;
show create table users \G; # \G 记录按字段竖着排列</pre

查看变量

show variables;

备份

mysqldump -uroot -ppassword --default-character-set=utf8 --opt --master-data=1 --single-transaction --flush-logs db_name > db_name.sql
mysqldump -uroot -ppwd --default-character-set=utf8 --opt db_name > filename.sql

恢复

mysql -uroot -ppwd db_name < db_name.sql
with utf8:
mysql -uroot -ppwd --default-character-set=utf8 db_name < db_name.sql

查看帮助

? contents

杳看变量

show variables like 'time_zone'

返回当前数据库名

select database();

返回当前数据库版本

select version();

返回当前登录用户

select user();

返回大于x的最小整数值

CEIL(x); # 0.8 => 1

返回小于x的最小整数值

FLOOR(x); # 0.8 => 0

快速导入数据的方法

引用

http://www.zhaopin.net.ru/list.php/id/441

导出数据
show variables like 'max_allowed_packet';
show variables like 'net_buffer_length';

mysqldump -uroot -pXXX --skip-opt --create-option --set-charset --default-character-set=utf8  -e --max_allowed_packet=1048576 --net_buffer_length=8192 db_name > backup.sql

注意：max_allowed_packet和net_buffer_length不能比目标数据库的设定数值大，否则可能出错。
-e 使用包括几个VALUES列表的多行INSERT语法;
--max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小;
--net_buffer_length=XXX TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行。

导入数据
mysql -uroot -ppwd --default-character-set=utf8 db_name < backup.sql