MySQL 實(shí)用語(yǔ)句集合

參考鏈接[用戶]：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、查詢數(shù)據(jù)庫(kù)中表的屬性：

SHOW TABLE STATUS FROM `DatabaseName` LIKE 'TableName' ;
結(jié)果舉例：

+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-------------------------------------------------+---------+ | 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、獲取數(shù)據(jù)庫(kù)表中自增字段的值：

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'TableName';
結(jié)果舉例：

+----------------+ | AUTO_INCREMENT | +----------------+ | 8 | +----------------+
3、創(chuàng)建MySQL登錄用戶，可以任意地址遠(yuǎn)端訪問(wèn)，同root權(quán)限：

grant all on *.* to 'remote'@'%' identified by 'password';結(jié)果舉例：

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

4、清空表：

TRUNCATE `TableName`;
5、查看表中的索引：

EXPLAIN SELECT `id`,`username` FROM `TableName`;結(jié)果舉例：

+----+-------------+-----------+-------+---------------+--------------+---------+------+------+-------------+ | 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] 返回當(dāng)前 MySQL用戶名和機(jī)主名

mysql> SELECT USER();

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

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

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

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

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

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

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

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

總結(jié)

以上是生活随笔為你收集整理的MySQL 实用语句集合的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。