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;
