解锁scott:
打开SQLPLUS(是oracle的自带的工具软件,主要用于执行SQL语句,pl\sql块),也可以在“运行”里面输入“sqlpus或sqlplusw" 输入:用户名(如system),密码
输入 alter user scott account unlock
PL/SQL developer属于第三方软件,主要用于开发,测试,优化oracle pl/sql的存储过程。这款软件用的很多,需要单独安装。
企业管理器
- oracleDBConsoleorcl(一般不启动)服务是启动状态才可以启动企业管理器
- 在浏览器中输入:http://ip:1158/em
oracle 常用的sql plus命令
- show user 可以用来显示当前的用户名
- conn 用户名/密码 切换用户,可用于连接
- disc 断开和oracle数据库的连接,但是不退出sql*plus窗口,exit是断开与oracle的连接,同时退出窗口
- passw/password 用于修改用户的密码。如果给自己修改密码则不需要用户名,给别人修改密码则需要带用户名
- & 交互命令 select * from emp where job=‘&job';可以自己输入值代替&进行查询
- set linesize 140;设置每行显示多少个,默认是80个字符;set pagesize 30;设置一而显示多少条数据。
- edit 用于编辑脚本。如:edit D:/a.sql ;
- spool 把屏幕上显示的记录,保存到文件中 。首选 spool D:/a.sql创建一个文件,然后点select * from emp;将查询数据写入,最后关闭 spool off;数据就写入了。
分配权限
- 创建用户:create user 用户名 identified by 密码 。如:create user xm identified by m123 【只有system/sys有权限创建用户,普通用户无法创建】
-
- 表空间:表存在的空间,是一个逻辑的概念。 sql> create user demo identify by m123 default tablespace users temporary tablespace temp quota 3m on users;
- identified by 表明该用户demo将用数据库方式验证,default tablespace users //用户的表空间在users上。 temporary tablespace temp //用户demo的临时表健在temp空间。 quota 3m on users//表明用户demo建立的数据对象(表、索引、视图,pl/SQL块 ...)最大只能是3M
- sql> grant/revoke [connect|resource|DBA|create session] to demo 授权或移除授权。create session 是权限,其它的是角色。
- connect和resource是两个系统内置的角色,和dba是并列的关系。DBA:拥有所有特权,是系统最高权限,仅仅有DBA才干够创建数据库结构。RESOURCE:拥有Resource权限的用户仅仅能够创建实体,不能够创建数据库结构。CONNECT:拥有Connect权限的用户仅仅能够登录Oracle,不能够创建实体,不能够创建数据库结构。
-
- 修改密码:password 用户名,具有dba的权限或者拥有alter user的系统权限;alter user 用户名 identified by 新密码
- 删除用户:drop user 用户名,当我们删除一个用户的时候,如果这个用户自己创建有数据对象时,那么我们在删除该用户的时候需要加选项 cascade ,表示把数据对象一起删除。
方案(schema)
理解:当一个用户创建好后,如果该用户创建了任意一个数据对象,这时我们的dbms就会创建一个对应的方案与该用户对应,并且该方案的名字和用户一致。
赋权:grant [select |delete|insert|update|all] on 表名 to 用户名 with grant option; with grant option 表示得到权限的用户可以把权限继续分配。如:grant all on scott.emp to stu;如果是管理员权限则带 with admin option
查询:select * from scott.emp
收回权限:revoke [select |delete|insert|update|all] on 表名 from 用户名
oracle用户管理
profile管理用户口令:profile是口令限制,资源限制的命令集合,当建立数据时,oracle会自动建立名称为default的profile,当建立用户没有指定profile选项,那oracle就会将default分配给用户。
使用profile文件对口令进行管理,
创建: create profile 文件名 limit failed_login_attempts 3 password_lock_time 2 ,
使用: alter 用户名 profile 文件名;解锁:alter 用户名 account unlock;
终止口令:create profile 文件名 limit password_life_time 10 password_grace_time 2; 10天修改密码,密码的宽限是2天
删除:drop profile 文件名 【cascade】
oracle数据库的启动流程
linux里的ssh远程登录工具,systeminfo可以显示系统的基本信息
windows操作系统:
lsnrctl start(启动监听)
oradmin -startup -sid orclhsp (启动实例)
unix操作系统/linux系统: 和windows相差不大
oracle登录认证方式
conn ***/*** as sysdba //能够连接成功,只要as sysdba 后系统不会去验证用户名和密码就自动登录。
- 操作系统认证
- 如果当前用户属于本地操作系统的ora_dba组(对于windows操作系统而言),即可通过操作系统认证。
- oracle数据库验证(密码文件验证)
- 对于普通用户,Oracle默认使用数据库验证
- 对于特权用户(比如sys用户),oracle默认使用操作系统认证,如果验证不通过,再到数据库验证(密码文件验证)。通过配置sqlnet.ora文件,可以修改Oracle登录
- SQLNET.AUTHENTICATION_SERVICES=(NTS)是基本操作系统验证
- SQLNET.AUTHENTICATION_SERVICES=(NONE)是使用数据库验证
- SQLNET.AUTHENTICATION_SERVICES=(NTS,NONE)是两者共存
- 管理员密码丢失
- 恢复办法:把原有密码文件删除,生成一个新的密码文件
- 步骤如下:
- 搜索名为PWD数据库实例名.ora文件,如(PWDORACLE.ORA)
- 删除该文件,为预防万一,建议大家备份
- 生成新的密码文件,在dos控制台输入命令:orapwd file=原来密码文件的全路径\密码文件名.ora password=新密码 entries=10;//entries:允许几个特权用户 ,密码文件名一定要和原来的密码文件名一样。如:orapwd file=D:\app\admin\product\11.2.0\dbhome_1\database\PRDorcl.ora password=123 entries=10
- 重新启动数据库实例
修改oracle数据库system密码:
一、脑子短路,oracle密码不记得了。
win+r 打开 cmd 小黑屋。运行 sqlplus "/as sysdba" (前提是安装的时候配置了环境变量)
二、修改System的密码
alter user system indentified by 12345678;
三、解锁System用户即可
alter user system account unlock;
数据库基本概念
1.数据类型
select *,dump(name) from table ; dump查看字段的详细信息(编码,长度等)
char(size),size不能超过2000,是定长。如果是英文字母,能存放size个字符,如果是中文,占用两个字符空间。
varchar2(size),varchar2的性能更优,是变长,最长可存放4000个字符,字母占一个字节,中文占两个字节。
nchar(size),是unicode数据类型,定长,最大2000字符。不管是英文还是中文,能存放size个字符。unicode的好处:为了满足各国不同大小容量的文字。
nvarchar2(size),unicode数据类型,变长,最大4000个字
clob(character large object),字符型大对象,最大8tb
blob(binary large object),二进制数据,可以存放图片/声音8tb
number(p,s),数值类型,变长,p为整数,s为小数位 ;范围:1<=p<=38,-84<=s<=127,保存数据泛微-1.0e-130<=number1.0e+126,p为有效位,从左往右数每一个非0的数就是每一个有效位,如number(5,2) 范围-999.99-999.99;number 什么都不写,保持原数;number(6,-2),存123.55——存入100,167.98——存入200;
date,日期型,默认的日期格式是美国格式(“DD-MM-YY"),如果要用习惯的格式的话要用oracle函数,timestamp(n)游戳类型,当数据更新时,日期自动更新;日期类型必须加单引号。to_date(string,'format');
2.基本语法
修改表名:rename 旧表名 to 新表名
查看表结构:desc 表名
修改列名:alter table 表名 modify (列名 列类型); 当写where语句为空时 where name is null
删除列名:alter table 表名 drop(列名1,列名2);
增加列名:alter table 表名 add(列名 列类型);
删除行后找回:delect * from mytable ;找回数据 先 savepoint a;然后rollback to a ; truncate table 表名 删除以后无法找回。
查询:select [distinct] * from mytable;distinct去重复数据。sql语句(如表名、字段名)不区分大小写,内容是区分大小写的。
soundex:返回一个与给定的字符串读间相同的字符串。select * from table1 where soundex(name)=soundex(‘weather’);
ABS:返回指定值的绝对值。select abs(price),abs(-100) from dual;
ACOS:给出反余弦的值。ATANT:返回一个数字的反正切值。COS:返回一个给定数据的余弦。select cos(-3.1415926) from dual ;———— -1
CEIL:返回大于或等于给出数字的最小整数。 select ceil(3.14) from dual;FLOOR:对给定的数据取整数;LN:返回一个数据的对数;
3.基本运算
nvl(字段名,0):用于处理数据为null时的问题,如果字段为空时不取0,如果不为空取本身值。
|| 连接字符串。 select name ||”年龄是“|| age from table1; // 张三年龄是25
alter user scott account lock;用户锁定以后,只是不能登录,但是表还是可以访问
to_char:字符格式的转换;//如:where to_char(birthday,'yyyy-mm-dd')>'1995-1-1'; 4月出生的:to_char(birthdat,'mm')='4'
like:%表示0到多个字符,_ 表示一个字符;
order by:是对查询结果进行排序。默认是升序asc。支持别样查询。order by后面是可以加表达式的。
数据分组:max、min、avg、sum、count,当返回一个值时可以放 在一起查询,当返回的结果条数不一样时一起查询会出错;avg(SAL),当SAL为null时不会统计。//统计带空的时 select sum(SAL)/count(*) from table1; count统计时也会排除null的项。
SQL语句的执行顺序:1、默认情况下是从右向左执行;
group by 和 having:group by 用于对查询的结果分组统计,group by 字段1,字段2 ;having子句用于过滤分组显示的结果,即相当于group by的查底结果where 的作用。
自查询:select * from table1 t1,table1 t2 where t1.name1=t2.grad1(+);一定要取别名否则会出错。(+)是外联的附号,(+)写在左边是右外联,写在右边是左外联。
4.子查询
单行子查询:子查询只返回一条记录。
多行子查询:子查询返回多条记录。any指结果中的任一一条数据都满足条件。
多列子查询:查询查询结果满足两列条件 //如查询与smith的部门和岗位完全相同的所有雇员 :select * from emp where (detpno,job)=select (depno,job)from emp;
注:oracle支持数字排序,但是不能数字不能超过列数;子查询的时候必须指定别名;where上面取个别名,前面的语句将无法识别;desc显示表结构。
//显示部门最高工资的人的详细信息
分页查询:
mysql分页:select * from 表名 where limit 从第几条取,条数
sql server分页:select top 10 * from (select rownum,tb1.* from tb1 where rownum>=10 );
oracle分页:三层过虑 select t2.* from(select t1.* ,rownum rn from (select * from emp) t1 where rownum<=6) t2 where t2.rn>=4;
显示上级的个数(distinct mgr)去掉重复的了再统计;count(列名)统计一列;
模拟10W条数据
- 创建一张表:create table mytest as select empno,ename,sal,comm,deptno,job from emp;
- 自我复制:insert into mytest (empno,ename,sal,comm,deptno,job) select empno,ename,sal,comm,deptno,job from mytest;
5.合并查询
union:取得两个结果集的并集。会自动去掉结果集中的重复行。
union all:和union相似,但是不会去掉重复行,也不会排序。
intersect:取交集
minus:取差集,A1集合-A2集合。cube立方体 select avg(sal),deptno,job from emp group by cube(empno,job)先对empno分组、再组job分组,再对empno和job分组
6.内连接和外连接
内连接:就是利用where子句对两张表的迪卡尔集进行筛选。 select ... tb1 inner join tb2 where tb1.id=tb2.id;(只有两张表两边都匹配的时候才被选择)
外连接:左外连,右外连,完全外连 。
left join...on(左侧的表完全显示);另外一种写法select * from stu ,emam where stu.id=exam.id(+);
right join...on (右边的表完全显示);另外一种写法select * from stu ,emam where stu.id(+)=exam.id;
full join...on (左边的表和右边的表都显示);
7.小结
- 分组函数(avg...)只能出现在选择列表、having、order by子句中
- 如果在select语句中同时包含group by,having,order by那么他们顺序是group by,having,order by
- 在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在group by子句中,否则会出错。