Fundamental SQL Commands and Database Design Basics

SQL Essential Basics This article introduces some essential SQL fundamentals for newcomers and seasoned developers as a study and revision reference.

001 Database Application System Design

1. Planning

2. Requirement Analysis

3. Conceptual Model Design

4. Logical Design

5. Physical Design

6. Program Development and Debugging

7. Operation and Maintenance

002 Create Database

CREATE DATABASE database_name

003 Show Databases

SHOW DATABASES

004 Use Database

USE database_name

005 Drop Database

DROP DATABASE database_name

006 Show Supported Engines

SHOW ENGINES;

007 Show Default Storage Engine

SHOW VARIABLES LIKE 'storage_engine%'

008 Create Table

CREATE TABLE table_name (

field_name data_type,

field_name data_type,

...

field_name data_type

);

009 Describe Table

DESCRIBE table_name

010 Drop Table

DROP TABLE table_name

011 Rename Table

ALTER TABLE old_table_name RENAME TO new_table_name

012 Add Column

ALTER TABLE table_name ADD column_name data_type

013 Show Create Table

SHOW CREATE TABLE table_name

014 Add Column First

ALTER TABLE table_name ADD column_name data_type FIRST

015 Add Column After Specified Column

ALTER TABLE table_name ADD column_name data_type AFTER existing_column_name

016 Drop Column

ALTER TABLE table_name DROP column_name

017 Modify Column

ALTER TABLE table_name MODIFY column_name data_type

018 Change Column Name

ALTER TABLE table_name CHANGE old_column_name new_column_name old_data_type

019 Change Column Name and Definition

ALTER TABLE table_name CHANGE old_column_name new_column_name new_data_type

020 Change Column Order

ALTER TABLE table_name MODIFY column_name_1 data_type FIRST or ALTER TABLE table_name MODIFY column_name_1 data_type AFTER column_name_2

021 NOT NULL Constraint

column_name data_type NOT NULL

022 Default Value

column_name data_type DEFAULT default_value

023 Unique Constraint

1. column_name data_type UNIQUE

2. CONSTRAINT constraint_name UNIQUE (column_name)

024 Primary Key Constraint

column_name data_type PRIMARY KEY

025 Composite Primary Key

CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...)

026 Auto Increment

column_name data_type AUTO_INCREMENT

027 Foreign Key Constraint

CONSTRAINT constraint_name FOREIGN KEY (column_name)

REFERENCES other_table_name(other_column_name)

028 Create Ordinary Index When Creating Table

CREATE TABLE table_name (

column_name INDEX|KEY [index_name] (field_name [(index_length)] [ASC|DESC])

);

029 Create Index on Existing Table

1. CREATE INDEX index_name ON table_name (field_name [(index_length)] [ASC|DESC])

2. ALTER TABLE table_name ADD INDEX|KEY index_name (field_name [(index_length)] [ASC|DESC])

030 Create Unique Index When Creating Table

CREATE TABLE table_name (

column_name UNIQUE INDEX|KEY [index_name] (field_name [(index_length)] [ASC|DESC])

);

Java Architect Journey

mush

Read and think, we recommend daily articles worth deep contemplation!

A platform focused on providing Java engineers with technical dry goods, helping them grow from junior to senior, advancing on the architect path together.

More ad‑free quality articles in the mini‑program, welcome to follow!