SQL Syntax Overview

SQL operates as a structured query language with specific rules:

• Commands are typically line-based
• Statements require termintaors: ;, \g, or \G (for vertical result display)
• Keywords should be enclosed in backticks if used as identifiers

Basic command patterns:

-- Structure creation
CREATE structure_type structure_name structure_details;

-- Structure display
SHOW structure_types;
SHOW CREATE structure_type structure_name;

-- Data manipulation
INSERT INTO table_name VALUES (...);
SELECT FROM table_name;
UPDATE table_name SET ...;
DELETE FROM table_name;

SQL operations are categorized by database object level: database operations, table operations, and data operations.

Database Operations

Creating Databases

Create storage containers for data:

CREATE DATABASE database_name [database_options];

Examples:

-- Basic database creation
CREATE DATABASE inventory_db;

-- Database with specific character set
CREATE DATABASE sales_db CHARACTER SET utf8mb4;

-- Database with character set and collation
CREATE DATABASE users_db CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

Database names should contain letters, numbers, and underscores, starting with a letter. Each database corresponds to a directory in the storage location.

Viewing Databases

Display existing databases:

-- List all databases
SHOW DATABASES;

-- View creation statement for specific database
SHOW CREATE DATABASE inventory_db;

Selecting Databases

Set the active database for subsequent operations:

USE inventory_db;

Modifying Databases

Alter database options (name changes require recreation):

ALTER DATABASE database_name database_options;

Examples:

-- Change character set
ALTER DATABASE sales_db CHARSET gbk;

-- Change character set and collation
ALTER DATABASE users_db CHARSET gbk COLLATE gbk_chinese_ci;

Removing Databases

Delete databases and all contained data:

DROP DATABASE database_name;

Table Operations

Creating Tables

Define table structure with fields:

CREATE TABLE [database_name.]table_name (
    column_name column_type,
    ...
    column_name column_type
) table_options;

Examples:

-- Create table in specific database
CREATE TABLE inventory_db.products (
    product_name VARCHAR(100)
);

-- Create table after selecting database
USE sales_db;
CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(100),
    order_date DATE
);

-- Create table with options
CREATE TABLE users (
    username VARCHAR(50)
) ENGINE=InnoDB CHARSET=utf8mb4;

Displaying Tables

View table information:

-- Show all tables in current database
SHOW TABLES;

-- Show tables from specific database
SHOW TABLES FROM inventory_db;

-- Show tables matching pattern
SHOW TABLES LIKE 'prod%';

-- View table creation statement
SHOW CREATE TABLE products;

Examining Table Structure

View detailed field information:

DESC table_name;
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

Modifying Tables

Alter table names or options:

-- Rename table
RENAME TABLE old_name TO new_name;

-- Change table options
ALTER TABLE table_name table_options;

Examples:

RENAME TABLE products TO items;
ALTER TABLE users CHARSET utf8;

Field Modifications

Adding Fields

Append new columns to existing tables:

ALTER TABLE table_name ADD [COLUMN] column_name column_type [column_properties] [position];

Examples:

-- Add column at end
ALTER TABLE users ADD birth_date DATE;

-- Add column at beginning
ALTER TABLE users ADD user_id INT FIRST;

-- Add column after specific field
ALTER TABLE users ADD email VARCHAR(100) AFTER username;

Renaming Fields

Change column names:

ALTER TABLE table_name CHANGE old_column_name new_column_name column_type [properties] [position];

Example:

ALTER TABLE users CHANGE birth_date date_of_birth DATE;

Modifying Fields

Alter column properties:

ALTER TABLE table_name MODIFY column_name column_type [properties] [position];

Example:

ALTER TABLE users MODIFY email VARCHAR(150) AFTER user_id;

Removing Fields

Delete columns and associated data:

ALTER TABLE table_name DROP column_name;

Example:

ALTER TABLE users DROP date_of_birth;

Data Operations

Inserting Data

Add records to tibles:

-- Insert all fields
INSERT INTO table_name VALUES (value_list);

-- Insert specific fields
INSERT INTO table_name (column_list) VALUES (value_list);

Examples:

-- Complete record insertion
INSERT INTO users VALUES (1, 'john@example.com', 'John', 'Doe');

-- Partial field insertion
INSERT INTO users (user_id, username, email) VALUES (2, 'jane_doe', 'jane@example.com');

Querying Data

Retrieve data from tables:

SELECT *|column_list FROM table_name [WHERE conditions];

Examples:

-- Retrieve all data
SELECT * FROM users;

-- Retrieve specific columns
SELECT username, email FROM users;

-- Retrieve with condition
SELECT * FROM users WHERE user_id = 1;

Updating Data

Modify existing records:

UPDATE table_name SET column = value [, column = value] [WHERE conditions];

Examples:

-- Update all records
UPDATE users SET status = 'active';

-- Conditional update
UPDATE users SET email = 'new@example.com', status = 'verified' WHERE user_id = 1;

Deleting Data

Remove records from tables:

DELETE FROM table_name [WHERE conditions];

Example:

DELETE FROM users WHERE user_id = 2;