oracle3,Oracle3

上节回顾：

1、order by  子句

order by + 字段名，字段名,字段名...

order by + list列表值，list列表值,list列表值...

2、where 子句

算术运算符

SQL运算符

逻辑运算符

3、单行函数

作业内容：

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

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

Oracle day3

查询连接

等值连接

查询员工表员部门编号为41的员工姓名；

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

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

select * from s_emp where first_name = 'LaDoris';

非等值连接

查询员工表中部门编号在40到42之间的所有员工信息；

select * from s_emp where dept_id between 40 and 42;

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

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

外连接

左外连接

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_manager_id;

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;

oracle 10g

笛卡尔积

交叉连接

select s_a.last_name,s_b.last_name from s_emp s_a ,s_emp s_b;

组函数

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;

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

SELECT MIN(last_name), MAX(last_name) FROM s_emp;

Group by ........Having

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

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

select sum(salary) from s_emp group by dept_id;

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

SELECT region_id, COUNT(name)  FROM s_dept;  报错

SELECT region_id, COUNT(name)  FROM s_dept group by region_id order by region_id;

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

SELECT dept_id, title,salary, COUNT(*)  FROM s_emp   GROUP BY dept_id, title ,salary

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

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

SELECT dept_id , count(*) FROM s_emp   GROUP BY dept_id order by dept_id;

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

SELECT dept_id , count(*) FROM s_emp   GROUP BY dept_id having count(*)>2;

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

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

select title ,avg(salary) from s_emp where  title not like'%Stock%'    group by title having avg(salary) > 1000;

子查询

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

select avg(salary) from s_emp;

select * from s_emp where salary < 平均工资

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

查询一个名字叫Biri的同一个部门的员工

select dept_id from s_emp where last_name = 'Biri';

select * from s_emp where dept_id = 部门ID

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

语法结构：

SELECT select_list

FROM table

WHERE expr operator

(SELECT select_list

FROM table  where (select ......));