目录
2.查询student表中的所有记录的sname、ssex和class列
6.查询student表中“95031”班或性别为“女”的同学记录
8.以cno升序,degree降序查询score表中的所有记录
10.查询score表中的最高分学生学号的课程号(子查询或者排序)
11.limit 第一个数字表示从多少开始,第二个数字表示查多少条
数据准备
学生表
Student
学号
姓名
性别
出生年月日
所在班级
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20)
);
课程表
Course
课程号
课程名称
教师编号
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
成绩表
Score
学号
课程号
成绩
create table score(
sno varchar(20) primary key,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
教师表
Teacher
教师编号
教师名字
教师性别
出生年月日
职称
所在部门
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
sbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
#添加学生信息
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','1974-06-03','95031');
#添加教师表
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','刘冰','女','1977-08-14','助教','电子工程系');
#添加课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
#添加成绩表
insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','3-105','64');
insert into score values('105','3-105','91');
insert into score values('109','3-105','78');
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');
1.查询student表的所有记录
select * from student;
2.查询student表中的所有记录的sname、ssex和class列
select sname,ssex,class from student;
3.查询教师所有的单位即不重复的depart列
distinct排除重复
select distinct depart from teacher;
4.查询score表中成绩在60到80之间的所有记录
查询区间
select * from score where degree between 60 and 80;
直接使用运算符比较
select * from score where degree > 60 and degree < 80;
5.查询score表中成绩为85,86或88的记录
表示或者关系的查询 in
select * from score where degree in(85,86,88);
6.查询student表中“95031”班或性别为“女”的同学记录
or表示或者
select * from student where class=’9501’ or ssex=’女’
7.以class降序查询student表中的所有记录
升序(默认)asc,降序desc
select * from student order by class desc;
8.以cno升序,degree降序查询score表中的所有记录
select * from score order by cno asc,degree desc;
9.查询“95031”班的学生人数
统计count
select count(*) from student where class=’95031’;
10.查询score表中的最高分学生学号的课程号(子查询或者排序)
select sno,cno from score where degree=(select max(degree) from score);
11.limit 第一个数字表示从多少开始,第二个数字表示查多少条
select sno,cno,degree from score order by degree desc limit 0,1