第3节-表的创建和查询

1. 查询表

查询 sys 数据库里的表:

USE sys;
SHOW TABLES;

+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| host_summary                                  |
| host_summary_by_file_io                       |
| host_summary_by_file_io_type                  |
| host_summary_by_stages                        |
| host_summary_by_statement_latency             |
|                    ...                        |
| x$waits_global_by_latency                     |
+-----------------------------------------------+
101 rows in set (0.03 sec)

2. 创建表

创建一个名为 user_db 的数据库，并且使用:

CREATE DATABASE IF NOT EXISTS user_db;
USE user_db;

在 user_db 里创建一个表：

标准形式

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ...
    字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];

实际语句

CREATE TABLE table1(
    -> id int COMMENT "编号",
    -> name varchar(50) COMMENT "姓名",
    -> age int COMMENT "年龄",
    -> gender varchar(1) COMMENT "性别"
    -> )COMMENT "用户表";

正常结果

Query OK, 0 rows affected (0.07 sec)

3. 查询表结构

DESC table1;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

4. 查询创建表的语句

SHOW CREATE TABLE table1;

+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
  `id` int DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表'            |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Reference

1. MySQL学习笔记
2. 黑马程序员 MySQL数据库入门到精通

Donate