前言

常常要用sql時忘記語法，每次都要再去google查，所以自己整理了一下我目前常用的指令，並簡單分類一下。

Basic SQL commands

mysql -u root -p <password>

create database <name>;

show databases; / use <database>;

show tables;

SHOW CREATE TABLE <table>; → shows all column and constraints

Table

• create table:

  CREATE TABLE <table> (<content>);

• delete table:

  DROP TABLE <table>;

Modify table

• add/modify column/constraint:

  ALTER TABLE <table>

  ADD/MODIFY <column1/constraint1> <type> <other setup...>,

  ADD/MODIFY <column2/constraint2> <type> <other setup...>;

• foreign key constraint

  CONSTRAINT <name> FOREIGN KEY <column> REFERENCES <other table>(<column>)

  [ON DELETE/UPDATE <ACTION>];

  constraint actions:

  CASCADE - 會將有所關聯的紀錄行也會進行刪除或修改。

  SET NULL - 會將有所關聯的紀錄行設定成 NULL。

  NO ACTION - 有存在的關聯紀錄行時，會禁止父資料表的刪除或修改動作。

  RESTRICT - 與 NO ACTION 相同。

• delete column/constraint

  ALTER TABLE <table> DROP <column>/<constraint>;

constraint can't be modified, only can be deleted and recreate new one

Row(value)

• insert value:

  INSERT INTO <table>(<column1>,<column2>...) VALUE(<value1>,<value2>...);

• update value:

  UPDATE <table> SET <column1>=<value1>,<column2>=<value2> [WHERE <condition>];

• delete row:

  DELETE FROM <table> [WHERE <condition>];

• get data:

  SELECT * or <column1>,<column2>... FROM <table>;

  ? ORDER BY <column> ASC/DESC

  ASC: asecond → 1,2,3,4,5 (default is asc)

  DESC: desecond → 5,4,3,2,1

  SELECT * FROM todolist ORDER BY created_time DESC