MySQL常用

写于2011年，mysql5.1

1、导入导出

  select * from f_pc_user_cndt_20101128 into outfile 'd:/f.txt' Fields Terminated By ',' Lines Terminated By '\n'
     LOAD DATA local INFILE 'd:/f.txt' IGNORE into table f_pc_user_cndt_20101128 Fields Terminated By ',' Lines Terminated By '\n';

2、启动

     /etc/init.d/mysqld-ib restart
     /usr/local/infobright/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

3、执行文件

  mysql -uroot -ppassword fdp < fdp.sql

4、登录

     /usr/local/mysql/bin/mysql -uroot -p123456
     mysql -uroot -h127.0.0.1 -pemaradmin

5、创建用户

create database cuixz;
GRANT ALL ON *.* TO cuixz@'%' IDENTIFIED BY 'cuixz'

6、查所有表

select trim(table_name), trim(table_comment), table_schema from information_schema.tables where table_schema in {0} order by table_schema, table_name

7、查表的列

select trim(column_name), trim(column_comment), column_type, case when left(column_key,1) = 'P' then 'Y' end, is_nullable from information_schema.columns where table_schema ='{0}' and table_name = '{1}' order by ordinal_position

8、查看变量：

SHOW VARIABLES LIKE 'character%'