What is the difference between MySQL and Oracle date conversion? Let's compare it.

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.