将oracle 11g导出的dmp文件导入到oracle10g 

问题:从数据库从导出的数据,接着进行导入数据操作,提示:

IMP-00010: 不是有效的导出文件, 头部验证失败
IMP-00000: 未成功终止导入

从网上查阅,头部验证失败是由于版本号不同所致,经试验可以

通过如下方法进行修改:用notepad++工具打开dmp文件,可以看到头部信

息 --TEXPORT:V11.01.00,即为源数据库的版本号,将其修改为目的数

据库的版本号,如本机为10.02.01,再次进行导入操作,导入成功

Oracle登录命令

1、运行SQLPLUS工具

  C:\Users\wd-pc>sqlplus

2、直接进入SQLPLUS命令提示符

  C:\Users\wd-pc>sqlplus /nolog

3、以OS身份连接 

  C:\Users\wd-pc>sqlplus / as sysdba   或

  SQL>connect / as sysdba

4、普通用户登录

  C:\Users\wd-pc>sqlplus scott/123456  或

  SQL>connect scott/123456  或

  SQL>connect scott/123456@servername

5、以管理员登录

  C:\Users\wd-pc>sqlplus sys/123456 as sysdba 或

  SQL>connect sys/123456 as sysdba

6、切换用户

  SQL>conn hr/123456 

  注:conn同connect

 7、退出

  exit

删除oracle用户: SQL>drop user username cascade; (删除与用户相关的所有对象)

--1.查看所有用户:

select * from dba_users;

select * from all_users;

select * from user_users;

--2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):

select * from dba_sys_privs;

select * from user_sys_privs;

--3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

sql>select * from role_sys_privs;

--4.查看用户对象权限: select * from dba_tab_privs;

select * from all_tab_privs;

select * from user_tab_privs;

--5.查看所有角色: select * from dba_roles;

--6.查看用户或角色所拥有的角色: select * from dba_role_privs;

select * from user_role_privs;

--7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS --注意:  

--1、以下语句可以查看Oracle提供的系统权限 select name from sys.system_privilege_map

--2、查看一个用户的所有系统权限(包含角色的系统权限) SELECT privilege FROM dba_sys_privs WHERE grantee = 'DATAUSER' UNION SELECT privilege FROM dba_sys_privs WHERE grantee IN (SELECT granted_role FROM dba_role_privs WHERE grantee = 'DATAUSER');

1、先查询空闲空间 select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space; 2、增加Oracle表空间 先查询数据文件名称、大小和路径的信息,语句如下: select tablespace_name,file_id,bytes,file_name from dba_data_files;

3、修改文件大小语句如下 alter database datafile '需要增加的数据文件路径,即上面查询出来的路径 'resize 800M;

4、创建Oracle表空间

create tablespace test datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8M

autoextend on

next 5M maxsize 10M;

create tablespace sales

datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M

autoextend on

next 50M

maxsize unlimited

maxsize unlimited 是大小不受限制

create temporary tablespace sales

tempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M

autoextend on

next 50M

maxsize 1000M

创建本地管理临时表空间,如果是临时表空间,所有语句中的datafile都换为tempfile ,所有语句中的datafile都换为tempfile8i

mysql查询取整数或小数或精确位数

select cast(19.16558 as decimal(9,2))精确到几位
select round(123.5); 四舍五入
select floor(123.5);取整数部分
select ceil(123.5);四舍五入

decode()oracle函数在mysql中转化

decode()是oracle数据库的一种函数,类似于java中if三目运算,在mysql中是没有decode()函数的,但mysql中可以用if语句来代替,长话短说,举个例子就很明白了:

我们首先看一下java中三目运算:

     int i=(4>3)?4:3;

     其中    (4>3)?4:3   java if三目运算

decode()在oracle 表示:

      decode(node_count ,null, 0, 1)
转换成mysql   语句 if:

      if(node_count=null,0,1)
  转换成mysql  case when 语句:

    (case when node_count=null then 0 else 1 end)

case xxx when xx then xxx when xx then xxx end


2.oracle 中的sysdate显示系统时间 mysql 有sysdate()函数获取时间

Oracle to_char函数的功能是将数值型或者日期型转化为字符型,

to_date 是日期装换

对于MySQL

是用的是cast(s as type)这种函数装换类型

如:转换char类型的 cast(s as char)

即使to_char(s)对应cast(s as char)

但是在MySQL中

要讲日期转为字符的,不可以用cast,有一个函数可以使用

date_format(date,'%Y-%m-%d')——对应—–>oracle中的to_char(date,yyyy-mm-dd);

str_to_date(date,'%Y-%m-%d')——对应—–>oracle中的to_date(date,yyyy-mm-dd);

要注意一下,%Y表示有4为的日期,比如2018, %m是数据月份,%d是日期
mysql 类似to_char() to_date()函数 
  mysql日期和字符相互转换方法 
  date_format(date,'%Y-%m-%d') -------------->oracle中的to_char(); 
  str_to_date(date,'%Y-%m-%d') -------------->oracle中的to_date(); 
  %Y:代表4位的年份 
  %y:代表2为的年份 
  %m:代表月, 格式为(01……12) 
  %c:代表月, 格式为(1……12) 
  %d:代表月份中的天数,格式为(00……31) 
  %e:代表月份中的天数, 格式为(0……31) 
  %H:代表小时,格式为(00……23) 
  %k:代表 小时,格式为(0……23) 
  %h: 代表小时,格式为(01……12) 
  %I: 代表小时,格式为(01……12) 
  %l :代表小时,格式为(1……12) 
  %i: 代表分钟, 格式为(00……59) 
  %r:代表 时间,格式为12 小时(hh:mm:ss [AP]M) 
  %T:代表 时间,格式为24 小时(hh:mm:ss) 
  %S:代表 秒,格式为(00……59) 
  %s:代表 秒,格式为(00……59) 
  SELECT DATE_FORMAT(20130111191640,'%Y-%m-%d %H:%i:%s') 
  DATE_FORMAT(20130111191640,'%Y-%m-%d %H:%i:%s')

3.oracle中的(+)是一种特殊的用法,(+)表示外连接,并且总是放在非主表的一方。

例如

左外连接:select A.a,B.a from A LEFT JOIN B ON A.b=B.b;

等价于select A.a,B.a from A,B where A.b = B.b(+);

再举个例子,这次是右外连接:select A.a,B.a from A RIGHT JOIN B ON A.b=B.b;

等价于select A.a,B.a from A,B where A.b (+) = B.b;

oracle

nvl(xxx,0)  转成   mysql      ifnull(xxx,0)

select sysdate from dual    转成   mysql      select now()

sys_guid()函数  select newid() from dual    转成   mysql    UUID()

oracle   的  to_number    mysql  不需要

Oracle trunc()函数的用法

/**************日期********************/

1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18

2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.

3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天

4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日

5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天

6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天

7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41

8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确

9. trunc(125.815) would return 125   截取少数点前面的数值

mysql

NOW()函数以'YYYY-MM-DD HH:MM:SS'返回当前的日期时间,可以直接存到DATETIME字段中。

CURDATE()以'YYYY-MM-DD'的格式返回今天的日期,可以直接存到DATE字段中。

CURTIME()以'HH:MM:SS'的格式返回当前的时间,可以直接存到TIME字段中。

取任意一个月的天数:日期可以从前台传入

select substr(last_day(date_format(20220401,'%Y-%m-%d')),9)

日期加一天

select DATE_ADD(t.voucher_date, INTERVAL 1 DAY) from tcc_voucher_date t 

-- 将dept 表中的数据按‘OPERATIONS’、‘ACCOUNTING’、‘SALES’ 进行自定义排序 SELECT deptno, dname, loc FROM dept ORDER BY DECODE(dname, 'OPERATIONS', 1, 'ACCOUNTING', 2, 'SALES', 3); -- Oracle 中用法

SELECT deptno, dname, loc FROM dept ORDER BY CASE dname WHEN 'OPERATIONS' THEN 1 WHEN 'ACCOUNTING' THEN 2 WHEN 'SALES' THEN 3 END ;  -- mysql中用法

mysql 字符串拼接的几种方式 

第一种:

mysql自带语法CONCAT(string1,string2,...),此处是直接把string1和string2等等的字符串拼接起来(无缝拼接哦)

说明:此方法在拼接的时候如果有一个值为NULL,则返回NULL

如:

1.SELECT CONCAT("name=","lich",NULL) AS test;

2.SELECT CONCAT("name=","lich") AS test;

第二种:
第二种也是mysql自带语法CONCAT_WS(separator,string1,string2,...),但是可以多个字符串用指定的字符串进行拼接(带缝拼接哦)

说明:string1,string2代表的是字符串,而separator代表的是连接其他参数的分隔符,可以是符号,也可以是字符串。如果分隔符为NULL,则结果为NULL。此方法参数可以为NULL。

如:

1.select concat_ws('#','name=','lich',null) AS test;

2.select concat_ws(NULL,'name=','lich',null) AS test;

3.select concat_ws("hello,",'name=','lich',null) AS test;

第三种:

也是mysql的自带语法GROUP_CONCAT([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

如:

1.SELECT id,GROUP_CONCAT(type) FROM log_sys_interview GROUP BY type ;

2.SELECT id,GROUP_CONCAT(type Separator '#') FROM log_sys_interview GROUP BY type ; 

 

3.SELECT id,GROUP_CONCAT(type ORDER BY type DESC) FROM log_sys_interview GROUP BY ip ;

4.SELECT id,GROUP_CONCAT(DISTINCT type ORDER BY type DESC) FROM log_sys_interview GROUP BY ip ;

oracle中substr() instr() 用法

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:

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个字符)

 格式2:

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)

instr函数格式

--instr(源字符串,目标字符串,起始字符串,匹配字符串)=返回要截取的字符串在源字符串中的位置,从字符的开始,只检索一次
--instr(string1,string2,index1,index2) 表示:要在string1的index1号位置,开始查找,第index2次,出现的string2
select instr('miaoying','i',2,2) from dual;--返回6:也就是说:在"miaoying"的第2号位置开始,查找第二次出现的i的位置
select instr('miaoying','k',2,2)from dual;--返回0:即如果查找不到,则返回0
select instr('miaoying','i') from dual;--返回2
select instr('miaoying','yi') from dual;--返回5:即"yi"的y的位置
select instr('miaoying','i',-1,2) from dual;--返回2:
--空格也是字符。。。。。
 
select * from omgnode a where name like '%miaoying%'
select * from omgnode a where instr(name,'miaoying')>0--效果一样

INITCAP
返回字符串并将字符串的第一个字母变为大写;

upper:小写字符转化成大写的函数

lower:大写字符转化成小写的函数 

Choose two
Examine the data in the CUST NAME column of the CUSTOMERS table:
CUST_NAME
------------------------------
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You want to display the CUST_NAME values where the last name starts with Mc or MC.
Which  WHERE clauses give the required result?
A) WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,' ') +1 ) ) IN ('MC%', 'Mc%)
B) WHERE UPPER (SUBSTR(cust_nane, INSTR(cust_name, ' ') +1 ) ) LIKE UPPER('MC%')
C) WHERE INITCAP(SUBSTR(cust_nane, INSTR(cust_name,' ') +1 ) ) LIKE 'Mc%'
D) WHERE SUBSTR(cust_name,INSTR(cust_name,' ') +1 ) LIKE 'Mc%' OR 'MC%'
E) WHERE SUBSTR(cust_name, INSTR(cust_name,' ') +1 ) LIKE 'Mc%'
(解析: Answer:BC。注意 instr 和 substr 的用法,没有实验还是很难理解的。

截取小数点后面的数值

select substr('123.456400',INSTR('123.456400','.')+1,2) from dual    返回   45
select substr('123.456400',INSTR('123.456400','.')) from dual    返回   .456400

过滤同一个表中多个字段重复数据的SQL技巧

方法一:
SELECT CODE,NAME,COUNT(*) AS [COUNT],MAX(ID) AS ID
FROM TEST
GROUP BY CODE,NAME HAVING COUNT(*) > 1
方法二:
SELECT * FROM (SELECT NAME,CODE,(SELECT COUNT(*) FROM TEST WHERE NAME=A.NAME AND CODE=A.CODE ) AS [COUNT],MAX(ID) AS ID
FROM TEST A
GROUP BY NAME,CODE) RES
WHERE [COUNT] > 1

mysql datediff() 函数的使用方法

mysql计算两个日期之间的天数

mysql> select datediff('2021-06-10','2021-06-08');
+-------------------------------------+
| datediff('2021-06-10','2021-06-08') |
+-------------------------------------+
|                                   2 |
+-------------------------------------+
1 row in set (0.01 sec)

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐