概念要达到默写的程度。

where 谓语查询 条件查询

范围

• eg1:select * from student where high >132 and avgscore > 75 and timestampdiff(year,birthday,now()) >24
• eg2:select name,high from student where high between 172 and 175

between and 包含边界值

• eg3:select name,sex,high from student where (sex='男' and high between 172 and 178) or (sex='女' and high between 158 and 164)
• 优先级：not>and>or

列表

• eg1 in n选1:select name,area from student where area in('成都','南充','广安')
• eg2属牛属羊:select name,birthday from student where timestampdiff(year,'1941-01-01', birthday) % 12 =0 or timestampdiff(year,'1943-01-01',birthday) % 12 =0
• eg3与上同，in改写:select name,birthday from student where timestampdiff(year,'1941-01-01',birthda y) % 12 in (0,2)
• eg4:select name,case timestampdiff(year,'1900-01-01',birthday) % 12 when '0' then '鼠' when '1' then '牛' when '2' then '虎' when '3' then '兔' when '4' then '龙' when '5' then '蛇' when '6' then '马' when '7' then '羊' when '8' then '猴' when '9' then '鸡' when '10' then '狗' when '11' then '猪' end as '属相',sex from student where timestampdiff(year,'1900-01-01',birthday) % 12 in(5,7)
• eg5:SELECT NAME,CASE FLOOR(avgscore/10) WHEN '0' THEN '不及格' WHEN '1' THEN '不及格' WHEN '2' THEN '不及格' WHEN '3' THEN '不及格' WHEN '4' THEN '不及格' WHEN '5' THEN '不及格' WHEN '6' THEN '及格' WHEN '7' THEN '良好' WHEN '8' THEN '良好' WHEN '9' THEN '优秀' WHEN '10' THEN '满分' END AS '成绩', avgscore FROM student

模糊查询 正则表达式

• eg1:select name,homephone from student where homephone like '%7%'
• eg2:select '陈' as '姓氏',count(*) from student where name like '%陈%'
• eg3:select substr(name,1,1),count(*) from student where substr(name,1,1) = '陈'
• 注释：where条件筛选与having条件筛选：where一定在group by前面，是对个体条件的匹配；having 一定在group by后面，是对组的统计结果的匹配
• eg4找出139开头的电话号码:

select name,homephone from student where homephone regexp('139[0-9]{8}')

• eg5统计使用号码各运营商的人数,union各段语句的结构必须完全一致:

select '移动号码',count(*) from student where homephone regexp('13[4-9][0-9]{8}')
union
select '联通号码',count(*) from student where homephone regexp('13[0-3][0-9]{8}')
union
select '电信号码',count(*) from student where homephone regexp('18[0-9][0-9]{8}')

having 分组结果查询

• eg1显示人数在2人以上的姓氏:select substr(name,1,1),count(*) from student group by substr(name,1,1) having count(*) >=2

---

Example

• eg1统计各工龄段:select floor(timestampdiff(DAY,jointime,NOW())/365) as '工龄', count(*) as '人数', concat(floor(floor(timestampdiff(DAY,jointime,NOW())/365)/5)*5,'-', floor(floor(timestampdiff(DAY,jointime,NOW())/365)/5)*5+4) as '工龄段' from employee group by floor(floor(timestampdiff(DAY,jointime,NOW())/365)/5)

• eg2统计工龄满15年以上，参加工作在20岁以下的人员名单:

SELECT NAME,FLOOR(FLOOR(TIMESTAMPDIFF(DAY,jointime,NOW())/365)) AS '工龄',
FLOOR(FLOOR(TIMESTAMPDIFF(DAY,brithday,jointime)/365)) AS '参加工作年龄' FROM employee 
WHERE FLOOR(FLOOR(TIMESTAMPDIFF(DAY,jointime,NOW())/365)) >=10

• eg3显示职称，工作5年以下学徒 ，5-10技师 ，10-15 中级技师， 15年以上 高级技师

• eg4统计各部门各工龄段人数(单表or多表？group by？where？having？order？):

select did,concat(floor(timestampdiff(YEAR,jointime,now())/3)*3,'-',floor(timestampdiff(YEAR,jointime,now())/3)*3+2) 
as '工龄段' from employee 
group by did,floor(timestampdiff(YEAR,jointime,now())/3) 

---

连接

有条件连接 和 无条件连接（笛卡尔积-排列组合）

• 无条件 自然连接 交叉连接（没有实际意义只用于统计）

eg1:select * from student,class
eg2:select * from student cross join class 

• 有条件 内连接 外连接,默认内连接

eg1:select * from student s join class c on s.cno = c.cno(等值连接)
eg2:select t.tno,count(cno) from teacher t left outer join class c on t.tno = c.tno group by t.tno

eg3:统计各位同学报道注册的次数
SELECT s.`name` AS '姓名',COUNT(SRID) AS '报道注册次数' FROM student s LEFT OUTER JOIN studentreg st ON s.`sno` = st.`sno` GROUP BY s.`sno`

• 多表查询
  1.找出相关的表，找出相关的连接字段。
  2.是否需要分组，分组是否需要考虑保留不符合条件数据

eg1:统计各位教师所带的学生人数

select t.tno,t.tname,count(s.sno) from teacher t left join class c on t.tno = c.tno 
left join student s on c.cno = s.cno 
group by t.tno,t.tname having count(s.sno) <= 2

eg2:统计各门课程选课的男女人数

SELECT c.cno,c.cname,s.sno,sex,COUNT(s.sno) FROM course c JOIN sc ON c.cno = sc.cno 
JOIN student s ON sc.sno = s.sno 
GROUP BY c.cno,c.cname,sex

eg3:统计各个技能男女生人数

select s.sid,s.name,e.sex,count(e.eid) from skill s join es on s.sid = es.sid join employee e on e.eid = es.eid 
group by s.sid,s.name,e.sex

• 自连接 同一张表的两个拷贝进行连接
  eg1:找出是同乡的同学的组合

select s1.name,s1.sex,s2.name,s2.sex from student s1 join student s2 on s1.area = s2.area 
where s1.sno <> s2.sno
效率优化：
select s1.name,s1.sex,s2.name,s2.sex from student s1 join student s2 on s1.area = s2.area 
and s1.sno <> s2.sno

• select if(5>1,1,0)
• 一条相亲匹配查询sql：

select mgirl.mname,mgirl.msex,timestampdiff(YEAR,mgirl.birthday,now()) as '女方年龄',mboy.mname,
mboy.msex,timestampdiff(YEAR,mgirl.birthday,now()) as '男方年龄' 
from memberinfo mboy join memberinfo mgirl 
where mboy.msex = '男' and mgirl.msex='女' and mboy.high - mgirl.high between 0 and 10 
and abs(timestampdiff(YEAR,mboy.birthday,mgirl.birthday)) <10 
and (if(mgirl.o1 - mboy.y1 > 0,1,0) + if(mgirl.o2 - mboy.y2 > 0,1,0) + if(mgirl.o3 - mboy.y3 > 0,1,0) + 
if(mgirl.o4 - mboy.y5 > 0,1,0)) > =2 and (if(mboy.o1 - mgirl.y1 > 0,1,0) + if(mboy.o2 - mgirl.y2 > 0,1,0) + 
if(mboy.o3 - mgirl.y3 > 0,1,0) +if(mboy.o4 - mgirl.y4 > 0,1,0) +) >=3 

• 分析 on 与 where 的区别
• 题:1.统计各部门男女人数，要求显示出部门名称
• 2.找出技术组长后一年入职的员工
• 3.找出掌握了驾驶技能的员工名单
• 4.统计各部门掌握了驾驶技能的员工人数
• 5.统计各班身高在175以上的人数
• 6.统计各管理员的工作量（统计选课审核通过分别是哪些管理员审核通过）
• 7.找出和陈涛老师是一个属相的同学名单
• 8.统计各类别课程的学生人数