Oracle练习(1)

练习一

1、查询dept表的结构
   在命令窗口输入:
   desc dept;

2、检索dept表中的所有列信息
   select * from dept

3、检索emp表中的员工姓名、月收入及部门编号
   select ename "员工姓名",sal "月收入",empno "部门编号" from emp
   注意查询字段用分号隔开。

4、检索emp表中员工姓名、及雇佣时间
   日期数据的默认显示格式为“DD-MM-YY",如果希望使用其他显示格式(YYYY-MM-DD),那么必须使用TO_CHAR函数进行转换。
   select ename "员工姓名", hiredate "雇用时间1",to_char(hiredate,'YYYY-MM-DD') "雇用时间2" from emp
   注意:第一个时间是日期类型的,在Oracle的查询界面它的旁边带有一个日历。
         第二个时间是字符型的。
   易错点:不要将YYYY-MM-DD使用双引号

5、使用distinct去掉重复行。
   检索emp表中的部门编号及工种,并去掉重复行。
   select  distinct deptno "部门编号",job "工种"  from emp order by deptno
   注意distinct放的位置
   为什么不放在from的前面?
   翻译成汉语就明白了
   应该是:选择不重复的部门编号和工种从emp表。而不是:选择部门编号和工种不重复地从emp表。这还是人话么???O(∩_∩)O哈哈~

6、使用表达式来显示列
   检索emp表中的员工姓名及全年的月收入
   select ename "员工姓名", (sal+nvl(comm,0))*12 "全年收入" from emp
   注意:防止提成comm为空的操作,使用nvl函数

7、使用列别名
   用姓名显示员工姓名,用年收入显示全年月收入。
   select ename "员工姓名",sal*12 "全年收入" from emp

8、连接字符串
  在oracle中连接字符串用“||”操作符来完成的,当连接字符串时,如果在字符串要加入数字值
   那么在“||”后可以直接指定数字,如果在字符串加入字符和日期值,则必须要用单引号。
   检索emp表,用is a 这个字符串来连接员工姓名和工种两个字段

   select ename||' is a '||job "他们各自的职位"from emp
   注意:用的是单引号!!!

9、使用WHERE子句
   检索月收入大于2000的员工姓名及月收入。
   select ename "姓名" ,sal "月薪"from emp where sal>2000
   检索月收入在1000元到2000元的员工姓名、月收入及雇佣时间。
   select ename "姓名" ,sal "月薪",hiredate "雇佣时间" from emp where sal between 1000 and 2000

10、like的用法:
    检索以S开头的员工姓名及月收入。
    select ename "员工姓名",sal "月收入" from emp where ename like 'S%'
    检索员工姓名中的第三个字符是A的员工姓名及月收入。
    select ename "员工姓名",sal "月收入" from emp where ename like '__A%'
    注意这里A前面有两个通配符。
    且注意like后面使用的是单引号

11、在WHERE条件中使用IN操作符
    检索emp表中月收入是800的或是1250的员工姓名及部门编号
    select ename "姓名",deptno "部门编号",sal "工资" from emp where sal in(800,1250)
    注意:IN的意思是或者。是800或者1250而不是表示范围

12、在WHERE条件中使用逻辑操作符(AND、OR、NOT)
    显示在部门20中岗位CLERK的所有雇员信息
    select * from emp where deptno='20' and job='CLERK'
    显示工资高于2500或岗位为MANAGER的所有雇员信息
    select * from emp where sal>'2500' or job='MANAGER'
    注意:在where里面的条件都是使用的单引号

13、查询表中是空值的数据
    检索emp表中有提成的员工姓名、月收入及提成。
    select ename "姓名",comm "提成",sal "工资" from emp where comm is not null

14、使用ORDER BY子句,进行排序。
    检索emp表中部门编号是30的员工姓名、月收入及提成,并要求其结果按月收入升序、然后按提成降序显示。
    select ename "姓名",comm "提成",sal "工资" from emp where deptno='30' order by sal asc,comm desc

练习二

1. 查询工资大于1200的员工姓名和工资
   select ename "姓名",sal "工资" from emp where sal>1200

2. 查询员工号为7934的员工的姓名和部门号
   select ename "姓名",deptno "部门编号" from emp where empno='7934'

3. 选择工资不在5000到12000的员工的姓名和工资
   select ename "姓名",sal "工资" from emp where sal not between 5000 and 12000
   注意:不是 is not

4. 选择雇用时间在1981-02-01到1981-05-01之间的员工姓名,职位(job)和雇用时间,按从早到晚排序.
   select ename "姓名",job "职位",hiredate "雇佣时间"
   from emp where hiredate between to_date('1981-02-01','YYYY-MM-DD') and to_date('1981-05-01','YYYY-MM-DD')
   order by hiredate asc
   总结:
   1,要转换为字符类型转换为日期类型!!!否则无法比较
   2,1981-05-01要用单引号引起来!!!否则报错——日期格式不够长
   这个例子很重要!!!!!!!!!!!!!!!!!!!!!!!!

5. 选择在20或10号部门工作的员工姓名和部门号
   select ename "姓名",deptno "部门号" from emp where deptno in ('20','10')
   select ename "姓名",deptno "部门号" from emp where deptno in (20,10)
   加不加单引号都可以。那么有什么区别呢???????????????????

6. 选择在1987年雇用的员工的姓名和雇用时间
   select ename "姓名",hiredate "雇佣时间" from emp where to_char(hiredate,'YYYY')='1987'
   取出雇用时间的年份且转换为字符形式;然后与'1987'比较
   select ename "姓名",hiredate "雇佣时间" from emp where to_char(hiredate,'MM')='04'
   select ename "姓名",hiredate "雇佣时间" from emp where to_char(hiredate,'MM')='4'
   前者是可以的,后者不可以??????????????????????????????
   这个例子也很重要!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

7. 选择公司中没有管理者的员工姓名及job
   select ename "姓名",job "工作" from emp where mgr is null

8. 选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名,工资和奖金比例,按工资逆排序,奖金比例逆排序.     
   select ename "姓名",sal "工资",comm "奖金比例"from emp where comm is not null and comm !=0 order by sal desc,comm desc

9. 选择员工姓名的第三个字母是a的员工姓名
   select ename "姓名"from emp where ename='__A'
   select ename "姓名"from emp where ename like '__A%'
   总结:
   1,前者是错的,这样定死了:一共只有三个字母,且最后一个是A
   2,后者正确。表示A后的不限定
   这个例子也很重要,可以用于模糊查询!!!!!!!!!!!!!!

练习三

1. 显示系统时间(取别名为“DATE”).
   select sysdate "Date" from dual
   注意:dual是Oracle系统的一个虚拟表
  
2. 查询员工号,姓名,工资(若为NULL则作为0处理),补助(若为NULL则作为0处理)
   以及工资提高百分之20%后四舍五入到整数的结果(取别名为new salary)
   select ename "姓名",empno "员工号",nvl(sal,0) "原来的sal",round(sal*(1+0.2),2) "new salary",nvl(comm,0) "补助"from emp
   总结:
   这里用了两个很重要的函数:nvl和round()用于四舍五入——比如将原工资除以7,只保留三位小数
   select round(sal/7,3) "new sal" from emp

3. 将员工的姓名(取别名为"Name")按字母表先后顺序排序,并写出姓名的长度(取别名为"length")
   select ename "Name",length(ename) "length" from emp order by ename asc
   select ename "名字",length(ename) "名字的长度" from emp order by ename asc

4. 查询各员工的姓名,并显示出各员工在公司工作了多少个月份(起别名为"worked_month")四舍五入到整数.
   select ename "名字",round(months_between(sysdate,hiredate),0) "worked_month" from emp
   计算指定日期到雇佣日期有多少个月?
   select ename "名字",round(months_between(to_date('1990-01-01','YYYY-MM-DD'),hiredate),0) "worked_month" from emp
   计算两个日期之间有多少个月?
   select ename "名字",round(months_between(to_date('1990-01-01','YYYY-MM-DD'),to_date('1980-01-01','YYYY-MM-DD')),0) "worked_month" from emp
   注意:months_between()这个函数里的两个参数是日期类型,所以需要转型!!!!!!!!!!!!!!!!

5. 查询员工的姓名和工资,按下面的形式显示结果(工资字段必须为15位,空位用$填充)
   姓名   工资
   KING   $$$$$$$$$$24000
   MIKE   $$$$$$$$$$$4800

   select ename "名字",lpad(sal,15,'$') "工资" from emp
   注意lpad的最后一个参数,因为它是字符型的,所以用单引号

6. 查询员工的姓名,以及在公司工作满了多少个月(worked_month),并按月份数降序排列
   select ename "姓名",months_between(sysdate,hiredate) "worked_month" from emp order by  having worked_month desc(错误的)
   select ename "姓名",months_between(sysdate,hiredate) "worked_month" from emp order by "worked_month" desc(不太正确的)
   注意order by后面的双引号
   select ename "姓名",trunc(months_between(sysdate,hiredate)) "worked_month" from emp order by "worked_month" desc
   trunc()函数表示只取整数!!!!!!!!!!题目要求的是"满了多少个月",所以应该取整
   为什么可以使用worked_month????????????????????????????????????????

7. 做一个查询,按下面的形式显示结果
   <ename> earns <sal> monthly but wants <sal*3>
   Dream Salary
   KING earns $24000 monthly but wants $72000
   JONE earns $18000 monthly but wants $54000
   select upper(ename)||' earns '||to_char(sal,'$99999.99')|| ' per month but wants '||to_char(sal*3,'99999.999') "Dream Salary" from emp
   总结(这个也很常用):
   9,代表一位数字,如果当前位有数字,显示数字,否则不显示(小数部分仍然会强制显示)
   0,强制显示该位,如果当前位有数字,显示数字,否则显示0

8. 做一个查询,按下面的形式显示结果(注意两个日期之间的关系)需要设置语言环境:  ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
   ENAME HIREDATE 6个月零4天后
   KING         17-JUN-87 MONDAY,the TWENTY-FIRST of DECEMBER , 1987

   alter session set nls_language='SIMPLIFIED CHINESE'

   Select upper(ename),to_char(hiredate,'dd-MON-yy'),
   to_char(add_months(hiredate,6)+4,'DAY ",the" DDSPTH " of" MONTH "," yyyy') from emp

   ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
   感觉这道题没有意思,这是老师的答案。

9. 做一个查询,按下面的形式显示结果
   Employees_and_their_salarys
   King********
   Zhang*****
   Wang*****
   其中每一个*代表一千元(四舍五入)。按工资从多到少排序.
   select rpad(initcap(ename),length(ename)+round(sal/1000),'*') "一个*代表1000块钱" from emp order by sal desc
   注意rpad()函数的参数。第一个是要填充的对象,第二个知道了填充后的长度,第三个指明用什么填充
   此例子中第二个参数很好,有点意思!!!!!!!!!!!!!!!!!!!!!!!

10. 使用decode函数,按照下面的条件:(根据job类别来确定级别GRADE)
    job                                         grade

    AD_PRES     A
    ST_MAN     B
    IT_PROG     C
    SA_REP      D
    ST_CLERK                E
      OTHER
    产生下面的结果:
    ENAME Job_id Grade

    king AD_PRES A
    kate IT_PROG C

  
select ename "姓名",job "工作" ,sal "薪水",decode(job,
                                                  'CLERK','A',
                                                  'SALESMAN','B',
                                                  'PRESIDENT','C',
                                                  'MANAGER','D',
                                                            'E'
                                                  )等级
                                               from emp

    注意比较和下面的异同

11. 将上一题的查询用case函数再写一遍。

    select ename "姓名",job "工作" ,sal "薪水",case job
                                               when 'CLERK' then 'A'
                                               when 'SALESMAN' then 'B'
                                               when 'PRESIDENT' then 'C'
                                               when 'MANAGER' then 'D'
                                               else 'E'
                                               end "等级"
                                               from emp

  注意它的骨干是select……case(要判断的列)……when then(写在同一行,可读性好)……else end(用于起别名) from……

  //这个是可以的
   select ename "姓名",job "工作", sal "薪水",case sal
                                           when to_number(800.00) then 'A'
                                           when to_number(1600.00) then 'B'
                                           else 'C'
                                           end "工资等级"
                                           from emp

 

//怎么样按照工资排序,这是错误的?
参见day3232笔记

select ename "姓名",job "工作", sal "薪水",case sal
                                           when to_number('3000')<sal<to_number('6000') then 'A'
                                           when to_number('1000')<sal<to_number('3000') then 'B'
                                           else 'D'
                                           end "工资等级"
                                           from emp

练习四

1.查询公司员工工资的最大值,最小值,平均值,总和
  select max(sal) "最高工资",min(sal) "最低工资",round(avg(sal),2) "平均工资",sum(sal) "工资总和" from emp

2.查询各job的员工工资的最大值,最小值,平均值,总和
  select job "工种",max(sal) "最高工资",min(sal) "最低工资",round(avg(sal),2) "平均工资",sum(sal) "工资总和" from emp group by job

3.选择具有各个job 的员工人数
  select job "工种",count(*) "此工种的员工人数" from emp group by job

4.查询员工最高工资和最低工资的差距(DIFFERENCE)
  select max(sal)-min(sal) "DIFFERENCE" from emp

5.查询公司的人数,以及在80,81,82,87年,每年雇用的人数,结果类似下面的格式
  total 1980 1981 1982 1987
  14  1  10  1  2
  select count(*) "total",
  sum(decode(to_char(hiredate,'YYYY'),1980,1,0)) "1980",
  sum(decode(to_char(hiredate,'YYYY'),1981,1,0)) "1981",
  sum(decode(to_char(hiredate,'YYYY'),1982,1,0)) "1982",
  sum(decode(to_char(hiredate,'YYYY'),1987,1,0)) "1987"
  from emp

 这个例子相当相当重要!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 其中select decode(to_char(hiredate,'YYYY'),1980,1,0) from emp这个语句是核心
 如果年份是1980的将其置为1,否则为0,这样操作了所有的年份(相当于形成了一张临时表)
 然后再利用sum计算其和(即计算这个临时表的总和就知道有多少个1980了)!!!
 
 同理可以判断每个月份有多少人入职
 select
 sum(decode(to_char(hiredate,'MM'),02,1,0)) "2月入职",
 sum(decode(to_char(hiredate,'MM'),03,1,0)) "3月入职",
 sum(decode(to_char(hiredate,'MM'),04,1,0)) "4月入职",
 sum(decode(to_char(hiredate,'MM'),05,1,0)) "5月入职"
 from emp

 

时间: 2024-09-19 10:04:33

Oracle练习(1)的相关文章

用PHP执行Oracle存储过程示例

oracle|存储过程|示例|执行 <?//在oracle中执行存储过程示例//供大家参考$gConn=ocilogon("test","test"); /* OCIDefineByPos example thies@digicol.de (980219) *///discrate与nEcode是out parameter,其余均是 in parameter.//存储过程get_discrate存放于package ebiz_pg中.$stmt = OCIPa

Oracle错误代码大全

Oracle错误代码大全--最新.最全的Oracle错误代码 对快速查找oracle数据库错误原因很有帮助 ORA-00001: 违反唯一约束条件 (.) ORA-00017: 请求会话以设置跟踪事件 ORA-00018: 超出最大会话数 ORA-00019: 超出最大会话许可数 ORA-00020: 超出最大进程数 () ORA-00021: 会话附属于其它某些进程:无法转换会话 ORA-00022: 无效的会话 ID:访问被拒绝 ORA-00023: 会话引用进程私用内存:无法分离会话 OR

Oracle大文本在ASP中存取问题的解决

oracle|解决|问题 在我开发BS结构程序中,由于经常需要在ORACLE中处理一些容量较大的文本数据,所以经过反复测试终于用ASP成功解决了大文本数据在ORACLE下存取问题. 一.运行环境: 1.Microsoft Windows 2000 Server + IIS 5.0 2.Oracle8i中文标准版 二.建立数据表: CREATE TABLE SYSTEM.TEST( BLOB LONG, ID NUMBER) / 三.源程序: 1.数据存入程序:test.asp <% '表单提交处

使用 Oracle XQuery 查询、构建和转换 XML

oracle|xml|转换 在 Oracle 数据库 10g 第 2 版中,Oracle 引入了一个与该数据库集成的全功能自带 XQuery 引擎,该引擎可用于完成与开发支持 XML 的应用程序相关的各种任务.XQuery 是一种用于处理 XML 数据模型的查询语言,它实际上可操作任何类型的可用 XML 表达的数据.尽管 Oracle XQuery 实施使您可以使用数据库数据和外部数据源,但在处理数据库中存储的结构化数据方面,Oracle XML DB 通常可以显著提高性能. 本文提供的示例不仅

将 Oracle 内置的安全特性用于 PHP

当今大多数 Web 应用程序都需要至少采用某种基本的安全策略.例如,提供用口令保护的内容的网站.仅具有管理员后端的网站.网志和个人杂志.电子商务网站.企业内联网,等等. 构建这些类型的 Web 应用程序最常用的设计方法是将安全策略整合到 Web 应用程序的业务逻辑中,即由应用程序决定某个用户是否有权访问数据库中的某个数据.在这种情形下,数据库的角色仅为存储数据和依请求提供数据.换句话说,如果 Web 应用程序命令数据库提供特定信息,则数据库会直接执行该命令而不检查用户的权限. 在该文中,您将学习

将Oracle内置的安全特性用于php

当今大多数 Web 应用程序都需要至少采用某种基本的安全策略.例如,提供用口令保护的内容的网站.仅具有管理员后端的网站.网志和个人杂志.电子商务网站.企业内联网,等等. 构建这些类型的 Web 应用程序最常用的设计方法是将安全策略整合到 Web 应用程序的业务逻辑中,即由应用程序决定某个用户是否有权访问数据库中的某个数据.在这种情形下,数据库的角色仅为存储数据和依请求提供数据.换句话说,如果 Web 应用程序命令数据库提供特定信息,则数据库会直接执行该命令而不检查用户的权限. 在该文中,您将学习

将Oracle 10g内置的安全特性用于PHP

当今大多数 Web 应用程序都需要至少采用某种基本的安全策略.例如,提供用口令保护的内容的网站.仅具有管理员后端的网站.网志和个人杂志.电子商务网站.企业内联网,等等. 构建这些类型的 Web 应用程序最常用的设计方法是将安全策略整合到 Web 应用程序的业务逻辑中,即由应用程序决定某个用户是否有权访问数据库中的某个数据.在这种情形下,数据库的角色仅为存储数据和依请求提供数据.换句话说,如果 Web 应用程序命令数据库提供特定信息,则数据库会直接执行该命令而不检查用户的权限. 在该文中,您将学习

PHP 5 数据对象 (PDO) 抽象层与 Oracle

oracle|对象|数据 一名新 PHP 数据对象 (PDO) 数据抽象层的原始开发人员为您简要介绍该抽象层,重点讲述与 Oracle 一起运行的情况. 需要 PHP:5.0 需要其他:Oracle 8 或更高版本客户端库 下载用于 Oracle 的 PDO (Windows):php_pdo.dll, php_pdo_oci.dll 下载用于 Oracle 的 PDO (Unix):pdo, pdo_oci PDO 简介 PHP 主要是由志愿者完成的项目:尽管有少数一些固定的"核心"

用php+oracle实现分页的示例程序

oracle|程序|分页|示例 <html><body><?include "/maya/inc/dbconn.php";$sql="select max(rownum) from xqhtest where id<50";$stmt=ociparse($gConn,$sql);ociexecute($stmt);ocifetch($stmt);$rowcount=ociresult($stmt,1);ocifreestateme

使用Oracle Text构建全文搜索应用程序

Oracle Text 是一种功能强大的搜索技术,它内置于 Oracle 数据库的所有版本(包括免费提供的快捷版 (XE))中.它所提供的开发 API 使软件开发人员能够轻松实现功能齐备的内容搜索应用程序. Oracle Text 可用于搜索结构化和非结构化文档,是对 SQL 通配符匹配的补充.Oracle Text 支持使用基本的布尔运算符(AND.OR.NOT.NEAR 等)将多个搜索条目组合到一起,此外,它还具有更高级的功能,如 soundex 和模糊搜索,以及结果排序等.该技术支持数百种