centos 数据库查询操作

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;