Similarities and differences between mysql and Oracle databases _ MySQL

Similarities and differences between mysql and Oracle databases 1. wildcards in the LIKE condition of the WHERE clause.

Both Oracle and mysql support the % and _ wildcards. The former matches any character, and the latter matches up to one character. However, when processing the original characters % and _, it is very different. Mysql can use escape characters/for processing; oracle can also use escape characters for processing, but it must be described using escape '/' after the LIKE expression; /can be any character (note that it must be a character ).

2. case sensitivity of object names
Oracle is case insensitive when processing objects such as tables, stored procedures, functions, indexes, and databases. mysql is determined by the operating system. For example, LINUX is sensitive and WINDOWS is not.

3. query table information
Mysql: show tables;
Oracle: select table_name from tabs;

4. support for automatic growth
Mysql: each table can have a column using auto_increment, but it must be used for primary keywords. The starting value of AUTO_INCREMENT = 500000 can be used as the table attribute constraints. step size cannot be set and cannot be used repeatedly.
Oracle: sequence objects are used to support this type. You can use nextval and currval in triggers and SQL statements to set values. Note that the value of CURRVAL must be correct in the same SESSION.
For example, SELECT seqid_main.nextval from dual.
CREATE a statement such as create sequence SEQID_MAIN increment by 1 start with 1 nocycle cache 20 NOORDER;

5. support for limited number of records
Mysql: implemented using the limit [start-index,] count clause. it is very simple and supports the DELETE syntax, which should be very convenient for users.
Oracle: no direct clause is supported. However, the approximate value can be achieved using the row_number function.
For example, row_number () over (order by col1 desc, col2) as row, and then filter the row. If there is no sort clause, you can use a simpler rownum.

6. support for join operators
In fact, both mysql and Oracle support left, right, and inline operations. However, Oracle syntax such as a. id = B. id (+) indicates that the left join operation is more intuitive and easy to construct.

7. null processing
Oracle: nvl function.
Mysql: the default context, such as '', 0, and UNDEF.

8. character join
Oracle: | (common)
Mysql: concat

9. date type conversion
Mysql: DateFormat, which is supported by default (based on the current region settings ).
Oracle: to_date/to_char

10. start character of the comment line
Mysql :#
Oracle :-

11. external table Association support
Mysql: Create an InnoDB table after mysql 4.0.
Oracle: Currently, almost all of them are supported. I don't know how old it is, and so on.