频道栏目
首页 > 数据库 > Oracle > 正文
oracle数据库服务器01
2016-08-10 09:21:39           
收藏   我要投稿

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

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')

点击复制链接 与好友分享!回本站首页
相关TAG标签 数据库 服务器
上一篇:Oracle的存储架构
下一篇:ORACLE数据库备份方法
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站