Oracle认证
dba认证(oca,ocp,ocm)java开发认证网络认证
安装oracle
(1)准备好你的oracle安装文件
Oracle安装会自动的生成sys用户和system用户
(1)sys用户是超级用户,具有最高权限,具有sysdba角色,有createdatabase的权限,该用户默认密码是manager
(2)System用户是管理操作员,权限也很大,具有sysoper角色,没有createdatabase的权限。
默认的密码是change_on_install
(3)一般讲,对数据库维护,使用system用户登录就可以了
命令:
connuser/pwdasSYSDBA..
showuser
passw修改当前密码
alteruserscottidentfiedby1234修改其他用户密码需要DBA权限
alteruserscottaccountunlock;
start和@:
@d:
\a.sql或者startd:
\a.sql运行sql脚本
editd:
\a.sql可编辑sql脚本
spoold:
\b.sql并输入spooloff可将sql*plus屏幕上的内容输出到指定文件中去
showlinesize;显示行的宽度默认80个字符
setlinesize90设置行宽度
setpagesize设置每页显示的行数目,默认是14和linesize用法一样
创建用户
概述:
在oracle中要创建一个新的用户使用createuser语句,一般是具有dba(数据库管理员)的权限才能使用createusertcgidentifiedby1234
passw修改当前密码
alteruserscottidentfiedby1234修改其他用户密码需要DBA权限
删除用户
概述:
一般以dba的身份去删除某个用户,如果用其他用户去删除用户则需要具有dropuser用户名【cascade】在删除用户时注意如果要删除的用户,已经创建表,就需要在删除的时候带参数cascade
用户管理的综合案例
概述:
创建的新用户是没有任何权限的,甚至连登录的数据库的权限都没有,需要为其指定相应的权限,给一个用户赋权限使用命令grant,回收权限使用命令revoke
系统权限:
用户对数据库的相关权限
对象权限:
用户对其他用户的数据对象操作的权限(select,insert,update,delete,all,createindex)grantselectonemptoxiaoming
建表需要system赋予resource权限grantresourcetoxiaoming
收回权限revokeselectonscott.empfromxiaoming
对权限的维护
权限的传递:
grantselectonemptoxiaomingwithgrantoption
系统权限:
grantconnecttoxiaohongwithadminoption
使用profile管理用户口令
概述:
profile是口令限制,资源限制的命令集合,当建立数据库时,oracle会自动建立名称为default的profile当建立用户没有指定profile选项,那oracle就会将default分配给用户。
(1)账户锁定
概述:
指定该账户(用户)登陆时最多可以输入密码的次数,也可以指定用户锁定的时间(天)一般用dba的身份执行该命令
例子:
指定tea这个用户最多只能尝试3次登陆,锁定时间为2天
createprofilelock_accountlimitfailed_login_attempts3password_lock_time2;
alertuserteaprofilelock_account
(2)给账户(用户)解锁
alteruserteaaccountunlock;
(3)终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba身份来操作
例子:
给前面创建的用户tea创建一个profile文件,要求该用户每隔十天要修改自家的登陆密码,宽限期为2天
createprofilemyprofilelimitpassword_life_time10password_grace_time2;
alertuserteaprofilemyprofile
口令历史
概述:
如果希望用户在修改密码时,不能使用以前使用过的密码,可以使用口令历史,这样oracle就会将口令修改的信息存放到数据字典中,这样当用户修改密码时,oracle就会对新旧密码进行比较,当发现新旧密码一样时,就提示用户重新输入密码。
(1)createprofilepassword_historyllimitpassword_life_time10password_grace_time2password_reuse_time10
password_reuse_time10//指定口令可重用时间即10天后就可以重用
(2)分配给某个用户
alertuserxiaomingprofilepassword_history;
删除profile
概述:
当不需要某个profile文件时,可以删除该文件
Dropprofilepassword_history
字符型
char定长最大2000字符
例子:
char(10)‘小韩’前四个字符‘小韩’,后添6个空格补全
varchar2(20)变长最大4000字符
例子:
varchar2(10)‘小寒’oracle分配四个字符,这样可以节省空间
clob(characterlargeobject)字符型大对象最大4G
数字型
number范围-10的38次方------10的38次方
可以表示整数,也可以表示小数
Number(5,2)
表示一个小数有5位有效数,2位小数
范围-999.99--------999.99
Number(5)
表示一个五位整数
范围-99999------99999
日期类型
date包含年月日和时分秒
timestamp这是对date数据类型的扩展
图片
blob二进制数据可以存放图片/声音4G
例子
-----学生表
createtablestudent(
xhnumber(4),----学号
xmvarchar2(20),------姓名
sexchar
(2),-----性别
birthdaydate,-------出生日期
salnumber(7,2)----奖学金
);
createtableclass(
classidnumber
(2),
cnamevarchar2(20)
);
添加一个字段
altertablestudentadd(classidnumber
(2));
修改字段长度
altertablestudentmodify(xmvarchar2(30));
修改字段的类型/或是名字(不能有数据)
altertablestudentmodify(xmchar(30));
删除一个字段
altertablestudentdropcolumnsal;
修改表的名字
renamestudenttostu;
删除表
droptablestudent;
添加数据
所有字段都插入
insertintostudentvalues(‘A001’,’张三’,’男’,’01-5月-05’,10);
ORACLE中默认的日期格式‘DD-MON-YY’dd日子(天)mon月份yy2位的年‘09-6月-99’
1999年月9号改日期的默认格式
altersessionsetnls|_date_format=‘yyyy-mm-dd’;
修改后,可以用我们熟悉的格式添加日期类型:
insertintostudentvalues(‘A002’,‘MIKE’,‘男’,‘1905-05-06’,10)
插入部分字段
insertintostudent(xh,xm,sex)values(‘A003’,’JOHN’,’女’);
插入空值
insertintostudent(xh,xm,sex,birthday)values(‘A004’,’MARTIN’,’男’,null)
查询空
select*fromxxxwherexisnull;
改一个字段
updatestudentsetsex=’女’wherexh=’A001’;
修改多个字段
updatestudentsetsex=’男’,birthday=’1980-04-01’wherexh=’A001’;
删除数据
deletefromstudent;
删除所有记录,表结构还在,写日志,可以恢复,速度慢
savepointaa;
deletefromstudent;
rollbacktoaa;
droptablestudent;删除表的结构和数据
deletefromstudentwherexh=’A001’;删除一条记录
truncatetablestudent;
删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快
介绍
Select语句在软件编程中非常的有用,希望大家好好的掌握
查看表结构
descdept;
查询所有列
select*fromdept;
查询指定列
selectename,sal,job,deptnofromemp;
如何取消重复行
selectdistinctdeptno,jobfromemp;
查询smith
selectsal,job,deptnofromempwhereename='SMITH';
使用算数表达式
显示每个雇员的年工资
使用列的别名
Selectename,sal*12asysalfromemp;
selectsal*13+comm*13年工资,enamefromemp;
使用nvl()处理空值
selectsal*13+nvl(comm,0)*13年工资,enamefromemp;
如何连接字符串(||)
selectename||‘isa’||jobfromemp;
使用where字句
如何显示工资高于3000的员工
selectename,salfromempwheresal>3000;
如何查找1982.1.1后入职的员工
selectename,hiredatefromempwherehiredate>’1982-01-01’
如何显示工资在2000到2500的员工情况
Selectename,salfromempwheresalbetween2000and2500;
如何使用like操作符
%:
表示0到多个字符_:
表示任意单个字符
如何显示首字符为S的员工姓名和工资
Selectename,salfromempwhereenamelike‘S%’;
如何显示第三个字符为大写0的所有员工的姓名和工资
Selectename,salfromempwhereenamelike‘%__O%’;
在where条件中使用in
如何显示empno为123,345,800..的雇员情况
Select*fromempwhereempnoin(123,345,800);
使用isnull的操作符
如何显示没有上级的雇员的情况
Select*fromempwheremgrisnull;
使用逻辑操作符号
查询工资高于500或是岗位为MANAGER的雇员,同时还要满足他们的姓名首写字母为大写的J
Select*fromempwhere(sal>500orjob=’mangaer’)andenamelike“J%”
使用orderby字句
如何按照工资的从低到高的顺序显示雇员的信息
按照部门号升序而雇员的工资降序排列
Select*fromemporderbydeptno,saldesc;
使用列的别名排序
Selectename,(sal+nvl(comm,0)*12)“年薪”fromemporderby“年薪”asc;
别名需要使用“号圈中
在实际应用中经常需要执行复杂的数据统计,经常需要显示多张表的数据,现在我们给大家介绍较为复杂的select语句
数据分组-max,min,avg,sum,count
如何显示所有员工中最高工资和最低工资?
selectmax(sal),min(sal)fromemp;
selectename,salfromempwheresal=(selectmax(sal)fromemp);
显示所有员工的平均工资和工资总和?
Selectavg(sal),sum(sal)fromemp;
计算共有多少员工?
Selectcount(*)fromemp;
请显示工资最高的员工的名字,工作岗位?
selectjob,salfromempwheresal=(selectmax(sal)fromemp);
请显示工资高于平均工资的员工信息?
Select*fromempwheresal>(selectavg(sal)fromemp);
Groupby和having子句
Groupby用于对查询结果的分组统计
Having子句用于限制分组显示结果
如何显示每个部门的平均工资和最高工资
selectavg(sal),max(sal),deptnofromempgroupbydeptno;
显示每个部门的每种岗位的平均工资和最低工资
selectavg(sal),max(sal),deptno,jobfromempgroupbydeptno,joborderbydeptno;
显示平均工资低于2000的部门号和它的平均工资
selectavg(sal),max(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000;
对数据分组的总结
1分组函数只能出现在选择列表、having,orderby子句中
2如果在select语句中同时包含有groupby,having,orderby他们的顺序是goupby,having,orderby
3在选择列中如果有列、表达式、和分组函数,那么这些列和表达式必须有一个出现在groupby子句中
如selectavg(sal),max(sal),deptnofromempgroupbydeptnohavingavg(sal)>2000;
这里deptno就一定出现在groupby中
多表查询是指基于两个和两个以上的表或是视图的查询,在实际应用中,查询单个表可能不能满足你的需求,(如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept和emp表);
笛卡尔集:
规定:
多表查询的条件是至少不能少于表的个数-1
显示雇员名,雇员工资及所在部门的名字?
Selecte.ename,e.sal,d.dnamefromempe,deptdwheree.deptno=d.deptno;
如何显示部门号为10的部门名,员工名和工资?
selecte.ename,e.sal,d.dnamefromempe,deptdwheree.deptno=d.deptnoande.deptno=10;
显示各个员工的姓名,工资,及其工资的级别
selecta2.grade,a1.ename,a1.salfromempa1,salgradea2wherea1.salbetweena2.losalanda2.hisal;
显示雇员名,雇员工资及所在部门的名字,并按部门排序
selecta1.ename,a1.sal,a2.dnamefromempa1,depta2wherea2.deptno=a1.deptnoorderbya2.deptno;
自连接是指在同一张表的连接查询
显示某个员工的上级领导的姓名
比如显示‘ford’的上级
select*fromempworker,empbosswhereworker.mgr=boss.empnoandworker.ename='FORD';
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
单行子查询是指只返回一行数据的子查询语句
如何显示与SMITH同一部门的所有员工?
select*fromempwheredeptno=(selectdeptnofromempwhereename='SMITH');
多行子查询
多行子查询指返回多行数据的子查询
如何查询和部门10的工作相同的雇员的名称,岗位,工资,部门号
select*fromempwherejobin(selectdistinctjobfromempwheredeptno=10);
在多行子查询中使用all操作符
如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
selectename,sal,deptnofromempwheresal>all(selectsalfromempwheredeptno=30);
select*fromempwheresal>(selectmax(sal)fromempwheredeptno=30);
如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
selectename,sal,deptnofromempwheresal>(selectmin(sal)fromempwheredeptno=30);
单行子查询是指子查询只返回单列,单行的数据,多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句
查询与smith的部门和岗位完全相同的所有雇员
select*fromempwhere(deptno,job)=(selectdeptno,jobfromempwhereename='SMITH')
在from子句中使用子查询
如何显示高于自己部门平均工资的员工的信息
select*fromempa2,(selectdeptno,avg(sal)mysalfromempgroupbydeptno)a1wherea2.deptno=a1.deptnoanda2.sal>a1.mysal;
当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图,当在from子句中使用子查询时,必须给子查询指定别名
分页查询
按雇员的ID号升序取出
有3种方式:
1.rownum分页
select*from(selecta1.*,rownumrnfrom(select*fromemp)a1whererownum<=10)wherern>=6;(如果要指定查询列,只需修改最里面的子查询((selectename,salfromemporderbysal)))
2.Rowid
Select*fromt_xiaoxiwhererowidin(selectridfrom(selectrownumrn,rid)from(selectrowidrid,cidfromt_xiaoxiaoorderbyciddesc)whererownum<10000)wherern>9980)orderbyciddesc;(效率最高)
3.分析函数来分
Select*from(selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit)whererk<10000andrk>9980;
用查询结果创建新表
这个命令是一种快捷的建表方法。
createtablemytable(id,name,sal,job,deptno)asselectempno,ename,sal,job,deptnofromemp;
合并查询
有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号union,unionall,intersect,minus
1.Union
该操作符用于取得两个结果集的并集。
当使用该操作符时,会自动去掉结果集中重复行。
Selectename,sal,jobfromempwheresal>2500unionselectename,sal,jobfromempwherejob=’MANAGER’;
2.unionall不取消重复
3.Intersect
使用该操作符用于取得两个结果集的交集
Selectename,sal,jobfromempwheresal<2500intersectselectename,sal,jobfromempwherejob=‘MANAGER’
4.Minus
使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不存在第二个集合中的数据
Selectename,sal,jobfromempwheresal>2500minusselectename,sal,jobfromempwherejob=’manager’;
如何插入列带有日期的表,并按照年-月-日的格式插入
Insertintoempvalues(9998,'小红','MANAGER',7778,to_date('1988-11-11','yyyy-mm-dd'),78.9,55.9,10)
当使用values子句时,一次只能插入一行数据,