多表查询:
1导入一个hellodb.sql数据库用于之后的查询
#mysql -uroot -hlocalhost < hellodb.sql
多表查询:查询时候最好有多个限定条件,因为会先生成一个新表,在新表基础上查询,不限定条件生成的表会太大。
交叉连接:笛卡尔乘积m*n 用的少,最消耗资源
内连接:
1 等值链接: 让表之间的字段以等值的方式建立链接,查询时会先生成一个中间表,基于这个表进行查询,
> select * from student,classes where student.classid = class.id
>select name,class from student,classes where student.classid = classes.classid;
>select name,class,student,classid from student,classes where student.classid = classes.classid and name = 'xi ren';
2 不等值链接
3自然链接:
4自链接: 在一张表中,自己与自己链接
> select * from student as t,techer as t where s.stuid = t.techerid;
外链接: 有对应关系就显示,没有对应项就留空
1 左外链接,以左表为标准,右表没有对应项的留空
>select * from student as s left join classes as c on s.classid = c.classid;
2 右外链接,以右表为标准,左表没有对应的项的留空
> select * from student as s right join classese as c on s.classid = c.classid;
子查询: 在查询中套且
1 用于where 子句中的子查询
#找表中同学年龄大于平均年龄的同学:
>select name,age from student where age > (select avg(age) from student);
(1) 用于比较表达式中的子查询:子查询仅能返回单个值,若返回多个值会报错;
>select name,age from student where age > (select avg(age) from student);
(2) 用于IN中的子查询:子查询可以返回一个列表值 返回多个值;
#求出所有女同学的班级编号
#那些男同学属于这些班级
>SELECT * FROM STUDENTS WHERE ClassID IN (SELECT DISTINCT(去重) ClassID FROM students WHERE Gender = ‘F’) AND Gender=‘M’;
2 用于FROM子句中的子查询;
> SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause;
联合查询:将多个查询语句的执行结果相合并;union
SELECT clause UNION SELECT cluase;
SELECT Name,Age FROM teacher UNION SELECT Name,Age FROM students;
练习:导入hellodb.sql生成数据库 (1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄; SELECT Name,Age FROM students WHERE Age > 25 AND Gender='M'; (2) 以ClassID为分组依据,显示每组的平均年龄; SELECT ClassID,avg(age) FROM students GROUP BY ClassID; (3) 显示第2题中平均年龄大于30的分组及平均年龄; SELECT ClassID,avg(age) AS Aging FROM students GROUP BY ClassID HAVING Aging>30; (4) 显示以L开头的名字的同学的信息; SELECT * FROM students WHERE Name LIKE 'L%'; (5) 显示TeacherID非空的同学的相关信息; SELECT * FROM students WHERE TeacherID IS NOT NULL; (6) 以年龄排序后,显示年龄最大的前10位同学的信息; SELECT * FROM students ORDER BY Age DESC LIMIT 10; (7) 查询年龄大于等于20岁,小于等于25岁的同学的信息;用三种方法; >select * from student where age > 20 and age <= 25;
1、以ClassID分组,显示每班的同学的人数; SELECT ClassID,count(StuID) FROM students GROUP BY ClassID; 2、以Gender分组,显示其年龄之和; SELECT Gender,SUM(Age) FROM students GROUP BY Gender; 3、以ClassID分组,显示其平均年龄大于25的班级; SELECT ClassID,avg(age) FROM students GROUP BY ClassID HAVING avg(Age) > 25; 4、以Gender分组,显示各组中年龄大于25的学员的年龄之和; > select gender,sum(age) from student where age > 25 group by gender; 练习:导入hellodb.sql,完成以下题目: 1、 显示前5位同学的姓名、课程及成绩; >select 2、显示其成绩高于80的同学的名称及课程; 3、求前8位同学每位同学自己两门课的平均成绩,并按降序排列oracle desc; 4、显示每门课程课程名称及学习了这门课的同学的个数; 思考: 1、如何显示其年龄大于平均年龄的同学的名字? 2、如何显示其学习的课程为第1、2,4或第7门课的同学的名字? 3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学? 4、统计各班级中年龄大于全校同学平均年龄的同学。