Oracle表空间
1.主要分为:
永久表空间、临时表空间、undo表空间(事务回滚,撤销操作存储位置)
查看用户的表空间
dba_tablespaces、user_tablespaces数据字典
使用system 用户登录时默认的表空间有六个
权限不一样
sys、system、sysman、scott用户权限依次减小
dba_users、user_users 数据字典
设置用户的默认或临时表空间
ALTER USER username
DEFAULT|TEMPORARY
TABLESPACE tablespace_name
创建表空间
CREATE [TEMPOTARY] TABLESPACE
tablespace_name
TEMPFILE|DATAFILE 'xx.dbf' SIZE xx
更改表空间名称
alter tablespace_name(旧名称) rename to (新名称);
修改 表空间
1.修改表空间的状态
设置联机或脱机状态
ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;
可根据dba数据字典查看相应字段信息,desc dba_tablespaces;
select status from dba_tablespaces where tablespace_name=' ';
设置只读或可读写状态
alter tablespace tablespace_name read only| read write
2.修改表空间的数据文件
增加数据文件
ALTER TABLESPACE tablespace_name ADD DATAFILE 'xx.dbf' SIZE XX;
例: alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10m;
查看该表空间下数据文件
SELECT FILE_NAME FROM DBA_DATA_FILES WEHRE TABLESPACE_NAME='tablespace_name';
删除数据文件
ALTER TABLESPACE tablespace_name DROP DATAFILE 'xx.dbf';
(但不能删除创建表空间时的第一个数据文件,除非删除整个表空间)
删除表空间
DROP TABLESPACE
tablespace_name[INCLUDING CONTENTS]
操作表
1.认识表
基本存储结构、二维结构、行(记录)和列(域和字段)。
约定:
a.每一列的数据必须具有相同的数据类型
b.列名唯一
c.每一行数据的唯一性
2.数据类型
a.CHAR(n)(max_length=2000)、 NCHAR(n)(max_length=1000)(支持Unicode格式存放,更适合存放中文数据)
b.VARCHAR2(n)(max_length=4000) 、NVARCHAR2(n)(max_length=2000) 可变长度类型
c.数值型:
NUMBER(p,s) 例: NUMBER(5,2) 有效数字 5位,保留2位小数,如123.45
FLOAT(n) 主要用来存放二进制数,有效位数:0-126 ,转换十进制要乘以0.30103,
d.DATE 表示范围:公元前4712年1月1日到公元9999年12月31日可精确到秒
TIMESTAMP时间戳类型
其他类型
BLOB:存放二进制的数据最大4GB
CLOB:存放字符串类型最大4GB
创建表:
CREATE TABLE table_name
(
column_name datatype,....
)
例:create table userinfo
(
id number(6,0),
username varchar2(20),
password varchar2(20),
email varchar2(30),
regdate date);
修改表
1.修改表结构
添加字段
ALTER TABLE table_name
ADD column_name datatype;
更改字段数据类型
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;
删除表
TRUNCATE TABLE table_name;删除表中全部数据不删除表,速度比drop快很多。
DROP TABLE table_name;直接删除表
操作表中数据
添加数据
INSERT INTO table_name
(column1,column2,...)
VALUES(value1,value2,..)
若全部字段添加数据,可省略第一个括号。
向表中添加默认值
创建时
create table userinfo1
(id number(6,0),
regdate date default sysdate);
修改时
alter table userinfo1
modify email default '无';
复制表数据
创建表时复制
CREATE TABLE table_new
AS
SELECT column1,....|* FROM table_old;
在添加数据时复制
INSERT INTO table_new
[(column1,...)] \\[]代表可选
SELECT column1,...| * from table_old;
修改数据
update语句
UPDATE table_name
SET column1=value1,....
[Where conditions]
delete语句
DELETE FROM table_name
[where conditions]
约束
约束的作用:定义规则、确保完整性。
非空约束:(五个约束中,只有非空约束只能在列级设置,不能在表级设置)
创建表时设置非空约束:
CREATE TABLE table_name(
column_name datatype NOT NULL,...
);
在修改表时添加非空约束
ALTER TABLE table_name
MODIFY column_name datatype Not NULL;
在修改表时去除非空约束
ALTER TABLE table_name
MODIFY column_name datatype NULL;
主键约束:确保表当中每一行数据的唯一性、一张表只能设计一个主键约束、主键约束可以由多个字段构成(联合主键或复合主键)
在创建表时设置主键约束
CREATE TABLE table_name(
column_name datatype PRIMARY KEY,....
)
或者在创建语句最后加上
CONSTRAINT constraint_name
PRIMARY KEY(column_name1,...)
在修改表时添加主键约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY(column_name1,...);
更改约束的名称
ALTER TABLE table_name
RENAME CONSTRAINT old_name
TO new_name;
删除主键约束
ALTER TABLE table_name
DISABLE|ENABLE CONSTRAINT constraint_name;//禁用
DROP CONSTAINT constraint_name;//删除
DROP PRIMARY KEY[CASCADE]
设置外键约束(可以在列级和表级设置)
在创建表时设置外键约束(列级)
CREATE TABLE table1
(column_nam datetype REFERENCES
table2(column_name),....);
注意:设置外键约束时,主表的字段必须是主键,主从表相应字段必须为同一数据类型。从表中外键字段的值必须来自主表的相应字段,或者为null。
在创建表时设置外键约束(表级)
CONSTRAINT constraint_name FOREIGN
KEY (column_name) REFERENCES
table_name(column_name) [ON DELETE CASCADE]
在修改表的时候添加外键约束
ADD CONSTRAINT constraint_name FOREIGN
KEY(column_name) REFERENCES
table_name(column_name) [ON DELETE CASCADE]
删除外键约束
1.禁用外键约束
DISABLE|ENABLE CONSTRAINT constraint_name;
select constraint_name,constaint_type,status from user_constraints where table_name='USERINFO_F4';//通过user_constraints数据字典查看表中的信息。
2.删除外键约束
DROP CONSTRAINT constraint_name;
唯一约束:
作用:保证字段值的唯一性。与主键约束的区别:唯一约束字段的值可以为空(但空值只能有一个),唯一约束在一张表中可以有多个。
在创建表时设置唯一约束(列级)
CREATE TABLE table_name
(column_name datatype UNIQUE,...);
在创建表时设置唯一约束(表级)
CONSTRAINT constraint_name
UNIQUE(column_name)
在修改表时添加唯一约束
ADD CONSTRAINT constraint_name
UNIQUE(column_name);
删除唯一约束
禁用:DISABLE|ENABLE CONSTRAINT constraint_name
删除: DROP CONSTRAINT constraint_name;
检查约束:
在创建表时设置检查约束(列级)
CREATE TABLE table_name
( column_name datatype CHECK(expressions),
....)
在创建表时设置检查约束(表级)
CONSTRAINT constraint_name
CHECK (expressions);
在修改表时添加检查约束
ADD CONSTRAINT constraint_name
CHECK(expression);
禁用检查约束:
DISABLE|ENABLE CONSTRAINT constraint_name;
删除检查约束:
DROP CONSTRAINT constraint_name;
总结:1.只有非空约束不能在表级设置
2.常用数据字典(user_constraints)来查看相关信息select constraint_name,constaint_type,status from user_constraints where table_name='USERINFO_F4';
3.RENAME CONSTRAINT old_name TO new_name;//重命名约束,非空约束没有名称。
查询
基本查询语句
SELECT [DISTINCT] column_name1,...|* //distinct去掉查询到的重复内容
FROM table_name
[WHERE conditions]
在SQL*PLUS中设置格式(不同表中有相同名称字段,,设置的到底是哪张表,还是全部都设)
COLUMN column_name HEADING new_name; //(column 可以简写为col)只是更改结果显示格式,不更改真正表中字段名
COLUMN column_name FORMAT dateformat; //字符类型只能设置显示长度,以a开头,如 FORMAT a10;数值型用9代替如设置4450.8长度用9999.9|$9999.9
COLUMN column_name CLEAR //清除已设置的格式
查询所有字段
SELECT * FROM table_name;
查询指定字段
SELECT column_name1,column_name2,... FROM table_name;
给字段设置别名
SELECT column_name AS new_name,... //as 可省略
FROM table_name;
运算符和表达式
算术运算符(+、-、*、/)
比较运算符(>,>=,<,<=,=,<>)
逻辑运算符(and,or, not )返回值为boolean型 优先级:按not、and、or的顺序依次递减。
例: select id, username, salary+200 from users;
select username from users where salary>800;
select username from users where salary>800 and salary<>1800.5;
带条件的查询
模糊查询
通配符的使用(_,%):一个_只能代表一个字符,%可以代表0到任意多个任意字符。
使用Like查询
例:select * from users where username like 'a%'; //查询用户名以a开头的用户信息
select * from users where username like '_a%'; //查询第二个字符是a的用户信息
select * from users where username like '%a%'; //查询用户名中含有a的用户信息
范围查询
BETWEEN....AND
select * from users where salary between 800 and 2000;
IN/NOT IN
select * from users where username in ('aaa','bbb'); //查询用户名是aaa或者bbb的用户信息
对查询结果排序
SELECT... FROM...[WHERE...]
ORDER BY column1 DESC/asc,....;
例:select * from users order by id desc;
select * from users order by id desc,salary asc;//先按ID排,再按工资排,ID为主要
case...when语句
CASE column_name
WHEN value1 THEN result1,....
[ELSE result] END;
例:select username ,case username when 'aaa' then '计算机部门'
when 'bbb' then '市场部门' else '其他部门' end as 部门
from users;
CASE
WHEN column_name=value1
THEN result,....[ELSE result] END;
例: select username, case when username='aaa' then '计算机部门'
when username='bbb' then '市场部门' else '其他部门' end as 部门
from users;
例: select username, case when salary<800 then '工资低'
when salary>5000 then '工资高' end as 工资水平
from users;
decode函数的使用
decode(column_name, value1,result1,..,defaultvalue)
例:select username, decode(username,'aaa','计算机部门','bbb','市场部门','其他')as 部门 from users;