MySQL讲义第4讲——创建数据表

最新推荐文章于 2024-03-18 18:34:55 发布

睿思达DBA_WGX

最新推荐文章于 2024-03-18 18:34:55 发布

阅读量2k

点赞数 9

分类专栏： MySQL基本操作 MySQL讲义文章标签：数据库 mysql 创建数据表

本文链接：https://blog.csdn.net/weixin_44377973/article/details/108693658

版权

MySQL基本操作同时被 2 个专栏收录

169 篇文章 93 订阅

订阅专栏

MySQL讲义

57 篇文章 24 订阅

订阅专栏

MySQL讲义第4讲——创建数据表

文章目录

MySQL讲义第4讲——创建数据表

MySQL 的数据表是最终保存数据的场所，在数据库中，所有的数据都保存在数据表中。创建数据表需要做三件事：
（1）定义表中所包含的列，包括：列名、数据类型、数据的完整性约束；
（2）定义表选项，包括：存储引擎，字符集；
（3）定义表分区选项。

可使用三种方式创建表：

一、使用 create table 语句直接定义表中的字段

语法如下：

create [temporary] table [if not exists]  <表名> ([列定义选项])
[表选项]
[分区选项];

[列定义选项]的格式为：
                  <列名1> <类型> <数据完整性约束>
                  [, <列名2> <类型> <数据完整性约束>
                  [, ...
                  [<列名n> <类型n>]]]
<数据完整性约束>的格式：
                    [NOT NULL | NULL] --非空约束
                    [DEFAULT default_value]  --默认值
                    [AUTO_INCREMENT]    --定义为自增列（类型必须是整型）
                    [PRIMARY KEY]  --主键
                    [[unique] index]   --索引，唯一索引
                    [foreign KEY(column_name) REFERENCES table_name(column_name))]  
                                            --定义外键
                    [COMMENT 'string']      --为字段添加注释             
[表选项]的格式为：
               ENGINE = engine_name（存储引擎的名称） --指定存储引擎
                                       --常用的存储引擎有 InnoDB 和 MyISAM
               AUTO_INCREMENT = value  --设置自增字段的起始值
               [DEFAULT] CHARACTER SET charset_name（字符集名称） --设置字符集
               [COLLATE collation_name（校对集名称）]  --设置校对集
               COMMENT = 'string'   --表注释

--参数说明：
（1）temporary：表示创建连时表。只有在当前连接情况下，TEMPORARY表才是可见的。当连接关闭时，TEMPORARY表被自动取消。这意味着两个不同的连接可以使用相同的临时表名称，同时两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。
（2）IF NOT EXISTS：如果加了该选项，只有在要创建的表不存在的情况下，才创建该表。如果要创建的表已经存在，则不执行 create table 命令。
（3）表名：如果只指定表名，则把表创建到当前数据库中，可以使用【db_name.table_name】格式指定表名，则把表创建到指定的数据库中。

举例：

（1）创建 t01 表

注：使用 desc 命令可以显示表结构。

mysql> create table hist.t01(id int,name char(20));
Query OK, 0 rows affected (0.03 sec)

mysql> desc t01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

（2）创建 dept 表，包含主键

可以使用 show create table 命令查看建表的语句。

mysql> create table if not exists hist.dept (
       dept_id int primary key comment '部门编号',
       dept_name char(20) not null comment '部门名称'
       )
       comment '部门表';
Query OK, 0 rows affected (0.03 sec)

mysql> show create table dept\G
*************************** 1. row ***************************
       Table: dept
Create Table: CREATE TABLE `dept` (
  `dept_id` int(11) NOT NULL COMMENT '部门编号',
  `dept_name` char(20) NOT NULL COMMENT '部门名称',
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表'
1 row in set (0.00 sec)

（3）创建 emp 表，包含自增字段（从1001开始），通过外键和 dept 表关联

mysql> create table if not exists emp (
         emp_id int auto_increment primary key comment '员工编号',
         emp_name char(20) not null comment '员工姓名',
         birth datetime comment '出生日期',
         phone char(20) comment '员工表',
         dept_id int,
         foreign key(dept_id) references dept(dept_id)
     )
     comment '员工表'
     auto_increment=1001
     default character set utf8 collate utf8_general_ci
     engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `emp_name` char(20) NOT NULL COMMENT '员工姓名',
  `birth` datetime DEFAULT NULL COMMENT '出生日期',
  `phone` char(20) DEFAULT NULL COMMENT '员工表',
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`emp_id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COMMENT='员工表'
1 row in set (0.00 sec)

（4）创建 stu 表，包含默认值，索引

mysql> create table if not exists stu(
         s_no int auto_increment primary key,
         s_name char(20) not null,
         birth datetime,
         phone char(11),
         addr char(100),
         index(s_name),
         unique index(phone),
         index(birth,s_name)
     );
Query OK, 0 rows affected (0.04 sec)

mysql> desc stu;
+--------+-----------+------+-----+---------+----------------+
| Field  | Type      | Null | Key | Default | Extra          |
+--------+-----------+------+-----+---------+----------------+
| s_no   | int(11)   | NO   | PRI | NULL    | auto_increment |
| s_name | char(20)  | NO   | MUL | NULL    |                |
| birth  | datetime  | YES  | MUL | NULL    |                |
| phone  | char(11)  | YES  | UNI | NULL    |                |
| addr   | char(100) | YES  |     | NULL    |                |
+--------+-----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show create table stu\G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `s_no` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` char(20) NOT NULL,
  `birth` datetime DEFAULT NULL,
  `phone` char(11) DEFAULT NULL,
  `addr` char(100) DEFAULT NULL,
  PRIMARY KEY (`s_no`),
  UNIQUE KEY `phone` (`phone`),
  KEY `s_name` (`s_name`),
  KEY `birth` (`birth`,`s_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

二、根据已经存在的表来定义新表

使用 create table like 命令可以利用一个已经存在的表创建一个新表。语法如下：

create [temporary] table [if not exists] table_name
like old_tbale_name;

--说明：使用该命令可以创建一个和原数据表结构完全相同的新表，但不会复制原数据表中的数据。

举例：根据 stu 表创建一个 student 表

mysql> create table if not exists student like stu;
Query OK, 0 rows affected (0.04 sec)

mysql> desc student;
+--------+-----------+------+-----+---------+----------------+
| Field  | Type      | Null | Key | Default | Extra          |
+--------+-----------+------+-----+---------+----------------+
| s_no   | int(11)   | NO   | PRI | NULL    | auto_increment |
| s_name | char(20)  | NO   | MUL | NULL    |                |
| birth  | datetime  | YES  | MUL | NULL    |                |
| phone  | char(11)  | YES  | UNI | NULL    |                |
| addr   | char(100) | YES  |     | NULL    |                |
+--------+-----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> show create table student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `s_no` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` char(20) NOT NULL,
  `birth` datetime DEFAULT NULL,
  `phone` char(11) DEFAULT NULL,
  `addr` char(100) DEFAULT NULL,
  PRIMARY KEY (`s_no`),
  UNIQUE KEY `phone` (`phone`),
  KEY `s_name` (`s_name`),
  KEY `birth` (`birth`,`s_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

可以看到，student 表和 stu 表的结构完全相同。然后可以对 student 表的结构进行修改，得到我们想要的表结构。

三、使用 select 查询的结果集来创建表

可以利用一个 select 查询的结果创建一张新表。语法如下：

create [temporary] table [if not exists] table_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [ignore | replace]
    [as] query_expression
    
--说明：使用此命令可以根据查询结果创建一张新表，并且把查询到的数据插入到新建的表中。

举例：

（1）创建新表 stu1，表结构来源于对 stu 表的查询结果

mysql> create table if not exists stu1 
     as select * from stu;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from stu;
+------+--------+---------------------+-------------+----------+
| s_no | s_name | birth               | phone       | addr     |
+------+--------+---------------------+-------------+----------+
|    1 | Jack   | 1999-01-23 00:00:00 | 13703735566 | Beijing  |
|    2 | Mark   | 1999-10-03 00:00:00 | 13783735566 | Beijing  |
|    3 | Rose   | 2000-11-21 00:00:00 | 13783735522 | Shanghai |
+------+--------+---------------------+-------------+----------+
3 rows in set (0.01 sec)

mysql> show create table stu1\G
*************************** 1. row ***************************
       Table: stu1
Create Table: CREATE TABLE `stu1` (
  `s_no` int(11) NOT NULL DEFAULT '0',
  `s_name` char(20) NOT NULL,
  `birth` datetime DEFAULT NULL,
  `phone` char(11) DEFAULT NULL,
  `addr` char(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

--新建的表包含了查询结果中的数据，但新建表的结构和原数据表的结构并不完全相同。

（2）创建新表 stu2，并且重新定义表结构

mysql> create table if not exists stu2(
         s_no int auto_increment primary key,
         s_name char(30),
         phone char(11)
     )
     engine=InnoDB
     character set utf8 collate utf8_general_ci
     as 
     select s_no,s_name,phone
     from stu;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table stu2\G
*************************** 1. row ***************************
       Table: stu2
Create Table: CREATE TABLE `stu2` (
  `s_no` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` char(30) DEFAULT NULL,
  `phone` char(11) DEFAULT NULL,
  PRIMARY KEY (`s_no`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

四、和表相关的命令总结

1、查看当前数据库中的数据表

语法格式如下：

show tables;

举例：

mysql> show tables;
+----------------+
| Tables_in_hist |
+----------------+
| dept           |
| emp            |
| emp1           |
| stu            |
| stu1           |
| stu2           |
| student        |
| t01            |
| t1             |
+----------------+
9 rows in set (0.00 sec)

2、删除数据表

语法格式如下：

drop table 表名;

举例：

mysql> drop table t01;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_hist |
+----------------+
| dept           |
| emp            |
| emp1           |
| stu            |
| stu1           |
| stu2           |
| student        |
+----------------+
7 rows in set (0.00 sec)

3、查看表结构

语法格式如下：

describe | desc 表名;

--说明：describe 是完整写法，desc 是简写。

举例：

mysql> describe stu;
+--------+-----------+------+-----+---------+----------------+
| Field  | Type      | Null | Key | Default | Extra          |
+--------+-----------+------+-----+---------+----------------+
| s_no   | int(11)   | NO   | PRI | NULL    | auto_increment |
| s_name | char(20)  | NO   | MUL | NULL    |                |
| birth  | datetime  | YES  | MUL | NULL    |                |
| phone  | char(11)  | YES  | UNI | NULL    |                |
| addr   | char(100) | YES  |     | NULL    |                |
+--------+-----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc stu;
+--------+-----------+------+-----+---------+----------------+
| Field  | Type      | Null | Key | Default | Extra          |
+--------+-----------+------+-----+---------+----------------+
| s_no   | int(11)   | NO   | PRI | NULL    | auto_increment |
| s_name | char(20)  | NO   | MUL | NULL    |                |
| birth  | datetime  | YES  | MUL | NULL    |                |
| phone  | char(11)  | YES  | UNI | NULL    |                |
| addr   | char(100) | YES  |     | NULL    |                |
+--------+-----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

4、查看创建表的语句

语法格式如下：

show create table 表名;

--说明：可以加上 \G 参数对显示结果进行格式化。

举例：

mysql> show create table stu\G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `s_no` int(11) NOT NULL AUTO_INCREMENT,
  `s_name` char(20) NOT NULL,
  `birth` datetime DEFAULT NULL,
  `phone` char(11) DEFAULT NULL,
  `addr` char(100) DEFAULT NULL,
  PRIMARY KEY (`s_no`),
  UNIQUE KEY `phone` (`phone`),
  KEY `s_name` (`s_name`),
  KEY `birth` (`birth`,`s_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

睿思达DBA_WGX

关注

9
点赞
踩
16

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL讲义第4讲——创建数据表

MySQL讲义第4讲——创建数据表MySQL 的数据表是最终保存数据的场所，在数据库中，所有的数据都保存在数据表中。创建数据表需要做三件事：（1）定义表中所包含的列，包括：列名、数据类型、数据的完整性约束；（2）定义表选项，包括：存储引擎，字符集；（3）定义表分区选项。可使用三种方式创建表：一、使用 create table 语句直接定义表中的字段语法如下：create [temporary] table [if not exists] <表名> ([列定义选项])[表选项]
复制链接

扫一扫