SQL语言的分类

1、SQL语言的四大组成部分DQL DML DDL DCLSQL语言共分为四大类：DQL（数据查询语言）,DML（数据操纵语言）,DDL（数据定义语言）,DCL（数据控制语 言）。1数据查询语言（DQL）?查询语句的一般结构?单表查询?多表查询?嵌套查询?分组查询?集合查询查询语句的一般结构? SELECT ALL DISTINCT目标列表达式 , 目标列表达式? FROM表名或视图名 , 表名或视图名? WHERE条件表达式? GROUP BY列名1HAVING条件表达式? ORDER BY 列名 2ASC|DESC;单表查询：无条件、有条件、排序无条件：SELEC冲的目标表达式 可以是表中的列

2、，也可以是表达式，包括算术表达式、字符串常数、函 数等。（字符串用单引号定界）1. 使用算术表达式（+、-、*、/）Select empno, sal*0.8 from emp;2. 使用字符常量Select empno, ' Name is: ' , ename from emp;3. 使用函数Select empno, UPPEFR ename） from emp;4. 改变列标题Select empno employeenamesalAS salary , job,dept from emp;5. 使用连接字符串Select en ame |: '| |1'

3、;| | month salary= '| sal as mon thy from emp6. 消除重复行SQL> select ALL en ame from emp;SQL> select DISTINCTe name from emp;有条件：WHERE常用的查询条件查询条件谓词比较大小=,>,<,>=,<=,< >确定围BETWEEN AND NOT BETWEEN AND确定集合IN, NOT IN字符匹配LIKE NOT LIKE空值IS NULL, IS NOT NULL多重条件AND, OR确定围:> select

4、emp no,en ame,sal ,dept no from emp where dept no betwee n 10 and 20; 确定集合:> select emp no,en ame,sal,dept no from emp where dept no in （10,30）;等于 select emp no,en ame,sal,dept no from emp where dept no =10 or dept no=30;字符匹配：NOT LIKE匹配串>'1. <匹配串可以是一个完整的字符串，也可以含有通配符的字符串。 通配符包括%、2. % （百分

5、号）代表任意长（长度为 0）字符串。3. _ （下划线）代表任意单个字符。SQL> select empno,ename,sal,deptno from emp where ename like'S%'SQL> select en ame from emp where en ame like '_S%'空值：涉及空值查询时使用IS NULL或IS NOT NULL，这里的IS不能用二替代 多重条件：用逻辑运算符NOT AND和OF来联结多个查询条件。优先级：NOT AND OR（用户可以用括号改变优先级）。 IN谓词实际上是多个OR运算的缩写。SEL

6、ECT勺交互查询：? 使用替代变量? Select * from emp where en ame=' &n ameSQL)b scltct 1呷 vhvrr吊械:SM1Wa 1: sritct frm np Ntan1: Hint * fron enp tfiere 廈冷齋：EMU EWWJOBHUI HltEMTESltTMCLtRKIfllU排序：? ORDER BY从句要放在SELECT语句的最后.? 表达式排序： SQL> select empno,ename,sal , sal*12 from emp order by sal*12 desc; ? 多列排序：

7、SQL> select emp no ,dept no ,sal from emp order bydept no asc,sal desc ;显示：按部门号升序，相同部门号的工资降序排列。? 另S名排序： SQL> select empno,ename，sal*12 annsal from emp order byannsal desc;多表查询?连接条件写在WHERE从句中?同一列名出现在多个表中时，列名前要加上表名来区分 连接类型：?交叉连接：笛卡尔积。?连接：使用比较运算符进行表间某列或某些列数据的比较操作，并列出这些表中与连接条 件相匹配的数据行。连接分为 等值连接、非等

8、值连接和自身连接。例:查询所有工资大于2000的雇员的和他的经理的名字。（自身连接例子）SQL>select first.e name,seco nd.e name,first.sal from emp first,emp sec ond where first.sal >2000 and first.dept no =sec on d.dept no and seco nd.job = 'MANAGER' 结果?ENAMEENAMESAL?CLARKCLARK2450?KINGCLARK5000?JONESJONES2975?CLARKJONES3000?SCOT

9、TJONES3000?BLAKEBLAKE2850?外连接：外连接不仅要列出与连接条件相匹配的数据行，还要列出左表、右表或两个表中 所有不符合查询条件的数据行。外连接分为 左外连接、右外连接和全外连接。1）用外连接可以看见通常不符合连接条件的行2）外连接操作符：（+）.夕卜连接：与连接相比，可以避免信息丢失现象。LNAH.PEPTHOr.l ARH1hKCCOum IMOKENCi»ACCaUHT I HRMILLER1HflCOtJNl I MGsrtt th2tJ<flESEARCMAbAHSNLSEARCHFDRDmrFARCihSUilTTRaESEAkCHJHCS29

10、RESEARCHALLtM3DMLESQLAKI3B3JALE5：HARTLHfiALESJHFS3dSAI F?TURMRsaSALESWfIKD3 DiSIILESHDilPkKni JUKSs：仲近)m( wisctD 兮QL$色ndfi" 甬亠dpt："心.ti訴.jtiTHhZ f row *np .4«pt 日i1 Hhrp «T d» pt nn (*)- d B d pptir d :己选tf'Mt-灯啪存EMP表的后面.则 无论EMP表是否存在相配的 行，都将选择DEPT表的所 有的行。如果存在相配的行' 则将

11、会选扌睪这些相配的行； 如果不存在相配的行，则“ missing"?'的値为NULL。嵌套查询?概念?查询块：一个select-from-where 语句称为一个查询块。? 嵌套查询：将一个查询块嵌套在另一个查询块的where子句或having子句中的查询。子查询的结果用于建立主查询的查找条件。?处理步骤般由里向外进行处理?注意?子查询可以有多层，所存取的表可以是父查询没有存取的表。? 子查询中不能有 ORDER B子句。?子查询选出的记录不显示。单行嵌套查询示例查询所有工资高于Jones的雇员的、工资等信息。SQL> select en ame,job,sal fro

12、m emp where sal > (select sal from emp where en ame = 'JONES');结果ENAME JOBSALSCOTTANALYST3000KINGPRESIDENT5000CLARKANALYST3000子查询中使用组函数¥enamefjobr sal1ejnpaW1sal =SELECTWIN(sal)FRCM；USQL> SELECT2 KCM3 (WHERE4HAVING从句中使用子查询ETOM GROUP W£ HW/INGemp deptnoHIM (Sal>(SELECTMIN(s

13、al) 、enpWHEREdeptno - 2C):SQL> SELECTdeptno, HIK(sal6多行嵌套查询（子查询的结果是多行的）多值比较运算符PJOTIN字覆的值是晉在所选崖合中NOTJANY是否将宇段的信与子查询遁回结果中的一个 值进行比较 <心丫；籀足一个条祎为直1NOTJALL是否将所选的值与集台中所育的值进行比较NOTEXISTSEXISTS®示一个子查询至少遠回一行时条件 鵰立，NOT EXISTS表示一个子查洞不返回 任何行时条件成血。in 、exists、not in、not exists 之间的区别all 、any （some替代）、not

14、in、in 之间的区别来看下面这样一条 SQL语句：select * from stude nt where 20 <all (select score from stude nt)其中，(select score from stude nt)返回的是所有分数的记录集只有当记录集中的所有分数都大于20后，才会执行前面的 SQL语句。显然，任何一条记录都大于20.如果把它改成50,则查不到任何数据，因为只要有一条记录小于50,条件都不成立但是，如果把all改面any就不一样了：select * from stude nt where 50 <any （select score fro

15、m stude nt）它的意思是，只要记录集中的记录有一条大于50,条件就成立。1、ALL与ANY的关系就是，AND与OR的关系。2、 ANY与SOME等价，据说搞这两个不同的词出来是为了迁就英语语法。例如，在用=ANY的地方在（英语） 语法上 就应该是=SOME some是新版本的SQL中取代any的关键字，用法基本一样。3、 IN与=ANY等价，均表示，变量在（子查询）列表之中，即a IN （table B） 表示a = ANY B.b4、 NOT IN与<> ALL等价，而不等于<> ANY，前两者均表示，变量不在（子查询）列表之中，即a NOT IN （tabl

16、eB）表示a <> ALL B.b 。而如果a <> ANY B.b，则只要任意一个 b<>a就true 了。5、 IN与EXISTS的性能区别主要来自，IN会编列子查询的每行记录，然后再返回，而EXISTS则只要遇到第一个满 足条件的记录就马上返回。6、 NOT IN与NOT EXISTS并不能完全等价，只有当子查询中，select关键字后的字段有 not null约束或者有这种 暗示时才用NOT IN。例查询所有雇员的工资都不低于1000的部门的所有人员信息SQL>select en ame,sal,job from empwhere dept n

17、o not in (select dept no from empwhere sal < 1000);SQL>select en ame,sal,job from emp where dept no != all (select dept no from empwhere sal <1000); 结果ENAMESAL JOBCLARK2450 MANAGERKING5000 PRESIDENTMILLER1300CLERK例2查找有工资在4000-5000之间的雇员所在部门的所有人员信息。SQL> select en ame,sal,job from emp where

18、 dept no= any (select dept no from empwhere sal between 4000 and 5000);结果ENAMESAL JOBCLARK2450 MANAGERKING5000 PRESIDENTMILLER1300 CLERK例3查找那些雇员的部门号在部门表中没有的雇员的信息。SQL> select en ame, sal, job from emp wherenot exists(select * from dept where dept no=emp.dept no);常用统计函数(一般where后不用统计函数)函数格式戶能AVGQDIS

19、TlhICTlALL)计算一别值的平均值(娶求數COUNTCOUhT(DISTINCT|ALL| * >统计元纟冃个埶COUNTCOUhT(DISTINCT|ALL)绽计一列中非空值的个数MAXMAX(DIST1NCT1ALL w列名a )求一列值中飾最犬值MINMIN(DIST1NCT|ALL)求一別值中的最小值SUMSUMdDISTMCT1ALL監列老a )计真一列值的总和(要求敎值 列)STBDEVSTDDEV(<5iJ>)-计負一别值的标淮差ARIAN CEARIANCEC<W>)计牡一刘值的方差?函数AVG和SUM应用于数值型? MIN和MAX可用于任意

20、类型.?函数AVG和SUM应用于数值型 ? COUNT(*)返回符合条件的记录数.(包括含null的所有行数)? COUNT(xpr)返回的非NULL的行数.?集合函数忽略列中的null值(COUTN除外).SQL> SELECT AVG(comm) FROM emp;?在组函数中使用 NVL函数，NVL函数强制集合函数包括null 值.SQL> SELECT AVG(NVL(comm,0) FROM emp;总结：当NVL(comm,0)时，佣金值赋0值，显示结果为0，不空 当NVL(comm,-1)时，佣金值赋-1值，不显示结果，为空NVL函数：?功能：把可能包含了空值的表达式

21、 1,转换成有实际意义的数据表达式 2? 格式：NVL(表达式1,表达式2)?作用的数据类型：可用于字符型、数值型、日期型 .?注意事项：转换前后的类型必须一致：-NVL(comm,0)NVL(hiredate,'01-JAN-97')-NVL(job,'No Job Yet')分组查询(一般句中有每个、各个字样的要用到分组查询)Having后一般是统计函数,不能是emp.depno=dept.depno这样的等式常用统计函数常与分组查询一块用?在包含GROUPY子句的查询语句中，SELECT?句后面的所有字段列表(除聚集函数外)， 均应该包含在 GROUP B

22、Y?句中。?在GROUP BY从句中使用多个列SQL > SELECT dept no, job, sum(sal) FROM emp GROUP BYdept no, job?使用HAVING从句选择满足条件分组?执行次序：?对行进行分组.?对每组数据执行组函数.?返回符合HAVING从句的分组结果.? WHER与 HAVING勺区别?作用对象不同。WHER作用于基本表或视图，从中选择满足条件的元组 HAVING短语作用于组，从中选择满足条件的组。?例查询平均工资超过2000的部门，并按部门号进行排序? SQL> SELECT dept no, avg(sal) from emp

23、?GROUP BY dept no?HAVING AVG(SAL)>2000?ORDER BY dept no;?结果?DEPTNO AVG(SAL)103725202175集合查询：查询结果的集合运算?并集(UNION ALL)?交集(INTERSECT ALL)?差集(MINUS ALL)2数据操纵语言数据操纵语言DML主要有三种形式:1)插入：INSERT2) 更新：UPDATE3) 删除：DELETEINSERT语句?插入单个元组格式：INSERT INTO表名 （ 属性列1,属性列2）VALUES常量1,常量2）;1) 在表定义时说明了 NOT NUL的属性列不能取空值，否则会

24、出错。2) 如果INTO子句中没有指明任何列名，则新插入的纪录必须在每个属性列上均有值。3) 指定列名时，列名顺序任意，列值与列名对应.4) 字符型和日期型数据在插入时要加单引号 。例SQLINSERT INTODEPT VALUES(6O,'PRODUCTION','SAN FRANCISCO');SQL INSERT INTO DEPT(DNAMEQEPTNO) VALUES('TEST',70);?插入子查询结果例向表中插入一条与SMITH勺容相同的记录，但改为了 FAN雇员号改为了 8000.SQLINSERT INTO EMP (EMP

25、NO, ENAME, JOB, MGR ,HIREDATE, SAL, COMM, DEPTNO) SELECT 8000, 'FAN',JOB,MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP WHERE ENAMI ='SMITH'UPDATED句?修改某一个元组的值格式：UPDATE表名SET列名=表达式 , 列名=表达式 WHER条件;例将雇员号为8000的雇员提升为部门20的经理，工资增加1000。.SQLUPDATE EMP SET JOB = 'MANAGER',SAL = SAL + 1000,D

26、EPTN0 = 20 WHERE EMPNO = 8000;?修改多个元组的值例将部门号为20的所有的雇员的工资加10%.SQL UPDATE EMP SET SAL = SAL + 0.1*SAL WHERE DEPTNO = 20;?带子查询的修改语句例将20号部门的所有雇员的工资都变为该部门平均工资加1000.SQL UPDATE EMP SET SAL = 1000 + SELECT AVG (SAL) FROM EMP WHERE DEPTN0 = 20 WHERE DEPTNO = 20;DELETED句?删除某一个元组的值格式：DELETE FRO表名 WHER条件;例删除名字为

27、FAN的雇员.SQL DELETE FROM EMP WHERE ENAME = 'FAN'?删除多个元组的值例删除EMP表中的所有数据.SQL DELETE FROM EMP?带子查询的删除语句例删除所有与雇员FAN相同部门的雇员信息。SQLDELETEZROMEMFWHERDEPTN(= (SELECTDEPTNOROMEMFWHERENAM= 'FAN');3数据定义语言DDL数据定义语言DDL用来创建数据库中的各种对象 表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER| | | | |表视图索引同义

28、词簇创建表(CREATE的一般格式：CREATE TABE 表名 (列名数据类型 列级完整性约束条件,列名 数据类型 列级完整性约束条件,列名 数据类型 表级完整性约束条件)；CREATE TABE 的 总结：1、实体完整性规则：主键(所有主属性)非空。2、参照完整性规则：不得引用不存在的外键(定义外键)3、用户定义的约束(CHECK,UNIQUE,NOT NULL) 例创建学生表SQL> CREATE TABLE STUDENT(sno CHAR(5)primary key.sn ame CHAR(20)not nu II,ssex CHAR(2)sage NUMBER(2)CHECK

29、(ssex IN( 男',女),CHECK (sage between 16 and 20),Sclass CHAR(7);not nu II,创建课程表SQL> CREATE TABLE COURCE(CNO CHAR(4) PRIMARY KEY,CNAME CHAR(16) NOT NULL);创建成绩表SQL£REATE TABLE score(SNO CHAR(4) ,CNO CHAR(16) ,SCORE1 NUMBER (5, 2),PRIMARY KEY (SNO CNO)FOREIGN KEY (SNOREFERENCES STUDENT (SNO ,

30、FOREIGN KEY (CNOREFERENCES COURCE (CNO)；例创建新表，结构与STUDEN相同，并拷贝数据。SQL> CREATE TABLE Stude nt_COPASSELECT * FROM STUDENT;例创建新表，结构与STUDEN相同，不拷贝数据。SQL>CREATE TABLE Stude nt_COPY AS SELECT * FROM STUDENT where1=2; ?基本表的修改一般格式：ALTER TABL呂表名 >ADD <新列名 ><数据类型 >完整性约束DROP <完整性约束名>MOD

31、IFY <列名 ><数据类型>?说明ADD子句用于新增列及其完整性约束条件DRO子句用于删除指定的完整性约束条件；MODIFY子句用于修改原有的列定义，包括修改列名和数据类型。SQL没有提供删除属性列的语句，用户只能间接实现这一功能。方法是先将表中要 保留的列及其容复制到一个新表中，然后删除原表，再将新表重新命名为原表。例删除STUDEN表上SNAM列上的唯一约束。ALTER TABLE STUDENT DROUNIQUE(SNAME);例删除STUDEN表上建立的主键约束。ALTER TABLE STUDENT DROPPRIMARY KEY;例为表STUDEN添加一

32、个新的列。ALTER TABLE STUDENT ADDSID CHAR(18) NULL);注意：新增加的字段只能作为表的最后一个字段。例将刚添加的列SID改为DATE类型。ALTER TABLE STUDENT MODIFYSID DATE;?基本表的删除DROP TABLE 表名 ；ALTER TABLE的补充：(constraint 定义主建的约束名 或constraint 定义外建的约束名)*建表后声明主关键字；table_nameconstraintprimary_a primary ke( ) 注意：table_name是表的名字primary_a是定义了主

33、槌的约束名字column naniE是要被定义为主键的字段名。也可以豈圭文主諱时不添加约耒名字- alter ta&k table_name(column_name);索引的创建(INDEX 般格式：(unique对应表，distinct 对应列)CREATEUNIQUE INDEX< 索引名 >ON< 表名 >(colum n1,colu mn2 )tablespace 表空间名storage storage_clause);?说明表名是要建立索引的基本表名字。索引可建立在一列或多列上，割裂名之间用逗号分隔。索引值的排列次序缺省为ASCUNIQUE指明此索引的

34、每一个索引值只对应唯一的数据记录。（唯一性）例为表EMPS立以ENAM为索引列的索引，索引名为emp_index,索引存放的表空间为indexSQLCREATE INDEX emp_i ndex ON EMP（ENAME DESC） tablespace index ;例在表DEPT勺DNAM列上建立唯一性索引，索引名为 ON_DNAMESQLCREATE UNIQUE INDEX ON_DNAME ON DEPT（DNAME DESC）;?当表中有数据时，建立索引将检查数据的唯一性，?如果出现重复，将有下列提示：? ORA-01452:无法 CREATE UNIQUE INDEX?找到重复的

35、关键字?建立了唯一性索引后，以后对数据的修改将自动进行? 唯一性验证。索引的删除：? 一般格式：DROP INDEX索引名称;总结索引：索引一旦建立，就由 系统使用和维护，不需要用户干预。索引的建立是为了减少查询时间，但如果数据增删频繁，系统将会花费很多时间来维护索引，因 此应该删除一些不必要视图的创建（VIEW 般格式：CREATE VIEW视图名 （ 列名 , 列名 ）AS SELECT 语句WITH CHECK OPTION;说明? 其中子查询可以是任意复杂的 SELECT语句，但通常不允许含有ORDER B子句和 DISTINCT短 语。? WITH CHECK OPTIO表示对视图进

36、行UPDATE, INSER和 DELETES作时要保证更新、插 入和删除的行满足视图定义中的谓词条件。? 属性列名或者全部省略或者全部指定，没有第 3种选择。例建立一个视图，使它包含所有工资高于 3000的雇员的、工资、部门以及部门地址SQLCREATE VIEW HIGHSALNAME, SAL, DEPT, LOCASSELECTENAME, SAL, DNAME, LOCFROM EMP, DEPTWHERE SAL>3000 AND DEPT.DEPTNO =EMP.DEPTNO;?视图的删除? 一般格式：DROP VIEW 视图 >?例删除TEST视图。DROP VIE

37、W TEST;总结：视图是保存select语句的，为了下一次懒得写而存在的，下一次只需select * from HIGHSAL;4数据控制语言DCL数据控制语言DCL用来授予或回收访问数据库的某种特权， 并控制数据库操纵事务发生的时间及 效果，对数据库实行监视等。如：1) GRANT 授权。2) ROLLBACK WORK TO SAVEPOINT回退到某一点。回滚-ROLLBACK回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;ROLLBAC后的数据状态：?数据改变被取消(Undo).?数据恢复到以前状态?被影响的行的锁被释放.L SQL> DE

38、 LETS FROMerapLcryee ;L14Rollback 到某一点：?用SAVEPOIN在当前事务中指定该点.?使用 ROLLBACK TO SAVEPOINT!至该点.SQL>UPDATEEMP SET SAL = SAL + 0.1*SAL WHERE DEPTNO = 20;SQLWAVEPOINT UPDATE_d oneSQL>INSERT INTO DEPT(DNAME,DEPTNO) VALUES('TEST',70);SQL>ROLLBACK TUPDATE_d one3) COMMIT WORK提交。COMMIT/ROLLBACK数

39、据的状态：被修改的数据可以修改当前用户可以看到DML操作的数据改动结果.其他用户不能看到DML操作的数据改动结果.被影响的行被锁定(lock);其他用户不能修改被影响的行.COMMIT!的数据状态：被改变的数据被持久写入DB. 以前的数据状态不可恢复.所有用户都可以看到.被锁定的行解锁一其他用户可以操作这些行.全部savepoint被清除.总结：一旦commit就不能rollback提交数据有三种类型：显式提交、隐式提交及自动提交。显式提交：用COMMITS令直接完成的提交为显式提交。其格式为：SQI>COMM；例子修改I UpdateI 2 SETdeptna - 10S VUE PE

40、 吨皿> =77S2:II roD Qpdatftd*执行SQLCOMMIT?隐式提交：用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER AUDIT COMME N T3ONNECTCREAT E DISCONNECTDROPEXIT , GRANT NOAUDIT QUIT, REVOKE RENAME自动提交：若把AUTOCOMM设置为ON则在插入、修改、删除语句执行后, 系统将自动进行提交，这就是自动提交。其格式为：SQ>SET AUTOCOMMIT ；N 下边有一些函数的定义，往下翻页字符串函数:函数诡回值ASCII(ehar)字符串首字符的ASC码值CHR(n

41、)ASC码值为口的字符CONCAT把两个列値拼接起来，操作符更通用INITCAP(char)将字符串中誓个单词的苜字母大写INSTR (char, c)返回指定字符的位置LEN GTH (char)计算字苻串的长度SUB STR(chaijn:n)求子串column中从起绘位置m开绘长度 为口的子串RcylaQ(str I t5ti2.5ti3)rLOWTRfchar).UPPER(chai)将宇符串中所有的大写宇母变为小LPADfcharl jiLchajQD从左侧用字符串ch&补齐字符串 char 1至丧度n （右对齐）RPAD (char l:n=chai 2)从右侧用char2

42、补"齐char 1至长度n一LTRIMCchait.SET)把皿中最左侧的若干个宇符去扌卓. 以使基苜字犢不在SET中RTRIM(charCSET)把由址中最右侧的若干个字符去掛 以使苴尾字符不在SET中TO_DATE (char =fmt )将字符型的日期转换为日期型TO_NLTMBER<char: f rat)将字符串转换为数字字符处理函数示例函数结果CONCAT( Good j String *)Good StringSUBSTR(* String 1,3)StrLENGTHS String6INSTRfr strings p “)3LPADfsal 0/* > )

43、*soaoTRIM( - S - FROMSMITH JMITH算数函数:固数返回值ABS(n)返酮的绝时值CELL(n)返回大于或等于的最小整数EXP(n)板回匕的n次幕ELOOR(n)返回小于或等于n的最大整数LN(n)返回以E为底的口的只擞LOG 血 u)返冋以血为底的口的录擞MODfm,!)遼回皿除以口的余数Pio返回常数 3.POWER(mn)L返回nn的n吹方1ROUKD(m;n对m进行四舍五入b大于0时，将m四舍 五入到1遨点右边n位，口等于零时.表示 对辺进行取整，讪于曲办则小数点左边 的数字位萱极圆整。SIGNfn)判断"的止负&大于0返回，n等于0返回& 垃小手驱向”】SQRT(n>返回点平方根SQUAREfa)返回H的平方TR.UNC(ni