Oracle，oracle转成mysql

将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)