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.