这是我学习Oracle数据库时自己总结的笔记。今天发布在博客上。带目录,带范例。由于个人知识的局限性。难免会有错误的地方。欢迎指正,共同学习。谢谢。
另外我总结的也有Word格式的笔记。如有需要学习的朋友请私信我、或评论留邮箱获取。
目录
第01讲:Oracle基础
1.1基本概念:
第02讲:oracle安装
2.1oracle安装
2.2登录数据库
2.3查询库表信息
第03讲:Oracle体系结构
3.1数据存储结构
3.2内存体系结构
3.3oracle库开启顺序
第04讲:表格管理
4.1表格管理
4.2数据类型
4.3-约束规则
4.4增删改查
4.5修改表alter
第05讲:用户管理
5.1创建用户
5.2用户授权:
第06讲:表的查询
6.1查询语句公式:
6.2简答查询
6.3判断空值函数
6.4排序order by
6.5多表联合查询
6.6子查询
6.7分组查询
第00讲:总结
End
第01讲:Oracle基础
1.1基本概念:
数据库:DB,数据存储的仓库,帮助人员进行有效的数据管理。
一个数据库即是一套完整功能的数据存储体系。
一台oracle上可以运行多个数据库
数据库系统:DBS,对数据库进行数据管理的思路。
如:数据库中以表格的形式存储所有数据
数据库管理系统:DBMS,数据库管理的软件,即用该软件实现管理思路
如:oracle MySQL SQL Server
关系型数据库:数据库中,表格之间建立关联关系,实现表格数据之间相互制约,相互关联的效果
两个表建立关联时,分主次表,次表中关联列的值必须是主表中存在的
常用数据库软件:ACCESS ,MySQL, SQL Server,Oracle,DB2
完整性:数据的准确,正确
SQL语句:增删改查
查询:子查询,分组,联合
1.2数据字典
数据字典:
Oracle使用数据字典记录本服务器的所有配置信息。oracle中自带的资源、规定、权限、设置等信息被放到了多个表中,这些表的集合称为数据字典。
如:v$instance ,dba_ constraints
select * from v$instance; //查询当前库,实例
select * from DBA_sys_privs; ---查看oracle中所有用户权限
select * from DBA_sys_privs where GRANTEE='SNOW'; ---GRANTEE表示用户对象,用户名必须大写,查看指定用户权限
select table_name from user_tables; ----
---查看约束规则
select OWNER, CONSTRAINT_NAME, SEARCH_CONDITION, TABLE_NAME
from dba_constraints
where owner='SYS' and table_name='STUINFO';
archive log list; ---sqlplus登录后,查看本库是否在归档模式
select LOG_MODE from v$database; ---功能同上
第02讲:oracle安装
2.1oracle安装
前提:放入oracle安装程序,安装程序解压后,统一位置:/u01/app/oracle/database中
安装辅助包:
#rpm -q binutils compat-libstdc++ elfutils-libelf elfutils-libelf-devel elfutils-libelf-devel-static gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers kernel-headers ksh libaio libaio-devel libgcc libgomp libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel | grep not
#yum install
1、vi /etc/hosts
加入本机 ip 主机名
127行也加主机名
2、oracle用户创建
#groupadd -g 501 oinstall
#groupadd -g 502 dba
#useradd -u 501 -g oinstall -G dba oracle
#passwd oracle
3、vi /etc/security/limits.conf 添加以下内容
oracle soft nproc 2048
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
vi /etc/pam.d/login 添加以下内容
session required pam_limits.so
linux内核参数设置:
vi /etc/sysctl.conf 添加以下内容
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
注意:修改完参数之后,运行 sysctl -p 让这些设置生效并打印
4、Oracle用户环境变量的设置
vi .bash_profile 添加如下内容:
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=ora11gR2
export PATH=$ORACLE_HOME/bin:$PATH
umask 022
5、#mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle
mkdir -p /u01/app/oraInventory
chown -R oracle:oinstall /u01/app/oraInventory
chmod -R 775 /u01/app/oraInventory
6、 以oracle用户登录系统,进入图形界面,打开终端窗口
#unset LANG ---清空语言,oracle建议在英文状态下安装
#cd /u01/app/oracle/database
#./runInstaller
2.2登录数据库
7、检查监听器(oracle对客户端登录的监听服务)
netca ---默认安装完orcle监听器已打开
lsof -i:1521 ---查看监听端口
lsnrctl status ---查看监听器状态
lsnrctl start ---开启监听器
8、新建数据库
#dbca
实例名:SchoolManage
SID: SchoolManage
9.数据库登录、连接
$lsnrctl start ---打开监听器
sqlplus ----登录,默认登录到orcl库
echo $ORACLE_SID ---查看默认库
ORACLE_SID=SchoolManage ---指定默认库
sqlplus / as sysdba
startup
exit
sqlplus sys/密码@实例 as sysdba
exit
例: sqlplus system/123456@orcl as sysdba
----登录, 指定 用户名/密码@数据库名 as 登录身份
登录进入后 conn 命令切换登录, conn命令格式与sqlplus相同
启动用户库
$export ORACLE_SID=SchoolManage ---设定默认目标库
$sqlplus / as sysdba ---登录进入
SQL>startup ---启动数据库目标库
$export ORACLE_SID=ora11gR2
10.初级操作
SQL>SELECT * FROM ALL_TABLES; //系统里有权限的表
SQL>SELECT * FROM DBA_TABLES; //系统表
SQL>SELECT * FROM USER_TABLES; //当前用户下的表
2.3查询库表信息
select * from v$instance; // 【数据字典】查询当前库,实例
show user; //显示当用登录用户
在sqlplus下登陆,比如按用户: sqlplus aaa/password@orcl
那么,当前的用户就是aaa
如果想查看当前用户自己的表,可以通过如下命令来实现
select table_name from user_tables;
如果想查看表的信息,可以通过命令
desc user_tables
在其他的数据库中,也许就不是这样,比如在postgres中,就可以用psql登陆
用\dt来查看用户表
用\l来查看用户数据库
—行号rownum
Select * from 表名;
Select 表名.* , rownum rn from 表名;
例:
第03讲:Oracle体系结构
3.1数据存储结构
数据存储结构—磁盘使用
一:物理结构:
磁盘上存储该库所有数据及操作日志的文件,位置与数据库同名的目录中。
统一位置:/u01/app/oracle/oradata/
数据文件: .dbf 存放表中数据
日志文件: .log 存放操作日志
重做日志: redoXX.log ---记录日常操作(每个日志文件有固定大小,写满一个再写另一个,三个写满,转回写入第一个,原有日志会被覆盖,可以把被覆盖日志转存到归档日志文件中。 .arc的文件)
归档日志: .arc 当重做日志文件被写满,需被覆盖,把被覆盖的日志存入归档日志
控制文件: .ctl 记录本库的所有属性信息,如各文件的所在位置。(有备份)
参数文件: .ora 记录本库占用的各资源信息,如:内存大小,CPU大小等(有备份)
.dbf .log .ctl 文件位置:/u01/app/oracle/oradata/
.ora 文件位置:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
数据库的开启过程:
startup=>先读取参数文件,占用各个系统资源(内存、CPU)
=>按照参数文件的指示,读取控制文件,找到各物理文件的存储位置(非挂载状态)
=>载入各个物理文件(挂载状态)]
=>启动数据库,提供服务,开启状态
二:逻辑结构:
---磁盘上存储数据的原理
(1)块:
block,区别于文件系统的块(系统中默认4kB), Oracle一个block一般占据文件系统n个存储块(默认2个可更改),分三个区域,存储表中的部分数据
①块头:记录本块内存放的哪个表及其列名的数据,
②数据区域:存放表中某些行数据
③空闲区域:可用空间,可存新数据,检索时存放临时数据。
参数:
PCTFREE:块内最小空闲区百分比,如10%。以备数据缓冲,当比例达到PCTFREE值时,该块将被锁
PCTUSED 被锁块,当空闲空间达到PCTUSED所指百分比时,解锁。
(2)区
多个块联合组成区,一个区针对一张表
(3)段
多个区联合组成段,一个段针对单个用户建立的所有表
(4)表空间
多个段联合组成表空间,针对一类用户所建立的表格数据,即一个 .dbf 文件。
3.2内存体系结构
内存体系结构
一:SGA 全局共享1)共享池:对命令做审核,语法、权限、约束规则
(2)数据缓冲区:共享池审核通过的命令,在数据缓冲区执行,执行命令后,未保存到磁盘的数据称为“脏数据”,用户执行commit命令后,保存脏数据到磁盘的.dbf 文件中
(3)日志缓冲区:用户执行commit后,保存脏数据,同时激活日志缓冲区生成日志,记录到日志文件 .log 中。va池,流池,大池等主要是给java程序做复制用的
二:PGA 程序共享区:
用于供给进程运行所需的内存空间。
相关进程(DBWR,LGWR, GCN,CKPT,SMON,PMON)
DBWR --保存脏数据进程
LGWR --保存日志进程
ARCN --保存归档日志进程
CKPT --监控各进程运行状态的进程
SMON PMON --开辟,监控SGA,PGA空间的进程
(1)实例:
一个数据库所占据的一整套系统资源
实例=进程+SGA+PGA等。
一个数据库可以有多个实例
一个实例只能供一个数据库使用。
(db2实例和库的关系正好相反)
Select * from v$instance; –查询当前实例
(2)数据字典:
Oracle使用数据字典记录本服务器的所有配置信息。oracle中自带的资源、规定、权限、设置等信息被放到了多个表中,这些表的集合称为数据字典。
如:v$instance ,dba_ constraints
(3)数据库操作:
开启
startup; ---开启数据库,正常开启,等于: startup open;
startup umount; ---开启数据库,只创建实例,数据库不挂载
startup mount; ---开启数据库并挂载,但不打开库
关闭
shutdown immediate; ---立即关闭库,放弃未提交数据
shutdown;