资源描述
单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,ORACLE,数据库教程,你可以不知道，但是绝对不能不懂,-毁人不倦语录,2007,年,5,月,SQL基础知识篇,SQL-001,简介,SQL,（,Structured Query Language,）结构化查询语言是,IBM,公司,San Jose,实验室为,System R,而设计的查询语言，后被国际标准化组织（,ISO,）批准作为关系数据库语言的国际标准。,SQL,目前遵循的是,1992,年标准，即,SQL-92,。,各数据库厂家对,SQL-92,标准均有扩充，扩充部分不能完全保证数据库之间的兼容性。,我的热情会灼伤一些人，但我是无心的,-毁人不倦语录,简介,SQL,可以分为,:,（基础篇只介绍前两种）,DML:,数据操作语言（,Data Manipulation Language,）,: select, insert, update, delete, (,其他：,truncate),DDL:,数据定义语言 （,Data Definition Languages,）,: create, drop , alter,(,其他：,rename),DCL,数据控制语言：,grant,、,revoke,、,set role,事务控制：,commit,、,rollback,、,savepoint,（其他：,lock table,、,set constraint(s),、,set transaction,）,审计控制：,audit,、,noaudit,系统控制：,alter system,会话控制：,alter session,其他语句：,comment,（添加注释）、,explain plan,、,analyze,、,validate,、,call,如果我的话没有错，一定是你听错了,-毁人不倦语录,DDL,数据定义语言,CREATE(,建立,),ALTER(,修改,),DROP(,删除,),无知者无畏，无畏者不一定无知,-毁人不倦语录,语言描述约定, ,：表示可选项, | ,：表示选择项,CREATE,：粗体表示关键字或必输项,Table_name,：斜体表示用户输入项,创建,：蓝色表示说明,如果1+12，我相信那说的是团队的力量,-毁人不倦语录,DDL,数据定义语言,CREATE-,创建用户,CREATE USER,user_name,IDENTIFIED BY,password,DEFAULT TABLESPACE,tablespace_name, TEMPORARY TABLESPACE,tablespace_name,;,创建,user_name,用户，密码为,password,，使用,tablespace_name,表空间，如果没有指定表空间，默认表空间为,system,表空间。,Oracle10g,有用户默认表空间设置。,建议在创建用户时一定要指名表空间。,先有鸡，还是先有蛋，这么重要的问题还是留给鸡去考证吧,-毁人不倦语录,DDL,数据定义语言,CREATE-,创建数据表,CREATE TABLE,table_name,(,Column1,datatype defaultnot null primary key,constraint,key_name,primary key(,column_list,) using index tablespace,tablespace_name,)tablespace,tablespace_name,;,Datatype,是数据类型,:varchar2(x), number(x,x), date, integer,等。,Not null,非空限制，如果不写表示可为空。,Primary key,主键，可以紧跟在字段后，或在最后使用,constraint,。,未指名表空间，数据表建立在用户默认的表空间中。,笔只留下了痕迹，人用笔留下了文化,-毁人不倦语录,DDL,数据定义语言,CREATE-,创建索引,CREATE UNIQUE INDEX,index_name,ON,table_name,(column_list)tablespace,tablespace_name,;,在,table_name,表上按照,column_list,建立索引，索引名为,index_name,，索引保存在,tablespace_name,表空间中。,UNIQUE:,唯一性索引。,-毁人不倦语录,你可以不听，但是我一定要说,DDL,数据定义语言,ALTER-,修改表,ALTER TABLE,table_name,RENAME TO,new_table_name,;,将,table_name,表名修改为,new_table_name,。,ALTER TABLE,table_name,ADD(,column1,datatype default not null,column2,datatype default not null, . );,在,table_name,表中增加,column1,column2.,字段，追加到字段最后。,ALTER TABLE,table_name,MODIFY(,column1,datatype default not null|nullable,column2,datatype default not null|nullable, .,修改,table_name,表字段属性，长度或精度不能小于原长度或精度。,ALTER TABLE,table_name,DROP COLUMN,column_name,;,删除,table_name,表中的,column_name,字段。,ALTER TABLE,table_name,RENAME COLUMN,old_name,TO,new_name,;,修改,table_name,表的,old_name,字段名为,new_name,。,ALTER TABLE,table_name,ADD CONSTRAINT,pk_name,PRIMARY KEY(,column_list,);,建立,table_name,表的主键，主键名,pk_name,，主键字段,column_list,。,ALTER TABLE,table_name,DROP CONSTRAINT,pk_name,;,删除,table_name,表的,pk_name,主键。,一个表只能有一个主键,-毁人不倦语录,不说了，再说就该吐了。补充一点：站着进，扶着出也许是侃爷的最高境界吧,DDL,数据定义语言,DROP -,删除,DROP TABLE,table_name,;,删除,table_name,表。,DROP INDEX,index_name,;,删除,index_name,索引。,删除主键（强制限制的一种），使用,ALTER TABLE table_name DROP CONSTRAINT pk_name;,-毁人不倦猜想,数据库和水库之间不可能没有区别,DML,小结,从,ALTER,和,CREATE,及,DROP,语法数量再一次证明需求是易变的。,如果增加表字段，并要求字段的排列位置，可以使用,:,CREATE TABLE,temp_table_name,AS SELECT * FROM,table_name,;,DROP TABLE,table_name,;,CREATE TABLE,table_name(column,);,INSERT INTO,table_name,VALUES(column_list,) (SELECT column_list1 FROM,temp_table_name,);,DROP TABLE,table_name,;,需要注意的是，删除表后，表中的索引也被删除，所以，在执行上述操作前要保留好表的索引脚本。,这里没有写,DROP DATABASE,的语法，如果大家想要尝试删除数据库的话，最好在专家的指导下进行！,人间本无对错，因为计算机才有了对与错,-毁人不倦语录,DDL,数据操作语言,SELECT(,查询,),INSERT(,插入,),UPDATE(,更新,),DELETE(,删除,),如果老天能再给我一次机会，我一定做甲方,-毁人不倦语录,DDL,数据操作语言,SELECT-,查询,SELECT ALL | DISTINCT ON (,expression, . ) * |,expression, AS,output_name, , ., INTO TEMPORARY | TEMP TABLE ,new_table, FROM,from_item, . , WHERE,condition, GROUP BY,expression, . , HAVING,condition, . , UNION | INTERSECT | EXCEPT ALL ,select, ORDER BY,expression, ASC | DESC | USING,operator, , . , FOR UPDATE OF,class_name, . , LIMIT ,count,| ALL OFFSET | , ,start,后续查询范例均以,oracle,中的,SCOTT/TIGER,用户表为例,如果失眠了，就看看上面的语法吧,-毁人不倦语录,DDL,数据操作语言,SCOTT/TIGER,表介绍,DEPT,部门定义表，,DEPTNO,是主键,EMP,职工表,EMPNO,主键，,DEPTNO,外键关联,DEPT,其他表没有数据或没有关系，不在这里介绍,将复杂问题简单化是修养，将简单问题复杂化是艺术，综合起来叫艺术修养,-毁人不倦语录,DDL,数据操作语言,SELECT-,单表查询,查询表中所有字段和所有记录，,select,后跟* 表示所有字段,SELECT * FROM DEPT;,查询指定字段，在,select,后跟查询的字段名列表，字段间用,隔开,SELECT DEPTNO, DNAME FROM DEPT;,条件查询，,FROM,后面使用,WHERE,，在,WHERE,中可以使用,=, , ,=, 10,GROUP BY DEPTNO, DNAME,HAVING COUNT(*) 0,ORDER BY,DEPTNO,DESC;,-ORDER BY,是对返回的结果进行排序，所以必须放在最后一句。,SELECT DEPTNO, DNAME,FROM SCOTT.DEPT,WHERE DEPTNO 10,GROUP BY DEPTNO, DNAME,HAVING COUNT(*) 0,ORDER BY,1,DESC;,-,结果同第一个查询，字段位置从,1,开始。,我是来做项目的，不是来创造奇迹的,-毁人不倦语录,DDL,数据操作语言,SELECT-FOR UPDATE,锁等待查询。当查询结果中有被锁定记录时等待解锁，当记录被解锁后返回结果集，并锁定返回的记录。如果,FOR UPDATE,后跟,NOWAIT,，遇到锁后不等待，返回错误。,ORACLE,使用记录级锁定，当事物被提交或回滚后锁定被释放。,死锁问题,当两个事物以不同的顺序同时更新多个表时就会发生死锁（对牛），解决死锁的途径是手工杀掉死锁的进程或者重新启动数据库。因此，在使用事物更新多表数据时一定要小心，使用面向对象的方法封装数据操作可以在很大程度上解决死锁问题。,没有经过严格测试的软件开发进度报告不看也罢,-毁人不倦语录,DDL,数据操作语言,SELECT-,子查询,查询语句可以嵌套，任何产生数值的地方都可以使用子查询。,在查询条件中使用子查询时，当子查询返回多个结果时只能使用,IN,。,查询顺序是先执行被依赖的底层查询，然后一层层向上查。,子查询只被执行一次。,查询平均工资最低的部门情况,SELECT DEPTNO, AVG(SAL),FROM SCOTT.EMP,GROUP BY DEPTNO,HAVING AVG(SAL) =,(SELECT MIN(AVG(SAL) FROM SCOTT.EMP,GROUP BY DEPTNO),;,-,子查询先查询出最低的部门平均工资，然后查询部门最低平均工资等于子查询工资的部门,就是喜欢编程也不能为了编程而放弃和用户的功能确认，如果你把用户对程序的任意修改提升为对你自身价值的否定，你还不先确认吗？,-毁人不倦语录,DDL,数据操作语言,SELECT-,子查询,在,FROM,中使用子查询，子查询在这里相对与,VIEW,SELECT D.DEPTNO, D.DNAME, S.AVG_SAL, S.SUM_SAL, S.NUM,FROM SCOTT.DEPT D,(SELECT DEPTNO, AVG(SAL) AS AVG_SAL, SUM(SAL) AS SUM_SAL, COUNT(*) AS NUM,FROM SCOTT.EMP,GROUP BY DEPTNO) S,WHERE D.DEPTNO = S.DEPTNO;,在,WHERE,中使用子查询，例子不好，应该直接写表关联，这里只是为了说明语法。,SELECT * FROM SCOTT.EMP WHERE DEPTNO IN,(SELECT DEPTNO FROM SCOTT.DEPT WHERE DEPTNO = 20),;,驴怎么了？人家采用的可是软件工程中的螺旋法则,-毁人不倦语录,DDL,数据操作语言,SELECT-,子查询,使用,EXISTS(NOT EXISTS),替换,IN(NOT IN),IN(NOT IN),在执行数据库操作时性能非常低下，应该使用,EXISTS(NOT EXISTS),替换，特别是,NOT IN,子句将执行一个内部的排序和合并,;,EXISTS,子查询使用主表的字段限制查询数据,SELECT *,FROM SCOTT.EMP,E,WHERE,EXISTS,(SELECT * FROM SCOTT.DEPT WHERE DEPT.DEPTNO =,E.DEPTNO,AND DEPTNO = 20);,-,因为,EXISTS,可以看到外表，所以，如果表名重复，使用表别名区分，在子查询中一定写清楚和外表的关联关系，另外，子查询写,SELECT *,是对的，不用写字段名。,多做一点，早知道一点,-毁人不倦语录,DDL,数据操作语言,UPDATE,修改,用来更新数据表中的数据,UPDATE,table_name,SET,column_name,=,new_value,WHERE,condition,;,更新,DEPT,表，将,20,号部门名称修改为,销售部,，地址修改为,北京,UPDATE DEPT SET DNAME = ,销售部, LOC = ,北京,WHERE DEPTNO = 20;,子查询也可在,UPDATE,中使用,将职工领导是,KING,的人员薪资增加,100,元,UPDATE SCOTT.EMP,SET SAL = SAL + 100,WHERE EMPNO IN (,SELECT E.EMPNO FROM SCOTT.EMP E, SCOTT.EMP G WHERE E.MGR = G.EMPNO AND G.ENAME = KING,);,UPDATE SCOTT.EMP SET SAL = SAL + 100WHERE,EXISTS (SELECT * FROM SCOTT.EMP E, SCOTT.EMP G WHERE E.MGR = G.EMPNO AND E.EMPNO = EMP.EMPNO AND G.ENAME = KING),;,金保工程是“阳光工程”，所以我才阳光,-毁人不倦语录,DDL,数据操作语言,INSERT-,插入,语法,:,INSERT INTO,table_name,(,column,.) VALUES (,expression,) | SELECT query,当表字段和插入值相同时，可以省略字段列表,INSERT INTO SCOTT.BONUS VALUES(TURNER, SALESMAN, 200, 40);,当只插入部分字段时，必须列举字段，未赋值字段使用默认值或为空,INSERT INTO SCOTT.BONUS(ENAME, JOB, SAL),VALUES(CLARK, MANAGER, 100);,也可以使用查询给表插入数据,INSERT INTO SCOTT.BONUS(ENAME, JOB, SAL),SELECT ENAME, JOB, SAL FROM SCOTT.EMP WHERE MGR = 7698;,理论上都是问题，做起来没有问题,-毁人不倦语录,DDL,数据操作语言,DELETE-,删除,语法,:,DELETE FROM,table_name,WHERE,condition,;,删除,BONUS,中,ENAME,为,CLARK,的记录,DELETE FROM SCOTT.BONUS WHERE ENAME = CLARK,删除重复记录，,ROWID,是记录的物理位置，一经确定永不改变,DELETE FROM SCOTT.BONUS B,WHERE ROWID 1,);,-,删除,ENAME,重复记录,自己先擦块玻璃再交代给别人擦，安排工作同理,-毁人不倦语录,序列号,创建,CREATE SEQUENCE,name,INCREMENT BY n START WITH n,MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE,CYCLE | NOCYCLE CACHE n | NOCACHE;,说明：,INCREMENT BY n,一次增长,n,个数字,START WITH n,初始值,NOMAXVALUE,缺省值,10E+27,NOMINVALUE,缺省值,1,NOCYCLE,不循环,常用于唯一关键字,CACHE n,在内存里缓存,n,个序列,出错回退时会丢失,创建从,1,开始，不缓存的,EMP_EMPNO,序列。,CREATE SEQUENCE EMP_EMPNO START WITH 1 NOCACHE;,修改,ALTER SEQUENCE,name,INCREMENT BY n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE,CYCLE | NOCYCLE CACHE n | NOCACHE;,-,起始值不能改变，如果要改变序列的起始值,先删除,再新建。,删除,DROP SEQUENCE,name,;,DROP SEQUENCE EMP_EMPNO;,序列号,使用,NEXTVAL,下一个序列号值,CURRVAL,当前序列号值,如果,EMP_EMPNO,是一个序列号，可以用下面,SQL,取序列值,-,去序列的当前值,SELECT EMP_EMPNO.CURRVAL FROM DUAL;,-,去序列的下一个值,SELECT EMP_EMPNO.NEXTVAL FROM DUAL;,-,使用序列插入数据,INSERT INTO EMP(EMPNO, ENAME,) VALUES(EMP_EMPNO.NEXTVAL, JONE,);,不能用序列号的,nextval,和,currval,的地方,视图查询、,distinct,查询、有,group by,having,order by,的查询、,有子查询的查询、表里的缺省值,注释,/*,注释内容 *,/,-,注释内容,-SELECT *,DELETE,FROM EMP,WHERE DEPTNO = 20;,提示：删除数据前最好先查询一下。可以先写,SELECT *,，然后把,SELECT *,注释掉，增加,DELETE,。,如果再想不出来就去上厕所,-毁人不倦语录,常用函数,数字函数,ABS 取绝对值,POWER 乘方,LN 10,为底数取,幂,SQRT 平方根,EXP e的n次乘方,LOG(m,n) m为底数n取,幂,数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH,CEIL 大于或等于取整数,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左边出现的字符c2,RTRIM(c1,c2) 去掉字符c1,右,边出现的字符c2,TRIM(c1,c2) 去掉字符c1左右两边的字符c2,LPAD(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,语法规则，如,select sysdate from dual; select 3 + 4 from dual;,零也是钱,-毁人不倦语录,常用函数,转换函数,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,时,:,HH,24, HH12, HH(12,小时,),分,:,MI,秒,:,SS,还差最后一点,-毁人不倦语录,常用函数,逻辑比较函数,NVL(EXPR1, EXPR2),当,EXPR1,为空用,EXPR2,替代,DECODE(EXPRV1R1V2R2.),当,EXPR=V1,时返回,R1,当,EXPR=V2,是放回,V2,EXPR,后条件和返回值成对出现，最后一个单值是不在条件中的返回值，如果没有单值，不满足条件的返回,NULL,。,SELECT ENAME, DECODE(JOB, MANAGER, ,经理, PRESIDENT, ,总裁, SALESMAN, ,销售, ,不详,) FROM SCOTT.EMP;,CASE WHEN,condition,THEN,express1,ELSE,express2,END,当,condition,成立返回,express1,否则返回,express2,SELECT ENAME, HIREDATE,(CASE WHEN TO_NUMBER(TO_CHAR(HIREDATE, MM) 6 THEN ,下半年, ELSE ,上半年, END) AS,入职时间,FROM SCOTT.EMP;,-TO_CHAR(HIREDATE, MM),取时间月份,写SQL时小心一点，别让硬盘飞出来伤到你,-毁人不倦语录,
展开阅读全文