《Oracle数据库操作基本语法(共6页).doc》由会员分享，可在线阅读，更多相关《Oracle数据库操作基本语法(共6页).doc（6页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选优质文档-倾情为你奉上数据库执行是从左到右执行的创建表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 dr
2、op 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 st
3、udent 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
4、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. 保存还原点SQLsav
5、epoint 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,j
6、ob列名 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;使用算数表达式SQLs
7、elect 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
8、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);/查询一个条件的多个情况的批量处理查询某个数
9、据行的某列为空的数据的相关数据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;/组
10、合排序【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(s
11、elect 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),deptn
12、o 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.dna
13、me,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 b
14、y 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 en
15、ame=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的所有员工的工资高的员工的姓名、工资和
16、部门号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 )
17、 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
18、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
19、select ename,sal,job from emp where job=MANAGER;/ union(求并集)事务SQLcommit;/事务（第一次创建，第二次提交）当退出数据库时，系统自动提交事务SQLsavepoint a1;/创建保存点（保存点的个数没有限制）SQLrollback to aa; /使用保存点回滚到aaSQLrollback；/回滚到事务创建开始只读事务SQLset transaction read only Java中的事务Ct.setAutoCommit(false);/设置事务自动提交为否Cmit();/提交事务字符函数lower(char)将字符串转换为小写的格式upper(char)将字符串装换为大写的格式length(char)返回字符串的长度substr(char,m,n)取字符串的子串SQLselect lower(ename) from emp;SQLselect ename from emp where length(ename)=5；专心-专注-专业