QQ账号登录	立即注册>>

您现在的位置：论坛 › 盖世程序员(我猜到了开头却没有猜到结局) › 盖世程序员 > Oracle增删查改 day02

总共48087条微博

动态微博

发表新帖

查看: 1718|回复: 0

Oracle增删查改 day02

45 主题	5 听众	119 金钱

三袋弟子

该用户从未签到

电梯直达

楼主

发表于 2014-06-02 19:40:52 |只看该作者 |倒序浏览

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 15000  8000
30 10000  5000

--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 5000  23000  6789    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;

科帮网 1、本主题所有言论和图片纯属会员个人意见，与本社区立场无关
2、本站所有主题由该帖子作者发表，该帖子作者与科帮网享有帖子相关版权
3、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者和科帮网的同意
4、帖子作者须承担一切因本文发表而直接或间接导致的民事或刑事法律责任
5、本帖部分内容转载自其它媒体，但并不代表本站赞同其观点和对其真实性负责
6、如本帖侵犯到任何版权问题，请立即告知本站，本站将及时予与删除并致以最深的歉意
7、科帮网管理员和版主有权不事先通知发贴者而删除本文