MySQL查询练习

1、 查询全部一年级的学生信息。

select gradeid from grade where gradename='一年级';

2、 查询全部二年级的学生的姓名和电话。

select s.studentname,s.phone from student s inner join grade g on s.gradeid = g.gradeid where g.gradename='二年级';

3、 查询全部一年级女同学的信息。

select s.*,g.gradename from student s inner join grade g on s.gradeid = g.gradeid where g.gradename='一年级' and s.sex='女';

4、 查询课时超过60的科目信息。

select * from subjects where classhour>=60;

5、 查询二年级的科目名称

select gradeid from grade where gradename='二年级';

6、 查询二年级男同学的姓名和住址。

select s.studentname,s.address from student s inner join grade g on s.gradeid = g.gradeid where g.gradename='二年级' and s.sex='男';

7、 查询无电子邮件的学生姓名和年级信息。

select * from student s ,grade g where s.gradeid=g.gradeid  and  s.email is null or s.email='' and  s.gradeid=g.gradeid; 

8、 查询出生日期在1992年之后的男学生姓名和年级信息。

select s.*,(select gradename from grade g where g.gradeid=s.gradeid) from student s where born >='1992-01-01';

9、 参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息

select * from mark where examdate='2015-07-03';

10、 按照出生日期查询一年级的学生信息。

select * from student where gradeid = (select gradeid from grade where gradename='一年级') order by born

11、 按成绩由高到低的次序查询参加编号为1的科目考试信息。

select * from mark where subjectid=1 order by studentscore desc

12、 查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。

select * from mark where examdate='2015-07-01' and subjectid=(select subjectid from subjects  where subjectname='MySQL深入') order by studentscore desc limit 2;

13、 查询课时最多的科目名称及课时。

select subjectname,classhour from subjects where classhour=(select max(classhour) from subjects);

14、 查询年龄最小的学生所在的年级及姓名。

select s.*,(select gradename from grade where grade.gradeid = s.gradeid) gradename from student s where born=(select max(born) from student);

15、 查询考试的最低分出现在哪个科目

select * from subjects where subjectid in(select subjectid from mark where studentscore=(select min(studentscore) from mark));

16、 查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。

select * from mark where studentno='s1001' order by examdate ;

17、 查询年龄超过25周岁的学生信息。

select s.*,(select gradename from grade g where g.gradeid=s.gradeid) from student s where old >25

18、 查询1月份过生日的学生信息

select * from student where bron like '%-01-%';

19、 查询今天过生日的学生姓名及所在年级。

20、 新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com

21、 查询住址为“雁塔区”的学生姓名、电话、住址

select studentname,phone,address from student where address = '雁塔区';

22、 查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。

select * from subjects where subjectname like '%计算机%' order by gradeid desc;

23、 查询电话中含有以“130”开头的学生姓名，住址和电话。

select studentname,address,phone from student where phone like '130%' ;

24、 查询姓“赵”的学号、姓名和住址。

select studentno,studentname,address from student where studentname like '赵%';

25、 统计一年级女生的总人数。

select gradeid from grade where gradename='一年级';
select count(1) from student where sex='女' and gradeid=(select gradeid from grade where gradename='一年级');

26、 查询李四总成绩

select studentno from student s where s.studentname='李四';
select studentno,'李四' as '姓名',sum(studentscore) from result where studentno in (select studentno from student s where s.studentname='李四') group by studentno;

27、 学号为s1003的学生所有课总成绩

select sum(studentscore) from result where studentno ='s1003';

28、 学号为s1003的学生考试的平均分。

select avg(studebtscore) from result where studentno = 's1003';

29、 查询一年级的科目“Mysql”的最高分、最低分、平均分。

select gradeid from grade where gradename='一年级';
select subjectid from subjects where subjectname like  '%MySQL%' and gradeid=(select gradeid from grade where gradename='一年级');
select subjectid,max(studentscore,min(studentscore),avg(studentscore) from result where subjectid in (select subjectid from subjects where subjectname like  '%MySQL%' and gradeid=(select gradeid from grade where gradename='一年级')) group by subjectid;

30、 查询每个年级的总学时数，并按照升序排列。

select gradeid,sum(classhour) from subjects group by gradeid order by sum(classhour);

31、 查询每个参加考试的学员的平均分。（Group by 学号）

select studentno,avg(studentscore) from result group by studentno;

32、 查询每门课程的平均分，并按照降序排列。（group by 课程）

select subjectid,avg(studentscore) from result group by subjectid  order by avg(studentscore) desc;

33、 查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）

select studentno,sum(studentscore) from result group by studentno order by sum(studentscore) desc;

34、 查询一年级的平均年龄。

select gradeid from subjects where subjectname = '一年级'; 
select avg(now() - born) from student where gradeid = (select gradeid from subjects where subjectname = '一年级');

35、 查询每个年级西安地区的学生人数。

select gradeid,count(1) from student where address like '%西安%' group by gradeid;

36、 查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列

select studentno,avg(studentresult) from result group by studentno having avg(studentresult)>=60 order by avg(studentresult);

37、 查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。

select studentno,(select s.studentname from student s where s.studentno=r.studentno ) stuname ,count(1) from result r where studentresult<60 group by studentno having count(1)>1;

38、 查询学生姓名、所属年级名称及联系电话。

select s.studentname, (select g.gradename from grade g where g.gradeid=s.gradeid) ,s.phone from student s;

39、 查询年级编号为1的科目名称、年级名称及学时。

select s.subjectname, (select g.gradename from grade g where g.gradeid=s.gradeid) , s.classhour from subjects s where gradeid=1;

40、 查询参加科目编号为1的考试的学生姓名、分数、考试日期。

select  ( select s.studentname from student s where s.studentno=r.studentno ), r.studentresult,r.examdate from result r where subjectid=1;

41、 查询学号为s1001的学生参加的考试科目名称、分数、考试日期。

select  (select s.subjectname from subjects s where  s.subjectid=r.subjectid ) ,r.studentresult,r.examdate from result r where studentno='s1001';

42、 查询所有科目的参考信息（某些科目可能还没有被考试过）

select r.*,(select subjectname from subjects s where s.subjectid=r.subjectid ) from result r;

43、 查询没有被考过的科目信息。

select distinct subjectid from result;
select * from subjects where subjectid not in (select distinct subjectid from result);

课后练习：

1. 查询所有记录的e_no、e_name和e_salary字段值

 select e_no,e_name,e_salary from employee;

2. 查询dept_no等于10和20的所有记录

select * from employee where dept_no =10 or dept_no = 20;

3. 查询工资范围在800~2500之间的员工信息

select * from employee where e_salary >800 and e_salary <= 2500;

4. 查询部门编号为20的部门中的员工信息

select * from employee where dept_no=20;

5. 查询每个部门最高工资的员工信息

select dept_no, max(e_salary) from employee group by dept_no;

6. 查询员工BLAKE所在部门和部门所在地

 select d_no, d_location  from dept where d_no=(select dept_no from employee where e_name='BLAKE');

7. 查询所有员工的部门和部门信息

select e_no, e_name, dept_no,d_name,d_location from employee, dept where dept.d_no=employee.dept_no;

8. 计算每个部门各有多少名员工

  select dept_no, count(*) from employee group by dept_no;

9. 计算不同类型职工的总工资数

 select e_job, sum(e_salary) from employee group by e_job;

10. 计算不同部门的平均工资

select dept_no, avg(e_salary) from employee group by dept_no;

11. 查询工资低于1500的员工信息

select * from employee where e_salary < 1500;

12. 将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列

select e_name,dept_no, e_salary from employee order by dept_no desc, e_salary desc;

13. 查询员工姓名以字母‘A’或‘S’开头的员工的信息

select * from employee wherew e_name regexp '^[as]';

14. 查询到目前为止工龄大于等于18年的员工信息

SELECT * FROM employee WHERE (YEAR(NOW()) - YEAR(hireDate)) > 18;

15. 计算所有女员工（‘F’）的年龄

SELECT e.e_name,e.e_gender,YEAR(CURDATE()) - YEAR(e.hireDate) FROM employee AS e
WHERE e.e_gender = ‘f’;

16. 使用LIMIT查询从第3条记录开始到第6条记录的结果

SELECT * FROM employee LIMIT 2,3;

17. 查询销售人员（SALSEMAN）的最低工资

SELECT MIN(e_salary) FROM employee WHERE e_job = ‘SALESMAN’;

18. 查询名字以字母N或者S结尾的记录

SELECT * FROM employee WHERE e_name LIKE ‘%N’ OR e_name LIKE ‘%S’;

19. 查询在BeiJing工作的员工的姓名和职务

select e.e_name, e.e_job from employee as e,dept as d where e.dept_no = d.d_no and d.d_location = ‘BeiJing’;

20. 使用左连接方式查询employee和dept表

select * from employee left join dept on employee.dept_no = dept.d_no;

21. 查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并两个查询结果

select * from employee where year(hireDate) between 2001 and 2005 
union 
select * from employee where dept_no IN(20,30);

22. 使用LIKE查询员工姓名中包含字母a的记录

select * from employee where e_name like ‘a%’ or e_name like ‘%a’ or e_name like ‘%a%’;

23. 使用REGEXP查询员工姓名中包含T、C或者M这3个字母中任意1个的记录

select * from employee where e_name REGEXP ‘[TCM]’;