数据表的基本操作例题（stage1-6）

今天药忘吃喽~ 2022-06-04 10:18 196阅读 0赞

# 第四章 数据表的基本操作 #

【例4.1】创建员工表tb\_emp1。  
首先创建数据库，SQL语句如下：

CREATE DATABASE test_db;

选择创建表的数据库，SQL语句如下：

USE test_db;

创建tb\_emp1表，SQL语句为：

CREATE TABLE tb_emp1 ( id INT(11), name VARCHAR(25), deptId INT(11), salary FLOAT );

语句执行后，便创建了一个名称为tb\_emp1的数据表，使用SHOW TABLES；语句查看数据表是否创建成功，SQL语句如下：

SHOW TABLES; +-----------------------+
    | Tables_in_ test_db | +----------------------+
    | tb_emp1 | +----------------------+
    1 row in set (0.00 sec)

可以看到，test\_db数据库中已经有了数据表tb\_tmp1，数据表创建成功。  
【例4.2】定义数据表tb\_emp 2，其主键为id，SQL语句如下：

CREATE TABLE tb_emp2 ( id INT(11) PRIMARY KEY, name VARCHAR(25), deptId INT(11), salary FLOAT );

【例4.3】定义数据表tb\_emp 3，其主键为id，SQL语句如下：

CREATE TABLE tb_emp3 ( id INT(11), name VARCHAR(25), deptId INT(11), salary FLOAT, PRIMARY KEY(id) );

上述两个例子执行后的结果是一样的，都会在id字段上设置主键约束。  
【例4.4】定义数据表tb\_emp4，假设表中间没有主键id，为了唯一确定一个员工，可以把name、deptId联合起来做为主键，SQL语句如下：

CREATE TABLE tb_emp4 ( name VARCHAR(25), deptId INT(11), salary FLOAT, PRIMARY KEY(name,deptId) );

【例4.5】定义数据表tb\_emp5，并在tb\_emp5表上创建外键约束。  
创建一个部门表tb\_dept1，SQL语句如下：

CREATE TABLE tb_dept1 ( id INT(11) PRIMARY KEY, name VARCHAR(22) NOT NULL, location VARCHAR(50) );

定义数据表tb\_emp5，让它的键deptId作为外键关联到tb\_dept1的主键id，SQL语句为：

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) );

以上语句执行成功之后，在表tb\_emp5上添加了名称为fk\_emp\_dept1的外键约束，外键名称为deptId，其依赖于表tb\_dept1的主键id。  
【例4.6】定义数据表tb\_emp6，指定员工的名称不能为空，SQL语句如下：

CREATE TABLE tb_emp6 ( id INT(11) PRIMARY KEY, name VARCHAR(25) NOT NULL, deptId INT(11), salary FLOAT );

【例4.7】定义数据表tb\_dept2，指定部门的名称唯一，SQL语句如下：

CREATE TABLE tb_dept2 ( id INT(11) PRIMARY KEY, name VARCHAR(22) UNIQUE, location VARCHAR(50) );

【例4.8】定义数据表tb\_dept3，指定部门的名称唯一，SQL语句如下：

CREATE TABLE tb_dept3 ( id INT(11) PRIMARY KEY, name VARCHAR(22), location VARCHAR(50), CONSTRAINT STH UNIQUE(name) );

【例4.9】定义数据表tb\_emp7，指定员工的部门编号默认为1111，SQL语句如下：

CREATE TABLE tb_emp7 ( id INT(11) PRIMARY KEY, name VARCHAR(25) NOT NULL, deptId INT(11) DEFAULT 1111, salary FLOAT );

【例4.10】定义数据表tb\_emp8，指定员工的编号自动递增，SQL语句如下：

CREATE TABLE tb_emp8 ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25) NOT NULL, deptId INT(11), salary FLOAT );

例如，执行如下插入语句：

INSERT INTO tb_emp8 (name,salary) VALUES('Lucy',1000), ('Lura',1200),('Kevin',1500);

语句执行完后，tb\_emp8表中增加3条记录，在这里并没有输入id的值，但系统已经自动添加该值，使用SELECT命令查看记录，如下所示。

SELECT * FROM tb_emp8;

【例4.11】分别使用DESCRIBE和DESC查看表tb\_dept1和表tb\_emp1的表结构。  
查看tb\_dept1表结构，SQL语句如下：

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 | | +-----------+---------------+------+-----+---------+--------+

查看tb\_emp1表结构，SQL语句如下：

【例4.12】使用SHOW CREATE TABLE查看表tb\_emp1的详细信息，SQL语句如下：

SHOW CREATE TABLE tb_emp1; +--------+----------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------+
    | Table  | Create Table
    
     | +--------+----------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------+
    | fruits | CREATE TABLE `fruits` (
     `f_id` char(10) NOT NULL,
     `s_id` int(11) NOT NULL,
     `f_name` char(255) NOT NULL,
     `f_price` decimal(8,2) NOT NULL,
     PRIMARY KEY (`f_id`),
     KEY `index_name` (`f_name`),
     KEY `index_id_price` (`f_id`,`f_price`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +--------+----------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------+

使用参数’\\G’之后的结果如下：

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=gb2312

【例4.13】将数据表tb\_dept3改名为tb\_deptment3。  
使用ALTER TABLE将表tb\_dept3改名为tb\_deptment3，SQL语句如下：

ALTER TABLE tb_dept3 RENAME tb_deptment3;

【例4.14】将数据表tb\_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)。  
执行修改表名操作之前，使用DESC查看tb\_dept表结构，结果如下：

可以看到现在name字段的数据类型为VARCHAR(22)，下面修改其类型。输入如下SQL语句并执行：

ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

【例4.15】将数据表tb\_dept1中的location字段名称改为loc，数据类型保持不变，SQL语句如下：

ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);

【例4.16】 将数据表tb\_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，SQL语句如下：

ALTER TABLE tb_dept1CHANGE loc location VARCHAR(60);

【例4.17】在数据表tb\_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号），SQL语句如下：

ALTER TABLE tb_dept1 ADD managerId INT(10);

【例4.18】在数据表tb\_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下：

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

【例4.19】在数据表tb\_dept1中添加一个INT类型的字段column2，SQL语句如下：

ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;

【例4.20】在数据表tb\_dept1中name列后添加一个INT类型的字段column3，SQL语句如下：

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

【例4.21】删除数据表tb\_dept1表中的column2字段。  
删除column2字段，SQL语句如下：

ALTER TABLE tb_dept1 DROP column2;

【例4.22】将数据表tb\_dept中的column1字段修改为表的第一个字段，SQL语句如下：

ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;

【例4.23】将数据表tb\_dept1中的column1字段插入到location字段后面，SQL语句如下：

ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;

【例4.24】将数据表tb\_deptment3的存储引擎修改为MyISAM。  
在修改存储引擎之前，先使用SHOW CREATE TABLE查看表tb\_deptment3当前的存储引擎，结果如下。

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=gb2312 1 row in set (0.00 sec)

可以看到，表tb\_deptment3当前的存储引擎为ENGINE=InnoDB，接下来修改存储引擎类型，输入如下SQL语句并执行：

ALTER TABLE tb_deptment3 ENGINE=MyISAM;

使用SHOW CREATE TABLE再次查看表tb\_deptment3的存储引擎，发现表tb\_dept的存储引擎变成了“MyISAM”，结果如下：

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=gb2312 1 row in set (0.00 sec)

【例4.25】删除数据表tb\_emp9中的外键约束。  
首先创建表tb\_emp9，创建外键deptId关联tb\_dept1表的主键id，SQL语句如下：

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) );

使用SHOW CREATE TABLE查看表tb\_emp9的结构，结果如下：  
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=gb2312 1 row in set (0.00 sec)

可以看到，已经成功添加了表的外键，下面删除外键约束，SQL语句如下：

ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

执行完毕之后，将删除表tb\_emp的外键约束，使用SHOW CREATE TABLE再次查看表tb\_emp9结构，结果如下：

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=gb2312 1 row in set (0.00 sec)

【例4.26】删除数据表tb\_dept2，SQL语句如下：

DROP TABLE IF EXISTS tb_dept2;

在数据库中创建两个关联表，首先，创建表tb\_dept2，SQL语句如下：

CREATE TABLE tb_dept2 ( id INT(11) PRIMARY KEY, name VARCHAR(22), location VARCHAR(50) );

接下来创建表tb\_emp，SQL语句如下：

CREATE TABLE tb_emp ( id INT(11) PRIMARY KEY, name VARCHAR(25), deptId INT(11), salary FLOAT, CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept2(id) );

使用SHOW CREATE TABLE命令查看表tb\_emp的外键约束，结果如下：

SHOW CREATE TABLE tb_emp\G ***************** 1. row ************************ Table: tb_emp Create Table: CREATE TABLE `tb_emp` ( `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_dept2` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 1 row in set (0.00 sec)

可以看到，以上执行结果创建了两个关联表tb\_dept2和表tb\_emp，其中tb\_emp表为子表，具有名称为fk\_emp\_dept的外键约束，tb\_dept2为父表，其主键id被子表tb\_emp所关联。  
【例4.27】删除被数据表tb\_emp关联的数据表tb\_dept2。  
首先直接删除父表tb\_dept2，输入删除语句如下：

DROP TABLE tb_dept2;
    ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

可以看到，如前所述，在存在外键约束时，主表不能被直接删除。  
接下来，解除关联子表tb\_emp的外键约束，SQL语句如下：

ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;

语句成功执行后，将取消表tb\_emp和表tb\_dept2之间的关联关系，此时，可以输入删除语句，将原来的父表tb\_dept2删除，SQL语句如下：

DROP TABLE tb_dept2;

最后通过SHOW TABLES;查看数据表列表，如下所示：

可以看到，数据表列表中已经不存在名称为tb\_dept2的表。