Mysql查询练习

查询数据准备

-- 查询练习数据准备

-- 学生表
-- Student
-- 学号
-- 姓名
-- 性别
-- 出生年月日
-- 所在班级
CREATE TABLE Student(
	sno VARCHAR(20) PRIMARY KEY,
	sname VARCHAR(20) not null,
	ssex VARCHAR(10) not null,
	sbirthday datetime,
	class VARCHAR(20)
);

-- 教师表
-- Teacher
-- 教师编号
-- 教师名称
-- 教师性别
-- 出生年月日
-- 职称
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
);

-- 课程表
-- 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) 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)
);


-- 添加数据
-- 学生表数据
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');

-- 教师表数据
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','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之间所有的记录(degree)
select * from score where degree between 60 and 80;

select * from score where degree>60 and degree<80;

-- 5.查询score表中成绩为85, 86, 或者88的记录(degree)
select * from score where degree in(85,86,88);

-- 6.查询student表中'95031'班或者性别为'女'的同学记录
select * from student where class='95031' 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表中的最高分数的学生号和课程号.(子查询或者排序)
-- (1)找到最高分
-- (2)找最高分sno和cno
select sno,cno from score where degree=(select max(degree) from score);
-- 排序做法
-- limit (从多少开始),(查多少条)
select sno,cno from score order by degree desc limit 0,1;

-- 11.查询每门课的平均成绩
-- avg()
-- select avg(degree) from score where cno='3-105';
-- group by分组
select cno,avg(degree) from score group by cno;

-- 12.查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';

-- 13.查询分数大于70但是小于90的sno列
select sno,degree from score where degree>70 and degree<90;
-- between 其实是大于等于和小于等于
select sno,degree from score where degree between 70 and 90;

-- 14.查询所有的学生 sname , cno, degree列
-- select sno,sname from student;
-- select sno,cno,degree from score;
select sname,cno,degree from student,score where student.sno = score.sno;

-- 15.查询所有学生的sno, cname, degree列
-- select cno,cname from course;
-- select cno,sno,degree from score;
select sno,cname,degree from course,score where course.cno = score.cno;

-- 16.查询所有的学生 sname , cname, degree列
-- sname -> student
-- cname -> scoure
-- degree -> score
select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno = score.cno;

-- 17.查询班级是'95031'班学生每门课的平均分
-- select sno from student where class='95031';
-- select * from score where sno in(select sno from student where class='95031');
select cno,avg(degree) from score where sno in(select sno from student where class='95031') group by cno;

-- 18.查询选修"3-105"课程的成绩高于'109'号同学'3-105'成绩 的所有同学的记录
-- select degree from score where sno='109' and cno='3-105';
select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');

-- 19.查询成绩高于学号为'109',课程号为'3-105'的成绩的所有记录
select * from score where degree>(select degree from score where sno='109' and cno='3-105');

-- 20.查询所有学号为108、101的同学同年出生的所有学生的sno,sname和sbirthday
-- select year(sbirthday) from student where sno in (108,101);
select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));

-- 21.查询 "张旭" 教师任课的学生的成绩
-- select tno from teacher where tname='张旭';
-- select cno from course where tno=(select tno from teacher where tname='张旭');
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));

-- 22.查询选修课程的同学人数多于 5 人的教师姓名
-- 为了效果,添加数据:
INSERT INTO score VALUES('101','3-105','90');
INSERT INTO score VALUES('102','3-105','91');
INSERT INTO score VALUES('104','3-105','89');
-- select cno from score group by cno having count(*)>5;
select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5));

-- 23.查询95033班和95031班全体学生的记录
-- 添加数据
INSERT INTO student VALUES('110','张飞','男','1974-06-03','95038');
select * from student where class in('95031','95033');

-- 24.查询存在85分以上成绩的课程cno
select cno from score where degree>85 group by cno;

-- 25.查出所有'计算机系' 教师所教课程的成绩表
-- select * from teacher where depart='计算机系';
-- select * from course where tno in(select tno from teacher where depart='计算机系');
select * from score where cno in (select cno from course where tno in(select tno from teacher where depart='计算机系'));

-- 26.查询'计算机系'与'电子工程系' 不同职称的教师的name和prof
-- union求并集
select * from teacher where depart ='计算机系' and prof not in(select prof from teacher where depart='电子工程系')
union
select * from teacher where depart ='电子工程系' and prof not in(select prof from teacher where depart='计算机系');

-- 27, 查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的cno,sno和degree,并且按照degree从高到地次序排序
-- any 任意一个,>min也可以
select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245') order by degree desc;

-- 28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学cno.sno和degree
-- all 就是所有，>max也可以
select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');

-- 29. 查询所有教师和同学的 name ,sex, birthday
-- as取别名，union取并集(这里第二排默认用第一排别名)
select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from student;

-- 30.查询所有'女'教师和'女'学生的name,sex,birthday
select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex = '女'
union
select sname,ssex,sbirthday from student where ssex = '女';

-- 31.查询成绩比该课程平均成绩低的同学的成绩表
select * from score a where degree< (select avg(degree) from score b where a.cno=b.cno);

-- 32.查询所有任课教师的tname 和 depart(课程表中安排了课程)
select tname,depart from teacher where tno in(select tno from course);

-- 33.查出至少有2名男生的班号
select class from student where ssex='男' group by class having count(*)>1; 

-- 34.查询student 表中 不姓"王"的同学的记录
select * from student where sname not like '王%';

-- 35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
-- 当前年份select year(now());
select sname,year(now())-year(sbirthday) as '年龄' from student;

-- 36. 查询student中最大和最小的 sbirthday的值
-- max与min函数
select max(sbirthday) as 'MAX',min(sbirthday) as 'MIN' from student;

-- 37.以班级号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class desc,sbirthday asc;

-- 38.查询"男"教师 及其所上的课
-- select * from teacher where tsex = '男';
select * from course where tno in (select tno from teacher where tsex = '男');

-- 39.查询最高分同学的sno cno 和 degree;
-- select max(degree) from score;
select * from score where degree =(select max(degree) from score);

-- 40. 查询和"李军"同性别的所有同学的sname
select sname from student where ssex= (select ssex from student where sname='李军');

-- 41.查询和"李军"同性别并且同班的所有同学的sname
select sname from student where ssex= (select ssex from student where sname='李军') and class=(select class from student where sname='李军');


-- 42. 查询所有选修'计算机导论'课程的'男'同学的成绩表
-- select * from student where ssex = '男';
-- select * from course where cname ='计算机导论';
select * from score where cno=(select cno from course where cname ='计算机导论') and sno in(select sno from student where ssex = '男');


-- 43. 假设使用了以下命令建立了一个grade表
CREATE TABLE grade(
    low INT(3),
    upp INT(3),
    grade CHAR(1)
);
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'c');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');

-- 查询所有同学的sno , cno 和grade列
select sno,cno,grade from score,grade where degree between low and upp;




-- 连接查询
-- person表 id ,name ,cardId
CREATE TABLE person(
	id int,
	name VARCHAR(20),
	cardId int	
);
-- card表 card,name
CREATE TABLE card(
	id int,
	name VARCHAR(20)
);

INSERT INTO card VALUES (1,'饭卡');
INSERT INTO card VALUES (2,'建行卡');
INSERT INTO card VALUES (3,'农行卡');
INSERT INTO card VALUES (4,'工商卡');
INSERT INTO card VALUES (5,'邮政卡');

INSERT INTO person VALUES (1,'张三',1);
INSERT INTO person VALUES (2,'李四',3);
INSERT INTO person VALUES (3,'王五',6);

-- 内连接(两张表中数据通过某个字段相等查询出相关记录数据)
-- inner join 或者 join
select * from person inner join card on person.cardId = card.id;

-- 外连接 
-- 1.左连接 left join 或者 left outer join
-- 左边表里面的所有数据取出来，右边表数据如果有则显示，没有则显示NULL
select * from person left join card on person.cardId = card.id;

-- 2.右连接 right join 或者 right outer join
-- 右边表里面的所有数据取出来，右边表数据如果有则显示，没有则显示NULL
select * from person right join card on person.cardId = card.id;

-- 3.完全外连接 full join 或者 full outer join(mysql不支持full join)
-- mysql实现就是左右连接取并集
select * from person left join card on person.cardId = card.id
union
select * from person right join card on person.cardId = card.id;