oracle 对于汉字,如果数据库字符编码为 GBK 占用2个字节,如果是UTF-8则占用3个字节。
1.建表
2.添加字段
3.修改字段类型
4.删除一个字段
5.修改表名
5.5 修改字段名
6.添加数据
7.插入空值
8.插入日期字段 使用to_date
9.设置字段为空
10.oracle中的默认日期格式是 dd-MON-yy
11.修改回话中的日期的默认格式
12.采用 delete 删除数据库后,可以采用rollback;回滚,要立刻回滚中间不能有操作,否则要用回滚点。
13.delete 删除数据 表结构在,需要写日志,可以恢复,数度慢。
14.truncate 删除数据 表结构还在,不需要写日志,不可以恢复记录,速度快
15.创建保存点
16.回滚到保存点(操作失误)
17.打开/关闭计时器(查看执行sql语句执行的时间)
18.一次性插入多行(将一个表中的数据复制给自己)
19.select * 和 select column 对于大量数据,会有明显效果。
20.取消重复行 使用 distinct (可以使用 group by 替换)
21.oracle 区分字符串中的大小写。
22.在oralce 中使用 算术运算符 +-*/
23.给字段起别名 采用 “”、不是 ‘’
24 nvl(避免null参与运算,如果null 参与运整个表达式的值为null)
25.查询日期字段在oralce中查询时间默认使用 dd-MON-yy
26.and 查询的时候添加多个条件
27.like 模糊匹配
28.查询首字母“S“的员工
29.查询第三个字符为 I 的员工
30.采用 in 比 or 的效率高,in 是批量处理
31.查询 工资高于 500 或者 岗位为 MANAGEER 的雇员,同时还要姓名首写字母大写的J
32.升序 asc 降序 desc
33.采用别名排序(使用 列的别名 排序 查询每个人的年工资并降序 重点 注意 有“” 和没有“” 的区别有的时候 采用别名排序无效)
34.使用别名的时候 汉字要用“ ” 英文不需要引号。
35.数据的分组 max,min,avg,sum(求和),count(记录数)。注意和 别名一起使用的使用 不要用“”。
36.查询最高工资和最低工资
37.查询 每个部门,每个岗位上的平均工资,和最高工资
38.多个分组后的 排序 如果不是分组中的列,采用“别名”排序没有效果,此时可以采用 ”原始列“来排序
39.group by having的使用(对分组后的列进行筛选)
40.多表连接查询注意 "笛卡尔集" 会每个表的数据 “相乘”;
41.between and 的使用(不仅仅是两个值之间的范围 还可以是一张含有 两个范围字段和其他多个附加字段的表) min<=x<max
42.采用多连接查询 时排序
43.自连接:同一个表中连接查询。(查询每个员工的上级)
44.单行单列子查询 (查询和SMITH同一部门的所有员工)
45.多行单列子查询 (查询 工作岗位在 10号部门的工作岗位的类型中的员工信息)
46.单行多列子查询 (查询和SMITH部门和岗位相同的的人 )
47.多行多列子查询
47.5 采用子查询 更新数据
48.all 的使用(注意用max替换) (查询工资比 部门30所有工资都高的人)
49.any的使用(注意用 min替换)(查询工资比30部门任意工资高的人)
50.查询工资大于该个部门的平均工资的员工
51.查询工资大于每个部门平均工资的员工
52.在from 子句中使用子查询,这个子查询会作为一个视图来对待,因此也叫内嵌视图,在使用的时候必须给别名 (上面的ee" 就是)
53.给列取别名的时候 可以选用 as 给表取别名的时候 不能用“as”
54.如果在查询的时候 给列的别名 有“ ”引号,在后面的查询中不好参与 条件的控制
55.利用结果集来创建一张新表(对于想要操作表中的数据,又怕损坏表中的数据和安全性能)
56.多个结果集之间的操作
57.采用 union ,union all, intersect,minus 比 and in or any all 的效率高
58.to_date 将 字符串 转换为 日期类型 主要用于插入 date类型字段
59.to_char 将日期转换为 字符类型 主要用于查询
60.where 条件后面不能用sum,max,min,avg 函数
having 后面可以用
order by 后面可以用
61.查询 某条记录的相邻两条记录
62 获取表里面的所有列名
63.获取表中的所有列名,并按建立列的顺序排序
64.修改 表中的字段可以为 null
65.树形 tree 结构查询
66.转义
67.varchar2 字段排序
68.查询 oralce 中的关键字
69:oralce update groupby count 分组统计更新
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)