mysql创建表的实例附答案

在example数据库创建一个student表和一个grade表,表结构如下图所示

1 将grade表的course字段的数据类型改为varchar(20)

2 将s_num字段的位置改到course字段的前面

3 将grade字段改名为score

4 删除grade表的外键约束

5 将grade表的存储引擎更改为MyISAM类型

6 将student表的address字段删除

7 在student表中增加名为phone的字段,数据类型为INT(10)

8 将grade表改名为gradeinfo

9 删除student表

Create database example;

Use example;

Crate table student(num int(10) not null unique primary key,

Name varchar(20) not null,

Sex varchar(4) not null,

Birthday datetime,

Address varchar(50)

);

Create grade (id int(10) not null unique primary key auto_increment,

Course varchar(10) not null,

S_num int(10) not null,

Grade varchar(4)

Constraint grade_fk foreign key(s_num) reference student(num)

);

Desc student;

Show create table grade \G;

Alter table grade modify course varchar(20);

Alter table grade modify s_num int(10) after id;

Alter table grade change grade score varchar(4);

Alter table grade drop foreign key grade_fk;

Alter table grade engine=myisam;

Alter table student drop address;

Alter table student add phone int(10);

Alter table grade rename gradeinfo;

Drop table student;