關(guān)系數(shù)據(jù)庫(kù)常用SQL語(yǔ)句語(yǔ)法大全.doc

_創(chuàng)建表語(yǔ)法CREATE TABLE ( 列級(jí)完整性約束條件 , 列級(jí)完整性約束條件);列級(jí)完整性約束條件有NULL可為空、NOT NULL不為空、UNIQUE唯一，可以組合使用，但是不能重復(fù)和對(duì)立關(guān)系同時(shí)存在。示例- 創(chuàng)建學(xué)生表CREATE TABLE Student( Id INT NOT NULL UNIQUE PRIMARY KEY, Name VARCHAR(20) NOT NULL, Age INT NULL, Gender VARCHAR(4) NULL);刪除表語(yǔ)法DROP TABLE ;示例- 刪除學(xué)生表DROP TABLE Student;清空表語(yǔ)法TRUNCATE TABLE ;示例- 刪除學(xué)生表TRUNCATE TABLE Student;修改表語(yǔ)法- 添加列ALERT TABLE ADD 列級(jí)完整性約束條件- 刪除列ALERT TABLE DROP COLUMN - 修改列ALERT TABLE MODIFY COLUMN 列級(jí)完整性約束條件示例- 添加學(xué)生表Phone列ALERT TABLE Student ADD Phone VARCHAR(15) NULL;- 刪除學(xué)生表Phone列ALERT TABLE Student DROP COLUMN Phone;- 修改學(xué)生表Phone列ALERT TABLE Student MODIFY Phone VARCHAR(13) NULL;SQL查詢(xún)語(yǔ)句語(yǔ)法SELECT ALL|DISTINCT , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESCSQL查詢(xún)語(yǔ)句的順序：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。SELECT、FROM是必須的，HAVING子句只能與GROUP BY搭配使用。示例SELECT * FROM Student WHERE Id10 GROUP BY Age HAVING AVG(Age) 20 ORDER BY Id DESCSQL插入語(yǔ)句語(yǔ)法- 插入不存在的數(shù)據(jù)INSERT INTO (字段名,字段名) VALUES (常量,常量);- 將查詢(xún)的數(shù)據(jù)插入到數(shù)據(jù)表中INSERT INTO (字段名,字段名) SELECT 查詢(xún)語(yǔ)句;示例- 插入不存在的數(shù)據(jù)INSERT INTO Student (Name,Age,Gender) VALUES (Andy,30,女);- 將查詢(xún)的數(shù)據(jù)插入到數(shù)據(jù)表中INSERT INTO Student (Name,Age,Gender) SELECT Name,Age,Gender FROM Student_T WHERE Id 10;SQL更新語(yǔ)句語(yǔ)法UPDATE SET 列名=值表達(dá)式,列名=值表達(dá)式 WHERE 條件表達(dá)式示例- 將Id在(10,100)的Age加1UPDATE Student SET Age= Age+1 WHERE Id10 AND Id100SQL刪除語(yǔ)句語(yǔ)法DELETE FROM WHERE 條件表達(dá)式示例- 刪除Id小于10的數(shù)據(jù)記錄DELETE FROM Student WHERE Id10;創(chuàng)建索引語(yǔ)法CREATE UNIQUE CLUSTER INDEX ON (,);UNIQUE：表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄CLUSTER：表明建立的索引是聚集索引次序：可選ASC(升序)或DESC(降序)，默認(rèn)ASC示例- 建立學(xué)生表索引：?jiǎn)我蛔侄蜪d索引倒序CREATE UNIQUE INDEX INDEX_SId ON Student (Id DESC);- 建立學(xué)生表索引：多個(gè)字段Id、Name索引倒序CREATE UNIQUE INDEX INDEX_SId_SName ON Student (Id DESC,Name DESC);刪除索引語(yǔ)法DROP INDEX ;示例- 刪除學(xué)生表索引 INDEX_SIdDROP INDEX INDEX_SId;創(chuàng)建視圖語(yǔ)法CREATE VIEW AS SELECT 查詢(xún)子句 WITH CHECK OPTION查詢(xún)子句：子查詢(xún)可以是任何SELECT語(yǔ)句，但是常不允許含有ORDER BY子句和DISTINCT短語(yǔ)；WITH CHECK OPTION：表示對(duì)UPDATE、INSERT、DELETE操作時(shí)要保證更新。示例CREATE VIEW VIEW_Stu_ManAS SELECT * FROM Student WHERE Gender = 男WITH CHECK OPTION刪除視圖語(yǔ)法DROP VIEW ;示例DROP VIEW VIEW_Stu_Man;SQL的訪問(wèn)控制訪問(wèn)控制是控制用戶(hù)的數(shù)據(jù)存儲(chǔ)權(quán)限，由DBA來(lái)決定。SQL標(biāo)準(zhǔn)語(yǔ)句包括SELECT、INSERT、UPDATE和DELETE語(yǔ)法- 1.授權(quán)GRANT , ON TO , WITH GRANT OPTION- 2.收回授權(quán)REVOKE , ON FROM ,WITH GRANT OPTION：若指定此子句，表示該用戶(hù)可以將權(quán)限賦給其他用戶(hù)示例- 授權(quán)GRANT SELECT,INSERT,UPDATE ON TABLE TO USER_Admi