---单表的查询学习
--查询表的所有数据 select * from 表名;*代表所有
select * from emp;
--查询表中指定字段的值 select 字段名1，字段名2，...from表名
select empno from emp;
select empno,ename from emp;
--给查询结果中的字段使用别名
--在字段名后使用关键字 字段名 as "别名"
--作用：方便查看查询结果
--注意：as关键字可以省略不写，别名中没有特殊字符双引号也可以省略不写。
select empno 员工编号,ename"员工 姓名",job as 工作，mgr as "领导编号" from emp;
--连接符：select 字段名||'字符'||字段名||..... from 表名
--||为sql语句的字符链接符，使用在select和from之间
--字符链接格式为 字段名||'字符'||字段名
--注意：一个拼接好的连接在结果集中是作为一个新的字段显示，可以使用别名优化字段显示。
select empno||'的姓名是'||ename as"信息"，job||'哈哈'||mgr from emp;
--去除重复 select distinct 字段名,字段名,...fromn 表名
---注意：去除重复的规则是按照行进行去除的，多行数据完全相同取其一
select distinct job ,mgr from emp;
--排序
--单字段排序
--select * from 表名 order by 字段名 asc 升序排序 asc可以省略不写
--select * from 表名 order by 字段名 desc 降序序排序
--多字段排序
--select * from emp order by 字段名1,字段名2...
--先按照字段1排序，如果字段1的值相同，则按照字段2排序，....
select * from emp order by empno desc--单字段排序 降序
select empno,ename,job from emp order by ename asc--单字段排序 升序
select * from emp order by empno,ename--多字段排序
--字段的逻辑运算
--select关键字和from关键字之间的字段可以直接进行四则运算
--字段与字段之间也可以直接进行运算
--注意：字段值为数值类型
select * from emp
select empno,ename,job,sal*2+1000，sal+comm from emp
-----------------------------------------------------------------
--使用where子句查询筛选
--select 字段名，字段名，...from表名 where 筛选条件
--单筛选条件
--使用运算符进行筛选 =,>,>=,<,<=,<> 单个条件中
--注意：如果条件中的值为字符，必须使用单引号括起来
--查询所有的员工的工资信息
select empno,ename,sal+comm as 薪资 from emp
--查询SMITH的个人信息
select * from emp where ename='SMITH'
--查询SMITH的薪资信息,逻辑运算符=
select empno,ename,sal,sal+comm from emp where ename='SMITH'
--查询工资大于1000的员工信息,逻辑符>
select * from emp where sal>'2000'
--查询工资不等于3000的员工信息
select * from emp where sal<>3000 order by sal
--练习：
--查看工资等于1250的员工信息
select *from emp where sal='1250'
--查看工作等于CLERK的员工信息
select * from emp where job='CLERK'
--查看工资大于1250的员工姓名和工作
select ename,job from emp where sal>1250
--查看工资大于等于2000的员工信息
select * from emp where sal>=2000;
--查看工资小于等于2000的员工信息；
select * from emp where sal<=2000;
--查看工资不等于1500的员工信息
select * from emp where sal<>1500;
--查看入职日期在81年后的员工信息
--注意：oracle默认的日期格式为 日-月-年，示例'03-1月-1981'
select * from emp order by hiredate
select * from emp where hiredate>='01-1月-1981' order by hiredate
--多条件筛选（where子句关键字：and,or,like,is null,is not null, in ,between and）
--查询工资在2000-3000之间的员工信息
--使用and关键字，多条件同时成立的筛选使用and关键字进行条件连接
select * from emp where sal>=2000 and sal<3000
--使用between and 关键字进行条件连接，包含两头的数据
select * from emp where sal between 2000 and 3000
--查询工作为SALESMAN,ANALYST,MANAGER的员工信息
--使用or关键字，进行或条件的筛选。
select * from emp where job='SALESMAN' or job='ANALYST' or job='MANAGER' order by job
--使用in关键字，也可以进行或筛选，但是in中的内容只能为一个字段的值。
select * from emp where job in('SALESMAN','ANALYST','MANAGER')
--查询姓名中包含s的，以s开头的，以s结尾的，第二个字符为A的。(模糊查询)
--%号表任意多个的任意字符
--select * from 表名 where 字段名 like '%字符%' 查询包含指定字符的数据
select * from emp where ename like '%S%' --包含s的
--select * from 表名 where 字段名 like '字符%' 查询以指定字符开头的数据
select * from emp where ename like 'S%'--以S开头
--select * from 表名 where 字段名 like '%字符' 查询以指定字符结尾的数据
select * from emp where ename like '%S'--以S结尾的
--select * from 表名 where 字段名 like '_字符%' 查询指定位置为指定字符的数据
--_表示一个任意字符
select * from emp where ename like '_A%'--第二个字符为A的
--select * from 表名 where 字段名 like '%字符2字符1%' escape'字符2'
--escape将指定的字符变为转义字符
--转义字符可以将特殊字符转为普通字符
select * from emp where ename like '%/_%' escape '/'
select * from emp for update
--查询有津贴的员工信息
-- select * from 表名 where 字段名 is null 字段值为null
-- select * from 表名 where 字段名 is not null 字段值不为null
--多个条件使用and关键进行连接，筛选的是符合所有条件的数据
--select * from 表名 where 筛选条件1 and 条件2 and ....
select * from emp where comm is not null and comm>0
--oracle的账户管理学习：
--权限：具备某类事物的操作的能力，此能力称为权限。
--角色：一系列权限的集合
--oracle自带账户：
-- system 管理账户 bjsxt
--特点：具备大部分oracle的操作权限，主要用来管理普通账户及oralce的数据
--使用人：oracle数据维护工作人员
-- sys 超级管理员账户 bjsxt
--特点：具备system的所有权限，同时又具备其他的权限
--使用人：oracle攻城狮
--创建账户
--使用system账户，并使用dba身份，登录oracle管理系统
--创建用户
create user bjsxt identified by bjsxt;
--维护账户
--赋予权限 grant 权限或者角色名 to 用户名
grant connect to bjsxt;--给用户赋予登录权限
grant resource to bjsxt;--给用户资源操作权限
grant dba to bjsxt;--给用户赋予dba权限
select * from scott.emp--查看其它用户的表 使用用户名.表名
--删除权限 revoke 权限或者角色名 from 用户名
revoke dba from bjsxt;
--删除账户 drop user 用户名
drop user bjsxt;
-SQL99多表查询
--注意1：依然可以给表添加别名
--注意2：如果使用on或者usering关键对结果进行筛选，必须使用inner join作用表与表的连接，其中inner可以省略
--注意3:外连接的 outer关键字可以省略不写
--注意4：依然可以继续使用分组，having ，排序等
--笛卡尔积:使用cross join 关键字
---select 内容 from 表名 cross join
select * from emp cross join dept
--筛选
--查询员工姓名，工作，薪资，部门名称
--自然连接：使用关键字 natural join
--使用：select 内容 from 表名 natural join 表名
--特点1：底层先笛卡尔积，然后按照所有的同名同值字段自动进行等值筛选。
--问题1：如果只想按照部分字段结果筛选怎么办？
--问题2：如果想按照字段名不同，但是值相同进行等值筛选怎么办？
select * from emp natural join dept
--解决1：使用using关键字
--作用1：指明使用指定的字段对联合查询的结果进行等值筛选
--注意：指明的字段必须是两表的同名同值字段
--使用:select 内容 from 表名 inner join 表名 using(字段名，字段名,....)
select * from emp inner join dept using(deptno)
--解决2：使用on关键字进行自定义连接条件筛选(等值筛选，不等值筛选)
--注意：普通筛选条件使用where进行筛选，不要使用on进行。好处：SQL语句的阅读性变强。
--使用：select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
select * from emp inner join dept on emp.deptno=dept.deptno where sal>2000
--外连接：
--左外连接:select 内容 from 表名 left outer join 表名 on 连接条件
--查询员工姓名，工作，薪资，部门名称及没有部门的员工信息
select * from emp e left outer join dept d on e.deptno=d.deptno
--右外连接:select 内容 from 表名 right outer join 表名 on 连接条件
--查询员工姓名，工作，薪资，部门名称及没有员工的部门信息
select * from emp e right outer join dept d on e.deptno=d.deptno
--全外连接：select 内容 from 表名 full outer join 表名 on 连接条件
select * from emp e full outer join dept d on e.deptno=d.deptno
--自连接:
--查询员工及其上级领导姓名
select e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno
子查询
--单行子查询:
--使用时机：筛选条件不明确需要执行一次查询，并且查询结果一个字段并值只有一个
--注意：where子句中允许出现查询语句，该查询语句称为子查询
--使用：select 内容 from 表名 where 字段名 比较运算符 子查询语句
--查询所有比雇员“CLARK”工资高的员工信息
select * from emp where sal>(select sal from emp where ename ='CLARK')
--查询工资高于平均工资的员工的名字和工资
select ename,sal from emp where sal>(select avg(sal) from emp )
--查询和soctt属于同一部门且工资比他低的员工资料
select * from emp where deptno=(select deptno from emp where ename='SCOTT') and sal<(select sal from emp where ename='SCOTT')
--查询工资最高的员工资料
select * from emp where sal=(select max(sal) from emp)
--查询职务和scott相同，雇佣时间早的员工信息
select * from emp where job=(select job from emp where ename='SCOTT') and hiredate <(select hiredate from emp where ename='SCOTT')
--查询工资比scott高或者雇佣时间早的员工编号和名字
select empno,ename from emp where job=(select job from emp where ename='SCOTT') or hiredate <(select hiredate from emp where ename='SCOTT')
----------------------------------------------------------------------------
----多行子查询：
--使用:子查询的结果只有一个字段但是字段有n个值，考虑使用多行子查询，其实就是使用关键字
--关键字1:any 任意
--select 内容 from 表名 where 字段名 比较运算符 any 子查询语句
--关键字2：all 所有
--select 内容 from 表名 where 字段名 比较运算符 all 子查询语句
--关键字3：in 表示任意存在,相当于 = any
--select 内容 from 表名 where 字段名 in 子查询语句
--select 内容 from 表名 where 字段名 not in 子查询语句
--查询工资高于任意一个CLERK的所有员工信息
select * from emp where sal> any (select sal from emp where job='CLERK')
--查询工资高于所有SALESMAN的员工信息
select * from emp where sal> all (select sal from emp where job='SALESMAN')
--查询部门20中同部门10的雇员工作一样的雇员信息
select job from emp where deptno=10
select *from emp where (job='MANAGER' or job='PRESIDENT' or job='CLERK') and deptno=20
select * from emp where job in (select job from emp where deptno=10) and deptno=20
select * from emp where job = any (select job from emp where deptno=10) and deptno=20
----分组查询&筛选学习：
--关键字：group by 分组字段名,分组字段名....
--注意1：使用了分组后，在select语句中只允许出现分组字段和多行函数。
--注意2：如果是多字段分组，则先按照第一字段分组，然后每个小组继续按照第二个字段继续分组，以此类推。
--注意3：在where子句中不允许出现多行函数。
--分组筛选
--关键字：having
--作用：针对分组进行分组后的数据筛选，允许使用多行函数。
--注意：having关键必须和分组结合使用。不允许单独使用。
--where和having的比较：
--where子句不允许出现多行函数，having允许出现多行函数
--where子句和having都可以使用普通字段直接进行筛选，但是where的效率高于having
--where执行顺序: from--->where--->group by-->select-->order by
--having执行顺序:from--->group by-->select--->having--->order by
--结论：在分组语句中，使用where进行字段级别的筛选，使用having进行多行函数的筛选。
--查询最高工资和员工数
select max(sal),count(*) from emp
--查询不同部门的最高工资
select deptno,max(sal) from emp group by deptno
select * from emp
--查询不同工作岗位的员工数
select job, count(*) from emp group by job
--查询不同部门的不同工作岗位的人数
select deptno ,lower(job),count(*) from emp group by deptno,job order by deptno
--查询不同部门的不同工作岗位的并且人数大于1的信息
select deptno ,lower(job),count(*) from emp group by deptno,job having count(*)>1 order by deptno
--查询部门号大于10的不同部门的不同工作岗位的人数
--使用having关键字
select deptno ,lower(job),count(*) from emp group by deptno,job having deptno>10 order by deptno
--使用where关键字
select deptno,job,count(*) from emp where deptno>10 group by deptno,job order by deptno
---SQL查询语句的结构
--select 子句 要查询的数据(oracle函数，别名，连接符，去除重复，逻辑运算)
--from语句 决定要查询的表(表名)
--where子句 筛选数据（筛选条件，关键字）
--group by子句 分组 （分组字段）
--having子句 分组筛选 (多行函数筛选条件)
--order by子句 排序 （排序）
--from-->where--->group by-->select--->having--->order by
增删改操作
--insert into 表名(字段名，字段名，...)values(值1，值2，值3....);
--注意1：主键必须给值，允许为空的字段可以不给值。
--注意2：插入语句表名后跟的字段名为要赋值的字段，值和字段数量和顺序必须是一一对应的。
--注意3：如果是全字段插入，可以省略字段名部分 insert into 表名 values(值1，值2，.....)
--在部门中新增一个新的部门信息，信息内容为 编号：50，名称：LOL学院,地址：北京
insert into dept(deptno,dname,loc)values(50,'LOL学院','北京');
insert into dept(deptno,dname,loc)values(60,'LOL学院','北京');
insert into dept values(60,'LOL学院','北京');
select * from dept
--在部门中新增一条数据，只有部门编号和名称，没有地址。
insert into dept(deptno,dname)values('吃鸡学院',70)
--删除数据
--delete from 表名 删除表中的所有记录
--truncate table 表名 删除表中的所有记录，但是效率高于delete
--delete from 表名 where 条件 删除指定的数据,只要符合条件就会删除
delete from dept where deptno=50 --删除指定的数据
delete from dept --清空表数据
truncate table dept--清空表中数据
--更新数据
--update 表名 set 字段名=新的值,字段名=新的值...(会将字段的值全部改为新的值)
--update 表名 set 字段名=新的值,字段名=新的值... where 条件(将符合条件的数据的字段改为新的值)
update dept set dname='java学院',loc='上海'
update dept set dname='java学院',loc='上海' where deptno=50
select * from dept
--数据的备份
--注意：只会备份表结构和表的数据，约束不会备份。
--表级别备份
--全部备份:create table 新的表名 as select * from 备份表名
--部分备份: create table 新的表名 as select 字段名，字段名，...from 备份表名
--数据整体插入
--insert into 插入表名 select * from 表名
--注意:查询语句结果的字段数据必须和插入表名的字段数量一致，类型要一致。
create table deptBak as select * from dept--全部备份
create table deptBak2 as select deptno,dname from dept-- 部分备份
select * from deptBak2
insert into deptBak2 select deptno,dname from dept
--转换函数：
--to_number(数值类型的字符):将字符转换为数值
--to_char(数值或者是日期):将数值或者日期转换为字符
--to_date(日期格式的字符)：将字符转换为日期
----------------数值和字符的互转-----------------------
--字符转换为数字char---->number
select to_number('123')+2 from dual
--数字转换字符number--->char
--指定显示格式:
--9表示位置占位，例如999,999,999会将数字按照三个一组使用逗号隔开。
--L表示人民币符号，$表示美元符号
--0可以进行占位分组，但是如果真实数据位数不足，会使用0进行补位。
select to_char(12345,'$999,999,999') from dual
select to_char(12345,'L999,999,999') from dual
select to_char(12345678,'000,000,000,000.000') from dual
--查询工资大于2000的员工信息
--数值和字符之间的转换可以隐式转换。to_number可以省略不写.
select * from emp where sal>'2000';
select * from emp where sal>to_number('2000');
---------------日期和字符的互转---------------------------
--一般使用方式：新增数据使用to_date(),查询数据使用to_char()
--字符转换为日期 char--->date
--使用to_date('要转换的字符',日期格式)函数将字符转换为日期
--注意1：字符必须符合日期格式
--注意2:oralce默认的转换格式为日月年，例如'01-1月-2018' oracle认为是一个日期
--常用日期格式：
-- yyyy-mm-dd
-- yyyy/mm/dd
--查询员工入职日期在82年后的信息
select * from emp where hiredate >to_date('1982-01-01','yyyy-mm-dd')
select * from emp where hiredate >to_date('1982/01/01','yyyy/mm/dd')
select * from emp where to_char(hiredate,'yyyy-mm-dd') >'1982-01-01'
--日期转换为字符 date--->char
--使用to_char('要转换的日期',转换格式)
--注意1：如果不指名转换格式，则使用默认格式，日月年例如:'01-1月-81'
--常用转换格式：
-- yyyy-mm-dd
-- yyyy/mm/dd
--'yyyy"年"mm"月"dd"日"'
select to_char(hiredate) from emp--使用默认格式将日期转换为字符
select to_char(hiredate,'yyyy-mm-dd') from emp--使用指定格式 yyyy-mm-dd
select to_char(hiredate,'yyyy/mm/dd') from emp--使用指定格式 yyyy/mmm/dd
select to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp--使用指定格式 'yyyy"年"mm"月"dd"日"'
-----------------------------------------------------------------------------------------------------
--其他函数：
--nvl()：nvl(字段名，新的值)
--如果字段值不为null，则返回该字段的值。如果为null则返回新的值
--nvl2()：nvl2(字段名，处理1，处理2)
--如果字段值不为null，则执行处理1，为null执行处理2
--decode()：decode(字段名,值1，处理1，值2，处理2，值3，处理3,...,公共处理)
--如果字段的值和decode中的条件值相同则执行对象的处理。如果都没有则执行公共处理
---查询员工的工资信息
select ename,job,sal from emp
--查询员工的薪水信息
select ename,job,sal+nvl(comm,0),sal+comm,sal from emp
select ename,job,nvl2(comm,sal+comm,sal) from emp
--显示员工的职称
select ename,job,decode(job,'MANAGER','经理','PRESIDENT','董事长','SALESMAN','销售','普通员工') from emp