连接查询
连接查询(Join query)是一种数据库查询,用于从多个表或数据源中检索相关的数据。通过连接查询,可以将不同表中的数据组合起来
inner join #内连接
left join #左连接 常见使用
right join #右连接
outer join #外连接
查询姓王的学生姓名、课程号、课程名、期末分数
select s.sname, c.cno, c.cname, sc.score
from student s,
course c,
score sc
where sname like '王%'
and s.sno = sc.sno
and sc.cno = c.cno;
查询与贺明明老师一个院系的其他老师的编号和姓名
select t2.tno, t2.tname
from teacher t1
inner join teacher t2
on t2.department = t1.department
where t1.tname = '贺明明'
and t2.tname <> '贺明明';
查询与耿娇同年出生的学生学号、姓名、电话
select s2.sno, s2.sname, s2.tel
from student s1
inner join student s2 on year(s1.birth) = year(s2.birth)
where s1.sname = '耿娇'
and s2.sname <> '耿娇';
聚合函数
sum() avg() 一般只适用于数值
max() min() count() 可适用于文字 数值
select * from emp;
聚集函数统计中忽略空值 聚集函数如果在统计时需要忽略重复值,则需要在属性名前加distinct,否则默认为all,不忽视重复值
select avg(salary)
from emp;
select avg(comm_pct)
from emp;
select count(comm_pct)
from emp;
已忽略重复值
select count(distinct comm_pct)
from emp;
count(*):统计查询结果有多少行
select count(*) from emp;
组合查询
2号部门的平均工资
select dept_id,avg(salary)
from emp
where dept_id=2;
每个部门的平均工资
select dept_id,avg(salary)
from emp
group by dept_id
order by dept_id;
如果select子句的属性列表中有聚集函数,则其他属性要么出现在group by 中,要么应以聚集函数的参数形式出现。
# sql_mode:only_full_group
各个部门的各个职位的平均工资
select dept_id,title,avg(salary)
from emp
GROUP BY dept_id, title
order by dept_id;
部门平均工资高于1500的部门及其平均工资
having:用于对分组的筛选,必须跟在group by之后不能单独使用,在分组之后完成筛选
where:用于对元组的筛选,在分组之前完成筛选
命令顺序
select dept_id,avg(salary)
from emp
group by dept_id
having avg(salary)>1500;
# order by
子查询
嵌套查询 103页 查询工资最低的员工 where里面不能使用聚集函数
as newtable
子查询的派生表需要另起别名
select name
from (select * from emp where salary=(select min(salary) from emp)) as newtable;
select * from emp where salary=(select min(salary) from emp);