SQL 指令大全

SQL指令包含四大類型 : DDL (資料定義語言)、DML (資料處理語言)、DCL (資料控制語言)、TCL (交易控制語言)。

DDL (Data Definition Language) 資料定義語言
CREATE - to create objects in the database 
ALTER - alters the structure of the database 
DROP - delete objects from the database 
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed 
RENAME - rename an object

範例:
CREATE DATABASE [database name] 
CHARACTER SET utf8 
COLLATE utf8_general_ci;

CREATE TABLE [table name] (

fid MEDIUMINT  NOT NULL AUTO_INCREMENT,

fname varchar(20), 
Primary key (fid));

ALTER TABLE  [table name]
CHANGE fname myname varchar(10);
//CHANGE "原本欄位名" "新欄位名" "新欄位名資料種類"

ALTER TABLE  [table name]

DROP fname;

//DROP "欄位 1" 

ALTER TABLE  [table name]

ADD newfield char(10);

//ADD "欄位 1" "欄位 1 資料種類" 

ALTER TABLE  [table name]

MODIFY fname char(10);

//MODIFY "欄位 1" "新資料種類"

DROP TABLE [table name];

DROP DATABASE [database name];

TRUNCATE TABLE [table name]

//DELETE FROM table name  [WHERE...]

RENAME TABLE db_a.old_table TO db_b.new_table;

//MySQL Table 從 db_a 要搬到 db_b

RENAME TABLE old_table TO new_table;

//MySQL Table 改名字(重新命名)

DML (Data Manipulation Language) 資料處理語言

SELECT - retrieve data from the a database  (也有說select是DRL: Data Retrieval Language)
INSERT - insert data into a table 
UPDATE - updates existing data within a table 
DELETE - deletes all records from a table, the space for the records remain 
MERGE - UPSERT operation (insert or update) 
CALL - call a PL/SQL or Java subprogram 
EXPLAIN  - explain access path to data 
LOCK TABLE - control concurrency

範例: 

SELECT [field name] FROM [table name];

更多請參考 : SELECT SQL語法總整理

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

DELETE FROM table_name WHERE condition;

MERGE 請參考 https://www.geeksforgeeks.org/merge-statement-sql-explained/

CALL 請參考 https://www.tutorialspoint.com/mysql/mysql_call.htm

EXPLAIN 請參考 https://segmentfault.com/a/1190000023565685

DCL (Data Control Language) 資料控制語言
GRANT - gives user's access privileges to database 
REVOKE - withdraw access privileges given with the GRANT command

範例: 

GRANT 請參考 https://ithelp.ithome.com.tw/articles/10030141

REVOKE 請參考 https://www.fooish.com/sql/revoke-privileges.html

TCL (Transaction Control Language) 交易控制語言
COMMIT - save work done 
SAVEPOINT - identify a point in a transaction to which you can later roll back 
ROLLBACK - restore database to original since the last COMMIT 
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use 

範例: 

COMMIT / ROLLBACK 請參考 https://www.mysql.tw/2018/05/mysqlrollbackcommit.html

SAVEPOINT  請參考 https://www.tutorialspoint.com/mysql/mysql_savepoint.htm

SET TRANSACTION 請參考 https://www.freecodecamp.org/news/how-to-use-mysql-transactions/

參考資料: http://www.1keydata.com/sql/sql-commands.html