首先声明，本文中的emp表是安装的Oracle自带的普通用户账号SCOTT（SCOTT用户下自带了4张表：bonus,dept,emp,salgrade）下的一张表，共有十四条数据，截图如下
sql基础：
sql 结构化查询语言
数据库软件的作用是：存储数据，管理数据 分析数据
oracle 甲骨文公司 大型企业用的较多 版本号是11g
sqlserver 微软公司
DB2 IBM公司
plsql 一款链接oracle数据库的可视化界面工具软件
sql语言：结构化查询语言
功能：实现数据的增 删 改 查
语法：关键字的使用规范、标准
sql语言语法的分类：
-
DQL 数据查询语言
-
DML 数据操控语言
-
DDL 数据定义语言
-
DCL 数据控制语言
-
TCL 事务控制语言
DQL数据查询语言包含的内容
-
简单查询
-
条件查询
-
排序分组查询
-
函数（系统函数：转换函数、字符型函数、数值型函数、日期型函数、其它函数、自定义函数）
-
嵌套函数
-
关联函数
-
开窗函数
1.1简单查询
语法： select * from 表名; --简单查询 --查询emp表中的所有数据 select * from EMP t; select --检索(关键字) * --通配符 表示所有数据 from --从(关键字) EMP --表名 t --表别名，可用可不用，可加可不加
*指定列查询
语法: select 列名1，列名2...n from 表名; --查询员工表中的员工姓名，工资，领导编号 select ename,sal,mgr from emp; --注意点： oracle中的代码（关键字，表名，列名）不区分大小写，数据值区分大小写
运算符 四则运算 + - * / ()
--查询员工表中的员工编号，姓名，月薪，奖金（提成），年薪（考虑提成字段） select empno,ename,sal,comm,(sal+ comm)*12 from emp;--注意很多地方没有值 nal(参数1,参数2) --空值处理函数 --参数1：要处理的列 --参数2：将参数1替换成一个值 select empno,ename,sal,comm,(sal+ nvl(comm,0))*12 from emp; select empno,ename,sal,comm,nvl(comm,0),(sal+ (comm))*12 from emp;
别名
--表别名 --用表别名去查询员工姓名，领导编号，工资，部门编号 select e.ename,e.mgr,e.deptno,e.job from emp e; --列别名 语法： select 列1 as "别名"(列1 "别名",注意：双引号可以不用加前提是 别名是英文字母，如果是数字，汉字，特殊字符必须要加上双引号
拼接符 ||管道符
--定义：将两个字段或者多个字段连接到一起变成一个字段
select ename,sal,job from emp; select ename||sal||job 拼接后的值 from emp; 将常量和常量，常量和变量，变量和变量拼接在一起，形成一个字段的符号 select ename ||'ename' from emp;--常量必须用单引号，双引号会报错 select *|列名|函数|计算表达式|常量 from 表名; --案例： --1、查询结果展示："员工***的奖金是**" 并起别名为奖金 select '员工'||ename||'的奖金是'||comm "奖金" from emp; --2、查询emp表，结果展示: "员工xxx今年的奖金是：xxx，工资是xxx，年薪是xxx" select '员工'||ename||'今年的奖金是：'||comm||'工资是'||sal || '，年薪是' ||(sal+ nvl(comm,0))*12 from emp;
排序查询
定义： 将查询结果按照一定的规则 排序
语法：
--注意：后面标注的1，2，3，4是查询时系统执行的顺序
select *|列|函数|计算表达式|常量 ---3.将满足条件的数据检索出来
from 表名 ---1.找到表
where 过滤条件 --2.数据要满足过滤条件
--下面大括号里面的内容皆是where后面可以使用的过滤条件
{
- (not) in
- =、<>(不等号)或者!=、>、<、>=、<=
- (not)between 内容 and 内容
- (not)like' 查询内容 '
- in (not)null --select * from emp where comm is (not)null;
- any (集合)
- all (集合)
}
order by 排序的内容 [asc]|desc; --asc 升序 desc 降序 --4.排序
案例展示
1.查询emp表中的员工信息，结果按照工资升序展示
select * from emp order by sal asc; select * from emp order by sal ; --不给排序规则，默认升序
2.查询emp表中20号部门的员工信息，结果按照工资降序展示
select * ---3 from emp --1 where deptno=20 ---2 order by sal desc; --4
3.查询emp表中的员工信息，结果按照部门编号升序展示，部门相同的人再按照工资降序展示
select * ---2 from emp ---1 order by deptno asc,sal desc;----3 --查询emp表中的员工信息，结果按照部门编号降序展示，部门相同的人再按照工资降序展示 select * from emp order by deptno desc,sal desc;
4.查询emp表中的员工信息，结果按照部门编号升序展示，部门相同的人再按照工资降序展示，要求工资大于1000的人才统计
select * ---3 from emp ---1 where sal>1000---2 order by deptno asc,sal desc; ---4
5.查询20号部门的员工姓名，工资，年薪，并且按照年薪降序排序
select ename,sal,sal*12 from emp where deptno=20 order by sal*12 desc; ---用计算表达式排序 select ename, sal, sal * 12 as 年薪 ---3 from emp --1 where deptno = 20 ---2 order by 年薪 desc; ---4 ---用列别名进行排序 ---查询20号部门的员工姓名，工资，年薪，并且按照年薪降序排序，年薪大于20000的才展示 select ename, sal, sal * 12 as 年薪 ---3 from emp --1 where deptno = 20 and (sal*12)>20000 ---2 order by 年薪 desc; ---4 ---用列别名进行排序
--错误案例：
查询20号部门的员工姓名，工资，年薪，并且按照年薪降序排序
select ename, sal, sal * 12 as 年薪 ---3 from emp --1 where deptno = 20 and 年薪>20000 ---2 ---报错：年薪 标识符无效 order by 年薪 desc; ---4 ---用列别名进行排序
6.查询emp表中的员工信息，结果按照奖金升序展示
select * from emp order by comm asc; ---null 空值默认最大 --查询emp表中的员工信息，结果按照奖金降序展示 select * from emp order by comm desc; ---null 空值默认最大 select * from emp order by comm asc nulls first; ---nulls first 控制空值放在首位 select * from emp order by comm desc nulls last; --nulls last 控制空值放在末位 select t.*,nvl(comm,0) from emp t order by nvl(comm,0) desc;
--案例 --1.查询员工信息表中部门编号是10或者部门编号是20， --并且岗位是经理的，并且入职日期早于1982/01/01的员工编号、员工姓名，工资，年薪， --并且按照部门编号和年薪降序排序
--方法一
select empno, ename, sal, sal * 12 as "年薪"
from emp
where (deptno = 10 or deptno = 20)
and job = 'MANAGER'
and hiredate < to_date('1982/01/01', 'yyyy-mm-dd')
order by deptno desc, 年薪 desc;
--方法二
select empno,ename,sal,sal*12 s from emp where deptno in(10,20)
and job='MANAGER' and hiredate<to_date('1982/01/01','yyyy/mm/dd')
order by deptno desc,s desc;
--方法三
select empno, ename, sal, sal * 12 ,deptno
from emp T
where hiredate < to_date('1982-01-01', 'yyyy-mm-dd')
and deptno in (10, 20)
and job = 'MANAGER'
order by deptno desc,sal * 12 desc;
order by 注意点：
1-order by 排序时，可以按照原字段排序，也可以按照字段的别名去排序 2-执行顺序： from - where -select - order by 3-order by 排序时，不加排序规则，默认为 asc 升序 4-order by 后边可以放多个字段，每个字段用逗号分隔，每个字段后边紧跟自己的排序规则，不加排序规则时默认的为升序。 先按第一个字段排序，第一个字段相同的情况下，再按照下一个字段排序，以此类推。
条件查询WHERE
定义：按照一定的规则或者条件 过滤掉不需要的数据（不符合条件的数据） 语法：
select *|列|函数|计算表达式|常量 ---3.将满足条件的数据检索并展示来 from 表名 ---1.找到表 where 过滤条件; ----2.数据要满足过滤条件
******过滤条件****
-
逻辑运算
-
包含查询
-
范围查询
-
模糊查询
-
null 判断
-
any 和 all
1.逻辑运算
逻辑运算符： =,不等号：<> 或者 !=,>,<,>=,<= 条件之间的逻辑运算符： and：并且，和（与）， and 连接两边的条件 必须同时满足 or： 或者，或 or 连接两边的条件 满足其一即可 and 和 or 同时存在 ，优先级：and > or 括号可以改变优先级
常见的数据类型
数值型（数字型）：number 靠右 number(4) --数字代表数据精度，存储数据的长度 number(7,2)--7 代表整个数据的长度，2代表 小数位的长度 number --默认38位 select * from emp for update; ---该查询语句可以编辑表数据 ，点击查询出来的表上面的棕色小锁进行解锁即可暂时更改查询出来表的内容，更改后点击上面运行按钮右边的提交事务按钮，则该表内容已经被彻底更改； 字符型：varchar2 靠左 varchar2 --是oracle中特有的字符型数据 --可变长度的字符类型，指存储数据的时候按照实际长度来存储 varchar2(10)--10是数据的最大存储长度，如果实际只有5位，按照实际长度5位来存储 char --固定长度的字符类型 char(10) ---10是数据的最大存储长度，如果实际只有5位，即长度达不到定义的长度，则右侧以空格填充 varchar --可变长度字符类型 --标准sql中
1.1数字型之间的逻辑运算
--1.查询工资大于2000的员工姓名，工资，入职日期 select ename,sal,hiredate from emp where sal>2000; --2.查询工资不低于3000的员工姓名，工资，入职日期，部门编号 select ename,sal,hiredate,deptno from emp where sal>=3000; select ename,sal,hiredate,deptno from emp where sal>='y'; --提示：无效数字 --注意：逻辑运算两边的数据类型要保持一致 --3.查询奖金大于400的员工信息 select * from emp where comm>400; ---2条数据 --查询奖金小于400的员工信息 select * from emp where comm<400; ---3 条数据 --注意：空值不参与逻辑运算（不参与比较）*************** select * from emp where nvl(comm,0)<400;
1.2字符型之间的逻辑运算
--1.查询姓名为SMITH的员工编号，工资，部门编号 select empno,sal,deptno,ename from emp where ename='SMITH'; --字符型数据需要加单引号，即该数据为常量数据 select empno,sal,deptno,ename from emp where ename='SMith'; --表中数据值区分大小写的。 --案例 ---1.薪资超过2500的员工信息 select * from emp where sal>2500; ---2.查询薪资低于1000员工的部门、岗位及入职日期 select deptno,job,hiredate from emp where sal<1000; ---3.查询岗位非销售的员工信息 select * from emp where job !='SALESMAN';
1.3日期型的逻辑运算
--查询入职日期为1981-2-20的员工信息
select * from emp where hiredate= 1981-2-20; --报错：数据类型不一致
select * from emp where hiredate= '1981-2-20'; --报错：数据类型不一致
select 1981-2-20 from emp; --1959
--to_date() 将字符型数据转为日期型
to_date(参数1，参数2)
--参数1：要转换的内容（字符型数据）
--参数2：日期格式
select 1981-2-20,'1981-2-20',to_date('1981-2-20','yyyy-mm-dd') from emp;
select * from emp where hiredate=to_date('1981-2-20','yyyy-mm-dd') ;
--案例：
--查询入职日期为1982-1-1之前入职的员工信息
select * from emp where hiredate < to_date('1982-1-1','yyyy-mm-dd');
select * from emp where hiredate <to_date('1982','yyyy');
select to_date('1982','yyyy') from emp; ---1982-05-01 :获取数据库所在服务器时间的当前月份的第一天
1.4.条件连接（条件之间的逻辑运算）
and：并且，和（与） and 连接两边的条件 必须同时满足 or： 或者，或 or 连接两边的条件 满足其一即可 and 和 or 同时存在 ，优先级：and > or 括号可以改变优先级 --案例： 1-查询工资大于2000且部门编号为20的员工信息 select * from emp where sal>2000 and deptno=20; 2-查询岗位既不是销售也是不是分析师 ANALYST 的员工信息 select * from emp where job<>'SALESMAN' and job<>'ANALYST'; 3-查询岗位是经理 MANAGER 或者部门编号为10的员工信息 select * from emp where job='MANAGER' or deptno=10; 4-查询岗位是经理 MANAGER 并且 部门编号为10的员工信息 select * from emp where job='MANAGER' and deptno=10; 5-查询部门编号等于20或者部门编号等于10且名字为KING的员工信息 --角度一：条件A:部门编号等于20或者部门编号等于10 并且 条件B：名字为KING select * from emp where (deptno=20 or deptno=10) and ename='KING'; --角度二：条件A:部门编号等于20 或者 条件B： 部门编号等于10且名字为KING select * from emp where deptno=20 or deptno=10 and ename='KING'; --案例2： --1.查询员工mgr是7698 并且 deptno 是30的员工信息 select * from emp where mgr=7698 and deptno=30; --2.查询岗位是销售SALESMAN，并且奖金超过400的员工 select * from emp where job='SALESMAN' and comm>400; --3.查询部门编号是20的经理（MANAGER）信息 select * from emp where deptno=20 and job='MANAGER'; --4.查询10号部门的部门经理或者查询20号部门的销售人员的信息 select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='SALESMAN'; --5.查询10号部门的员工、30号部门的经理以及所有的销售人员 select * from emp where deptno=10 and job='CLERK' or deptno=30 and job='MANAGER' or job='SALESMAN'; select * from emp where deptno=10 or deptno=30 and job ='MANAGER' or job='SALESMAN'; select * from emp where deptno=10 OR (deptno=30 and JOB='MANAGER') OR (deptno=30 and JOB='SALESMAN'); select * from emp where deptno=10 OR deptno=30 and (JOB='MANAGER' or JOB='SALESMAN');
2.包含查询in
in(集合)--在集合里即满足条件
not in(集合)--不在集合里即满足条件
集合：同一类型数据的组合
in(2,3,4,5,6,78)
in('b','g','t','y')
--查询部门编号为10号或者20号部门的员工信息
select * from emp where deptno=10 or deptno=20;
select * from emp where deptno in (10,20);
--查询部门编号不为10 并且不为20的员工信息
select * from emp where deptno not in (10,20);
select * from emp where deptno<>10 and deptno<>20;
--案例：
--1.查询薪资是3000或者5000的员工信息（用两种方法）
select * from emp where sal=3000 or sal=5000;
select * from emp where sal in(3000,5000);
--2.查询岗位是SALESMAN或者是MANAGER的员工信息（用两种方法）
select * from emp where job='SALESMAN' or job='MANAGER';
select * from emp where job in ('SALESMAN','MANAGER');
--3.查询岗位既不是SALESMAN也不是MANAGER的员工信息（用两种方法)
select * from emp where job!='SALESMAN' and job!='MANAGER';
select * from emp where job not in('SALESMAN','MANAGER');
3.范围查询
between A and B A<B --在A和B之间 等价于 >=A and <=B 边界值可以取到的 not between A and B --不在A和B之间 等价于 <A or >B --查询员工工资在1500和3000之间的员工姓名，工资，部门编号 select ename,sal,deptno from emp where sal between 1500 and 3000; --查询员工工资超过1500且不高于3000的员工信息 select * from emp where sal>1500 and sal<=3000; select * from emp where sal between 1500 and 3000 and sal!=1500; select * from emp where sal between 1500 and 3000 and sal<>1500; select * from emp where sal between 1500 and 3000 and sal>1500
4.模糊查询
like 像，匹配 not like 不匹配 % 通配符 --未知位数的未知字符 _ 占位符 --只占一位的未知字符 --查询姓名为SMITH的员工信息 ----精确查询 select * from emp where ename='SMITH'; --查询姓名为以S开头的员工信息 select * from emp where ename like 'S%'; --查询姓名中包含字母A的员工信息 select * from emp where ename like '%A%'; select * from emp where ename = '%A%'; ---查询结果为空 精确查询 --查询姓名倒数第三个字符为A的员工信息 select * from emp where ename like '%A__'; --销售订单表 --查询订单表中客户姓 李的相关订单 select * from 订单表 where 客户姓名 like '李%'; --1.查找姓名前边SM 后边是 TH ，中间有一位不确定的员工信息 select * from emp where ename like 'SM_TH'; --2.查找姓名总共有五位的员工信息 select * from emp where ename like '_____'; --3.查找姓名前边是S 后边是H中间有三位不确定的员工信息 select * from emp where ename like 'S___H'; --4.查询姓名前边是SMT 最后一位字符不确定的员工信息 select * from emp where ename like 'SMT%_'; --5.查找姓名不以S开头的员工信息 select * from emp where ename not like 'S%';
5.null 判断
5. null 判断 --查询员工表中没有奖金的人 select * from emp where comm is null; --查询员工表中有奖金的人 select * from emp where comm is not null;
6.any 和 all
any 任何 任意 all 所有 全部 any(集合) --满足集合任意一个即满足条件 all(集合) --满足集合所有值 即满足条件 >any() --大于集合中的 最小值 <any() --小于集合中的 最大值 >all() --大于集合中的最大值 <all() --小于集合中的最小值 select * from emp where deptno >any(15,16,17,29,45); select * from emp where deptno <any(15,16,17,29,45); select * from emp where deptno >all(15,16,17,29);
总结:
where 过滤条件; 1.逻辑运算 = !=或<> > < >= <= and or () 2.包含查询 (not)in(集合) 3.范围查询 between A and B 等价于 >=A and <=B ---边界值可以取到 (not)between A and B 等价于 <A or >B 4.模糊查询 (not)like % _ 5.判空 is (not) null 6. any all
分组查询group by
5.15-分组查询 group by 定义：按照一定的规则分组，统一分析各组的情况，每一个组返回一个结果值
--聚合函数（分组函数） sum() 求和 avg() 求平均 max() 最大 min() 最小 count() 统计、计数
关键：
--查询员工表中的人数 select count(*) from emp; --整张表的记录数 select count(empno) from emp; --选择一个主键列 （代表表中每条数据的唯一标识：非空且唯一） select count(1) from emp;--整张表的记录数
案例：
--8.统计薪资在1000到3000之间的人员中，有奖金的员工总数及无奖金的员工总数
select count(1) as 总人数,count(comm) as 有奖金的人数 ,count(1)-count(comm) as 无奖金的人数
from emp
where sal>=1000 and sal<=3000;
select count(comm),count(1),(count(1)-count(comm))
from emp
where sal between 1000 and 3000;
SELECT COUNT(COMM) "有奖金",COUNT(EMPNO) - COUNT(COMM) "无奖金"
FROM EMP
WHERE SAL BETWEEN 1000 AND 3000;
--6.每个部门员工的平均工资,去掉小于2000的工资，按照部门平均工资排名
select avg(sal),deptno
from emp
where sal>=2000
group by deptno
order by avg(sal);
--1.查询 1982年以前入职的员工，按部门、职位，查出平均薪资超过800的部门 和 岗位类型
--及其 平均薪资 和 最高薪资，按最高薪资降序排列
select deptno,job,avg(sal),max(sal)
from emp
where hiredate<to_date('1982-1-1','yyyy-mm-dd') --这里要给出具体的年月日，否则会查到当前的月份的第一天的
group by deptno,job
having avg(sal)>800
order by max(sal) desc;
--案例：
--查询emp表中的所有人的工资 select sal from emp; --查询emp表中的最大工资 select max(sal) from emp; --查询emp表中的最小工资 select min(sal) from emp; select avg(sal) from emp; select sum(sal) from emp; ---count： select count(sal) from emp; ---统计个数 14 select count(comm) from emp; ---空值不参与统计 ---5 select count(empno) from emp; ---14 --count计数时，传具体的列名，统计的是该列不为空值的个数。 select count(*) from emp; --->整张表的记录数 --查询有奖金的人数 select count(comm) from emp; ---5 select count(1) from emp where comm is not null;
6.查询除了10号部门以外的每个部门每个岗位的平均工资，要求平均工资高于2000的才显示,结果按照平均工资降序展示 select deptno,job,avg(sal) ---5 from emp ---1 where deptno<>10 ---2 group by deptno,job ---3 having avg(sal)>2000 ---4 order by avg(sal) desc; ---6 select 列|函数|表达式|常量 ---5 检索并展示数据 from emp ---1 找到表 where 过滤条件 --2数据要满足的过滤条件 group by 分组的内容 ---3按分组的需求进行数据分组 having 分组后的过滤条件 ---4分组后条件限制的话，用having进行条件过滤 order by 排序内容 asc/desc --6按照字段顺序进行顺序排序
--案例：
1.查询每个部门的工资总和。 --定义：按照一定的规则分组，统一分析各组的情况，每一个组返回一个结果值 select deptno, sum(sal) from emp group by deptno select sum(sal) from emp group by deptno; --在分组查询时，group by 后边出现的字段，select 后边可以不去展示。 select deptno, ename, sum(sal) ---报错： 不是group by 表达式 from emp group by deptno select deptno, ename, sum(sal) --每个人的总工资 from emp group by deptno,ename --按部门和姓名两个字段分组，取到的是每个人的总工资 --在分组查询时，select 后边想要展示某个字段，就必须要按照该字段去分组，也就是说group by 后边必须得有该字段。 2.查询各个部门下不同岗位的总工资 （查询每个部门每个岗位的总工资） select deptno,job, sum(sal) from emp group by deptno,job order by deptno; select deptno,job, sum(sal) from emp group by job,deptno order by deptno; 3.查询每个部门的每个岗位的平均工资，要求平均工资高于2000的才展示 select deptno,job, avg(sal) ---4 from emp ---1 group by deptno,job ---2 having avg(sal)>2000 ---3 order by deptno; ---5 select deptno,job, avg(sal) as pjgz ---4 from emp ---1 group by deptno,job ----2 having pjgz>2000 ---3 pjgz 标识符无效 order by deptno; ---5
案例
--1.查询员工表中每个部门的人数 select count(*) from emp group by deptno; SELECT DEPTNO, COUNT(1) FROM EMP GROUP BY DEPTNO; --2.查询每个部门中有奖金的人数 select count(comm),deptno from emp group by deptno; select count(*), deptno from emp where comm > 0 group by deptno; select deptno, count(1) from emp where comm is not null group by deptno; --3.查询每个岗位的平均工资 select job,avg(sal) from emp group by job; 4-查询每个部门经理的平均工资 select deptno, job, avg(sal) ---4 from emp ---1 group by deptno, job ---2 having job = 'MANAGER'; ---3 ---分组后的条件过滤 select deptno,job,avg(sal) ----4 from emp ----1 where job='MANAGER' ---分组前进行条件过滤用 where ---2 group by deptno,job ---3 5.查询除了10号部门以外的每个部门每个岗位的平均工资，要求平均工资高于2000的才显示 select deptno,job,avg(sal) from emp where deptno<>10 group by deptno,job having avg(sal)>2000; select deptno,job,avg(sal) from emp group by deptno,job having deptno<>10 and avg(sal)>2000; 6.查询除了10号部门以外的每个部门每个岗位的平均工资，要求平均工资高于2000的才显示,结果按照平均工资降序展示 select deptno,job,avg(sal) ---5 from emp ---1 where deptno<>10 ---2 group by deptno,job ---3 having avg(sal)>2000 ---4 order by avg(sal) desc; ---6 select deptno,job,avg(sal) ---4 from emp ---1 group by deptno,job ---2 having deptno<>10 and avg(sal)>2000 ---3 order by avg(sal) desc; ---5 select deptno,job,avg(sal) as pjgz ---4 from emp ---1 group by deptno,job ---2 having deptno<>10 and avg(sal)>2000 ---3 order by pjgz desc; ---5 ---用别名排序
*****完整的查询sql语句**** select *|列|函数|表达式|常量 ---5 from 表名 ----1 where 分组前的过滤条件 ---不能放聚合函数 ---2 group by 分组的内容 ---3 having 分组后的过滤条件 ---4 order by 排序的内容 asc|desc; ---6
执行顺序： from - where -group by -having -select -order by
select from --在一条查询sql 语句中，至少要包含 selec from 两个关键字。
--分组查询案例：
--1、请查询表DEPT中所有部门的情况按部门编号升序排列
select *
from DEPT
order by deptno;
--2、查询销售中的最高工资。
select max(sal)
from emp
group by job
having job='SALESMAN';
select max(sal) from emp where job='SALESMAN';
SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN' GROUP BY JOB;
--3、显示所有雇员的姓名、工资和奖金，按工资的降序顺序排序，工资相同时按奖金升序
select ename,sal,comm
from emp
order by sal desc,comm;
--4、显示在一个月为30天的情况下所有雇员的日薪金并降序排列
select ename,sal/30
from emp
order by sal/30 desc;
select (sal + nvl(comm, 0)) / 30 as daysal, ename
from emp
order by daysal desc;
--5、查询至少有4个员工的部门
select count(1),deptno
from emp
group by deptno
having count(1)>=4;
--7.每个部门的工资总和 ,按照工资总和排序
select deptno,sum(sal)
from emp
group by deptno
order by sum(sal);
--8.求每个部门的员工数量想得到员工数量>5的部门
select count(1),deptno
from emp
group by deptno
having count(1)>5;
--9.查询在1981/04/13之后入职的员工，
--结果展示平均工资低于1800的部门号和该部门的平均工资
select deptno,avg(sal)
from emp
where hiredate>to_date('1981-04-13','yyyy-mm-dd')
group by deptno
having avg(sal)>1800 ;
--分组查询
--2.查询部门人数超过3的部门的部门编号、最高薪资、最低薪资、部门人数，按部门人数升序排列
select max(sal),min(sal),count(1),deptno
from emp
group by deptno
having count(1)>3;
--3.查询岗位名称不以S开头、并且岗位平均薪资超过2000的岗位的 岗位名称、岗位人数、岗位平均薪资,按岗位平均薪资升序排列
select job,count(1),avg(sal)
from emp
where job not like 'S%'
group by job
having avg(sal)>2000
order by avg(sal);
--4.查询各部门部门编号、最高薪资、最低薪资，以及最高和最低薪资的差值，按部门编号降序排列
select deptno,max(sal),min(sal),max(sal)-min(sal) as "差值"
from emp
group by deptno
order by deptno desc;
--5.统计岗位类型、最高薪资、最低薪资、薪资合计、平均薪资，以及人数，各字段以中文列名展示
select job as "岗位类型",
max(sal) as "最高薪资",
min(sal) as "最低薪资",
sum(sal) as "薪资合计",
avg(sal) as "平均薪资",
count(1) as "人数"
from emp
group by job;
--6.查询姓名重复的员工的姓名
select ename,count(ename)
from emp
group by ename
having count(ename)>1;
select ename,count(1)
from emp
group by ename
having count(1) > 1;
--7.查询各部门下,人数超过两人的岗位有哪些，显示部门编号、岗位名称
select deptno,job,count(1)
from emp
group by deptno,job
having count(1)>2;
函数
排序、分组查询 排序查询： order by 分组查询： group by 将数据按照一定规则进行分组，每组返回一个结果。一般会用到聚合函数。 聚合函数： sum、max、min、avg、count
函数： to_date(要转换的字符，日期格式)--将字符型数据转成日期型数据 nvl（要处理的列，替换后的值） ---处理空值
定义：函数是封装好的，可以直接拿来使用的程序 分类： 1.系统函数 :系统已经封装好的，可以直接拿来使用的程序 2.自定义函数： 需要程序员自己去编写的，自己去实现函数功能的一些程序----》pl\sql
1.系统函数分类： 1.1转换函数 1.2字符型函数 1.3数值型函数 1.4日期型函数 1.5聚合函数 * 1.6开窗函数 *** 1.7其他函数（条件判断，返回非空。。。）
1.1转换函数
1.1.1 to_date(参数1，参数2)
----将字符型数据转成日期型数据 参数1：目标字符 参数2：日期格式 ：yyyy-mm-dd 返回值：日期型数据
dual: oracel中的虚拟表--只是为了补全sql语法 select * from dual;
select to_date('2003','yyyy') from dual; --->2003-05-01 格式中没有mm,默认为当前月份的第一天
select to_date('200311','yyyymm') from dual;--->2003-11-01 格式中没有dd 默认为1号
select to_date('200311','yyyy/mm') from dual;
1.1.2 to_char(目标日期或者数值,格式)
作用：将日期型或者数值型数据转成想要的字符型数据 返回值：字符型数据
****将数值转成字符****** select 1234,to_char(1234),'1234' from dual;
--格式：0 ，9 表示参数中的格式 0 代表格式，如果整数位无数字，则强制显示为0，若小数位无数字，则以0补全 9 代表格式，如果整数位无数字，则格式不生效， 若小数位无数字，则以0补全 FM 作用：将用0，或者9 去格式化后带来的空格 删除，还可以给用9格式化数据时的小数位补全的0 删除
注意：在小数点左边当0和9混合使用时，如果0在最左边，则9全部当成0使用，否则看情况
0和9也可以应用于to_number()函数，但与to_char()相反
select to_char(12.456,'09099.9900$0') from dual; select to_char(12.456,'90099.9900$0') from dual; select 123.45,to_char(123.45), to_char(123.45,'999.999'),to_char(123.45,'FM999.999') from dual; select 123.45,to_char(123.45), to_char(123.45,'000.000'),to_char(123.45,'FM000.000') from dual; select 123.45,to_char(123.45), to_char(123.45,'9999.999') from dual; select 123.45,to_char(123.45), to_char(123.45,'0000.000') from dual;
0,9 若格式中小数位精度不够，则四舍五入，整数位精度不足，则显示成#########
select 123.45,to_char(123.4567), to_char(123.4567,'999.999') from dual; select 123.45,to_char(123.4567), to_char(123.4567,'000.000') from dual; select 123.45,to_char(123.4567), to_char(123.4567,'99.999') from dual; select 123.45,to_char(123.4567), to_char(123.4567,'00.000') from dual;
0,9 格式可以混合用
select 123.45,to_char(123.4567), to_char(123.4567,'00099900.990') from dual; select 123.45,to_char(123.4567), to_char(123.4567,'333.990') from dual;---无效的数字格式模型，格式化只能用 0 ，9
--千位符 123,456
--千位符加在什么位置，就会显示在什么位置 select to_char(123456.789,'fm99,999,999.0000000') from dual; --加货币符号 $:美元符号 ￥：该符号不能用，如果想显示，应该使用L L：当地货币 select to_char(123456.789,'fm$99,999,999.0000000') from dual; -->$123,456.7890000 ---$ 位置不受限制 select to_char(123456.789,'fm99,999,99$9.0000000') from dual; -->$123,456.7890000 select to_char(123456.789,'fm99,9$99,99$9.0000000') from dual; --->$ 只能出现一次 select to_char(123456.789,'fm99,9L99,999.0000000') from dual; ---》L 只能放在最前面 select to_char(123456.789,'fml99,999,999.0000000') from dual; ---》￥123,456.7890000 select to_char(123456.789,'fmL99,999,999.0000000') from dual;--->L 不区分大小写 select to_char(123456.789,'fm￥99,999,999.0000000') from dual; ---》￥无效
****将日期型转成字符******
sysdate --获取当前的系统时间 select sysdate from dual; select sysdate,to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual; ---12小时制 select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; ---24小时制 select sysdate,to_char(sysdate,'yyyy#mm#dd') from dual;--->2024#05#16 select sysdate,to_char(sysdate,'yyyy-mm-dd') from dual;--->2024-05-16 select sysdate,to_char(sysdate,'yyyy') from dual; -->2024 获取年份 select sysdate,to_char(sysdate,'year') from dual; --》twenty twenty-four select sysdate,to_char(sysdate,'mm') from dual; -->05 获取月份 select sysdate,to_char(sysdate,'month') from dual; --》5月 select sysdate,to_char(sysdate,'mon') from dual;---》5月 select sysdate,to_char(sysdate,'dd') from dual;-->16 获取当月的第几天 select sysdate,to_char(sysdate,'day') from dual; --->星期四 星期几 select sysdate,to_char(sysdate,'d') from dual; ---》5 获取当周的第几天 周日是第一天 select sysdate,to_char(sysdate,'ddd') from dual;---》137 获取当年的第几天 select sysdate,to_char(sysdate,'w') from dual;-->3 当月的第几周 select sysdate,to_char(sysdate,'ww') from dual;--》20 当年的第几周 select sysdate,to_char(sysdate,'q') from dual;--》2 当年的第几季度 --案例： --找出在2月受雇的员工 select * from emp where to_char(hiredate,'mm') = '02';
1.1.3 to_number(参数1，参数2)
功能：将字符串参数1转为数字 返回值：数值型数据
--注意： --1.被转换的字符串必须符合数据型格式。如果被转换的字符串不符合数据型格式，oracle将抛出错误提示。
select to_char(12.456,'9.9900$0') from dual;报错
select to_char(12.456,'99.9900$0') from dual;正确
select to_number('12.456',99.990) from dual;报错
select to_number('12.456',99.9990) from dual;正确
select '20245',
to_number('20245'),
to_number('1234.678')
--to_number('1234,678')
--to_number('sjfgjkad'), --无效数字
--to_number('123#656*') ---无效数字
from dual;
select to_number('-6') from dual;
--2.如果数字在格式范围内的话是正确的，否则会报错
select to_number('$124354.89080','$9999999.99999') from dual;
select to_number('1243540.8918','0000000.000000000') from dual; ---》0 代表格式时，格式中整数部分0的个数和数字位数要保持一致
select to_number('1243540.8918','99999999999.000000000') from dual;
--格式话位数不足：
select to_number('$124354.898','$9999999.99') from dual; ---提示：无效数字
select to_number('$124354.898','$999.999') from dual; --提示：无效数字
select to_number('￥124,354.898','L99999,999.999') from dual;
select to_number('124,354.898','99,999,999.999') from dual;
格式： 0 9
9 代表一个数字，标识一个有效位
0 代表格式时，格式中整数部分0的个数和数字位数要保持一致
$ 代表美元符号
L 代表当地货币符号
. 显示为小数点
, 显示一个千位符
1.2字符型函数
1.2.1 连接函数
concat(字符1，字符2)
作用：将字符1和字符2进行连接，连接成一个字符 返回值：字符型数据
select concat('EER','sfa') from dual;
select concat(ename,'sfa') from emp;
和|| 拼接对比：
1.concat只能连接两个参数，要想连接多个，必须使用函数的嵌套
select concat('姓名',concat(ename,job))from emp;
select '姓名'||ename||job from emp;
2.concat 是函数，存在于多个数据库中，|| 只适应于oracle 数据库
1.2.2大小写转换函数
upper(字符) 作用:将参数转为大写 返回值：字符型数据
lower(字符) 作用:将参数转为小写 返回值：字符型数据
select upper(ename),upper('asfTGHdgdsg智云') from emp;
select lower(ename),lower('asfTGHdgdsg智云') from emp;
1.2.3 首字母大写函数
initcap(字符) 作用：将一串字符的首字母转为大写，其余转为小写 返回值：字符型数据
select initcap('dgfsdTYJfhdfHTYJ') from dual;
select initcap('dgfsd*TYJfhd#fHTYJ') from dual; -->Dgfsd*Tyjfhd#Fhtyj
select initcap('dgfsd张三TYJfhd中国fHTYJ') from dual; --》Dgfsd张三Tyjfhd中国Fhtyj
select initcap('dgfs dTYJfhd fHTYJ') from dual; --》Dgfs Dtyjfhd Fhtyj
select initcap(ename) from emp;
1.2.4 替换函数
(1)replace(原字符，目标字符，替换后的字符) ---整体替换 (2) translate(原字符，目标字符，替换后的字符) ---逐一对应替换
案例：
select replace('ABCDEF','B','gh') from dual; -->AghCDEF
select replace('ABCDEF','Bcd','gh') from dual;--->ABCDEF
select replace('ABCDEF','BCD','gh') from dual; --->AghEF
select translate('ABCDEF','BCD','gh') from dual;--->AghEF
select replace('ABCDEBCF','BCD','gh') from dual; --->AghEBCF
select translate('ABCDEBCF','BCD','gh') from dual;--->AghEghF
select translate('abcdefgatbhijik','ab','hm') from dual;-->hmcdefghtmhijik
select replace('abcdefgatbhijik','ab','hm') from dual;--->hmcdefgatbhijik
select translate('abcdefgatbhijik','ab','hmmmm') from dual;-->hmcdefghtmhijik ->如果参数3中对应的字符过多，多余的字符不影响结果
select replace('abcdefgatbhijik','ab','hmmmm') from dual;--->hmmmmcdefgatbhijik
select translate('abcdefgatbhifjik','abf','hmmmm') from dual;-->hmcdemghtmhimjik 逐一对应替换
select translate('abcdefgatbhifjik','abf','hm') from dual; ---》hmcdeghtmhijik -》如果参数3中没有对应的字符，则替换成 null
select translate('abcdefgatbhifjik','abf','hm ') from dual; ---》hmcde ghtmhi jik --》 f 替换成 空格
select translate('123456','#123','h') from dual; --》456 如果想筛掉参数1中某些字符，应在参数2中传入一个不相关的字符，同时，在参数3中也传入一个不相关的字符
select translate('123456','#','') from dual; --》如果替换字符（参数3）整个为空，则结果直接返回为null
select translate('123456','','h') from dual; --》如果目标字符（参数2）整个为空，则结果直接返回为null
select translate('123456','#',' ') from dual;---》123456
--案例：
--1 以首字母大写的方式显示所有员工的姓名 select initcap(ename) from emp; --2 以首字母大写的方式显示所有员工的岗位 select initcap(job) from emp; --3 显示所有员工的姓名，用“我是老虎”替换所有“K” select replace(ename,'K','我是老虎') from emp; --4.将员工的职位用小写显示 select lower(job) from emp; --5.将员工的名字分别用大写和小写显示 select upper(ename) as 大写,lower(ename) as 小写 from emp;
1.2.5 去除空格函数
trim(字符) 作用：去除字符左右两边的空格 返回值：字符
select trim(' dfsdg dgsdg iyiy ') from dual;
ltrim(字符) 作用：去除字符左边的空格 返回值：字符
select ltrim(' dfsdg dgsdg iyiy ') from dual;
rtrim(字符) 作用：去除字符右边的空格 返回值：字符
select rtrim(' dfsdg dgsdg iyiy ') from dual;
--去除中间空格
select replace(' dfsdg dgsdg iyiy ',' ','') from dual;
1.2.6 截取函数*********
substr(原字符，位置参数n,长度参数L) 作用： 将原字符从第n位开始截取，截取出L个长度 返回值：字符型数据
select substr('hello world',1,8) from dual;--->hello wo 空格也占一位
select substr('hello world',3,4) from dual; ---》llo
--截取员工表中姓名的前两位字符
select ename,substr(ename,1,2) from emp;
select ename,substr(ename,0,2) from emp; ---位置参数传0和1 结果一样，都是从第一位开始截取
select ename,substr(ename,-1,2) from emp; --位置参数传-1，从倒数第一位开始，正向去截取长度
select ename,substr(ename,-1,8) from emp; --长度参数超过剩余位数，也是截取到最后一位
select ename,substr(ename,-3,8) from emp;
select ename,substr(ename,-3,2) from emp;
select ename,substr(ename,2,-4) from emp; --》null 长度参数不能为0或者负数必须大于0
select ename,substr(ename,2,0) from emp; --》null
select ename,substr(ename,2) from emp; ---长度参数不传参，默认截取到原字符的最后一位
select ename,substr(ename,2,1000000) from emp; -->长度参数再大也不影响结果
总结： 1.位置参数传0 和1 结果一样，都是从第一位开始截取 2.当位置参数为负数时，是从倒数第几位开始，找位置是从后往前数的 3.长度参数不能为0或者负数，必须大于0，否则返回为空值 4.不管位置是从前开始还是从后开始，截取的时候都是从左向右去截取 5.位置参数不能省略，长度参数可以省略，即截取到最后 --案例2：
--1.截取字符串'ABCDEE'中的前4个字符 dual
select substr('ABCDEE',1,4) from dual;
--2.截取名字中的前三位字符
select ename,substr(ename,1,3) from emp;
--3.从名字中的第二位字符开始，截取三位长度
select ename,substr(ename,2,3) from emp;
--1.截取字符串'ABCDEE'中的前4个字符 dual
select substr('ABCDEF',0,4) from dual;
--2.截取名字中的前三位字符
select substr(ename,0,3) from emp;
--3.从名字中的第二位字符开始，截取三位长度
select substr(ename,2,3) from emp;
1.2.7 填充函数
lpad(原字符，总长度L，要填充的值) 作用：向原字符左侧填充想要的值，使其总长度达到L 返回：字符型
rpad(原字符，总长度L，要填充的值) 作用：向原字符右侧填充想要的值，使其总长度达到L 返回：字符型
select lpad('ABC',10,'*') from dual;
select rpad('ABC',10,'*') from dual;
1.2.8 获取字符长度*******
length(参数) 作用：返回参数的总长度 返回值：数字
select length('sfafasg') from dual;
select length('张三') from dual;
lengthb(参数) 作用：返回参数的字节长度 返回值：数字
select lengthb('sfafasg') from dual;--一个英文字符 占一个字节长度
select lengthb('张三') from dual;--取参数的字节长度，一个汉字占2个字节 (GBK) 一个汉字占3个字节(UTF-8)
1.2.9 获取字符位置的函数 定位字符位置函数*********
instr(参数1，参数2，n，m) 作用：定位字符位置，查询字符位置 返回值：数字
参数1：原字符 参数2：目标字符 参数3：从第n位开始找 参数4：目标字符出现第m次
返回：目标字符出现第m次的位置（原字符的第一位开始数）
select instr('qweroracle','e',1,2),length('qweroracle') from dual; --->10
select instr('qweroracle','w',3,1),length('qweroracle') from dual; --->0 找不到
select instr('qweroracle','e',2,1),length('qweroracle') from dual; --->3
select instr('hello world',' ',1,1),length('hello world') from dual; --->原字符有空格，空格也占一位
select instr('qwerorweracle','wer',1,2),length('qwerorweracle') from dual;--》7 查询字符串时，字符串要整体匹配，返回目标字符中第一个字符出现的位置
select instr('qwerorwerawecrle','wer',1,3),length('qwerorweracle') from dual;
---最后一个参数不传参，默认为第一次出现的位置
select length('jshfuakfidjdkaiwj'),instr('jshfuakfidjdkaiwj','j',3) from dual; ---》11
select instr('jshfuakfidjdkaiwj','j') from dual; --位置参数不写默认的是1
--第3个为参数是负数时，倒数第n位开始找，倒着找目标字符 位置：顺位数位置（正着数位置）
select length('jshfuakfidjdkaiwj')，instr('jshfuakfidjdkaiwj','j',-7,2) from dual;----》1
select length('jshfuakfidjdkaiwj')，instr('jshfuakfidjdkaiwj','j',-7,1) from dual;----》11
select instr('qwerorweracle','wer',-3,2),length('qwerorweracle') from dual; ---》2
案例
--1.以首字母小写后面大写方式显示所有员工姓名
select concat(lower(substr(ename,1,1)),substr(upper(ename),2)) from emp;
--2.显示所有员工姓名的后三个字符
select substr(ename,-3) from emp;
--3.去除字符串' helle word '两边的空格
select trim(' helle word ') from dual;
--4.查询emp表中姓名长度是5位的员工信息
select ename from emp where length(ename)=5;
--5.查询姓名长度总共是五位的并且首字母是A的员工信息
select ename from emp where length(ename)=5 and substr(ename,1,1)='A';
--6.按照员工姓名长度分组统计每组的人数
select count(*) from emp group by length(ename);
--1.找出ename中A第一次出现的位置,显示员工编号,名字,A的位置
select ename,instr(ename,'A',1,1) from emp;
--2.把员工姓名的最后一位变成 '你好'
select concat(substr(ename,1,length(ename)-1),'你好') from emp;
--3.把员工姓名长度大于4位的信息查出来，并把最后一位变成 '你好'
select concat(substr(ename,1,length(ename)-1),'你好') from emp where length(ename)>4;
--4.把员工姓名最后一位变成 '你好'，并把转换后的字符串长度大于6 位的查出来
select e.*,concat(substr(ename,1,length(ename)-1),'你好') from emp e where length(concat(substr(ename,1,length(ename)-1),'你好'))>6;
--5.筛出姓名中包含'L'的姓名，并且取第一个'L'到第3个字符,显示员工编号,员工姓名,截取后的字符
select ename,empno,substr(ename,instr(ename,'L',1),3) from emp where instr(ename,'L',1)>0;
SELECT EMPNO,ENAME,SUBSTR(ENAME,INSTR(ENAME,'L'),3)FROM EMPWHERE ENAME LIKE '%L%';
1.3 数值函数 number
1.3.1 绝对值函数
abs(数值) 作用：返回参数的绝对值 返回值：数值
select abs(-8),abs(8),abs(0) from dual; select abs(nvl(comm,0)-sal) from emp;
1.3.2 向上取整
ceil(数值) 作用：返回离参数最近的比较大的整数
select ceil(123.45),ceil(-123.45),ceil(123) from dual; select ceil(123.0001),ceil(123.9999) from dual;---->124
1.3.3 向下取整
floor(数值) 作用：返回离参数最近的比较小的整数
select floor(123.45),floor(-123.45),floor(123) from dual; select floor(123.0001),floor(123.9999) from dual;---->123
1.3.4 取余函数 --可以判断一个数的奇偶性 ******
mod(m,n) 作用：返回m除以n的余数 返回值：数值
select mod(10,4) from dual; select mod(10,5) from dual;
select mod(17,2) from dual; --和2取余可以判断奇偶性，0为偶数反之为奇数
1.3.5 power(m,n) 取m的n次方
select power(2,3) from dual;
1.3.6 round(m,n) ******
作用：保留m中的n位小数，并且四舍五入 ---》涉及 金额 余额 返回值：数值
select round(12345.6789,3) from dual; --->12345.679 select round(12345.6789,0) from dual; --->12346 select round(12345.6789,-1) from dual; --->12350 select round(12345.6789,-2) from dual; --->12300
1.3.7 trunc(m,n) ****
作用：保留m中的n位小数，并且不会四舍五入 ---》涉及 金额 余额 返回值：数值
select trunc(12345.6789,3) from dual; --->12345.678 select trunc(12345.6789,0) from dual; --->12345 select trunc(12345.6789,-1) from dual; --->12340 select trunc(12345.6789,-2) from dual; --->12300
1.3.8 sign(n) --判断正负 --判断两个数的大小 **
作用：判断正负 返回值：数值 参数为负数时，返回 -1；参数为正数时，返回 1，参数为0时，返回 0 参数为空 返回为空
select sign(-20),sign(20),sign(0),sign(null) from dual;
--查询奖金大于工资的员工数据 select * from emp where nvl(comm,0)>sal;
select * from emp where sign(nvl(comm,0)-sal)=1;
1.3.9 sqrt(n) --开平方函数
select sqrt(16),sqrt(17) from dual;
select sqrt(-16) from dual;---提示：参数超过范围
1.4 日期函数
-
round() --会四舍五入
trunc() --不会四舍五入
select sysdate from dual;
select sysdate, round(sysdate), --最近0点的日期 round(sysdate, 'day'), --最近的星期日 round(sysdate, 'month'), --最近的月初日期 round(sysdate, 'year'),---最近的年初日期 round(sysdate, 'q') ---最近的季初日期 from dual;
select sysdate, trunc(sysdate), --当天的日期 trunc(sysdate, 'day'), --本周的星期日 trunc(sysdate, 'month'), --本月的月初日期 trunc(sysdate, 'year'),---本年的年初日期 trunc(sysdate, 'q') ---本季的季初日期 from dual;
2.months_between(date1,date2)--参数传参时，如果date1>date2 ，返回值是正数，否则为负数
作用：计算两个日期的间隔月数 返回值：数值
select months_between(sysdate,to_date('2024-01-17','yyyy-mm-dd')) from dual;
3.add_months(date,n) --月份加减函数
作用：在原有日期的基础上加上 n个月 返回值：日期型
select add_months(sysdate,4) from dual; ---月份 + select add_months(sysdate,-4) from dual; ---月份 -
4.last_day(date) --获取月末日期函数
作用：返回参数所在月的最后一天 返回值：日期
select sysdate,last_day(sysdate) from dual;
--日期间的直接运算：
1.日期直接相减 --返回结果单位是天 日期-日期=天数
select sysdate-to_date('2024-01-17','yyyy-mm-dd') from dual;
####
2.天数的加减 --返回为日期
select sysdate + 6 , sysdate - 6 from dual; ####
3.日期的加减公式
select sysdate ,sysdate + interval '7' year from dual; select sysdate ,sysdate - interval '7' year from dual; select sysdate ,sysdate + interval '-7' year from dual; select sysdate ,sysdate + interval '-7' month from dual; select sysdate ,sysdate +|- interval '+|-7' year|month|day|hour|minute|second from dual;
4.to_char() --获取日期中的 年份、月份、日期、季度、周几
--案例：
-********************
--1.查询结果显示 ：今天是****年***月***日
SELECT '今天是'||TO_CHAR(SYSDATE,'YYYY')||'年'||
TO_CHAR(SYSDATE,'MM')||'月'||
TO_CHAR(SYSDATE,'DD')||'日'
FROM DUAL;
SELECT TO_CHAR(SYSDATE, '"qwe"YYYY-MM/DD '),
TRANSLATE(TO_CHAR(SYSDATE, '"qwe"YYYY-MM/DD '),
'qwe-/ ',
'今天是年月日')
FROM DUAL;
SELECT TO_CHAR(SYSDATE, '"今天是"YYYY"年"MM"月"DD"日"') FROM DUAL;
select sysdate,translate(to_char(sysdate,'/!@yyyy*mm-dd#'),'/!@*-#','今天是年月日')
from dual;
--2.找出各月倒数第3天受雇的所有员工
SELECT *
FROM EMP
WHERE LAST_DAY(HIREDATE) - HIREDATE = 2;
*****************日期函数案例**********************
--1.查找已经入职 8 个月多的员工 ---假设当前日期是'1982-01-01'
select *
from emp
where months_between(to_date('1982-01-01','yyyy-mm-dd'),hiredate)>8 ;
--2.显示满10年服务年限的员工的姓名和受雇日期。假设当前日期是'1982-01-01'
SELECT ENAME,HIREDATE
FROM EMP
WHERE MONTHS_BETWEEN(TO_DATE('1992-01-01','YYYY-MM-DD'),HIREDATE) >= 120;----要取整就向下取
SELECT ENAME,HIREDATE
FROM EMP
WHERE HIREDATE + INTERVAL '10' YEAR <= TO_DATE('1992-01-01','YYYY-MM-DD');
--3.对于每个员工，显示其加入公司的天数。
select empno,ceil((sysdate - hiredate))
from emp;
--4.查询员工的入职日期和转正日期 以3个月试用期考核
select hiredate,add_months(hiredate,3)
from emp;
--5 查询距离现在35年前入职的员工
select * from emp where sysdate-hiredate>35*365;
--6.显示员工在此公司工作了几个月（要求结果是整数）
select trunc(months_between(sysdate,hiredate)) from emp;
--7.显示所有12月入职的员工信息
select * from emp where to_char(hiredate,'mm')=12;
1.5 其他函数
1.5.1 nvl(字段，值)--去空函数 --要求两个参数的数据类型要保持一致
作用：将字段中的空值替换成想要的值
select nvl(comm,'m') from emp; ---提示：无效数字 。 --要求两个参数的数据类型要保持一致 select nvl(comm,100) from emp; select * from emp for update; select job,nvl(job,'m') from emp; select job,nvl(job,678) from emp; ---字符型可以兼容数值型数据
1.5.2 coalesce (参数1，参数2，参数3....)--判断非空函数
返回：返回第一个不为空的参数 ---参数的数据类型要保持一致
select coalesce(comm,0) from emp; select ename,mgr,comm,job,coalesce(comm,mgr,888) from emp; select ename,mgr,comm,job,coalesce(job,ename,'888') from emp; select 678,'678' from dual;
1.5.3 distinct 关键字
作用：将查询结果去重
--查询emp表中的部门编号 select deptno from emp group by deptno; ---分组实现去重 select distinct deptno from emp ; --查询emp表中有哪些部门和岗位 select deptno,job from emp; select deptno,job from emp group by deptno,job; select distinct deptno,job from emp; 执行顺序： from -where -group by -having -select - distinct -order by
--案例： --查询emp表中有多少个部门 select count(1) from( select deptno from emp group by deptno);--嵌套查询实现 select count(1) from( select distinct deptno from emp); select count(distinct deptno) from emp;
1.5.4 条件判断函数
4.1 decode (条件表达式,值1,返回值1, 值2,返回值2 ... 默认值)，默认值如果不传参，返回为空
--案例 --查询员工工资，如果工资大于2000，显示高薪，其他显示低薪 select ename,sal,decode(sign(sal-2000),1,'高薪','低薪') from emp; 4.2 case when case when 条件1 --和 where 后边的条件写法一样 then 结果1 --满足条件1后，要执行的内容 when 条件2 then 结果2 ... else 其他结果 --不满足以上所有条件时，要执行的内容 end; --查询员工工资，如果工资大于2000，显示高薪，其他显示低薪 select ename,sal,case when sal>2000 then '高薪' else '低薪' end from emp; --查询员工工资，如果大于3000，显示高薪，等于3000，显示中薪，小于3000，显示低薪。 select sal,decode(sign(sal-3000),1,'高薪',0,'中薪','低薪') from emp; select sal,decode(sign(sal-3000),1,'高薪',0,'中薪',-1,'低薪') from emp; select sal,decode(sign(sal-3000),1,'高薪',0,'中薪',-1,'低薪',999999) from emp;
---案例：
--8.显示员工的名字, 工资, 岗位， 如果是SALESMAN 工资涨200,如果是MANAGER 工资扣200 后的结果
SELECT ENAME,SAL,JOB,
(CASE WHEN JOB = 'SALESMAN' THEN SAL + 200
WHEN JOB = 'MANAGER' THEN SAL - 200
ELSE SAL
END) "工资处理后"
FROM EMP;
SELECT ENAME,SAL,JOB,
DECODE(JOB,'SALESMAN',(SAL + 200),'MANAGER',(SAL - 200),SAL) "工资处理后"
FROM EMP;
select ename,sal,job,decode(job,'SALESMAN',sal + 200,'MANAGER',sal - 200,sal)from emp;
select ename,sal,job,
case when job = 'SALESMAN'
then sal + 200
when job = 'MANAGER'
then sal - 200
else sal
end
from emp;
--9.显示员工的名字,名字长度, 如果名字长度为奇数则显示 奇数 如果名字长度为偶数则显示 偶数
SELECT ENAME,LENGTH(ENAME),
(CASE WHEN (MOD(LENGTH(ENAME),2) = 0) THEN '偶数'
ELSE '奇数'
END)
FROM EMP;
SELECT ENAME,LENGTH(ENAME),
DECODE(MOD(LENGTH(ENAME),2),0,'偶数',1,'奇数',LENGTH(ENAME))
FROM EMP;
select ename,length(ename),decode(mod(length(ename),2),0,'偶数','奇数')
from emp;
select ename,length(ename),
case when mod(length(ename),2) = 0
then '偶数'
else '奇数'
end
from emp;
--10.查询emp中每年入职的人数
SELECT TO_CHAR(HIREDATE,'YYYY'),COUNT(1)
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY');
--11.查询emp中每个季度的入职人数
SELECT TO_CHAR(HIREDATE,'"第"Q"季度"'),COUNT(1)
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'"第"Q"季度"');
--11.查询emp中每个季度的入职人数
select case when (to_number(h) between 01 and 03)
then '1季度'
when (to_number(h) between 04 and 06)
then '2季度'
when (to_number(h) between 07 and 09 )
then '3季度'
when (to_number(h) between 10 and 12 )
then '4季度' end as 季度 ,count(1)
from (
select to_char(hiredate,'mm') as h
from emp
)
group by case when (to_number(h) between 01 and 03)
then '1季度'
when (to_number(h) between 04 and 06)
then '2季度'
when (to_number(h) between 07 and 09 )
then '3季度'
when (to_number(h) between 10 and 12 )
then '4季度' end
order by count(1);
case when 案例：
--1.显示员工的名字，工资，如果工资小于1000，显示小可怜，大于1000小于2000，显示一千多，如果工资大于2000小于3000 显示二千多，。。。三千多。。其他 显示 土豪
select ename,sal,
case when sal < 1000
then '小可怜'
when sal >= 1000 and sal < 2000
then '一千多'
when sal >= 2000 and sal < 3000
then '二千多'
when sal >= 3000 and sal < 4000
then '三千多'
else '土豪'
end
from emp;
select ename,
sal,
decode(sign(sal - 1000),-1,'小可怜',0,'一千多',1,decode(sign(sal - 2000),-1,'一千多',0,'二千多',1,decode(sign(sal - 3000),-1,'二千多',0,'三千多',1,decode(sign(sal - 4000), -1, '三千多', 1, '土豪'))))
from emp;
select ename,
sal,
case
when sal < 1000 then
'小可怜'
when sal between 1000 and 2000 and sal<>2000 then
'一千多'
when sal between 2000 and 3000 and sal<>3000 then
'二千多'
when sal between 3000 and 4000 and sal<>4000 then
'三千多'
else
'土豪'
end
from emp;
--2.使用case when表达式，结果展示职位是分析员的，工资+1000: 职位是经理的，工资+800: 职位是其它的，工资+400
select job,case when job = 'ANALYST' then sal + 1000 when job = 'MANAGER' then sal + 800 else sal + 400 end
from emp;
--3.某员工1981/10/08入职，将比该员工入职早的员工标记为老员工,其他员工标记为新员工
select ename,hiredate,case when sign((hiredate - to_date('1981-10-08','yyyy-mm-dd'))) = 1 then '新员工' else '老员工' end
from emp;
select hiredate,
case
when hiredate < to_date('1981-10-08', 'yyyy-mm-dd') then
'老员工'
else
'新员工'
end
from emp;
--4.某公司一三季度业绩不好，想将第一三季度入职员工降薪20%，给二四季度加薪10%
select case when to_char(hiredate,'q') = 1 or to_char(hiredate,'q') = 3 then sal*0.8 else sal*1.1 end
from emp;
SELECT ENAME,SAL,TO_CHAR(HIREDATE,'"第"Q"季度"') "季度",
(CASE WHEN TO_CHAR(HIREDATE,'Q') IN (1,3) THEN SAL - SAL * 0.2
ELSE SAL + SAL * 0.1
END ) "你降薪还是加薪"
FROM EMP;
--5.emp表中工资能被3整除的,提成加工资的10%，被4整除的提成加工资的20%，被5整除的提成加工资的50%
SELECT ENAME,SAL,NVL(COMM,0),
(CASE WHEN MOD(SAL,3) = 0 THEN NVL(COMM,0) + SAL * 0.1
WHEN MOD(SAL,4) = 0 THEN NVL(COMM,0) + SAL * 0.2
WHEN MOD(SAL,5) = 0 THEN NVL(COMM,0) + SAL * 0.5
END) "你的提成加了多少"
FROM EMP;
select case
when mod(sal, 3) = 0 then
nvl(comm,0) + sal * 0.1
when mod(sal, 4) = 0 then
nvl(comm,0) + sal * 0.2
when mod(sal, 5) = 0 then
nvl(comm,0) + sal * 0.5
end
from emp;
开窗函数
定义：按照一定的规则分组，统一分析各组的情况，每一条记录（数据）返回一个结果。
group by 分组查询： 定义：按照一定的规则分组，统一分析各组的情况，每一个组返回一个结果
开窗函数（窗口函数、分析函数、over 函数） 语法： 开窗函数名()over(分析子句) --分类： 1.聚合类开窗 max() min() avg() sum() count() 2.排序类开窗 row_number() rank() dense_rank() 3.偏移类开窗 lag() lead()
分析子句： partition by --分组 后边跟要分组的字段 order by--排序 后边跟要排序的字段（在聚合类开窗中有累计的功能）
--案例
1.查询每个部门的平均工资
--分组实现：
select deptno,avg(sal) from emp group by deptno;
--开窗实现：
select empno,ename,deptno,avg(sal)over(partition by deptno) from emp;
select t.*,avg(sal)over(partition by deptno) from emp t;
--扩展：
查询高于自己部门平均工资的员工信息
select *
from (select t.*, avg(sal) over(partition by deptno) as pjgz from emp t)
where sal > pjgz;
2.查询每个岗位的最高工资，显示员工姓名，岗位，工资，最高工资
select ename,job,sal,max(sal)over(partition by job) from emp;
3.查询每个部门不同岗位的工资总和及人数
select deptno,
job,
ename,
sal,
sum(sal) over(partition by deptno, job),
count(1) over(partition by deptno, job)
from emp;
4.查询员工编号，姓名，部门编号，工资，部门总人数，按部门分组在组内累计人数。
select empno,
ename,
deptno,
sal,
count(1) over(partition by deptno),
count(1) over(partition by deptno order by empno), --按部门分组在组内按empno排序并累计人数
count(1) over(partition by deptno order by sal), --按部门分组在组内按sal排序并累计人数
count(1)over(), ---整张表的人数
count(1)over(order by sal)--在整张表上按工资排序并累计人数
from emp
--order by empno;
5.查询员工编号，姓名，部门编号，工资，部门的总工资，整张表的总工资，按部门分组在组内按工资升序后的累加工资
select empno,
ename,
deptno,
sal,
sum(sal) over(partition by deptno),--->部门的总工资
sum(sal) over(), --->整张表的总工资
sum(sal) over(partition by deptno order by sal)-->按部门分组在组内按工资升序后的累加工资
from emp;
--案例：
--1.查出员工信息，并且显示相同工作的人数
select t.*, count(1) over(partition by job) from emp t;
--2.查出员工信息，并且显示相同部门相同岗位的工资总和
select t.*, sum(sal) over(partition by deptno, job) from emp t;
--3.按照deptno分组，而后计算员工工资累计
select deptno,ename,sal,
sum(sal) over(partition by deptno),
sum(sal) over(partition by deptno order by sal)
from emp;
select empno,deptno,ename,sal,
sum(sal) over(partition by deptno),
sum(sal) over(partition by deptno order by empno)
from emp
--4.查出员工信息，并且查出所有员工的总收入,部门的工资总和，
--按照员工编号升序取累计收入总和,按部门分组，同时按照员工编号排序取部门的累计收入总和
select t.*,
sum(sal) over(),--员工的总收入
sum(sal) over(partition by deptno),--部门的工资总和
sum(sal) over(partition by empno),--按照员工编号升序取累计收入总和
sum(sal) over(partition by deptno order by empno)--按部门分组，同时按照员工编号排序取部门的累计收入总和
from emp t;
1.聚合类开窗：
--1.开窗函数内必须要有内容 --2.分析子句没有硬性要求 --3.聚合类开窗中 order by 子句不光有排序的功能，还可以实现累计 --累计计数 count，累计求和 sum，累计取平均 avg，；累计取最大 max，min；
--查询每个部门的平均工资
select empno,
ename,
sal,
deptno,
avg(sal) over(partition by deptno),
avg(sal) over(partition by deptno order by sal)---累计取平均 avg
from emp;
--查询每个部门的最大工资
select empno,
ename,
sal,
deptno,
max(sal) over(partition by deptno),
max(sal) over(partition by deptno order by empno)---累计取最大
from emp;
2.排序类开窗
row_number()over() rank()over() dense_rank()over()
--对比三个函数 --查询员工表中每个部门的员工工资排序情况
select t.*,
row_number() over(partition by deptno order by sal),
rank() over(partition by deptno order by sal),
dense_rank() over(partition by deptno order by sal)
from emp t;
--注意点：
1-排序类开窗函数的括号内不能放任何内容 2-排序类开窗函数的over（） 括号中必须要有 order by 3-row_number()over() 单纯的返回当前记录在窗口中所处的位置行号，该行号有唯一性 rank()over() 排序结果中出现相同的记录时，有并列的序号，下一个排名具有跳跃性---跳号 dense_rank()over() 排序结果中出现相同的记录时，有并列的序号，下一个排名不具有跳跃性---不跳号
--查询员工表中每个部门的员工工资最高的前两名
select *
from (select t.*, row_number() over(partition by deptno order by sal desc) as q
--rank() over(partition by deptno order by sal),
-- dense_rank() over(partition by deptno order by sal)
from emp t)
where q <= 2;
select *
from (select t.*, --row_number() over(partition by deptno order by sal desc)
rank() over(partition by deptno order by sal) as q
-- dense_rank() over(partition by deptno order by sal)
from emp t)
where q <= 2;
--案例：
--1.将各部门的员工按薪水排序
select t.*, row_number() over(partition by deptno order by sal) from emp t;
--2.查找各部门中薪水最高的前3位
select *
from (select t.*,
row_number() over(partition by deptno order by sal desc) as 最高
from emp t)
where 最高 <= 3;
select *
from (select t.*, rank() over(partition by deptno order by sal) as salor
from emp t)
where salor <= 3
--3.查询每个部门每个job员工人数以及总工资
select t.*,
sum(sal) over(partition by deptno, job),
count(1) over(partition by deptno, job)
from emp t;
3.偏移类开窗
lag()over()--向下偏移 lead()over()--向上偏移
--查询员工表中员工信息，将工资列进行偏移 select t.*,lag(sal)over(order by sal) from emp t; select t.*,lead(sal)over(order by sal) from emp t; lag(参数1，参数2，参数3)over(order by 排序的字段) --参数1：要偏移的列 --参数2：偏移量 默认1 --参数3：超出偏移范围后的默认值 不传默认为空 select t.*,lag(sal,2,4444)over(order by sal) from emp t; --按部门分组，在组内将员工工资列偏移 select t.*,lag(sal)over(partition by deptno order by sal) from emp t;
--同比：当年某一时期的数据与上年同一时期的数据去比较 比如：24年4月 与23年4月 --同比增长率： （本年-上年）/上年 --环比：当年某一时期的数据与本年上一时期的数据去比较 比如：24年4月 与24年3月 --环比增长率： （本期-上期）/上期
集合运算
定义：多个查询语句的结果之间的运算 语法： sql 语句 集合运算 sql 语句 集合运算 sql 语句;
集合运算符： 1.并集 union ---去重 union all ---不去重
2.交集 intersect
3.差集 minus
1.并集
select * from emp where deptno=10;---4 select * from emp where sal>2000;----7; select * from emp where deptno=10 union ---8 select * from emp where sal>2000; select * from emp where deptno=10 union all---11 select * from emp where sal>2000;
2.交集
select * from emp where deptno=10 intersect --3 select * from emp where sal>2000;
3.差集
select * from emp where deptno=10 minus --3 select * from emp where sal>2000; select * from emp where sal>2000 minus --3 select * from emp where deptno=10;
注意点： 1.除了union all 以外，其余的集合运算，会按照第一列升序展示 2.执行顺序是从上到下，用括号改变优先级，有括号先算括号 3.两个查询语句的查询结果中的字段，数量，顺序，数据类型要保持一致 4.两个查询语句中的字段名可以不一样，最后展示的结果中会按照第一条sql语句显示字段名
select ename,job,mgr from emp union select ename,job,empno from emp;
行列转换
1.行转列 ---竖表转成横表 pivot 2.列转行 ---横表转成竖表 unpivot
--查询各个岗位在不同部门下的人数
--分组查询：
select deptno,job,count(1) from emp group by deptno,job;
select * from emp for update;
--case when
select deptno,count(1),count(case when job='CLERK' then 1 else null end) as CLERK,
count(case when job='PRESIDENT' then 1 else null end) as PRESIDENT,
count(case when job='MANAGER' then 1 else null end) as MANAGER ,
count(case when job='ANALYST' then 1 else null end) as ANALYST ,
count(case when job='' then 1 else null end) as SALESMAN
from emp group by deptno;SALESMAN
select job,count(case when deptno=10 then 1 else null end) as "10号部门",
count(case when deptno=20 then 1 else null end) as "20号部门",
count(case when deptno=30 then 1 else null end) as "30号部门"
from emp group by job;
--decode
select job,count(decode( deptno,10,1,null)) as "10号部门",
count(decode( deptno,20,1,null)) as "20号部门",
count(decode( deptno,30,1,null)) as "30号部门"
from emp group by job;
--pivot
select * from 表名 pivot(聚合函数 for 要转换的字段 in (要转换的字段的值));
select * from (select deptno,job from emp) pivot(count(1) as 部门 for deptno in (10,20,30));
select * from (select deptno,job from emp) pivot(count(1) as 部门 for deptno in (10 as "10号部门",20,30));
select *
from (select deptno, job from emp)
pivot(count(1)
for job in('CLERK' CLERK, 'PRESIDENT' PRESIDENT, 'ANALYST' ANALYST, 'MANAGER' MANAGER, 'SALESMAN' SALESMAN));
--案例：查询不同部门下各个岗位的总工资 --竖表转横表
--竖表：
select distinct job,deptno,sum (sal)over(partition by deptno ,job) as salor from emp t;
--横表
select * from (select job,deptno,sal from emp)
pivot (sum(sal) for deptno in (10,20,30));
select * from (select job,deptno,sal from emp)
pivot (sum(sal) for job in ('CLERK' CLERK, 'PRESIDENT' PRESIDENT, 'ANALYST' ANALYST, 'MANAGER' MANAGER, 'SALESMAN' SALESMAN));
select job,sum(sal),sum(case when deptno=10 then sal else 0 end) as "10部门",
sum(case when deptno=20 then sal else 0 end) "20部门",
sum(case when deptno=30 then sal else 0 end) "30部门"
from emp group by job;
2.列转行 横表转竖表 unpivot
select * from testpivot; select * from 表名 unpivot(新列名1 for 新列名2 in (值1，值2....n)); --新列名1：用来接受交叉字段的数据值 自定义列名 salnew --新列名2：用来接受交叉字段的字段(列名) 自定义列名 jobnew --值：列名2接受的交叉字段的 值
select * from testpivot unpivot(salnew for jobnew in(CLERK,MANAGER,PRESIDENT,ANALYST,SALESMAN)); --testpivot是上面竖表转横表的结果表
--合并行
--将多行合并成一行 wm_concat();--将查询出的某一列数据 只用逗号分隔并拼接成一条数据
--输出每个部门员工的名字，用一行展示 select wm_concat(ename) from emp where deptno=10; select deptno,wm_concat(ename) from emp group by deptno; select deptno,wm_concat(job) from emp group by deptno;
listagg()within group (order by 子句)--将多行合并成一行
--输出每个部门员工的名字，用一行展示 select listagg(ename,'和')within group (order by ename) from emp where deptno=10; select deptno,listagg(ename,'和')within group (order by ename) from emp group by deptno;
子查询 嵌套查询
定义：在一个查询语句中嵌套一个或者多个查询语句，该嵌套的查询语句成了外层查询的一部分
--里层查询 --子查询 --外层查询 --主查询 --把子查询（里层查询 ）的结果，当做条件来进行主查询（外层查询）
--分类： --按照子查询 是否与主查询相关联来分类： 1.不相关子查询：子查询可以单独运行，子查询不会牵扯到主查询的部分内容 --主查询只在乎子查询的查询结果，把子查询（里层查询 ）的结果，当做条件来进行主查询（外层查询）
2.相关子查询：子查询不可以单独运行，子查询会牵扯到主查询的部分内容 --主查询只在乎和子查询的关系（关联关系）
**********不相关子查询********* 1.不相关子查询：子查询可以单独运行，子查询不会牵扯到主查询的部分内容 --主查询只在乎子查询的查询结果，把子查询（里层查询 ）的结果，当做条件来进行主查询（外层查询）
分类：按照子查询的查询结果不同分类 1.1.单列子查询 1.2.多列子查询
1.1.单列子查询
语法： select *|字段 from 表名1 where 字段 =|in|>|<（select 字段 from 表名2）
案例：
--5.检索雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名
select e1.*,
e1.mgr,
(select ename from emp e3 where e3.empno = e1.mgr) as 姓名,
(select hiredate from emp e3 where e3.empno = e1.mgr) as 领导入职日期
from emp e1
where hiredate < (select hiredate from emp e2 where e1.mgr = e2.empno);
select ename, (select ename from emp where empno = t.mgr)
from emp t
where hiredate <(select hiredate from emp where empno = t.mgr);
--先查询t表中的内容然后执行where，接着查询后面的emp表，根据hiredate（此处是员工的日期）所对应的那条记录的领导编号与emp表的员工编号相等
--查询emp表中领导（一些领导也有员工编号）编号所对应的日期
--8.查询公司内哪个部门的平均工资高于整个公司的平均工资
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>(select avg(sal) from emp);
select * from
(select deptno,avg(sal) as f
from emp
group by deptno) where f>(select avg(sal) from emp);
1--查询相同工资的员工信息
select * from emp where sal in(select sal from emp group by sal having count(sal)>1);
select *
from emp p
where sal in (select sal
from emp m
where m.sal = p.sal
group by sal
having count(sal) > 1);
select *
from emp e1
where (select count(1) from emp e2 where e2.sal = e1.sal) > 1;
案例：
--1.查询地点在纽约的部门下的所有员工信息
--分步：
1.地点在纽约的部门编号
select deptno from dept where loc='NEW YORK';
2.查询步骤一结果中（部门编号）下的员工信息
select * from emp where deptno=10;
3.合并前两步
select * from emp where deptno=(select deptno from dept where loc='NEW YORK');
--子查询需要用括号括起来
--2.查询和SMITH在同一个部门下的所有员工姓名，工资，部门编号
--分步：
1.SMITH的部门编号
select deptno from emp where ename='SMITH'
2.查询步骤一结果中（部门编号）下的员工信息
select * from emp where deptno=20;
3.合并前两步
select ename,sal,deptno from emp where deptno = (select deptno from emp where ename='SMITH');
--3.查询和10号部门的岗位相同的员工信息 ----多行
1.10号部门的岗位 ----多行
select job from emp where deptno=10;
2.岗位和步骤一结果相同的员工信息
select * from emp where job =(select job from emp where deptno=10);--报错：单行子查询返回多个行
select * from emp where job in (select job from emp where deptno=10); ---in（值1，值2，值3....）
select * from emp where job in ('MANAGER','PRESIDENT','CLERK');
--4.查询emp表中的总共有多少个部门
--子查询实现
select count(1) from (
select deptno from emp group by deptno);--- 先分组去重，再取记录数 ---3个部门
select count(1) from (
select deptno from emp);--14个
--直接用distinct：
select count(distinct deptno) from emp;---3个
select count(deptno) from emp;---14个
--5.查询SMITH的领导的姓名 ---员工的领导编号==领导的员工编号
1.SMITH的领导编号 mgr
select mgr from emp where ename='SMITH';
2.取步骤一的结果对应的姓名
select ename from emp where empno=(select mgr from emp where ename='SMITH');
--6.查询显示工资比30号部门所有员工工资高的员工信息
--（1）
1.--取到30号部门的最高工资
select max(sal) from emp where deptno=30;
2.--在emp表中查询工资大于步骤一结果的员工信息
select * from emp where sal>(select max(sal) from emp where deptno=30);
--(2)
1.--取到30号部门的所有工资
select sal from emp where deptno=30;
2.>all(集合) --大于集合中的最大值
select * from emp where sal>all(select sal from emp where deptno=30);
--7.查询显示工资比30号部门任意一个员工工资高的员工信息
--（1）
1.--取30号部门的最小工资
select min(sal) from emp where deptno=30;
2.--在emp表中查询工资大于步骤一结果的员工信息
select * from emp where sal>(select min(sal) from emp where deptno=30);
--案例：
--（1）、查询和FORD相同部门的员工姓名和雇用日期 select ename,hiredate from emp where deptno=(select deptno from emp where ename='FORD'); --（2）、查询工资比公司平均工资高的员工的员工号，姓名和工资。 select empno,ename,sal from emp where sal>(select avg(sal) from emp); --（3）、查询和姓名中包含字母A的员工在相同部门的员工的员工号和姓名 select empno,ename from emp where deptno in (select deptno from emp where ename like'%A%'); --（4）、查询领导是 KING 的员工姓名和工资 select ename,sal from emp where mgr=(select empno from emp where ename='KING'); --（5）、列出工资比"SMITH"工资多的所有雇员 select * from emp where sal>(select sal from emp where ename='SMITH'); --（6）、查询emp表中所有领导的信息 select * from emp where empno in (select mgr from emp where mgr is not null); select * from emp where empno in ( select mgr from emp group by mgr);
--（3） any
any(集合) --大于集合中的最小值
select * from emp where sal>any(select sal from emp where deptno=30);
--案例：
--1.查询10号部门与20号部门在平均薪资上相差了多少
select max(sal) - min(sal)
from (select avg(sal) sal
from emp
where deptno = 20
or deptno = 10
group by deptno);
select distinct (select avg(sal) from emp where deptno = 10) -
(select avg(sal) from emp where deptno = 20) as 差值
from emp;
select (select avg(sal) from emp where deptno =10)-(select avg (sal) from emp where deptno =20) from dual;
--2.查询工资高于所有部门的平均工资的员工
select * from emp where sal>(select max(avg(sal)) from emp group by deptno);
select * from emp where sal>(select max(r) from (select avg(sal) as r from emp group by deptno));
--取所有部门平均工资工资的最大值：
select max(avg(sal)) from emp group by deptno;
select max(r) from (
select avg(sal) as r from emp group by deptno);
--> >all(集合)
select * from emp where sal>all(select avg(sal) as r from emp group by deptno);
--3.查询工资高于任意部门的平均工资的员工
select * from emp where sal>(select min(avg(sal)) from emp group by deptno);
select min(r) from (
select avg(sal) as r from emp group by deptno);
-- >any(集合)
select *
from emp
where sal > any (select avg(sal) from emp group by deptno);
--子查询可以层层嵌套，也可以平行嵌套
select (select(),select(),select()...) from 表名; ---平行嵌套
select (select (select)) ---层层嵌套
1.2.多列子查询
语法： select * from 表1 where(字段，字段)=|in(select 字段1，字段2 from 表 where 条件);
--1.查询与SMITH相同部门并且相同岗位的员工信息 select * from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH') and ename <> 'SMITH'; --2.查询和10号部门的职位和领导都相同员工信息 select * from emp where (job,mgr)in (select job,mgr from emp where deptno=10) and deptno<>10;
--注意：
--多列子查询查询出来的列的个数，顺序，数据类型要和 主查询 条件中的保持一致。
--案例：
--查询和SMITH在同一个部门并且同一个岗位的员工信息或者和ALLEN在同部门同一个岗位的员工信息
select *
from emp
where (deptno, job) in (select deptno, job
from emp
where ename = 'SMITH'
or ename = 'ALLEN')
and ename not in ('SMITH', 'ALLEN');
select *
from emp
where (deptno, job) in (select deptno, job from emp where ename = 'SMITH')
and ename != 'SMITH'
or (deptno, job) in (select deptno, job from emp where ename = 'ALLEN')
and ename != 'ALLEN';
***************************************
--案例：
--1.查询SMITH所在部门的所有员工信息 select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename<>'SMITH'; --2.查询BLAKE的带领的员工有哪些 select * from emp where mgr =(select empno from emp where ename='BLAKE'); --3.查询BLAKE的领导手下有哪些员工 select *from emp where mgr=(select mgr from emp where ename='BLAKE'); --4.查询与SMITH同部门且薪资相等的员工 select * from emp where(deptno,sal) in (select deptno,sal from emp where ename='SMITH') and ename<>'SMITH'; --5.查询与SMITH同部门同薪资或与JAMES同部门同薪资的员工 select * from emp where(deptno,sal) in (select deptno,sal from emp where ename='SMITH' or ename='JAMES'); --6.查询公司内薪资最高的员工 select * from emp where sal in (select max(sal) from emp); --7.查询公司内各部门薪资最高的员工 select * from emp where (deptno,sal) in (select deptno, max(sal) from emp group by deptno); select * from emp for update; --8.查询公司内哪个部门的平均工资高于整个公司的平均工资 select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp); select * from (select deptno,avg(sal) as f from emp group by deptno) where f>(select avg(sal) from emp); --9.查询scott的领导的领导的姓名 select ename from emp where empno=(select mgr from emp where empno=(select mgr from emp where ename='SCOTT'));
相关子查询
2.相关子查询：子查询不可以单独运行，子查询会牵扯到主查询的部分内容 --主查询只在乎和子查询的关系（关联关系）
--例题：
1.查询员工姓名，岗位，薪资，部门编号以及对应的部门名称
1.1主查询：
select ename,job,t.sal,t.deptno ,对应的部门名称 from emp t;
1.2 子查询：对应的部门名称
select dname from dept where deptno= t.deptno;
1.3 两步合并
select ename,job,t.sal,t.deptno,(select dname from dept t1 where t1.deptno= t.deptno) from emp t;
select ename,
job,
t.sal,
t.deptno,
(select dname from dept t1 where t1.deptno = t.deptno) as 部门名称,
(select loc from dept t1 where t1.deptno = t.deptno) as 部门地点
from emp t;
2.要求每个员工后边显示他的领导姓名 ---员工的领导编号 ==领导的员工编号
2.1 每个员工的信息
select t.*,t.mgr,他的领导姓名 from emp t;
2.2 员工的领导姓名
select ename from emp t1 where t1.empno=t.mgr
2.3 两步合并
select t.*,t.mgr,(select ename from emp t1 where t1.empno=t.mgr) from emp t;
--在相关子查询中，子查询和主查询用同一张表时，一定要起别名区分开
select t.*,t.mgr,(select ename from emp where empno=t.mgr) from emp t;
select t.*,t.mgr,(select ename from emp t1 where t1.empno= t.mgr) from emp t;
3.查询工资高于自己部门平均工资的员工信息
3.1
select * from emp t where sal > (自己部门的平均工资)
3.2
select avg(sal) from emp where deptno=10;
select avg(sal) from emp where deptno=20;
select avg(sal) from emp where deptno=t.deptno;
3.3
select t.*, (select avg(sal) from emp where deptno = t.deptno) as 自己部门的平均工资
from emp t
where sal > (select avg(sal) from emp where deptno = t.deptno);
--
select t.* --, --(select avg(sal) from emp where deptno = t.deptno) as 自己部门的平均工资
from emp t
where (sal)> (select avg(sal) from emp group by deptno having deptno = t.deptno);
--查询30部门的平均工资
select avg(sal) from emp where deptno=30;
select avg(sal) from emp group by deptno having deptno=30;
4.查询从事CLERK工作的员工姓名和其所在部门的名称。
select ename ,(select dname from dept where deptno=t.deptno) from emp t where job='CLERK';
--相关子查询案例：
--1.查询每个部门平均薪水 大于 所有人员平均薪水的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp);
select distinct deptno
from emp
where (select avg(sal) from emp t where t.deptno=emp.deptno)>(select avg(sal) from emp);
--2.检索部门编号、部门名称、部门所在地及其每个部门的员工总数
select distinct deptno,
(select dname from dept t1 where t1.deptno = t.deptno),
(select loc from dept t2 where t2.deptno = t.deptno),
(select count(1) from emp where deptno=t.deptno)
from emp t;
select t.deptno,
count(1),
(select dname from dept where deptno = t.deptno),
(select loc from dept where deptno = t.deptno)
from emp t
group by deptno;
select t.deptno,
dname,
loc,
(select count(1) from emp where deptno = t.deptno)
from dept t;
--3.检索30部门中1980年1月份之前入职的员工信息,显示: 员工号、员工名、部门号，部门名、入职日期.
select empno,
ename,
deptno,
(select dname from dept where deptno = t.deptno),
hiredate
from emp t
where deptno = 30
and hiredate < to_date('1980-1-1', 'yyyy-dd-mm');
--4.部门人数为3人的部门编号
select deptno from emp group by deptno having count(1) = 3;
select deptno
from (select count(1) as t, deptno from emp group by deptno)
where t = 3;
select distinct deptno
from emp t
where (select count(1) from emp where deptno = t.deptno) = 3;
select deptno
from emp e1
group by deptno
having (select count(1) from emp e2 where e1.deptno = e2.deptno) = 3
--总结：
--子查询： --不相关子查询 子查询可以单独运行 --多列子查询（子查询的返回结果与主查询的条件 在 个数、 顺序、数据类型要保持一致）
--相关子查询 子查询不可以单独运行 --子查询与主查询之间存在关联关系
--1.子查询可以放在 where 和 having 后边 --2.子查询可以放在select 后边 --注意：--只能放单行单列的子查询 select empno,(单行单列子查询) from emp; --3.子查询可以放在 from 后边 --4.子查询不能放在 group by 后边 ******* --5.子查询必须要用括号括起来 --6.子查询的返回结果与主查询的条件在 个数、 顺序、数据类型要保持一致 --7.不相关子查询返回结果为单行时可以用=|in，返回结果为多行时 必须要用 in
多表连接查询
定义：将多个表的内容拼接到一起，拼接在一起的表可以作为一个临时的数据源 ---临时表（宽表）
--什么时候会用到表连接：当遇到多个数据源时，或者一张表满足不了业务需求时
--表连接的分类： 1.内连接 2.外连接 3.交叉连接 4.特殊连接
1.内连接
语法： [inner] join ... on ... 定义： 按照关联条件将两张表关联，只显示匹配成功的数据，匹配不成功的不显示
语法： select 查询的内容 from 表1 inner join 表2 on 关联条件 inner join 表3 on 关联条件 where 分组前的条件 group by 分组的内容 having 分组后的条件 order by 排序的内容 asc|desc
需要仔细琢磨琢磨：
--1.查询出每个员工的上级领导(查询内容:员工编号、员工姓名、员工部门编号、员工工资、领导的员工编号、领导的姓名、领导的工资)
select e1.empno as 员工编号,
e1.ename as 员工姓名,
e1.deptno as 员工部门编号,
e1.sal as 员工工资,
e2.empno as 领导的员工编号,
e2.ename as 领导的姓名,
e2.sal as 领导的工资
from emp e1 --员工信息表
left join emp e2 ---领导所在的表
on e1.mgr = e2.empno;
--2.在题目1的基础上查询员工所对应的部门信息
select e1.empno as 员工编号,
e1.ename as 员工姓名,
e1.deptno as 员工部门编号,
e1.sal as 员工工资,
e2.empno as 领导的员工编号,
e2.ename as 领导的姓名,
e2.sal as 领导的工资,
d.*
from emp e1
left join emp e2
on e1.mgr = e2.empno
left join dept d
on e1.deptno = d.deptno;
--3.在题目2的基础上查询领导的部门名称
select e1.empno as 员工编号,
e1.ename as 员工姓名,
e1.deptno as 员工部门编号,
e1.sal as 员工工资,
e2.empno as 领导的员工编号,
e2.ename as 领导的姓名,
e2.sal as 领导的工资,
d.*,
d2.dname as 领导的部门编号
from emp e1
left join emp e2
on e1.mgr = e2.empno
left join dept d
on e1.deptno = d.deptno
left join dept d2
on e2.deptno=d2.deptno;
--4.查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
select e1.empno as 员工编号,
e1.ename as 姓名,
d.dname as 部门名称,
s1.grade as 工资等级,
e2.ename as 领导的姓名,
s2.grade as 工资等级
from emp e1
left join dept d
on e1.deptno = d.deptno
left join salgrade s1
on e1.sal between s1.losal and s1.hisal
left join emp e2
on e1.mgr = e2.empno
left join salgrade s2
on e2.sal between s2.losal and s2.hisal;
--5.查询职位是经理并且工资比7782号员工高的员工
select *
from emp e1
inner join (select sal from emp where empno = 7782) e2
on e1.sal > e2.sal
where job = 'MANAGER';
select ename
from emp t1
inner join (select sal from emp where empno=7782) t2
on t1.sal>t2.sal and job='MANAGER' ;
SELECT E1.*
FROM (SELECT * FROM EMP WHERE EMPNO = 7782) E
JOIN (SELECT * FROM EMP WHERE JOB = 'MANAGER') E1
ON E1.SAL > E.SAL;
--6.查询出不是领导的员工
select e1.*
from emp e1
left join emp e2
on e1.empno = e2.mgr
where e2.empno is null;
select *
from emp t1
left join (select mgr ,empno from emp) t2
on t1.empno = t2.mgr
where t2.mgr is null;
select *
from emp
where empno not in (select mgr from emp where mgr is not null);
--7.查询 员工的名字，工资，比该员工工资高的人数
--方法一：
select e1.ename, e1.sal, count(e2.empno)
from emp e1 --员工表
left join (select empno, sal from emp e2) e2 ---比表一工资高的人
on e1.sal < e2.sal
group by e1.empno,e1.ename, e1.sal;
--方法二：
select ename,sal,(select count(1) from emp t1 where t1.sal>t.sal )
from emp t;
--方法三：
select emp.ename,
emp.sal,
nvl(temp.该员工工资高的人数, 0) 该员工工资高的人数
from emp
left join (select emp.empno, count(1) 该员工工资高的人数
from emp
inner join emp t on emp.sal < t.sal
group by emp.empno) temp
on emp.empno = temp.empno
--7.查询部门人数大于所有部门平均人数的 部门编号，部门名称，部门人数
select d.deptno,d.dname,e.rs
from dept d
inner join (select deptno,count(1) as rs from emp group by deptno) e
on d.deptno = e.deptno
inner join (select avg(count(1)) as pjrs from emp group by deptno) e2
on e.rs > e2.pjrs
select t2.*, t1.dname
from dept t1
inner join (select deptno, count(1)
from emp
group by deptno
having count(1) > (select count(1) from emp) / (select count(1)
from dept)) t2
on t1.deptno = t2.deptno;
select t2.*, t1.dname
from dept t1
inner join (select deptno, count(1)
from emp
group by deptno
having count(1) > (select avg(count(1)) from emp group by deptno)) t2
on t1.deptno = t2.deptno;
--7-查询 员工的名字，工资，比该员工工资高的人数
select t1.empno,t1.ename,t1.sal,count(t2.empno)
from emp t1--员工所在的表
left join emp t2 --看做比t1表中工资高的人
on t1.sal<t2.sal
group by t1.empno,t1.ename,t1.sal
order by count(t2.empno);
--8.附加题 : 查询各部门工资的前两名
--方法一：
select t1.empno,t1.ename,t1.sal,t1.deptno,count(t2.empno)
from emp t1--员工所在的表
left join emp t2 --看做比t1表中工资高的人
on t1.sal<t2.sal and t1.deptno=t2.deptno
group by t1.empno,t1.ename,t1.sal,t1.deptno
having count(t2.empno)<=1
order by t1.deptno,count(t2.empno);
--方法二：
select *
from (select deptno, max(sal) from emp t1 group by deptno) t3 ---每个部门的最高工资 ：第一名
inner join (select t2.deptno, max(sal)
from emp t2
where (deptno, sal) not in
(select t1.deptno, max(sal) m1
from emp t1
group by deptno)
group by t2.deptno) t4 ---每个部门的第二名
on t3.deptno = t4.deptno
--方法三：
select deptno, max(sal) as zd
from emp t1
group by deptno ---每个部门的最高工资 ：第一名
union
select t2.deptno, max(sal)
from emp t2
where (deptno, sal) not in
(select t1.deptno, max(sal) m1 from emp t1 group by deptno)
group by t2.deptno ---每个部门的第二名
order by deptno, zd desc;
--方法四：
select *
from emp
left join (select emp.deptno, max(sal) ttt
from emp
left join (select deptno, max(sal) maxsal
from emp
group by deptno) maxbydept
on (emp.deptno = maxbydept.deptno)
where emp.sal < maxbydept.maxsal
group by emp.deptno) temp
on emp.deptno = temp.deptno
where emp.sal >= temp.ttt
order by emp.deptno
--9.查询员工表中工资最高的前三名
--方法一
select emp.*
from emp
left join (select max(sal) 第三
from (select *
from emp
left join (select max(sal) 第二
from (select *
from emp
left join (select max(sal) 第一
from emp) onet
on 1 = 1
where emp.sal < onet.第一)) twot
on 1 = 1
where emp.sal < twot.第二)) threet
on 1 = 1
where emp.sal >= threet.第三
order by emp.sal desc;
--方法二
select e1.ename, e1.sal, count(e2.empno)
from emp e1 --员工表
left join (select * from emp e2) e2 ---比表一工资高的人
on e1.sal < e2.sal
group by e1.empno, e1.ename, e1.sal
having count(e2.empno)<=2 ---前3名，--如果比A工资高的人有2个，那么A排名为3
--方法三
select *
from(select * from emp order by sal desc)
where rownum <=3;
--10.在上一题的基础上查询工资第4~6名的员工信息
--方法一：
select e1.ename, e1.sal, count(e2.empno)
from emp e1 --员工表
left join (select * from emp e2) e2 ---比表一工资高的人
on e1.sal < e2.sal
group by e1.empno, e1.ename, e1.sal
having count(e2.empno)>=3 and count(e2.empno)<6; ---前3名，--如果比A工资高的人有2个，那么A排名为3
select e1.empno,e1.ename,e1.job,e1.hiredate,e1.sal,e1.comm,e1.deptno,count(e2.empno)
from emp e1
left join (select empno, sal from emp e2) e2
on e1.sal < e2.sal
group by e1.empno,e1.ename,e1.job,e1.hiredate,e1.sal,e1.comm,e1.deptno,e1.sal
having count(e2.empno) between 3 and 5;
--方法二：
select *
from (select *
from emp
where empno not in (select empno
from (select * from emp order by sal desc)
where rownum <= 3)
order by sal desc)
where rownum <= 3;
--例题：
--1.将emp表和dept表进行内连接 select * from emp t1 inner join dept t2 on t1.deptno = t2.deptno; --2.每个员工后边展示他的部门名称 select t1.*,dname from emp t1 inner join dept t2 on t1.deptno=t2.deptno; select t1.*,(select dname from dept t2 where t2.deptno= t1.deptno) as from emp t1 --3.查询每个员工的编号，姓名，工资，部门编号，部门名称，地点 select empno,ename,sal,t1.deptno,dname,loc from emp t1 inner join dept t2 on t1.deptno=t2.deptno; select * from emp; select * from dept;
--案例：
--查询部门地址在纽约的员工信息及部门信息 select e.*,d.* from emp e inner join dept d on e.deptno = d.deptno where loc = 'NEW YORK';
2.外连接
2.1左外连接 ---左连接
left [outer] join ... on 关联条件 定义：将两张表按照关联条件进行关联，左表作为主表，左表数据全部展示，匹配不成功的数据，右表以空值填充
语法： select 查询的内容 from 表1 left join 表2 on 关联条件 inner join 表3 on 关联条件 where 分组前的条件 group by 分组的内容 having 分组后的条件 order by 排序的内容 asc|desc
表1 left join 表2 on 关联条件 ---left join 前边的表为主表，左表
--案例：
--将emp表和dept表进行左连接 1.--dept作为主表 select * from dept t1 --dept作为主表 left join emp t2 on t1.deptno=t2.deptno; 2.--emp表作为主表 select * from emp t1 left join dept t2 on t1.deptno=t2.deptno; select * from emp for update; --案例： --用左连接查询每个员工的编号，姓名，工资，部门编号，部门名称，地点 select empno,ename,sal,e.deptno,dname,loc from emp e left join dept d on e.deptno = d.deptno;
条件放在 on 和 where 后边的区别总结：
1.on 后边是关联条件，where 后边是过滤条件，对已经关联好的表进行过滤。 2.放在on 后边是，如果是左(右)连接，左（右）表数据全部展示，右（左）表匹配不上的时候以空值填充。 如果是内连接，放在on 和where 后边 没有区别。
----查询部门地址在纽约的员工信息及部门信息 --内连接： select * from emp t1 inner join dept t2 on t1.deptno =t2.deptno and t2.loc='NEW YORK'; --关联条件 select * from emp t1 inner join dept t2 on t1.deptno =t2.deptno where t2.loc='NEW YORK'; ---过滤条件 --左连接： select * from emp t1 left join dept t2 on t1.deptno =t2.deptno and t2.loc='NEW YORK'; --关联条件 select * from emp t1 --主表 left join dept t2 --匹配表 on t1.deptno =t2.deptno and t2.loc='NEW YORK' --关联条件 where t2.deptno is not null ; select * from emp t1 left join dept t2 on t1.deptno =t2.deptno where loc ='NEW YORK'; ---过滤条件
2.2右外连接 ---右链接
右链接 right [outer] join ... on 关联条件
定义：将两张表按照关联条件进行关联，右表作为主表，右表数据全部展示，匹配不成功的数据，左表以空值填充
语法： select 查询的内容 from 表1 right join 表2 on 关联条件 right join 表3 on 关联条件 where 分组前的条件 group by 分组的内容 having 分组后的条件 order by 排序的内容 asc|desc
表1 right join 表2 on 关联条件 ---right join 后边的表为主表，右表
--emp表和dept表进行右连接 select * from emp t1 right join dept t2 ---dept表作为主表 on t1.deptno=t2.deptno; select * from dept t1 right join emp t2 on t1.deptno=t2.deptno; ---emp表作为主表
--综合案例：
--1.要求每个员工后边显示他的部门名称
select emp.*, dept.dname
from emp
left join dept
on emp.deptno = dept.deptno;
select e.*,dname
from emp e
inner join dept d
on e.deptno = d.deptno;
--2.列出所有办事员的姓名及其部门名称
select ename,dname
from emp e
inner join dept d
on e.deptno = d.deptno
where job = 'CLERK';
SELECT ENAME,DNAME
FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO AND JOB = 'CLERK'
WHERE D.DNAME IS NOT NULL;
--3.查询高于自己部门平均工资的员工信息
select *
from emp e1
left join (select deptno,avg(sal) as pingjun from emp group by deptno) e2
on e1.deptno = e2.deptno
where e1.sal > e2.pingjun;
--4.查询相同工资的员工信息
select e1.*
from emp e1
inner join emp e2
on e1.sal = e2.sal
and e1.ename != e2.ename;
select t.*
from emp t
left join (select sal ,empno from emp) t1
on t1.sal = t.sal
where t1.empno<>t.empno;
select e.*
from emp e
left join emp p
on e.sal=p.sal and e.empno<>p.empno
where p.empno is not null;
--2.3全外连接 ---全连接
full join on 关联条件 定义：将两张表按照关联条件关联，两张表的数据全部展示，匹配不成功的数据，对应的表以空值填充。
语法： select 查询的内容 from 表1 full join 表2 on 关联条件 full join 表3 on 关联条件 where 分组前的条件 group by 分组的内容 having 分组后的条件 order by 排序的内容 asc|desc
--内连接 select * from emp t1 inner join dept t2 on t1.deptno=t2.deptno; --左连接 select * from emp t1 --主表 left join dept t2 on t1.deptno=t2.deptno; --右连接 select * from emp t1 right join dept t2 --主表 on t1.deptno=t2.deptno; --全连接 select * from emp t1 full join dept t2 on t1.deptno=t2.deptno;
3.交叉连接 cross join 后边不要关联条件,不要 on
定义：两张表数据交叉相乘，不需要关联条件，等同于笛卡尔积 select * from emp t1 --15 cross join dept t2; ---4 ---连接后为60条数据
select * from emp,dept; --->笛卡尔积 等价于 交叉连接 select * from emp t1,dept t2 where t1.deptno=t2.deptno; ---等价于内连接 select * from emp t1,dept t2 where t1.deptno=t2.deptno(+);---等价于左连接 select * from emp t1,dept t2 where t1.deptno(+)=t2.deptno;---等价于右连接
4.特殊连接
4.1 自然链接 natural join
定义：两张表自动寻找关联字段，将两张表进行关联，只显示匹配成功的数据，去掉重复字段
select * from emp natural join dept; select * from emp natural join (select empno,deptno from emp) t2; select * from emp where comm is not null; select 1 from emp;
4.2 自链接 *没有关键字
定义：是表连接的一种特殊形式，一张表自己和自己连接，没有关键字
select * from emp t1 inner join emp t2 on t1.sal=t2.sal and t1.empno<>t2.empno; --查询员工信息以及其对应的领导姓名 select t1.* ,t1.mgr,(select ename from emp where empno=t1.mgr) from emp t1; select t1.*,t2.ename from emp t1 --员工表 inner join emp t2 --领导表 on t1.mgr= t2.empno; select t1.*,t2.ename from emp t1 --员工表 left join emp t2 --领导表 on t1.mgr= t2.empno;
4.3 不等值连接
定义：也是表连接的一种特殊形式，关联条件里不用等号
--查询员工信息以及对应的工资等级 select * from salgrade; select t1.*,t2.grade from emp t1 inner join salgrade t2 on t1.sal>=t2.losal and t1.sal<=t2.hisal; select t1.*,t2.grade from emp t1 inner join salgrade t2 on t1.sal between t2.losal and t2.hisal ;
--1.各种连接的特点：
--内连接：按照关联条件关联，将两表中都符合关联条件（匹配成功的）的数据展示出来 --左外连接：按照关联条件关联，左表作为主表，将左表所有数据展示出来，右表（匹配表）中不满足关联条件的以空值填充； --右外连接：按照关联条件关联，右表作为主表，将右表所有数据展示出来，左表（匹配表）中不满足关联条件的以空值填充； --全外连接：将两张表按照关联条件关联，两张表的数据全部展示，匹配不成功的数据，对应的表以空值填充 --笛卡尔连接：没有关联条件，左表中的每一条数据都与右表中的每一条数据做了一次关联
--2、常用 1-left join union --集合运算（并集） 2-inner join 3-full join 4-right join 5-cross join 6-natural join
--多表连接综合案例：
--1、列出与"SCOTT" 从事相同工作的所有雇员
select *
from emp t1
inner join (select job from emp where ename = 'SCOTT') t2
on t1.job = t2.job
where ename != 'SCOTT';
--2、列出所有员工的姓名及其直接上级的姓名
select t1.ename, t2.ename
from emp t1 --员工所在的表
inner join emp t2 --领导所在的表
on t1.mgr = t2.empno; ---员工的领导编号==领导的员工编号
select t1.ename, t2.ename
from emp t1 --员工所在的表
left join emp t2 --领导所在的表
on t1.mgr = t2.empno; ---员工的领导编号==领导的员工编号
--3、列出工资高于公司平均水平的所有员工
select *
from emp t1
inner join (select avg(sal) aaa from emp) t2
on t1.sal>t2.aaa;
--4、列出入职日期早于其直接上级的所有雇员
select t1.*
from emp t1
inner join emp t2
on t1.mgr = t2.empno
and t1.hiredate < t2.hiredate;
select t1.*
from emp t1
left join emp t2
on t1.mgr = t2.empno
and t1.hiredate < t2.hiredate
where t2.empno is not null;
select t1.*
from emp t1
left join emp t2
on t1.mgr = t2.empno
where t1.hiredate < t2.hiredate
--5、查询工资大于10号部门平均工资的员工信息
select *
from emp t1 , (select avg(sal) aaa from emp group by deptno having deptno=10) t2
where t1.sal>t2.aaa;
select e1.*
from emp e1
left join (select avg(sal) as pingjun from emp where deptno = 10) e2
on e1.sal > e2.pingjun
where pingjun is not null;
select e1.*
from emp e1
inner join (select avg(sal) as pingjun from emp where deptno = 10) e2
on e1.sal > e2.pingjun;
--6.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
select e1.ename,e1.sal,d.dname
from emp e1
left join (select sal from emp where ename = 'SMITH') e2
on e1.sal > e2.sal
left join dept d
on e1.deptno = d.deptno
where e2.sal is not null;
select t1.ename,t1.sal,t3.dname
from emp t1
inner join emp t2
on t1.sal>t2.sal and t2.ename='SMITH'
inner join dept t3
on t1.deptno=t3.deptno and t3.loc ='CHICAGO';
select t1.ename,t1.sal,t3.dname
from emp t1
inner join (select sal from emp where ename='SMITH') t2
on t1.sal>t2.sal
inner join (select deptno,dname from dept where loc = 'CHICAGO') t3
on t1.deptno=t3.deptno;
--7.查询部门人数大于所有部门平均人数的 部门编号，部门名称，部门人数
select d.deptno,d.dname,e.rs
from dept d
inner join (select deptno,count(1) as rs from emp group by deptno) e
on d.deptno = e.deptno
inner join (select avg(count(1)) as pjrs from emp group by deptno) e2
on e.rs > e2.pjrs
select t2.*, t1.dname
from dept t1
inner join (select deptno, count(1)
from emp
group by deptno
having count(1) > (select count(1) from emp) / (select count(1)
from dept)) t2
on t1.deptno = t2.deptno;
select t2.*, t1.dname
from dept t1
inner join (select deptno, count(1)
from emp
group by deptno
having count(1) > (select avg(count(1)) from emp group by deptno)) t2
on t1.deptno = t2.deptno;
--所有部门平均人数： 总人数/总部门数
select (select count(1) from emp)/
(select count(1) from dept) from dual;
select 15/4 from dual;
select avg(count(1)) from emp group by deptno;
select count(distinct deptno) from emp;--部门数
select count(count(deptno)) from emp group by deptno; --部门数
--8.列出至少有三个员工的所有部门和部门信息。
select *
from dept d
inner join (select deptno,count(1) as rs from emp group by deptno) e
on d.deptno = e.deptno
where e.rs >= 3;
--9.列出职位为“CLERK”的员工姓名和其所在部门名称，部门人数
select ename,dname,e2.rs
from emp e1
full join dept d
on e1.deptno = d.deptno
left join (select deptno,count(1) as rs from emp group by deptno) e2
on d.deptno = e2.deptno
where e1.job = 'CLERK';
select t2.dname,t3.aa
from (select * from emp where job = 'CLERK') t1
inner join dept t2
on t1.deptno = t2.deptno
inner join (select deptno,count(*) aa
from emp
group by deptno
) t3
on t1.deptno = t3.deptno;
数据操作语言
数据的新增 修改 删除
--DQL 数据查询语言 --开窗函数 行列转换 集合运算
DML 数据操作语言 定义：针对数据的处理，经过DML语句对数据进行 增删改
1.数据的新增 insert into 2.数据的修改 update 3.数据的删除 delete
4.同时实现增删改 merge into
--2.查出部门平均工资最少的部门下的员工，并把信息插入到test表里
--先对每个部门进行分组并计算平均工资，然后在这些平均工资中查找最小值。
--各部门中平均工资最小的值：
select min(pjgz) from (select deptno ,avg(sal) as pjgz from emp group by deptno);
select min(avg(sal)) from emp group by deptno ;
--方法一：
select * from emp where deptno=
(select deptno
from emp
group by deptno
having avg(sal) = (select min(pjgz)
from (select deptno, avg(sal) as pjgz
from emp
group by deptno)));
select * from test1;
insert into test1
(ename, deptno)
select t.ename, t.deptno
from emp t
inner join (select deptno, avg(sal)
from emp
group by deptno
having avg(sal) = (select min(n)
from (select deptno, avg(sal) as n
from emp
group by deptno))) t1
on t.deptno = t1.deptno
--方法二：
insert into test1
select t3.*
from (select deptno, avg(sal) ss from emp group by deptno) t1
inner join (select min(avg(sal)) aa from emp group by deptno) t2
on t1.ss = t2.aa
inner join emp t3
on t3.deptno = t1.deptno;
--方法三：
insert into test1
select e1.*
from emp e1
left join (select deptno, avg(sal) as pjgz from emp group by deptno) e2
on e1.deptno = e2.deptno
where pjgz =
(select min(pjgz) as zuixiao
from (select deptno, avg(sal) as pjgz from emp group by deptno))
1.数据的新增 insert into --DML语句执行完后，需要手动提交
语法：
1.1指定列插入
insert into 表名(列1，列2...) values(值1，值2...) --注意：值和列在数量，顺序，数据类型（字段属性）要保持一致
select * from emp;
select * from emp for update; ---查询后的结果可更新
insert into emp(empno,ename,sal,deptno)values(99999,'李四',7777); ---没有足够的值
insert into emp(empno,ename,sal,deptno)values(99999,'李四',7777,30,98); ---值过多
insert into emp(empno,ename,sal,deptno)values(99999,'李四',7777,30); ---值大于为此列指定的精度
insert into emp(empno,ename,sal,deptno)values(9999,'李四',7777,30); ---正常插入
insert into emp(empno,ename,sal,deptno)values('u','李四',7777,30); ---无效数字
1.2全列插入
insert into 表名 values(值1,值2,值3,...值n)--n和列的个数要保持一致。 --注意：值和列在数量，顺序，数据类型（字段属性）要保持一致
insert into emp values(4444,'李四','CLERK',7777,to_date('2024-5-22','yyyy-mm-dd'),9999,9999,10);
insert into emp values(4443,'李四','CLERK',7777,to_date('2024-5-22','yyyy-mm-dd'),null,'',10);
1.3查询插入
select * from emp41; --测试代码： --创建一张临时测试表：（快捷建表） --DDL -数据定义语句：
create table emp41 as select * from emp; create table emp41 as select * from emp where 1=2;---快速复制一张空表 drop table emp41; ---慎用。
语法： insert into 表名 查询语句;---将查询结果插入到表中 insert into 表名(列1，列2) select 列1，列2 from 表名;
--注意：值和列在数量，顺序，数据类型（字段属性）要保持一致
--查询20号部门的员工信息并插入到emp41中
insert into emp41 select * from emp where deptno=20;
--查询30号部门的员工编号，姓名并插入到emp41中
insert into emp41(empno,ename,deptno) select empno,ename,deptno from emp where deptno=30;
--案例：
--1.查出SMITH 领导的信息，并插入到test1 表里
create table test1 as select * from emp where 1=2;
select * from test1;
insert into test1 select * from emp where empno=(select mgr from emp where ename='SMITH');
--3.查询和scott相同岗位的员工姓名ename和雇用日期hiredate，并插入到test表
insert into test1
(ename, hiredate)
select ename, hiredate
from emp
where job = (select job from emp where ename = 'SCOTT') and ename!='SCOTT'
--4.查询在部门表中的 loc 为newYork 的部门工作的员工的员工信息，并插入到test表
insert into test1
select t1.*
from emp t1
left join (select deptno from dept where loc='NEW YORK')t2
on t1.deptno=t2.deptno
where t2.deptno is not null;
insert into test1
select t2.*
from (select deptno from dept where loc = 'NEW YORK') t1
inner join emp t2
on t1.deptno = t2.deptno;
select * from emp
2.数据的修改 update
语法： 1.update 表名 set 列1=新值1，列2=新值2; ---更新表中的数据--整张表的数据 = 赋值;
--将emp表中的sal列更新为10000，comm更新为1； update emp set sal=10000,comm=1;
2.update 表名 set sal =新值1 where 条件; ---按条件更新
--更新SMITH的工资为10000 update emp set sal=10000 where ename='SMITH'; --将20号部门的员工涨薪50% update emp set sal=sal*1.5 where deptno=20;
set 后边的赋值：
1.放具体的值 ：10000 2.可以放计算表达式 sal1.5 3.可以放函数 update emp set sal=length(job)1000 where deptno=10; 4.可以放子查询 （按条件更新时，给一个字段赋值，只能放单行单列子查询）
--将SCOTT的薪资更新给SMITH;
update emp set sal=(select sal from emp where ename='SCOTT') where ename='SMITH'; --按条件更新时，给一个字段赋值，只能放单行单列子查询
update emp set sal=(select sal from emp where deptno=20) where ename='SMITH';
--将WARD的薪资,奖金更新给SMITH;
update emp
set sal =(select sal from emp where ename = 'WARD'),
comm =(select comm from emp where ename = 'WARD')
where ename = 'SMITH'; ----正常执行
update emp
set (sal,comm) =(select sal,comm from emp where ename = 'WARD')
where ename = 'SMITH';----正常执行
update emp set (sal,comm)=(9999,888);--报错：---多个字段赋值，后边只能放子查询
update emp set sal=9999,comm=888; --正常执行
--案例：
--1.将编号为 7788 的雇员的领导取消
update emp set mgr=null where empno=7788;
--2.将SMITH领导的工资加1000
update emp set sal=sal+1000 where empno=(select mgr from emp where ename='SMITH');
--3.将和SMITH同部门的员工工资涨薪60%
update emp set sal=sal+sal*0.6 where deptno=(select deptno from emp where ename='SMITH');
--4.将和SCOTT岗位相同的工资加3000
update emp set sal=sal+3000 where job=(select job from emp where ename='SCOTT');
3.数据的删除 delete
delete --对表中的数据的删除 语法： delete from 表名;--删除整张表的数据
delete from 表名 where 条件;--按条件删除
--4.将和FORD岗位相同的员工删掉 delete from emp where job=(select job from emp where ename='FORD');
--案例：
--1.删除掉有奖金的雇员 delete from emp where comm is not null; --2.删除部门20的员工 delete from emp where deptno=20; --3.将SMITH的领导信息删掉 delete from emp where empno=(select mgr from emp where ename='SMITH');
4.同时实现增删改 merge into
merge into 表1 t1 --目标表 using 表2 t2 --数据源表 on (关联条件)--主键列 或者 唯一列 when matched then ---匹配成功时要做的事情 update set t1.列1=值1, t1.列2=t2.列 where 条件 delete where 条件----此时要满足以上所有条件 when not matched then ---匹配不成功时要做的事情 insert (t1.列1,t1.列2) values( t2.列，t2.列2);
select * from emp41; --目标表：emp41 数据源表：emp merge into emp41 t1 using emp t2 on(t1.empno=t2.empno) when matched then update set sal=777 where deptno=20 delete where ename='WARD' or ename='SMITH' ---WARD 还在，没有被删除，因为其是30号部门，不满足update后边的条件 when not matched then insert (t1.empno,t1.ename,t1.job)values (t2.empno,t2.ename,t2.job); select * from emp; --用表a更新表b:参照表a更新表b的价格，如果表a中的数据在表b中不存在，则将数据插入表b中 select * from t_bb; --目标表 select * from t_aa; merge into T_BB bb using T_AA aa on (bb.ID=aa.ID) when matched then update set bb.price=aa.price when not matched then insert(bb.id,bb.type,bb.price) values(aa.id,aa.type,aa.price); --用表a更新表b:参照表a更新表b的价格，如果表a中的数据在表b中不存在，则将数据插入表b中 select * from t_bb; --目标表 select * from t_aa;--数据源 merge into t_bb b using t_aa a on (b.id = a.id) when matched then update set b.price = a.price when not matched then insert (b.id,b.type,b.price) values (a.id,a.type,a.price); merge into t_bb t1 --目标表 using t_aa t2 --数据源表 on (t1.id=t2.id) --主键列 或者 唯一列 when matched then ---匹配成功时要做的事情 update set t1.price=t2.price where t1.id=t2.id when not matched then ---匹配不成功时要做的事情 insert (t1.id,t1.type,t1.price) values(t2.id,t2.type,t2.price);
DDL DCL TCL 约束
DQL - 数据查询语句 数据的查询
select distinct *|列|函数|常量|计算表达式 from 表名 inner|left join 表2 on 关联条件 where 条件 group by 分组的内容 having 分组后的条件 order by 排序的内容 asc|desc
执行顺序： from- on -join - where - group by - having - select -distinct - order by
--面试题523-1
select * from A1;
--1
--不相关子查询：
select std_id, club_id
from A1
where std_id in
(select std_id from A1 t1 group by std_id having count(*) < 2);
--相关子查询：
select std_id,(select club_id from A1 where A1.std_id =t1.std_id)
from A1 t1
group by std_id
having count(*) < 2;
--多表连接-内连接实现
select t1.std_id,t1.club_id
from A1 t1
inner join (select std_id
from A1
group by std_id
having count(*) < 2) t2
on t1.std_id =t2.std_id;
select * from club;
--2
select * from club where std_id in(select std_id
from club t1
group by std_id
having count(std_id) > 1) and main_club_flg = 'Y';
select sport.*
from sport
left join (select std_id from sport t1 group by std_id having count(1) > 1) t1
on sport.std_id = t1.std_id and sport.main_club_flg = 'Y'
where t1.std_id is not null
select s1.std_id,s1.club_id
from SSS s1
inner join (select std_id ,count(1) from SSS group by std_id having count(1)<>1)s2
on s1.std_id=s2.std_id and main_club_flg='Y';
DML - 数据操纵语句 数据的 增 删 改
insert into 表名 (列 ...) values (值); insert into 表名 select 查询语句;
delete from 表名 where 条件;
update 表名 set 列 = 值;
merge into 目标表 using 数据源表 on(关联条件) when matched then update delete when not matched then insert
DDL---数据定义语言
定义：针对数据库对象的操作 --数据库对象：表、用户、视图、约束、索引、存储过程、函数、 序列、包，角色
--表：DDL
-
create --创建一个数据库对象
-
drop --删除数据库对象
-
alter -- 修改数据库对象
-
truncate --清空表数据 --不能跟where 条件 ---DDL delete --删除表中的数据 可以跟where 条件 ---DML
-
create --创建一个数据库对象 --创建表语法： create table 表名 ( 列名1(字段名) 字段属性, 列名2(字段名) 字段属性, 列名3(字段名) 字段属性, ...
列名n(字段名) 字段属性 )
Oracle建表时表名命名规则：
①必须以字母开始 （切记不可以数字开始） ②长度不能超过30个字符 ③只能包括A-Z，a-z，0-9，_，$和# ④不能在相同用户下建立两个同名的对象 ⑤不能使用保留字和关键字
--表名和列名 命名规范： 1.必须以字母开头 a_1 A 2.长度不能超过30个字符 3.同一个用户下，表名不能重复，用一张表下，列名不能重复 4.不建议使用中文 5.不建议使用下划线以外的其他特殊字符 6.不建议和oracle关键字重复
create table class41 ( sno number(9), sname varchar(30), age number(8) )
select * from class41; --注意：DDL 语句 执行成功即生效 不需要手动提交
--快捷建表 --复制一张表 create table 表名 as sql查询 --把sql语句查询结果创建成一张新表
--快捷建表时，必须使用表中原有的字段，如果一个字段在表中没有，要定义一个别名
--创建一张表，展示员工编号，姓名，部门编号，年薪 create table test41 as select empno,ename,deptno,sal*12 as 年薪 from emp;
select * from test41;
--案例：
--快速建一个空表
create table 表名 as select * from 表名(参考表) where 1=2;
--快捷建表：要求展示员工姓名 岗位 薪资 部门编号 以及对应的部门名称 对应部门的平均工资
--多表连接查询：
create table TTTT as
select ename, job, sal, emp.deptno, dept.dname, deptavg.avgsal
from emp
left join (select deptno, avg(sal) avgsal from emp group by deptno) deptavg
on emp.deptno = deptavg.deptno
left join dept
on dept.deptno = emp.deptno
create table test41 as
select t1.ename,t1.job,t1.sal,t1.deptno ,t2.dname,pjgz
from emp t1
left join dept t2
on t1.deptno=t2.deptno
left join (select avg(sal) as pjgz,deptno from emp group by deptno)t3
on t1.deptno=t3.deptno;
create table tese2 as
select n1.*,n2.nsal
from (select ename, job, sal, t1.deptno, t2.dname
from emp t1
left join dept t2
on t1.deptno = t2.deptno) n1
left join (select deptno, avg(sal) as nsal from emp group by deptno) n2
on n1.deptno = n2.deptno ;
--相关子查询：
create table test4444 as
select ename,
job,
t.deptno,
(select dname from dept where deptno = t.deptno) as 部门名称,
(select avg(sal) from emp where deptno = t.deptno) as pjgz
from emp t;
select avg(sal) from emp where deptno=20;
--字段属性： 每一个字段都有自己的属性，该属性决定了该字段中存入的数据类型。
1.字符型字段属性
1.1. char(n):固定长度的字符串，该字段属性下面只能存放字符型数据，n代表长度，存入的数据总长度不能超过n 存入的数据长度不足n，则在右侧填充空格使其长度达到n，最终以n个长度存储。 默认长度 2000 1.2 varchar2(n):可变长度字符串，该字段属性下面只能存放字符型数据，n代表长度，存入的数据总长度不能超过n 存入的数据长度不足n，则以实际长度存储。 默认长度 4000
--在oracle中： --varchar是标准sql里面的；varchar2是oracle提供的独有的数据类型。 --varchar对于汉字占两个字节，对于数字，英文字符是一个字节，占的内存小； --varchar2一般情况下把所有字符都占两字节处理。具体要看数据库使用的字符集， --比如GBK，汉字就会占两个字节，英文1个， --如果是UTF-8，汉字一般占3个字节，英文还是1个。 --varchar2是存放可变长度的字符串，最大长度是4000。 --mysql，那么就用varchar；如果就用oracle，那么用varchar2比较好一点。
select userenv('language') from dual;
select lengthb('张三') from dual;---返回参数的字节长度 select length('张三') from dual; select lengthb('abc') from dual;
byte 字节 bit 比特
1byte = 8 bit; 1KB = 1024 byte; 1MB =1024 KB; 1GB = 1024MB; 1TB = 1024GB;
2.数值型字段属性：
number(p,s) :常见的数值属性，该字段只能存放数值型数据，p代表总长度（包含小数位），s代表的精度，p和s 都可以省略， 都不写， 默认长度是 38 位 number（7,2） 123--123.00 1234567.00---长度超过 7 12345.678----12345.68 int: 只存整数
3.日期型字段属性
date：日期属性，存放日期型数据，包含了7个单位 世纪 年月日 时分秒 不一定都要显示 time：时间戳，比date多出 一个 毫秒部分 -----写日志、导数据用
-
drop --删除数据库对象 --删除表语法： drop table 表名;--表结构和表数据一并删除， drop--在工作中谨慎使用。 ---DDL truncate table 表名; --清空表数据 --不能跟where 条件 ---DDL
delete --删除表中的数据 可以跟where 条件 ---DML
--truncate、drop 和 delete 的区别主要有以下几点：
1、执行速度：drop > truncate > delete。 2、delete 和 truncate 只删除表数据，而 drop 会删除表数据和表结构以及表的索引、约束和触发器。 3、delete 可以加 where 条件实现部分数据删除，而 truncate 和 drop 不能加 where 条件是整体删除。 4、truncate 和 drop 是立即执行，且不能恢复；而 delete 会走事务，可以撤回（回滚）。 5、truncate 和 drop 是 DDL 语句，而 delete 是 DML 语句。 ***
-
alter -- 修改数据库对象 alter 表 修改 表名 列名 增加 字段（列） 删除 字段 修改 字段属性
1.修改 表名 列名 alter table 旧表名 rename to 新表名;----修改表名 alter table 表名 rename column 旧列名 to 新列名; ----修改列名 --注意：在工作中尽量不要去修改表名和列名
2.增加 字段 alter table 表名 add(列1 字段属性,列2 字段属性2...);
alter table class41 add( birthday date); select * from class41;
3.删除 字段
alter table 表名 drop (列1，列2 ...);
alter table class41 drop (age,birthday); alter table class41 drop (sno,sname);---提示：无法删除全部列
注意：
1.删除现有字段 2.删除多个字段时，不能换删除多有字段，至少要保留一个
4.修改 字段属性
alter table 表名 modify(列1 新字段属性);
alter table class41 modify(sno number(7)); ---number 要减小精度，该列必须为空 insert into class41 values(888888,'zs'); insert into class41 values(888888,'zs'); alter table class41 modify(sname varchar2(1)); ---varchar2提示：无法减小列的长度，因为一些值过大 alter table class41 modify(sname number(6)); --要修改数据类型，该列必须为空 select * from class41;
--注意点：
4.1修改字段属性 数据类型和精度时，不用写明原来的字段数据类型和精度 4.2要修改数据类型，要修改的列必须为空 4.3要修改字段属性精度时，一般只会扩充，不会缩减，若真有必要缩减，最短只能缩减到现有的数据长度，不能小于表中现有数据长度。number 要减小精度，该列必须为空。
DCL --数据控制语言
----在sql语言中，是一种可对数据访问权进行控制的指令， --它可以控制特定用户对数据表，查看表，存储过程，函数等数据库对象的控制权
grant to ---赋权，授权 revoke from --收回权限
--用户
user 用户：用来登录数据库的账号 system--管理员用户 scott --普通用户
--新建用户：
create user 用户名 identified by 密码; 1.用户名不区分大小写，密码区分大小写 2.用户创建完，仅仅是创建一个账号，该账号没有任何权限，登录的权限 3.用户和用户之间不能直接访问，只有授权后才可以访问 4.管理员用户可以直接访问其他用户
create user uname41 identified by 123456;
--给用户赋权
grant 权限 to 用户;
grant create session to uname41; （如果不赋予create session权限会报ORA-01045:.....错误) create table utest (sno number, sname varchar2(10) ); grant create table to uname41;（如果不赋予 create table权限会报ORA-01031:....错误) select * from scott.emp; grant select any table to uname41;（如果不赋予select any table权限会报ORA-00942:....错误)
--角色
role:--带有一系列权限的集合 作用：给用户赋予一个角色，相当于 赋予该角色下的所有权限 --分类： --系统角色： DBA 管理员角色 --自定义角色：自己创建角色
1.创建角色：
create role 角色名; create role urole;
2.给角色赋权：
grant 权限 to 角色;
grant create user to urole; grant create table to urole; grant select any table to urole;
3.将角色赋权给用户：
grant 角色 to 用户; grant urole to 新用户; grant DBA to scott;
总结:
grant 权限|角色 to 角色|用户; revoke 权限|角色 from 角色|用户;
--修改用户密码： alter user 用户名 identified by 新密码; --删除用户： drop user 用户名;
--锁定 和解锁用户 alter user 用户名 account lock; ---锁定 alter user 用户名 account unlock;--解锁
TCL 事务控制语言
事务：为了完成某些业务，由一些看的见的sql 或者看不见的后台程序组成的一系列的逻辑单元。
事务的四个属性（ACID）(面试) 1.原子性：事务是一个不可分割的整体 2.一致性：所有的数据保持状态一致 3.隔离性：当前事务在修改数据时，其他事务只能看到修改之前的样子 4.持久性：当事务提交成功后，数据永久保存，直到下一个事务来改变它
commit;---提交 rollback;--回滚
显示提交：由commit 决定当前事务是否提交 针对 DML 语言; 隐式提交：一旦事务执行成功，会自动提交 比如：DDL 语言;
约束
--其他数据库对象：约束、 视图、索引、序列、 存储过程、函数
--约束：constraint 定义：强加在表中的规则或者条件 作用：让表中的数据更加符合实际业务要求，如果录入不符合的数据，则拒绝录入
主键约束：empno 数据特点：非空且唯一
1.约束的分类 1.按照约束效果分类： 1.1主键约束 primary key 作用：字段中不能出现重复值，也不能出现空值
1.2唯一约束 unique
作用：字段中不能出现重复值
1.3外键约束 foreign key
作用：字段中的取值来源于另一张表的某个字段 例子：emp表中的deptno的取值来源于dept表中deptno
select * from emp for update;
1.4检查约束 check
作用：自定义约束内容 类似 where 后边的条件
1.5 非空约束 not null
作用：字段中不能出现空值
1.6 默认值 default
作用：字段中不插入数据时，系统会自动填入默认值
2.按照约束创建的时候，是否和字段一起声明分类：
2.1 行级约束
在建表的时候，声明字段及字段属性时，紧随其后去声明的约束
create table 表名 ( 列 字段属性1 约束1， 列2 字段属性2 约束2， .... )
2.2 表级约束
在建表的时候，所有字段及字段属性声明结束后，在单独的去声明的约束 create table 表名 ( 列 字段属性1 ， 列2 字段属性2 ， .... 约束 ---表级约束 )
---创建约束： 》》》》》》》》》》》》》》》》》》》》》》》》》 1.1主键约束 primary key 作用：字段中不能出现重复值，也不能出现空值
1.2唯一约束 unique 作用：字段中不能出现重复值
（一） （1）表已经存在，字段也存在，添加主键|唯一约束 语法： alter table 表名 add constraint 约束名 约束类型(列);
select * from class41; alter table class41 add constraint PK_41sno primary key(sno); insert into class41 values(888889,'tt'); insert into class41 values(null,'tt'); alter table class41 add constraint PK_41sname primary key(sname); -->提示：表只能有一个主键 alter table class41 add constraint PK_41sno primary key(sno,sname); -->可以在多个列创建组合主键
（2）表已经存在，字段不存在，新增字段，添加主键|.唯一约束
alter table 表名 add (age number(10) unique); alter table class41 add (age number(10) unique); ---系统自动生成约束名 alter table class41 add (heigth number(10) constraint UN_heig unique); --自定义约束名
（二）建表同时建立主键|唯一约束
2.1行级约束 create table stu41 (sno number primary key,--行级约束 sname varchar2(18) unique, sage number ) create table stu412 (sno number constraint PK_412 primary key,--行级约束 sname varchar2(18) unique, sage number ) 2.2 create table stu41 (sno number, sname varchar2(18) unique, sage number, primary key(sno,sname)--表级主键约束 ) create table stu412 (sno number , sname varchar2(18) unique, sage number, constraint PK_412 primary key(sno)--表级约束 )
--注意：
1.唯一约束，约束的非空值，约束不了空值，空值不受限制，可存在多个 2.建立唯一约束的字段上会自动建立一个同名的唯一索引 3.一张表中只能有一个主键约束 4.建立主键约束的字段上会自动建立一个同名的唯一索引 5.主键约束字段中不能出现重复值，也不能出现空值
1.3外键约束 foreign key
作用：字段中的取值来源于另一张表的某个字段 例子：emp表中的deptno的取值来源于dept表中deptno
1.3.1 alter table 表名 add constraint 约束名 foreign key(列) references 父表名(列);---父表中的主键列或者唯一约束列
表已存在，建立外键约束 alter table emp add constraint FK_dept foreign key(deptno) references dept(deptno); 表已存在，新增字段时建立外键约束 alter table stu41 add (cno number(4) constraint FK_test41 references class41(sno)); select * from stu41 select * from class41; 建表同时建立外键约束 --行级约束 create table class41n ( sno number(3) constraint FK_test41n references class41(sno), id number(18) references class41(age) ) select * from class41 for update; --表级约束 create table class41n1 ( sno number(3) , id number(18) , sname varchar2(10), constraint FK_test41n1 foreign key (sno) references class41 (zno) ---表级约束 )
--外键约束注意点：
1.外键约束也是针对的非空数据 2.父表中的列必须是主键列或唯一列 3.子表中若存在父表中不存在的数据，建立约束会失败 4.父表中已经存在子表中的内容，想要删除或更改，必须要删除或更改子表的内容 delete from dept where deptno=30;
1.4检查约束 check
作用：自定义约束内容 类似 where 条件 1.4.1 建表之后建立检查约束 alter table 表名 add constraint 约束名 check（约束条件）;
select * from class41; --表存在，字段也存在，添加检查约束 alter table class41 add constraint ck_test41 check (age >=18 and age<=60);**** insert into class41 values(20015,8884,59,null); --表存在，字段不存在，新增字段时同时添加检查约束 alter table class41 add (sg number(5) constraint ck_sg41 check (sg between 160 and 180));***
1.4.1 建表同时 建立检查约束 --行级约束 create table 表名 (列1 字段属性1 check(约束条件), --行级约束中，该条件只能用当前列 列2 字段属性2, 列3 字段属性3 ) --表级约束
create table 表名 (列1 字段属性1 , 列2 字段属性2, 列3 字段属性3, check(约束条件) --表级约束中 )
检查约束： 1.条件中不能放子查询 2.行级约束只针对当前列，不能用其他列作为条件，表级约束不受限制 3.检查约束针对非空数据
1.5 非空约束 not null
作用：字段中不能出现空值 1.5.1 表已经存在
alter table 表名 modify 列名 not null;
1.5.2 建表时添加 --只有行级约束 create table 表名 ( 列1 字段属性1 not null, 列2 字段属性2, 列3 字段属性3 )
1.6 默认值 default
作用：字段中不插入数据时，系统会自动填入默认值
1.6.1 表已经存在 alter table 表名 modify 列名 default 值;
1.6.2 建表时添加 --只有行级约束 create table 表名 ( 列1 字段属性1 default 值, 列2 字段属性2, 列3 字段属性3 ) select * from class41; alter table class41 modify sg default 165;
insert into class41 values(2005,8886,null,32,null); insert into class41(sno,sname,age) values(2004,8884,30);
insert into class41 values(2003,8884,null,26,170);
--删除约束：
alter table 表名 drop constraint 约束名;(主键，唯一，外键，检查)
alter table 表名 modify 列 null; ---非空约束的删除 alter table 表名 modify default null; ---默认值约束的删除
--修改约束名
alter table 表名 rename constraint 旧约束名 to 新约束名;
--禁用约束
alter table 表名 disable constraint 约束名;
--激活约束
alter table 表名 enable constraint 约束名;
--总结：
1-约束会影响DML操作，所以约束并不是越多越好 2-当快捷建表时，除了非空约束，其他的约束都会忽略 3-同一个用户下不能出现重复的约束名 4-表删除的时候，表下面的约束也会一并删除 5-约束也可以在建表完成后，单独的去创建 6-一个约束 可以建立在一个字段上，也可以建立在多个字段上
索引 视图 序列
--伪列
-
rownum 虚拟列 --分页查询 定义：是针对查询结果，自动生成的一个从1开始的自然数序号
select * from emp where rownum =1; select * from (select * from emp order by sal desc) where rownum=1;
-
rowid 伪列 定义：实际存在的，标识一条数据物理位置的18位字符串，除非特意查询才能查到 select t.*,rowid from emp t;
-
rownum 虚拟列 --分页查询 定义：是针对查询结果，自动生成的一个从1开始的自然数序号
1.1 查询员工表中工资最高的员工信息
--子查询实现：
select * from emp where sal=(select max(sal) from emp);
select * from (select * from emp order by sal desc) where rownum=1;
1.2 查询员工表中工资最高的前3名员工信息
select * from (select * from emp order by sal desc) where rownum<=3;
1.3 查询员工表中的前3名员工信息
select * from emp where rownum<=3;
1.4 查询员工表中工资降序排序在第5到第9的员工信息
select * from (select * from emp order by sal desc) where rownum<=9;
select * from (select * from emp order by sal desc) where rownum>=5 ---rownum 取不到>= 需要将rownum实例化，变成实际的列
select * from (select t.* ,rownum as sn from (select * from emp order by sal desc) t) where sn>=5 and sn<=9;
select *
from (select *
from (select * from emp order by sal desc)
where rownum <= 9
order by sal)
where rownum <= 5 order by sal desc;
select *
from (select *
from emp
where empno not in (select empno
from (select * from emp order by sal desc)
where rownum <= 4)
order by sal desc)
where rownum <= 5;
--rownum 对结果集加的一个序列号，先查询出结果，然后再加上去的一列虚拟号，也就是说rownum 总是从1开始。 --rownum不能用大于等于，也不能单独用等于 除了1以外的数， 只能用=1； 或者用小于等于。
-
rowid 伪列 定义：实际存在的，标识一条数据物理位置的18位字符串，除非特意查询才能查到
select t.*,rowid from emp t;
select t.*,rowid from emp t where deptno=30;
WARD --》AAAYKuAAEAAAAisAAE
--->AAAYKuAAEAAAAisAAE
select t.*,rowid from emp t where rowid='AAAYKuAAEAAAAisAAE';
select * from emp41;
insert into emp41 select * from emp;
select t.*,rowid from emp41 t;
--去重：
1.在查询结果中去重：
select distinct * from emp41;
select t.empno,
t.ename,
t.job,
t.mgr,
t.hiredate,
t.sal,
t.comm,
t.deptno from emp41 t group by t.empno,
t.ename,
t.job,
t.mgr,
t.hiredate,
t.sal,
t.comm,
t.deptno;
select * from emp41 t group by t.empno,
t.ename,
t.job,
t.mgr,
t.hiredate,
t.sal,
t.comm,
t.deptno;
select * from emp41 where rowid in(
select max(rowid) from emp41 group by empno);
2.删除重复数据，只保留一条
delete from emp41 where rowid not in(
select max(rowid) from emp41 group by empno);
select * from emp41;
select t.*,row_number()over(partition by empno order by empno) from emp41 t;
select t.*,avg(sal)over(partition by deptno) from emp t;
--索引 index
索引是什么： (1)一种供服务器在表中快速查找的一行的 数据库结构 (2) 可以理解成：一本书的目录
定义：指向表中的数据，通过建立索引，提高我们的查询效率，依赖于表，添加在列（一般会添加在唯一列上）
--oracle 自动使用和维护索引
--全盘扫描
--全盘扫描： 按照一定的条件 在对应的数据列，从第一条数据开始检索到最后一条数据，一旦条件在某数据上成立， --将其rowid取出，然后按照rowid取出整行数据
--索引扫描： 按照一定的条件 在对应的索引列，从大概位置开始检索到最后一条符合条件的数据，一旦条件在某数据上成立， --将其rowid取出，然后按照rowid取出整行数据
select * from emp where deptno=10; select * from emp where empno=7782;
--注意点：
1-没有索引的时候，查询数据会是全表扫描 2-建立索引后，只要查询到索引数据，就会停止查询
创建索引： 语法： create [unique|bitmap] index 索引名 on 表名(列1，列2...);
--索引的分类： --1.按照存储形式分类 1.B-tree 索引 ---平衡树 1.1默认的普通索引 B-tree (索引列原始数据+rowid) 1.2反向键索引 B-tree索引的一种特殊形式 （索引列原始数据反向存储+rowid） 1.3 基于函数的索引 B-tree 索引的一种特殊形式（索引列原始数据经过函数处理+rowid）
2.位图索引 bitmap (位图+rowid)
--2.按照唯一性分类 --唯一索引 unique --索引列中的数据不能重复的 create unique index 索引名 on 表名(列1，列2...); --非唯一索引 normal
--3.按照列的个数分类 --单列索引 --多列索引（复合索引）
1.B-tree 索引
使用场景： 列基数比较大的列 列基数： 该字段不重复的个数 --唯一列： 学号、订单号 、身份证号
1.1默认的普通索引 B-tree (索引列原始数据+rowid) create index 索引名 on 表名(列1，列2...);
1.2反向键索引 B-tree索引的一种特殊形式 （索引列原始数据反向存储+rowid） create index 索引名 on 表名(列1，列2...) reverse;
1.3 基于函数的索引 B-tree 索引的一种特殊形式（索引列原始数据经过函数处理+rowid）
create index 索引名 on 表名(函数（列1），列2...); create index ID_comm on emp (nvl(comm,0));
2.位图索引 bitmap (位图+rowid)
create bitmap index 索引名 on 表名(列1，列2...);
使用场景： 列基数比较小的列 列基数： 该字段不重复的个数 --性别 婚姻
---索引的优点
(1) 加快数据的 '检索速度' (2) 可以保证列值的 '唯一性'（unique、主键） (3) 实现表与表之间的 '参照完整性'（外键） (4) 在使用 order by、group by 子句时，可以减少排序和分组的时间
（三）. 索引的缺点 (1) 当对表数据进行 DML 操作的时候，索引自动维护，'降低 DML 操作的速度' (2) 索引需要占 '物理空间'，同数据表的 '表空间' tablespace 一样 (3) 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加 （四）. 索引的使用原则 (1) '主键' 和 '外键' 会自动创建索引，无需人工操作 (2) 选择正确的表：经常检索包含大量数据的表中小于 15% 的行 (3) 选择正确的列：多个表之间的关联关系列 (4) 合理安排索引列：（A，B，C） A 最常用，B 次之，C 最末 A建立索引
--面试常问：
--索引的缺点 --1-索引并不一定加快所有表的查询速度，尤其对于比较小的表 --2-索引是用空间换时间 来实现的快速查询，因此会影响 DML的效率，因为操作后索引也需要同步更新
-- 注意sql优化：
--1.如果对某大表进行筛选时，某列或某几列频繁出现在WHERE子句中，并且检索出的数据低于总行数的15% ，应考虑在这些列上建立索引。 --2.如果对某大表进行排序时，某列或某几列频繁出现在ORDER BY 子句中，应考虑在这些列上建立索引。 --3.小表不建议建立索引。 --4.对于含有空值的列，如果经常在查询时查询非空值，建议在该列上建立索引； ---如果经常在查询时查询空值，建议在该列上建立基于函数的索引。 --5.为了提高表连接的性能，应在连接列上建立索引(建立一般普通的索引即可) --6.索引是数据库的一种实体对象，级别类似于表，会占用内存空间，ORACLE会自动进行索引维护和使用，表和索引可以建立在不同的表空间。 --7.通过索引可以提升数据的查询速度，但是会相对地降低DML语句的操作速度，尤其是插和改的速度， --ORACLE会花费时间在索引维护上，所以说要把握好索引的数量 --8.对于列基数比较大的列，适合B-TREE索引，列基数比较小的列，适合位图索引。 --9.某列可以出现在多个索引中，但相同的某列或某几列无法多次建立索引。 --(就是可以有多个组合，但不能有重复组合，不同顺序的相同几列视为不同组合) （empno,ename） （empno,ename,deptno） （ename，empno） empno ename --10.索引建立后并不一定会被引用，ORACLE 会分析整个SQL后做出最优的执行方式。 --11.ORACLE会自动在主键约束和唯一约束列上建立唯一索引。
--索引失效的情况 --1.对于一般的B-TREE索引，通配符出现在搜索词的首位时不会引用索引 ename like'%M%' --2.在索引列上使用<> !=号时，或对空值进行判断时，索引不会生效 ename<>'张三' ename is not null --3.使用索引不能使用 or 连接，否则索引会失效 ename='张三' or ename='李四' --4.查询条件有多个列的时候务必要索引列开头 deptno=10 and job=''; ---deptno列上有索引，则deptno要放在条件的开头
--修改索引名 alter index 旧索引名 rename to 新索引名; --禁用索引 alter index 索引名 unusable; --激活索引 alter index 索引名 rebuild; all_indexes;--索引表 select * from all_indexes where table_name='CLASS41';
视图 view
定义：视图是将sql查询结果 动态的保存在数据库中，作为一个临时的虚拟表，不占内存空间 创建视图基于的表 叫做基表 作用：在不依赖数据库基表的前提下，将需要的数据通过多个数据库基表联合查询后，得到目标数据集合，创建虚拟表。
--创建视图的语法： create view 视图名 as select 查询; create [or replace ]view 视图名 as select 查询 [with read only]; ---只读视图 --创建一个视图包含员工信息表中20号部门的员工编号，姓名，工资，部门编号 create view V_41 as select empno,ename,sal,deptno from emp where deptno=20; create or replace view V_41 as select empno,ename,sal,deptno from emp where deptno=20; select * from V_41; --通过视图插入数据： insert into v_41 values(9999,'ZS',80000,10); select * from emp; --正常插入数据，数据插入在基表中。 --删除视图中的数据 delete from v_41 where empno=7369;---通过视图删除的数据，基表中的数据也会被删除。 delete from v_41 where empno=9999; --修改视图中的数据 update v_41 set sal=10; ---修改视图的数据，基表中的数据也会被修改。 --*****通过基表创建的视图，可以通过增删改视图来达到对基表的增删改*****
--案例：
--创建视图 包含部门名称，工作地点，员工的小写名字，工资，工资和奖金的合计
create or replace view V_t1 as
select (select dname from dept d1 where d1.deptno=e.deptno) as dname,
(select loc from dept d2 where d2.deptno=e.deptno) as loc,
lower(ename) as ename,
sal,
sal + nvl(comm,0) as sc
from emp e;
create or replace view V_t1 as
select dname, --as dname,
loc, --as loc,
lower(e.ename) as ename,
e.sal,
e.sal + nvl(e.comm,0) as sc
from emp e
left join dept d
on e.deptno=d.deptno;
--案例：
--在上个案例题的基础(视图上)，创建一个视图，包含部门名称，及各部门的薪资合计
create view vv_t2 as
select dname, sum(sal) sumsal from V_t1 group by dname
select * from v_t1;
select * from vv_t2; ACCOUNTING 88750
update v_t1 set sal=9999 where ename='clark';
select * from emp;
update v_t1 set lower(ename)='NEWSMITH' where sal=800; --报错：此处不允许修改虚拟列。--更新视图时，不能修改虚拟列。
update vv_t2 set dname='ACCT' where sumsal=96299; ----报错：不允许该操作
update v_t1 set loc='NEW YORK1' where dname='ACCOUNTING' and ename='clark';
--基于视图创建的新视图，原始图中的数据发生变化，也就是修改了基表中的数据， 所以也会引起新视图中的数据变化。
--注意点：
1.视图基于的表叫做基表 2.视图可以像普通表一样去查询 3.视图本身不占用存储空间，它是动态获取的查询结果 4.对于基表中没有的列，定义视图的时候必须要加列别名 5.基于视图创建的新视图，视图的基表数据发生变化，也会引起新视图的变化
--修改视图 内容 create [or replace ]view 视图名 as select 查询; --修改视图名 rename 旧视图名 to 新视图名; --删除视图 drop view 视图名;
序列
sequence 定义： 是oracel 提供的一组能自动增长的序列号; 序列（sequence） 是序列号生成器，可以为 表中的行自动生成序列号，产生一组等间隔的数值。 不占用磁盘空间，占用内存。
作用：主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检索当前值，或者使序列增至下一个值。
--使用场景： 在设计表的时候 需要一个不用明确意义的列来作为主键，这个时候可以用序列。 序列可以是升序也可以是降序。
--创建语法:
create sequence 序列名 start with n--定义序列的初始值 默认为1 select * from emp; create sequence sq41 start with 100; --注意：此时仅仅是创建了一个序列，它还没有任何值， select sq41.nextval from dual; --- 序列的下一个值 使用序列第一次使用时，必须要先执行一次 nextval 取到它的初始值。 select sq41.currval from dual; ---序列当前值 insert into emp41 (empno,ename) values(sq41.nextval,'李四'); select * from emp41;
--完整语法：
create sequence 序列名 start with n--定义序列的初始值 默认为1 increment by n --增长幅度 默认1 如果设置为正数 递增 如果设置为负数 递减 maxvalue n|[nomaxvalue] ---默认最大值为 10的27次方 对于递减序列 最大值 -1 minvalue n|[nominvalue] ---默认最小值为 -10的27次方 对于递增序列 最小值 1 cycle | [nocycle] ---循环|不循环 cache n| [nocache] --缓存 n |没有缓存 默认20 --设置缓存时，缓存值必须小于 cycle值 cache<= ceil((maxvalue-minvalue)/abs(increment)) create sequence sq411 start with 2 increment by 2 maxvalue 57 cycle; select sq411.nextval from dual; ---使用序列第一次使用时，必须要先执行一次 nextval 取到它的初始值。 select sq411.currval from dual; --没有最小值的循坏序列，执行到最大值后，再次取值，会取到1；有最小值时，循环后，从最小值开始 select * from emp;
---注意：
1-第一次使用序列时，必须先使用下一个值nextval，第一次使用nextval，结果是初始值 2-一次nextval 会增加一个序列的值，所以在同一个事务中，使用多个nextval，其值是不一样的 insert into emp41(empno,ename) values(sq411.nextval ,'uuu'); insert into emp41(empno,ename) values(sq411.nextval ,'uuu'); select * from emp41; 3-无法修改序列的初始值，只能删除重建序列，或者设置一个新的增长幅度，通过执行nextval 执行到想要的初始值 4-序列的初始值不能小于最小值 5-创建一个循环序列，必须要设置最大值 6-如果创建带缓存的序列，缓存值要满足以下公式：cache <=ceil(maxvalue -minvalue)/abs(increment) 7-有循环时，在循环后，序列是从1开始循环，如果有最小值，会从最小值开始循环。
--修改序列
--修改内容 alter sequence sq411 increment by 1; --修改序列名 rename 旧序列名 to 序列名; --删除序列 drop sequence 序列名;
--注释 comment --对表或者每一列添加一个说明
--创建注释
--列注释 comment on column 表名.列名 is '注释的内容'; comment on column class41.sno is '学号'; --表注释 comment on table 表名 is '注释的内容'; comment on table class41 is '41班'; --删除列注释 comment on column class31.sno is ''; comment on table class41 is '';
扫一扫
2639
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
