SQL 指令大全


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 Customer;

DROP DATABASE dbname;

TRUNCATE TABLE table1
//DELETE FROM table1  [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 PLAN - explain access path to data 
LOCK TABLE - control concurrency

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 

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

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理