标签：sal -- 基础知识 emp Oracle 查询 where select

Oracle基础知识

1、Oracle安装

安装oracle注意事项：
1、关闭杀毒软件以及电脑管家。
2、电脑主机名为英文。
3、安装路径不要出现中文或者中文符号。
4、如果没有正常安装成功，安装程序也是卸载程序，直接卸载，重装。
卸载后重启电脑后再重新安装。
测试：cmd--->sqlplus-->用户名、密码 或者 sqlplus 用户名/密码 
要是连接数据库时出现协议错误是服务没开启
超级管理员：sys  1872694955
管理员： system 1872694955
用户：scott tiger
cmd登录语法：sqlplus 用户名/密码  [管理员加 as sysdba]

• SQLDevopment

oracle操作的可视化界面软件，若是服务属于手动开启，初学者只需要开启oracleServiceORCL(开启sqlplus)，oracle****TNSListener(连接可视化工具)，防止系统资源开启过多出现卡顿。

2、数据库入门

1、对于复杂庞大的数据存储，虽然IO流可以实现可持续化，但是对数据的操作非常复杂，不利于大规模发展，因此诞生了数据库。
2、数据库是按照数据结构来组织、存储和管理数据的仓库。
3、常用的数据库分类：
 小型：access foxbase：负载小，用户大概在100人以内，安全需求不高。
 中型：sqlServer mysql：日访问在5000-10000，满足日常安全。
 大型：sysbase、db2、oracle：海量负载，可以处理海量数据，安全性高。
4、DBMS：数据库管理系统，大部分DBMS提供数据定义语言和数据操作语言DML。
5、根据存储模型可以将数据库划分为关系型数据库和非关系型数据库，关系模型值得是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织，关系型数据库可以用sql语法，非关系型数据库不支持sql语法
6、DCL:数据库控制语言，对数据库中对象设置访问权限，用户授权(grant)和取消权限(revoke)。
   DML:数据库操作语言，对数据库中的数据进行增删改查操作。
   DDL:数据库定义语言，对数据中对象创建、删除、修改。比如对视图、索引、表格等。

3、数据表

分类	命令
DDL	create(创建) drop(删除)alter(修改)rename(重命名) truncate(截断)
DML	insert：插入 delete：删除 update：更新 select：查询
DCL	granc：授权 revoke：回收权利 commit：提交事务 rollback：回滚事务

• 表

1、列：字段  行：记录
2、表是从属于用户的：查询表(用户.表名)，当前用户查询自己的表时，用户名可以省略，其他用户查询别的用户表，不能省略，同时必须存在权限(超级管理员)。

• 表是逻辑表（概念表），不是物理表

块-->区(连续块)-->段(连续区)-->表(多个段)，数据段不全是表，表一定是数据段，还有其他段如索引段。

• 表结构

表名、字段名、字段类型(number、char、varchar)、字段约束、记录

4、SCOTT用户表

emp：雇员表   dept:部门表  salgrade：薪资等级表  bonus:奖金表
【注意】oracle 命令不区分大小写(SELECT 与 select)， 存储数据|内容 区分大小写。

5、查询语句

• SQL查询的模板

select [字段名1,字段名2……]
from [表名1 alias，……]
where 字段名 条件
order by 字段名1 [asc/desc],字段名2 [asc/desc],…… ASC(默认升序)DESC(降序)
SQL执行顺序:from-->where-->select-->order by

• 不带条件查询的SQL

-- 注释
/*
  多行注释
*/
--查询一张表中的所有数据的所有字段 select * from 表名
select * from dept;  --部门表
select * from  emp;  --雇员表
select * from tab;
-- 查询语言select 要查询的数据 from 数据来源
-- *：匹配一条数据的所有字段值
--要查询的数据：结果集中保留每条数据指定的数据结果集叫伪列
--字符串要用单引号
--1、 查询所有员工信息
select * from emp;
--2、查询所有的员工名字
--查询的数据：员工姓名 ename
--数据的来源：员工表emp
-- 条件 无
select ename from emp;
--3、查询所有的员工存在的部门编号，每个部门编号只出现一次
select deptno from emp;
--4、查询员工部门号并去重复 distinct 表示去除重复
select distinct deptno from emp;
--5、查询公司所有部门编号
select deptno from dept;
--查询多有员工存在的部门编号，每个部门编号只出现一次
select  distinct deptno from emp;
--查询年薪  设置伪列  取别名
select deptno，sal*12 as yearSal from emp;
--字符串要使用单引号'' 如果是""表示原封不动显示  数据表取别名不能使用as关键字
select ename,sal,'msb' "公司名称" from emp;
-- 字符串拼接 ||
select 'yjx'||ename char_value from emp;
-- 虚表 dual
select * from dual;
select 12*18 "值" from dual;
--给每一个员工在原来的基础上+1块钱奖金
--null值与数字运算结果是null值
--null值与字符串运算结构为原串
select ename,sal,comm,comm+1 from emp;
--nvl(判定字段，默认值)，对null值进行判定的函数
--如果判断字段不为null，nvl函数的结果就为判定字符的结果
--如果判定字段为null，nvl函数的结果为第二个参数默认值。
select nvl(comm,0)+1 from emp;
-- 练习题
select * from emp;
select ename,job,sal*12 year_sal from emp;
select ename,job,sal*12 year_sal,nvl(comm,0)*12 year_comm from emp;
select ename,job,sal*12 year_sal,nvl(comm,0) from emp;

• 带条件查询的语句

-- 带条件查询 select 要查询的数据 from 数据源 where 行过滤条件
--SQL执行流程：from--》where--》select--》order by
--查询20部门的员工信息表
--条件判断运算符 = < > <=  >=  !=(<>)
--条件判断连接符 and or not
--区间判断：条件1 and 条件2 | between 值1 and 值2
select * from emp where deptno=20;
--查询年资大于2000的员工的姓名、工作岗位、年薪、所属员工部门编号
select ename,job,sal*12,deptno from emp where sal*12>2000;
--注意 where后面不能使用别名，因为执行流程的问题
-- select sal*12 n from emp where n>200;  错误sql语句
--可以先查询再过滤实现别名使用
select * from (select ename,job,sal*12 r,deptno from emp) where r>2000;
--查询 any(任意一个) some(任意一个) all(所有)
--查询工资比我们三个人都高的那些员工的信息
select * from emp where sal>all(900,1000,1100);
--查询比我们中随便一个工资高的员工信息
select * from emp where sal>some(900,1000,1100);
--查询工种为'SALESMAN'的员工信息
select * from emp where job='SALESMAN';
--检索工资2000、3000员工名称 岗位 工资
select ename,sal from emp where sal=2000 or sal=3000;
--检索工资在2000到3000之间的员工信息
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;
--查询岗位为clerk且部门编号为20的员工名称、部门编号、工资
select * from emp where job='CLERK' and deptno=20;
--查询岗位是clerk或者部门编号为20的员工名称、部门编号、工资
select * from emp where job='CLERK' or deptno=20;
--查询岗位不是clerk员工名称、部门编号、工资
select * from emp where job!='CLERK';
select * from emp where job<>'CLERK';
select * from emp where not job='CLERK';
--查询岗位不为CLERK并且部门编号不为20的员工名称 部门编号 工资
select * from emp where not deptno=20 and job!='CLERK';
select * from emp  where not (deptno=20 or job='CLERK');
--存在佣奖金的员工名称 判断null 要使用is关键字
select * from emp where comm is not null;
select * from emp where not comm is null;
--查询工资大于1500或含有佣金的人员姓名
select ename from emp where sal>1500 or comm is not null;
--集合函数对结果求进一步操作
--union 并集去重 
union all 并集不去重 
intersect交集  查询一样拥有的
minus差集  多减去少  否则会出现null
--查询显示不存在雇员的所有部门号 40号部门没有雇员信息
select deptno from dept
minus
select distinct deptno from emp;
--like 模糊匹配查询 需要配合%任意个字符 一个任意字符，模糊查询比使用定值查询效率低
--查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';
--查询员工姓名中第二个字符为A的员工信息
select * from emp where ename like '_A%';
--查询员工姓名以A开头的员工信息
select * from emp where ename like 'A%';
--测试数据
insert into emp(EMPNO,ENAME,SAL)values(1000,'T_TES%T',7800);
insert into emp(empno,ename,sal)values(1201,'T_%TEST',8000);
commit;
select * from emp;
--查询雇员姓名含有%的雇员信息  %要进行转义  A表示转义字符  使用escape(转义字符) A相当于/
select * from emp where ename like '%A%%' escape('A');
--查询工资为1500,2000,2500,5000的员工信息,满足其中一项就够了
--in(值列表)判断是否在in后面值列表中，如果在满足条件，不在不满足条件。
select * from emp where sal in(1500,2000,2500,5000);
--排序order by排序字段1，排序字段2... asc升序(默认) desc降序
--对结果集中的数据进行排序
--按30部门的员工信息，并且按工资降序排序
select * from emp where deptno=30 order by sal desc;
--查询30部门的员工信息，并且按照工资排序如果工资相同，则按照员工编号排序
select * from emp where deptno=30 order by sal desc,empno desc;
--查询所有员工信息，按照奖金升序排序 null;
--默认null放在后面，要想null放在前面  使用nulls first
select * from emp order by comm nulls first;

• exists关键字

-exists(结果集) 存在即保留，存在即合理
--执行流程：from-->where-->select-->order by
--exists(数据结果集)从from后面数据源中拿出每一条数据，判断是否满足where后的条件，如果是exists就判断exists()中的结果集中是否存在数据，存在当前判断的这条数据就满足跳进，不满足就过滤。
select * from emp where exists (select * from dept);
select * from emp where exists(select * from dept where deptno=40);
select * from emp where exists(select * from emp where comm is not null);
--从emp表中查询出部门编号为10或者30的雇员信息。
select * from emp where exists(select * from dept where deptno in(10,30) and dept.deptno=emp.deptno);
--查询销售部和会计部的雇员信息
select * 
from emp e 
where exists(select dname,deptno
 from dept d where d.dname in ('ACCOUNTING','SALES')
 and d.deptno=e.deptno);          
--查询与有奖金同一个部门的员工信息
select * from
emp e1
where exists(
select comm,deptno from emp e2
where comm is not null and
e2.deptno=e1.deptno);

• 子查询

--查询所有行记录
select * from emp;
select * from emp where 1=1;
--部门名称为SALES或ACCOUNTING的雇员信息
--数据：员工信息*
--来源：员工表 emp
--条件：SALES和ACCOUNTING部门编号
select * from emp e where exists(select deptno from dept d where d.dname in ('SALES','ACCOUNTING')and d.deptno=e.deptno);
select * from emp where deptno in(select deptno from dept where dname in('SALES','ACCOUNTING'));
--查询工资等级为2的员工信息
select * from emp where sal between(select losal from salgrade where grade=2) and (select hisal from salgrade where grade=2);
--查询销售部(SALES)中工资大于1500的员工信息
select * from emp e where e.deptno=(select deptno from dept d where d.dname='SALES') and e.sal>1500;
--查询工资比SMITH高的同一部门的员工信息 (作业)
select * from emp where sal>(select sal from emp where ename='SMITH')and deptno=(select deptno from emp where ename='SMITH');

• SQL函数

--单行函数：输入某些内容，而后可以自己进行处理得到所需要的结果
--常用单行函数 字符串处理函数 upper() lower() initcap() length() substr() replace()
--数值函数：round() 四舍五入 truncate()截取取整  mod()求余数
--日期处理函数 add_months()、 months_between()、
--转换函数 to_date() to_char() to_number()
--通用函数： nvl()空默认值函数  decode()判定函数
--这些函数可以直接在SQL语句任意位置上进行数据的处理。

--字符串函数 最大特点''声明就是字符串
--转大小写:upper(字段名|字符串) lower(字段名|字符串)
--字符串长度  length(字段名|字符串)
select length('hello word') from dual;
--字符串截取处理 substr(字段名|字符串,起始索引,字符长度) oracle索引是从1开始计数，空格也算一个字符;
--除了可以正向索引数值之外，也可以使用反向索引。
select substr('hello',1,4) from emp;
select substr('hello',-2,4) from dual; --使用负数索引  java和c不支持
--字符串替换  replace(字段名|字符串,被替换的字符串,替换的字符串);
select replace('hello','e','_') from dual;

--数值函数 进行小数位的处理  round()在数值函数里可以四舍五入 不设置只保留整数
select round(19.45214) from dual;
--保留2位小数的round(数值,保留小数的位数);
select round(19.5242,1) from dual;
--截取数值函数  trunc() 向上取整
select trunc(12.012) from dual;
--求模 mod()  10%3=1
select mod(10,3) from dual;

--日期函数
--查询当前日期
select sysdate from dual;
--查询时间戳  时间戳是从1970年1月1日0点开始 程序不认所谓的时间
select systimestamp from dual;
--计算若干天后
select sysdate+150 from dual;
select sysdate-8000 from dual;
--add_months() 若干月后的日期
select add_months(sysdate,5) from dual;
--当前月的最后一天
select last_day(sysdate) from dual;
--查询员工雇佣所在月倒数第三天
select last_day(hiredate)-2 from emp;
--查询下个周二的日期
select next_day(sysdate,'星期二') from dual;
--计算两个日期之间所跨的月数 精确到小数点
select hiredate,months_between(sysdate,hiredate) from emp;
--查询雇佣日期距今多少年多少月多少日
--获取雇佣日期年份
select trunc(months_between(sysdate,hiredate)/12) year,
--获取雇佣日期的月份
 trunc(mod(months_between(sysdate,hiredate),12)) month ,
--获取雇佣日期的天数
 trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) day from emp;

--转化函数
--to_char(日期|数字|字段名,转换格式)：可以将数字、日期、字段名进行格式化，还可以货币格式化
--时间日期化格式化
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--货币转换  L表示本地货币标记
select to_char(145250102154,'l999,999,999,999') from dual;
--to_number(字符串，字段名) 把字符串转为数值型
select to_number('abc',) from dual;

--通用函数
--nvl(字段名,0)  给字段值为null默认赋值0
--decode(字段名,判断条件1,返回值1,判断条件2,返回值2……,default)

• SQL案例

--函数
--内置函数
--自定义函数
--单行函数：一条记录返回一个结果 例如：比如：lower(全部字符转成小写)、 upper(全部字符转成大写)、initcap(单词首字母大写)。
--多行函数\组函数：聚合函数 多条记录返回一个结果 例如：sum()求和、avg()求平均、count()求非空计数、max()求最大值、min()求最小值。
--单行函数
--当前时间
select sysdate from dual;
select current_date from dual;
--日期可以直接+-
--2天以后是几号
select sysdate+2 from dual;
--查询所有员工的试用期到期（转正的日期）3个月试用期
select ename,hiredate,hiredate+90 from emp;
--转正期三个月前
select ename,hiredate,add_months(hiredate,-3) from emp;
--查询所有员工到目前为止一共工作了几个月
select ename,months_between(sysdate,hiredate) from emp;
--trunc 截断取整
select ename,trunc(months_between(sysdate,hiredate))from emp;
--查询当前月的最后一天
select ename,last_day(sysdate) from emp;
--明天是几月几号  并取名星期四
select next_day(sysdate,'星期四') from dual;
--日期对象与日期字符串之间相互转换
--to_char 日期格式化
select sysdate,to_char(sysdate,'yyyy-MM-dd hh12:mi:ss') from dual;
--如果想要中文展示  要有双引号" "原封不动展示
select sysdate,to_char(sysdate,'yyyy"年"MM"月"dd"日" hh12:mi:ss') from dual;
--to_date 把字符串转换为date日期格式
select sysdate,to_date('2022年02月23日','yyyy"年"mm"月"dd"日"') from dual;
--查询02年入职的员工信息
--decode(判断变量名,判定值1,执行块1,判定值2,执行块2,……)如下，如果判定变量为10 就显示十 变量为20 显示 二十
select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十',40,'四十') from dept;
--第二种判定函数
--case 变量值 when 判定值1 then 执行块1 when 判定值2 then 执行块2 end ……
select deptno,
(case deptno when 10 then '十'
when 20 then '二十'
when 30 then '三十'
when 40 then '四十' else '无' end) from emp;
--nvl(变量名,默认值) 如果变量名为null赋值指定默认值  否则显示本身的值
select nvl(comm,0) from emp;
--作业
--查询82年入职的员工信息 判断是否82年1月到82年12月
select * from emp where hiredate between to_date('1982-1-1','yyyy-MM-dd')and to_date('1982-12-31','yyyy-MM-dd');
select * from emp where to_char(hiredate,'yyyy')='1982';
--给20部门的所有员工都涨薪10%，显示出员工的名称，原来薪水、所属部门编号、涨薪后的薪水
select ename,sal,deptno,decode(deptno,20,sal*1.1,sal) from emp;
--10部门涨薪10% 20涨薪20% 30降薪1% 40部门翻倍3倍
select decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*0.99,40,sal*3) 涨薪后 from emp;
select case deptno
when 10 then
sal*1.1
when 20 then
sal*1.2
when 30 then
sal*1.3
when 40 then
sal*3
end from emp;
-----------------------------------------------------------
--PDF版作业(7.3)解答
select dname from dept;
select ename,(sal+nvl(comm,0))*12 年收入 from emp;
--查询没有雇员你的部门号 差集以上面查询为主
select deptno from dept
minus
select deptno from emp;
select ename,sal from emp where sal>2850;

select * from emp where sal not between 1500 and 2850;
--且逻辑取反前不能加字段名
select * from emp where  not (sal>=1500 and sal<=2850);
select ename,deptno from emp where empno=7566;
select ename,sal,deptno from emp where deptno in(10,30) and sal>1500;
select ename,sal from emp where ename like '_A%';
select ename,comm from emp where comm is not null;

select ename,sal,hiredate from emp order by ename asc;
select ename,job,hiredate from emp where hiredate between to_date('1981年2月1日','yyyy"年"MM"月"dd"日"') and  to_date('1981年5月1日','yyyy"年"MM"月"dd"日"')order by hiredate ;
select ename,sal,comm from emp where comm is not null or comm!=0 order by sal asc,comm desc;

• 统计函数

--统计函数
--count(*|字段名|distinct 字段) 统计不为null字段的个数
--sum(字段名) 求和
--avg(字段名) 求平均值
--max(字段名) 最大值 可以在日期或字符串上使用
--min(字段名) 最小值 可以在日期或字符串上使用。

--查询工资总和
select sum(sal) from emp;
--查询工资平均值
select avg(sal) from emp;
--查询员工工作平均年限
select trunc(avg(trunc(months_between(sysdate,hiredate)/12))) avg_years from emp;
--查询最早和最晚雇佣日期
select min(hiredate) from emp;
select max(hiredate) from emp; 
--count(*) 有无null全部进行统计  count(字段名)字段值有null不纳入统计
select count(*) from emp;  --16条记录
select count(comm) from emp; --4条记录

• 分组查询 (group by）

--分组统计基本实现
--拥有相同特征的一组信息实现分组，也就是列上有重复数值可以进行分组 可以使用group by进行分组
/*
  执行顺序 from-->where-->group by-->select-->order by
  语法排列
  select
  from
  where
  group by
  order by
*/
--统计出每个职位的人数、平均工资、平均雇佣年限
select count(*),avg(sal),avg(months_between(sysdate,hiredate)/12) year_avg
from emp
group by job;
--统计每个部门编号的人数、总工资、平均工资  分组时要判断有无重复数值为依据  要是不存在重复数值 就没有统计的依据
select count(*),sum(sal),avg(sal) from emp group by deptno;
--分组查询注意事项
--由于是判断字段记录有无重复的时候进行分组，所以存在一定的限制，我们要消除限制。
--在不使用group by的时候  select子句只允许出现统计函数 不允许出现其他字段。
--在使用group by的时候  select子句只能出现分组字段和统计函数，不允许出现其他字段。
--统计函数允许嵌套，嵌套后的统计操作select子句中不允许出现任何字段，包括分组字段。

• 分组查询与多表查询

-- 查询每个部门的名称、人数、平均工资
select d.dname,count(*),avg(e.sal)
from dept d
left outer join emp e on  d.deptno=e.deptno
group by d.dname;

--统计出每个工资等级的人数、平均服务年限 重复数据是工资等级
select count(e.empno) num,avg(months_between(sysdate,hiredate)/12) year_avg
from salgrade s
join emp e on (e.sal between s.losal and s.hisal)
group by s.grade;

• 多字段分组查询

--多字段分组  group by后面可以实现多个字段进行分组
--如果使用多字段分组  则必须要求这多个字段的内容完全重复
--查询出每个部门的编号、名称、部门人数、平均工资
select d.deptno,d.dname,count(e.empno) number_count,avg(e.sal) avg_sal
from dept d
left outer join emp e on (d.deptno=e.deptno)
group by d.deptno,d.dname,d.loc;
--查询每个工资等级的编号、等级最高与最低工资，此等级的人数，总工资
select s.grade,count(e.empno),sum(e.sal),s.losal,s.hisal
from salgrade s
left outer join emp e on (e.sal between s.losal and s.hisal)
group by s.grade,s.losal,s.hisal;

• having

--having 子句  主要针对group by分组过滤条件
/**
完整语法顺序
select
from
where
group by
having
order by
执行顺序 from-->where-->group by-->having-->select-->order by
*/
--where与having的区别？
--where是在group by分组之前使用的，无法使用统计函数
--having是在group by分组之后使用的，目的是针对统计函数的二次筛选

• 测试sql

--查询工资总和高于3000的部门编号、部门人数、总工资
select count(e.empno),sum(e.sal),e.deptno
from emp e
left outer join dept d on d.deptno=e.deptno
group by e.deptno
having sum(e.sal)>9000;
--查询所有不领取佣金的雇员职位的名称，职位人数以及平均工资，并且要求这些平均工资大于2000
select e.job,avg(e.sal) sal_avg,count(e.empno) number_c
from emp e
where comm is null
group by e.job
having avg(e.sal)>2000;

• 案例分析

--分析案例
--查询公司内所有领取佣金的雇员人数、平均工资、总工资和不领取佣金的雇员人数、平均工资、总工资。
--这种题目由于考虑以佣金展开分类，但是由于佣金种类过多，不利于使用分组查询，需要使用联合查询 union
select '不领取佣金' title,avg(sal),sum(sal),count(empno)
from emp 
where comm is not null
union
select '领取佣金' title,avg(sal),sum(sal),count(empno)
from emp 
where comm is null;

• 行转列并分组求最小值显示

--原来数据表
select * from t_student;
--行转列后的数据表
select name,min(decode(course,'语文',score)) 语文,min(decode(course,'数学',score)) 数学,min(decode(course,'英语',score,nvl(score,0))) 英语
from t_student
group by name;

6、多表查询

--多表查询
--在之前所实现的都是基于单张表实现的(from 子句之后的表)
--语法结构：只需要修改from后面的表数量
--如果两张表数据量特别大，会导致查询速度变慢
--内连接：内连接指的是等值连接，在之前所实现的开发实现的都是内连接，必须保证多张表指定列的内容完全一致。
--外连接：（左外连接、右外连接、全外连接):可以显示左表或者右表完整的记录(哪怕条件不满足)
select count(*) from emp,dept;  --实现迪卡尔乘积 16*5
select count(*) from dept;

--实现右外连接 对于(+)符号是属于oracle数据库自己的外连接控制，其中两种使用形式 可以防止记录丢失 (oracle独有语法)
--右外连接 [from 左表,右表 where 左表(+)=右表] 以右表显示为主 右表数据全部显示
--左外连接 [from 左表,右表 where 右表(+)=左表] 以左表显示为主 左表数据全部显示
select ename from emp e,dept d where e.deptno(+)=d.deptno; --右外连接查询
select ename from emp e,dept d where e.deptno=d.deptno(+); --左外连接查询

--SQL1999语法 (推荐使用)
--交叉连接语法  目的产生迪卡尔积
/**
select e.*,d.* 
from 表1 cross join 表2;
**/

--自然连接语法  主要形式是同名字段(默认关联列) 放在首列 数据量大的时候也可以较快获取结果
/*
select 字段名
from 表1 natural join  表2 [on (条件语句)];
*/
--设置指定的关联列 如果我们的关联列很多  我们要设置关联列连接
/*
select 字段名
from 表1 join 表2  using(关联字段名);
*/
--使用on设置条件
/**
select 字段名
from 表1 join 表2  on(表1.关联字段名=表2.关联字段名);
**/
--全外连接  显示两张表的全部记录  关联列放在第一列
/**
  select 字段名
   from 表1 full outer 
   join 表2 [on (条件语句)]； 
**/
--左外连接  以左表为主显示左表的全部数据
/**
select 字段名
from 表1 left outer join
表2 [on (条件语句)];
**/
--右外连接
--左外连接  以左表为主显示左表的全部数据
/**
select 字段名
from 表1 right outer join
表2 [on (条件语句)];
**/
--考虑性能问题 多表查询要少用  否则在海量数据面前会降低查询效率
select * from emp e
full outer join dept d on (e.deptno=d.deptno);
[多表查询题目]
--1、查询每个雇员的编号、姓名、职位、基本工资、部门名称、工资等级
select e.empno,e.ename,e.job,e.sal,d.dname,s.grade
from emp e 
left outer join dept d on(e.deptno=d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal);
select * from salgrade;
--2、查询每个雇员的编号、姓名、职位、部门名称、领导姓名、领导所在部门
select * from emp;
select e.empno,e.ename,e.job,d.dname,e.mgr,e.deptno,e1.ename leader,d1.dname leader_dept,d1.deptno leader_deptno
from emp e
left join dept d on(e.deptno=d.deptno)
join emp e1 on(e.mgr=e1.empno) 
join dept d1 on(e1.deptno=d1.deptno);

• rowid

--rowid 行记录的地址 行记录的唯一标识 应用：根据rowid的唯一性来删除重复数据
--实现没有主键,唯一字段的表中完全相同数据的去重
select empno,ename,rowid from emp;
select * from t_student;
--去重
--1、查询到要保留的数据
select distinct id,name,course,score from t_student; --显示去重,并不是真正的删除重复数据。
select distinct id,name,rowid,course,score from t_student;
--2、查询要保留的数据
select name,course,score,max(rowid) from t_student group by id, name,course,score;
--3、查找要删除的数据、
select * from t_student where not ROWID in(select max(rowid)from t_student group by id, name,course,score); 
--4、删除重复数据
delete from t_student where not ROWID in(select max(rowid)from t_student group by id, name,course,score);

• rownum

--rownum:结果集中记录的序号
--一个结果集就存在一份rownum
--rownum从1开始 每次+1 有顺序有规律 用排order by查询 rownum是乱序的
select deptno,dname,rownum from dept;
--根据主键排序先确定主键排序再确定rownum  非主键排序相反
--如果存在排序之后，rownum序号被打乱，可以在select外部再次嵌套select语句，外部的select语句的rownum是从小到大排序，有规律可以做为判断使用。
--分页  假如i=3:每页显示3条数据 n是页码  起始位置rownum>(n-1)*i,结束位置rownum<=i*n
--测试rownum代码  以下查询rownum是乱序的  不利于取数据
select ename,empno,job,mgr,deptno,hiredate,comm,rownum from emp order by sal;
--解决方案  在select子句外再嵌套select子句查询rownum 外部select子句rownum是有规律有序的可以用来取数据  注意from子句要先插叙有结果 才能进行条件筛选
select ename,empno,job,mgr,deptno,hiredate,comm,rownum m,n from(select ename,empno,job,mgr,deptno,hiredate,comm,rownum n,sal from emp order by sal) order by sal;
--以下sql语句写无法查询出记录，主要原因是from子句中的rownum是无序的
--select ename,empno,job,mgr,deptno,hiredate,comm,rownum m,n from(select ename,empno,job,mgr,deptno,hiredate,comm,rownum n,sal from emp order by sal) where rownum>3 and rownum<=6 order by sal;
--要解决上述问题  应该实现三次select子句嵌套
select ename,empno,job,mgr,deptno,hiredate,comm,sal from(select ename,empno,job,mgr,deptno,hiredate,comm,rownum m,n,sal from(select ename,empno,job,mgr,deptno,hiredate,comm,rownum n,sal from emp)) where m>3 and m<=6 order by sal;

7、表设计

1、表设计的思路

--表设计
--明确表的作用 表名
--明确表中的字段  字段名 字段类型：number、char、varchar2、date 数据类型(默认字节数)  可以指定为字符数 数据类型(n char) n表示整数
-- 字段约束：
        --主键约束(唯一＋非空) primary key
        --唯一约束 unique
        --非空约束 not null
        --默认约束 default('默认值')
        --检查约束 check(判断SQL块)
        --外键约束 foreign key 
        --涉及到两张表 父表|主表  子表|从表
        --子表中添加一个外键字段，关联主表的主键字段
        --外键字段的值只能为主表中主键字段已有的值

2、三大范式

--设计表的三大范式
--第一范式：每一列都是不可分割的基本数据项（原子性）
--第二范式：在第一范式的基础上，非主键属性非部分依赖于主键，也就是非主键其他字段必须依赖于主键字段
--第三范式：在第一二范式的基础上，属性不依赖于其他非主键属性（主外键关联 拆分表）。
--简而言之，最终目的避免数据重复冗余

3、表与表之间的关系

表与表之间的关系
-- 一对一关系：用户表 身份证信息表 （主外键关系）
-- 一对多|多对一：班级表 学生表  （主外键关系，在多的一方设置外键，关联一方的主键）
-- 多对多 （创建中间表处理 主外键可以不设置）

8、DDL创建与约束

1、不加约束的创建表

表名必须唯一，如果表名存在必须删除。
drop table 表名1； --删除数据表
create table 表名(
字段名1 数据类型  [约束1 约束2 ……],
字段名2 数据类型  [约束1 约束2 ……],
字段名3 数据类型  [约束1 约束2 ……],
字段名4 数据类型  [约束1 约束2 ……],
……
);

• 测试SQL语句

create table tb_user(
userid number(5) ,
username varchar2(30) ,
userpwd varchar2(20) ,
age number(3) ,
gender char(3) ,
email varchar2(30) ,
regtime date
);
--添加注释
comment on table tb_user is '用户表';
comment on column tb_user.userid is '流水号';
comment on column tb_user.username is '用户名';
comment on column tb_user.userpwd is '密码';
comment on column tb_user.age is '年龄';
comment on column tb_user.gender is '性别';
comment on column tb_user.email is '邮箱';
comment on column tb_user.regtime is '注册日期';

2、加约束的创建表

• 测试SQL

--创建文章表 并指定字段约束
create table tb_user(
userid number(5) primary KEY,
username varchar2(30) check(length(username) between 4 and 20),
userpwd varchar2(20) check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age>=18) ,
gender char(4) default('男') check(gender='男'or gender='女'),
email varchar2(30) unique,
regtime date default(sysdate)
);
commit;

--创建文章表
create table tb_txt(
--设置主键并指定主键名
txtid number(10) constraint pk_uid primary key,
title varchar2(32) check(length(title)between 4 and 30),
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) constraint fk_id references tb_user(userid)    --设置外键并指定外键名
);

• 在创建表时指定约束名

1、直接在字段名后操作
create table 表名(
字段名1 数据类型 constraint 主键自定义约束名 primary key,
字段名2 数据类型 constraint 检查自定义约束名 check(条件),
字段名3 数据类型 constraint 唯一自定义约束名  unique，
字段名4 数据类型 constraint 默认自定义约束名 default(默认值),
字段名5 数据类型 constraint 不为空自定义约束  not null,
字段名6 数据类型 constraint 外键自定义约束 references 主表（主键）
);
2、在创建表结束之前指定约束
create table 表名(
字段名1 数据类型，
……,
constraint 主键自定义约束名 primary key(字段名),
constraint 检查自定义约束名 check(条件),
constraint 唯一自定义约束名  unique(字段名),
constraint 外键自定义约束 foreign key(字段名) references 主表（主键)
);

• 创建表格后追加约束

alter table 表名 add constraint 约束名 primary key(字段名);
alter table 表名 add constraint 约束名 check(字段条件);
alter table 表名 add constraint 约束名 unique(字段名);
alter table 表名 add constraint 约束名 foreign key(字段名) references 主表(主键);
--使用不为空(not null)和默认(default)约束时：
alter table 表名 modify(字段名 constraint 约束名  not null);
alter table 表名 modify(字段名 default(默认值));

3、拷贝数据表

1、只拷贝数据表结构
create table 新表名 as (select * from 已存在表名 where 1!=1);
2、拷贝数据表结构和数据
create table 新表名 as(select * from 已存在表名 [where 条件]);

4、操作约束

• 查看某个用户的约束

select constraint_name, constraint_type
from user_constraints
where owner = upper('SCOTT');

• 查看表的约束

select constraint_name, constraint_type
from user_constraints
where table_name = upper('emp');

• 约束的禁用和启用

ALTER TABLE tb_user disable constraint nn_user_name; --禁用
ALTER TABLE tb_user enable constraint nn_user_name; --启用 

• 删除约束

alter table tb_user drop constraint uq_user_email cascade;

• 修改约束

--非空
alter table tb_user modify (username varchar2(20));
--默认为空
alter table tb_user modify (age default null)

5、删除表

drop table tb_X [cascade constraints];--删除数据表同时删除所有约束。
--如果出现主从表时，删除表格两种方案：
--1、先删除从表，再删除主表。 
drop table 从表名;
drop table 主表名;
--2、删除主表时，同时删除约束。
drop table 主表名 cascade constraints;

6、 修改表结构

修改表名：rename 旧表名 to 新表名;
修改列名：alter table 表名 rename column 旧列名 to 新列名;
修改类型：alter table 表名 modify(字段名 数据类型);
添加字段：alter table 表名 add 字段名 数据类型;
删除字段：alter table 表名 drop column 字段名；

9、视图

--视图与索引
--视图：建立在结果集与表之间的虚拟表
--物理视图：真的存储数据（直接对数据表进行读写）
--逻辑视图：不会真是存储数据，数据来自数据源（推荐使用）
    --简化封装SQL语句
--权限问题解决方案
   --登录sys管理员账户名;
   --授权：grant dba to 账户名; 授予到哪个账户权限
   --回收：revoke dba from 账户名; 回收来自哪个账户的权限

• 创建视图的基本语法

--创建视图语法
create or replace view 视图名 as select语句 [with read only];  如果创建视图设置为只读，就无法对视图进行增删改,复杂视图不能更改数据，只能查询。(复杂视图就是封装多表SQL查询的视图)
--删除视图
drop view 视图名;
--修改视图数据
update 视图名 set 字段名=修改值(字段名必须为select子句已经查询到的字段名)  where 条件。

• 测试代码

-- 创建视图时设置只读
create or replace view vw_emp as 
select ename,job,mgr,sal 
from emp with read only;
-- 删除视图
drop view vw_emp;
-- 查询视图
select * from vw_emp;
-- 更新视图字段值
update vw_emp set job='SALESMAN' where sal=8000;

10、索引

1、索引相当于目录，使用索引主要是对查询数据时提高效率，如果大量的使用增加和删除数据反而会降低效率，索引是数据库的一部分也需要维护，在大量数据情况下，使用索引会大大提高我们的查询效率，有无索引对SQL语句执行无影响,oracle数据库自动为主键添加索引。
2、创建和删除索引的语法
create index index_索引名 on 表名(字段名1,字段名2,……);
drop index index_索引名;

11、事务

1、事务是作为单个逻辑工作单位执行的一组相关操作，这些操作要求全部完成或者全部不执行，使用事务是为了保证数据安全有效。

2、事务四大特性：
原子性：事务中所有数据修改，要么同时成功操作，要么同时失败。
一致性：操作完成，数据更新后，所有设备显示一致，进行同步。
隔离性：事务应该在操作前或者操作后进行访问。
持久性：保持事务对数据库的修改是持久有效的，即使发生系统故障，也不会丢失。

3、隔离级别：从上到下等级越来越高，数据越来越安全，效率越来越低。
     读未提交：可脏读  不可重复读  可幻读
     读已提交：不可脏读  不可重读读  可幻读 
     可重读：  不可脏读   可重复读  可幻读 （锁行）
     串行读：  不可脏读  可重复读  不可幻读 （锁表）
------------------------------------------------------------------------------ 
脏读：事务T1提交了更新操作未提交，事务T2去读取更新后的数据，T1进行回滚，数据失效，T2读取数据失败。

不可重复读:T1读取一行数据，T2对T1读取的数据进行修改，T1再次查询，发现第二次读取的数据与第一次不相同。

幻读：T1使用where子句查询时返回结果集，T2插入一条记录，插入的记录刚好符合where子句查询的条件
  ，T1读取了T2插入的记录。
  
4、oracle默认是读已提交的隔离等级，它支持读已提交和串行读隔离等级。

5、默认开启事务的情况：1、进行DML增删改操作。 
                 2、进行DCL授权(grant)和收权(revoke)操作。
                 3、正常退出sqlplus和可视化窗口数据库管理软件。
                 4、开启单个连接的自动提交，setautocommit=true；
6、回滚事务：1、意外退出，不正常的断电。
        2、手动执行rollback回滚操作。              

12、DML（数据库操作语言）

主要对数据库的数据进行操作，对数据进行增删改查，操作的单位是记录。

• 为数据插入数据

insert into 表(指定列1,……)values(值1,……)；值与表中定义的字段对应
insert into 表 values(值1，……) 值一定要与表中的所有字段进行对应。
insert into 表(指定列) select …… from  [where];查询到数据为指定列赋值。
insert into 表 select…… from [where]；查询到数据为所有列赋值。

• 更新数据操作

前提：要求数据已存在，类型长度兼容、字段兼容值
update 表名 set 字段名=值 [,....] where 过滤条件;
update 表名 set(字段名1,字段名2,……)=(select 字段名1，字段名2，……from 表名 where ……)[where] 字段名数量要一一对应，查询的数据只能有一条记录。

• 删除数据操作

--删除一个或多个或所有数据
delete from 表名 [where]
--删除主从表中的记录
--从表中的数据可以直接正常删除
--删除主表中数据
    --主表中没有被从表引用的数据，可以直接删除。
    --主表中已被从表引用的数据，不能直接删除。解决方案：1、先删除从表中引用了当前主表数据的那些从表删除，然后再删除当前主表数据（默认）。2、删除主表数据的同时，为从表所有引用当前主表数据的那些从表数据的外键字段设置为null，需要为从表中外键字段设置约束的时候加 on delete set null(删除时从键设置为null)。

13、数据截断与序列

• 数据截断

--数据截断
--实现 截断删除数据 没有事务管理 没有提交和回滚
truncate table tb_student;
--truncate与delete区别？
    --数据截断没有事务管理，而delete可以通过事务管理。
    --数据截断不能操作主表，从表结构上检查如果存在从表，不能截断，但是delete可以删除。

• 序列

--序列：
  --工具
  --管理类似主键字段的值、数值型的，有变化规律的。
  --序列与字段没有绑定，删除后，已经使用的序列值不受影响。
  --第一次获取序列值，要先获取下一个序列。
  --适合不重复记录的字段名设置
  --create sequence 序列名 start with 起始值 increment by 步长。
create sequence seq_text start with 60 increment by 10;
--获取当前序列
select seq_text.currval from dual;
--获取下一个序列
select seq_text.nextval from dual;
--删除序列
drop sequence seq_text;
--为表中字段添加序列
insert into dept values(seq_text.nextval,'','');
insert into dept values(seq_text.nextval,'网管部','北京');

14、DCL(数据库控制语言)

1、前提要登录sysdba超级管理员权限。
2、alter user 用户名 password identified by '新密码';修改密码。
3、grant  select(字段名，字段名),insert(字段名)，delete，update/all  privileges   on   表名  to   用户名/public   为用户授予权限。
4、revoke  权限名1,... on 表名 from 用户名; 回收权限。 
5、show user;显示登陆用户
6、alter user scott account unlock;解锁

15、JDBC

JDBC:java database connectivity java与数据库的连接。
角色分类：
服务器(db)：接收sql 执行sql 返回结果。
客户端：接收数据 组装sql 发送sql(与数据库建立连接） 分析结果。
jdbc操作步骤：
1、加载驱动。
2、建立连接。
3、sql预处理。
4、执行sql。
5、返回数据集。
6、关闭连接。
如果jdbc代码在执行增删改操作时处于阻塞状态，是事务没有提交，先在客户端或者使用conn.commit()先提交事务。或者  设置自定提交事务 
[sql]set autocommit on;

接口名称	作用
java.sql.connection	连接
java.sql.statement	静态处理块
java.sql.preparedstatement	预处理块
java.sql.resultset	结果集
java.sql.resultsetmetadata	结果集元信息

• 笔记

//url路径名(两种方式)
1、jdbc:oracle:thin:@//host:port/service_name (oracle端口号：1521  mysql端口：3306 )
2、jdbc:oracle:thin:@[HOST][:PORT]:SID实例
//驱动名
oracle.jdbc.driver.OracleDriver 
//使用驱动包 
ojdbc6.jar （oracle 11g版本）

前提：必须登录超级管理员权限。 sqlplus sys/1872694955 as sysdba;
--查看sid实例名
SELECT instance_name FROM v$instance;
--查看service_name值
show parameter service;
--查询sid值
select sid from v$session where username='SCOTT';

• 测试代码

 /**
     * url：两种写法
     * 1、jdbc:oracle:thin:@ip:port:sid实例名
     * 必须通过sys管理员权限
     * 查询sid实例名：select instance_name from v$instance;
     * 2、jdbc:oracle:thin:@//ip:port/service_name
     * 查询service_name:show parameter service;
     */
    private static final String  URL="jdbc:oracle:thin:@//localhost:1521/orcl.mshome.net";
    private static final String USER="SCOTT";  //用户名不分大小写
    private static final String PASSWORD="tiger";  //注意密码是小写

    public static void main(String[] args) {
      //导入驱动jar包  ojdbc6.jar
        Connection con=null;
        try {
            //加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //建立连接
           con= DriverManager.getConnection(URL,USER,PASSWORD);
          //静态处理块
            Statement statement = con.createStatement();
            //执行sql语句
            String sql="select * from dept";  //注意sql语句中不能加;
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println("部门编号："+resultSet.getInt(1));
                System.out.println("部门名称："+resultSet.getString(2));
                System.out.println("部门地点"+resultSet.getString(3));
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

• 静态处理块和预处理块的区别？

1、静态处理块是通过是sql拼接来查询，容易被SQL注入导致数据不安全。（statement）
2、预处理块是先编译，提高效率，防止sql注入，sql语句不是通过字符串的连接符直接拼接，而是通过预处理块内部拼接，拼接之前检查传入的数据，建议使用预处理块代理静态处理块。（PreparedStatement）

• 模拟登录注册测试代码

public class SQLUtils {
    private static String url =null;
    private static String username =null;
    private static String pwd =null;
   private static Properties prop = new Properties();
    //此工具类封装SQL JDBC
    static {
        try {
            prop.load(new FileInputStream("resources\\Oracle.properties"));
            url = prop.getProperty("url");
            String drive = prop.getProperty("drive");
            username = prop.getProperty("username");
            pwd = prop.getProperty("pwd");
            Class.forName(drive);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

    }

    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, pwd);
        return connection;
    }

    public  static void  closeLink(Connection con, Statement state, ResultSet rs){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    public static void closeLink(Connection con, Statement state){
        closeLink(con,state,null);
    }

    public static void main(String[] args) throws SQLException {
        Connection connection = getConnection();
        System.out.println(connection);

    }
}


 private static Connection conn=null;
   private static PreparedStatement statement=null;

   private static ResultSet rs=null;
    public static boolean register(String username,String password){
        boolean total=false;
        try {
            conn = SQLUtils.getConnection();
            String sql="insert into or_user (username,password)values(?,?)";
            statement = conn.prepareStatement(sql);
            statement.setObject(1,username);
            statement.setObject(2,password);
              int count=statement.executeUpdate();
              //execute执行返回值是若判断返回结果集 则返回true
            //若是执行增删改 返回更新次数 或者  无结果 返回false
            if (count>0){
                total=true;
            }
            return total;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            SQLUtils.closeLink(conn,statement);
        }


    }

    public static void main(String[] args) {
        System.out.println(register("王五","123456789")?"注册成功":"注册失败");
    }

• 模拟转账事务

//模拟转账事务
        Connection connection=null;
        PreparedStatement statement1=null;
        PreparedStatement statement2=null;
        ResultSet rs=null;
        try {
            connection = SQLUtils.getConnection();
            //设置不自动提交
            connection.setAutoCommit(false);
            String sql1="update emp set sal=sal+1000 where empno=1201";
            String sql2="update emp set sal=sal-1000 where empno=1000";
            statement1=connection.prepareStatement(sql1);
            statement2=connection.prepareStatement(sql2);
            int row1 = statement2.executeUpdate();
            int row2 = statement1.executeUpdate();
            if(row1>0 && row2>0){
                connection.commit();//提交事务
            }else{
                connection.rollback();  //回滚事务
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            SQLUtils.closeLink(connection,statement1);
            if(statement2!=null){
                try {
                    statement2.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }

标签：sal,--,基础知识,emp,Oracle,查询,where,select
From： https://www.cnblogs.com/smallzengstudy/p/17626474.html