Oracle数据库常用操作总结（一）

忘是亡心i 2022-05-12 07:00 301阅读 0赞

\--oracle cs架构软件  
\--客户端  
\--tns   
\--协议  
\--ip  
\--端口  
\--数据库名字

\--监听如果出了问题，先删除所有监听，再重建。netca。tns文件中名字不能重复，

\--oracle默认自带两个管理员用户 sys system 这两个用户在登录时候必须使用sysdba角色，一般我们用system来进行管理，  
\--但是如果system搞不定，在用sys

\--角色就是权限的集合

\--normal  普通用户登录时候的权限  
\--sysoper 系统操作员权限  
\--sysdba  最NB oracle管理员的权限

\--oralce 服务的开启与关闭  
\--oracle的主服务是OracleService数据库名字  
select status from v$instance;--查看数据库的实例的运行情况，如果是open，说明数据库开启状态  
\--数据库的状态 close nomount mount open  
shutdown immediate;--关闭数据库，出现oracle例程已关闭说明数据库已经关了  
startup ;--开启数据库，数据库已经打开  
\--oracle监听服务的开启与关闭  
\--oralce的监听服务Oracle。。。TNSlistener  
\--启动监听  
\--lsnrctl start  
\--关闭监听  
\--lsnrctl stop  
\--查看监听状态  
\--lsnrctl status

\--数据库的对象（objects）触发器，队列，表（数据库中存数数据的容器），视图，索引等等。

\--用户  
\--创建用户  
\--语法：create user 用户名 identified by 密码；在创建用户时候oracle会默认把名字小写改为大写，创建用户不能同名  
\--新建一个用户叫matasha密码为shuiya  
create user matasha  
  identified by shuiya;  
\--创建一个用户叫bulutuo密码aolifo  
create user bulutuo identified by aolifo;

\--授权 一般来说是管理员来授权  
\--授权语法grant 角色 to 用户，默认创建一个普通用户时候，只给connect和resource权限  
connect   --连接数据库  
resource  --使用数据库资源  
dba       --最NB  
\--给bulutuo授权可以登录并且使用数据库资源  
grant connect,resource to bulutuo;

\--收回权限  
\--revoke 权限 from 用户名  
revoke connect,resource from misamisa;

\--在同桌电脑上创建一个用户，然后用自己电脑登录同桌数据库（用创建的用户）

\--1.在server端创建用户并且授权

\--2.配置客户端tns，连接server端数据库

\---------c------------/---------------s  
tns2                       创建用户并授权1  
172.11.1.250               172.11.1.181(orcl)  
                           create user bbs identified by bbs;  
                           grant connect ,resource to bbs;  
                           
\--在写sql的时候要加分号

\--表空间 --存储数据库对象的容器  
\--表空间分为 数据表空间和临时表空间  
\--数据表空间--存放数据位置  
\--临时表空间--存放临时数据的地方（缓存）  
\--创建数据表空间的语法  
create tablespace 表空间名 datafile '文件位置' size 大小  
       【 autoextend on    --自动扩展功能，假如我的数据表空间满了，那么自动扩展  
                    next 大小     --每次自动扩展的大小  
                         maxsize 大小  --最大大小 】  
                           
create tablespace bank\_wawa2 datafile 'F:\\tb\\bank\_wawa2' size 5m;  
\--创建表空间bank\_wawa 数据文件存储在F:\\tb\\bank\_wawa.dbf 大小为5m，bank\_wawa 名字一定要明确是什么功能。。dbf 数据库文件的意思，但是不  
\--强制使用 database file,强调文件存储路径要写，大小（默认）要写。

create tablespace shanghai1 datafile 'F:\\tb\\shanghai1.dbf' size 5m   
       autoextend on   
                  next 1m   
                       maxsize 10m;  
\--创建表空间shanghai 数据文件存储在F:\\tb\\shanghai.dbf，默认大小5m，自动扩展功能打开，空间不足时候，每次自动扩展1m，最大大小10m

\--临时表空间  
create temporary tablespace 表空间名字 tempfile '文件位置' size 大小  
       【 autoextend on    --自动扩展功能，假如我的临时表空间满了，那么自动扩展  
                    next 大小     --每次自动扩展的大小  
                         maxsize 大小  --最大大小 】  
create temporary tablespace shanghai1\_tmp tempfile 'F:\\tb\\shanghai1\_tmp.dbf' size 5m autoextend on next 1m maxsize 10m;

\--创建用户  
create user 用户名 identified by 密码 \[ default tablespace 数据表空间名字 temporary tablespace  临时表空间名字 \]；  
\--创建一个用户 dollar 指定表空间 设置密码  
create user dollar identified by dollar default tablespace shanghai temporary tablespace shanghai\_tmp ;

\--如果新建用户需要制定表空间，那么，在创建用户之前必须要创建表空间。  
\--举个栗子  
\--创建用户  
\--1.创建数据表空间和临时表空间  
create tablespace shenzhen datafile 'F:\\tb\\shenzhen.dbf' size 5m;  
create temporary tablespace shenzhen\_tmp tempfile 'F:\\tb\\shenzhen\_tmp.dbf' size 5m;  
\--2.创建用户指定表空间和密码  
create user renminbi identified by renminbi default tablespace shenzhen temporary tablespace shenzhen\_tmp;  
\--3.授权  
grant connect,resource to renminbi;

修改用户信息  
\--改密码（管理员 用户本身）  
\--语法：  
alter user 用户名 identified by密码；  
\--忘记密码  
cmd  --sqlplus / as sysdba --conn 用户名/密码 可以登录  
alter user renminbi identified by test;  
\--更改用户renminbi的密码为test

\--解锁和锁定用户  
alter user 用户名 account unlock(lock);  
alter user 用户名 identified by 密码 account unlock；

\--解锁scott用户  
alter user scott account unlock;  
\--解锁scott用户并且指定密码为tiger  
alter user scott identified by tiger account unlock;  
\--锁定用户 无法登录  
alter user scott account lock;

\--修改用户默认信息  
alter user 用户名 default tablespace 数据表空间名字 temporary tablespace 临时表空间名字；  
\--数据表空间一定要指定为数据表空间

\--把renmibi用户的默认数据表空间修改为bank\_wawa  
alter user renminbi default tablespace bank\_wawa;

\--用自己创建的用户登录（普通用户）

\--删除表空间  
drop tablespace 表空间名字

\--建表 建立一个excel   
\--表结构 列的信息  
\--查看表结构的命令  
\--1.desc 表名  
\--2.右键表名 view  
\--创建表的语法  
/\*  
create table 表名（  
       列名1 数据类型 （约束），  
       列名2 数据类型  （约束）  
）；\*/  
\--创建（复制表）  
\--数据类型 就是对数据的一种约束  
\--日期 1986-03-23  
\--数字 1234567  
\--字符 hello  
\--关键字 蓝绿色 oralce内部是有定义的所以不建议使用  
\--建立一个用户表名字为users 里面有2个列 一个叫cname列 数据类型为数字 ，money 数字类型  
create table users1(  
       cname  number,  
       money  number  
);

\--约束 对数据的一种规范化操作  
\--主键约束  
\--主要针对于外键约束，它实现的功能就是通过一个条件只能找到唯一的一个数据  
\--唯一约束  
\--现的功能就是通过一个条件只能找到唯一的一个数据  
\--检查约束  
\--检查该列的数据是否满足检查条件  
/\*外键约束（参考约束）  
参考另外一张表里面的数据  
默认值等等  
语法  
create table 表名（  
       列1 数据类型 constraint 约束名 primary key,  
       列2 数据类型 unique，  
       列3 数据类型 default 默认值，  
       列4 数据类型 constraint 约束名 check （列4=数据 and（or） 列4=数据。。）  
       列5 数据类型 constraint 约束名 references 被参考的表（被参考的列）  
       列 6     数据类型 not null（null）  
）；  
constraint 约束 后面跟的都是约束的名字，如果在新建表，里面有约束的情况下不指定约束名字，oralce会默认给这个约束一个名字

\--产看约束的方法  
1.view  
2.小箭头\*/  
\--创建表money，里面有一个列，是主键约束，没有指定约束名字  
create table money (  
       money number primary key  
);

\--创建表moenyforme 里面一个列 主键约束，指定约束名字（工作习惯）  
\--约束名一般是约束类型\_表名\_列名 方便故障时候排查,约束不能重名  
create table moneyforme (  
       money number constraint pk\_moneyforme\_money primary key  
);

\--创建表atm 里面2个列，一个为唯一约束  
create table atm (  
       loc varchar2(20),  
       money number constraint un\_atm\_money unique  
);

\--创建表baoan里面1个列默认值为交通  
create table baoan (  
       bname varchar2(20) default '交通'  
);

/\*默认值的数据类型一定要和列一样  
字符类型数据必须加单引号  
数字类型直接写  
sysdate

char      定长  
varchar2（10）可变长度\*/

\--检查约束   
\--创建一张表student 里面要求性别为男或者女不能为泰国  
create table student (  
       cname  varchar2(20),  
       sex    varchar2(20) constraint ck\_student\_sex check(sex='男' or sex='女')   --check后面检查的条件只能是本列  
);

\--外键约束  
\--两张表 一张是被参考表，另外一个是参考表  
\--1.被参考表一定要存在并且被参考列一定是主键约束或者唯一约束

\--2.参考表的列与被参考表的列的数据类型一定要一致

\--建立被参考表  
create table bck (  
       bclass varchar2(20) unique  
);

create table ck (  
       id    number,  
       cclass varchar2(20) constraint fk\_ck\_cclass references bck(bclass)  
);

create table all1 (  
       money number constraint pk\_all\_money primary key,  
       money1 number constraint un\_all\_money1 unique,  
       cname varchar2(20),  
       bname varchar2(20) default '交通',  
       sex   varchar2(20) constraint ck\_all\_sex check(sex='男' or sex='女'),  
       cclass varchar2(20) constraint fk\_all\_cclass references bck(bclass)   
);

\--删除列  
alter table 表名 drop column 列名  
alter table STUDENT drop column cname;  
\--修改列的数据类型  
alter table 表名 modify 列 数据类型 （约束）  
alter table STUDENT modify cname number;  
\--删除约束  
alter table表名 drop constraint 约束名  
alter table STUDENT drop constraint CK\_STUDENT\_SEX;  
\--重命名列  
alter table 表名 rename column 旧列名 to 新列名   
alter table STUDENT rename column cname to ccc;  
\--在修改的时候需要注意约束。

create table test1 (  
       bname varchar2(20) default '交通' not null unique  
       );

alter table test1 rename to test2;

\--事务 一件事情的开端与结尾，有开始必然要有结束

\--开端  
\--DML语句 ： Data Manipulation Language 数据操纵语言 使用户能够查询数据库以及操作已有数据库中的数据的计算机语言

\--结束  
\--commit； 提交数据，永久保存  
\--rollback； 回滚

增   
1. insert into 表名 values (值1，值2。。。）  
insert into atm values('usa',100）;  
commit;

insert into atm(loc) values('usa1'）;  
commit;

insert into atm(money) values(200);  
commit;

\--插入数据时，如果列没做约束，该列无数据插入，则默认为空  
\--插入字符类型数据，必须用单引号  
\--日期类型数据 固定日期 to\_date  系统当前日期 sysdate  
create table bbs(  
       cname varchar2(20),  
       id number,  
       bday date  
);

insert into bbs values ('黑哥真自恋',100,sysdate);  
commit;

insert into bbs(bday) values (to\_date('1983-10-12','YYYY-MM-DD'));  
commit;

\--to\_date函数  
insert into bbs(bday)   
values(to\_date('1999-09/09 18:01:02','YYYY-MM/DD HH24:MI:SS'));  
commit;  
\--YYYY 表示年  
\--MM 表示月  
\--DD 表示日  
\--hh24 24小时显示  
\--mi 分钟  
\--ss 秒

\--测试验证主键约束与唯一约束  
create table vi(  
       co number unique,  
       bo number primary key  
);

insert into vi values(1,1);  
commit;  
insert into vi values(2,2);  
commit;

create table sushe(  
       susheming varchar2(10) default'汉'，  
       sushetel number check (sushetel >= 6330000 and sushetel <= 6339999)  
);

\--默认值的测试  
insert into sushe values(null,null);  
commit;

insert into sushe(sushetel) values(6338888);  
commit;

\--检查约束 范围外的值插入  
insert into sushe(sushetel) values(100);  
commit;

\--外键参考约束测试  
\--向被参考表中插入数据  
insert into bck values('leohao');  
insert into bck values('muji');  
commit;

\--向参考表中插入数据  
insert into ck values(10,'muji');  
insert into ck values(10,'moua');  
commit;

alter user scott account unlock;  
\--scott 用户默认密码 tiger

\--查询  
select 列 from 表；  
\--可以是一列或者多列，列之间用，连接，全表查询用\*

select \* from atm t;  
\--别名不是必须存在  
\--查询atm表中所有行所有列的数据 \*表示全表查询

\--单独查询atm表中money列的值  
select money from atm;  
\--查询atm表中money列与loc列的数据，按查询顺序排列  
select money,loc from atm;

\--语法结构  
\--select 列 from 表 where 过滤条件  
\--select后面 from前面是与列有关的，where后面是与行有关的  
\--where后面的过滤条件，列和值数据类型要一致

select \* from atm where loc = 'usa';  
\--查询 atm表中loc为 usa 的所有信息

select \* from atm where money = 200;  
\--查询 atm表中money为 200 的所有信息

select \* from bbs where bday = to\_date('1983-10-12','YYYY-MM-DD');  
\--查询 bbs 表中 bday为 ‘1983-10-12’的所有信息

/\*关系运算符  
and 同时满足条件  
or  满足一个条件  
查询的时候可以用多个条件\*/

\--查询20号部门工资为800的人的所有信息  
select \* from emp where deptno = 20 and sal = 800;

\--在多次使用and和or的时候，建议用括号

\--查询工资为800或者1600的人的所有信息  
select \* from emp where sal = 800 or sal = 1600;

\--where后面的结果 一定是true 或者false，如果结果为true，返回所有满足条件的内容  
\--如果结果为false，没有数据返回，也不会报错  
select \* from emp where 1=1;  
select \* from emp where 1=2;

\--复制表  
create table 表名 as select \* from 表 where 条件  
create table emp2 as select \* from emp;

\--复制表结构  
create table emp3 as select \* from emp where 1=2;

\--简单查询补充  
/\*比较运算符  
> < = >= <=   
!= <> --不等于 \*/

select \* from emp where sal > 800;  
select \* from emp where sal <>800;  
select \* from emp where sal !=800;

\--错误的写法 =< =>  
\--查询工资范围在1500到2000的人的姓名  
select ename from emp where sal >= 1500 and sal <= 2000;

\--between and 区间范围 包括两个点  
select \* from emp where sal between 1500 and 2000;

\--in any all

\--in 在。。。里面  
\--工资在800，1500，2000的人所有信息  
select \* from emp where sal in (800,1500,2000);  
\--未查询到in括号内的值，不予返回

\--any 任何一个  
select \* from emp where sal > any (800,1500,2000);  
select \* from emp where sal > 800 or sal > 1500 or sal > 2000;  
select \* from emp where sal >800;

\--all 所有  
select \* from emp where sal > all (800,1600,2000);  
select \* from emp where sal >800 and sal > 1600 and sal > 2000;  
select \* from emp where sal > 2000;

\--模糊查询  
\--like %代表任意字符  \_ 代表一个字符  
\--条件不完整，一般用于字符类型  
\--只记得姓不记得名字

\--查询以SCO开头的人的所有信息  
select \* from emp where ename like 'SCO%';  
\--SCO区分大小写

\--查询以TT结尾的人的所有信息  
select \* from emp where ename like '%TT';

\--查询中间为O的人的所有信息  
select \* from emp where ename like '%O%';

\--查询名字为5个字符的人的所有信息  
select \* from emp where ename like '\_\_\_\_\_';

\--查询O后面带有两个字符的人的所有信息  
select \* from emp where ename like '%O\_\_';

\--空值 NULL  
\--空值 就是一个未知数 有可能是8 也有可能是100万 也可能是1986  
\--空值 不能用于比较  
select \* from emp where sal = null;  
select \* from emp where null+1 > NULL;   
select \* from emp where 1 > NULL;

\--查询空值 is null 或者 is not null  
select \* from emp where comm is null;  
select \* from emp where comm is not null;

\--空值处理函数nvl 仅用于显示  
\--nvl 判断该列是否为空，如果为空设置成什么  
\--不在数据库中做修改  
select \* from emp;  
select comm,nvl(comm,100000) from emp;

\--函数 数据库中的数据不会变化，用于显示  
\--upper(列） 把数据变成大写  
\--lower(列） 把数据变成小写  
\--initcap(列） 把数据首字母奕大写  
select ename,upper(ename) from emp;  
select ename,lower(ename) from emp;  
select ename,initcap(ename) from emp;

\--查询scott用户的所有数据  
select \* from emp where lower(ename) = 'scott';  
select \* from emp where upper(ename) = 'SCOTT';  
select \* from emp where INITCAP(ename) = 'Scott';

\--修改数据  
\--update 表名 set 列 = 值 where 条件

\--修改emp2表中名字为SCOTT的人，工资改为100  
update emp2 set sal = 100 where ename = 'SCOTT';  
commit;

\--如果不用WHERE字句 ，那么默认修改的就是整个列的值  
update emp2 set sal = 20;  
commit;

\--删除表  
drop table 表名

\--删除数据  
\--delete 可以删除部分数据 DML语句 ，可以恢复  
\--delete 表名 where 条件

delete emp2 where ename = 'SCOTT';  
commit;

\--truncate 截断 DDL语句  只能全表删除，数据不可以恢复  
truncate table emp3;  
delete table 表名

/\*dml：数据库操作语句，常见的增、删、改、查(最重要)  
事务：通俗理解就是做一件事情的过程，这个过程有两种结果：成功、失败，在数据库中发出一条dml语句即开启一个事务，开启事务就一定要结束事务，  
      结束事务的两种方式：  
          a1)、commit，提交事务，所有的操作都生效，操作的结果直会永久保存在数据库中  
          a2)、rollback，回滚事务，所有的操作都不会生效，操作的结果直接扔掉，不会永久保存到数据库中

基本sql(结构化查询语言)语言：ddl、dml、dcl、tcl  
dml:数据库操作语句   
          insert  
          delete  
          update  
          select(重点）  
            
DDL：数据库定义语句   
          create  
          alter  
          drop  
          rename  
          truncate  
            
DCL：数据库控制语句   
          grant  
          revoke  
   
TCL：事物控制语句   
          commit  
          rollback  
          savepoint \*/  
            
\--简单查询  
select \* from emp where ename = 'SCOTT';  
select \* from emp where sal > 800;  
select \* from emp where hiredate > to\_date('1981/04/02','YYYY-MM-DD');  
select \* from emp where sal > any(100,600,1600);    
select \* from emp where sal > all(100,600,1600);          
select \* from emp where sal in (800,1500);  
select \* from emp where ename like 'AL%';

create table mm (  
       mm number  
);

create table nn (  
       nn number  
);

insert into mm values(1);  
insert into mm values(2);  
insert into mm values(3);  
insert into nn values(1);  
insert into nn values(2);  
insert into nn values(4);  
commit;

\--内联查询 两张表都有的数据  
\--内联的写法

\--1.  
\--select 列 from 表1，表2 where 表1.列 = 表2.列  
select \* from mm,nn where mm.mm = nn.nn;

\--2.  
\--select 列 from 表1 inner join 表2 on (表1.列 = 表2.列）  
select \* from mm inner join nn on (mm.mm = nn.nn);

\--假如有三张表用内联，用两个关联来进行处理  
\--举个例子 emp dept 表  
\--查询20号部门的员工姓名和部门位置  
select e.ename,d.loc  
       from emp e,dept d  
       where e.deptno = d.deptno --建议连接   
       and e.deptno = 20;        --条件过滤  
         
select emp.ename,dept.loc   
       from emp,dept   
       where emp.deptno = dept.deptno  
       and emp.deptno = 20;  
         
\--查询 30号 部门的人名 工资 部门号 部门名字

/\*         
\--1.做多表查询的时候，首先分析需要查询的列有哪些，分别来源于哪些表  
       30号部门        emp dept  
       人名            emp  
       工资            emp  
       部门号          emp  
       部门名字        dept  
\--确定使用两张表，必须建立关联关系 \*/

/\*  
2.分析多表的关系  
SCOTT>desc emp  emp表  
 名称                                      是否为空? 类型  
 EMPNO                                     NOT NULL NUMBER(4)  
 ENAME                                              VARCHAR2(10)  
 JOB                                                VARCHAR2(9)  
 MGR                                                NUMBER(4)  
 HIREDATE                                           DATE  
 SAL                                                NUMBER(7,2)  
 COMM                                               NUMBER(7,2)  
 DEPTNO                                             NUMBER(2)  
 SCOTT>desc dept  
 名称                                      是否为空? 类型  
 ----------------------------------------- -------- --------------------

DEPTNO                                    NOT NULL NUMBER(2)  
 DNAME                                              VARCHAR2(14)  
 LOC                                                VARCHAR2(13)  
\*/

select \* from emp,dept   
       where emp.deptno = dept.deptno; --表建立关系

3.查询需要的列  
select emp.ename, emp.sal, dept.deptno, dept.dname  --查询所需要的列  
  from emp, dept                                    --从哪些表中查询  
 where emp.deptno = dept.deptno        --建立关联  
   and dept.deptno = 30;                        --条件过滤

select e.ename,e.sal,e.deptno,d.dname  
       from emp e,dept d  
       where e.deptno = d.deptno  
       and e.deptno = 30;