Common MySQL Commands and Comparison with Oracle

Background

As a tester, mastering basic SQL commands is essential; this guide compiles frequently used MySQL commands and compares them with Oracle equivalents.

Basic Commands

net start mysql – start MySQL service

net stop mysql – stop MySQL service

quit; – exit the client

flush privileges; – reload privilege tables

grant select on tableName to 'username'@'host' – grant privileges

Mysqladmin -u root -p old_password password new_password – change password

mysql -h host_address -u username -p password – connect to MySQL

Information Retrieval

SELECT user() – show current user

SELECT database() – show current database

SELECT version() – show version

SHOW TABLES – list tables

CREATE DATABASE db_name – create database

DROP DATABASE db_name – delete database

Table Operations

DESCRIBE table_name (or DESC table_name ) – show table structure

CREATE TABLE table_name (col1 VARCHAR(20), col2 CHAR(1)) – create table

DROP TABLE table_name – delete table

ALTER TABLE t1 RENAME TO t2 – rename table

SELECT * FROM table_name – query all rows

INSERT INTO table_name VALUES ('', '') – insert rows

UPDATE table_name SET column='value' – update rows

DELETE FROM table_name – delete rows

ALTER TABLE table_name ADD column_name column_type [NOT NULL] – add column

ALTER TABLE table_name MODIFY column_name column_type [NOT NULL] – modify column

ALTER TABLE table_name DROP column_name – drop column

Oracle and MySQL Command Comparison

Date Operations

Oracle: SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual;

MySQL: SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

Oracle: SELECT TO_CHAR(SYSDATE,'hh24-mi-ss') FROM dual;

MySQL: SELECT TIME_FORMAT(NOW(),'%H-%i-%S');

Oracle: SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20180119','yyyymmdd'),1),'yyyy-mm-dd') FROM dual;

MySQL: SELECT DATE_ADD('20180119', INTERVAL 1 MONTH);

String Functions

Oracle: SELECT SUBSTR('123swd',1,5) FROM dual;

MySQL: SELECT SUBSTRING('asdqw2',2);

Oracle concatenates with || or CONCAT (two‑argument only); MySQL uses CONCAT('a','b') which accepts multiple arguments.

Other Differences

Oracle lacks LIMIT ; pagination uses ROWNUM .

Oracle does not have auto‑increment columns; sequences must be created and used manually.

Oracle enforces strict use of single and double quotes.

---

Promotional content follows, highlighting the Qtest testing team and encouraging readers to follow the public account for daily testing technology updates.