-- 查询
-- 1.要求求出每个学生对应的老师。
create view wr_v_teach
as
SELECT a.`name`,c.teachername
from wr_t_students a, wr_t_class b,wr_t_teacher c
where a.classid=b.id
and b.teacherid=c.id
;
-- java优化建议:尽量多使用视图(VIEW)来查询.
-- VIEW就是快捷方式
create view wr_v_students
as
SELECT a.id,a.age,a.birth,a.classid,a.creditcard,a.major,a.`name`,a.photoid,a.telephone,a.univercity,
(case when a.sex=1 then '男' else '女' end) sex
from wr_t_students a
;
-- 2.取students表的前5条记录(不用top,而用Limit)
SELECT *
from wr_t_students a limit 1,5 -- limit n 表示取前n条 limit m,n表示取从第m条(不包括m)开始取n条 分页
;
-- 3.取students表有几个班级的学生
SELECT DISTINCT a.classid -- 祛除重复的关键字:distinct
from wr_t_students a
;
-- 4.取出photoid为空的数据
SELECT a.id,a.age,a.birth,a.classid,a.creditcard,a.major,a.`name`,a.photoid,a.telephone,a.univercity,
(case when a.sex=1 then '男' else '女' end) sex
from wr_t_students a
where a.photoid is null -- a='null' =''
;
-- 5.让性别=1显示男,=0显示女
SELECT a.id,a.age,a.birth,a.classid,a.creditcard,a.major,a.`name`,a.photoid,a.telephone,a.univercity,
(case when a.sex=1 then '男' -- 1.case xx when xx1 (不推荐: ) 2. case when xx=xx1 /xx is null
else '女'
end) sex
from wr_t_students a
;
-- 6.计算每名老师分别都有多少学生
-- 聚合函数:count,sum,max,min,avarage
-- 第一种取交集查询
SELECT c.teachername,count(a.`name`) -- 计算有多少条数据 :count
from wr_t_students a, wr_t_class b,wr_t_teacher c
where a.classid=b.id
and b.teacherid=c.id
GROUP BY c.teachername
;
-- 第二种方法view
select a.teachername,count(*)
from wr_v_teach a
GROUP BY a.teachername
;
-- 第三种方法嵌套查询
SELECT d.teachername,count(*)
from (SELECT a.`name`,c.teachername
from wr_t_students a, wr_t_class b,wr_t_teacher c
where a.classid=b.id
and b.teacherid=c.id) d
GROUP BY d.teachername
;
-- 7.按照年龄,性别从小到大排序
SELECT *
from wr_t_students a
ORDER BY a.sex,a.age
;
-- 8.取左、取右
SELECT a.creditcard,right(a.creditcard,2),left(right(a.creditcard,2),1)
from wr_v_students a
;
-- 9.去除空格 trim
SELECT *
from wr_v_students a
where trim(a.univercity)='南京邮电大学'
or a.univercity='南邮'
MYSQL教程:查询篇
最新推荐文章于 2024-08-15 23:27:38 发布