第三章 数据表的基本操作

第三章   数据表的基本操作

 

4.1 创建数据表

在创建完数据库之后，接下来我们就需要创建数据表。创建数据表是指在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性约束的过程。

4.1.1  创建表的语法形式

  数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库名>” 指定操作是从哪个数据库中进行，如果没有选择数据库，会报错

语法：

CREATE TABLE <表名>

（

字段名1， 数据类型 [列级别约束条件] [默认值]，

字段名2， 数据类型 [列级别约束条件] [默认值]，

... ...

）：

例如：

mysql> CREATE TABLE tb_emp1

    -> (

    -> id INT(11),

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT

    -> );

Query OK, 0 rows affected (0.08 sec)

mysql> SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| tb_emp1        |

+----------------+

1 row in set (0.05 sec)

 

4.1.2 使用主键约束

主键约束要求主键列的数据唯一，并且不允许为空。

1、单字段主键

语法：

字段名   数据类型  PRIMARY KEY [默认值]

例如：

mysql> CREATE TABLE tb_emp2

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(25),

    -> deptID INT(11),

    -> salary FLOAT

    -> );

Query OK, 0 rows affected (0.05 sec)

2、在定义完所有列之后指定主键

语法：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

例如：

mysql> CREATE TABLE tb_emp3

    -> (

    -> id INT(11),

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> PRIMARY KEY(id)

    -> );

Query OK, 0 rows affected (0.01 sec)

3、多字段联合主键

语法：

PRIMARY KEY [字段1，字段2，....]

例如：

mysql> CREATE TABLE tb_emp4

    -> (

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> PRIMARY KEY(name,deptId)

    -> );

Query OK, 0 rows affected (0.00 sec)

 

4.1.3 使用外键约束

外键用来在两个表数据之间建立连接，它可以是一列或者多列

语法：

[CONSTRAINT<外键名>] FOREIGN KEY [字段名1，字段名2...]

  REFERENCES<主表名>  主键列1[主键列2...]     

例如：

mysql> CREATE TABLE tb_dept1

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(22) NOT NULL,

    -> location VARCHAR(50)

    -> );

Query OK, 0 rows affected (0.00 sec)

 

mysql> CREATE TABLE tb_emp5

 (

 id INT(11) PRIMARY KEY,

 name VARCHAR(25), 

 deptId INT(11),

 salary FLOAT,

  CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)

 );

Query OK, 0 rows affected (0.06 sec)

需要有相同的 主键，俩张表可以放在一起进行查看

4.1.4  使用非空约束

非空约束指字段的值不能为空。

语法：

字段名  数据类型  not null

例如：

mysql> CREATE TABLE tb_emp6

    -> (

    -> id INT(11) PRIMARY KEY ,

    -> name VARCHAR(25) NOT NULL,

    -> deptId INT(11),

    -> salary FLOAT

    -> );

Query OK, 0 rows affected (0.06 sec)

或者

语法：

[CONSTRATIN <约束名>] UNIQUE （<字段名>）

例如：

mysql> CREATE TABLE tb_dept3

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(22),

    -> location VARCHAR(50),

    -> CONSTRAINT STH UNIQUE(name)

    -> );

Query OK, 0 rows affected (0.00 sec)

使用唯一性条件约束

unique

4.1.6  使用默认约束

默认约束指定某列的默认值。

语法：

字段名  数据类型  DEFAULT 默认值

例如：

mysql> CREATE TABLE tb_emp7

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(25) NOT NULL,

    -> deptId INT(11) DEFAULT 1111,

    -> salary FLOAT,

    -> info VARCHAR(50)

    -> );

Query OK, 0 rows affected (0.00 sec)

 

4.1.7  设置表的属性值自动增加

语法：

字段名  数据类型  AUTO_INCREMENT

例如：

mysql> CREATE TABLE tb_emp8

    -> (

    -> id INT(11) PRIMARY KEY AUTO_INCREMENT,

    -> name VARCHAR(25) NOT NULL,

    -> deptId INT(11),

    -> salary FLOAT

    -> );

Query OK, 0 rows affected (0.00 sec)

插入数据验证：

mysql> INSERT INTO tb_emp8(name,salary)

    -> VALUES('lucy',1000),('lura',1200),('kevin',1500);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

查看：

mysql> SELECT * FROM tb_emp8;

+----+-------+--------+--------+

| id | name  | deptId | salary |

+----+-------+--------+--------+

|  1 | lucy  |   NULL |   1000 |

|  2 | lura  |   NULL |   1200 |

|  3 | kevin |   NULL |   1500 |

+----+-------+--------+--------+

3 rows in set (0.00 sec)

 

 

4.2  查看数据表结构

4.2.1  查看表基本结构语句DESCRIBE

语法：

DESCRIBE  表名；

或

DESC  表名；

 

例如：

mysql> DESCRIBE tb_dept1;

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| id       | int(11)     | NO   | PRI | NULL    |       |

| name     | varchar(22) | NO   |     | NULL    |       |

| location | varchar(50) | YES  |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

或者

mysql> DESC tb_dept1;

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| id       | int(11)     | NO   | PRI | NULL    |       |

| name     | varchar(22) | NO   |     | NULL    |       |

| location | varchar(50) | YES  |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

 

4.2.2  查看表详细结构语句

语法：

SHOW  CREATE  TABLE  <表名\G>

mysql> SHOW CREATE TABLE tb_emp1\G

*************************** 1. row ***************************

       Table: tb_emp1

Create Table: CREATE TABLE `tb_emp1` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(25) DEFAULT NULL,

  `deptId` int(11) DEFAULT NULL,

  `salary` float DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

4.3  修改数据表

4.3.1  修改表名

语法:

ALTER TABLE <旧表名>  RENAME  [TO]  <新表名>.

例如：

mysql> SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| tb_dept1       |

| tb_dept3       |

| tb_emp1        |

| tb_emp2        |

| tb_emp3        |

| tb_emp4        |

| tb_emp5        |

| tb_emp7        |

| tb_emp8        |

+----------------+

9 rows in set (0.00 sec)

 

mysql> ALTER TABLE tb_dept3 RENAME tb_deptment3;

Query OK, 0 rows affected (0.05 sec)

 

mysql> SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| tb_dept1       |

| tb_deptment3   |

| tb_emp1        |

| tb_emp2        |

| tb_emp3        |

| tb_emp4        |

| tb_emp5        |

| tb_emp7        |

| tb_emp8        |

+----------------+

9 rows in set (0.00 sec)

 

4.3.2  修改字段的数据类型

语法：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

例如：

mysql> DESC tb_dept1;

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| id       | int(11)     | NO   | PRI | NULL    |       |

| name     | varchar(22) | NO   |     | NULL    |       |

| location | varchar(50) | YES  |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

 

mysql> ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

Query OK, 0 rows affected (0.08 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC tb_dept1;

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| id       | int(11)     | NO   | PRI | NULL    |       |

| name     | varchar(30) | YES  |     | NULL    |       |

| location | varchar(50) | YES  |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

 

4.3.3  修改字段名

语法：

ALTER TABLE <表名> CHANGE<旧字段名><新字段名> <新数据类型>

例如：

mysql> ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);

Query OK, 0 rows affected (0.10 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC tb_dept1;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)     | NO   | PRI | NULL    |       |

| name  | varchar(30) | YES  |     | NULL    |       |

| loc   | varchar(50) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

 

4.3.4  添加字段

语法：

ALTER TABLE <表名> ADD <新字段名><数据类型> [约束条件] [FIRST|AFTER 已存在字段名]

例如：

mysql> DESC tb_dept1;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(11)     | NO   | PRI | NULL    |       |

| name  | varchar(30) | YES  |     | NULL    |       |

| loc   | varchar(50) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

 

mysql> ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC tb_dept1;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| id      | int(11)     | NO   | PRI | NULL    |       |

| name    | varchar(30) | YES  |     | NULL    |       |

| loc     | varchar(50) | YES  |     | NULL    |       |

| column1 | varchar(12) | NO   |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

 

或者

 

mysql> ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC tb_dept1;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| column2 | int(11)     | YES  |     | NULL    |       |

| id      | int(11)     | NO   | PRI | NULL    |       |

| name    | varchar(30) | YES  |     | NULL    |       |

| loc     | varchar(50) | YES  |     | NULL    |       |

| column1 | varchar(12) | NO   |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

 

或者

 

mysql> ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name ;

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC tb_dept1;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| column2 | int(11)     | YES  |     | NULL    |       |

| id      | int(11)     | NO   | PRI | NULL    |       |

| name    | varchar(30) | YES  |     | NULL    |       |

| column3 | int(11)     | YES  |     | NULL    |       |

| loc     | varchar(50) | YES  |     | NULL    |       |

| column1 | varchar(12) | NO   |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

 

4.3.5  删除字段

语法：

ALTER  TABLE  <表名>  DROP  <字段名>

例如：

mysql> ALTER TABLE tb_dept1 DROP column2;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC tb_dept1;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| id      | int(11)     | NO   | PRI | NULL    |       |

| name    | varchar(30) | YES  |     | NULL    |       |

| column3 | int(11)     | YES  |     | NULL    |       |

| loc     | varchar(50) | YES  |     | NULL    |       |

| column1 | varchar(12) | NO   |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

 

4.3.6  修改字段的排列位置

语法：

ALTER  TABLE  <表名>  MODIFY <字段名>  <数据类型>  FIRST | AFTER <字段2>

例如：

mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC tb_dept1;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| column1 | varchar(12) | YES  |     | NULL    |       |

| id      | int(11)     | NO   | PRI | NULL    |       |

| name    | varchar(30) | YES  |     | NULL    |       |

| column3 | int(11)     | YES  |     | NULL    |       |

| loc     | varchar(50) | YES  |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

5 rows in set (0.01 sec)

 

或者

 

mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER loc;

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> DESC tb_dept1;

+---------+-------------+------+-----+---------+-------+

| Field   | Type        | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| id      | int(11)     | NO   | PRI | NULL    |       |

| name    | varchar(30) | YES  |     | NULL    |       |

| column3 | int(11)     | YES  |     | NULL    |       |

| loc     | varchar(50) | YES  |     | NULL    |       |

| column1 | varchar(12) | YES  |     | NULL    |       |

+---------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

 

4.3.7  更改表的存储引擎

语法：

ALTER TABLE <表名> ENGINE=<更改后的存储引擎>

例如：

mysql> SHOW CREATE TABLE tb_deptment3\G

*************************** 1. row ***************************

       Table: tb_deptment3

Create Table: CREATE TABLE `tb_deptment3` (

  `id` int(11) NOT NULL,

  `name` varchar(22) DEFAULT NULL,

  `location` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `STH` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

mysql> ALTER TABLE tb_deptment3 ENGINE=MyISAM;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> SHOW CREATE TABLE tb_deptment3\G

*************************** 1. row ***************************

       Table: tb_deptment3

Create Table: CREATE TABLE `tb_deptment3` (

  `id` int(11) NOT NULL,

  `name` varchar(22) DEFAULT NULL,

  `location` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `STH` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

4.3.8  删除表的外键约束

语法：

ALTER  TABLE  <表名>  DROP  FOREIGN  KEY  <外键约束名>

例如：

mysql> CREATE TABLE tb_emp9

    -> (

    -> id INT(11) PRIMARY KEY,

    -> name VARCHAR(25),

    -> deptId INT(11),

    -> salary FLOAT,

    -> CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1(id)

    -> );

Query OK, 0 rows affected (0.04 sec)

 

mysql> SHOW CREATE TABLE tb_emp9\G

*************************** 1. row ***************************

       Table: tb_emp9

Create Table: CREATE TABLE `tb_emp9` (

  `id` int(11) NOT NULL,

  `name` varchar(25) DEFAULT NULL,

  `deptId` int(11) DEFAULT NULL,

  `salary` float DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_emp_dept` (`deptId`),

  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

mysql> ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

Query OK, 0 rows affected (0.08 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> SHOW CREATE TABLE tb_emp9\G

*************************** 1. row ***************************

       Table: tb_emp9

Create Table: CREATE TABLE `tb_emp9` (

  `id` int(11) NOT NULL,

  `name` varchar(25) DEFAULT NULL,

  `deptId` int(11) DEFAULT NULL,

  `salary` float DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_emp_dept` (`deptId`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

4.4  删除数据表

4.4.1  删除没有被关联的表

语法：

DROP TABLE [IF EXISTS]表1，表2...

例如：

mysql> DROP TABLE IF EXISTS tb_dept2;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| tb_dept1       |

| tb_deptment3   |

| tb_emp1        |

| tb_emp2        |

| tb_emp3        |

| tb_emp4        |

| tb_emp5        |

| tb_emp7        |

| tb_emp8        |

| tb_emp9        |

+----------------+

10 rows in set (0.00 sec)