DQL语句.docx
《DQL语句.docx》由会员分享，可在线阅读，更多相关《DQL语句.docx（35页珍藏版）》请在冰豆网上搜索。
DQL语句
--DQL语句
--查询语句用来检索数据使用
--SELECT子句用来指定要查询的字段,若写'*'则表示查询所有字段
--FROM子句用来指定数据来源的表
SELECTempno,ename,JOB,salFROMemp_gugo;
SELECTmgr,hiredate,commFROMemp_gugo;
--SELECT子句中也允许使用函数或表达式,这样可以将结果查询出来
SELECTename,sal*12,salFROMemp_gugo;
--在DQL中使用where子句也可以只查询出满足条件的记录
SELECTename,JOB,salFROMemp_gugoWHEREJOB='CLERK';
SELECTename,JOB,salFROMemp_gugoWHEREsal>2500;
--字符串函数
--concat（c1,c2）
SELECTconcat（ename,sal）FROMemp_gugo;
SELECTconcat（concat（ename,','）,sal）FROMemp_gugo;
SELECTename||','||salFROMemp_gugo;
--length（c）:
求字符串长度
SELECTename,LENGTH（ename）FROMemp_gugo;
--upper,lower,initcap将字符串转换为全大写,全小写,首字母大写
--dual:
伪表,当查询的内容与表没有任何内容的时候可以在FROM子句中查询该表,会查询出一条记录
--intitcap支持字符内按空格区分每个单词之后首字母大写
SELECTupper（'HelloWorld'）,lower（'HelloWorld'）,initcap（'helloworld'）FROMdual;
--trim去除字符串两边的指定字符,截取集只能有一个字符
--ltrim去除字符串左边的的截取集中指定的的任一字符,直到遇到不符合截取集条件的字符为止,截取集可以不只一个字符
--rtrim去除字符串右边的的截取集中指定的的任一字符,直到遇到不符合截取集条件的字符为止,截取集可以不只一个字符
SELECTtrim（'e'FROM'eeeeeelieieieeele'）FROMdual;
SELECTltrim（'eeeeeelieieieeele','el'）FROMdual;
SELECTrtrim（'eeeeeelieieieeele','el'）FROMdual;
--lpad,rpad补位函数
--允许将指定字符串显示指定位数,不足时补充若干指定字符达到该长度
--lpad从左添加,rpad从右添加,当指定位数小于字符串长度时,从左截取指定位数
--可以达到右对齐/左对齐的效果（指定数较大的时候且使用空格字符）
SELECTename,rpad（sal,5,'!
'）FROMemp_gugo;
SELECTename,lpad（sal,10,'!
'）FROMemp_gugo;
--substr（str,m[,n]）
--将给定字符串从m处开始连续截取n个字符,n不指定时默认取到字符串末尾,n超过可以截取的字符数量也是截取到末尾
--n表示n个字符,不能为负数
--m可以是负数,负数则是从倒数位置开始
--读取顺序是从左往右的
--数据库中的下标是从1开始的
SELECTsubstr（'thinkinginjava',-7,4）FROMdual;
--instr（c1,c2,m,n）
--查看c2在c1中的位置,m用来指定从哪里开始检索,不写默认从第一个字符开始
--n表示第几次出现,不写默认为1
SELECTinstr（'thinkinginjava','i',2,2）FROMdual;
--数值函数
--round（m,n）:
四舍五入
--n为保留到小数点后的位数,若n为负数,则是十位以上的数字,
SELECTround（55.567,1）FROMdual;
SELECTround（33.745,0）FROMdual;
SELECTround（645.333,-3）FROMdual;
--trunc（m,n）截取数字
SELECTtrunc（55.335,1）FROMdual;
SELECTtrunc（564.44,0）FROMdual;
SELECTtrunc（55.678,-1）FROMdual;
--mod（m,n）:
返回m除以n后的余数,n为0则直接返回m
SELECTmod（555,20）FROMdual;
SELECTmod（555,0）FROMdual;
--ceil（n）,floor（n）返回取大于或等于n的最小整数值（向上取整）/小于或等于n的最大整数值（向下取整）
SELECTceil（45.5）FROMdual;
SELECTfloor（-45.333）FROMdual;
--日期相关函数
--和日期相关的关键字
--sysdate:
返回一个date类型数据类型,表示当前系统时间
--systimestamp:
返回一个时间戳类型数据,表示当前系统时间
SELECTSYSDATEFROMdual;
SELECTSYSTIMESTAMPFROMdual;
--to_datedate默认只显示日月年
--在日期格式表示中,出现汉字及其他非格式字符时,使用双引号约束起来
SELECTto_date（
'1992-08-0314:
22:
15','yyyy-mm-ddhh24:
mi:
ss'
）FROMdual;
SELECTto_date（
'1992年08月03日14时22分15秒','yyyy"年"mm"月"dd"日"hh24"时"mi"分"ss"秒"'
）fromdual;
--to_char
SELECTto_char（SYSDATE,'yyyy-mm-ddhh24:
mi:
ss'）FROMdual;
--日期类型是可以进行计算的
--1:
对一个日期加减一个数字等同于加减天数
--2:
两个日期相减,差为相差的天数
--同样,两个日期也可以相比较大小,越晚的越大
SELECTename,trunc（SYSDATE-hiredate,0）FROMemp_gugo;
SELECTename,SYSDATE+2FROMemp_gugo;
--last_day（date）返回给定日期所在月的月底日期
selectlast_day（sysdate）fromdual;
--add_months（date,i）返回给定日期加上i个月之后的日期
SELECTename,add_months（hiredate,20*12）FROMemp_gugo;
--months_between（d1,d2）计算两个日期之间相差的月
SELECTename,trunc（months_between（sysdate,hiredate）,0）fromemp_gugo;
--next_day（date,char）:
返回的date日期数据之后一周之内的指定周几的日期,周几是有参数char来决定的
--char从1-7分别表示周日到周六,
SELECTnext_day（SYSDATE,1）FROMdual;
--least（）,greatest（）比较函数,求最小值/最大值,可以比较时间,可以比较多个参数
SELECTleast（SYSDATE,to_date（'2008-08-08','yyyy-mm-dd'））FROMdual;
SELECTgreatest（SYSDATE,to_date（'2008-08-08','yyyy-mm-dd'））FROMdual;
--extract（datefromdatetime）提取指定日期指定时间分量的值
SELECTEXTRACT（YEARFROMSYSDATE）FROMdual;
SELECTename,hiredateFROMemp_gugoWHEREEXTRACT（YEARFROMhiredate）=1980;
--null
CREATETABLEstudent_gugu（
IDNUMBER（4）,NAMEVARCHAR2（20）,genderCHAR
（1）
）;
DESCstudent_gugu;
INSERTINTOstudent_guguVALUES（1000,'李莫愁','F'）;
INSERTINTOstudent_guguVALUES（1001,'林平之',NULL）;
INSERTINTOstudent_gugu（ID,NAME）VALUES（1002,'张无忌'）;
SELECT*FROMstudent_gugu;
UPDATEstudent_guguSETgender=NULL;
UPDATEstudent_guguSETgender='F'WHEREID=1000;
SELECT*FROMstudent_gugu;
COMMIT;
--使用null作为判断条件将性别值为null的记录删除
--使用isnull/isnotnull
--字符串与null连接等于什么都没做
--null与数字运算结果还是null
DELETEFROMstudent_guguWHEREgenderISnotNULL;
ROLLBACK;
--null的空值函数
--nvl（f1,f2）当f1为null时,函数返回f2的值,
--否则返回f1自身,所以nvl函数的作用是将null值替换为非null值
SELECT*FROMstudent_guguWHEREgender=nvl（NULL,'F'）;
SELECT*FROMemp_gugo;
UPDATEemp_gugoSETcomm=NULLWHEREcomm=0;
updateemp_gugosetmgr=nullwheremgr=0;
--nvl2（f1,f2,f3）函数,当f1不为null时,函数返回f2,否则返回f3
SELECTename,comm,nvl2（comm,'有绩效','没有绩效'）FROMemp_gugo;
selectename,sal,comm,nvl2（comm,sal+comm,sal）fromemp_gugo;
--在select子句中出现的函数或表达式会在结果集中作为字段名
--这样的可读性差,因此可以为这样的字段添加别名
--别名中如果希望包含空格或者区分大小写
--那么别名需要使用双引号括起来
--as可以省略
SELECTenameAS姓名
FROMemp_gugo;
SELECTenameAS姓名,sal*12年薪FROMemp_gugo;
SELECTenameAS姓名,sal*12salFROMemp_gugo;
SELECTenameAS"Name",sal"Sal"FROMemp_gugo;
--使用where进行大小比较
--大于>小于<等于=不等于<>/!
=
--与and或or用来连接多个条件,and优先级高于or,
--所以可以通过括号来提高or的优先级
SELECTename姓名,
sal薪水
FROMemp_gugo
WHEREsal<2000;
SELECTename姓名,sal薪水,JOB职位FROMemp_gugoWHEREdeptno<>10;
SELECTename姓名,
sal薪水,
hiredate入职日期
FROMemp_gugo
WHEREhiredate>to_date（'1982-01-01','yyyy-mm-dd'）;
SELECTename姓名,
sal薪水,
JOB职位
FROMemp_gugo
WHEREsal>2000
ANDJOB='CLERK';
SELECTename姓名,sal薪水,JOB职位FROMemp_gugoWHEREsal>2000ORJOB='CLERK';
SELECTename姓名,
sal薪水,
JOB职位
FROMemp_gugo
WHEREsal>1000
AND（JOB='CLERK'
ORJOB='SALESMAN'）;
--like用于模糊匹配字符串
--有两个通配符,_和%
--_表示单一的一个字符
--%表示任意个字符（0个到多个）
SELECTename,
JOB
FROMemp_gugo
WHEREenameLIKE'_A%';
SELECTename,JOBFROMemp_gugoWHEREenameLIKE'_A_T%';
SELECTename,JOBFROMemp_gugoWHEREenameLIKE'%T';
--in（list）/notin（list）
--判断在/不在列表中,他们也常用在子查询中
SELECTename,
JOB
FROMemp_gugo
WHEREJOBIN（'CLERK','SALESMAN'）;
SELECTename,JOB,deptnoFROMemp_gugoWHEREdeptnoNOTIN（10,20,30）;
--betweenAandBA小于B
--用来查询符合某个值域范围条件的数据,可以用在日期和字符类型数据上
SELECTename,
sal
FROMemp_gugo
WHEREsalBETWEEN1400AND3000;
--ALL（list）和ANY（list）不能单独使用,需要配合单行比较操作符>,<,>=,<=来使用
-->any（list）:
大于最小
--小于最大
-->all（list）:
大于最大
-->all（list）:
小于最小
--他们用在子查询的判断中
SELECTename,
JOB,
sal,
hiredate,
deptno
FROMemp_gugo
WHEREsal>ANY（500,1500,2000）;
SELECTename,JOB,sal,hiredate,deptnoFROMemp_gugoWHEREsal--查询条件中使用表达式和函数
SELECTename,
sal,
JOB
FROMemp_gugo
WHEREename=upper（'scott'）;
SELECTename,sal,JOBFROMemp_gugoWHEREsal*12>50000;
--destinct关键字在SELECT子句中使用用来对指定的字段值去除重复行
--查看公司有哪些职位
SELECTDISTINCTJOB
FROMemp_gugo;
--SELECTename,DISTINCTJOBFROMemp_gugo;
--distinct修饰多字段时,不保证单一的某个字段的值没有重复
--而去重复原则是这些字段值的组合没有重复行
SELECTDISTINCTJOB,
deptno
FROMemp_gugo;
--orderby只能写在查询语句最后;
--orderby子句用来排序结果集,该子句只能写在SELECT语句的最后一个子句上
--orderby可以根据给定字段升序或降序排列结果集
--其中,asc为升序,通常不写,因为默认为升序;desc为降序
--排序的字段若有null值,则null被认为是最大值
SELECTcomm
FROMemp_gugo
ORDERBYcommDESC;
--查看公司工资的排名
SELECTename,salFROMemp_gugoORDERBYsalDESC;
--多字段排序
--排序是有优先级的,首先按照第一个字段的排序方式对结果集进行排序,
--当第一个字段的值相同时按照第二个字段的排序方式排序这些记录,
--以此类推,每个字段可以且必须单独定义其升降序属性
SELECTename,
deptno,
sal
FROMemp_gugo
ORDERBYdeptnoDESC,
sal;
--查看年薪高于两万并且在10或者20号部门的人,名字中含E的,按工资降序排列
SELECTename,
sal,
deptno
FROMemp_gugo
WHEREsal*12>20000
ANDdeptnoIN（10,20）
ANDenameLIKE'%E%'
ORDERBYsalDESC;
--聚合函数-用于统计数据-
--其中包含max,min,sum,avg,count
--max,min求最大/小值
--sum,avg求和/平均值
--count统计记录数
SELECTMAX（sal）,
MIN（sal）,
SUM（sal）,
AVG（sal）,
COUNT（comm）
FROMemp_gugo;
--聚合函数都忽略null值
SELECTAVG（comm）FROMemp_gugo;
--将null替换成0进行计算
SELECTROUND（AVG（NVL（comm,0））,2）FROMemp_gugo;
--当SELECT子句中出现了聚合函数,那么不是聚合函数的
--其他单独字段都必须出现在groupby子句中,反过来则无要求
--SELECTename,MAX（sal）FROMemp_gugo;---不是单组分组函数
--groupby子句
--groupby是配合聚合函数使用的
--groupby允许将结果集按照给定字段值一样的记录进行分组,
--然后配合聚合函数对这些分组的记录分别统计结果
SELECTMAX（sal）,
ROUND（AVG（sal）,2）,
COUNT（sal）,
deptno
FROMemp_gugo
GROUPBYdeptno
ORDERBYdeptno;
SELECTJOB职位,MAX（sal）最高工资,MIN（sal）最低工资FROMemp_gugoGROUPBYJOB;
--where中不能使用聚合函数当做过滤条件,原因是过滤的时机不对,
--where是在检索表中数据的时候进行过滤的,所以where是用来确定结果集记录数的,
--而聚合函数是建立在结果集生成后的数据中进行统计的,
--所以使用聚合函数过滤是在where之后进行的
--having子句
--having子句必须出现在groupby子句之后,作用是添加过滤条件
--来去除不符合条件的分组,having中可以使用聚合函数作为过滤条件
--查看平均工资高于两千的那些部门的具体平均工资
SELECTdeptno,
ROUND（AVG（sal）,2）
FROMemp_gugo
GROUPBYdeptno
HAVINGAVG（sal）>2000;
SELECTMAX（sal）,
MIN（sal）,
ROUND（AVG（sal）,2）,
deptno
FROMemp_gugo
GROUPBYdeptno
HAVINGAVG（sal）>2000;
SELECTCOUNT（*）人数,
MIN（sal）最低工资,
deptno部门
FROMemp_gugo
GROUPBYdeptno
HAVINGMIN（sal）>800;
--count
（1）/select1fromemp_gugo========
--查询语句的执行顺序
--1.from子句,从后往前,从右往左
--2.where子句,执行顺序为自下而上,从右往左
--3.groupby子句,执行顺序从左往右排序,消耗资源
--4.having子句,消耗资源,尽量避免使用
--5.select子句,少用*号,尽量取字段名称
--6.orderby子句,执行顺序为从左往右
SELECT*FROMdept_gugo;
SELECT*FROMemp_gugo;
--关联查询
--关联查询指的是数据从多张表中联合查询,结果集中的字段来自不同表.
--关联查询中用于指定表与表的数据的联系的条件成为关联条件.
--通常关联查询中都要写关联条件,因为不写会产生笛卡尔积,通常
--情况下都是无意义的结果集,开销巨大.
SELECTemp_gugo.ename,
dept_gugo.dname,
emp_gugo.deptno
FROMemp_gugo,
dept_gugo
WHEREemp_gugo.deptno=dept_gugo.deptno;
SELECTe.ename,
f.dname,
e.deptno
FROMemp_gugoe,
dept_gugof
WHEREe.deptno=f.deptno;
--不满足连接条件的记录是查询不出来的
--不写关联条件会产生笛卡尔积,
--n张表关联查询至少要写n-1个关联条件
SELECTe.ename,
f.dname,
e.deptno
FROMemp_gugoe,
dept_gugof;
--过滤条件必须和连接条件同时成立.
--查看在newyork工作的员工都有谁
SELECTe.ename,
f.loc,
f.dname
FROMemp_gugoe,
dept_gugof
WHEREe.deptno=f.deptno
ANDf.loc='NEWYORK';
--查看工资高于2000的员工都有谁,查看该员工的名字,工资,所在部门名字,以及工作所在地
SELECTe.ename姓名,
e.sal工资,
d.dname部门,
d.loc工作地址
FROMemp_gugoe,
dept_gugod
WHEREe.deptno=d.deptno
ANDe.sal>2000;
--内连接join...on
--内连接也是关联查询的一种,返回所有满足连接条件的记录
--on之后接关联条件,where之后接筛选条件
SELECTe.ename,
升级会员 