MySQL 實用語句集合

參考鏈接[用戶]：http://blog.csdn.net/dmtnewtons_blog/article/details/9136339

參考鏈接[屬性]：http://stackoverflow.com/questions/15821532/get-current-auto-increment-value-for-any-table

參考鏈接[索引]：http://blog.csdn.net/dmtnewtons_blog/article/details/18605947

1、查詢數據庫中表的屬性：

SHOW TABLE STATUS FROM `DatabaseName` LIKE 'TableName' ;

結果舉例：

+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options                                  | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+
| TableName    | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |            0 |  11534336 |              8 | 2014-03-06 02:13:49 | NULL        | NULL       | utf8_general_ci |     NULL | checksum=1 delay_key_write=1 row_format=DYNAMIC |         |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+

2、獲取數據庫表中自增字段的值：

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';

結果舉例：

+----------------+
| AUTO_INCREMENT |
+----------------+
|              8 |
+----------------+

3、創建MySQL登錄用戶，可以任意地址遠端訪問，同root權限：

grant all on *.* to 'remote'@'%' identified by 'password';

結果舉例：

#/usr/local/mysql/bin/mysql -h 1.1.1.1 -u remote -p

4、清空表：

TRUNCATE `TableName`;

5、查看表中的索引：

EXPLAIN SELECT `id`,`username` FROM `TableName`;

結果舉例：

+----+-------------+-----------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | TableName | index | NULL          | idx_username | 98      | NULL |    1 | Using index |
+----+-------------+-----------+-------+---------------+--------------+---------+------+------+-------------+

6、字符串截取：

[1] 在路徑中截取文件名：

SELECT SUBSTRING_INDEX('C:\\dir\\file.txt', '\\', -1);    //windows
SELECT SUBSTRING_INDEX('/usr/local/ade.c', '/', -1);      //Linux

[2] 返回當前 MySQL用戶名和機主名

mysql> SELECT USER();

??????? -> 'davida@localhost'

這個值指示了你指定的連接服務器時的用戶名,及你所連接的客戶主機。這個值可以和CURRENT_USER()的值不同。

你可以這樣提取用戶名部分：

mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);

??????? -> 'davida'

由于 USER() 返回一個utf8 字符集中的值，你也應確保'@'字符串文字在該字符集中得到解釋：

mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);

??????? -> 'davida'