02第二章 Oracle SQL查询和函数

第二章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、LOB

a.字符包括

Char 存储2000个以下的字符

varchar2 存储4000个以下的字符

long 存储2GB以下的字符

b.数值类型number

c.日期类型date、timestamp

d.大对象

e.rowid和rownum伪列

4.2数据表

4.2.1创建表

--建表

-- Create table

create 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,'135********');

insert into student values('bbb',18,'135********');

insert into student values('ccc',18,'135********');

insert into student values('ddd',18,'135********');

--查询

select * from student

--删除

delete from student where name='DDD'

truncate table t_temp;

可以根据查询的记录结果来重新创建一个新表

create table emp1 as select * from emp

4.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='DDD'

truncate table t_temp;

4.4SQL查询语句

4.4.1常用的SQL

--示例数据库查询

--查询销售岗位的，薪金在1200以上的人员

select * from emp where job='SALESMAN' and sal>1200

--查询员工表中姓名带有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 comm>sal;

--找出部门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.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 =7839connect by prior empno=mgr

oracle中的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 = 1then

1

else

end as a1,

case

when fid = 2then

2

else

end as a2,

case

when fid = 3then

3

else

end as a3

from rp_asset_facctattr) 纵向变横向

4.5数据库函数

4.5.1单行函数

4.5.1.1日期函数

4.5.1.2数字函数

4.5.1.3字符函数

4.5.1.4转换函数

To_char select to_char(sysdate,'yyyy') from dual;

to_date select to_date('2001-01-03','yyyy-mm-dd') from dual YYYY、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 dual

4.5.1.5其他函数

Nvl select nvl(null,1) from dual

nvl2 select nvl2(null,null,1) from dual

nullif

NULL指的是空值，或者非法值。

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，不等返回expr1

4.5.1.6集合运算函数

Union 排除重复的记录

Union all 不会排除重复的记录

INTERSECT 交集，2个集合集中相同的记录

MINUS 割集, 一个集合减去另一个集合中相同的记录

4.5.2分组函数

Avg,min,max,sum,count

Group 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(order by sal) idx,empno,ename,sal,deptno from emp;

Dense_rank:具有相等值的行排位相同，序号是连续的

select dense_rank() over(order by sal) idx,empno,ename,sal,deptno from emp;

五、作业

1.创建表，产品表包含产品id,产品名称，产品价格，生产日期，表名和字段名不准用汉字，

创建主键约束，并插入10条示例数据。

2.根据scott账户下提供的表，按照下列要求编写SQL查询，掌握SQL查询和函数的使用--示例数据库查询

--查询销售岗位的，薪金在1200以上的人员

--查询员工表中姓名带有S的人员

--查询一段雇佣时期的人员

--选择部门30中的雇员

--列出所有办事员的姓名、编号和部门

--找出佣金高于薪金的雇员

--找出部门10中所有经理和部门20中所有办事员的详细资料

--找出部门10中所有经理、部门20中所有办事员以及既不时经理又不时办事员但其薪金大于或等于2000的所有雇员的详细资料

--找出各月最后一天受雇的所有雇员

--找出早于30年之前受雇的雇员

--显示只有首字母大写的所有雇员的姓名

--显示正好为15个字符的雇员姓名

--显示不带有R的雇员姓名

--显示所有雇员的姓名的前三个字符

--显示所有雇员的姓名，用a替换所有的“A”

--显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年份的项目排在前面

--找出在（任何年份的）2月受聘的所有雇员

--对于每个雇员，显示其加入公司的天数

--列出至少有一个雇员的所有部门

--列出薪金比“SMITH”多的所有雇员

--列出所有雇员的姓名及其上级的姓名

--列出入职日期早于其直接上级的所有雇员

--列出所有“CLERK”（办事员）的姓名及其部门名称

--列出各种类别的最低薪金