ORACLE数据库教程-SQL基础知识篇.ppt

ORACLE数据库教程 你可以不知道 但是绝对不能不懂 毁人不倦语录 2007年5月 SQL基础知识篇 SQL 001 简介 SQL StructuredQueryLanguage 结构化查询语言是IBM公司SanJose实验室为SystemR而设计的查询语言 后被国际标准化组织 ISO 批准作为关系数据库语言的国际标准 SQL目前遵循的是1992年标准 即SQL 92 各数据库厂家对SQL 92标准均有扩充 扩充部分不能完全保证数据库之间的兼容性 我的热情会灼伤一些人 但我是无心的 毁人不倦语录 简介 SQL可以分为 基础篇只介绍前两种 DML 数据操作语言 DataManipulationLanguage select insert update delete 其他 truncate DDL 数据定义语言 DataDefinitionLanguages create drop alter 其他 rename DCL数据控制语言 grant revoke setrole事务控制 commit rollback savepoint 其他 locktable setconstraint s settransaction 审计控制 audit noaudit系统控制 altersystem会话控制 altersession其他语句 comment 添加注释 explainplan analyze validate call 如果我的话没有错 一定是你听错了 毁人不倦语录 DDL数据定义语言 CREATE 建立 ALTER 修改 DROP 删除 无知者无畏 无畏者不一定无知 毁人不倦语录 语言描述约定 表示可选项 表示选择项CREATE 粗体表示关键字或必输项Table name 斜体表示用户输入项创建 蓝色表示说明 如果1 1 2 我相信那说的是团队的力量 毁人不倦语录 DDL数据定义语言 CREATE 创建用户CREATEUSERuser nameIDENTIFIEDBYpassword DEFAULTTABLESPACEtablespace name TEMPORARYTABLESPACEtablespace name 创建user name用户 密码为password 使用tablespace name表空间 如果没有指定表空间 默认表空间为system表空间 Oracle10g有用户默认表空间设置 建议在创建用户时一定要指名表空间 先有鸡 还是先有蛋 这么重要的问题还是留给鸡去考证吧 毁人不倦语录 DDL数据定义语言 CREATE 创建数据表CREATETABLEtable name Column1datatype default notnull primarykey constraintkey nameprimarykey column list usingindextablespacetablespace name tablespacetablespace name Datatype是数据类型 varchar2 x number x x date integer等 Notnull非空限制 如果不写表示可为空 Primarykey主键 可以紧跟在字段后 或在最后使用constraint 未指名表空间 数据表建立在用户默认的表空间中 笔只留下了痕迹 人用笔留下了文化 毁人不倦语录 DDL数据定义语言 CREATE 创建索引CREATE UNIQUE INDEXindex nameONtable name column list tablespacetablespace name 在table name表上按照column list建立索引 索引名为index name 索引保存在tablespace name表空间中 UNIQUE 唯一性索引 毁人不倦语录 你可以不听 但是我一定要说 DDL数据定义语言 ALTER 修改表ALTERTABLEtable nameREMAMETOnew table name 将table name表名修改为new table name ALTERTABLEtable nameADD column1datatype default notnull column2datatype default notnull 在table name表中增加column1 column2 字段 追加到字段最后 ALTERTABLEtable nameMODIFY column1datatype default notnull nullable column2datatype default notnull nullable 修改table name表字段属性 长度或精度不能小于原长度或精度 ALTERTABLEtable nameDROPCOLUMNcolumn name 删除table name表中的column name字段 ALTERTABLEtable nameRENAMECOLUMNold nameTOnew name 修改table name表的old name字段名为new name ALTERTABLEtable nameADDCONSTRAINTpk namePRIMARYKEY column list 建立table name表的主键 主键名pk name 主键字段column list ALTERTABLEtable nameDROPCONSTRAINTpk name 删除table name表的pk name主键 一个表只能有一个主键 毁人不倦语录 不说了 再说就该吐了 补充一点 站着进 扶着出也许是侃爷的最高境界吧 DDL数据定义语言 DROP 删除DROPTABLEtable name 删除table name表 DROPINDEXindex name 删除index name索引 删除主键 强制限制的一种 使用ALTERTABLEtable nameDROPCONSTRAINTpk name 毁人不倦猜想 数据库和水库之间不可能没有区别 DML小结 从ALTER和CREATE及DROP语法数量再一次证明需求是易变的 如果增加表字段 并要求字段的排列位置 可以使用 CREATETABLEtemp table nameASSELECT FROMtable name DROPTABLEtable name CREATETABLEtable name column INSERTINTOtable nameVALUES column list SELECTcolumn list1FROMtemp table name DROPTABLEtable name 需要注意的是 删除表后 表中的索引也被删除 所以 在执行上述操作前要保留好表的索引脚本 这里没有写DROPDATABASE的语法 如果大家想要尝试删除数据库的话 最好在专家的指导下进行 人间本无对错 因为计算机才有了对与错 毁人不倦语录 DDL数据操作语言 SELECT 查询 INSERT 插入 UPDATE 更新 DELETE 删除 如果老天能再给我一次机会 我一定做甲方 毁人不倦语录 DDL数据操作语言 SELECT 查询SELECT ALL DISTINCT ON expression expression ASoutput name INTO TEMPORARY TEMP TABLE new table FROMfrom item WHEREcondition GROUPBYexpression HAVINGcondition UNION INTERSECT EXCEPT ALL select ORDERBYexpression ASC DESC USINGoperator FORUPDATE OFclass name LIMIT count ALL OFFSET start 后续查询范例均以oracle中的SCOTT TIGER用户表为例 如果失眠了 就看看上面的语法吧 毁人不倦语录 DDL数据操作语言 SCOTT TIGER表介绍DEPT部门定义表 DEPTNO是主键EMP职工表 EMPNO主键 DEPTNO外键关联DEPT其他表没有数据或没有关系 不在这里介绍 将复杂问题简单化是修养 将简单问题复杂化是艺术 综合起来叫艺术修养 毁人不倦语录 DDL数据操作语言 SELECT 单表查询查询表中所有字段和所有记录 select后跟 表示所有字段SELECT FROMDEPT 查询指定字段 在select后跟查询的字段名列表 字段间用 隔开SELECTDEPTNO DNAMEFROMDEPT 条件查询 FROM后面使用WHERE 在WHERE中可以使用 BETWEEN LIKE IN 返回WHERE条件为true的数据SELECT FROMDEPTWHEREDEPTNO 20 返回部门编码为20的部门数据SELECT FROMDEPTWHEREDEPTNOBETWEEN20AND30 返回部门编码在20到30之间的部门数据 包括20和30 同意的从门出去 反对的请走窗户 毁人不倦语录 DDL数据操作语言 SELECT 单表查询SELECT FROMDEPTWHEREDNAMELIKE ACCOUNT 返回部门名称以ACCOUNT开始的部门数据SELECT FROMDEPTWHEREDNAMELIKE UNT 返回部门名称含UNT的部门数据 全匹配符 单匹配符SELECT FROMDEPTWHEREDEPTNOIN 20 30 AND DNAMELIKE UNT ORDNAME SALES 返回部门编码等于20和30并且部门名称含UNT或等于SALES的部门数据 AND与 OR或 IN和 等同OR 知识可以拷贝 思想无法复制 毁人不倦语录 DDL数据操作语言 SELECT 多表查询在FROM中列举表名 WHERE中写表关联SELECTEMP EMPNO EMP ENAME EMP JOB DEPT DEPTNO DEPT DNAMEFROMEMP DEPTWHEREEMP DEPTNO DEPT DEPTNOANDDEPT DEPTNO 20 字段名前使用表名 指定表字段 在WHERE中先写表关联关系 从FROM的内侧 即左侧 向外侧写 即右侧 表关联写完后再写查询条件 不要把表管理和查询条件混在一起写 这虽然不是必须的 但却是规范推荐的 SELECTE EMPNOAS职工编码 E ENAME姓名 E JOB D DEPTNO D DNAMEFROMEMPE DEPTDWHEREE DEPTNO D DEPTNOANDD DEPTNO 20 字段和表名可以使用别名 方法为AS别名 或直接写别名 表如果指定别名 原表名在该查询中不能再被使用 淹死的都是会游泳的 累死的都是能干的 毁人不倦语录 DDL数据操作语言 SELECT Joins 连接 语法 T1 NATURAL INNER LEFT RIGHT FULL OUTER JOIN T2 ONsearchcondition USING joincolumnlist 一个条件JOIN必须通过提供一个 并且只能有一个 NATURAL ON 或者USING这样的关键字来声明它的连接条件 ON子句接受一个searchcondition 它与一个WHERE子句相同 USING子句接受一个用逗号分隔的字段名列表 连接表中必须都有这些字段 并且用那些字段连接这些表 NATURAL是USING子句的缩写 它列出两个表中所有公共的字段名字 对于所有JOIN而言 INNER和OUTER都是可选的 INNER是缺省 LEFT RIGHT和FULL只用于OUTERJOIN DDL数据操作语言 SELECT INNERJOIN内连接对于T1的每行R1 连接成的表在T2里都有一行满足与R1的连接条件 SELECTEMP EMPNO EMP ENAME EMP JOB DEPT DEPTNO DEPT DNAMEFROMEMPJOINDEPTUSING DEPTNO 结果同 SELECTEMP EMPNO EMP ENAME EMP JOB DEPT DEPTNO DEPT DNAMEFROMEMP DEPTWHEREEMP DEPTNO DEPT DEPTNOINNERJOIN完全可以写成WHERE子句 重复两遍后你就应该把他变为程序 毁人不倦语录 DDL数据操作语言 SELECT OUTERJOIN外连接LEFT OUTER JOIN首先执行一次INNERJOIN 然后 如果T1里有一行对任何T2的行都不满足连接条件 那么返回一个连接行 该行的T2的字段为null 左表数据全部返回 右表无对应数据返回NULL Oracle也可以写成 WHEREEMP DEPTNO DEPT DEPTNORIGHT OUTER JOIN首先执行一次INNERJOIN 然后 如果T2里有一行对任何T1的行都不满足连接条件 那么返回一个连接行 该行的T1的字段为null 右表数据全部返回 左表无对应数据返回NULL Oracle也可以写成 WHEREEMP DEPTNO DEPT DEPTNO FULL OUTER JOIN首先执行一次INNERJOIN 然后 如果T1里有一行对任何T2的行都不满足连接条件 那么返回一个连接行 该行的T1的字段为null 同样 如果T2里有一行对任何T1的行都不满足连接条件 那么返回一个连接行 该行的T2的字段为null 两个表数据全部返回 相互之间没有对应数据返回NULL 三分技术 七分管理 毁人不倦语录 DDL数据操作语言 SELECT DISTINCT取差异数据 在SELECT查询列表中只返回有差异的结果集 SELECT 聚集操作聚集操作 AVG COUNT SUM MIN MAX 以表达式为参数 满足WHERE子句的行均被计算 通常 一个聚集对整个SELECT语句生成一个计算结果 如果在一个查询里面声明了分组 那么数据库将对每个组进行一次独立的计算 SELECT 分组操作SQL使用GROUPBY实现查询结果的分组 GROUPBY后面跟着字段列表 字段列表值均相同的记录成为一组 分组操作伴随聚集操作使用才有使用价值 注意 是分组伴随聚集 而不是聚集伴随分组 SELECT HAVING在分组操作后可以跟HAVING子句 限定分组聚集的查询结果 WHERE是分组之前限定查询结果 HAVING是对分组后的限定 即 HAVING伴随GROUPBY子句才有意思 难道我现在是和上帝说话 怎么总是没有回答 毁人不倦语录 DDL数据操作语言 SELECT 范例查询人数和人次 KC61是中心门诊报销表 其中AAC001是个人编码 一个人只有一个唯一的编码 可以使用DISTINCT和聚集查询SELECTCOUNT AS人次 COUNT DISTINCT AAC001 AS人数FROMKC61 统计各部门人数 工资总额和平均工资SELECTDEPTNO COUNT AS人数 SUM SAL AS工资总额 AVG SAL AS平均工资FROMEMPGROUPBYDEPTNO 如果需要显示部门名称 必须关联DEPT表SELECTD DEPTNO D DNAME COUNT AS人数 SUM E SAL AS工资总额 AVG E SAL AS平均工资FROMSCOTT DEPTD SCOTT EMPEWHERED DEPTNO E DEPTNOGROUPBYD DEPTNO D DNAME 非聚集字段必须在GROUPBY中列举 如果列举字段太多势必影响性能 可以使用查询子句解决此问题 有关查询子句见后详述 解决问题的问题 而不是问题 这是关键性问题 毁人不倦语录 DDL数据操作语言 SELECT UNION UNIONALL MINUS INTERSECT返回两个查询结果的集合操作 两个查询结果集必须字段相同 UNION和UNIONALL并集操作 UNION并集后去掉重复结果 UNIONALL直接并集MINUS差集操作INTERSECT交集操作SELECT FROMSCOTT DEPTUNIONSELECT FROMSCOTT DEPTWHEREDEPT DEPTNO 20 返回所有部门 因为去掉了重复的结果SELECT FROMSCOTT DEPTUNIONALLSELECT FROMSCOTT DEPTWHEREDEPT DEPTNO 20 返回所有部门 部门编码为20的有两条 工作境界 吃苦耐劳 苦中作乐 乐在其中 无苦无乐 毁人不倦语录 DDL数据操作语言 SELECT ORDERBYORDERBY排序 跟数字 代表字段位置 或字段名 ASC升序 DESC降序 默认升序 SELECTDEPTNO DNAMEFROMSCOTT DEPTWHEREDEPTNO 10GROUPBYDEPTNO DNAMEHAVINGCOUNT 0ORDERBYDEPTNODESC ORDERBY是对返回的结果进行排序 所以必须放在最后一句 SELECTDEPTNO DNAMEFROMSCOTT DEPTWHEREDEPTNO 10GROUPBYDEPTNO DNAMEHAVINGCOUNT 0ORDERBY1DESC 结果同第一个查询 字段位置从1开始 我是来做项目的 不是来创造奇迹的 毁人不倦语录 DDL数据操作语言 SELECT FORUPDATE锁等待查询 当查询结果中有被锁定记录时等待解锁 当记录被解锁后返回结果集 并锁定返回的记录 如果FORUPDATE后跟NOWAIT 遇到锁后不等待 返回错误 ORACLE使用记录级锁定 当事物被提交或回滚后锁定被释放 死锁问题当两个事物以不同的顺序同时更新多个表时就会发生死锁 对牛 解决死锁的途径是手工杀掉死锁的进程或者重新启动数据库 因此 在使用事物更新多表数据时一定要小心 使用面向对象的方法封装数据操作可以在很大程度上解决死锁问题 没有经过严格测试的软件开发进度报告不看也罢 毁人不倦语录 DDL数据操作语言 SELECT 子查询查询语句可以嵌套 任何产生数值的地方都可以使用子查询 在查询条件中使用子查询时 当子查询返回多个结果时只能使用IN 查询顺序是先执行被依赖的底层查询 然后一层层向上查 子查询只被执行一次 查询平均工资最低的部门情况SELECTDEPTNO AVG SAL FROMSCOTT EMPGROUPBYDEPTNOHAVINGAVG SAL SELECTMIN AVG SAL FROMSCOTT EMPGROUPBYDEPTNO 子查询先查询出最低的部门平均工资 然后查询部门最低平均工资等于子查询工资的部门 就是喜欢编程也不能为了编程而放弃和用户的功能确认 如果你把用户对程序的任意修改提升为对你自身价值的否定 你还不先确认吗 毁人不倦语录 DDL数据操作语言 SELECT 子查询在FROM中使用子查询 子查询在这里相对与VIEWSELECTD DEPTNO D DNAME S AVG SAL S SUM SAL S NUMFROMSCOTT DEPTD SELECTDEPTNO AVG SAL ASAVG SAL SUM SAL ASSUM SAL COUNT ASNUMFROMSCOTT EMPGROUPBYDEPTNO SWHERED DEPTNO S DEPTNO 在WHERE中使用子查询 例子不好 应该直接写表关联 这里只是为了说明语法 SELECT FROMSCOTT EMPWHEREDEPTNOIN SELECTDEPTNOFROMSCOTT DEPTWHEREDEPTNO 20 驴怎么了 人家采用的可是软件工程中的螺旋法则 毁人不倦语录 DDL数据操作语言 SELECT 子查询使用EXISTS NOTEXISTS 替换IN NOTIN IN NOTIN 在执行数据库操作时性能非常低下 应该使用EXISTS NOTEXISTS 替换 特别是NOTIN子句将执行一个内部的排序和合并 EXISTS子查询使用主表的字段限制查询数据SELECT FROMSCOTT EMPEWHEREEXISTS SELECT FROMSCOTT DEPTWHEREDEPT DEPTNO E DEPTNOANDDEPTNO 20 因为EXISTS可以看到外表 所以 如果表名重复 使用表别名区分 在子查询中一定写清楚和外表的关联关系 另外 子查询写SELECT 是对的 不用写字段名 多做一点 早知道一点 毁人不倦语录 DDL数据操作语言 UPDATE 修改用来更新数据表中的数据UPDATEtable nameSETcolumn name new value WHEREcondition 更新DEPT表 将20号部门名称修改为 销售部 地址修改为 北京 UPDATEDEPTSETDNAME 销售部 LOC 北京 WHEREDEPTNO 20 子查询也可在UPDATE中使用 将职工领导是KING的人员薪资增加100元UPDATESCOTT EMPSETSAL SAL 100WHEREEMPNOIN SELECTE EMPNOFROMSCOTT EMPE SCOTT EMPGWHEREE MGR G EMPNOANDG ENAME KING UPDATESCOTT EMPSETSAL SAL 100WHEREEXISTS SELECT FROMSCOTT EMPE SCOTT EMPGWHEREE MGR G EMPNOANDE EMPNO EMP EMPNOANDG ENAME KING 金保工程是 阳光工程 所以我才阳光 毁人不倦语录 DDL数据操作语言 INSERT 插入语法 INSERTINTOtable name column VALUES expression SELECTquery 当表字段和插入值相同时 可以省略字段列表INSERTINTOSCOTT BONUSVALUES TURNER SALESMAN 200 40 当只插入部分字段时 必须列举字段 未赋值字段使用默认值或为空INSERTINTOSCOTT BONUS ENAME JOB SAL VALUES CLARK MANAGER 100 也可以使用查询给表插入数据INSERTINTOSCOTT BONUS ENAME JOB SAL SELECTENAME JOB SALFROMSCOTT EMPWHEREMGR 7698 理论上都是问题 做起来没有问题 毁人不倦语录 DDL数据操作语言 DELETE 删除语法 DELETEFROMtable nameWHEREcondition 删除BONUS中ENAME为 CLARK 的记录DELETEFROMSCOTT BONUSWHEREENAME CLARK 删除重复记录 ROWID是记录的物理位置 一经确定永不改变DELETEFROMSCOTT BONUSBWHEREROWID1 删除ENAME重复记录 自己先擦块玻璃再交代给别人擦 安排工作同理 毁人不倦语录 序列号 创建CREATESEQUENCEname INCREMENTBYn STARTWITHn MAXVALUEn NOMAXVALUE MINVALUEn NOMINVALUE CYCLE NOCYCLE CACHEn NOCACHE 说明 INCREMENTBYn一次增长n个数字STARTWITHn初始值NOMAXVALUE缺省值10E 27NOMINVALUE缺省值1NOCYCLE不循环 常用于唯一关键字CACHEn在内存里缓存n个序列 出错回退时会丢失创建从1开始 不缓存的EMP EMPNO序列 CREATESEQUENCEEMP EMPNOSTARTWITH1NOCACHE 修改ALTERSEQUENCEname INCREMENTBYn MAXVALUEn NOMAXVALUE MINVALUEn NOMINVALUE CYCLE NOCYCLE CACHEn NOCACHE 起始值不能改变 如果要改变序列的起始值 先删除 再新建 删除DROPSEQUENCEname DROPSEQUENCEEMP EMPNO 序列号 使用NEXTVAL下一个序列号值CURRVAL当前序列号值如果EMP EMPNO是一个序列号 可以用下面SQL取序列值 去序列的当前值SELECTEMP EMPNO CURRVALFROMDUAL 去序列的下一个值SELECTEMP EMPNO NEXTVALFROMDUAL 使用序列插入数据INSERTINTOEMP EMPNO ENAME VALUES EMP EMPNO NEXTVAL JONE 不能用序列号的nextval和currval的地方视图查询 distinct查询 有groupby having orderby的查询 有子查询的查询 表里的缺省值 注释 注释内容 注释内容 SELECT DELETEFROMEMPWHEREDEPTNO 20 提示 删除数据前最好先查询一下 可以先写SELECT 然后把SELECT 注释掉 增加DELETE 如果再想不出来就去上厕所 毁人不倦语录 常用函数 数字函数ABS取绝对值POWER乘方LN10为底数取幂SQRT平方根EXPe的n次乘方LOG m n m为底数n取幂数学运算函数 ACOSATANATAN2COSCOSHSIGNSINSINHTANTANHCEIL大于或等于取整数FLOOR小于或等于取整数MOD取余数ROUND n m 按m的位数取四舍五入值如果round 日期 中午12以后将是明天的日期 round sysdate Y 是年的第一天TRUNC n m 按m的位数取前面的数值如果trunc 日期 确省的是去掉时间 冲锋号响起后执行的是命令而不是计划 毁人不倦语录 常用函数 字符函数CHR按数据库的字符集由数字返回字符CONCAT c1 c2 把两个字符c1 c2组合成一个字符 和 相同REPLACE c s r 把字符c里出现s的字符替换成r 返回新字符SUBSTR c m n m大于0 字符c从前面m处开始取n位字符 m等于0和1一样 m小与0 字符c从后面m处开始取n位字符TRANSLATE c f1 t1 字符c按f1到t1的规则转换成新的字符串INITCAP字符首字母大写 其它字符小写LOWER字符全部小写UPPER字符全部大写LTRIM c1 c2 去掉字符c1左边出现的字符c2RTRIM c1 c2 去掉字符c1右边出现的字符c2TRIM c1 c2 去掉字符c1左右两边的字符c2LPAD c1 n c2 字符c1按指定的位数n显示 不足的位数用c2字符串替换左边的空位RPAD c1 n c2 字符c1按指定的位数n显示 不足的位数用c2字符串替换右边的空位提示 去掉字符串中间的空格可以使用replace 如果有三条路供你选择 不要选中间的 剩下两条可以扔硬币决定 毁人不倦语录 常用函数 日期函数ADD MONTHS d n 日期值加n月LAST DAY d 返回当月的最后一天的日期MONTHS BETWEEN d1 d2 两个日期值间的月份NEXT DAY d 返回日期值下一天的日期SYSDATE当前的系统时间DUAL是SYS用户下一个空表 它只有一个字段dummy提示 dual可以理解为虚表 多在查询系统函数返回值时使用 以满足SELECT语法规则 如selectsysdatefromdual select3 4fromdual 零也是钱 毁人不倦语录 常用函数 转换函数TO CHAR date 日期显示格式 TO CHAR number 用于显示或报表的格式对齐TO DATE char 日期显示格式 TO LOB把long字段转换成lob字段TO NUMBER char 用于计算或者比较大小日期显示格式年 YYYY YEAR YY季 Q月 MM MONTH MON日 DD DAY DY时 HH24 HH12 HH 12小时 分 MI秒 SS 还差最后一点 毁人不倦语录 常用函数 逻辑比较函数NVL EXPR1 EXPR2 当EXPR1为空用EXPR2替代DECODE EXPR V1 R1 V2 R2 当EXPR V1时返回R1当EXPR V2是放回V2 EXPR后条件和返回值成对出现 最后一个单值是不在条件中的返回值 如果没有单值 不满足条件的返回NULL SELECTENAME DECODE JOB MANAGER 经理 PRESIDENT 总裁 SALESMAN 销售 不详 FROMSCOTT EMP CASEWHENconditionTHENexpress1ELSEexpress2END当condition成立返回express1否则返回express2SELECTENAME HIREDATE CASEWHENTO NUMBER TO CHAR HIREDATE MM 6THEN 下半年 ELSE 上半年 END AS入职时间FROMSCOTT EMP TO CHAR HIREDATE MM 取时间月份 写SQL时小心一点 别让硬盘飞出来伤到你 毁人不倦语录