查询

  • 查询当前数据库的所有表

    1. SHOW TABLES;

  • 查询表结构

    1. DESC 表名;

  • 查询指定表的建表语句

    1. SHOW CREATE TABLE 表名;

    创建

    1. CREATE TABLE 表名(
    2. 字段1 字段类型 [COMMENT 字段1注释],
    3. 字段2 字段类型 [COMMENT 字段2注释],
    4. ...
    5. 字段n 字段类型 [COMMENT 字段n注释]
    6. ) [COMMENT 表注释];
  1. mysql> use world;
  2. Database changed
  3. mysql> show tables;
  4. +-----------------+
  5. | Tables_in_world |
  6. +-----------------+
  7. | city            |
  8. | country         |
  9. | countrylanguage |
  10. +-----------------+
  11. 3 rows in set (0.01 sec)
  13. mysql> desc countrylanguage;
  14. +-------------+---------------+------+-----+---------+-------+
  15. | Field       | Type          | Null | Key | Default | Extra |
  16. +-------------+---------------+------+-----+---------+-------+
  17. | CountryCode | char(3)       | NO   | PRI |         |       |
  18. | Language    | char(30)      | NO   | PRI |         |       |
  19. | IsOfficial  | enum('T','F') | NO   |     | F       |       |
  20. | Percentage  | float(4,1)    | NO   |     | 0.0     |       |
  21. +-------------+---------------+------+-----+---------+-------+
  22. 4 rows in set (0.01 sec)
  25. mysql> use ithema;
  26. Database changed
  28. mysql> create table user_id(
  29.     -> id int comment '编号',
  30.     -> name varchar(50) comment '姓名',
  31.     -> age int comment '年龄',
  32.     -> gender varchar(1) comment '性别'
  33.     -> ) comment '用户信息';
  34. Query OK, 0 rows affected (0.03 sec)
  37. mysql> show tables;
  38. +------------------+
  39. | Tables_in_ithema |
  40. +------------------+
  41. | user_id          |
  42. +------------------+
  43. 1 row in set (0.01 sec)
  45. mysql> desc user_id;
  46. +--------+-------------+------+-----+---------+-------+
  47. | Field  | Type        | Null | Key | Default | Extra |
  48. +--------+-------------+------+-----+---------+-------+
  49. | id     | int         | YES  |     | NULL    |       |
  50. | name   | varchar(50) | YES  |     | NULL    |       |
  51. | age    | int         | YES  |     | NULL    |       |
  52. | gender | varchar(1)  | YES  |     | NULL    |       |
  53. +--------+-------------+------+-----+---------+-------+
  54. 4 rows in set (0.01 sec)
  56. mysql> show create table user_id;
  57. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  58. | Table   | Create Table                                                                                                                                                                                                                                                                                        |
  59. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  60. | user_id | CREATE TABLE `user_id` (
  61.   `id` int DEFAULT NULL COMMENT '编号',
  62.   `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  63.   `age` int DEFAULT NULL COMMENT '年龄',
  64.   `gender` varchar(1) DEFAULT NULL COMMENT '性别'
  65. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息' |
  66. +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  67. 1 row in set (0.00 sec)