MLDN魔乐科技Oracle课堂笔记Word文档格式.docx
《MLDN魔乐科技Oracle课堂笔记Word文档格式.docx》由会员分享,可在线阅读,更多相关《MLDN魔乐科技Oracle课堂笔记Word文档格式.docx(10页珍藏版)》请在冰豆网上搜索。
![MLDN魔乐科技Oracle课堂笔记Word文档格式.docx](https://file1.bdocx.com/fileroot1/2023-1/29/3e98bfbd-debf-4b05-846c-1bb353b0dd11/3e98bfbd-debf-4b05-846c-1bb353b0dd111.gif)
我的名字是:
"
||namefromt_user;
14.NOTNULL/ISNULL
选择列值不为空的记录wherecollumnameISNOTNULL;
相反ISNULL;
15.BETWEEN…AND…
WHERECOLUMENAMEBETWEEN...AND...;
等价于>
=,<
=,如果是在时间之前,则需将时间'
'
起来;
16.大小写
oracle中查询值是大小写区分的,但关键字不区分;
17.In
FieldNamein(值1,值2,值3,...值n);
NOTIN;
18.Like
在使用Like时常用的通配符:
%,匹配任意长度内容,_,匹配一个长度内容;
例:
select*fromempwhereENAMELike'
_M%'
;
表示第二个字母为M的名字.
19.>
<
>
>
!
=用法
20.orderby
Orderby语句,放在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_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的话替换为two;
22.ORACLE用户
(1)超级管理员:
sys/change_on_install;
(2)管理员:
system/manager;
(3)普通用户:
scott/tiger;
23.左、右(外)连接
默认左连接
wherea.field1(+)=b.field2
+"
在左边表示右连接,在右边表示左连接
crossjoin产生笛卡尔积;
标准语法:
selecttable1.*,table2.*fromtable1,table2wheretable1.no=table2.no;
24.sql1999
selecttable1.*,table2.*fromtable1
[crossjointable2]
[naturaljointable2]
[jointable2usingcollumname]
[left|right|fullouterjointable2]
ontable1.collum1=table2.collum2
where1=1
groupby分组条件having分组条件
orderby...
注:
where子句中不能带组函数;
25.组函数
(1).count();
(2).max();
(3).min();
(4).sum();
(5).avg();
26.分组统计
groupby
27.子查询
示例:
select*fromempwheresal>
(selectsalfromempwhereempno=7654)
使用分类:
单列(用得最多),单行,多行;
子查询的三种操作:
(1)in在结果集之中;
(2)any
=any即=,>
any比最小值的要大的结果集,<
any比值最大的要小的结果集;
(3)all
>
all,比最大的值要大,<
all,比最小的值要小;
格式:
wherefield1>
all(子查询)
28.事务
一个窗口一个会话,如没有提交互不影响;
commit;
提交;
rollback;
回滚;
死锁,等待,一个session没有提交,其它session不能处理,要等待前一个session提交了再进行处理;
29.子查询、外连接练习
selecte.job,count(e.empno)
fromemperightouterjoin(selectjobfromempgroupbyjobhavingmin(sal)>
1500)em
one.job=em.job
groupbye.job;
fromempe,(selectjobfromempgroupbyjobhavingmin(sal)>
wheree.job(+)=em.job
fromempe
wheree.jobin(selectjobfromempgroupbyjobhavingmin(sal)>
1500)
29.表的建立与删除
Oracle中的主要数据类型;
varchar,varchar2为255字符;
number(m,n),---float,number(n)----int
date
clob,blog4G
(1)复制表
createtabletablenameasselect*fromemp;
(2)复制表结构(加永不成立的where条件)
createtabletablenameasselect*fromempwhere1==2;
(3)创建表
createtabletablename(
字段1类型1default'
默认值'
字段2类型2,
...
字段n类型n
)
(4)修改表
删除表:
droptabletablename;
增加列:
altertabletablenameadd(columnname类型default'
columnname类型default'
修改列:
altertabletablenamemodify(columnname类型default'
修改列名:
altertablerenamecolumncolumnnametonewcolumnname
重命名表:
renametablename1totablename2;
只能用于oracle
截断表:
truncatetabletablename;
与delete类似,但直接释放,不能回滚;
30.约束的分类与使用
作用:
保证数据库中数据的完整性;
分类:
主键(PRIMARYKEY),唯一(UNIQUE),检查(CHECK),非空(NOTNULL),外键约束(FOREIGNKEY);
其中,前四种约束为单表约束,外键约束为多表约束;
通过constraint指定约束:
constraintperson_pid_pkPRIMARYKEY(pid)
constraintperson_sex_ckCHECK(sexin('
男'
'
女'
))
外键:
强制删除父表:
一般是先删除子表,再删除父表,但技术上可以实现强制先删除父表(同时删除从表相关约束):
DROPTABLEtablenameCASCADECONSTRAINT;
(一般不使用)
强制删除从表数据:
删除父表数据时,从表相应有约束的记录也删除,需在创建表约束时这样处理:
CONSTRAINTperson_book_pid_fkFOREIGNKEY(pid)REFERENCESpersion(pid)ONDELETECASCADE
示例:
CREATETABLET_PARENT(
IDNUMBER(10)PRIMARYKEYNOTNULL,
NAMEVARCHAR2(10)
);
CREATETABLET_CHIRLD(
NAMEVARCHAR2(10),
PARENT_IDNUMBER(10),
CONSTRAINTCHIRLD_PARENT_FKFOREIGNKEY(PARENT_ID)REFERENCEST_PARENTONDELETECASCADE
增加约束:
ALTERTABLEtablenameADDCONSTRAINT约束名PRIMARYKEY(pid);
altertableT_CHIRLD
addconstraintCHIRLD_PARENT_FKforeignkey(PARENT_ID)
referencesT_PARENT(ID)ondeletecascade;
修改约束:
ALTERTABLEtablenameMODIFYCONSTRAINT约束名PRIMARYKEY(pid);
字段名_PK,字段名_UK,字段名_CK,字段名_NK,从表字段名_父表字段名_FK
删除约束:
ALTERTABLEtablenameDROPCONSTRAINT约束名;
31.表的关联查询:
并(UNION),交(INTERSECT),差(MINUS)
UNION:
将多个查询的结果组合到一个查询结果中,没有重复;
UNIONALL:
将多个查询的结果组合到一个查询结果中,可以有重复;
INTERSECT:
返回两个结果集的公共部分;
MINUS:
返回两个结果集的差值;
(左边表减右边表)
select*fromempUNIONselect*fromemp2;
32.ROWNUM伪列的作用
自动编号,存在于每一个查询中,使用情境:
只想显示前五条记录,则只需加条件:
ROWNUM<
=5;
常用于分页操作
如果想取得中间记录的数据,不能用ROWNUM(BETWEENAND),只能用子查询:
例:
查出第五条到第十条记录;
SELECT*FROM(SELECTROWNUMrn,empno,ename,jobFROMempWHEREROWNUM<
=10)temp
WHEREtemp.rn>
=5;
33.序列的使用
用途:
用于自动增长;
创建序列:
CREATESEQUENCEseqname
[INCREMENTBYn][STARTWITHn]--STARTWITH默认从1开始;
[{MAXVALUEn|NOMAXVALUE}]
[{MINVALUEn|NOMINVALUE}]
[{CYCLE|NOCYCEL}]
[{CACHE|NOCACHE}]
createsequencemyseq;
使用序列:
insertintotablename(next,curr)values(myseq.nextval,myseq.currval);
删除序列:
DROPSEQUENCEseqname;
34.视图
创建语法:
CREATEORREPLACEVIEW视图名称AS子查询
其中子查询是一个复杂的select语句
视图创建好后,可以作为一张表来查询使用;
以上创建的视图可以执行UPDATE,且可将原表进行修改;
但实际应用用,视图是只读的,可以需要在创建视图的时候加上以下参数:
WITHCHECKOPTION不能更新视图条件字段,但能更新其它非条件字段;
WITHREADONLY表示只读,不能更新;
删除语法:
DROPVIEW视图名称;
35.同义词,用户管理,权限分配与撤销,数据库的备份
同义词(只适用于oracle):
通过同义词可访问不同用户下的表,例如,scott用户可以访问sys用户下的dual表;
创建同义词:
CREATESYNONYM同义词名称FOR用户名.表名称;
createsynonymempforscott.emp;
删除同义词:
DROPSYNONYM同义词名称;
用户管理
创建用户:
CREATEUSER用户名IDENTIFIEDBY密码;
为用户授权:
GRANT权限1,权限2,...TO用户;
示例将创建session的权限赋给testuser,以使得其可以连接到数据库:
GRANTCREATESESSIONTOtestuser;
赋予角色给用户testuser:
GRANTCONNECT,RESOURCETOtestuser;
修改用户密码:
ALTERUSER用户名IDENTIFIEDBY密码;
使用户密码失效:
ALTERUSER用户名PASSWORDEXPIRE;
锁住用户:
ALTERUSER用户名ACCOUNTLOCK;
解锁用户:
ALTERUSER用户名ACCOUNTUNLOCK;
将某张表的读取,删除权限赋给用户testuser:
GRANTSELECT,DELETEONscott.empTOtestuser;
回收权限:
REVOKE
REVOKE权限ON用户.表名称FROM用户;
REVOKESELECT,DELETEONscott.empFROMtestuser;
数据库的备份:
导入导出命令
EXP,IMP
36.可变数组
类似于嵌套表,一般开始过程中不用
37.数据库设计范式
第一范式:
每一个字段不可再分;
第二范式:
实现多对多的关联;
第三范式:
实现一对多的关联;
(用得最多90%以上的项目)
注:
以上三范式在设计数据库时仅作参考,数据库设计的维一原则是:
表关联尽可能少,SQL尽可能简单;
37.嵌套表
(ORACLE特有,使用复杂,一般实际开发中不使用)
在一个表中还包含另一个子表;
先定义类型.