SQL查询基础课件.ppt

1、SQL查询基础1本章要点 SQL的基本概念和作用 SQL与SQL*Plus、PL/SQL之间的关系 Select Where Order By Group By Having2概述 本节包括两部分内容。首先，介绍SQL的作用和特点，SQL技术是本章的中心内容；其次，介绍SQL与SQL*Plus、PL/SQL之间的区别和联系，以便更好地学习和理解SQL。3SQL的作用和特点 SQL是结构化查询语言(structure query language)的简称，是关系型数据库管理系统中最流行的数据查询和更新语言。用户可以使用SQL语言在数据库中执行各种操作。1986年，SQL-86，也被称为SQL-1

2、。1992年，SQL-92，也被称为SQL-2。1999年，SQL-99，也被称为SQL-3。还存在不同的数据库管理系统厂商开发的不同类型的SQL。SQL查询语言包括了所有对数据库的操作，这些操作可以分为4个部分，即 数据定义语言DDL：定义数据库的逻辑结构 数据操纵语言DML：数据检索和数据更新 数据控制语言DCL：授权、完整性规则 事务管理语言TML：事务的定义、保存、提交commit、取消rollback4SQL与SQL*Plus、PL/SQL之间的关系 SQL*Plus是一个用于连接Oracle数据库的强大工具。使用该工具，用户可以连接服务器上的数据库，还可以执行许多操作。总结一下SQ

3、L*Plus的主要功能特点：在数据库中执行SQL语言和PL/SQL语言；更新数据库中的数据；执行数据查询；将查询结果集格式化为报表；建立、编辑、检索和执行SQL脚本；管理数据库；描述数据库中的表和PL/SQL对象；将数据从一个数据库复制到另一个数据库；向用户发送消息，并接受用户的输入。5PL/SQL PL/SQL是Oracle系统的过程化编程语言。用户可以使用PL/SQL语言编写在数据库中执行的定制程序和过程代码。如同拥有执行SQL语言的SQL引擎一样，Oracle系统也拥有执行PL/SQL语言的PL./SQL引擎。PL/SQL程序能够存储在数据库中，以便用于用户应用程序和日常操作。这样可以使

4、用户在Oracle数据库设计和规划的编程语言中，实现与用户数据密切相关的业务逻辑。6SQL语句的书写规则 一般情况下，在使用SQL语句(包括使用select语句)时，用户应该遵循下面的规则或约定：除非明确指定，否则SQL语句不区分大小写；SQL语句可以一行或多行的形式输入；关键字不能跨行；为了增强可读性，关键字左对齐；可以在SQL语句中使用各种制表符和伸缩符号。7select语句 数据操纵语言就是那些检索、更新表中数据的语句。这些语句包括select、insert、update、delete等4个语句。本节首先介绍select语句，后面将陆续讲述其他的数据操纵语言。在SQL语言中，最简单的语句

5、是select语句。使用select语句可以检索数据库中的信息。具体地说，使用select语句可以完成选择、投影、连接等运算。8select基本语法 在Oracle系统中，select语句的基本语法格式如下：select distinct*,column_name alias,from tablenamewheregroup byhavingorder by9基本的SELECT 命令SELECT*,column alias,.FROMtable;SELECT 识别什么列FROM 识别哪个表 选择所有列 DEPTNO DNAME LOC-10 ACCOUNTING NEW YORK 20 RES

6、EARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL SELECT*2 FROM dept;选择指定的列 DEPTNO LOC-10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTONSQL SELECT deptno,loc 2 FROM dept;算数表达式 对NUMBER和DATE型数据可用算数运算创建表达式运算运算+-*/描述描述加法加法减法减法乘法乘法除法除法 使用算数运算 SQL SELECT ename,sal,sal+300 2 FROMemp;ENAME SAL SAL+300-KING 500

7、0 5300BLAKE 2850 3150CLARK 2450 2750JONES 2975 3275MARTIN 1250 1550ALLEN 1600 1900.14 rows selected.运算的优先级乘法和除法的优先级高于加法和减法同级运算的顺序是从左到右表达式中使用括号可强行改变优先级的运算顺序 运算的优先级SQL SELECT ename,sal,12*sal+100 2 FROM emp;ENAME SAL 12*SAL+100-KING 5000 60100BLAKE 2850 34300CLARK 2450 29500JONES 2975 35800MARTIN 1250

8、 15100ALLEN 1600 19300.14 rows selected.使用括号SQL SELECT ename,sal,12*(sal+100)2 FROM emp;ENAME SAL 12*(SAL+100)-KING 5000 61200BLAKE 2850 35400CLARK 2450 30600JONES 2975 36900MARTIN 1250 16200.14 rows selected.定义空值 空值是指不可用，不知道，不适用的值 空值不等于零或空格SQL SELECT ename,job,comm 2 FROMemp;ENAME JOB COMM-KING PRE

9、SIDENTBLAKE MANAGER.TURNER SALESMAN 0.14 rows selected.空值的运算表达式包括空值的算法表达式等于空SQL select ename NAME,12*sal+comm 2 from emp 3 WHERE ename=KING;NAME 12*SAL+COMM-KING处理NULL 当算术表达式包含NULL，其结果也是NULL Select ename,sal,comm,sal+comm from emp;使用NVL函数处理NULL NVL（expr1,expr2):如果expr1是null，则返回expr2；否则返回expr1 Select

10、 ename,sal,comm,sal+nvl(comm,0)as“月收入”from emp;使用NVL2函数处理NULL NVL2(expr1,expr2,expr3):如果expr1不是null，则返回expr2；否则返回expr3 Select ename,nvl2(comm,sal+comm,sal)from emp;20定义列的别名改变列的标题头使用计算结果列的别名如果使用特殊自符,或大小写敏感,或有空格时,需加双引号 使用列的别名 SQL SELECT ename AS name,sal salary 2 FROM emp;NAME SALARY-.SQL SELECT ename

11、 Name,2 sal*12 Annual Salary 3 FROM emp;Name Annual Salary-.连结操作将列或字符与其它列连结 用双竖条表示(|)产生的结果列是一个字符表达式使用连结操作SQL SELECTename|job AS Employees 2 FROM emp;Employees-KINGPRESIDENTBLAKEMANAGERCLARKMANAGERJONESMANAGERMARTINSALESMANALLENSALESMAN.14 rows selected.文字字符串文字字符串是一个包括在SELECT列表中的字符,表达式,或数字 日期和字符型文字字符

12、必须用单引号扩起来每返回一条记录字符被输出一次使用文字字符串Employee Details-KING is a PRESIDENTBLAKE is a MANAGERCLARK is a MANAGERJONES is a MANAGERMARTIN is a SALESMAN.14 rows selected.SQL SELECT ename|is a|job 2 AS Employee Details 3 FROM emp;检索日期列 默认日期显示格式：Select ename,hiredate from emp;使用YYYY-MM-DD显示格式显示日期 Select ename,to_

13、char(hiredate,YYYY-MM-DD)from emp;27 重复记录 缺省情况下查询显示所有行,包括重行SQL SELECT deptno 2 FROM emp;DEPTNO-10 30 10 20.14 rows selected.删除重复行SQL SELECT DISTINCT deptno 2 FROM emp;DEPTNO-10 20 30 使用where子句 使用select语句中的where子句可以限制数据量，实际上，select语句中的where子句可以用来指定检索数据的条件。where子句的语法格式如下：select distinct*,column_name a

14、lias,from tablenamewhere condition_expression以上的where子句用来指定检索到的数据应该满足指定的条件，参数condition_ expression就是用于指定条件。在where子句的条件中，可以比较各种列值、文字串、算术表达式、函数等内容。30使用WHERE子句SQL SELECT ename,job,deptno 2 FROM emp 3 WHERE job=CLERK;ENAME JOB DEPTNO-JAMES CLERK 30SMITH CLERK 20ADAMS CLERK 20MILLER CLERK 10字符串和日期字符串和日期要

15、用单引号扩起来字符串是大小写敏感的,日期值是格式敏感的缺省的日期格式是 DD-MON-YYSQL SELECTename,job,deptno 2 FROM emp 3 WHEREename=JAMES;SQL SELECTSELECT*FROMFROM emp WHEREWHEREhiredate 01-1月-82SQL SELECTSELECT*FROMFROM emp WHEREWHEREhiredateto_date(1982-01-01,YYYY-MM-DD)比较运算符运算符运算符=含义含义等于等于大于大于 大于等于大于等于 小于小于 小于等于小于等于不等于不等于使用比较运算符SQL

16、 SELECT ename,sal,comm 2 FROM emp 3 WHERE sal SELECTename,sal 2 FROM emp 3 WHEREsal BETWEEN 1000 AND 1500;LowerlimitHigherlimit 使用BETWEEN运算符显示某一 值域范围的记录使用IN运算符 使用IN运算符获得匹配列表值的记录 SQL SELECTempno,ename,sal,mgr 2 FROM emp 3 WHEREmgr IN(7902,7566,7788);EMPNO ENAME SAL MGR-7902 FORD 3000 7566 7369 SMITH

17、800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788使用LIKE运算符 使用LIKE运算符执行通配查询 查询条件可包含文字字符或数字(%)可表示零或多个字符(_)可表示一个字符SQL SELECTename 2 FROM emp 3 WHEREename LIKE S%;使用LIKE运算符使用组合方式匹配字符You can combine pattern matching characters.使用ESCAPE 标识符来查找带特殊符号的字符号SQL SELECTename 2 FROMemp 3 WHEREename LIKE _A%;ENAME

18、-JAMES WARDSQL SELECTename 2 FROMemp 3 WHEREename LIKELIKE%a_%escapeescape a使用IS NULL运算符 查询包含空值的记录SQL SELECT ename,mgr 2 FROM emp 3 WHERE mgr IS NULL;ENAME MGR-KING逻辑运算符运算符运算符含含 义义AND如果组合的条件都是TRUE,返回TRUEOR如果组合的条件 之一是TRUE,返回TRUENOT如果下面的条件是FALSE,返回TRUE使用AND运算符AND需要条件都是TRUE.SQL SELECT empno,ename,job,s

19、al 2 FROM emp 3 WHERE sal=1100 4 AND job=CLERK;EMPNO ENAME JOB SAL-7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300使用OR运算符OR需要条件之一是需要条件之一是TRUESQL SELECT empno,ename,job,sal 2 FROM emp 3 WHERE sal=1100 4 OR job=CLERK;EMPNO ENAME JOB SAL-7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER

20、2450 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250.14 rows selected.使用NOT运算符SQL SELECT ename,job 2 FROM emp 3 WHERE job NOT IN(CLERK,MANAGER,ANALYST);ENAME JOB-KING PRESIDENTMARTIN SALESMANALLEN SALESMANTURNER SALESMANWARD SALESMAN优先级规则 括号将跨越所有优先级规则 次序次序 运算符运算符 1所有的比较运算所有的比较运算2NOT3AND4OR优先级规则ENA

21、ME JOB SAL-KING PRESIDENT 5000MARTIN SALESMAN 1250ALLEN SALESMAN 1600TURNER SALESMAN 1500WARD SALESMAN 1250SQL SELECT ename,job,sal 2 FROM emp 3 WHERE job=SALESMAN 4 OR job=PRESIDENT 5 AND sal1500;优先级规则ENAME JOB SAL-KING PRESIDENT 5000ALLEN SALESMAN 1600使用扩号提高优先级使用扩号提高优先级SQL SELECT ename,job,sal 2 F

22、ROM emp 3 WHERE (job=SALESMAN 4 OR job=PRESIDENT)5 AND sal1500;使用order by子句 在前面介绍的数据检索技术中，只是把数据库中的数据直接取出来。这时，在结果集中数据的排列顺序由数据的存储顺序决定。但是，这种存储顺序经常不符合实际的各种业务需要，因此需要对数据库中的数据在检索时进行排序。在select语句中，可以使用order by子句执行数据排序操作。使用order by子句的语法格式如下：order by column_name,expression asc|desc48排序是昂贵的操作 从开始就考虑优化 排序往往花费大量的

23、时间及资源磁盘排序比内存排序慢14000倍49ORDER BY 子句使用ORDER BY 子句将记录排序 ASC:升序,缺省 DESC:降序 ORDER BY 子局在SELECT指令的最后SQL SELECT ename,job,deptno,hiredate 2 FROM emp 3 ORDER BY hiredate;ENAME JOB DEPTNO HIREDATE-SMITH CLERK 20 17-DEC-80ALLEN SALESMAN 30 20-FEB-81.14 rows selected.降序排序SQL SELECT ename,job,deptno,hiredate 2

24、FROM emp 3 ORDER BY hiredate DESC;ENAME JOB DEPTNO HIREDATE-ADAMS CLERK 20 12-JAN-83SCOTT ANALYST 20 09-DEC-82MILLER CLERK 10 23-JAN-82JAMES CLERK 30 03-DEC-81FORD ANALYST 20 03-DEC-81KING PRESIDENT 10 17-NOV-81MARTIN SALESMAN 30 28-SEP-81.14 rows selected.使用列的别名排序SQL SELECT empno a,ename b,sal*12 a

25、nnsal 2 FROM emp 3 ORDER BY annsal;EMPNO ENAME ANNSAL-7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7654 MARTIN 15000 7521 WARD 15000 7934 MILLER 15600 7844 TURNER 18000.14 rows selected.多个列排序通过ORDER BY 列表的顺序来排序 你可使用不在SELECT序列上的列来排序SQL SELECTename,deptno,sal 2 FROM emp 3 ORDER BY deptno,sal DESC

26、;ENAME DEPTNO SAL-KING 10 5000CLARK 10 2450MILLER 10 1300FORD 20 3000.14 rows selected.什么是分组函数 分组函数运算每一组记录,每一组返回一个结果EMP表中工资的最高值 DEPTNO SAL-10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 MAX(SAL)-5000聚合函数（合计计算）合计计算就是把一组数据作为计算的对象而执行的计算，这种计

27、算包括求最大值、平均值、方差等。进行合计计算必须使用Oracle系统提供的合计函数。在Oracle系统中，可以使用的合计函数及其语法和功能如表6-5所示。55使用AVG 和 SUM 函数AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)-1400 1600 1250 5600 在数字类型数据使用AVG and SUM 函数SQL SELECTAVG(sal),MAX(sal),2MIN(sal),SUM(sal)3FROMemp 4WHEREjob LIKE SALES%;使用MIN 和 MAX 函数 MIN and MAX适用于任何数据类型 SQL SELECTMIN(hire

28、date),MAX(hiredate)2 FROMemp;MIN(HIRED MAX(HIRED-17-DEC-80 12-JAN-83 使用 COUNT 函数 COUNT(*)-6SQL SELECTCOUNT(*)2 FROMemp 3 WHEREdeptno=30;COUNT(*)返回表中的记录数使用 COUNT 函数 COUNT(expr)返回非空非空记录数SQL SELECTCOUNT(comm)2 FROMemp 3 WHEREdeptno=30;COUNT(COMM)-4 COUNT(distinct expr)返回非非重复重复记录数分组函数和空值 分组函省略列中的空值SQL S

29、ELECT AVG(comm)2 FROM emp;AVG(COMM)-550在分组函数中使用NVL函数 NVL函数迫使分组函数包括空值SQL SELECT AVG(NVL(comm,0)2 FROM emp;AVG(NVL(COMM,0)-157.14286使用group by和having子句 表中包含了许多数据，可以对这些数据按照某个指定的列进行分组，然后使用合计函数计算相应列的合计值。例如，如果需要检索最高薪水和最低薪水的雇员，并返回不同部门中最高薪水和最低薪水的雇员，那么就需要对表中的数据进行分组计算。在Oracle系统中，可以在select语句中使用group by子句进行分组计算

30、。使用分组计算的语法格式如下：group by group_by_expressionhaving group_condition 组函数忽略空值,可以使用NVL,NVL2,COALESCE 函数处理空值 结果集按group by的列隐式按升序排列62数据分组 EMP表中每一个部门表中每一个部门的平均工资的平均工资 DEPTNO SAL-10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTNO AVG(SAL)-10 2

31、916.6667 20 2175 30 1566.6667使用GROUP BY子句 所有用来分组的列在SELECT列表中不能使用分组函数SQL SELECT deptno,AVG(sal)2 FROM emp 3 GROUP BY deptno;DEPTNO AVG(SAL)-10 2916.6667 20 2175 30 1566.6667使用GROUP BY子句 GROUP BY 列可以不在SELECT列表中 SQL SELECT AVG(sal)2 FROM emp 3 GROUP BY deptno;AVG(SAL)-2916.6667 21751566.6667多列分组按部门统计em

32、p表中每种职位的工资总和 DEPTNO JOB SAL-10 MANAGER 2450 10 PRESIDENT 5000 10 CLERK 1300 20 CLERK 800 20 CLERK 1100 20 ANALYST 3000 20 ANALYST 3000 20 MANAGER 2975 30 SALESMAN 1600 30 MANAGER 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250JOB SUM(SAL)-CLERK 1300MANAGER 2450PRESIDENT 5000ANAL

33、YST 6000CLERK 1900MANAGER 2975CLERK 950MANAGER 2850SALESMAN 5600DEPTNO-101010202020303030在多列上使用 GROUP BY子句 SQL SELECT deptno,job,sum(sal)2 FROM emp 3 GROUP BY deptno,job;DEPTNO JOB SUM(SAL)-10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900.9 rows selected.使用分组函数的非法查询 如果没有

34、GROUP BY子句SELECT列表中的不能同时出现列和合计函数SQL SELECTdeptno,COUNT(ename)2 FROMemp;SELECT deptno,COUNT(ename)*ERROR at line 1:ORA-00937:not a single-group group function使用分组函数时的非法查询不能使用WHERE子句限定组可使用HAVING子句限定分组之后的结果SQL SELECT deptno,AVG(sal)2 FROM emp 3 WHERE AVG(sal)2000 4 GROUP BY deptno;WHERE AVG(sal)2000 *E

35、RROR at line 3:ORA-00934:group function is not allowed here限定组的结果查询部门的最高查询部门的最高工资大于工资大于$2900 DEPTNO SAL-10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTNO MAX(SAL)-10 5000 20 3000限定组的结果:HAVING子句 使用HAVING子句限定组 记录被分组 应用组函数 匹配HAVING子句的组被

36、显示SELECTcolumn,group_functionFROMtableWHEREconditionGROUP BYgroup_by_expressionHAVINGgroup_conditionORDER BYcolumn;使用HAVING子句SQL SELECT deptno,max(sal)2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)2900;DEPTNO MAX(SAL)-10 5000 20 3000使用HAVING子句SQL SELECT job,SUM(sal)PAYROLL 2 FROM emp 3 WHERE job NOT

37、 LIKE SALES%3 GROUP BY job 4 HAVING SUM(sal)5000 5 ORDER BY SUM(sal);JOB PAYROLL-ANALYST 6000MANAGER 8275嵌套组函数SQL SELECT max(avg(sal)2 FROM emp 3 GROUP BY deptno;MAX(AVG(SAL)-2916.6667 显示最高的部门平均工资 ROLLUP rollup和group by一起使用 用来产生各分组的小计以及最后的合计 例：统计每个部门每种岗位的平均工资，并添加合计select deptno,job,avg(sal)from empg

38、roup by rollup(deptno,job)ROLLUP操作符75 CUBE CUBE 和group by一起使用 用来产生各分组的横向小计、纵向小计以及最后的合计 例：统计每个部门每种岗位的平均工资，并添加合计select deptno,job,avg(sal)from empgroup by cube(deptno,job)CUBE操作符76练习1 1、查询工资不在1500-2850之间的所有雇员名及工资 2、部门10和30中工资超过1500的雇员名及工资 3、在1981年2月1日-1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期先后顺序排列 4、获得补助的所有雇佣

39、名、工资及补助额，并以工资和补助的降序排列 5、工资低于1500的职员增加10%的工资,1500及以上的增加5%的工资 6、查询公司每月、每季度、每年的资金支出数 7、查询所有雇员的平均工资、总计工资、最高工资和最低工资 8、每种岗位的雇员总数、平均工资 9、雇员总数以及获得补助的雇员数 10、管理者的总人数 11、雇员工资的最大差额 12、每个部门每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资 13、查询每个岗位人数超过2人的所有职员信息 14、查询直接下级超过2人的职员的全部信息 15、查询没有直接下级的职员的全部信息77练习2 在职员表中查询出基本工资比平均基本工资高的职工编号78练习3 查询一个或多个部门的所有员工信息，该部门的所有员工工资都高于公司的平均工资79Review SQL的基本概念和作用 SQL与SQL*Plus、PL/SQL之间的关系 Select Where Order By Group By Having80