racle数据库sql学习笔记

1、oracle-9i sql（structuredquerylanguage,结构化查询语言）SQL语言按照功能可分为4大类*DQL（数据查询语言）:查询*DDL（数据定义语言）建立/删除和修改费对象*DML（数据操纵语言）:完成操作的命令，包括查询*DCL（数据控制语言）控制对数据库的访问，服务器的关闭/启动等在0acle9i中为使用SQL语言提供了 2个主要工具*SQLPIus*SQLPIusWorksheet select*fomscott.emp-用户名.数据君的形式selectdistinctjobfromscott.emp-distinct保留字指在显示时去除相同的记录,select

2、empno/enamejobfromscottempwherejob=MANAGERselectempnofenamersalfromscott.empwheresal=2500-等于select*fromscott.empwherejob=,MANAGER,select*fromscott.empwheresal=1100-不等于select*fromscott.empwherejob!=,MAN AGER1select*fromscott.empwheresal! = 1100select*fromscott.empwherejobA=,MANAGERselect*fromscott.em

3、pwheresalA = 1100select*fromscott.empwherejobMAN AGERselect*fromscott.empwheresal 1100一小于select*fromscott.empwherejobMANAGER select*fromscott.empwheresalMANAGER select*fromscott.empwheresal2000-int列表select*fromscott.empwheresalin(2000,1000,3000) select*fromscott.empwherejobin(MAN AGER,CLERK) select*

4、fromscott.empwheresalnotin(2000,1000,3000) selecCfromscott.empwherejobnotinCMANAGERJCLERK) -betweenselect*fromscott.empwheresalnotbetween2000and3000selecCfromscott.empwherejobnotbetweenMANAGERandCLERKlikeselect*fromscott.empwherejoblike%M%-代表包含 M 的字符串 select*fromscott.empwherejoblike%M-代表以 M 字符结尾的字符

5、串 selectHromscott.empwherejoblikeM%-代表以 M 字符开头的字符串 selectHromscott.empwherejoblikeM-代表M开头的长度为2的字符串 selectempno,ename,jobfromscottempwherejob = CLERKorsal=2000 notjob二,CLERK割介于joboCLERK 一逻辑比较符and(-)select*fromscott.empwherejob=MANAGERandsal2000 or(或)select*fromscott.empwherejob ! =MANAGERorsal2000 no

6、t(菲)select*fromscott.empwherenotjob=MANAGER一排序查询selectempnorenamejobrsalfromscott.empwherejob = ,CLERK,orderbyjobasc/saldesc 一分组查询selectempnofenamejoblsalfromscott.empgroupbyjob/empnofenamelsalhavingsal=2000 selectempno/enamejobrsalfromscott.empwheresal=(selectsalfomscottempwhereename =WARD)查询薪水=/人区

7、口 薪水的员工selectemp.empno.emp.ename.emp.job.emp.salfromscott.empwheresdlin(selectsalfromscott.empwhereename =WARD) -查询薪水和 WARD 相等的员工带any查询selectemp.empno.emp.ename.emp.job.emp.salfromscott.empwheresalany(selectsalfromscott.empwherejob=lMANAGER,)/*selectsalfromscott.empwherejob:MANAGER查询至ij 3 各薪水值 2975/

8、2850/2450selectemp.empno.emp.ename.emp.job.emp.salfromscott.empwheresal2975orsal2850orsal2450一四亍 any 查询条件语句*/带some查询selectemp.empno.emp.ename.emp.job.emp.salfromscott.empwheresal=some(selectsalfromscott.empwherejob=MAN AGER)/*selectsalfr。mscott.empwherejob:MANAGER-查询到 3 各薪水值 2975/2850/2450selectemp.

9、empno.emp.ename.emp.jobemp.salfromscott.empwheresal=2975orsal=2850orsal=2450期亍 some 查询条件语句*/带all查询selectemp.empno.emp.ename.emp.jobemp.salwheresalall(selectsalfromscott.empwherejob=,MANAGER,)sdectsalfromscott.empwherejob=MANAGER一查询到 3 各薪水值 2975/2850/2450selectemp.empno.emp.ename.emp.job.emp.salfroms

10、cott.empwheresal2975andsal2850andsal2450一执行 all 查询条彳牛语句*/带exists查询selectemp.empno.emp.enamemp.job.emp.salfromscott.emp,scott.deptwhereexists(select*fromscott.empwherescott.emp.deptno=scott.dept.deptno)并操作查询(selectdeptnofromscott.emp)union(selectdeptnofromscott.dept)交操作查询(selectdeptnofromscott.emp)in

11、tersect(selectdeptnofromscott.dept)差操作查询-属于A且不属于B(selectdeptnofromscott.emp)minus(selectdeptnofromscott.dept)-用SQL进行函数查询ceil函数:ceil(n),取大于等于数值n的最小整数 selectmgGmgt/lOOeiKmgr/lOOJfromscott.empfloor函数:floor(n),取小于等于数值n的最大整数selectmgcmgt/lOOJIoorfmgr/lOOJfromscott.empmod函数:mod(m,n),取m整除n后的余数selectmgr/mod(

12、mgr/1000)/mod(mgrl100),mod(mgr,10)fromscott.emppower函数:powe(m,n),取 m 的 n 次方selectmgr;power(mgn2)fpower(mgr3)fromscott.empround函数round(m,n),四舍五入,保留n位 selectmgr,ound(mgr/100,2),round(mgr/1000,2)fromscott.empsign函数:sign(n),n0,取 l;n=0,取 0;n =6999);-表间数据复制createtable语句的功能就是创建新的 createtablescott.testselectdistinctempno.ename.hiredate fromscott.emp whereempno =7000一删除数据 使用delete命令可以删除数据，数据将存储在系统回滚段中,需要的时候可以回滚恢复;不可恢复.使用truncate命令可以删除整表数据但保留结构,deletefrom表where条件deletefromscott.testwhereempno =7500andempno =8000;truncatetablescott.test;-更新数