Oracle

系统用户

 

1.sys,system

2.system 

3.scott    ( 默认密码是tiger）

 

#   使用系统用户登录

1.    system/root   (system是用户   root是密码)

2.    connect sys/toor as sysdba;

 

#查看登录用户

1.    show user 

2.    desc   dba_users

 

#启动scott用户

alter user scott account unlock;

connect scott/tiger

show user

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

 

 

#表空间

概念：在数据库中开辟的空间用来存放数据库的对象；

分类：永久表空间，临时表空间，UNDO表空间；

UNDO表空间：会对修改之前的数据进行保存，可以对数据进行撤销操作；

 

 

#查看用户的表空间

desc user_tablespaces (查看用户的）

select tablespace_name from user_tablespaces;

 

desc dba_tablespaces（查看系统的）

select tablespace_name from dba_tablespaces;

scott用户查不到系统表空间，可以查到用户表空间，由此得知scoot权限低；

 

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

 

alter user system default tablespace users;(更改表空间的方法）

 

 

#创建表空间

1.永久表空间创建

 create tablespace test1_tablespace
 datafile 'test1file.dbf' size 10m;

2.临时表空间创建

create temporary tablespace temptest1_tablespace
tempfile 'tempfile1.dbf' size 10m;

3.默认情况数据存放位置

 select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

注意：文件名要大写

临时表空间默认位置的查看

select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLESPACE';

 

 

 

#修改表空间

 

1.表空间的状态

 alter tablespace test1_tablespace
 offline;（脱机）

alter tablespace test1_tablespace
  online;（联机）

alter tablespace test1_tablespace
  read only;（只读）

alter tablespace test1_tablespace
  read write;（读写）

 

查看表空间的状态

select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';

 

 

#修改数据文件

1.增加数据文件

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

2.删除数据文件

alter tablespace test1_tablespace
drop datafile 'test2_file.dbf';

查看是否添加删除成功

select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';

 

dba_data_files为 数据字典

 

 

#删除表空间

drop tablespace test1_tablespace including contents;

如果要只删除表空间，不删除表空间中的文件就不用加 including contents ；

 

 

#操作表

1.认识表

*基本的存储单位

*二维结构

*行和列

*约定

每一列数据必须具有相同数据类型

列名唯一

每一行数据的唯一性

2.数据类型

字符型  CHAR(n)  ,NCHAR(n)

VARCHAR2(n),NVARCHAR2(n)

nvarchar2和varchar2都可以表示可变长度的字符，其中nvarchar2用于存储Unicode格式的数据，更适合存储中文数据。

数值型  BUMBER(p,s)  p代表有效数字，s代表小数点后的位数

FLOAT(n)

日期型  DATE  范围是公元前4712年1月1日到公园9999年12月31日

TIMESTAMP 时间戳

其他类型   BLOB 

CLOB 

3.管理表

●    所需字段

编号   用户名   密码   邮箱   注册时间

 create table userinfo
    (id number(6,0),
    username varchar2(20),
    userpwd varchar2(20),
    email varchar2(30),
    regdate date)；

 

4.修改表

●    添加字段

（把remarks字段加入userinfo表中）

 alter table userinfo
 add remarks varchar2(500);

●    更改字段类型

（把userpwd改为number类型）

alter table userinfo

modify userpwd number(6,0);

●    删除字段

（把remarks字段删除）

alter table userinfo

drop column remarks;

●    修改字段名

（把email改成new_email）

alter table userinfo
rename column email to new_email;

●    修改表名

（把userinfo改为new_userinfo）

rename userinfo to new_userinfo;

●    删除表

（删除表中的数据）

truncate table new_userinfo;

 (删除整张表)

 drop table new_userinfo;

 

#操作表中的数据

●    给字段添加值

（给表中全部字段添加值）

insert into userinfo
values(1,'xxx','123','xxx@126.com',sysdate);

（给表中指定字段添加值）

insert into userinfo(id,username,userpwd)
values(2,'yyy','123');

（在创建表时，给date添加默认值）

 create table userinfo1
 (id number(6,0),
 regdate date default sysdate);

●    复制表数据

（创建userinfo_new复制userinfo表中全部数据）

  create table userinfo_new
  as
  select * from userinfo;

（创建userinfo_new1复制userinfo表中部分字段数据）

  create table userinfo_new1
  as
  select id,username from userinfo;

(把userinfo里的数据复制插入到userinfo_new中)

insert into userinfo_new
select * from userinfo;

（把userinfo里的指定字段复制插入到userinfo_new中指定字段中）

insert into userinfo_new(id,username)
select id,username from userinfo;

●    修改表数据

（无条件修改）

update userinfo
set email='123@qq.com',userpwd='111';

（指定条件修改）

update userinfo
set userpwd='123'
where username='xxx';

●    删除表数据

（无条件删除）

delete from testdel;

(有条件的删除）

delete from userinfo
where username='yyy';

 

 

#约束

●    非空约束

（在创建表时设置非空约束）

create table userinfo_1
(id number(6.0),
username varchar2(20) not null,
 userpwd varchar2(20) not null);

（在已有字段加非空约束，前提是之前保存的无空值）

alter table userinfo
modify username varchar2(20) not null;

（把非空约束去除）

alter table userinfo
modify username varchar2(20) null;

 

●    主键约束

作用：确保表中每一行数据的唯一性 

（在创建表userinfo_p的时候为ID加主键约束）

create table userinfo_p
(id number(6,0) primary key,
username varchar2(20),
userpwd varchar2(20));

（在创建表userinfo_p1的时候为id,username加主键联合约束）

create table userinfo_p1
 (id number(6,0),
 username varchar2(20),
 userpwd varchar2(20),
 constraint pk_id_username primary key(id,username));

注意：主键名字可以用数据字典查看

desc user_constraints

select constraint_name from user_constraints where table_name='USERINFO_P1';

（在修改表时添加主键约束）

alter table userinfo
add constraint pk_id primary key(id);

（更改约束名）

alter table userinfo
 rename constraint pk_id to new_pk_id;

（禁用主键约束）

 alter table userinfo
 disable constraint new_pk_id;

（删除主键约束）

alter table userinfo
drop constraint new_pk_id;

（删除主键约束）

alter table userinfo_p
drop primary key;

 

 

●    外键约束

（在创建表时添加一个外键约束）

在从表中相应的字段输入的值，只能是主表的值，或者是空值

  create table userinfo_f
 (id varchar2(20) primary key,
 username varchar2(20),
 typeid_new varchar2(20) references typeinfo(typeid));

create table userinfo_f2
 (id varchar2(20) primary key,
 username varchar2(20),
 typeid_new varchar2(10),
 constraint fk_typeid_new foreign key(typeid_new)references typeinfo(typeid));

（级联删除同时创建位键约束）

create table userinfo_f3
 (id varchar2(20) primary key,
 username varchar2(20),
  typeid_new varchar2(10),
  constraint fk_typeid_new1 foreign key(typeid_new)references typeinfo(typeid) on delete cascade);

（修改表时添加外键约束）

注：修改useinfo_4时为type_new字段添加typeinfo表中的typeid为外键约束

alter table userinfo_f4
add constraint fk_typeid_alter foreign key(type_new)references typeinfo(typeid);

（从数据字典user_constraints中查找表名为userinfo_f4的约束的名字和状态）

select constraint_name,constraint_type,status from user_constraints
where table_name='USERINFO_F4';

（禁用外键约束）

alter table userinfo_f4
disable constraint FK_TYPEID_ALTER;

(删除外键约束）

alter table userinfo_f4

drop constraint FK_TYPEID_ALTER;

 

 

 

●    唯一约束

唯一约束和主键约束的区别：主键字段值必须是非空的，唯一约束允许有一个空值。

（在创建表的时候设置唯一约束）列级

create table userinfo_u
 (id varchar2(10) primary key,
username varchar2(20) unique,
userpwd varchar2(20));

（在创建表的时候设置唯一约束）表级

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

（在修改表时添加唯一约束）

alter table userinfo_u2
add constraint un_username_new unique(username);

（禁用唯一约束）

alter table userinfo_u2
disable constraint UN_USERNAME_NEW;

（删除唯一约束）

 alter table userinfo_u2
drop constraint UN_USERNAME_NEW;

 

 

●     检查约束

（在创建表的时候设置检查约束）列级

 create table userinfo_c
 (id varchar2(10) primary key,
 username varchar2(20),
  salary number(5,0) check(salary>0));

（在创建表的时候设置检查约束）表级

create table userinfo_c1
(id varchar2(10) primary key,
 username varchar2(20),
 salary number(5,0),
 constraint ck_salary check (salary>0));

（在修改表时添加检查约束）

alter table userinfo_c3
add constraint ck_salary_new check(salary>0);

（禁用检查约束）

alter table userinfo_c3
disable constraint CK_SALARY_NEW;

（删除检查约束）

alter table userinfo_c3
drop constraint CK_SALARY_NEW;

 

 

#查询

（设置新的字段名，把username改为用户名）

 col username heading 用户名;

（a10设置字符型的长度）

col username format a10;

（9999.9表示四位整数，一位小数）

col salary format 9999.9

（清除之前设置的格式）

col username clear;

 

 

（查询所有字段）

select * from users;

（查询指定字段）

select username,salary from users;

（为指定字段加别名，as可以省略）

select id as 编号,username as 用户名,salary 工资
from users;

（为只能字段加别名的同时，去除重复的值）

select distinct username as 用户名
from users;

 

 

●     运算符和表达式

算术运算符，比较运算符，逻辑运算符

（查询中使用运算表达式）

select id,username,salary+200 from users;

（查询中使用比较运算符）

select username from users where salary>3200;

（查询中使用逻辑运算符）

select username from users where salary>3200 and salary<>6200;

（单条件的查询）

select username,salary from  users where id=3;

（多条件的查询）

 select * from users where username='aaa' or salary>2000;

注：逻辑运算符的优先级：按not,and,or的顺序依次递减;

 

 

 

●     模糊查询

一个_只能代替一个字符

%可以代表0到多个任意字符

（查询已a开头的username字段）

select * from users where username like 'a%';

（查询含有a的）

 select * from users where username like '%a%';

 

●     范围查询

（between and 查询）

 select * from users where salary between 800 and 2000;

（in  查询用户名为aaa或者bbb）

select * from users where username in('aaa','bbb');

 

 

●     对查询的结果排序

（按ID降序排列）asc升序排列

select * from users order by id desc;

case  ....when语句

（当username为aaa时显示计算机部门，bbb时显示市场部门，其他的情况为其他部分，为这个语句定一个别名叫部门）

select username,case username when 'aaa' then '计算机部门'
when 'bbb' then '市场部门' else '其他部门' end as 部门
from users;

（同上）（这个方法更灵活）

select username,case when username='aaa' then '计算机部门'
 when username='bbb' then '市场部门' else '其他部门' end as 部门
 from users;

注：

使用as关键字设置字段别名的语法：select column_name as new_name,... from table_name;可以同时为多个字段设置别名。
使用column命令设置字段别名的语法：column column_name heading new_name，只能为一个字段设置别名。

 

 

●     decode函数的使用

select username,decode(username,'aaa','计算机部门','bbb','市场部门','其他')as 部门
from users;