SQL(Structured Query Language_結構式查詢語言，讀音為[`sikwl])是關聯性資料庫系統(Relational DataBase Management System, RDBMS)與外界使用者溝通的電腦語言標準。
因功能的不同，又可分為DDL、DCL與DML等三個部份：
DDL _Data Definition Language 屬於資料庫、資料表之增減與資料型態定義的領域；
DCL _Data Control Language 屬於資料庫的使用權限設定與安全管理的領域；
DML _Data Manipulation Language 為資料查詢與資料增、減、修改等操作的範疇。
1970年，IBM 公司的研究人員E. F. Codd 數學博士在「A Relational Model of Data for Large Shared Data Banks」這篇論文中所提出「關聯式資料模型(relational model of data)」的概念。IBM根據這個理論開始研發關聯性資料庫後，發展出名為SEQUEL _Structured English Query Language的資料庫操作語言。經過不斷地歸納與修改，美國國家標準局_ANSI於1989與1992年分別訂定了SQL-89與SQL-92這兩個關聯性資料庫操作語言的標準供資料庫業者遵循。雖然之後ANSI與ISO合作，先後又推出了最新的ANSI/ISO "SQL:1999" 以及 "SQL:2003"，但因為資料庫研發業界尚未全面實做，所以，目前市面上的資料庫在實際運用上還是以SQL-92為主要的標準。
時至今日，關聯性資料庫已經席捲所有資料庫的市場，所有的關聯性資料庫系統(RDBMS)都支援ANSI SQL的標準，PHP中支援最完整的MySQL資料庫當然也不例外。如果要發揮PHP伺服器動態網頁全面功能，基本上，就必須要認識、學習如何撰寫SQL指令來操作MySQL資料庫。它，有另外一個效應，從一個樂觀的角度來說，只要熟悉了MySQL關聯性資料庫的SQL語法結構，那麼，其他各式各樣的關聯性資料庫系統就難不倒你啦！
SQL之所以被稱為(電腦)語言，當然有它的原因。首先，它真的很像一句英文句子；再者，SQL也有必須遵循的語法。SQL語言最終的目的就是在關聯性資料庫中發揮管理、查詢、增減、修改資料的功能。如果要讓我們所撰寫的PHP網頁具有智慧，具備較強的功能，那我們非得依靠資料庫系統不可。因此，我們今天將先針對SQL做一次概略性的簡介，建立我們操作使用MySQL資料庫系統的基礎。
DDL _Data Definition Language
CREATE DATABASE | CREATE DATABASE database_name |
CREATE TABLE | CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ... ... ) |
CREATE UNIQUE INDEX | CREATE UNIQUE INDEX index_name ON table_name (column_name) |
CREATE INDEX | CREATE INDEX index_name ON table_name (column_name) |
CREATE VIEW | CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
ALTER TABLE (ADD cols) | ALTER TABLE table_name ADD column_name datatype |
ALTER TABLE (DROP cols) | ALTER TABLE table_name DROP COLUMN column_name |
DROP DATABASE | DROP DATABASE database_name |
DROP TABLE | DROP TABLE table_name |
DROP INDEX | DROP INDEX table_name.index_name |
DCL _Data Control Language
GRANT … ON … TO …
| Grant Select,Update(au_lname,au_fname) On Authors To Classis or Grant Select On Authors To Classic With Grant Option |
REVOKE … ON … FROM … | Revoke Select,Update(au_lname,au_fname) On Authors From Classic or Revoke Grant Option For Select On Authors From Classic CASCADE |
DENY | Deny select on sales to Classic |
DML _Data Manipulation Language
SELECT … FROM | SELECT column_name(s) FROM table_name |
SELECT *(all columns) FROM | SELECT * FROM table_name |
SELECT DISTINCT ... FROM | SELECT DISTINCT column_name(s) FROM table_name |
SELECT TOP rows_number FROM | SELECT DISTINCT TOP 5 column_name(s) FROM table_name |
SELECT ... INTO newtable (used to create data copies of tables) | SELECT column_name(s) INTO new_table_name FROM original_table_name |
WHERE (conditions) | SELECT column_name(s) FROM table_name WHERE condition |
BETWEEN val1 AND val2 | SELECT column_name(s) FROM table_name WHERE column_name BETWEEN val1 AND val2 |
IN (val1,val2,val3...) | SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) |
LIKE (string_pattern) [ % for arbitrary characters] | SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
IS NOT NULL | SELECT column_name FROM table_name WHERE column_name IS NOT NULL
|
AND / OR [ logical operating ] | SELECT column_name(s) FROM table_name WHERE condition1 AND | OR condition2 …… |
ORDER BY | SELECT column_name(s) FROM table_name ORDER BY column_name DESC |
AS (alias for table) | SELECT table_alias.column_name FROM table_name AS table_alias |
AS (alias for column) | SELECT column_name AS column_alias FROM table_name WHERE alias_condition
|
GROUP BY [ Cooperate with agregate ] | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 |
HAVING (conditions) | SELECT column_name1,SUM(column_name2) FROM table_name WHERE condition GROUP BY column_name1 HAVING SUM(column_name2) condition |
INNER tbA LEFT JOIN tbB RIGHT ON (conditions) | SELECT table_name.column_name(s)FROM table_A INNER JOIN table_BON table_A.column_X = table_B.column_Y or SELECT table_name.column_name(s)FROM table_A LEFT JOIN table_BON table_A.column_X = table_B.column_YWHERE select_condition |
INSERT INTO VALUES ( ) | INSERT INTO table_name (column_name1, column_name2, ...) VALUES (value1, value2, ...) |
UPDATE … SET | UPDATE table_name SET column_name1=new_value1 [, column_name2=new_value2] WHERE column_name=some_value |
DELETE FROM [WHERE (conditions)] | DELETE FROM table_name (Note: Deletes all the data from table!!) or
DELETE FROM table_name WHERE condition |
TRUNCATE TABLE (deletes all the data inside the table) | TRUNCATE TABLE table_name |
留言列表
