欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle数据库服务器01

程序员文章站 2022-03-19 20:54:53
【以下操作,必须以超级管理员身份登录,才能修改】 oracle安装后,会默认生成很多个用户 以超级管理员身份登录,请注意,其中的空格符: 【 sys是一个超级管理员,有最大的...

【以下操作,必须以超级管理员身份登录,才能修改】

oracle安装后,会默认生成很多个用户

以超级管理员身份登录,请注意,其中的空格符:

【 sys是一个超级管理员,有最大的权限,dba是角色名,/杠二边是用户名和密码,以dba角色名进入,无需用户名和密码 】

sqlplus / as sysdba

例如:C:\Documents and Settings\Administrator>sqlplus / as sysdba

解锁普通用户:

alter user 用户名 account unlock;

例如:SQL> alter user scott account unlock;

修改普通用户密码,密码以英文字母开头

alter user 用户名 identified by 新密码【密码以字母开头】;

例如:SQL> alter user scott identified by tiger;

一)oracle概述

(1)了解一些关于数据库的概念

数据:在数据库领域看来,数据是存储的基本单位,包含文本,图片,视频,音频

数据库:就是数据仓库,存储数据的地方,特指计算机设备中的硬盘,以二进制压缩文本的形式存放

该文件不能直接操作,必须由各数据库公司提供的工具方可操作,该文件的格式是每个数据库公司内部

定义的,不是统一规则

数据库对象:在Oracle中,例如:表,视图,索引,函数,过程,触发器。。。

关系型数据库:简单的说,以行列结构的形式,将数据库中的信息表示出来的对象,即二维表

常见流行的关系型数据库:Oracle&MySQL/Oracle-->DB2/IBM--->SQLServer/Microsoft-->。。。

(2)了解oracle11g背景

(3)oracle数据库服务器由二部份组成

(A)实例:理解为对象,看不见的

(B)数据库:理解为类,看得见的,E:\app\Administrator\oradata\orcl\*.DBF

(4)oracle服务器与orcl数据库的关系

一个oracle数据库服务器中包括多个数据库,例如:orcl,orm,oa,bbs,tax,erp等等

在E:\oracleDB\oradata\目录下,有多少个文件夹,就有多少个数据库,例如:orcl文件夹=orcl数据库

我们向数据库中存储的所有数据库,最终都会存放在对应库的*.DBF文件中,以二进制压缩形式存放

在oracle服务器中创建数据库:参见《在oracle服务器中创建数据库.JPG》

注意:我们在安装oracle时,已经创建好了一个数据库,默认名叫orcl,除非你当时改了数据库名字

(5)sqlplus和sqldeveloper,orcl实例,orcl数据库之间的关系

sqlplus是oracle11g自带的一个客户端黑屏界面工具,该工具可以连接到某个数据库的实例上,从而操作数据库

sqldeveloper是oracle11g自带的一个客户端彩屏界面工具,该工具可以连接到某个数据库的实例上,从而操作数据库

如果你觉得这二款客户端工具不喜欢,可以上网下载第三方的客户端工具

(6)理解失败转移和负载平衡概念【今天最后再看】

失败转移:一个群集中的某个oracle服务器坏掉,应该让该台oracle服务器上的用户转移到其它的几台oracle服务器上

这个过程对用户来说,无需知道

负载平衡:多个用户来并发访问时,集群内的oracle服务器共同承担用户并发访问的压力,但不一定是平均分配

上述二个概念,不光出现在数据库领域,也能出现在WEB服务器领域

(7)以sys超级用户名,dba角色,即超级管理员身份解锁scott方案/用户,并为scott设置一个密码为tiger

解锁用户:alter user scott/hr account unlock;

设置密码:alter user scott/hr identified by tiger/lion;

普通用户scott

密码tiger

(8)使用客户端sqlplus工具进入与退出orcl数据库

------以超级管管理员角色进入

c:/>sqlplus / as sysdba

sql>exit

------以普通用户进入

c:/>sqlplus scott/tiger

sql>exit

声明:我们以普通用户的身份进入

二)JavaEE工程师,oracleSQL和oracle的关系

(1)第四代语言:SQL【结构化查询语言,面向关系的】

第一代:机器语言

第二代:汇编

第三代:C/C++/C#/Java/VB/...

第四代:SQL

将来。。。

(2)SQL92/【99】标准的四大分类

(A)DML(数据操纵语言):select,insert,update,delete

(B)DDL(数据定义语言):create table,alter table,drop table,truncate table 。。。

(C)DCL(数据控制语言):grant 权限 to scott,revoke 权限 from scott 。。。

(D)TCL(事务控制语言):commit,rollback,rollback to savepoint 。。。

(3)oracleSQL与SQL92/99的关系

SQL92/99标准,访问任何关系型数据库的标准

oracleSQL语言,只访问Oracle数据库服务器的专用语言

(4)Java技术和oracleSQL的关系

JDBC-->使用OracleSQL语法-->Oracle服务器--->orcl数据库-->表-->记录

Hibernate-->使用OracleSQL语法-->Oracle服务器

MyBatis-->使用OracleSQL语法-->Oracle服务器

(5)声明:我们这四天时间,主要以在工作中【JavaEE工程师】角度,操作数据库【常用】功能

-------------------------------------------------------------------------------------准备篇

/*

--以下代码是对emp表进行显示做设置

col empno for 9999;

col ename for a10;

col job for a10;

col mgr for 9999;

col hiredate for a12;

col sal for 9999;

col comm for 9999;

col deptno for 99;

set pagesize 20;

col tname for a20;

set pagesize 80;

--创建新表new_emp,复制emp表中的结构和数据到new_emp表中

create table copy_emp

as

select * from emp;

create table copy_copy_emp

as

select * from emp;

*/

启动如下二个服务:【OracleServiceORCL(设置为手动启动)和OracleOraDb11g_home1TNSListener(设置为自动启动端口1521)】

类似于MySQL中的:use 数据库名,即use orcl;

即启动当前数据库名:orcl

-------------------------------------------------------------------------------------select

使用password命令,为scott用户名修改新密码,以字母开头,但提倡使用tiger

password

旧口令:tiger

新口令:abc123

再次输入新口令:abc123

退出sqlplus工具

exit

查询当前用户是谁

show user;

查询scott用户下的所有对象,使用tab表,tab表每个用户都有

select * from tab;

设置显示的列宽(字符型varchar2、日期型date),10个宽度位,a表示字符型,大小写均可

column ename format a12;

column hiredate format a10;

设置显示的列宽(数值型number),9表示数字型,一个9表示一个数字位,四个9表示四个数字位,只能用9

column empno format 9999;

column mgr format 9999;

column sal format 9999;

column comm format 9999;

column deptno format 9999;

设置一页显示80个条记录的高度

set pagesize 80;

使用/杠,执行最近一次的SQL语句

/

清屏,属于SQL*PLUS工具中的命令

host cls;

查询emp表的结构

desc emp;

查询emp表的所有内容,*号表示通配符,表示该表中的所有字段,但*号不能和具体字段一起使用

select * from emp;

select empno,ename,sal,deptno from emp;

查询emp表的员工编号,姓名,工资,部门号,列名,大小写不敏感,但提倡大写

select empno "编号",ename "姓名",sal "工资",deptNO "部门号" FROM Emp;

查询emp表的不重复的工作

select distinct job from emp;

查询员工的编号,姓名,月薪,年薪(月薪*12)

select empno,ename,sal,sal*12 "年薪" from emp;

查询员工的编号,姓名,入职时间,月薪,年薪,年收入(年薪+奖金)

select empno "编号",ename"姓名",hiredate "入职时间",sal "月薪",sal*12 "年薪",sal*12+comm "年收入" from emp;

如果结果为null,在sqlplus客户端工具中,是不显示null这个值的

解决null的问题,使用NVL()函数,NVL(a,b):如果a是NULL,用b替代;如果a是非NULL,就不用b替代,直接返回a的值\

select NVL(null,10) from emp;结果有14行记录

select NVL(null,10) from dual;结果有1行记录

select empno "编号",ename"姓名",hiredate "入职时间",sal "月薪",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"

from emp;

注意:null与具体数字运算时,结果为null

使用列别名,查询员工的编号,姓名,月薪,年薪,年收入(年薪+奖金),AS大小写都可且可以省略AS,别名用双引号

select empno AS "编号",ename as "姓名",sal "月薪"

from emp;

select empno AS 编号,ename as 姓名,sal 月薪

from emp;

区别

select empno AS "编号",ename as 姓名,sal "月 薪"

from emp;

不加双引号的别名不能有空格;加了双引号的别名可以有空格

要加只能加双引号,不能加单引号,因为在oracle中单引号表示字符串类型或者是日期类型

列名不能使用单引号,因为oracle认为单引号是字符串型或日期型

使用dual哑表或者伪表,使用字符串连接符号||,输出"hello world",在oracle中from是必须写的

select 'hello' || ' world' "结果" from dual;

使用sysdate,显示系统当前时间,在默认情况下,oracle只显示日期,而不显示时间,格式:26-4月-15

select sysdate from dual;

使用字符串连接符号||,显示如下格式信息:****的薪水是****美元

select ename || '的薪水是' || sal || '美元'

from emp;

使用spool命令,保存SQL语句到硬盘文件e:/oracle-day01.sql,并创建sql文件

spool e:/oracle-day01.sql;

使用spool off命令,保存SQL语句到硬盘文件e:/oracle-day01.sql,并创建sql文件,结束语句

spool off;

使用@命令,将硬盘文件e:/crm.sql,读到orcl实例中,并执行文件中的sql语句

@ e:/crm.sql;

使用--符号,设置单行注释

--select * from emp;

使用/* */符号,设置多行注释

/*

select

*

from

emp;

*/

总结:你学过的所有技术中,关于注释的符号

html:

css:

js:

xml:

jsp:

mysql:

oracle:

java:

SQL语句的特点

1)是SQL92/99的ANSI官方标准,只要按照该标准来写,在任何的关系型数据库中都可以直接执行

2)SQL语句的关健字不能简写,例如:select,where,from

3)大小写不敏感,提倡大写

4)能够对表数据进行增删改查操作

5)必须以分号结束

6)通常称做语句

SQLPLUS命令的特点

1)是oracle自带的一款工具,在该工具中执行的命令叫SQLPLUS命令

2)SQLPLUS工具的命令中的关健字可以简写,也可以不简写,例如:col ename for a10;

3)大小写不敏感,提倡大写

4)不能够对表数据进行增删改查操作,只能完成显示格式控制,例如:设置显示列宽,清屏,记录执行结果

5)可以不用分号结束,也可以用分号结束,个人提倡不管SQL或SQLPLUS,都以分号结束

6)通常称做命令,是SQLPLUS工具中的命令

注意:SQLPLUS命令是SQLPLUS工具中特有的语句

单引号出现的地方如下:

1)字符串型,例如:'hello' || ' world'

2)日期型,例如'25-4月-15'

双引号出现的地方如下:

1)列别名,例如:sal*12 "年 薪",或 sal*12 年薪,个人提倡用""双引号作列别名

-------------------------------------------------------------------------------------where

查询emp表中20号部门的员工信息

select * from emp where deptno = 20;

查询姓名是SMITH的员工,字符串使用'',内容大小写敏感

select * from emp where ename = 'SMITH';

总结:你所学过的技术中,哪些是大小写敏感,哪些是大小写不敏感

查询1980年12月17日入职的员工,注意oracle默认日期格式(DD-MON-RR表示2位的年份)

select * from emp where hiredate = '17-12月-80';

查询工资大于1500的员工

select * from emp where sal > 1500;

查询工资不等于1500的员工【!=或<>】

select * from emp where sal <> 1500;

查询薪水在1300到1600之间的员工,包括1300和1600

select * from emp where (sal>=1300) and (sal<=1600);

select * from emp where sal between 1300 and 1600;

查询薪水不在1300到1600之间的员工,不包括1300和1600

select * from emp where sal NOT between 1300 and 1600;

查询入职时间在"1981-2月-20"到"1982-1月-23"之间的员工

select * from emp where hiredate between '20-2月-81' and '23-1月-82';

注意:

1)对于数值型,小数值在前,大数值在后

2)对于日期型,年长值在前,年小值在后

查询20号或30号部门的员工,例如:根据ID号,选中的员工,批量删除

select * from emp where (deptno=20) or (deptno=30);

select * from emp where deptno in (30,20);

查询不是20号或30号部门的员工

select * from emp where deptno NOT in (30,20);

查询姓名以大写字母S开头的员工,使用%表示0个,1个或多个字符

select * from emp where ename like 'S';

等价

select * from emp where ename = 'S';

select * from emp where ename like 'S%';

注意:

凡是精确查询用=符号

凡是不精确查询用like符号,我们通常叫模糊查询

查询姓名以大写字母N结束的员工

select * from emp where ename like '%N';

查询姓名第一个字母是T,最后一个字母是R的员工

select * from emp where ename like 'T%R';

查询姓名是4个字符的员工,且第二个字符是I,使用_只能表示1个字符,不能表示0个或多个字符

select * from emp where ename like '_I__';

插入一条姓名为'T_IM'的员工,薪水1200

insert into emp(empno,ename) values(1111,'T_IM');

查询员工姓名中含有'_'的员工,使用\转义符,让其后的字符回归本来意思【like '%\_%' escape '\'】

select * from emp where ename like '%\_%' escape '\';

插入一个姓名叫'的员工

insert into emp(empno,ename) values(2222,'''');

插入一个姓名叫''的员工

insert into emp(empno,ename) values(2222,'''''');

查询所有员工信息,使用%或%%

select * from emp;

select * from emp where ename like '%';

select * from emp where ename like '%_%';

查询佣金为null的员工

select * from emp where comm is null;

注意:null不能参数=运算

null能参数number/date/varchar2类型运算

查询佣金为非null的员工

select * from emp where comm is not null;

查询无佣金且工资大于1500的员工

select *

from emp

where (comm is null) and (sal>1500);

查询工资是1500或3000或5000的员工

select *

from emp

where sal in (4000,10000,1500,3,300,3000,5000);

查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式一,使用!=或<>)

select *

from emp

where (job='MANAGER') or (job<>'ANALYST');

查询职位是"MANAGER"或职位不是"ANALYST"的员工(方式二,使用not)

select *

from emp

where (job='MANAGER') or (not(job='ANALYST'));

-------------------------------------------------------------------------------------order by

查询员工信息(编号,姓名,月薪,年薪),按月薪升序排序,默认升序,如果月薪相同,按oracle内置的校验规则排序

select empno,ename,sal,sal*12

from emp

order by sal asc;

查询员工信息(编号,姓名,月薪,年薪),按月薪降序排序

select empno,ename,sal,sal*12

from emp

order by sal desc;

查询员工信息,按入职日期降序排序,使用列名

select empno,ename,sal,hiredate,sal*12 "年薪"

from emp

order by hiredate desc;

order by后面可以跟列名、别名、表达式、列号(从1开始,在select子句中的列号)

列名:

select empno,ename,sal,hiredate,sal*12 "年薪"

from emp

order by hiredate desc;

别名:

select empno,ename,sal,hiredate,sal*12 "年薪"

from emp

order by "年薪" desc;

表达式:

select empno,ename,sal,hiredate,sal*12 "年薪"

from emp

order by sal*12 desc;

列号,从1开始:

select empno,ename,sal,hiredate,sal*12 "年薪"

from emp

order by 5 desc;

查询员工信息,按佣金升序或降序排列,null值看成最大值

select * from emp order by comm desc;

查询员工信息,对有佣金的员工,按佣金降序排列,当order by 和 where 同时出现时,order by 在最后

select *

from emp

where comm is not null

order by comm desc;

查询员工信息,按工资降序排列,相同工资的员工再按入职时间降序排列

select *

from emp

order by sal desc,hiredate desc;

select *

from emp

order by sal desc,hiredate asc;

注意:只有当sal相同的情况下,hiredate排序才有作用

查询20号部门,且工资大于1500,按入职时间降序排列

select *

from emp

where (deptno=20) and (sal>1500)

order by hiredate desc;

select * from emp where deptno in (10,20,30,50,'a');

-------------------------------------------------------------------------------------单行函数

单行函数:只有一个参数输入,只有一个结果输出

多行函数或分组函数:可有多个参数输入,只有一个结果输出

测试lower/upper/initcap函数,使用dual哑表

select lower('www.BAIdu.COM') from dual;

select upper('www.BAIdu.COM') from dual;

select initcap('www.BAIdu.COM') from dual;

测试concat/substr函数,从1开始,表示字符,不论中英文

select concat('hello','你好') from dual;正确

select concat('hello','你好','世界') from dual;错误

select 'hello' || '你好' || '世界' from dual;正确

select concat('hello',concat('你好','世界')) from dual;正确

select substr('hello你好',5,3) from dual;

5表示从第几个字符开始算,第一个字符为1,中英文统一处理

3表示连续取几个字符

测试length/lengthb函数,编码方式为UTF8/GBK(赵君),一个中文占3/2个字节长度,一个英文一个字节

select length('hello你好') from dual;

select lengthb('hello你好') from dual;

测试instr/lpad/rpad函数,从左向右找第一次出现的位置,从1开始

select instr('helloworld','o') from dual;

注意:找不到返回0

大小写敏感

select LPAD('hello',10,'#') from dual;

select RPAD('hello',10,'#') from dual;

测试trim/replace函数

select trim(' ' from ' he ll ') from dual;

select replace('hello','l','L') from dual;

测试round/trunc/mod函数作用于数值型

select round(3.1415,3) from dual;

select trunc(3.1415,3) from dual;

select mod(10,3) from dual;

当前日期:sysdate = 26-4月-15

测试round作用于日期型(month)

select round(sysdate,'month') from dual;

测试round作用于日期型(year)

select round(sysdate,'year') from dual;

测试trunc作用于日期型(month)

select trunc(sysdate,'month') from dual;

测试trunc作用于日期型(year)

select trunc(sysdate,'year') from dual;

显示昨天,今天,明天的日期,日期类型 +- 数值 = 日期类型

select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;

以年和月形式显示员工近似工龄,日期-日期=数值,假设:一年以365天计算,一月以30天计算

select ename "姓名",round(sysdate-hiredate,0)/365 "天数" from emp;

使用months_between函数,精确计算到年底还有多少个月

select months_between('31-12月-15',sysdate) from dual;

使用months_between函数,以精确月形式显示员工工龄

select ename "姓名",months_between(sysdate,hiredate) "精确月工龄" from emp;

测试add_months函数,下个月今天是多少号

select add_months(sysdate,1) from dual;

测试add_months函数,上个月今天是多少号

select add_months(sysdate,-1) from dual;

测试next_day函数,从今天开始算,下一个星期三是多少号【中文平台】

select next_day(sysdate,'星期三') from dual;

测试next_day函数,从今天开始算,下下一个星期三是多少号【中文平台】

select next_day(next_day(sysdate,'星期三'),'星期三') from dual;

测试next_day函数,从今天开始算,下一个星期三的下一个星期日是多少号【中文平台】

select next_day(next_day(sysdate,'星期三'),'星期日') from dual;

测试last_day函数,本月最后一天是多少号

select last_day(sysdate) from dual;

测试last_day函数,本月倒数第二天是多少号

select last_day(sysdate)-1 from dual;

测试last_day函数,下一个月最后一天是多少号

select last_day(add_months(sysdate,1)) from dual;

测试last_day函数,上一个月最后一天是多少号

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

注意:

1)日期-日期=天数

2)日期+-天数=日期

-------------------------------------------------------------------------------------三大类型转换

oracle中三大类型与隐式数据类型转换

(1)varchar2变长/char定长-->number,例如:'123'->123

(2)varchar2/char-->date,例如:'25-4月-15'->'25-4月-15'

(3)number---->varchar2/char,例如:123->'123'

(4)date------>varchar2/char,例如:'25-4月-15'->'25-4月-15'

oracle如何隐式转换:

1)=号二边的类型是否相同

2)如果=号二边的类型不同,尝试的去做转换

3)在转换时,要确保合法合理,否则转换会失败,例如:12月不会有32天,一年中不会有13月

查询1980年12月17日入职的员工(方式一:日期隐示式转换)

select * from emp where hiredate = '17-12月-80';

使用to_char(日期,'格"常量"式')函数将日期转成字符串,显示如下格式:2015 年 04 月 25 日 星期六

select to_char(sysdate,'yyyy" 年 "mm" 月 "dd" 日 "day') from dual;

使用to_char(日期,'格式')函数将日期转成字符串,显示如格式:2015-04-25今天是星期六 15:15:15

select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual;

select to_char(sysdate,'yyyy-mm-dd"今天是"day HH12:MI:SS AM') from dual;

使用to_char(数值,'格式')函数将数值转成字符串,显示如下格式:$1,234

select to_char(1234,'$9,999') from dual;

使用to_char(数值,'格式')函数将数值转成字符串,显示如下格式:¥1,234select to_char(1234,'$9,999') from dual;

select to_char(1234,'L9,999') from dual;

使用to_date('字符串','格式')函数,查询1980年12月17日入职的员工(方式二:日期显式转换)

select * from emp where hiredate = to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');

select * from emp where hiredate = to_date('1980#12#17','yyyy"#"mm"#"dd');

select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');

使用to_number('字符串')函数将字符串‘123’转成数字123

select to_number('123') from dual;

注意:

select '123' + 123 from dual;246

select '123' || 123 from dual;123123

-----------------------------------

使用NVL(a,b)通用函数,统计员工年收入,NVL()作用于任何类型,即(number/varchar2/date)

通用函数:参数类型可以是number或varchar2或date类型

使用NVL2(a,b,c)通用函数,如果a不为NULL,取b值,否则取c值,统计员工年收入

使用NULLIF(a,b)通用函数,在类型一致的情况下,如果a与b相同,返回NULL,否则返回a,比较10和10.0是否相同

使用SQL99标准通用语法中的case表达式,将职位是分析员的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400

case 字段

when 条件 then 表达式1

when 条件 then 表达式2

else 表达式n

end

使用oracle专用语法中的decode()函数,职位是分析员的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400

decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)

单引号出现的地方如下:

1)字符串,例如:'hello'

2)日期型,例如:'17-12月-80'

3)to_char/to_date(日期,'YYYY-MM-DD HH24:MI:SS')

双引号出现的地方如下:

1)列别名,例如:select ename "姓 名" from emp

2)to_char/to_date(日期,'YYYY"年"MM"月"DD"日" HH24:MI:SS')