select的高级用法

Posted by

根据两张表的内容查出想要的数据

mysql> select t1.name,t2.score from t1,t2 where t1.id=t2.id and t1.name='bgx';

1.1传统连接(只能内连接,只能取交集)

语法:

​ select 列名 from 表名 where 条件 and 条件;

#世界上国家人口数量小于100的城市在哪个国家,说的什么语言?

mysql> select country.population,city.name,country.name,countrylanguage.Language 
from city,country,countrylanguage 
where city.CountryCode=country.code 
and country.code=countrylanguage.CountryCode 
and country.Population<100;

1.2NATURAL JOIN(自连接的表要有共同的列名字)

语法:

​ select 列名 from 小表 natural join 大表 where 条件 order by 列名

#城市人口数量等于2352121,城市说的什么语言,国家代码是什么?

mysql> select city.name,countrylanguage.language,city.countrycode 
from city natural join countrylanguage 
where population=2352121;

mysql> select city.name,city.countrycode,countrylanguage.language,city.population
from city natural join countrylanguage 
where population > 1000000
order by population;

ORDER BY 语句用于根据指定的列对结果集进行排序。

1.3企业中多表连接查询(内连接)

语法:

​ select 列名 from 小表 join 大表 on 共同的字段 where 条件;

mysql> select city.name,city.countrycode,countrylanguage.language,city.population
from city join countrylanguage 
on city.CountryCode=countrylanguage.CountryCode
where population>1000000;

建议:使用join语句时,小表在前,大表在后。

1.4外连接

语法:

​ select 列名 from 左表 left (左)|right (右) join 右表 on 相同字段 and 条件;

#左外连接
mysql> select city.name,country.code,country.name 
from city left join country 
on city.countrycode=country.code 
and city.population<100;

#右外连接
mysql> select city.name,country.code,country.name 
from city right join country 
on city.countrycode=country.code 
and city.population<100;

1.5UNION(合并查询)

#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';
#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');
前两种方法速率相同,后面方法速率更快
替换为:
mysql> select * from city where countrycode='CHN' 
union  all
select * from city where countrycode='USA' limit 10

练习:

1、查询所有的课程的名称以及对应的任课老师姓名

#传统连接查询
select course.cname,teacher.tname
from course,teacher
where course.cid=teacher.tid;

#内连接查询
select course.cname,teacher.tname
from teacher join course
on course.cid=teacher.tid;

#外连接查询
select course.cname,teacher.tname
from teacher right join course
on course.cid=teacher.tid;

2、查询数据库成绩等于100的学生的姓名

#传统连接查询
select student.sname,course.cname,score.num
from student,course,score
where student.sid=score.student_id
and course.cid=score.course_id
and score.num=100;

#内连接查询
select student.sname,course.cname,score.num
from course join student join score
on course.cid=score.course_id
and student.sid=score.student_id
where score.num=100;

select student.sname,score.num
from student left join score
on score.student_id=student.sid
and score.num=100
group by student.sid;


3、查询平均成绩大于八十分的同学的姓名和平均成绩

#传统连接查询
select student.sname,avg(score.num)
from student,score
where student.sid=score.student_id
group by student.sid
having avg(score.num)>80;

#内连接查询
select student.sname,avg(score.num)
from student join score 
on student.sid=score.student_id
group by student.sid
having avg(score.num)>=80;

#外连接查询
select student.sname,avg(score.num)
from student right join score 
on student.sid=score.student_id
group by student.sid
having avg(score.num)>=80;

4、查询姓李的老师的个数

select count(tname) 
from teacher 
where tname like '李%';

select tname from teacher where tname like '李%';

5、查询架构成绩不及格的学生姓名和对应架构分数

#传统连接查询
select student.sname,course.cname,score.num
from student,course,score
where course.cid=1 and
student.sid=score.student_id and
course.cid=score.course_id and
score.num < 60;

#内连接查询
select student.sname,course.cname,score.num
from course join student join score
on student.sid=score.student_id 
and course.cid=score.course_id
where course.cid=1 and score.num<60;

select student.sname,avg(score.num)>80 from student,score where score.student_id=student.sid group by student.sid ;
select student.sname,avg(score.num) from student,score where student.sid=score.student_id group by student.sid ;
select student.sname,avg(score.num) from student,score where student.sid=score.student_id group by student.sid having avg(score.num)>80;