SQL statements are categorized into four primary groups based on their functionality:
- DDL (Data Definition Language): Used to define and manage data base structures, including databases, tables, and columns.
- DML (Data Manipulation Language): Used for managing data within tables (inserting, updating, and deleting records).
- DQL (Data Query Language): Used to retrieve data from tables.
- DCL (Data Control Language): Used to manage user access and permissions.
General Syntax Rules
- SQL is case-insensitive, though it is standard practice to write keywords in upercase for readability.
- Every statement must terminate with a semicolon (
;). - Whitespace is ignored; multiple spaces are treated as a single space.
- Comments can be single-line (
--or#) or multi-line (/* comment */).
Database Operations (DDL)
Viewing Databases
To list all databases available on the server:
SHOW DATABASES;
To identify which database you are currently using:
SELECT DATABASE();
Creating Databases
Syntax for creating a new database with optional safety checks and character sets:
CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT CHARSET charset_name] [COLLATE collation_name];
Example:
CREATE DATABASE IF NOT EXISTS inventory_system DEFAULT CHARSET utf8mb4;
Deleting and Using Databases
To remove a database:
DROP DATABASE [IF EXISTS] inventory_system;
To set a database as the active context for subsequent operations:
USE inventory_system;
Table Operations (DDL)
Veiwing Table Structures
To list all tables in the current database:
SHOW TABLES;
To inspect the columns and data types of a specific table:
DESC table_name;
Creating Tables
Tables are defined by specifying column names, data types, and optional comments:
CREATE TABLE user_profile (
id INT COMMENT 'Unique Identifier',
username VARCHAR(30) COMMENT 'Login Name',
age TINYINT UNSIGNED COMMENT 'User Age',
gender CHAR(1) COMMENT 'M/F'
) COMMENT 'User Management Table';
Data Types in MySQL
Numeric Types
| Type | Storage | Description |
|---|---|---|
| TINYINT | 1 Byte | Very small integer (Signed: -128 to 127; Unsigned: 0 to 255) |
| INT | 4 Bytes | Standard integer |
| BIGINT | 8 Bytes | Large integer |
| DOUBLE | 8 Bytes | Double-precision floating point |
| DECIMAL | Varies | Exact fixed-point number for financial data |
String Types
| Type | Description |
|---|---|
| CHAR(n) | Fixed-length string (padded with spaces) |
| VARCHAR(n) | Variable-length string (efficient storage) |
| TEXT | Large block of text data |
Date and Time Types
| Type | Format | Description |
|---|---|---|
| DATE | YYYY-MM-DD | Date only |
| DATETIME | YYYY-MM-DD HH:MM:SS | Date and time combined |
Modifying Table Structures
Adding and Modifying Columns
To add a new field to an existing table:
ALTER TABLE user_profile ADD email VARCHAR(100) COMMENT 'Contact Email';
To change the data type of an existing column:
ALTER TABLE user_profile MODIFY username VARCHAR(50);
To rename a column and change its type simultaneously:
ALTER TABLE user_profile CHANGE username account_name VARCHAR(60) COMMENT 'User Account';
Deleting Columns and Renaming Tables
To remove a column from a table:
ALTER TABLE user_profile DROP COLUMN gender;
To rename the table itself:
ALTER TABLE user_profile RENAME TO member_registry;
Deleting and Truncating Tables
To permanently remove a table:
DROP TABLE [IF EXISTS] member_registry;
To clear all data from a table while keeping its structure intact:
TRUNCATE TABLE member_registry;