Oracle数据库操作基本语法

1、精品文档数据库执行是从左到右执行的创建表SQLcreate table classes( classId number(2), cname varchar2(40)，birthday date);添加一个字段SQLalter table student add (classId number(2);修改字段长度SQLalter table student modify(xm varchar2(30);修改字段的类型/或是名字(不能有数据)SQLalter table stude nt modify(xm char(30);删除一个字段SQLalter table student drop co

2、lumn 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;/临时生效， 重启后不起错用SQLinsertintostudentvalues(001,salina, 女,to_date(01-5-05,yyyy-mm-dd)，10)；SQLinsertintostudentvalues

3、(001,salina, 女,to_date(01/5-05,yyyy/mm/dd)，10)；插入部分字段SQLinsert into student (xh,xm,sex) values(插入空值1欢迎。下载精品文档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;查询

4、空值/(非空)的数据SQLselect * from student where brithday is null(/not null) 修改（更新）数据SQLupdate stude nt set sal=sql/2 where sex =男 更改多项数据SQL update emp set (job,sal,comm)=(selectjob,sal,comm from emp whereename=SMITH) where ename=SCOTT;删除数据1.保存还原点SQLsavepoint aa;2.删除数据【1】SQLdelete from student; /删除表的数据【2】SQ

5、Ldrop 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;为表格添加大的数据行（用于测试反应时间）SQ

6、Linsert into users （userid,username,userpss） select * from users;统计表内有多少条记录2欢迎。下载精品文档SQLselect count（*） from users;屏蔽列内相同数据SQLselect distinct deptno,job from emp;查询指定列的某个数据相关的数据SQLselect dept no ,job,sal from emp where en ame=smith使用算数表达式SQLselect sal*12 from emp;使用类的别名SQLselect ename“姓名 ”，sal*12 as

7、“年收入 ”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 w

8、here sal=2000 and salselect * from emp order by salLike操作符： %、_SQLselect ename,sal from emp where ename like一个字符为S的员工的信息（工资） 】SQLselect ename,sal from emp where ename like第三个字符为O的员工的信息（工资）】批量查询SQLselect * from emp where in（123,456,789）;处理查询某个数据行的某列为空的数据的相关数据SQL select * from emp where mgr is null;条件

9、组合查询（与、或）SQLselect * from emp where （sal500 or job=S%;/第一个字符【名字第_O%;/其它字符【名字/查询一个条件的多个情况的批量MANAGER） and ename likeJ%;asc); /从低到高默认3欢迎。下载精品文档【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)

10、;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

11、.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),deptnofromemp groupbydeptnohavingavg(sal)2000;SQL select avg (sal)

12、,max(sal),deptnofromemp groupbydeptnohavingavg(sal)2000 order by avg(sal);多表查询笛卡尔集:规定多表查询的条件是至少不能少于：SQL selecta1.ename,a1.sal,a2.dnamea1.deptno=a2.deptno;SQL selecta1.dname,a2.ename,a2.sala1.deptno=a2.deptno and a1.deptno=10; /工资SQL select a1.ename,a1.sal,a2.dname from emp a1,dept a1.deptno=a2.deptn

13、o orderby a1.deptno;/多表排序SQLselectworker.ename,boss.enamefromempworker,empworker.mgr=boss.empno; /自连接(多表查询的特殊情况)SQLselectworker.ename,boss.enamefrom empworker,emp worker.mgr=boss.empno and worker.ename=FORD;表的个数-1fromempa1,depta2wherefromdepta1,emp a2where显示部门编号为10的部门名、员工名和SQLselecta1.ename,a1.sal,a

14、2.gradebetween a2.losal and a2.hisal;from emp a1,salgrade a2 where a1.sala2 whereboss whereboss where4欢迎。下载精品文档子查询SQL select * from emp where deptno=(select deptno from emp where ename=SMITH);SQL select distinct job from emp where deptno=10;SQLselect * from empwhere job in (select distinct job from

15、empwhere deptno=10);/如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号。from emp where salall (select sal from emp where30的所有员工的工资高的员工的姓名、工资和部门号fromempwhere sal(select max(sal) from empwhere deptno=30);SQL select * from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH);内嵌视图/当在from子句中使用子查询的时候，必须给子查询指

16、定别名SQLselect a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) (as ) mysalfrom emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sala1.mysal;分页SQL select a1.*,row num rn from (select * from emp) a1;/orcle为表分酉己的行号SQL select * from (select a1.*,rownum rn from (select * from

17、 emp) a1 where rownum=6;/查询内容的变化1.所有的改动(指定查询列)只需更改最里面的子查询2.(排序)只需更改最里面的子查询子查询(用查询结果创建新表)SQL create table mytable (id,name,sal,job,deptno)as selectempno,ename,sal,job,deptno from emp;合并查询union(求并集), union all , intersect(取交集), minus(差集)SQL select ename,sal,job from emp where sal2500;SQL select ename,

18、sal,job from emp where job=MANAGER;SQLselect ename,sal,job from empwhere sal2500 union select ename,sal,job from emp wherejob=MANAGER; / union(求并集)5欢迎。下载SQL select ename ,sal,deptnodeptno=30);/如何查询工资比部门SQLselect ename,sal,deptno只读事务SQLset tran sact ion read onlyJava中的事务Ct.setAutoCommit(false); /设置事务自动提交为否Cmit();/提交事务字符函数lower(char)将字符串转换为小写的格式upper(char)将字符串装换为大写的格式len gth(char)返回字符串的长度substr(char,m, n)取字符串的子串SQLselect l