Differences between MySQL and Oracle

Differences	Oracle	MySQL
Start command	C: \ ora92 \ bin \ sqlplus.exe	C: \ mysql \ bin \ mysql.exe
With User Startup Mode	C: \ ora92 \ bin \ sqlplus.exe system/manager @ TNS	C: \ mysql \ bin \ mysql.exe Test C: \ mysql \ bin \ mysql.exe-u Root Test
Default User	Sys, system, Scott	MySQL Test
Change connected user (database)	SQL> conn username/password @ host string	Mysql> Use Database Name
Query all current tables	SQL> select * From user_tables;	Mysql> show tables;
Show current user (database)	SQL> show user	Mysql> connect Mysql> Select User ();
View help	SQL>?	Mysql> help
Display table structure	SQL> DESC table name SQL> describe table name	Mysql> DESC table name; Mysql> describe table name; Mysql> show columns from table name;
Date Functions	SQL> select sysdate from dual;	Mysql> select now (); Mysql> select sysdate (); Mysql> select curdate (); Mysql> select current_date; Mysql> select curtime (); Mysql> select current_time;
Date formatting	SQL> select to_char (sysdate, 'yyyy-mm-dd') from dual; SQL> 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 ');
Date Functions (Add one year, month, or day)	SQL> select to_char (add_months (to_date ('201312', 'yyyymmdd'), 5), 'yyyy-mm-dd') from dual; Result: 2000-06-01	Mysql> select date_add ('2017-01-01 ', interval 5 month ); Result: 2000-06-01
Alias	SQL> select 1 A from dual;	Mysql> select 1 as;
String truncation Function	SQL> select substr ('abcdefg', 1, 5) from dual; SQL> select substrb ('abcdefg', 1, 5) from dual; Result: ABCDE	Mysql> select substring ('abcdefg', 2, 3 ); Result: BCD Mysql> select mid ('abcdefg', 2, 3 ); Result: BCD Mysql> select substring ('abcdefg', 2 ); Result: bcdefg Mysql> select substring ('abcdefg' from 2 ); Result: bcdefg Substring_index (STR, delim, count) Function Returns the substring after the delimiter delim that appears from the count of the STR string. If count is a positive number, all characters from the last separator to the left (from the left) are returned. If count is a negative number, return all characters (from the right) from the last separator to the right ).
Execute external script commands	SQL> @ A. SQL	1: mysql> source A. SQL 2: C: \ mysql \ bin> mysql <A. SQL 3: C: \ mysql \ bin> MySQL database name <A. SQL
Import and Export tools	Exp.exe Imp.exe	Mysqldump.exe Mysqlimport.exe
Change table name	SQL> rename A to B;	Mysql> alter table a rename B;
Execute Command	; <Press enter> / R Run	; <Press enter> Go Ego
Distinct usage	SQL> select distinct column 1 from table 1; SQL> select distinct column 1, column 2 from table 1;	Mysql> select distinct column 1 from table 1; Mysql> select distinct column 1, column 2 from table 1;
Note	-- /* And */	# -- /* And */
As a calculator	SQL> select 1 + 1 from dual;	Mysql> select 1 + 1;
Limit the number of returned records	SQL> select * from table name where rownum <5;	Mysql> select * from table name limit 5;
External Connection	Use (+)	Use left join
Query Indexes	SQL> select index_name, table_name from user_indexes;	Mysql> show index from table name [from database name];
Wildcard	"%"	"%" And "_"
SQL syntax	Select selection_list From table_list where to select a row Where primary_constraint How does group by grouping_columns group results? Having secondary_constraint must meet the second condition Order by sorting_columns how to sort results	Select selection_list From table_list where to select a row Where primary_constraint How does group by grouping_columns group results? Having secondary_constraint must meet the second condition Order by sorting_columns how to sort results Limit on limit count results
Continuous Columns	Use Sequence	MySQL has a data type that increases automatically. When you insert a record, you do not need to operate on this field. The data value is automatically obtained.
Quotation marks	Single quotes: String Double quotation marks: only after the	MySQL can use double quotation marks to enclose strings.
Paging statement	Statement 1: Select ID, [field_name,...] from table_name where ID in (select ID from (select rownum as numrow, ID from table_name where Condition 1 order by condition 2) Where numrow> 80 and numrow <100) order by Condition 3;   Statement 2: Select * from (select rownum as numrow, C. * From (select [field_name,...] from table_name where Condition 1 order by condition 2) c) Where numrow> 80 and numrow <100) order by Condition 3;	The SQL statement for MySQL to process page flip is relatively simple. It uses limit to start the position and record the number of records.
Time Field Type	MySQL date fields include date and time.	Oracle Date Field only has date Convert string to date type function to_date Convert a period field to a string function to_char
Date Field Calculation	MySQL found 7 days from current time Date_field_name> subdate (now (), interval 7 day)	Oracle found 7 days from current time Date_field_name> sysdate-7
Current Time Function	The now () function returns the current date and time with ''yyyy-MM-DD hh: mm: s'' and can be saved directly to the datetime field. Curdate () returns today's date in 'yyyy-MM-DD 'format and can be saved directly to the date field. Curtime () returns the current time in 'hh: mm: ss' format and can be saved directly to the time field. Example: insert into tablename (fieldname) values (now ())
Non-empty Processing	Non-empty MySQL fields are also empty. The Oracle table structure is defined based on the not null value of MySQL. errors may occur when data is imported. Therefore, when importing data, you need to judge the null character. If it is null or null, you need to change it to a space string.	Empty content is not allowed when non-empty fields are defined in Oracle