Comprehensive Guide to SQL Syntax, Operations, and Advanced Database Features

Basic Concepts

Defines core database terminology such as database, table, schema, column, row, and primary key.

SQL Syntax

SQL (Structured Query Language) is governed by the ANSI standard. Its structure consists of clauses, expressions, predicates, queries, and statements.

DML Operations (Data Manipulation Language)

Typical commands for inserting, updating, deleting, and selecting data.

INSERT INTO user VALUES (10, 'root', 'root', '[email protected]');
UPDATE user SET username='robot', password='robot' WHERE username='root';
DELETE FROM user WHERE username='robot';
SELECT * FROM products LIMIT 5;

DDL Operations (Data Definition Language)

Commands for defining and modifying database objects.

CREATE DATABASE test;
CREATE TABLE user (id INT UNSIGNED NOT NULL AUTO_INCREMENT 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 user ADD age INT(3);
DROP TABLE user;

Functions

Examples of text, date/time, and numeric functions.

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

Joins and Unions

Various join types and set operations.

SELECT vend_name, prod_name 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;
SELECT cust_name FROM customers UNION SELECT cust_name FROM suppliers;

Grouping and Ordering

Using GROUP BY, HAVING, and ORDER BY to aggregate and sort results.

SELECT cust_name, COUNT(*) AS addr_num FROM Customers GROUP BY cust_name ORDER BY cust_name DESC;
SELECT * FROM products WHERE prod_price BETWEEN 3 AND 5 ORDER BY prod_price DESC;

Transactions

Control of atomic operations with START TRANSACTION, SAVEPOINT, ROLLBACK, and COMMIT.

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 Management

Creating users and granting/revoking privileges.

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

Stored Procedures

Defining procedures with IN, OUT parameters and invoking 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;

Cursors

Iterating over result sets within a procedure.

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;

Triggers

Automatic actions on table events using NEW and OLD references.

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 ;