夏日博客

A-A+

MySQL中创建、修改和删除表命令

2017年10月24日 PHP技术文章 暂无评论 阅读 0 views 次

对于mysql数据表创建,修改,删除命令也是最基本的数据库入门基础知识了,下面小编来给大家介绍一下MySQL中创建、修改和删除表一些实例,希望对大家有所帮助.

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

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

1、登陆数据库系统

在命令行中登陆MySQL数据库管理系统,输入以下内容:

mysql -h localhost -u root -p

很多人都知道这个,但是其中参数的具体表示什么我们还是要了解的,其中:

-h：指连接的主机名,默认情况下是localhost,可以忽略此参数.

-u：表示用户名,此处的用户名为root.

-p：表示用户的密码

按下Enter键后就显示“Enter password：”,输入密码即可登录进去了,代码如下:

  1. Welcome to the MySQL monitor.  Commands end with ; or g.   
  2. Your MySQL connection id is 2   
  3. Server version: 5.6.13 Source distribution   
  4. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.   
  5. Oracle is a registered trademark of Oracle Corporation and/or its   
  6. affiliates. Other names may be trademarks of their respective   
  7. owners.   
  8. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.   
  9. mysql>   

2、创建数据库

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

格式:CREATE DATABASE 数据库名;

示例:查看已经存在的数据库,代码如下:

  1. mysql> SHOW DATABASES;   
  2. ++   
  3. | Database           |   
  4. ++   
  5. | information_schema |   
  6. | mysql              |   
  7. | performance_schema |   
  8. ++   
  9. 3 rows in set (0.00 sec)   

示例:创建一个名为example的数据库,代码如下:

  1. mysql> CREATE DATABASES `example`;   
  2. Query OK, 1 row affected (0.00 sec)   
  3. mysql> SHOW DATABASES;   
  4. ++   
  5. | Database           |   
  6. ++   
  7. | information_schema |   
  8. | example            |   
  9. | mysql              |   
  10. | performance_schema |   
  11. ++   
  12. 4 rows in set (0.00 sec)   

3、删除数据库:

格式:DROP DATABASE 数据库名;

示例:删除example数据库,代码如下:

  1. mysql> DROP DATABASE `example`;   
  2. Query OK, 0 rows affected (0.02 sec)   
  3. mysql> SHOW DATABASES;   
  4. ++   
  5. | Database           |   
  6. ++   
  7. | information_schema |   
  8. | mysql              |   
  9. | performance_schema |   
  10. ++   
  11. 3 rows in set (0.00 sec)   

4、数据库存储引擎

存储引擎就是指表的类型,数据库存储引擎决定了表在计算机的存储方式,MySQL中查询存储引擎的类型命令:SHOW ENGINES;代码如下:

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

查询结果中:

Engine参数指存储引擎名称；

Support参数说明MySQL是否支持该类型引擎；

Comment参数表示对该引擎的评论；

Transaction参数表示是否支持事务处理；

XA参数表示是否分布式交易处理的XA规范；

Savepoints参数表示是否支持保存点,以方便事务的回滚操作；

由上面我们看到InnoDB存储引擎是default的,也就是数据库默认的存储引擎,下面我们简单介绍一下InnoDB.

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

下面就是正式的内容:

5、创建表:

5.1、创建表的形式:

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

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

格式:

USE 数据库名;

示例:创建一个student表,代码如下:

  1. mysql> CREATE DATABASE `example`;   
  2. Query OK, 1 row affected (0.01 sec)   
  3. mysql> USE `example`;   
  4. Database changed   
  5. mysql> CREATE TABLE `student` (   
  6.     -> `id` INT,   
  7.     -> `name` VARCHAR(20)   
  8.     -> );   
  9. Query OK, 0 rows affected (0.11 sec)   

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

约束条件 说明

PRIMARY KEY 标识该属性为该表的主键,可以唯一的标识对应的元组

FOREIGN KEY 标识该属性为该表的外键,是与之联系某表的主键

NOT NULL 标识该属性不能为空

UNIQUE 标识该属性的值是唯一的

AUTO_INCREMENT 标识该属性的值是自动增加,这是MySQL的SQL语句的特色

DEFAULT 为该属性设置默认值

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

5.2、设置表的主键

5.2.1、单字段主键

格式:

属性名 数据类型 PRIMARY KEY

示例,代码如下:

  1. mysql> CREATE TABLE `student1` (   
  2.     -> `id` INT PRIMARY KEY,   
  3.     -> `name` VARCHAR(20)   
  4.     -> );   
  5. Query OK, 0 rows affected (0.07 sec)   

5.2.2、多字段主键

格式:PRIMARY KEY(属性名1,属性名2....属性名n)

示例,代码如下:

  1. mysql> CREATE TABLE `student2` (   
  2.     -> `id` INT,   
  3.     -> `stu_id` INT,   
  4.     -> `name` VARCHAR(20),   
  5.     -> PRIMARY KEY(`id`, `stu_id`)   
  6.     -> );   
  7. Query OK, 0 rows affected (0.05 sec)   

5.3、设置表的外键

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

示例,代码如下:

  1. mysql> CREATE TABLE `teacher` (   
  2.     -> `id` INT PRIMARY KEY,   
  3.     -> `stu_id` INT,   
  4.     -> `name` VARCHAR(20),   
  5.     -> CONSTRAINT `STUID` FOREIGN KEY(`stu_id`) REFERENCES `student1`(`id`)   
  6.     -> );   
  7. Query OK, 0 rows affected (0.08 sec)   

5.4、设置表的非空约束

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

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

5.5、设置表的唯一性约束,就是这个属性的值是不能重复的

格式:属性名 数据类型 UNIQUE

5.6、设置表的属性值自动增加

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

格式:属性名 数据类型 AUTO_INCREMENT

5.7、设置表的属性的默认值

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

下面对5.4-5.7进行综合示例,代码如下:

  1. mysql> CREATE TABLE `student3` (   
  2.     -> `id` INT PRIMARY KEY AUTO_INCREMENT,   
  3.     -> `teacher_id` INT UNIQUE,   
  4.     -> `name` VARCHAR(20) NOT NULL,   
  5.     -> `sex` VARCHAR(10) default 'male'   
  6.     -> );   
  7. Query OK, 0 rows affected (0.07 sec)   

6、查看表结构

6.1、查看表基本结构

语句:DESCRIBE

格式:DESCRIBE 表名;

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

示例,代码如下:

  1. mysql> DESC `student3`;   
  2. ++-++-+-++   
  3. | Field      | Type        | Null | Key | Default | Extra          |   
  4. ++-++-+-++   
  5. | id         | int(11)     | NO   | PRI | NULL    | auto_increment |   
  6. | teacher_id | int(11)     | YES  | UNI | NULL    |                |   
  7. | name       | varchar(20) | NO   |     | NULL    |                |   
  8. | sex        | varchar(10) | YES  |     | male    |                |   
  9. ++-++-+-++   
  10. 4 rows in set (0.00 sec)   

6.2、查看表详细结构

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

语句:SHOW CREATE TABLE

格式:SHOW CREATE TABLE 表名;

示例,代码如下:

  1. mysql> mysql> SHOW CREATE TABLE `student3`;   
  2. ++-+   
  3. | Table    | Create Table                                                                                                                                                                                                                                                                  |   
  4. ++-+   
  5. | student3 | CREATE TABLE `student3` (   
  6.   `id` int(11) NOT NULL AUTO_INCREMENT,   
  7.   `teacher_id` int(11) DEFAULT NULL,   
  8.   `name` varchar(20) NOT NULL,   
  9.   `sex` varchar(10) DEFAULT 'male',   
  10.   PRIMARY KEY (`id`),   
  11.   UNIQUE KEY `teacher_id` (`teacher_id`)   
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   
  13. ++-+   
  14. 1 row in set (0.00 sec)   

7、修改表.

7.1、修改表名

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

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

示例,代码如下:

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

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

7.2、修改字段的数据类型

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

示例,代码如下:

  1. mysql> DESCRIBE `student1`;   
  2. +-+-++-+-+-+   
  3. | Field | Type        | Null | Key | Default | Extra |   
  4. +-+-++-+-+-+   
  5. | id    | int(11)     | NO   | PRI | NULL    |       |   
  6. | name  | varchar(20) | YES  |     | NULL    |       |   
  7. +-+-++-+-+-+   
  8. 2 rows in set (0.00 sec)   
  9. mysql> ALTER TABLE `student1` MODIFY `name` VARCHAR(30);   
  10. Query OK, 0 rows affected (0.09 sec)   
  11. Records: 0  Duplicates: 0  Warnings: 0   
  12. mysql> DESCRIBE `student1`;   
  13. +-+-++-+-+-+   
  14. | Field | Type        | Null | Key | Default | Extra |   
  15. +-+-++-+-+-+   
  16. | id    | int(11)     | NO   | PRI | NULL    |       |   
  17. | name  | varchar(30) | YES  |     | NULL    |       |   
  18. +-+-++-+-+-+   
  19. 2 rows in set (0.00 sec)   

7.3、修改字段名.

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

示例,代码如下:

  1. mysql> DESCRIBE `student1`;   
  2. +-+-++-+-+-+   
  3. | Field | Type        | Null | Key | Default | Extra |   
  4. +-+-++-+-+-+   
  5. | id    | int(11)     | NO   | PRI | NULL    |       |   
  6. | name  | varchar(30) | YES  |     | NULL    |       |   
  7. +-+-++-+-+-+   
  8. 2 rows in set (0.00 sec)   
  9. mysql> ALTER TABLE `student1` CHANGE `name` `stu_name` VARCHAR(40);   
  10. Query OK, 0 rows affected (0.07 sec)   
  11. Records: 0  Duplicates: 0  Warnings: 0   
  12. mysql> DESCRIBE `student1`;   
  13. ++-++-+-+-+   //www.xiariboke.net   
  14. | Field    | Type        | Null | Key | Default | Extra |   
  15. ++-++-+-+-+   
  16. | id       | int(11)     | NO   | PRI | NULL    |       |   
  17. | stu_name | varchar(40) | YES  |     | NULL    |       |   
  18. ++-++-+-+-+   
  19. 2 rows in set (0.00 sec)   

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

7.4、增加字段

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

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

示例,代码如下:

  1. mysql> DESCRIBE `student1`;   
  2. ++-++-+-+-+   
  3. | Field    | Type        | Null | Key | Default | Extra |   
  4. ++-++-+-+-+   
  5. | id       | int(11)     | NO   | PRI | NULL    |       |   
  6. | stu_name | varchar(40) | YES  |     | NULL    |       |   
  7. ++-++-+-+-+   
  8. 2 rows in set (0.00 sec)   
  9. mysql> ALTER TABLE `student1` ADD `teacher_name` VARCHAR(20) NOT NULL AFTER `id`;   
  10. Query OK, 0 rows affected (0.13 sec)   
  11. Records: 0  Duplicates: 0  Warnings: 0   
  12. mysql> DESCRIBE `student1`;   
  13. ++-++-+-+-+   
  14. | Field        | Type        | Null | Key | Default | Extra |   
  15. ++-++-+-+-+   
  16. | id           | int(11)     | NO   | PRI | NULL    |       |   
  17. | teacher_name | varchar(20) | NO   |     | NULL    |       |   
  18. | stu_name     | varchar(40) | YES  |     | NULL    |       |   
  19. ++-++-+-+-+   
  20. 3 rows in set (0.00 sec)   

7.5、删除字段

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

示例,代码如下:

  1. mysql> DESCRIBE `student1`;   
  2. ++-++-+-+-+   
  3. | Field        | Type        | Null | Key | Default | Extra |   
  4. ++-++-+-+-+   
  5. | id           | int(11)     | NO   | PRI | NULL    |       |   
  6. | teacher_name | varchar(20) | NO   |     | NULL    |       |   
  7. | stu_name     | varchar(40) | YES  |     | NULL    |       |   
  8. ++-++-+-+-+   
  9. 3 rows in set (0.00 sec)   
  10. mysql> ALTER TABLE `student1` DROP `teacher_name`;   
  11. Query OK, 0 rows affected (0.09 sec)   
  12. Records: 0  Duplicates: 0  Warnings: 0   
  13. mysql> DESCRIBE `student1`;   
  14. ++-++-+-+-+   
  15. | Field    | Type        | Null | Key | Default | Extra |   
  16. ++-++-+-+-+   
  17. | id       | int(11)     | NO   | PRI | NULL    |       |   
  18. | stu_name | varchar(40) | YES  |     | NULL    |       |   
  19. ++-++-+-+-+   
  20. 2 rows in set (0.00 sec)  

7.6、更改表的存储引擎

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

示例,代码如下:

  1. mysql> SHOW CREATE TABLE `student2`;   
  2. ++-+   
  3. | Table    | Create Table                                                                                                                                                                                                  |   
  4. ++-+   
  5. | student2 | CREATE TABLE `student2` (   
  6.   `id` int(11) NOT NULL DEFAULT '0',   
  7.   `stu_id` int(11) NOT NULL DEFAULT '0',   
  8.   `name` varchar(20) DEFAULT NULL,   
  9.   PRIMARY KEY (`id`,`stu_id`)   
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   
  11. ++-+   
  12. 1 row in set (0.00 sec)   
  13. mysql> ALTER TABLE `student2` ENGINE=MYISAM;   
  14. Query OK, 0 rows affected (0.08 sec)   
  15. Records: 0  Duplicates: 0  Warnings: 0   
  16. mysql> SHOW CREATE TABLE `student2`;   
  17. ++-+   
  18. | Table    | Create Table                                                                                                                                                                                                  |   
  19. ++-+   
  20. | student2 | CREATE TABLE `student2` (   
  21.   `id` int(11) NOT NULL DEFAULT '0',   
  22.   `stu_id` int(11) NOT NULL DEFAULT '0',   
  23.   `name` varchar(20) DEFAULT NULL,   
  24.   PRIMARY KEY (`id`,`stu_id`)   
  25. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |   
  26. ++-+   
  27. 1 row in set (0.00 sec)   

7.7、删除表的外键约束

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

示例,代码如下:

  1. mysql> SHOW CREATE TABLE `teacher`;   
  2. +-+-+   
  3. | Table   | Create Table                                                                                                                                                                                                                                                                        |   
  4. +-+-+   
  5. | teacher | CREATE TABLE `teacher` (   
  6.   `id` int(11) NOT NULL,   
  7.   `stu_id` int(11) DEFAULT NULL,   
  8.   `name` varchar(20) DEFAULT NULL,   
  9.   PRIMARY KEY (`id`),   
  10.   KEY `STUID` (`stu_id`),   
  11.   CONSTRAINT `STUID` FOREIGN KEY (`stu_id`) REFERENCES `student1` (`id`)   
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   
  13. +-+-+   
  14. 1 row in set (0.00 sec)   
  15. mysql> ALTER TABLE `teacher` DROP FOREIGN KEY `STUID`;   
  16. Query OK, 0 rows affected (0.04 sec)   
  17. Records: 0  Duplicates: 0  Warnings: 0   
  18. mysql> SHOW CREATE TABLE `teacher`;   
  19. +-+-+   
  20. | Table   | Create Table                                                                                                                                                                                              |   
  21. +-+-+   
  22. | teacher | CREATE TABLE `teacher` (   
  23.   `id` int(11) NOT NULL,   
  24.   `stu_id` int(11) DEFAULT NULL,   
  25.   `name` varchar(20) DEFAULT NULL,   
  26.   PRIMARY KEY (`id`),   
  27.   KEY `STUID` (`stu_id`)   
  28. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   
  29. +-+-+   
  30. 1 row in set (0.00 sec)   

8、删除表.

格式:DROP TABLE 表名;

删除没有被关联的普通表,直接上面的SQL语句就行了,删除被其他表关联的父表.

方法一:先删除子表,在删除父表

方法二:删除父表的外键约束,上面有介绍,再删该表.

标签：

给我留言