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)