Some simple commands of Oracle and MySQL comparison reference

Some simple commands of Oracle and MySQL comparison reference

Oracle

mysql

Contrast version

Personal Oracle7 Release

7.3.4

.0.0

mysql

3.22.34

-ShaReware-debug

Default installation directory

C: / orawin95

C: / mysql

Various practical procedures

C: / ORAWIN95 / BIN

C: / mysql / bin

Console tool

SVRMGR.EXESVRMGR23.EXE

mysqladmin.exe

Database startup program

0start73.exe screen

MySQLD-ShaReware.exe

Close the database command

Ostop73.exe

mysqladmin.exe -u root shutdown

Client program

SQL * Plus

mysql

Start command

C: /orawin95/bin/sqlplus.exe

C: /mysql/bin/mysql.exe

With user start-up mode (direct library mode)

C: /orawin95/bin/sqlplus.exe system / manager @ TNS

C: /mysql/bin/mysql.exe testc: /mysql/bin/mysql.exe -u root test

After installing the system default user (library)

Syssystemscott

Mysqltest

Show all users (libraries)

SQL> SELECT * from ALL_USERS;

C: / mysql / bin> mysqlshowc: / mysql / bin> mysqlshow --statusmysql> show data;

Exit command

SQL> EXITSQL> Quit

Mysql> exitmysql> quit

Change the connection user (library)

SQL> CONN User Name / 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

Show current connection users (libraries)

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 library name table name

Date function

SQL> SELECT SYSDATE from DUAL;

MySQL> Select now (); mysql> select sysdate (); 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 function (add one month) SQL> select to_char (add_months (to_date ('20000101', 'YYYMMMDD'), 1), 'YYYY-MM-DD') from Dual; Result:

2000-02-01

SQL> SELECT TO_CHAR (add_months (to_date ('20000101'), 5), 'YYYY-MM-DD') from Dual; Result:

2000-06-01

MySQL> SELECT DATE_ADD ('

2000-01-01

', Interval 1 Month; Result:

2000-02-01

MySQL> SELECT DATE_ADD ('

2000-01-01

', Interval 5 Month; Result:

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; Result: Abcde

MySQL> Select Substring ('AbcDefg', 2, 3); Results: BCDMYSQL> SELECT MID ('Abcdefg', 2, 3); Result: BCDMYSQL> SELECT SUBSTRING ('Abcdefg', 2); Result: BcDefgmysql> Select Substring ('Abcdefg' from 2); Result: BCDEFG Another SubString_index (STR, DELIM, Count) function returns a substring after the separator Delim that appears from the string STR. If count is a positive, return all the characters that the last separator to the left (from the left side). If Count is a negative number, return the last separator to all the characters on the right (from the right side).

Execute an external scripting command

SQL> @ a.sql

1: mysql> Source A.SQL2: C: / mysql / bin> mysql mysql library name

Import, export tool

Exp.exeexp73.exeimp.exeimp73.exe

MySQLDUMP.EXEMYSQLIMPORT.EXE

Reform table name

SQL> RENAME A to B;

Mysql> ALTER TABLE A RENAME B;

Excuting an order

; / run

; GoEGo

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;

Comment

--/*versus*/

#--/*versus*/

As a calculator

SQL> SELECT 1 1 from DUAL;

MySQL> SELECT 1 1;

Restriction return record

SQL> SELECT * FROM Name WHERE ROWNUM <5;

MySQL> SELECT * FROM table name LIMIT 5; New User (Library)

SQL> CREATE User User Name Identified by password;

MySQL> CREATE DATABASE library name;

Delete users (library)

SQL> DROP User User Name;

MySQL> DROP Database library name;

External connection

Use ( )

Use Left Join

Query index

SQL> SELECT INDEX_NAME, TABLE_NAME from User_indexes;

MySQL> Show Index from the Name [from library name];

Wildcard

"%"

"%"with"_"

SQL syntax

Select Selection_List Select which column from Table_List Select line where primary_constraint line must meet what condition GROUP BY Grouping_COLUMNS How to Group HAVING Secondary_ConstRAINT You must satisfy the second condition You must satisfy the order by sorting_columns how to sort the results

Which columns FROM table_list SELECT selection_list choose to select rows WHERE primary_constraint line must meet where how what conditions GROUP BY grouping_columns how to group results HAVING secondary_constraint line must meet a second condition ORDER BY sorting_columns limited sort the results LIMIT count results