oracle 基础语法（二）

一、实现分页

说明以下tablename是同一表。这些操作是对同一表(tablename)的操作
=======================================
如何实现分页提取记录
方法1：oracle的ROWNUM伪列返回查询的行序号。
        例如要查询表的前10条记录，可以使用
        select * from tablename where ROWNUM<=10
        但是要返回第11-第20条记录，尝试以下的语句
        select * from tablename where ROWNUM<=20 and ROWNUM>=11;
        这个人报错。返回0条记录。因为ROWNUM是伪列，不能用>=条件
        使用以下方法可以查询第11-第20条记录
        select * from
        (select ROWNUM rn ,t.* from tablename t where ROWNUM<=20) where rn>=11;
方法2：使用分析函数ROW_NUMBER实现分页
        select * from (select ROW_NUMBER() OVER (ORDER BY id) rn,t.* from tablename t) 
                where rn between 11 and 20;
方法3：使用集合运算MINUS实现分页
        select * from tablename where ROWNUM<=20 MINUS
        select * from tablename where ROWNUM<11;
点评：方法1在查找前几页时速度很快。但在数据量很大时，最后几页速度比较慢。
方法2查询效率比较稳定，是推荐使用的方法。
方法3只适合查询结果在200行以内的情况，记录数很多时会导致oracle错误，需谨慎使用。
==================================
删除重复的行
在做本练习之前，最好先在ID列上创建索引，否则执行查询等待的时间将会很长。
使用以下的查询语句可以找到ID重复的记录。
select id,count(*) from tablename group by id having count(*)>1;
或
select id from tablename group by id having count(*)>1;
删除重复记录的方法至少有以下3种。
方法1：
        delete from tablename where id IN (select id from tablename group by id having count(*)>1)
            and ROWID NOT IN (select MIN(ROWID) from tablename group id having count(*)>1);
方法2：
        delete from tablename where ROWID IN 
                    (select a.ROWID from tablename a,tablename b wehre a.id=b.id and a.ROWID>b.ROWID);
方法3：
        delete from tablename t where t.rowid>
                (select MIN(x.rowid) from tablename x where t.id=x.id);
点评：方法1是最直观的方法，但是效率不高，因为使用了not in ，会增加一次全表描。
     方法2的效率是3种方法中比较高的。
     方法3在没有索引的情况下使用效率很低，建立索引之后可提高好几倍。
======================================
有关查询优化
在oracle的scott示例用户模式的emp表和dept表为例
第一条查询语句：select * from dept where deptno IN (select deptno from emp);
第二条查询语句：select * from dept d where exists 
                                    (select e.deptno from emp e where e.deptno=d.deptno);
使用exists（第二条语句）效率更高。当emp表记录非常多时，二者的差异非常明显。
相对而言，子查询的开销是大的。

 二、语法 

-- 临时修改日期语言, 以识别英文格式的月份
alter session set nls_date_language = 'AMERICAN';
-- 修改日期语言为简体中文
alter session set nls_date_language = 'SIMPLIFIED CHINESE';
--设置默认的日期格式
alter session set nls_date_format='YYYY-MM-DD HH24:MI:ss';
---日期TO_CHAR()
MM：本年中的2位的月份
MONTH：月份全部大写
MON：月份的前3个字母
WW:本年中的第几周，2位数字
W：本月中第几周，1位数字
DDD：本年中的第几天，3位数字
DD：本月中的第几天，2位数字
D：本周中的第几天，1位数字
DAY：周几的全名，全部大写
Day：周几的全名，首字母大写
select to_date(SYSDATE,'YYYY_MM_DD HH24:MI:SS') from dual;
select to_char(to_date('2006-04-5','YYYY-MM-DD'),'WW') from dual;
日期函数months_between(x,y);x-y=如果为负数:表示x的日期比Y日期早.
-----------------------------------
   create  table  sales (xm varchar2(10), dTime date, count number, totalmoney number,city varchar2(10))
    
    insert into sales values('张三',to_date('2003-01-01','yyyy-mm-dd'),1200,30000,'南昌');
    insert into sales values('张三',to_date('2004-01-01','yyyy-mm-dd'),1200,30000,'南昌');
    insert into sales values('张三',to_date('2003-01-01','yyyy-mm-dd'),1000,30000,'北京');
    insert into sales values('张三',to_date('2004-01-01','yyyy-mm-dd'),2333,40000,'北京');
     
    insert into sales values('李四',to_date('2003-01-01','yyyy-mm-dd'),800,24567,'南昌');
    insert into sales values('李四',to_date('2004-01-01','yyyy-mm-dd'),600,15000,'南昌');
    insert into sales values('李四',to_date('2003-01-01','yyyy-mm-dd'),400,20000,'北京');
    insert into sales values('李四',to_date('2004-01-01','yyyy-mm-dd'),1000,18000,'北京');
    
   select xm,sum(count) 数量,city from sales group by rollup(city,xm) ;
    
    --group分组语句 rollup函数形成类似交叉表
   select xm,sum(count) 数量,sum(totalmoney) 金额 ,city from sales group by xm ,count ,totalmoney,rollup(city)  having count>2000 order by xm ,count ,totalmoney,city
    
    --cube函数形成类似交叉表
    select xm,sum(count) 数量,city from sales group by cube(city,xm) ;
----------------------------------------------    

--scott用户给system用户授权。它们都是有accp数据库下
grant all on emp to system;
--这是收权
revoke all on emp from system;

--这system用户创建同义词
create synonym emp for scott.emp;
select * from emp;
--创建公有同义词
create public synonym emp_public for scott.emp;
select * from emp_public;
--这是查询字典视图user_synonyms来查看用户所创建的同义词的详细信息
select * from User_Synonyms;

--这是查询字典视图user_sequences来查看用户所创建的序列的详细信息
select * from user_secondary_objects;

--创建序列
create sequence abc
    start with 10    --指定要生成的第一个序列号，
    increment by 2    --步长
    maxvalue[nomaxvalue] 2000 --最大值
    minvalue[nominvalue]
    cycle --是否环。
    nocycle--默认为不环

--创建表employee的副本，但不包含表中的记录
create table employee_copy as select * from employee where 1=2;
--把employee表中符合条件的行插入到副本(employee_copy)中
insert into employee_copy select * from employee where deptcode='dp02';
--建表
create table salary_records
(
 empcode varchar2(10),
 working_Days number(5),
 empsal number(10)
);       
--这是增加表的字段列
alter table myfirst add(title varchar2(20));
--这是删除表的字段列
alter table myfirst drop column title;
--这是修改现有表的字段列的定义如：类型
alter table myfirst modify(age int);
--ASC这是升序,distinct无重复行出现
select * from myfirst where age>20 order by age DESC;

--集合操作(1具有相同的列，并且数据类型相同2不该有LONG类型3列标题来自第一个select语句)
select vencode from vendor_master 
minus(减集-不相同的行数),intersect(交集-相同的行数)
select vencode from order_master;

--这是插入建表时日期字段默认值为'sysdate',在插入时要写'sysdate'
insert into datetest values(dt1.nextval,sysdate,'jon');

-----------------
sqlplus /nolog
connect system/accp as sysdba
startup mount
alter database open;
--关闭数据库
shutdown --当用户都断开后，才断开连接关闭数据库
shutdown immediate--当用户执行完sql语句后断开连接关闭数据库

 三、查询语句 

 

=======================子查询(不能包含order by子句)
--1.in :可以用来检查在一个值列表中是否包含指定的值。这个值列表也可以来自一个子查询的返回结果。
--2.any ：用来将一个值与一个列表中的"任何值"进行比较。在查询中any操作之前，必须使用一个=,>,>=等比较操作符。
select id from userinfo where uid= any(select id from product);
--3.all ：用来将一个值与一个列表中的"所有值"进行比较。在查询中all操作之前，必须使用一个=,>,>=等比较操作符。
select id from userinfo where uid= all(select id from product);
--4.Exists ：用于检查子查询所返回的行的存在性。用在select语句中。
由于Exists只检查子查询返回的行的存在性，因此查询不必返回一列，可以返回一个学量值(1).
  示例:用not exists检索从来未曾被购买过的产品
    select product_id,name from product outer
      where not exists (select 1 from purchases inner
     where inner.product_id=outer.product_id);
--5.比较Exists和In之间的区别
Exists与In不同:Exists只检查行的存在性,而In则要检查实际值的存在性.
通常来讲,Exists比In的性能要高,应尽可能使用Exists.
Not Exists与Not In的查询区别:
当一个值列表包含一个空值时,not exists就返回一个true,而not in则返回false;
 示例:not exists
   select product_type_id,name from product_type_id outer
     where not exists (select 1 from products inner
       where inner.product_type_id=outer.product_type_id)
  这个查询是有结果的.正常情况下,products产品表中不定有所有的产品类型的产品.所以这个本应是无结果.
  示例:not in
    select product_type_id,name from product_type_id 
     where product_type_id not in (select produt_type_id from     products )

========================分析函数
first()和last()
示例:查询2004年中销量最高和最低的月份
select 
 min(month) keep(dense_rank first order by sum(amount)) as     highest_sales_month,
 min(month) keep(dense_rank last order by sum(amount)) as
        lowest_sales_month
 from all_sales
 where year=2003
 group by month
 order by month;
==================查询闪回
如果错误地提交了"修改"(只是修改)操作，并想看修改的原来的值，可以使用查询闪回。然后如果需要，就可以使用查询闪回的结果将这些行手工地修改回原来的值。
----使用闪回的授权
grant execute on sys.dbms_flashback to store;
----禁用闪回操作
 execute dbms_flashback.disable();
------时间闪回
 执行dbms_flashback.enable_at_time(时间参数)过程。
24*60=1440分钟
execute dbms_flashback.enable_at_time(sysdate-10/1440);
现在再执行任何查询都将显示10分钟之前的状态。
execute dbms_flashback.disable();--禁用闪回
这时禁用闪回操作,如果再执行查询,检索结果就是当前的状态
-------系统变更号(SCN)查询闪回
这种操作比时间闪回更精确。因为数据库就是使用SCN来跟踪数据库的变化的。
--1.获得当前的SCN.
dbms_flashback.get_system_change_number();
 例如:
variable current_scn number 
 execute :current_scn:=dbms_flashback.get_system_change_number();
print current_scn
--2.运行SCN
  示例:
  insert into userinfo values('2001','abc');
  commit;
  select * from userinfo where id='2001';
execute dbms_flashback.enable_at_system_change_number(:current_scn);
现在再执行任何查询都将显示insert语句之前的状态。
execute dbms_flashback.disable();--禁用闪回
这时禁用闪回操作,如果再执行查询,检索结果就是当前的状态
---在再次启用闪回操作之前,必须先将其禁用.
===============在select中使用case
select case 
             when name='aa' then 'book'
             when name='bb' then 'video'
             else 'cd'
             end
 from idtest;
======================使用Exception(自定义)
SET SERVEROUTPUT ON 
DECLARE 
  invalidCATEGORY EXCEPTION;
  category varchar2(10);
BEGIN
  category := '&Category';
  IF category NOT IN ('附件','顶盖','备件') THEN
    RAISE invalidCATEGORY;
  ELSE
    DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);
  END IF;
EXCEPTION
  WHEN invalidCATEGORY THEN
    DBMS_OUTPUT.PUT_LINE('无法识别该类别');
END;
=============================
数据字典视图，USER_SOURCE 包含存储过程的代码文本。

四、游标异常动态执行SQL语句 

----------------动态SQL
declare
 sql_stmt varchar2(200);
 emp_id number(4) :=7563;
 emp_rec emp%rowtype;
begin
 exceute immediate
     'create table bonusl(id number,amt number)';
--:id相当于一个参数变量。就像是java中的？
 sql_stmt:='select * from emp where empno=:id';
 exceute immediate 
       sql_stmt into emp_rec USING emp_id;
end;
------------------异常
----内置异常
 declare
 ordernum varchar2(10);
  begin
   select orderno into ordernum from order_master;
--orthers可以捕获所有错误异常。本例中的例异常是一个变量接受多行。
   exception
    when orthers then
    dbms_output.put_line('返回多行');
  end;
----用户自定异常
 declare
   invalid EXCEPTION;--定义自己定义的异常
   category varchar2(10);
 begin
   category :='&Category';--会弹出输入对话框。
   if cartegory not in ('附件','顶盖','备件') then
       RAISE invalid;---抛出自定义异常
   else
       dbms_output.put_line('你输入的类别是:'||category);
   end if;
  exception
    when invalid then
       dbms_output.put_line('无法识别该类别');
 end;
=========================================================
----------------------游标
------隐式游标
影响一行。 oracle为隐式游标的名为SQL。如insert,update,delete,select都是隐式游标。有四个属性
SQL%found :只有在DML(数据操纵语句)语句影响一行或多行时,返回true.
SQL%notfound :与上一个属性正好相反.如果DML没有影响任何行,则返回true.
SQL%rowcount :返回DML语句影响的行数.如果没有影响任何行.则返回0.
SQL%isopen：是否打开.默认是false. oracle会自动的打开或关闭隐式游标.
begin
  update order_master set ordername='rose' where orderno='123';
  if SQL%found then
    dbms_output.put_line('表已更新');
    dbms_output.put_line('影响的行数: '||SQL%rowcount);
  elsif SQL%notfound then
    dbms_output.put_line('没有更新,编号未找到');
  elsif SQL%rowcount=0 then
     dbms_output.put_line('没有影响任何行');
  end if;
end;
--------显式游标
   影响多行,可以查询返回的行集可以包含0行或多行.这些行称为活动集.游标将指向活动集中的当前行.
  open:打开游标
  fetch:从游标提取行
  close:关闭游标.
也有四个属性
显式游标名%found :如果执行最后一条fetch语句成功返回行,则%found返回为true.
显式游标名%notfound :如果执行最后一条fetch语句未能提取行时,则返回false.
显式游标名%isopen :如果游标已经打开,则返回true.
显式游标名%rowcount :返回到目前为止游标提取的行数.当第一次获取之前,%rowcount为0.当fetch语句返回一行时,则该数加1.
   declare
     myprice toys.toyprice%type;
     COUSOR toy_cur IS  --定义显工游标
       select toyprice from toys where toyprice<250;
   begin
     open toy_cur;  --打开显式游标.
     Loop
      fetch toy_cur into myprice;
      exit when toy_cur%rowcount;
      dbms_output.put_line(toy_cur%rowcount || '玩具单价:'||myprice);
    end loop;
    close toy_cur;--关闭显式游标.
   end;
-------带参数的显式游标
    declare
     dept_code emp.deptno%type;
     emp_code emp.empno%type;
     emp_name emp.ename%tepe;
  --定义游标(有参数)
     CURSOR emp_cur (detparam number) IS 
       select empno,ename from emp where deptno=deptparm(参数);
   begin
     dept_code :='&部门编号';
     open emp_cur(dept_code);--打开游标(同定义时的一样有参数)
     loop
       fetch emp_cur into emp_code,emp_name;--游标定义的查询只查询两个字段
       exit when emp_cur%notfound;
       dbms_output.put_line('在游标中提取的行数:'||toy_cur%rowcount ||emp_code ||emp_name);
     end loop;
    close emp_cur;
   end;
------循环游标可以简化显式游标,并且自动打开或关闭游标
循环游标自动创建%rowtype类型的变量并将此变量用作记录索引.语法如下:
FOR record_inder IN cursor_name
loop
  主体
end loop;
其中record_index是PL/SQL声明的记录变量.此变量的属性声明为%rowtype类型.
作用域在FOR循环之内,即在FOR循环之外不能访问此变量.
特性:
  在从游标中提取了所有记录后自动终止.提取和处理游标中的每一条记录.
  如果在提取记录之后%notfound属性返回true.则终止循环.如果未返回行,则不进入循环.
   declare
     cursor mytoy_cur IS
           select toyid,toyname,toyprice from toys;
   begin
     FOR toy_rec in mytoy_cur;--把游标赋给一个变量.这个变量称为游标变量.
     Loop
        dbms_output.put_line(
        '玩具编号:'||toy_rec.toyid || ' '
        '玩具名称:'||toy_rec.toyname || ' '
          '玩具单价:'||toy_rec.toyprice || ' ');
        --从游标中获取相应查询的三个字段
     end loop;
   end;
-----------带参数的循环游标
 语法如下:  FOR record_index IN cursor_name(parameters)
            LOOP
             主体....
            end LOOP;
------------------------REF
---------REF游标(动态游标)(隐式游标和显式游标都是静态游标)
带有返回语句的表示是强类型REF游标.不带有返回则是弱类型REF游标.
REF游标语法如下:
--声明REF CURSOR语法
Type ref_corsor_name IS REF CURSOR
[return record_type]
--打开游标语法
OPEN cursor_name FOR select_statement
----示例1
set serveroutput on
accept tabl prompt '你想查看什么信息?员工信息(E)或部门信息(D) :';
  declare
    TYPE refcur_t IS REF CURSOR; --声明REF游标
    refcur refcur_t; --这是把REF游标赋给一个游标变量吗?(声明游标类型变量)
    p_id number;
    p_name varchar2(100);
            --这不是查询,而是赋给变量
    selection varcahr2(1) :=upper(substr('&tab',1,1));
  begin
    if selection='E' then
       OPEN refcur FOR --打开游标,注意没有;号(分员)
       select empno id,ename name from emp;
        dbms_output.put_line( '==========员工信息:======');
    elsif selection='D' then
       OPEN refcur FOR --打开游标,注意没有;号(分员)
       select deptno id dname name from dept;
        dbms_output.put_line( '==========部门信息:======');
    else
       dbms_output.put_line( '请输入员工信息(E)或部门信息(D)');
       return;
    end if;
  ---前面相当于定义REF游标的内容,下面fetch相当于读取了,如同显式游标的做法
   FETCH refucr into p_id,p_name;
   while refcur%found 
    loop
      dbms_output.put_line('#'||p_id||': '||p_name);
      fetch refcur into p_id,p_name;
    end loop;
  close refcur;---关闭游标.
  end;
===============实现动态SQL的REF游标
语法:打开游标 OPEN cursor_name FOR dynamic_select_string
              [using bind_argument_list]
示例动态SQL的用法
set serveroutput on
variable maxsal number---variable:万能,常量
execute :maxsal :=2500
declare
  r_emp emp%rowtype;
  TYPE c_type IS REF CURSOR;
  cur c_type;声明游标类型变量
  p_salary number;
begin
  p_salary := :maxsal;
--:1相当于一个参数变量。就像是java中的？
  OPEN cur FOR 'select * from emp where sal>:1 order by sal desc'
  USING p_salary;
  dbms_output.put_line( '薪水大于'||p_salary||'的员工有:');
  LOOP
   FETCH cur into r_emp;
   exit when cur%notfound;
   dbms_output.put_line(
         '编号:'||r_emp.empno
      '姓名:'||r_emp.ename
        '薪水:'||r_emp.sal);
  end LOOP;
  close cur;--关闭游标
end;
===============================================
-----------游标的优点:
1.游标变量可用于从不同的结果集中提取记录
2.游标变量可作为过程的参数进行传递.
3.游标变量可以引用游标的所有属性.
4.游标变量可用于赋值运算.
-----游标受限制的地方:
1.FOR UPDATE 子名不能与游标变量一起使用
2.不允许在程序包中声明游标变量
3.另一服务器上的远程子过程不能接受游标变量参数的值
4.不能将NULL值赋给比较运算符.
5.游标变量不能使用比较运算符.
6.数据库的列不能存储游标变量
=====自我总结游标
游标是个什么东西?游标相当是一个记录集.
除隐式游标和循环游标外，显式游标和REF游标都是要OPEN(打开游标)和CLOSE(关闭游标).只有在REF游标中才有,才能把游标赋给一个变量.
首先是定义一个游标.可以把定义了的游标赋给一个变量,就成了游标变量.
一般定义的游标是一个SQL语句(如查询select * from table).
然后通过循环(loop end loop)遍历这个游标.
在REF游标中,可以通过游标变量.(点)字段名的方式得到SQL语句中的字段.

五、建表命令 

 

--在system/accp下
create table dual
(dummy varchar2(1));
-----------
create table customers
(customer_id INTEGER constraint customer_id primary key,
 first_name varchar2(10) not null,
 last_name varchar2(10) not null,
 dob date,
 phone varchar2(12));
 --这样插入行,企业管理器日期会变成01-十二月-2007 12:00:00 AM
 insert into customers values(1,'John','Brown',To_DATE('2007-12-1','YYYY-MM-DD'),'800-555-1211');
  commit;
  --设置默认的日期格式
alter session set nls_date_format='YYYY-MM-DD HH24:MI:ss';
  insert into customers values(7,'John','Brown','2006-05-10','800-555-1211');
rollback;回滚
 select * from customers;
--------主键和外键
create table products
(product_id INTEGER constraint products_pk primary key,
product_type_id INTEGER constraint product_fk_product_types references product_types(product_type_id),
name varchar2(30) not null,
description varchar2(50),
price number(5,2));
---联全主键
create table purchases
(
 product_id INTEGER constraint purchases_fk_products references products(product_id),
 customer_id INTEGER constraint purchases_fk_customers references customers(customer_id),
 quantity INTEGER not null,
 constraint purchases_pk primary key(product_id,customer_id)
);
--利用查询判断是否空值
select customer_id,first_name,last_name,dob
from customers where dob is null;
--利用where in查询
select * from customers where customer_id in (2,3,5);

 

员工基本信息表EMPINFO
 CREATE TABLE EMPINFO
 (
  EMP_ID  char(10)  PRIMARY KEY,  
 NAME  char(6) ,
 BIRTHDAY  date ,   
 SAL  number(4)      
 );
输出结果;
Table created

积分表（INTEG）共有3个字段：
 CREATE TABLE INTEG
 (
 INTEGER_ID  char(10) PRIMARY KEY,
 HABITUDE  varchar2(10),  
 SCORE number(4)    
 );
输出结果;
Table created

积分情况表:
CREATE TABLE THING
 (
EMP_ID  char(10),  
 INTEGER_id  char(10) 
 );
输出结果;
Table created

信息表表中插入相应的几条数据：
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL ) 
 VALUES ('iex_0001','王彪','12-8月-1983',600);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL) 
VALUES ('iex_0002','祝新平','11-5月-1984',600);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL ) 
VALUES ('iex_0003','谢青营','5-7月-1985',400);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL ) 
VALUES ('iex_0004','王淼','17-8月-1986',400);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL ) 
VALUES ('iex_0005','张求熙','25-9月-1984',800);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL ) 
VALUES ('iex_0006','习哲亮','14-8月-1986',1200);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL ) 
VALUES ('iex_0007','许丽丽','15-7月-1985',500);
输出结果;
1 row inserted

积分表中插入3条记录如下：
INSERT INTO INTEG values('I_001','公司类别',4);
INSERT INTO INTEG values('I_002','学习类别',2.5);
INSERT INTO INTEG values('I_003','协作类别',2.5);
INSERT INTO INTEG values('I_004','其他类别',1);
输出结果;
1 row inserted 

积分情况表插入6条如下;
INSERT INTO THING values('iex_0001','I_001');
INSERT INTO THING values('iex_0001','I_002');
INSERT INTO THING values('iex_0002','I_003');
INSERT INTO THING values('iex_0002','I_002');
INSERT INTO THING values('iex_0004','I_004');
INSERT INTO THING values('iex_0005','I_001');
输出结果;
1 row inserted 
 
1,查询员工基本信息表中的所有信息
 select * from EMPINFO;
输出结果;
EMP_ID     NAME   BIRTHDAY      SAL
---------- ------ ----------- -----
iex_0001   王彪   1983-8-12     600
iex_0002   祝新平 1984-5-11     600
iex_0003   谢青营 1985-7-5      400
iex_0004   王淼   1986-8-17     400
iex_0005   张求熙 1984-9-25     800
iex_0006   习哲亮 1986-8-14    1200
iex_0007   许丽丽 1985-7-15     500

2、    查询员工基本信息表中所有员工的员工编号，姓名和薪水。
select EMP_ID,NAME,SAL from EMPINFO;
输出结果;
EMP_ID     NAME     SAL
---------- ------ -----
iex_0001   王彪     600
iex_0002   祝新平   600
iex_0003   谢青营   400
iex_0004   王淼     400
iex_0005   张求熙   800
iex_0006   习哲亮  1200
iex_0007   许丽丽   500

3、    查询所有员工的姓名，出生年月，并在出生年月前显示“出生日期”字串。
select name,'出生年月'||BIRTHDAY from EMPINFO;
输出结果;
NAME   '出生年月'||BIRTHDAY
------ --------------------
王彪   出生年月12-8月 -83
祝新平 出生年月11-5月 -84
谢青营 出生年月05-7月 -85
王淼   出生年月17-8月 -86
张求熙 出生年月25-9月 -84
习哲亮 出生年月14-8月 -86
许丽丽 出生年月15-7月 -85

4、    查询员工基本信息表中的所有信息，要求列标题显示为中文。
 select EMP_ID as 员工编号,NAME as 员工姓名,BIRTHDAY as 出生日期,SAL as 薪水 from EMPINFO;
输出结果;
   员工姓名 出生日期     薪水
---------- -------- ----------- -----
iex_0001   王彪     1983-8-12     600
iex_0002   祝新平   1984-5-11     600
iex_0003   谢青营   1985-7-5      400
iex_0004   王淼     1986-8-17     400
iex_0005   张求熙   1984-9-25     800
iex_0006   习哲亮   1986-8-14    1200
iex_0007   许丽丽   1985-7-15     500

5、    查询员工基本信息表中姓名和加200后的薪水。
update EMPINFO set SAL=sal+200;
select NAME,SAL from EMPINFO;
输出结果;
NAME     SAL
------ -----
王彪     800
祝新平   800
谢青营   600
王淼     600
张求熙  1000
习哲亮  1400
许丽丽   700

6、    查询员工基本信息表中姓名和负值薪水。
select NAME,SAL from EMPINFO where sal<0;
输出结果;
NAME     SAL
------ -----

7、    查询员工基本信息表中姓名及提高5%后的薪水。
update EMPINFO set SAL=sal*1.5;
select NAME,SAL from EMPINFO;
输出结果;
NAME     SAL
------ -----
王彪    1200
祝新平  1200
谢青营   900
王淼     900
张求熙  1500
习哲亮  2100
许丽丽  1050

8、    查询员工基本信息表中姓名及一个月中每天的薪水。
select NAME,SAL/30 as 每天的薪水 from EMPINFO;
输出结果;
NAME   每天的薪水
------ ----------
王彪           40
祝新平         40
谢青营         30
王淼           30
张求熙         50
习哲亮         70
许丽丽         35

9、    查询积分表中的所有信息，要求输出格式为“XXX----XXX----XXX----XXX”。
select INTEGER_ID,'----'||HABITUDE,'----'||SCORE from INTEG;
输出结果;
INTEGER_ID '----'||HABITUDE '----'||SCORE
---------- ---------------- --------------------------------------------
I_001      ----公司类别     ----4
I_002      ----学习类别     ----3
I_003      ----协作类别     ----3
I_004      ----其他类别     ----1

10、    查询员工基本信息表中薪水低于300员的所有员工信息。
select * from EMPINFO where SAL<300;
输出结果;
EMP_ID     NAME   BIRTHDAY      SAL
---------- ------ ----------- -----

11、    查询姓名是“王彪”的员工信息。
select * from EMPINFO where name='王彪';
输出结果;
EMP_ID     NAME   BIRTHDAY      SAL
---------- ------ ----------- -----
iex_0001   王彪   1983-8-12    1200

12、    查询薪水在300至600之间的员工信息。
 select * from EMPINFO where sal>300 and sal<600;
输出结果;
EMP_ID     NAME   BIRTHDAY      SAL
---------- ------ ----------- -----
13、    查询出生年月在“1-5月-1983”至“31-12月-1986”之间的所有员工信息。
SQL> select * from EMPINFO where BIRTHDAY>'1-5月-1983' and BIRTHDAY<'31-12月-1986';
输出结果;
EMP_ID     NAME   BIRTHDAY      SAL
---------- ------ ----------- -----
iex_0001   王彪   1983-8-12    1200
iex_0002   祝新平 1984-5-11    1200
iex_0003   谢青营 1985-7-5      900
iex_0004   王淼   1986-8-17     900
iex_0005   张求熙 1984-9-25    1500
iex_0006   习哲亮 1986-8-14    2100
iex_0007   许丽丽 1985-7-15    1050

14、    查询积分编号为“I_001”和“I_004”，“I_002”的所有信息。
select * from INTEG where INTEGER_ID='I_001' or INTEGER_ID='I_004' or INTEGER_ID='I_002';
输出结果;
INTEGER_ID HABITUDE   SCORE
---------- ---------- -----
I_002      学习类别       3
I_004      其他类别       1
I_001      公司类别       4

15、    查询所有姓王的员工信息。
select * from EMPINFO where name LIKE '王%';
输出结果;
EMP_ID     NAME   BIRTHDAY      SAL
---------- ------ ----------- -----
iex_0001   王彪   1983-8-12    1200
iex_0004   王淼   1986-8-17     900

16、    查询没有登记出生年月的员工信息。
select * from EMPINFO where BIRTHDAY=null;
输出结果;

EMP_ID     NAME   BIRTHDAY      SAL
---------- ------ ----------- -----

17、    查询公司的薪水发放标准。
?
18、    查询员工编号为iex_0001完成的积分总数。
select THING.EMP_ID,INTEG.SCORE from THING join INTEG on THING.Emp_Id='iex_0001';
输出结果;
EMP_ID     SCORE
---------- -----
iex_0001       4
iex_0001       4
iex_0001       3
iex_0001       3
iex_0001       3
iex_0001       3
iex_0001       1
iex_0001       1

--错了19、    查询习哲亮的所有信息，并把他的出生年月推迟一个月。
 select EMP_ID, NAME,SAL,BIRTHDAY from EMPINFO where name='习哲亮';

20、    查询习哲亮出生那个月的最后一天。
 select EMP_ID, NAME,SAL,BIRTHDAY from EMPINFO where name='习哲亮';

23、    查询今天的日期。
 SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日"') FROM dual;
输出结果;
TO_CHAR(SYSDATE,'YYYY"年"FMMM"
------------------------------
2007年3月29日

24、    查询所有员工信息，按薪水的升序显示
 select * from EMPINFO order by SAL;
输出结果;
EMP_ID     NAME   BIRTHDAY      SAL
---------- ------ ----------- -----
iex_0003   谢青营 1985-7-5      900
iex_0004   王淼   1986-8-17     900
iex_0007   许丽丽 1985-7-15    1050
iex_0001   王彪   1983-8-12    1200
iex_0002   祝新平 1984-5-11    1200
iex_0005   张求熙 1984-9-25    1500
iex_0006   习哲亮 1986-8-14    2100

25、    对员工基本信息表和积分情况表进行非限制连接查询员工编号，姓名，积分编号;

 

六、日志触发器 

 

--建立一张日志表及触发器
create table logger(shijian date,name varchar(10),rema varchar2(100),oper varchar(10))

--before 在。。之前 after 在。。之后
--用户表
create or replace trigger cuidake before insert or delete or update 
--for each row 行触发
on first for each row
declare
 cdk1 varchar2(10);
begin
--dual 系统表 user在此系统中为用户
 select user into cdk1 from dual;
 if(inserting) then
 insert into logger values(sysdate,cdk1,'用户你插入的姓名是：'||:new.name||'年龄是：'||:new.age,'insert');
 end if;
 if(deleting) then
 insert into logger values(sysdate,cdk1,'用户你删除的姓名是：'||:old.name||'年龄是：'||:old.age||'的人','delete');
 end if;
 if(updating) then
 insert into logger values(sysdate,cdk1,'用户你更新了姓名是：'||:old.name||'年龄是：'||:old.age||'的人,更新为姓名为'||:new.name||'年龄为：'||:new.age,'update');
 end if;
end;
insert into first values('老汉','26')
update first set age=22 where name='老汉'
delete first where name='老汉'

select * from first 
  

select * from logger
================ 1.创建一个触发器,无论用户插入新记录,还是修改emp表的JOB列,都将用户指定的JOB列的值转换成大写.
说明。dual表是数据库本身就具有的表，主要作用是临时存放数据的。如
tempName varchar2(10);
select name into tempName from dual;
---------------
create  table aa
(
 id varchar2(10),
 name varchar2(10)
);
create or replace trigger tri_upper
before insert or update of name
on aa
for each row
declare
tempStr varchar2(10);
begin
 if (inserting) then
  select upper(:new.name) into :new.name from dual;
 end if;
 if (updating) then
  select upper(:new.name) into :new.name from dual where :new.id=:old.id;
 end if;
end ;
insert into aa values('2','aaa');
select * from aa;
update aa set name='fff' where id='1';

select sysdate as aa from dual;
 drop table dual;
 drop table aa;