sql总结(oracle篇)

来源：互联网发布：软件业务需求说明书编辑：程序博客网时间：2024/06/13 01:22

sql在项目开发中占有很重要的地位，大家在开发项目中无可避免的都会涉及sql的编写、修改、维护，如果对与sql语法不太了解的话，这样的工作对于我们来说是举步维艰，下面呢，我将我在工作中遇到的sql相关的知识与大家分享一下。

添加表字段

应用场景：新增相关信息需保存到数据库。

语法：alter table 表名 add 字段字段类型;

例子：alter table client add name VARCHAR2(32);

修改字段

应用场景：表数据存储不了该信息，需要修改字段长度，用于保存相关信息。

语法：alter table 表名 modify 字段 varchar2(1000);

例子：alter table client modify name varchar2(64);

删除字段

应用场景：设计表时，用于记录数据的字段，因需求变更被遗弃，所以需要删除此字段以减少表空间的占用与提高效率。

语法：alter table 表名 drop column 字段;

例子：alter table client drop column name;

字段添加注释

应用场景：数据库新增字段添加注释。

语法：comment on column 表名.字段 is '注释';

例子：comment on column client.name is '姓名';

树形查询

应用场景：部门表需要明确看出层级关系及相关部门下有那些下级部门。

语法：select * from 表名 start with 树形结构根节点 connect by prior 关联条件;
例子：select * from department t start with t.parent_deptcode=100 connect by prior t.dept_code = t.parent_deptcode;

通过时间段最为过滤条件查询数据

应用场景：查询本月某段时间内的新增客户记录

语法：select * from 表名 where 时间字段a >= 指定时间a and 时间字段a < 指定时间b;

例子：select * from client t where t.fcreatetime > to_date('2013-01-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss') and t.fcreatetime < to_date('2013-11-23 23:59:59', 'yyyy-MM-dd HH24:mi:ss');

删除重复项

应用场景：字典项产生重复数据，造成重复字典项。

语法：delete from 表名a a where rowid !=(select max(rowid) from 表名a b where 判等条件)

例子：delete from CITY a where rowid !=(select max(rowid) from CITY b where a.code = b.code and a.parentcode = b.parentcode);

注：本sql也可以用于查询。

分组查询最大

应用场景：查询某班学生每个人考试最高分数。

语法：select * from 表名 s where s.fraction = (select max(fraction) from 表名 st where st.id = dr.id);

例子：select * from students s where s.fraction = (select max(fraction) from students st where st.id = dr.id);

查询存在则更新数据不存在则插入数据

应用场景：清洗数据。

语法：merge into 表名 c using (select 插入/更新数据 from dual) a on (判等条件)

when matched then

update set 变更项 where 判等条件

when not matched then

insert values (插入数据值);

例子：merge into DEPARTMENT c

using (select '4388' DEPT_CODE, '100' PARENT_DEPTCODE from dual) x

on (c.DEPT_CODE = x.DEPT_CODE and c.PARENT_DEPTCODE = x.PARENT_DEPTCODE)

when matched then

update DEPT_CODE='4388' , DEPT_NAME='租赁部', PARENT_DEPTCODE=’100‘;

when not matched then

INSERT (DEPT_CODE, DEPT_NAME, PARENT_DEPTCODE) VALUES('4388', '租赁部', '100');

oracle case when then else end, orcale的if else 函数

应用场景：当需要根据数据库值去选择返回的值。

语法：case

when 条件1 then 返回值1

when 条件2 then返回值2

else 返回值3

end;

例子：select case

when t.sex = 0 then 男

when t.sex = 1 then 女

else 人妖

end sex

from client;

oracle decode函数

语法：decode(字段a, value1, then1, value2, then2, then3) 如果字段a=value1,就会返回结果为then1，如果字段a=value2，就会返回结果为then2，如果字段a既不等于value1也不等于value2，则返回结果为then3

例子：select decode(sex, 0, 男, 1, 女, 人妖) from client;

注：decode函数和case when then else end实现的功能是一样的，根据笔者的测试发现decode函数的效率比case when then else end略低.

oracle sign函数

语法：sign(number)

例子：select sign(100), sign(0), sign(-100) from dual; 返回结果为 1 0 -1

如果number > 0 则返回1 如果number = 0 则返回 0 如果number < 0 则返回 -1

oracle round函数

语法：round(number)

例子：select round(2.6) from dual; 结果为：3

round()详细大家一看就知道此函数是四舍五入函数，就不多说了。

oracle dbms_random.value() (随机数函数)

语法： dbms_random.value(1, 2)

例子：select dbms_random.value(1, 2) from dual;

dbms_random.value(1, 2) 返回1到2之间38位精度随机数

oracle 获取UUID

语法： sys_guid()

例子：select sys_guid() from dual; 返回结果为32位UUID

oracle || 连接符

例子： select 'a' || 'b' from dual; 返回结果为ab.

0 0