社区 › 盖世程序员 › Oracle增删查改 day02

万蕊科技 发表于 2014-6-2 19:40

Oracle增删查改 day02

SQL*Plus   
sqlplus.exe

1)数据定义语言：create / drop / alter / truncate
对数据结构起作用。
********
Data Difinition Language: DDL
2)数据操纵语言：insert / update / delete
对数据起作用
****
Data Manipulation Language: DML
3)数据查询语言：select
      ****
Data Query Language: DQL
4)事务控制语句：commit / rollback
对DML操作确认

一、学习单表查询语句。
1.计算金额的四舍五入？
select ename, salary * 0.1234567 s1,
round(salary * 0.1234567, 2) s2,
round(salary * 0.1234567) s3,
trunc(salary * 0.1234567, 2) s4
from emp_ning;

round(数字, 小数点后的位数):四舍五入
如果没有第二个参数，默认是0.

trunc(数字，小数点后的位数）：截取
如果没有第二个参数，默认是0.

2.Oracle中的日期
1)取系统时间的函数：sysdate
select sysdate from dual;

2)把时间数据按指定格式输出
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
from dual;

select to_char(sysdate, 'year month dd day dy')
from dual;

to_char(日期数据，格式)

public String to_char(Date d, String style){
        //SimpleDateFormat把d按照style的格式转变为字符串
        return 字符串;
}

select to_char(sysdate, 'yyyy/mm/dd')
from dual;

yyyy: 四位数字年2011
year：全拼的年 twenty eleven
month: 全拼的月 november中文：11月
mm:两位数字月 11
mon：简拼的月 nov
dd：两位数字日
day：全拼的星期 tuesday
dy: 简拼的星期 tue
am: 上午/下午 am/pm

sqlplus中日期的默认格式是：DD-MON-RR
现在的时间是2011年
         YY         RR
05年      2005         2005
98年      2098         1998

假设现在的时间是1998年
05年      1905         2005
95年      1995         1995

insert into emp_ning(empno, ename, hiredate)
values(1012, 'amy', sysdate);
--实际入职时间是2011-10-10
insert into emp_ning(empno, ename, hiredate)
values(1012, 'amy', '10-OCT-11');

insert into emp_ning(empno, ename, hiredate)
values(1012, 'amy',
to_date('2011-10-10','yyyy-mm-dd'));

显示员工姓名和入职时间，显示格式为：
amy   2011-10-10

         to_date
字符串   --------->   日期
         <---------
         to_char

3.计算员工入职多少天?
select ename, hiredate, (sysdate - hiredate) days
from emp;

日期数据相减，得到两个日期之间的天数差。
不足一天用小数表示。

select ename, hiredate,
round(sysdate - hiredate) days
from emp;

4.计算员工入职多少个月？用整数表示。
select ename, hiredate,
months_between(sysdate, hiredate) months
from emp_ning;

select ename, hiredate,
round(months_between(sysdate, hiredate)) months
from emp_ning;

f3(f2(f1(p1,p2),p3),p4) : 函数的嵌套

5.计算员工的年终奖金
bonus不是null，发bonus的数字。
bonus是null，发salary * 0.5.
salary还是null, 100.

public double calculate(double bonus,
                        double salary,
                        double comm){
        if (bonus != null) return bonus;
        if (salary != null) return salary * 0.5;
        return comm;                        
}
--返回参数列表中第一个非空数据
--最后一个参数通常是常量。
select ename, bonus, salary,
coalesce(bonus, salary*0.5, 100) bonus
from emp_ning;

6.根据员工的职位，计算加薪后的薪水数据：
Analyst: 10%
Programmer: 5%
clerk: 2%
其他职位：不变。

case语句:
select ename, salary, job,
case job when 'Analyst' then salary * 1.1
         when 'Programmer' then salary * 1.05
         when 'clerk' then salary * 1.02
else salary
end new_salary
from emp_ning;

select ename, salary, job,
decode(job, 'Analyst', salary * 1.1,
            'Programmer', salary * 1.05,
            'clerk', salary * 1.02,
            salary) new_salary
from emp_ning;

7.薪水由低到高排序
select ename, salary from emp_ning
order by salary;

select ename, salary from emp_ning
order by salary desc;--倒序排列 descend

desc emp_ning; --查看表结构 describe

8.按入职时间排序，入职时间越早排在前面。
select ename, hiredate
from emp_ning
order by hiredate;

9.按部门排序，同一部门按薪水由高到低排序
select ename, deptno, salary
from emp_ning
order by deptno, salary desc;

10.员工表中有多少条记录？
select count(*) from emp_ning;

openlab帐户下有多少个表？
select count(*) from user_tables;

openlab帐户下有多少个名字中包含emp的表？
select count(*) from user_tables
where table_name like '%emp%';

--表名默认大写
select count(*) from user_tables
where table_name like '%EMP%';

--入职时间不是null的数据总数。
--count函数忽略空值。
select count(hiredate) from emp_ning;

11.计算员工的平均薪水和薪水总和是多少？
select avg(salary) avg_sal,
       sum(salary) sum_sal
from emp_ning;

avg(salary) = sum(salary) / count(salary)
            68500 / 10 = 6850
--纠正逻辑上的错误
select sum(salary)/count(*) avg_sal,
       sum(salary) sum_sal
from emp_ning;

select avg(nvl(salary,0)) avg_sal,
       sum(salary) sum_sal
from emp_ning;

12.计算员工的最高薪水和最低薪水
select max(salary) max_sal,
       min(salary) min_sal
from emp_ning;

组函数：count / avg / sum / max / min 忽略空值
其中：avg / sum 针对数字的操作。
      max / min 对所有数据类型都可以操作。

13.计算最早和最晚的员工入职时间。
select max(hiredate) max_hiredate,
       min(hiredate) min_hiredate
from emp_ning;

14.按部门计算每个部门的最高和最低薪水分别是多少？
格式如下：
10 4500   3000
20 150008000
30 100005000

--group by 列名 ： 表示按哪个列分组
select deptno, max(salary) max_s, min(salary) min_s
from emp_ning
group by deptno;

15.计算每个部门的薪水总和和平均薪水？
select deptno, sum(salary) sum_s,
               avg(nvl(salary,0)) avg_s
from emp_ning
group by deptno;

16.每个部门的统计信息：格式如下：
deptno max_s min_s sum_s avg_s emp_num
10   10000 5000230006789       3
....
select deptno, max(salary) max_s,
               min(salary) min_s,
               sum(salary) sum_s,
               avg(nvl(salary,0)) avg_s,
               count(*) emp_num
from emp_ning
group by deptno;

select后出现的列，凡是没有被组函数处理的列，必须
出现在group by 短语中。

按职位分组，每个职位的最高、最低薪水和人数？
select job, max(salary) max_s,
            min(salary) min_s,
            count(*) emp_num
from emp_ning
group by job
order by emp_num;
17.平均薪水大于5000元的部门数据？
select deptno, avg(nvl(salary,0)) avg_s
from emp_ning
where deptno is not null
group by deptno
having avg(nvl(salary,0)) > 5000;

18.薪水总和大于20000元的部门数据？
select deptno, sum(salary) sum_s
from emp_ning
where deptno is not null
group by deptno
having sum(salary) > 20000;

19.哪些职位的人数超过2个人？
select job, count(*) emp_num
from emp_ning
where job is not null
group by job
having count(*) > 2
order by emp_num;

查看完整版本: Oracle增删查改 day02