隐性转换 oracle 默认的将数值转换成字符尽量避免出现隐形转换子查询定义 是指 一条语句 嵌套 一条或者多条查询语句---1. 把 外层的查询叫做 主查询 里层的查询叫做 子 查询-- 2 里层的子查询 必须加上括号 select (select )子查询的分类1. 相关子查询 是指里层子查询与外层的主查询部分内容相关里层的子查询 不能单独运行因为会牵扯主查询部分内容主查询 在意的是 与子查询的----------关系2. 非相关子查询 是指里层的子查询与外层的主查询没有任何关联里层的子查询 能单独运行不会牵扯主查询的内容主查询在意的是 子查询的-------------结果2. 非相关子查询可以将非相关子查询 按照 里层的子查询返回的结果不同(1) 单行单列(2) 单列多行(3) 单行多列(4) 多列多行---子查询能放在哪里select where having from注意 如果 子查询放在了 select 后面则 必须是单行单列子查询 或者是 相关子查询---不能放在 group by 后面表连接 ----联合查询定义 是指 两张表 或者多张表 横向的拼接到一起拼接到一起变成一个临时的大表作用 是当需求 需要查看 数据源来自于多个表的时候表链接的方式内连接外连接交叉连接特殊连接内连接 inner join语法select *|列名 |计算|常量|函数|(子查询) -----展示from 表名1|(子查询)inner join 表名2|(子查询)on 关联条件........ --------------条件 and orinner join 表名3|(子查询)on 关联条件........ --------------条件 and orinner join 表名4|(子查询)on 关联条件........ --------------条件 and or..................................................where 分组前的过滤条件 ------------------------不能使用聚合函数group by 分组内容having 分组后的过滤条件------------------------能使用聚合函数order by 排序内容 asc | desc;内连接的特点是按照关联条件连接两张表只显示匹配成功的数据匹配不成功的忽略不显示外连接左外连接 右外连接 全外连接左外连接 left [outer] join语法 from 表名1 left join 表名2 on 关联条件.........左连接的特点 按照关联条件连接两张表左表为主 左表的数据 全部都会显示 如果匹配不成功则右表以空值填充右连接 right join语法 from 表名1 right join 表名2 on 关联条件............右连接的特点 以右表为主 右边的表全部都会显示 没有匹配成功的左表以空值填充全连接 full join语法 from 表名1 full join 表名2 on 关联条件...........全连接的特点 两张表的数据全部都会显示 匹配不成功的对应的表以空值填充交叉连接 --------------类似于笛卡尔积 ---两张表的数据相乘cross join语法 from 表名1 cross join 表名2 -----------------没有关联条件------工作中尽量避免笛卡尔积特殊连接自然连接自连接不等值连接自然连接 natural join语法 from 表名1 natural join 表名2 ---------没有关联条件自然连接的特点 是会自动的寻找相同列关联只会显示匹配成功的数据会去除重复 列不等值连接没有关键字 不是连接方式 是指 关联条件 不是等号自连接 没有关键字 只是 一种特殊的形式 自己表和自己表相连执行顺序from on join where group by having select distinct order byDML ----------数据操纵语言定义是指针对 “数据” 进行 增 删 改 操作的语言insert ---增加数据update ---修改数据delete ---删除数据-----字段属性 ----字段属性是指 每个字段在创建时 都会自带一个属性 该属性决定了 该字段存储的数据类型select * from emp字符型字段属性日期型字段属性数值型字段属性字符型字段属性char(n) :固定长度字符串该字段是char类型 该字段只能存储字符型数据n 表示总长度 存入的数据总长度不能超过n ,如果存入的数据总长度不足n 则有右侧以空格填充使其长度达到n ,最终以n个长度存储varchar(n) 可变长度字符串 该字段只能存储字符型数据n 表示总长度 存入的数据总长度不能超过n ,如果存入的数据总长度不足n则以实际长度存储varchar2(n) -----最长能填入 4000数值型字段属性number([p][,s]) 数值型字段属性该字段只能存储数值型数据p 是总长度包含小数位的存入的数据总长度不能超过ps 表示精度 小数点后保留几位number(7) ---没有小数number ---- 默认是 38 个长度int ----------------------------存储整数日期型字段属性date 日期型字段属性 包含 世纪 年月日 时分秒 --但是不一定都会显示timestamp 时间戳类型 比 date 多了毫秒部分--查看表结构------------------------------------------------------------------------insert ---增加数据语法 ----------------------------------背insert into 表名 [ (列名1,列名2,列名3...........)]values(值1,值2.....)----值 和 列 的个数 顺序 属性一致--------DML 语句 需要提交 才能生效-----提交之后不能回滚的提示 emp empno dept deptno 主键约束列 --不能为空删除数据 ---行语法delete from 表名 [where 条件]delete from emp -----------------------删除所有行select * from empupdate -----修改数据语法update 表名 set 列名1 值1 , 列名2 值2 ...... [where 条件]DML语句下的 merge into 操作作用 能同时增 删 改merge into 表名1 ----------目标表 ---是指 增 删 改 的表using 表名2 ----------数据源 ---为表1 提供值的on (匹配字段) -------------必须是 主键列 或者 唯一列 ----该列不能有重复数据when matched then ----------------当匹配成功 则update set 列名1值1,表名1.列名2表名2.列名2......... where 条件1......----------------------------------------可以结束delete where 条件2..................--必须满足以上所有条件--when not matched then ----当匹配不成功则insert (表名1.列名1,表名1.列名2...............)values(表名2.列名1,表名2.列名2................)DDL -------数据定义语言定义 是 针对 数据库对象 进行操作的语言数据库对象 表 视图 索引 序列 约束 函数 存储过程create ---创建数据库对象drop ---删除数据库对象alter ---修改数据库对象truncate ---清空创建表的语法create table 表名 (列名1 字段属性,列名2 字段属性 , 列名3 字段属性....)命名规范 1. 名字长度不能超过302. 必须以字母开头3. 不建议使用 _ 以外其余的特殊符号 to_date qingdao#zhiyun4. 不建议和关键字重名5. 。。。。。。。。。。。。。。。。。。。。。。。。。。。。注意 DDL 语句执行成功即生效 没有回滚机会的create table 表名 as select语句 ---select 语句的结果创建成表drop 删除数据库对象删除表的语法drop table 表名----工作中 不要使用 dropalter 修改数据库对象alter table 表名 rename to 新表名alter table 表名 rename column 列名 to 新列名添加字段语法alter table 表名 add (列名1 字段属性,列名2 字段属性...)删除字段 语法alter table 表名 drop (列名1,列名2.....................)修改字段属性语法alter table 表名 modify (列名1 新字段属性,列名2 新字段属性.....)不能小于现在有的数据truncate ---清空清空表语法truncate table 表名 ---清空表中所有数据truncate 和 delete 的区别1. truncate 只能清空所有 数据 delete 能删除部分数据2. truncate 是 DDL 语句 delete 是 DML 语句3. truncate 会释放空间 delete 不会释放空间4. truncate 效率高于 deleteDCL ---数据控制语言grant ---赋权revoke ---收权用户1. oracle 自带的用户 比如 SCOTT SYSTEM2。 自定义用户 可以自己创建用户自己创建用户 语法create user 用户名 identified by 密码注意 1 此时 仅仅 是创建了一个用户 还没有任何的权限2.用户名 是不区分大小写的 密码区分大小写的给用户赋权赋权grant 权限 to 用户; ---给用户付权收回权限revoke 权限 from 用户删除用户drop user 用户名 ----删除没有任何数据库对象的用户drop user 用户名 cascade ---串联 联通数据库对象一起删除角色 是指 一系列权限的集合1. 系统角色 DBA ----管理员角色2. 创建角色2. 创建角色 语法create role 角色名给角色 赋权grant 权限 to 角色将角色交给用户grant 角色 to 用户收回角色revoke 角色 from 用户删除角色drop role 角色名TCL 事务控制语言commit ---提交事务rollback --回滚事务事务 事务是指 为了完成某项业务或者任务由一系列看的见sql和一系列看不见的 后台进程组成的一个逻辑工作单元事务的属性1. 原子性 事务是一个整体 不可在分割2. 一致性 一旦事务提交成功 则所有的数据保持状态一致3. 隔离性 当前事务在操作是 其他事务只能查看提交之前的数据4. 永久性 一旦事务提交 数据将永久保存名词隐式提交 是指 一旦事务执行成功 oracle 会自动提交 比如 DDL显式提交 : 是需要 通过 commit 提交才能生效 比如 DML约束 constraint约束是指 强加在表中的 规则 或者 条件可以 使表 更加的 复合实际约束的分类一 按照 约束的 作用分1. 唯一约束 unique作用 该 列不能出现 重复的 非空数据2. 主键约束 primary key作用 该 列不能出现空值 也 不能出现重复数据语法alter table 表名 add constraint 约束名 primary key(列名1.....)注意 1. 一个字段上面可以添加多个约束但是不能添加重复作用的约束2. 每张表 只能有一个主键约束2. 建表的同时添加主键约束1. 行级create table 表名(列名1 字段属性 primary key,列名2 字段属性................)----系统分配约束名create table 表名(列名1 字段属性 constraint 约束名 primary key,列名2 字段属性................)----自己取约束名. 表级create table 表名(列名1 字段属性,列名2 字段属性................,constraint 约束名 primary key(列名1,列名2....))3. 外键约束 foreign key作用 该列的数据的取值 来自于另一张表的某字段 唯一列或主键列1. 建完表之后添加外键约束语法alter table 子表 add constraint 约束名foreign key (列名) references 父表(主键列或为一列)注意 外键 不能约束空值---外键比较麻烦 也比较顽固--工作中尽量不适用外键2. 建表的同时添加外键约束1. 行级create table 表名(列名1 字段属性 references 父表(主键列或为一列),列名2 字段属性...............)create table 表名(列名1 字段属性 constraint 约束名 references 父表(主键列或为一列),列名2 字段属性...............)2. 表级create table 表名(列名1 字段属性,列名2 字段属性...............,constraint 约束名 foreign key (列名 ) references 父表(主键列或为一列))4. 检查约束 check作用 根据自己的需求 自定义约束内容 ---类似于 条件1. 建完表语法alter table 表名 add constraint 约束名 check (条件1.......................)检查约束约束不了空值2 建表的同时 添加检查约束1. 行级语法create table 表名 (列名1 字段属性 check (条件1.....)-------------------条件 只能引用当前列, 列名2 字段属性...........)2 表级create table 表名 (列名1 字段属性, 列名2 字段属性...........,constraint 约束名 check (条件1.....))1. 建完表之后添加约束2. 建表的同时添加约束1. 建完表之后添加约束语法alter table 表名 add constraint 约束名 unique (列名1,列名2.......)2. 建表的同时添加唯一约束1 行级 语法create table 表名(列名1 字段属性 unique -----不能作用在多个字段上,列名2 字段属性..........)----------------系统分配约束名create table 表名(列名1 字段属性 constraint 约束名 unique -----不能作用在多个字段上,列名2 字段属性..........)----------------自己取约束名2.表级create table 表名(列名1 字段属性,列名2 字段属性.........., constraint 约束名 unique(列名1,列名2.....) )5. 非空 not null作用 该列不能有空值1. 建完表之后添加非空语法alter table 表名 modify 列名 not null ;2. 建表的同时添加非空1. 行级create table 表名 (列名1 字段属性 not null,列名2 字段属性..........)--没有表级的写法6. 默认值 default作用 该列没有插入数据的时候 填入默认值1. 建完表之后添加默认值语法alter table 表名 modify 列名 default 值;2. 建表的同时添加默认值1. 行级create table 表名(列名1 字段属性 default 值,列名2 字段属性.....)删除约束语法alter table 表名 drop constraint 约束名删除非空语法alter table 表名 modify 列名 null;alter table 表名 modify 列名 not null ;删除默认值alter table 表名 modify 列名 default null;视图 view是指 对 查询结果 的 一个 动态的保存是一张 虚拟 ----表--视图本身 不包含数据 数据是来自于基表--视图可以基于一张表创建 也可以基于多张表创建--视图可以创建成只读视图创建视图的语法create[or replace] ------或者 替换view 视图名as select语句[with read only] ------只读视图索引 index *********************************************定义 索引是类似于 目录 -----为了提高查询效率 ***************************----------------------------降低 DML 语句速度---------oracle 会自动的维护和使用索引---------经常查询的字段创建索引------必须是大表索引的分类一 按照索引的 存储内容 不同(1) B-tree 索引 --索引列原始数据rowid(2) 位图索引 --位图rowid(3) 反向建索引 --存索引列原始数据反向存储rowid(4) 基于函数的索引 --索引列经过函数处理rowid二 按照索引列的个数1 单列索引 基于一个字段创建的索引2 复合索引 基于多个列创建的索引 --联合索引三 唯一性分1. 唯一索引 是指 索引列的数据唯一2. 非唯一索引 是指 索引列数据可以不唯一创建索引 语法create index 索引名 on 表名 (列名1,列名2,列名3............);create bitmap index 索引名 on 表名 (列名1,列名2,列名3............);--位图索引create index 索引名 on 表名 (列名1,列名2,列名3............) reverse;--反向建索引create index 索引名 on 表名 (函数(列名1),列名2,列名3............);--基于函数的索引create unique index 索引名 on 表名 (列名1,列名2,列名3............);--唯一索引伪列 伪装起来的列除非特意查询才能看见rownumrowidrownum 是 针对 查询结果 从1 开始的一连续的自然数 序号rowid :类似于身份证 每存入一行数据 oracle 会自动的分配一个 18 位的不重复的字符串为了记录每行数据的物理位置全盘扫描按照条件列 从 第一行数据 检索到最后一行数据将复合条件的数据的rowid 取出 根据 rowid 取出整行数据索引扫描根据 索引列 从 大概位置 检索到最后一条复合条件的数据为止将复合条件的数据的rowid 取出 根据 rowid 取出整行数据B_tree --- 根 ----最顶级块--- 枝 ----指向下一节点-- 叶 ---最终数据的入口复合索引A B C 共同创建了一个 联合索引----最左原则where A and B.. and c...where B.. and c... --------------------不会走到索引where A and c...-----------效率会降低**********************************索引失效的情况1. 当用 or 条件的时候 索引会失效 where ename付浩浩 or ename颜琪琪2. 当通配符出现在搜索词的首位 索引也会失效 where ename like%S%3. 当有多个条件的时候 务必将索引列放在第一位 否则会失效 where deptno10 and ename付浩浩序列 sequence定义 是 oracle 提供的一组 能够自动增长 的 序号作用 大多情况是 为了 主键列提供值用的创建序列语法create sequence 序列名start with n ------初始值n , 默认的是1increment by n -----增长幅度n 默认的是1 --可以为负数maxvalue n | nomaxvalue ---最大值n 或者没有最大值默认 10的27次方minvalue n | nominvalue ---最小值n 或者没有最小值默认 -10的27次方cycle | nocycle ---循环或者 不循环 默认不循环cache n | nocache ---缓存n 或者没有缓存 默认 20个序号---注意 缓存值 不能大于 最大值/增长幅度currval ---当前值nextval ---下一个值select seq_97.currval --查看序列的当前值select seq_97.nextval --查看序列的下一个前值注意 1.序列此时没有当前值2.第一次使用序列必须使用下一个值 返回的是初始值3.循环都是从1 开始开窗函数--分析函数---窗口函数 ---over函数语法分析函数名() over(分析子句)分析函数名1. 聚合类 sum avg min max count ------聚合开窗2. 排序类 row_number() rank() dense_rank() ---排序开窗3. 偏移类 lag() lead() ----偏移开窗分析子句partition by 分组内容order by 排序内容 asc | descrow ----窗口 范围 。。。。。。。。。。。。。。。。。。。。。。。。。。。。1. 聚合类 sum avg min max count ------聚合开窗定义 按照一定规则分组 统一分析各组的情况 每一行数据返回一个值例题 查询 员工的姓名 岗位 薪资 以及部门编号 以及部门平均工资select ename,job,sal,deptno, avg(sal) over(partition by deptno)from emp;练习 查询员工姓名岗位薪资岗位平均薪资 ---要求薪资高于自己岗位平均薪资select ename,job,sal,avg(sal) over(partition by job)from empselect *from (select ename,job,sal,avg(sal) over(partition by job) vfrom emp )where salv----------------------------------------------------例题 查询 员工姓名岗位薪资部门编号以及 部门累计工资总和select ename,job,sal,deptno,sum(sal) over(partition by deptno order by sal )from emp------order by 做累计求和 ---有瑕疵select ename,job,sal,deptno,sum(sal) over()from emp注意 聚合开窗 over 里面 随意练习 查询员工姓名岗位薪资部门编号部门累计平均薪资以及部门名称要求查询部门地址是纽约的员工信息select ename,job,sal,emp.deptno,avg(sal) over(partition by emp.deptno order by sal),dnamefrom empleft join depton emp.deptnodept.deptnowhere locNEW YORK----------------------------------------------排序开窗 ************************************row_numbver() rank() dense_rank()select ename,job,sal,deptno,row_number() over(partition by deptno order by sal)----不考虑并列的 123456from emp ;select ename,job,sal,deptno,rank() over(partition by deptno order by sal)------考虑并列 但会跳过并列 123356from emp ;select ename,job,sal,deptno,dense_rank() over(partition by deptno order by sal)------考虑并列 不会跳过并列 123345from emp ;练习 查询 每个部门的工资的前两名select ename,sal,deptno,row_number() over(partition by deptno order by sal desc)from empselect *from (select ename,sal,deptno,row_number() over(partition by deptno order by sal desc) rfrom emp )where r2---排序开窗 注意 over 里面必须有 order by-----------------------------------------------------聚合开窗 累计求和select ename ,sal ,deptno,row_number() over(partition by deptno order by sal) rfrom empselect a.*,sum(sal) over(partition by deptno order by r)from(select ename ,sal ,deptno ,row_number() over(partition by deptno order by sal) rfrom emp ) a在 Oracle 中偏移开窗函数主要有lag()和lead()。lag(要分析的字段, 偏移量默认为偏移一行, [默认返回值默认为null])over(partition by... order by...)注意如果没有更多行可供偏移则返回该默认值。需注意偏移量不允许写负数且默认返回值的数据类型要与分析字段的数据类型保持一致。-- over 里面必须要有 order By 排序以下是一个在 Oracle 中创建表并使用 LAG 函数的示例drop table zy97CREATE TABLE zy97 (zid NUMBER PRIMARY KEY,zname VARCHAR2(50),zsal NUMBER,zdate DATE);-- 插入一些示例数据INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (1, 付浩浩, 5000, TO_DATE(2024-01-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (2, 付浩浩, 6000, TO_DATE(2024-03-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (3, 付浩浩, 7000, TO_DATE(2024-02-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (4, 付浩浩, 8000, TO_DATE(2024-04-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (5, 颜琪琪, 9000, TO_DATE(2024-01-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (6, 颜琪琪, 8000, TO_DATE(2024-03-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (7, 颜琪琪, 9999, TO_DATE(2024-02-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (8, 颜琪琪, 8888, TO_DATE(2024-04-01, YYYY-MM-DD));select * from zy97;使用 LAG 函数 查询每个员工的上一个月员工的薪水select zid,zname,zsal,zdate,lag(zsal,1,0) over(partition by zname order by zdate) 上个月的薪水from zy97环比增长率 本期数 - 上期数 / 上期数 × 100%select zid,zname,zsal,zdate,lag(zsal, 1, 1) over(partition by zname order by zdate) 上个月的薪水,(round(((zsal-lag(zsal, 1, 1) over(partition by zname order by zdate))/(lag(zsal, 1, 1) over(partition by zname order by zdate))*100,2))||%from zy97环比 --跟上个月 上个星期同比 --跟去年比练习同比增长率 本期数 - 上年同期数 / 上年同期数 × 100%-----------------------------------lead(要分析的字段, 偏移量默认为偏移一行, 默认返回值默认为null) over(partition by... order by...)则是向后偏移取数参数含义与lag()类似。SELECT zid, zname, zsal,lead(zsal, 1, 0) OVER (ORDER BY zdate) AS 下个月的工资FROM zy741;--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------***************************************************************行列转换行转列1 decode ------函数2. case when3. 行转列专用函数 pivotselect * from sc ;------成绩表 ---401 decode ------函数select sno,sum(decode(cno,c001,score ,0 ) ) c001,sum(decode(cno,c002,score ,0 ) ) c002,sum(decode(cno,c003,score ,0 ) ) c003,sum(decode(cno,c007,score ,0 ) ) c007,sum(decode(cno,c010,score ,0 ) ) c010-------c001,c002,c003,c007,c010from scgroup by sno2. case when ---你们做select sno,sum(case when cnoc001 then score else 0 end) c001,sum(case when cnoc002 then score else 0 end) c002,sum(case when cnoc003 then score else 0 end) c003,sum(case when cnoc007 then score else 0 end) c007,sum(case when cnoc010 then score else 0 end) c010from scgroup by sno3.行 转 列 专用函数 pivot语法select * from 表名 pivot(聚合函数() for 列名 in(排序) )聚合函数() ---不能写 count列名 --------是指 要转换的字段排序 -------转换的字段的数据 c001 c002 。。。。。--展示select * from sc pivot(sum(score)for cno in(c001 a,c002 b,c010,c007,c003))select * from sc pivot(count(score)for cno in(c001 a,c002 b,c010,c007,c003))----------------------------------------------------------------列转行列转行专用函数 unpivot语法select * from 表名 unpivot (列名1 for 列名2 in(排序))列名2 用来接收 转换的表的 字段的 --列名2 名字自己取排序 是列名2 接收的字段的一个顺序列名1 是接收 转换的数据的 ---列名1 名字自己取select * from lzhcreate table lzh as select sno,sum(case when cnoc001 then score else 0 end) c001,sum(case when cnoc002 then score else 0 end) c002,sum(case when cnoc003 then score else 0 end) c003,sum(case when cnoc007 then score else 0 end) c007,sum(case when cnoc010 then score else 0 end) c010from scgroup by snoselect *from lzhunpivot( 成绩 for 课程 in(c001,c002,c003,c010,c007))集合运算是指 sql语句之间的运算select语句运算select语句运算select语句...........................运算1 并集 union union all -----区别2.交集 intersect3.差集 minus并集select * from emp where deptno10 ---5union -----2 ---754-2select * from emp where sal2000 --4----union 会去除重复的数据 --行select * from emp where deptno10 ---5union all -----2 ---954select * from emp where sal2000 --4-----union all 是不会去重 的select * from emp where deptno10 ---5intersect -----2 ---2select * from emp where sal2000 --4select * from emp where deptno10 ---5minus -----2 ---35-2select * from emp where sal2000 --4select * from emp where sal2000 --4minus -----2 ---24-2select * from emp where deptno10 ---5注意 1. 差集是有上下之分2. union 是去除重复数据 union all 是不去重的3. 除了 union all 意外 其余的都会按照第一列升序排列4. 列的个数 顺序 属性 需要一致5. 列名可以不一致 最终展示的是 第一个语句的字段6. 有多个运算是从上向下运算 有() 先运算()--------------------------------------------------------------------------sql 结束简单查询条件查询排序查询分组查询函数子查询表连接DML DDL DCL TCL约束 视图 索引 序列开窗函数 --函数行列转换集合运算
隐性转换 oracle 默认的将数值转换成字符尽量避免出现隐形转换子查询定义 是指 一条语句 嵌套 一条或者多条查询语句---1. 把 外层的查询叫做 主查询 里层的查询叫做 子 查询-- 2 里层的子查询 必须加上括号 select (select )子查询的分类1. 相关子查询 是指里层子查询与外层的主查询部分内容相关里层的子查询 不能单独运行因为会牵扯主查询部分内容主查询 在意的是 与子查询的----------关系2. 非相关子查询 是指里层的子查询与外层的主查询没有任何关联里层的子查询 能单独运行不会牵扯主查询的内容主查询在意的是 子查询的-------------结果2. 非相关子查询可以将非相关子查询 按照 里层的子查询返回的结果不同(1) 单行单列(2) 单列多行(3) 单行多列(4) 多列多行---子查询能放在哪里select where having from注意 如果 子查询放在了 select 后面则 必须是单行单列子查询 或者是 相关子查询---不能放在 group by 后面表连接 ----联合查询定义 是指 两张表 或者多张表 横向的拼接到一起拼接到一起变成一个临时的大表作用 是当需求 需要查看 数据源来自于多个表的时候表链接的方式内连接外连接交叉连接特殊连接内连接 inner join语法select *|列名 |计算|常量|函数|(子查询) -----展示from 表名1|(子查询)inner join 表名2|(子查询)on 关联条件........ --------------条件 and orinner join 表名3|(子查询)on 关联条件........ --------------条件 and orinner join 表名4|(子查询)on 关联条件........ --------------条件 and or..................................................where 分组前的过滤条件 ------------------------不能使用聚合函数group by 分组内容having 分组后的过滤条件------------------------能使用聚合函数order by 排序内容 asc | desc;内连接的特点是按照关联条件连接两张表只显示匹配成功的数据匹配不成功的忽略不显示外连接左外连接 右外连接 全外连接左外连接 left [outer] join语法 from 表名1 left join 表名2 on 关联条件.........左连接的特点 按照关联条件连接两张表左表为主 左表的数据 全部都会显示 如果匹配不成功则右表以空值填充右连接 right join语法 from 表名1 right join 表名2 on 关联条件............右连接的特点 以右表为主 右边的表全部都会显示 没有匹配成功的左表以空值填充全连接 full join语法 from 表名1 full join 表名2 on 关联条件...........全连接的特点 两张表的数据全部都会显示 匹配不成功的对应的表以空值填充交叉连接 --------------类似于笛卡尔积 ---两张表的数据相乘cross join语法 from 表名1 cross join 表名2 -----------------没有关联条件------工作中尽量避免笛卡尔积特殊连接自然连接自连接不等值连接自然连接 natural join语法 from 表名1 natural join 表名2 ---------没有关联条件自然连接的特点 是会自动的寻找相同列关联只会显示匹配成功的数据会去除重复 列不等值连接没有关键字 不是连接方式 是指 关联条件 不是等号自连接 没有关键字 只是 一种特殊的形式 自己表和自己表相连执行顺序from on join where group by having select distinct order byDML ----------数据操纵语言定义是指针对 “数据” 进行 增 删 改 操作的语言insert ---增加数据update ---修改数据delete ---删除数据-----字段属性 ----字段属性是指 每个字段在创建时 都会自带一个属性 该属性决定了 该字段存储的数据类型select * from emp字符型字段属性日期型字段属性数值型字段属性字符型字段属性char(n) :固定长度字符串该字段是char类型 该字段只能存储字符型数据n 表示总长度 存入的数据总长度不能超过n ,如果存入的数据总长度不足n 则有右侧以空格填充使其长度达到n ,最终以n个长度存储varchar(n) 可变长度字符串 该字段只能存储字符型数据n 表示总长度 存入的数据总长度不能超过n ,如果存入的数据总长度不足n则以实际长度存储varchar2(n) -----最长能填入 4000数值型字段属性number([p][,s]) 数值型字段属性该字段只能存储数值型数据p 是总长度包含小数位的存入的数据总长度不能超过ps 表示精度 小数点后保留几位number(7) ---没有小数number ---- 默认是 38 个长度int ----------------------------存储整数日期型字段属性date 日期型字段属性 包含 世纪 年月日 时分秒 --但是不一定都会显示timestamp 时间戳类型 比 date 多了毫秒部分--查看表结构------------------------------------------------------------------------insert ---增加数据语法 ----------------------------------背insert into 表名 [ (列名1,列名2,列名3...........)]values(值1,值2.....)----值 和 列 的个数 顺序 属性一致--------DML 语句 需要提交 才能生效-----提交之后不能回滚的提示 emp empno dept deptno 主键约束列 --不能为空删除数据 ---行语法delete from 表名 [where 条件]delete from emp -----------------------删除所有行select * from empupdate -----修改数据语法update 表名 set 列名1 值1 , 列名2 值2 ...... [where 条件]DML语句下的 merge into 操作作用 能同时增 删 改merge into 表名1 ----------目标表 ---是指 增 删 改 的表using 表名2 ----------数据源 ---为表1 提供值的on (匹配字段) -------------必须是 主键列 或者 唯一列 ----该列不能有重复数据when matched then ----------------当匹配成功 则update set 列名1值1,表名1.列名2表名2.列名2......... where 条件1......----------------------------------------可以结束delete where 条件2..................--必须满足以上所有条件--when not matched then ----当匹配不成功则insert (表名1.列名1,表名1.列名2...............)values(表名2.列名1,表名2.列名2................)DDL -------数据定义语言定义 是 针对 数据库对象 进行操作的语言数据库对象 表 视图 索引 序列 约束 函数 存储过程create ---创建数据库对象drop ---删除数据库对象alter ---修改数据库对象truncate ---清空创建表的语法create table 表名 (列名1 字段属性,列名2 字段属性 , 列名3 字段属性....)命名规范 1. 名字长度不能超过302. 必须以字母开头3. 不建议使用 _ 以外其余的特殊符号 to_date qingdao#zhiyun4. 不建议和关键字重名5. 。。。。。。。。。。。。。。。。。。。。。。。。。。。。注意 DDL 语句执行成功即生效 没有回滚机会的create table 表名 as select语句 ---select 语句的结果创建成表drop 删除数据库对象删除表的语法drop table 表名----工作中 不要使用 dropalter 修改数据库对象alter table 表名 rename to 新表名alter table 表名 rename column 列名 to 新列名添加字段语法alter table 表名 add (列名1 字段属性,列名2 字段属性...)删除字段 语法alter table 表名 drop (列名1,列名2.....................)修改字段属性语法alter table 表名 modify (列名1 新字段属性,列名2 新字段属性.....)不能小于现在有的数据truncate ---清空清空表语法truncate table 表名 ---清空表中所有数据truncate 和 delete 的区别1. truncate 只能清空所有 数据 delete 能删除部分数据2. truncate 是 DDL 语句 delete 是 DML 语句3. truncate 会释放空间 delete 不会释放空间4. truncate 效率高于 deleteDCL ---数据控制语言grant ---赋权revoke ---收权用户1. oracle 自带的用户 比如 SCOTT SYSTEM2。 自定义用户 可以自己创建用户自己创建用户 语法create user 用户名 identified by 密码注意 1 此时 仅仅 是创建了一个用户 还没有任何的权限2.用户名 是不区分大小写的 密码区分大小写的给用户赋权赋权grant 权限 to 用户; ---给用户付权收回权限revoke 权限 from 用户删除用户drop user 用户名 ----删除没有任何数据库对象的用户drop user 用户名 cascade ---串联 联通数据库对象一起删除角色 是指 一系列权限的集合1. 系统角色 DBA ----管理员角色2. 创建角色2. 创建角色 语法create role 角色名给角色 赋权grant 权限 to 角色将角色交给用户grant 角色 to 用户收回角色revoke 角色 from 用户删除角色drop role 角色名TCL 事务控制语言commit ---提交事务rollback --回滚事务事务 事务是指 为了完成某项业务或者任务由一系列看的见sql和一系列看不见的 后台进程组成的一个逻辑工作单元事务的属性1. 原子性 事务是一个整体 不可在分割2. 一致性 一旦事务提交成功 则所有的数据保持状态一致3. 隔离性 当前事务在操作是 其他事务只能查看提交之前的数据4. 永久性 一旦事务提交 数据将永久保存名词隐式提交 是指 一旦事务执行成功 oracle 会自动提交 比如 DDL显式提交 : 是需要 通过 commit 提交才能生效 比如 DML约束 constraint约束是指 强加在表中的 规则 或者 条件可以 使表 更加的 复合实际约束的分类一 按照 约束的 作用分1. 唯一约束 unique作用 该 列不能出现 重复的 非空数据2. 主键约束 primary key作用 该 列不能出现空值 也 不能出现重复数据语法alter table 表名 add constraint 约束名 primary key(列名1.....)注意 1. 一个字段上面可以添加多个约束但是不能添加重复作用的约束2. 每张表 只能有一个主键约束2. 建表的同时添加主键约束1. 行级create table 表名(列名1 字段属性 primary key,列名2 字段属性................)----系统分配约束名create table 表名(列名1 字段属性 constraint 约束名 primary key,列名2 字段属性................)----自己取约束名. 表级create table 表名(列名1 字段属性,列名2 字段属性................,constraint 约束名 primary key(列名1,列名2....))3. 外键约束 foreign key作用 该列的数据的取值 来自于另一张表的某字段 唯一列或主键列1. 建完表之后添加外键约束语法alter table 子表 add constraint 约束名foreign key (列名) references 父表(主键列或为一列)注意 外键 不能约束空值---外键比较麻烦 也比较顽固--工作中尽量不适用外键2. 建表的同时添加外键约束1. 行级create table 表名(列名1 字段属性 references 父表(主键列或为一列),列名2 字段属性...............)create table 表名(列名1 字段属性 constraint 约束名 references 父表(主键列或为一列),列名2 字段属性...............)2. 表级create table 表名(列名1 字段属性,列名2 字段属性...............,constraint 约束名 foreign key (列名 ) references 父表(主键列或为一列))4. 检查约束 check作用 根据自己的需求 自定义约束内容 ---类似于 条件1. 建完表语法alter table 表名 add constraint 约束名 check (条件1.......................)检查约束约束不了空值2 建表的同时 添加检查约束1. 行级语法create table 表名 (列名1 字段属性 check (条件1.....)-------------------条件 只能引用当前列, 列名2 字段属性...........)2 表级create table 表名 (列名1 字段属性, 列名2 字段属性...........,constraint 约束名 check (条件1.....))1. 建完表之后添加约束2. 建表的同时添加约束1. 建完表之后添加约束语法alter table 表名 add constraint 约束名 unique (列名1,列名2.......)2. 建表的同时添加唯一约束1 行级 语法create table 表名(列名1 字段属性 unique -----不能作用在多个字段上,列名2 字段属性..........)----------------系统分配约束名create table 表名(列名1 字段属性 constraint 约束名 unique -----不能作用在多个字段上,列名2 字段属性..........)----------------自己取约束名2.表级create table 表名(列名1 字段属性,列名2 字段属性.........., constraint 约束名 unique(列名1,列名2.....) )5. 非空 not null作用 该列不能有空值1. 建完表之后添加非空语法alter table 表名 modify 列名 not null ;2. 建表的同时添加非空1. 行级create table 表名 (列名1 字段属性 not null,列名2 字段属性..........)--没有表级的写法6. 默认值 default作用 该列没有插入数据的时候 填入默认值1. 建完表之后添加默认值语法alter table 表名 modify 列名 default 值;2. 建表的同时添加默认值1. 行级create table 表名(列名1 字段属性 default 值,列名2 字段属性.....)删除约束语法alter table 表名 drop constraint 约束名删除非空语法alter table 表名 modify 列名 null;alter table 表名 modify 列名 not null ;删除默认值alter table 表名 modify 列名 default null;视图 view是指 对 查询结果 的 一个 动态的保存是一张 虚拟 ----表--视图本身 不包含数据 数据是来自于基表--视图可以基于一张表创建 也可以基于多张表创建--视图可以创建成只读视图创建视图的语法create[or replace] ------或者 替换view 视图名as select语句[with read only] ------只读视图索引 index *********************************************定义 索引是类似于 目录 -----为了提高查询效率 ***************************----------------------------降低 DML 语句速度---------oracle 会自动的维护和使用索引---------经常查询的字段创建索引------必须是大表索引的分类一 按照索引的 存储内容 不同(1) B-tree 索引 --索引列原始数据rowid(2) 位图索引 --位图rowid(3) 反向建索引 --存索引列原始数据反向存储rowid(4) 基于函数的索引 --索引列经过函数处理rowid二 按照索引列的个数1 单列索引 基于一个字段创建的索引2 复合索引 基于多个列创建的索引 --联合索引三 唯一性分1. 唯一索引 是指 索引列的数据唯一2. 非唯一索引 是指 索引列数据可以不唯一创建索引 语法create index 索引名 on 表名 (列名1,列名2,列名3............);create bitmap index 索引名 on 表名 (列名1,列名2,列名3............);--位图索引create index 索引名 on 表名 (列名1,列名2,列名3............) reverse;--反向建索引create index 索引名 on 表名 (函数(列名1),列名2,列名3............);--基于函数的索引create unique index 索引名 on 表名 (列名1,列名2,列名3............);--唯一索引伪列 伪装起来的列除非特意查询才能看见rownumrowidrownum 是 针对 查询结果 从1 开始的一连续的自然数 序号rowid :类似于身份证 每存入一行数据 oracle 会自动的分配一个 18 位的不重复的字符串为了记录每行数据的物理位置全盘扫描按照条件列 从 第一行数据 检索到最后一行数据将复合条件的数据的rowid 取出 根据 rowid 取出整行数据索引扫描根据 索引列 从 大概位置 检索到最后一条复合条件的数据为止将复合条件的数据的rowid 取出 根据 rowid 取出整行数据B_tree --- 根 ----最顶级块--- 枝 ----指向下一节点-- 叶 ---最终数据的入口复合索引A B C 共同创建了一个 联合索引----最左原则where A and B.. and c...where B.. and c... --------------------不会走到索引where A and c...-----------效率会降低**********************************索引失效的情况1. 当用 or 条件的时候 索引会失效 where ename付浩浩 or ename颜琪琪2. 当通配符出现在搜索词的首位 索引也会失效 where ename like%S%3. 当有多个条件的时候 务必将索引列放在第一位 否则会失效 where deptno10 and ename付浩浩序列 sequence定义 是 oracle 提供的一组 能够自动增长 的 序号作用 大多情况是 为了 主键列提供值用的创建序列语法create sequence 序列名start with n ------初始值n , 默认的是1increment by n -----增长幅度n 默认的是1 --可以为负数maxvalue n | nomaxvalue ---最大值n 或者没有最大值默认 10的27次方minvalue n | nominvalue ---最小值n 或者没有最小值默认 -10的27次方cycle | nocycle ---循环或者 不循环 默认不循环cache n | nocache ---缓存n 或者没有缓存 默认 20个序号---注意 缓存值 不能大于 最大值/增长幅度currval ---当前值nextval ---下一个值select seq_97.currval --查看序列的当前值select seq_97.nextval --查看序列的下一个前值注意 1.序列此时没有当前值2.第一次使用序列必须使用下一个值 返回的是初始值3.循环都是从1 开始开窗函数--分析函数---窗口函数 ---over函数语法分析函数名() over(分析子句)分析函数名1. 聚合类 sum avg min max count ------聚合开窗2. 排序类 row_number() rank() dense_rank() ---排序开窗3. 偏移类 lag() lead() ----偏移开窗分析子句partition by 分组内容order by 排序内容 asc | descrow ----窗口 范围 。。。。。。。。。。。。。。。。。。。。。。。。。。。。1. 聚合类 sum avg min max count ------聚合开窗定义 按照一定规则分组 统一分析各组的情况 每一行数据返回一个值例题 查询 员工的姓名 岗位 薪资 以及部门编号 以及部门平均工资select ename,job,sal,deptno, avg(sal) over(partition by deptno)from emp;练习 查询员工姓名岗位薪资岗位平均薪资 ---要求薪资高于自己岗位平均薪资select ename,job,sal,avg(sal) over(partition by job)from empselect *from (select ename,job,sal,avg(sal) over(partition by job) vfrom emp )where salv----------------------------------------------------例题 查询 员工姓名岗位薪资部门编号以及 部门累计工资总和select ename,job,sal,deptno,sum(sal) over(partition by deptno order by sal )from emp------order by 做累计求和 ---有瑕疵select ename,job,sal,deptno,sum(sal) over()from emp注意 聚合开窗 over 里面 随意练习 查询员工姓名岗位薪资部门编号部门累计平均薪资以及部门名称要求查询部门地址是纽约的员工信息select ename,job,sal,emp.deptno,avg(sal) over(partition by emp.deptno order by sal),dnamefrom empleft join depton emp.deptnodept.deptnowhere locNEW YORK----------------------------------------------排序开窗 ************************************row_numbver() rank() dense_rank()select ename,job,sal,deptno,row_number() over(partition by deptno order by sal)----不考虑并列的 123456from emp ;select ename,job,sal,deptno,rank() over(partition by deptno order by sal)------考虑并列 但会跳过并列 123356from emp ;select ename,job,sal,deptno,dense_rank() over(partition by deptno order by sal)------考虑并列 不会跳过并列 123345from emp ;练习 查询 每个部门的工资的前两名select ename,sal,deptno,row_number() over(partition by deptno order by sal desc)from empselect *from (select ename,sal,deptno,row_number() over(partition by deptno order by sal desc) rfrom emp )where r2---排序开窗 注意 over 里面必须有 order by-----------------------------------------------------聚合开窗 累计求和select ename ,sal ,deptno,row_number() over(partition by deptno order by sal) rfrom empselect a.*,sum(sal) over(partition by deptno order by r)from(select ename ,sal ,deptno ,row_number() over(partition by deptno order by sal) rfrom emp ) a在 Oracle 中偏移开窗函数主要有lag()和lead()。lag(要分析的字段, 偏移量默认为偏移一行, [默认返回值默认为null])over(partition by... order by...)注意如果没有更多行可供偏移则返回该默认值。需注意偏移量不允许写负数且默认返回值的数据类型要与分析字段的数据类型保持一致。-- over 里面必须要有 order By 排序以下是一个在 Oracle 中创建表并使用 LAG 函数的示例drop table zy97CREATE TABLE zy97 (zid NUMBER PRIMARY KEY,zname VARCHAR2(50),zsal NUMBER,zdate DATE);-- 插入一些示例数据INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (1, 付浩浩, 5000, TO_DATE(2024-01-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (2, 付浩浩, 6000, TO_DATE(2024-03-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (3, 付浩浩, 7000, TO_DATE(2024-02-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (4, 付浩浩, 8000, TO_DATE(2024-04-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (5, 颜琪琪, 9000, TO_DATE(2024-01-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (6, 颜琪琪, 8000, TO_DATE(2024-03-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (7, 颜琪琪, 9999, TO_DATE(2024-02-01, YYYY-MM-DD));INSERT INTO zy97 (zid, zname, zsal,zdate)VALUES (8, 颜琪琪, 8888, TO_DATE(2024-04-01, YYYY-MM-DD));select * from zy97;使用 LAG 函数 查询每个员工的上一个月员工的薪水select zid,zname,zsal,zdate,lag(zsal,1,0) over(partition by zname order by zdate) 上个月的薪水from zy97环比增长率 本期数 - 上期数 / 上期数 × 100%select zid,zname,zsal,zdate,lag(zsal, 1, 1) over(partition by zname order by zdate) 上个月的薪水,(round(((zsal-lag(zsal, 1, 1) over(partition by zname order by zdate))/(lag(zsal, 1, 1) over(partition by zname order by zdate))*100,2))||%from zy97环比 --跟上个月 上个星期同比 --跟去年比练习同比增长率 本期数 - 上年同期数 / 上年同期数 × 100%-----------------------------------lead(要分析的字段, 偏移量默认为偏移一行, 默认返回值默认为null) over(partition by... order by...)则是向后偏移取数参数含义与lag()类似。SELECT zid, zname, zsal,lead(zsal, 1, 0) OVER (ORDER BY zdate) AS 下个月的工资FROM zy741;--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------***************************************************************行列转换行转列1 decode ------函数2. case when3. 行转列专用函数 pivotselect * from sc ;------成绩表 ---401 decode ------函数select sno,sum(decode(cno,c001,score ,0 ) ) c001,sum(decode(cno,c002,score ,0 ) ) c002,sum(decode(cno,c003,score ,0 ) ) c003,sum(decode(cno,c007,score ,0 ) ) c007,sum(decode(cno,c010,score ,0 ) ) c010-------c001,c002,c003,c007,c010from scgroup by sno2. case when ---你们做select sno,sum(case when cnoc001 then score else 0 end) c001,sum(case when cnoc002 then score else 0 end) c002,sum(case when cnoc003 then score else 0 end) c003,sum(case when cnoc007 then score else 0 end) c007,sum(case when cnoc010 then score else 0 end) c010from scgroup by sno3.行 转 列 专用函数 pivot语法select * from 表名 pivot(聚合函数() for 列名 in(排序) )聚合函数() ---不能写 count列名 --------是指 要转换的字段排序 -------转换的字段的数据 c001 c002 。。。。。--展示select * from sc pivot(sum(score)for cno in(c001 a,c002 b,c010,c007,c003))select * from sc pivot(count(score)for cno in(c001 a,c002 b,c010,c007,c003))----------------------------------------------------------------列转行列转行专用函数 unpivot语法select * from 表名 unpivot (列名1 for 列名2 in(排序))列名2 用来接收 转换的表的 字段的 --列名2 名字自己取排序 是列名2 接收的字段的一个顺序列名1 是接收 转换的数据的 ---列名1 名字自己取select * from lzhcreate table lzh as select sno,sum(case when cnoc001 then score else 0 end) c001,sum(case when cnoc002 then score else 0 end) c002,sum(case when cnoc003 then score else 0 end) c003,sum(case when cnoc007 then score else 0 end) c007,sum(case when cnoc010 then score else 0 end) c010from scgroup by snoselect *from lzhunpivot( 成绩 for 课程 in(c001,c002,c003,c010,c007))集合运算是指 sql语句之间的运算select语句运算select语句运算select语句...........................运算1 并集 union union all -----区别2.交集 intersect3.差集 minus并集select * from emp where deptno10 ---5union -----2 ---754-2select * from emp where sal2000 --4----union 会去除重复的数据 --行select * from emp where deptno10 ---5union all -----2 ---954select * from emp where sal2000 --4-----union all 是不会去重 的select * from emp where deptno10 ---5intersect -----2 ---2select * from emp where sal2000 --4select * from emp where deptno10 ---5minus -----2 ---35-2select * from emp where sal2000 --4select * from emp where sal2000 --4minus -----2 ---24-2select * from emp where deptno10 ---5注意 1. 差集是有上下之分2. union 是去除重复数据 union all 是不去重的3. 除了 union all 意外 其余的都会按照第一列升序排列4. 列的个数 顺序 属性 需要一致5. 列名可以不一致 最终展示的是 第一个语句的字段6. 有多个运算是从上向下运算 有() 先运算()--------------------------------------------------------------------------sql 结束简单查询条件查询排序查询分组查询函数子查询表连接DML DDL DCL TCL约束 视图 索引 序列开窗函数 --函数行列转换集合运算
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
