子查询练习（数据库）

练习一：

数据表的字段情况及表间关系：（上篇已创建数据表）

1> 年级表： Grade 表

2> 学生表： Student表

 

3> 科目表：Subjects表

4> 成绩表：Result表

 

5>表关系 

 

查询 练习内容：

--1、 查询全部一年级的学生信息。
SELECT * FROM student WHERE GradeId =(SELECT GradeId FROM grade WHERE GradeName = '一年级');
--2、 查询全部二年级的学生的姓名和电话。
SELECT StudentName,Phone FROM student WHERE GradeId =(SELECT GradeId FROM grade WHERE GradeName = '二年级');
--3、 查询全部一年级女同学的信息。
SELECT * FROM student WHERE GradeId =(SELECT GradeId FROM grade WHERE GradeName = '一年级') AND Sex = '女';
--4、 查询课时超过60的科目信息。
SELECT * FROM subjects ClassHour > 60;
--5、 查询二年级的科目名称
SELECT SubjectName FROM subjects WHERE GradeId = (SELECT GradeId FROM GradeName = '二年级');
--6、 查询二年级男同学的姓名和住址。
SELECT StudentName,Address FROM student WHERE GradeId=(SELECT GradeId FROM grade WHERE GradeName = '二年级') and Sex = '男';
--7、 查询无电子邮件的学生姓名和年级信息。
SELECT StudentName,GradeName FROM student,grade WHERE (Email = '' or Email is NULL) AND student.GradeId = grade.GradeId;
--8、 查询出生日期在1992年之后的男学生姓名和年级信息。
SELECT StudentName ,GradeName FROM student,grade WHERE BornDate > '1992-12-31' and student.GradeId = grade.GradeId;
--9、 参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
SELECT t1.* FROM result t1,subjects t2 WHERE t1.ExamDate = '2015-7-3' and t2.SubjectName = '计算机组成原理' and t1.SubjectId = t2.SubjectId;
--10、 按照出生日期查询一年级的学生信息。
SELECT * FROM student WHERE GradeId = (SELECT GradeId FROM GradeName = '一年级') ORDER BY BornDate;
--11、 按成绩由高到低的次序查询参加编号为1的科目考试信息。
SELECT StudentResult FROM result WHERE SubjectId='1' ORDER BY StudentResult DESC;
--12、 查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
SELECT * FROM result,subjects WHERE StudentResult = (SELECT DISTINCT result.StudentResult FROM result WHERE ExamDate = '2015-7-1' AND SubjectId = (SELECT SubjectId FROM subjects WHERE SubjectName= 'MySql深入考试')  ORDER BY StudentResult DESC LIMIT 0,1) or StudentResult = (SELECT DISTINCT result.StudentResult FROM result WHERE ExamDate = '2015-7-1' AND SubjectId = (SELECT SubjectId FROM subjects WHERE SubjectName= 'MySql深入考试')  ORDER BY StudentResult DESC LIMIT 1,1) AND result.SubjectId = subjects.SubjectId AND subjects.SubjectName = 'MySql深入考试';


SELECT * FROM result WHERE StudentResult IN (SELECT a.StudentResult FROM (SELECT DISTINCT result.StudentResult FROM result WHERE ExamDate = '2015-7-1' AND SubjectId = (SELECT SubjectId FROM subjects WHERE SubjectName= 'MySql深入考试')  ORDER BY StudentResult DESC LIMIT 2)a);
--13、 查询课时最多的科目名称及课时。
SELECT SubjectName,ClassHour FROM subjects WHERE ClassHour = (SELECT MAX(ClassHour) FROM subjects);
--14、 查询年龄最小的学生所在的年级及姓名。
SELECT t1.GradeName,t2.StudentName FROM grade t1,student t2 WHERE BornDate = (SELECT MAX(BornDate) FROM student) and t1.GradeId = t2.GradeId;
--15、 查询考试的最低分出现在哪个科目
SELECT t1.SubjectName,StudentResult FROM subjects t1, result  t2 WHERE t2.StudentResult = (SELECT min(StudentResult) FROM result) AND t1.SubjectId = t2.SubjectId;
--16、 查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
SELECT * FROM result WHERE StudentNo = 's1001' ORDER BY ExamDate ASC;
--17、 查询年龄超过25周岁的学生信息。
SELECT * FROM student WHERE YEAR(NOW()) - YEAR(BornDate) > 25;
--18、 查询1月份过生日的学生信息
SELECT * FROM student WHERE MONTH(BornDate)=1;
--19、 查询今天过生日的学生姓名及所在年级。
SELECT StudentName ,GradeName FROM student ,grade WHERE MONTH(student.BornDate) = MONTH(NOW()) AND day(student.BornDate) = day(NOW()) AND student.GradeId = grade.GradeId;
--20、 新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com
--21、 查询住址为“雁塔区”的学生姓名、电话、住址
SELECT StudentName,Phone,Address FROM student WHERE Address LIKE '%雁塔区%';
--22、 查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
SELECT SubjectName,ClassHour,GradeName FROM grade,subjects WHERE subjects.SubjectName LIKE '%计算机%' AND grade.GradeId = subjects.GradeId ORDER BY subjects.GradeId ASC;
--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 = '女' and GradeId = (SELECT GradeId FROM grade WHERE GradeName = '一年级');
--26、 查询李四总成绩
SELECT StudentNo,sum(StudentResult) '总成绩' FROM result WHERE StudentNo in  (SELECT StudentNo FROM student WHERE StudentName = '李四') GROUP BY StudentNo;
--27、 学号为s1003的学生所有课总成绩
SELECT sum(StudentResult) '总成绩' FROM result WHERE StudentNo= 's1003';
--28、 学号为s1003的学生考试的平均分。
SELECT avg(StudentResult) '平均分' FROM result WHERE StudentNo= 's1003';
--29、 查询一年级的科目“Mysql”的最高分、最低分、平均分。
SELECT max(StudentResult),min(StudentResult),avg(StudentResult) FROM result WHERE SubjectId IN (SELECT SubjectId FROM subjects WHERE SubjectName = 'MySql'AND GradeId = (SELECT GradeId FROM grade WHERE GradeName = '一年级'));
--30、 查询每个年级的总学时数，并按照升序排列。
SELECT sum(ClassHour) '总学时' FROM subjects GROUP BY GradeId ORDER BY sum(ClassHour) ASC;
--31、 查询每个参加考试的学员的平均分。
SELECT GradeId ,avg(StudentResult) '平均分'FROM result GROUP BY StudentNo;
--32、 查询每门课程的平均分，并按照降序排列。
SELECT avg(StudentResult) FROM result GROUP BY SubjectId ORDER BY avg(StudentResult) DESC;
--33、 查询每个学生参加的所有考试的总分，并按照降序排列。
SELECT sum(StudentResult) FROM result GROUP BY StudentNo ORDER BY sum(StudentResult) DESC;
--34、 查询一年级的平均年龄。
SELECT YEAR(NOW()) - avg(YEAR(BornDate)) FROM student;
--35、 查询每个年级西安地区的学生人数。
SELECT  GradeId,count(1) '总人数' FROM student WHERE Address LIKE '%西安%' GROUP BY GradeId;
--36、 查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
SELECT * FROM student t1, (SELECT StudentNo,avg(StudentResult) avgresult FROM result GROUP BY StudentNo) t2 WHERE t2.avgresult >= 60 AND t1.StudentNo =t2.StudentNo ORDER BY t2.avgresult;
--37、 查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
SELECT StudentNo,(SELECT StudentName FROM student WHERE student.StudentNo = result.StudentNo) stuname,count(1) FROM result WHERE StudentResult < 60 GROUP BY StudentNo HAVING count(1) > 0 ;
--38、 查询学生姓名、所属年级名称及联系电话。
SELECT StudentName , Phone, GradeName FROM student,grade WHERE student.GradeId = grade.GradeId;
--39、 查询年级编号为1的科目名称、年级名称及学时。
SELECT SubjectName,GradeName,ClassHour FROM subjects,grade WHERE subjects.GradeId = grade.GradeId AND grade.GradeId = 1;
--40、 查询参加科目编号为1的考试的学生姓名、分数、考试日期。
SELECT StudentName,StudentResult,ExamDate FROM student,result WHERE SubjectId = 1 AND result.StudentNo = student.StudentNo;
--41、 查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
SELECT SubjectName,StudentResult,ExamDate FROM result,subjects WHERE StudentNo = 's1001' AND result.SubjectId = subjects.SubjectId;
--42、 查询所有科目的参考信息（某些科目可能还没有被考试过）
SELECT * FROM subjects LEFT JOIN result ON subjects.SubjectId = result.SubjectId ;
--43、 查询没有被考过的科目信息
SELECT subjects.* FROM subjects WHERE subjects.SubjectId  not in (SELECT DISTINCT SubjectId FROM subjects);
--平均低于总平均
SELECT * FROM student WHERE StudentNo in  (SELECT StudentNo FROM result GROUP BY StudentNo HAVING avg(StudentResult)<(SELECT avg(StudentResult) FROM result));

练习二

数据表字段

 

创建数据表及查询练习 ：

USE mytest;
CREATE TABLE employee(
		e_no int(11) PRIMARY KEY NOT NULL UNIQUE,
		e_name varchar(50) not NULL,
		e_age INT NOT NULL,
		e_gender char(2),
		dept_no int(11) NOT NULL,
		e_job VARCHAR(50) NOT NULL,
		e_salary int(11) NOT NULL,
		hireDate date NOT NULL,
		CONSTRAINT fk_emp_dept FOREIGN KEY ( dept_no ) REFERENCES dept ( d_no )
		);
CREATE TABLE dept(
		d_no INT(11) PRIMARY KEY auto_increment,
		d_name VARCHAR(50) not NULL,
		d_location VARCHAR(100)
);
--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 in (10,20);
--3. 查询工资范围在800~2500之间的员工信息
SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;
--4. 查询部门编号为20的部门中的员工信息
SELECT * FROM employee WHERE dept_no = 20;
--5. 查询每个部门最高工资的员工信息
SELECT t1.* FROM employee t1,
(SELECT max(e_salary) maxsalary,dept_no FROM employee  GROUP BY dept_no) t2 WHERE t1.e_salary > t2.maxsalary AND t1.dept_no = t2.dept_no; 
--6. 查询员工BLAKE所在部门和部门所在地
SELECT d_name,d_location FROM dept WHERE d_no = (SELECT dept_no FROM employee WHERE e_name = 'BLAKE');
--7. 查询所有员工的部门和部门信息
SELECT * FROM employee,dept WHERE employee.dept_no = dept.d_no ;
--8. 计算每个部门各有多少名员工
SELECT count(1) FROM employee GROUP BY dept_no;
--9. 计算不同类型职工的总工资数
SELECT  sum(e_salary) FROM employee GROUP BY e_job;
--10. 计算不同部门的平均工资
SELECT avg(e_salary) FROM employee GROUP BY dept_no;
--11. 查询工资低于1500的员工信息
SELECT * FROM employee WHERE e_salary < 1500;
--12. 将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列
SELECT * FROM employee ORDER BY dept_no DESC , e_salary asc;
--13. 查询员工姓名以字母‘A’或‘S’开头的员工的信息
SELECT * FROM employee WHERE e_name LIKE 'A%' or e_name LIKE 'S%'; 
--14. 查询到目前为止工龄大于等于18年的员工信息
SELECT * FROM employee WHERE YEAR(NOW()) - YEAR(hiredate) > 18;
--15. 计算所有女员工（‘F’）的平均年龄
SELECT avg(e_age) FROM employee WHERE e_gender = 'F';
--16. 使用LIMIT查询从第3条记录开始到第6条记录的结果
SELECT * FROM employee LIMIT 2,3;
--17. 查询销售人员（SALSEMAN）的最低工资
SELECT min(e_salary) FROM employee WHERE e_job = 'SALSEMAN' ;
--18. 查询名字以字母N或者S结尾的记录
SELECT * FROM employee WHERE e_name LIKE '%N' or e_name like '%S';
--19. 查询在BeiJing工作的员工的姓名和职务
SELECT e_name,e_job FROM employee,dept WHERE d_location='BeiJing' AND dept_no = d_no; 
--20. 使用左连接方式查询employee和dept表
SELECT * FROM employee LEFT JOIN dept on dept_no = 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%';
--23. 使用REGEXP查询员工姓名中包含T、C或者M这3个字母中任意1个的记录
SELECT * FROM employee WHERE e_name REGEXP '[T|C|M]';