mysql查询练习
一、创建数据
--学生表
学号
性别
出生年月日
所在班级
create table Student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20)
);
insert into student values('101','曾华','男','1977-09-01','95033');
insert into student values('102','匡明','男','1975-10-02','95031');
insert into student values('103','王蕾','女','1976-01-23','95033');
insert into student values('104','李军','男','1976-02-20','95033');
insert into student values('105','王芳','女','1975-02-10','95031');
insert into student values('106','陆军','男','1974-06-03','95031');
insert into student values('107','王二蛋','男','1976-02-20','95033');
insert into student values('108','张二蛋','男','1975-02-10','95031');
insert into student values('109','赵铁柱','男','1974-06-03','95031');
--教师表
教师编号
教师名称
出生年月日
职称
所在部门
create table teacher(
tno varchar(20) primat key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1972-08-14','助教','电子工程系');
--课程表
课程号
课程名称
教师编号
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
insert into