【自学oracle】韩顺平oracle视频教程学习笔记

第一讲
oracle的数据对象(表,视图,序列,函数,包,过程,触发器)
不同的数据库 数据对象未必是完全相同的
例如sqlserver2000里面就没有包
多少人用(并发问题)


第二讲
1.9i只有一个安装文件
2.把这个10201_database_win32.zip文件拷贝到没有中文的路径下
3.到电脑的服务选项去确定本机没有安装oracle数据库,如果有,则卸载
orclxuzhu 命令
4.解锁步骤
  先使用system登录
  然后输入alter user scott account unlock
 5 system用户名 orclxuzhu密码


第三讲
1.show user 当前用户名是什么
2.请使用scott用户登录oracle数据库实例,然后,切换身份为system用户登录后,使用conn[ect] 用户名/密码@网络[as sysdba/as sysoper]
   
3.简单使用:conn[ect] 用户名/密码 
   该命令经常用于切换当前用户,因此我们建议大家在登录的时候使用普通用户,scott,如果确实需要system用户,则可以使用该命令切换为高级用户
4.disc[onnect] 该命令用户断开和oracle连接,但是不退出sqlplus窗口
5.exit命令 该命令用户断开和oracle连接,同时退出sqlplus窗口
6.passw[ord] 该命令用于修改用户的密码
  基本用法
  如果给自己修改密码,则可以不带用户名,如果给别人修改密码(前提是system或者sys用户来修改),则需要带用户名
7.& 交互命令
  select * from emp where job='&job';
  输入job的值:CLERK
8.edit命令
  用于编辑脚本
  edit d:/aa.sql
9.spool命令 该命令把屏幕上显示的记录,保存到文件中,以后分析
  spool d:/bak.sql
  select * from emp
  spool off
 
第四讲
1.linesize 用户控制每行显示多少个字符,默认80字符 set linesize 120;
2.pagesize 用于每页显示多少行 set pagesize 100;
3.创建用户(具有dba权限的用户才能操作,system,sys)
  create user 用户名 identified by 密码
  create user xiaoming identified by m123 [oracle要求用户密码不能用数字开始]
  
  这里刚刚创建的用户是没有任何权限的,甚至连登陆都不行
  需要管理员给用户分配相应的权限才能登陆,给一个用户赋权限使用命令grant,回收权限使用命令revoke
  
  如何给用户分配具体的权限
  grant create session to xiaoming 
  执行成功就可以登陆了
  
4.如果给别人修改密码则需要具有dba的权限,或是拥有alter user的系统权限,也可以使用password用户名
  alter user 用户名 identified by 新密码
  
第五讲
1.预定义角色把常用的权限集中起来,形成角色dba connect resource


2.使用system创建xiaohong
create user xiaohong identified by m123


3.给小红分配两个常用角色
grant connect to xiaohong
grant resource to xiaohong 


4.让xiaohong登录
connect xiaohong/m123


5.小红修改密码 如果不加上名字表示对当前用户
password xiaohong
旧口令:
新口令:


6.断开连接
disconnect


7.创建一张表
create table users(id number);


8.插入数据
insert into users values(1);


9.查询数据
select * from users;


10.使用system登录,然后回收角色
revoke connect from xiaohong
revoke resource from xiaohong


11.删除用户
drop user 用户[cascade]
drop user xiaohong cascade;
当我们删除一个用户的时候,如果这个用户自己已经创建过数据对象,那么我们在删除用户的时候,需要加选项cascade,表示把这个用户删除同时,把该用户创建的数据对象一并删除


12.显示当前连接用户
show user 


13.oracle方案
当一个用户,创建好后,如果该用户创建了任意一个数据对象,这时,我们的dbms就会创建一个对应的方案与该用户对应,并且该方案的名字和用户名一致
小技巧:如果希望看到某个用户的方案究竟有什么数据对象,我们可以用pl/sql developer


14.连接用户
conn scott/tiger;


15.如果希望看到某个用户的方案究竟有什么数据对象,我们可以用pl/sql developer


16.要求让xiaohong用户可以去查询scott的emp表
步骤1:先用scott登录
conn scott/tiger
步骤2:赋权限

grant select[update|delete|insert|all] on emp to xiaohong


conn xiaohong/m123
select * from scott.emp


第六讲
1.创建用户tea ,stu 并给这两个用户resource,connect角色
conn system/yzj;
create user tea identified by tea;
grant resource  to tea;
grant connect to tea;
create user stu identified by stu;
grant resource  to stu;
grant connect to stu;
2.使用scott用户把对emp表的select权限给tea
conn scott/tiger;
grant select on emp to tea;
使用tea查询scott的emp表
conn scott/tiger;
select * from scott.emp;
使用scott用户把对emp表的所有权限赋给tea
conn scott/tiger;
grant all on emp to tea;
使用tea更新/删除/插入 scott的emp表
conn tea/tea;
update scott.emp set job='Teacher' where job='&job';
delete from scott.emp where job='&job';
insert into scott.emp values(8888,'FORD','Teache','7698','08-9月 -81',1500,300,20);
使用scott收回权限
conn scott/tiger;
revoke select on scott.emp from tea;
revoke all on scott.emp from tea;
想办法将让tea把自己拥有的对scott.emp的权限转给stu


scott->tea->stu [权限转移.]


conn scott/tiger;
grant all on scott.emp to tea with grant option;


//with grant option 表示得到权限的用户,可以把权限继续分配
//with admin option 如果是系统权限,则带with admin option 


conn tea/tea;
grant select on scott.emp to stu;
使用stu查询scott用户的emp表
conn stu/stu;
select * from scott.emp;
使用tea收回给stu的权限
conn tea/tea;
revoke select on scott.emp from stu;


profile是口令限制,资源限制的命令集合,当建立数据时,oracle会自动建立名称为default的profile,当建立用户没有指定profile选项,那oracle就会将default分配给用户


需求:只允许某个用户,最多尝试登录三次,如果三次没有成功,则锁定两天,两天后才能重新登录
基本语法:
create profile 文件名 limit failed_login_attempts 3 password_lock_time 2;
alter user 用户名 profile 文件名;


给账户用户解锁
alter user 用户名 account unlock;


终止命令:为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba身份来操作
需求:说一个账号密码,最多10,宽限期为2天,到时必须设置新的密码


creat profile myprofile limit password_life_time 10 password_grace_time 2 继续加限制条件;
alter user tea profile myprofile;


口令历史
如果希望用户在修改密码时,不能使用以前使用过的密码,可使用口令历史,这样oracle就会将口令修改的信息存放到数据字典中,这样当用户修改密码时,oracle就会对新旧密码
进行比较,当发现新旧密码一样时,就提示用户重新输入密码


create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 1;
alter user tea profile password_history;


删除profile
drop profile profile文件名


cmd中启动oracle的监听服务
lsnrctl start


启动数据库实例
oradim -startup -sid orclxuzhu 


查看电脑什么时候安装的操作系统,网卡信息
systeminfo


第七讲
oracle认证方式


conn system/xxx;
conn system/xxx as sysdba;
show user;
user为"sys"
conn xxx/xxx as sysdba;
show user;
user为"sys"


不用认证用户名和密码 用户就是sys


oracle用户验证机制
普通用户,默认是以数据库方式验证,比如:conn scott/xx


特权用户(sys),默认是以操作系统认证(即:只要当前这个用户是在ora_dba组中,则可以通过),比如:conn system/orclxuzhu as sysdba 
dbms 一看到 as sysdba 则认为要以特权用户登录,前面的用户名和密码不看,登录后,自动切换成sys用户<=>conn sys/orclxuzhu
如果当前用户不在ora_dba组,这时再使用数据库验证方式


如果输入用户名和密码是对的 还是可以连接上的
conn sys/orclxuzhu as sysdba;


我们可以通过修改sqlnet.ora文件,让特权用户登录的时候,直接使用数据库验证
搜索到sqlnet.ora文件,修改
SQLNET.AUTHENTICATION_SERVICES=(NTS)


如何找回管理员密码
搜索名为PWD数据库名.ora文件
数据库实例名是根据实际情况定,比如orclxuzhu
拷贝一份密码文件
生成新的密码文件,在dos控制台下输入命令
orapwd file=E:\myoracle\oracle\database\PWDorclxuzh.ora password=xuzhu entries=10
entries 表示登录sys最多用户


如果希望新的密码生效,则需要重新启动数据库实例




第八讲
在运行里面输入
sqlplus 这个是dos窗口
sqlplusw 这个是应用窗口


create table table_name{
   列名 列的数据类型
   ......
}


create table users(
   id number,
   name varchar2(32),
   password varchar2(32),
   birthday date);


char(size) 定长 存放字符串最大2000个字符        1个a1个字符 1个汉字2个字符 原因:1个空间是1个字节来表示的
举例说明
create table test1(name char(32));
这样在name这列,最多只能放入32个字符,如果超过,就报错,如果不够'abc',则用空格补全


varchar2 变长 存放字符串最大4000个字符          1个a1个字符 1个汉字2个字符 原因:1个空间是1个字节来表示的


select name,dump(name) from test1;


如果我们的数据长度是固定,比如商品编号(8),则应当使用char来存放,因为这样存取速度快,如果存放数据的长度是变化,则使用varchar2


nchar 定长 编码方式是unicode   最大字符2000个   1个a1个字符 1个汉字1个字符 原因:1个空间是2个字节来表示的


nvarchar2 变长 编码方式是unicode   最大字符4000个   1个a1个字符 1个汉字1个字符 原因:1个空间是2个字节来表示的


clob 字符型大对象 变长 最大8tb


blob 变长 最大8tb 说明,我们实际开发中很少把文件存放数据库(效率问题),实际上我们一般记录文件的有一个路径(http://www.baidu.com/image/3.jpg d:/file/a.jpg)
然后通过io 网络来操作
如果我们要求对文件安全性,可以考虑放入数据库


第九讲
number是变长的
number可以存放整数,也可以存放小数
number(p,s)
p为整数位,s为小数位 范围:1<=p<=38,-84<=s<=127
保存数据范围:-1.0e-130<=number value <1.0e+126
-1.0e-130(科学计数法) :就是-1.0乘以10的-130次方
保存在机器内部的范围:1~22bytes


举例说明:
有效位:从左到右,第一个非0数就是第一个有效位


date日期类型
用于表示时间(年/月/日/时/分/秒)
insert into test8 values('11-11月-11');
oracle日期的默认格式是'dd-mm-yyyy';如果我们希望使用自己习惯的日期添加,也可以,但是需要借助oracle函数


第十讲
使用alter table 语句添加 修改 或删除列的语法
alter table tablename
add (columnname datatype);


alter table tablename
modify (columnname datatype);


alter table tablename
drop column columnname;


alter table tablename
drop column (columnname1,columnname2);


修改表的名称:rename 表名 to 新表名


删除表
drop table tablename;


增删改查
create table test10(id number);


insert into test10 (id) values('123'); 这是可以的 dbms视图把数据转化成对应的字段类型


字符和日期类型数据应包含在单引号中


oracle 会把‘’=Null


create table test14(name varchar2(64),age number);
insert into test14 (name,age) values("shunping",null);正确
insert into test14 (name) values("abc");正确 age就是null


如果给表的每列都添加值,则可以不带列名


update 如果没有where语句 就是更新所有的行


第十一讲
update students set fellowship=10 where fellowship is null;


delete 如果不使用where子句,将删除表中所有数据
delete from 表名 删除表中所有数据,表结构还在,写日志,可以恢复的,速度慢


delete语句不能删除某一列的值,如果要删除某列的值,则需要使用update语句


truncate table 表名:
删除表中所有数据,表结构还在,不写日志,无法找回删除的记录,速度快


savepoint aa;
delete from students;
rollback to aa;
select * from students;


查看表结构
desc 表名


去重复行
返回的数据完全一样才是重复行
select distinct deptno,job from emp order by deptno;


数据库的内容,字段 表名都不区分大小写
sqlserver2000 不区分大小写 
oracle 区分大小写


虚表
select abs(-100) from dual;


select abs(sal) from emp;


select sal*sal from emp;


select sal*abs(sal) from emp;


select ename,sal*13+comm*13 from emp;


select ename,sal*13+NVL(comm,0)*13 from emp;


oracle 不让用单引号
select ename,sal*13+NVL(comm,0)*13 "年薪" from emp;


select ename,sal*13+NVL(comm,0)*13 as 年薪 from emp;
说明:NVL函数是oracle提供的,用于处理数据null的问题
nvl(comm,0):如果comm为空null,则返回0,如果不为空,则返回本身的值


||:在查询时如果希望把多列拼接起来作为一列返回可以使用||
select ename || '是一个' || job from emp;


select ename || ' 是一个 ' || job from emp;


第十二讲
我们希望删除用户,同时保留该用户的数据对象,怎么处理
锁定用户
alter user scott account lock;
虽然锁定了用户 但是scott.emp在system 依然使用它的对象
select * from scott.emp;


用户解锁
alter user scott account unlock;


select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1982-1-1';


select * from emp where to_char(hiredate,'yyyy')='1980';


select * from emp where to_char(hiredate,'mm')='4';


select * from emp where sal>=2000 and sal<=2500;


select * from emp where sal between 2000 and 2500;


%表示任意0到多个字符,_表示任意单个字符


如何显示首字母为S的员工姓名和工资
select ename,sal from emp where ename like 'S%';


如何显示第三个字符为大写O的所有的员工姓名和工资
select ename,sal from emp where ename like '__O%';


第十三讲
如何显示empno为123,345,800的雇员情况
select * from emp where empno in(123,345,800);


如何显示没有上级的雇员的情况
select * from emp where mgr is null;


不写asc也行 默认是升序
select * from emp order by sal asc;


降序
select * from emp order by sal desc;


select * from emp order by deptno,hiredate desc;


select ename,sal*13+nvl(comm,0)*13 年薪 from emp order by 年薪;


下面这个也能排序
select ename,sal*13+nvl(comm,0)*13 年薪 from emp order by sal*13+nvl(comm,0)*13;


select max(sal) from emp;


select min(sal) from emp;


只能返回一个值 不确定是哪个人的
select max(sal*13+nvl(comm,0)*13) 年工资 from emp


select max(sal),min(sal) from emp;


avg会忽略sal为空的人
select avg(sal),sum(sal) from emp;


select sum(comm)/count(*) from emp;


总的员工数
select count(*) from emp;


comm不为空的字段
select count(comm) from emp;


select ename,job from emp where sal=(select max(sal) from emp);


select * from emp where sal>(select avg(sal) from emp);


select avg(sal),max(sal),deptno from emp group by deptno;


显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),max(sal),deptno,job from emp group by deptno,job order by deptno;


select avg(sal),deptno from emp group by deptno;


select avg(sal),deptno from emp group by deptno having avg(sal)<2000;


select avg(sal),deptno from emp group by deptno having avg(sal)>100 order by avg(sal);


第十四讲
多表查询
这下面两个查询语句效果一样
select distinct * from emp,dept;
select  * from emp,dept;


跟上面的条数一样但是数据呈现有点不一样
select  * from dept,emp;


显示各个员工的姓名,工资,及其工资级别
select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;


select * from emp where empno=(select mgr from emp where ename='FORD');


显示各员工的姓名和他的上级领导姓名
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;


select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno(+);左外连


select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename !='SMITH';
select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename <>'SMITH';


第十五讲


如何查询和部门10的工作相同的雇员的名字,岗位,工资,部门号
select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10);


如何显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno =30);
select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno =30);


如何显示工资比部门30的任意一个员工的工资高的员工的姓名,工资和部门号
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno =30);
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno =30);


如何查询与smith的部门和岗位完全相同的所有雇员
select * from emp where (deptno,job)=(select deptno,job from where emp where ename='SMITH');


select * from emp where deptno=(select deptnofrom where emp where ename='SMITH') and job=(select job where emp where ename='SMITH');不好


第十六讲
在from子句中使用子查询
如何显示高于自己部门平均工资的员工的信息


这里要用到数据查询的小技巧,把一个子查询当作一个临时表使用


各个部门的平均工资是多少?
select avg(sal),deptno from emp group by deptno;


把上面查询的结果当做一个临时表对待
select t2.ename,t2.sal,t1.myavg ,t2.deptno
from emp t1,(select avg(sal) myavg,deptno from emp group by deptno) t1 where t2.deptno=t1.deptno and t2.sal>t1.myavg;


如何显示各个部门最高工资的员工信息
select t2.ename,t2.sal,t1.mymax ,t2.deptno
from emp t1,(select max(sal) mymax,deptno from emp group by deptno) t1 where t2.deptno=t1.deptno and t2.sal=t1.mymax;


显示每个部门的信息(编号,名称)和人员数量
先查询出各个部门有多少人
select deptno,count(*) from emp group by deptno;
 
部门相同的只显示一条数据
select deptno from emp group by deptno;


分组之后部门的数量
select count(*) from emp group by deptno;


部门表中有个一个部门 但是没有人 结果没有显示那个没有人的部门信息
select t1.dname,t2.num from dept t1,(select deptno,count(*) num from emp group by deptno) t2  where t1.deptno=t2.deptno;


把没有人的那个部门也显示出来了
select t1.dname,t2.num from dept t1,(select deptno,count(*) num from emp group by deptno) t2  where t1.deptno=t2.deptno(+);


分页查询
分页查询是我们学习任何数据库,必须掌握的一个要点
mysql:
select * from 表名 where 条件 limit 从第几条取,取几条


sqlserver:
select top 4 * from 表名 where id not in(select top 4 id from 表名 where 条件);
排除前4条,再取4条,这个案例实际上是取出5-8


select emp.*,rownum from emp;


oracle:
select t2.* from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=6) t2  where rn>=4;


说明:上面的这个sql是oracle数据库效率比较高的查询方法,在百万级都可以及时响应


create table mytest as select empno,ename,sal,comm,deptno from emp;


自我复制


insert into mytest (empno,ename,sal,comm,deptno) select empno,ename,sal,comm,deptno from emp;


如果我们需要针对不同的情况,分页,请在最内层进行处理,包括多表


第十七讲
显示每个部门每个岗位的平均工资,每个部门的平均工资,每个岗位的平均工资   ??
select avg(sal),deptno,job from emp group by cube(deptno,job)


inner join
select 列名.. from 表1 inner join 表二 on 条件..


left join的另外一种写法
select stu.name,stu.id,exam.grade from stu,exam where stu.id = exam.id(+);


right join的另外一种写法
select stu.name,stu.id,exam.grade from stu,exam where stu.id(+)= exam.id;


full out join 
select stu.name,stu.id,exam.grade from stu full outer join exam on stu.id = exam.id;


第十八讲
select ename,job,hiredate from emp where hiredate between '01-2月-81' and '01-5月-81' order by hiredate;


select ename,job,hiredate from emp where to_char(hiredate,'yyyy-mm-dd') between '1981-02-01' and '1981-05-01' order by hiredate;


小结:
1.分组函数(avg...)只能出现在选择列表,having、order by 子句中
2.如果在select 语句中同事包含有group by,having,order by 那么他们的顺序是group by,having,order by
3.在选择列中如果有列,表达式,分组函数,那么这些列和表达式必须有一个出现在group by 子句中,否则就会出错


下面这句错误 order by 没有意义

select ename,job,sal from emp where job  in(select job from emp where depto = 30 order by job)


第十九讲
创建数据库有两种方式:
通过oracle提供的向导工具 推荐
我们可以用手工步骤直接创建


client:                oracle             orclxuzhu    方案(schema) scott       数据对象(table 过程 触发器 view 序列)
sqlplus                 dbms                            方案(schema) system      也有自己的数据对象
sqlplusw
pl/sql developer
网页 企业管理器


数据库orclxuzhu2  口令  orclxuzhu2


当我们创建完一个新的数据库实例后,在服务中就会有两个新的服务创建,这时,你根据实际需要去启动相应的数据库实例


在同一台机器,可以同时启动多个数据库实例,我们在登录或链接的时候,需要指定主机字符串


java连接oracle有两种方式
1.jdbc直连 特别说明:如果使用jdbc连接,需要启动监听服务
A;sun公司提供了一套对数据库操作接口/类 放在java.sql包     B;oracle公司把接口实现就打了一个包 ojdbc14.jar 提供给程序员使用
2.jdbc-odbc桥连接


第二十讲
dml 语句(数据操作语言)[insert,update,delete]
ddl 语句(数据定义语音) create table drop table
dql 语句(数据查询语言) select 
dcl 语句(数据控制语言) commit rollback


//完成查询
ps.executeQuery();


//插入 删除 更新
ps.executeUpdate();


第二十一讲
jdbc-odbc桥连接
步骤如下:
(1)配置数据源
(2)1521的监听实际上是可以不起的


Class.forName("sun.jdbc.odbc.jdbcOdbcDriver");//实际上是sun公司提供的
url="jdbc:odbc:hsporc"; hsporc是数据源名称


桥连和直连 实际上改下driver url 就可以互相使用


insert into emp(empno,hiredate) values(2222,'1988-11-11')报错 因为默认格式是(日-月-年)而且年是两位的,怎么解决 我们可以使用函数
TO_DATE(string,'format') 把字符串转成指定格式的日期
insert into emp(empno,hiredate) values(2222,to_date('1988-11-11','yyyy-mm-dd'));

©️2020 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值