ORACLE第一天

来源：互联网发布：sim800连接网络流程编辑：程序博客网时间：2024/05/16 15:52

课程

java语言 java基本编程 -> j2EE编程

数据库的能力 sqlserver -> ORACLE

数据结构训练编程思维方式实际问题到计算机问题的转化

linux 熟悉一种新的操作系统

软件工厂学到的知识去做一个项目

课程安排

linux操作系统 4天

数据结构 5天

DOTNET介绍 4天

ORACLE

ORACLE数据库 10天

XML语言 5天

J2EE

JSP/servlet 12天

EJB 3天

struts 4天

软件工厂 2周

找工作的技能

j2EE + XML + ORACLE/SQLSERVER

基础 java html javascript sql

ORACLE数据库

甲骨文英文中意思是神寓神说的话

复杂性

公司野心

1973年

i internet

g grid (网格)

ERP 企业资源计划 Enterprise Resource Plan

MIS 管理信息系统 Management Information System

德国 SAP

ORACLE application 11i

别的数据库

DB2 IBM公司 IBM产品绑定紧密 (windows unix/linux)

sybase sybase公司电信和银行 (windows unix/linux)

sqlserver windows平台

小型

access office套件

mysql PHP(Personal HomePage)(linux) +

mysql(不支持存储过程,触发器等)

学习方法

动手能力

做会的不是书本学会的

找问题的答案：

问同学问老师

从网上找答案

搜索引擎 www.google.com

百度

错误号错误文字放入关键字中

提取关键字多实验

ORACLE安装

ORACLE +“安装”

ORACLE安装和删除

ORACLE9i 9.0.1 空间1.76G

ORACLE9i 9.0.2 空间2.86G

事务处理 -- 增删除修改频繁

数据仓库 -- 查询频繁

GBK --多200个字左右(偏僻字)

GB2312

SETUP.exe 双击

启动ORACLE数据库 OracleServiceACCP 服务多启动几次

OracleOraHome90TNSListener 服务

如果启动不了Listener(监听器)

<1>改注册表

ImagePath D:\oracle\ora90\BIN\TNSLSNR

<2>用命令行启动

cmd -> tnslsnr 最小化

tNSlsnter 监听器如果无法启动

运行cmd 切换到DOS界面敲tnslsnr 窗口不要关闭

<3>D:\oracle\ora90\network\ADMIN

listener.ora 文本文件

HOST=localhost或机器名

存盘

用工具 ORACLE SQL*PLUS验证数据库是否启动

用户名system 密码manager

出现连接到和 SQL>符号说明数据库已经可用了

默认用户:

超级用户: 用户名system/密码manager

用户名sys/密码change_on_install

普通的用户: 用户名scott/密码tiger

注意的问题:安装的源程序放在什么目录下??

<2>数据库的名字不要是中文,8个字符以内

<3>字符集用默认的ZHS16GBK

删除:

在硬盘上的一个隐含安装的目录Programm Files下的ORACLE

操作ORACLE数据库的工具——

ORACLE SQL*PLUS 字符界面 ORACLE自带

sqlplusw.exe windows窗口下的sqlplus

sqlplus.exe DOS下的sqlplus

用户名scott 密码tiger 普通用户

system manager 超级用户

sys change_on_install 超级用户比system权限还要大

SQLPLUS下的常用命令

show user 察看用户

connect 用户名/密码连接到指定用户

connect scott/tiger

select * from tab where tabtype='TABLE'; 察看当前用户下的表

select count(*) from dept; 查询表dept中记录的行数

desc dept 察看表dept的结构

quit/exit 退出

clear screen 清除屏幕

set linesize 200 设置一行显示200个字符

set pagesize 20 设置每页显示20行

spool 文件名 (spool c:\abc.txt) 作日志文件命令

spool off

修改D:\oracle\ora90\sqlplus\admin\glogin.sql文件

可以设置SQLPLUS的环境

set linesize 200

set pagesize 20

ORACLE 企业管理器 (OEM) 图形界面

PL/SQL Developer 第三方软件 ORACLE不带的

SQLPLUS /nolog nolog是不登陆

浏览器使用oracle (isqlplus)

D:\oracle\ora90\Apache\Apache\conf\httpd.conf

修改80端口 -> 8001

http://localhost:8001/isqlplus

ORACLE中字段的数据类型

字符型 char 范围最大2000个字节定长

char(10) '张三' 后添空格6个把10个字节补满 '张三 '

性别 char(2) '男'

varchar2 范围最大4000个字节变长

varchar2(10) '张三' 在数据库中'张三'

大对象字符型大对象 >4000字节最大4G

CLOB (Character Large OBject)

数字 number 范围 10的-38次方到10的38次方

可以表示小数也可以表示整数

number(4) 最大表示4位整数 -9999 到 9999

number(5,2) 表示5位有效数字 2位小数的一个小数 -999.99 到 999.99

日期 date 包含年月日和时分秒 7个字节

图片 blob 二进制大对象图像/声音 4G

如何建表

学生表student

create table student( --学生表

xh number(4), --学号

xm varchar2(10), --姓名

sex char(2), --性别

birthday date, --日期

sal number(7,2) --奖学金

);

班级class

create table class( --班级表

classid number(2), --班级编号

cname varchar2(20) --班级名字

);

添加字段(学生所在班级classid)

alter table student add (classid number(2));

修改字段的长度

alter table student modify (xm varchar2(12)) ;

修改字段的类型(不能有记录的)

alter table student modify (xh varchar2(5));

删除一个字段

alter table student drop column sal;

删除表

drop table student;

表的名字修改

rename student to stu;

字段如何改名字

--先删除

a)alter table student drop column sal;

--再添加

b)alter table student add (salary number(7,2));

如何插入数据

插入数据 insert语句

所有字段都插入

insert into student values ('A001','张三','男','01-5月-05',10);

ORACLE中默认的日期格式'DD-MON-YY' dd 日子(天) mon 月份 yy 2位的年

'09-6月-99' 1999年6月9号

改日期的默认格式

alter session set nls_date_format = 'yyyy-mm-dd';

insert into student values ('A002','MIKE','男','1905-05-06',10);

恢复ORACLE默认格式

alter session set nls_date_format = 'dd-mon-yy';

察看日期的格式

set linesize 1000

select * from nls_session_parameters

where parameter='NLS_DATE_FORMAT';

永久设置日期格式

改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd

部分字段插入

insert into student(xh,xm,sex) values ('A003','JOHN','女');

插入空值

insert into student(xh,xm,sex,birthday) values ('A004','MARTIN','男',null);

修改 update

改一个字段

update student set sex='女' where xh='A001';

改多个字段

update student set sex='男',

birthday='1980-04-01'

where xh='A001';

改为空值 (修改为空时=null)

update student set birthday=null where xh='A001';

把生日为空的人的班级编号改为20(条件中的空是is null / is not null)

update student set classid=20 where birthday is null;

错误的没有达到要求

update student set classid=20

where birthday=null;

不表示空值表示xm是null的字符串

update student set classid=20 where xm='null';

删除 delete

delete from student; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢

drop table student; 删除表的结构和数据

delete from student where xh='A001'; 删除一条记录

truncate table student; 删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

查询 select

select * from student;

select xh,xm,sex from student;

select * from student where xh like 'A%1'; %任意多个字符

select * from student where xh like 'A__1'; _1个字符

select * from student where xh like '%A%'; select * from student where xh like 'A%';

select * from student where xh like '%A';

select * from student where xh = 'A%';

select * from student

order by birthday ; 升序 (order by birthday asc;)

select * from student

order by birthday desc; --降序

select * from student

order by birthday desc,xh asc; --按birthday 降序按xh升序(asc/默认)

select * from student

where sex='女' or birthday='1999-02-01';

select * from student

where sex='女' and birthday='1999-02-01';

select * from student

where salary > 20 and xh <> 'B002'; (!=)

ORALCE的函数

单行函数返回值只有一个

分组函数返回值是多条记录

group by

sum

avg

单行函数

字符函数

concat 连接 ||

<1>显示dname和loc中间用-分隔

select deptno,dname||'----'||loc from dept;

dual哑元表没有表需要查询的时候可以用它

select 'Hello World' from dual;

select 1+1 from dual;

查询系统时间

select sysdate from dual;

<2> initcap 首字母大写

select ename,initcap(ename) from emp;

<3> lower 转换为小写字符

select ename,lower(ename) from emp;

<4> upper 转换为大写

update dept set loc=lower(loc);

update dept set loc=upper(loc);

<5> LPAD 左填充

select deptno,lpad(dname,10,' '),loc from dept;

<6> RPAD 右填充

<7> LTRIM 去除左边的空格

RTRIM 去除右边的空格

ALLTRIM 去除两边的空格

<8>replace 替换

translate 转换

select ename,replace(ename,'S','s') from emp;

用's'去替换ename中的'S'

select ename,translate(ename,'S','a') from emp;

<9> ASCII 求ASC码

chr asc码变字符

select ascii('A') from dual;

select chr(97) from dual;

select 'Hello'||chr(9)||'World' from dual;

'\t' ascii码是 9

'\n' ascii码是 10

select 'Hello'||'\t'||'World' from dual;

<10> substr 字符截取函数

select ename,substr(ename,1,3) from emp;

从第1个位置开始显示3个字符

select ename,substr(ename,4) from emp;

从第4个位置开始显示后面所有的字符

<11> instr 测试字符串出现的位置

select ename,instr(ename,'S') from emp;

'S'第1次出现的位置

select ename,instr(ename,'T',1,2) from emp;

从第1个位置开始测试'T'第2次出现的位置

<12> length 字符串的长度

select ename,length(ename) from emp;

日期和时间函数

<1> sysdate 系统时间

select sysdate from dual;

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

select to_char(sysdate,'DDD') from dual

select to_char(sysdate,'D') from dual

select to_char(sysdate,'DAY') from dual

select to_char(sysdate,'yyyy-mm-dd') from dual;

select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

select '''' from dual;

select to_char(sysdate,'SSSSS') from dual;

--从今天零点以后的秒数

<2> ADD_MONTHS 添加月份得到一个新的日期

select add_months(sysdate,1) from dual;

select add_months(sysdate,-1) from dual;

select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;

select add_months(sysdate,12) from dual;

一年以后的今天

select add_months(sysdate,-12) from dual;

一年以前的今天

trunc(sysdate) 截取年月日

select sysdate+2 from dual;

数字代表的是天数

两个日期之间的差值代表天数

<3> last_day 某月的最后一天

select last_day(sysdate) from dual;

select add_months(last_day(sysdate)+3,-1) from dual;

本月第3天的日期

<4> months_between 两个日期之间的月数

select months_between(sysdate,'2005-02-01') from dual;

方向 sysdate - '2005-02-01'

select months_between('2005-02-01',sysdate) from dual;

转换函数

to_char 把日期或数字类型变为字符串

select to_char(sysdate,'hh24:mi:ss') from dual;

select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

select sal,to_char(sal,'L9,999') from emp;

L本地货币

to_number 把字符串变成数字

select to_number('19990801') from dual;

to_date 把字符串变成日期

select to_date('19800101','yyyymmdd') from dual;

select to_char(to_date('19800101','yyyymmdd'),

'yyyy"年"mm"月"dd"日"') from dual;

数学函数

ceil(x) 不小于x的最小整数

ceil（12.4) 13

ceil(-12.4) -12

floor(x) 不大于x的最大整数

floor(12.5) 12

floor(-12.4) -13

round(x) 四舍五入

round(12.5) 13

round(12.456,2) 12.46

trunc(x) 舍去尾数

trunc(12.5) 12

trunc(12.456,2) 12.45

舍去日期的小时部分

select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;

mod(x,n) x除以n以后的余数

mod(5,2) 1

mod(4,2) 0

power(x,y) x的y次方

select power(3,3) from dual;

混合函数

求最大值

select greatest(100,90,80,101,01,19) from dual;

求最小值

select least(100,0,-9,10) from dual;

空值转换函数 nvl(comm,0) 字段为空值那么就返回0 否则返回本身

select comm,nvl(comm,0) from emp;

comm 类型和值的类型是一致的

复杂的函数

decode 选择结构 (if ... elseif .... elesif ... else结构)

要求:

sal=800 显示低工资

sal=3000 正常工资

sal=5000 高工资

只能做等值比较

select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')

from emp;

表示如下的if else 结构

if sal=800 then

'低工资'

else if sal =3000 then

'正常工资'

else if sal = 5000 then

'高工资'

else

'没判断'

end if

sal > 800 sal -800 > 0

判断正负

sign(x) x是正 1

x是负 -1

x是0 0

select sign(-5) from dual;

如何做大于小于的比较？？？？

sal<1000 显示低工资 sal-1000<0 sign(sal-1000) = -1

1000<=sal<=3000 正常工资

3000<sal<=5000 高工资

select sal,decode(

sign(sal-1000),-1,'低工资',

decode(sign(sal-3000),-1,'正常工资',

0,'正常工资',1,

decode(sign(sal-5000),-1,'高工资','高工资')

)) as 工资状态 from emp;

一般的情况 decode(x,y1,z1,y2,z2,z3)

if x= y1 then

else if x = y2 then

else

end if

分组函数返回值是多条记录或计算后的结果

group by

sum

avg

<1> 计算记录的条数 count

select count(*) from emp;

select count(1) from emp;

select count(comm) from emp; 字段上count 会忽略空值

comm不为空值的记录的条数

统计emp表中不同工作的个数 ????

select count(distinct job) from emp;

select distinct job from emp;

select distinct job,empno from emp;

select job,empno from emp;

得到的效果是一样的,distinct 是消去重复行

不是消去重复的列

<2>group by 分组统计

--在没有分组函数的时候

--相当于distinct 的功能

select job from emp group by job;

select distinct job from emp;

--有分组函数的时候

--分组统计的功能

统计每种工作的工资总额是多少??

select job,sum(sal) from emp

group by job; --行之间的数据相加

select sum(sal) from emp; --公司的工资总额

统计每种工作的平均工资是多少??

select job,avg(sal) from emp

group by job;

select avg(saL) from emp; --整个公司的平均工资

显示平均工资>2000的工作?？？

<1>统计每种工作的平均工资是多少

<2>塞选出平均工资>2000的工作

从分组的结果中筛选 having

select job,avg(sal) from emp

group by job

having avg(sal) > 2000;

group by 经常和having搭配来筛选

计算工资在2000以上的各种工作的平均工资？？？？

select job,avg(sal) from emp

where sal > 2000

group by job

having avg(sal) > 3000;

一般group by 和 having搭配

表示对分组后的结果的筛选

where子句 --- 用于对表中数据的筛选

<3> max min

select max(sal) from emp;

公司的最高工资

select min(sal) from emp ;

公司的最低工资

找每个部门的最高和最低的工资??

select deptno,max(sal),min(sal) from emp

group by deptno;

找每个工作的最高和最低的工资??

select job,max(sal),min(sal) from emp

group by job;

找每个部门中每种工作的最高和最低的工资??

select deptno,job,max(sal),min(sal)

from emp

group by deptno,job;

select max(sal),min(sal)

from emp

group by deptno,job;

单个字段如果没有被分组函数所包含，

而其他字段又是分组函数的话

一定要把这个字段放到group by中去

<4>关联查询

多张表,而表与表之间是有联系的

是通过字段中的数据的内在联系来发生

而不是靠相同的字段名来联系的或者是否有主外键的联系是没有关系的

select dname,ename from emp,dept;

笛卡尔积 (无意义的)

--当2个表作关联查询的时候一定要写关联的条件

--N个表关联条件一定有N-1个

select dname,ename from mydept,myemp

where mydept.no = myemp.deptno;

多表查询的时候一定要有关联的条件

--使用的表的全名

select dname,ename from emp,dept

where emp.deptno = dept.deptno ;

--使用表的别名

select dname,ename,a.deptno from emp a,dept b

where a.deptno = b.deptno and a.deptno = 10;

--等值连接(内连接-两个表的数据作匹配a.deptno = b.deptno )

select dname,ename,a.deptno from

emp a inner join dept b

on a.deptno = b.deptno;

where a.deptno = 10;

--on写连接条件的

--where中写别的条件

--使用where/on

select dname,ename,a.deptno from emp a,dept b

where a.deptno = b.deptno and a.deptno=10;

--on中写连接条件

--where中写其他的条件

select dname,ename,a.deptno from

emp a inner join dept b

on a.deptno = b.deptno

where a.deptno = 10 ;

--外连接

左外连接右外连接全外连接

(+)写法只有在ORACLE中有效

select dname,ename,b.deptno

from emp a,dept b

where a.deptno(+) = b.deptno;

--标准写法

select dname,ename,b.deptno

from emp a right outer join dept b

on a.deptno = b.deptno;

select dname,ename,b.deptno

from emp a,dept b

where a.deptno = b.deptno(+);

--标准写法

select dname,ename,b.deptno

from emp a left outer join dept b

on a.deptno = b.deptno;

--标准写法(全外联)

select dname,ename,b.deptno

from emp a full outer join dept b

on a.deptno = b.deptno;

--自连接

select a.ename as 员工姓名,b.ename as 经理名字 from emp a,emp b

where a.mgr = b.empno(+);

a.empno = b.mgr ???????

<5>子查询

在select语句中嵌套了另一个select语句

1)where 子句中嵌套子查询

2)用子查询的结果作为字段来出现

--1)where 子句中嵌套子查询,执行顺序是

先执行子查询再执行主查询

找出工资高于公司平均工资的所有员工??

select * from emp where sal+nvl(comm,0) > (select avg(sal+nvl(comm,0)) from emp);

高于部门30中员工最高工资的其他员工???

select * from emp where sal+nvl(comm,0) > all (select sal+nvl(comm,0) from emp

where deptno = 30);

低于部门30中员工工资的其他员工???

select * from emp where sal+nvl(comm,0) < all (select sal+nvl(comm,0) from emp

where deptno = 30);

select * from emp where sal+nvl(comm,0) < any (select sal+nvl(comm,0) from emp

where deptno = 30);

--2）用子查询的结果作为字段来出现

先执行主查询再执行子查询

<1>找员工姓名和直接上级的名字

select ename as 员工姓名,(select ename from emp where empno = a.mgr) as 经理姓名

from emp a;

<2>显示部门名称和人数

select dname,(select count(*) from emp where deptno=a.deptno) as rs from dept a;

<3>显示每个部门的最高工资的员工

select * from emp a where (deptno, sal) in ( select deptno,max(sal) from emp group by deptno);

select a.* from emp a,( select deptno,max(sal) as msal from emp group by deptno) c where a.deptno = c.deptno and

a.sal = c.msal;

--最大值和最小值的比较转化为人数的比较

select * from emp a where (select count(*) from

emp where deptno = a.deptno and

sal > a.sal) = 0 and a.deptno is not null;

<4>显示每个部门的工资前2名的员工

select * from emp a where (select count(*) from

emp where deptno = a.deptno and

sal > a.sal) <=1 and a.deptno is not null;

<6> 层次查询

--level 伪列层次查询的时候可以使用的层的编号

select lpad('+',level,' ')||ename from emp

connect by prior empno = mgr --父子关系父结点中的empno = 子节点中的mgr

start with mgr is null;--从 mgr is null的节点开始遍历

select lpad('+',level,' ')||ename from emp

connect by prior empno = mgr

start with ename = 'BLAKE';

<7> TOP 前几行 (sqlserver)

rownum (oracle伪列)

--emp表的前2行

--rownum必须使用<=的关系比较运算符

select * from emp where rownum <= 2;

select top 2 * from emp; --sqlserver的写法

select * from emp where rownum = 1;

--公司工资最高的2个人

/*select * from emp

where rownum <= 2

order by sal desc;*/ 错误的

select * from (select * from emp order by sal desc)

where rownum <= 2;

--分页查询

--每页显示4行一共有14行记录

第1页第1-4行

第2页第5-8行

第3页第9-12行

第4页第13-14行

--希望每次页面显示的时候都只查询回需要的记录

select * from (select rownum as num,emp.* from emp)

where num >= 9 and num <= 12;

select * from (select rownum as num,emp.* from emp)

where num >= 13 and num <= 14;

<例子>

建立表如下:

学生基本信息表

CREATE Student(

[Studentid][Int]IDENTITY(1,1)NOT NULL primary key,--主键

[StudentName][char]NOT NULL

)

课程信息表

CREATE Subject(

[SubjectID][char]NOT NULL primary key, --主键

[SubjectName][char]NOT NULL

)

成绩表

CREATE Grade(

[Studentid][Int]NOT NULL, --联合主键

[SubjectID][char]NOT NULL, --联合主键

[Grade] [INT]NOT NULL,

primary key (studentid,subjectid)

)

1.将建表命令改为ORACLE语句在ORACLE中建表

create table student( --学生表

studentid number(3) primary key, --学生编号

studentname varchar2(20) --学生的姓名

);

create table subject( --课程表

subjectid char(3) primary key, --课程编号

subjectname varchar2(20) --课程的名字

);

create table grade( --分数表

studentid number(3) references student(studentid), --学生id

subjectid char(3) references subject(subjectid), --课程id

mark number(3), --分数

primary key (studentid,subjectid) --联合主键

);

insert into student values (101,'张三');

insert into student values (102,'李云');

insert into student values (103,'未');

insert into subject values ('A01','C++');

insert into subject values ('A02','ASP');

insert into subject values ('A03','JAVA');

insert into grade values (101,'A01',59);

insert into grade values (101,'A02',72);

insert into grade values (101,'A03',90);

insert into grade values (102,'A01',75);

insert into grade values (102,'A02',91);

insert into grade values (103,'A01',71);

2.作如下4题

第一问：查询出以下信息

学号学生姓名课程名称成绩（要全部学生信息）

关联查询 (多张表的)

别名

select a.studentid as "学号",studentname "学生姓名",

subjectname "课程名称",mark "成绩"

from student a , subject b , grade c

where a.studentid = c.studentid

and b.subjectid = c.subjectid;

[select a.studentid "学号",studentname "学生姓名",

subjectname "课程名称",mark "成绩"

from student a , subject b , grade c] 笛卡尔积

3 * 3 * 6 = 54;

第二问：查询出以下信息

学号学生姓名课程名称成绩（只显示每科最高分）

select a.studentid "学号",studentname "学生姓名",

subjectname "课程名称",mark "成绩"

from student a,subject b,grade c

where a.studentid = c.studentid

and b.subjectid = c.subjectid

and (subjectname,mark)

in (select subjectname "课程名称",max(mark) "成绩"

from student a,subject b,grade c

where a.studentid = c.studentid

and b.subjectid = c.subjectid

group by subjectname)

(最高分---分数比我高的学生的人数=0)

select a.studentid "学号",studentname "学生姓名",

subjectname "课程名称",mark "成绩"

from student a,subject b,grade c

where a.studentid = c.studentid

and b.subjectid = c.subjectid

and (select count(*) from grade

where subjectid = b.subjectid and

mark > c.mark) = 0

第三问：查询出以下信息

学号学生姓名课程名称成绩（成绩大于60时的显示及格，小于60时的显示不及格）

select a.studentid "学号",studentname "学生姓名",

subjectname "课程名称",

decode(sign(mark-60),-1,'不及格','及格') "成绩"

from student a,subject b,grade c

where a.studentid = c.studentid

and b.subjectid = c.subjectid

第四问：查询出以下信息

学号学生姓名（查询出课程超过1门以上学生的信息）

select a.studentid "学号",studentname "学生姓名",

count(subjectname)

from student a , subject b , grade c

where a.studentid = c.studentid

and b.subjectid = c.subjectid

group by a.studentid,studentname

having count(subjectname) >= 2

-- select * from emp where rownum > 2; 错误的