2. DDL – Data Definition Language

1. Database operation

-- The "[xxx]" are optional.

-- Show all databases
SHOW DATABASES;
-- show current database you used.
SELECT DATABASE();
-- create a new database（utf8mb4 utf8mb4_general_ci）
CREATE DATABASE [IF NOT EXISTS] databasename [DEFAULT CHARSET charset] [COLLATE collate];
-- delete database
DROP DATABASE [IF EXISTS] databasename;
-- select a database for operation
USE databasename;

2. Table Operation - Create & Query

-- Create table
CREATE TABLE user(
    id int,
    uname varchar(20) comment 'username',
    pwd varchar(24) comment 'password'
) comment 'user table';

-- show all tables
show tables;

-- show description of table
desc user;

-- show the creation SQL of table
show create table user;

3. Table Operation - Alter

-- Add a new column
Alter TABLE tableName ADD columnName datatype(size) [comment "remark"];

-- Alter column name
Alter TABLE tableName RENAME TO newTableName
-- Alter datatype
Alter TABLE tableName MODIFY columnName datatype(size);

-- Alter the column name and datatype
Alter TABLE tableName CHANGE oldColumnName newColumnName datatype(size) [comment "remark"];

-- Delete column
Alter Table tableName DROP columnName

-- Delete table
DROP TABLE [IF EXISTS] tableName

-- Rebuild table
TRUNCATE TABLE tableName