Oracle

77 阅读 0 评论 51 点赞

我是靠谱客的博主老迟到眼睛，最近开发中收集的这篇文章主要介绍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的各种工作。

select job from (select min(sal) min_sal,job from emp group by job) where min_sal > 2500 ;

（31）查询最低工资低于2000的部门及其员工信息。

select * from emp where deptno in (select deptno from (select min(sal) min_sal,deptno from emp group by deptno) where min_sal < '2000') ;

（32）查询在SALES部门工作的员工的姓名信息。

select ename from emp where deptno = (select deptno from dept where dname like 'SALES');

（33）查询工资高于公司平均工资的所有员工信息。

select * from emp where sal > (select avg(sal) from emp) ;

（34）查询与SMITH员工从事相同工作的所有员工信息。

select * from emp where job in (select job from emp where ename like 'SMITH') and ename not like 'SMITH' ;

（35）列出工资等于30号部门中某个员工工资的所有员工的姓名和工资。

select ename,sal from emp where sal =any (select sal from emp where deptno = 30) ;

（36）查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资。

select ename,sal from emp where sal >all (select sal from emp where deptno = 30) ;

（37）查询每个部门中的员工数量、平均工资和平均工作年限。

select dname,count,avg_sal,avg_date from dept join (select count(*) count,avg(sal) avg_sal,avg((sysdate-hiredate)/365) avg_date,deptno from emp group by deptno) t on dept.deptno = t.deptno ;

（38）查询从事同一种工作但不属于同一部门的员工信息。

select distinct t1.empno,t1.ename,t1.deptno from emp t1 join emp t2 on t1.job like t2.job and t1.deptno <> t2.deptno ;

（39）查询各个部门的详细信息以及部门人数、部门平均工资。

Select dept.*,person_num,avg_sal from dept,(select count(*) person_num,avg(sal) avg_sal,deptno from emp group by deptno) t where dept.deptno = t.deptno ;

（40）查询各种工作的最低工资。

select job,min(sal) from emp group by job ;

（41）查询各个部门中的不同工种的最高工资。

select max(sal),job,deptno from emp group by deptno,job order by deptno,job ；

（42）查询10号部门员工以及领导的信息。

select * from emp where empno in (select mgr from emp where deptno=10) or deptno = 10 ；

（43）查询各个部门的人数及平均工资。

select deptno,count(*),avg(sal) from emp group by deptno ；

（44）查询工资为某个部门平均工资的员工信息。

select * from emp where sal in (select avg(sal) avg_sal from emp group by deptno) ;

（45）查询工资高于本部门平均工资的员工的信息。

select emp.* from emp join (select deptno,avg(sal) avg_sal from emp group by deptno) t on emp.deptno=t.deptno and sal>avg_sal ;

（46）查询工资高于本部门平均工资的员工的信息及其部门的平均工资。

select emp.*,avg_sal from emp join (select deptno,avg(sal) avg_sal from emp group by deptno) t on emp.deptno=t.deptno and sal>avg_sal ;

（47）查询工资高于20号部门某个员工工资的员工的信息。

select * from emp where sal >any(select sal from emp where deptno=20);

（48）统计各个工种的人数与平均工资。

select job,count(*),avg(sal) from emp group by job ;

（49）统计每个部门中各个工种的人数与平均工资。

select deptno,job,count(*),avg(sal) from emp group by deptno,job order by deptno,job;

（50）查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息。

select emp.* from emp join (select sal,comm from emp where deptno = 10) t on emp.sal=t.sal and nvl(emp.comm,0)=nvl(t.comm,0) and emp.deptno != 10;

（51）查询部门人数大于5的部门的员工的信息。

select * from emp where deptno in (select deptno from emp group by deptno having count(*)>5);

（52）查询所有员工工资都大于1000的部门的信息。

select * from dept where deptno in (select distinct deptno from emp where deptno not in (select distinct deptno from emp where sal < 1000)) ;

（53）查询所有员工工资都大于1000的部门的信息及其员工信息。

select * from emp join dept on dept.deptno in (select distinct deptno from emp where deptno not in (select distinct deptno from emp where sal < 1000)) and dept.deptno=emp.deptno;

（54）查询所有员工工资都在900~3000之间的部门的信息。

select * from dept where deptno in (select distinct deptno from emp where deptno not in (select distinct deptno from emp where sal not between 900 and 3000)) ;

（55）查询所有工资都在900~3000之间的员工所在部门的员工信息。

select * from emp where deptno in (select distinct deptno from emp where deptno not in (select distinct deptno from emp where sal not between 900 and 3000)) ;

（56）查询每个员工的领导所在部门的信息。

select * from (select e1.empno,e1.ename,e1.mgr mno,e2.ename mname,e2.deptno from emp e1 join emp e2 on e1.mgr=e2.empno) t join dept on t.deptno=dept.deptno ;

（57）查询人数最多的部门信息。

select * from dept where deptno in (select deptno from (select count(*) count,deptno from emp group by deptno) where count in (select max(count) from (select count(*) count,deptno from emp group by deptno)));

（58）查询30号部门中工资排序前3名的员工信息。

select * from emp where empno in (select empno from (select empno,sal from emp where deptno=30 order by sal desc) where rownum < 4) ;

（59）查询所有员工中工资排在5~10名之间的员工信息。

select * from emp where empno in (select empno from (select empno,rownum num from (select empno,sal from emp order by sal desc)) where num between 5 and 10 ) ;

select empno from (select empno,sal from emp order by sal desc) where rownum <= 10 minus select empno from (select empno,sal from emp order by sal desc) where rownum < 5 ;

（60）向emp表中插入一条记录，员工号为1357，员工名字为oracle，工资为2050元，部门号为20，入职日期为2002年5月10日。

insertinto emp(empno,ename,sal,deptno,hiredate) values (1357,'oracle',2050,20,to_date('2002年5月10日','yyyy"年"mm"月"dd"日"')) ;

（61）向emp表中插入一条记录，员工名字为FAN，员工号为8000，其他信息与SMITH员工的信息相同。

（62）将各部门员工的工资修改为该员工所在部门平均工资加1000。

update emp t1 set sal = (select new_sal from (select avg(sal)+1000 new_sal,deptno from emp group by deptno) t2 wher e t1.deptno = t2.deptno ) ;

1、查询82年员工

select e.* from emp e where to_char(e.hiredate, 'yy') like '82';

select e.* from emp e where to_char(e.hiredate,'yyyy')='1982';

2、查询32年工龄的人员

select round(sysdate-e.hiredate)/365, e.ename,e.hiredate from emp e where round((sysdate-e.hiredate)/365)=32;

3、显示员工雇佣期 6 个月后下一个星期一的日期

select next_day(add_months(e.hiredate,6),2) from emp e ;

4、找没有上级的员工，把mgr的字段信息输出为 "boss"

select decode(e.mgr,null,'boss','中国好声音') from emp e;

5、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%

select decode(e.deptno,10,e.sal*1.1,20,e.sal*1.15, e.sal*1.18) 涨工资 ,e.deptno, e.sal from emp e ;

Oracle_练习与答案

1.求部门中薪水最高的人

select ename,sal,emp.deptno from emp

join (select deptno,max(sal) max_sal from emp group by deptno) t

on (emp.deptno = t.deptno and emp.sal = t.max_sal);

select ename, sal, deptno

from emp

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

2.求部门平均薪水的等级

select deptno, avg_sal, grade from

(select deptno,avg(sal) avg_sal from emp group by deptno) t

join salgrade

on (t.avg_sal between salgrade.losal and salgrade.hisal);

3. 求部门平均的薪水等级

select deptno, avg(grade) avg_sal_grade from (select deptno, grade from emp

join salgrade on emp.sal between salgrade.losal and salgrade.hisal) group by deptno;

4. 雇员中有哪些人是经理人

select distinct e2.ename manager from emp e1 join emp e2 on e1.mgr = e2.empno;

select ename from emp where empno in (select mgr from emp);

5. 不准用组函数，求薪水的最高值

select distinct sal max_sal from emp where sal not in

(select e1.sal e1_sal from emp e1 join emp e2 on e1.sal < e2.sal);

select * from (select * from emp order by sal desc) t where rownum <2

6. 求平均薪水最高的部门的部门编号

select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno)

where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal from emp group by

deptno) );

组函数嵌套写法(对多可以嵌套一次，group by 只对内层函数有效)

select deptno, avg_sal from

(select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal =

(select max(avg(sal)) from emp group by deptno);

7. 求平均薪水最高的部门的部门名称

select t1.deptno, dname, avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t1 join dept on t1.deptno = dept.deptno where avg_sal = (select max(avg_sal) from

(select deptno,avg(sal) avg_sal from emp group by deptno) );

select dname from dept where deptno = (select deptno from

(select deptno,avg(sal) avg_sal from emp group by deptno) where avg_sal =

(select max(avg_sal) from

(select deptno,avg(sal) avg_sal from emp group by deptno) ) );

8. 求平均薪水的等级最低的部门的部门名称

select dname from dept join (select deptno, grade from

(select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade

on (t.avg_sal between salgrade.losal and salgrade.hisal) ) t

on dept.deptno = t.deptno where t.grade = (select min(grade) from

(select avg(sal) avg_sal from emp group by deptno) t join salgrade

on (t.avg_sal between salgrade.losal and salgrade.hisal) );

9.求部门经理人中平均薪水最低的部门名称

select dname from (select deptno, avg(sal) avg_sal from emp where empno in (select mgr from emp)group by deptno)t join dept on t.deptno = dept.deptno where avg_sal = (select min(avg_sal) from (select avg(sal) avg_sal from emp where empno in

(select mgr from emp) group by deptno) t );

10. 求比普通员工的最高薪水还要高的经理人名称(not in)

select ename from emp where empno in (select mgr from emp) and sal > (select max(sal) from (select e2.sal from emp e1 right join emp e2 on e1.mgr = e2.empno where e1.ename is null) t );

select ename from emp where empno in (select mgr from emp) and sal >

(select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null) );

//NOT IN遇到NULL则返回NULL，必须排除NULL值

11. 求薪水最高的前5名雇员

select empno,ename from (select * from emp order by sal desc) where rownum<=5;

12. 求薪水最高的第6到第10名雇(!important)

select ename,sal from (select t.*,rownum r from (select * from emp order by sal desc) t

) where r>=6 and r<=10;

13. 求最后入职的5名员工

select ename, to_char(hiredate,'YYYY"年"MM"月"DD"日"') hiredate from (select t.*,rownum r from (select * from emp order by hiredate desc)t )

where r<=5;

select ename, to_char(hiredate,'YYYY"年"MM"月"DD""') hiredate from (select t.*,rownum r from

(select * from emp order by hiredate)t

)where r>(select count(*)-5 from emp);

经典笔试题

中国移动sql面试题：

create table test(

id number(10) primary key,

type number(10) ,

t_id number(10),

value varchar2(5)

);

insert into test values(100,1,1,'张三');

insert into test values(200,2,1,'男');

insert into test values(300,3,1,'50');

insert into test values(101,1,2,'刘二');

insert into test values(201,2,2,'男');

insert into test values(301,3,2,'30');

insert into test values(102,1,3,'刘三');

insert into test values(202,2,3,'女');

insert into test values(302,3,3,'10');

请写出一条查询语句结果如下：

姓名性别年龄

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

张三男 50

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

2.一道SQL语句面试题，关于group by

表内容：

2005-05-09 胜

2005-05-09 负

2005-05-10 胜

2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

胜负

2005-05-09 2 2

2005-05-10 1 2

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

create table tmp(rq varchar2(10),shengfu varchar2(5));

insert into tmp values('2005-05-09','胜');

insert into tmp values('2005-05-09','负');

insert into tmp values('2005-05-10','胜');

insert into tmp values('2005-05-10','负');

4.create table STUDENT_SCORE

(

name VARCHAR2(20),

subject VARCHAR2(20),

score NUMBER(4,1)

);

insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);

insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);

insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);

insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);

insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);

insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);

insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);

insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);

insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

4.1得到类似下面的结果

姓名语文数学英语

王五 89 56 89

4.2有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：

大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。

显示格式：

语文数学英语

及格优秀不及格

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

5.请用一个sql语句得出结果

从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，

只是作为一个格式向大家请教。

table1

月份mon 部门dep 业绩yj

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

一月份 01 10

一月份 02 10

一月份 03 5

二月份 02 8

二月份 04 9

三月份 03 8

table2

部门dep 部门名称dname

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

01 国内业务一部

02 国内业务二部

03 国内业务三部

04 国际业务部

table3 （result）

部门dep 一月份二月份三月份

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

01 10

02 10 8

03 5 8

04 9

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

create table yj01(

month varchar2(10),

deptno number(10),

yj number(10)

)

insert into yj01(month,deptno,yj) values('一月份',01,10);

insert into yj01(month,deptno,yj) values('二月份',02,10);

insert into yj01(month,deptno,yj) values('二月份',03,5);

insert into yj01(month,deptno,yj) values('三月份',02,8);

insert into yj01(month,deptno,yj) values('三月份',04,9);

insert into yj01(month,deptno,yj) values('三月份',03,8);

create table yjdept(

deptno number(10),

dname varchar2(20)

)

insert into yjdept(deptno,dname) values(01,'国内业务一部');

insert into yjdept(deptno,dname) values(02,'国内业务二部');

insert into yjdept(deptno,dname) values(03,'国内业务三部');

insert into yjdept(deptno,dname) values(04,'国际业务部');

# 使用amoeba实现mysql读写分离 ###

1、什么是amoeba？ Amoeba(变形虫)项目，专注分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户端透明。具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。主要解决： • 降低数据切分带来的复杂多数据库结构 • 提供切分规则并降低数据切分规则给应用带来的影响 • 降低db 与客户端的连接数 • 读写分离 ###

2、为什么要用Amoeba 目前要实现mysql的主从读写分离，主要有以下几种方案：

1、通过程序实现，网上很多现成的代码，比较复杂，如果添加从服务器要更改多台服务器的代码。

2、通过mysql-proxy来实现，由于mysql-proxy的主从读写分离是通过lua脚本来实现，目前lua的脚本的开发跟不上节奏，而写没有完美的现成的脚本，因此导致用于生产环境的话风险比较大，据网上很多人说mysql-proxy的性能不高。

3、自己开发接口实现，这种方案门槛高，开发成本高，不是一般的小公司能承担得起。

4、利用阿里巴巴的开源项目Amoeba来实现，具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库，并且安装配置非常简单。国产的开源软件，应该支持，目前正在使用，不发表太多结论，一切等测试完再发表结论吧，哈哈！ ### amoeba安装 ##### 1、首先安装jdk，直接使用rpm包安装即可 ##### 2、下载amoeba对应的版本amoeba download | SourceForge.net，直接解压即可 ##### 3、配置amoeba的配置文件 **dbServers.xml** ```xml <?xml version="1.0" encoding="gbk"?> <!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd"> <amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">  <dbServer name="abstractServer" abstractive="true"> <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"> <property name="connectionManager">${defaultManager}</property> <property name="sendBufferSize">64</property> <property name="receiveBufferSize">128</property>  <property name="port">3306</property>  <property name="schema">msb</property>  <property name="user">root</property> <property name="password">123456</property> </factoryConfig> <poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool"> <property name="maxActive">500</property> <property name="maxIdle">500</property> <property name="minIdle">1</property> <property name="minEvictableIdleTimeMillis">600000</property> <property name="timeBetweenEvictionRunsMillis">600000</property> <property name="testOnBorrow">true</property> <property name="testOnReturn">true</property> <property name="testWhileIdle">true</property> </poolConfig> </dbServer> <dbServer name="writedb" parent="abstractServer"> <factoryConfig>  <property name="ipAddress">192.168.85.11</property> </factoryConfig> </dbServer> <dbServer name="slave" parent="abstractServer"> <factoryConfig>  <property name="ipAddress">192.168.85.12</property> </factoryConfig> </dbServer> <dbServer name="myslave" virtual="true"> <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">  <property name="loadbalance">1</property>  <property name="poolNames">slave</property> </poolConfig> </dbServer> </amoeba:dbServers> ``` **amoeba.xml** ```xml <?xml version="1.0" encoding="gbk"?> <!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd"> <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/"> <proxy>  <service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService">  <property name="port">8066</property>   <property name="connectionFactory"> <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory"> <property name="sendBufferSize">128</property> <property name="receiveBufferSize">64</property> </bean> </property> <property name="authenticateProvider"> <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"> <property name="user">root</property> <property name="password">123456</property> <property name="filter"> <bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController"> <property name="ipFile">${amoeba.home}/conf/access_list.conf</property> </bean> </property> </bean> </property> </service> <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">  <property name="executeThreadSize">128</property>  <property name="statementCacheSize">500</property>  <property name="serverCharset">utf8</property>  <property name="queryTimeout">60</property> </runtime> </proxy>  <connectionManagerList> <connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper"> <property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property> </connectionManager> </connectionManagerList>  <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader"> <property name="configFile">${amoeba.home}/conf/dbServers.xml</property> </dbServerLoader> <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"> <property name="ruleLoader"> <bean class="com.meidusa.amoeba.route.TableRuleFileLoader"> <property name="ruleFile">${amoeba.home}/conf/rule.xml</property> <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property> </bean> </property> <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property> <property name="LRUMapSize">1500</property> <property name="defaultPool">writedb</property> <property name="writePool">writedb</property> <property name="readPool">myslave</property> <property name="needParse">true</property> </queryRouter> </amoeba:configuration> ``` ##### 4、启动amoeba ```shell /root/amoeba-mysql-3.0.5-RC/bin/launcher ``` ### 4、测试amoeba ```sql --测试的sql --在安装amoeba的服务器上登录mysql mysql -h192.168.85.13 -uroot -p123 -P8066 --分别在master、slave、amoeba上登录mysql use msb select * from user; --在amoeba上插入数据 insert into user values(2,2); --在master和slave上分别查看表中的数据 select * from user; --将master上的mysql服务停止，继续插入数据会发现插入不成功，但是能够查询 --将master上的msyql服务开启，停止slave上的mysql，发现插入成功，但是不能够查询 ```

数据结构学习网站

GeeksforGeeks | A computer science portal for geeks

Data Structure Visualization

数据结构和算法动态可视化 (Chinese) - VisuAlgo

读写分离

主从原理

SQL

--给表添加注释

comment on table emp is '雇员表';

--给列添加注释

comment on column emp.ename is '雇员姓名';

/*sql语句学习

SELECT [DISTINCT] {*,column alias,..}

FROM table alias

Where 条件表达式

--查询雇员表中部门编号是10的员工

select empno,ename,job from emp where deptno = 10;

--dinstinct 去除重复数据

select distinct deptno from emp;

--去重也可以针对多个字段，多个字段值只要有一个不匹配就算是不同的记录

select distinct deptno,sal from emp;

--在查询的过程中可以给列添加别名，同时也可以给表添加别名

select e.empno 雇员编号,e.ename 雇员名称,e.job 雇员工作 from emp e where e.deptno = 10;

--给列起别名可以加as，也可以不加，看你心情

select e.empno as 雇员编号,e.ename as 雇员名称,e.job as 雇员工作 from emp e where e.deptno = 10;

--给列起别名，如果别名中包含空格，那么需要将别名整体用“”包含起来

select e.empno as "雇员编号",e.ename as "雇员名称",e.job as "雇员工作" from emp e where e.deptno = 10;

--查询表中的所有字段,可以使用*,但是在项目中千万不要随便使用*,容易被打死

select * from emp;

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

is null,is not null

between x and y

in（list），not in（list）

exists（sub－query）

like _ ,%,escape ‘‘ _% escape ‘’

-- =

select * from emp where deptno = 20;

--!=

select * from emp where deptno !=20;

--<> 不等于

select * from emp where deptno <> 20;

--<,

select sal from emp where sal <1500;

-->,

select sal from emp where sal >1500;

--<=,

select sal from emp where sal <=1500;

-->=,

select sal from emp where sal >=1500;

--any,取其中任意一个

select sal from emp where sal > any(1000,1500,3000);

--some,some跟any是同一个效果，只要大于其中某一个值都会成立

select sal from emp where sal > some(1000,1500,3000);

--all，大于所有的值才会成立

select sal from emp where sal > all(1000,1500,3000);

--is null,在sql的语法中，null表示一个特殊的含义，null != null,不能使用=，！=判断，需要使用is ,is not

select * from emp where comm is null;

--,is not null

select * from emp where comm is not null;

select * from emp where null is null;

--between x and y,包含x和y的值

select * from emp where sal between 1500 and 3000;

select * from emp where sal >=1500 and sal <=3000;

--需要进行某些值的等值判断的时候可以使用in和not in

--in（list），

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

--可是用and 和or这样的关键字，and相当于是与操作，or相当于是或操作

--and和or可能出现在同一个sql语句中，此时需要注意and和or的优先级

--and 的优先级要高于or，所以一定要将or的相关操作用（）括起来，提高优先级

select * from emp where deptno = 10 or deptno = 20;

--not in（list）

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

select * from emp where deptno != 10 and deptno !=20;

/*exists（sub－query）,当exists中的子查询语句能查到对应结果的时候，

意味着条件满足

相当于双层for循环

--现在要查询部门编号为10和20的员工，要求使用exists实现

select * from emp where deptno = 10 or deptno = 20;

--通过外层循环来规范内层循环

select *

from emp e

where exists (select deptno

from dept d

where (d.deptno = 10 or d.deptno = 20)

and e.deptno = d.deptno)

模糊查询：

like _ ,%,escape ‘‘ _% escape ‘’

在like的语句中，需要使用占位符或者通配符

_,某个字符或者数字仅出现一次

%，任意字符出现任意次数

escape,使用转义字符,可以自己规定转义字符

使用like的时候要慎重，因为like的效率比较低

使用like可以参考使用索引，但是要求不能以%开头

涉及到大文本的检索的时候，可以使用某些框架 luence，solr，elastic search

--查询名字以S开头的用户

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

--查询名字以S开头且倒数第二个字符为T的用户

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

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

--查询名字中带%的用户

select * from emp where ename like('%%%') escape('')

order by进行排序操作

默认情况下完成的是升序的操作，

asc:是默认的排序方式，表示升序

desc：降序的排序方式

排序是按照自然顺序进行排序的

如果是数值，那么按照从大到小

如果是字符串，那么按照字典序排序

在进行排序的时候可以指定多个字段，而且多个字段可以使用不同的排序方式

每次在执行order by的时候相当于是做了全排序，思考全排序的效率

会比较耗费系统的资源，因此选择在业务不太繁忙的时候进行

select * from emp order by sal;

select * from emp order by sal desc;

select * from emp order by ename;

select * from emp order by sal desc,ename asc;

--使用计算字段

--字符串连接符

select 'my name is '||ename name from emp;

select concat('my name is ',ename) from emp;

--计算所有员工的年薪

select ename,(e.sal+e.comm)*12 from emp e;

--null是比较特殊的存在，null做任何运算都还是为null，因此要将空进行转换

--引入函数nvl，nvl(arg1,arg2),如果arg1是空，那么返回arg2，如果不是空，则返回原来的值

select ename,(e.sal+nvl(e.comm,0))*12 from emp e;

--dual是oracle数据库中的一张虚拟表，没有实际的数据，可以用来做测试

select 100+null from dual;

--A

select * from emp where deptno =30;

--B

select * from emp where sal >1000;

--并集，将两个集合中的所有数据都进行显示，但是不包含重复的数据

select * from emp where deptno =30 union

select * from emp where sal >1000;

--全集，将两个集合的数据全部显示，不会完成去重的操作

select * from emp where deptno =30 union all

select * from emp where sal >1000;

--交集，两个集合中交叉的数据集，只显示一次

select * from emp where deptno =30 intersect

select * from emp where sal >1000;

--差集,包含在A集合而不包含在B集合中的数据，跟A和B的集合顺序相关

select * from emp where deptno =30 minus

select * from emp where sal >1000;

函数

--函数的测试

组函数又称为聚合函数

输入多个值，最终只会返回一个值

组函数仅可用于选择列表或查询的having子句

单行函数

输入一个值，输出一个值

--查询所有员工的薪水总和

select sum(sal) from emp;

--查看表中有多少条记录

select deptno,count(*) from emp group by deptno where count(*) >3;

--字符函数

--concat：表示字符串的连接等同于||

select concat('my name is ', ename) from emp;

--将字符串的首字母大写

select initcap(ename) from emp;

--将字符串全部转换为大写

select upper(ename) from emp;

--将字符串全部转换为小写

select lower(ename) from emp;

--填充字符串

select lpad(ename,10,'*') from emp;

select rpad(ename,10,'*') from emp;

--去除空格

select trim(ename) from emp;

select ltrim(ename) from emp;

select rtrim(ename) from emp;

--查找指定字符串的位置

select instr('ABABCDEF','A') from emp;

--查看字符串的长度

select length(ename) from emp;

--截取字符串的操作

select substr(ename,0,2) from emp;

--替换操作

select replace('ababefg','ab','hehe') from emp;

--数值函数

--给小数进行四舍五入操作，可以指定小数部分的位数

select round(123.123,2) from dual;

select round(123.128,2) from dual;

select round(-123.128,2) from dual;

--截断数据,按照位数去进行截取，但是不会进行四舍五入的操作

select trunc(123.128,2) from dual;

--取模操作

select mod(10,4) from dual;

select mod(-10,4) from dual;

--向上取整

select ceil(12.12) from dual;

--向下取整

select floor(13.99) from dual;

--取绝对值

select abs(-100) from dual;

--获取正负值

select sign(-100) from dual;

--x的y次幂

select power(2,3) from dual;

--日期函数

select sysdate from dual;

select current_date from dual;

--add_months,添加指定的月份

select add_months(hiredate,2),hiredate from emp;

--返回输入日期所在月份的最后一天

select last_day(sysdate) from dual;

--两个日期相间隔的月份

select months_between(sysdate,hiredate) from emp;

--返回四舍五入的第一天

select sysdate 当时日期,

round(sysdate) 最近0点日期,

round(sysdate,'day') 最近星期日,

round(sysdate,'month') 最近月初,

round(sysdate,'q') 最近季初日期,

round(sysdate,'year') 最近年初日期 from dual;

--返回下周的星期几

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

--提取日期中的时间

select

extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,

extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,

extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,

extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,

extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,

extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年

from dual;

--返回日期的时间戳

select localtimestamp from dual;

select current_date from dual;

select current_timestamp from dual;

--给指定的时间单位增加数值

select

trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)

trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)

trunc(sysdate)+(interval '1' hour), --加1小时(1/24)

trunc(sysdate)+(INTERVAL '1' DAY), --加1天(1)

trunc(sysdate)+(INTERVAL '1' MONTH), --加1月

trunc(sysdate)+(INTERVAL '1' YEAR), --加1年

trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒

trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒

trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟

trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟

from dual;

转换函数

在oracle中存在数值的隐式转换和显式转换

隐式转换指的是字符串可以转换为数值或者日期

显式转换：

to_char: 当由数值或者日期转成字符串的时候，必须要规定格式

select '999'+10 from dual;

--date ：to_char

select to_char(sysdate,'YYYY-MI-SS HH24:MI:SS') from dual;

-- number : to_char

select to_char(123.456789,'9999') from dual;

select to_char(123.456789,'0000.00') from dual;

select to_char(123.456789,'$0000.00') from dual;

select to_char(123.456789,'L0000.00') from dual;

select to_char(123456789,'999,999,999,999') from dual;

--to_date:转换之后都是固定的格式

select to_date('2019/10/10 10:10:10','YYYY-MM-DD HH24:MI:SS') from dual;

--to_number:转成数字

select to_number('123,456,789','999,999,999') from dual;

--显示没有上级管理的公司首脑

select ename,nvl(to_char(mgr),'boss') from emp where mgr is null;

--显示员工雇佣期满6个月后下一个星期五的日期

select hiredate,next_day(add_months(hiredate,6),'星期五') from emp;

--条件函数

--decode,case when

--给不同部门的人员涨薪，10部门涨10%，20部门涨20%，30部门涨30%

select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3) from emp;

select ename,

sal,

deptno,

case deptno

when 10 then

sal * 1.1

when 20 then

sal * 1.2

when 30 then

sal * 1.3

end

from emp;

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

create table test(

id number(10) primary key,

type number(10) ,

t_id number(10),

value varchar2(5)

);

insert into test values(100,1,1,'张三');

insert into test values(200,2,1,'男');

insert into test values(300,3,1,'50');

insert into test values(101,1,2,'刘二');

insert into test values(201,2,2,'男');

insert into test values(301,3,2,'30');

insert into test values(102,1,3,'刘三');

insert into test values(202,2,3,'女');

insert into test values(302,3,3,'10');

select * from test;

需求

将表的显示转换为

姓名性别年龄

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

张三男 50

select decode(type, 1, value) 姓名,

decode(type, 2, value) 性别,

decode(type, 3, value) 年龄

from test;

select min(decode(type, 1, value)) 姓名,

min(decode(type, 2, value)) 性别,

min(decode(type, 3, value)) 年龄

from test group by t_id;

组函数,一般情况下，组函数都要和groupby组合使用

组函数一般用于选择列表或者having条件判断

常用的组函数有5个

avg() 平均值,只用于数值类型的数据

min() 最小值，适用于任何类型

max() 最大值，适用于任何类型

count() 记录数,处理的时候会跳过空值而处理非空值

count一般用来获取表中的记录条数，获取条数的时候可以使用*或者某一个具体的列

甚至可以使用纯数字来代替，但是从运行效率的角度考虑，建议使用数字或者某一个具体的列

而不要使用*

sum() 求和，只适合数值类型的数据

select avg(sal) from emp;

select min(sal) from emp;

select max(sal) from emp;

select count(sal) from emp;

select sum(sal) from emp;

--group by,按照某些相同的值去进行分组操作

--group进行分组操作的时候，可以指定一个列或者多个列，但是当使用了groupby 之后，

--选择列表中只能包含组函数的值或者group by 的普通字段

--求每个部门的平均薪水

select avg(sal) from emp group by deptno;

--求平均新书大于2000的部门

select avg(sal),deptno from emp where sal is not null group by deptno having avg(sal) >2000 order by avg(sal);

select count(10000) from emp;

--部门下雇员的工资>2000 人数

select deptno,count(1) from emp where sal>2000 group by deptno

--部门薪水最高

select deptno,max(sal) from emp group by deptno;

--部门里面工龄最小和最大的人找出来,知道姓名

select deptno,min(hiredate),max(hiredate) from emp group by deptno;

select ename, deptno

from emp e

where hiredate in (select min(hiredate) from emp group by deptno)

or hiredate in (select max(hiredate) from emp group by deptno)

select * from emp

select mm2.deptno, e1.ename, e1.hiredate

from emp e1,

(select min(e.hiredate) mind, max(e.hiredate) maxd, e.deptno

from emp e

group by e.deptno) mm2

where (e1.hiredate = mm2.mind

or e1.hiredate = mm2.maxd)

and e1.deptno = mm2.deptno;

关联查询

--关联查询

select t1.c1,t2.c2 from t1,t2 where t1.c3 = t2.c4

在进行连接的时候，可以使用等值连接，可以使用非等值连接

--查询雇员的名称和部门的名称

select ename,dname from emp,dept where emp.deptno = dept.deptno;

--查询雇员名称以及自己的薪水等级

select e.ename,e.sal,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;

--等值连接，两个表中包含相同的列名

--非等值连接，两个表中没有相同的列名，但是某一个列在另一张表的列的范围之中

--外连接

select * from emp;

select * from dept;

--需要将雇员表中的所有数据都进行显示,利用等值连接的话只会把关联到的数据显示，

--没有关联到的数据不会显示，此时需要外连接

--分类：左外连接（把左表的全部数据显示）和右外连接（把右表的全部数据显示）

select * from emp e,dept d where e.deptno = d.deptno；--等值连接

select * from emp e,dept d where e.deptno = d.deptno(+);--左外连接

select * from emp e,dept d where e.deptno(+) = d.deptno;--右外连接

--自连接,将一张表当成不同的表来看待，自己关联自己

--将雇员和他经理的名称查出来

select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;

--笛卡尔积,当关联多张表，但是不指定连接条件的时候，会进行笛卡尔积，

--关联后的总记录条数为M*n，一般不要使用

select * from emp e,dept d;

--92的表连接语法有什么问题？？？？

--在92语法中，多张表的连接条件会方法where子句中，同时where需要对表进行条件过滤

--因此，相当于将过滤条件和连接条件揉到一起，太乱了，因此出现了99语法

--99语法

CROSS　JOIN

NATURAL JOIN

USING子句

ON子句

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

Inner join

--cross join 等同于92语法中的笛卡儿积

select * from emp cross join dept;

--natural join 相当于是等值连接，但是注意，不需要写连接条件，会从两张表中找到相同的列做连接

--当两张表中不具有相同的列名的时候，会进行笛卡儿积操作,自然连接跟92语法的自连接没有任何关系

select * from emp e natural join dept d ;

select * from emp e natural join salgrade sg;

--on子句，可以添加任意的连接条件，

--添加连接条件相当于92语法中的等值连接

select * from emp e join dept d on e.deptno = d.deptno;

--相当于92语法中的非等值连接，

select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;

--left outer join ,会把左表中的全部数据正常显示，右表没有对应的数据直接显示空即可

select * from emp e left outer join dept d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno = d.deptno(+);

--right outer join ,会把右表中的全部数据正常显示，左表中没有对应的记录的话显示空即可

select * from emp e right outer join dept d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno(+) = d.deptno;

--full outer join ,相当于左外连接和右外连接的合集

select * from emp e full outer join dept d on e.deptno = d.deptno;

--inner outer join，两张表的连接查询，只会查询出有匹配记录的数据

select * from emp e inner join dept d on e.deptno = d.deptno;

select * from emp e join dept d on e.deptno = d.deptno;

--using,除了可以使用on表示连接条件之外，也可以使用using作为连接条件,此时连接条件的列不再归属于任何一张表

select deptno from emp e join dept d using(deptno);

select e.deptno,d.deptno from emp e join dept d on e.deptno = d.deptno;

--总结:两种语法的SQL语句没有任何限制，再公司中可以随意使用，但是建议使用99语法，不要使用92语法，SQL显得清楚明了

--检索雇员名字、所在单位、薪水等级

select e.ename, d.loc, sg.grade

from emp e

join dept d

on e.deptno = d.deptno

join salgrade sg

on e.sal between sg.losal and sg.hisal;

子查询：

嵌套再其他sql语句中的完整sql语句，可以称之为子查询

分类：

单行子查询

多行子查询

--有哪些人的薪水是在整个雇员的平均薪水之上的

--1、先求平均薪水

select avg(e.sal) from emp e;

--2、把所有人的薪水与平均薪水比较

select * from emp e where e.sal > (select avg(e.sal) from emp e);

--我们要查在雇员中有哪些人是经理人

--1、查询所有的经理人编号

select distinct e.mgr from emp e;

--2、再雇员表中过滤这些编号即可

select * from emp e where e.empno in (select distinct e.mgr from emp e);

--每个部门平均薪水的等级

--1、先求出部门的平均薪水

select e.deptno,avg(e.sal) from emp e group by e.deptno;

--2、跟薪水登记表做关联，求出平均薪水的等级

select t.deptno, sg.grade

from salgrade sg

join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t

on t.vsal between sg.losal and sg.hisal;

--1、求平均薪水最高的部门的部门编号

--求部门的平均薪水

select e.deptno,avg(e.sal) from emp e group by e.deptno;

--求平均薪水最高的部门

select max(t.vsal) from (select e.deptno,avg(e.sal) vsal from emp e group by e.deptno) t

--求部门编号

select t.deptno

from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t

where t.vsal =

(select max(t.vsal)

from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t);

--2、求部门平均薪水的等级

--3、求部门平均的薪水等级

--求部门每个人的薪水等级

select e.deptno, sg.grade

from emp e

join salgrade sg

on e.sal between sg.losal and sg.hisal;

--按照部门求平均等级

select t.deptno, avg(t.grade)

from (select e.deptno, sg.grade

from emp e

join salgrade sg

on e.sal between sg.losal and sg.hisal) t

group by t.deptno;

--限制输出，limit，mysql中用来做限制输出的，但是oracle中不是

--再oracle中，如果需要使用限制输出和分页的功能的话，必须要使用rownum，

--但是rownum不能直接使用，需要嵌套使用

--4、求薪水最高的前5名雇员

select *

from (select * from emp e order by e.sal desc) t1

where rownum <= 5

select * from emp e where rownum <=5 order by e.sal desc

--5、求薪水最高的第6到10名雇员

select t1.*,rownum

from (select * from emp e order by e.sal desc) t1

where rownum <= 10

--使用rownum的时候必须要再外层添加嵌套，此时才能将rownum作为其中的一个列，然后再进行限制输出

select *

from (select t1.*, rownum rn

from (select * from emp e order by e.sal desc) t1

where rownum <= 10) t

where t.rn > 5

and t.rn <= 10;

select *

from (select t1.*, rownum rn

from (select * from emp e order by e.sal desc) t1) t

where t.rn > 5

and t.rn <= 10;

行转列

create table tmp(rq varchar2(10),shengfu varchar2(5));

insert into tmp values('2005-05-09','胜');

insert into tmp values('2005-05-09','负');

insert into tmp values('2005-05-10','胜');

insert into tmp values('2005-05-10','负');

胜负

2005-05-09 2 2

2005-05-10 1 2

select rq,decode(shengfu,'胜',1),decode(shengfu,'负',2) from tmp;

select rq,

count(decode(shengfu, '胜', 1)) 胜,

count(decode(shengfu, '负', 2)) 负

from tmp

group by rq;

create table STUDENT_SCORE