Comprehensive Guide to SQL Syntax, Commands, and Advanced Features

This article introduces the basic concepts of relational databases, defining terms such as database, table, schema, column, row, and primary key.

SQL Syntax

SQL (Structured Query Language) follows the ANSI standard; implementations include PL/SQL, Transact‑SQL, etc. The syntax consists of clauses, expressions, predicates, queries, and statements, and is case‑insensitive for keywords.

SELECT * FROM table_name;

Key Points

Statements end with a semicolon.

Whitespace is ignored; statements may span multiple lines.

SQL Classification

Data Definition Language (DDL) defines database objects. Core commands: CREATE , ALTER , DROP .

Data Manipulation Language (DML) accesses and modifies data. Core commands: INSERT , UPDATE , DELETE , SELECT (CRUD).

Transaction Control Language (TCL) manages transactions with COMMIT , ROLLBACK , SAVEPOINT , START TRANSACTION .

Data Control Language (DCL) controls access using GRANT and REVOKE .

CRUD Operations

Insert

INSERT INTO user VALUES (10, 'root', 'root', '[email protected]');
INSERT INTO user(username, password, email) VALUES ('admin', 'admin', '[email protected]');
INSERT INTO user(username) SELECT name FROM account;

Update

UPDATE user SET username='robot', password='robot' WHERE username='root';

Delete

DELETE FROM user WHERE username='robot';
TRUNCATE TABLE user;

Select

SELECT prod_name FROM products;
SELECT prod_id, prod_name, prod_price FROM products;
SELECT * FROM products;
SELECT DISTINCT vend_id FROM products;
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0,5;
SELECT * FROM mytable LIMIT 2,3;

Subqueries

Subqueries can be nested within SELECT, INSERT, UPDATE, DELETE, or other subqueries and are placed inside parentheses.

SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (
    SELECT cust_id FROM orders WHERE order_num IN (
        SELECT order_num FROM orderitems WHERE prod_id='RGAN01'
    )
);

Joins and Unions

Joins combine rows from multiple tables based on related columns. Types include INNER JOIN, LEFT/RIGHT JOIN, NATURAL JOIN, and SELF JOIN.

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
SELECT * FROM customers LEFT JOIN orders ON customers.cust_id = orders.cust_id;

UNION merges result sets of compatible queries, removing duplicates unless UNION ALL is used.

SELECT cust_name, cust_contact, cust_email FROM customers WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email FROM customers WHERE cust_name='Fun4All';

Functions

Common MySQL functions are grouped by purpose: text handling (e.g., LEFT() , UPPER() ), date/time (e.g., NOW() , DATE_ADD() ), numeric (e.g., SIN() , ROUND() ), and aggregation (e.g., AVG() , COUNT() ).

Sorting and Grouping

Use ORDER BY to sort results (ASC/DESC) and GROUP BY to aggregate rows, optionally filtering groups with HAVING .

SELECT * FROM products ORDER BY prod_price DESC, prod_name ASC;
SELECT cust_name, COUNT(*) AS addr_num FROM Customers GROUP BY cust_name HAVING COUNT(*) >= 1;

Constraints

Constraints enforce data integrity: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

Transactions

START TRANSACTION;
INSERT INTO user VALUES (1,'root1','root1','[email protected]');
SAVEPOINT updateA;
INSERT INTO user VALUES (2,'root2','root2','[email protected]');
ROLLBACK TO updateA;
COMMIT;

Permission Control

CREATE USER myuser IDENTIFIED BY 'mypassword';
GRANT SELECT, INSERT ON *.* TO myuser;
REVOKE SELECT, INSERT ON *.* FROM myuser;
DROP USER myuser;

Stored Procedures

DELIMITER ;;
CREATE PROCEDURE proc_adder(IN a INT, IN b INT, OUT sum INT)
BEGIN
    IF a IS NULL THEN SET a = 0; END IF;
    IF b IS NULL THEN SET b = 0; END IF;
    SET sum = a + b;
END;;
DELIMITER ;
CALL proc_adder(2, @b, @s);
SELECT @s AS sum;

Cursors

DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
    DECLARE total INT DEFAULT 0;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT id, name, age FROM cursor_table WHERE age > 30;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    FETCH cur INTO sid, sname, sage;
    WHILE NOT done DO
        SET total = total + 1;
        FETCH cur INTO sid, sname, sage;
    END WHILE;
    CLOSE cur;
    SELECT total;
END $
DELIMITER ;
CALL getTotal();

Triggers

DELIMITER $
CREATE TRIGGER trigger_insert_user AFTER INSERT ON user
FOR EACH ROW
BEGIN
    INSERT INTO user_history(user_id, operate_type, operate_time)
    VALUES (NEW.id, 'add a user', NOW());
END $
DELIMITER ;
SHOW TRIGGERS;
DROP TRIGGER IF EXISTS trigger_insert_user;

The article concludes with a reminder to like and share if the content was helpful.