数据库基础之认识 sql 并学习数据库的基础操作
学习环境
目标数据库 Oracle 12c
学习记录
一、什么是关系型和非关系型数据库，两者都包含哪些种类的数据库
1. 关系数据库：是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单说来就是关系型数据库用了选择、投影、连接、并、交、差、除、增删查改等数学方法来实现对数据的存储和查询。可以用SQL语句方便的在一个表及其多个表之间做非常复杂的数据查询。安全性高。
关系数据库包含Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access、MySQL、浪潮K-DB 等
2. 非关系型数据库：简称NOSQL，是基于键值对的对应关系，并且不需要经过SQL层的解析，所以性能非常高。但是不适合用在多表联合查询和一些较复杂的查询中。NoSQL用于超大规模数据的存储。
非关系型数据库包含NoSql、Cloudant、MongoDB、redis、HBase
两种数据库之间的区别：
对于关系型数据库
关系型数据库的特性
1、关系型数据库，是指采用了关系模型来组织数据的数据库；
2、关系型数据库的最大特点就是事务的一致性；
3、简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
关系型数据库的优点
1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；
3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
4、支持SQL，可用于复杂的查询。
关系型数据库的缺点
1、为了维护一致性所付出的巨大代价就是其读写性能比较差；
2、固定的表结构；
3、高并发读写需求；
4、海量数据的高效率读写；
---------------------------------------------------------------------
对于非关系型数据库
非关系型数据库的特性
1、使用键值对存储数据；
2、分布式；
3、一般不支持ACID特性；
4、非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。
非关系型数据库的优点
1、无需经过sql层的解析，读写性能很高；
2、基于键值对，数据没有耦合性，容易扩展；
3、存储数据的格式：nosql的存储格式是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，而关系型数据库则只支持基础类型。
非关系型数据库的缺点
1、不提供sql支持，学习和使用成本较高；
2、无事务处理，附加功能bi和报表等支持也不好；
二、学习数据库中的字段类型并创建库和用户表，需要包含所有字段类型
1、创建表空间：
格式：
create tablespace 表间名 datafile '数据文件名' size 表空间大小
具有命令如下：
mkdir /data/oracle/
#创建数据库物理文件位置 之前有/data 这个目录 不需要mkdir /data
mkdir /data/oracle/test #创建数据库物理文件位置
chown -R oracle:dba /data/oracle/ #更改/data/oracle/ 目录所有者
sqlplus "/as sysdba" #以sysdba用户登录
注:
sys --系统管理员，拥有最高权限
system --本地管理员，次高权限
scott --普通用户，密码默认为tiger,默认未解锁
sys --系统管理员，拥有最高权限
system --本地管理员，次高权限
scott --普通用户，密码默认为tiger,默认未解锁
sqlplus / as sysdba; --登陆sys帐户
sqlplus sys as sysdba; -- 登陆sys帐户
sqlplus scott/tiger; --登陆普通用户scott
sqlplus / as sysdba; --登陆sys帐户
sqlplus sys as sysdba; -- 登陆sys帐户
sqlplus scott/tiger; --登陆普通用户scott
create tablespace zuoer datafile '/data/oracle/test/zuoer.dbf' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
注:
AUTOEXTEND ON EXTENT设置表空间自动扩展
MANAGEMENT LOCAL本地管理表空间
SEGMENT SPACE MANAGEMENT AUTO 自动段空间管理
2、创建用户
create user 用户名 identified by 密码 default tablespace 表空间表;
例如：create user u_zuoer identified by 123456 default tablespace zuoer;
3、授权给新用户
注:
1) 系统权限分类：
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。
2) 系统权限传递：
增加WITH ADMIN OPTION选项，则得到的权限可以传递。
grant connect,resorce to u_zuoer with admin option; --可以传递所获权限。
3) 系统权限回收：
系统权限只能由DBA用户回收
命令：SQL> Revoke connect,resource from u_zuoer;
4) 查看所用用户对表的权限
select * from user_sys_privs;--查看当前用户所有权限
select * from user_tab_privs;--查看所用用户对表的权限
select * from user_sys_privs;--查看当前用户所有权限
select * from user_tab_privs;--查看所用用户对表的权限
5)角色
角色即权限的集合，可以把一个角色授予给用户
create role myrole; --创建角色
grant create session to myrole; --将创建session的权限授予myrole
grant myrole to zhangsan; --授予zhangsan用户myrole的角色
drop role myrole; --删除角色
--但是有些权限是不能授予给角色的，比如unlimited tablespace和any关键字
6)、赋予表操作权限
grant select,update,insert,delete on TABLENAME to USER;
例如：grant select,update,insert,delete on t_zuoer to u_zuoer;
开始操作
1) 把 connect,resourc权限授予u_zuoer用户
grant connect,resource to u_zuoer;
或者
grant dba to study;
#表示把 dba权限授予给 u_zuoer
2) 给u_zuoer空间分配权限
alter user u_zuoer quota 50M on zuoer;
4、创建数据表
conn u_zuoer/123456@localhost/orcl:1521; #切换用户
create table t_zuoer(
UUID VARCHAR2(99) primary key,
Z_CHAR CHAR(20) not null,
Z_NCHAR NCHAR(20) default '1' check(Z_NCHAR in('1','2')),
Z_NVARCHAR2 NVARCHAR2(20) not null,
Z_LONG LONG not null,
Z_NUMBER NUMBER(20) not null,
Z_DATE DATE not null,
Z_RAW RAW(20) not null,
Z_LONG_RAW RAW(200) not null,
Z_BLOB BLOB not null,
Z_CLOB CLOB not null,
Z_NCLOB NCLOB not null,
Z_BFILE BFILE not null
);
#这个表创建很艰难，
我一直以为是括号的事，半角和全角的问题，我还关机了，我都以为我CPU坏了呢，后来无意间发现多了一个逗号。
5.创建一个学生表实现增删改查，扩展回滚。
1.创建学生表
create table student(
xh number(4),
xm varchar(20),
sex char(2),
birthday date,
sal number(7,2)
);
2.对表结构的扩展
alter table student add (classid number(2)); --添加一个字段
alter table student modify(xm varchar(30)); --修改字段的类型、长度或者是名字（不能有数据）
alter table student drop column sal; --删除字段
rename student to stu; --修改表名字
create table student as select * from stu; --复制表结构及其数据
create table s as select * from stu where 1=2;--复制表结构不复制数据
drop table stu; --删除表
3.对数据进行操作
insert into student values(1,'zuoer','男',to_date('1888-08-08','yyyy-mm-dd'),2);
--添加数据
update student set sex='女' where xh=1; --修改数据
savepoint aa; --创建保存点
delete from student where xh=1; --删除数据
rollback to aa; --回滚
select * from student;
SQL> delete from student where xh=1;
1 row deleted.
SQL> select * from student;
no rows selected
SQL> rollback to aa;
Rollback complete.
SQL> select * from student;
XH XM SEX BIRTHDAY CLASSID
---------- ------------------------------ --------- --------- ----------
1 zuoer 女 08-AUG-88 2
三、读写文件以及系统库和表
1.读写文件
1) 创建目录
create or replace directory TESTFILE as '/data/oracle/test';
2) 给读写权限
grant read,write on directory TESTFILE to u_zuoer;
3) 写文件
conn u_zuoer/123456@localhost/orcl:1521; #切换用户
DECLARE
filehandle utl_file.file_type; --句柄
begin
filehandle := utl_file.fopen('TESTFILE','hello.txt','w'); --打开文件
utl_file.put_line(filehandle,'Hello Oracle!');--写入一行记录
utl_file.put_line(filehandle,'Hello World!');
utl_file.put_line(filehandle,'zuoer');
utl_file.fclose(filehandle);--关闭句柄
end;
/
4) 读文件
set serveroutput on; --打开输出功能
declare
filehandle utl_file.file_type;
fp_buffer varchar2(4000);
begin
filehandle:= utl_file.fopen ('TESTFILE','hello.txt', 'R');
utl_file.get_line (filehandle, fp_buffer );
dbms_output.put_line(fp_buffer );
utl_file.get_line (filehandle, fp_buffer );
dbms_output.put_line(fp_buffer );
utl_file.fclose(filehandle);
end;
/
2.系统表
1）常用查询
select username,password from dba_users; --查看用户名和密码
select * from role_sys_privs; --查看当前用户的权限
select * from v$pwfile_users; --查看哪些用户有sysdba权限
select * from all_tables; --获取所有用户及创建的表
select * from user_tables; --获取用户的表对象
select * from dba_sys_privs; --用户的系统权限信息
select * from dba_tab_privs --用户具有的对象权限信息
select * from dba_col_privs --用户具有的列对象权限信息
select * from user_sys_privs --当前用户的系统权限信息
select * from user_tab_privs --当前用户的对象权限信息
select * from user_col_privs --当前用户的表列权限信息
select * from user_role_privs --当前用户的角色权限信息
select * from session_privs --会话的权限信息
select * from v$instance --数据库实例信息
select * from v$database --数据库信息
select * from v$datafile --数据文件信息
select * from v$controlfile --控制文件信息
2）dba
dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
dba_data_files 数据文件设置信息
dba_temp_files 临时数据文件信息
dba_rollback_segs回滚段信息
dba_ts_quotas 用户表空间配额信息
dba_free_space 数据库空闲空间信息
dba_profiles 数据库用户资源限制信息
dba_sys_privs 用户的系统权限信息
dba_tab_privs 用户具有的对象权限信息
dba_col_privs 用户具有的列对象权限信息
dba_role_privs 用户具有的角色信息
dba_audit_trail 审计跟踪记录信息
dba_stmt_audit_opts 审计设置信息
dba_audit_object 对象审计结果信息
dba_audit_session 会话审计结果信息
dba_indexes 用户模式的索引信息
3）user
user_objects 用户对象信息
user_source 数据库用户的所有资源对象信息
user_segments 用户的表段信息
user_tables 用户的表对象信息
user_tab_columns 用户的表列信息
user_constraints 用户的对象约束信息
user_sys_privs 当前用户的系统权限信息
user_tab_privs 当前用户的对象权限信息
user_col_privs 当前用户的表列权限信息
user_role_privs 当前用户的角色权限信息
user_indexes 用户的索引信息
user_ind_columns 用户的索引对应的表列信息
user_cons_columns 用户的约束对应的表列信息
user_clusters 用户的所有簇信息
user_clu_columns 用户的簇所包含的内容信息
user_cluster_hash_expressions 散列簇的信息
4）v$
v$database 数据库信息
v$datafile 数据文件信息
v$controlfile 控制文件信息
v$logfile 重做日志信息
v$instance 数据库实例信息
v$log 日志组信息
v$loghist 日志历史信息
v$sga 数据库SGA信息
v$parameter 初始化参数信息
v$process 数据库服务器进程信息
v$bgprocess 数据库后台进程信息
v$controlfile_record_section 控制文件记载的各部分信息
v$thread 线程信息
v$datafile_header 数据文件头所记载的信息
v$archived_log 归档日志信息
v$archive_dest 归档日志的设置信息
v$logmnr_contents 归档日志分析的DML DDL结果信息
v$logmnr_dictionary 日志分析的字典文件信息
v$logmnr_logs 日志分析的日志列表信息
v$tablespace 表空间信息
v$tempfile 临时文件信息
v$filestat 数据文件的I/O统计信息
v$undostat Undo数据信息
v$rollname 在线回滚段信息
v$session 会话信息
v$transaction 事务信息
v$rollstat 回滚段统计信息
v$pwfile_users 特权用户信息
v$sqlarea 当前查询过的sql语句访问过的资源及相关的信息
v$sql与v$sqlarea 基本相同的相关信息
v$sysstat 数据库系统状态信息
5）其他
all_users 数据库所有用户的信息
all_objects 数据库所有的对象的信息
all_def_audit_opts 所有默认的审计设置信息
all_tables 所有的用户及创建的表
all_indexes 所有的数据库对象索引的信息
session_roles 会话的角色信息
session_privs 会话的权限信息
index_stats 索引的设置和存储信息
dual 系统伪列表信息
管理员已关闭本篇文章评论！