![](https://img-blog.csdnimg.cn/20201014180756928.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
Oracle
icecoola_
这个作者很懒,什么都没留下…
展开
-
Oracle_单表更新
update table t1 set t1.col_1 = t1.col2 where t1.col_1 is null and t1.col_2 is not null;原创 2021-02-12 09:26:41 · 524 阅读 · 0 评论 -
Oracle_查看版本信息
sqlselect * from v$version where rownum = 1;原创 2020-12-18 15:15:40 · 387 阅读 · 0 评论 -
Oracle_查询当前用户_其他用户下所有表
sqlselect * from user_tables;select * from all_tables where owner='OTHER_TABLE' order by table_name asc;原创 2020-09-14 17:00:59 · 1241 阅读 · 0 评论 -
Oracle_START WITH_CONNECT BY PRIOR
oracle的start with connect by prior如何使用转载 2020-05-21 01:51:35 · 211 阅读 · 0 评论 -
Oracle_同义词
Oracle 同义词Oracle 同义词1. Oracle同义词简介Oracle数据库中提供了同义词管理的功能。同义词是数据库方案对象的一个别名,经常用于简化对象访问和提高对象访问的安全性。在使用同义词时,Oracle数据库将它翻译成对应方案对象的名字。与视图类似,同义词并不占用实际存储空间,只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象,如表、视图、同义词、序列、存储过程、包等等,数据库管理员都可以根据实际情况为他们定义同义词。同义词拥有如下好处:节省大量的转载 2020-05-09 10:11:46 · 431 阅读 · 0 评论 -
Oracle_plsql_this is the trial version of pl/sql developeryou've got X day left
plsqlProduct Code:ljkfuhjpccxt8xq2re37n97595ldmv9kchSerial Number:302967Password:xs374caLicense Number:999原创 2020-04-28 13:58:35 · 1057 阅读 · 0 评论 -
leetcode_Oracle_197. 上升的温度_偏移分析函数 lag()与lead()
197. 上升的温度ORACLE 偏移分析函数 lag()与lead() 用法select temp.id as "Id" from (select id, recorddate r2, Temperature as t2, lag(Temperature, 1, null) over(order ...原创 2020-04-21 23:32:04 · 311 阅读 · 0 评论 -
leetcode_Oracle_176. 第二高的薪水
176. 第二高的薪水SELECT MAX(Salary) AS "SecondHighestSalary"FROM Employeewhere salary < ( SELECT MAX(Salary) FROM Employee )原创 2020-04-16 03:09:56 · 347 阅读 · 0 评论 -
leetcode_Oracle_184. 部门工资最高的员工
184. 部门工资最高的员工select depart.Name as "Department",temp.Name as "Employee" ,temp.Salary as "Salary"from Employee temp,Department depart where temp.DepartmentId = depart.Id and(temp.Salary,tem...原创 2020-04-09 00:44:00 · 324 阅读 · 0 评论 -
leetcode_Oracle_178. 分数排名
178. 分数排名row_number() 是没有重复值的排序dense_rank() 是连续排序,两个第二名仍然跟着第三名rank() 是跳跃拍学,两个第二名下来就是第四名select Score as "Score",dense_rank() over(order by Score desc) as "Rank" from Scoresselect B.S...原创 2020-04-07 02:06:08 · 240 阅读 · 0 评论 -
leetcode_Oracle_181. 超过经理收入的员工
181. 超过经理收入的员工select a.Name as "Employee" from Employee a ,Employee b where a.ManagerId = b.Id and a.Salary >b.Salary select a.Name as "Employee"from Employee awhere a.salary > (selec...原创 2020-04-05 10:35:16 · 220 阅读 · 0 评论 -
leetcode_Oracle_175. 组合两个表
175. 组合两个表select b.FirstName as "FirstName",b.LastName as "LastName",a.City as "City",a.State as "State"from Person b left join Address a on a.PersonId = b.PersonIdselect b.FirstName as "F...原创 2020-04-04 18:51:56 · 198 阅读 · 0 评论 -
leetcode_Oracle_620. 有趣的电影
620. 有趣的电影select id as "id",movie as "movie",description as "description",rating as "rating"from cinema where description <>'boring' and mod(id,2)=1 order by rating desc;原创 2020-04-03 14:05:54 · 182 阅读 · 0 评论 -
leetcode_Oracle_595. 大的国家
595. 大的国家select name as "name",population as "population", area as "area"from world where area>3000000unionselect name as "name",population as "population", area as "area"from world where po...原创 2020-04-03 13:19:21 · 208 阅读 · 0 评论 -
leetcode_Oracle_182. 查找重复的电子邮箱
182. 查找重复的电子邮箱select Email as "Email" from (select Email,count(1) as countsfrom Persongroup by Email) temp where temp.counts>1;select distinct a.Email as "Email" from Person a,Person b whe...原创 2020-04-03 10:17:48 · 591 阅读 · 0 评论 -
Oracle_字段精度修改_18_18,2
已有数据修改alter table T_CONTRACT add (PAY_MONEY_TEMP number(18,2));update T_CONTRACT set PAY_MONEY_TEMP = round(PAY_MONEY,2);alter table T_CONTRACT drop column PAY_MONEY;alter table T_CONTRACT add (...原创 2019-12-13 16:35:47 · 874 阅读 · 0 评论 -
Oracle_生成uuid
oracleselect sys_guid() from dual; select LENGTH(sys_guid())from dual;select RAWTOHEX(sys_guid()) from dual;select lower(sys_guid()) from dual;�Hc�:M��P?nOD32951B4863B23F4DA1E0500A0A3F6E4F44...原创 2019-10-17 20:35:00 · 2610 阅读 · 0 评论 -
Oracle_类型转换_date转String_clob转String
1clob转stringdbms_lob.substr(CLOB_BODY) "clobBody"date转stringto_char(CREATE_TIME,'yyyy-MM-dd HH24:mi:ss') "createDate",原创 2019-10-17 20:17:28 · 4207 阅读 · 0 评论 -
Oracle_获取最新一条记录
1select rownum ,a.* from t_table a where rownum <1002SELECT * FROM ( SELECT *,ROWNUM rn FROM t ORDER BY inputDate DESC) WHERE rn = 1原创 2018-11-15 16:43:22 · 7250 阅读 · 1 评论 -
Oracle_字段列值_逗号拼接_wmsys.wm_concat()函数
sqlSELECT WMSYS.WM_CONCAT(NAME) FROM T_TABLEresult1,2,3,4,5原创 2018-12-07 14:57:43 · 1424 阅读 · 0 评论 -
Oracle_dual???
Selecting from the DUAL TableDUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all ...原创 2018-12-07 15:19:32 · 173 阅读 · 0 评论 -
ORA-01861: 文字与格式字符串不匹配;literal does not match format string;
问题描述: 保存系统当前时间,精确到时分秒错误sqltime date类型字段update t_table SET time = to_char(sysdate,'yyyy-MM-dd HH24:mi:ss')报错信息: ORA-01861: 文字与格式字符串不匹配处理方式:select sysdate from dual;select to_date(sysdate) fr...原创 2018-12-14 16:28:11 · 1710 阅读 · 0 评论 -
Oracle_left_join_关联最新一条记录
1原创 2019-01-29 17:23:24 · 4047 阅读 · 0 评论 -
Oracle_replace函数
replace就是一般意义上的字符串替换replace(c1,c2[,c3])在字符串c1中找到字符串c2,替换成c3.若c3为空,则在c1中删除所有c2.原创 2019-04-15 15:16:39 · 651 阅读 · 0 评论 -
Oracle_字段左右去空
1update T_TABLE set REMARK = trim(REMARK) where REMARK is not NULL;原创 2019-05-27 10:40:13 · 764 阅读 · 0 评论 -
Oracle_分组查询_group_by
sqlselect temp.code,temp.countsFROM(select code,count(sexType) as counts from t_class where sexType= 2 group by code)temp where temp.counts>2原创 2019-07-08 10:53:40 · 690 阅读 · 0 评论 -
Oracle_查询表结构_每列属性
1select t.COLUMN_NAME, t.DATA_TYPE, t.DATA_LENGTH, t.DATA_PRECISION, t.NULLABLE, t.COLUMN_ID, c.COMMENTS from user_tab_columns t, user_col_comments c wher...转载 2019-07-23 18:12:28 · 2869 阅读 · 0 评论 -
Oracle_逗号拼接的varchar2字符串_行转列
sqlSELECT REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, ROWNUM) CODE FROM DUALCONNECT BY ROWNUM <= LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1;result'a,b,c,d'abcd原创 2019-07-31 11:38:15 · 987 阅读 · 0 评论 -
Oracle_Mybatis_大于_小于_处理
test原创 2019-07-31 13:35:03 · 2188 阅读 · 0 评论 -
Oracle_字段拼接_逗号_单引号
Oracle_字段拼接逗号单引号select code,USER_USERID,USER_USERNAME,('update t_user set userId = ''' || USER_USERID|| ''',userName=''' || USER_USERNAME|| ''' where userCode = ''' || code||'''')from user_in...原创 2018-06-21 15:33:33 · 3683 阅读 · 0 评论