Master SQL Basics: From Core Concepts to Advanced Queries and Database Management

Basic Concepts

Key relational‑database terms:

Database – a container that stores organized data (usually one or more files).

Table – a structured list of rows of a specific type.

Schema – layout and properties 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 – one or a set of columns whose values uniquely identify each row.

SQL Syntax

SQL follows the ANSI standard; vendors provide extensions such as PL/SQL (Oracle) and T‑SQL (SQL Server).

Structure

Clause – a component of a statement or query (optional in some cases).

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

Predicate – supplies conditions for three‑valued logic (TRUE/FALSE/UNKNOWN) or Boolean evaluation.

Query – retrieves data based on specific conditions.

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

Key Points

SQL keywords are case‑insensitive; identifier case‑sensitivity depends on the DBMS configuration.

Multiple statements must be terminated with a semicolon ( ;).

Whitespace is ignored; statements may be written on a single line or split across lines.

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

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

Supported comment styles:

## comment 1
-- comment 2
/* comment 3 */

CRUD Operations

INSERT

Insert a complete 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 SELECT query:

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 a table (remove all rows):

TRUNCATE TABLE user;

SELECT

Single column: SELECT prod_name FROM products; Multiple columns: SELECT prod_id, prod_name, prod_price FROM products; All columns: SELECT * FROM products; Distinct values: SELECT DISTINCT vend_id FROM products; Limit first 5 rows: SELECT * FROM mytable LIMIT 5; Offset/limit rows 3‑5:

SELECT * FROM mytable LIMIT 2, 3;

Subqueries

A subquery is a nested SELECT used inside another query (SELECT, INSERT, UPDATE, DELETE). It must be enclosed in parentheses and is usually placed in the WHERE clause.

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

Common comparison operators: =, <>, >, <, >=, <=, BETWEEN, LIKE, IN.

LIKE and Wildcards

LIKE '%bean bag%'

– matches any string containing “bean bag”. LIKE '__ inch teddy bear' – matches strings where the first two characters are any characters, followed by “ inch teddy bear”.

Joins and Unions

JOIN Types

INNER JOIN – returns rows with matching values; without a join condition it produces a Cartesian product.

SELF JOIN – an inner join where a table joins to itself.

NATURAL JOIN – automatically joins columns with the same name.

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

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

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

UNION

Combines the result sets of two or more SELECT statements.

All queries must have the same number of columns and compatible data types.

Column names are taken from the first query. UNION removes duplicate rows; UNION ALL retains them.

Only one ORDER BY clause is allowed, placed at the end.

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 (MySQL examples)

Text Functions

LEFT(str, n)

/ RIGHT(str, n) – return the leftmost/rightmost n characters. LOWER(str) / UPPER(str) – convert to lower‑case or upper‑case. LTRIM(str) / RTRIM(str) – remove leading or trailing spaces. LENGTH(str) – return string length. SOUNDEX(str) – return a phonetic representation.

SELECT * FROM mytable WHERE SOUNDEX(col1) = SOUNDEX('apple');

Date and Time Functions

AddDate(date, INTERVAL n DAY)

– add days/weeks. AddTime(time, INTERVAL n HOUR) – add hours/minutes. CurDate() – current date. CurTime() – current time. Date(dateTime) – date part. Time(dateTime) – time part. DateDiff(unit, date1, date2) – difference between two dates. Date_Add(date, INTERVAL ...) – flexible date arithmetic. Date_Format(date, format) – format a date/time string. Day(date), Month(date), Year(date) – extract components. Now() – current date and time.

SELECT NOW();

Numeric Functions

SIN(x)

, COS(x), TAN(x) – trigonometric functions. ABS(x) – absolute value. SQRT(x) – square root. MOD(a,b) – remainder. EXP(x) – exponential. PI() – π constant. RAND() – random number.

Aggregate Functions

AVG(expr)

– average (ignores NULL). COUNT(expr) – row count. MAX(expr), MIN(expr) – maximum/minimum. SUM(expr) – sum of values.

SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;

Ordering and Grouping

ORDER BY

Sorts result sets; ASC is default, DESC for descending. Multiple columns can be sorted with different directions.

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

GROUP BY

Groups rows for aggregation. The GROUP BY clause determines the grouping columns; aggregate functions operate on each group.

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers
GROUP BY cust_name;

HAVING

Filters groups after aggregation (similar to WHERE but applied to grouped results).

SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;

Data Definition (DDL)

Database

CREATE DATABASE test;
DROP DATABASE test;
USE test;

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='用户表';

CREATE TABLE vip_user AS SELECT * FROM user;
DROP TABLE user;

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;

View

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

Index

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

Constraints

Common constraint types: 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='用户表';

Transaction Control (TCL)

MySQL defaults to autocommit. Use START TRANSACTION (or BEGIN) to begin a transaction, COMMIT to make changes permanent, and ROLLBACK to revert. Savepoints allow partial rollbacks.

-- Start a transaction
START TRANSACTION;

-- Insert operation A
INSERT INTO `user` VALUES (1,'root1','root1','[email protected]');

-- Create a savepoint
SAVEPOINT updateA;

-- Insert operation B
INSERT INTO `user` VALUES (2,'root2','root2','[email protected]');

-- Roll back to savepoint (only A remains)
ROLLBACK TO updateA;

-- Commit the transaction
COMMIT;

Permission Control (DCL)

Grant and revoke privileges at server, database, table, column, or routine level.

USE mysql;
SELECT user FROM user;

CREATE USER myuser IDENTIFIED BY 'mypassword';
UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;
DROP USER myuser;

SHOW GRANTS FOR myuser;
GRANT SELECT, INSERT ON *.* TO myuser;
REVOKE SELECT, INSERT ON *.* FROM myuser;
SET PASSWORD FOR myuser = 'mypass';

Stored Procedures

Procedures encapsulate a series of SQL statements and can have IN, OUT, and INOUT parameters.

DROP PROCEDURE IF EXISTS proc_adder;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(
  IN a INT,
  IN b INT,
  OUT sum INT
)
BEGIN
  DECLARE c INT;
  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 ;

SET @b = 5;
CALL proc_adder(2, @b, @s);
SELECT @s AS sum;

Cursors

Cursors enable row‑by‑row processing inside stored procedures.

DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
  DECLARE total INT;
  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;
  SET total = 0;
  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

Triggers execute automatically when a specified event (INSERT, UPDATE, DELETE) occurs on a table. Timing can be BEFORE or AFTER . Use NEW and OLD to reference the affected row.

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;