mysql命令汇总

1.mysql新增、删除用户和权限分配

查看用户及权限

mysql>use mysql
mysql>select * from user\G;

新增用户

mysql>insert into mysql.user(Host,User,Password) values("localhost","hive",password("123456"));  
mysql>flush privileges;

修改密码

mysql>update mysql.user set password=password('new password') where User="hive" and Host="localhost";  
mysql>flush privileges; 

权限分配

 grant 权限 on 数据库.* to 用户名@'登录主机' identified by '密码'分配

权限：  
    常用总结, ALL/ALTER/CREATE/DROP/SELECT/UPDATE/DELETE  
数据库：  
     *.*                    表示所有库的所有表  
     test.*                表示test库的所有表  
     test.test_table  表示test库的test_table表       
用户名：  
     mysql账户名  
登陆主机：  
     允许登陆mysql server的客户端ip  
     '%'表示所有ip  
     'localhost' 表示本机  
     '192.168.10.2' 特定IP  
密码：  
      账户对应的登陆密码  

mysql>grant all  on test.* to hive@'%' identified by '123456';  
mysql>flush privileges;

　　就会拥有test库的所有操作权限

删除用户

mysql>DELETE FROM user WHERE User="hive" and Host="localhost";  
mysql>flush privileges; 

2.创建数据库和表

显示数据库

mysql> SHOW DATABASES; 
+----------+ 
| Database | 
+----------+ 
| mysql | 
| test | 
+----------+ 
3 rows in set (0.00 sec

创建数据库

mysql> CREATE DATABASE kang;

mysql> USE kang 

创建数据表

mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), 
-> birth DATE, birthaddr VARCHAR(20)); 

因为name、birthaddr的值是变化的，因此选择VARCHAR,其长度不一定是20.可以选择从1到255的任何长度，如果以后需要改变它的字长，可用ALTER TABLE语句。

显示数据表

mysql> SHOW TABLES;
mysql> DESCRIBE mytable;
mysql> select * from mytable;
mysql> show index from tblname;
mysql> show keys from tblname;　　

查看建表时间，表大小

USE information_schema
SELECT create_time FROM TABLES WHERE table_name ='inst_avgrankall1';

添加记录

mysql> insert into mytable values (′abccs′,′f′,′1977-07-07′,′china′); 
Query OK, 1 row affected (0.05 sec)　　

文本方式导入数据库表

可以用文本文件的方式将所有记录加入你的数据库表中。

创建一个文本文件“mysql.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以在CREATE TABLE语句中列出的列次序给出，例如： 

abccs f 1977-07-07 china 　 
mary f 1978-12-12 usa 

使用下面命令将文本文件“mytable.txt”装载到mytable表中:

字段之间用tab键隔开

mysql> LOAD DATA LOCAL INFILE "mytable.txt" INTO TABLE pet; 

再使用如下命令看看是否已将数据输入到数据库表中：

mysql> select * from mytable;

主键删除/添加

mysql>alter table tb add primary key(id);
mysql>alter table tb change id id int(10) not null auto_increment=1;
mysql>alter table tb change id id int(10);//删除自增长
mysql>alter table tb drop primary key;　　

删除表

mysql>drop table mytable;

清空表

mysql>delete from mytable;
mysql>truncate table mytable;

更新表

mysql>update mytable set sex="f"where name ='kxd';

导出数据库

mysqldump --opt test > mysql.test
即将数据库test数据库导出到mysql.test文件，后者是一个文本文件
如：mysqldump -u root -p123456 --databases dbname > mysql.dbname
就是把数据库dbname导出到文件mysql.dbname

导入数据库

mysqlimport -u root -p123456 < mysql.dbname

备份数据库

mysqldump -uroot 库名>xxx.data

远程连接

mysql -hIP -uroot -ppassword

改变字段长度

alter table user modify column name varchar(50) 

　添加自我增长的主键列

alter table `表格名` add column `列名` int not null auto_increment primary key comment '主键' first; 

insert into 数据表名(name,sex,age) values('val1',val2'','val3');

id 字段省去就可以了，但其余的不能省

增加字段：
    alter table   tablename    add   new_field_id   type   not null default '0'; 
    例： 
    alter table mmanapp_mmanmedia add appid_id integer not null default 372;

    增加主键：
    alter table  tabelname   add   new_field_id   type    default 0 not null auto_increment ,add   primary key (new_field_id); 

   增加外键： 在已经存在的字段上增加外键约束
    ALTER TABLE yourtablename    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)    REFERENCES tbl_name (index_col_name, ...)    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] 
 
删除字段:
    alter table    tablename    drop column      colname; 
     例： 
     alter table   mmanapp_mmanmedia    drop column     appid_id;

修改字段名：
     alter table     tablename    change   old_field_name    new_field_name  old_type;

修改字段类型：
    alter table     tablename     change    filed_name   filed_name   new_type; 

　　

出现问题：

mysql自增列的问题:Duplicate entry '0' for key 'PRIMARY' 代表出现相同的主键

建表参照脚本

CREATE TABLE IF NOT EXISTS isa_chxx_ext (
  ext_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID 主键 自动增长',
  ch_id int(11) NOT NULL COMMENT '储户ID 外键',
  zhbh varchar(20) NOT NULL COMMENT '账号',
  ext1 varchar(20) DEFAULT NULL COMMENT '扩展属性1',
  ext2 varchar(20) DEFAULT NULL COMMENT '扩展属性2',
  ext3 varchar(20) DEFAULT NULL COMMENT '扩展属性3',
  PRIMARY KEY (ext_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='储户信息扩展信息表' AUTO_INCREMENT=1 ;