MLDN魔乐科技Oracle课堂笔记Word文档格式.docx

1、我的名字是： | name from t_user;14.NOT NULL/IS NULL选择列值不为空的记录where collumname IS NOT NULL;相反IS NULL;15.BETWEENANDWHERE COLUMENAME BETWEEN.AND.; 等价于=,!=用法20.order byOrder by语句，放在SQL语句最后；desc（从大到小）/asc（从小到大）（默认）21.单行函数字符/数值/日期/转换/通用函数；（1）.字符：UPPER（）变大写,LOWER（）变小写,INITCAP（）将单词第一个字母大写; 字符长度LENGTH（）,字符串截取SUBST

2、R（）,字符串替换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

3、（）,TO_DATE（）; fm去除前导0,例如:to_char（sysdate,fmyyyy-mm-dd） 得出结果2009-1-2（本应为2009-01-02）. 千位分隔符（格式字符用9表示）,货币前缀（$美元，L本地币种） to_char（123456,$99,999）美元L99,999）本地币种（5）.通用：如果有NULL类型数据参与运算,必需用NVL（）转换成特定值再计算; 如:NVL（filed1,0） DECODE（）函数,用于替换;DECODE（field/expression,1,one,2,two 表示如果field/expression如果是1的话,则替换为one,是2

4、的话替换为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 table1cross j

5、oin table2natural join table2join table2 using collumnameleft|right|full outer join table2on table1.collum1 = table2.collum2 where 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 （se

6、lect sal from emp where empno = 7654）使用分类： 单列（用得最多），单行，多行；子查询的三种操作：（1） in 在结果集之中；（2） any =any即=，any 比最小值的要大的结果集，all,比最大的值要大， all（子查询）28.事务 一个窗口一个会话，如没有提交互不影响； commit;提交； rollback;回滚； 死锁,等待,一个session没有提交,其它session不能处理,要等待前一个session提交了再进行处理;29.子查询、外连接练习select e.job,count（e.empno） from emp e right oute

7、r join （select job from emp group by job having min（sal） 1500） em on e.job = em.job group by e.job;from emp e ,（select job from emp group by job having min（sal） where e.job（+） = em.job from emp e where e.job in （select job from emp group by job having min（sal） 1500）29.表的建立与删除 Oracle中的主要数据类型; varchar

8、,varchar2为255字符; number（m,n）,-float,number（n）-int date clob,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 tabl

9、ename; 增加列: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.约束的

10、分类与使用 作用:保证数据库中数据的完整性; 分类:主键（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

11、 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 VARCHAR2（10） ）; CREATE TABLE T_CHIRLD（ NAME VARCHAR2（10）, PARENT_ID

12、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_CHIRLD add constraint CHIRLD_PARENT_FK foreign key （PARENT_ID） references T_PARENT （ID） on delete cascade; 修改约束： ALTER T

13、ABLE 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:返回两个结果集的差值;（左边表减

14、右边表） 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=5;33.序列的使用 用途:用于自动增长; 创建序列: CREATE SEQUENCE seqname

15、 INCREMENT BY nSTART WITH n -START WITH默认从1开始; MAXVALUE n|NOMAXVALUE MINVALUE n|NOMINVALUE 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 子查询 其中子查询

16、是一个复杂的select语句 视图创建好后,可以作为一张表来查询使用; 以上创建的视图可以执行UPDATE,且可将原表进行修改;但实际应用用,视图是只读的,可以需要在创建视图的时候加上以下参数: WITH CHECK OPTION 不能更新视图条件字段,但能更新其它非条件字段; WITH READ ONLY 表示只读,不能更新; 删除语法: DROP VIEW 视图名称;35.同义词,用户管理,权限分配与撤销,数据库的备份 同义词（只适用于oracle）:通过同义词可访问不同用户下的表,例如,scott用户可以访问sys用户下的dual表; 创建同义词: CREATE SYNONYM 同义词名

17、称 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 用户名

18、 IDENTIFIED BY 密码; 使用户密码失效: ALTER USER 用户名 PASSWORD EXPIRE; 锁住用户: ALTER USER 用户名 ACCOUNT LOCK; 解锁用户: ALTER USER 用户名 ACCOUNT UNLOCK; 将某张表的读取,删除权限赋给用户testuser: GRANT SELECT,DELETE ON scott.emp TO testuser; 回收权限:REVOKE REVOKE 权限 ON 用户.表名称 FROM 用户;REVOKE SELECT,DELETE ON scott.emp FROM testuser; 数据库的备份: 导入导出命令EXP,IMP 36.可变数组 类似于嵌套表,一般开始过程中不用37.数据库设计范式 第一范式：每一个字段不可再分； 第二范式：实现多对多的关联； 第三范式：实现一对多的关联； （用得最多90%以上的项目） 注：以上三范式在设计数据库时仅作参考，数据库设计的维一原则是：表关联尽可能少，SQL尽可能简单；37.嵌套表（ORACLE特有,使用复杂,一般实际开发中不使用） 在一个表中还包含另一个子表; 先定义类型.