最全的ORACLE-SQL笔记
时间：2022-03-14 01:46
-- 首先，以超级管理员的身份登录oracle sqlplus sys/bjsxt as sysdba --然后，解除对scott用户的锁 alter user scott account unlock; --那么这个用户名就能使用了。 --(默认全局数据库名orcl) 1、select ename, sal * 12 from emp; --计算年薪 2、select 2*3 from dual; --计算一个比较纯的数据用dual表 3、select sysdate from dual; --查看当前的系统时间 4、select ename, sal*12 anuual_sal from emp; --给搜索字段更改名称（双引号 keepFormat 别名有特殊字符，要加双引号）。 5、--任何含有空值的数学表达式，最后的计算结果都是空值。 6、select ename||sal from emp; --(将sal的查询结果转化为字符串，与ename连接到一起，相当于Java中的字符串连接) 7、select ename||‘afasjkj‘ from emp; --字符串的连接 8、select distinct deptno from emp; --消除deptno字段重复的值 9、select distinct deptno , job from emp; --将与这两个字段都重复的值去掉 10、select * from emp where deptno=10; --(条件过滤查询) 11、select * from emp where empno > 10; --大于 过滤判断 12、select * from emp where empno <> 10 --不等于 过滤判断 13、select * from emp where ename > ‘cba‘; --字符串比较，实际上比较的是每个字符的AscII值，与在Java中字符串的比较是一样的 14、select ename, sal from emp where sal between 800 and 1500; --(between and过滤，包含800 1500) 15、select ename, sal, comm from emp where comm is null; --(选择comm字段为null的数据) 16、select ename, sal, comm from emp where comm is not null; --(选择comm字段不为null的数据) 17、select ename, sal, comm from emp where sal in (800, 1500,2000); --(in 表范围) 18、select ename, sal, hiredate from emp where hiredate > ‘02-2月-1981‘; --(只能按照规定的格式写) 19、select ename, sal from emp where deptno =10 or sal >1000; 20、select ename, sal from emp where deptno =10 and sal >1000; 21、select ename, sal, comm from emp where sal not in (800, 1500,2000); --(可以对in指定的条件进行取反) 22、select ename from emp where ename like ‘%ALL%‘; --(模糊查询) 23、select ename from emp where ename like ‘_A%‘; --(取第二个字母是A的所有字段) 24、select ename from emp where ename like ‘%/%%‘; --(用转义字符/查询字段中本身就带%字段的) 25、select ename from emp where ename like ‘%$%%‘ escape ‘$‘; --(用转义字符/查询字段中本身就带%字段的) 26、select * from dept order by deptno desc; (使用order by desc字段 对数据进行降序排列 默认为升序asc); 27、select * from dept where deptno <>10 order by deptno asc; --(我们可以将过滤以后的数据再进行排序) 28、select ename, sal, deptno from emp order by deptno asc, ename desc; --(按照多个字段排序 首先按照deptno升序排列,当detpno相同时，内部再按照ename的降序排列) 29、select lower(ename) from emp; --(函数lower() 将ename搜索出来后全部转化为小写)； 30、select ename from emp where lower(ename) like ‘_a%‘; --(首先将所搜索字段转化为小写，然后判断第二个字母是不是a) 31、select substr(ename, 2, 3) from emp; --(使用函数substr() 将搜素出来的ename字段从第二个字母开始截，一共截3个字符) 32、select chr(65) from dual; --(函数chr() 将数字转化为AscII中相对应的字符) 33、select ascii(‘A‘) from dual; --(函数ascii()与32中的chr()函数是相反的 将相应的字符转化为相应的Ascii编码)) 34、select round(23.232) from dual; --(函数round() 进行四舍五入操作) 35、select round(23.232, 2) from dual; --(四舍五入后保留的小数位数 0 个位 -1 十位) 36、select to_char(sal, ‘$99,999.9999‘)from emp; --(加$符号加入千位分隔符，保留四位小数，没有的补零) 37、select to_char(sal, ‘L99,999.9999‘)from emp; --(L 将货币转化为本地币种此处将显示￥人民币) 38、select to_char(sal, ‘L00,000.0000‘)from emp; --(补零位数不一样，可到数据库执行查看) 39、select to_char(hiredate, ‘yyyy-MM-DD HH:MI:SS‘) from emp; --(改变日期默认的显示格式) 40、select to_char(sysdate, ‘yyyy-MM-DD HH:MI:SS‘) from dual; --(用12小时制显示当前的系统时间) 41、select to_char(sysdate, ‘yyyy-MM-DD HH24:MI:SS‘) from dual; --(用24小时制显示当前的系统时间) 42、select ename, hiredate from emp where hiredate > to_date(‘1981-2-20 12:24:45‘,‘YYYY-MM-DD HH24:MI:SS‘) --(函数to-date 查询公司在所给时间以后入 职的人员) 43、select sal from emp where sal > to_number(‘$1,250.00‘, ‘$9,999.99‘); --(函数to_number()求出这种薪水里带有特殊符号的) 44、select ename, sal*12 + nvl(comm,0) from emp; --(函数nvl() 求出员工的"年薪 + 提成(或奖金)问题") 45、select max(sal) from emp; -- (函数max() 求出emp表中sal字段的最大值) 46、select min(sal) from emp; -- (函数max() 求出emp表中sal字段的最小值) 47、select avg(sal) from emp; --(avg()求平均薪水); 48、select to_char(avg(sal), ‘999999.99‘) from emp; --(将求出来的平均薪水只保留2位小数) 49、select round(avg(sal), 2) from emp; --(将平均薪水四舍五入到小数点后2位) 50、select sum(sal) from emp; --(求出每个月要支付的总薪水) ------------------------/组函数(共5个)：将多个条件组合到一起最后只产生一个数据------min() max() avg() sum() count()----------------------------/ 51、select count(*) from emp; --求出表中一共有多少条记录 52、select count(*) from emp where deptno=10; --再要求一共有多少条记录的时候，还可以在后面跟上限定条件 53、select count(distinct deptno) from emp; --统计部门编号前提是去掉重复的值 ------------------------聚组函数group by() -------------------------------------- 54、select deptno, avg(sal) from emp group by deptno; --按照deptno分组，查看每个部门的平均工资 55、select max(sal) from emp group by deptno, job; --分组的时候，还可以按照多个字段进行分组，两个字段不相同的为一组 56、select ename from emp where sal = (select max(sal) from emp); --求出 57、select deptno, max(sal) from emp group by deptno; --搜素这个部门中薪水最高的的值 --------------------------------------------------having函数对于group by函数的过滤 不能用where-------------------------------------- 58、select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; （order by ）--求出每个部门的平均值，并且要 > 2000 59、select avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;--求出sal>1200的平均值按照deptno分组，平均值要>1500最后按照sal的倒序排列 60、select ename,sal from emp where sal > (select avg(sal) from emp); --求那些人的薪水是在平均薪水之上的。 61、select ename, sal from emp join (select max(sal) max_sal ,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno=t.deptno); --查询每个部门中工资最高的那个人 ------------------------------/等值连接-------------------------------------- 62、select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; --自连接，把一张表当成两张表来用 63、select ename, dname from emp, dept; --92年语法 两张表的连接 笛卡尔积。 64、select ename, dname from emp cross join dept; --99年语法 两张表的连接用cross join 65、select ename, dname from emp, dept where emp.deptno = dept.deptno; -- 92年语法 表连接 + 条件连接 66、select ename, dname from emp join dept on(emp.deptno = dept.deptno); -- 新语法 67、select ename,dname from emp join dept using(deptno); --与66题的写法是一样的，但是不推荐使用using ： 假设条件太多 --------------------------------------/非等值连接------------------------------------------/ 68、select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); --两张表的连接 此种写法比用where更清晰 69、select ename, dname, grade from emp e join dept d on(e.deptno = d.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like ‘_A%‘; --三张表的连接 70、select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); --自连接第二种写法，同62 71、select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); --左外连接 把左边没有满足条件的数据也取出来 72、select ename, dname from emp e right join dept d on(e.deptno = d.deptno); --右外连接 73、select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);--求每个部门平均薪水的等级 74、select ename from emp where empno in (select mgr from emp); -- 在表中搜索那些人是经理 75、select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal)); -- 面试题 不用组函数max()求薪水的最大值 76、select deptno, max_sal from (select avg(sal) max_sal,deptno from emp group by deptno) where max_sal = (select max(max_sal) from (select avg(sal) max_sal,deptno from emp group by deptno) );--求平均薪水最高的部门名称和编号。 77、select t1.deptno, grade, avg_sal from (select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal) ) t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select min(grade) from (select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal) ) )--求平均薪水等级最低的部门的名称 哈哈 确实比较麻烦 78、create view v$_dept_avg_sal_info as select deptno, grade, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal); --视图的创建，一般以v$开头，但不是固定的 79、select t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select min(grade) from v$_dept_avg_sal_info t1 ) )--求平均薪水等级最低的部门的名称 用视图，能简单一些，相当于Java中方法的封装 80、---创建视图出现权限不足时候的解决办法： conn sys/admin as sysdba; --显示：连接成功 Connected grant create table, create view to scott; -- 显示: 授权成功 Grant succeeded 81、-------求比普通员工最高薪水还要高的经理人的名称 ------- select ename, sal from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > ( select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null) ) 82、---面试题：比较效率 select * from emp where deptno = 10 and ename like ‘%A%‘;--好，将过滤力度大的放在前面 select * from emp where ename like ‘%A%‘ and deptno = 10; 83、-----表的备份 create table dept2 as select * from dept; 84、-----插入数据 insert into dept2 values(50,‘game‘,‘beijing‘); ----只对某个字段插入数据 insert into dept2(deptno,dname) values(60,‘game2‘); 85、-----将一个表中的数据完全插入另一个表中（表结构必须一样） insert into dept2 select * from dept; 86、-----求前五名员工的编号和名称（使用虚字段rownum 只能使用 < 或 = 要使用 > 必须使用子查询） select empno,ename from emp where rownum <= 5; 86、----求10名雇员以后的雇员名称-------- select ename from (select rownum r,ename from emp) where r > 10; 87、----求薪水最高的前5个人的薪水和名字--------- select ename, sal from (select ename, sal from emp order by sal desc) where rownum <=5; 88、----求按薪水倒序排列后的第6名到第10名的员工的名字和薪水-------- select ename, sal from (select ename, sal, rownum r from (select ename, sal from emp order by sal desc) ) where r>=6 and r<=10 89、----------------创建新用户--------------- 1、backup scott--备份 exp--导出 2、create user create user guohailong identified(认证) by guohailong default tablespace users quota(配额) 10M on users grant create session(给它登录到服务器的权限),create table, create view to guohailong 3、import data imp 90、-----------事务回退语句-------- rollback; 91、-----------事务确认语句-------- commit;--此时再执行rollback无效 92、--当正常断开连接的时候例如exit,事务自动提交。 当非正常断开连接，例如直接关闭dos窗口或关机，事务自动提交 93、/*有3个表S，C，SC S（SNO，SNAME）代表（学号，姓名） C（CNO，CNAME，CTEACHER）代表（课号，课名，教师） SC（SNO，CNO，SCGRADE）代表（学号，课号成绩） 问题： 1，找出没选过“黎明”老师的所有学生姓名。 2，列出2门以上（含2门）不及格学生姓名及平均成绩。 3，即学过1号课程有学过2号课所有学生的姓名。 */答案： 1、 select sname from s join sc on(s.sno = sc.sno) join c on (sc.cno = c.cno) where cteacher <> ‘黎明‘; 2、 select sname where sno in (select sno from sc where scgrade < 60 group by sno having count(*) >=2); 3、 select sname from s where sno in (select sno, from sc where cno=1 and cno in (select distinct sno from sc where cno = 2); ) 94、--------------创建表-------------- create table stu ( id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50) unique ); 95、--------------给name字段加入 非空 约束，并给约束一个名字，若不取，系统默认取一个------------- create table stu ( id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50) ); 96、--------------给nameemail字段加入 唯一 约束 两个 null值 不为重复------------- create table stu ( id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50) unique ); 97、--------------两个字段的组合不能重复 约束：表级约束------------- create table stu ( id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50)， constraint stu_name_email_uni unique(email, name) ); 98、--------------主键约束------------- create table stu ( id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50)， constraint stu_id_pk primary key (id), constraint stu_name_email_uni unique(email, name) ); 99、--------------外键约束 被参考字段必须是主键 ------------- create table stu ( id number(6), name varchar2(20) constraint stu_name_mm not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4) references class(id), email varchar2(50)， constraint stu_class_fk foreign key (class) references class(id), constraint stu_id_pk primary key (id), constraint stu_name_email_uni unique(email, name) ); create table class ( id number(4) primary key, name varchar2(20) not null ); 100、---------------修改表结构，添加字段------------------ alter table stu add(addr varchar2(29)); 101、---------------删除字段-------------------------- alter table stu drop (addr); 102、---------------修改表字段的长度------------------ alter table stu modify (addr varchar2(50));--更改后的长度必须要能容纳原先的数据 103、----------------删除约束条件---------------- alter table stu drop constraint 约束名 104、-----------修改表结构添加约束条件--------------- alter table stu add constraint stu_class_fk foreign key (class) references class (id); 105、---------------数据字典表---------------- desc dictionary; --数据字典表共有两个字段 table_name comments --table_name主要存放数据字典表的名字 --comments主要是对这张数据字典表的描述 105、---------------查看当前用户下面所有的表、视图、约束-----数据字典表user_tables--- select table_name from user_tables; select view_name from user_views; select constraint_name from user-constraints; 106、-------------索引------------------ create index idx_stu_email on stu (email);-- 在stu这张表的email字段上建立一个索引：idx_stu_email 107、---------- 删除索引 ------------------ drop index index_stu_email; 108、---------查看所有的索引---------------- select index_name from user_indexes; 109、---------创建视图------------------- create view v$stu as selesct id,name,age from stu; 视图的作用: 简化查询 保护我们的一些私有数据，通过视图也可以用来更新数据，但是我们一般不这么用 缺点：要对视图进行维护 110、-----------创建序列------------ create sequence seq;--创建序列 select seq.nextval from dual;-- 查看seq序列的下一个值 drop sequence seq;--删除序列 111、------------数据库的三范式-------------- （1）、要有主键，列不可分 （2）、不能存在部分依赖：当有多个字段联合起来作为主键的时候，不是主键的字段不能部分依赖于主键中的某个字段 （3）、不能存在传递依赖 ==============================================PL/SQL========================== 112、-------------------在客户端输出helloworld------------------------------- set serveroutput on;--默认是off，设成on是让Oracle可以在客户端输出数据 113、begin dbms_output.put_line(‘helloworld‘); end; / 114、----------------pl/sql变量的赋值与输出---- declare v_name varchar2(20);--声明变量v_name变量的声明以v_开头 begin v_name := ‘myname‘; dbms_output.put_line(v_name); end; / 115、-----------pl/sql对于异常的处理(除数为0)------------- declare v_num number := 0; begin v_num := 2/v_num; dbms_output.put_line(v_num); exception when others then dbms_output.put_line(‘error‘); end; / 116、----------变量的声明---------- binary_integer:整数，主要用来计数而不是用来表示字段类型 比number效率高 number:数字类型 char:定长字符串 varchar2：变长字符串 date：日期 long：字符串，最长2GB boolean：布尔类型，可以取值true，false，null--最好给一初值 117、----------变量的声明，使用 ‘%type‘属性 declare v_empno number(4); v_empno2 emp.empno%type; v_empno3 v_empno2%type; begin dbms_output.put_line(‘Test‘); end; / --使用%type属性，可以使变量的声明根据表字段的类型自动变换，省去了维护的麻烦，而且%type属性，可以用于变量身上 118、---------------Table变量类型(table表示的是一个数组)------------------- declare type type_table_emp_empno is table of emp.empno%type index by binary_integer; v_empnos type_table type_table_empno; begin v_empnos(0) := 7345; v_empnos(-1) :=9999; dbms_output.put_line(v_empnos(-1)); end; 119、-----------------Record变量类型 declare type type_record_dept is record ( deptno dept.deptno%type, dname dept.dname%type, loc dept.loc%type ); begin v_temp.deptno:=50; v_temp.dname:=‘aaaa‘; v_temp.loc:=‘bj‘; dbms_output.put_line(v temp.deptno || ‘ ‘ || v temp.dname); end; 120、-----------使用 %rowtype声明record变量 declare v_temp dept%rowtype; begin v_temp.deptno:=50; v_temp.dname:=‘aaaa‘; v_temp.loc:=‘bj‘; dbms_output.put_line(v temp.deptno || ‘‘ || v temp.dname) end; 121、--------------sql%count 统计上一条sql语句更新的记录条数 122、--------------sql语句的运用 declare v_ename emp.ename%type; v_sal emp.sal%type; begin select ename,sal into v_ename,v_sal from emp where empno = 7369; dbms_output.put_line(v_ename || ‘‘ || v_sal); end; 123、 -------- pl/sql语句的应用 declare v_emp emp%rowtype; begin select * into v_emp from emp where empno=7369; dbms_output_line(v_emp.ename); end; 124、-------------pl/sql语句的应用 declare v_deptno dept.deptno%type := 50; v_dname dept.dname%type :=‘aaa‘; v_loc dept.loc%type := ‘bj‘; begin insert into dept2 values(v_deptno,v_dname,v_loc); commit; end; 125、-----------------ddl语言，数据定义语言 begin execute immediate ‘create table T (nnn varchar(30) default ‘‘a‘‘)‘; end; 126、------------------if else的运用 declare v_sal emp.sal%type; begin select sal into v_sal from emp where empno = 7369; if(v_sal < 2000) then dbms_output.put_line(‘low‘); elsif(v_sal > 2000) then dbms_output.put_line(‘middle‘); else dbms_output.put_line(‘height‘); end if; end; 127、-------------------循环 =====do while declare i binary_integer := 1; begin loop dbms_output.put_line(i); i := i + 1; exit when (i>=11); end loop; end; 128、---------------------while declare j binary_integer := 1; begin while j < 11 loop dbms_output.put_line(j); j:=j+1; end loop; end; 129、---------------------for begin for k in 1..10 loop dbms_output.put_line(k); end loop; for k in reverse 1..10 loop dbms_output.put_line(k); end loop; end; 130、-----------------------异常(1) declare v_temp number(4); begin select empno into v_temp from emp where empno = 10; exception when too_many_rows then dbms_output.put_line(‘太多记录了‘); when others then dbms_output.put_line(‘error‘); end; 131、-----------------------异常(2) declare v_temp number(4); begin select empno into v_temp from emp where empno = 2222; exception when no_data_found then dbms_output.put_line(‘太多记录了‘); end; 132、----------------------创建序列 create sequence seq_errorlog_id start with 1 increment by 1; 133、-----------------------错误处理(用表记录：将系统日志存到数据库便于以后查看) -- 创建日志表: create table errorlog ( id number primary key, errcode number, errmsg varchar2(1024), errdate date ); declare v_deptno dept.deptno%type := 10; v_errcode number; v_errmsg varchar2(1024); begin delete from dept where deptno = v_deptno; commit; exception when others then rollback; v_errcode := SQLcode; v_errmsg := SQLERRM; insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate); commit; end; 133---------------------PL/SQL中的重点cursor(游标)和指针的概念差不多 declare cursor c is select * from emp; --此处的语句不会立刻执行，而是当下面的open c的时候，才会真正执行 v_emp c%rowtype; begin open c; fetch c into v_emp; dbms_output.put_line(v_emp.ename); --这样会只输出一条数据 134将使用循环的方法输出每一条记录 close c; end; 134----------------------使用do while 循环遍历游标中的每一个数据 declare cursor c is select * from emp; v_emp c%rowtype; begin open c; loop fetch c into v_emp; (1) exit when (c%notfound); --notfound是oracle中的关键字，作用是判断是否还有下一条数据 (2) dbms_output.put_line(v_emp.ename); --(1)(2)的顺序不能颠倒，最后一条数据，不会出错，会把最后一条数据，再次的打印一遍 end loop; close c; end; 135------------------------while循环，遍历游标 declare cursor c is select * from emp; v_emp emp%rowtype; begin open c; fetch c into v_emp; while(c%found) loop dbms_output.put_line(v_emp.ename); fetch c into v_emp; end loop; close c; end; 136--------------------------for 循环，遍历游标 declare cursor c is select * from emp; begin for v_emp in c loop dbms_output.put_line(v_emp.ename); end loop; end; 137---------------------------带参数的游标 declare cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is select ename, sal from emp where deptno=v_deptno and job=v_job; --v_temp c%rowtype;此处不用声明变量类型 begin for v_temp in c(30, ‘click‘) loop dbms_output.put_line(v_temp.ename); end loop; end; 138-----------------------------可更新的游标 declare cursor c --有点小错误 is select * from emp2 for update; -v_temp c%rowtype; begin for v_temp in c loop if(v_temp.sal < 2000) then update emp2 set sal = sal * 2 where current of c; else if (v_temp.sal =5000) then delete from emp2 where current of c; end if; end loop; commit; end; 139-----------------------------------procedure存储过程(带有名字的程序块) create or replace procedure p is--这两句除了替代declare，下面的语句全部都一样 cursor c is select * from emp2 for update; begin for v_emp in c loop if(v_emp.deptno = 10) then update emp2 set sal = sal +10 where current of c; else if(v_emp.deptno =20) then update emp2 set sal = sal + 20 where current of c; else update emp2 set sal = sal + 50 where current of c; end if; end loop; commit; end; --执行存储过程的两种方法： （1）exec p;(p是存储过程的名称) （2） begin p; end; / 140-------------------------------带参数的存储过程 create or replace procedure p (v_a in number, v_b number, v_ret out number, v_temp in out number) is begin if(v_a > v_b) then v_ret := v_a; else v_ret := v_b; end if; v_temp := v_temp + 1; end; 141----------------------调用140 declare v_a number := 3; v_b number := 4; v_ret number; v_temp number := 5; begin p(v_a, v_b, v_ret, v_temp); dbms_output.put_line(v_ret); dbms_output.put_line(v_temp); end; 142------------------删除存储过程 drop procedure p; 143------------------------创建函数计算个人所得税 create or replace function sal_tax (v_sal number) return number is begin if(v_sal < 2000) then return 0.10; elsif(v_sal <2750) then return 0.15; else return 0.20; end if; end; ----144-------------------------创建触发器（trigger） 触发器不能单独的存在，必须依附在某一张表上 --创建触发器的依附表 create table emp2_log ( ename varchar2(30) , eaction varchar2(20), etime date ); create or replace trigger trig after insert or delete or update on emp2 ---for each row 加上此句，每更新一行，触发一次，不加入则值触发一次 begin if inserting then insert into emp2_log values(USER, ‘insert‘, sysdate); elsif updating then insert into emp2_log values(USER, ‘update‘, sysdate); elsif deleting then insert into emp2_log values(USER, ‘delete‘, sysdate); end if; end; 145-------------------------------通过触发器更新数据 create or replace trigger trig after update on dept for each row begin update emp set deptno =:NEW.deptno where deptno =: OLD.deptno; end; ------只编译不显示的解决办法 set serveroutput on; 145-------------------------------通过创建存储过程完成递归 create or replace procedure p(v_pid article.pid%type,v_level binary_integer) is cursor c is select * from article where pid = v_pid; v_preStr varchar2(1024) := ‘‘; begin for i in 0..v_leave loop v_preStr := v_preStr || ‘****‘; end loop; for v_article in c loop dbms_output.put_line(v_article.cont); if(v_article.isleaf = 0) then p(v_article.id); end if; end loop; end; 146-------------------------------查看当前用户下有哪些表--- --首先，用这个用户登录然后使用语句： select * from tab; 147-----------------------------用Oracle进行分页！-------------- --因为Oracle中的隐含字段rownum不支持‘>‘所以： select * from ( select rownum rn, t.* from ( select * from t_user where user_id <> ‘root‘ ) t where rownum <6 ) where rn >3 148------------------------Oracle下面的清屏命令---------------- clear screen; 或者 cle scr; 149-----------将创建好的guohailong的这个用户的密码改为abc-------------- alter user guohailong identified by abc --当密码使用的是数字的时候可能会不行 --使用在10 Oracle以上的正则表达式在dual表查询 with test1 as( select ‘ao‘ name from dual union all select ‘yang‘ from dual union all select ‘feng‘ from dual ) select distinct regexp_replace(name,‘[0-9]‘,‘‘) from test1 ------------------------------------------ with tab as ( select ‘hong‘ name from dual union all select ‘qi‘ name from dual union all select ‘gong‘ name from dual) select translate(name,‘\\0123456789‘,‘\\‘) from tab; CREATE OR REPLACE PROCEDURE calc(i_birth VARCHAR2) IS s VARCHAR2(8); o VARCHAR2(8); PROCEDURE cc(num VARCHAR2, s OUT VARCHAR2) IS BEGIN FOR i IN REVERSE 2 .. length(num) LOOP s := s || substr(substr(num, i, 1) + substr(num, i - 1, 1), -1); END LOOP; SELECT REVERSE(s) INTO s FROM dual; END; BEGIN o := i_birth; LOOP cc(o, s); o := s; dbms_output.put_line(s); EXIT WHEN length(o) < 2; END LOOP; END; set serveroutput on; exec calc(‘19880323‘); ----算命pl/sql with t as (select ‘19880323‘ x from dual) select case when mod (i, 2) = 0 then ‘命好‘ when i = 9 then ‘命运悲惨‘ else ‘一般‘ end result from (select mod(sum((to_number(substr(x, level, 1)) +to_number(substr(x, -level, 1))) * greatest(((level - 1) * 2 - 1) * 7, 1)),10) i from t connect by level <= 4); --构造一个表，和emp表的部分字段相同，但是顺序不同 SQL> create table t_emp as 2 select ename,empno,deptno,sal 3 from emp 4 where 1=0 5 / Table created --添加数据 SQL> insert into t_emp(ename,empno,deptno,sal) 2 select ename,empno,deptno,sal 3 from emp 4 where sal >= 2500 5 / select * from tb_product where createdate>=to_date(‘2011-6-13‘,‘yyyy-MM-dd‘) and createdate<=to_date(‘2011-6-16‘,‘yyyy-MM-dd‘); sysdate --获取当前系统的时间 to_date(‘‘,‘yyyy-mm-dd‘) select * from tb_product where to_char(createdate,‘yyyy-MM-dd‘)>=‘2011-6-13‘ and to_char(createdate,‘yyyy-MM-dd‘)<=‘2011-6-16‘; select * from tb_product where trunc(createdate)>=? and trunc(createdate)<=? 用trunc函数就可以了 </pre> <p> </p> <pre name="code" >第一次 1、Oracle安装及基本命令 1.1、Orace简介 Oracleso一个生产中间件和数据库的较大生产商。其发展依靠了IBM公司。创始人是Larry Ellison。 1.2、Oracle的安装 1) Oracle的主要版本 Oracle 8； Oracle 8i；i，指的是Internet Oracle 9i；相比Oracle8i比较类似 Oracle 10g；g，表示网格技术 所谓网格技术，拿百度搜索为例，现在我们需要搜索一款叫做“EditPlus”的文本编辑器软件，当我们在百度搜索框中输入“EditPlus”进行搜索时， 会得到百度为我们搜索到的大量关于它的链接，此时，我们考虑一个问题，如果在我所处的网络环境周边的某个地方的服务器就提供这款软件的下载（也就是说提供一个下载链接供我们下载），那么，我们就没必要去访问一个远在地球对面的某个角落的服务器去下载这款软件。如此一来就可以节省大量的网络资源。使用网格技术就能解决这种问题。我们将整个网络划分为若干个网格，也就是说每一个使用网络的用户，均存在于某一个网格，当我们需要搜索指定资源时，首先在我们所处的网格中查找是否存在指定资源，没有的话就扩大搜索范围，到更大的网格中进行查找，直到查找到为止。 2） 安装Oracle的准备工作 关闭防火墙，以免影响数据库的正常安装。 3） 安装Oralce的注意事项 为了后期的开发和学习，我们将所有数据库默认账户的口令设置为统一口令的，方便管理和使用。 在点击“安装”后，数据库相关参数设置完成，其安装工作正式开始，在完成安装时，不要急着去点击“确定”按钮，这时候，我们需要进行一个非常重要的操作——账户解锁。因为在Oracle中默认有一个叫做scott的账户，该账户中默认有4张表，并且存有相应的数据，所以，为了方便我们学习Oracle数据库，我们可以充分利用scott这个内置账户。但是奇怪的是，在安装Oracle数据库的时候，scott默认是锁住的，所以在使用该账户之前，我们就需要对其进行解锁操作。在安装完成界面中，点击“口令管理”进入到相应的口令管理界面，找到scott账户，将是否解锁一栏的去掉，即可完成解锁操作，后期就可以正常使用scott账户。我们运行SQLPlus（Oracle提供的命令行操作），会提示我们输入用户名，现在我们可以输入用户名scott，回车后，会提示输入口令，奇怪的是，当我们输入在安装时设置的统一口令时，提示登录拒绝，显然是密码错误，那么，在Oracle数据库中，scott的默认密码是tiger，所以使用tiger可以正常登录，但是提示我们scott的当前密码已经失效，让我们重新设置密码，建议还是设置为tiger。 在Oracle中内置了很多账户，那么，我们来了解下一下几个比较重要的内置账户： |-普通用户：scott/tiger |-普通管理员：system/manager |-超级管理员：sys/change_on_install 4） 关于Oracle的服务 在Oracle安装完成之后，会在我们的系统中进行相关服务的注册，在所有注册的服务中，我们需要关注一下两个服务，在实际使用Oracle的过程中，这两个服务必须启动才能使Oracle正常使用。 |-第一个是OracleOraDb11g_home1TNSListener，监听服务，如果客户端想要连接数据库，此服务必须开启。 |-第二个是OracleServiceORCL，数据库的主服务。命名规则：OracleService + 数据库名称，此 服务必须启动。 此后，我们可以通过命令行方式进入到SQLPlus的控制中心，进行命令的输入。 1.3、SQLPlus SQLPlus是Oracle提供的一种命令行执行的工具软件，安装之后会自动在系统中进行注册。连接到数据库之后，就可以开始对数据库中的表进行操作了。 1） 对SQLPlus的环境设置 set linesize 长度;--设置每行显示的长度 set pagesize 行数;--修改每页显示记录的长度。 需要注意的是，上述连个参数的设置只在当前的命令行有效，命令行窗口重启或者开启了第二个窗口需要重新设置。 2） SQLPlus常用操作 在SQLPlus中输入ed a.sql，会弹出找不到文件的提示框，此时点击“是”，将创建一个a.sql文件，并弹出文本编辑页面，在这里可以输入相关的sql语句，编辑完成后保存，在命令行中通过 @ a.sql的方式执行命令，如果创建的文件后缀为“sql”，那么在执行的时候可以省略掉，即可以这么写， @ a。除了创建不存在的文件外，sqlplus中也可以通过指定本地存在的文件进行命令的执行，方式为 @ 文件路径。 在SQLPlus中可以通过命令使用其他账户进行数据库的连接，如，当前连接的用户是scott，我们需要使用sys进行连接，则可以这么操作：conn sys/430583 as sysdba，这里需要说明的是，sys是超级管理员，当我们需要使用sys进行登录的时候，那么需要额外的加上as sysdba表示sys将以管理员的身份登录。这里有几点可以测试下 |-当我们使用sys以sysdba的角色登录时，其密码可以随意输入，不一定是我们设置的统一口令（430583）。所以，我们得出结论，在管理员登录时，只对用户进行验证，而普通用户登录时，执行用户和密码验证。 在sys账户下访问scott下的emp表时，会提示错误，因为在sys中是不存在emp表的，那么如果需要在sys下访问scott的表（也就是说需要在a用户下访问b用户下的表），该如何操作呢？首先，我们应该知道每个对象是属于一种模式（模式是对用户所创建的数据库对象的总称，包括表，视图，索引，同义词，序列，过程和程序包等）的，而每个账户对应一个模式，所以我们需要在sys下访问scott的表时，需要指明所访问的表是属于哪一个模式的，即，我们可以这样操作来实现上面的操作：select * from scott.emp; 如果我们需要知道当前连接数据库的账户是谁，可以这样操作：show user; 我们知道，一个数据库可以存储多张表，那么，如何查看指定数据库的所有表名称呢？select * from tab; 在开发过程中，我们需要经常的查看某张表的表结构，这个操作可以这样实现：desc emp; 在SQLPlus中，我们可以输入“/”来快速执行上一条语句。例如，在命令行中我们执行了一条这样的语句：select * from emp;但是我们需要再次执行该查询，就可以输入一个“/”就可快速执行。 3） 常用数据类型 number(4)-->表示数字，长度为4 varchar2(10)-->表示的是字符串，只能容纳10个长度 date-->表示日期 number(7,2)-->表示数字，小数占2位，整数占5位，总共7位 第二次 1、SQL语句 1.1 准备工作--熟悉scott账户下的四张表及表结构 第一张表emp-->雇员表，用于存储雇员信息 empno number(4) 表示雇员的编号，唯一编号 ename varchar2(10) 表示雇员的姓名 job varchar2(9) 表示工作职位 mgr number(4) 表示一个雇员的上司编号 hiredate date 表示雇佣日期 sal number(7,2) 表示月薪，工资 comm number(7,2) 表示奖金 deptno number(2) 表示部门编号 第二张表dept-->部门表，用于存储部门信息 deptno number(2) 部门编号 dname varchar2(14) 部门名称 loc varchar2(13) 部门位置 第三张表salgrade-->工资等级表，用于存储工资等级 grade number 等级名称 losal number 此等级的最低工资 hisal number 此等级的最高工资 第四张表bonus-->奖金表，用于存储一个雇员的工资及奖金 ename varchar2(10) 雇员姓名 job varchar2(9) 雇员工作 sal number 雇员工资 comm number 雇员奖金 1.2、SQL简介 什么是SQL? SQL（Structured Query Language，结构查询语言）是一个功能强大的数据语言。SQL通常用于与数据库的通讯。SQL是关系数据库管理系统的标准语言。SQL功能强大，概括起来，分为以下几组： |-DML-->Data Manipulation Language，数据操纵语言，用于检索或者修改数据，即主要是对数据库表中的数据的操作。 |-DDL-->Data Definition Language,数据定义语言，用于定义数据的结构，如创建、修改或者删除数据库对象，即主要是对表的操作。 |-DCL-->Data Control Language,数据控制语言，用于定义数据库用户的权限，即主要对用户权限的操作。 1.3、简单查询语句 简单查询语句的语法格式是怎样的？ select * |具体的列名 [as] [别名] from 表名称; 需要说明的是，在实际开发中，最好不要使用*代替需要查询的所有列，最好养成显式书写需要查询的列名，方便后期的维护；在给查询的列名设置别名的时候，可以使用关键字as，当然不用也是可以的。 拿emp表为例，我们现在需要查询出雇员的编号、姓名、工作三个列的信息的话，就需要在查询的时候明确指定查询的列名称，即 select empno,ename,job from emp; 如果需要指定查询的返回列的名称，即给查询列起别名，我们可以这样操作 select empno 编号,ename 姓名,job 工作 from emp;--省略as关键字的写法 或者 select empno as 编号,ename as 姓名,job as 做工 from emp; --保留as关键字的写法 如果现在需要我们查询出emp中所有的job，我们可能这么操作 select job from emp; 可能加上一个别名会比较好 select job 工作 from emp; 但是现在出现了一个问题，从查询的结果中可以看出，job的值是有重复的，这是为什么呢？因为我们知道一个job职位可能会对应多个雇员，比如，在一个公司的市场部会有多名市场人员，在这里我们使用select job from emp;查询的实际上是当前每个雇员对应的工作职位，有多少 个雇员，就会对应的查询出多少个职位出来，所以就出现了重复值，为了消除这样的重复值，我们在这里可以使用关键字distinct，接下来我们继续完成上面的操作，即 select distinct job from emp; 所以，我们可以看到，使用distinct的语法是这样的： select distinct *|具体的列名 别名 from 表名称; 但是在消除重复列的时候，需要强调的是，如果我们使用distinct同时查询多列时，则必须保证查询的所有列都存在重复数据才能消除掉。也就是说，当我们需要查询a,b,c三列时，如果a表存在重复值，但是b和c中没有重复值，当使用distinct时是看不到消除重复列的效果的。拿scott中的emp表为例，我们需要查询出雇员编号及雇员工作两个列的值，我们知道一个工作会对应多个雇员，所以，在这种操作中，雇员编号是没有重复值的，但是工作有重复值，所以，执行此次查询的结果将是得到每一个雇员对应的工作名称，显然，distinct没起到作用。 现在我们得到了一个新的需求，要求我们按如下的方式进行查询： 编号是：7369的雇员，姓名是：SMITH，工作是：CLERK 那么，我们该如何解决呢？在这里，我们需要使用到Oracle 中的字符串连接（“||”）操作来实现。如果需要显示一些额外信息的话，我们需要使用单引号将要显示的信息包含起来。那么，上面的操作可以按照下面的方式进行， select ‘编号是‘ || empno || ‘的雇员，姓名是：‘ || ename || ‘，工作是：‘ || job from emp; 下面我们再看一个新的应用。公司业绩很好，所以老板想加薪，所有员工的工资上调20%，该如何实现呢？难道在表中一个一个的修改工资列的值吗？很显然不是的，我们可以通过使用四则运算来完成加薪的操作。 select ename,sal*1.2 newsal from emp;--newsal是为上调后的工资设置的列名 四则运算，+、-、*、/，同样有优先顺序，先乘除后加减。 1.4、限定查询（where子句） 在前面我们都是将一张表的全部列或者指定列的所有数据查询出来，现在我们有新的需求了，需要在指定条件下查询出数据，比如，需要我们查询出部门号为20的所有雇员、查询出工资在3000以上的雇员信息......，这些所谓的查询限定条件就是通过where来指定的。我们先看看如何通过限定查询的方式进行相关操作。我们在前面知道了简单的查询语法是： select *|具体的列名 from 表名称; 那么，限定查询的语法格式如下： select *|具体的列名 from 表名称 where 条件表达式; 首先，我们现在要查询出工资在1500（不包括1500）之上的雇员信息 select * from emp where sal>1500; 下面的操作是查询出可以得到奖金的雇员信息（也就是奖金列不为空的记录） select * from emp where comm is not null; 很显然，查询没有奖金的操作就是 select * from emp where comm is null; 我们来点复杂的查询，查询出工资在1500之上，并且可以拿到奖金的雇员信息。这里的限定条件不再是一个了，当所有的限定条件需要同时满足时，我们采用and关键字将多个限定条件进行连接，表示所有的限定条件都需要满足，所以，该查询可以这么进行： select * from emp where sal>1500 and comm is not null; 现在的需求发生了变化，要求查询出工资在1500之上，或者可以领取到奖金的雇员信息。这里的限定条件也是两个，但是有所不同的是，我们不需要同时满足着两个条件，因为需求中写的是 “或者”，所以在查询时，只需要满足两个条件中的一个就行。那么，我们使用关键字or来实现“或者”的功能。 select * from emp where sal>1500 or comm is not null; 需求再次发生变化，查询出工资不大于1500（小于或等于1500），同时不可以领取奖金的雇员信息，可想这些雇员的工资一定不怎么高。之前我们使用not关键字对限定条件进行了取反，那么，这里就相当于对限定条件整体取反。我们分析下，工资不大于1500同时不可以领取奖金的反面含义就是工资大于1500同时可以领取奖金，所以，我们这样操作： select * from emp where sal>1500 and comm is not null; 这是原始需求取反后的查询语句，那么为了达到最终的查询效果，我们将限定条件整体取反，即 select * from emp where not(sal>1500 and comm is not null); 在这里，我们通过括号将一系列限定条件包含起来表示一个整体的限定条件。 我们在数学中学过这样的表达式：100<a<200，那么在数据库中如何实现呢？一样的，我们现在要查询出工资在1500之上，但是在3000之下的全部雇员信息，很显然，两个限定条件，同时要满足才行。 select * from emp where sal>1500 and sal<3000; 这里不要异想天开的写成select * from emp where 1500<sal<3000;（）试试吧，绝对会报错。 很简单，数据库软件不支持这样的写法，至少现在的数据库没有谁去支持这样的写法。 在SQL语法中，提供了一种专门指定范围查询的过滤语句：between x and y，相当于a>=x and a<=y,也就是包含了等于的功能，其语法格式如下： select * from emp where sal between 1500 and 3000; 现在我们使用这种范围查询的方式修改上面的语句如下： select * from emp where sal between 1500 and 3000; 好了，我们现在开始使用数据库中非常重要的一种数据类型，日期型。 查询出在1981年雇佣的全部雇员信息，看似很简单的一个查询操作，这样写吗？ select * from emp where hiredate=1981;（） 很显然，有错。hiredate是date类型的，1981看似是一个年份（日期），但是像这样使用，它仅仅是一个数字而已，类型都匹配，怎么进行相等判断呢？继续考虑。我们先这样操作，查询emp表的所有信息， select * from emp; 从查询结果中，我们重点关注hiredate这一列的数据格式是怎么定义的， 20-2月 -81，很显然，我们抽象的表示出来是这样的， 一个月的第几天-几月 -年份的后两位 所以，接下来我们就有思路了，我们就按照这样的日期格式进行限定条件的设置，该如何设置呢？考虑下，查询1981年雇佣的雇员，是不是这个意思，从1981年1月1日到1981年12月31日雇佣的就是我们需要的数据呢？所以，这样操作吧 select * from emp where hiredate between ‘1-1月 -81‘ and ‘31-12月 -81‘; 可以看到，上面的限定条件使用了单引号包含，我们暂且可以将一个日期看做是一个字符串。由上面的范例中我们得到结论：between...and ...除了可以进行数字范围的查询，还可以进行日期范围的查询。 我们再来看下面的例子，查询出姓名是smith的雇员信息，嗯，很简单，这样操作 select * from emp where ename=‘smith‘; 看似没有问题，限定条件也给了，单引号也没有少，可是为什么查询不到记录呢？明明记得emp表中有一个叫做smith的雇员呀？难道被删掉了，好吧，我们不需要在这里耗费时间猜测语句自身的问题了，上面的语句在语法上没有任何问题，我们先看看emp表中有哪些雇员， select * from emp; 可以看到，确实有一个叫做smith的雇员，但是不是smith，而是SMITH，所以，我们忽略了一个很重要的问题，Oracle是对大小写敏感的。所以，更改如下： select * from emp where ename=‘SMITH‘; 现在看一个这样的例子，要求查询出雇员编号是7369,7499,7521的雇员信息。如果按照以前的做法，是这样操作的， select * from emp where empno=7369 or empno=7499 or empno=7521; 执行一下吧，确实没任何问题，查询出指定雇员编号的所有信息。但是SQL语法提供一种更好的解决方法，使用in关键字完成上面的查询，如下： select * from emp where empno in(7369,7499,7521); 总结下in的语法如下： select * from tablename where 列名 in (值1，值2，值3); select * from tablename where 列名 not in (值1，值2，值3); 需要说明的是，in关键字不光可以用在数字上，也可以用在字符串的信息上。看下面的例子 查询出姓名是SMITH、ALLEN、KING的雇员信息 select * from emp where ename in(‘SMITH‘,‘ALLEN‘,‘KING‘); 如果在指定的查询范围中附加了额外的内容，不会影响查询结果。 模糊查询对于提高用户体验是非常好的，对于数据库的模糊查询，我们通过like关键字来实现。首先我们了解下like主要使用的两种通配符： |-“%”-->可以匹配任意长度的内容，包括0个字符； |-“_”-->可以匹配一个长度的内容。 下面我们看这样一个需求，查询所有雇员姓名中第二个字母包含“M”的雇员信息 select * from emp where ename like ‘_M%‘; 说明下，前面的“_”匹配姓名中第一个字母（任意一个字母），“%”匹配M后面出现的0个或者多个字母。 查询出雇员姓名中包含字母M的雇员信息。分析可知，字母M可以出现在姓名的任意位置，如何进行正确的匹配呢？ select * from emp where ename like ‘%M%‘; 这里还是使用%，匹配0个或者多个字母，即可以表示M出现在姓名的任意位置上。 如果我们在使用like查询的时候没有指定查询的关键字，则表示查询内容，即 select * from emp where ename like ‘%%‘; 相当于我们前面看到的 select * from emp; 前面我们遇到了一个这样的需求，查询出在1981年雇佣的所有雇员信息，当时我们采取的是使用范围查询between ... and ...实现的，现在我们使用like同样可以实现 select * from emp where hiredate like ‘%81%‘; 查询工资中包含6的雇员信息 select * from emp where sal like ‘%5%‘; 在操作条件中还可以使用：>、>=、=、<、<=等计算符号 对于不等于符号，有两种方式：<>、!= 现在需要我们查询雇员编号不是7369的雇员信息 select * from emp where empno<>7369; select * from emp where empno!=7369; 1.5、对查询结果进行排序（order by子句） 在查询的时候，我们常常需要对查询结果进行一种排序，以方便我们查看数据，比如以雇员编号排序，以雇员工资排序等。排序的语法是： select *|具体的列名称 from 表名称 where 条件表达式 order by 排序列1,排序列2 asc|desc; asc表示升序，默认排序方式，desc表示降序。 现在要求所有的雇员信息按照工资由低到高的顺序排列 select * from emp order by sal; 在升级开发中，会遇到多列排序的问题，那么，此时，会给order by指定多个排序列。要求查询出10部门的所有雇员信息，查询的信息按照工资由高到低排序，如果工资相等，则按照雇佣日期由早到晚排序。 select * from emp where deptno=10 order by sal desc,hiredate asc; 需要注意的是，排序操作是放在整个SQL语句的最后执行。 1.6、单行函数 在众多的数据库系统中，每个数据库之间唯一不同的最大区别就在于函数的支持上，使用函数可以完成一系列的操作功能。单行函数语法如下： function_name(column|expression,[arg1,arg2...]) 参数说明： function_name：函数名称 columne：数据库表的列名称 expression：字符串或计算表达式 arg1,arg2：在函数中使用参数 单行函数的分类： 字符函数：接收字符输入并且返回字符或数值 数值函数：接收数值输入并返回数值 日期函数：对日期型数据进行操作 转换函数：从一种数据类型转换到另一种数据类型 通用函数：nvl函数，decode函数 字符函数： 专门处理字符的，例如可以将大写字符变为小写，求出字符的长度。 现在我们看一个例子，将小写字母转为大写字母 select upper(‘smith‘) from dual; 在实际中，我们会遇到这样的情况，用户需要查询smith雇员的信息，但是我们数据库表中存放的是SMITH，这时为了方便用户的使用我们将用户输入的雇员姓名字符串转为大写， select * from emp where ename=upper(‘smith‘); 同样，我们也可以使用lower()函数将一个字符串变为小写字母表示， select lower(‘HELLO WORLD‘) from dual; 那么，将字符串的首字母变为大写该如何实现呢？我们使用initcap()函数来完成上面的操作。 select initcap(‘HELLO WOLRD‘) from dual; 在前面的学习中我们知道，在scot账户下的emp表中的雇员姓名采用全大写显示，现在我们需要激昂姓名的首字母变为大写，该如何操作呢？ select initcap(ename) from emp; 我们在前面使用了字符串连接操作符“||”对字符串连接显示，比如： select ‘编号为‘ || empno || ‘的姓名是：‘ || ename from emp; 那么，在字符函数中提供concat()函数实现连接操作。 select concat(‘hello‘,‘world‘) from dual; 上面的范例使用函数实现如下： select concat(‘编号为‘,empno,‘的姓名是：‘,ename);????????? 此种方式不如连接符“||”好使。 在字符函数中可以进行字符串的截取、求出字符串的长度、进行指定内容的替换 select substr(‘hello‘,1,3) 截取字符串，length(‘hello‘) 字符串长度,replace(‘hello‘,‘l‘,‘x‘) 字符串替换 from dual; 通过上面范例的操作，我们需要注意几以下： Oralce中substr()函数的截取点是从0开始还是从1开始；针对这个问题，我们来操作看下，将上面的截取语句改为： select substr(‘hello‘,0,3) 截取字符串 from dual; 由查询结果可以发现，结果一样，也就是说从0和从1的效果是完全一样，这归咎于Oracle的智能化。 如果现在要求显示所有雇员的姓名及姓名的后三个字符，我们知道，每个雇员姓名的字符串长度可能不同，所以我们采取的措施是求出整个字符串的长度在减去2，我们这样操作， select ename,substr(ename,length(ename)-2) from emp; 虽然功能实现了，但是有没有羽化的方案呢？当然是有的，我们分析下，当我们在截取字符串的时候，给定一个负数值是什么效果，对，就是倒着截取，我们采取这种方式优化如下： select ename,substr(ename,-3) from emp; 数值函数： 四舍五入：round() 截断小数位：trunc() 取余（取模）：mod() 执行四舍五入操作,可以指定保留的小数位 select round(789.536) from dual; select round(789.436) from dual; select round(789.436,2) from dual; 可以直接对整数进行四舍五入的进位 select round(789.536,-3) from dual;--1000 select round(789.536,-2) from dual;--800 trunc()函数与round()函数的不同在于，trunc不会保留任何的小数位，而且小数点也不会执行四舍五入的操作，也就是说在使用trunc()函数时，它会将数值从小数点截断，只保留整数部分。 select trunc(789.536) from dual;--789 当然使用trunc()函数也可以设置小数位的保留位数 select trunc(789.536,2) from dual;--789.53 select trunc(789.536,-2) from dual;--700 使用mod()函数可以进行取余的操作 select mod(10,3) from dual;--1 日期函数： 在Oracle中提供了很多余日期相关的函数，包括加减日期等。但是在日期进行加或者减结果的时候有一些规律： 日期-数字=日期 日期+数字=日期 日期-日期=数字（天数的差值） 显示部门10的孤雁进入公司的星期数，要想完成此查询，必须知道当前的日期，在Oralce中可以通过以下的操作求出当前日期，使用sysdate表示 select sysdate from dual; 如何求出星期数呢？使用公式：当前日期-雇佣日期=天数 / 7 = 星期数，所以 select empno,ename,round((sysdate-hiredate)/7) from emp; 在Oracle中提供了以下的日期函数支持： months_between()-->求出给定日期范围的月数 add_months()-->在指定日期上加上指定的月数，求出之后的日期 next_day()-->下一个的今天是哪一个日期 last_day()-->求出给定日期的最后一天日期 查询出所有雇员的编号，姓名，和入职的月数 select empno,ename,round(months_between(sysdate,hiredate)) from emp; 查询出当前日期加上4个月后的日期 select add_months(sysdate,4) from dual; 查询出下一个给定日期数 select next_day(sysdate,‘星期一‘) from dual; 查询给定日期的最后一天，也就是给定日期的月份的最后一天 select last_day(sysdate) from dual; 转换函数： to_char()-->转换成字符串 to_number()-->转换成数字 to_date()-->转换成日期 我们先看看前面做的一个范例，查询所有雇员的雇员编号，姓名和雇佣时间 select empno,ename,hiredate from emp; 但是现在的要求是讲年、月、日进行拆分，此时我们就需要使用to_char()函数进行拆分，拆分的时候必须指定拆分的通配符： 年-->y，年是四位数字，所以使用yyyy表示 月-->m，月是两位数字，所以使用mm表示 日-->d，日是两位数字，所以使用dd表示 select empno,ename,to_char(hiredate,‘yyyy‘) year,to_char(hiredate,‘mm‘) month,to_char(hiredate,‘dd‘) day from emp; 我们还可以使用to_char()进行日期显示的转换功能，Oracle中默认的日期格式是：19-4月 -87，而中国喜欢的格式是：1987-04-19 select empno,ename,to_char(hiredate,‘yyyy-mm-dd‘) from emp; 从显示结果中我们可以看到，对于月份和日的显示中，如果不足10，就会自动补零，哪个0我们成为前导0，如果不希望显示前导0的话，则可以使用fm去掉 select empno,ename,to_char(hiredate,‘fmyyyy-mm-dd‘) from emp; 当然，to_char()也可以用在数字上 查询全部的雇员编号、姓名和工资 select empno,ename,sal from emp; 我们可以看到，当工资数比较大时，是不利于读的，那么，我们可以在数字
热门排行
今日推荐
-
小全能美颜相机高清版
版本：v1.0
大小：61.86MB
日期：2024-10-07
-
水印抹布手机版
版本：v1.0.0
大小：62.86MB
日期：2024-10-07
-
截图大王安卓版
版本：v1.0.0
大小：48.1MB
日期：2024-10-07
-
水印相机实时打卡免费版
版本：v1.0.0
大小：99MB
日期：2024-10-07
-
速悦音乐剪辑官方版
版本：v1.1
大小：71.64MB
日期：2024-10-07
-
满天星漫画免费版
版本：v1.0
大小：1.47MB
日期：2024-10-07
