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] 
COLLATE utf8_general_ci;

CREATE TABLE [table name] (
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;

DCL (Data Control Language
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 


0 留言