Mysql|oracle some simple commands for Oracle and MySQL compare references
Oraclemysql Contrast version personal Oracle7 release 7.3.4.0.0mysql 3.22.34-shareware-debug splash screen
(Click to enlarge) The default installation directory C:\orawin95c:\mysql the directory of various utilities C:\orawin95\binc:\mysql\bin console tools SVRMGR.EXE
SVRMGR23. EXEmysqladmin.exe Database launcher 0start73.exe screenmysqld-shareware.exe shutdown database command Ostop73.exemysqladmin.exe-u root Shutdown client program sql*plusmysql Start command c:\orawin95\bin\sqlplus.exec:\mysql\bin\mysql.exe with user startup mode
(direct link) c:\orawin95\bin\sqlplus.exe System/manager@tnsc:\mysql\bin\mysql.exe test
C:\mysql\bin\mysql.exe-u root test system default User (library) sys after installation
System
Scottmysql
Test displays all user (library) SQL >select * from All_users; C:\mysql\bin>mysqlshow
C:\mysql\bin>mysqlshow--status
Mysql> Show databases Exit command Sql> exit
Sql> quitmysql> exit
Mysql> Quit Change connection user (library) sql> conn username/password @ host string mysql> use library name query all current tables sql> select * from tab;
Sql> SELECT * from cat;mysql> show tables;
C:\mysql\bin>mysqlshow Library name Display current connection user (library) sql> show usermysql> connect view Help sql>?mysql> Assist 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;
C:\mysql\bin>mysqlshow Library Name Table name Date function 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 format 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 function
(added one months) sql> select To_char (add_months (to_date (' 20000101 ', ' YYYYMMDD '), 1), ' YYYY-MM-DD ') from dual;
Results: 2000-02-01
Sql> Select To_char (add_months (to_date (' 20000101 ', ' YYYYMMDD '), 5), ' YYYY-MM-DD ') from dual;
Results: 2000-06-01
Mysql> Select Date_add (' 2000-01-01 ', Interval 1 month);
Results: 2000-02-01
Mysql> Select Date_add (' 2000-01-01 ', Interval 5 month);
Results: 2000-06-01
Alias Sql> Select 1 A from dual;mysql> select 1 as A; string intercept function sql> Select substr (' ABCDEFG ', 1,5) from dual;
Sql> Select SUBSTRB (' ABCDEFG ', 1,5) from dual;
Results:abcdemysql> 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
Another substring_index (str,delim,count) function
Returns a substring of Delim after the occurrence of the delimiter from the count of string str.
If Count is a positive number, returns all characters from the last separator to the left (from the left).
If count is a negative number, returns the last separator to all characters on the right (from the right).
Execute external Scripting command SQL >@a.sql1:mysql> source A.sql
2:c:\mysql\bin>mysql <a.sql
3:c:\mysql\bin>mysql Library name <a.sql Import, export tool Exp.exe
Exp73.exe
Imp.exe
Imp73.exemysqldump.exe
Mysqlimport.exe Change table name sql> rename A to b;mysql> alter TABLE a rename B; Execute command;< carriage return >
/
R
run;< Carriage return >
Go
EGODISTINCT 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 returns the number of record bars sql> select * from table name where rownum<5;mysql> s Elect * from table name limit 5; New User (library) sql> create username identified by password;mysql> CREATE database name; Delete User (library) sql> drop us ER user name;mysql> drop database name; outer joins use (+) to index sql> select Index_name,table_name from User_indexes;mysql using the left JOIN query > Show index from table name [from library name]; Wildcard "%" "%" and "_" SQL syntax select Selection_list Choose which columns
Where to select rows from table_list
Where Primary_constraint line must meet what conditions
Group BY Grouping_columns How the results are grouped
A second condition that must be met by a secondary_constraint line
Order by Sorting_columns How to sort the results
Select Selection_list Choose which columns
Where to select rows from table_list
Where Primary_constraint line must meet what conditions
Group BY Grouping_columns How the results are grouped
A second condition that must be met by a secondary_constraint line
Order by Sorting_columns How to sort the results
LIMIT Count Result Qualification
