玩转oracle从入门到熟练.docx
- 文档编号:24457591
- 上传时间:2023-05-27
- 格式:DOCX
- 页数:16
- 大小:21.91KB
玩转oracle从入门到熟练.docx
《玩转oracle从入门到熟练.docx》由会员分享,可在线阅读,更多相关《玩转oracle从入门到熟练.docx(16页珍藏版)》请在冰豆网上搜索。
玩转oracle从入门到熟练
第1章Oracle系统使用
1.1.连接命令
连接命令conn用户名/密码@网络服务名
特权用户连接,必须带上assysydba(超级)或者assysoper(管理员)
SQL>showuser;
SQL>connsystem/manager;
1.2.断开连接disc断开数据库连接passw
修改用户密码→alteruserscottidentifiedbytiger;
1.3.Oracle账号scott被锁定,
1.3.1.connsys/lsy403assysdba;//以dba身份登录
1.3.2.alteruserscottaccountunlock;//解锁
1.3.3.connscott/tiger;//弹出修改密码
SQL>startd:
\aa.sql;
SQL>spoold:
\bb.sql;
SQL>spooloff;
Select*fromempwhereename=’SMITH’
第2章用户的管理
2.1.用户的管理
2.1.1.创建用户:
createuserxiaomingidentifiedbym123;(密码以字母开头)
2.1.2.删除用户:
在删除用户的时候,如果用户已经创建了表,那就需要在删除时带上一个参数cascade
例如:
dropuser用户名cascade;
2.1.3.赋予权限
grantconnecttoxiaoming;
系统权限:
用户对数据库的相关权限(140多种)
对象权限:
用户对其他用户的数据对象操作的权限(25种)
角色:
预定义角色(connect,dba,resource在任一表空间建表)
希望小明能查询scottd的emp表
Grantselectonemptoxiaoming;授权成功
登录xiaoming账号:
select*fromscott.emp;
grantupdate(all)onemptoxiaoming;
2.1.4.收回权限
scott希望回收xiaoming对emp表的查询权限
Revokeselectonempfromxiaoming;(用scott账号登陆)
2.1.5.权限传递
grantselectonemptoxiaomingwithgrantoption;
如果是对象权限:
withgrantoption
如果是系统权限:
withadminoption
>connsystem/manager;
>createuserxiaoming2identifiedbym123;
>用户已创建
>grantconnecttoxiaoming2;
>connxiaoming/m123;
>grantselectonscott.emptoxiaoming2;
第3章管理用户
3.1.用户管理
Profile管理用户口令
创建profile文件
createprofilelock_accountlimitfailed_login_attempts3password_lock_time2;(密码尝试3次,锁定2天)
>alteruserxiaomingprofilelock_account;
3.2.给账户(用户)解锁
alteruserxiaomingaccountunlock;
3.3.终止口令
给创建的用户tea创建一个profile文件,要求用户每隔10天修改自家的登陆密码,宽限期2天
>createprofilemyprofilelimitpassword_life_time10password_grace_time2;
>alteruserteaprofilemyprofile;
>createprofilepassword_historylimitpassword_life_time10password_grace_time2password_reuse_time10;
3.4.删除profile
dropprofilepassword_history[cascade]级联删除相关
第4章oracle表的管理
4.1.表名与列名的命名规则
①必须以字母开头
②长度不能超过30字符
③不能使用oracle的保留字
④只能使用如下字符A-Z,a-z;
4.2.数据类型
字符型:
char(20)定长最大2000查询速度快
varchar2(20)变长,最大4000字符
clob
数字型:
number-10^38~~~~10^38范围
number(5,2)有五位有效数字,2位小数
日期型:
date包含年月日时分秒
timestamp是oracle9i对date数据类型的扩展
图片类型:
图片,视频,mp3等;
4.3.建表
>createtablestudent(--表名
>xhnumber(4),--学号
>xmvarchar2(20),--姓名
>sexchar
(2),--性别
birthdaydate,--出生日期
salnumber(7,2)奖学金
);
4.4.修改表
添加字段altertablestudentadd(classidnumber
(2));
修改字段长度altertablestudentmodify(xmvarchar2(30));
修改字段类型名称altertablestudentmodify(xmchar(30));(表不能有数据)
删除一个字段altertablestudentdropcolumnsal;
修改表的名字renamestudenttostu;
删除表droptablestudent;
查看表结构descstudent;
insertintostudentvalues(1,’小明’,’男’,’11-12月-1997’,236.7,12)
修改oracle默认日期格式
altersessionsetnls_date_format=’yyyy-mm-dd’;(重启后失效)
插入部分字段insertintostudent(xh,xm,sex)values(‘A03’,’JOHN’,’女’);
select*fromstudentwherebirthdayisnull;
eg:
把‘男’的工资减半(修改一个字段)
updatestudentsetsal=sal/2wheresex=’男’;
updatestudentsetsex=’男’,birthday=’1980-04-01’wherexh=’A001’;
4.5.删除数据
deletefromstudent;表结构在,写日子,可以恢复
savepointaa;保存回滚点;①设置保存点
rollbacktoaa;回滚到aa点
droptablestudents;删除表的结构和数据
deletefromstudentwherexh=’A001’;删除一条记录
deletefromstudent;
truncatetablestudent;速度快,结构在,无法恢复;
第5章oracle表查询
5.1.查看表结构descstudent;
查询所有列select*fromdept;
查询指定列selectename,sal,jobfromemp;
取消重复行selectdistinctdeptno,jobfromemp;
打开显示操作时间的开关settimingon;
5.2.疯狂复制
insertintousers(userid,username,userpasw)select*fromuser;
selectcount(*)fromuser;查询总共有多少条记录
例:
显示每个员工的年工资
selectsal*13,enamefromemp;
selectsal*13+nvl(comm,0)*13“年工资”,ename,commfromemp;
5.3.使用where子句
①显示工资高于3000的员工>selectename,salfromempwheresal>3000;
②找1982.1.1后入职的员工selectename,hiredatefromempwherehiredate>’1-1月-1982’;
③工资在2000~~~~2500之间……wheresal>=2000andsal<=2500;
5.4.使用like操作符
①首字符为S的员工姓名和工资:
selectename,salfromempwhereenamelike‘S%’;
②第3个字符为大写O所有员工姓名和工资
selectename,salwhereenamelike‘__O%’;
③查询多个编号条件
select*fromempwhereempnoin(123,234,456);
select*fromempwhere(sal>500orjob=’MANAGER’)andenamelike‘J%’;
5.5.orderby默认从低到高
select*fromempsalorderbysalasc(升序)/desc降序;
例:
部门从低到高,薪水从高到低
select*fromemporderbydeptnoasc,saldesc;
5.6.复杂查询
①数据分组max,min,avg,sum,count
eg:
显示员工最高工资,最低工资
selectmax(sal),min(sal)fromemp;
②子查询
selectename,salfromempwheresal=(selectmax(sal)fromemp);
查询高于平均工资的人
select*fromempwheresal>(selectavg(sal)fromemp);
5.7.groupby和having子句
例:
显示每个部门的平均工资和最高工资
selectavg(sal),max(sal),deptnofromempgroupbydeptno;(按组分)
例:
显示每个部门的每种岗位的平均工资和最低工资;
selectava(sal),max(sal),deptno,jobfromempgroupbydeptno,job;
例:
显示平均工资,小于2000的部门号,和它的平均工资
selectavg(sal),max(sal),deptnofromempgroupbydeptnohavingavg(sal)<2000;
对分组后的结果进行筛选
5.8.多表查询
例:
显示部门号为10的部门名,员工和工资
selecta1.dname,a2.ename,a2.salfromdepta1,empa2wherea1.deptno=a2.detpnoanda1.dept
no=’10’;
例2:
……whereemp.salbetweensalgrade.losalandsalgrade.hisal;
自连接(在同一张表自查询)
查询FORD的上级是谁
selectworker.ename,boss.enamefromempworker,empbosswhereworker.mgr=boss.empnoandworker.ename=’FORD’;
5.9.子查询
单行子查询
多行子查询
查10号部门里所有工作,员工与里面工作相同的名字,岗位,工资部门号
selectdistisctjobfromempwheredeptno=10
select*fromempwherejobin(填入上面的语句);
返回多行用in不用等号
5.10.多例子查询
例:
与smith的部门和岗位完全相同的所有雇员(子查询)
①selectdeptno,jobfromempwhereename=’SMITH’
②select*fromempwhere(deptno,job)=(①);
5.11.复杂查询
在form子句中使用子查询
例:
查出高于自己部门的平均工资的员工信息
1查各个部门平均工资和部门号
selectdeptno,avg(sal)mysalfromempgroupbydeptno;(mysal别名)
2把上面的查询看做一张子表。
selecta1.ename,a1.sal,a1.deptno,a2.mysalfromempa1,(①)a2wherea1.deptno=a2.deptnoanda1.sal>a2.mysal;
5.12.分页查询
select*from(selecta1.*,rownumrnfrom(select*fromemp)a1whererownum<=10)wherern>=6;
几个查询变化
1只需修改最里层的子查询
5.13.用查询结果建表
createtablemyemp2(id,ename,sal)asselectempno,ename,salfromemp;
第6章数据库管理
6.1.数据库管理
>connsystem/managerassysdba;否则是普通用户登录
>shutdown关闭数据库
6.2.数据库备份(逻辑备份)
导出分三种:
①导出表②导出方案③导出数据库
exp命令来完成
userid用于指定执行导出操作的用户名,口令,连接字符串
tables用于指定导出表
owner导出方案
full=y指定导出的数据库
inctype导出增量类型
rows是否导出表中的数据
file指定导出文件名
例:
导出自己的表
expuserid=scott/tiger@myoraltables=(emp)file=d:
\e1.dmp
在cmd中输入oracle安装目录d:
\program\myoracle\bin
6.3.导出方案
expuserid=scott/tiger@myoralowner=scottfile=d:
\scott.dmp
第7章数据库管理
7.1.导出数据库(需要具有dba或者exp_full_database权限)
expuserid=system/manager@myoraclfull=yinctype=completefile=d:
\aa.dmp
inctype=incremental备份上一次改变的数据
导入表
impuserid=scott/m123@myoraltables=(emp)file=d:
\emp.dm
导入表到其他用户
impuserid=system/manager@myoraltables=(emp)file=d:
\***.dmptouse=scott;
导入方案
impuserid=scott/tigerfile=d:
\***.dmp
导入其他方案
impuserid=system/managerfile=d:
\***.dmpfromuser=systemtouser=scott;
原用户(从)目标用户(到)
7.2.导入数据库
impuserid=system/managerfull=yfile=d:
\***.dmp
7.3.数据字典
1user_tables
当前用户拥有的所有表selecttable_namefromuser_tables;
2all_talbes
当前用户可以访问的所有表(还会返回当前用户可以返回的其他方案的表)
selecttable_namefromall_tables;
3dba_tables
selecttable_namefromdba_tables;
7.4.用户名、权限、角色
dba_users数据库中总共有多少用户
selectusernamefromdba_users;
查询前看下表的结构descdba_users;
数据字典视图dba_sys_privs用户所有的系统权限
数据字典视图dba_tab_privs用户所有的对象权限
数据字典dba_col_privs用户具有的列权限(少)
数据字典视图dba_role_privs用户具有角色
例:
scott具有的角色,可查dba_role_privs
descdba_role_privs;
select*fromdba_role_privswhereGRANTEE=’SCOTT’;
例:
oracle中有多少角色
select*fromdba_roles;
例:
某个角色中有多少权限呢?
1一个角色包含的系统权限
select*fromdba_sys_privswheregrantee=’DBA/CONNECT’;(角色)
2一个角色包含的对象权限
select*fromdba_tab_privswheregrantee=’角色’
例:
查看某用户有什么角色
select*fromdba_role_privswheregrantee=’SCOTT’;
数据库中的系统权限
select*fromsystem_privilege_maporderbyname;
数据库中的对象权限
selectdistinceprivilegefromdba_tab_privs;
7.5.当前用户可以访问数据字典的视图
select*fromdictwherecommentslike‘%grant%’;
当前数据库的全称
select*fromglobal_name;
第8章数据库管理
8.1.表空间
㈠表空间是数据库的逻辑组成部分,从物理上讲,数据库数据存放在数据文件中。
逻辑上讲,数据库则是存放在表空间中,表空间由一个或者多个数据文件组成。
㈡oracle中逻辑结构包括表空间,段,区和块(例如:
省、市、县、区)
8.2.建立表空间
createtablespace
例如:
createtablespacespooldatafile‘d:
\spool.dbf’size20Muniformsize128k;
自定义表空间名保存位置20兆区的大小
8.3.使用表空间
创建一个表,这个表保存在名叫SPOOL的表空间上。
createtablemypart(deptnonumber
(2),dnamevarchar2(14),locvarchar2(13))tablespacespool;
8.4.改变表空间状态
①使用表空间脱机altertablespace表空间名offline;
②使表空间联机altertablespace表空间名online;
③只读表空间altertablespacequery_datareadonly;
insertintomypartvaluse(12,’sp’,’beijing’);
④可读可写altertablespace表空间名(例如:
spool)readwrite;
8.5.查看表空间包括的所有表
select*fromall_tableswheretablespace_name=’表空间名(大写)’;
②表属于哪个表空间
selecttablespace_name,table_namefromuser_tableswheretable_name=’EMP’;也是大写
8.6.扩展表空间
①增加数据文件
②增加数据文件大小
③设置文件的自增长
altertablespacespooladddatafile‘d:
\spoo2.dbf’size300M;增加数据文件
8.7.移动数据文件
①数据文件在的表空间
>selecttablespace_namefromdba_data_fileswherefile_name=’d:
\spool.dbf’;
②使表空间脱机
>altertablespacespooloffline;
③移动到指定位置
>hostmoved:
\spool.dbfc:
\spool.dbf;
④逻辑修改
altertablespacespoolrenamedatafile‘d:
\spool.dbf’to‘c:
\spool.dbf’;
⑤表空间联机
>altertablespacespoolonline;
第9章oracle事物
9.1.事物
>commit;提交
>savepointa1;
>rollbacktoa1;
注意:
一旦提交了,保存点就不存在了
第10章oracle的约束
insertintoempvalus(8888,’ok’,’clea’,7782,to_date(‘1992-12-12’,’yyyy-mm-dd’));
10.1.约束
①oracle数据完整性的技巧
②会建索引
③oracle的权限和角色
数据库的完整性:
约束,触发器,应用程序(过程,函数)
约束包括:
notnull,nuique,primarykey,foreignkeycheck
createtablegoods(goodsIdchar(8)primarykey,--主键
goodsNamevarchar2(30),
unitpricenumber(10,2)check(unitprice>0)--单价
categoryvarchar2(8),--商品类别
providervarchar2(30));
修改:
altertablegoodsmodifygoodsNamenotnull;
altertablecustomeraddconstraintcarduniqueunique(cardId);
altertablecustomeraddconstraintaddresscheckcheck(addressin(‘东城区’,’西城区’));
删除约束
altertable表名dropconstraint约束名(addresscheck)
第11章PLSQ
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 入门 熟练