谈谈Oracle基本操作(上)
当前我们市面上流行的数据库有sybase,Oracle,DB2,Mysql,sqlSever,MSQL, MariaDB
今天我们主要谈谈Oracle数据库
一:Oracle原理
1:什么是数据库:数据库管理的是硬盘中的数据,把数据打包,方便管理,数据增删改查,它的保存数据的是数据库文件;数据库一切都是表:用数据表来保存数据;
2:分类:
(1)结构化数据库:保存在硬盘当中,SQL语句查询;以SQL语句模型为基础的数据库
(2)非结构化数据库:mongoDB,Redis,memecache;把数据保存在内存,在一定时间内写
3:每个数据库就是一个实例,就是一个服务,直接面对操作系统,没有隔离层,效率高
4:sql语句是定位到表,定位到行(从后往前执行),定位到列,输出
二:数据库的常用简单常识:
1:Oracle主要以system登陆
2:Oracle管理工具
(1)sqlplus
(2)pl/sql
3:sqlplus常用命令:
(1)连接管理命令---conn(ect)用户名/密码@网络服务器[as system/sysdba];
---断开连接:disconnect/disc;
---修改密码:passw[ord] 修改自己的密码:password 回车 旧密码 然后新密码;
---显示当前用户:show user;
---退出:exit
(2)文件操作命令---运行指定的sql脚本:start和@ ; start d:\test.sql/start d:\test.sql;
---edit:编辑指定的SQL标本:sql>edit d:a.sql;
---spool:将屏幕上的内容输出到指定文件中spool d:\b.sql 然后 :sql>spool off;
(3)交换命令:可以替代变量,该变量在执行时需要用户输入select * from student where job = '&job';
(4)看当前用户可以操作的表:pl/sql:我的对象-->Tables;
三:用户管理:
1:用户本身管理:
(1):增---create user C##用户名 identified by 密码;注意:密码必须以字母开头;
(2):删---drop user C##用户名 cascade;
(3):改---Paddword 用户名;
(4):查---select * from dba_users;查看数据库里面所有的用户;
---select * from all_users;查看你能管理的所有用户;
---select * from user_users;查看当前用户信息;
2:用户权限管理:
(1):基本原理:什么是权限:就是你能不能曹组某跳SQL语句;
(2):权限有对象权限;系统权限;
(3):添加权限:
---grant connect to C##用户名;
---grant 操作名 on 表明 to 用户名;
---grant 操作名 on 表明 to 用户名 with grant option;
(4)删除权限---revoke 操作名 on 表名 from 用户名;
3:用户密码管理:
(1)profile:用户密码管理文件,用于强制对用户进行管理,步骤是创建文件,赋予文件;
(2)数据库创建时会自动创建默认的profile选项:default,建立用户时如果没有指定profile选项就会分配default;
(3)用户锁定:
---增加用户锁定:create profile 规则名称 limit failed_login_attempts 错误次数 password_lock_time 锁定天数;
---删除用户锁定:drop profile password_history/名字 (cascade);
4:基于角色的权限管理:
(1):基本原理把各个权限打包,继承到某个角色上,给用户赋予这个角色,用户就拥有了这些权限;
(2):重要角色---resource:数据库内部资源操作权限;
---connect:连接权限;
---dba:数据库管理员权限;
四:数据表管理
1:增:简单插入---insert into 表名 values ('A001','tom','男','01-05-14',10); //和列字段对应即可
多个插入---insert into 新表名(字段名) select 列 from 表名 where 行限定条件(不是所有数据,年龄段,某个部门)
技巧函数---to_date():to_date('日期',yyyy-mm-dd):按照你喜欢的格式插入日期(y/m/d)
2:查---select * from 表名; //该用户自己的表
select * from 用户名.表名; //查属于别人的表,需要授权(系统用户,或者数据拥有用户)
3:改---update 表名 set 列名='值' where id='B0002';
4:删---drop table 表名; //删除数据和表,快,不能恢复
delete from student; //删除所有记录,表还在,同时会把操作过程写入日志中,可以恢复,但是速度很慢
五:Oracle查询:
在复合SQL语句语句中,如果没有达到预期结果,我们从内到外或者从外到内,把每个分语句都执行一遍,进行查错;先定位到表,定位到行,定位到列,输出
1:单表查询
(1)存查询语句
---select 列限定 from 表限定 where 行限定; //数据限定条件区分大小写,java限定大小写,select什么就输出什么select * from emp where job = 'CLERK';
---查询空值:select * from 表名 where 列名 is null;
---模拟中的数据复制的技巧:insert into users(id,name,pass) select * from users;
---查询特定列的值:select col1,col2,col3,... from 表限定 where 行限定;
---去除重复行(distinct):select distinct 列限定 from 表名 where 行限定(z只能单行查询)
---where 行限定 and 行限定:select * from emp where empno < 7800 and sal>2000
---like 模糊行限定:
(1)%:匹配多个字符:select * from 表名 where 限定列 like "%k%"
(2)_:匹配一个字符:select * from 表名 where 限定列 like "__k%";//两个下划线
---in:枚举查询:select * from emp where empno in(12,56,90);
(2)使用逻辑操作符:比如工资高于400或岗位为manager的雇员,并且名字首字母为大写的J---select * from emp where (sal>500 or job='manager') and ename like 'J%';
(3)统计函数总结:统计函数只能出现在选择列表,having,order by子句中
列子:select avg(sal), max(sal),deptno from emp group by deptno having avg(sal) > 2000 order by avg (sal);
2:多表查询
(1)笛卡尔乘积的原理----默认回去每条数据都去对应一遍;所以,多表查询的时候,绑定条件不能等于表的个数-1(n-1); 列子:select * from emp,dept;
(2)逻辑外键多表联查---列子:select e.name,d.name, from emp e,dept d where e.deptno=d.deptno
(3)外表限定范围的多表查询(设计)----列子:select * from emp e,salgrade s where e.sal between s.losal and s.hisal;
(4)本表多层次查询(无限分类),自连接的多表查询---列子:select * from emp worker,emp boss where worker.mgr = boss.empno;//查出的都是有领导的人
3:子查询/嵌套查询
(1)什么叫子查询:多个select 关键词在同一个查询语句中,这种情况下,就是子查询.把内部select查询到的结果当成一张表,在通过外面的select语句查询出最终的结果
(2)行子查询---"=" 单行查询(只返回一行数据):select * from emp where depno=(select deptno from emp where ename='SMITH');//和smith同一部门的所有员工
---"in" 多行子查询(返回多行数据)(包含): select * from emp where job in(select distinct job from emp where deptno=20);//查询20号部门工作相同的员工信息
---"all"操作符-多行(比最大的大,取最大值):select * from emp where sal > all (select sal from emp where deptno=30);
select * from emp where sal>(select max(sal) from emp where deptno=30);//显示比部门30的所有员工工资高的员工
(3)列子查询---列子:select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');//注意查询的列顺序一致
(4)from子句子查询---在from子句中使用子查询的时候,该子查询会被当做一个视图来对待(也是一张缓存表),因此叫做内嵌视图,当在from子句中使用子查询的时候,必须指定别名,但是不要加as,列别名可以加as
---列子:select t2.ename,t2.sal,t2.deptno,t1.avsal from emp t2, (select deptno, avg(sal) avsal from emp group by deptno) t1 where t2.deptno = t1.deptno and t2.sal > t1.avsal;
(5)子查询更新数据---列子:update emp set (job,sal,comm)=(select job, sal,comm from emp where ename='SMITH') where ename='SCOTT';
4:分页查询
(1)分页查询原理:只要是到了数据库,一切都是表.视图,数据字典,包括我们查询所获得的结果,都是表;那我们查询的对象本身就是表,获得的结果也是表
(2)rownum分页:列子
---第一步:select * from emp;//子查询,
---第二步:2 select ta.*, rownum rn from (select * from emp) ta;//显示行号,oracle分配的
---第三部:2 select ta.*, rownum rn from (select * from emp) ta;//显示行号,oracle分配的
---第四部:4 select * from (select ta.*, rownum rn from (select * from emp) ta where rownum <=10) where rn >= 6;
(3)RowID分页---列子:select * from ** where rowid in (select rid from (select rownum rn, rid from (select rowid rid, cid from emp order cid desc) where rownum <1000) where rn>9980) order by cid desc;
(4)分析函数来分页---列子:select * from (select t.*, row_number() over( order by cid desc) rk from ** t) where rk <10000 an rk >9980;
5:查询结果直接建表---列子:create table mytable(id,name,sal) as select empno,ename,sal from emp
6:合并查询
(1):union//或(or关键词)---列子:select * from emp where sal>2500 union select * from emp where job='MANAGER';//取得两个结果集的并集,去掉重复
(2):union all//或(or关键词)---列子:select * from emp where sal>2500 union all select * from emp where job='MANAGER';//取得两个结果集的并集,不去掉重复
(3):intersect//且(and)---列子:select * from emp where sal>2500 union all select * from emp where job='MANAGER';//取得两个结果集的并集,不去掉重复
(4):minus//差集(-)in/not in---列子:select * from emp where sal>2500 minus select * from emp where job='MANAGER';//取得两个结果集的差集,选择第一个集合中特有的数据,前面的是被减去
7:SQL函数(dual:测试表)
(1):字符函数---lower(字符):把字符串转化为小写;
---upper(char):将字符串转化为大写格式
---length():返回字符串的长度
---substr(char,m,n):取字符串的字串
---replace(char,search_s,replace_s) : 后换前
---instr(char_1,char_2,[,n[,m]]):取得chr_2,在char_1中起始位置下标
(2):数学函数
---abs(n):取绝对值
---round(n,[m]):四舍五入,n为数据,m为四舍五入到第几位
---trunc(n,[m]):截取,截取到小数点的第几位
---mod(m,n):对m用n取摸(余数)
---floor(n):向下取整
---ceil(n):向上取整
(3):日期函数
---dd-mm-yy:默认日期:天,月,年
---to_date
---sysdate:返回系统时间
---add_month(d,n):
---last_day(d);指定月份最后一天
(4):数据转换函数:用于将一种数据类型转换成另外一种数据类型,某些情况下,oracle会允许值的数据类型和实际的不一样,这是oracle会隐式的进行数据类型转换.
并不好,最好用转换函数进行显式的转换---列子:create table t1(id,int);insert into t1 values('10');
(5):系统函数/sys_content
---terminal:当前会话客户对应的终端的标示符
---language:语言
---db_name:当前数据库名称
---nls_date_format:当前会话客户对应的日期格式
---nls_date_format:当前会话客户对应的日期格式
---host:主机名称
---select sys_context('userenv','db_name') from dual;//usernv:用户环境,固定格式
六:数据表字段管理
1:增加一个字段---alter table student add (age number(3));
2:删除一个字段---alter table 表名 drop column 列名; //强烈建议不要对成熟的系统这么做
3:修改字段的类型或是名字(不能有数据)---alter table student(表名) modify (sex number(1));
4:修改表的名字---修改表的名字
5:查:desc 表名
七:数据对象管理
1:简单插入---insert into 表名 values ('A001','tom','男','01-05-14',10); //和列字段对应即可
2:多个插入---insert into 新表名(字段名) select 列 from 表名 where 行限定条件(不是所有数据,年龄段,某个部门)
3:查询---select * from 表名; //该用户自己的表
4:修改---update 表名 set 列名='值' where id='B0002';
5删除:
(1)drop table 表名; //删除数据和表,快,不能恢复
(2)delete from student; //删除所有记录,表还在,同时会把操作过程写入日志中,可以恢复,但是速度很慢
(3)truncate table 表名; //相当于删除表和数据然后重建表.
(4)delete from 表名 where 行限定条件; //删除不定条数
