初识Oracle数据库

一、Oracle数据库的安装

1.登陆Oracle的官网下载https://www.oracle.com/index.html
2.登陆Oracle数据库的几种方式：
01 ：普通用户的登陆：sqlplus 用户名 密码 quit退出
也可以 sqlplus 用户名/密码
02 ：超级用户的登陆：sqlplus /nolog –>connect /as sysdba–>通过超级用户的方式链接服务器。

show user 查看当前用户
dba_users数据字典
SQL> desc dba_users
名称 是否为空? 类型

---

USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)

查看系统所有用户

select username from dba_users;

启用scott用户
启用用户的语句：alter user username account unlock;
启用scott用户的命令：

alter user scott account unlock;
connect scott/密码  连接scott用户
show user;查看当前用户

在sys，system，sysman，scott四个用户权限中，scott用户最低。

1.表空间

表空间可以理解为在数据库中开辟的一片空间用来存放数据库的对象。一个数据库可以有多个表空间组成
表空间与数据文件之间的关系：
表空间是由多个数据文件构成的。数据文件的内容和大小可以由用户自己来定义。
表空间的分类：

• 永久表空间：永久化存储的对象。如：表、视图、存储过程等。
• 临时表空间：存放数据库操作过程中中间执行的一些过程。执行结束之后存储的内容就会被自动的释放。
• UNDO表空间：保存事物被修改之前的旧地址。用于事物的回滚操作。

查看用户的表空间
dba_tablespaces、user_tablespaces数据字典；
desc dba_tablespaces;

SQL> select tablespace_name from dba_tablespaces;
查看到的在dba数据字典中表空间的名字。

TABLESPACE_NAME

SYSTEM----系统表空间
SYSAUX----实例表空间
UNDOTBS1--存储一些撤销信息的表空间
TEMP---存储sql语句处理的表和索引信息的。
USERS---存储用户创建的表空间。
EXAMPLE---

已选择6行。

数据字典
dba_users：系统管理级可以查看的。
user_users：普通用户可以查看的。

SQL> select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM';//查看用户为SYSTEM的默认表空间和临时表空间。

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM                         TEMP

设置用户默认或者临时表空间：

alter user username default |temporary tablespace tablespace_name;

创建表空间

CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'xx.dbf' SIZE xx

SQL> create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;
//创建了一个大小为10兆文件名为test1file.dbf表空间名为test1_tablespace的永久表空间。
表空间已创建。

创建一个临时表空间

SQL> create temporary tablespace temp_test1_tablespace tempfile 'temp_test1file.dbf' size 10m;

表空间已创建。

查看创建的表空间
SQL> desc dba_data_files;
名称 是否为空? 类型

---

FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
通过如下的命令可以看到文件的存放位置。

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME

D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF

修改表空间

• 修改表空间的状态：设置联机或者脱机的状态，创建表空间之后默认的状态是联机的状态。表空间设置成脱机状态后就不能够使用了。

ALTER TABLESAPCE tablespace_name  ONLINE|OFFLINE；

查看表空间的状态：

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS

ONLINE

设置表空间只读或者可读写的状态:默认情况为可读写

ALTER TABLESAPCE tablesapce_name READ ONLY|READ WRITE;

修改表空间状态为只读，注意：只有在联机状态下才可以更改。

SQL> alter tablespace test1_tablespace read only;

表空间已更改。

SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

STATUS

READ ONLY

• 修改数据文件：
  1.向表空间增加数据文件：

ALTER TABLESAPCE tablespace_name ADD DATAFILE 'xx.dbf' SIZE xx;

向表空间增加数据文件的实例

SQL> alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10m;

表空间已更改。

SQL> desc dba_data_files;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME

D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST2_FILE.DBF

2.向表空间删除数据文件

 ALTER TABLESAPCE tablespace_name DROP DATAFILE 'xx.dbf' ;

删除的实例

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST2_FILE.DBF

SQL> ALTER TABLESPACE TEST1_TABLESPACE DROP DATAFILE 'TEST2_FILE.DBF';

表空间已更改。

SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

FILE_NAME

D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF

SQL>

删除表空间

DROP TABLESAPCE tablespace_name[INCLUDING CONTENTS]

不加后面including contents是只删除表空间，而不删除数据文件，加上以后是删除表空间和里面的数据文件。
实例：

SQL> drop tablespace test1_tablespace including contents;

表空间已删除。

SQL>

操作表

• 认识表：表都是存在表空间之中的，是存储数据的基本存储单位，表都是一个二维结构行和列。
  有一些约定：
  
  1. 每一列数据必须拥有相同的数据类型
  2. 列名唯一
  3. 每一行数据的唯一性。

• 数据类型：
  1.字符型：char(n)最大值2000 nchar(n)最大值1000–>n表示大小，不可变长度
  varchar2(n) —nvarchar2(n)可变长度的类型
  nvarchar2和varchar2都可以表示可变长度的字符，其中nvarchar2用于存储Unicode格式的数据，更适合存储中文数据。
  2.数值型：NUMBER(p,s)–p:有效数字。s:保留的小数位
  FLOAT(N)–
  3.日期型:TATE和TIMESTAMP更加精确
  4.其他类型：BLOB:二进制形式存放4GB
  CLOB：字符串的形式存放4GB

• 管理表:
  1.创建表：同一个用户下表名要唯一。
  基本语法

 CREATE TABLE table_name（
 column_name datatype,...）

创建用户信息表：

SQL> create table userinfo
  2  ( id number(6,0),
  3  username varchar2(20),
  4  password varchar2(20),
  5  regtime date);

表已创建。

SQL> desc userinfo;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(6)
 USERNAME                                           VARCHAR2(20)
 PASSWORD                                           VARCHAR2(20)
 REGTIME                                            DATE

SQL>

2.删除表
TRUNCATE TABLE table_name;//表被截断，表里的数据被删除但是表仍然存在。
DROP TABLE table_name;//删除表。
3.修改表

• 添加字段：

 ALTER TABLE table_name ADD column_name datatype;

实例

SQL>  alter table userinfo add remarks varchar2(500);

表已更改。

SQL> desc userinfo;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(6)
 USERNAME                                           VARCHAR2(20)
 PASSWORD                                           VARCHAR2(20)
 REGTIME                                            DATE
 REMARKS                                            VARCHAR2(500)

• 更改字段数据类型

 ALTER TABLE table_name MODIFY column_name datatype;

• 删除字段：

ALTER TABLE table-name DROP COLUMN column_name;

• 修改字段名

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

• 修改表名

 RENAME table_name TO new_table_name;

操作表中的数据

insert语句

INSERT INTO table_name (column1,column2,...) values(values1,values2,...);

• 向表中的所有字段添加值：

SQL> insert into userinfo values(1,'xxx','123',sysdate,'test');

已创建 1 行。

SQL> select * from userinfo;//查询表中数据

• 向表中指定字段添加值：

SQL> insert into userinfo(id ,username,regtime) values(2,'yyy',sysdate);

已创建 1 行。

SQL> select * from userinfo;

• 向表中添加默认数据

 SQL> create table userinfo1
  2  (id number(6,0),regdate date default sysdate);
//创建表，并且给regdate添加默认值
表已创建。

SQL> insert into userinfo1 values(1);
insert into userinfo1 values(1)//还是需要指定字段添加值
            *
第 1 行出现错误:
ORA-00947: 没有足够的值


SQL> insert into userinfo1(id) values(1);

已创建 1 行。

SQL> select * from userinfo1;//查询表的所有内容

        ID REGDATE
---------- --------------
         1 12-8月 -17

SQL>

SQL> alter table userinfo modify password default '0000';
//修改表中的数据为默认值0000，以后添加的时候如果不给password添加值则密码为0000，如果插入数据的时候指定密码的值则为指定值。
表已更改。

向表中复制数据

在建表时复制

CREATE TABLE table_new 
AS
SELECT column1,...| * from table_old;

SQL> create table userinfo_new as select * from userinfo;
//在创建userinfo_new的时候把userinfo中的所有字段和值都复制过来了。
表已创建。

SQL> create table userinfo_new1
  2  as
  3  select id,username from userinfo;
//只复制部分字段和值
表已创建。

SQL> select *from userinfo_new1;

添加时复制

INSERT INTO table_new [(column1,...)]
SELECT (column1,...)|* FROM table_old;

修改表中的数据

• UPDATE语句

 UPDATE table_name 
 SELECT column1=value1,...
 [WHERE conditions];

• 无条件更新

 SQL> update userinfo
  2  set password='111111';
//更新所有数据的密码为6个1
已更新3行。

SQL> select password from userinfo;

PASSWORD

111111
111111
111111

• 有条件的更新

SQL> update userinfo
  2  set password='000'
  3  where username='xxx';
//修改username为xxx的密码为000，其他的不更改。
已更新 1 行。


SQL> select username,password from userinfo;

USERNAME             PASSWORD
-------------------- --------------------
xxx                  000
yyy                  111111
                     111111

删除表中的数据

DELECT FROM table_name
[WHERE conditions];

约束

约束的作用–定义规则，确保数据的完整性（精确性）

1.非空约束

在创建表的时候为字段设置非空约束：

CREATE TABLE table_name(column_name datatype NOT NULL,...);

在修改表的时候创建非空约束：

ALTER TABLE table_name MODIFY colunm_name datatype NOT NULL;

在修改表的除去非空约束：

ALTER TABLE table_name MODIFY colunm_name datatype NULL;

2.主键约束

• 在创建表的时候设置主键约束：

 CREATE TABLE table_name (column_name datatype PRIMARY KEY,...)

在创建表之后设置主键约束：

CONSTRAINT constraint_name PRIMARY KEY (column_name1,...)

可以设置联合主键：

SQL> create table userinfo_p
  2  (id number(6,0),
  3  username varchar2(20),
  4  password varchar2(20),
  5  constraint pk_id_username primary key(id,username));

表已创建。

SQL> desc userinfo_p
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(6)
 USERNAME                                  NOT NULL VARCHAR2(20)
 PASSWORD                                           VARCHAR2(20)

在数据字典user_constraints中查找约束

SQL> select constraint_name from user_constraints where table_name='USERINFO_P';

CONSTRAINT_NAME
------------------------------
PK_ID_USERNAME

在修改表的时候添加主键约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name1,...);

更改约束的名称：

ALTER TABLE table_name
RENAME CONSTRAINT ald_name TO new_name;

删除主键约束

ALTER TABLE table_name
DISABLE|ENABLE CONSTRAINT constraint_name
//禁用约束

ALTER TABLE table_name
DROP CONSTRAINT constraint_name
//删除约束

ALTER TABLE table_name
DROP PRIMARY KEY[CASCADE]
//删除主键约束

在创建表时添加外键约束

create table table1（从表）
(column datatype references table2（主表）(column_name(必须为主键)...);

在创建表时设置外键约束2（表级）

语法：CREATE TABLE table_name(
column_name datatype,...,
CONSTRAINT constraint_name FOREIGN KEY(column_name)REFERENCES table_name2(column_name)[ON DELETE CASCADE]);

修改表时添加外键约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(column_name)
REFERENCES table_name(column_name)[ON DELETE CASCADE];

删除外键约束

disable|enable constraint constraintname
或delete constraint constraintname

唯一约束主键约束的区别：
主键字段值必须是非空的，唯一约束允许有一个空值
主键只能有一个，唯一约束可以有多个
1、在创建表时设置唯一约束（列级）

语法：CREATE TABLE table_name(column_name datatype UNIQUE,...);

2、在创建表时设置唯一约束（表级）

语法：CREATE TABLE table_name(
column_name datatype,...,
CONSTRAINT u_name
UNIQUE(column_name));

注：（1）唯一约束的名称（u_name）必须唯一；
（2）如果想设置多个唯一约束字段，需要将子句（CONSTRAINT u_name UNIQUE(column_name)）重复书写即可。
在表集设置唯一约束（用户名约束）：

create table userinfo_u1
(id varchar2(10) primary key,
username varchar2(20),
constraint un_username uinque(username));

在修改表时添加:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_name);