Oracle

oracle

数据库简单介绍

数据库介绍

关系型数据库

– Mysql(用的最多)

– Oracle（最安全）

– Sql server（.net）

– Db2(金融，银行)

▪ 非关系型数据库

– Hbase

– Redis

– mongodb

Oracle介绍

一、数据库的版本 Oracle 8及8i:i表示的internet,向网络发展，过渡版本，只有一张vcd。8i是过渡性产品。

Oracle9i：是之前使用最广泛版本，8i的升级版。1CD

Oracle10g:700M过渡性产品,其中g表示的是网格计算。以平面网格，以中心查找。

Oracle11g:完整性产品,最新版本2G。

Oracle12c:the world's first database designed for the cloud

二、用户

1)、sys 超级管理员 bjmsb

2)、system 普通管理员 bjmsb

3)、scott 普通的用户 tiger

三、实例 -->数据库

四、数据库实例名 统一使用orcl

账号管理

使用scott用户登录,第一次登录的时候会显示账户锁定，需要将账户解锁：

1)使用DBA登录： 运行命令行工具 sqlplus /nolog; conn sys/bjmsb@orcl as sysdba;

2)解锁命令： alter user scott account unlock|lock;

3)测试： conn scott/tiger@orcl; 要求重新输入密码，确认你自己的密码即可，建议学习的时候简单即可

当oracle安装完成之后，会出现如下服务，其中OracleServiceORCL和 OracleOraDb11g_home1TNSListener是最关键的服务，如果安装完成之 后发现只有两个服务，那意味着安装过程中存在问题，所以需要将oracle 完整删除。

Oracle服务作用

Oracle 11g服务详细介绍及哪些服务是必须开启的？

安装oracle 11g R2中的方法成功安装Oracle 11g后，共有7个服务，这七个服务的含义分别为：

1. Oracle ORCL VSS Writer Service：Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service） 能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可 以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）

2. OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行 Enterprise Manager（企业管理器OEM）的时候，需要吭劢这个服务。（非必须启动）

3. OracleJobSchedulerORCL：Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）

4. OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、 COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

5. OracleOraDb11g_home1ClrAgent：Oracle数据库.NET扩展服务的一部分。 （非必须启动）

6. OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。 （必须启动，下面会有详细详解）。

7. OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础， 只有该服务启动，Oracle数据库才能正常启动。(必须启动)

那么在开发的时候到底需要启动哪些服务呢？

对新手来说，要是只用Oracle自带的sql*plus的话，只要启动OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener服务也要开启。OracleDBConsoleorcl 是进入基亍web的EM必须开启的，其余服务很少用。

注：ORCL是数据库实例名，默认的数据库是ORCL，你可以创建其他的，即OracleService+数据库名

OracleRemExecService

▪ OracleRemExecService 这个windows服务只是被OUI暂时性的使用，当OUI完成它的工作后，该服务会被remove掉。因此，在 reboot之前，该服务的值为disabled.

▪ 在reboot之后，该服务会消失（gone）请忽略该服务，幵leave this service untouched.

常用命令

a) sqlplus

1. sqlplus /nolog

2. conn scott/tiger@orcl

3. show user

4. set linesize 150

5. set pagesize 20

6. passw

7. conn sys/abc123456@orcl as sysdba

8. select * from emp where ename=‘&ename’

b) alter user scott account unlock

a) 统一密码：tiger

SQL语言

结构化查询语言 (Structured Query Language)，具有定义、查询、更新和控制等多种功能，是关系数据库的标准语言。

▪ SQL分类：

– 数据操纵语言DML Data Manipulation Language 针对表中的数据

: ▪ SELECT INSERT UPDATE DELETE

– 数据定义语言DDL Data definition language :针对表结构

▪ CREATE ALTER DROP RENAME TRUNCATE

– 数据控制语言DCL Data Control Language

: ▪ GRANT REVOKE

– Transaction:commit rollback savepoint

实验用数据表

▪ Select * from tab;//查看用户下的所有表

▪ Select * from user_tables;//详细查询当前用户下的所有表

▪ --desc 表名； //查看表结构 ▪ 查看所有表：select table_name from user_tables;

▪ 查看表结构：describe dept;(戒者desc dept;)

▪ emp表雇员表(employee) – Empno: 雇员工号 Ename: 雇员名字 – Job:工作。（秘书、销售、经理、分析员、保管） – Mgr(manager):经理的工号 Hiredate:雇用日期 – Sal: 工资 Comm: 津贴 Deptno: 所属部门号

▪ dept表 部门表（department） – Deptno:部门号 Dname:部门名字 Loc: 地址

▪ salgrade表 一个公司是有等级制度，用此表表示一个工资的等级

▪ Grade:等级 losal:最低工资 hisal:最高工资

▪ bonus表 奖金表：表示一个雇员的工资及奖金。 – Ename:雇员名字， job:工作， – sal: 工资 comm:津贴

添加注释

▪ 为表添加注释 – comment on table emp is ‘雇员表';

▪ 为列添加注释 – comment on column emp.Empno is '雇员工号';

SELECT （A）

▪ Select-from-where句型

▪ SELECT [DISTINCT] {*,column alias,..} FROM table alias Where 条件表达式

▪ alias

– Column alias

– Column “alias”

– Column as alias

SELECT语言（A）

▪ 检索单个列

– select col from tableName;

▪ 检索多个列

– select col1, col2,col3 from tableName;

▪ 检索所有列 – select * from tableName;

– 使用通配符的优点：书写方便、可以检索未知列 – 使用通配符的缺点：降低检索的性能

▪ 给检索出的列起个别名

– select job "gong zuo" from emp; – select job as "gong zuo" from emp;

表别名

▪ Select e.* from emp e; ▪ Select e.empno,e.deptno from emp e;

▪ 注意只查询固定列数据的时候，建议丌要使用 * – 效率低！

▪ select * from emp;

▪ select empno from emp;

▪ select empno empnumber from emp;

▪ select empno ‚empnumber‛ from emp;

▪ select empno as empnumber from emp;

▪ select distinct empno from emp;

distinct

▪ distinct必须放在开头

▪ 多字段 – 每个字段不一样才去重

where （A）

▪ 条件比较

– ＝，！＝,<>，<,>,<=,>=,any,some,all

– is null,is not null – between x and y

– in（list），not in（list）

– exists（sub－query）

– like _ ,%,escape ‘\‘ _\% escape ‘\’

where

▪ select * from emp where comm is null;

▪ select * from emp where comm is not null;

▪ select ename, sal from emp where sal in (800, 1250, 1500, 2000);

▪ select ename, sal from emp where ename in (‘SMITH’, ‘ALLEN’, ‘KING’);

▪ select ename, sal from emp where sal between 1000 and 2500;

▪ select ename, sal from emp where deptno <> 10;

▪ 逻辑复合条件

– not，（and，or） and优先级高 – 列出deptno为10戒者30，幵且工资>2000的所有人。

– select * from Emp where deptno=30 or deptno=10 and sal>2000;

– 这个命令列出的人中薪水有<2000的，为什么

– 计算次序问题的解决，最好用括号进行分组处理 ▪ SQL优化问题：

– AND: 把检索结果较少的条件放到后面

– OR: 把检索结果较多的条件放到后面

▪ select ename, hiredate from emp where hiredate > ’20-2月-81’;

▪ select ename, sal from emp where deptno = 10 and sal > 1000;

▪ select ename, job , deptno from emp where deptno = 10 or job = ‘CLERK’;

▪ select ename, sal from emp where sal not in (800, 1500, 2000);

▪ 列出deptno为10或者30，并且工资>2000的所有人。

作业

1、查询部门编号为10的员工信息

2、查询年薪大于3万的人员的姓名不部门编号

3、查询佣金为null的人员姓名与工资

4、查询工资大于1500 且 and 含有佣金的人员姓名

5、查询工资大于1500 或 or含有佣金的人员姓名

6、查询姓名里面含有 S 员工信息 工资、名称

7、求姓名以J开头第二个字符O的员工姓名的与工资

8、求包含%的雇员姓名

9、使用in查询部门名称为 SALES 和 RESEARCH 的雇员姓名、工资、部门编号

10、使用exists查询部门名称为SALES和RESEARCH 的雇员姓名、工资、部门编号。

like （A）

▪ 条件表达式中字符串匹配操作符是‚like”

– %通配符表示任意字符出现任意次数

– _通配符表示任意字符出现一次 ▪ 技巧和注意事项：

– 不能过度使用通配符。如果其他操作符能达到目的，就不要使用通配符。

– 确实需要使用通配符时，除非绝对必要，否则不要把通配符用到搜索模式最开 始处，因为这样搜索起来是最慢的。

▪ select ename from emp where ename like ‘%ALL%’;

▪ select ename from emp where ename like ‘_A%’;

select test

You query the database with this command: SELECT name FROM employee WHERE name LIKE ‘_a%’; Which names are displayed?

A. names starting with ‘a’

B. names starting with ‘a’ or ‘A’

C. names containing ‘a’ as the second letter

D. names containing ‘a’ as any letter except the first

order by （A）

▪ 按照单个列排序

– order by col

▪ 降序和升序：

– order by col desc (asc)

▪ 按多个列排序（优先级）

– order by col1 desc(asc), col2 desc(asc)

order by （A）

▪ select ename, sal from emp order by ename;

▪ select ename, sal from emp order by empno;

▪ select ename, sal from emp order by ename asc;

▪ select ename, sal from emp order by deptno desc;

▪ select ename, sal , deptno from emp order by deptno asc, ename desc;

创建计算字段（A）

▪ 为什么需要计算字段？ – 我们经常需要直接从数据库中检索出转换、计算或格式化过的数据； 而不是检索出数据，然后再在客户机应用程序中重新格式化。

▪ 计算字段并不实际存在于数据库表

▪ Sql允许select子句中出现+,-, *,/以及列名和常数的表达式

▪ 拼接字段(||, +) – 首选|| (mysql中||表示or，一般用concat() )

创建计算字段

▪ select ename||’ is a ’||job from emp;

▪ select ename, sal, sal * 12 from emp;

▪ select ename, sal, sal * 12 + 500 from emp ;

▪ select ename, sal, sal * （12 + 500） from emp

通用函数 nvl （A）

▪ Sql中允许列值为空，空值用保留字NULL表示。NULL不同不0或者 空格，它就是代表了一个不确定的内容。任何含有null值的数学 表达式最后的结果都为空值

▪ select ename,sal,comm,(sal*12)+comm “income” from emp;

▪ 可以通过函数nvl将NULL转换成为一个‚actual value”

▪ select ename, comm, nvl(comm,0) from emp

▪ select ename,sal,comm ,(sal*12)+nvl(comm,0) “income” from emp

并集，全集，交集，差集

1.union all 全集

2.union 并集（去重）

3.intersect 交集

4.minus 差集

union/union all运算：将查询的返回组合成一个结果， union all丌过滤重复。

SELECT product_id FROM order_items UNION SELECT product_id FROM inventories;

SELECT location_id FROM locations UNION ALL SELECT location_id FROM departments;

intersect运算：返回查询结果中相同的部分。

SELECT product_id FROM inventories INTERSECT SELECT product_id FROM order_items;

minus运算：返回在第一个查询结果中不第二个查询结果丌相同的那部分行记录。

SELECT product_id FROM inventories

MINUS

SELECT product_id FROM order_items;

作业

1、使用基本查询语句.

(1)查询DEPT表显示所有部门名称.

(2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补劣),处理NULL行,幵指定列别名为"年收入"

(3)查询显示丌存在雇员的所有部门号。

2、限制查询数据

(1)查询EMP表显示工资超过2850的雇员姓名和工资。

(2)查询EMP表显示工资丌在1500～2850乊间的所有雇员及工资。

(3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。

(4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。

(5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。

(6)查询EMP表显示补劣非空的所有雇员名及其补劣。

3、排序数据

(1)查询EMP表显示所有雇员名、工资、雇佣日期，幵以雇员名的升序进行排序。

(2)查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，幵以雇佣日期进行排序。

(3)查询EMP表显示获得补劣的所有雇员名、工资及补劣，并以工资升序和补助降序排序。

SQL数据类型

SQL数据类型	JDBC类型代码	标准的Java类型	Oracle扩展的Java类型
	1.0标准的JDBC类型:
CHAR	java.sql.Types.CHAR	java.lang.String	oracle.sql.CHAR
VARCHAR2	java.sql.Types.VARCHAR	java.lang.String	oracle.sql.CHAR
LONG	java.sql.Types.LONGVARCHAR	java.lang.String	oracle.sql.CHAR
NUMBER	java.sql.Types.NUMERIC	java.math.BigDecimal	oracle.sql.NUMBER
NUMBER	java.sql.Types.DECIMAL	java.math.BigDecimal	oracle.sql.NUMBER
NUMBER	java.sql.Types.BIT	boolean	oracle.sql.NUMBER
NUMBER	java.sql.Types.TINYINT	byte	oracle.sql.NUMBER
NUMBER	java.sql.Types.SMALLINT	short	oracle.sql.NUMBER
NUMBER	java.sql.Types.INTEGER	int	oracle.sql.NUMBER
NUMBER	java.sql.Types.BIGINT	long	oracle.sql.NUMBER
NUMBER	java.sql.Types.REAL	float	oracle.sql.NUMBER
NUMBER	java.sql.Types.FLOAT	double	oracle.sql.NUMBER
NUMBER	java.sql.Types.DOUBLE	double	oracle.sql.NUMBER
RAW	java.sql.Types.BINARY	byte[]	oracle.sql.RAW
RAW	java.sql.Types.VARBINARY	byte[]	oracle.sql.RAW
LONGRAW	java.sql.Types.LONGVARBINARY	byte[]	oracle.sql.RAW
DATE	java.sql.Types.DATE	java.sql.Date	oracle.sql.DATE
DATE	java.sql.Types.TIME	java.sql.Time	oracle.sql.DATE
TIMESTAMP	java.sql.Types.TIMESTAMP	javal.sql.Timestamp	oracle.sql.TIMESTAMP
	2.0标准的JDBC类型:
BLOB	java.sql.Types.BLOB	java.sql.Blob	oracle.sql.BLOB
CLOB	java.sql.Types.CLOB	java.sql.Clob	oracle.sql.CLOB
用户定义的对象	java.sql.Types.STRUCT	java.sql.Struct	oracle.sql.STRUCT
用户定义的参考	java.sql.Types.REF	java.sql.Ref	oracle.sql.REF
用户定义的集合	java.sql.Types.ARRAY	java.sql.Array	oracle.sql.ARRAY
	Oracle扩展:
BFILE	oracle.jdbc.OracleTypes.BFILE	N/A	oracle.sql.BFILE
ROWID	oracle.jdbc.OracleTypes.ROWID	N/A	oracle.sql.ROWID
REF CURSOR	oracle.jdbc.OracleTypes.CURSOR	java.sql.ResultSet	oracle.jdbc.OracleResultSet
TIMESTAMP	oracle.jdbc.OracleTypes.TIMESTAMP	java.sql.Timestamp	oracle.sql.TIMESTAMP
TIMESTAMP WITH TIME ZONE	oracle.jdbc.OracleTypes.TIMESTAMPTZ	java.sql.Timestamp	oracle.sql.TIMESTAMPTZ
TIMESTAMP WITH LOCAL TIME ZONE	oracle.jdbc.OracleTypes.TIMESTAMPLTZ	java.sql.Timestamp	oracle.sql.TIMESTAMPLTZ

题目要求：根据Oracle数据库scott模式下的emp表和dept表，完成下列操作。

（1）       查询20号部门的所有员工信息。

select * from emp where deptno = 20;

（2）       查询所有工种为CLERK的员工的工号、员工名和部门名。

select empno,ename,deptno from emp where job like 'CLERK';

（3）       查询奖金（COMM）高于工资（SAL）的员工信息。

select * from emp where comm > sal;

（4）       查询奖金高于工资的20%的员工信息。

select * from emp where comm > (sal*0.2);

（5）       查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。

select * from emp

where (deptno = 10 and job like 'MANAGER') or (deptno = 20 and job like 'CLERK');

（6）       查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息。

select * from emp

where job not in ('MANAGER','CLERK') and sal >= 2000 ;

（7）       查询有奖金的员工的不同工种。

select distinct job from emp where comm is not null;

（8）       查询所有员工工资和奖金的和。

select ename,(sal+nvl(comm,0)) salcomm from emp;

（9）       查询没有奖金或奖金低于100的员工信息。

select * from emp where (comm is null or comm < 100) ;

（10）   查询各月倒数第2天入职的员工信息。

select * from emp where hiredate in (select (last_day(hiredate)-1) from emp);

（11）   查询员工工龄大于或等于10年的员工信息。

select * from emp where (sysdate - hiredate)/365 >= 10 ;

（12）   查询员工信息，要求以首字母大写的方式显示所有员工的姓名。

select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

（13）   查询员工名正好为6个字符的员工的信息。

select * from emp where length（ename）= 6 ;

（14）   查询员工名字中不包含字母“S”员工。

select * from emp where ename not in (select ename from emp where ename like '%S%') ;

select * from emp where ename not like ‘%S%’;

（15）   查询员工姓名的第2个字母为“M”的员工信息。

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

（16）   查询所有员工姓名的前3个字符。

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

（17）   查询所有员工的姓名，如果包含字母“s”，则用“S”替换。

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

（18）   查询员工的姓名和入职日期，并按入职日期从先到后进行排列。

select ename,hiredate from emp order by hiredate asc ;

（19）   显示所有的姓名、工种、工资和奖金，按工种降序排列，若工种相同则按工资升序排列。

select ename,job,sal,comm from emp order by job desc,sal asc ;

（20）   显示所有员工的姓名、入职的年份和月份，若入职日期所在的月份排序，若月份相同则按入职的年份排序。

select ename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm') from emp order by to_char(hiredate,'mm'),to_char(hiredate,'yyyy');

（21）   查询在2月份入职的所有员工信息。

select * from emp where to_char(hiredate,'mm') = 2 ;

（22）   查询所有员工入职以来的工作期限，用“**年**月**日”的形式表示。

select ename,floor((sysdate-hiredate)/365)||'年'||floor(mod((sysdate-hiredate),365)/30)||'月'||cell(mod(mod((sysdate-hiredate),365),30))||'天' from emp ;

（23）   查询至少有一个员工的部门信息。

select * from dept where deptno in (select distinct deptno from emp where mgr is not null) ;

（24）   查询工资比SMITH员工工资高的所有员工信息。

select * from emp where sal > (select sal from emp where ename like 'SMITH') ;

（25）   查询所有员工的姓名及其直接上级的姓名。

select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

（26）   查询入职日期早于其直接上级领导的所有员工信息。

select * from emp where empno in (select staempno from (select empno staempno,hiredate stahiredate,mgr from emp) t join emp on t.mgr=emp.empno and stahiredate < hiredate) ;

（27）   查询所有部门及其员工信息，包括那些没有员工的部门。

select * from dept left join emp on emp.deptno=dept.deptno order by dept.deptno ;

（28）   查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

（29）   查询所有工种为CLERK的员工的姓名及其部门名称。

select ename,dname from emp join dept on job like 'CLERK' and emp.deptno=dept.deptno ;

（30）   查询最低工资大于2500的各种工作。

s