一、用户和表空间
1、使用系统用户登陆Oracle
系统用户分为:sys、system、sysman、scott,前三个是安装oracle设置的密码,最后一个密码默认“tiger”,这四个用户权限从高到低排序
登陆数据库的命令:[username/password][@server][as sysdba|sysoper]
2、查看登陆用户
查看当前登陆的用户是谁:show user
查看当前用户的数据字典:desc dba_users
查询数据字典中的用户: select username from dba_users;
在SQL*Plus中,当你输入命令的时候,不需要用;结尾
但当输入SQL语句时,在后面就要添加上;
3、启用scott用户
启用用户的语句:alter user 用户名 account unlock;
4、表空间概述
oracle与mysql的主要区别就在于表空间,表空间它又有一个或多个数据文件组成,表空间的作用就是存放数据库中创建的表和对象等数据,表空间分为:
(1)永久表空间 :持久化保存,例如表,视图,存储过程
(2)临时表空间 :临时查询的表
(3)UNDO表空间 :存储数据修改之前的数据,用于事务回滚操作
5、查看用户表空间
不同的用户登陆查看到的表空间也可能不一样。
数据字典:dba_tablespaces,user_tablespaces
查看表空间:desc dba_tablespaces,desc user_tablespaces,
表空间有6个:system(系统表空间)、(sysaux)用来辅助example表空间、(undotbs1)用来回滚撤销的表空间、(temp)临时表空间、(users)用户表空间、(example)oracle安装时用来处理示例的表空间
注意点:scott不能查询dba表空间只能查看user表空间、sys,system可以查看dba表空间还可以查看user表空间、权限高的可以查看权限低的表空间
数据字典:dba_users,user_users
查看系统管理员用户:desc dba_users
查看普通用户:desc user_users
查询默认表空间和临时表空间:
select default_tablespace,temporary_tablespace from dba_users /user_users where username=SYSTEM/username=soctt
注意username=SYSTEM 这里必须大写才能查得到数据不然会显示未选定行
修改默认表空间和临时表空间:alter user username default/temporary tablespace tablespace_name
将用户默认表空间修改为system:alter user username default tablespace system
将用户临时表空间修改为system:alter user username temporary tablespace system
6、创建表空间
创建表空间:create [temporary] tablespace tablespace_name tempfile|datafile xx.dbf size xx
创建永久表空间(文件名.dbf 文件大小10兆):create tablespace test1_tablespace datafile est1file.dbf size 10m;
创建临时表空间:create temporary tablespace temp1_tablespace tempfile emp1file.dbf size 10m;
如何查看表空间中文件的路径(表空间名字要大写):
select file_name from dba_data_files where tablespace_ name =TEST1 _TABLESPACE;
select file_name from dba_temp_files where tablespace_name=TEMPTEST1_ TABLESPACE ;
7、修改表空间
设置联机或是脱机状态:alter tablespace tablespace_name online|offline;
设置只读或可读写状态(联机状态才能更改,脱机无法更改。):alter tablespace tablespace_name read only |read write;
8、修改数据文件
向表空间里增加数据文件:alter tablespace 表空间名 add datafile xxx.dbf;
向表空间删除数据文件:alter tablespace 表空间名 drop datafile xxx.dbf;
9、删除表空间
不删除数据文件:drop tablespace tablespace_name;
删除数据文件:drop tablespace 表空间名字 including contents;
二、管理表
1、认识表
表是存放在表空间用来存储数据的基本单位。
约定:
a.每一列数据必须具有相同数据类型
b.每一列的名字要是唯一的
c.每一行的数据是唯一的(唯一性)
2、数据类型
字符型:
char(n)不可变长度,固定n,最大2000;nchar(n)存储汉字比较多,最大1000
varchar2(n)可变长度的,最大4000;nvarchar2(n)最大2000
数值型:
number(p,s) p:有效数字 s:小数点后的位数,常用
float(n)存储二进制数据,1到126位,转换为十进制数时要乘以0.30103
日期型:
date 表示范围:公元前471月1日到公元9999年12月31日
timestamp 时间戳类型,精确到小数秒
其他类型:
blob 可以存4GB字节数量的数据,以二进制的形式存放
clob 可以存4GB字节数量的数据,以字符串的形成存放
3、创建表
4、修改表
添加字段:alter table 表名 add 字段名称 类型及长度
更改字段类型:alter table 表名modify 字段名称 类型及长度
删除字段:alter table 表名drop column 字段名称;
更改字段名:alter table 表名rename column 字段名称 to 新名称;
更改表名:rename 表名 to 新表名
5、删除表
truncate table 表名;
删除表的所有数据,而不是删除表,即是截断表
drop table 表名;
删除表的结构和所有数据
三、操作表中数据
1、新增数据
向表中所有字段添加值:insert into 表名 values(值1,值2,值3,...)
向表中指定字段添加值:insert into 表名(列1,列x,...)values(值1,值x,...)
向表中添加默认值:
create table 表名
(列名 数据类型 default 默认值)
alter table 表名 modify 列名 数据类型 default 默认值
2、复制表数据
建表时复制数据:create table 新表名 as select 列1|列2...|* from 旧表名
添加时复制数据:insert into 此表名(列1,列x,...)select 列1,列x,... from 旧表名
3、修改表数据
update 表名 set column1=value1,... [where 条件]
4、删除数据
delete from 表名 [where 条件]
四、约束
1、非空约束
在创建表时设置非空约束:
create table 表名
(列名 数据类型 not null);
在修改表时添加非空约束:alter table 表名modify 列名 类型 not null;
在修改表时去除非空约束:alter table 表名modify 列名 类型 null;
2、主键约束
主键约束用来确保表中每一行数据的唯一性,能够设置主键的字段必须为非空、唯一。一张表只能设计一个主键约束;主键约束可以由多个字段构成(联合主键或复合主键)
在创建表时设置主键约束:
--设置单主键
create table 表名(列名 类型 primary key);
--设置联合主键
create table 表名(
列名1 类型,列名2 类型...,
constraints 键名 primary key (列x,列x)
)
--从约束字典查看表的约束信息
select constraints_name from user_constraints
where tablename=大写;
修改表时设置主键约束:ALTER TABLE userinfo add constraint pk_id primary key(id)
修改表中的主键约束名:ALTER TABLE userinfo RENAME CONSTRAINT pk_id to new_pk_id;
禁用主键约束:
DISABLE|ENABLE CONSTRAINT constraint_name
删除主键约束:DROP CONSTRAINT constraint_name
DROP PRIMARY KEY[CASCADE] //将关联外键一并删除
3、外键约束
从表中外键的值必须来自主表中相应字段的值或者null
设置外键约束时,主表的字段必须是主键,且主从表中相应的字段必须是同一个数据类型
在创建表时设置外键约束:
create table 创建表名
(字段名 字段类型 peferences
外键表名(外键表的主键字段)
);
在创建表时设置外键约束2(表级) 语法:
CREATE TABLE table_name(
column_name datatype,...,
CONSTRAINT constraint_name FOREIGN KEY(column_name)REFERENCES table_name2(column_name)[ON DELETE CASCADE]);
注: table_name2为主表名、[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 constraint_name;
删除外键约束:DROP CONSTRAINT constraint_name;
4、唯一约束
唯一约束与主键约束的区别:主键字段值必须是非空的、唯一约束允许有一个空值、主键在每张表中只能有一个、唯一约束在每张表中可以有多个
创建表时设置唯一约束:
CREATE TABLE table_name (
column_name datatype UNIQUE,
...
)
CREATE TABLE table_name (
column_name datatype ,
...,
CONSTRAINT constraint_name UNIQUE(column_name)
)
修改表时设置唯一约束:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column_name);
禁用唯一约束:
DISTABLE | ENABLE CONSTRAINT constraint_name
删除唯一约束:
DROP CONSTRAINT constraint_name
5、检查约束
检查约束:让字段的值更有实际意义
在创建表时设置检查约束:
CREATE TABLE table_name(
column_name datatype CHECK(expressions),
...
)
CREATE TABLE table_name(
column_name datatype,
...,
CONSTRAINT constraint_name CHECK(expressions)
)
在修改表时添加检查约束:ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(expressions);
禁用检查约束:DISABLE | ENABLE CONSTRAINT constraint_name
查找检查约束:select constraint_name,consstraint_type,status from user_constraints; 检查结果中C代表check
删除检查约束:DROP CONSTRAINT constraint_name
五、查询语句
1、查询语句
基本查询语句格式:select [distinct] colunmn_name1,...|* from table_name [where conditions]
2、SQL/PLUS中设置格式
更改查询结果字段名:COLUMN/COL column_name HEADING new_name
设置结果显示格式:COLUMN/COL column_name FORMAT dataformat
(如果是数值型,用‘9’来表示一个数位,e:999.99表示5位含有两位小数的数。如果设置的数位小于数值的实际位数,则会输出建表时规定的位个‘#’。如果是字符形式 ,开头用a表示 如a10)
清除之前设置的格式:COLUMN/COL column_name CLEAR
3、给字段设置别名
SELECT column_name AS new_name,... FROM table_name
注:AS 可省略,用空格隔开原来的字段名和新字段名即可。
4、运算符和表达式
算术运算符:+-*/
比较运算符:>,>=,<,<=,=,<>
逻辑运算符:and,or,not
在SELECT 语句中使用算术运算符:
例如:SELECT id,username,salary+200 FROM users;(给公司里面的每一个人的工资都增加了200块钱。)
注:使用此类运算符的时候,是更改了显示的结果,并不是更改了表的数据,若想更改表中的数据,则需使用update语句。
在SELECT 语句中使用比较运算符:
例如:SELECT username FROM user WHERE SALARY>800
在SELECT 语句中使用逻辑运算符:
例如:SELECT username FROM user WHERE SALARY>800 AND SALARY <>1800.5;
查询公司员工工资大于800,不等于1800.5 的姓名。
5、模糊查询
- 代表一个字符
%代表多个任意字符
select * from table_name where column1_name like a%;
6、对查询结果排序
SELECT...FROM...[WHERE...] ORDER BY column1 DESC【降序,即从大到小排序】/ASC【升序,即从小到大排序】,...【整句放最后一行】
7、case…when语句
8、decode函数的使用