個人筆記，僅供參考
查詢練習(xí)數(shù)據(jù)準備
準備數(shù)據(jù)供下文使用
1咽白、學(xué)生表(student)
學(xué)號啤握，姓名鸟缕，性別晶框，出生年月日，班級
mysql> create table student(
-> sno varchar(20) primary key,
-> sname varchar(20) not null,
-> ssex varchar(20) not null,
-> sbirthday datetime,
-> class varchar(20)
-> );
2懂从、課程表(course)
課程號授段，課程名稱，教師編號
mysql> create table course(
-> cno varchar(20) primary key,
-> cname varchar(20) not null,
-> tno varchar(20) not null,
-> foreign key(tno) references teacher(tno)
-> );
3番甩、成績表(score)
學(xué)號侵贵，課程號，成績
mysql> create table score(
-> sno varchar(20) not null,
-> cno varchar(20) not null,
-> degree decimal,
-> foreign key(sno) references student(sno),
-> foreign key(cno) references course(cno),
-> primary key(sno,cno)
-> );
4缘薛、教師表(teacher)
教師編號窍育，教師姓名。教師性別宴胧，出生年月日漱抓，職稱，所在部門
mysql> create table teacher(
-> tno varchar(20) primary key,
-> tname varchar(20) not null,
-> tsex varchar(10) not null,
-> tbirthday datetime,
-> prof varchar(20) not null,
-> depart varchar(20) not null
-> );
往數(shù)據(jù)表中添加數(shù)據(jù)
1恕齐、在學(xué)生表中添加以下數(shù)據(jù)
insert into student values('188','曾華','男','1997-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');
2乞娄、教師表
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','助教','電子工程系');
3、課程表
insert into course values('3-105','計算機導(dǎo)論','825');
insert into course values('3-245','操作系統(tǒng)','804');
insert into course values('6-166','數(shù)字電路','856');
insert into course values('9-888','高等數(shù)學(xué)','831');
4显歧、成績表
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','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');
查詢練習(xí)
1仪或、查詢student表的所有信息
select * from student;
2、查詢student表中的所有記錄的sname士骤，ssex和class列
select sname, ssex, class from student;
3范删、查詢教師所有的單位，即不重復(fù)的depart列
select distinct depart from teacher;
4拷肌、查詢score表中成績在60到80之間的所有記錄
使用between...and...
select * from score where degree between 60 and 80;
或者使用運算符
select * from score where degree > 60 and degree < 80;
5到旦、查詢score表中成績?yōu)?5束铭、86或88的記錄
表示或者關(guān)系，in
select * from score where degree in(85, 86 ,88);
6厢绝、查詢student表中“95031”班或性別為女的同學(xué)記錄
使用or
select * from student where class='95031' or ssex='女';
7契沫、以class降序查詢student表的所有記錄
升序(asc)，降序(desc) 默認為升序
select * from student order by class desc;
8昔汉、以con升序懈万、degree降序查詢score表的所有記錄
即以cno升序排列，遇到相同的再以degree降序排列
select * from score order by cno asc,degree desc;
9靶病、查詢“95031”班的學(xué)生人數(shù)
統(tǒng)計count
select count(*) from student where class='95031';
10会通、查詢score表中的最高分的學(xué)生學(xué)號和課程號。（子查詢或排序）
select sno,cno from score where degree=(select max(degree) from score);
