Comprehensive Guide to SQL Syntax, Commands, and Operations

1. Basic Concepts

Database – a container for organized data; Table – a structured list of a specific data type; Schema – layout and attributes of databases and tables; Column – a field in a table; Row – a record; Primary key – a column (or set) uniquely identifying each row.

2. SQL Syntax

SQL (Structured Query Language) follows the ANSI standard; various DBMS implement dialects such as PL/SQL or T‑SQL.

SQL Structure

Clauses, expressions, predicates, queries, and statements compose an SQL command. Keywords are case‑insensitive, statements end with a semicolon, and whitespace is ignored.

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

SQL Categories

Data Definition Language (DDL)

Core commands: CREATE , ALTER , DROP .

Data Manipulation Language (DML)

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

Transaction Control Language (TCL)

Core commands: COMMIT , ROLLBACK .

Data Control Language (DCL)

Core commands: GRANT , REVOKE (manage access privileges).

3. 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 FROM products;
SELECT * FROM products;
SELECT DISTINCT vend_id FROM products;
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0,5;

4. Subqueries

Subqueries can be nested inside SELECT, INSERT, UPDATE, or DELETE statements.

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

5. Joins and Unions

JOIN types include INNER, LEFT, RIGHT, SELF, and NATURAL. UNION combines result sets vertically.

SELECT vend_name, prod_name FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;
SELECT * FROM Products NATURAL JOIN Customers;
SELECT cust_name, cust_contact FROM customers UNION SELECT cust_name, cust_contact FROM customers WHERE cust_name='Fun4All';

6. Functions

Text functions: LEFT() , RIGHT() , LOWER() , UPPER() , LTRIM() , RTIM() , LENGTH() , SOUNDEX() . Date/Time functions: NOW() , CURDATE() , DATE_ADD() , DATE_FORMAT() , etc. Numeric functions: SIN() , COS() , ABS() , RAND() . Aggregate functions: AVG() , COUNT() , MAX() , MIN() , SUM() .

7. Ordering and Grouping

ORDER BY sorts results (ASC/DESC, multiple columns). GROUP BY groups rows for aggregation; HAVING filters grouped results.

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

8. Constraints

Constraint types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.

9. 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;

10. Permissions

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

11. 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);

12. Cursors

DECLARE cur CURSOR FOR SELECT id,name,age FROM cursor_table WHERE age>30;
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;

13. 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 ;