(day3)数据表的连接

1、对员工表中日期值进行排序。

select *from s_emp;
select to_char(start_date,'yyyy-mm-dd' )from s_emp order by start_date desc;

等值连接
2.查询员工表员部门编号为41的员工姓名；

select first_name ,dept_id from s_emp where dept_id=41;
select first_name || last_name "姓名",dept_id from s_emp where dept_id=41;

3.查询员工表员姓名为LaDorisNgao的员工信息；

select * from s_emp where first_name = 'LaDoris';

非等值连接
4.查询员工表中部门编号在40到42之间的所有员工信息；

select * from s_emp where dept_id between 40 and 42;

5.查询员工表中员工编号为1,5,9,23的员信息;

select * from s_emp where id in(1,5,9,23);

6.外连接
左外连接
select s_a.last_name "A NAME",s_b.last_name "B NAME" from s_emp s_a ,s_emp s_b where s_a.id= s_b.manager_id(+);
select s_a.last_name,s_b.last_name from s_emp s_a left outer join s_emp s_b on s_a.id = s_b.manager_id;

右外连接
select s_a.last_name,s_b.last_name from s_emp s_a ,s_emp s_b where s_a.id(+)= s_b.manager_id;
select s_a.last_name,s_b.last_name from s_emp s_a right outer join s_emp s_b on s_a.id=s_b.manager_id;

7.from 第一表(A)，第二表(B)
A B

左：B===》A======》只要跟A表内容相匹配，A表内容都显示，B表内容补空
右：A===》B ======》只要跟B表内容相匹配，B表内容都显示, A表内容补空

自连接(A表和B表代表同一张表)

select s_a.last_name,s_b.last_name from s_emp s_a ,s_emp s_b where s_a.id=s_b.manager_id;

8.组函数
AVG (DISTINCT|ALL|n)平均值
COUNT (DISTINCT|ALL|expr|*)统计个数
MAX (DISTINCT|ALL|expr)最大值
MIN (DISTINCT|ALL|expr)最小值
SUM (DISTINCT|ALL|n)求和

select avg(salary),count(salary),max(salary),min(salary),sum(salary) from s_emp;

9.查询员工表中姓名的最大值和最小值

select max(last_name),min(last_name) from s_emp;
select last_name from s_emp order by last_name desc;

10.Group by ........Having
SELECT字段名 FROM 表名[ where 条件 GROUP BY 分组条件 having(过滤条件) ORDER BY 字段名]

11.按部门编号分组统计员工表中工资；

select * from s_emp;
select dept_id , sum(salary) from s_emp group by dept_id;

12.查询部门表中对部门名字进行统计

select * from s_dept;
select region_id,count(name) from s_dept;//有误
select region_id, count(name) from s_dept group by region_id order by region_id ;

13.查询员工表中按部门编号分组在按标题分组；

SELECTdept_id, title,salary, COUNT(*) FROMs_emp GROUP BY dept_id,title,salary;

14.group by 字段名,字段名,字段名.....

查询员工表中对部门编号做统计

select *from s_emp;
select dept_id ,count(dept_id) from s_emp group by dept_id;
SELECTdept_id , count(*) FROMs_emp GROUP BY dept_id order by dept_id;

15.查询员工表中部门编号统计大于2的记录

select dept_id ,count(dept_id) from s_emp group by dept_id having count(dept_id)>2;

16.查询员工表中标题数量大于5的记录

select title , 12*avg(salary), count(*) from s_emp group by title having count(*)>5;
select title ,count(title) from s_emp group by title having count(title)>5;

17.子查询

查出所有员工低于平均工资的记录

select avg(salary) from s_emp;
select * from s_emp where salary<(select avg(salary) from s_emp);

18.查询一个名字叫Biri的同一个部门的员工
select dept_id from s_emp where last_name='Biri';//查询名叫Biri的员工所在的部门

select * from s_emp where dept_id=(select dept_id from s_emp where last_name='Biri');