Table Operation

Show All Tables

SHOW TABLES;

Create A Table

CREATE TABLE mytb (
  column_name_1 data_type_1 column_constraint_1,
  column_name_2 data_type_2 column_constraint_2,
  column_name_3 data_type_3 column_constraint_3
);

Column name is similar to the variable name in programming languages.

Data Types

There are a lot of data types, and they can be categorized into different types.

Numeric Types

^* DECIMAL / NUMERIC should be declared in the format of DECIMAL(M,D), where M is the integer part, and D is the fractional part.

Date And Time Types

String / Character Types

TEXT is used to store text, while BLOB is used to store binary data.

Column Constraint

UNIQUE

This constraint ensures all values in the column is unique, but it allows multiple NULL.

NOT NULL

This constraint disallow the column to have NULL values.

PRIMARY KEY

Each table can have only one primary key. It has properties of both UNIQUE and NOT NULL.

DEFAULT

Used to set the default value of a column.

FOREIGN KEY

It links two keys in different tables together.

Suppose that there is table a and table b, and there is a column in table a named id:

CREATE TABLE a (
  id INT PRIMARY KEY
);

And we want table b to have a reference or a foreign key to a.id, then the statement can be:

CREATE TABLE b (
  id INT PRIMARY KEY,
  FOREIGN KEY fk_a_id REFERENCES a(id)
);

Delete / Drop A Table

DROP TABLE mytb;

Change / Alter A Table

Add A Column

ALTER TABLE mytb
  ADD COLUMN mycol INT;

Change A Column

ALTER TABLE mytb
  CHANGE COLUMN old_column new_column VARCHAR(100);

When use CHANGE COLUMN, both new column and column type should be declared together.

Modify A Column

If we just want to change the type or the constraint of a column, then it is more convenient to use MODIFY COLUMN.

ALTER TABLE mytb
  MODIFY COLUMN mycol SMALLINT NOT NULL;

Add A Constraint

ALTER TABLE mytb
  ADD PRIMARY KEY (id);

ALTER TABLE mytb
  ADD CONSTRAINT unique_col UNIQUE (mycol);

Delete / Drop A Column

ALTER TABLE mytb
  DROP COLUMN mycol;

Delete / Drop A Constraint

ALTER TABLE mytb
  DROP PRIMARY KEY;

ALTER TABLE mytb
  DROP FOREIGN KEY fk_name;

Rename A Table

ALTER TABLE mytb
  RENAME TO new_tb_name;

Insert Data into A Table

INSERT INTO mytb (column_1, column_2, ...)
  VALUES (value_1, value_2, ...);

Query Data in A Table (Basic)

SELECT column_1, column_2, ... FROM mytb;

SELECT * FROM mytb;

Delete Data in A Table

DELETE FROM mytb WHERE conditions;

Delete All Data in A Table

DELETE FROM mytb;

TRUNCATE TABLE mytb;