[SQL]mysql命令学习备忘

• 登陆：

>mysql -h 127.0.0.1  -utest -p
-h为主机，可省略
-p为密码，没有密码可省略，如果有密码可以直接跟-u一样跟在后面，但不推荐这样写，因为暴露了密码。
e.g. 以test用户登陆，密码为空
>mysql -utest

====================================================================

• 退出：

>quit

====================================================================

• 添加用户：

以root用户登陆，其他用户可能没有权限
>quit
>mysql -uroot -p
>password:******
>insert into user (user,host,password) values ('test','%',password('12345'));//用password函数进行md5运算，不直接把密码明文暴露出来

====================================================================

• 修改用户权限:

如设置用户test只对数据库test具有某些权限
>grant select,update,insert on test.* to 'test' @'%' identified by '12345';
允许用户test对数据库test具有select ,update, insert权限，@后面跟主机地址，%表示全部，identified by后面跟密码
如要使用户test对数据库test具有全部的读写权限：
>grant all privileges on test.* to 'test' @'%' identified by '12345';
>flush privileges

====================================================================

• 一些信息查看

>show databases;   //查看所有数据库
>show tables;            //查看所有表
>describe tableName;   //查看某个表的属性，包括各个字段类型等。

====================================================================

• 切换数据库：

>use dbName;

====================================================================

• 备份数据库

c:/>mysqldump -utest -p --opt test > d:/test2.sql
PASSWORD:******

====================================================================

• 修改表名

>alter table t1 rename to t3;
====================================================================

• INSERT … ON DUPLICATE KEY UPDATE

t1      CREATE TABLE `t1` (                     
          `c1` int(4) NOT NULL,                 
          `c2` int(4) default NULL,             
          PRIMARY KEY  (`c1`)                   
        ) ENGINE=MyISAM DEFAULT CHARSET=gb2312

原始数据
c1   c2
1    100
2    200

c1是主键，欲插入(1,1),如果已经存在c1=1，则c2自增,（类似于计数器）

INSERT INTO t1 VALUES(1, 1) ON DUPLICATE KEY UPDATE c2=c2+1;

执行以上语句后的数据
c1   c2
1    101
2    200

该语句等价于

if(SELECT * FROM t1 WHERE c1=1){
    UPDATE t1 SET c2=c2+1 WHERE c1=1;
}else{
    INSERT INTO t1 (c1, c2) VALUES (1, 1);
}

====================================================================

• 输出查询结果到本地文件/从本地文件导入数据到table中

mysql> SELECT * INTO OUTFILE 'data.txt'

    ->          FIELDS TERMINATED BY ','

    ->          FROM table2;

要读取由逗号分隔的文件并返回，则正确的语句应该是：

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2

    ->           FIELDS TERMINATED BY ',';

====================================================================

• 查看建表信息

mysql>show describe table sc;
Table   Create Table  
------  -------------------------------------------------------------------
sc      CREATE TABLE `sc`                                  (                                                                                                                                                                                                                                                                               
          `sid` char(10) NOT NULL,                                                                                                                                                                                                                                                                        
          `cid` char(10) NOT NULL,                                                                                                                                                                                                                                                                        
          KEY `sid` (`sid`),                                                                                                                                                                                                                                                                              
          KEY `cid` (`cid`),                                                                                                                                                                                                                                                                              
          CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `s` (`sid`),                                                                                                                                                                                                                              
          CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `c` (`cid`)                                                                                                                                                                                                                               
        ) ENGINE=InnoDB DEFAULT CHARSET=gb2312        
====================================================================

• 执行外部.sql 文件

mysql > source F:/b.sql;

====================================================================

• 结束当前输入

mysql > abcd
            >/c
mysql >

====================================================================