02.16,連線查詢
注意:分組查詢時,select後的欄位必須包含在聚合函式 或 group by子句中
分組-->過濾-->排序:group by-->havin
g-
->order by
查詢所有員工的平均工資;select avg(sar) avg_sar from emp
連線查詢,等值連線,【group by】分組
查詢每個部門的平均工資;
select did,avg(sar) avg_sar from emp group by did
select did,ename,avg(sar) avg_sar from emp group by did
查詢每個部門的工資總和;select did,sum(sar) sum_sar from emp group by did
查詢每個性別的平均工資;select sex,avg(sar) avg_sar from emp group by sex
查詢每個部門的平均工資,只顯示平均工資大於7000的資料;【having】
select did,avg(sar) avg_sar from emp group by did having avg_sar>7000
查詢每個部門的平均工資,只顯示平均工資大於7000的資料,並且按平均工資排序;
select did,avg(sar) avg_sar from emp group by did having avg_sar>5000 order by avg_sar desc
二,子查詢:
查詢研發部所有的員工
select * from emp where did in(
select did from dept where dname=‘研發部’
)
2、 查詢研發部和市場部所有的員工
select * from emp where did in(
select did from dept where dname= ‘研發部’ or ‘市場部’
)
3、查詢在南京和北京工作過的員工
select * from emp where eid in (
select eid from hist where ress= ‘南京’ or ress=(北京)
)
4、 查詢和輕道永在同一個城市:工作過的員工
select * from emp where eid in(
select eid from hist where ress in(select ress from hist where eid in(
select eid from emp where ename=輕道永‘
)
)
)
1,查詢員工的姓名、賬號、密碼
select e。ename,a。nam,a,pwd from emp e,acc a where e。eid=a。eid
2,查詢員工的資訊及賬號、密碼
select e。* ,a。nam,a。pwd from emp e,acc a where e。eid=a。eid
3,查詢員工的姓名、賬號、密碼以及部門名稱
select e。ename,a。nam,a。pwd,d。dname from emp e,acc a, dept d where e。eid=a。eid and e。did=d。did
二,左連線、右連線、內連線【left join左連線 right join右連線 inner join內連線也可以寫join】
注意:左連線以左表為基準,左表全部展示,右邊符合條件的才會展示
右連線以右表為基準,右表全部展示,右邊符合條件的才會展示
內連線展示符合條件的
1,查詢有員工的部門
select distinct d。dname from emp e inner join dept d on e。did=d。didselect * from emp e,dept d where e。did=d。did
2,查詢員工的部門名稱,沒有部門的也要顯示
select distinct d。dname from emp e left join acc a on e。did=a。did
3,查詢有賬號的員工資訊
select * from emp e,acc a where e。eid=a。eid
4,查詢員工及賬號資訊,沒有賬號的員工也要展示
select e。*,a。nam from acc a right join emp e on e。eid=a。eid