create database studentsdb;
use studentsdb;
create table student_info(
学号 char(4) not null primary key,
姓名 char(8) not null,
性别 char(2),
出生日期 date,
家庭住址 varchar(50)
);
create table curriculum(
课程编号 char(4) not null primary key,
课程名称 varchar(50),
学分 int
);
create table grade(
学号 char(4) not null,
课程编号 char(4) not null,
分数 int,
primary key(学号,课程编号)
);
insert into student_info values('0001','张青平','男','2000-10-01','衡阳市东风路77号');
insert into student_info values('0002','刘东阳','男','1998-12-09','东阳市八一北路33号 ');
insert into student_info values('0003','马晓夏','女','1995-05-12','长岭县五一路763号 ');
insert into student_info values('0004','钱忠理','男','1994-09-23','滨海市洞庭大道279号');
insert into student_info values('0005','孙海洋','男','1995-04-03','长岛县解放路27号 ');
insert into student_info values('0006','郭小斌','男','1997-11-10','南山市红旗路113号 ');
insert into student_info values('0007','肖月玲','女','1996-12-07','东方市南京路11号 ');
insert into student_info values('0008','张玲珑','女','1997-12-24','滨江市新建路97号 ');
insert into curriculum values('0001','计算机应用基础',2);
insert into curriculum values('0002','c语言课程设计',2);
insert into curriculum values('0003','数据库原理及应用',2);
insert into curriculum values('0004','英语',4);
insert into curriculum values('0005','高等数学',4);
insert into grade values('0001','0001',80);
insert into grade values('0001','0002',91);
insert into grade values('0001','0003',88);
insert into grade values('0001','0004',85);
insert into grade values('0001','0005',77);
insert into grade values('0002','0001',73);
insert into grade values('0002','0002',68);
insert into grade values('0002','0003',80);
insert into grade values('0002','0004',79);
insert into grade values('0002','0005',73);
insert into grade values('0003','0001',84);
insert into grade values('0003','0002',92);
insert into grade values('0003','0003',81);
insert into grade values('0003','0004',82);
insert into grade values('0003','0005',75);
alter table curriculum
modify column 课程名称 varchar(50) null;
alter table grade
modify column 分数 decimal(5,2);
alter table student_info
add 备注 varchar(50);
create database studb;
use studb;
create table stu
as select * from studentsdb.student_info;
set sql_safe_updates=0;
delete from stu where 学号 ='0004';
update stu set 家庭住址='滨江市新建路96号'where 学号='0002';
alter table stu
drop column 备注;
drop table stu;
drop database studb;
运行结果: