数据库技术：SQL语言基础

SQL语言基础SQL历史简介SQL是由位于加利福尼亚SanJose的IBM实验室与20实际70年代后期开发出来的，其含义为结构化查询语言（StructuredQueryLanguage）。SQL是一种非过程化的语言，它使得建立关系数据库成为可能。数据库历史简介数据库的用途关系型数据库的产生Codd博士提出关系数据库模型的12条准则用集合来组织数据库系统SQL是关系数据库操作和检索的标准语言目录基本SQL语句对数据进行限定和排序处理单行函数从多个表中获取数据使用分组函数对数据进行聚集子查询操作数据——DML语句创建和管理表第一部分基本SQL语句目标完成本节课学习后，应当达到如下目标:能够指出SQL语句中SELECT语句的作用执行一个基本SELECT查询语句SELECTt语句的功能11/15/2022选择投影Table1Table2Table1Table1连接基本SELECT语句11/15/2022SELECT {*,column[alias],...}FROM table;SELECT关键字标明取哪些列的数据.FROM关键字标明从哪些表中取数据.重要特征：表达式以分号结尾.SQL语句的书写SQL语句不是大小写敏感的建议关键字用大写，其他用小写；SQL语句可以分布在一行或者多行中；关键字不能进行缩写，也不能分布在两行；一般地，不同的子句写在不同的行为宜；经常用跳格键或者缩进来增强可读性。选出所有的列11/15/2022

DEPTNODNAMELOC------------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON40OPERATIONSBOSTONSQL>SELECT*2FROM dept;选出特定的列11/15/2022

DEPTNOLOC----------------------10NEWYORK20DALLAS30CHICAGO40BOSTONSQL>SELECTdeptno,loc2FROMdept;列头部的缺省格式缺省的对齐方式左部对齐：日期、字符数据右部对齐：数字格式数据缺省的显示方式:大写11/15/2022剔除重复数据

DEPTNODNAMELOC------------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONSQL>SELECTDISTINCT*2FROM dept;数学表达式11/15/2022符号+-*/ 描述加减乘除可以用下述的数学符号，将日期或者数字数据组合起来形成数学表达式在SQL语句中运用数学表达式11/15/2022

SQL>SELECTename,sal,sal+3002FROM emp;ENAMESALSAL+300----------------------------KING50005300BLAKE28503150CLARK24502750JONES29753275MARTIN12501550ALLEN16001900...14rowsselected.表达式优先级11/15/2022*/+_乘和除操作的优先级高于加和减；相同优先级的操作符自左向右进行计算；圆括号“（”“）”，用来重新组合表达式的优先级。操作符顺序11/15/2022

SQL>SELECTename,sal,12*sal+1002FROMemp;ENAMESAL12*SAL+100-----------------------------KING500060100BLAKE285034300CLARK245029500JONES297535800MARTIN125015100ALLEN160019300...14rowsselected.使用圆括号11/15/2022

SQL>SELECTename,sal,12*(sal+100)2FROMemp;ENAMESAL12*(SAL+100)------------------------------KING500061200BLAKE285035400CLARK245030600JONES297536900MARTIN125016200...14rowsselected.11/15/2022

ENAMEJOB SALCOMM-------------------------------------KINGPRESIDENT5000BLAKEMANAGER 2850...TURNERSALESMAN1500 0...14rowsselected.SQL>SELECTename,job,sal,comm2FROMemp;空值是一个表达不可用、未分配、未知、或者不适用等意义的值。空值与零和空格的概念不同，应当严格区分。定义空值（Null）11/15/2022

SQL>selectename,12*sal+comm2fromemp3WHEREename='KING';ENAME12*SAL+COMM---------------------KING如果数学表达式中包含了一个空值，那么该数学表达式计算的结果为空值数学表达式中空值的处理定义一个列别名11/15/2022重新命名一个列的名字有时很有用；可以用AS关键字为列名取一个别名；如果别名中包含空格，或者其他特殊自符，则需要用引号（“”）将别名包含起来。

SQL>SELECTenameASname,salsalary2FROMemp;NAMESALARY----------------------

...SQL>SELECTename"Name",2sal*12"AnnualSalary"3FROMemp;

NameAnnualSalary--------------------------

...使用列的别名串接操作符11/15/2022可以将一些列连接起来，也可以将一些列和一些字符串连接起来；串接操作符用两个竖杠(||)表示；可以用这种方法创建一个由字符串组成的结果列。使用串接操作符11/15/2022SQL>SELECT ename||jobAS"Employees"2FROM emp;Employees-------------------KINGPRESIDENTBLAKEMANAGERCLARKMANAGERJONESMANAGERMARTINSALESMANALLENSALESMAN...14rowsselected.文字（literal）字符串11/15/2022文字字符串是包含在一个Select语句中的字符串、数字、或者日期；文字字符串必须用单引号（‘’）括起来；对于返回的每一行，文字字符串都显示一次；在生成报表时非常有用。11/15/2022EmployeeDetails-------------------------KINGisaPRESIDENTBLAKEisaMANAGERCLARKisaMANAGERJONESisaMANAGERMARTINisaSALESMAN...14rowsselected.SQL>SELECTename ||'isa'||job2 AS"EmployeeDetails"3FROMemp;使用文字字符串重复行11/15/2022

SQL>SELECTdeptno2FROMemp;DEPTNO---------10301020...14rowsselected.

查询语句执行的结果，缺省的显示方法是显示所有的行，包括重复的行。消除掉重复行11/15/2022

在SELECT语句中用DISTINCT关键字来消除所有重复的行。SQL>SELECTDISTINCTdeptno2FROMemp;DEPTNO---------102030

显示表结构11/15/2022DESC[RIBE]tablename用SQL*Plus的DESCRIBE命令来展示表的结构.显示表结构11/15/2022SQL>DESCRIBEdeptNameNull?Type-------------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)总结11/15/2022SELECT [DISTINCT]{*,column[alias],...}FROM table;第二部分数据进行限定和排序目标11/15/2022结束本节课后，应当达到如下目标:在一个查询中限定查询出的行对查询出的行进行排序用“选择”限定行11/15/2022“…查询出department为10的所有员工"

EMPEMPNO ENAME JOB ...DEPTNO7839 KING PRESIDENT 107698 BLAKE MANAGER 307782 CLARK MANAGER 107566 JONES MANAGER 20...

EMPEMPNO ENAME JOB ...DEPTNO7839 KING PRESIDENT 107782 CLARK MANAGER 107934 MILLER CLERK 10选择查询出的行11/15/2022SELECT [DISTINCT]{*|column[alias],...}FROM table[WHERE condition(s)];用where子句限定查询出的行.Where子句紧跟在From子句之后.运用WHERE子句11/15/2022SQL>SELECTename,job,deptno2FROMemp3WHEREjob='CLERK';ENAMEJOBDEPTNO----------------------------JAMESCLERK30SMITHCLERK20ADAMSCLERK20MILLERCLERK10字符串和日期11/15/2022SQL>SELECT ename,job,deptno2FROM emp3WHERE ename=;'JAMES'字符串和日期值应当用单引号括起来.字符串值大小写敏感，日期值对于日期的格式敏感。缺省的日期值格式：DD-MON-YY.例如：23-09-02.为02年9月23日比较操作符11/15/2022操作符=>>= <<= <>含义等于大于大于或等于小于小于或等于不等于使用比较操作符11/15/2022SQL>SELECTename,sal,comm2FROMemp3WHEREsal<=comm;ENAMESALCOMM----------------------------MARTIN12501400其它比较操作符11/15/2022操作符BETWEEN

...AND...IN(list)LIKEISNULL含义在两个值之间(包含着两个值)

在列出的值中与给出的字符串匹配是一个空值用BETWEEN操作符11/15/2022ENAMESAL-------------------MARTIN1250TURNER1500WARD1250ADAMS1100MILLER1300SQL>SELECT ename,sal2FROM emp3WHERE salBETWEEN1000AND1500;Lower

limitHigher

limit用BETWEEN操作符来查询出在某一范围内的行.使用IN操作符11/15/2022SQL>SELECT empno,ename,sal,mgr2FROM emp3WHERE mgrIN(7902,7566,7788);EMPNOENAMESALMGR-------------------------------------7902FORD300075667369SMITH80079027788SCOTT300075667876ADAMS11007788用IN操作符来检验一个值是否在一个列表中.使用LIKE操作符11/15/2022SQL>SELECT ename2FROM emp3WHERE enameLIKE'S%';用LIKE进行某个字符串值的通配符匹配，来选出某些行.查询条件中既可以包含字符，也可以包含数字.%代表０个或者多个字符._代表一个字符.使用LIKE操作符11/15/2022SQL>SELECT ename2FROM emp3WHERE enameLIKE'_A%';ENAME----------MARTINJAMESWARD可以混合使用模式字符串.如果要匹配％这个字符，可以用＼％来表示使用ISNULL操作符11/15/2022SQL>SELECTename,mgr2FROMemp3WHEREmgrISNULL;ENAMEMGR-------------------KING用ISNULL操作符来检查有无空值逻辑操作符11/15/2022操作符AND

OR

NOT含义两个条件都为TRUE，则返回TRUE

两个条件中任何一个为TRUE，则返回TRUE如果条件为FALSE，返回TRUE使用AND操作符11/15/2022AND需要所有的条件都为TRUE.SQL>SELECTempno,ename,job,sal2FROMemp3WHEREsal>=11004ANDjob='CLERK';EMPNOENAMEJOBSAL-------------------------------------7876ADAMSCLERK11007934MILLERCLERK1300使用OR操作符11/15/2022OR操作符只需任意条件为TRUE即可SQL>SELECTempno,ename,job,sal2FROMemp3WHEREsal>=11004ORjob='CLERK';EMPNOENAMEJOBSAL-------------------------------------7839KINGPRESIDENT50007698BLAKEMANAGER28507782CLARKMANAGER24507566JONESMANAGER29757654MARTINSALESMAN1250...7900JAMESCLERK950...14rowsselected.使用NOT操作符11/15/2022SQL>SELECTename,job2FROMemp3WHEREjobNOTIN('CLERK','MANAGER','ANALYST');ENAMEJOB-------------------KINGPRESIDENTMARTINSALESMANALLENSALESMANTURNERSALESMANWARDSALESMAN优先级规则11/15/2022

顺序 操作符

1 所有比较操作符

2 NOT 3 AND 4 OR用圆括号可以改变操作符的优先级次序。11/15/2022ENAMEJOBSAL----------------------------KINGPRESIDENT5000MARTINSALESMAN1250ALLENSALESMAN1600TURNERSALESMAN1500WARDSALESMAN1250SQL>SELECTename,job,sal2FROMemp3WHEREjob='SALESMAN'4ORjob='PRESIDENT'5ANDsal>1500;优先级规则优先级规则11/15/2022ENAMEJOBSAL----------------------------KINGPRESIDENT5000ALLENSALESMAN1600用圆括号强制改变操作符优先级.SQL>SELECTename,job,sal2FROMemp3WHERE(job='SALESMAN'4ORjob='PRESIDENT')5ANDsal>1500;11/15/2022SQL>SELECT ename,job,deptno,hiredate2FROM emp3ORDERBYhiredate;ENAMEJOBDEPTNOHIREDATE-------------------------------------SMITHCLERK2017-DEC-80ALLENSALESMAN3020-FEB-81...14rowsselected.用ORDERBY子句进行行的排序ASC:升序,缺省DESC:降序ORDERBY子句跟在SELECT语句之后ORDERBY子句采用降序排序11/15/2022SQL>SELECT ename,job,deptno,hiredate2FROM emp3ORDERBYhiredateDESC;ENAMEJOBDEPTNOHIREDATE-------------------------------------ADAMSCLERK2012-JAN-83SCOTTANALYST2009-DEC-82MILLERCLERK1023-JAN-82JAMESCLERK3003-DEC-81FORDANALYST2003-DEC-81KINGPRESIDENT1017-NOV-81MARTINSALESMAN3028-SEP-81...14rowsselected.按照列的别名来进行排序11/15/2022SQL>SELECTempno,ename,sal*12annsal2FROMemp3ORDERBYannsal;EMPNOENAMEANNSAL----------------------------7369SMITH96007900JAMES114007876ADAMS132007654MARTIN150007521WARD150007934MILLER156007844TURNER18000...14rowsselected.按照多个列进行排序11/15/2022可以按照SELECT列中没有的列来进行排序.SQL>SELECT ename,deptno,sal2FROM emp3ORDERBY deptno,salDESC;ENAMEDEPTNOSAL----------------------------KING105000CLARK102450MILLER101300FORD203000...14rowsselected.ORDERBY后的列的顺序既排序的顺序.总结11/15/2022SELECT [DISTINCT]{*|column[alias],...}FROM table[WHERE condition(s)][ORDERBY {column,expr,alias}[ASC|DESC]];第三部分处理单行的函数目标11/15/2022完成本节课后，应当达到如下的目标：描述在SQL语句中可能用到的各种函数。在SELECT语句中使用字符串、数字、日期的函数。描述转换函数的用法SQL函数函数输入参数1参数2参数n函数处理动作输出结果值两种类型的SQL函数11/15/2022函数单行函数多行函数单行函数11/15/2022function_name(column|expression,[arg1,arg2,...])操作数据项接收参数并返回一个值在返回的每一行上进行处理每一行都有一个处理结果可能要修改数据类型可以进行嵌套接受多个参数，参数可以是一个列或者一个表达式单行函数11/15/2022格式转换字符数字日期常规处理单行函数字符函数11/15/2022字符函数LOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPADTRIM大小写转换函数字符操作函数大小写转换11/15/2022函数结果LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourse对字符串的大小写进行转换11/15/2022SQL>SELECT empno,ename,deptno2FROM emp3WHERE ename='blake';norowsselectedEMPNOENAMEDEPTNO----------------------------7698BLAKE30SQL>SELECT empno,ename,deptno2FROM emp3WHERE LOWER(ename)='blake';使用大小写转换函数查出员工Blake的号码、名字、部门号11/15/2022CONCAT('Good','String')SUBSTR('String',1,3)LENGTH('String')INSTR('String','r')LPAD(sal,10,'*')TRIM('S'FROM'SSMITH')GoodStringStr63******5000MITH函数结果操作字符串字符操作函数使用字符操作函数11/15/2022SQL>SELECTename,CONCAT(ename,job),LENGTH(ename),2 INSTR(ename,'A')3FROMemp4WHERESUBSTR(job,1,5)='SALES';ENAMECONCAT(ENAME,JOB)LENGTH(ENAME)INSTR(ENAME,'A')----------------------------------------------------------MARTINMARTINSALESMAN62ALLENALLENSALESMAN51TURNERTURNERSALESMAN60WARDWARDSALESMAN42数字函数11/15/2022ROUND:对指定的值进行四舍五入

ROUND(45.926,2) 45.93TRUNC: 对指定的值进行取整

TRUNC(45.926,2) 45.92MOD:返回除法计算后的余数

MOD(1600,300)小数小数100使用ROUND函数11/15/2022SQL>SELECTROUND(45.923,2),ROUND(45.923,0),2 ROUND(45.923,-1)3FROMDUAL;ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)----------------------------------------------45.924650使用TRUNC函数11/15/2022SQL>SELECT TRUNC(45.923,2),TRUNC(45.923),2 TRUNC(45.923,-1)3FROMDUAL;TRUNC(45.923,2)TRUNC(45.923)TRUNC(45.923,-1)-------------------------------------------45.924540使用MOD函数11/15/2022SQL>SELECT ename,sal,comm,MOD(sal,comm)2FROM emp3WHERE job='SALESMAN';ENAMESALCOMMMOD(SAL,COMM)-----------------------------------------MARTIN125014001250ALLEN1600300100TURNER150001500WARD1250500250计算工作为salesman的员工的工资（Sal），除以COMM后的余数。使用日期11/15/2022Oracle以一种内部的格式来保存日期:世纪,年,月,日,小时,分钟,秒.缺省的格式：DD-MON-YY.例如：23-JAN-02SYSDATE是一个返回日期和时间的函数.DUAL是一个显示SYSDATE的虚拟表.日期的计算11/15/2022为一个日期值增加或者减少一个数字.计算出两个日期之间相隔的天数.要为日期值增加小时数，可以将小时数除以24后加上。用数学运算符对日期进行计算11/15/2022SQL>SELECTename,(SYSDATE-hiredate)/7WEEKS2FROMemp3WHEREdeptno=10;ENAMEWEEKS-------------------KING830.93709CLARK853.93709MILLER821.36566日期函数11/15/2022两个日期之间的月数MONTHS_BETWEENADD_MONTHSNEXT_DAY LAST_DAYROUND TRUNC 为一个日期增加月份一个日期的下一个指定日子（例如：星期1）的日期某个月份的最后一天对日期进行四舍五入计算 对日期进行取整计算函数描述使用Date函数11/15/2022MONTHS_BETWEEN('01-SEP-05','11-JAN-04')ADD_MONTHS('11-JAN-05',6)NEXT_DAY('01-SEP-05','FRIDAY')LAST_DAY('01-SEP-05')19.6774194'11-JUL-05''02-SEP-05''30-SEP-05'使用日期函数11/15/2022ROUND('25-JUL-05','MONTH')01-AUG-05ROUND('25-JUL-05','YEAR') 01-JAN-06TRUNC('25-JUL-05','MONTH') 01-JUL-05TRUNC('25-JUL-05','YEAR') 01-JAN-05NVL函数11/15/2022NVL(expr1,expr2)如果expr1为null,则返回expr2,否则返回expr1可以使用的数据类型是数字、日期、字符型.数据类型必须能够匹配NVL(comm,0)NVL(hiredate,'01-JAN-97')NVL(job,'NoJobYet')使用NVL函数11/15/2022SQL>SELECTename,sal,comm,(sal*12)+NVL(comm,0)2FROMemp;ENAMESALCOMM(SAL*12)+NVL(COMM,0)------------------------------------------------KING500060000BLAKE285034200CLARK245029400JONES297535700MARTIN1250140016400ALLEN160030019500...14rowsselected.函数的嵌套11/15/2022F3(F2(F1(col,arg1),arg2),arg3)Step1=Result1Step2=Result2Step3=Result3单行的函数可以嵌套到任何一个层次.嵌套函数的计算顺序——先计算深层嵌套，再计算浅层嵌套.嵌套函数11/15/2022SQL>SELECT ename,2 NVL(TO_CHAR(mgr),'NoManager')3FROM emp4WHERE mgrISNULL;ENAMENVL(TO_CHAR(MGR),'NOMANAGER')---------------------------------------KINGNoManager总结11/15/2022Characterdata:

LOWER,UPPER,INITCAP,CONCAT,SUBSTR,INSTR,LENGTHNumberdata:ROUND,TRUNC,MODDatedata:MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC第四部分从多个表中获取数据目标11/15/2022完成本节课的学习后，应当达到如下目标:写出能从多个等连接或者非等连接的表中取出数据的SQL语句。使用外连接来查找数据。写出一个表与其自身连接的SQL语句。从多个表中获取数据11/15/2022

EMPNO DEPTNO LOC--------------------7839 10NEWYORK7698 30CHICAGO7782 10NEWYORK7566 20DALLAS7654 30CHICAGO7499 30CHICAGO...14rowsselected.EMPDEPTEMPNO ENAME ... DEPTNO

------ ----- ... ------

7839 KING ... 107698 BLAKE ... 30... 7934 MILLER ... 10DEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON什么是连接？11/15/2022SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column1=table2.column2;用一个连接来从多个表中获取数据.在Where子句中书写连接的条件.如果某个列的名字在多个表中出现了，那么需要在列的名字前面加上表名作为前缀.笛卡尔积11/15/2022以笛卡尔积连接的表具有下列特征:连接条件被忽略。第一个表中的所有的行与第二个表中的所有行相连接。如果在WHERE子句中加入条件，那么可以避免笛卡尔积.产生一个笛卡尔积11/15/2022ENAME DNAME------ ----------KING ACCOUNTINGBLAKE ACCOUNTING...KING RESEARCHBLAKE RESEARCH...56rowsselected.EMP(14行)DEPT(4行)EMPNO ENAME ... DEPTNO

------ ----- ... ------

7839 KING ... 107698 BLAKE ... 30... 7934 MILLER ... 10DEPTNODNAME LOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON“笛卡尔积运算结果:

14*4=56行”连接的类型11/15/2022等连接非等连接外连接自连接什么是等连接？11/15/2022

EMPDEPTEMPNOENAMEDEPTNO--------------------7839KING107698BLAKE307782CLARK107566JONES207654MARTIN307499ALLEN307844TURNER307900JAMES307521WARD307902FORD207369SMITH20...14rowsselected.DEPTNODNAMELOC-------------------------10ACCOUNTINGNEWYORK30SALES CHICAGO10ACCOUNTING NEWYORK20RESEARCH DALLAS30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO20RESEARCH DALLAS20RESEARCH DALLAS...14rowsselected.外键主键用等连接获取记录11/15/2022

SQL>SELECT emp.empno,emp.ename,emp.deptno,

2dept.deptno,dept.loc3FROM emp,dept4WHERE emp.deptno=dept.deptno;EMPNOENAME DEPTNODEPTNOLOC--------------------------------7839KING 1010NEWYORK7698BLAKE 3030CHICAGO7782CLARK 1010NEWYORK7566JONES 2020DALLAS...14rowsselected.限定不明确的列名11/15/2022用表名作为前缀来限定在多个表中都出现的列名字.利用表的前缀可以提高性能.使用不同的别名来标志在不同表中的相同的名字。用AND操作符增加查询条件11/15/2022EMPDEPTEMPNOENAMEDEPTNO--------------------7839KING107698BLAKE307782CLARK107566JONES207654MARTIN307499ALLEN307844TURNER307900JAMES307521WARD307902FORD207369SMITH20...14rowsselected.DEPTNODNAME LOC--------------- --------10ACCOUNTING NEWYORK30 SALES CHICAGO10ACCOUNTING NEWYORK20RESEARCH DALLAS30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO30SALES CHICAGO20RESEARCH DALLAS20RESEARCH DALLAS...14rowsselected.使用表的别名11/15/2022SQL>SELECTemp.empno,emp.ename,emp.deptno,2 dept.deptno,dept.loc3FROMemp,dept4WHEREemp.deptno=dept.deptno;SQL>SELECTe.empno,e.ename,e.deptno,2d.deptno,d.loc3FROMempe,deptd4WHEREe.deptno=d.deptno;使用的表的别名来简化查询连接多个表11/15/2022NAME CUSTID----------- ------JOCKSPORTS 100TKBSPORTSHOP 101VOLLYRITE 102JUSTTENNIS 103K+TSPORTS 105SHAPEUP 106WOMENSSPORTS107... ...9rowsselected.CUSTOMERCUSTIDORDID--------------101610102611104612106601102602106604106605...21rowsselected.ORDORDIDITEMID-------------61036111612160116021...64rowsselected.ITEM非等连接11/15/2022EMPSALGRADE“在EMP表中，如何查出在SALGRADE表所示的每个工资段中的员工号码？”EMPNOENAMESAL-------------------7839KING50007698BLAKE28507782CLARK24507566JONES29757654MARTIN12507499ALLEN16007844TURNER15007900JAMES950...14rowsselected.GRADE LOSALHISAL----------------1700 120021201 140031401 20004 2001 300053001 9999用非等连接查询记录11/15/2022ENAMESALGRADE----------------------------JAMES9501SMITH8001ADAMS11001...14rowsselected.SQL> SELECT e.ename,e.sal,s.grade2 FROM empe,salgrades3 WHERE e.sal4 BETWEEN s.losalANDs.hisal;外连接11/15/2022

EMPDEPT在OPERATIONS部门中没有任何员工ENAME DEPTNO

----- ------

KING 10BLAKE 30CLARK 10JONES 20... DEPTNODNAME----------------10 ACCOUNTING30 SALES10 ACCOUNTING20 RESEARCH... 40 OPERATIONS外连接（基于Oracle）11/15/2022SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column(+)

=table2.column;SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column=table2.column(+);可以用外连接来查出在一个表中不匹配连接条件的行.外连接的符号是：(+).外连接（基于Informix）外连接outerSELECT table1.column,table2.columnFROM table1,outertable2WHERE table1.column

=table2.column;使用外连接11/15/2022SQL>SELECT e.ename,d.deptno,d.dname2FROM empe,deptd3WHERE e.deptno(+)=d.deptno4ORDERBY e.deptno;ENAMEDEPTNODNAME--------------------------------KING10ACCOUNTINGCLARK10ACCOUNTING...40OPERATIONS15rowsselected.自连接11/15/2022EMP(WORKER)EMP(MANAGER)“在WORKER表中的MGR列等于MANAGER表中的EMPNO列”EMPNO ENAME MGR

----- ------ ----

7839 KING 7698 BLAKE 78397782 CLARK 78397566 JONES 78397654 MARTIN 76987499 ALLEN 7698EMPNO ENAME

----- --------

7839 KING7839 KING7839 KING7698 BLAKE7698 BLAKE11/15/2022WORKER.ENAME||'WORKSFOR'||MANAG-------------------------------BLAKEworksforKINGCLARKworksforKINGJONESworksforKINGMARTINworksforBLAKE...13rowsselected.SQL>SELECTworker.ename||'worksfor'||manager.ename2FROM empworker,empmanager3WHERE worker.mgr=manager.empno;表与自身连接总结11/15/2022SELECT table1.column,table2.columnFROM table1,table2WHERE table1.column1=table2.column2;等连接非等连接外连接自连接第五部分用分组函数来对数据进行聚集目标11/15/2022完成本节课的学习后，应当完成下面的目标:识记常用的分组函数。描述分组函数的用处。使用GROUPBY子句对元组进行分组使用HAVING子句来筛选分组11/15/2022EMP“EMP表中最大的SAL值”DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250MAX(SAL)---------5000分组函数对一组数据行进行操作，并对每个组得出一个结果.什么是分组函数？分组函数的类型11/15/2022AVGCOUNTMAXMINSTDDEVSUMVARIANCE使用分组函数11/15/2022SELECT [column,]group_function(column)FROM table[WHERE condition][GROUPBY column][ORDERBY column];使用AVG和SUM函数11/15/2022AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)-----------------------------------1400160012505600SQL>SELECT AVG(sal),MAX(sal),2 MIN(sal),SUM(sal)3 FROM emp4 WHERE jobLIKE'SALES%';对于数字类型的数据，可以使用AVG和SUM函数.使用MIN和MAX函数11/15/2022

SQL>SELECT MIN(hiredate),MAX(hiredate)2FROM emp;MIN(HIREDMAX(HIRED------------------17-DEC-8012-JAN-83可以对许多类型的数据使用MIN和MAX函数.使用COUNT函数11/15/2022

COUNT(*)---------6SQL>SELECT COUNT(*)2FROM emp3WHERE deptno=30;COUNT(*)返回查询出的总行数.使用COUNT函数11/15/2022SQL>SELECT COUNT(comm)2FROM emp3WHERE deptno=30;COUNT(COMM)-----------4COUNT(expr)返回expr值非空的行的数.11/15/2022SQL>SELECTAVG(comm)2FROMemp;AVG(COMM)---------550分组函数和空值分组函数忽略了行中所有的空行.11/15/2022SQL>SELECTAVG(NVL(comm,0))2FROMemp;AVG(NVL(COMM,0))----------------157.14286分组函数和NVL函数的结合NVL函数使得分组函数可以处理空值.11/15/2022EMP“EMP表中每个部门的平均工资”2916.666721751566.6667DEPTNOSAL------------------1024501050001013002080020110020300020300020297530160030285030125030950301500301250DEPTNOAVG(SAL)----------------102916.6667202175301566.6667创建分组数据11/15/2022SELECT column,group_function(column)FROM table[WHERE condition][GROUPBY group_by_expression][ORDERBY column];创建分组数据:GROUPBY子句将表中的数据行用GROUPBY语句分为几个组.使用GROUPBY子句11/15/2022SQL>SELECTdeptno,AVG(sal)2FROMemp3GROUPBYdeptno;DEPTNOAVG(SAL)------------------102916.6667202175301566.6667在SELECT语句中，没有使用分组函数的列必须在GROUPBY子句中.使用GROUPBY子句11/15/2022SQL>SELECTAVG(sal)2FROMemp3GROUPBYdeptno;AVG(SAL)---------2916.666721751566.6667GROUPBY后面的列可以不出现在SELECT链中.根据多个列进行分组11/15/2022EMP“每个部门中，每种工作的工资总和”

DEPTNOJOBSAL---------------------10MANAGER245010PRESIDENT500010CLERK130020CLERK80020CLERK110020ANALYST300020ANALYST300020MANAGER297530SALESMAN160030MANAGER285030SALESMAN125030CLERK95030SALESMAN150030SALESMAN1250JOBSUM(SAL)---------------CLERK1300MANAGER2450PRESIDENT5000ANALYST6000CLERK1900MANAGER2975CLERK950MANAGER2850SALESMAN5600DEPTNO------101010202020303030在多个列上使用GROUPBY子句11/15/2022SQL>SELECTdeptno,job,sum(sal)2FROMemp3GROUPBYdeptno,job;DEPTNOJOBSUM(SAL)---------------------------10CLERK130010MANAGER245010PRESIDENT500020ANALYST600020CLERK1900...9rowsselected.分组函数的误用11/15/2022SQL>SELECT deptno,COUNT(ename)2FROM emp;SELECTdeptno,COUNT(ename)*ERRORatline1:ORA-00937:notasingle-groupgroupfunctionDep