SQL Basics: Concepts, Syntax, and Common Commands

This article introduces the general syntax of relational databases, focusing on usage rather than underlying features or theory.

1. Basic Concepts

Database Terminology

database – a container that stores organized data, usually as one or more files.

table – a structured list of a specific type of data.

schema – information about the 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 – a column (or set of columns) whose values uniquely identify each row.

SQL Syntax

SQL (Structured Query Language) is governed by the ANSI standard; individual DBMSs provide extensions such as PL/SQL or T‑SQL.

SQL Syntax Structure

SQL statements consist of the following components:

clause – building blocks of statements and queries (some are optional).

expression – produces scalar values or references to columns/rows.

predicate – supplies a condition for three‑valued logic (true/false/unknown).

query – retrieves data based on conditions.

statement – can affect schema, data, transactions, sessions, etc.

Key Points

SQL keywords are case‑insensitive, but identifiers (table names, column names, values) may be case‑sensitive depending on the DBMS and its configuration.

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

All whitespace is ignored; statements can be written on a single line or split across many lines.

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

SQL supports three comment styles: ## comment 1 -- comment 2 /* comment 3 */

SQL Classification

Data Definition Language (DDL)

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

Core DDL commands: CREATE , ALTER , DROP .

Data Manipulation Language (DML)

DML performs operations on data within existing objects.

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

Transaction Control Language (TCL)

TCL manages transactions, grouping DML changes into logical units.

Core TCL commands: COMMIT , ROLLBACK .

Data Control Language (DCL)

DCL controls access permissions.

Core DCL commands: GRANT , REVOKE .

2. CRUD Operations

CRUD (Create, Read, Update, Delete) represents the fundamental database operations.

Insert Data

INSERT INTO adds new records.

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 using a SELECT 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 (remove all rows)

TRUNCATE TABLE user;

Query Data

Common clauses include DISTINCT , LIMIT , ASC , DESC .

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 results

-- first 5 rows
SELECT * FROM mytable LIMIT 5;
-- rows 3‑5
SELECT * FROM mytable LIMIT 2, 3;

3. Subqueries

A subquery is a query nested inside a larger query; it can appear in SELECT , INSERT , UPDATE , or DELETE statements.

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

WHERE Clause

The WHERE clause filters records based on a true/false condition and can be used with SELECT , UPDATE , and DELETE .

Operator

Description

=

equal

<>

not equal (sometimes written

!=

)

>

greater than

<

less than

>=

greater than or equal

<=

less than or equal

BETWEEN

within a range

LIKE

pattern matching

IN

specifies multiple possible values for a column

Examples

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

IN and BETWEEN

SELECT * FROM products
WHERE vend_id IN ('DLL01', 'BRS01');

SELECT * FROM products
WHERE prod_price BETWEEN 3 AND 5;

Logical Operators

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

SELECT *
FROM products
WHERE prod_price NOT BETWEEN 3 AND 5;

LIKE Operator

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '%bean bag%';

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

4. Joins and Unions

JOIN

JOIN combines rows from two or more tables based on a related column.

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

Other join types (self‑join, natural join, left/right join) are demonstrated similarly.

UNION

UNION merges the result sets of multiple SELECT statements with identical column counts and compatible data types.

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

⚠ Note: Function availability varies across DBMSs; examples use MySQL.

String Functions

Function

Description

LEFT()

,

RIGHT()

extract characters from the left or right side

LOWER()

,

UPPER()

convert case

LTRIM()

,

RTRIM()

trim spaces

LENGTH()

string length

SOUNDEX()

phonetic representation

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

Date & Time Functions

Function

Description

AddDate()

add days/weeks to a date

AddTime()

add hours/minutes to a time

CurDate()

current date

CurTime()

current time

Date()

date part of a datetime

DateDiff()

difference between two dates

Date_Add()

flexible date arithmetic

Date_Format()

format a date/time string

Now()

current date and time

SELECT NOW();
-- returns e.g., 2018-04-14 20:25:11

Numeric Functions

Function

Description

SIN()

sine

COS()

cosine

TAN()

tangent

ABS()

absolute value

SQRT()

square root

MOD()

remainder

EXP()

exponential

PI()

π constant

RAND()

random number

Aggregate Functions

Function

Description

AVG()

average value (ignores NULL)

COUNT()

row count

MAX()

maximum value

MIN()

minimum value

SUM()

sum of values

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

6. Ordering and Grouping

ORDER BY

Sorts result sets; default is ascending ( ASC ), descending is DESC . Multiple columns can be specified.

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

GROUP BY

Groups rows for aggregation; often used with COUNT , MAX , SUM , etc.

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

HAVING

Filters groups after aggregation, similar to WHERE but operates on aggregated results.

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

7. Data Definition (DDL)

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

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

Constraints enforce data rules (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='用户表';

8. Transaction Control (TCL)

-- start transaction
START TRANSACTION;

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

SAVEPOINT updateA;

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

ROLLBACK TO updateA; -- undo B

COMMIT; -- only A persists

9. Permission Control (DCL)

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

10. Stored Procedures

DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 ;

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

11. Cursors

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

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

—END—