mysql常见练习题45题

create database school;
use school;

#学生表
create table Student
(
Sno varchar(20) not null COMMENT ‘人名’,
Sname varchar(20) not null COMMENT ‘姓名’,
Ssex varchar(20) not null COMMENT ‘性别’,
Sbirthday datetime COMMENT ‘出生日期’,
Class varchar(20) COMMENT ‘班级’
);
#课程表
create table Course(
Cno varchar(20) not null COMMENT ‘课程号’,
Cname varchar(20) not null COMMENT ‘课程名称’,
Tno varchar(20) not null COMMENT ‘教工编号’
);
#成绩表
Create table Score (
Sno varchar(20) not null COMMENT ‘学号’,
Cno varchar(20) not NULL comment ‘课程号’,
Degree DECIMAL(4,1) null COMMENT ‘成绩’
);
#教师表
create table Teacher (
Tno varchar(20) not null COMMENT ‘教工编号’,
Tname varchar(20) not null COMMENT ‘教工姓名’,
Tsex varchar(20) not null COMMENT ‘教工性别’,
Tbirthday datetime null COMMENT ‘教工出生日期’,
Prof varchar(20) null COMMENT ‘职称’,
Depart varchar(20) null COMMENT ‘教工所在部门’
);

insert into Student value
(108,‘曾华’,‘男’,‘1977-09-01’,95033),
(105,‘匡明’,‘男’,‘1975-10-02’,95031),
(107,‘王丽’,‘女’,‘1976-01-23’,95033),
(101,‘李军’,‘男’,‘1976-02-20’,95033),
(109,‘王芳’,‘女’,‘1975-02-10’,95031),
(103,‘陆军’,‘男’,‘1974-06-03’,95031);

insert into Course value
(3-105,‘计算机导论’,825),
(3-245,‘操作系统’,804),
(6-166,‘数字电路’,856),
(9-888,‘高等数学’,831);

insert into Score value
(103,3-245,86),
(105,3-245,75),
(109,3-245,68),
(103,3-105,92),
(105,3-105,88),
(109,3-105,76),
(101,2-105,64),
(107,3-105,91),
(108,3-105,78),
(101,6-166,85),
(107,6-166,79),
(108,6-166,81);

insert into Teacher value
(804,‘李城’,‘男’,‘1958-12-02’,‘副教授’,‘计算机系’),
(856,‘张旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’),
(825,‘王萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’),
(831,‘刘冰’,‘女’,‘1977-08-14’,‘助教’,‘电子工程系’);