MySQL8 查看数据表结构及数据

查看表数据

数据表创建成功后，可以使用select关键字查看表中的数据：

表中没有数据时：

mysql> select * from school.student;
Empty set (0.00 sec)

表中有数据时：

mysql> select * from school.student;
+------------+--------------+--------+------------+-----+----------+-------+
| student_id | student_name | gender | birth_day  | age | class_id | score |
+------------+--------------+--------+------------+-----+----------+-------+
| S20180001  | 方东美       | 女     | 2006-02-04 |  12 | G0101    | 80.65 |
| S20180002  | 方香         | 女     | 2008-09-28 |  10 | G0101    | 75.48 |
| S20160055  | 俞莺         | 女     | 2003-02-17 |  15 | G0304    | 86.29 |
| S20160056  | 张浩楠       | 男     | 2003-05-12 |  15 | G0304    | 63.36 |
+------------+--------------+--------+------------+-----+----------+-------+
4 rows in set (0.00 sec)

查看表结构

数据表创建成功后，可以使用describe/desc关键字，查看表结构：

mysql> describe school.student;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| student_id   | varchar(50)   | NO   |     | NULL    |       |
| student_name | varchar(100)  | NO   |     |         |       |
| gender       | varchar(10)   | NO   |     |         |       |
| birth_day    | date          | NO   |     | NULL    |       |
| age          | int           | NO   |     | 0       |       |
| class_id     | varchar(50)   | NO   |     |         |       |
| score        | decimal(18,2) | NO   |     | 0.00    |       |
+--------------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

• Field：字段名；
• Type：字段数据类型；
• Null：字段是否可以为NULL值；
• Key：字段的索引信息。PRI表示该字段是主键或主键的一部分；UNI表示该字段是唯一索引或唯一索引的一部分；MUI表示该字段是普通索引或普通索引的一部分；

也可以使用show create table子句，查看建表脚本：

mysql> show create table school.student;
+---------+-----------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------+
| Table   | Create Table
                                                                                             |
+---------+-----------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `student_id` varchar(50) CHARACTER SET gbk NOT NULL COMMENT '学生编号',
  `student_name` varchar(100) CHARACTER SET gbk NOT NULL DEFAULT '' COMMENT '学生姓名',
  `gender` varchar(10) CHARACTER SET gbk NOT NULL DEFAULT '' COMMENT '性别',
  `birth_day` date NOT NULL COMMENT '生日',
  `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
  `class_id` varchar(50) CHARACTER SET gbk NOT NULL DEFAULT '' COMMENT '班级编号',
  `score` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩'
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 COMMENT='学生' |
+---------+-----------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

如果觉得格式看起来比较乱，可以添加\G参数：

mysql> show create table school.student\G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `student_id` varchar(50) CHARACTER SET gbk NOT NULL COMMENT '学生编号',
  `student_name` varchar(100) CHARACTER SET gbk NOT NULL DEFAULT '' COMMENT '学生姓名',
  `gender` varchar(10) CHARACTER SET gbk NOT NULL DEFAULT '' COMMENT '性别',
  `birth_day` date NOT NULL COMMENT '生日',
  `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
  `class_id` varchar(50) CHARACTER SET gbk NOT NULL DEFAULT '' COMMENT '班级编号',
  `score` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩'
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 COMMENT='学生'
1 row in set (0.00 sec)