Oracle数据库之第一篇
2019-10-05 本文已影响0人
小小一技术驿站
1 : Oracle 简介 : 是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器IP，端口，用户名、密码，点击：连接
(CLIENT/SERVER)或B/S体系结构的数据之一.
2 : Oracle数据库的体系结构:
数据库: database
Oracle数据库是数据的物理存储.这就包括(数据文件ORA或者DBF,控制文件,联机日志,参数文件).其实Orcale数据库的概念和其他数据不一样,这里的数据库是一个操作系统只有一个库.可以
看做是Orcale就只有一个大数据库.
例如 :一个Oracle实例有一系列的后台进程和内存结构组成.一个数据库可以有n个实例.
数据文件(dbf) :
数据文件是数据库的物理存储单位.数据库的数据是存储在表空间中的,真正是在某一个或者多个数据文件中.而一个表空间可以由一个或多个数据文件组成,一个数据文件只能属于一个表空间.
一旦数据文件被加入到某个表空间后,就不能删除这个文件,如果要删除某个数据文件,只能删除其所属于的表空间才行.
表空间 :
表空间是Oracle对物理数据库上相关数据文件(ORA或者DBF文件)的逻辑映射.一个数据库再逻辑上被划分成一到若干个表空间,每个表空间包含了在逻辑上相关联的一组结构.每个数据库至少有一个表
空间(称之为system表空间).
每个表空间由同一磁盘上的一个或多个文件组成,这些文件叫数据文件(datafile).一个数据文件只能属于一个表空间.
用户 :
用户是在实例下建立的.不同实例中可以建相同名字的用户.表的数据,是用户放入某一个表空间的,而这个表空间会随机把这些表数据放到一个或者多个数据文件中.
由于oracle的数据库不是普通的概念,oracle是有用户和表空间对数据进行管理和存放的.但是表不是有表空间去查询的,而是由用户去查的.因为不同用户可以在同一个表空间建立同一个名字的表,表里区分就是用户了.
Oracle 应用开发实战
一、Oracle 的基本概念和安装
Oracle 简介
ORACLE 数据库系统是美国ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一
组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S 体系结构的数据库之一。
比如SilverStream 就是基于数据库的一种中间件。ORACLE 数据库是目前世界上使用最为广
泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个
关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它
的所有知识，只要在一种机型上学习了ORACLE 知识，便能在各种类型的机器上使用它。
Oracle10g 的安装
1. 解压oracle 数据库安装包，如果是win7 或者win8 系统右键点击setup.exe 选择兼容性，
以xp 方式，并且以管理员方式运行，以及其他所有用户都按着此规则如图
2. 如果是xp 系统可以直接并双击解压目录下的setup.exe，出现安装界面，如下：
3. 输入口令和确认口令，如：baidu，点击下一步，出现如下进度条，
注：此口令即是管理员密码。
11
4. 检查先决条件，选中红框所示的选择框，如下图：
5. 点击“下一步”，出现“概要”界面，点击“安装”。
11
6. 出现安装进度条，等待安装完成，如下图：
7. 安装完成后，自动运行配置向导，如下图，等待其完成：
8. 完成后，出现“口令管理”界面，点击“口令管理”，如下图：
11
9. 将SCOTT 和HR 用户的沟去掉（解锁这两个账户），如下图所示，点击“确定”：
10. 回到“口令管理”界面，点击“确定”，如下图：
11. 安装结束，点击“退出”。
11
虚拟网卡设置
本机和虚拟机之间能相互访问，它们的IP 段必须相同，但是本机将会连接不同的网络
环境（比如教室、宿舍、家庭），那么本机的IP 段会产生变化就连不上虚拟机了，为了避
免这种情况我们让本机和虚拟机之间用虚拟网卡的方式互相通信，配置方式参考如下文档：
PLSQL Developer 客户端工具的安装
1. 网络的测试
参考：
11
2. 安装PLSQL Developer 客户端
3. 中文乱码的处理
二、Oracle 数据库的体系结构
数据库：database
Oracle 数据库是数据的物理存储。这就包括（数据文件ORA 或者DBF、
控制文件、联机日志、参数文件）。其实Oracle 数据库的概念和其它
数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作
是Oracle 就只有一个大数据库。
实例：
11
一个Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound
Processes)和内存结构（Memory Structures)组成。一个数据库可以有n
个实例。
数据文件（dbf）:
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中
的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个
或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据
文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个
数据文件，只能删除其所属于的表空间才行。
表空间：
表空间是Oracle 对物理数据库上相关数据文件（ORA 或者DBF 文件）
的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个
表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表
空间(称之为system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文
件(datafile)。一个数据文件只能属于一个表空间。
用户：
用户是在实例下建立的。不同实例中可以建相同名字的用户。
注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这
些表数据放到一个或者多个数据文件中。
由于oracle 的数据库不是普通的概念，oracle 是有用户和表空间对
数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户
去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这
里区分就是用户了！
11
SCOTT 用户和HR 用户
Oracle 为了让学习者更好的进行学习，在安装成功后，也创建了初始的用户，其中SCOTT
与HR 就是初始的普通用户。这些用户下面都默认存在了表结构，我们重点掌握SCOTT 用户
下的所有表，如下所示：
SCOTT 用户下的表
11
HR 用户下的表
11
三、基本查询
sql 简介
结构化查询语言(Structured Query Language)简称SQL(发音：/ˈɛs kjuː ˈɛl/ "S-Q-L")，结构
化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数
据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，
允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解
具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构
化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的
灵活性和强大的功能。
DML(数据库操作语言): 其语句包括动词INSERT，UPDATE 和DELETE。它们分别用于添
加，修改和删除表中的行。也称为动作查询语言。
DDL(数据库定义语言): 其语句包括动词CREATE 和DROP。在数据库中创建新表或删除
表（CREAT TABLE 或DROP TABLE）；为表加入索引等。DDL 包括许多与人数据库目录
中获得数据有关的保留字。它也是动作查询的一部分。
DCL(数据库控制语言):它的语句通过GRANT 或REVOKE 获得许可，确定单个用户和用户
组对数据库对象的访问。某些RDBMS 可用GRANT 或REVOKE 控制对表单个列的访
问。
Select 语句的语法格式和示例
1. 查询语法
Select * |列名from 表名
11
2.别名用法
在查询的结果列中可以使用别名
Select 列名别名，列名别名，... from emp;
别名中，有没有双引号的区别就在于别名中有没有特殊的符号或者关键字。
3.消除重复的数据
Select distinct *|列名, ... from emp;
使用distinct 可以消除重复的行，如果查询多列的必须保证多列都重复才能去掉重复
4. 查询中四则运算
查询每个雇员的年薪
select ename, sal*12 from emp;
select ename, sal*12 income from emp;
11
Sql 中支持四则运算“+，-，*，/”
什么是空值？
空值是无效的，未指定的，未知的或不可预知的值
空值不是空格或者0 。
注意：*、包含null 的表达式都为null
*、空值永远不等于空值
11
连接符||
字符串连接查询
Mysql 中实现方法：
查询雇员编号，姓名，工作
编号是：7369 的雇员， 姓名是：smith，工作是：clerk
字符串的连接使用‘||’
四、条件查询和排序
使用where 语句对结果进行过滤
比较运算符
11
其他比较运算符
逻辑运算符
Where 语句示例
1. 非空和空的限制
示例：查询每月能得到奖金的雇员
分析：只要字段中存在内容表示不为空，如果不存在内容就是null，
语法：列名IS NOT NULL
为空列名IS NULL
11
范例：查询工资大于1500 并且有奖金领取的雇员
分析：多个查询条件同时满足之间使用‘AND’
范例:查询工资大于1500 或者有奖金的雇员
分析：多个查询条件或满足，条件之间使用“OR”
范例：查询工资不大于1500 和没有奖金的人
语法：NOT(查询条件)
2.范围限制
范例：基本工资大于1500 但是小于3000 的全部雇员
分析：sal>1500, sal<3000
11
Between and 等于sal > =1500 and sal <= 3000
范例：查询1981-1-1 到1981-12-31 号入职的雇员
分析：between and 不仅可以使用在数值之间，也可以用在日期的区间
范例：查询雇员名字叫smith 的雇员
在oracle 中的查询条件中查询条件的值是区分大小写的
范例:查询雇员编号是7369,7499,7521 的雇员编号的具体信息
如果使用之前的做法可以使用OR 关键字
11
实际上，此时指定了查询范围，那么sql 可以使用IN 关键字
语法: 列名IN (值1，值2，....)
列名NOT IN (值1， 值2，...)
其中的值不仅可以是数值类型也可以是字符串
范例:查询雇员姓名是’SMITH’,’ALLEN’,’WARD’的雇员具体信息
3．模糊查询
在常用的站点中经常会有模糊查询，即：输入一个关键字，把符合的内容全部的查询出
来，在sql 中使用LIKE 语句完成。
在LIKE 中主要使用以下两种通配符
“%”：可以匹配任意长度的内容
“_”:可以匹配一个长度的内容
范例：查询出所有雇员姓名中第二个字符包含“M”的雇员
在LIKE 中如果没有关键字表示查询全部
11
查询名字中带有“M”的雇员
在oracle 中不等号的用法可以有两种形式“<>”和“!=”
范例：查询雇员编号不是7369 的雇员信息
11
使用order by 对结果排序
1.排序的语法
在sql 中可以使用ORDER BY 对查询结果进行排序
语法：SELECT * |列名FROM 表名{WEHRE 查询条件} ORDER BY 列名1 ASC|DESC，列名
2...ASC|DESC
范例：查询雇员的工资从低到高
分析：ORDER BY 列名默认的排序规则是升序排列，可以不指定ASC，如果按着降序排列必
须指定DESC
11
如果存在多个排序字段可以用逗号分隔
注意ORDER BY 语句要放在sql 的最后执行。
2.排序中的空值问题
当排序时有可能存在null 时就会产生问题，我们可以用nulls first , nulls last 来指定null 值
显示的位置。
--查询雇员的工资从低到高
select * from emp order by sal nulls first;
select * from emp order by sal desc nulls last ;
11
五、单行函数
什么是SQL 的函数？
函数的类型
单行函数
11
字符函数
示例：
接收字符输入返回字符或者数值，dual 是伪表
1. 字符串的连接可以使用concat 可以使用“||”建议使用“||”
concat('hello', 'world')
2. 字符串的截取，使用substr，第一个参数是源字符串，第二个参数是开始索引，第
三个参数长度，开始的索引使用1 和0 效果相同
substr('hello', 1,3)
3. 获取字符串的长度
length('hello')
4. 字符串替换，第一个参数是源字符串，第二个参数被替换的字符串，第三个是替换
11
字符串
replace('hello', 'l','x')
数值函数
日期函数
Oracle 中的日期：
Oracle 中的日期型数据实际含有两个值: 日期和时间。
默认的日期格式是DD-MON-RR 。
日期的数学运算
在日期上加上或减去一个数字结果仍为日期
两个日期相减返回日期之间相差的天数
可以用数字除24
日期函数示例
1. 范例：查询雇员的进入公司的周数。
分析：查询雇员进入公司的天数(sysdate – 入职日期)/7 就是周数
11
2. 获得两个时间段中的月数：MONTHS_BETWEEN()
范例：查询所有雇员进入公司的月数
3. 获得几个月后的日期：ADD_MONTHS()
范例：求出三个月后的日期
转换函数
11
TO_CHAR 函数对日期的转换
日期的格式：
TO_CHAR 函数对数字的转换
数字转换的格式：
TO_NUMBER 和TO_DATE 函数
11
示例：
1. TO_CHAR:字符串转换函数
范例：查询所有的雇员将将年月日分开，此时可以使用TO_CHAR 函数来拆分
拆分时需要使用通配符
年：y, 年是四位使用yyyy
月：m, 月是两位使用mm
日：d, 日是两位使用dd
在结果中10 以下的月前面被被补了前导零，可以使用fm 去掉前导零
2. TO_NUMBER:数值转换函数
TO_NUMBER 可以把字符串转换成数值
3. TO_DATE:日期转换函数
TO_DATE 可以把字符串的数据转换成日期类型
11
通用函数
什么是通用函数？
这些函数适用于任何数据类型，同时也适用于空值
常用的通用函数
通用函数示例
1．空值处理nvl
范例：查询所有的雇员的年薪
我们发现很多员工的年薪是空的，原因是很多员工的奖金是null，null 和任何数值计算都是
null，这时我们可以使用nvl 来处理。
11
条件表达式
什么是条件表达式？
在SQL 语句中使用IF-THEN-ELSE
实现的方式：
CASE 表达式：SQL99 的语法，类似Basic，比较繁琐
DECODE 函数：Oracle 自己的语法，类似Java，比较简介
CASE 表达式
DECODE 函数
条件表达式示例: 根据10 号部门员工的工资，显示税率
六、多行函数
什么是多行函数？
分组函数作用于一组数据，并对一组数据返回一个值。
也叫：组函数、分组函数
组函数会忽略空值；NVL 函数使分组函数无法忽略空值
11
常用的多行函数
多行函数示例
1.统计记录数count()
范例：查询出所有员工的记录数
不建议使用count(*)，可以使用一个具体的列以免影响性能。
2.最小值查询min()
范例：查询出来员工最低工资
3.最大值查询max()
范例：查询出员工的最高工资
11
4.查询平均值avg()
范例：查询出员工的平均工资
5.求和函数sum()
范例：查询出20 号部门的员工的工资总和
分组数据
范例：查询每个部门的人数
11
范例：查询出每个部门的平均工资
范例：查询出来部门编号，和部门下的人数
我们发现报了一个ORA-00937 的错误
注意：
1. 如果使用分组函数，SQL 只可以把GOURP BY 分组条件字段和分组函数查询出来，
不能有其他字段。
2. 如果使用分组函数，不使用GROUP BY 只可以查询出来分组函数的值
过滤分组数据
范例：查询出部门平均工资大于2000 的部门
11
WHERE 和HAVING 的区别
最大区别在于：where 后面不能有组函数
练习 SQL语句 :
/*
基本查询
select 列名|* from 表名
*/
select * from emp;
select emp.*,1+1 from emp;
select 1+1 from emp where empno=7369--太麻烦
--获取系统时间
select sysdate from dual;
-------------------------------------------
/*
dual 虚拟表 没有实际意义 为了补全oracle数据库查询语法
*/
--查看虚拟表
select * from dual;
select 1+1 from dual;
/*
查询员工表中所有记录
*/
select * from emp;
--查询员工的编号和姓名
select empno,ename from emp;
--给列起别名特殊字符（#$）和数字需要使用双引号处理
select empno as "员工编号",ename "员工姓名",job 工作 from emp;
select empno "员工!编号",ename "员工姓名",job 工作 from emp;
select empno "员工!编号",ename "员工姓名",job "工 作" from emp;
select empno "员工!编号",ename "员工姓名",job 123 from emp;
select empno "员工!编号",ename "员工姓名",job 工#作 from emp;
--查询员工的工作 --distinct 关键字去重重复记录
select distinct job from emp;
--查询员工的年薪 null值参与运算结果为空
--使用nvl函数处理空值 nvl(v1,v2) v1是被判断的数值 v1为空v2是返回值 -- 不为空 返回v1
select sal*12+comm from emp;
select sal*12+nvl(comm,0) 年薪 from emp;
--列值的拼接 员工编号7369员工姓名SMITH
--mySql 可以用一个函数 concat实现
--oracle数据库使用concat函数实现 oracle只支持两个参数做拼接
select concat('员工编号',empno) from emp;
select concat(concat('员工编号',empno),'员工姓名') from emp;--多重嵌套实现麻烦
--oracle数据库 特有连接符 ||
--java '员工编号'+empno+'员工姓名'+ename
select '员工编号: '||empno||'员工姓名: '||ename 员工简介 from emp;
/*
条件查询
需要使用关键字 where 条件 目的是过滤出满足条件的记录
where 后的条件表达式
比较运算表达式 > < >= <= = != <>
逻辑运算 and or not
其他运算 between and 判断区间 包含边界
in not in 判断范围
like 模糊查询 % 匹配0个或多个
_ 占位一个字符
is null is not null 判断空值
*/
---查询员工的工作不是MANAGER的员工信息
select * from emp where job !='MANAGER';
select * from emp where job <>'MANAGER';
--查询员工工作是SALESMAN 并且工资>1500的员工信息
select * from emp where job = 'SALESMAN' and sal >1500
select * from emp where job = 'SALESMAN' or sal >1500
--查询员工的工资在 1500 和3000范围内
select * from emp where sal between 1500 and 3000;--会不会提取正好1500和3000？
--查询员工的工作 是MANAGER 或 PRESIDENT
select * from emp where job ='MANAGER' or job = 'PRESIDENT'
--使用in来实现
select * from emp where job in ('MANAGER','PRESIDENT')
--查询员工姓名包含M的员工信息
select * from emp where ename like '%M%'
--查询员工姓名第二位为M的员工信息
select * from emp where ename like '_M%'
--查询有奖金的员工信息
select * from emp where comm is not null
--过滤奖金为0的数据
select * from emp where comm is not null and comm != 0
select * from emp where comm > 0
/*
根据需求对数据做排序
order by 列 排序类型
倒叙 desc
正序 asc 默认的 可以不写
*/
--查询员工数据 按照工资做倒叙排序
select * from emp order by sal desc
select * from emp order by sal --默认正序
--按照奖金倒叙排序 取第一位最高工资的
select * from emp order by comm desc
--空值倒叙排序 默认在记录之上 处理null值显示记录结尾
--nulls last 指定空值记录 置于记录末尾
select * from emp order by comm desc nulls last
select * from emp order by comm
/*
函数
单行函数
理解概念： 针对每条记录中的数值处理 只会影响本条记录
影响多少条 返回多少条 nvl()
数值函数
字符函数
日期函数
转换函数
通用函数
多行函数 聚合函数
理解概念： 针对表中多条记录参与运算 结果是一条记录
count() 统计记录数
sum() 求和运算
avg() 求平均值
min() 求最小值
max() 求最大值
*/
/*
数值函数 对数值做处理的函数
四舍五入 round(v1,v2) v1是原始的数值,v2是保留的小数位数
截取 trunc(v1,v2) v1是原始的数值,v2是保留的小数位数 不做四舍五入
取余数 mod(v1,v2) 10%3 答案 --1
*/
--47.628四舍五入操作
select round(47.628) from dual; --48 默认小数位0
select round(47.628,0) from dual; --48
select round(47.628,1) from dual; --47.6
select round(47.628,2) from dual; --47.63
select round(47.628,-1) from dual; --50
select round(47.628,-2) from dual; --0
--47.628截取操作
select trunc(47.628) from dual; --47
select trunc(47.628,0) from dual; --47
select trunc(47.628,1) from dual; --47.6
select trunc(47.628,2) from dual; --47.62
select trunc(47.628,-1) from dual; -- 40
select trunc(97.628,-2) from dual; --0
--mod函数求余数
select mod(10,3) from dual; --1
/*
字符函数 对字符串处理
获取字符串长度 length()
截取字符串 substr(v1,v2,v3) v1是原始字符串 v2是截取起始位 v3 截取的长度
替换字符串 replace(v1,v2,v3) v1是原始字符串 v2被替换的字符串 v3 替换后的字符
替换匹配的所有字符
大小写转换 upper() lower()
用于验证码 Xy8Yo xy98yo XY8YO
*/
---abcde
select length('abcde') from dual;
---截取字符串 从0和1开始都是第一位开始截取
select substr('abcde',0,2) from dual; --ab
select substr('abcde',1,2) from dual; -- ab
select substr('abcde',-1,2) from dual; -- e
select substr('abcde',-2,2) from dual; -- de
--替换字符串
select replace('hello','l','o') from dual; --heooo
--查询员工表中姓名为SMITH
select * from emp where ename=upper('smith');
select * from emp where ename=upper('SMITH');
select * from emp where ename=upper('SmiTH');
/*
通用函数
nvl(v1,v2) 处理空值使用 v1为空返回v2
不为空是v1本身
nvl2(v1,v2,v3) v1是被判断的数值 不为空v2是返回值 为空v3也是返回值
concat(v1,v2) 只能传两个参数 做拼接使用
*/
select nvl2(null,2,3) from dual;
/*
转换函数
数值和字符的转换
日期和字符的转换
转换数值 to_number('123')
转换字符 to_char(v1,v2) v1是被转换的数据v2是转换后的格式
转换日期 to_date(v1,v2) v1是被转换的字符(日期格式的字符串) v2是转换的日期格式
*/
select to_number('123') +1 from dual;--124
select '123'+1 from dual; --to_number 有点鸡肋
--查询员工的工资800 9000 将工资转换成货币格式
select to_char(sal,'$9,999,999.00') from emp;
---日期和字符的互换
--查询当前日期 转换字符
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;--oracle数据分钟使用mi
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'day') from dual;--获取星期
---字符串转日期
select to_date('2017-09-13','yyyy-mm-dd') from dual;
select to_date('2017-09-13 11:56:56','yyyy-mm-dd hh:mi:ss') from dual;
select to_date('2017-09-13','yyyy-mm-dd hh:mi:ss') from dual;--如果是JSP页面取日期 00:00:00
select to_date('2017-09-13 23:56:56','yyyy-mm-dd hh24:mi:ss') from dual;--24表明24小时制
/*
日期函数
获取系统时间 sysdate
给系统增加时间 add_months(v1,v2) v1是时间，v2是增加的月数
获取系统时间间隔的月数 months_between(v1,v2) 两个参数都是日期
*/
--查询3个月之后的日期
select add_months(sysdate,3) from dual;
--获取间隔的月数 获取员工到目前为止入职多少个月
select months_between(sysdate,hiredate) from emp;
--获取间隔的天数 日期和日期做运算结果为天数
select round(sysdate-hiredate) from emp;
/*
多行函数 聚合函数
特点是 空值记录不参与运算 忽略空值的记录
count() 统计记录数
sum() 求和运算
avg() 求平均值
min() 求最小值
max() 求最大值
*/
--统计员工表记录数如果表中存在索引 三种形式效率一致
-- 如果没有索引 使用后两种
select count(*) from emp; ---公认没有索引效率最低
select count(empno) from emp;
select count(100) from emp;
--使用奖金列作统计
select count(comm) from emp; --4
select sum(comm) from emp; --2200
select avg(comm) from emp; --550
/*
聚合函数搭配分组使用
group by 分组的列
分组后过滤数据使用 having
分组之前使用where
使用规则
如果使用group by做分组 那么 select 语句中只能查询分组使用的列
和 聚合函数
*/
--查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno
--多个列作分组
select ename,deptno,avg(sal) from emp group by deptno,ename
--将job和部门做分组
select job,deptno,count(*) from emp group by deptno,job
--查询部门平均工资>2000的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal) >2000
--起别名做条件 oracle不可以 mySql可以
select deptno,avg(sal) s from emp group by deptno having s >2000
--mySql 和oracle都不可以
select sal s from emp where s>1500 --条件不识别别名
--查询部门中员工的工资大于1500的部门平均工资
select deptno,avg(sal) from emp where sal>1500 group by deptno;
select deptno,avg(sal) from emp group by deptno;
/*
条件表达式 if.. 处理语句 else
是所有sql数据库都支持的
case 判断列
when 列值 then
显示值
when 列值 then
显示值
else
默认值
end
*/
--查询员工的工作 使用汉字显示 MANAGER 经理 PRESIDENT 总裁
select case job
when 'MANAGER' then
'经理'
when 'PRESIDENT' then
'总裁'
else
'普通员工'
end
from emp;
--默认值不写 匹配不上显示空
select case job
when 'MANAGER' then
'经理'
when 'PRESIDENT' then
'总裁'
end
from emp;
---数据库oracle 有个特有条件表达式 decode(v1,v2,v3) v1是被判断的列，v2是等于值，v3是显示值
select decode(job,'MANAGER','经理','PRESIDENT','总裁','普通员工') from emp;
练习2 :
1. 查询工资大于12000的员工姓名和工资
2. 查询员工号为176的员工的姓名和部门号
3. 选择工资不在5000到12000的员工的姓名和工资
4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间
5. 选择在20或50号部门工作的员工姓名和部门号
6. 选择在1994年雇用的员工的姓名和雇用时间
7. 选择公司中没有管理者的员工姓名及job_id
8. 选择公司中有奖金的员工姓名，工资和奖金级别
9. 选择员工姓名的第三个字母是a的员工姓名
10. 选择姓名中有字母a和e的员工姓名
11. 显示系统时间
12. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
13. 将员工的姓名按首字母排序，并写出姓名的长度（length）
14. 查询各员工的姓名，并显示出各员工在公司工作的月份数
15. 查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列
select * from employees;
select first_name,salary from employees where salary > 12000;
select first_name,manager_id from employees where employee_id = 176;
select first_name,salary from employees where salary < 5000 or salary > 12000;
select first_name,job_id,hire_date from employees where hire_date > 1998-02-01 and hire_date < 1998-05-01;
select FIRST_NAME "名字",employee_id "员工id",hire_date "入职时间" from EMPLOYEES where hire_date between '1-2月-1998' and '1-5月-1998';
select first_name,department_id from employees where department_id = 20 or department_id = 50;
select first_name,hire_date from employees where hire_date between '1-1月-1994' and '31-12月-1994';
select first_name,salary,commission_pct from employees where commission_pct is not null and commission_pct != 0;
select first_name from employees where first_name like '__a%';
select first_name from employees where first_name like '%a%e%' or first_name like '%e%a%';
select employees.*,1+1 from employees;
select sysdate from dual;
select employee_id,first_name,salary,salary*1.2 from employees;
select first_name,length(first_name) from employees order by substr(first_name,0,1) desc;
select first_name,months_between(sysdate,hire_date) from employees;
select first_name,round(months_between(sysdate,hire_date)) from employees;
select first_name,round(months_between(sysdate,hire_date)) from employees order by round(months_between(sysdate,hire_date)) desc;
select first_name,job_id from employees where manager_id = 0 or manager_id is null;
