Oracle数据库基本知识与SQL操作（1）

最新推荐文章于 2024-08-23 07:44:33 发布

小宿长谈

最新推荐文章于 2024-08-23 07:44:33 发布

阅读量1.3k

点赞数

分类专栏： Oracle数据库文章标签：后端 dba 数据库开发

本文链接：https://blog.csdn.net/s18764696599/article/details/121487602

版权

Oracle数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

登录数据库方式：

超级管理员：system +密码

Conn sys/pwd as sysdba

windows打开sql plus 命令：sqlplus /nolog

一.查看数据库当前的状态、用户、数据库：

select status from V$INSTANCE;

show user；

show parameter service;

查看数据库是何种状态，oracle必须在打开状态下才能进行操作。

二、启动数据库命令 startup。

数据库四种启动模式：

1.nomount（只创建实例不加载数据库且不会打开数据文件）

例如：startup nomount；

2.mount（为实例加载数据库但保持数据库为关闭状态）

例如：startup mount

3.open（正常启动数据库模式完成数据库实例的装载启动以及打开数据库）

例如：startup [open]

4.force（强制启动数据库模式）

例如：startup force

三、关闭数据库命令 shutdown

数据库四种关闭模式：

1.normal ：断开所有连接，阻止新连接

例如 shutdown normal；

2. immediate；：强制终止回退事务，并关闭数据库

例如：shutdown immediate；

3.transactional：组织新连接，等待事务完成后断开连接并关闭数据库

例如：shutdown transactional

4.abort：终止，强制关闭数据库，会丢失当前操作。

例如：shutdown sbort

四、修改数据库命令:alter database +<状态或命令>；

修改表空间命令:

重命名：alter tablespace a rename to new_a;
对表空间的属性进行修改（数据文件大小，扩展，最大空间）：

alter tablespace new_a

add datafile 'H:test2.dbf'

size 5m

reuse autoextend on next 1m maxsize 10m;

五、表空间：

每个Oracle数据库都是由若干个表空间构成的，用户在数据库中建立的所有内容都被存储到表空间中。一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个表空间。与数据文件这种物理结构相比，表空间属于数据库的逻辑结构。

　　在每个数据库中，都会有一个名为SYSTEM的表空间，即系统表空间。还会有SYSAUX、TEMP、UNDO、USERS等表空间，这些都是在创建数据库时自动创建的。管理员可以创建自定义的表空间并分配给指定用户，也可以为表空间增加或删除数据文件。

1.创建表空间并指定数据文件属性（需要system权限）：

create tablespace tablespace_name

datafile file_clause ；

file_clause形式如下：‘路径’文件大小

例如：datafile ’D：filename’ size 10M；

Eg：创建大小为50m的表空间test，包含一个数据文件，禁止自动扩展数据文件

Create tablespace test

Logging

Datafile ‘H:test1.DBF’

Size 50M

Reuse autoextend off;

Eg:创建临时表空间

create temporary tablespace b

tempfile 'H:test1.dbf'

size 10m

extent management local;

六、表空间分类：

用户表空间user
临时表空间temp
回滚、撤销表空间undo
系统表空间system
辅助系统表空间sysaux

七、查看表空间：

1.查看表空间所有属性：

select * from Dba_Tablespaces;

2.查看表空间名：

select tablespace_name from Dba_Tablespaces;

3.查看特定表空间名：

select tablespace_name from Dba_Tablespaces where tablespace_name='B';

八、删除表空间：

drop tablespace b including contents and datafiles;

九、创建用户步骤：创建用户授予权限

1. 用户创建并指定保存的表空间

Create user 用户名 identified by 密码

(default tablespace users

temporary tablespace temp

quota 3M on users)可省略;

2.用户

1. 查看所有用户：

Select username form all_users;

2.查看数据库dba权限用户：

Select username form dba_users;

3.精确查看某个用户(用户名大写)：

Select username from dba_users where username='SU3'

4.修改用户密码：

alter user 用户 identified by 新密码;

5.删除用户

drop user 用户名（cascade级联）；

3.权限

1.授予权限

grant create session to 用户名；只赋予用户登陆权限

grant resource to 用户名；赋予该用户建表没有空间的限制

grant dba to 用户名；赋予dba(相当于所有权限)的权限给用户

2.收回权限

Revoke 权限（on 对象名）from 用户名；

十、数据表的创建

Oracle支持多种类型：

1.数据类型

1.数值数据类型：number

Number(p[ , s ])取值范围为1~30，

意义为该数值共有p位，小数点后有s位。超过位数限制后按照四舍五入舍弃多余位数。如果为整数则不需要逗号和s。

2.日期/时间类型：date、timestamp

查询当前时间：Select sysdate from DUAL;

修改时间格式：Alter session set nls_date_format=’yyyy-mm-dd’;

插入日期信息时应该把格式相匹配，否则无法插入。

3.字符串类型：char(n)、varchar2(n)、

nvarchar2（n）:Unicode可变字符型，保存中文常用、

long : 储存变长的字符串

2.表操作

1.创建表语句（表名开头首字符应为字母且一个用户下表名不重复）

create table 表名（属性数据类型）

eg：create table a

(

id number(20)primary key,

name varchar(10) not null ,

sex char(4)check(sex in('男','女'))

);

2.查看表的字段信息

describe 表名；

eg：describe student；

3.修改表结构：

修改表中数据应add，modify等关键字，修改表时使用alter，drop等关键字。

Alter table 表名 +属性

{

Rename +（关键字column）/旧字段名 to()：修改表/字段名

Add()：向表中增加字段信息

Drop +column/() ：删除字段或约束

Modify() ：修改表的某一列的属性

}

删除表

eg: drop table 表名 cascade constraints;

十一、完整性约束

1、域完整性

非空：not null

2、实体完整性（行完整性）

唯一性约束unique，

主键约束primary key，

检查约束check（）

Eg：

create table d(

id number not null,

name char(20) unique,

sex char(2) check (sex in('男','女')) );

3、参照完整性（引用完整性）

存在主外键要保证主外键之间的参照数据一致性

外键：foreign key

EG:

create table a(

name Varchar2(10) primary key,

age number(5)

)；

create table b(

name Varchar2(10) foreign key,

sex char(2)

)；

4、默认值约束：

1.在表中创建默认值

属性类型 default ’默认值‘；

Eg：sex char（2）default ‘男‘；

2.修改默认值

Eg：alter table 表名

Sex char（2） default ‘女’；

3.删除默认值

Eg：alter table 表名

Sex char(2) default null;

5、查看约束

Select constraint_name, constraint_type,table_name,last_change

From user_ constraints

Where table_name=’TMP_PK10’;

1.自增约束：

字段名数据类型 generated by default as identity ；

一个表只能有一个字段使用自增约束，且该字段必须为主键的一部分

Eg：定义数据表tmp_id，指定学生编号自动递增。SQL语句如下：

CREATE TABLE tmp_id(

id NUMBER(11) GENERATED BY DEFAULT AS IDENTITY,

name VARCHAR(25),

sex CHAR(4),

class VARCHAR(50)

);

上述语句执行后，创建数据表tmp_id，其id列字段的值在进行数据添加时不需要用户提供数据，由系统维护。Id列初始值从1开始，每添加一条新记录，该值自动加1。

十三、索引

索引在Oracle数据库中是占据储存空间的，在其他数据库中不占。在创建主键约束时数据库会自动生成一个与约束名字相同的索引。

1、创建索引

Eg：create unique index unique_ind_f_属性 on 表名(属性);

十四、数据操作

新建的数据库表是空表，提供了存储和操作数据的结构。针对表中数据的操作主要包括插入、更新、删除、查询等。

其中插入（INSERT）、更新（UPDATE）和删除（DELETE）操作会使数据库中的数据发生变化，因此这三种操作在SQL语言中被称为数据操作语言（DML）

1、插入数据 insert：

常用的插入方式有：插入完整的记录、插入记录的一部分、插入多条记录以及插入另一个查询的结果。另外，还可以为数据表批量导入数据。

1.插入命令INSERT语句的语法格式如下：

NSERT INTO <表名>[(<列名1>,<列名2>,...n)]

VALUES(<列值1>,<列值2>,...n)；

该语句的功能是向指定表中插入一行记录。列名列表“(<列名1>,<列名2>,...n)”提供了插入数据对应的列，VALUES后的列值列表“(<列值1>,<列值2>,...n)”提供了要插入到表中的数据值。

说明：

（1）插入数据时，列名列表可以不与数据表的结构一致。只要保证列值列表中的数据与列名列表中的列名、数据类型一一对应即可。列名列表可以不包含数据表中的所有列，但是缺失的列必须是可以为空或有默认值约束的列。当没有为这些列提供数值时，系统会自动为其填充空值或默认值。

（2）列名列表可以省略。如果不指定表名后面的列名列表，则VALUES子句中要给出每一列的值，并且其提供的值要与原表中字段的顺序和数据类型完全一致，而且不能缺少字段。

（3）VALUES中描述的值可以是一个常量、变量或一个表达式。字符串类型的数值必须用单引号引起来。字符串转换函数TO_DATE可以把字符串形式的日期型数据转换成Oracle规定的合法的日期型数据

2. 插入完整的记录

向表中插入完整的记录，指在插入数据时为每个列都指定数据值。可以有两种方式：一种是指定所有字段名，另一种是不指定字段名。

Eg：

向student表中插入新记录，指定所有列值。

SQL语句如下：

INSERT INTO student(Snum,Sname,Ssex,Sbirth,Sdept,Snote)

VALUES('1506101','王玫','女','02-1月-1997','计算机系','入学新生');

INSERT INTO student(Snum,Sname,Sbirth,Ssex,Snote,Sdept)

VALUES('1506102','李东','13-3月-1998','男','入学新生','计算机系');

INSERT INTO student

VALUES('1506103','孙翔','男',TO_DATE('19971120','YYYYMMDD'),'计算机系','入学新生');

本例中，前两条语句给出了完整的列名列表和值列表。第一条语句给出的列名列表和表结构一致。第二条语句给出的列名列表和表的结构不一致。要保证列名列表和列值列表中的数据一一对应。第三条语句省略了列名列表。这时候要保证列值列表与表结构完全一致。如果表结构修改了，如对列进行增加、删除或者位置改变的操作，在进行插入时数值列表的顺序也要相应修改。如果指定列名列表，则不会受到表结构的影响。

日期型数据在输入时需要注意数据库当前的默认格式，需要输入正确的日期格式才能正确插入数据。也可以使用字符串转换函数TO_DATE()来将字符串表示的数据按照对应的格式转换成日期型数据。

需要注意，使用命令方式对表数据进行插入、更新和删除后，还需要使用COMMIT命令进行提交，这样才会把数据的改变真正保存到数据库中。为方便介绍，本书后面的SQL语句均省略COMMIT命令，运行时请自行添加。

3.插入多条记录

使用多个INSERT语句可以向数据表中插入多条记录。

Eg：向student表中插入2条记录。SQL语句如下：

BEGIN

INSERT INTO student(Snum,Sname,Ssex)

VALUES('1506105','马丁','男');

INSERT INTO student(Snum,Sname,Ssex)

VALUES('1506106','郭阳','女');

END;

记得提交！！！！

Commit；

Eg：

在一条INSERT语句中插入2条新记录。SQL语句如下：

INSERT INTO student(Snum,Sname,Ssex)

SELECT '1506108','刘飞飞','女' FROM DUAL

UNION ALL

SELECT '1506109','王子辰','男' FROM DUAL;

系统提示：

2 行已插入

注意：

一个同时插入多行记录的INSERT语句可以等同于多个单行插入的INSERT语句。但是多行的INSERT语句在处理过程中，效率更高。因为Oracle执行单条INSERT语句插入多行数据比使用多个单行INSERT语句速度快。所以，在插入多条记录时，最好选择使用单条INSERT语句的方式插入。

4.运行begin-end时，要看到插入成功时要先打开输出模式（默认为off）：

语句：Set serveroutput on；

5.插入另一个查询的结果

INSERT语句可以将其他表中已存在的数据以SELECT查询结果的形式插入到表中，从而快速地从一个或多个表中向另一个表中插入多行。

其基本语法格式如下：

INSERT INTO 表名1(列名1,列名2,...n)

SELECT 数值1,数值2,...n

FROM 表2

WHERE 查询条件；

其中，SELECT语句后的“数值1,数值2,...n”需要与“(列名1,列名2,...n)”的列的数据类型一一对应。

Eg：

创建一个新表new_student，将student中的部分数据插入到新表中。插入语句如下：

INSERT INTO new_student(num,name,sex)

SELECT Snum,Sname,Ssex

FROM student;

2、更新 update

对于表中已有的数据进行修改称为更新操作，采用UPDATE命令进行。

其语法结构如下：

UPDATE 表名

SET 字段名1=数值1,字段名2=数值2,...n

WHERE 条件表达式；

其中，“字段名1=数值1,字段名2=数值2,...n”表示为指定的字段赋予新的数值。更新多个列时，“列=值”之间要有逗号隔开。最后一列不需要逗号。WHERE条件表达式代表更新记录需要满足的条件，即只有符合条件的数据才会被修改。保证UPDATE语句以WHERE子句结束。如果忽略了WHERE子句，Oracle将更新所有的行。

Eg:

在student表中，将“刘飞飞”同学的系别改为“计算机系”，备注给为“入学新生”。SQL语句如下：

UPDATE student

SET Sdept='计算机系',Snote='入学新生'

WHERE Sname='刘飞飞';

系统提示：1行已更新。

如果有多行符合WHERE条件的数据，这些数据都将被修改。