Latest Oracle vs. mysql reference----development (RPM)

	Oracle	Mysql
Compare versions	Release 10.2.0.1.0 XE windowsxp	5.0.45-community-nt-log MySQL Community Edition (GPL)
As a calculator	Sql> Select from dual;	Mysql> Select;
		Mysql> Select from dual;
Show Table Structure	Sql> desc Table Name	mysql> desc table name;
	Sql> Describe table name	mysql> describe table name;
		Mysql> explain table name; Note: Explain can also be used to obtain execution plans.
	Need to visit: User_tables, User_tab_cols, user_col_comments, user_indexes, etc. dictionary tables.	Mysql> Show columns from table name;
		Bin>mysqlshow Library Name Table name
The concept of a date	A type that is completely different from the string. Date types do not have a so-called format, dates are dates, and you can specify the format of a converted string only when displayed. This will never be allowed to happen in 2008-00-00. It is relatively simple for a date to be converted to another date, such as Selecttrunc (sysdate) from dual;	It is easy to compare and convert strings. Allow 0000-00-00 this situation to appear as a null substitute for easy handling (the individual does not think this is more convenient, but confusing me). It is easier to convert a string to a date, such as CreateTable ' DateTime ' ( ' DT ' datetime not NULL default ' 0000-00-00 00:00:00 ', PRIMARY KEY (' DT ') ); INSERT into ' datetime ' VALUES (' 2008-01-01 00:00:00 '); SELECT * from ' DateTime ';
Date type	Date	Datetime
	No	Date Date
	No	Time Time
	No	Timestamp If other fields in the table change, this type of field is automatically updated to the current system time.
	Timestamp Time of the millisecond level was saved	I don't know
Date function	Sql> Select Trunc (sysdate) from dual;	Mysql> select Curdate (); Mysql> Select Current_date;
	Sql> select Sysdate from dual;	Mysql> select Sysdate ();
		Mysql> Select Now ();
	Sql> Select To_char (sysdate, ' HH24:Mi:SS ') from dual; Note that this returns the result as a string, not a date type. Oracle does not provide a type corresponding to the time type in MySQL.	Mysql> Select Current_time; Mysql> select Curtime (); Note: The time type is returned here. It's time 17:22:14 this time. There is no such type in Oracle.
	Millisecond level: Sql> select Current_timestamp from dual;	You need to use the function microsecond. Not yet.
Date formatting	Sql> Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;	Mysql> Select Date_format (now (), '%y-%m-%d ');
	Sql> Select To_char (sysdate, ' Hh24-mi-ss ') from dual;	Mysql> Select Time_format (now (), '%h-%i-%s ');
Date function (Increase one day)	Sql> select sysdate+1 from dual; Result: 2008-2-20 19:34:27	Mysql> Select Date_add (now (), Interval 1 day);
		Mysql>select now () +interval 1 day;
Date function (added one months)	Sql> Select Add_months (sysdate,1) from dual; Result: 2008-3-19 19:34:27	Mysql> Select Date_add (now (), Interval 1 month);
		Mysql>select now () +interval 1 month;
Alias	Sql> Select 1 as a from dual;	Mysql> Select 1 as A;
	Sql> Select 1 A from dual;	Mysql> Select 1 A;
String intercept function	Sql> Select substr (' ABCDEFG ', 1,5) from dual; Results: ABCDE	Mysql> selectsubstr (' ABCDEFG ', 1,5); Results: ABCDE
	Remove spaces: Select Trim (' abc ') from dual;	Remove spaces: Select Trim (' abc ') fromdual;
	string concatenation: SELECT CONCAT (' A ', ' test ') from dual; Result: a test	SELECT CONCAT (' A ', ' test '); Result: a test
	Note Oracle in | | Concatenation of strings Select ' A ' | | ' Test ' from dual; Result: a test	Note in MySQL | | is the OR operator. Select 0| | 1; Results 1; Select 0| | 0; Result 0.
	Decide whether to include: Select InStr (' ABCD ', ' BCD ') from dual; Results: 2	mysql> SELECT INSTR (' Foobarbar ', ' Bar '); Results: 4
	There is also a regular expression.	Another substring_index (str,delim,count) function
Execute SQL Script	SQL >@a.sql	1:mysql> Source A.sql
Execute external shell script	Sql>host test.sh	is not yet
	sql>! Cd..
Change table name	sql> ALTER TABLE T Rename to T1;	Mysql> ALTER TABLE t rename T1;
		mysql> ALTER TABLE T1 Rename to T;
Execute command	;< return >	;< return >
	/	Go
	R	Ego
	Run
Distinct usage	Sql> select DISTINCT column 1 from table 1;	Mysql> select DISTINCT column 1 from table 1;
	Sql> select DISTINCT column 1, column 2 from table 1;	Mysql> select DISTINCT column 1, column 2 from table 1;
Comments	--	--
	/*  with the */	/* and */
	Rem	#
Limit the number of returned record bars to 5	Sql> select * FROM table name where rownum<=5;	Mysql> select * FROM table name limit 5;
Paging Query	SELECT * From ( Select row_.*, RowNum rownum_ From ( Yoursqlhere) Row_ where rownum <= 100) where Rownum_ > 20;	SELECT * from t limit , +;
External connection	(+)	Left Join
	Left OUTER JOIN	Left OUTER JOIN
	Right OUTER JOIN
Query index	Sql> select Index_name,table_name from User_indexes;	Mysql> Show index from table name [from library name];
Wildcard characters	“%”	"%" and "_"
Update if it exists, otherwise insert	Merge into	Replace into See, MySQL replace is not strict with Oracle's merge: http://www.bitbybit.dk/carsten/blog/?p=128#comment-50048
SQL syntax	Select Selection_list which columns to choose	Select Selection_list which columns to choose
	From table_list where to select rows	From table_list where to select rows
	What conditions the WHERE Primary_constraint line must meet	What conditions the WHERE Primary_constraint line must meet
	Group by Grouping_columns How to group results Note: Oracle must specify an ORDER BY clause if it needs to be sorted.	Group by Grouping_columns How to group results Note: Even if the ORDER BY clause does not exist, MySQL is sorted by the group by column. You can even specify the ASC/DESC clause for the GROUP BY statement.
	Having secondary_constraint the second condition that the line must satisfy	Having secondary_constraint the second condition that the line must satisfy
	Order by sorting_columns How to sort results	Order by sorting_columns How to sort results
	Oracle's result set QUALIFIED statement see: "Paged Query" Note: Oracle's rownum and MySQL limit are completely different.	Limit count Results limit
The handling of not in NULL is an issue with respect to the three-valued logic.	create table a ( code INT ); insert into a      values  (2); insert into a      values  (NULL); select 1   from dual  where 1 not in  (select code  from a); Result: no results returned.	CREATE TABLE A (Code INT); INSERT into a VALUES (2); INSERT into a VALUES (NULL); SELECT 1 From DUAL WHERE 1 not in (SELECT code from a); Result: No results returned
Handling of NOT EXISTS	create table a ( code INT ); insert into a      values  (2); insert into a      values  (NULL); select 1   from dual  where not exists  (select 1                        FROM&NBSP;A&NBSP;WHERE&NBSP;A.CODE&NBSP;=&NBSP;1); Results: 1	create table a ( code INT ); insert into a      values  (2); insert into a      values  (NULL); select 1   from dual  where not exists  (select 1                        FROM&NBSP;AWHERE&NBSP;A.CODE&NBSP;=&NBSP;1); Results: 1
Get SQL statement Execution plan	Sql>explain PLAN for SELECT * from T; And then Sql>select * FROM table (Dbms_xplan. DISPLAY)	Mysql> explain select * from t;
Execution Plan Comparison	All-table Scan: Table ACCESS full	Type=all
	Where conditional filtering: Filter	Extra=using where
	Sort: Sort ORDER by	Extra=using Filesort
Transaction management	Default does not commit automatically	InnoDB Support Transactions
		Default Auto-Commit autocommit
Blocking read mode
The difference between Unique index. It's also about the three-valued logic.	create table hr.t  (    id  int not null,    data char ( default null),    unique  (id, data)   ); insert into hr.t       VALUES  (1, null); insert into hr.t       VALUES  (1, null);	mysql> CREATE TABLE t (    id INT Not NULL,    data CHAR (+) DEFAULT NULL,     UNIQUE (ID, DATA)   ); insert into T      values (1, NULL); insert into T      values (1, NULL); select * from T; query OK, 0 rows affected query OK, 1 row affected query OK, 1 row affected +----+------+ | ID | DATA | +----+------+ |  1 | NULL | |  1 | NULL | +----+------+ 2 rows in set
	ORA-00001: Violates the unique constraint (HR. sys_c003999)

Thanks again for yueliangdao0608 's support for MySQL content.

http://blog.csdn.net/fenixshadow/article/details/2106546

Latest Oracle vs. mysql reference----development (RPM)