实验 2 数据表的创建与修改
一、实验目的
(1) 掌握表的基础知识。
(2) 掌握使用Navicat或其他第三方管理工具和SQL语句创建表的方法。
(3) 掌握表的修改、查看、删除等基本操作方法。
(4) 掌握表中完整性约束的定义。
(5) 掌握完整性约束的作用
二、实验内容
(一)teacherInfo表定义与修改操作
按照下列要求进行表定义操作:
(1)首先创建数据库schoolInfo。
create database schoolInfo default charset utf8 collate utf8_general_ci;
(2)创建 teacherInfo 表。
CREATE TABLE teacherInfo(
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10) NOT NULL UNIQUE ,
Name VARCHAR(20) NOT NULL,
Sex VARCHAR(4) NOT NULL,
Birthday DATETIME,
Address VARCHAR(50)
);
(3)将 teacherInfo 表的name字段的数据类型改为 VARCHAR(30)。
ALTER TABLE teacherInfo MODIFY name VARCHAR(30) NOT NULL;
(4)将birthday宁段的位置改到sex字段的前面。
ALTER TABLE teacherInfo MODIFY birthday DATETIME AFTER name;
(5)将num字段改名为t_id。
ALTER TABLE teacherInfo CHANGE num t_id INT(10)NOT NULL;
(6)将 teacherInfo 表的address字段删除。
ALTER TABLE teacherInfo DROP address;
(7)在 teacherInfo 表中增加名为wages的字段,数据类型为FLOAT。
ALTER TABLE teacherInfo ADD wages FLOAT;
(8)将 teacherInfo 表改名为 teacherInfo Info。
ALTER TABLE teacherInfo RENAME teacherInfolnfo;
(9)将 teacherInfo 表的存储引擎更改为MyISAM类型。
ALTER TABLE teacherInfolnfo ENGINE=MyISAM;
(二)创建staffinfo数据库,并在定义department表和worker表,完成两表之间的完整性约束。
Department表的结构
字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
d_id | 部门号 | INT(4) | 是 | 否 | 是 | 是 | 否 |
d_name | 部门名 | VARCHAR(20) | 否 | 否 | 是 | 是 | 否 |
function | 部门职能 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
address | 部门位置 | VARCHAR(20) | 否 | 否 | 否 | 否 | 否 |
Worker表的结构
字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
id | 编号 | INT(4) | 是 | 否 | 是 | 是 | 是 |
num | 员工号 | INT(10) | 否 | 否 | 是 | 是 | 否 |
d_id | 部门号 | INT(4) | 否 | 是 | 否 | 否 | 否 |
name | 姓名 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
sex | 性别 | VARCHAR(4) | 否 | 否 | 是 | 否 | 否 |
birthday | 出生日期 | DATE | 否 | 否 | 否 | 否 | 否 |
address | 家庭住址 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
按照下列要求进行表操作:
(1)在staffinfo数据库下创建department表和worker表。
(2)删除department表。
操作如下:
create database if not exists staffinfo
default charset utf8 collate utf8_general_ci;
(1)创建department表,代码如下:
CREATE TABLE department(
d_id INT(4)NOT NULL UNIQUE PRIMARY KEY,
d_name VARCHAR(20)NOT NULL UNIQUE ,
function VARCHAR(50),
address VARCHAR(50)
);
(2)创建worker表,代码如下:
CREATE TABLE worker(
id INT(4)NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10)NOT NULL UNIQUE,
d_id INT(4),
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATE,
address VARCHAR(50),
CONSTRAINT worker_fk FOREIGN KEY(d_id),
REFERENCES department(d_id)
);
(3)删除department表,代码如下:
DROP TABLE department;
(4)删除worker表的外键约束,代码如下:
ALTER TABLE worker DROP FOREIGN KEY worker_fk;
(5)重新删除department表,代码如下:
DROP TABLE department;
三、实验步骤与实验结果
(一)teacherInfo表定义与修改操作
1.创建数据库
create database schoolInfo;
2.使用数据库
use schoolinfo;
3.创建 teacherInfo 表
CREATE TABLE teacherInfo(
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10) NOT NULL UNIQUE ,
Name VARCHAR(20) NOT NULL,
Sex VARCHAR(4) NOT NULL,
Birthday DATETIME,
Address VARCHAR(50)
);
4.将 teacherInfo 表的name字段的数据类型改为 VARCHAR(30)。
ALTER TABLE teacherInfo MODIFY name VARCHAR(30) NOT NULL;
5.将birthday宁段的位置改到sex字段的前面。
ALTER TABLE teacherInfo MODIFY birthday DATETIME AFTER name;
6.将num字段改名为t_id。
ALTER TABLE teacherInfo CHANGE num t_id INT(10)NOT NULL;
7.将 teacherInfo 表的address字段删除。
ALTER TABLE teacherInfo DROP address;
8.在 teacherInfo 表中增加名为wages的字段,数据类型为FLOAT。
ALTER TABLE teacherInfo ADD wages FLOAT;
9.将 teacherInfo 表改名为 teacherInfo Info。
ALTER TABLE teacherInfo RENAME teacherInfolnfo;
10.将 teacherInfo 表的存储引擎更改为MyISAM类型。
ALTER TABLE teacherInfolnfo ENGINE=MyISAM;
(二)创建staffinfo数据库,并在此数据库中定义department表和worker表,完成两表之间的完整性约束
1.创建数据库staffinfo
create database staffinfo;
2.使用数据库
use staffinfo;
3.创建表department
CREATE TABLE department(
d_id INT(4)not null unique primary key,
d_name VARCHAR(20)not null unique,
function VARCHAR(50),
address VARCHAR(20)
);
4.创建表worker
CREATE TABLE worker(
id INT(4)NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
num INT(10)NOT NULL UNIQUE ,
d_id INT(4),
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATE,
address VARCHAR(50),
CONSTRAINT worker_fk FOREIGN KEY(d_id)
REFERENCES department(d_id)
);
5.删除department表,代码如下:
DROP TABLE department;
6.删除worker表的外键约束,代码如下:
ALTER TABLE worker DROP FOREIGN KEY worker_fk;
7.删除department表,代码如下:
DROP TABLE department;
三、观察与思考
1、关于NOT NULL
(1) 在定义基本表语句时,NOT NULL参数的作用是什么?
答:NOT NULL表示字段不可以为空值。
主码列修改成允许NULL能否操作?为什么?
答:不能因为主码是唯一非空且不重复的。
2、关于外码
(1) 根据下面设计的表结构,Employee表的外键能否设置成功?思考外码设置需要注意哪些问题?
答:不能,外键关联的字段类型长度不一致;
外键是用于两个表的数据之间建立连接,可以是一列或者多列,即一个表可以有一个或多个外键。外键可以不是这个表的主键,但必须和另外一个表的主键相对应,字段的类型和值必须一样。带有主键的那张表称为父表,含外键的是子表,必须先删除外键约束才能删除父表。 Department表的结构
字段名 | 字段描述 | 数据类型 | 主键 | 外键 |
dno | 部门号 | INT(4) | ||
dname | 部门名 | VARCHAR(20) |
Employee表的结构
字段名 | 字段描述 | 数据类型 | 主键 | 外键 |
eno | 员工号 | INT(10) | ||
dno | 所在部门号 | Char(10) | √ | |
name | 姓名 | VARCHAR(20) |
(2) 如果主表无数据,从表的数据能输入吗?
答:只要从表没有建外键索引就可以插入数据,如果建了外键索引,就不能插入数据。
(3)先创建从表,再创建主表是否可以?
答:可以。
3、关于主码和唯一约束
(1) 唯一约束列是否允许NULL值?
答:可以。
(2) 一张表可以设置几个主码,可以设置几个唯一约束?
答:一张表可以设置一个主码,但可以设置多个唯一约束。
四、实验要求
按要求完成,详细记录操作步骤,书写实验报告。所有实验环节均由每位学 生独立完成,认真记录操作过程,严禁抄袭他人实验结果。
五、实验总结
通过本次实验,掌握了用sql语法对数据库和数据表的增加、修改和删除。完成此次实验后,掌握了相关的操作技术和知识和对sql语句的复习