Oracle04

s_emp s_dept s_region salgrade

first_name 名 id 部门编号 id 地区编号 grade 工资级别

salary 月薪 name 部门名 name 地区编号 losal 级别对应的低工资

dept_id 部门编号 region_id 地区编号 hisal 级别对应的高工资

id 员工编号

manager_id 直接领导的员工编号

1.显示每个员工的first_name salary 和对应的部门名

2.显示每个员工的 first_name salary 和 salary 对应的工资级别

3.显示s_emp 表中所有的领导显示 id first_name

不能忘记 distinct

4.显示s_emp 表中所有的普通员工显示 id first_name

select m.id,m.first_name

from s_emp e,s_emp m

where e.manager_id(+) = m.id and e.manager_id is null;

5.显示每个员工的 first_name salary 和 salary 对应的工资级别超出统计范围的

员工信息也要显示

6.使用sql99 的内连接实现题目 1 7.使用sql99 的左外连接实现题目 4

select m.id,m.first_name

from s_emp m left join s_emp e

on e.manager_id = m.id where e.manager_id is null;

8.写出sql中常用的 5 个组函数 9.按照部门编号分组统计每个部门的人数

10.按照部门编号分组统计每个部门的平均工资显示平均工资大于1500的部门最终显示部门编号

和平均工资

---------------------------------------------------------------------------------

1.分组

1.1 按照部门编号分组统计每个部门的平均工资显示平均工资大于1500的部门最终显示部门编号

和平均工资

select dept_id,avg(salary) from s_emp

group by dept_id having avg(salary)>1500;

select dept_id,avg(salary) asal from s_emp

group by dept_id having avg(salary) >1500 order by asal;

sql的执行顺序:

from where group by having select order by

1.2 按照部门编号分组统计每个部门的平均工资显示部门编号平均工资还要部门名

由于部门编号可以决定部门名所以分组时在部门编号后面加部门名不会影响分组

select dept_id,avg(salary),name

from s_emp,s_dept where dept_id = s_dept.id

group by dept_id,name;

select dept_id,avg(salary),min(name) name

from s_emp,s_dept where dept_id = s_dept.id

group by dept_id;

结论:在分组语句中 select 后的字段要么是分组标准要么是经过合适的组函数处理过的

2.子查询

2.1 概念

把一个查询结果作为另一个查询的基础。

2.2 子查询出现的位置

where 之后

having 之后

from 之后

2.3 举例 where 之后

/* 查询出所有的领导编号多一个NULL值 */

select distinct manager_id from s_emp;

/* 再写一个查询让员工的编号出现在上面的列表范围内 8个领导 */

select id,first_name from s_emp where id in

((select distinct manager_id from s_emp));

/* 通过子查询找出所有的普通员工 */

select id,first_name from s_emp where id not in

((select distinct manager_id from s_emp where manager_id is not null));

2.4 having 之后

按照部门编号分组统计每个部门的平均工资显示大于41 部门平均工资的部门

最终显示部门编号和平均工资。

select dept_id,avg(salary) from s_emp

group by dept_id having avg(salary) >

(select avg(salary) from s_emp where dept_id=41);

2.5 from 之后 (分页)

任何合法查询语句都对应一张内存表 . nn

select id,first_name name ,salary from s_emp;

select id,salary from

(select id,first_name name ,salary from s_emp) where salary>1000;

3.DDL 语句

3.1 create table 建立表

语法:

create table 表名(

字段名类型,

字段名类型

);

类型: number 数字 varchar(n) 变长字符串 char(n) 定长字符串 date 日期类型

练习:

建立一张表叫 myemp 表字段有 id number fname varchar(30)

sname char(30) start_date date

create table myemp(

id number,

fname varchar(30),

sname char(30),

start_date date

);

3.2 删除表

drop table 表名;

drop table myemp;

3.3 修改表 alter table

删除表中的一个字段

alter table myemp drop column start_date;

增加表中的一个字段

alter table myemp add sdate date;

4.DML

4.1 insert 增加数据

全字段插入数据

insert into 表名 values(值1,值2,值3);

注意:字段的排列顺序和表头相同注意字段的类型如果是字符串值必须使用单引号

举例:

insert into myemp values(100,'xsy','xsy',sysdate);

commit;

select length(fname),length(sname) from myemp;

select * from myemp where fname='xsy ';

select * from myemp where sname='xsy '; 这个有数据的原因自动补空格

注意插入数据之后一定要提交

全字段插入的另外一个手段? 只要字段没有规定不能为空则可以使用 NULL

insert into myemp values(101,'zbs',NULL,sysdate);

commit;

选择部分字段插入没有选择的字段默认值是 NULL (必须包含所有的非空字段)

insert into 表名(字段名1,字段名3) values(值1,值3);

commit;

insert into myemp(id,sdate) values(110,sysdate);

commit;

练习:

给 s_emp 表插入一条数据给 id last_name 赋值成 26 'abc' 其它值为NULL

insert into s_emp values(26,'abc',NULL,NULL,NULL,NULL,

null,null,null,null,null);

commit;

insert into s_emp(id,last_name) values(27,'cba');

commit;

4.2 更新数据 update

update 表名 set 字段名1=值 where 条件;

update 表名 set 字段名1 = 值1,字段名2=值2 where 条件;

commit;

练习:

把s_emp 表中 id=20 员工把 first_name 改成 abc salary 改成 54321

update s_emp set first_name='abc',salary=54321 where id=200;

commit;

4.3 删除数据 delete

delete from 表名 where 条件;

commit;

5.TCL事务控制语句 (transaction control language)

5.1 概念

transaction 叫事务也叫交易。

事务就是多个操作要看成一个逻辑整体，这些操作必须同时成功或者同时失败。

5.2 具体的操作转账

commit; 确认提交

rollback; 回退回滚

update account set money=money-5000 where id=1;

// 假设能获取执行的结果 f

update account set money=money+5000 where id=2;

// 假设能获取执行的结果 f2

if(f && f2) {

commit;

}else{

rollback;

}

5.3 保存点 savepoint (了解)

可以做到部分成功部分失败

发信息到基站

基站记录收到信息的日志

扣钱

基站发送信息到目标手机

基站记录发送信息的日志

rollback to a;

commit;

insert into myemp values(1,'aa','aa',sysdate);

savepoint a;

insert into myemp values(2,'bb','bb',sysdate);

savepoint b;

insert into myemp values(3,'cc','cc',sysdate);

savepoint c;

insert into myemp values(4,'dd','dd',sysdate);

rollback to b;

commit;

6.日期类型

6.1 日期类型的默认表现

dd-MON-yy

select id,start_date from s_emp;

按照入职日期排序显示 id first_name start_date

select id,first_name,start_date from s_emp order by start_date;

6.2 使用 to_char 来改变显示的格式

新的日期格式:

yyyy 四位年

mm 两位月

dd 天

hh 12小时制

hh24 24小时制

mi 分钟

ss 秒

day 星期几

MON 英文月的缩写

month 英文月的全写

pm 上午显示成 am 下午显示成pm

select id,first_name,to_char(start_date,'yyyy-mm-dd hh24:mi:ss MON pm')

from s_emp order by start_date;

bbbbbb

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day pm') from dual;

6.3 日期数据的插入

sysdate 可以代表当前的时间点

过去和未来的时间如何处理?

2008-08-08 20:08:10

2090-08-08 08:10:25

dd-MON-yy 只能放入年月日时分秒默认是零

to_date(par1,par2) par1 是要处理的日期字符串 par2 是日期格式字符串

能把日期字符串根据日期格式转换成日期数据

i o myemp values(5,'test','test','08-AUG-08');

commit;

select id,to_char(sdate,'yyyy-mm-dd hh24:mi:ss') from myemp;

insert into myemp values(6,'test','test',

to_date('2008-08-08 20:08:10','yyyy-mm-dd hh24:mi:ss'));

commit;

insert into myemp values(7,'test','test',

to_date('2090-08-08 08:10:25','yyyy-mm-dd hh24:mi:ss'));

commit;

6.4 日期调整

先调整秒分钟小时天默认以天为单位