mysql常用表管理语句

#查看库下面有多少张表.
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| boy            |
| boy2           |
| boy3           |
| course         |
| pj             |
| sc             |
| sc2            |
| stage          |
| student        |
+----------------+
9 rows in set (0.00 sec)

#删除视图
mysql> drop view pj;
Query OK, 0 rows affected (0.00 sec)

#删除表
mysql> drop tables sc;
Query OK, 0 rows affected (0.02 sec)

#查看表结构信息
mysql> desc boy;
+--------+--------------+------+-----+------------+-------+
| Field  | Type         | Null | Key | Default    | Extra |
+--------+--------------+------+-----+------------+-------+
| hid    | int(11)      | NO   |     | NULL       |       |
| bname  | varchar(100) | NO   |     | NULL       |       |
| birth  | date         | NO   |     | 0000-00-00 |       |
| weight | smallint(6)  | YES  |     | NULL       |       |
+--------+--------------+------+-----+------------+-------+
4 rows in set (0.00 sec)

#查看建表的过程、语句.
mysql> show create table boy;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| boy   | CREATE TABLE `boy` (
  `hid` int(11) NOT NULL,
  `bname` varchar(100) NOT NULL,
  `birth` date NOT NULL DEFAULT '0000-00-00',
  `weight` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#查看视图建表过程(table/view都可以)
mysql> show create table stage;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View  | Create View                                                                                                                                                            | character_set_client | collation_connection |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| stage | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stage` AS select `student`.`SNAME` AS `sname`,`student`.`AGE` AS `age` from `student` | utf8                 | utf8_general_ci      |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show create view stage;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View  | Create View                                                                                                                                                            | character_set_client | collation_connection |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| stage | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stage` AS select `student`.`SNAME` AS `sname`,`student`.`AGE` AS `age` from `student` | utf8                 | utf8_general_ci      |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

#查看表的信息和过程(所有表).
mysql> show table  status \G;
*************************** 1. row ***************************
           Name: boy
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
Auto_increment: NULL
    Create_time: 2019-01-29 00:33:46
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:

#单独的查看某张表的状态、信息.
mysql> show table  status where name="student" \G;
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
Auto_increment: NULL
    Create_time: 2018-09-21 00:55:30
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)
ERROR:
No query specified


清空表数据truncate和delete的区别？
使用delete对表中的数据进行操作:

#创建t2表
mysql> create table t2 ( id int primary key auto_increment );
Query OK, 0 rows affected (0.02 sec)
mysql> select * from t2;
Empty set (0.00 sec)

#插入两行数据.
mysql> insert into t2 values (null);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values (null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

#delete其中的一条数据.
mysql> delete from t2 where id="2";
Query OK, 1 row affected (0.00 sec)

#再次插入数据.
mysql> insert into t2 values (null);
Query OK, 1 row affected (0.01 sec)

#发现插入的id从3开始,继续原数据的id增长.
mysql> select * from t2;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)


使用truncate对表中的数据进行操作:

#表中的数据清空.
mysql> truncate table t2;
Query OK, 0 rows affected (0.02 sec)

#插入数据
mysql> insert into t2 values (null);
Query OK, 1 row affected (0.01 sec)

#发现表的id从1开始.
mysql> select * from t2;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
注意:truncate操作其实相当与对表进行了drop tables---create table操作.

----------------

本文转自青衫解衣博客51CTO博客

如需转载，请联系作者授权

原文链接：http://blog.51cto.com/215687833/2352593