Oracle 子查询等常见的sql语句

oracle 对于汉字，如果数据库字符编码为 GBK 占用2个字节，如果是UTF-8则占用3个字节。
1.建表

create table student();
create table class(id number primary key,name varchar(20));

2.添加字段

alter table student add （id numner,stuname varchar(30),sal number(5,2),birthday date）;

3.修改字段类型

alter table student modify (stuname varchar(50));

4.删除一个字段

alter table student drop column sal;

5.修改表名

rename student to students;

5.5 修改字段名

alter table students rename column stuname to sname;

6.添加数据

insert into students values(1,'S001');
insert into students(id,stuname) values(2,'S002');

7.插入空值

insert into students(id,name,birthday) values(3,'S003',null);

8.插入日期字段 使用to_date

insert into students values(4,'S004',to_date('2012-08-22 22:18:16','yyyy-mm-dd hh24:mi:ss'));

insert into students values(5,'S005',to_date('2012/08/22 22:18:16','yyyy/mm/dd hh24:mi:ss'));

9.设置字段为空

update students set birthday=null where id=1;

10.oracle中的默认日期格式是 dd-MON-yy

11.修改回话中的日期的默认格式

alter session set nls_date_format='yyyy-mm-dd';
alter session set nls_date_format='dd-MON-yy';

12.采用 delete 删除数据库后，可以采用rollback；回滚，要立刻回滚中间不能有操作，否则要用回滚点。

13.delete 删除数据 表结构在，需要写日志，可以恢复，数度慢。

14.truncate 删除数据 表结构还在，不需要写日志，不可以恢复记录，速度快

15.创建保存点

save point aa： 创建后不要 使用 commit 和exit 语句，否则保存点会释放。

16.回滚到保存点（操作失误）

rollback to aa； 可以创建多个保存点

17.打开/关闭计时器（查看执行sql语句执行的时间）

set timing on;
set timing off;

18.一次性插入多行(将一个表中的数据复制给自己)

insert into students（id,stuname,birthday） select * from students;

19.select * 和 select column 对于大量数据，会有明显效果。

20.取消重复行 使用  distinct (可以使用 group by 替换)

21.oracle 区分字符串中的大小写。

22.在oralce 中使用 算术运算符  +-*/

   select deptno,job,sal*13 from scott.emp where ename='SMITH';

23.给字段起别名 采用 “”、不是 ‘’

select ename,sal*13 "年薪" from scott.emp;

24 nvl（避免null参与运算，如果null 参与运整个表达式的值为null）

//查询 年薪和奖金  nvl(comm,0) 如果 comm 为null 就用0替换
select enmae,(sal+nvl(comm,0))*13 "年薪" from emp；

25.查询日期字段在oralce中查询时间默认使用 dd-MON-yy

select ename,hiredate from scott.emp where hiredate>'1-1月-1982'；
select ename,hiredate from scott.emp where hiredate=to_date('1982-1-1','yyyy-mm-dd');

26.and 查询的时候添加多个条件

27.like 模糊匹配

like:表示0个或者多个任意字符
_:表示单个任意字符

28.查询首字母“S“的员工

select * from emp where ename like 'S%';

29.查询第三个字符为 I 的员工

 select * from emp where ename like '__I%'；//前有两个下划线

30.采用 in 比 or 的效率高，in 是批量处理

31.查询 工资高于 500 或者 岗位为 MANAGEER 的雇员，同时还要姓名首写字母大写的J

select * from scott.emp where ename like 'J%' and (job='MANAGER' or sal>500);

32.升序 asc  降序 desc

select * from emp order by id [asc],deptno,sal desc; //多列降序用 逗号分割

33.采用别名排序（使用 列的别名 排序 查询每个人的年工资并降序 重点 注意 有“” 和没有“” 的区别有的时候 采用别名排序无效）

错误:select ename,(sal+nvl(comm,0))*13 "年薪" from scott.emp order by 年薪 desc;
正确：select ename,(sal+nvl(comm,0))*13 ysal from scott.emp order by ysal  desc;

34.使用别名的时候 汉字要用“ ” 英文不需要引号。

35.数据的分组 max,min,avg,sum(求和)，count(记录数)。注意和 别名一起使用的使用 不要用“”。

36.查询最高工资和最低工资

 select max(sal),min(sal) from emp;

37.查询 每个部门，每个岗位上的平均工资，和最高工资

select max(sal),avg(sal) from scott.emp group by deptno,job 

38.多个分组后的 排序 如果不是分组中的列，采用“别名”排序没有效果，此时可以采用 ”原始列“来排序

排序无效：select deptno,job,max(sal),avg(sal) "avgSal" from emp group by deptno,job order by 'avgSal' desc;
排序有效：
1：select deptno,job,max(sal),avg(sal) from emp group by deptno,job order by avg(sal) desc; //使用 "原始列"
2:select deptno,job,max(sal),avg(sal) ss from emp group by deptno,job order by ss desc;

39.group by having的使用（对分组后的列进行筛选）

select deptno,avg(sal) from emp group by deptno  having acg(sal)>2000

40.多表连接查询注意 "笛卡尔集" 会每个表的数据 “相乘”；

41.between and 的使用(不仅仅是两个值之间的范围 还可以是一张含有 两个范围字段和其他多个附加字段的表)  min<=x<max

//查询薪水在 300-2000的员工
select * from emp where sal between 300 and 2000

//根据员工的薪水查询出员工的薪水等级
select enam,e.sal,g.grade from emp e salgrade g where e.sal between g.losal and g.hisal; //注 salgrade 是薪水等级表。

42.采用多连接查询 时排序

select e.ename,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno order by d.deptno

43.自连接：同一个表中连接查询。(查询每个员工的上级)

select worker.empno,worker.ename,boss.ename from emp worker,emp boss where workder.mgr=boss.empno;

44.单行单列子查询 (查询和SMITH同一部门的所有员工)

select * from e where deptno=(select deptno from emp where ename='SMITH');

45.多行单列子查询 （查询 工作岗位在 10号部门的工作岗位的类型中的员工信息）

select * from emp where e.job in(select distinct job from emp where deptno=10);

46.单行多列子查询 （查询和SMITH部门和岗位相同的的人 ）

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

47.多行多列子查询

select * from emp where (deptno,job) in (select deptno,job from emm2);

47.5 采用子查询 更新数据

1.笨方法   update emp  set job=(select job from emp where enmae='SMITH'),sal=(select sal from emp wehre ename='SMITH'),comm=(select comm from emp where ename='SMITH') where ename='SCOTT';
  2.好方法  update emp set (job,sal,comm)=(select job,sal,comm from emp where='SMITH') where ename=‘SCOTT’

48.all 的使用(注意用max替换) （查询工资比 部门30所有工资都高的人）

效率低：select * from emp where sal>all(select sal from emp where 
deptno=30);
效率高：select * from emp where sal>(selec max(sal) from emp where deptno=30)

49.any的使用（注意用 min替换）（查询工资比30部门任意工资高的人）

效率低：select * from emp where sal>any(select sal from emp where deptno=30);
效率高：selet * from emp where sal>(select min(sal) from emp where deptno=30);

50.查询工资大于该个部门的平均工资的员工

1.select * from emp e,(select deptno,avg(sal) mysal from emp group by deptno) ee where e.deptno=ee.deptno and e.sal>ee.mysal;

2.select * from emp e where e.sal>(select avg(sal) from emp ee where e.deptno=ee.deptno group by ee.deptno);

51.查询工资大于每个部门平均工资的员工

1:select * from emp where sal>(select max(avg(sal)) from emp group by deptno);
2.select * from emp where sal>all(select avg(sal) from emp group by deptno);

52.在from 子句中使用子查询，这个子查询会作为一个视图来对待，因此也叫内嵌视图，在使用的时候必须给别名 （上面的ee" 就是）

53.给列取别名的时候 可以选用 as  给表取别名的时候 不能用“as”

54.如果在查询的时候 给列的别名 有“ ”引号，在后面的查询中不好参与 条件的控制

(错误)select * from emp e,(select deptno,avg(sal) "mySal" from emp group by deptno) ee where ee.deptno=e.deptno and e.sal> 'mySal'
（正确) select * from emp e,(select deptno,avg(sal) mySal from emp group by deptno) ee
where ee.deptno=e.deptno and e.sal> mySal;

55.利用结果集来创建一张新表(对于想要操作表中的数据，又怕损坏表中的数据和安全性能)

create table emp4 (id,ename,sal) as select empno,ename,sal from emp;

56.多个结果集之间的操作

union:取得两个结果集的并集，去掉重复的行
union all：取得两个结果集的并集，不会去掉重复行
intersect:取交集
minus:取差集

union ：select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER'

union all select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='MANAGER'

intersect: select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='MANAGER'

57.采用 union ，union all, intersect,minus 比 and in or any all 的效率高

58.to_date 将 字符串 转换为  日期类型 主要用于插入 date类型字段

insert into emp values(9996,'小红2','MANAGER',7782,to_date('1988/11/11','yyyy/mm/dd'),79.9,55.33,10);
        select * from bi_message where createtime=to_date('2012-8-22 18:17:57','yyyy-mm-dd hh24:mi:ss');

59.to_char 将日期转换为 字符类型 主要用于查询

select * from emp where to_char(hiredate,'yyyy')='1980';
    select * from emp where to_char(hiredate,'mm')=12;

60.where 条件后面不能用sum，max,min,avg 函数
   having 后面可以用
   order by 后面可以用

61.查询 某条记录的相邻两条记录

--后一条
select * from (select * from bi_baoliao b where b.starttime < (select starttime from bi_baoliao where id=3)  order by b.starttime desc) where  rownum=1 union all
--前一条
select * from (select * from bi_baoliao b where b.starttime > (select starttime from bi_baoliao where id=3)  order by b.starttime asc) where  rownum=1

62 获取表里面的所有列名

select WM_CONCAT(tc.COLUMN_NAME) from user_tab_columns tc where table_name='BI_BAOLIAO'

63.获取表中的所有列名,并按建立列的顺序排序

select WM_CONCAT(COLUMN_NAME) from (select COLUMN_NAME from user_tab_columns tc where table_name='BI_BAOLIAO'  order by COLUMN_ID asc)

64.修改 表中的字段可以为 null

 alter table fbb_manager_fun modify description null

65.树形 tree 结构查询

 
   select f.*,level from fbb_manager_fun f start with parentid is null connect by prior id=parentid ORDER SIBLINGS BY sortid;

66.转义

  select 'update bi_user_cashcard a set sendmoney='||sum(money+nsmoney-   charge+gift)||'  where id='||usercardid from bi_user_cashapply b where status='2' g roup by usercardid

67.varchar2 字段排序

//前提 attributevalue  必须从字面上能够转换成 int 否则会出错
 select * from fbb_user u order by cast(u.attributevalue as int) 

68.查询 oralce 中的关键字

select * from v$reserved_words; 

select "COMMENT" from bi_tb_psubject

--如果非要用关键字,可以加 "关键字列" 进行查询 和插入 等操作
insert into bi_tb_psubject (ID,TITLE,SUBTITLE,"COMMENT",PICTURE,INNERPIC,STYPE,STARTTIME,ENDTIME,CREATETIME,AUTHORNAME,STATUS)
values (BI_SUPER_SEQUENCE.Nextval,'月亮代表我的心','月亮惹的祸','你闯祸没','pp','admin','1',sysdate-1,sysdate,sysdate,'luob','1');

--另外,如果 采用类是 ibatis 等xml配置文件 由于在sql中加上了 ""因此 要用 
<![CDATA[  sql ]]>   进行 声明

69:oralce update groupby count 分组统计更新

update fbb_bagitem t1 set t1.recordtime = (select count(c.id) from fbb_bagitem m,fbb_bagitem_recommend r where m.id=r.itemid and t1.id=m.id group by m.id) 

update fbb_bagitem t1 set t1.fantime = (select count(c.id) from fbb_bagitem m,fbb_bagitem_comment c where m.id=c.itemid and t1.id=m.id group by m.id)