MLDN魔乐科技Oracle课堂笔记

MLDN魔乐科技_Oracle课堂笔记1.sqlplusw命令(窗口形式),sqlplusw不支持编辑，一般在编辑器（记事本）中编辑好了后拷贝进去执行,或用ed命令；2.descdesc [tablename];查看表结构3.show user查看当前用户4.select table_name from tabs显示当前用户下的表名；5.set linesizeset linesize [number];6.set pagesizeset pagesize [number];7.eded命令用来从sqlplusw中打开编辑器来编辑文件(文本文件);8.@执行sql文件@D:\a.txt;@d:\a；(a文件的扩展名为.sql)9.connconn username/pwd@实例名;10.sql标准，其功能：DML(数据操作语言),DDL（数据定义语言）,DCL(数据控制语言)11.别名Oracle中指定列别名；(不要指定为中文);12.distinct去除重复记录；13.||字符串连接符；select "我的名字是：" || name from t_user;14.NOT NULL/IS NULL选择列值不为空的记录where collumname IS NOT NULL;相反IS NULL;15.BETWEEN…AND…WHERE COLUMENAME BETWEEN...AND...; 等价于>=,<=,如果是在时间之前，则需将时间''起来；16.大小写oracle中查询值是大小写区分的,但关键字不区分；17.InFieldName in (值1，值2，值3,...值n);NOT IN;18.Like在使用Like时常用的通配符：%,匹配任意长度内容，_，匹配一个长度内容;例：select * from emp where ENAME Like '_M%';表示第二个字母为M的名字.19.>,<,>=,<=,<>,!=用法20.order byOrder by语句，放在SQL语句最后；desc(从大到小)/asc(从小到大)(默认)21.单行函数字符/数值/日期/转换/通用函数；(1).字符：UPPER()变大写,LOWER()变小写,INITCAP()将单词第一个字母大写;字符长度LENGTH(),字符串截取SUBSTR(),字符串替换REPLACE()其中substr()的第二个参数即起始位置索引为0或1效果都是从第一个字符开始，负数是从右边开始；(2).数值：四舍五入ROUND(),截取TRUNC(),求模MOD();ROUND(23.45,2),TRUNC(23.45,-1),MOD(10,3);(3).日期：MONTHS_BETWEEN(),两个日期之间的月数;MONTHS_BETWEEN(DATE1,DATE2)ADD_MONTHS(),下月的今天;NEXT_DAY(),下一个的今天日期;LAST_DAY(), 给定日期的最后一天日期;(4).转换：TO_CHAR(),TO_NUMBER(),TO_DA TE();fm去除前导0,例如:to_char(sysdate,'fmyyyy-mm-dd') 得出结果2009-1-2(本应为2009-01-02).千位分隔符（格式字符用9表示）,货币前缀($美元，L本地币种)to_char(123456,'$99,999')美元to_char(123456,'L99,999')本地币种(5).通用：如果有NULL类型数据参与运算,必需用NVL()转换成特定值再计算;如:NVL(filed1,'0')DECODE()函数,用于替换;DECODE(field/expression,1,"one",2,"two")表示如果field/expression如果是1的话,则替换为one,是2的话替换为two;22.ORACLE用户(1)超级管理员:sys/change_on_install;(2)管理员:system/manager;(3)普通用户:scott/tiger;23.左、右(外)连接默认左连接where a.field1(+) = b.field2"+"在左边表示右连接，在右边表示左连接cross join产生笛卡尔积；标准语法：select table1.*,table2.* from table1,table2 where table1.no=table2.no;24.sql1999select table1.*,table2.* from table1[cross join table2][natural join table2][join table2 using collumname][left|right|full outer join table2]on table1.collum1 = table2.collum2where 1=1group by 分组条件having 分组条件order by...注：where子句中不能带组函数;25.组函数(1).count();(2).max();(3).min();(4).sum();(5).avg();26.分组统计group by27.子查询示例：select * from emp where sal > (select sal from emp where empno = 7654)使用分类：单列(用得最多），单行，多行；子查询的三种操作：(1) in 在结果集之中；(2) any=any即=，>any 比最小值的要大的结果集，<any比值最大的要小的结果集；(3) all>all,比最大的值要大，<all,比最小的值要小；格式：where field1 > all(子查询)28.事务一个窗口一个会话，如没有提交互不影响；commit;提交；rollback;回滚；死锁,等待,一个session没有提交,其它session不能处理,要等待前一个session提交了再进行处理;29.子查询、外连接练习select e.job,count(e.empno)from emp e right outer join (select job from emp group by job having min(sal) > 1500) emon e.job = em.jobgroup by e.job;select e.job,count(e.empno)from emp e ,(select job from emp group by job having min(sal) > 1500) emwhere e.job(+) = em.jobgroup by e.job;select e.job,count(e.empno)from emp ewhere e.job in (select job from emp group by job having min(sal) > 1500)group by e.job;29.表的建立与删除Oracle中的主要数据类型;varchar,varchar2为255字符;number(m,n),---float,number(n)----intdateclob,blog 4G(1)复制表create table tablename as select * from emp;(2)复制表结构(加永不成立的where条件)create table tablename as select * from emp where 1==2;(3)创建表create table tablename (字段1 类型1 default '默认值',字段2 类型2,...字段n 类型n)(4)修改表删除表: drop table tablename;增加列:alter table tablename add(columnname 类型default '默认值',columnname 类型default '默认值')修改列:alter table tablename modify(columnname 类型default '默认值')修改列名:alter table rename column columnname to newcolumnname重命名表:rename tablename1 to tablename2;只能用于oracle截断表:truncate table tablename;与delete类似,但直接释放,不能回滚；30.约束的分类与使用作用:保证数据库中数据的完整性;分类:主键(PRIMARY KEY),唯一(UNIQUE),检查(CHECK),非空(NOT NULL),外键约束(FOREIGN KEY);其中，前四种约束为单表约束，外键约束为多表约束；通过constraint指定约束:constraint person_pid_pk PRIMARY KEY(pid)constraint person_sex_ck CHECK(sex in ('男','女'))外键：强制删除父表:一般是先删除子表，再删除父表,但技术上可以实现强制先删除父表（同时删除从表相关约束）：DROP TABLE tablename CASCADE CONSTRAINT;(一般不使用)强制删除从表数据:删除父表数据时，从表相应有约束的记录也删除, 需在创建表约束时这样处理：CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES persion(pid) ON DELETE CASCADE示例:CREATE TABLE T_PARENT(ID NUMBER(10) PRIMARY KEY NOT NULL,NAME V ARCHAR2(10));CREATE TABLE T_CHIRLD(ID NUMBER(10) PRIMARY KEY NOT NULL,NAME V ARCHAR2(10),PARENT_ID NUMBER(10),CONSTRAINT CHIRLD_PARENT_FK FOREIGN KEY(PARENT_ID) REFERENCES T_PARENT ON DELETE CASCADE);增加约束：ALTER TABLE tablename ADD CONSTRAINT 约束名PRIMARY KEY(pid);示例:alter table T_CHIRLDadd constraint CHIRLD_PARENT_FK foreign key (PARENT_ID)references T_PARENT (ID) on delete cascade;修改约束：ALTER TABLE tablename MODIFY CONSTRAINT 约束名PRIMARY KEY(pid);字段名_PK,字段名_UK,字段名_CK,字段名_NK,从表字段名_父表字段名_FK删除约束:ALTER TABLE tablename DROP CONSTRAINT 约束名;31.表的关联查询:并(UNION),交(INTERSECT),差(MINUS)UNION:将多个查询的结果组合到一个查询结果中,没有重复;UNION ALL:将多个查询的结果组合到一个查询结果中,可以有重复;INTERSECT:返回两个结果集的公共部分;MINUS:返回两个结果集的差值;(左边表减右边表)示例:select * from emp UNION select * from emp2;32.ROWNUM伪列的作用自动编号,存在于每一个查询中,使用情境:只想显示前五条记录,则只需加条件:ROWNUM <= 5;常用于分页操作如果想取得中间记录的数据,不能用ROWNUM(BETWEEN AND),只能用子查询:例:查出第五条到第十条记录;SELECT * FROM (SELECT ROWNUM rn,empno,ename,job FROM emp WHERE ROWNUM<=10) tempWHERE temp.rn>=5;33.序列的使用用途:用于自动增长;创建序列:CREATE SEQUENCE seqname[INCREMENT BY n][START WITH n] --START WITH默认从1开始;[{MAXV ALUE n|NOMAXV ALUE}][{MINV ALUE n|NOMINV ALUE}][{CYCLE|NOCYCEL}][{CACHE|NOCACHE}]示例:create sequence myseq;使用序列:insert into tablename (next,curr) values (myseq.nextval,myseq.currval);删除序列:DROP SEQUENCE seqname;34.视图创建语法:CREATE OR REPLACE VIEW 视图名称AS 子查询其中子查询是一个复杂的select语句视图创建好后,可以作为一张表来查询使用;以上创建的视图可以执行UPDA TE,且可将原表进行修改;但实际应用用,视图是只读的,可以需要在创建视图的时候加上以下参数:WITH CHECK OPTION 不能更新视图条件字段,但能更新其它非条件字段;WITH READ ONL Y 表示只读,不能更新;删除语法:DROP VIEW 视图名称;35.同义词,用户管理,权限分配与撤销,数据库的备份同义词(只适用于oracle):作用:通过同义词可访问不同用户下的表,例如,scott用户可以访问sys用户下的dual表;创建同义词:CREATE SYNONYM 同义词名称FOR 用户名.表名称;示例:create synonym emp for scott.emp;删除同义词:DROP SYNONYM 同义词名称;用户管理创建用户:CREATE USER 用户名IDENTIFIED BY 密码;为用户授权:GRANT 权限1,权限2,... TO 用户;示例将创建session的权限赋给testuser,以使得其可以连接到数据库:GRANT CREATE SESSION TO testuser;赋予角色给用户testuser:GRANT CONNECT,RESOURCE TO testuser;修改用户密码:ALTER USER 用户名IDENTIFIED BY 密码;使用户密码失效:ALTER USER 用户名PASSWORD EXPIRE;锁住用户:ALTER USER 用户名ACCOUNT LOCK;解锁用户:ALTER USER 用户名ACCOUNT UNLOCK;将某张表的读取,删除权限赋给用户testuser:GRANT SELECT,DELETE ON scott.emp TO testuser;回收权限:REVOKEREVOKE 权限ON 用户.表名称FROM 用户;示例:REVOKE SELECT,DELETE ON scott.emp FROM testuser;数据库的备份:导入导出命令EXP,IMP36.可变数组类似于嵌套表,一般开始过程中不用37.数据库设计范式第一范式：每一个字段不可再分；第二范式：实现多对多的关联；第三范式：实现一对多的关联；（用得最多90%以上的项目）注：以上三范式在设计数据库时仅作参考，数据库设计的维一原则是：表关联尽可能少，SQL 尽可能简单；37.嵌套表(ORACLE特有,使用复杂,一般实际开发中不使用)在一个表中还包含另一个子表; 先定义类型.。