centos 数据库查询操作
2019年11月20日
21:01
1. 联合查询,多张表查询,纵向合并
MariaDB [hellodb]> select name,age from students union select name,gender from teachers
2. 横向合并,交叉连接(横向笛卡尔)
MariaDB [hellodb]> select * from students cross join teachers;
MariaDB [hellodb]> select * from teachers , students;
给表定义别名(注意定义别名后,就必须使用别名)
MariaDB [hellodb]> select stuid,s.name student_name,s.age student_age,tid,t.name teacher_name,t.age teacher_age from teachers t cross join students s ;
3. 内连接(找出两个表共同的特征)
MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;
交叉查询,然后筛选条件
MariaDB [hellodb]> select * from students , teachers where students.teacherid=teachers.tid;
内连接后过滤数据
MariaDB [hellodb]> select * from students s inner join teachers t on s.teacherid=t.tid and s.age > 30
4. 左外连接(找出左边的表和以及两个表的交集)
MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid;
左外连接扩展(找出左边的表,并且不显示和右边有交集的表)
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where tid is null;
5. 右外连接(找出右边的表和以及两个表的交集)
MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid ;
右外连接扩展(找出右边的表,并且不显示和左边有交集的表)
MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid where s.teacherid is null;
6. 完全连接
MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students
as s left join teachers as t on s.teacherid=t.tid
-> union
-> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right
join teachers as t on s.teacherid=t.tid;
7. 自连接
MariaDB [hellodb]> select e.name,l.name from emp as e inner join emp as l on
e.leaderid=l.id;
MariaDB [hellodb]> select e.name emp ,l.name leader from emp as e left join emp
as l on e.leaderid=l.id;
8. 三张表查询
MariaDB [hellodb]> select st.name,co.course,sc.score from students st inner
join scores sc on st.stuid=sc.stuid inner join courses co on
sc.courseid=co.CourseID;
9. 正序排列,但是把null放在最后
order by -classid desc ;
where in (select 语句) 括号里不支持limit
limit 取多少个
select distinct stuid from 去重stuid
10. 练习
1. 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
select * 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) from students group by classID having avg(age) > 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 students where age >= 20 and age <=25;
8. 以ClassID分组,显示每班的同学的人数
select classid,count(*) from students where classid is not null group by classid;
9. 以Gender分组,显示其年龄之和
select gender,sum(age) from students group by gender;
10. 以ClassID分组,显示其平均年龄大于25的班级
select classid,avg(age) from students group by classid having avg(age) > 25 and classid is not null;
11. 以Gender分组,显示各组中年龄大于25的学员的年龄之和
select gender,sum(age) from students where age > 25 group by gender;
12. 显示前5位同学的姓名、课程及成绩
select st.stuid,st.name,sc.score,courses.Course from scores sc inner join students st on sc.stuid=st.stuid inner join courses on sc.courseid=courses.Courseid where st.stuid <=5 ;
13. 显示其成绩高于80的同学的名称及课程
select st.name,sc.score,courses.Course from scores sc inner join students st on sc.stuid=st.stuid inner join courses on sc.courseid=courses.Courseid where sc.score > 80 ;
14. 取每位同学各门课的平均成绩,显示成绩前三名的同学的姓名和平均成绩
select students.name,avg(scores.score) from scores inner join students on students.stuid=scores.stuid group by scores.stuid order by avg(scores.score) desc limit 3;
15. 显示每门课程课程名称及学习了这门课的同学的个数
select courses.CourseID,courses.Course,scores.StuID,count(courses.CourseID) from courses inner join scores on scores.CourseID=courses.CourseID group by courses.CourseID;
16. 显示其年龄大于平均年龄的同学的名字
select * from students where age > (select avg(age) from students);
17. 显示其学习的课程为第1、2,4或第7门课的同学的名字
select stuid,name from students where stuid in (select distinct stuid from scores where courseid in (1,2,4,7));
18. 显示其成员数最少的3个的班级的同学中年龄大于同班同学平均年龄的同学
select * from students inner join (select classid from students group by classid having classid is not null order by count(classid) limit 3 ) as sc on students.ClassID=sc.Classid where age > (select avg(age) from students);
19. 统计各班级中年龄大于全校同学平均年龄的同学
select * from students where age > (select avg(age) from students) and classid is not null;