DQL语句.docx

1、DQL语句-DQL语句-查询语句用来检索数据使用-SELECT 子句 用来指定要查询的字段,若写*则表示查询所有字段-FROM 子句 用来指定数据来源的表SELECT empno,ename,JOB,sal FROM emp_gugo;SELECT mgr,hiredate,comm FROM emp_gugo;-SELECT 子句中也允许使用函数或表达式,这样可以将结果查询出来 SELECT ename,sal*12,sal FROM emp_gugo; -在DQL中使用where子句也可以只查询出满足条件的记录 SELECT ename,JOB,sal FROM emp_gugo WHER

2、E JOB = CLERK; SELECT ename,JOB,sal FROM emp_gugo WHERE sal 2500; -字符串函数 -concat(c1,c2) SELECT concat(ename,sal) FROM emp_gugo; SELECT concat(concat(ename,),sal) FROM emp_gugo; SELECT ename | , | sal FROM emp_gugo; -length(c):求字符串长度 SELECT ename,LENGTH(ename) FROM emp_gugo; -upper,lower,initcap 将字符串

3、转换为全大写,全小写,首字母大写 -dual:伪表,当查询的内容与表没有任何内容的时候可以在FROM子句中查询该表,会查询出一条记录 -intitcap 支持字符内按空格区分每个单词之后首字母大写 SELECT upper(HelloWorld),lower(HelloWorld),initcap(hello world) FROM dual; -trim 去除字符串两边的指定字符,截取集只能有一个字符 -ltrim 去除字符串左边的的截取集中指定的的任一字符,直到遇到不符合截取集条件的字符为止,截取集可以不只一个字符 -rtrim 去除字符串右边的的截取集中指定的的任一字符,直到遇到不符合截

4、取集条件的字符为止,截取集可以不只一个字符 SELECT trim (e FROM eeeeeelieieieeele) FROM dual; SELECT ltrim (eeeeeelieieieeele,el) FROM dual; SELECT rtrim (eeeeeelieieieeele,el) FROM dual; -lpad,rpad 补位函数 -允许将指定字符串显示指定位数,不足时补充若干指定字符达到该长度 -lpad 从左添加,rpad 从右添加,当指定位数小于字符串长度时,从左截取指定位数 -可以达到右对齐/左对齐的效果(指定数较大的时候且使用空格字符) SELECT e

5、name,rpad(sal,5,!) FROM emp_gugo; SELECT ename,lpad(sal,10,!) FROM emp_gugo; -substr (str,m,n) -将给定字符串从m处开始连续截取n个字符,n不指定时默认取到字符串末尾,n超过可以截取的字符数量也是截取到末尾 -n表示n个字符,不能为负数 -m可以是负数,负数则是从倒数位置开始 -读取顺序是从左往右的 -数据库中的下标是从1开始的 SELECT substr(thinking in java,-7,4) FROM dual; -instr(c1,c2,m,n) -查看c2在c1中的位置,m用来指定从哪里

6、开始检索,不写默认从第一个字符开始 -n表示第几次出现,不写默认为1 SELECT instr(thinking in java,i,2,2) FROM dual; -数值函数 -round(m,n):四舍五入 -n为保留到小数点后的位数,若n为负数,则是十位以上的数字, SELECT round(55.567,1) FROM dual; SELECT round(33.745,0) FROM dual; SELECT round(645.333,-3) FROM dual; -trunc(m,n)截取数字 SELECT trunc(55.335,1) FROM dual; SELECT tr

7、unc(564.44,0) FROM dual; SELECT trunc(55.678,-1) FROM dual; -mod(m,n):返回m除以n后的余数,n为0则直接返回m SELECT mod(555,20) FROM dual; SELECT mod(555,0) FROM dual; -ceil(n),floor(n)返回取大于或等于n的最小整数值(向上取整)/小于或等于n的最大整数值(向下取整) SELECT ceil(45.5) FROM dual; SELECT floor(-45.333) FROM dual; -日期相关函数 -和日期相关的关键字 -sysdate:返回

8、一个date类型数据类型,表示当前系统时间 -systimestamp:返回一个时间戳类型数据,表示当前系统时间SELECT SYSDATE FROM dual;SELECT SYSTIMESTAMP FROM dual;-to_date date默认只显示日月年-在日期格式表示中,出现汉字及其他非格式字符时,使用双引号约束起来SELECT to_date(1992-08-03 14:22:15,yyyy-mm-dd hh24:mi:ss) FROM dual;SELECT to_date(1992年08月03日 14时22分15秒,yyyy年mm月dd日 hh24时mi分ss秒) from

9、dual;-to_charSELECT to_char(SYSDATE,yyyy-mm-dd hh24:mi:ss) FROM dual;-日期类型是可以进行计算的-1:对一个日期加减一个数字等同于加减天数-2:两个日期相减,差为相差的天数-同样,两个日期也可以相比较大小,越晚的越大SELECT ename,trunc(SYSDATE-hiredate,0) FROM emp_gugo;SELECT ename,SYSDATE+2 FROM emp_gugo;-last_day(date) 返回给定日期所在月的月底日期select last_day(sysdate) from dual;-ad

10、d_months(date,i) 返回给定日期加上i个月之后的日期SELECT ename,add_months(hiredate,20*12) FROM emp_gugo;-months_between(d1,d2) 计算两个日期之间相差的月SELECT ename,trunc(months_between(sysdate,hiredate),0) from emp_gugo;-next_day(date,char):返回的date日期数据之后一周之内的指定周几的日期,周几是有参数char来决定的-char从1-7分别表示周日到周六,SELECT next_day(SYSDATE,1) FR

11、OM dual;-least(),greatest()比较函数,求最小值/最大值,可以比较时间,可以比较多个参数SELECT least(SYSDATE,to_date(2008-08-08,yyyy-mm-dd) FROM dual;SELECT greatest(SYSDATE,to_date(2008-08-08,yyyy-mm-dd) FROM dual;-extract(date from datetime)提取指定日期指定时间分量的值SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;SELECT ename,hiredate FROM emp_

12、gugo WHERE EXTRACT(YEAR FROM hiredate)=1980;-null CREATE TABLE student_gugu(ID NUMBER(4),NAME VARCHAR2(20),gender CHAR(1);DESC student_gugu;INSERT INTO student_gugu VALUES(1000,李莫愁,F);INSERT INTO student_gugu VALUES(1001,林平之,NULL);INSERT INTO student_gugu (ID,NAME) VALUES(1002,张无忌);SELECT * FROM stu

13、dent_gugu;UPDATE student_gugu SET gender = NULL;UPDATE student_gugu SET gender = F WHERE ID = 1000;SELECT * FROM student_gugu;COMMIT;-使用null作为判断条件将性别值为null的记录删除-使用is null/is not null-字符串与null连接等于什么都没做-null与数字运算结果还是nullDELETE FROM student_gugu WHERE gender IS not NULL;ROLLBACK;-null的空值函数-nvl(f1,f2)当f

14、1为null时,函数返回f2的值,-否则返回f1自身,所以nvl函数的作用是将null值替换为非null值SELECT * FROM student_gugu WHERE gender = nvl(NULL,F);SELECT * FROM emp_gugo;UPDATE emp_gugo SET comm = NULL WHERE comm = 0;update emp_gugo set mgr = null where mgr = 0;-nvl2(f1,f2,f3)函数,当f1不为null时,函数返回f2,否则返回f3SELECT ename,comm, nvl2(comm,有绩效,没有绩

15、效) FROM emp_gugo;select ename,sal,comm, nvl2(comm,sal+comm,sal) from emp_gugo;-在select子句中出现的函数或表达式会在结果集中作为字段名-这样的可读性差,因此可以为这样的字段添加别名-别名中如果希望包含空格或者区分大小写-那么别名需要使用双引号括起来-as 可以省略SELECT ename AS 姓名FROM emp_gugo;SELECT ename AS 姓名,sal*12 年薪 FROM emp_gugo;SELECT ename AS 姓名,sal*12 sal FROM emp_gugo;SELECT

16、ename AS Name,sal Sal FROM emp_gugo;-使用where进行大小比较-大于 小于 等于= 不等于/!=-与 and 或 or 用来连接多个条件,and优先级高于or,-所以可以通过括号来提高or的优先级SELECT ename 姓名, sal 薪水FROM emp_gugoWHERE sal2000;SELECT ename 姓名,sal 薪水,JOB 职位 FROM emp_gugo WHERE deptno 10;SELECT ename 姓名, sal 薪水, hiredate 入职日期FROM emp_gugoWHERE hiredateto_date(

17、1982-01-01,yyyy-mm-dd);SELECT ename 姓名, sal 薪水, JOB 职位FROM emp_gugoWHERE sal 2000AND JOB = CLERK;SELECT ename 姓名,sal 薪水,JOB 职位 FROM emp_gugo WHERE sal 2000 OR JOB = CLERK;SELECT ename 姓名, sal 薪水, JOB 职位FROM emp_gugoWHERE sal 1000AND (JOB = CLERKOR JOB = SALESMAN);-like用于模糊匹配字符串-有两个通配符,_ 和 %-_ 表示单一的一

18、个字符-% 表示任意个字符(0个到多个)SELECT ename, JOBFROM emp_gugoWHERE ename LIKE _A%;SELECT ename,JOB FROM emp_gugo WHERE ename LIKE _A_T%;SELECT ename,JOB FROM emp_gugo WHERE ename LIKE %T;-in (list) /not in (list)-判断在/不在列表中,他们也常用在子查询中SELECT ename, JOBFROM emp_gugoWHERE JOB IN (CLERK,SALESMAN);SELECT ename,JOB,d

19、eptno FROM emp_gugo WHERE deptno NOT IN (10,20,30);-between A and B A小于B-用来查询符合某个值域范围条件的数据,可以用在日期和字符类型数据上SELECT ename, salFROM emp_gugoWHERE sal BETWEEN 1400 AND 3000;- ALL (list)和 ANY (list)不能单独使用,需要配合单行比较操作符,=,any (list):大于最小- all (list):大于最大- all (list):小于最小-他们用在子查询的判断中SELECT ename, JOB, sal, hir

20、edate, deptnoFROM emp_gugoWHERE salANY(500,1500,2000);SELECT ename,JOB,sal,hiredate,deptno FROM emp_gugo WHERE sal50000;-destinct关键字 在SELECT子句中使用用来对指定的字段值去除重复行-查看公司有哪些职位SELECT DISTINCT JOBFROM emp_gugo;-SELECT ename,DISTINCT JOB FROM emp_gugo;-distinct 修饰多字段时,不保证单一的某个字段的值没有重复-而去重复原则是这些字段值的组合没有重复行SEL

21、ECT DISTINCT JOB, deptnoFROM emp_gugo;-order by只能写在查询语句最后;-order by子句用来排序结果集,该子句只能写在SELECT语句的最后一个子句上-order by 可以根据给定字段升序或降序排列结果集-其中,asc为升序,通常不写,因为默认为升序;desc为降序-排序的字段若有null值,则null被认为是最大值SELECT commFROM emp_gugoORDER BY comm DESC;-查看公司工资的排名SELECT ename,sal FROM emp_gugo ORDER BY sal DESC;-多字段排序-排序是有优先

22、级的,首先按照第一个字段的排序方式对结果集进行排序,-当第一个字段的值相同时按照第二个字段的排序方式排序这些记录,-以此类推,每个字段可以且必须单独定义其升降序属性SELECT ename, deptno, salFROM emp_gugoORDER BY deptno DESC, sal;-查看年薪高于两万并且在10或者20号部门的人,名字中含E的,按工资降序排列SELECT ename, sal, deptnoFROM emp_gugoWHERE sal *12 20000AND deptno IN (10,20)AND ename LIKE %E%ORDER BY sal DESC;-聚

23、合函数-用于统计数据-其中包含 max,min,sum,avg,count-max,min求最大/小值-sum,avg求和/平均值-count统计记录数SELECT MAX(sal), MIN(sal), SUM(sal), AVG(sal), COUNT(comm)FROM emp_gugo;-聚合函数都忽略null值SELECT AVG(comm) FROM emp_gugo;-将null替换成0进行计算SELECT ROUND(AVG(NVL(comm,0),2) FROM emp_gugo;-当SELECT子句中出现了聚合函数,那么不是聚合函数的-其他单独字段都必须出现在group b

24、y子句中,反过来则无要求-SELECT ename,MAX(sal) FROM emp_gugo;-不是单组分组函数-group by 子句-group by 是配合聚合函数使用的-group by 允许将结果集按照给定字段值一样的记录进行分组,- 然后配合聚合函数对这些分组的记录分别统计结果SELECT MAX(sal), ROUND(AVG(sal),2), COUNT(sal), deptnoFROM emp_gugoGROUP BY deptnoORDER BY deptno;SELECT JOB 职位,MAX(sal) 最高工资,MIN(sal) 最低工资 FROM emp_gugo

25、 GROUP BY JOB;-where中不能使用聚合函数当做过滤条件,原因是过滤的时机不对,-where是在检索表中数据的时候进行过滤的,所以where是用来确定结果集记录数的,-而聚合函数是建立在结果集生成后的数据中进行统计的,-所以使用聚合函数过滤是在where之后进行的-having子句-having子句必须出现在group by子句之后,作用是添加过滤条件-来去除不符合条件的分组,having中可以使用聚合函数作为过滤条件-查看平均工资高于两千的那些部门的具体平均工资SELECT deptno , ROUND(AVG(sal),2)FROM emp_gugoGROUP BY dept

26、noHAVING AVG(sal)2000;SELECT MAX(sal), MIN(sal), ROUND(AVG(sal),2), deptnoFROM emp_gugoGROUP BY deptnoHAVING AVG(sal) 2000;SELECT COUNT(*) 人数, MIN(sal) 最低工资, deptno 部门FROM emp_gugoGROUP BY deptnoHAVING MIN(sal)800;-count(1)/select 1 from emp_gugo=-查询语句的执行顺序-1.from子句,从后往前,从右往左-2.where子句,执行顺序为自下而上,从右往

27、左-3.group by子句,执行顺序从左往右排序,消耗资源-4.having子句,消耗资源,尽量避免使用-5.select子句,少用*号,尽量取字段名称-6.order by子句,执行顺序为从左往右SELECT * FROM dept_gugo;SELECT * FROM emp_gugo;-关联查询-关联查询指的是数据从多张表中联合查询,结果集中的字段来自不同表.-关联查询中用于指定表与表的数据的联系的条件成为关联条件.-通常关联查询中都要写关联条件,因为不写会产生笛卡尔积,通常-情况下都是无意义的结果集,开销巨大.SELECT emp_gugo.ename, dept_gugo.dnam

28、e, emp_gugo.deptnoFROM emp_gugo, dept_gugoWHERE emp_gugo.deptno = dept_gugo.deptno;SELECT e.ename, f.dname, e.deptnoFROM emp_gugo e, dept_gugo fWHERE e.deptno = f.deptno;-不满足连接条件的记录是查询不出来的-不写关联条件会产生笛卡尔积,-n张表关联查询至少要写n-1个关联条件SELECT e.ename, f.dname, e.deptnoFROM emp_gugo e, dept_gugo f;-过滤条件必须和连接条件同时成

29、立.-查看在new york工作的员工都有谁SELECT e.ename, f.loc, f.dnameFROM emp_gugo e, dept_gugo fWHERE e.deptno = f.deptnoAND f.loc = NEWYORK;-查看工资高于2000的员工都有谁,查看该员工的名字,工资,所在部门名字,以及工作所在地SELECT e.ename 姓名, e.sal 工资, d.dname 部门, d.loc 工作地址FROM emp_gugo e, dept_gugo dWHERE e.deptno = d.deptnoAND e.sal 2000;-内连接 join.on-内连接也是关联查询的一种,返回所有满足连接条件的记录-on 之后接关联条件,where 之后接筛选条件SELECT e.ename,