oracle基础(整理自慕课网)

用户和表空间

登录sql plus

系统用户：sys；system（sys权限高于system）

                  sysman；scott（管理员账户）

语法：username/password @server as sysdba | sysoper（sql plus不区分大小写）

查看登陆用户:show user;

dba_users数据字典：数据库提供的表

desc users；

select username from dba_users；

scott用户：使用需要先解锁，默认密码tiger

alter user username account unlock；

在四个用户中，scott的用户权限最低。

表空间概述：表空间是数据库中开辟的一个空间用于存放对象，一个数据库可以有多个表空间。表空间由一个/多个数据文件构成。

表空间的分类：永久表空间；临时表空间；UNDO表空间

保存永久存储的对象；存放数据库操作中的执行过程，临时变量；保存数据修改前的值，类似备份

数据字典：dba_tablespaces（管理员级别）；user_tablespaces；dba_users(管理员级别);user_users

desc dba_tablespaces;

select tablespace_name from dba_tablespaces;

存在6个表空间 system系统表空间；sysyaux；UNDOTBS1存储撤销信息；TEMP存储临时信息；USERS存储用户创建的数据库对象；EXAMPLE

desc user_tablespaces;

select tablespace_name from user_tablespaces;

scott权限过低，没有数据字典dba_tablespaces。

查看用户在某一数据字典的默认表空间：

select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM';

设置用户的默认或临时表空间(只有管理员才有权限改名)：

alter user username default|temporary tablespace tablespace_name;

system用户的默认表空间为system，默认临时表空间为temp

创建表空间：

create 【temporary】tablespace tablespace_name tempfile|datafile  'xx.dbf' size xx;默认存放到oracle的安装目录下

如：create tablespace test1_tablespace datafile ' test1.dbf' size 10m;创建一个10m大小的表空间，表空间中有一个数据文件

查看表空间路径;

desc dba_data_files;

删除表空间：

drop tablespace tablespace_name  including contents;(删除表空间内容)

select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE'//默认表空间查数据字典dba_data_files临时表空间查dba_temp_files;

设置联机和脱机状态：

alter tablespace test1_tablespace offline/online 脱机/联机

查看表空间状态：

select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

设置只读或可读写的状态：

alter tablespace test1_test1_tablespace read only/read write;

在表空间中添加数据文件：

alter tablespace tablesapce_name add datafile 'xx.dbf' size xx;

删除数据文件：

alter tablespace tablespace_name drop datafile ' filename.dbf';(不能删除建表空间时自动建立的数据文件，除非删除整个表空间)

操作表

表：数据库中的基本存储单位，二维结构（行和列）记录和字段。用户创建的表存储在用户默认的表空间内，,用a用户登录可以直接select * from b这样访问,而其他用户(有权限的)要用select * from a.b这样来访问。

约定：每一列数据必须具有相同的数据类型。

          列名唯一。

          每一行数据的唯一性。

表的数据类型

字符型；数值型；日期型；其他类型

字符型：

CHAR(n) 固定长度，即使数据位数较少其占用内存不变

NCHAR(n) 固定长度，适合存放汉字，支持unicode格式的数据

VARCHAR2（n）可变字符型

NVARCHAR2（n）可变字符型，支持unicode格式的数据

数值型：

NUMBER(p，s) p指有效数字，s为精度（小数位数）

FLOAT（n）主要用于存放二进制数

日期型：

DATE 表示范围极大，可以精确到秒

TIMESTAMP 精确到小数秒

其他类型：

BLOB存放4GB二进制数

CLOB以字符串存放4GB二进制数

创建表：

create table table_name(

      col_name datatype，

);

修改表：

查询表的字段信息：

desc tab_name;

查询表的字段：

select col_name from tab_name;

向表中添加字段：

alter table table_name add col_name datatype;

更改字段的数据类型：

alter table table_name modify col_name datatype;(修改时字段中最好没有数据)

删除字段：

alter table tab_name drop column col_name ;

修改字段名：

alter table tab_name rename column col_name to new_col_name;

修改表名：

rename tab_name to new_name;

删除表：

truncate table table_name;(删除表中的所有数据，而不删除表，也不删除字段)

drop table tab_name;(删除整张表)

操作表中的数据

添加数据：

insert into tab_name (col_name1,col_name2.........) values(value1,value2.....)  (into不可省略！！)

查询数据：

select *from tab_name;

向表中添加默认值：

create table username（

       id number(6,0),

      redate date default sysdate           ( sysdate一个系统函数)

）

复制表的数据：

create table table_name as select      col_1,......|*from tab_oldname   (在创建新表时复制，复制字段和数据)

insert into tab_name [(column1,......)] select column1,...|*from tab_oldname;（通过插入的方法进行复制）

修改表中数据：

update tab_name set column1=value1,........[where conditions] (加入where只更改部分数据)

删除数据：

delete from tab_name [where conditions];

约束

约束用于对数据定义规则，排除错误和无效信息的输入，确保数据的完整性，可靠性。以下约束均可设置列级和表级。

列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用’,’分隔

非空约束（没有名字；没有表级约束；修改约束使用modify；删除约束也比较特别）：

创建表时设置非空约束：

create table tab_name(

    col_name datatype NOT NULL

);

修改表时添加非空约束;

alter table tab_name modify col_name datatype not null;

修改表时删除非空约束：

alter table tab_name modify col_name datatype null; 

主键约束：

确保行中数据的唯一性，非空且唯一。一张表只能由一个主键约束，但主键约束可以由多个字段构成，称联合主键/符合主键。

创建表时设置主键约束：

create table tab_name(

     col_name datatype primary key  (列级约束，未至定主键名，系统自动生成)

);

或：

constraint constraint_name primary key(col_name1,...);  (表级约束，前面是主键名，可以设置联合主键)

可以通过数据字典查阅主键名：desc user_constraings;

修改表时添加主键约束：

alter table tab_name add constraint con_name primary key(col_name1,.....);

更改约束名称：

rename constraint oid_name to new_name;

删除主键约束：

disable|enable constraint con_name;  (禁用/启用约束)

drop constraint con_name;  (删除约束)

或drop primary key；

外键约束：

创建表时设置外键约束：

create table tabl_name(

       .....

    col_name datatype references table2(col_name),...     (table2为主表，tab_name为从表，进行外键约束后，子表添加字段要么是父表里的值，要么为空)

);

或：

constraint constraing_name froeign key(col_name) references table_name(col_name) [on delete cascade(级联删除，父表数据被删除后，字表使用该数据所在的行也被删除)]

修改表时设置外键约束：

alter table tab_name add constraint_name foreign key(col_name) references table_name(col_name) [on delete cascade]

删除约束：

alter table tab_name disable|enable constraint constraint_name;(禁用/启用约束)

drop constraint constraint_name;

唯一约束：

创建表时设置唯一约束：

create tab_name (col_name datatype unique);

或：

constraint constraint_name unique(col_name);

修改表时添加唯一约束：

add constraint constraint_name unoque(col_name);

删除唯一约束：

disable|enable constraint constraint_name;

alter table tab_name drop constraint constraint_name;

根据数字字典查询表中的约束名称,类型，状态：

select constraint_name,constraint_type,stautus from user_constraints where table _name ='xxx';

检查约束：

限定字段的值更符合实际情况，更有实际意义。

创建表时设置检查约束：

create table tab_name(

   col_name datatype check(expression(约束的条件，表达式))

）；

或：

constraint constraing_name check(expression);

修改表时添加检查约束：

alter tale tab_name add constraing constraint_name check(exp);

删除检查约束：

disable|enable constraint constraint_name;

drop constraint constraint_name;

查询

给字段设置别名：