Oracle基本操作

曾经终败给现在 2022-04-04 14:42 261阅读 0赞

MySQL和Oracle的区别以及选择

1.  Oracle是大型数据库而Mysql是中小型数据库，Oracle市场占有率达40%，Mysql只有20%左右，同时Mysql是开源的而Oracle价格非常高。
2.  Oracle支持大并发，大访问量，是OLTP最好的工具。
3.  安装所用的空间差别也是很大的，Mysql安装完后才152M而Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能。  
    4.Oracle也Mysql操作上的区别  
    ①主键  
    Mysql一般使用自动增长类型，在创建表时只要指定表的主键为auto increment,插入记录时，不需要再指定该记录的主键值，Mysql将自动增长；Oracle没有自动增长类型，主  
    键一般使用的序列，插入记录时将序列号的下一个值付给该字段即可；只是ORM框架是只要是native主键生成策略即可。

②单引号的处理  
MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。

③翻页的SQL语句的处理  
MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用  
ROWNUM<100, 不能用ROWNUM>80

④ 长字符串的处理  
长字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用  
ORACLE里自带的DBMS\_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告,返回上次操作。

⑤空字符的处理  
MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符  
进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

⑥字符串的模糊比较  
MYSQL里用 字段名 like ‘%字符串%’,ORACLE里也可以用 字段名 like ‘%字符串%’ 但这种方法不能使用索引, 速度不快。

⑦Oracle实现了ANSII SQL中大部分功能，如，事务的隔离级别、传播特性等而Mysql在这方面还是比较的弱。  
启动Oracle实例  
net start oracleServiceORCL----数据库的名称就是ORCL,启动数据库实例  
OracleOraDb11g\_home1TNSListener如果需要使用C/S方式通信则必须启动监听器

如果IP地址发生变化，则启动数据库实例一定失败，则需要通过安装菜单中的Net Configuration Assistant重新进行配置，否则不能启动

Oracle11g的常用工具包括  
SQL\*PLUS提供命令行的操作方式  
企业管理器OEM提供了web管理方式  
SQL Developer提供了C/S图形化界面的管理方式  
第三方工具PLSQL Developer

进入SQLPlUS操作

sqlplus / as sysdba
    	一般命令格式为sqlplus 用户名/口令@本地网络服务名
    		例如sqlplus yanjun/123456
    		    sqlplus yanjun/123456fied by 123456;
    		或者：sqlplus

Oracle提供的SQL语言(非过程式编程语言)

DDL数据定义语言   create drop alter truncate
    DML数据库操纵语言 select insert update delete
    DCL数据控制语言  grant revoke
    TCL事务控制语言  commit rollback savepoint

Oracle的数据库物理结构：  
控制文件CONTROL01.CTL记录数据库管理系统的相关控制信息  
数据文件EXAMPLE01.DBF…存储相关的数据信息  
重做日志文件REDO01.LOG…存储从做日志信息

Oracle系统的表空间  
System系统表空间  
USER用户表空间  
TEMP临时表空间  
TOOLS 工具表空间  
…

DUAL表:哑元表，属于sys用户的一个实际存在的表，但是一般充当计算器功能

select sysdate from dual;获取系统当前时间

Oracle用户  
oracle中针对所有的数据库对象的管理都是基于用户(Schema)的

只有用合法的用户帐号才能访问Oracle数据库
    
    默认安装
    	sys/change_on_install
    	system/manager
    	scott/tiger
    
    scott用户下有样例表emp和dept，默认情况下该用户处于锁定状态，需要解锁才可使用
    	alter user scott account unlock;
    	connect scott/tiger

**SQL中的基础语法：  
sql语句不区分大小写  
sql语句可以分布在一行或者多行，最后一行加上“;”结束  
关键字不能进行缩写，不能分布在两行  
一般不同的子句写在不同的行**

用户操作
    创建新用户
    create user yanjun用户名 identified by 123456口令;
    	注意：新建的用户不能执行任何操作，包括连接数据库管理系统。如果需要执行什么操作必须有对应的权限
    
    删除用户
    	drop user 用户名
    		drop user yanjun cascade;级联删除用户下的所有数据库对象
    		drop user yanjun; 删除用户，要求用户下不能包含任何数据库对象
    
    修改用户口令
    	 alter user 用户名 identified by 新口令;
    		alter user b identified by abc;

权限管理: oracle中采用的是rbac基于角色的权限管理

Oracle中的权限可以分为两大类：系统权限、对象权限
    		grant 权限列表 to 用户名:  授予权限
    			grant create session to yanjun; //允许这个用户登录
    			grant all on scott.emp to yanjun;
    	revoke 权限列表 from 用户名： 撤销权限
    		revoke create session from yanjun;
    
    考核点  with admin option和with grant option之间的区别
    	这两个参数用于打开权限的级联授予
    	with admin option用于系统权限,允许级联授予，但是不支持自动的级联回收
    	with grant option用于对象权限,允许级联授予,同时支持级联回收
    
    权限的颗粒度太小，所以管理比较复杂。解决方案是引入角色
    
    三个重要的系统预定义的角色：
    	connect表示连接数据库的权限，一般授予给最终用户
    	resource表示数据库资源的操作权限，一般授予给开发人员
    	dba表示数据库管理的权限，一般授予给系统管理员
    
    		grant connect,resource,dba to yanjun
    	（3）撤销权限
    
     		 语法： revoke connect, resource from 用户名;
    
    	 列子： revoke connect, resource from test;

Oracle数据类型

1.数值型  number
    		整数  number(n)   1<=n<=38
    			create table t2(id number(3));取值范围为[-999,999]
    		浮点数number(p,s),其中p为所有的位数,取值范围为[1,38]，s为小数位数,取值范围为[-84,127]
    			考点：  number(5,1)  [-9999.9,9999.9]
    				number(3,5)  表示数据应该为0.00xxx
    				number(3,-5) xxx00000
    
    
    2.字符型
    		定长字符串char(n)  n表示允许最多的字符个数[1,2000]，如果实际插入字符数不足时，系统自动在末尾添加空格补齐长度。如果不使用长度单位，默认单位是byte。一个中文字符等于2个长
    			like 'a_'和'a%'  _表示一个任意字符，其中系统自动添加的空格也是任意字符。%表示任意长度的任意字符
    		变长字符串varchar2(n),n表示允许插入的最多字符数[1,4000],不会自动添加空格
    		超长字符串long，允许上限为2G
    
    	注意：允许在char或者varchar2的长度限制上添加单位byte/char，默认byte。
    		 create table c6(id varchar2(4 char));
    		 最多插入4个字符，不区分中英文
        日期型:
           	年份值必须介于-4713和+9999之间, 且不为0
    
    	date时间可以精确到1秒,一般开发使用.系统提供了sysdate函数可以自动填充系统当前时
    	timestamp时间戳类型，可以精确到小数点后9位的秒，这个类型的数据一般是供系统使用或者满足特殊要求
    
    	时间的格式问题：
    		系统默认的日期格式为dd-MON yy  dual演员表
    			SQL> select sysdate from dual;
    
    			SYSDATE
    			--------------
    			10-10月-18
    		修改当前系统默认的会话日期格式
    			 alter session set nls_date_format='yyyy-MM-dd hh24:mi:ss';
    		使用系统函数
    			to_date('字串值','识别字串数据的格式')将字串转换为日期格式
    				select to_date('1989-2-3','yyyy-MM-dd') from dual;
    			to_char(日期数据，'格式')将日期类型数据按照指定的格式转换为字符串
    				 select to_char(sysdate,'yyyy-MM-dd')from dual;
             二进制型用于存放二进制数据，一般开发中不建议使用
    	        raw(n)  [1,2000]
    	        long raw
    3.大对象型  LOB  上限是4G
    	BLOB二进制大对象，一般可以存放媒体数据
    	CLOB字符大对象，一般用于存放文本数据
    	BFile文件大对象，数据库中只存放文件指针，具体的数据存放在操作系统的文件中

Oracle中定义了6种约束：针对字段中允许填充的数据进行限制  
数据的完整性：实体、参照、用户自定义

primary key 主键--实体完整性
    foreign key  外键---参照完整性
    default
    not null/null
    check
    unique