目录与学习目标

  1. 1：数据库的层次结构上
  2. 2：数据库中表字段类型
  3. 3：主键
  4. 4：单引号处理
  5. 5：空字符的处理
  6. 6：翻页的sql语句的处理   
  7. 7：组函数用法规则
  8. 8：日期字段的处理
  9. 9：长字符串的处理
  10. 10：字符串的模糊比较

1：数据库的层次结构上

  1. oracle：创建一个数据库，数据库下有好多用户：sys、system、scott等，不同用户下有好多表，一般情况下只创建一个数据库用。
  2. mysql：默认用户是root，用户下可以创建好多数据库，每个数据库下还有好多表，一般情况下都是使用默认用户，不会创建多个用户；

2：数据库中表字段类型

  1. oracle：number 数值型
  2.           varchar2、varchar、char 字符型
  3.           date 日期型 等…
  5. mysql：int、float、double等数值型，
  6.           varchar、char字符型，
  7.           date、datetime、time、year、timestamp等日期型。

3：主键

  1. oracle：没有自动增长类型，主键一般使用的序列，插入记录时将序列号的下一个值赋给该字段即可，只是ORM框架是只要是native主键生成策略即可。
  2. mysql：一般使用自动增长类型，在创建表时只要指定表的主键auto increment，插入记录时，不需要再指定该记录的主键值，mysql将自动增长。
  4. mysql有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。oracle没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。
  6. create sequence序列号的名称(最好是表名+序列号标记)increment by 1 start with 1 maxvalue 99999 cycle nocache;
  8. 其中最大的值按字段的长度来定，如果定义的自动增长的序列号number(6)，最大值为999999
  10. insert语句插入这个字段值为：序列号的名称.nextval

4：单引号处理

  1. oracle：只可以用单引号包起字符串，
  2.           如果需要输入字符串 ' ，则需要使用 ''  替换 ' 。
  4. mysql：可以用双引号包起字符串。

5：空字符的处理

  1. oracle：
  2.     定义了非空字段就不容许有null与空字符串。（因为Oracle没有空字符串的概念）
  4. mysql：
  5.      定义了非空字段就不容许有null，但运行有空字符串。(因为MySQL有空字符串的概念)

6：翻页的sql语句的处理

  1. oracle处理翻页的 sql语句比较繁琐。
  2. 有3种方式：
  3. 1：rowId
  4. 2：分析函数 row_number
  5. 3：rownum
  7. 下面仅讲解使用rownum进行分页
  9. 每个结果集只有一个rownum字段标明它的位置，并且只能用rownum<100，不能用rownum>50。
  11. 这里就要对oracle的rownum做进一步的理解。
  12. 因为rownum并不是当作实体数据存放在每一张表中，而是在每一次select查询的时候，
  13. 根据基表的默认insert顺序由oracle动态分配的，有前100才有打印50，如果rownum没有前100，那么大于50也就没有了意义，所以这个查询就不会有任何结果出来。
  15. 以下是经过分析后较好的两种oracle翻页sql语句(id是唯一关键字的字段名)，
  16. 我们需要利用子查询和别名列来实现这个需求：
  18. 语句一：
  19.     select id, [field_name,...] 
  20.     from table_name where 
  21.     id in ( select id from (select rownum as numrow, id from table_name where 条件1 order by 条件2) where numrow > 50 and numrow < 100 ) 
  22.     order by 条件3;
  24. 语句二：
  25.     select * from (
  26.     ( select rownum as numrow, c.* from (select [field_name,...] from table_name where 条件1 order by 条件2) c) 
  27.     where numrow > 50 and numrow < 100 ) 
  28.     order by 条件3;
  1. mysql 处理翻页的sql语句比较简单，用limit开始位置，记录个数；

7：组函数用法规则

  1. 在oracle中如果查询语句中有组函数，那其他列名必须是组函数处理过的，或者是group by子句中的列否则报错。
  2. 而在mysql中组函数在select语句中可以随意使用
  1. 在oracle中就有问题：
  2. select name,count(money) from user；
  4. 需要修改成：
  5. 1：select name,count(money) from user group by name 
  6. 2：select max(name),count(money) from user；
  1. 以下三个SQL放在mysql中没有问题
  2. select name,count(money) from user；
  3. 1：select name,count(money) from user group by name 
  4. 2：select max(name),count(money) from user；

8：日期字段的处理

  1. oracle：
  2.     日期字段只有date，包含年月日时分秒信息。
  3.     有两种格式SYSDATE与SYSTIMESTAMP  本质上都是date，只是展示的时候不太一样。
  5.     转换通常使用两种方式：
  6.         1：to_char函数，date形式转字符串
  7.         2：to_date函数，字符串形式转date形式  
  9.     用当前数据库的系统时间为SYSDATE，精确到秒，
  10.     SELECT SYSDATE FROM dual;
  11.     SELECT to_char(SYSDATE,'yyyy-mm-dd hh24:mi:SS') TIME FROM DUAL;
  13.     用当前数据库的系统时间为SYSTIMESTAMP，精确到毫秒，
  14.     SELECT SYSTIMESTAMP FROM DUAL;
  15.     SELECT to_char(SYSTIMESTAMP,'yyyy-mm-dd hh24:mi:SS:ff') TIME FROM DUAL;
  16.     SELECT to_char(SYSTIMESTAMP,'yyyy-mm-dd hh24:mi:SS') TIME FROM DUAL;
  18.     日期型字段转换成字符串函数to_char(‘2001-08-01','yyyy-mm-dd hh24:mi:ss')
  20.     select to_date('20220410','yyyy-mm-dd hh24:mi:SS') from dual
  21.     select to_date('20220410153010','yyyy-mm-dd hh24:mi:SS') from dual
  23.     oracle找到离当前时间7天用 date_field_name >sysdate - 7;
  1. mysql
  2.     日期字段分date和time两种>
  4.     插入当前时间的几个函数是：
  5.         now()函数以`'yyyy-mm-dd hh:mm:ss'返回当前的日期时间，可以直接存到datetime字段中。
  6.         curdate()以'yyyy-mm-dd'的格式返回今天的日期，可以 直接存到date字段中。
  7.         curtime()以'hh:mm:ss'的格式返回当前的时间，可以直接存到time字段中。
  9.         例：insert into tablename (fieldname) values (now())
  11.     mysql找到离当前时间7天用date_field_name > subdate(now()，interval 7 day)

9：长字符串的处理

  1. oracle：
  2.     长字符串的处理oracle有它特殊的地方。
  3.     insert和update时最大可操作的字符串长度小于等于4000个单字节，如果要插入更长的字符串，请考虑字段用clob类型，方法借用oracle里自带的dbms_lob程序包。
  4.     插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告，返回上次操作。
  6.  mysql：
  7.     varchar类型
  8.     MySQL5.0以下版本: 最大长度255
  9.     MySQL5.0以上版本: 最大长度65535
  11.     如果VARCHAR类型不能满足你存取字符串长度的需求，那么应选择以下字符字段类型。
  13.     text类型
  14.     最大长度65535
  16.     mediumtext类型
  17.     最大长度16777215
  19.     longtext类型
  20.     最大长度4294967295

10：字符串的模糊比较

  1. 两者都可以使用 like%XXX%  或者 instr函数，
  2. 不管是Oracle还是MySQL，当查询的字段添加索引之后，使用instr函数明显比like要快
  4. 1.select * from tb where name like '%XX%';
  5. 2.select * from tb where instr(name,'XXX')>0;
  7. 若是在name字段上没有加索引，两者效率差不多，基本没有区别。
  9. 为提高效率，我们在name字段上可以加上非唯一性索引：
  10. create index idx_tb_name on tb(name);
  11. 然后再重复上面的查询，会发现速度有所提升