Master SQL: Essential Syntax, Queries, and Advanced Techniques

1. Basic Concepts

Database Terminology

Database : a container that stores organized data, usually as a file or set of files.

Table : a structured list of a specific type of data.

Schema : information about the layout and characteristics of databases and tables.

Column : a field in a table; tables consist of one or more columns.

Row : a record in a table.

Primary Key : a column or set of columns that uniquely identifies each row.

SQL Syntax

SQL (Structured Query Language) is standardized by ANSI; different DBMS implement their own extensions such as PL/SQL or T‑SQL.

SQL Syntax Structure

SQL syntax structure includes:

Clause – components of statements and queries (some optional).

Expression – produces scalar values or works with table columns and rows.

Predicate – supplies conditions for three‑valued logic (true/false/unknown).

Query – retrieves data based on conditions.

Statement – can affect data, control transactions, program flow, connections, sessions, or diagnostics.

SQL Syntax Highlights

SQL statements are case‑insensitive, but identifier case‑sensitivity depends on the DBMS and configuration.

Multiple statements must be separated by a semicolon (;).

All whitespace is ignored; statements may be written on one line or split across lines.

-- single‑line SQL statement
UPDATE user SET username='robot', password='robot' WHERE username='root';

-- multi‑line SQL statement
UPDATE user
SET username='robot', password='robot'
WHERE username='root';

SQL supports three comment styles:

# comment1
-- comment2
/* comment3 */

SQL Classification

Data Definition Language (DDL)

DDL defines database objects such as databases, tables, views, and indexes.

Core commands: CREATE , ALTER , DROP .

Data Manipulation Language (DML)

DML is used to access and modify data.

Core commands: INSERT , UPDATE , DELETE , SELECT (CRUD).

Transaction Control Language (TCL)

TCL manages transactions, allowing grouping of DML statements into logical units.

Core commands: COMMIT , ROLLBACK .

Data Control Language (DCL)

DCL controls access permissions.

Core commands: GRANT , REVOKE .

2. CRUD Operations

Insert Data

Insert a full row:

INSERT INTO user VALUES (10, 'root', 'root', '[email protected]');

Insert specific columns:

INSERT INTO user (username, password, email) VALUES ('admin', 'admin', '[email protected]');

Insert from a query:

INSERT INTO user (username) SELECT name FROM account;

Update Data

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

Delete Data

DELETE FROM user WHERE username='robot';

TRUNCATE TABLE clears all rows:

TRUNCATE TABLE user;

3. Query Data

SELECT retrieves data; DISTINCT returns unique values; LIMIT restricts row count.

SELECT prod_name FROM products;
SELECT DISTINCT vend_id FROM products;
SELECT * FROM mytable LIMIT 5;

WHERE Clause

Filters records based on a condition; can be used with SELECT, UPDATE, DELETE.

SELECT * FROM Customers WHERE cust_name = 'Kids Place';
UPDATE Customers SET cust_name='Jack Jones' WHERE cust_name='Kids Place';
DELETE FROM Customers WHERE cust_name='Kids Place';

Logical Operators

AND – both conditions must be true.

OR – either condition may be true.

NOT – negates a condition.

SELECT * FROM products WHERE vend_id='DLL01' AND prod_price <= 4;
SELECT * FROM products WHERE vend_id='DLL01' OR vend_id='BRS01';
SELECT * FROM products WHERE prod_price NOT BETWEEN 3 AND 5;

LIKE Operator

Pattern matching for strings using % (any sequence) and _ (single character).

SELECT * FROM products WHERE prod_name LIKE '%bean bag%';
SELECT * FROM products WHERE prod_name LIKE '__ inch teddy bear';

4. Joins and Unions

JOIN Types

INNER JOIN – returns rows with matching values in both tables.

LEFT JOIN – returns all rows from the left table and matching rows from the right.

RIGHT JOIN – returns all rows from the right table and matching rows from the left.

NATURAL JOIN – automatically joins on columns with the same name.

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

UNION

Combines results of multiple SELECT statements with the same column count and order.

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';

5. Functions

Examples of MySQL functions (text, date/time, numeric, aggregation). Note that functions vary across DBMS.

SELECT * FROM mytable WHERE SOUNDEX(col1) = SOUNDEX('apple');
SELECT NOW();
SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;

6. Ordering and Grouping

ORDER BY sorts result sets; GROUP BY aggregates rows.

SELECT * FROM products ORDER BY prod_price DESC, prod_name ASC;
SELECT cust_name, COUNT(cust_address) AS addr_num FROM Customers GROUP BY cust_name;
SELECT cust_name, COUNT(*) AS num FROM Customers WHERE cust_email IS NOT NULL GROUP BY cust_name HAVING COUNT(*) >= 1;

7. Data Definition

Create Database

CREATE DATABASE test;

Create Table

CREATE TABLE user (
  id INT(10) UNSIGNED NOT NULL COMMENT 'Id',
  username VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
  password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
) COMMENT='用户表';

Alter Table

ALTER TABLE user ADD age INT(3);
ALTER TABLE user DROP COLUMN age;
ALTER TABLE user MODIFY COLUMN age TINYINT;
ALTER TABLE user ADD PRIMARY KEY (id);
ALTER TABLE user DROP PRIMARY KEY;

Create View

CREATE VIEW top_10_user_view AS SELECT id, username FROM user WHERE id < 10;

Create Index

CREATE INDEX user_index ON user(id);
CREATE UNIQUE INDEX user_index ON user(id);
ALTER TABLE user DROP INDEX user_index;

8. Constraints

Constraints enforce rules on table data (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT).

CREATE TABLE Users (
  Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT '用户名',
  Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '邮箱地址',
  Enabled TINYINT(4) DEFAULT NULL COMMENT '是否有效',
  PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

9. Transaction Management

MySQL defaults to autocommit; use START TRANSACTION, SAVEPOINT, ROLLBACK, COMMIT to control transactions.

-- start transaction
START TRANSACTION;
-- insert operation A
INSERT INTO `user` VALUES (1, 'root1', 'root1', '[email protected]');
SAVEPOINT updateA;
-- insert operation B
INSERT INTO `user` VALUES (2, 'root2', 'root2', '[email protected]');
ROLLBACK TO updateA; -- undo B
COMMIT; -- only A persists

10. Permission Control

GRANT and REVOKE manage access at server, database, table, column, and routine levels.

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

11. Stored Procedures

Procedures encapsulate reusable SQL logic; use DELIMITER to define them.

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, 5, @s);
SELECT @s AS sum;

12. Cursors

Cursors iterate over result sets within procedures.

DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
  DECLARE total INT DEFAULT 0;
  DECLARE sid INT; DECLARE sname VARCHAR(10); DECLARE sage INT;
  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();

13. Triggers

Triggers execute automatically on INSERT, UPDATE, or DELETE events.

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;