cmd 下：

sqlplus /nolog

//连接

--sqlplus 连接数据库

请输入用户名

输入：用户名/密码@IP地址/数据库名称（实例名）。

1）执行sql文件（可以把需要执行的sql放一个文件中 @@文件名）

输入 @'文件路径'

conn sys/sntsoft as sysdba

//启动关闭

startup open

shutdown immediate

//创建用户授权

create user 用户名 identified by 口令 account lock|unlock

grant 角色|权限 to 用户（角色） 角色|权限 ：connect resource dba create session table

//回收权限

revoke 角色|权限 from 用户（角色）

//修改密码

alter user 用户名 identified by 新密码

//修改锁定状态

alter user 用户名 account lock|unlock

//创建约束

alter table 表名 add constraint 约束名 约束内容

使用sysdba账号 登陆后 可以修改其他账号密码

运行 cmd 按如下输入命令

sqlplus / as sysdba ---------以sys登陆 超级用户（sysdba）

alter user 用户名 account unlock; --------- 解除锁定(必须带“;”号)

alter user 用户名 identified by 密码; -------------修改密码

主键 primary key

外键 foreign key

//创建学生表

create table INFOS

(

stuid varchar(7) not null,

stuname varchar(10) not null,

gender varchar(2) not null,

age number(2) not null,

seat number(2) not null,

enrolldate date,

stuaddress varchar(50) default '地址不详',--默认值

classno varchar(4) not null

);

alter table infos add constraint pk_infos primary key(stuid);--创建主键约束

alter table infos add constraint ck_infos_gender check(gender='男'or gender='女');--check 约束

alter table infos add constraint un_stuname unique(stuname);--唯一约束 索引

//创建成绩表

create table scores

(

id number,

term varchar(2)

);

alter table scores add constraint fk_scores_infos_stuid foreign key(stuid) references infos(stuid）;--外键约束

--拷贝表

create table table_name as select * from Source_table where 1=1;

--复制表结构

create table table_name as select * from Source_table where 1 <> 1;

drop table infos;--删除表

select distinct * from table;--不显示重复行

like '-%' --模糊查询

_表示任意一个字符

%表示零个或多个任意字符

---集合操作

select * from infos union all select * from infos2;--并集所有（显示重复行）

intersect 两个表共有的记录（去掉重复的行）。

minus 补集 返回第一个减第二个表剩余的记录去掉重复的行。

子查询

select * from infos e,infos2 t where e.stuid=t.stuid and e.age>20;--内连接

select * from infos e,infos2 t where e.stuid(+)=t.stuid and e.age>20;--外连接

 

create alter drop 操作数据库对象 （表 用户 同义词 序列 视图 索引）

----view 视图：

CREATE OR REPLACE VIEW 视图名 AS select * from table;

直接用create 创建 如果已存在，则会报错。

同义词

create synonym myemp for emp;

序列

create sequence my seq

minvalue 1

start with 1

maxvalue 50

increment by 2

cycle

cache 30;//序列seq 最小值为1 从1开始 最大值为50 每次增长2 循环开始 缓存30个序列号。

访问序列的当前值和下一个值：

select myseq.currvalue from dual;

select myseq.nextvalue from dual;

修改 删除 alter drop

create unique index index_name on table_name(lie_name);

--创建唯一索引 unique（唯一）

表空间：

create tablespace 空间名称

datafile 'd:/a.ora' size 10M

extent mangement local

uniform size 5M

为某一用户指定默认表空间

create user acong identfied by acong default tablespace myspace

 

 

select * form biaoming for update

select t.*, t.rowid from OSM_T_MR_BOOKS t --给编辑权限，最后F10上传。

--增

insert into osm_t_mr_books (id, organization_code, name, reader_id, route_id, read_cycle_id, type_id, remark, created_time, creator_id, is_valid)

values（'10061','0004','chao','10060','10082','1','1','111','2016-12-01 16:52:00','10060','1')

--删

delete from osm_t_mr_books where id=10061

--改

update osm_t_mr_books set name='抄表册' where id=10060

--查

select name from osm_t_mr_books where id=10061

tnsping 服务名。 查看数据库服务器的配置在哪个地方。

导入

imp system/sntsoft@sntsoft_251 file=lujin fromuser=olduser touser=newuser log=lujing

导出

exp system/sntsoft@sntsoft_251 file=lujin owner=s8osmtc

order by id desc 排序、倒序。

--修改日期时 把字符串转为日期型数据

to_date('2015-01-01','yyyy-mm-dd')

rwoid 物理地址

rownum 行号

<> 表示不等于

跟踪sql

alter system flush shareD_pool; ---清除运行池

select * from v$sql a

where a.PARSING_SCHEMA_NAME='S8OSM' ;---取程序运行sql

select dbms_sqltune.extract_binds(bind_data) ---bind

from v$sql where sql_id = '5k3p5pa1ktstv';

查询某个字段重复行

select * from test where name in

(select name from test group by name having COUNT(*)>1);

查询某个字段在哪些表中有：

select table_name from dba_tab_columns where column_name='字段名（大写）'; system用户登录

--查看被锁的表

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

--查看那个用户那个进程照成死锁

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

--查看连接的进程

SELECT sid, serial#, username, osuser FROM v$session;

--查询是什么SQL引起了锁表的原因

select l.session_id sid,

s.serial#,

l.locked_mode,

l.oracle_username,

s.user#,

l.os_user_name,

s.machine,

s.terminal,

a.sql_text,

a.action

from v$sqlarea a, v$session s, v$locked_object l

where l.session_id = s.sid

and s.prev_sql_addr = a.address

order by sid, s.serial#;

--解锁杀掉进程 sid,serial#

alter system kill session'210,11562';

--锁表

LOCK TABLE tablename IN EXCLUSIVE MODE;

--解锁

rollback/commit

dual：

　　1、查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual;

　　2、用来调用系统函数

　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间

　　select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名

　　select SYS_CONTEXT('USERENV','language') from dual;--获得当前 locale

　　select dbms_random.random from dual;--获得一个随机数

　　3、得到序列的下一个值或当前值，用下面语句

　　select your_sequence.nextval from dual;--获得序列your_sequence的下一个值

　　select your_sequence.currval from dual;--获得序列your_sequence的当前值

　　4、可以用做计算器 select 7*9 from dual;

　　------

 

---删除重复行

Delete table t where t.rowid!=(select max(t1.rowid) from table1 t1 where t1.name=t.name)

---给sql语句取个别名

with 别名 as （sql语句）

 

---not exists

exists （sql 返回结果集为真）

not exists (sql 不返回结果集为真）

--join

LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。

select xmid,sql,sqlid from ptb_fpsql a join ptb_fpxm_sql b on a.id=b.sqlid;

 

--让有某个重复字段，只显示一行数据

例子：重复字段为fplb。

1.select a.* from ptb_fp a,(select max(id) as id,fplb from ptb_fp group by fplb) b

where a.id=b.id and a.fplb=b.fplb;

2.select distinct(fplb) from test1;

使用 {} 可以明确指示大括号中的内容是查询表达式，而不是常量

--修改字段类型

ALTER TABLE tableName modify(columnName 类型);

--增加一个字段

ALTER TABLE tablename add columnName 类型;

--删除字段

alter table tablename drop columnname;

--修改字段的名称

ALTER TABLE table_name RENAME COLUMN 原名称 to 新名称;

--修改表的名称

rename t1 to t_1;

----Oracle | 给表和字段添加注释

comment on column 表名.字段名 is '注释内容';

comment on table 表名 is '注释内容';

---select into

select * into newtable from oldtable;sqlserver的语法。

create table newtable as select * from oldtabel;oracle语法。

--foreign key

create table table_name(

columns_name int FOREIGN KEY REFERENCES table(columns_name)

)

--default 默认值

default '0'

--index 索引

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

---functions

SELECT function(列) FROM 表

函数

---NVL( string1, replace_with)两个表达式的数据类型一定要相同

如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

--NVL2(E1, E2, E3)

如果E1为NULL，则函数返回E3，否则返回E2。

NULLIF (expr1, expr2)

如果expr1和 expr2相同就返回空，否则返回expr1

COALESCE (expr1, expr2, ..., exprn)

返回括号内第一个非空的值。

grouping sets 增强版的group by 可以指定多个选项。

--not exists

not exists (sql 不返回结果集为真） 前面要有where

|| 连接符。

oracle 存储过程的基本语法

1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字

(

参数1 IN NUMBER,

参数2 IN NUMBER

) IS

变量1 INTEGER :=0;

变量2 DATE;

BEGIN

END 存储过程名字

--tnsping 实例名

---lsnrctl status 命令查看监听器状态

---v$

ORACLE 系统动态性能视图

---查询出oracle所有关键字

select * from v$reserved_words order by keyword asc;

---查看表空间使用情况

select b.file_id 文件ID,

　　b.tablespace_name　　表空间,

　　b.file_name　　　　　物理文件名,

　　b.bytes　　　　　　　总字节数,

　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,

　　sum(nvl(a.bytes,0))　　　　　　　　剩余,

　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比

　　from dba_free_space a,dba_data_files b

　　where a.file_id=b.file_id

　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes

　　order by b.tablespace_name

---查看当前用户的权限

select * from session_privs;

 

---group by语句

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

--不是group by 表达式

1、GROUP BY 和 ORDER BY一起使用时，ORDER BY要在GROUP BY的后面。

2、ORDER BY 的字段在GROUP BY 后面必须有

3、在select需要查询的语句中选中的字段，必须出现在group by子句中

4、在有组函数的SELECT中，不是组函数的列，一定要放在GROUP BY子句中。

---组函数

Avg平均

Sum求和

Max最大

Min最小

Count计数

---having 语句

Having是在结果中再次筛选。Having一定得出现在group by 子句得后面。不能独立存在。

---子查询

子查询返回多行，用=不可以，得用in。

子查询返回多列，所以对比的列也要匹配。

---any 表示任意的 小于any小于最大，大于any大于最小

all 表示所有的 小于all小于最小，大于all大于最大

---查看当前用户所有的表

select * from tab;

---查找为null的字段

用is null 不能用=null;

---函数

lower小写 upper 大写 initcap 首字母大写 concat连接字符在一起

substr(字符串,m,n),m是从第几个字符开始，如果为负的意思是从后边的第几个开始。N是数多少个，如果不说就是一直到字符串的结尾。

instr（字符串，‘a’）求子串在父串中的位置，0表示没有在父串中找到该子串。

----操作数字的函数

ceil 向上取整 abs 取绝对值 mod 取余数 round四舍五入 trunc 全部去掉

FM消除前置的零和空格。 别名的本质使用方法是使非法的合法化。

to_char(xxxxx,'fm99999999.00') 保留两位小数。

--case

select name1 ,id1,id2,

case id1

when 1 then 2*id1

when 2 then 3*id1

-- else 5

end id2

from testtable;

---显示回收站的信息

SQL> show recyclebin

---将回收站的表还原

FLASHBACK TABLE t2 TO BEFORE DROP;

--高级分组

rollup cube

pl/sql块

DECLARE

变量声明部分，可以没有

Begin

逻辑处理执行部分，到end结束，必须有

EXCEPTION

错误处理部分，可以没有

End;

:= 为赋值，=为逻辑判断，判断是否相等。

Null的逻辑运算真值表

Ture and null 结果为 null

Flase and null结果为 flase

变量的命名规则

在不同的模块中，变量可以重名

变量的名称不应该和模块中引用的列的名称相同

变量名称应该有一定的可读性

变量的作用范围

外部模块变量可以传到内部模块

内部模块的变量不会影响外部

数据库中的两种属性类型：

%rowtype 一行

前缀为表的名称

内部域的属性为表中列的数据类型

域的名称为列的名称

便于存储表的一行

declare

myemp infos%rowtype;

begin

select * into myemp from infos where stuid=3;

dbms_output.put_line(myemp.stuname);

end;

%type 某列的类型

声明一个变量和某列数据类型相同

声明一个变量和另外一个变量数据类型一致

减小程序的无效的可能性，可以不知道列的数据类型，定义一个与之相同的变量。

查询年龄为20的学生 如果他的学号大于0，则把他的年龄设为50,否则设为10.

declare

newstu infos.stuid%type;

begin

select stuid into newstu from infos where age=20;

if newstu>0 then

update infos set age=50 where age=20;

else

update infos set age=10 where age=20;

end if;

commit;

end;

case语式

例子：

declare

v_grade char(1):=upper('&p_greade');

begin

case v_grade

when 'A'then

dbms_output.put_line('111');

else

dbms_output.put_line('1212');

end case ;

end;

循环结构：

loop

死循环

end loop

例子：

declare

counter number(3):=0;

sumresult number:=0;

begin

loop

counter:=counter+1;

sumresult:=sumresult+counter;

if counter>=100 then

exit;

end if;

end loop;

dbms_output.put_line('result is:' ||to_char(sumresult));

end;

 

--查询数据库预先定义的错误

select text from dba_source where name='STANDARD'

AND ROWNUM<100 AND TEXT LIKE '%EXCEPTION_INIT%';

块 ---函数 ----存储过程 -------包体

---查看表空间

select tablespace_name,file_name,ceil(bytes/1024/1024) mb

from dba_data_files order by 1;

----decode 函数

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN

　　　　RETURN(翻译值1)

ELSIF 条件=值2 THEN

　　　　RETURN(翻译值2)

　　　　......

ELSIF 条件=值n THEN

　　　　RETURN(翻译值n)

ELSE

　　　　RETURN(缺省值)

END IF

--sign 函数

当x>0，sign(x)=1;

当x=0，sign(x)=0;

当x<0， sign(x)=-1；

---merge into 只能用insert或者update

有一个表A和表B，如果满足条件，则更新表A中c的值。

merge into A

using B

on (A.a=B.a and A.b=B.b)

when matched then

update set A.c=B.c

--回车换行符

返回 Stringchr（65）=A asscll码

chr(10)是换行符，

chr(13)是回车，

---is和as

在存储过程（PROCEDURE）和函数(FUNCTION)中没有区别，

在视图（VIEW）中只能用AS不能用IS，

在游标(CURSOR)中只能用IS不能用AS。

---undo信息

1.一致性 回滚事务 实例恢复

--- truncate 和delete

delete 可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据,一般我们把delete 操作收作删除表,而truncate操作叫作截断表.

truncate操作与delete操作对比

truncate 效率高

---查询出存储过程的定义语句

select text from user_source WHERE NAME='TEST_COUNT'; (大小写敏感）

----查询存储过程test_paramINOUT的参数

DESCRIBE test_paramINOUT; 在命令行中执行

---查看当前连的服务

show parameter service;

---删除某个用户的所有数据

drop user username cascade;

select username from dba_users;

---rank within gourp

for in loop ---隐身游标

---语句

DML 操做 select

DDL 定义 create

DCL 控制 commit

SELECT tablespace_name 表空间, sum(blocks * 8192 / 1000000) 剩余空间M

FROM dba_free_space

GROUP BY tablespace_name;----查表空间剩余量

Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name

alter database tempfile 'F:\SHEYANG\PTBTEMP.ORA' resize 1G;--扩展临时表空间大小

select file_name , tablespace_name from dba_data_files; ---查看表空间路径

select 'alter index '||index_name||' rebuild;' from user_indexes X where x.table_name

IN ('PTB_FYMX')---查找所有索引 重建索引

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;---查询临时表空间文件路径