oracle表空间管理、各种约束、表操作、事务、基本查询(模糊查询)、过滤运算及排序

一、表空间管理

 

1、查看用户的表空间

数据字典-----指数据库提供的用于查询信息的表

a、dba_tablespaces //数据库管理员级别表空间信息

b、user_tablespaces //普通用户级别表空间信息

例：

select tablespace_name from dba_tablespaces;

select tablespace_name from user_tablespaces;

注：dba_users 查看所有用户信息

user_users 查看当前连接用户的信息

例：查看system用户的表空间信息

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

2、设置用户默认或临时表空间

alter(更改) user（用户） username（用户名） default|temporary tablespace tablespace_name(表空间名);

例：alter user user01

default tablespace test1_tablespace

temporary tablespace temptest1_tablespace;

3、修改表空间的状态（设置联机或脱机状态）

alter tablespace tablespace_name online|offline;

特别说明：若一个表空间设置成脱机状态，表示该表空间暂时不让访问，设置成脱机状态不是删除，当需要时可改为联机状态，正常使用。

select status from dba_tablespaces where tablespace_name

='TEST1_TABLESPACE';//查询状态

4、设置只读或可读写状态

alter tablespace tablespace_name (read only)|(read write)

//默认是可读写状态

5、增加数据文件

alter tablespace tablespace_name add datafile(数据文件) 'filename.dbf'(指定名称) size(指定大小) xx;

//向创建好的表空间里增加数据文件

6、删除数据文件

alter tablespace tablespace_name drop datafile 'filename.dbf';

说明：不能删除表空间中第一个创建的数据文件，如果需要删除的话，我们需要删除整个表空间。

7、删除表空间

drop tablespace tablespace_name [including contents]

说明：a、若只是删除表空间而不删除数据文件

drop tablespace tablespace_name;

b、删除表空间及数据文件

drop tablespace tablespace_name including contents;

8、sql——结构化查询语言

DDL——数据定义语言：创建、修改、删除数据库对象

DML——数据操作语言：检索或修改数据

DCL——数据控制语言：定义数据库用户权限

TCL——事物控制语言：事物提交

常用DDL语句：

create table：创建数据库表

create index：创建数据库表的索引

drop table：删除数据库表

drop index:删除索引

truncate：删除表中的所有行

alter table：更改表结构，增加，修改，删除列

alter table add constraint：在已有的表上增加约束

二、数据库表的约束

1、创建表

语法：create table table_name(

column_name(列名) datatype（数据类型）,...

) ;

创建普通表

数值类型：number（p，s）

p表示精度，s表示小数点的位数。最高精度为38位

日期类型：date

 

例：1、建立一张学生信息表

2、包含（学号、姓名、性别、出生日期、家庭住址）

create table student(

sid number(8,0),

name varchar2(20),

sex char(2),

birthday date,

address varchar2(50)

);

2、主键约束

约束是oracle中提供的自动保持数据库完整性的一种方法。它通过限制字段中的数据，记录中数据和表之间的数据来保证数据的完整性。

语法格式：constraint constraint_name(约束名) <约束类型>

说明：约束不指定名称时，系统会给定一个名称。

主键（primary key）约束用于定义基本表的主键，它是唯一确定表中每一条记录的标识符，其值不能为null，也不能重复。表中主键只能有一个，但可以由多个列构成。如primary key（学号，科目编号）

例：a、创建表时添加主键约束

create table student(

sid number(8,0),

name varchar2(20),

sex char(2),

birthday date,

address varchar2(50),

constraint sid_pk(主键名) primary key(sid)

);

b、修改表时添加主键约束

alter table student add constraint sid_pk primary key(sid[此列加约束]);

3、非空约束（它是列级约束）

非空（NOT NULL）约束用于确保列不能为NULL，如果在列上定义了NOT NULL约束，则插入数据时，必须为该列提供数据。当更新列数据时，不能将其值设为NULL。

列级约束：

column [constraint constraint_name(可省略)] constraint_type,

说明：列级约束必须跟在列的定义后面。

表级约束:

column ,...

[constraint constraint_name(可省略)] constraint_type

(column,...),

说明：表级约束不与列在一起，而是单独定义的

例：创建表时添加非空约束

create table student(

sid number(8,0),

name varchar2(20) not null(未指定约束名),

sex char(2) constraint nn_sex not null,

birthday date,

address varchar2(50),

constraint sid_pk(主键名) primary key(sid)

);

修改表时添加非空约束

修改表添加约束的语法：

alter table table_name add [constraint constraint_name] constraint_type (column)指哪一列;

而添加非空约束要用MODIFY语句

alter table table_name MODIFY (column datatype not null);

删除约束的方式

a、将约束禁用/激活

alter table 表名 disable/enable constraint constraint_name

b、将约束彻底删除

alter table table_name drop constraint constraint_name;

c、删除主键约束的格式

alter table table_name drop primary key;

d、删除非空约束的语法

alter table table_name MODIFY column_name datatype null;

4、唯一约束

唯一（unique）约束用于指定一个或多个列的组合值具有唯一性，以防止在列中输入重复的值。

 

例：创建表时设置唯一性约束

create table student(

sid number(8,0),

name varchar2(20),

sex char(2),

birthday date,

address varchar2(50),

email varchar2(50) unique,(列级约束)

cardid varchar2(18),

constraint UK_cardid unique(cardid) （表级约束）

);

修改表时添加唯一性约束

alter table student add constraint UK_cardid unique(cardid某列);

删除唯一约束

a、禁用

alter table student disable constraint UK_cardid;

b、彻底删除

alter table student drop constraint UK_cardid;

5、检查约束

检查（check）约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。

例：创建表时设置检查约束

a、列级约束

create table student(

sid number(8,0),

name varchar2(20),

sex char(2) check(sex='男' or sex='女'),

birthday date,

address varchar2(50),

);

b、表级约束(单独定义)

create table student(

sid number(8,0),

name varchar2(20),

sex char(2) ,

birthday date,

address varchar2(50),

constraint ck_sex check(sex='男' or sex='女')

);

c、修改表时添加约束

alter table student add constraint ck_sex check(sex='男' or sex='女');

指定约束类型

d、禁用约束

alter table student disable constraint ck_sex;

e、永久删除

alter table student drop constraint ck_sex;

6、外键约束

外键（foreign key）是用于建立和加强两个表数据之间的链接的一列或多列。外键约束是唯一涉及两个表关系的约束。

主表（院系信息表） 从表（学生信息表）

列级约束

create table 从表

( column_name(外键列名) datatype references

主表(column_name主键列名) [on delete cascade级联删除],...);

表级约束

constraint constraint_name foreign key (column_name)

references 主表 (column_name)

[on delete cascade]

例：创建表时设置外键约束

主表：

create table department(

depid(系号) varchar2(10) primary key,

depname(系名) varchar2(30)

);

从表：

create table student(

sid number(8,0),

name char(10),

sex char(2) ,

birthday date,

address varchar2(20),

depid varchar2(10) references department(depid)

);

注意：

1、设置外键约束时主表的字段必须是主键列

2、主从表中相应字段必须是同一类型

3、从表中外键字段的值必须来自主表中相应字段的值，或为null

例：修改表时添加外键约束

alter table student

add constraint fk_depid foreign key(depid)

references department(depid)

on delete cascade;

禁用约束

alter table student disable constraint fk_depid;

删除约束

alter table student drop constraint fk_depid;

 三、表操作及事务回滚

1、修改表

a、添加列

alter table 表名 add 新增列名 数据类型;

例：alter table student add tel varchar2(11);

b、修改列

alter table 表名 MODIFY 列名 新数据类型;

例：alter table student modify tel number(11,0);

c、删除列

alter table 表名 drop column 列名;

例：alter table student drop column tel;

2、修改列名

alter table 表名 rename column 列名 to 新列名;

例：alter table student rename column sex to gender;

3、修改表名

rename 表名 to 新表名

例：rename student to studentinfo;

4、删除表

a、truncate table 表名

用于删除表中的全部数据，并不是把表删除掉，这种删除方式比delete删除方式删除数据的速度快，也叫截断表。

b、drop table 表名

删除表结构（包括数据）。

5、增删查改（DML操作）

a、添加信息

insert into 表名[(列1，列2，...，列n)] values (值1，值2，...，值n);

例：

insert into student(sid,name,sex) values(20010001,'张三','女');

b、查询信息

select */column(指定列) [,...] from 表名;

例：查询全体学生的学号和姓名

select sid,name from student;

查询全体学生

select * from student;

c、修改信息

update table

set column=value[,column=value,...]

[where condition];

例：

update student set tel='139xxxx8888' where sid=20010001;

update student set address='甘肃省庆阳市';(所有都修改了)

d、删除信息

delete from table [where condition];

例：

delete from student;(删除student表中所有数据)

delete from student where sid=20010001;(删除指定数据)

6、表操作——事务（保证数据的安全有效）

事务可以看做是由数据库的若干操作组成的一个单元，这些操作要么都完成，要么都取消，从而保证数据满足一致性的要求。

DML语句要使用commit提交事务或使用rollback回滚事务

DDL和DCL是自动提交事务的

比如当执行事务操作（DML语句）时，oracle会在被作用表上加表锁，以防止其他用户改变表结构；同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作。

事务的控制命令：

a、通过commit语句提交事务，当执行了commit语句后，会确认事务的变化，结束事务，删除保存点，释放锁。

b、当使用commit语句结束事务之后，其他会话将可以查看到事务变化后的新数据。

回滚事务（rollback）

rollback只能对未提交的数据撤销，已经commit的数据是无法撤销的，因为commit之后已经持久化到数据库中。

保存点（savepoint）

是事务中的一点，用于取消部分事务，当结束事务时，会自动删除该事务所定义的所有保存点。当执行rollback时，通过指定保存点可以回退到指定的点。

设置保存点：savepoint a；

回滚部分事务：rollback to a；

回滚全部事务：rollback；

7、oracle的数据字典（了解数据库的内部信息）

数据字典是oracle存放有关数据库信息的地方，是用来描述数据的。数据字典是一组表和视图结构。

数据字典中的表是不能直接被访问的，但是可以访问数据字典中的地图。

以user_*开头：该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）

以all_*开头：该视图存储了当前用户能够访问的对象的信息。（与user_*相比，all_*并不需要拥有该对象，只需具有访问该对象的权限即可。）

以dba_*开头：该视图存储了数据库中所有对象的信息。（当前用户必须有管理员权限。）

例：应用数据字典查看相应信息。

select * from user_users; //查看当前用户下的用户信息

select * from all_users; //当前用户有权访问的所有用户信息

select * from dba_users; //数据库所有用户的用户信息

 四、oracle的基本查询

1、oracle的基本查询语句

a、基本查询：

select *|{[distinct] column |expression [alias],...}

from table [where condition];

table 用于指定表名

column 用于指定列名

expression 用于指定表达式

alias 用于指定列的别名

condition 用于指定查询条件

排除重复行（distinct）

默认情况下，当执行查询操作时，会显示所有满足条件的行，包括重复行。

例：select distinct depno,job from emp;

b、查询日期列：

日期列：数据类型为日期类型的列，如date类型。

日期列的默认显示格式为DD-MON-RR

RR:代表年，且只取年份的后两位数字

以简体中文显示日期结果

alter session set nls_language ='simplified chinese';

以特定格式显示日期结果

alter session set nls_date_format='yyyy/mm/dd';

2、算术运算符的使用

a、需求：应用scott.emp表查询雇员编号，雇员名，雇员月工资，雇员年收入

select empno,ename,sal,sal*12 from emp;

b、优先级举例（需求：查看雇员编号，雇员名，年收入（年薪加半月工资））

select empno,ename,sal*12+sal/2 from emp;

在算术运算中的空值null

null表示未知值，它既不是空格，也不是0.

当算术表达式包含null时，其显示结果也为空（null）。

例：查询雇员编号，雇员名，工资，补助，实发工资的信息

select empno,ename,sal,comm,sal+comm from emp;

空值：当插入数据时，如果没有为特定列提供数据，并且该列没有默认值，那么其结果为null。

默认值：在创建表时可以使用default关键字为列设置默认值，在表中插入数据时，如果没有为该列提供数据，那么该列将使用默认值。

默认值的例子：

create table student(

sid number(8,0) primary key,

name varchar2(10),

sex char(2) default '男',

age number(2,0) default 20,

address varchar2(50)

);

insert into student (sid,name) values (20010001,'张三');

insert into student values (20010002,'王五',default,default,'甘肃省庆阳市');

3、查询语句中列的别名，连接符，字符串

a、列的别名用于改变列的显示标题

b、列的别名可以跟在列名的后面，也可在二者间加as关键字

c、若别名中包含空格或特殊字符，或要区分大小写，则要给别名加双引号

列的别名举例：

不使用列的别名

select empno,ename,sal*12 from emp;

使用列的别名

select empno"雇员编号"，ename“雇员名”,sal*12"全年工资" from emp;

select empno as "雇员编号",ename as "雇员名",sal*12 as "全年工资" from emp;

连接符||

a、连接字符串可以使用“||”操作符或concat函数

例：select ename ||'的岗位是：'||job as 员工的职位信息 from emp;

字符串可以是select列表中的字符，数字，日期；日期和字符只能在单引号中出现；每当返回一行时，字符串被输出一次。

例：select ename ||'的月薪是'|| sal from emp;

4、过滤数据（比较运算）

a、使用where子句，将不满足条件的行过滤掉

select *|{[distinct] column|expression [alias],...}

from table

[where conditions];

b、where子句中使用数字值

当在where子句中使用数字值时，即可直接引用数字值，也可以用单引号引住数字值。

例：select * from emp where deptno=20;

c、where子句中使用字符值

当where子句使用字符值时，必须给字符值加上单引号

字符值是区分大小写的

例:select * from emp where job='MANAGER';

d、where子句中使用日期值

必须给日期加单引号

日期值必须符合日期语言和显示格式

例：select * from emp where hiredate = '02-4月-81'；

e、比较运算符

举例：

select * from emp where sal = 3000;

select * from emp where sal>=3000;

select * from emp where sal<>3000;

select * from emp where sal>1000 and sal<3000;

select * from emp where sal<1000 or sal>3000;

5、过滤数据（范围模糊运算）

a、范围查询 between...and...

用于指定特定条件；两值之间包含边界；between 较小值 and 较大值

例：

select empno,ename,sal from emp where sal between 1500 and 3000;

等价于 select empno,ename,sal from emp where sal>=1500 and sal<=3000;

b、范围查询 in

in执行列表匹配的操作；

列或表达式结果匹配列表中的任意一个值；

in（值1，值2，...值n）

例：select empno,ename,job from emp where job in ('SALESMAN','MANAGER','CLERK');

等价于 select empno,ename,job from emp where job='SALESMAN'

or job='MANAGER' or job='CLERK';

c、模糊查询like

用like执行模糊查询；

当执行模糊查询时，要使用通配符%和_；

%：用于表示0个或多个字符；

_：用于表示单个字符

例：

select * from emp where ename like 'J%';

select * from emp where ename like '_AR%';

d、模糊查询中特殊符号的处理

若字符本身包含_和%字符，并且开发人员想使用这两个字符执行模糊查询，那么escape和转义符实现。

回避特殊符号的方法：使用转义符。例如，将【%】转为【\%】、【_】转为【\_】，然后再加上【escape'\'】即可。

例：select * from emp where ename like 'G\_%' escape'\';

e、判断空值 is null

is null用于检测列或表达式的结果是否为null，若为null，则返回true；否则返回false。

判断列或表达式的值是否为空可用is null或is not null，但不能用=null或<>null来判断。

例：select empno,ename,sal,comm from emp where comm is null;

select empno,ename,sal,comm from emp where comm is not null;

例、一张学生表，name,sex,id 查询男女的人数(一条sql)

 **select sex,count(id) 人数

   from student

   group by sex;

 

6、过滤数据（逻辑运算）

a、当执行sql操作时，如果sql语句结果必须同时满足多个条件，那么要用and

例：select empno,ename,job,deptno

from emp

where job='MANAGER' and deptno=10;

b、or

例：select empno,ename,job,sal

from emp

where job='MANAGER' or sal>2000;

c、not

例：select empno,ename,job

from emp

where job not in('CLERK','SALESMAN','MANAGER');

d、混合使用

优先级从高到低：not，and,or

例： select empno,ename,job,sal

from emp

where (sal>2000 or deptno=30) and job not in ('PRESIDENT','MANAGER');

7、排序数据

a、使用order by子句

select *|column[,column...]

from table

[where condition]

[order by column [ASC|DESC]];

ASC 升序

DESC 降序

注意：当select语句包含多个子句（where，group by，having，order by）时，order by必须是最后一条语句。

b、单列升序或降序排序

升序

说明：如果排序列存在null行，那么null行会显示在最后面。

select ename,sal from emp order by sal ASC;

select ename,sal from emp order by sal;(默认为升序)

降序

说明：如果排序列存在null行，那么null行会显示在最前面。

select ename,sal from emp order by sal desc;

使用列别名排序

select empno,ename,sal*12 年收入

from emp

order by 年收入 desc;

多列排序

以多列进行排序时，首先按第一列进行排序，当第一列存在相同的数据时，以第二列进行排序，以此类推。

例：select empno,ename,deptno,sal

from emp

order by deptno asc,sal desc;

//以部门编号升序排序，当其编号相同时，以薪资降序排序。