Date conversion for 1.MYSQL and Oracle
There are 2 date formats in MySQL, dates and time,oracle have only one date format.
Oracle> Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;
Oracle> Select To_char (sysdate, ' Hh24-mi-ss ') from dual;
Mysql> Select Date_format (now (), '%y-%m-%d ');
Mysql> Select Time_format (now (), '%h-%i-%s ');
2.MYSQL and Oracle Modify field lengths or types
Oracle>alter TABLE tableName Modify column columnName type;
Mysql>alter TABLE tableName Modify (columnName type);
3. Group function rules are different
The group functions in MySQL are freely available in the SELECT statement. In Oracle, however, if there is a set of functions in the query statement, the other column names must be processed by the group function, or the columns in the GROUP BY clause will otherwise be error-listed.
For example: Oralce>select Name,count (Money) from the user group by name;
Mysql>select Name,count (money) from user;
4. Self-growth and sequencing
MySQL has a self-growing type, and the value of the self-growing column automatically increases in turn, no action is required. But Oracle has no self-growth type, Oracle has serial numbers, and serial numbers need to be added manually.
Oracle>create sequence Customer_id_seq increment by 2 start with 1
Oracle>create table customer (ID int primary key NOT NULL, name varchar (15));
Oracle>insert into customer values (customer_id_seq.curval, "name1"), (Customer_id_seq.nextval, "name2");
Mysql>create table customer (ID int auto_increment PRIMARY key NOT NULL, name varchar (15));
Mysql>insert into customer (name) VALUES ("Name1"), ("name2");
5. Handling of quotation marks
MySQL can handle double quotes and single-quote wrapped strings, while oarcle can only handle strings that are enclosed in single quotes
Oracle>select * from user where name= ' Zhang San ';
Mysql>select * from user where name= "Zhang San";
6. Paging
MySQL's paging is relatively simple and can be implemented with limit Startnum,pagenum, which specifies the starting position and the amount of data on a page.
Oracle's paging must use RowNum to indicate the paging location, and the rownum can only be less than a value, not greater than a value, so rownum and where are used to complete the control of the data range, where<= the page <=rownum value.
Oracle>
7. Empty string comparison
Null content in MySQL can be an empty string, but Oracle's null value is only null and cannot contain an empty string.
8. Fuzzy Query comparison
Both MySQL and oarcle can use the field name like%str% for fuzzy queries, but Oracle cannot use the index when using a like query.
What is the difference between MySQL and Oracle date conversion? Let's compare it.