系统用户：
1.sys system（sys权限最高）
2.sysman(操作企业管理器) 密码是安装的时候输入的密码
3.scott（默认是tiger)
oracle中用户，权限，角色
用户：创建/修改/删除 用户
创建用户： CREATE USER name
IDENTIFIED BY password
[ACCOUNT LOCK|UNLOCK]
[PASSWORD EXPIRE];(口令立刻过期)
修改用户： ALTER USER name
IDENTIFIED BY password
[ACCOUNT LOCK|UNLOCK]
[PASSWORD EXPIRE];(口令立刻过期)
删除用户：DDROP USER name [CASCADE];
具体操作：
CREATE USER test
IDENTIFIED BY test
ACCOUNT LOCK
PASSWORD EXPIRE;
grant create session to test;（用户授权）
ALTER USER test ACCOUNT UNLOCK;（解锁）
登录
1.使用system用户登录
system/password@server
2.使用sys用户登录
connect sys/password as sysdba
查看登录用户
1.show user命令
2.dba_users数据字典(数据字典是数据库提供的表，用于查看数据库的信息)
desc dba_users; （查看数据字典里包含哪些字段）
结果包含如下一些字段：USERNAME NOTNULL VARCHAR2(30)
USER_ID NOTNULL NUMBER
PASSWORD VARCHAR2(30)
...
在数据字典中包含哪些用户？select username from dba_users;
启用scott用户
alter user username account unlock （解锁）
alter user scott ccount unlock;（用户已更改0
使用scott用户登录SQL Plus
conn scott/tiger
修改scott密码
SQL> conn /as sysdba
SQL> alter user scott identified by tiger;
SQL> conn scott/tiger
SQL> select * from tab;
表空间:
理解表空间：数据库与表空间（一个数据库有多个表空间）
表空间与数据文件
表空间的分类：永久表空间（表，视图，存储过程）
临时表空间（数据库操作当中，中间执行的过程，执行结束后会被释放）
undo表空间（保存事务所修改的旧值，可以对数据进行回滚即撤销操作）
查看用户的表空间：
1.dba_tablespaces(系统用户表空间) user_tablespaces（普通用户表空间） 数据字典
步骤：conn system/abc...123
select tablespace_name from dba_tablespaces;
结果为：system (存放sys系统表空间)
sysaux (作为一个example的辅佐表空间)
undotbs1 （存储撤销的表空间）
temp （临时表空间）
users
exmple
步骤：desc user_tablespaces;
select tablespace_name from user_tablespaces;
2.dba_users user_usres数据字典
conn sys/abc...123 as sysdba
desc dba_users;
查看system默认的表空间和临时表空间是什么？
select defult_tablespace,temporary_tablespce from dba_users where username='SYSTEM';
结果是：SYSTEM TEMP(学会如何更改默认表空间和临时表空间，方便数据的备份及恢复)
设置用户默认或临时表空间：
ALTER USER usernme DEFAULT|TEMPORARY TABLESPACE tablespace_name
alter user system default tablespace user;
创建表空间：
create [temporary] tablespace tablespace_name tempfile|datafile 'xx.dbf' size xx
创建永久表空间：
create tablespace test1_tablespace datafile 'textfile.dbf' size 10m;
创建临时表空间：
create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m;
通过数据字典查看表空间位置：
通过数据字典查看创建的永久表空间：
desc dba_data_files;(返回的字段有file_name tablespace_name...)
查看表空间所在的文件位置：
select file_name from dba_data_files where tablespce_name='TEST1_TABLESPACE';
查看临时表空间：desc dba_temp_files;
查看表空间所在的文件位置：
select file_name from dba_temp_files where tablespce_name='TEMPTEST1_TABLESPACE';
修改表空间：
1.修改表空间的状态
a.设置联机或脱机状态：alter tablespace tablespace_name online|offline;
创建完一个表空间之后，默认状态是联机状态；
具体操作：
alter tablespace test1_tablespace offline;
desc dba_tablespaces;数据字典查看status
select status from dba_tablesaces where tablespace_name='TEST1_TABLESPACE';结果为：status:offline
再设置回联机状态：alter tablespace test1_tablespace online;
select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';结果为status:online
b.设置只读或可读写状态：alter tablespace tablespace_name read only|read write;
默认情况下是可读写的，联机状态才可以更改读写性
具体操作：
alter tablespace test1_tablespace read only;
select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';结果为：status:online
2.修改数据文件
a.增加数据文件:
alter tablespace tablespace_name add datafile 'xx.dbf' size xx;
具体操作：向表空间test1里添加数据文件
alter tablespace test1_tablespace add datafile 'text2_file.dbf' size 10m;
select file_name from dba_files where tablespace_name='TEST_TABLESPACE';查看新增的文件位置
b.删除数据文件：
alter tablespace tablespace_name drop datafile 'filename.dbf'
不能删除表空间里面的第一个数据文件，如果要删除的话需要把整个表空间删掉。
具体操作：删除上面新增的text2_file.dbf
alter tablespace test1_tblespace drop datafile'text2_file.dbf';
select file_name from dba_files where tablespace_name='TEST_TABLESPACE';查看文件位置发现text2已经删除
删除表空间：
drop tablespace tablespace_name [INCLUEDING CONTENTS]
如若仅删除表空间，不删除数据文件：则不需要加 [INCLUDING CONTENTS]
具体操作：删除表空间test1
drop tablespace test1_tablespace including contents;删除表空间成功
操作表：
1.认识表
a.表都存放在表空间里，是数据库基本存储单位，是一个二维结构，行（记录）和列（域和字段）
b.约定：1.每一列数据必须具有相同的数据类型
2.列名唯一
3.每行数据的唯一性（重复的行会造成表中数据的冗余）
2.数据类型
a.字符型：
char(n),nchar(n)是固定长度的，nchar是按照unique格式存放数据，char的n最大值是2000，而nchar的n最大值是1000；一般用nchar存储汉子。不够会自动补上空格。
varchar2(n),nvarchar2(n),varchar2(n)最大值是4000，nvarchar2(n)最大值是2000.不够不会补上空格，可以节省空间。
b.数值型
number(p,s) p代表有效数字，s代表小数点后的位数；例如：number(5,2) 有效数字5位，保留2位小数，如123.45s
float(n) 主要存储二进制数据（能表示二进制的数据位数是1-126位），二进制数转换成十进制需要乘以0.30103才能得到。所以在oracle中使用number比较多
c.日期型
data date类型表示范围：公元前4712年1月1日到公元9999年12月31日
可以精确到秒。
timestamp时间戳类型，能精确到小数秒
d.其他
blob:能存放4g的数据（二进制）
clob:能存放4g的数据（字符串类型）
3.管理表
创建表：
基本语法：
create table table_name
(
column_name datatype,...
)在同一个用户下，所有的表名要是唯一的
创建用户信息表：
所需字段： 编号 用户名 密码 邮箱 注册时间
字段的类型：number varchar2(n) date
create table userinfo
(id number(6,0),
username varchar2(20),
userpwd varchar2(20),
email varchar2(30),
regdate date);
查看表的结构与查看数据字典的方法一样：desc userinfo
修改表:（修改表的结构，而不是数据）
1.添加字段（列或者域）：
如果一个表已经创建好，还想再添加一些字段：
alter table table_name add column_name datatype;凡是对表进行修改都是alter table
具体操作：向useinfo表里添加一个备注字段
alter table usreinfo add remarks varchars(500);
2.更改字段的数据类型：
alter table table_name modify column_name datatype;
操作：修改数据类型的长度，更换数据类型
alter table userinfo modify remarks varchar2(400);注意：如果在一个表当中已经存在了数据，则不能修改，只有在数据为空的情况下才能进行更改）。
alter table userinfo modify userpwd number(6,0);修改密码字段的数据类型
3.删除字段：
alter table table_name drop column column_name;
具体操作：alter table userinfo drop column remarks;
4.修改字段名：
alter table table_name rename column column_name to new_column_name;
具体操作：alter table useinfo rename column email to new_email;
5.修改表名：
rename table_name to new_table_name;
rename userinfo tp new_userinfo;
4.删除表：
truncate table table_name;(删除表当中的全部数据，也叫截断表，比delete速度快)
truncate table new_userinfo;删除表里的数据，表的结构仍然存在。
drop table table_name;删除整张表的结构：
drop table new_userinfo;删除表不存在了。
操作表中的数据：
1.添加数据：
insert语句：
insert into table_name
(column1,column2,...)
values(value1,value2,...)
操作实例：
1.向表中所有字段添加值：
insert into userinfo values(1,'xxx','123','xxx@126.com',sysdate);向表中插入一条数据，sysdate函数获取当前系统时间。
查看该条数据：select * from userinfo;
2.向表中指定字段添加值：
insert into userinfo(id,username,userpwd) values(2,'yyy','123');
查看数据：select username,userpwd from userinfo;
3.向表中添加默认值：
a.创建表时就设置;create table userinfo1(id number(6,0),regdate date default sysdate);
插入数据时：insert into userinfo1 (id) values(1);则regdate字段会自动补上。
查询：select * from userinfo1;
修改userinfo表email字段
b.已经创建好表，后来再修改成default
更改字段的数据类型：alter table userinfo modify email default '无'；
再向表中插入数据：insert into userinfo (id) values(2);
查看：select email from useinfo;
注意：字符型加单引号；如果在创建表市，某些字段不能为空时添加值时需不为空。
2.复制表数据：
1.在建表时复制：
create table table_new as select column1,...|*from table_old;
create table userinfo_new as select * from userinfo;全部复制
create table userinfo_new1 as select id,username from userfino;复制部分
2.在添加时复制：
insert into table_new [(column1,..)] select column1,...| * from table_old;
表已经存在，在添加数据时复制数据：
insert into userinfo_new select * from userinfo;复制userinfo表的全部数据
查看：select if from userinfo_new;
inert into uerinfo_new(id,username) select id,username from userinfo;复制userinfo表的部分数据
3.修改数据：
update语句：
update table_name set column1=value1,...[where cinditions];修改的条件，如果不加where condition则会给所有的行更改
操作实例：
无条件的更新：
update userinfo set userpwd='111111';一个字段的更改
select userpwd from userinfo;
update userinfo set userpwd='111',email='111@126.com';多个字段的更改
select userpwd,email from userinfo;
有条件的更新：
用户名是xxx的密码改为：123456
update userinfo set userpwd='123456' where username='xxx';
select userpwd from userinfo;
4.删除数据：
以行为单位来进行删除。
delete from table_name; truncate table截断表 和delete from table_name效果一样
操作实例：
无条件的删除：
先创建删除表：create table testdel1 as select * from userinfo;
删除表；delete from testdel1;
查看：select * from testdel1;结果：未选定行，删除成功。
有条件的删除：删除用户名是yyy的用户：
先查看yyy用户是否存在：select username from userinfo;
删除用户名yyy:detele from userinfo where username='yyy';
约束：数据类型是控制输入的值的类型，约束是控制你输入的值要满足你设定好的一些要求的。
1.约束的作用：
1.定义规则
2.确保完整性
2.oracle中的5个约束：
1.非空约束
1.在创建表时设置非空约束：
create table table_name (column_name datatype not null,...);
create table userinfo_1(id number(6,0),username varchar2(20) not null, userpwd varchar2(20) not null);
2.在修改表时添加非空约束：
alter table table_name modify column_name dtatype not null;
给userinfo添加非空约束：alter table userinfo modify username varchar2(20) no tnull;报错，因为表内有数据。
此时需要把表内数据删除才能添加约束：delete from userinfo;
再执行: alter table table_name modify column_name dtatype not null;添加约束，就会成功。
3.在修改表时去除非空约束：
alter table table_name modify column_name datatype null;
alter table table_name modify column_name dtatype null;
2.主键约束
作用：确保表当中每一行数据的唯一性（非空，唯一）一张表里只能设置一个主键约束，但是这个主键约束可以由多个字段构成（联合主键或复合主键）
1.在创建表的时候设置主键约束：
列集：
create table table_name ( column_name datatype primary key,...)
create table userinfo_p
(id number(6,0) primary key,
username varchar2(20),
userpwd varchar2(20)
); 主键约束默认是not null;
表集：
联合主键：（在所有的字段写完后，再写此句约束）表集约束
constraint constraint_name primary key(column_name1,...)
create table userinfo_p1
(id number(6,0),
username varchar2(20),
userpwd varchar2(20),
constraint pk_idusername primary key(id,username));
结果表userinfo_p1的id 和username都为not null.
desc user_constraints;通过这个数据字典来查询约束的名字，类型等
例如查约束的名字：
select constraint_name from user_constraints where table_name=' userinfo_p1';
查询到的主键约束名字为：pk_idusername
之前给userinfo_p的id字段设置主键约束但并未设置名字，而是由系统生成的， select constraint_name from user_constraints where table_name=' userinfo_p';结果为：SYS_C0010836
2.在修改表的时候添加主键约束:
ADD CONSTRAINT constraint_name primary key(column_name1,...);
alter table userinfo
add constraint pk_id primary key(id);
如果表当中已经存在值了，值必须得是唯一的，不能为空值；最好的在添加主键时表内没有值。
select constraint_name from user_constraints where table_name=' userinfo';结果：pk_id
3.更改约束的名称：
rename constraint old_name to new_name
alter table userinfo
rename constraint pk_id to new_pk_id;
可以更改主键约束的名字，也可以更改任何一个约束的名字。
4.删除主键约束：
1.禁用|启用：
disable|enable constraint constraint_name
操作：alter table userinfo
disable constriant new_pk_id;
查看禁用的约束：
select constraint_name,status from user_constraints where table_name='USERINFO';
2.drop constraint constraint_name完全删除约束
操作：alter table userinfo
drop constraint new_pk_id;
3.drop primary key[cascade],由于每张表只有一个主键约束，cascade是表存在及联约束时，删除其余表对这表的外键约束
3.外键约束
1.在创建表时设置外键约束：
列集：
create table table1
(column_name datatype references
table2(column_name),...);
table2是主表，table1是从表；设置外键约束时，主表的字段必须是主键，主从表中相应的字段必须是同一个数据类型；从表中外键字段的值必须来自主表中的相应字段的值，或者为null的值
操作：
1.先创建主表：（用户类型表）
create table typeinfo
(typeid varchar2(10) primary key,
typename varchar2(20));
2.创建从表：
create table userinfo_f
(id varchar2(10) primary key,
username varchar2(20),
typeid_new varchar2(10) references typeinfo(typeid));
3.验证向从表当中的typeid的来源要么是主表当中的值要么是空值。
insert into typeinfo values(1,1);
insert into userinfo_f(id,typeid_new) values(1,2)报错
insert into userinfo_f(id,typeid_new) values(1,1); ok了
插入空值insert into userinfo_f(id,typeid_new) values(2,null); ok了
表集：
关键字 外键约束关键字
constraint constraint_name foreign key(column_name) references table_name(column_name) [ON DELETE CASCADE]及联删除：主表当中的数据删除之后，从表当中使用了这条数据的字段也会被删除。确保了主从表数据的完整性。约束的名字是唯一的
create table userinfo_f2
(id varchar2(10) 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(10) primary key,
username varchar2(20),
typeid_new varchar2(10),
constraint fk_typeid_new1 foreign key(typeid_new) references typeinfo(typeid) on delete cascade);
2.在修改表时添加外键约束：
add constraint constraint_name foreign key(column_name) references table_name(column_name) [on delete cascade]
操作：
1.创建表：create table userinfo_f4
(id varchar2(10) primary key,
username varchar2(20),
typeid_new varchar2(10));
2.更改表：
add constraint fk_typeid_alter foreign key(typeid_new)references typeinfo(typeid);
3.删除外键约束：
1.禁用|启用：
disable|enable constraint constraint_name
因为忘记了外键约束的名字，所以先查找外键约束的名字：
select constraint_name,constraint_type,status from user_constraints where table_name='USERINFO_F4';
结果：一个P，主键约束；一个R，外键约束，名字为：FK_TYPEID_ALTER
操作：alter table userinfo
disable constraint FK_TYPEID_ALTER;
验证是否禁用：
select constraint_name,status from user_constraints where table_name='USERINFO_F4';
2.彻底删除：
drop constraint constraint_name完全删除约束
操作：alter table userinfo
drop constraint FK_TYPEID_ALTER;
4.唯一约束
作用：保证字段值的唯一性
主键约束作用是保证值的唯一性；唯一约束和主键约束的区别：主键字段必须是非空的，而唯一约束则可以为空值；每张表的主键约束只能有一个，而唯一约束却可以有多个。
1.在创建表时设置唯一约束：
列集：
create table table_name
(column_name datatype UNIQUE,...)
操作：create table userinfo_u
(id varchar2(10) primary key,
username varchar2(20) unique,
userpwd varchar2(20));
表集：
constraint constraint_name unique(column_name)，这句话放在所有字段定义完成之后。
要将多个字段设置成唯一的约束，需要写多个唯一约束语句。
操作：create table userinfo_u1
(id varchar2(10) primary key,
username varchar2(20),
constraint un_username unique(username));
2.在修改表时添加唯一约束：
add constraint constraint_name unique(column_name);
操作：1.创建表：create table userinfo_u2
(id varchar2(10) primary key,
username varchar2(20));
2.添加唯一约束：
alter table userinfo_u2 add constraint un_username_new unique(username);
3.删除唯一约束：
1.暂时禁用：
disable|enable constraint constraint_name
查看约束的名字，类型，状态：
select constraint_name,constrint_type,status from user_constraints where table_name=' userinfo_ u2';
结果为： 一个u 一个p.
alter table userinfo_u2
disable constraint UN_USENAME_NEW;
2.彻底删除：
drop constraint constraint_name
5.检查约束
作用：让表中的数据更具有实际意义
员工个人信息：年龄，工资，电话号码
50 1000 134XXXXX
1.在创建表时设置检查约束：
在一个表当中也可以有多个。
列集:
create table table_name (column_name datatype check(expressions),...)
操作：create table userinfo_c
(id varchar2(10) primary key,
username varchar2(20),
salary number(5,0) check(salary>0));
这样就不能往里插负数的工资数据了。
表集：
constraint constraint_name check(expressions);通常以ck,chk开头
操作：
create table userinfo_c1
(id varchar2(10) primary key,
username varchar2(20),
salary number(5,0),
constraint ck_salary check(salary>0));
2.在修改表时添加检查约束：
add constraint constraint_name check(expressions);
操作：create table userinfo_c3
(id varchar2(10) primary key,
username varchar2(20),
salary number(5,0));
alter table userinfo_c3
add constraint ck_salary_new check(salary>0);
3.删除检查约束：
1.暂时禁用：
disable|enable constraint constraint_name
2.彻底删除：
drop constrint constraint_name
约束小结：1.只有主键约束在每张表里面只能有一个，可以由多个字段构成。
2.外键约束时唯一一个涉及两张表之间关系的约束
3.在创建表时设置约束：非空约束只能在列级设置，不能在表级设置，并且设置非空约束时，非空约束没有名字。
4.在修改表时添加约束：在修改表的时候添加非空约束实际上用的是：修改字段的语句，为：alter table table_name modify column_name datatype not null;
5.更改约束的名称：非空约束没有名字，不能使用该语句
1.不知道约束名称时，可以更加数据字典来查看：user_constraints
2.rename constraint old_name to new_name 此语句就是更改约束名称的语句
6.删除约束：1.alter table table_name modify column_name datatype null;
2.禁用：disable | enable constraint constraint_name(放在alter table之后出现)
3.彻底删除：drop constraint constraint_name
4.删除主键约束：drop primary key
查询：
1.基本查询语句：
select [distinct] column_name1,...|* from table_name [where conditions]
2.在SQL*PLUS中设置格式：设置查询结果的显示格式
1.更改查询后结果的字段名：column column_name heading new_name； (column可以简写成col)
操作：设置新的字段名：col username heading 用户名;
2.column column_name format dataformat; （注意：字符类型只能设置显示的长度）
操作：把用户名字段的数值长度改为10：
col username format a10;(a代表字符型，10代表10个长度)
把工资字段设置为显示一位小数：
col salary format 9999.9;(9代表一个数值)
把工资格式改为美元符号开头：
col salary format $9999.9;
3.清除设置的格式：col column_name clear;
3.查询表中的所有字段及指定字段：
1.查询所有字段：select * from table_name;
操作：select * from users;
2.查询部分字段：select column1_name,column2_name from table_name;
4.给字段设置别名 ：（注意：是针对查询结果进行的，并没有更改字段的名字）
可以一次为多个字段设置别名
select column_name as new_name,... from table_name;(注意：as可以省略，用空格隔开原来的字段名和新名字即可)
操作：select id as 编号，username as 用户名，salary 工资 from users;
通过distinct去除重复的用户名：
select distinct username as 用户名 from users；
5.运算符和表达式：
表达式=操作数+运算符
操作数：变量，常量，字段；
运算符：算术运算符：+ - * /
比较运算符：>, >=, <, <=, =, <>
逻辑运算符：and or not (not是非)
6.在select语句中使用运算符：（只影响查询结果，不影响实际表中的数据，要真的改表中的数据，需要使用update语句）
使用算术运算符：select id,username,salary+200 from users;(给工资加200元)
使用比较运算符：select username from users where salary>800;(查询工资超过800元的人)
使用逻辑运算符：select username from users where salary>800 and salary<>1500;(查询工资高于800，但不等于1500的人)；
7.带条件的查询：
1.单一条件查询： select salary from users where username='aaa';
2.多条件的查询： 使用逻辑运算符连接语句 select * from users where username='aaa' or salary>2000;
select * from users where username='aaa' or (salary>800 and salary<=2000);
逻辑运算符的有限性：not > and > or; 比较运算符 >逻辑运算符
select * from users where not (username='aaa');
8.模糊查询：
like
通配符的使用：_ %(一个_只能代表一个字符，%可以代表0到多个任意字符)
操作：查找以a开头的用户：select * from users where username like 'a%';
查找含有a的用户名：select * from users where username like '%a%';
9.范围查询：
between...and(从什么值到什么值)
操作：查询工资从800到2000的用户：select * from users where salary between 800 and 2000;(是闭合区间)
in/not in (后面的值不是范围，而是一个具体的值)查询的值等于什么，不等于什么。
select * from users where username in ('aaa','bbb');
10.对查询结果排序：
select... from...[where...] order by column1 desc/asc,...
操作：select * from users order by username desc,salary asc;(用户名)
11.case...when语句使用：
1. case column_name
when calue1 then result1,...
[else result] end
操作:select username, case username when 'aaa' then '计算机部门'
when 'bbb' then '市场部门' else '其他部门' end as 部门
from users;
2. case
when column_name=value1
then result1,...[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;
12.decode函数的使用：
decode (column_name,value1,result1,...,defaultvalue)
操作: select username, decode(username,'aaa','计算机部门','bbb','市场部门','其他')as 部门
from users;
总结:
1.用户表空间:
1.如何查看登录用户:show user命令 dba_users数据字典
2.启用scoot用户
3.如何查看某个用户的默认表空间和临时表空间
4.表空间管理:创建,修改,删除表空间
2.表与约束:
1.数据类型:char(n) nchar(n) varchar2(n) nvarchar2(n) ;数值型:number(p,s) float(n) ;日期型:date,timestamp ;其他类型:blob,clob
2.对表的创建,修改,删除
3.对表中数据的操作:添加数据insert 修改数据update 删除数据:delete
4.5个约束:非空约束,主键约束,外键约束,唯一约束,检查约束
5.查询语句:查询所有字段和指定字段;在查询语句中使用运算符和表达式;在查询语句中加入where;范围查询;模糊查询:like关键字,通配符;case...when语句和Decode函数
网友评论