Home > Tech > Content

MySQL Command Reference: Key Operations for Developers

Tech 2

Fundamental Concepts

Term	Description
Database	A container for organized data, typically a file or set of files.
Table	A structured list of data of a specific type.
Column	A single field within a table.
Row	A single record in a table.
Datatype	The type of data allowed in a column.
Primary Key	A column (or set of columns) whose values uniquely identify each row.

Clause Order in SELECT Statements

Clause	Purpose	Required?
SELECT	Columns or expressions to return	Yes
FROM	Table(s) to retrieve data from	Only when selecting from tables
WHERE	Row-level filtering	No
GROUP BY	Grouping specification	Only when using aggregates per group
HAVING	Group-level filtering	No
ORDER BY	Sort order for output	No
LIMIT	Number of rows to retrieve	No

Database Interaction: USE, SHOW

Operation	Command
Connect to database	`mysql -u <username> -p -h <hostname> -P <port>`
List databases	`SHOW DATABASES;`
Select a database	`USE <database_name>;`
List tables in current db	`SHOW TABLES;`
Show columns in a table	`SHOW COLUMNS FROM <table_name>;`
Show server status	`SHOW STATUS;`
Show create database stmt	`SHOW CREATE DATABASE <db_name>;`
Show create table stmt	`SHOW CREATE TABLE <table_name>;`
Show user grants	`SHOW GRANTS;`
Show errors	`SHOW ERRORS;`
Show warnings	`SHOW WARNINGS;`
Get help on a command	`help <command>;`
Exit MySQL	`exit;`

Retrieving Data: SELECT

Operation	Command
Single column	`SELECT col FROM tbl;`
Multiple columns	`SELECT col1, col2 FROM tbl;`
All columns	`SELECT * FROM tbl;`
Distinct rows	`SELECT DISTINCT col FROM tbl;`
Limit results (offset, count)	`SELECT col FROM tbl LIMIT 0, 5;`
Fully qualified names	`SELECT tbl.col FROM db.tbl;`

Sorting Results: ORDER BY

Operation	Command
Basic sort	`SELECT col FROM tbl ORDER BY col;`
Multiple columns	`SELECT col1, col2 FROM tbl ORDER BY col1, col2;`
Descending order	`SELECT col FROM tbl ORDER BY col DESC;`
Ascending order	`SELECT col FROM tbl ORDER BY col ASC;`
Highest value	`SELECT col FROM tbl ORDER BY col DESC LIMIT 1;`
Lowest value	`SELECT col FROM tbl ORDER BY col ASC LIMIT 1;`

Filtering: WHERE

Operation	Command
Exact match	`SELECT col FROM tbl WHERE col = value;`
Not equal	`SELECT col FROM tbl WHERE col != value;`
Range (`BETWEEN`)	`SELECT col FROM tbl WHERE col BETWEEN v1 AND v2;`
Null check	`SELECT col FROM tbl WHERE col IS NULL;`

Advanced Filtering: AND, OR, IN, NOT

Operation	Command
AND (all conditions true)	`SELECT col FROM tbl WHERE cond1 AND cond2;`
OR (at least one true)	`SELECT col FROM tbl WHERE cond1 OR cond2;`
IN (specify set of values)	`SELECT col FROM tbl WHERE col IN (v1, v2);`
NOT IN (exclude set)	`SELECT col FROM tbl WHERE col NOT IN (v1, v2);`

Pattern Matching: LIKE, %, _

Operation	Command
Pattern at start/end	`SELECT col FROM tbl WHERE col LIKE 'start%end';`
Pattern anywhere	`SELECT col FROM tbl WHERE col LIKE '%substring%';`
Single character wildcard	`SELECT col FROM tbl WHERE col LIKE 'a_c';`

Regular Expressions: REGEXP

Operation	Command
Case-insensitive	`SELECT col FROM tbl WHERE col REGEXP 'pattern';`
Case-sensitive	`SELECT col FROM tbl WHERE col REGEXP BINARY 'pattern';`

Common Regex Constructs

Construct	Meaning
`text`	Match literal text
`[abc]`	Match any character in brackets (OR)
`[a-z]`	Match any character in range
`\\.`	Match a literal dot (escape)

Blank Metacharacters

Metachar	Description
`\\f`	Form feed
`\\n`	Newline
`\\r`	Carriage return
`\\t`	Tab
`\\v`	Vertical tab

Character Classes

Class	Description
`[:alnum:]`	Alphanumeric (same as `[a-zA-Z0-9]`)
`[:alpha:]`	Alphabetic (`[a-zA-Z]`)
`[:blank:]`	Space or tab (`[\\t]`)
`[:digit:]`	Digits (`[0-9]`)
`[:lower:]`	Lowercase letters (`[a-z]`)
`[:upper:]`	Uppercase letters (`[A-Z]`)

Quantifiers

Symbol	Description
`*`	0 or more
`+`	1 or more
`?`	0 or 1
`{n}`	Exactly n
`{n,}`	n or more
`{n,m}`	Between n and m

Positional Anchors

Anchor	Description
`^`	Start of string (also negation inside `[]`)
`$`	End of string
`[[:<:]]`	Start of word
`[[:>:]]`	End of word

Computed Fields: CONCAT, Aliases, Arithmetic

Operation	Command
Concatenate	`SELECT CONCAT(col1, ' ', col2) FROM tbl;`
With alias	`SELECT CONCAT(col1, col2) AS newcol FROM tbl;`
Arithmetic	`SELECT (price * quantity) AS total FROM tbl;`

Data Processing Functions

Text Functions

Function	Description
`Left()`	Return leftmost characters
`Length()`	Return string length
`Locate()`	Find substring
`Lower()`	Convert to lowercase
`LTrim()`	Remove leading spaces
`Right()`	Return rightmost characters
`RTrim()`	Remove trailing spaces
`Soundex()`	Return SOUNDEX value
`SubString()`	Return substring
`Upper()`	Convert to uppercase

Date/Time Functions

Function	Description
`AddDate()`	Add days, weeks, etc.
`AddTime()`	Add hours, minutes, etc.
`CurDate()`	Current date
`CurTime()`	Current time
`Date()`	Date part of datetime
`DateDiff()`	Difference between two dates
`Date_Add()`	Flexible date arithmetic
`Date_Format()`	Format date as string
`Day()`	Day of month
`DayOfWeek()`	Day of week (1=Sunday)
`Hour()`	Hour part
`Minute()`	Minute part
`Month()`	Month part
`Now()`	Current datetime
`Second()`	Second part
`Time()`	Time part
`Year()`	Year part

Format: Always use YYYY-MM-DD for date strings.

Numeric Functions

Function	Description
`Abs()`	Absolute value
`Cos()`	Cosine
`Exp()`	Exponential
`Mod()`	Modulo (remainder)
`Pi()`	π constant
`Rand()`	Random number 0-1
`Sin()`	Sine
`Sqrt()`	Square root
`Tan()`	Tangent

Aggregate Functions: AVG, MAX, MIN, COUNT, SUM

Operation	Command
Average	`SELECT AVG(col) FROM tbl;`
Maximum	`SELECT MAX(col) FROM tbl;`
Minimum	`SELECT MIN(col) FROM tbl;`
Row count	`SELECT COUNT(col) FROM tbl;`
Sum	`SELECT SUM(col) FROM tbl;`
Distinct average	`SELECT AVG(DISTINCT col) FROM tbl;`

Grouping: GROUP BY

Operation	Command
Basic grouping	`SELECT col, COUNT(*) FROM tbl GROUP BY col;`
With rollup	`SELECT col, COUNT(*) FROM tbl GROUP BY col WITH ROLLUP;`
Filter groups	`SELECT col, COUNT() FROM tbl GROUP BY col HAVING COUNT() > 1;`

Subqueries

Filtering with Subquery

SELECT col1 FROM table_b
WHERE id IN (SELECT id FROM table_a WHERE condition);

Computed Field Subquery

SELECT a.name,
       (SELECT COUNT(*) FROM orders o WHERE o.cust_id = a.id) AS order_count
FROM customers a;

Joins: INNER, OUTER, SELF

Equi-Join (Inner Join)

SELECT a.col, b.col
FROM table_a a, table_b b
WHERE a.key = b.key;

Alternative syntax:

SELECT a.col, b.col
FROM table_a a INNER JOIN table_b b ON a.key = b.key;

Self-Join

SELECT p1.prod_name
FROM products p1, products p2
WHERE p1.vendor_id = p2.vendor_id
  AND p2.prod_id = 'DTNTR';

Left Outer Join

SELECT a.col, b.col
FROM table_a a LEFT OUTER JOIN table_b b ON a.key = b.key;

Right Outer Join

SELECT a.col, b.col
FROM table_a a RIGHT OUTER JOIN table_b b ON a.key = b.key;

Combined Queries: UNION

Operation	Command
Remove duplicates	`SELECT ... UNION SELECT ...;`
Keep duplicates	`SELECT ... UNION ALL SELECT ...;`

Full-Text Search: MATCH, AGAINST

Basic Search

SELECT col FROM tbl WHERE MATCH(col) AGAINST('search text');

Query Expansion

SELECT col FROM tbl WHERE MATCH(col) AGAINST('text' WITH QUERY EXPANSION);

Boolean Mode

SELECT col FROM tbl WHERE MATCH(col) AGAINST('+rabbit +bait' IN BOOLEAN MODE);

Operator	Meaning
`+`	Word must be present
`-`	Word must be absent
`>`	Increase rank
`<`	Decrease rank
`( )`	Group subexpressions
`~`	Negate rank
`*`	Wildcard
`""`	Phrase match

Data Manipulation: INSERT, UPDATE, DELETE

Insert

INSERT INTO tbl (col1, col2) VALUES ('v1', 'v2');

-- Multiple rows
INSERT INTO tbl (col1, col2) VALUES ('v1', 'v2'), ('v3', 'v4');

-- From query
INSERT INTO tbl_a (col1, col2) SELECT col1, col2 FROM tbl_b;

Update

UPDATE tbl SET col1 = 'new_val' WHERE condition;

Delete

DELETE FROM tbl WHERE condition;

-- Faster truncate
TRUNCATE TABLE tbl;

Table Creation and Alteration: CREATE, ALTER, DROP, RENAME

Create Table

CREATE TABLE tbl (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

Add Column

ALTER TABLE tbl ADD newcol VARCHAR(50);

Drop Column

ALTER TABLE tbl DROP COLUMN oldcol;

Foreign Key

ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent (col);

Drop Table

DROP TABLE tbl;

Rename Table

RENAME TABLE old_name TO new_name;

Views

Create View

CREATE VIEW view_name AS
SELECT a.col1, b.col2
FROM table_a a JOIN table_b b ON a.key = b.key;

Stored Procedures

Basic Procedure

CREATE PROCEDURE get_total(IN order_id INT, OUT total DECIMAL(8,2))
BEGIN
    SELECT SUM(price * qty) INTO total FROM items WHERE order_num = order_id;
END;

Execute

CALL get_total(20005, @t);
SELECT @t;

Cursors

CREATE PROCEDURE process_orders()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE o INT;
    DECLARE cur CURSOR FOR SELECT order_num FROM orders;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    REPEAT
        FETCH cur INTO o;
        -- Process each row
    UNTIL done END REPEAT;
    CLOSE cur;
END;

Triggers

INSERT Trigger

CREATE TRIGGER after_insert_orders AFTER INSERT ON orders
FOR EACH ROW
SET @info = NEW.order_num;

DELETE Trigger

CREATE TRIGGER before_delete_orders BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders SELECT OLD.order_num, OLD.order_date, OLD.cust_id;
END;

UPDATE Trigger

CREATE TRIGGER before_update_vendors BEFORE UPDATE ON vendors
FOR EACH ROW
SET NEW.state = UPPER(NEW.state);

Indexes

Type	Example
Primary	`PRIMARY KEY (col)`
Unique	`CREATE UNIQUE INDEX idx_name ON tbl(col)`
Fulltext	`CREATE FULLTEXT INDEX idx_name ON tbl(col)`
Composite	`CREATE INDEX idx_name ON tbl(col1, col2)`

Prefix Index Optimization

-- Full selectivity
SELECT COUNT(DISTINCT col) / COUNT(*) FROM tbl;

-- Prefix selectivity
SELECT COUNT(DISTINCT LEFT(col, n)) / COUNT(*) FROM tbl;

Transactions

START TRANSACTION;
SAVEPOINT sp1;
-- operations
ROLLBACK TO sp1;
-- or COMMIT;

Character Sets and Collations

CREATE TABLE tbl (
    col1 VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci
) DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

User Management

Operation	Command
Create user	`CREATE USER 'user'@'host' IDENTIFIED BY 'pass';`
Grant	`GRANT SELECT ON db.* TO 'user'@'host';`
Revoke	`REVOKE SELECT ON db.* FROM 'user'@'host';`
Set password	`SET PASSWORD FOR 'user'@'host' = PASSWORD('new');`
Drop user	`DROP USER 'user'@'host';`

Maintenance Commands

Operation	Command
Analyze table	`ANALYZE TABLE tbl;`
Check table	`CHECK TABLE tbl;`
Optimize table	`OPTIMIZE TABLE tbl;`
Repair table	`REPAIR TABLE tbl;`

Performance Tips

Use EXPLAIN to analyze query plans.
Avoid SELECT *; fetch only needed columns.
Use UNION instead of complex OR conditions.
Prefer full-text search over LIKE.
Add indexes on columns used in WHERE and JOIN.
Use appropriate data types.
InnoDB is better for transactions; MyISAM for full-text search.

Common Data Types

Type	Description
`CHAR(n)`	Fixed-length string (max 255)
`VARCHAR(n)`	Variable-length string (max 65535 bytes)
`TEXT`	Varible text (max 64KB)
`INT`	Integer (-2^31 to 2^31-1)
`BIGINT`	Large integer
`DECIMAL(p,s)`	Exact decimal with precision
`FLOAT`	Approximate single-precision
`DOUBLE`	Approximate double-precision
`DATE`	Date (1000-01-01 to 9999-12-31)
`DATETIME`	Date and time
`TIMESTAMP`	Timestamp (range smaller than DATETIME)
`BLOB`	Binary large object (max 64KB)

Tags: MySQL

Back to List

Prev: Understanding and Implementing Enumerations in Java

Next: Python Classes, Objects, and Special Methods

Understanding Strong and Weak References in Java

Strong References Strong reference are the most prevalent type of object referencing in Java. When an object has a strong reference pointing to it, the garbage collector will not reclaim its memory. F...

Comprehensive Guide to SSTI Explained with Payload Bypass Techniques

Introduction Server-Side Template Injection (SSTI) is a vulnerability in web applications where user input is improper handled within the template engine and executed on the server. This exploit can r...

Implement Image Upload Functionality for Django Integrated TinyMCE Editor

Django’s Admin panel is highly user-friendly, and pairing it with TinyMCE, an effective rich text editor, simplifies content management significantly. Combining the two is particular useful for bloggi...

Fading Coder