Oracle day3
来源:互联网 发布:旋转木马js效果带左右 编辑:程序博客网 时间:2024/04/28 11:38
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 ......));
- Oracle day3
- Oracle,day3
- day3-oracle
- Oracle day3 总结
- Oracle 学习 Day3 - Oracle 文件
- Oracle数据库学习整理day3
- day3
- DAY3
- DAY3
- day3
- day3
- day3
- day3
- Day3
- day3
- day3
- Day3
- DAY3
- fedora 13下使用代理(wget)
- Tab标签的使用【安卓入门六】
- AVD启动问题汇总【安卓入门七】
- 段和页区别
- 表现。。
- Oracle day3
- Oracle day4
- 为自己的Ogre 应用程序添加 CEGUI应用 (四部曲)
- 使用javascript实现单选及多选的向右和向左移动
- int getchar(void);
- 网站的推广方法
- Common Problems with OgreMax 导出时的注意点
- 百度对网站主建议
- Opengl GLUT工具开发 学习笔记 001