mysql技巧总结

使用Mysql也很长一段时间了，在使用的过程中遇到过各种各样的问题，有些看上去很小的问题，在解决的过程中却花了很长时间，解决后才发现，原来这样简单。所以总结了一些经验和技巧，把它写出来，希望对读者能有所帮助，少走弯路。
1. binary
BINARY不是函数，是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写。举例：
mysql> select * from tjob;
+-------+
| title |
+-------+
| 11    |
| 22    |
| 33    |
| aa    |
| bb    |
| cc    |
| AA    |
| BB    |
| CC    |
+-------+
9 rows in set (0.00 sec)

mysql> select title from tjob where title like 'a%';
+-------+
| title |
+-------+
| aa    |
| AA    |
+-------+
2 rows in set (0.00 sec)

mysql> select title from tjob where binary title like 'a%';
+-------+
| title |
+-------+
| aa    |
+-------+
1 row in set (0.00 sec)

mysql> select title from tjob where binary title like 'A%';
+-------+
| title |
+-------+
| AA    |
+-------+
1 row in set (0.00 sec)
2.group by with rollup
在group by结果的最后一行，列出所有行的总和，如下，多了最后一行
mysql> select count(*),title from tjob group by title with rollup;
+----------+-------+
| count(*) | title |
+----------+-------+
|        1 | 11    |
|        1 | 22    |
|        1 | 33    |
|        2 | aa    |
|        2 | bb    |
|        2 | cc    |
|        9 | NULL  |
+----------+-------+
7 rows in set (0.00 sec)
3.group_concat
group_concat合并行，例如把title字段的值打印在一行，逗号分隔。
mysql> select group_concat(title separator ',') from tjob;
+-----------------------------------+
| group_concat(title separator ',') |
+-----------------------------------+
| 11,22,33,aa,bb,cc,AA,BB,CC        |
+-----------------------------------+
1 row in set (0.00 sec)
4.order by if
排序时将某列中特定值排在最前，例如我在排序中要把aa放在最前面：
select title from tjob order by if(title='aa',0,1),title;
+-------+
| title |
+-------+
| aa    |
| AA    |
| 11    |
| 22    |
| 33    |
| bb    |
| BB    |
| cc    |
| CC    |
+-------+
5.prompt
如果有很多mysql数据库管理，有时会发生自己忘记了在那台服务器，那台数据库上，毋庸多说，谁都知道这有多危险。可以使用prompt来重写mysql客户端提示符。
mysql>prompt \u@\h(\d) \r:\m:\s>
\u:连接用户
\h:连接主机
\d:连接数据库
\r:\m:\s:显示当前时间

如果嫌每次都在命令行修改麻烦，直接修改my.cnf参数
[mysql]    #这是不是[mysqld]  
prompt=\\u@\\d >
6.pager
如果select出来的结果集超过几个屏幕，那么前面的结果一晃而过无法看到。使用pager可以设置调用os的more或者less等显示查询结果，和在os中使用more或者less查看大文件的效果一样。
mysql> pager more
PAGER set to 'more'
mysql> \P more
PAGER set to 'more'

mysql> pager less
PAGER set to 'less'
mysql> \P less
PAGER set to 'less'

mysql> nopager
PAGER set to stdout
7.delimiter
delimiter就是告诉MySQL解释器,命令的结束符是什么。
默认情况下MySQL命令结束是以分号(;)，在写过程或者函数等情况下，这会产生不少问题，因为存储过程中有许多语句，所以每一个都需要一个分号。因此你需要选择一个不太可能出现在你的语句或程序中的字符串作为分隔符。
mysql> delimiter $
mysql> select count(*) from tjob$
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

想改回到分号，只需
mysql> delimiter ;
8.load data local infile
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS      //字段
        [TERMINATED BY 'string']   //字段分割符
        [[OPTIONALLY] ENCLOSED BY 'char']//字段的包含符，默认是''（空）
        [ESCAPED BY 'char' ]       //转义符，如'\\'
    ]
    [LINES
        [STARTING BY 'string']     //行的开始字符串，从string后面开始
        [TERMINATED BY 'string']   //行分隔符,默认是'\n'(空行)
    ]
    [IGNORE number LINES]          //跳过前面number行
    [(col_name_or_user_var,...)]   //字段名称
    [SET col_name = expr,...)]
使用load data导入数据，看起来是很简单，但是却经常容易出错。比如，需要导入的文本有时候并不是以’\n’(空行)结尾，不细心的话很容易出错。有时候导入的数据有列的信息，则可以使用IGNORE number LINES跳过前面指定行数等。
9.unix_timestamp and from_unixtime
unix_timestamp将时间转化成时间戳格式。
from_unixtime将时间戳转化成时间格式。
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1251884321 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1251884321);
+---------------------------+
| from_unixtime(1251884321) |
+---------------------------+
| 2009-09-02 17:38:41       |
+---------------------------+
1 row in set (0.00 sec)
很多时候mysql的应用都是使用时间戳格式来存储时间，这两个函数应用的场合很多，需要牢记。

 

 

 

1.修改mysql中root的密码：
shell>mysql -u root -p
mysql>SET PASSWORD FOR root=PASSWORD("root");

2.远程登录mysql server：(当然server必须首先给某个远程用户授权了）
shell>mysql -h host -u user -p

3.打开数据库：use dbname；
显示所有数据库：show databases;
显示数据库mysql中所有的表：先use mysql；然后show tables;
显示表的列信息：describe user;(显示表mysql数据库中user表的信息）；

4.创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令something做这个
GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY 'something' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO " IDENTIFIED BY 'something' WITH GRANT OPTION;

5.删除授权：
REVOKE ALL PRIVILEGES ON *.* FROM ";
USE mysql;
DELETE FROM user WHERE User="root" and Host="%";
FLUSH PRIVILEGES;

6. 创建一个用户custom在特定客户端weiqiong.com登录，可访问特定数据库bankaccount
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.*
TO [e-mail] IDENTIFIED BY 'stupid';

7.重命名表:
ALTER TABLE t1 RENAME t2;

为了改变列a，从INTEGER改为TINYINT NOT NULL(名字一样)，
并且改变列b，从CHAR(10)改为CHAR(20)，同时重命名它，从b改为c:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

增加一个新TIMESTAMP列，名为d：
ALTER TABLE t2 ADD d TIMESTAMP;

在列d上增加一个索引，并且使列a为主键：
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

删除列c：
ALTER TABLE t2 DROP COLUMN c;

增加一个新的AUTO_INCREMENT整数列，命名为c：
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);
注意，我们索引了c，因为AUTO_INCREMENT柱必须被索引，并且另外我们声明c为NOT NULL，
因为索引了的列不能是NULL。

8.删除记录:
DELETE FROM t1 WHERE C>10;

6.改变某几行:
UPDATE t1 SET user=weiqiong,password=weiqiong;

7.使用name列的头10个字符创建一个索引:
CREATE INDEX part_of_name ON customer (name(10));