Oracle数据库操作基本语法.doc

精品文档数据库执行是从左到右执行的创建表SQLcreate table classes(classId number(2),cname varchar2(40)，birthday date);添加一个字段SQLalter table studentadd (classId number(2);修改字段长度SQLalter table student modify(xm varchar2(30);修改字段的类型/或是名字（不能有数据）SQLalter table student modify(xm char(30);删除一个字段SQLalter table student drop column sal;修改表的名字SQLrename student to stu;删除表SQLdrop table student;插入所有字段数据SQLinsert into student values (001,salina,女,01-5月-05，10)；修改日期输入格式SQLalter session set nls_date_format = yyyy-mm-dd;/临时生效，重启后不起错用SQLinsert into student values (001,salina,女,to_date(01-5 -05,yyyy-mm-dd)，10)；SQLinsert into student values (001,salina,女,to_date(01/5 -05,yyyy/mm/dd)，10)；插入部分字段SQLinsert into student (xh,xm,sex所要插入列的列名) values(001,lison,女所要插入列的内容);插入空值SQLinsert into student (xh,xm,sex,birthday) values(021,BLYK,男,null);一条插入语句可以插入多行数据SQL insert into kkk (Myid,myname,mydept) select empno ,ename,deptno from emp where deptno=10;查询空值/(非空)的数据SQLselect * from student where brithday is null（/not null）;修改（更新）数据SQLupdate student set sal=sql/2（如果还有，则加“，”以此类推） where sex =男（如果是空的，则用“is null”）；更改多项数据SQL update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=SMITH) where ename=SCOTT;删除数据1. 保存还原点SQLsavepoint aa保存点的名称;2. 删除数据【1】 SQLdelete from student; /删除表的数据【2】 SQLdrop table student; /删除表的结构和数据【3】 SQLdelete from student where xh=001; /删除一条记录【4】 SQLtruncate table student; /删除表中的所有记录，表结构还在，不写日志，无法扎找回的记录，速度快查看表结构SQLdesc student;查询指定列SQLselect sex,xh,xm from student;如何取消重复SQLselect distinct deptno,job列名 from student表名;打开显示操作时间的开关SQLset timing on;为表格添加大的数据行（用于测试反应时间）SQLinsert into users (userid,username,userpss) select * from users;统计表内有多少条记录SQLselect count(*) from users;屏蔽列内相同数据SQLselect distinct deptno,job from emp;查询指定列的某个数据相关的数据SQLselect deptno,job,sal from emp where ename=smith;使用算数表达式SQLselect sal*12 from emp; 使用类的别名SQLselect ename “姓名”，sal*12 as “年收入” from emp；处理null(空)值SQLselect sal*13+nvl(comm,0)*13 “年工资”，ename,comm from emp;连接字符串（|）SQLselect ename | is a | job from emp;Where子句的使用【1】SQLselect ename,sal from emp where sal3000;/number的范围确定【2】SQLselect ename,hiredate from emp where hiredate1-1月-1982;/日期格式的范围确定【3】SQLselect ename,sal from emp where sal=2000 and salselect ename,sal from emp where ename like S%;/第一个字符【名字第一个字符为S的员工的信息（工资）】SQLselect ename,sal from emp where ename like _O%;/其它字符【名字第三个字符为O的员工的信息（工资）】批量查询SQLselect * from emp where in(123,456,789);/查询一个条件的多个情况的批量处理查询某个数据行的某列为空的数据的相关数据SQL select * from emp where mgr is null;条件组合查询（与、或）SQLselect * from emp where (sal500 or job=MANAGER) and ename like J%;Order by 排序【1】SQLselect * from emp order by sal （asc）;/从低到高默认【2】SQLselect * from emp order by sal desc;/从高到低【3】SQLselect * from emp order by deptno (asc),sal desc;/组合排序【4】SQLselect ename,sal*12 “年薪” from emp order by “年薪” (asc);SQL select ename,(sal+nvl(comm,0)*12 as 年薪 from emp order by 年薪;资料分组（max、min、avg、sum、count）SQLselect max(sal),min(sal) from emp;SQLselect ename,sal from emp where sal=(select max(sal) from emp); /子查询，组合查询SQL select * from emp where sal(select avg(sal) from emp); /子查询，组合查询SQL update emp set sal=sal*1.1 where sal(select avg(sal) from emp) and hiredateselect avg(sal),max(sal),deptno from emp group by deptno;/显示每个部门的平均工资和最低工资SQLselect avg(sal),max(sal),deptno from emp group by deptno;/显示每个部门的平均工资和最低工资SQL select avg (sal),max(sal),deptno from emp group by deptno having avg(sal)2000;SQL select avg (sal),max(sal),deptno from emp group by deptno having avg(sal)2000 order by avg(sal);多表查询笛卡尔集:规定多表查询的条件是至少不能少于：表的个数-1SQL select a1.ename,a1.sal,a2.dname from emp a1,dept a2用于区别多个表中的列名 where a1.deptno=a2.deptno多表查询中多表的关联符;SQL select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10; /显示部门编号为10的部门名、员工名和工资SQL select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between某个条件 a2.losal and a2.hisal;SQL select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;/多表排序SQL select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;/自连接（多表查询的特殊情况）SQL select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=FORD;子查询SQL select * from emp where deptno=(select deptno from emp where ename=SMITH);SQL select distinct过滤关键词 job from emp where deptno=10;SQL select * from emp where job in (select distinct job from emp where deptno=10);/ 如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号。SQL select ename ,sal,deptno from emp where salall (select sal from emp where deptno=30);/如何查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门号SQL select ename ,sal,deptno from emp where sal(select max(sal) from emp where deptno=30);SQL select * from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH);内嵌视图/当在from子句中使用子查询的时候，必须给子查询指定别名SQLselect a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) (as ) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sala1.mysal;分页select a1.*,rownum rn from (select * from emp) a1;/orcle为表分配的行号SQL select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum=6;/查询内容的变化1. 所有的改动（指定查询列）只需更改最里面的子查询2. （排序）只需更改最里面的子查询子查询（用查询结果创建新表）SQL create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;合并查询union(求并集), union all , intersect（取交集）, minus （差集）SQL select ename,sal,job from emp where sal2500;SQL select ename,sal,job from emp where job=MANAGER;SQL select ename,sal,job from emp where sal2500 union select ename,sal,job from emp where job=MANAGER;/ union(求并集)Java连接数据库事务SQLcommit;/事务（第一次创建，第二次提交）当退出数据库时，系统自动提交事务SQLsavepoint a1;/创建保存点（保存点的个数没有限制）SQLrollback to aa; /使用保存点回滚到aaSQLrollback；/回滚到事务创建开始只读事务SQLset transaction read