Comparison of some simple commands in Oracle and mysql

Comparison of some simple commands in Oracle and mysql

	Oracle	Mysql
Comparison version	Personal Oracle7 Release 7.3.4.0.0	Mysql 3.23.58-nt
Default installation directory	C: ORAWIN95	F: MySQL
Directory of various utilities	C: ORAWIN95BIN	F: MySQLBIN
Console Tools	SVRMGR. EXE SVRMGR23.EXE	Mysqladmin.exe
Database startup program	0start73.exe screen	Mysqld-shareware.exe
Shut down database commands	Ostop73.exe	Mysqladmin.exe-u root shutdown
Customer Program	SQL * Plus	Mysql
Start command	C: orawin95binsqlplus.exe	F: MySQLbinmysql.exe
With user startup mode (Direct database connection)	C: orawin95binsqlplus.exe system/manager @ TNS	F: MySQLbinmysql.exe test F: MySQLbinmysql.exe-u root test
Default user (library) after installation)	Sys System Scott	Mysql Test
Show all users (databases)	SQL> select * from all_users;	F: MySQLbin> mysqlshow F: MySQLbin> mysqlshow -- status Mysql> show databases;
Exit command	SQL> exit SQL> quit	Mysql> exit Mysql> quit
Change connected user (database)	SQL> conn username/password @ host string	Mysql> use database name
Query all current tables	SQL> select * from tab; SQL> select * from cat;	Mysql> show tables; F: MySQLbin> mysqlshow database name
Displays the current connected user (database)	SQL> show user	Mysql> connect
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; F: MySQLbin> mysqlshow database name 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 month)	SQL> select to_char (add_months (to_date ('201312', 'yyyymmdd'), 1), 'yyyy-mm-DD') from dual; Result: 2000-02-01 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 1 month ); Result: 2000-02-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: F: MySQLbin> mysql 3: F: MySQLbin> mysql database name
Import and Export tools	Exp.exe Exp73.exe Imp.exe Imp73.exe	Mysqldump.exe Mysqlimport.exe
Change Table name	SQL> rename a to B;	Mysql> alter table a rename B;
Execute command	; <回车> / R Run	; <回车> 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;
Create a user (database)	SQL> create user username identified by password;	Mysql> create database name;
Delete user (database)	SQL> drop user username;	Mysql> drop database name;
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