02第二章 Oracle SQL查询和函数.doc

第二章 oracle sql查询和函数一、目标1. oracle数据类型、运算符2. 创建表、创建约束3. 基本的增删改查4. Sql查询和函数二、知识点1. 数据类型和运算符2. 创建表、创建约束，删除表DDL语句3. 数据的增删改查基本操作，DML语句4. Sql查询，子查询、联合查询5. 函数的分类和使用三、难点1数据类型2.sql查询语句3.函数的分类和使用四、内容讲解4.1 Oracle的数据类型数据类型分为数值、字符、日期时间、RAW/LONG、LOBa. 字符包括Char 存储2000个以下的字符varchar2 存储4000个以下的字符long存储2GB以下的字符b.数值类型 numberc.日期类型 date、timestampd.大对象e.rowid和rownum伪列4.2数据表4.2.1创建表-建表- Create tablecreate table STUDENT( NAME VARCHAR2(20) not null, AGE NUMBER(10), TEL VARCHAR2(1000);- Create/Recreate primary, unique and foreign key constraints alter table student add constraint PK_STUDNET primary key (NAME);-插入记录insert into student values(aaa,18;insert into student values(bbb,18;insert into student values(ccc,18;insert into student values(ddd,18;-查询select * from student-删除delete from student where name=DDDtruncate table t_temp;可以根据查询的记录结果来重新创建一个新表create table emp1 as select * from emp4.2.2修改表结构-增加列alter table T_TEMP add newcol number;-修改列名alter table T_TEMP rename column newcol to dept;alter table T_TEMP modify(dept) number(10);-删除列Alter table T_TEMP drop dept;4.2.3创建约束-主键约束alter table student add constraint PK_STUDNET primary key (NAME);-外键约束alter table STUDENT add constraint FK_tel foreign key (TEL) references emp1 (列名);-唯一性约束alter table STUDENT add constraint unt unique (TEL);4.2.4删除表-删除表Drop table student;4.3基本操作sql语句-一般查询select * from student;select * from student for update;-插入Insert into student values();INSERT INTO (cloumn_list) SELECT column_names FROM ;-修改Update student set name=zhangshan where name=aaa;-删除delete from student where name=DDDtruncate table t_temp;4.4 SQL查询语句4.4.1常用的SQL-示例数据库查询-查询销售岗位的，薪金在1200以上的人员select * from emp where job=SALESMAN and sal1200-查询员工表中姓名带有S的人员select * from emp where ename like %S%-查询一段雇佣时期的人员select * from emp where hiredate between 01-6月-1981 and 31-12月-1981-选择部门30中的雇员Select * from emp where deptno=30-列出所有办事员的姓名、编号和部门select a.ename,a.empno,b.dname from emp a,dept b where a.deptno=b.deptno-找出佣金高于薪金的雇员select * from emp where commsal;-找出部门10中所有经理和部门20中所有办事员的详细资料select * from emp where (deptno=10 and job=MANAGER) or (deptno=20 and job=CLERK)；-找出部门10中所有经理、部门20中所有办事员以及既不时经理又不时办事员但其薪金大于或等于2000的所有雇员的详细资料select * from emp where (deptno=10 and job=MANAGER) or (deptno=20 and job=CLERK) or (deptno=20 and job not in(MANAGER,CLERK) and sal=2000)-找出各月最后一天受雇的所有雇员select * from emp where last_day(hiredate)=hiredate-找出早于30年之前受雇的雇员select * from emp months_between(sysdate,hiredate)/12 30-显示只有首字母大写的所有雇员的姓名select * from emp where substr(ename,0,1)=upper(substr(ename,0,1)-显示正好为15个字符的雇员姓名select * from emp where length(ename)15-显示不带有R的雇员姓名select * from emp where ename not like %R%-显示所有雇员的姓名的前三个字符select substr(ename,0,3) from emp-显示所有雇员的姓名，用a替换所有的“A”select replace(ename,A,a) from emp;-显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年份的项目排在前面select to_char(hiredate,MM)month,to_char(hiredate,YYYY)year from emp order by month,year-找出在（任何年份的）2月受聘的所有雇员select * from emp where to_char(hiredate,MM)=2-对于每个雇员，显示其加入公司的天数select ename,round(sysdate-hiredate,0) from emp-列出至少有一个雇员的所有部门select count(*),deptno from emp group by deptno having count(*)1-列出薪金比“SMITH”多的所有雇员select * from emp where sal(select sal from emp where ename= SMITH )-列出所有雇员的姓名及其上级的姓名select a.ename,b.ename parentname from emp a,emp b where a.mgr=b.empno;-列出入职日期早于其直接上级的所有雇员select a.ename,a.hiredate,b.ename,b.hiredate parentname from emp a,emp b where a.mgr=b.empno and a.hiredateb.hiredate-列出所有“CLERK”（办事员）的姓名及其部门名称select emp.empno,emp.ename,dept.dname from emp,dept where job=CLERK and emp.deptno=dept.deptno-列出各种类别的最低薪金4.4.2其他(一般了解)select * from emp start with empno =7839 connect by prior empno=mgroracle中的select语句可以用START WITH.CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法select . from where start with connect by ：过滤条件，用于对返回的所有记录进行过滤。：查询结果重起始根结点的限定条件。：连接条件如果connect by prior中的prior被省略，则查询将不进行深层递归。select rownum,rowid,empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;select a1, a2,a3 from (select case when fid = 1 then 1 else 0 end as a1, case when fid = 2 then 2 else 0 end as a2, case when fid = 3 then 3 else 0 end as a3 from rp_asset_facctattr)纵向变横向4.5 数据库函数4.5.1单行函数4.5.1.1日期函数函数名说明举例ADD_MONTHS(date,day)查询一个时间增加或减少月份的时间Day可以使负数select add_months(sysdate,2) from dualMONTHS_BETWEEN(date,date)查询2个时间相差的月份数select months_between(sysdate,add_months(sysdate,-2) from dualLAST_DAY(date)活动本月最后一天select last_day(sysdate) from dualROUND(date)保留日期，去掉时分秒select ROUND(sysdate) from dualNEXT_DAY(date,day)一周内的某一天,day不可为负数，只可以使17之间的数字select next_day(sysdate,7) from dualTRUNC()去掉时分秒EXTRACT截取一个时间格式中的特定部分YEAR|MONTH|DAY|HOUR|MINUTE|SECONDselect extract(year from 2012-12-03) from dualselect extract(month from sysdate) from dual4.5.1.2数字函数 函数 输入 输出 Abs(n) Select abs(-15) from dual; 15Ceil(n) Select ceil(44.778) from dual; 45Cos(n) Select cos(180) from dual; -.5984601 Cosh(n) Select cosh(0) from dual; 1Floor(n) Select floor(100.2) from dual; 100Power(m,n) Select power(4,2) from dual; 16 Mod(m,n) Select mod(10,3) from dual; 1Round(m,n) Select round(100.256,2) from dual; 100.26 Trunc(m,n) Select trunc(100.256,2) from dual; 100.25 Sqrt(n) Select sqrt(4) from dual; 2 Sign(n) Select sign(-30) from dual; -1 4.5.1.3字符函数 函数 输入 输出 Initcap(char) Select initcap(hello) from dual;Hello Lower(char) Select lower(FUN) from dual;fun Upper(char) Select upper(sun) from dual;SUN Ltrim(char,set) Select ltrim( xyzadams,xyz) from dual; adamsRtrim(char,set) Select rtrim(xyzadams,ams) from dual; xyzad Translate(char, from, to) Select translate(jack,j ,b) from dual; back Replace(char, searchstring,rep string) Select replace(jack and jue ,j,bl) from dual;black and blue Instr (char, m, n) Select instr (worldwide,d) from dual; 5 Substr (char, m, n) Select substr(abcdefg,3,2) from dual; cd Concat (expr1, expr2) Select concat (Hello, world) from dual; Hello world4.5.1.4转换函数To_char select to_char(sysdate,yyyy) from dual;to_date select to_date(2001-01-03,yyyy-mm-dd) from dualYYYY、YYY、YY 分别代表4位、3位、2位的数字年YEAR 年的拼写 MM 数字月 MONTH 月的全拼 MON 月的缩写 DD 数字日 DAY 星期的全拼 DY 星期的缩写 AM 表示上午或者下午 HH24、HH12 12小时制或24小时制 MI 分钟 SS 秒钟 SP 数字的拼写 TH 数字的序数词to_number() select to_number(3) from dual4.5.1.5其他函数Nvl select nvl(null,1) from dualnvl2 select nvl2(null,null,1) from dualnullifNULL指的是空值，或者非法值。NVL (expr1, expr2)-expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致NVL2 (expr1, expr2, expr3) -expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型NULLIF (expr1, expr2) -相等返回NULL，不等返回expr14.5.1.6集合运算函数Union 排除重复的记录Union all 不会排除重复的记录INTERSECT 交集，2个集合集中相同的记录MINUS 割集, 一个集合减去另一个集合中相同的记录4.5.2分组函数Avg,min,max,sum,countGroup by 和 having子句4.5.3分析函数Row_number: 返回连续的排位，不论值是否相等select rownum,row_number() over(order by rownum) row_number from emp;Select row_number() over(order by sal) as empindex,empno,ename,sal,deptno from emp;Rank: 具有相等值的行排位相同，序数随后跳跃select rank() over(ord