MySQL学习之创建、修改、删除表

发布时间:2020-06-09 21:31:22 作者:hectorHe
来源:网络 阅读:2018

1、如何创建表结构?


  语法格式如下:    

   CREATE TABLE [IF NOT EXISTS] 表名 (
          字段1 数据类型 [完整性约束条件],
          字段2 数据类型 [完整性约束条件],
          ....
          字段n 数据类型 [完整性约束条件]
         );

  说明 :[IF NOT EXISTS] 为可选字段,加上此选项后如果创建的数据表已经存在,不会报错,只会出现警告信息,而不会报错,如果不加此选项会出现报错信息。创建的表的名字不能为SQL语言的关键字如create、updata和order等,每个字段的结束后用逗号隔开,最后一个字段不需要加逗号。

  完整性约束条件也是可选字段,是对字段进行限制。MySQL中常用的完整性约束条件如下表所示:

表1 MySQL中的完整性约束条件

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

下面分小节说明一下,这几个完整性约束条件的测试。


1.1 主键的测试 


   设置主键的目的是可以帮助MySQL以最快的速度查找表中的某一条信息。

   特点:主键必须唯一;

       任意两条表中记录主键的字段不能相同;

       主键必须是非空值;

       主键可以是单一字段,也可以是多个字段的组合。

   单个字段的主键测试

   语法规则:字段名 数据类型 PRIMARY KEY  

    实例:创建一张student表,设置stu_id为主键,实现代码如下:
    
      CREATE TABLE IF NOT EXISTS student(
        stu_id INT PRIMARY KEY ,
        stu_name VARCHAR(20),
        stu_age INT,
        stu_sex ENUM('男','女','保密')
    );

   多个主键的测试

   语法规则:PRIMARY KEY(字段1,字段2,...字段n)

    实例:创建一张teacher表,设置主键为tea_id和course_id,实现代码如下:
    
    CREATE TABLE IF NOT EXISTS teacher(
        tea_id TINYINT,
        name VARCHAR(20),
        course_id INT,
        PRIMARY KEY(tea_id,course_id)
        );

1.2 外键的测试


  语法规则如下:

   CONSTRAINT 外键别名 FOREIGN KEY(字段1.1 ,字段1.2,字段1.3, ...)

        REFERENCES 表名(字段2.1 ,字段2.2,...)

   说明:‘外键别名’是外键的代号;字段1中的参数列表是在子表中设置的外键,‘表名’参数是父表的名称;‘字段2’参数列表是父表的主键。

   实例:新建一张grade表,设置stu_id为外键,与student表中的主键stu_id相关联。

SQL代码如下:

CREATE TABLE IF NOT EXISTS grade(
        id INT PRIMARY KEY,
        stu_id INT,
        stu_name VARCHAR(20),
        `数学` FLOAT,
        `英语` FLOAT,
        `语文` FLOAT,
        CONSTRAINT grade_fk FOREIGN KEY (stu_id)
            REFERENCES student(stu_id)        
);

注意:创建的子表的外键必须是父表的主键。并且两者的数据类型必须是一致的,如果不一致,则不能创建成功。


1.3 非空约束的测试


  非空约束就是要求表中设置的字段的值不能为空值,如果用户插入的字段值为空值时,此时数据库就会出现报错信息,不能正确的插入数据,‘NOT NULL’ 约束经常与“DEFAULT”约束条件联合使用

  基本语法规则:

    字段 数据类型 NOT NULL

  实例:重新创建一张student2表,设置sex字段为非空。

 CREATE TABLE IF NOT EXISTS student2(
    id TINYINT PRIMARY KEY,
    name VARCHAR(20),
    sex ENUM('男','女','保密') NOT NULL,
    age TINYINT
);

1.4 测试唯一性约束   


  唯一性约束就是指所有记录中的该字段的值不能够重复出现,例如,每个人的×××号都是不同的,可以将×××字段设置为唯一性,当插入的数据出现两个相同的×××号时,数据库就会出现告警信息。简单的说,唯一性约束条件要求所有记录该字段的值不能重复出现。

  基本语法如下:

     字段 数据类型 UNIQUE

  实例:创建一张student3表,设置id字段的属性为唯一性,SQL 代码如下:

CREATE TABLE IF NOT EXISTS student3(
        id INT NOT NULL UNIQUE,
        name VARCHAR(20),
        age TINYINT
);

1.5 测试自增长


   AUTO_INCREMENT 是MySQL数据库中一种特殊的约束条件,为表中插入的新纪录自动生成一个唯一的ID。且一张表中只能有一个字段使用AUTO_INCREMENT约束,该字段必须为主键的一部分,被约束的字段的类型可以为任何整数类型(INT TINYINT SMALLINT MEDIUMINT BIGINT)默认情况下该字段从1开始自增长。

  基本语法:字段名 数据类型 AUTO_INCREMENT

  实例:新建一张表student4,设置字段id为自增长属性。

CREATE TABLE IF NOT EXISTS student4(
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(30) ,
        age TINYINT
);

1.6 测试默认值


  在创建表时可以为表中的字段添加默认值,如果插入数据时没有为这个字段插入数据,则这个字段就会为自动添加一个默认值。通过DEFAULT关键字来设置默认值的。

   基本语法:字段 数据类型 DEFAULT 默认值

   实例:创建一张表student5,位表中字段age和sex设置默认值,SQL代码如下:

CREATE TABLE IF NOT EXISTS student5(
        id INT PRIMARY KEY ,
        name VARCHAR(20) NOT NULL,
        age TINYINT DEFAULT '20',
        sex ENUM('男','女','保密') DEFAULT '男'
  );

2、如何查询表结构?


  数据库定义完成后,可以通过查询语句来查看已经定义好的数据库,经常使用的查询语句为

DESCRIBE和SHOW CREATE TABLE通过这两个语句可以查看表的字段名、数据类型和完整性约束条件。


2.1 测试DESCRIBE语句


   语法结构: DESCRIBE 表名;

   实例:查询一下刚刚建立好的student表结构。

 Thu Dec 08 00:58:40 2016localhosttest_db>DESCRIBE student;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(20)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

DESCRIBE 可以简写为DESC,代码运行如下:

Thu Dec 08 00:59:02 2016localhosttest_db>DESC student;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(20)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

从查询结果中,可以看到字段名(Field)、数据类型(Type)、是否为空(null)、是否为主外键(key)、默认值(Default)、额外信息(Extra)。


2.2 测试 SHOW CREATE TABLE 语句


   通过SHOW CREATE TABLE语句可以查看更加详细的信息,除了能够查询字段名、数据类型、完整性约束条件信息外,还可以查看表的存储引擎和使用的字符编码。

   基本语法: SHOW CREATE TABLE 表名;

   实例:要查看刚刚建好的student1的表结构,SQL语句显示如下:

Thu Dec 08 01:12:27 2016localhosttest_db>SHOW CREATE TABLE student2 \G
*************************** 1. row ***************************
       Table: student2
Create Table: CREATE TABLE `student2` (
  `id` tinyint(4) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` enum('男','女','保密') NOT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注:后面加\G结尾可以使得显示的结果更加直观。


3、如何修改表?


  数据库中的表建立好以后,可以通过SQL语句修改已经建立好的数据表。MySQL通过ALTER TABLE 语句修改表中的元素。例如,可以修改表名、修改字段的数据类型、修改字段名、增加字段、删除字段、修改字段的排列位置、更改默认的存储引擎和删除表的外键约束等。


3.1 修改表名


   通过表名在一个数据库下是唯一确定的,不可能存在两个相同的数据表名。有时候为了实际需要,我们需要更改数据表的名字,重新建立一张一样的表费时费力,这时我们可以通过SQL语句ALTER TABLE修改表的名字。

  基本语法:ALTER TABLE 旧的表名 RENAME [TO |AS] 新表名;

  实例:我们要把表student表名改为stu,把student2表名改为stu1,把student3表名改为stu2。SQL代码如下:

--先查看一下库中都有那些表
Thu Dec 08 01:11:32 2016localhosttest_db>SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| grade             |
| student           |
| student2          |
| student3          |
| student4          |
| student5          |
| teacher           |
+-------------------+
7 rows in set (0.00 sec)
-- 执行SQL 代码
ALTER TABLE student RENAME TO stu;
ALTER TABLE student2 RENAME AS stu1;
ALTER TABLE student3 RENAME stu2;
-- 通过SHOW TABLES语句查看是否更改成功

Thu Dec 08 01:33:59 2016localhosttest_db>SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| grade             |
| stu               |
| stu1              |
| stu2              |
| student4          |
| student5          |
| teacher           |
+-------------------+
7 rows in set (0.00 sec)

说明:[TO|AS]为可选参数,可加可不加。


3.2 修改字段的数据类型


  用ALTER TABLE 语句可以修改字段的数据类型,基本语法如下:

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

  实例:将stu数据表中的name 字段的数据类型修改为VARCHAR(30),SQL代码如下:

--先查看一下stu_name 现有的数据类型
Thu Dec 08 01:34:09 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(20)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

-- 执行SQL代码,将stu_name的数据类型修改为VARCHAR(30)

Thu Dec 08 01:44:53 2016localhosttest_db>ALTER TABLE stu MODIFY stu_name VARCHAR(30);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询修改后的stu_name的数据类型

Thu Dec 08 01:45:55 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(30)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

 

3.3 修改表中的字段名


   有时候我们需要修改表的字段名,通过ALTER TABLE 语句可以修改表的字段名。

   基本语法如下:

    ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据属性;

   实例1:修改表stu中的字段'stu_name'改为name,不改变他的数据类型。SQL语句实现如下:

-- 先查看一下stu的表结构

Thu Dec 08 20:54:26 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| stu_id   | int(11)                | NO   | PRI | NULL    |       |
| stu_name | varchar(30)            | YES  |     | NULL    |       |
| stu_age  | int(11)                | YES  |     | NULL    |       |
| stu_sex  | enum('男','女','保密') | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


-- 执行ALTER TABLE 语句修改字段的名字
Thu Dec 08 20:54:40 2016localhosttest_db>ALTER TABLE stu CHANGE stu_name name VARCHAR(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 20:56:56 2016localhosttest_db>DESC stu;
+---------+------------------------+------+-----+---------+-------+
| Field   | Type                   | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| stu_id  | int(11)                | NO   | PRI | NULL    |       |
| name    | varchar(30)            | YES  |     | NULL    |       |
| stu_age | int(11)                | YES  |     | NULL    |       |
| stu_sex | enum('男','女','保密') | YES  |     | NULL    |       |
+---------+------------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

 

 实例2:修改字段名同时修改字段的数据类型,将stu表中的字段'stu_id'修改为'id',数据类型变为TINYINT,约束条件仍为主键。将'stu_age'改为‘age’,数据类型为TINYINT,完整性约束条件设为‘非空’,

字段‘stu_sex’改为sex 数据类型不变,完整性约束条件改为‘DEFAULT '保密'’。执行代码如下:

Thu Dec 08 21:32:00 2016localhosttest_db>DESC stu;
+---------+------------------------+------+-----+---------+-------+
| Field   | Type                   | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| stu_id  | int(11)                | NO   | PRI | 0       |       |
| name    | varchar(30)            | YES  |     | NULL    |       |
| stu_age | int(11)                | YES  |     | NULL    |       |
| stu_sex | enum('男','女','保密') | YES  |     | NULL    |       |
+---------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Thu Dec 08 21:32:12 2016localhosttest_db>ALTER TABLE stu CHANGE stu_id id TINYINT primary key;
ERROR 1068 (42000): Multiple primary key defined
-- 需要注意的是,修改主键名时,后面不需要添加 PRIMARY KEY 关键字,否则会出现以上报错信息。

Thu Dec 08 21:33:03 2016localhosttest_db>ALTER TABLE stu CHANGE stu_id id TINYINT ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 21:34:35 2016localhosttest_db>ALTER TABLE stu CHANGE stu_age age TINYINT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 21:35:31 2016localhosttest_db>ALTER TABLE stu CHANGE stu_sex sex ENUM ('男','女','保密') DEFAULT '保密';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 21:37:29 2016localhosttest_db>DESC stu;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| id    | tinyint(4)             | NO   | PRI | 0       |       |
| name  | varchar(30)            | YES  |     | NULL    |       |
| age   | tinyint(4)             | YES  |     | NULL    |       |
| sex   | enum('男','女','保密') | YES  |     | 保密    |       |
+-------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
-- 最后,查询一下表结构,可以看到表中的字段已经修改为我们需要的字段类型了。


3.4 为已经建好的表增加字段


  对于已经建好的数据表来说,如果我们想对表进行修改,为表中再添加一个新的字段,使用ALTER TABLE 语句可以为表中增加一个新的字段。它的基本语法如下:

  ALTER TABLE 表名 ADD 新的字段名 数据类型 [完整性约束条件] [FIRST|AFTER 已有的字段名];

 实例1:为stu表增加一个新的字段 Tel 不需要添加任何的完整性约束条件。SQL代码如下:

-- 先查看一下表结构

Thu Dec 08 21:37:29 2016localhosttest_db>DESC stu;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| id    | tinyint(4)             | NO   | PRI | 0       |       |
| name  | varchar(30)            | YES  |     | NULL    |       |
| age   | tinyint(4)             | YES  |     | NULL    |       |
| sex   | enum('男','女','保密') | YES  |     | 保密    |       |
+-------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 为表增加一个新的字段tel
Thu Dec 08 21:37:39 2016localhosttest_db>ALTER TABLE stu ADD tel CHAR(11);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询一下表结构,可以看到表stu增加的一个新的字段tel

Thu Dec 08 21:55:42 2016localhosttest_db>DESC stu;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| id    | tinyint(4)             | NO   | PRI | 0       |       |
| name  | varchar(30)            | YES  |     | NULL    |       |
| age   | tinyint(4)             | YES  |     | NULL    |       |
| sex   | enum('男','女','保密') | YES  |     | 保密    |       |
| tel   | char(11)               | YES  |     | NULL    |       |
+-------+------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

 实例2:为stu表添加一个字段address ,设置完整性约束条件为‘非空’。SQL代码如下:

 

Thu Dec 08 21:55:49 2016localhosttest_db>ALTER TABLE stu ADD address VARCHAR(20) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:00:47 2016localhosttest_db>DESC stu;
+---------+------------------------+------+-----+---------+-------+
| Field   | Type                   | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| id      | tinyint(4)             | NO   | PRI | 0       |       |
| name    | varchar(30)            | YES  |     | NULL    |       |
| age     | tinyint(4)             | YES  |     | NULL    |       |
| sex     | enum('男','女','保密') | YES  |     | 保密    |       |
| tel     | char(11)               | YES  |     | NULL    |       |
| address | varchar(20)            | NO   |     | NULL    |       |
+---------+------------------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

 测试3:在表的第一个位置增加字段,在stu表中增加num字段,其SQL代码如下: 

Thu Dec 08 22:00:53 2016localhosttest_db>ALTER TABLE stu ADD num TINYINT FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:28:24 2016localhosttest_db>DESC stu;
+---------+------------------------+------+-----+---------+-------+
| Field   | Type                   | Null | Key | Default | Extra |
+---------+------------------------+------+-----+---------+-------+
| num     | tinyint(4)             | YES  |     | NULL    |       |
| id      | tinyint(4)             | NO   | PRI | 0       |       |
| name    | varchar(30)            | YES  |     | NULL    |       |
| age     | tinyint(4)             | YES  |     | NULL    |       |
| sex     | enum('男','女','保密') | YES  |     | 保密    |       |
| tel     | char(11)               | YES  |     | NULL    |       |
| address | varchar(20)            | NO   |     | NULL    |       |
+---------+------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

 测试4:在指定位置之后添加一个字段。

  

-- 在stu表的tel 字段后面添加一个新的字段‘birthday’
Thu Dec 08 22:28:38 2016localhosttest_db>ALTER TABLE stu ADD birthday DATE AFTER tel;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:34:52 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| num      | tinyint(4)             | YES  |     | NULL    |       |
| id       | tinyint(4)             | NO   | PRI | 0       |       |
| name     | varchar(30)            | YES  |     | NULL    |       |
| age      | tinyint(4)             | YES  |     | NULL    |       |
| sex      | enum('男','女','保密') | YES  |     | 保密    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| address  | varchar(20)            | NO   |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)


3.5 删除字段


   删除字段是指我们可以删除表中已经定义好的字段。使用ALTER TABLE 可以实现将表中的某个定义好的字段进行删除操作。其基本语法如下:

   ALTER TABLE 表名 DROP 字段名;

   例如,表stu中的字段num我们不在需要,现在需要对它进行删除操作,SQL代码如下:

Thu Dec 08 22:42:31 2016localhosttest_db>ALTER TABLE stu DROP num;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:42:53 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| id       | tinyint(4)             | NO   | PRI | 0       |       |
| name     | varchar(30)            | YES  |     | NULL    |       |
| age      | tinyint(4)             | YES  |     | NULL    |       |
| sex      | enum('男','女','保密') | YES  |     | 保密    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| address  | varchar(20)            | NO   |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)


3.6 修改字段的排列位置


  使用ALTER TABLE 语句也可以修改表中字段的排列位置,基本语法如下:

  ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;

 说明:字段名1 是需要改变位置的字段名称;“数据类型”是字段1的数据类型;FIRST 参数是将字段1改变到第一个位置;“AFTER 字段名2”参数是将字段1加入到字段2的后面。

 

--测试将stu表中的name字段插入到第一个位置,将字段‘sex’修改到字段‘tel’的后面
ALTER TABLE stu MODIFY name VARCHAR(30) FIRST;
ALTER TABLE stu MODIFY sex ENUM('男','女','保密')  AFTER tel;

Thu Dec 08 22:43:08 2016localhosttest_db>ALTER TABLE stu MODIFY name VARCHAR(30) FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:56:00 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| name     | varchar(30)            | YES  |     | NULL    |       |
| id       | tinyint(4)             | NO   | PRI | 0       |       |
| age      | tinyint(4)             | YES  |     | NULL    |       |
| sex      | enum('男','女','保密') | YES  |     | 保密    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| address  | varchar(20)            | NO   |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
Thu Dec 08 22:56:12 2016localhosttest_db>ALTER TABLE stu MODIFY sex ENUM('男','女','保密')  AFTER tel;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 22:56:26 2016localhosttest_db>DESC stu;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| name     | varchar(30)            | YES  |     | NULL    |       |
| id       | tinyint(4)             | NO   | PRI | 0       |       |
| age      | tinyint(4)             | YES  |     | NULL    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
| sex      | enum('男','女','保密') | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| address  | varchar(20)            | NO   |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
7 rows in set (0.02 sec)

  

3.7 更改表的存储引擎


  MySQL中常用的存储引擎有三个:InnoDB、MyISAM、MEMORY等。通过ALTER TABLE 语句可以更改表的存储引擎类型。其语法结构如下:

  ALTER TABLE 表名 ENGINE=存储引擎名;

 例如,将stu表的存储引擎改为 MyISAM,SQL 代码如下:

Thu Dec 08 22:56:34 2016localhosttest_db>ALTER TABLE stu ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 23:03:35 2016localhosttest_db>SHOW CREATE TABLE stu \G
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `name` varchar(30) DEFAULT NULL,
  `id` tinyint(4) NOT NULL DEFAULT '0',
  `age` tinyint(4) DEFAULT NULL,
  `tel` char(11) DEFAULT NULL,
  `sex` enum('男','女','保密') DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

 注意:如果表中已经有很多数据,不建议更改表的存储引擎,如果更改了存储引擎可能会发生一些意料之外的影响。


3.8 删除表的外键的约束


   外键是一个特殊的字段,它将某一表与父表建立关联关系。 在创建表的时候外键约束就已经设定好了,如果想要去除与父表之间的关联,可以用ALTER TABLE 语句删除外键的约束。基本语法如下:

   ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

-- 创建表grade 设置stu_num为表student主键的外键
Thu Dec 08 23:36:16 2016localhosttest_db>CREATE TABLE IF NOT EXISTS grade(
    -> 	id INT PRIMARY KEY,
    ->         stu_num INT ,
    ->         english FLOAT,
    ->  	math   FLOAT,
    ->       CONSTRAINT g_fk FOREIGN KEY (stu_num)
    ->        REFERENCES student(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

-- 查看一下grade的表结构

Thu Dec 08 23:40:53 2016localhosttest_db>SHOW CREATE TABLE grade;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grade | CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `english` float DEFAULT NULL,
  `math` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `g_fk` (`stu_num`),
  CONSTRAINT `g_fk` FOREIGN KEY (`stu_num`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 删除外键约束

Thu Dec 08 23:41:18 2016localhosttest_db>ALTER TABLE grade DROP FOREIGN KEY g_fk;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 23:42:45 2016localhosttest_db>SHOW  CREATE TABLE grade;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grade | CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `english` float DEFAULT NULL,
  `math` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `g_fk` (`stu_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


4.删除表


  删除表时指删除已经存在的数据表。删除表同时也会删除表中的所有数据,因此,删除前应做好必要的准备。MySQL中使用 DROP TABLE 语句来删除表。对于一些表来说,创建表的时候存在着外键的约束,一些表成为了与之相联系的表的父表。要删除这些父表,情况就比较复杂了。


4.1 删除没有被关联的普通表

   直接使用DROP TABLE语句删除没有被关联的普通表,使用语法如下:

   DROP TABLE 表名;

   例如,要删除student5表,SQL 语句如下:

-- 先查看一下student5的表结构
Thu Dec 08 23:22:02 2016localhosttest_db>DESC student5;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| id    | int(11)                | NO   | PRI | NULL    |       |
| name  | varchar(20)            | NO   |     | NULL    |       |
| age   | tinyint(4)             | YES  |     | 20      |       |
| sex   | enum('男','女','保密') | YES  |     | 男      |       |
+-------+------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

-- 执行删除代码

Thu Dec 08 23:23:46 2016localhosttest_db>DROP TABLE student5;
Query OK, 0 rows affected (0.00 sec)

Thu Dec 08 23:24:05 2016localhosttest_db>DESC student5;
ERROR 1146 (42S02): Table 'test_db.student5' doesn't exist

4.2 删除有外键关联的表

  

  要想删除一个带有外键关联的表,首先,需要删除表的外键,然后才能删除表结构,SQL语句如下:


-- 先创建一个grade表,字段stu_num为表student主键的外键
Thu Dec 08 23:48:16 2016localhosttest_db>CREATE TABLE IF NOT EXISTS grade(
    -> 	id INT PRIMARY KEY,
    ->         stu_num INT ,
    ->         english FLOAT,
    ->  	math   FLOAT,
    ->       CONSTRAINT g_fk FOREIGN KEY (stu_num)
    ->        REFERENCES student(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 查看一下grade的表结构

Thu Dec 08 23:48:51 2016localhosttest_db>SHOW CREATE TABLE grade \G
*************************** 1. row ***************************
       Table: grade
Create Table: CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `english` float DEFAULT NULL,
  `math` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `g_fk` (`stu_num`),
  CONSTRAINT `g_fk` FOREIGN KEY (`stu_num`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- 执行一下删除命令,删除表student,发现不能执行删除命令

Thu Dec 08 23:49:19 2016localhosttest_db>DROP TABLE student;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

-- 执行命令,删除一下表grade的外键约束

Thu Dec 08 23:49:39 2016localhosttest_db>ALTER TABLE grade DROP FOREIGN KEY g_fk;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thu Dec 08 23:50:46 2016localhosttest_db>SHOW CREATE TABLE grade \G
*************************** 1. row ***************************
       Table: grade
Create Table: CREATE TABLE `grade` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `english` float DEFAULT NULL,
  `math` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `g_fk` (`stu_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- 再次执行一下删除命令,发现成功删除了student表

Thu Dec 08 23:50:54 2016localhosttest_db>DROP TABLE student;
Query OK, 0 rows affected (0.02 sec)
Thu Dec 08 23:51:13 2016localhosttest_db>SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| animalinfo        |
| grade             |
| stu               |
| teacher           |
+-------------------+
4 rows in set (0.00 sec)


  


   










推荐阅读:
  1. MySQL之表的创建、删除、修改、删除、查看及索引
  2. Oracle进阶学习之创建dblink

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 创建表 修改表

上一篇:新手学习 react 迷惑的点(一)

下一篇:seci-log 1.05 发布,日志分析增加业务系统日志

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》