Master MySQL: From DDL Basics to Advanced Table Constraints

Introduction

SQL language classification includes DDL, DML, DQL, DCL, and TCL, each serving different purposes such as defining schema, manipulating data, querying, controlling access, and managing transactions.

SQL Language Types

DDL – Data Definition Language (create, drop, alter)

DML – Data Manipulation Language (insert, delete, update)

DQL – Data Query Language (select)

DCL – Data Control Language (grant, revoke)

TCL – Transaction Control Language (set autocommit, start transaction, commit, rollback, savepoint)

Database Management

Create Database

create database [if not exists] dbname;

Drop Database

drop database [if exists] dbname;

When dropping, use if exists to avoid errors; similarly, check existence before creating.

Table Management

Create Table

create table tbname(
    column_name_1 column_type_1[(n)] [constraints] [comment 'comment1'],
    column_name_2 column_type_2[(n)] [constraints] [comment 'comment2'],
    column_name_3 column_type_3[(n)] [constraints] [comment 'comment3']
) [table_options];

Key points: column name is required, type (CHAR, INT, etc.) is mandatory, length n is optional, constraints include NOT NULL, DEFAULT, PRIMARY KEY, etc., and comments describe the column.

Constraints

NOT NULL

Ensures a column cannot contain NULL values.

DEFAULT

Provides a default value when none is supplied during INSERT.

PRIMARY KEY

Uniquely identifies each row; can be defined inline or at the end of the column list, and composite primary keys are allowed.

FOREIGN KEY

Creates a relationship between tables: foreign key(col) references other_table(other_col). The referenced column must be a primary key.

UNIQUE

Ensures column(s) contain unique values; can be single‑column or multi‑column.

AUTO_INCREMENT

Automatically generates sequential numeric values, starting at 1 by default; the start value and increment step can be configured.

Altering Tables

Add Column

alter table tname add column column_name column_type [constraints];

Modify Column

alter table tname modify column col_name new_type [constraints];

Change Column (rename)

alter table tname change column col_name new_name new_type [constraints];

Drop Column

alter table tname drop column col_name;

Renaming and Dropping Tables

alter table old_name rename to new_name;
drop table [if exists] tname;

Copying Tables

Copy only structure: create table new_table like existing_table; Copy structure and data:

create table new_table as select * from existing_table;

Field Management Examples

Examples demonstrate creating databases, tables, inserting data, handling constraint violations (e.g., duplicate primary key, foreign key errors), and using desc to view table definitions.

Conclusion

The article provides a comprehensive, hands‑on reference for MySQL database and table operations, covering creation, alteration, constraints, and copying techniques.