实验4.2-3

实验4.2

mysql> create DATABASE storeexpm;

mysql> use storeexpm;

mysql> create table Employee

-> (

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

-> );

mysql> create table Department

-> (

-> DeptID varchar(4) not null primary key,

-> DeptName varchar(20) not null

-> );

mysql> create table Employee1 like Employee;

mysql> alter table Employee

-> add column Eno int not null unique auto_increment first;

mysql> alter table Employee1

-> change column Sex Gender char(2) null default '女';

mysql> alter table Employee1

-> change column Address Telephone char(20) null;

mysql> alter table Employee1

-> alter column Gender set default '男';

mysql> alter table Employee1

-> modify column Wages float after EmplName;

mysql> alter table Employee

-> drop column Eno;

mysql> alter table Employee1

-> rename to Employee2;

mysql> drop table Employee2;

实验4.3

mysql> create database stuexpm;

mysql> use stuexpm;

mysql> create table StudentInfo

-> (

-> StudentID varchar(6) not null primary key,

-> Name varchar(8) not null,

-> Sex varchar(2) not null default '男',

-> Birthday date not null,

-> Speciality varchar(12) null,

-> Address varchar(20) null

-> );

mysql> create table StudentInfo1 like StudentInfo;

mysql> alter table StudentInfo

-> add column StuNo int not null unique auto_increment first;

mysql> alter table StudentInfo1

-> change column Address City char(20) null default '北京';

mysql> alter table StudentInfo1

-> change column Speciality School char(20) null default '计算机学院';

mysql> alter table StudentInfo1

-> alter column City set default '上海';

mysql> alter table StudentInfo1

-> modify column City varchar(20) after Name;

mysql> alter table StudentInfo

-> drop column StuNo;

mysql> alter table StudentInfo1

-> rename to StudentInfo2;

mysql> drop table StudentInfo2;