Oracle\SQL Server等及其他基本语句写法

Oracle\SQL  Server等及其他基本语句写法

目录

一．Excel相关 1

1.Excel中写脚本范例： 1

2.提取字节 2

3. 提取单元格内字符 2

4.VLOOKUP函数： 2

二．SQL语句汇总 2

1.建表： 2

2.增 3

3.删 4

4.查 6

5.改 23

6.Alter的应用 24

三．数据库备份与恢复脚本 26

1. Oracle： 26

2.MongoDB： 27

四．其他 27

1.判断某个表是否存在 27

2.恢复oracle 中误删、误修改的表 或delete 删掉的数据恢复 28

3.Oracle数据库用户密码过期时间不过期调整 28

 

 

 

一．Excel相关

1.Excel中写脚本范例：

="update T_GOODS set ftype_id=(select fid from t_goodstype where fname='"&J2&"') where fid='"&B2&"';"

提交：commit;

 

2.提取字节

取字节长度函数：LENB(A1)

取字符串长度函数：LEN(A1)

取字符串中间的数字：=MID(A4,6,3)——6代表第六位数，3代表长度

 

3. 提取单元格内字符

提取单元格左侧数字函数（右侧换RIGHT）：=LEFT(A1,2*LEN(A1)-LENB(A1))

提取汉字： =LEFT(A2,LENB(A2)-LEN(A2))

 

4.VLOOKUP函数：

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。

表述就是VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）

 

二．SQL语句汇总

1.建表：

1.1.Create  table  TableName

(

列名  字段类型  约束，

UserID  int  primary key，

UserName  varchar(50),

UserAge  int

)

1.2.查询表A中的数据，同时新建一个表#temp,将查询结果插入新建表#temp中

select   *   into   #temp   from   A

select distinct   *   into   #temp   from   A （将重复数据新建一个表存放）

2.增

2.1.通用增加inset  nto

Insert  into  TableName  （Column1,Column 2,……） values （’1001’，‘张三’）

或者（每一列都添加数据）：

Insert  into  TableName  values （’1001’，‘张三’，‘18’）

2.2.（目前只测试了SQL Server数据库）批量新增：

首先是前面说过的方法：   

复制代码代码如下:

INSERT INTO MyTable(ID,NAME) VALUES(1,'123');
INSERT INTO MyTable(ID,NAME) VALUES(2,'456');
INSERT INTO MyTable(ID,NAME) VALUES(3,'789');

第二种方法，使用UNION ALL来进行插入操作：  

复制代码代码如下:

INSERT INTO MyTable(ID,NAME)
SELECT 4,'000'
UNION ALL
SELECT 5,'001'
UNION ALL
SELECT 6,'002'

是不是要比第一种方法简单点，据说要比第一种要快！

第三种方法，是SQL Server2008以上特有的。

复制代码代码如下:

INSERT INTO MyTable(ID,NAME)
VALUES(7,'003'),(8,'004'),(9,'005')

2.3.复制表（如下，表a是数据库中已经存在的表，b是准备根据表a进行复制创建的表）

1、只复制表结构的sql

create table b as select * from a where 1<>1 

2、即复制表结构又复制表中数据的sql

create table b as select * from a 

3、Oracle复制表的制定字段的sql

create table b as select row_id,name,age from a where 1<>1 

前提是row_id,name,age都是a表的列

4、复制表的指定字段及这些指定字段的数据的sql

create table b as select row_id,name,age from a 

以上语句虽然能够很容易的根据a表结构复制创建b表，但是a表的索引等却复制不了，需要在b中手动建立。

5、insert into 会将查询结果保存到已经存在的表中

insert into t2(column1, column2, ....)select column1, column2, .... from t1 

3.删

3.1.Drop  table  表名

• drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
• drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
• drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

 

3.2.truncate   table  表名

• 1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
• 2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
• 3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
• 4、truncate table不能用于参与了索引视图的表。

 

3.3.Delete 表名 where 条件

• 1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
• 1、在速度上，一般来说，drop> truncate > delete。
• 2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
• 3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；如果和事务有关，或者想触发trigger，还是用delete；如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

3.4.总结：

 

 

3.5.删除表中的列：点击此处跳转

4.查

4.1.查询目前数据库中该实例下的所有表：

select * from tabs

4.2.连接查询：

（首选）Select * from 表A  A  left  join  表B  B  on  A.ID=B.ID  left  join  表C  C  on  A.name=C.name  where  条件

或者：

Select * from 表A，表B  B ，表C  C  where  A.ID=B.ID  and  A.name=C.name

4.3.其余查询：

4.3.1.查询表中前两条数据：

select * from t_goods where rownum <=2（Oracle写法）

select TOP 2 * from t_goods（Sql Server写法）

4.3.2.（SQL Sever）随机取出三条数据：

select top 3 * from A order by newid()

4.3.3.获取行ID

4.3.3.1.Oracle写法：

select D.*,rownum rn from departments D（rn就是行ID可以直接用rownum）

4.3.3.2.SQL Server写法：

select *,ROW_NUMBER() over(order BY getdate()) rn from A（按照行数据创建的时间顺序，不对原数据行顺序只多加一列显示行ID）

select *,ROW_NUMBER() over(order BY ID) rn from A（以表中的ID对表数据进行order by排序之后得到的行ID）

4.3.4.排序查询ID，取前几条数据

4.3.4.1.查询不排序，直接取出表中第二行数据

select * from (select D.*,rownum rn from departments D) where rn=2（Oracle写法）

select * from (select *,ROW_NUMBER() over(order BY getdate()) rn from ) T where rn=2（Sql Server写法）

4.3.4.2.（Oracle）查询ID从小到大排序的第二条数据
select * from (select T.*,rownum rn from (select D.* from departments D   order by D.dept_id desc) T) where rn=2

4.3.4.3.（Oracle）查询第2到第5条数据：
select * from (select T.*,rownum rn from (select D.* from departments D order by D.dept_id desc) T) where rn>=2 and rn <=5  (where后也可写作：rn between 2 and 5)

4.3.5.SQL查询是否等于空时

 “Name  is  null”、“Name  not  is  null”，优于“Name=null”、”Name<>null”

4.3.6.查询重复数据和删除

4.3.6.1．SQL查询并删除重复值，仅保留一条数据：

select * from B where id in (select ID from B group by ID having count(id)>1) and rowid not in (select min(rowid) from B group by ID having count(id)>1)
先查询再删除
delete B where id in (select ID from B group by ID having count(id)>1) and rowid not in (select min(rowid) from B group by ID having count(id)>1)

4.3.6.2．SQL查询重复值：

select count(*) from temp1 group by a,b having count(*)>1

4.3.6.3．SQL去重复显示：

select distinct * from temp1

4.3.6.4．SQL查询一个表中两列重复值：

select d.dept_name,d.dept_leader from departments D group by d.dept_name,d.dept_leader having count(*)>1

4.3.6.5．SQL删除重复值：

delete temp1 where a in (select a from temp1 group by a,b having count(*)>1)

4.3.7.SQL查询字符串中指定的字符：

Select Replace(字段名(或者列名),'指定字符','替换字符') From 表名

4.3.8.SQL模糊匹配两表两列的值：

select * from temp1 T,temp2 C where c.a like '%'||t.b||'%'

4.3.9.SQL中case when的用法：

4.3.9.1．case when简单写法

select id,num,datetime,
case 
when num >=1000 and num<2000 then '1'
when num >=2000 and num<3000 then '2'
when num >=3000 and num<4000 then '3'
when num >=4000 and num<5000 then '4'
when num >=5000 and num<6000 then '5'
else '6' end
from B

4.3.9.2．case when深入案例（Case when案例：

 

--Oc_date         result

 

--20090509          胜

--20090509          胜

--20090509          负

--20090509          负

--20090510          胜

--20090510          负

--20090510          负

--如果要生成下列结果，该如何写Sql语句？

--date        胜     负

 

--20090509    2       2

--20090510    1       2

select

    T.Oc_date

    ,sum(case when T.result='胜' then 1 else 0 end) 胜

    ,sum(case when T.result='负' then 1 else 0 end) 负

from

    Casewhen T

group by

T.Oc_date

或者：

select T.Oc_date,

count(case when T.result='胜' then 1 end) 胜,

count(case when T.result='负' then 1 end) 负

from Casewhen T group by T.Oc_date

4.3.10.分析函数求和：

Select nvl(t.id,'sum'),sum(t.num)
from B t
group by rollup(t.id)

注：select ID,sum(num) from B group by ID
union all
select 'sum',sum(num) from B也能达到效果

 

 

 

4.3.11.查询语句给字段加单引号

1.单纯加单引号：

select chr(39)||name||chr(39) from users ;
或者
select ''''||name||'''' from users ;

如果后面需要加逗号，写为：

select ''''||name||''',' from users ;

2.加其他内容

select 'alter table '||table_name||' allocate extent;' from user_tables

 

 

4.3.12. 比较一行的最大值或最小值

Oracle比较一列的最大值或者最小值，我们会不假思索地用MAX和MIN函数，但是对于比较一行的最大值或最小值，很多人都不知道有ORACLE也有内置函数实现这个功能：COALESCE / GREATEST /LEAST.

1. COALESCE 返回该表达式列表的第一个非空value。

    格式： COALESCE(value1, value2, value3, ...)

    含义： 返回value列表第一个非空的值。

               value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

    EXAMPLE: select coalesce (1, null, 2 ) from dual ; -- 返回1

                     select coalesce ( null, 2, 1 ) from dual ; -- 返回2

                     select coalesce (t.empno, t.mgr ) from  scott.emp t ; -- 效果类似 NVL( t.empno, t.mgr )

2. GREATEST 返回值列表中最大值

    格式： GREATEST(value1, value2, value3, ...)

    含义： 返回value列表最大的值。

               value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

               当value值列表中有一个为NULL，则返回NULL值。

    EXAMPLE: select greatest (1, 3, 2 ) from dual ; -- 返回3

                     select greatest ( 'A', 'B', 'C' ) from dual ; -- 返回C

                     select greatest (null, 'B', 'C' ) from dual ; -- 返回null

                     select greatest (t.empno, t.mgr ) from  scott.emp t ; -- 返回empno和mgr 较大值

3. LEAST 返回值列表中最小值

    格式： LEAST(value1, value2, value3, ...)

    含义： 返回value列表最小的值。

               value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

               当value值列表中有一个为NULL，则返回NULL值。

    EXAMPLE: select least (1, 3, 2 ) from dual ; -- 返回1

                     select least ( 'A', 'B', 'C' ) from dual ; -- 返回A

                     select least (null, 'B', 'C' ) from dual ; -- 返回null

                     select least (t.empno, t.mgr ) from  scott.emp t ; -- 返回empno和mgr 较小值

 

4.3.13.NVL及NVL2的用法

4.3.13.1.NVL

NVL是Oracle PL/SQL中的一个函数。它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

注意事项：string1和replace_with必须为同一数据类型，除非显式的使用TO_CHAR函数进行类型转换。

例：NVL(TO_CHAR(numeric_column), 'some string') 其中numeric_column代指某个数字类型的值。

例：nvl(yanlei777,0) > 0

NVL(yanlei777, 0) 的意思是 如果 yanlei777 是NULL， 则取 0值

通过查询获得某个字段的合计值，如果这个值为null将给出一个预设的默认值

例如：

select nvl(sum(t.dwxhl),1)

from tb_jhde t

就表示如果sum(t.dwxhl) = NULL 就返回 1

另一个有关的有用方法

declare i integer

select nvl(sum(t.dwxhl),1) into i from tb_jhde t where zydm=-1这样就可以把获得的合计值存储到变量

i中，如果查询的值为null就把它的值设置为默认的1

orcale中:

select nvl(rulescore,0) from zwjc_graderule where rulecode='FWTD';

如果记录中不存在rulecode ='FWTD'的数据.则查不出数据.

select nvl(rulescore,0) into rule_score from zwjc_graderule where rulecode='FWTD';会报查不到数据的错

select nvl(sum(rulescore),0) from zwjc_graderule where rulecode='FWTD';

如果记录中不存在rulecode ='FWTD'的数据.还是可以得到一行列名为nvl(rulescore,0),值为0的数据.

select nvl(sum(rulescore),0) into rule_score from zwjc_graderule where rulecode='FWTD'; 不会报错

4.3.13.2.NVL2

Oracle在NVL函数的功能上扩展，提供了NVL2函数。

NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，否则返回E2。

 

4.4.行列转换

4.4.1.Oracle行列转换

4.4.1.2行转列

写法：select * from test1 pivot (max(y) for w in ('sw1','sw2','sw3','sw4','sw5','fw'));

 

 

 

 

 

 

 

 

4.4.1.2.列转行：

select * from tbname;

 

 

 

 

 

行转列之后的数据：

select pud, listagg(ud, ',') within group(order by null) as ud

  from tbname

 group by pud;

 

 

 

 

 

4.4.2.Sql server 行列转换

4.4.2.1.行转列

1、测试数据准备

 

CREATE  TABLE [StudentScores]

(

   [UserName]         NVARCHAR(20),        --学生姓名

   [Subject]          NVARCHAR(30),        --科目

   [Score]            FLOAT,               --成绩

)

 

INSERT INTO [StudentScores] SELECT '张三', '语文', 80

INSERT INTO [StudentScores] SELECT '张三', '数学', 90

INSERT INTO [StudentScores] SELECT '张三', '英语', 70

INSERT INTO [StudentScores] SELECT '张三', '生物', 85

INSERT INTO [StudentScores] SELECT '李四', '语文', 80

INSERT INTO [StudentScores] SELECT '李四', '数学', 92

INSERT INTO [StudentScores] SELECT '李四', '英语', 76

INSERT INTO [StudentScores] SELECT '李四', '生物', 88

INSERT INTO [StudentScores] SELECT '码农', '语文', 60

INSERT INTO [StudentScores] SELECT '码农', '数学', 82

INSERT INTO [StudentScores] SELECT '码农', '英语', 96

INSERT INTO [StudentScores] SELECT '码农', '生物', 78

 

 

 

 

 

2、行转列sql

 

SELECT * FROM [StudentScores] /*数据源*/

AS P

PIVOT

(

    SUM(Score/*行转列后 列的值*/) FOR 

    p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)

) AS T

 

执行结果：

 

 

 

 

4.4.2.2.列转行

1、测试数据准备

 

CREATE TABLE ProgrectDetail

(

    ProgrectName         NVARCHAR(20), --工程名称

    OverseaSupply        INT,          --海外供应商供给数量

    NativeSupply         INT,          --国内供应商供给数量

    SouthSupply          INT,          --南方供应商供给数量

    NorthSupply          INT           --北方供应商供给数量

)

 

INSERT INTO ProgrectDetail

SELECT 'A', 100, 200, 50, 50

UNION ALL

SELECT 'B', 200, 300, 150, 150

UNION ALL

SELECT 'C', 159, 400, 20, 320

UNION ALL

 

 

 

 

 

2、列转行的sql

 

SELECT P.ProgrectName,P.Supplier,P.SupplyNum

FROM 

(

    SELECT ProgrectName, OverseaSupply, NativeSupply,

           SouthSupply, NorthSupply

     FROM ProgrectDetail

)T

UNPIVOT

(

    SupplyNum FOR Supplier IN

    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )

) P

 

执行结果：

 

 

 

 

 

4.5. Oracle中substr()函数详解： 

1）substr函数格式   (俗称：字符截取函数)

　　格式1： substr(string string, int a, int b);

　　格式2：substr(string string, int a) ;

解释：

    格式1：
        1、string 需要截取的字符串 
        2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
        3、b 要截取的字符串的长度

    格式2：
        1、string 需要截取的字符串
        2、a 可以理解为从第a个字符开始截取后面所有的字符串。

2）实例解析

 

 1、select substr('HelloWorld',0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符

 2、select substr('HelloWorld',1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符

 3、select substr('HelloWorld',2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符

 4、select substr('HelloWorld',0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。

 5、select substr('HelloWorld',5,3) value from dual; //返回结果：oWo

 6、select substr('Hello World',5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)

 7、select substr('HelloWorld',-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色 第三个注解）

 8、select substr('HelloWorld',-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色 第三个注解）

 9、select substr('HelloWorld',-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）

10、select substr('HelloWorld',-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）

 

    （注：当a等于0或1时，都是从第一位开始截取(如：1和2)）
    （注：假如HelloWorld之间有空格，那么空格也将算在里面(如：5和6)）
    （注：虽然7、8、9、10截取的都是3个字符，结果却不是3 个字符； 只要 |a| ≤ b,取a的个数(如：7、8、9)；当 |a| ≥ b时，才取b的个数，由a决定截取位置(如：9和10)）

 

11、select substr('HelloWorld',0) value from dual;  //返回结果：HelloWorld，截取所有字符

12、select substr('HelloWorld',1) value from dual;  //返回结果：HelloWorld，截取所有字符

13、select substr('HelloWorld',2) value from dual;  //返回结果：elloWorld，截取从“e”开始之后所有字符

14、select substr('HelloWorld',3) value from dual;  //返回结果：lloWorld，截取从“l”开始之后所有字符

15、select substr('HelloWorld',-1) value from dual;  //返回结果：d，从最后一个“d”开始 往回截取1个字符

16、select substr('HelloWorld',-2) value from dual;  //返回结果：ld，从最后一个“d”开始 往回截取2个字符

17、select substr('HelloWorld',-3) value from dual;  //返回结果：rld，从最后一个“d”开始 往回截取3个字符

 

    （注：当只有两个参数时；不管是负几，都是从最后一个开始 往回截取(如：15、16、17)）

3）实例截图：

1、

 

 

 

 

2、

 

 

 

5、

 

 

 

6、

 

 

 

7、

 

 

 

8、

 

 

 

9、

 

 

 

10、

 

 

 

15、

 

 

 

16、

 

 

 

17、

 

 

 

 

5.改

5.1.多行字段更新——用一个表的字段更新另一个表的字段：

update table_a a set a.name=

(select bname from table_b b where b.id=a.id and rownum= 1 and a.name is null);

5.2.SQL修改日期(hh24为24小时制，hh为12小时制，oracle数据和sql Server写法区别)：

set  begin_date=to_date('2000-12-17','yyyy-mm-dd')

set  begin_date=to_date('2000-12-17 15:43:36','yyyy-mm-dd hh24:mi:ss')

5.3.SQL修改——在字段前加字段：

set  fcode = ‘SCQY’||fcode

5.4.SQL修改字符串中指定的字符：

Update 表名 set字段名（列名）= Replace(字段名,'指定字符','替换字符')

5.5.SQL交换两列数据：

update t_customer set fname=fsname,fsname=fname

 

6.Alter的应用

6.1.增加一个列：

a、Oracle中的写法：

ALTER TABLE 表名 ADD(列名 数据类型);

如：

ALTER TABLE emp ADD(weight NUMBER(38,0));

b、Sql Server的写法：

ALTER TABLE 表名 ADD 列名 数据类型;

如：

ALTER TABLE emp ADD weight varchar(100);

6.2.修改一个列的数据类型(一般限于修改长度，修改为一个不同类型时有诸多限制):

a、Oracle中的写法：

ALTER TABLE 表名 MODIFY(列名 数据类型);

如：

ALTER TABLE emp MODIFY(weight NUMBER(3,0) NOT NULL);

b、Sql Server的写法：

ALTER TABLE 表名 Alter column 列名 数据类型;

6.3.给列改名：

a、oracle写法：

ALTER TABLE 表名 RENAME COLUMN 当前列名 TO 新列名;

如：

ALTER TABLE emp RENAME COLUMN weight TO weight_new;

b、sql server写法：

exec sp_rename '表明.原列名','新列名','column'

6.4.删除一个列(Oracle和Sql server通用)：

ALTER TABLE 表名 DROP COLUMN 列名;

如：

ALTER TABLE emp DROP COLUMN weight_new;

6.5.将一个表改名：

1. MYSQL

rename table table1 to table2;

2. SQL SERVER

EXEC sp_rename 'table1', 'table2';

3. Oracle

alter table table1 rename to table2

4. db2

rename table table1 to table2;

6.6查询表中的字段类型长度等：

select column_name,data_type,DATA_LENGTH From all_tab_columns
where table_name=upper('表名') AND owner=upper('数据库登录用户名')

column_name是字段名
data_type是字段类型
DATA_LENGTH 是字段长度。你要查具体的字段就加上column_name的条件就是了，
注意大写 ，所有表名，字段名都尽量大写

select * from user_tab_columns t where t.TABLE_NAME='T_GOODS' and column_name='FNOTE'

 

三．数据库备份与恢复脚本

 

1. Oracle：

注：如果此处打开CMD执行脚本没有反应，则考虑打开Oracle的bin执行。

Oracle备份：

exp vta/vtaPwd@hrst file=F:\BackUp\daochu.dmp log=F:\BackUp\daochu.log owner=vta

 

Oracle恢复：

imp vta/vta@hrst file=F:\BackUp\2018-11-13.dmp fromuser=vta touser=vta grants=n ignore=y statistics=none

 

fromuser=vtahg ：原用户

touser=vta：目标用户

ignore忽略创建错误

grants导出权限

statistics分析对象(ESTIMATE)

 

2.MongoDB：

MongoDB备份：

mongodump -h 127.0.0.1:27017 -d fileservice -o F:\BackUp

 

-h：MongDB所在服务器地址，例如：127.0.0.1，也可以指定端口号

-d：需要备份的数据库实例，例如：hrst （数据库名称，可以备份该数据库下所有集合）

-o：备份的数据存放位置，例如：E:\MongoDB\bin ，在备份完成后，系统自动在bin目录下建立一个hrst目录，这个目录里面存放该数据库实例的备份数据。

双击bin目录下的mongodump.exe也可备份所有数据

 

MongoDB恢复：

mongorestore -h 127.0.0.1:27017 -d hrstTest F:\BackUp\hrstTest

 

-h：MongoDB所在服务器地址

-d：需要恢复的数据库实例，恢复时会自动新建实例，例如：hrstTest

四．其他

1.判断某个表是否存在

 

在SQL中，我们常常使用if exists来判断一个表或某个对象是否存在，例如：

         IF EXISTS (SELECT * FROM sys.tables WHERE name = 'CODE_BMDM')

如果存在，则返回true，不存在则返回false。

但是在Oracle中这种方法就行不通了，我们只能通过使用select count(*) 的方式判断当前表是否存在，返回1则代表存在，0则代表不存在，例如：

        SELECT COUNT(*) FROM User_Tables WHERE table_name = 'CODE_BMDM';（在SQL中使用这种方法亦可）

需要注意的是：表名（或者其他对象名）必须全部大写，有特殊字符的除外（表名之间有空格等特殊字符），否则查询不到。

其中的 User_Tables（用户下的所有表） 也可以换成dba_tables(管理员权限下的所有表) 或者all_tables(所有表)

2.恢复oracle 中误删、误修改的表 或delete 删掉的数据恢复

2.1.查看回收站中表  drop表之后的恢复

select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;

SQL>flashback table "OBJECT_NAME" to before drop;

 

2.2.delete 删除数据恢复：

利用oracle提供的闪回方法，如果在删除数据后还没做大量的操作（只要保证被删除数据的块没被覆写），就可以利用闪回方式直接找回删除的数据
具体步骤为：

*确定删除数据的时间（在删除数据之前的时间就行，不过最好是删除数据的时间点）

*用以下语句找出删除的数据：select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss')

*把删除的数据重新插入原表：

     insert into 表名 (select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss'));注意要保证主键不重复。

 

3.Oracle数据库用户密码过期时间不过期调整

 

Oracle的密码过期规则是用Profile来管理的，系统默认只有一个Profile（DEFAULT），该profile的密码过期规则为180天。修改Oracle数据库用户的密码过期时间为永不过期方法如下 :
Oracle的密码过期规则是用Profile来管理的，系统默认只有一个Profile（DEFAULT），该profile的密码过期规则为180天；

而所有的用户都使用该DEFAULT的profile，所以如果修改DEFAULT的过期规则会影响所有的用户；

所以我们新建一个profile，修改该profile的过期规则为无限期，在让某个用户适用于该profile，则该用户的过期规则会变更。

以下举例：修改MESSERIES用户的密码过期规则为UNLIMITED

1. 新建profile "PASSWD_UNLIMIT"，规则与DEFAULT一致（因为一个profile中包含很多的项目，我们此时只想修改过期规则，其他的不变）

2. 修改profile "PASSWD_UNLIMIT"，修改过期规则为UNLIMITED

3. 将'MESSERIES'用户适用新的profile "PASSWD_UNLIMIT"

SQL如下：

 

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
SELECT * FROM dba_profiles s ;
--查看用户profile
select username, user_id, account_status,expiry_date, profile from dba_users where username = 'MESSERIES';
--1. 创建新的profile（PASSWD_UNLIMIT）复制DEFAULT的Script
CREATE PROFILE "PASSWD_UNLIMIT" LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
PASSWORD_VERIFY_FUNCTION NULL;
SELECT * FROM dba_profiles;
--2. 修改新建的profile（PASSWD_UNLIMIT），密码过期为不过期
ALTER profile PASSWD_UNLIMIT limit PASSWORD_LIFE_TIME UNLIMITED;
--3. 修改用户的Profile为新的profile
alter user MESSERIES profile PASSWD_UNLIMIT;

Oracle-SQLserver20181122