oracle数据库_sql学习笔记

oracle-9isql(structuredquerylanguage,结构化查询语言)SQL语言按照功能可分为4大类*DQL(数据查询语言):查询数据*DDL(数据定义语言):建立/删除和修改数据对象*DML(数据操纵语言):完成数据操作的命令,包括查询*DCL(数据控制语言):控制对数据库的访问,服务器的关闭/启动等在Oracle9i中为使用SQL语言提供了2个主要工具*[SQLPlus]*[SQLPlusWorksheet]select*fromscott.emp--用户名.数据表的形式selectdistinctjobfromscott.emp--distinct保留字指在显示时去除相同的记录,selectempno,ename,jobfromscott.empwherejob='MANAGER'selectempno,ename,salfromscott.empwheresal=2500--等于select*fromscott.empwherejob='MANAGER'select*fromscott.empwheresal=1100--不等于select*fromscott.empwherejob!='MANAGER'select*fromscott.empwheresal!=1100select*fromscott.empwherejob^='MANAGER'select*fromscott.empwheresal^=1100select*fromscott.empwherejob'MANAGER'select*fromscott.empwheresal1100--小于select*fromscott.empwherejob'MANAGER'select*fromscott.empwheresal2000--大于select*fromscott.empwherejob'MANAGER'select*fromscott.empwheresal2000--int列表select*fromscott.empwheresalin(2000,1000,3000)select*fromscott.empwherejobin('MANAGER','CLERK')select*fromscott.empwheresalnotin(2000,1000,3000)select*fromscott.empwherejobnotin('MANAGER','CLERK')--betweenselect*fromscott.empwheresalnotbetween2000and3000select*fromscott.empwherejobnotbetween'MANAGER'and'CLERK'--likeselect*fromscott.empwherejoblike'%M%'--代表包含M的字符串select*fromscott.empwherejoblike'%M'--代表以M字符结尾的字符串select*fromscott.empwherejoblike'M%'--代表以M字符开头的字符串select*fromscott.empwherejoblike'M_'--代表M开头的长度为2的字符串selectempno,ename,jobfromscott.empwherejob='CLERK'orsal=2000notjob='CLERK'等价于job'CLERK'--逻辑比较符and(与)select*fromscott.empwherejob='MANAGER'andsal2000or(或)select*fromscott.empwherejob！='MANAGER'orsal2000not(菲)select*fromscott.empwherenotjob='MANAGER'--排序查询selectempno,ename,job,salfromscott.empwherejob='CLERK'orderbyjobasc,saldesc--分组查询selectempno,ename,job,salfromscott.empgroupbyjob,empno,ename,salhavingsal=2000selectempno,ename,job,salfromscott.empwheresal=2000groupbyjob,empno,ename,sal/*where检查每条记录是否符合条件,having是检查分组后的各组是否满足条件.having语句只能配合groupby语句使用,没有groupby时不能使用having,但可使用where*/selectempno,ename,sal,mgr,sal+mgrfromscott.emp--常见'+''-''*''/'selectempno编号,ename姓名,job工作,sal薪水fromscott.emp--无条件多表查询笛卡尔积的方式组合起来selectemp.empno,emp.ename,emp.deptno,dept.dname,dept.locfromscott.emp,scott.dept--等值多表查询selectemp.empno,emp.ename,emp.deptno,dept.dname,dept.locfromscott.emp,scott.deptwherescott.emp.deptno=scott.dept.deptno--具有相同的属性(相同的数据类型/宽度和取值范围)--非等值多表查询selectemp.empno,emp.ename,emp.deptno,dept.dname,dept.locfromscott.emp,scott.deptwherescott.emp.deptno!=scott.dept.deptnoandscott.emp.deptno=10--嵌套查询子查询可以嵌套多层,子查询操作的数据表可以是父查询不操作的数据表,子查询中不能有orderby分组语句selectemp.empno,emp.ename,emp.job,emp.salfromscott.empwheresal=(selectsalfromscott.empwhereename='WARD')--查询薪水=WARD薪水的员工selectemp.empno,emp.ename,emp.job,emp.salfromscott.empwheresalin(selectsalfromscott.empwhereename='WARD')--查询薪水和WARD相等的员工带any查询selectemp.empno,emp.ename,emp.job,emp.salfromscott.empwheresalany(selectsalfromscott.empwherejob='MANAGER')/*selectsalfromscott.empwherejob='MANAGER'--查询到3各薪水值2975/2850/2450selectemp.empno,emp.ename,emp.job,emp.salfromscott.empwheresal2975orsal2850orsal2450--执行any查询条件语句*/带some查询selectemp.empno,emp.ename,emp.job,emp.salfromscott.empwheresal=some(selectsalfromscott.empwherejob='MANAGER')/*selectsalfromscott.empwherejob='MANAGER'--查询到3各薪水值2975/2850/2450selectemp.empno,emp.ename,emp.job,emp.salfromscott.empwheresal=2975orsal=2850orsal=2450--执行some查询条件语句*/带all查询selectemp.empno,emp.ename,emp.job,emp.salfromscott.empwheresalall(selectsalfromscott.empwherejob='MANAGER')/*selectsalfromscott.empwherejob='MANAGER'--查询到3各薪水值2975/2850/2450selectemp.empno,emp.ename,emp.job,emp.salfromscott.empwheresal2975andsal2850andsal2450--执行all查询条件语句*/带exists查询selectemp.empno,emp.ename,emp.job,emp.salfromscott.emp,scott.deptwhereexists(select*fromscott.empwherescott.emp.deptno=scott.dept.deptno)并操作查询(selectdeptnofromscott.emp)union(selectdeptnofromscott.dept)交操作查询(selectdeptnofromscott.emp)intersect(selectdeptnofromscott.dept)差操作查询--属于A且不属于B(selectdeptnofromscott.emp)minus(selectdeptnofromscott.dept)--用SQL进行函数查询[ceil]函数:ceil(n),取大于等于数值n的最小整数selectmgr,mgt/100,ceil(mgr/100)fromscott.emp[floor]函数:floor(n),取小于等于数值n的最大整数selectmgr,mgt/100,floor(mgr/100)fromscott.emp[mod]函数:mod(m,n),取m整除n后的余数selectmgr,mod(mgr,1000),mod(mgr,100),mod(mgr,10)fromscott.emp[power]函数:power(m,n),取m的n次方selectmgr,power(mgr,2),power(mgr,3)fromscott.emp[round]函数:round(m,n),四舍五入,保留n位selectmgr,round(mgr/100,2),round(mgr/1000,2)fromscott.emp[sign]函数:sign(n),n0,取1;n=0,取0;n0,取-1selectmgr,mgr-7800,sign(mgr-7800)fromscott.emp[avg]函数:avg(字段名),求平均值,要求字段为数据值selectavg(mgr)平均薪水fromscott.emp[count]函数:count(字段名)或count(*),统计总数selectcount(*)记录总数fromscott.empselectcount(distinctjob)工作类别总数fromscott.emp[min]函数:min(字段名),计算数值型字段最小数selectmin(sal)最少薪水fromscott.emp[max]函数:max(字段名),计算数值型字段最大数selectmax(sal)最高薪水fromscott.emp[sum]函数:sum(字段名),计算数值型字段总和selectsum(sal)薪水总和fromscott.emp--记录的录入1.单行记录的录入insertinto数据表valuesinsertintoscott.emp(empno,ename,hiredate)values(8000,'JONE','25-11月-2003');2.多行记录的录入insertinto数据表(selectfrom数据表where条件)insertintos