MySQL数据库创建、修改和删除表操作实例介绍

2020-06-27 15:35:23

字体：大中小

来源：转载

供稿：网友

其实对很多人来说对于SQL语句已经忘了很多，或者说是不懂很多，因为有数据库图形操作软件，方便了大家，但是我们不能忘记最根本的东西，特别是一些细节上的东西，可能你用惯了Hibernate，不用写SQL语句，但是不是任何项目都要用到大框架的，如果不用，那你是不是就不会操作数据库了呢，所以我们最好还是熟悉一点好，对我们以后找工作和工作都有帮助。

在说创建、修改和删除表前，我们还是要进行一个操作的简单说明：

1.登陆数据库系统

在命令行中登陆MySQL数据库管理系统，输入一下内容：

mysql -h localhost -u root -p

很多人都知道这个，但是其中参数的具体表示什么我们还是要了解的，其中，“-h”参数指连接的主机名，所以后面是localhost；“-u”参数表示用户名，此处的用户名为root；“-p”参数表示用户的密码，按下Enter键后就显示“Enter password：”，输入密码即可登录进去了。

2.创建数据库

在创建数据库之前，我们可以查看已经存在的数据库：

mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || community          || community_test     || data               || mydata             || mysql              || performance_schema || test               |+--------------------+8 rows in set (0.04 sec)

创建数据库的格式：CREATE DATABASE 数据库名;

示例：创建一个名为example的数据库

mysql>  CREATE DATABASE example;Query OK, 1 row affected (0.00 sec)mysql>  SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || community          || community_test     || data               || example            || mydata             || mysql              || performance_schema || test               |+--------------------+9 rows in set (0.00 sec)

3.删除数据库：

格式：DROP DATABASE 数据库名;

示例：删除example数据库

mysql>  DROP DATABASE example;Query OK, 0 rows affected (0.07 sec)mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || community          || community_test     || data               || mydata             || mysql              || performance_schema || test               |+--------------------+8 rows in set (0.00 sec)

4.数据库存储引擎

存储引擎就是指表的类型，数据库存储引擎决定了表在计算机的存储方式。

MySQL中查询存储引擎的类型命令：SHOW ENGINES;

mysql> SHOW ENGINES;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| FEDERATED          | NO      | Federated MySQL storage engine                | NULL         | NULL | NULL       || MRG_MYISAM         | YES     | Collection of identical MyISAM tables                | NO           | NO   | NO         || MyISAM             | YES     | MyISAM storage engine                | NO           | NO   | NO         || BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                | NO           | NO   | NO         || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || ARCHIVE            | YES     | Archive storage engine                | NO           | NO   | NO         || InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || PERFORMANCE_SCHEMA | YES     | Performance Schema                | NO           | NO   | NO         |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.00 sec)

查询结果中，Engine参数指存储引擎名称；Support参数说明MySQL是否支持该类型引擎；Comment参数表示对该引擎的评论；Transaction参数表示是否支持事务处理；XA参数表示是否分布式交易处理的XA规范；Savepoints参数表示是否支持保存点，以方便事务的回滚操作；由上面我们看到InnoDB存储引擎是default的，也就是数据库默认的存储引擎，下面我们简单介绍一下InnoDB。

InnoDB是MySQL的一种存储引擎，InnoDB给MySQL提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。InnoDB是MySQL上第一个提供外键约束的表引擎，而且对事务处理的能力，也是其他存储引擎不能比拟的。不过这种引擎的缺点就是读写效率稍差，占用的数据空间相对比较大。

下面就是正式的内容：

创建表：

1）创建表的形式：

CREATE TABLE 表名 (    属性名 数据类型 [完整约束条件],    属性名 数据类型 [完整约束条件],    ...    ...    属性名 数据类型 [完整约束条件]);

如果你很急的登陆进去就创建表，恭喜你，你会出现“No database selected”的错误，因为你没有告诉别人你要选择在哪个数据库创建表，所以在创建之前要选择数据库，格式：USE 数据库名;

示例创建一个student表：

mysql> use example;Database changedmysql> CREATE TABLE student (    -> id int,    -> name varchar(20)    -> );Query OK, 0 rows affected (0.09 sec)

上面创建表的时候涉及到一个完整性约束条件，下面就列出一个完整性约束条件表：

约束条件说明PRIMARY KEY 标识该属性为该表的主键，可以唯一的标识对应的元组FOREIGN KEY 标识该属性为该表的外键，是与之联系某表的主键 NOT NULL 标识该属性不能为空UNIQUE 标识该属性的值是唯一的AUTO_INCREMENT 标识该属性的值是自动增加，这是MySQL的SQL语句的特色 DEFAULT为该属性设置默认值

下面讲解一下上面完整性约束条件的应用：

2）设置表的主键

单字段主键格式：属性名数据类型 PRIMARY KEY

示例：

mysql>  CREATE TABLE student1 (    -> id int PRIMARY KEY,    -> name varchar(20)    -> );Query OK, 0 rows affected (0.06 sec)

多字段主键格式：PRIMARY KEY(属性名1,属性名2....属性名n)

示例：

mysql> CREATE TABLE student2 (    -> id int,    -> stu_id int,    -> name varchar(20),    -> PRIMARY KEY(id,stu_id)    -> );Query OK, 0 rows affected (0.00 sec)

3）设置表的外键

格式：CONSTRAINT 外键别名 FOREIGN KEY(属性1,属性2,....属性n) REFERENCES 表名(属性1',属性2',...属性n')

示例：

mysql> CREATE TABLE teacher (    -> id int PRIMARY KEY,    -> stu_id int,    -> name varchar(20),    -> CONSTRAINT STUID FOREIGN KEY(stu_id) REFERENCES student1(id)    -> );Query OK, 0 rows affected (0.00 sec)

4）设置表的非空约束

简单的说就是不让这个属性的值为空，不填的话就会报错

格式：属性名数据类型 NOT NULL

5）设置表的唯一性约束

就是这个属性的值是不能重复的

格式：属性名数据类型 UNIQUE

6）设置表的属性值自动增加

AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT和BIGINT），在默认的情况下，该字段的值是从1开始自增

格式：属性名数据类型 AUTO_INCREMENT

7）设置表的属性的默认值

格式：属性名数据类型 DEFAULT 默认值

下面对4-7进行综合示例：

mysql> CREATE TABLE student3 (    -> id int PRIMARY KEY AUTO_INCREMENT,    -> teacher_id int UNIQUE,    -> name varchar(20) NOT NULL,    -> sex varchar(10) DEFAULT 'male'    -> );Query OK, 0 rows affected (0.01 sec)

查看表结构

查看表基本结构语句DESCRIBE

格式：DESCRIBE 表名;

通过查看表的结构，就很明确的对表进行解读，而且可以查看一下自己创建的表有没错误，这个SQL语句必须会用啊

示例：

mysql> desc student3;+------------+-------------+------+-----+---------+----------------+| Field      | Type        | Null | Key | Default | Extra          |+------------+-------------+------+-----+---------+----------------+| id         | int(11)     | NO   | PRI | NULL    | auto_increment || teacher_id | int(11)     | YES  | UNI | NULL    |                || name       | varchar(20) | NO   |     | NULL    |                || sex        | varchar(10) | YES  |     | male    |                |+------------+-------------+------+-----+---------+----------------+4 rows in set (0.01 sec)

查看表详细结构语句SHOW CREATE TABLE

通过这个SQL语句可以查看表的详细定义，除了字段名、字段的数据类型、约束条件外，还可以查看表的默认存储引擎和字符编码

格式：SHOW CREATE TABLE 表名;

示例：

mysql> SHOW CREATE TABLE student3;+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table    | Create Table                                             |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student3 | CREATE TABLE `student3` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `teacher_id` int(11) DEFAULT NULL,  `name` varchar(20) NOT NULL,  `sex` varchar(10) DEFAULT 'male',  PRIMARY KEY (`id`),  UNIQUE KEY `teacher_id` (`teacher_id`)) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

修改表：

1）修改表名

表名可以在一个数据库中唯一的确定一张表。

格式：ALTER TABLE 旧表名 RENAME 新表名;

示例：

mysql> ALTER TABLE student RENAME student4;Query OK, 0 rows affected (0.11 sec)mysql> DESCRIBE student;ERROR 1146 (42S02): Table 'example.student' doesn't exist

由上面可以看出，改名后的表已经不存在了。

2）修改字段的数据类型

格式：ALTER TABLE 表名 MODIFY 属性名数据类型;

示例：

mysql> DESCRIBE student1;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.08 sec)mysql> ALTER TABLE student1 MODIFY name varchar(30);Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> DESCRIBE student1;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)

3）修改字段名：

格式：ALTER TABLE 表名 CHANGE 旧属性名新属性名新数据类型;

示例：

mysql> DESCRIBE student1;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(30) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> ALTER TABLE student1 CHANGE name stu_name varchar(40);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> DESCRIBE student1;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | int(11)     | NO   | PRI | NULL    |       || stu_name | varchar(40) | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

这里我修改的字段名的同时也修改了数据类型了，如果你不想修改数据类型的话就按照原来的写就行了。

4）增加字段

格式：ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];

其中，“属性名1”参数指需要增加的字段的名称；“FIRST”参数是可选参数，其作用是将新增字段设置为表的第一个字段；“AFTER”参数也是可选的参数，其作用是将新增字段添加到“属性名2”后面；“属性名2”当然就是指表中已经有的字段

示例：

mysql> DESCRIBE student1;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | int(11)     | NO   | PRI | NULL    |       || stu_name | varchar(40) | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> ALTER TABLE student1 ADD teacher_name varchar(20) NOT NULL AFTER id;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> DESCRIBE student1;+--------------+-------------+------+-----+---------+-------+| Field        | Type        | Null | Key | Default | Extra |+--------------+-------------+------+-----+---------+-------+| id           | int(11)     | NO   | PRI | NULL    |       || teacher_name | varchar(20) | NO   |     | NULL    |       || stu_name     | varchar(40) | YES  |     | NULL    |       |+--------------+-------------+------+-----+---------+-------+3 rows in set (0.01 sec)

5）删除字段

格式：ALTER TABLE 表名 DROP 属性名;

示例：

mysql> DESCRIBE student1;+--------------+-------------+------+-----+---------+-------+| Field        | Type        | Null | Key | Default | Extra |+--------------+-------------+------+-----+---------+-------+| id           | int(11)     | NO   | PRI | NULL    |       || teacher_name | varchar(20) | NO   |     | NULL    |       || stu_name     | varchar(40) | YES  |     | NULL    |       |+--------------+-------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> ALTER TABLE student1 DROP teacher_name;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> DESCRIBE student1;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | int(11)     | NO   | PRI | NULL    |       || stu_name | varchar(40) | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

6)更改表的存储引擎

格式：ALTER TABLE 表名 ENGINE = 存储引擎名;

示例：

mysql> SHOW CREATE TABLE student2;+----------+------------------------------------------------------------------------------------------------------------------------------------------------| Table    | Create Table+----------+------------------------------------------------------------------------------------------------------------------------------------------------| student2 | CREATE TABLE `student2` (  `id` int(11) NOT NULL DEFAULT '0',  `stu_id` int(11) NOT NULL DEFAULT '0',  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`,`stu_id`)) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.05 sec)mysql> ALTER TABLE student2 ENGINE = MYISAM;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW CREATE TABLE student2;+----------+------------------------------------------------------------------------------------------------------------------------------------------------| Table    | Create Table+----------+------------------------------------------------------------------------------------------------------------------------------------------------| student2 | CREATE TABLE `student2` (  `id` int(11) NOT NULL DEFAULT '0',  `stu_id` int(11) NOT NULL DEFAULT '0',  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`,`stu_id`)) ENGINE=MyISAM DEFAULT CHARSET=gb2312 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)

7)删除表的外键约束

格式：ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

示例：

mysql> SHOW CREATE TABLE teacher;+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table   | Create Table                                                  |+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| teacher | CREATE TABLE `teacher` (  `id` int(11) NOT NULL,  `stu_id` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `STUID` (`stu_id`),  CONSTRAINT `STUID` FOREIGN KEY (`stu_id`) REFERENCES `stu) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.08 sec)mysql> ALTER TABLE teacher DROP FOREIGN KEY STUID;Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW CREATE TABLE teacher;+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table   | Create Table                                                        |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+| teacher | CREATE TABLE `teacher` (  `id` int(11) NOT NULL,  `stu_id` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `STUID` (`stu_id`)) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)