MySQL数据库查询练习

• 练习01
• 练习02

练习01

1. 如下有一个department表，employee表，jobs表，job_grade表，locations表
   
   
   
   
   
   先创建以上五个表，然后再进行一下练习；

-- 子查询
select * from employees;
-- 1.查询 最低工资 大于 50号部门最低工资 的 部门id 和 其最低工资
select min(salary) from employees where department_id = 50; -- 50号部门最低工资
select department_id,min(salary) from employees group by department_id;  -- 分组每个部门的最低工资
select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50); -- 加过滤器：最低工资 大于 50号部门最低工资

-- 2.查询 各部门中的工资比本部门平均工资高 的 员工的员工号, 姓名和工资
select department_id,avg(salary) from employees group by department_id; -- 分组查询各部门的本部门的平均工资，虚拟表
select e.employee_id,e.first_name,e.last_name,e.salary,e.department_id from employees e 
inner join (select department_id de,avg(salary) ag from employees group by department_id) avs on e.department_id = avs.de -- 进行内连接，是部门ID号相等，对应起来
where e.salary > avs.ag; -- 加条件：各部门中的工资比本部门平均工资高

-- 3.查询 在部门的location_id为1700的部门工作的 员工的员工号
select * from departments;
select department_id from departments where location_id = 1700; -- 先查询location_id为1700的部门ID
select employee_id from employees where department_id = any (select department_id from departments where location_id = 1700); -- 只要员工表里的部门ID额查询出来的ID对应一个即可 any

-- 4.查询 在名字中包含字母u的员工 其相同部门下的员工的 员工号和名字
select employee_id,last_name,department_id from employees where last_name like '%u%'; -- 先查询名字中有u的员工号，名字，部门号
select distinct department_id from employees where last_name like '%u%'; -- 去掉重复的部门号，只要一个对应的部门号
select employee_id,last_name from employees where department_id in (select distinct department_id from employees where last_name like '%u%'); -- 只要满足部门号在里面即可 in

-- 5.查询 每个部门的 平均工资的 工资等级
select department_id,avg(salary) from employees group by department_id -- 分组查询各部门的本部门的平均工资，虚拟表
select * from job_grade; -- 等级表
select avs.*, g.job_level from (select department_id de,avg(salary) ag from employees group by department_id) avs -- 要查询的是每个部门的id和平均工资，对应的等级
inner join job_grade g on avs.ag between lowest_sal and highest_sal; -- 内连接，使平均工资对应到等级表里面的最低和最高上，划分等级

练习02

学生表：

科目表：

成绩表：

年级表：

先创建以上四个表，然后再进行一下练习：

• 数据修改与删除
  1.将地址是西安市的更新为 西安科技二路
  2.将S1001的email修改为空的字符串
  3.将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
  4.将S1001 ，课程编号为 2 的成绩 提高 5分
  5.将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
  6.经核实 S1004 课程编号为2 的 缺考
  7.将计算机网络课程删除掉
• 数据查询
  1、查询全部一年级的学生信息。
  2、查询全部二年级的学生的姓名和电话。
  3、查询全部一年级女同学的信息。
  4、查询课时超过60的科目信息。
  5、查询二年级的科目名称
  6、查询二年级男同学的姓名和住址。
  7、查询无电子邮件的学生姓名和年级信息。
  8、查询出生日期在1992年之后的男学生姓名和年级信息。
  9、参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
  10、按照出生日期查询一年级的学生信息。
  11、按成绩由高到低的次序查询参加编号为1的科目考试信息。
  12、查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
  13、查询课时最多的科目名称及课时。
  14、查询年龄最小的学生所在的年级及姓名。
  15、查询考试的最低分出现在哪个科目
  16、查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
  17、查询年龄超过25周岁的学生信息。
  18、查询1月份过生日的学生信息
  19、查询今天过生日的学生姓名及所在年级。
  20、新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com
  21、查询住址为“雁塔区”的学生姓名、电话、住址
  22、查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
  23、查询电话中含有以“130”开头的学生姓名，住址和电话。
  24、查询姓“赵”的学号、姓名和住址。
  25、统计一年级女生的总人数。
  26、查询李四总成绩
  27、学号为s1003的学生所有课总成绩
  28、学号为s1003的学生考试的平均分。
  29、查询一年级的科目“Mysql”的最高分、最低分、平均分。
  30、查询每个年级的总学时数，并按照升序排列。
  31、查询每个参加考试的学员的平均分。（Group by 学号）
  32、查询每门课程的平均分，并按照降序排列。（group by 课程）
  33、查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
  34、查询一年级的平均年龄。
  35、查询每个年级西安地区的学生人数。
  36、查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
  37、查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
  38、查询学生姓名、所属年级名称及联系电话。
  39、查询年级编号为1的科目名称、年级名称及学时。
  40、查询参加科目编号为1的考试的学生姓名、分数、考试日期。
  41、查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
  42、查询所有科目的参考信息（某些科目可能还没有被考试过）
  43、查询没有被考过的科目信息。

-- 数据修改与删除
-- 1.将地址是西安市的更新为 西安科技二路
update student set address = '西安科技二路' where address = '西安市';
select * from student;
-- 2.将S1001的email修改为空的字符串
update student set email = '' where studentno = 's1001';
-- 3.将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
update subjects set subjectname = 'java基础',classhour = 60, gradeid = 2 where subjectid = 2;
select * from subjects;
-- 4.将S1001 ，课程编号为 2 的成绩 提高 5分
select * from mark;
update mark set studentscore = studentscore + 5 where studentno = 's1001'and subjectid = 2;
-- 5.将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
update mark set studentscore = 60,examdate = '2015-10-10' where studentno = 's1004' and subjectid = 3;
-- 6.经核实 S1004 课程编号为2 的 缺考
update mark set studentscore = 0 where studentno = 's1004' and subjectid = 2;
-- 7.将计算机网络课程删除掉
delete from subjects where subjectname = '计算机网络';

-- 数据查询
-- 1、查询全部一年级的学生信息。
select * from student where gradeid = 1; 
-- 2、查询全部二年级的学生的姓名和电话。
select studentname,phone from student where gradeid = 2;
-- 3、查询全部一年级女同学的信息。
select * from student where gradeid = 1 and sex = '女';
-- 4、查询课时超过160的科目信息。
select * from subjects where classhour>60; 
-- 5、查询二年级的科目名称
select * from subjects where gradeid = 2;
-- 6、查询二年级男同学的姓名和住址。
select studentname,address from student where gradeid = 2 and sex = '男'; 
-- 7、查询无电子邮件的学生姓名和年级信息。
select studentname,gradeid from student where email is null or email = '';
-- 8、查询出生日期在1992年之后的男学生姓名和年级信息。
select studentname,gradeid from student where sex = '男' and born>1992;
-- 9、参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
select * from mark where examdate = '2015-07-03';
select subjectid,subjectname from subjects where subjectname = '计算机基本原理';
select * from mark where subjectid = (select subjectid sd from subjects where subjectname = '计算机基本原理') and examdate = '2015-07-03';
-- 10、按照出生日期查询一年级的学生信息。
select * from student where gradeid = 1 order by born;
-- 11、按成绩由高到低的次序查询参加编号为1的科目考试信息。
select * from mark where subjectid = 1 order by studentscore desc;
-- 12、查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
select subjectid,subjectname from subjects where subjectname = 'MySQL深入';
select studentno,studentscore,subjectid from mark where subjectid = (select subjectid from subjects where subjectname = 'MySQL深入') and examdate = '2015-07-01' order by studentscore desc limit 0,2;
-- 13、查询课时最多的科目名称及课时。
select subjectname,classhour from subjects order by classhour desc limit 0,1;
-- 14、查询年龄最小的学生所在的年级及姓名。
select studentname,gradeid from student order by born desc limit 0,1;
-- 15、查询考试的最低分出现在哪个科目
select * from mark order by studentscore limit 0,1;
select subjectname from subjects where subjectid = (select subjectid from mark order by studentscore limit 0,1);
-- 16、查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
select * from mark where studentno = 's1001' order by examdate;
-- 17、查询年龄超过25周岁的学生信息。
select * from student where now()-born > 25;
-- 18、查询1月份过生日的学生信息
select * from student where born like '%-01-%';
-- 19、查询今天过生日的学生姓名及所在年级。
-- 20、新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com(待补充学习)
-- 21、查询住址为“雁塔区”的学生姓名、电话、住址
select studentname,phone,address from student where address like '%雁塔区%';
-- 22、查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
select subjectname,classhour,gradeid from subjects where subjectname like '%计算机%' order by gradeid;
-- 23、查询电话中含有以“130”开头的学生姓名，住址和电话。
select studentname,address,phone from student where phone like '130%';
-- 24、查询姓“赵”的学号、姓名和住址。
select studentno,studentname,address from student where studentname like '赵%';
-- 25、统计一年级女生的总人数。
select count(1) from student where sex = '女';
-- 26、查询李四总成绩
select studentno, studentname from student where studentname = '李四';
select studentno, studentscore from mark where studentno = (select studentno from student where studentname = '李四');
-- 27、学号为s1003的学生所有课总成绩
select sum(studentscore) from mark where studentno = 's1003';
-- 28、学号为s1003的学生考试的平均分。
select avg(studentscore) from mark where studentno = 's1003';
-- 29、查询一年级的科目“MySQL深入”的最高分、最低分、平均分。
select subjectid, subjectname from subjects where subjectname = 'MySQL深入';
select max(studentscore),min(studentscore),avg(studentscore) from mark where subjectid = (select subjectid from subjects where subjectname = 'MySQL深入');
-- 30、查询每个年级的总学时数，并按照升序排列。
select gradeid, sum(classhour) from subjects group by gradeid order by sum(classhour);
-- 31、查询每个参加考试的学员的平均分。（Group by 学号）
select studentno, avg(studentscore) from mark group by studentno;
-- 32、查询每门课程的平均分，并按照降序排列。（group by 课程）
select subjectid, avg(studentscore) from mark group by subjectid order by avg(studentscore) desc;
-- 33、查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
select studentno, sum(studentscore) from mark group by studentno order by sum(studentscore) desc;
-- 34、查询一年级的平均年龄。
select 
-- 35、查询每个年级西安地区的学生人数。
select gradeid, count(1) from student where address like '%西安%' group by gradeid;
-- 36、查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
select studentno, avg(studentscore) from mark group by studentno having avg(studentscore) >= 60 order by avg(studentscore) desc;
-- 37、查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
select studentno, count(studentscore) from mark a where studentscore < 60 group by studentno having count(studentScore) >= 1;
select studentno, studentname from student where stude;
select studentno, (select b.studentname from student b where b.studentno = a.studentno)studentName, count(studentscore) from mark a where studentscore < 60 group by studentno having count(studentScore) >= 1;
-- 38、查询学生姓名、所属年级名称及联系电话。
select studentname,(select g.gradename from grade g where g.gradeid = s.gradeid)gradename,phone from student s;
-- 39、查询年级编号为1的科目名称、年级名称及学时。
select (select g.gradename from grade g where g.gradeid = s.gradeid )gradeid,subjectname,classhour from subjects s where s.gradeid = 1;
-- 40、查询参加科目编号为1的考试的学生姓名、分数、考试日期。
select (select s.studentname from student s where s.studentno = m.studentno)studentname,studentscore,examdate from mark m where subjectid = 1;
-- 41、查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
select (select s.subjectname from subjects s where s.subjectid = m.subjectid)subjectname,studentscore,examdate from mark m where studentno = 's1001';
-- 42、查询所有科目的参考信息（某些科目可能还没有被考试过）
select studentno,(select s.subjectname from subjects s where s.subjectid = m.subjectid)subjectname,studentscore,examdate from mark m left join subjects s on m.subjectid = s.subjectid;
select * from subjects;
-- 43、查询没有被考过的科目信息。
select s.subjectname '科目名称' from mark m right join subjects s on m.subjectid = s.subjectid where m.studentscore is null and m.studentno is null;