2018年04月6日网站服务器迁移完成……

MySQL语句之describe,explain,help,use

mysql 苏 demo 1513℃ 0评论

1 创建测试表及数据

create table utility_table(      c1 int not null auto_increment,       c2 varchar(10),       c3 int(10),       primary key(c1)  )engine=innodb;  insert into utility_table values('', 'name1', 001);  insert into utility_table values('', 'name2', 002);  insert into utility_table values('', 'name3', 003);  insert into utility_table values('', 'name4', 004);  insert into utility_table values('', 'name5', 005);

2 DESCRIBE,DESC,EXPLAIN语句

DESCRIBE,DESC与EXPLAIN是同义词,它门是SHOW COLUMNS FROM的快捷方式,DESCRIBE语句被设立出来,用于与Oracle相兼容。语句给出了有关表的列信息。

基本语法:

{EXPLAIN | DESCRIBE | DESC}    tbl_name [col_name | wild]{EXPLAIN | DESCRIBE | DESC}    [explain_type]    explainable_stmtexplain_type: {    EXTENDED  | PARTITIONS}explainable_stmt: {    SELECT statement  | DELETE statement  | INSERT statement  | REPLACE statement  | UPDATE statement}

实例:

mysql> describe utility_table;mysql> desc utility_table;mysql> explain utility_table;mysql> show columns from  utility_table;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| c1    | int(11)     | NO   | PRI | NULL    | auto_increment || c2    | varchar(10) | YES  |     | NULL    |                || c3    | int(10)     | YES  |     | NULL    |                |+-------+-------------+------+-----+---------+----------------+

注:上面四个语句打印结果都一样。    NULL字段指示是否NULL可以被存储在列中。
Key字段指示是否该列已编制索引。PRI的值指示该列是表的主键的一部分。UNI指示,该列是UNIQUE索引的一部分。MUL值指示,在列中某个给定值多次出现是允许的。
Extra字段包含可以获取的与给定列有关的附加信息。在我们的例子中,Extra字段指示,Id列使用AUTO_INCREMENT关键词创建。

mysql> describe utility_table 'c1%';+-------+---------+------+-----+---------+----------------+| Field | Type    | Null | Key | Default | Extra          |+-------+---------+------+-----+---------+----------------+| c1    | int(11) | NO   | PRI | NULL    | auto_increment |+-------+---------+------+-----+---------+----------------+mysql> describe select c1,c2,c3 from utility_table where c1 < 3;+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra       |+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+|  1 | SIMPLE      | utility_table | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+mysql> describe extended select c1,c2,c3 from utility_table where c1 < 3 /G;+----+-------------+---------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |+----+-------------+---------------+-------+---------------+---------+---------+------+------+----------+-------------+|  1 | SIMPLE      | utility_table | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |+----+-------------+---------------+-------+---------------+---------+---------+------+------+----------+-------------+mysql> describe partitions select c1,c2,c3 from utility_table where c1 < 3;+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+-------------+|  1 | SIMPLE      | utility_table | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+-------------+

注:mysql5.6之前explain只可以用在select上,5.6及其以后可以使用SELECT, DELETE, INSERT, REPLACE, 和UPDATE。            EXPLAIN EXTENDED:可获取额外的执行计划信息。
EXPLAIN PARTITIONS:对分区表语句的检查非常有用。

3 HELP语句

显示关于可能用到的命令的信息,它是mysql里面最有用的一个命令,有了它就不用担心记不清语法了。

基本语法:

HELP 'search_string'

实例:

mysql> HELP 'contents'You asked for help about help category: "Contents"For more information, type 'help <item>', where <item> is one of the followingcategories:   Account Management   Administration   Compound Statements   Data Definition   Data Manipulation   Data Types   Functions   Functions and Modifiers for Use with GROUP BY   Geographic Features   Help Metadata   Language Structure   Plugins   Procedures   Table Maintenance   Transactions   User-Defined Functions   Utility

这个语句列出了所有MySQL使用的类别。    当然,我们还可以查看很多,例如:

HELP 'data types'HELP 'ascii'HELP 'create table'HELP 'status'HELP 'functions'HELP 'change master'

4 USE语句

 

打赏

转载请注明:苏demo的别样人生 » MySQL语句之describe,explain,help,use

   如果本篇文章对您有帮助,欢迎向博主进行赞助,赞助时请写上您的用户名。
支付宝直接捐助帐号oracle_lee@qq.com 感谢支持!
喜欢 (0)or分享 (0)
发表我的评论
取消评论
表情