创建用户导入表:
--创建用户 scott 并设置密码为 tiger
create user scott identified by tiger
--用户刚刚创建没有任何的权限,连登录的权限都没有
--给用户授予权限。
--角色:一个角色是一个权限的集合。
--常用的角色:connect Resource。
grant connect, resource to scott
--给scott 导入4张表。
--复制scott.sql中的内容,粘贴到一个命令窗口。
--BONUS:奖金表: ename job sal comm
select * from bonus
--DEPT:部门表 deptno dname loc
select * from dept
--EMP:员工表 empno ename job mgr hriedate sal comm deptno
select * from emp
--SALGRADE :工资登记表 grade losal hisal
select * from salgrade
基本查询操作:
--最简单的sql 语句
--查询所有员工的信息
--* 通配符 代表所有的。 select 后跟的是要查询的内容。from 后跟表的名称。
select * from emp
--部分内容查询
--查询员工的编号,名称,工资,部门编号
select empno, ename, sal, deptno from emp
--使用算术表达式
--查询员工的姓名,工作,年薪
select ename, job, sal*12 from emp
--把奖金加上, 任何数据和 null 运算,结果还是 null。
select ename, job, sal*12+comm from emp
--使用别名 在oracle 中 字符串 使用单引号,只有别名使用双引号 来表是字符串 as 可以缺省。
--注意:别名 可以使用双引号,不能使用单引号,或者不加双引号。
select ename 名字 , job as "工种" , sal*12 年薪 from emp
--使用连接符 || 相当于 java 中的 + 连接符
--编号是XXX的员工的名字为XXXX,入职日期为XXX
select '编号是' || empno || '的员工的名字为' || ename ||',入职日期为:'|| hiredate from emp
--去除重复行 distinct
--显式所有的部门编号
select deptno from emp
select distinct deptno from emp
--去除多个字段组合的重复行
select distinct deptno, job from emp
--排序 order by 默认是升序排列 asc 降序 desc
select * from emp order by deptno asc
select distinct deptno, job from emp order by deptno desc
--查询员工的所有的信息,员工的部门编号升序排列,部门编号相同的,工资降序排列
select * from emp order by deptno asc ,sal desc
--字符串排序
select ename from emp order by ename
--排序的时候,使用字段的别名
select ename, job, sal*12 as 年薪 from emp order by sal*12
select ename, job, sal*12 as 年薪 from emp order by 年薪
--sql 中那些内容是大小写敏感的?哪些是不敏感的。
--关键字 大小写不敏感
SELECT * FROM emp
--表名 大小写不敏感
select * from EMP
--字段名 大小写不敏感
select ENAME, job, MGR from emp
--元组的内容 字段的内容,大小写是敏感的。
select * from emp where ename='SMITH'
--where 子句 后跟筛选数据的条件
--作用:进行行数据的过滤
--查询姓名 为 scott的员工的信息
select * from emp where ename='SCOTT'
--查询入职日期为1981/4/2 的员工的信息
--1 :使用默认的日期的字符串形式 'DD-MON-RR‘
select * from emp where hiredate='2-4月-1981'
-- 2:将日期字符串转换成 日期对象 to_date()
--查询工资大于1600的员工的信息
select * from emp where sal>1600 order by sal
select * from emp where sal<1600 order by sal
select * from emp where sal>=1600 order by sal
select * from emp where sal<=1600 order by sal
--查询工资等于1600的员工的信息
select * from emp where sal=1600 order by sal
--查询工资不等于1600的员工的信息的两种方式
select * from emp where sal!=1600 order by sal
select * from emp where sal<>1600 order by sal
--查询1982/1/23 日期之后入职的员工的信息
select * from emp where hiredate>'23-1月-1982'
--查询工资在1000-2000之间的所有的员工的信息
--and 相当于 并且 java 中的 &&
select * from emp where sal >=1000 and sal <=2000
--between xx and xx 闭区间的
select * from emp where sal between 1000 and 2000
--查询 员工信息 工资是 1100 或者是 1600
--or 代表或者的意思
select * from emp where sal=1100 or sal=1600
--查询所有员工中工种为 clerk manager analyst 的员工的信息 ename ,job deptno
select ename, job,deptno from emp where job='CLERK' or job='MANAGER' or job='ANALYST'
-- 在集合中的某一个值就可以 in ()
select ename, job, deptno from emp where job in ('CLERK','MANAGER','ANALYST')
--模糊查询 like
-- % 代表 任意个字符 通配符
-- _ 代表一个字符
select * from emp
--查询名称的第一个字符为 A 的员工的信息
select * from emp where ename like 'A%'
--查询名字中包含A 字符的
select * from emp where ename like '%A%'
--查询第二个字符为A 的员工的信息
select * from emp where ename like '_A%'
--查询不包含A字符的员工信息
select * from emp where ename not like '%A%'
--特殊情况 名字中包含 _ 员工的信息
select * from emp where ename like '%\_%' escape '\'
select * from emp
--空判断 所有奖金为空的员工的信息
select * from emp where comm is null
--不为空的员工的信息
select * from emp where comm is not null
--查询 工资在 1000--2000 之间 或者是职位是 职员 的员工的信息
select * from emp where job='CLERK' or sal between 1000 and 2000
select * from emp where job='CLERK'
--工作是 clerk 或者 manager 并且 sal 大于 1500的
--连接条件的关键字的连接的优先级(参照 PPT)配合小括号使用
select * from emp where (job='CLERK' or job='MANAGER') and sal > 1000
---伪表 dual 也称为虚表
-- 存在的意义:不依赖于任何表的查询或者是计算的工作。
-- 查询系统日期,和当前用户
select sysdate from dual
select user from dual
select * from dual
select 1+1 from dual
select ceil(1.5) from dual
Oracle常用函数
--函数:
--函数的分类:2类
--1:单行函数:对于一个查询的结果计算之后会得到一个对应的结果。
--2:多行函数:对于多个结果处理之后得到一个结果。
--注意:函数名大小写不敏感。
--单行函数:日期处理函数、字符串处理函数、数序运算的函数,转换函数,通用函数
--将emp表中所有的员工的姓名全部小写输出
select ename, lower(ename) from emp
-- 查询所有员工的名字 和入职天数
select ename,sysdate-hiredate 入职天数 from emp
select ename ,floor(sysdate- hiredate) 入职天数 from emp
-- 查询所有员工的名字 和入职月数,要求整月输出。 函数的嵌套使用。
select ename,floor(months_between(sysdate, hiredate)) 入职的月数 from emp
-- 查询下周三的日期
select next_day(sysdate,'星期三') from dual
-- 查询本月最后一天的日期
select last_day(sysdate) from dual
-- 查询所有员工的入职的星期数,年数,使用别名显示 按照入职时间长短 升序排列
select ename, round((sysdate-hiredate)/7) as "星期数" , round((sysdate-hiredate)/365) as 年数 from emp order by 星期数
--转换函数 to_number to_char to_date
--to_number : 字符串 -->数值的转换
--to_char :数值--->字符串 的转换 日期 --->字符串的转换
--to_date : 字符串--->日期的转换
--数值和字符串之间的相互转换
--java Integer.toString(int) Integer.parseInt(String)
--日期对象和字符串之间的相互转换
--sdf String format(Date) Date parse(String)
--自动转换
--字符串自动转换为数值形式
select '2' -'2' from dual
--数值向字符串的自动转换
select 1 || 1 from dual
--函数转换
--数值--->字符串 的转换
--to_char(number, format)
--9:代表一位数字,整数部分:如果该位没有数字则不进行显示,但对于小数点后面的部分仍会强制显示
select to_char(123123.123 ,'L999,999.9999') from dual
--0:代表一位数字,如果该位没有数字则强制显示0 整数和小数部分
select to_char(123.123 ,'L000,000.00000') from dual
--日期-->字符串
--to_char(date,format)
--将所有员工的受雇日期,按照指定的格式显式 2018-09-26 16:19:33
select hiredate, to_char(hiredate, 'YYYY-MON-DD HH24:MI:SS') from emp
--员工入职的年份
select round((sysdate-hiredate)/365) 年数 from emp
select to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY') 年数 from emp
--to_date : 字符串到日期对象
--查询 XXXX时间之后入职的员工信息 1981/4/2
--将日期字符串转换为 日期对象 和 hiredate 比较
--将字符串解析为 日期对象 解析的格式需要和 日期字符串 一致。
select * from emp where hiredate > to_date('1981/4/2','YYYY/MM/DD') order by hiredate
--查询 指定 日期之间的入职的员工的信息 1981/4/2 1982/1/23
select * from emp where hiredate > to_date('1981/4/2','YYYY/MM/DD') and hiredate < to_date('1982/1/23','YYYY/MM/DD')
select * from emp where hiredate between to_date('1981/4/2','YYYY/MM/DD') and to_date('1982/1/23','YYYY/MM/DD')
--to_number
--将字符串转换为 数值
select to_number('$123.123','$000.000') + 1 from dual
select to_number('¥123.123','L999.999') + 1 from dual
--select '¥123.123' + 1 from dual
--通用函数
--nvl (exp1,exp2) : 如果exp1 是null 那么返回 exp2 如果不是null 就返回自身
--所有员工的年薪
select ename, sal*12 +comm 年薪 from emp
select ename, sal *12 + nvl(comm, 0) 年薪 from emp
--nvl2(exp1,exp2,exp3) : 参数的意义 :如果exp1 是null 就返回 exp3 ,否则返回 exp2
select ename, sal * 12 + nvl2(comm, comm, 0) 年薪 from emp
--decode (value, key0,value0,key1,value 1,..... ,valuen)
--参数的意思,如果 value 的值 是 key0? 整个函数返回 value0 ,如果值是key1就返回value1,以此类推,如果都没有找到合适,最后返回 valuen。
--将emp 表中所有的员工的名字 工作 以及工作中文显示
--有多少个工种
select distinct job from emp
select ename, job, decode(job,'CLERK','职员','SALESMAN','销售','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师') 中文职业 from emp
select ename, job, decode(job,'SALESMAN','销售','PRESIDENT','主席','MANAGER','经理','ANALYST','分析师','职员') 中文职业 from emp