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] (
Primary key (fid));
ALTER TABLE [table name]
CHANGE fname myname varchar(10);
//CHANGE "原本欄位名" "新欄位名" "新欄位名資料種類"
ALTER TABLE [table name]
ALTER TABLE [table name]
ALTER TABLE [table name]
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
範例:
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"
ADD newfield char(10);
//ADD "欄位 1" "欄位 1 資料種類"
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;
EXPLAIN 請參考 https://segmentfault.com/a/1190000023565685
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
範例:
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 - 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
範例:
SAVEPOINT 請參考 https://www.tutorialspoint.com/mysql/mysql_savepoint.htm
SET TRANSACTION 請參考 https://www.freecodecamp.org/news/how-to-use-mysql-transactions/
0 留言