Differences between Oracle and MySQL

Differences between Oracle and MySQL

1.
In Oracle, select * From all_users is used to display all users, while the command for displaying all databases in MySQL is show
Databases. For my understanding, an oracle project should have a user and a tablespace corresponding to it, while a MySQL project should also have a user and a database. In
In Oracle (similar to DB2), tablespaces are logical representations of physical containers in the file system. Views, triggers, and stored procedures can also be stored in tablespaces. MySQL does not use table space.
.
2. query all current tables. ORACLE: Select * From tab, MySQL: show tables.
3. Change the connected user (database ). ORACLE: conn username/password @ host string, MySQL: Use Database Name.
4. The current connected user (database) is displayed ). ORACLE: Show user, MySQL: connect.
5. Execute the external script command. ORACLE: @ A. SQL, MySQL: source A. SQL.

Comparison	Personal Oracle 10	For MySQL 5.1
Default installation directory	Optional	C:/program files/MySQL
Directory of various utilities	Optional	C:/Program Files // Bin
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:/orawin95/bin/sqlplus.exe	C:/MySQL/bin/mysql.exe
With User Startup Mode (Direct database connection)	C:/orawin95/bin/sqlplus.exe system/manager @ TNS	C:/MySQL/bin/mysql.exe Test C:/MySQL/bin/mysql.exe-u Root Test
Default User (Library) after installation)	Sys System Scott	MySQL Test
Show all users (databases)	SQL> select * From all_users;	C:/MySQL/bin> mysqlshow C:/MySQL/bin> 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; C:/MySQL/bin> 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; C:/MySQL/bin> 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 ('000000', 'yyyymmdd'), 1), 'yyyy-mm-dd') from dual; Result: 2000-02-01 SQL> select to_char (add_months (to_date ('000000', '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: C:/MySQL/bin> mysql <A. SQL 3: C:/MySQL/bin> MySQL database name <A. SQL
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	; <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;
Create a user (database)	SQL> create user username identified by password;	Mysql> Create Database database name;
Delete user (database)	SQL> drop User Username;	Mysql> drop database 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