使用SQL语句创建商店实验数据库

p75-p77

注：本文章中employee打成了exployee

#(1）创建数据库storeexpm
create database storeexpm;

 #（2）创建Employee表，显示Employee表的基本结构
use storeexpm;
create table exployee
(
EmplID varchar(4) not null primary key,
EmplName varchar(8) not null,
Sex varchar(2) not null default '男',
Birthday date not null,
Address varchar(20) null,
Wages decimal(8,2) not null,
DeptID varchar(4) null
);
desc exployee;

 #(3)在数据库storeexpm中，创建Department表,显示Department表的基本构造
create table department
(
DeptID varchar(4) not null primary key,
deptName varchar(20) not null
 );
desc department; 

 #(4)由Employee表使用复制方法创建Employee1表
use storeexpm;
create table exployee1 like exployee;

 #(5)在Employee表中增加一列Eno,添加到表的第1列，不为空，取值唯一并自动增加，显示Employee表的基本结构
alter table exployee
add column Eno int not null unique auto_increment first;
desc exployee;

 

 #(6)将Employee表的Sex列修改为Gender,将数据类型改为char，可为空，默认值改为"女",显示Employee1表的基本结构
alter table exployee1
change column Sex Gender char(2) null default '女';
desc exployee1;

 

#(7)将Employee1表的Address列修改为Telephone,将数据类型改为char，可为空
alter table exployee1
change column Address Telephone char(20) null;

 

 #(8)将Employee1表的Gender列的默认值修改为"男"
alter table exployee1
alter column Gender set default '男';

 

 

#(9)将Employee1表的Wages列的类型修改为float,并移到列EmplName之后
alter table exployee1
modify column Wages float after EmplName; 

 

 #(10)在Employee表中删除Eno列
alter table exployee
drop column Eno;

 

#(11)将Employee1表更名为Employee2表
alter table exployee1
rename to exployee2; 

 #(12)删除Employee2表
drop table exployee2;