create database B08班;
use B08班;
create table 系
(
系号 char(6),
系名 varchar(10),
primary key(系号)
);
create table 班级
(
班号 char(6),
班名 varchar(7),
系号 char(6),
primary key(班号),
foreign key (系号) references 系(系号)
);
create table 学生
(
学号 char(6),
姓名 varchar(7),
班号 char(6),
primary key(学号),
foreign key (班号) references 班级(班号)
);
insert into 系 values('01','数学系');
insert into 系 values('02','物理系');
insert into 系 values('03','音乐系');
insert into 班级 values('0101','数学一班','01');
insert into 班级 values('0201','物理一班','02');
insert into 班级 values('0301','音乐一班','03');
insert into 学生 values ('010101','迪丽热巴','0101');
insert into 学生 values ('020101','古力娜扎','0201');
insert into 学生 values ('030101','哈尼克孜','0301');
insert into 学生 values ('010102','热依扎','0101');
insert into 学生 values ('010103','木克代斯','0201');
select * from 学生;
select * from 系;
select * from 班级;
select * from 系,班级 where 系.系号=班级.系号;
select 班名,系号 from 班级 where 系号='02';
select * from 学生 order by 学号 desc;
select * from 学生 order by 学号 asc;
select * from 学生 order by 学号 asc limit 2;
select 班号,count(学号) as班内学生个数 from 学生 group by 班号;
select * from 班级,学生 where 班级.班号=学生.班号 and 学号='030101' ;
select 班名,系名,系.系号 from 班级,系 where 系.系号=班级.系号 and 班号='0201' ;
select 班级.系号,系名,班号,班名 from 班级 inner join 系 on 系.系号=班级.系号 and 班号='0201' ;
select 班级.系号,系名,班号,班名 from 班级 inner join 系 on 系.系号=班级.系号 and 系.系号='03' ;
create table student(
studentNo char(11) comment '学号',
sname char(8) comment '姓名',
sex char(2) comment '性别',
birthdate date comment '出生日期',
entrance int comment '入学成绩',
phone char(11) comment '电话号码',
email VARCHAR(20) comment '邮件号码',
primary key(studentNo)
);
insert into student values('18122221324','何白露',
'女','2000/12/4','879','13178978999','heyy@sina.com '),
('18125111109','敬横江',
'男','2000/3/1','789','15678945623','jing@sina.com '),
('18125121107','梁一苇',
'女','1999/9/3','777','13145678921','bing@126.com '),
('18135222201','凌浩风',
'女','2001/10/6','867','15978945645','tang@163.com '),
('18137221508','赵临江',
'男','2000/2/13','789','12367823453','ping@163.com '),
('19111133071','崔依歌',
'女','2001/6/6','787','15556845645','cui@126.com '),
('19112100072','宿沧海',
'男','2002/2/4','658','12545678998','su12@163.com'),
('19112111208','韩山川',
'男','2001/2/14','666','15878945612','han@163.com '),
('19122203567','封月明',
'女','2002/9/9','898','13245674564','jiao@126.com'),
('19123567897','赵既白',
'女','2002/8/4','999','13175689345','pingan@163.com'),
('19126113307','梅惟江',
'女','2003/9/7','787','13245678543','zhu@163.com');
select * from student;
select sex,count(studentno) from student group by sex;
select entrance,count(studentno) from student group by entrance;
CREATE table course(
courseno char(6) comment '课程号',
cname varchar(20) comment '课程名',
type char(8) not null comment '课程类型',
cpno char(6) comment '先行课程',
period int(2) not null comment '总学时',
exp int(2) not null comment '实验学时',
term int(2) not null comment '开课学期',
primary key(courseno)
);
insert into course VALUES
('c05103','电子技术','选修',null,48,16,2),
('c05107','程序设计基础','必修',null,48,16,4),
('c05109','C语言','必修','c05108',48,16,4),
('c05127','Java语言','必修','c05109',64,16,4),
('c05138','软件工程','必修','c05109',48,8,5),
('c05124','经济学','必修',null,64,16,4),
('c05108','数据库原理','必修','c05107',48,16,3),
('c05222','会计实务','必修',null,48,8,2),
('c05223','UML_SOFT','选修','c05224',48,8,2),
('c05224','UML设计','选修','c05138',64,8,2);
select period,count(courseno) from course group by period;
select type,count(courseno) from course group by type;
select * from student where sname like '赵%';
select * from course where cname like '电子%' ;
select * from student where sname like '%江';
select * from student where sname like '%月%';
select * from student where entrance between 800 and 900;
select * from student where studentno between 18122221321 and 18122221327;
CREATE table score(
studentno char(11) comment '学号',
courseno char(6) comment '课程号',
daily float(3,1) default 0 comment '平时成绩',
final float(3,1) default 0 comment '期末成绩',
primary key(studentno,courseno)
);
insert into score VALUES
('18122221324','c05103',87.0,92.0),
('18122221324','c05109',85.0,90.0),
('18122221324','c05127',95.0,93.0),
('18125111109','c05127',91.0,88.0),
('18125111109','c05138',80.0,80.0),
('18125111109','c05108',76.0,78.0),
('19125121107','c05108',88.0,72.0),
('19125121107','c05127',81.0,76.0),
('19125121107','c05138',75.0,74.0),
('19137156732','c05124',89.0,88.0),
('19137156732','c05222',81.0,79.0);
select score.studentno,sname,final from student,score
where student.studentNo=score.studentno and courseno='c05109';
select studentno,cname,term from score ,course
where course.courseno=score.courseno and course.courseno='c05138';
select cname,term,final from course,score
where course.courseno=score.courseno and studentno='18122221324';
select cname,term,final from course inner join score
on course.courseno=score.courseno and studentno='18122221324';
select cname,studentno from course inner join score
on course.courseno=score.courseno and final=88;
select cname,studentno from course,score
where course.courseno=score.courseno and final=88;
select cname,score.studentno from course,score,student
where course.courseno=score.courseno and student.studentNo=score.studentno and final=88;
select cname ,score.studentno,sname from score,course,student
where course.courseno=score.courseno and score.studentno=student.studentno and final=88;
select sname ,course.cname,term from score,course,student
where course.courseno=score.courseno and student.studentno='18122221324';