數(shù)據(jù)庫及應(yīng)用程序開發(fā)關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL.ppt

1,第4章 數(shù)據(jù)庫及應(yīng)用程序開發(fā) 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,西安交通大學(xué) 計算機教學(xué)實驗中心,軟件開發(fā)技術(shù)基礎(chǔ),2,4.1.3關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,結(jié)構(gòu)化查詢語言(SQL，Structured Query Language)是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。它的主要功能包括： 數(shù)據(jù)定義:Data Defination Language(DDL) 數(shù)據(jù)查詢: Data Query 數(shù)據(jù)操縱:Data Manipulation Language(DML) 數(shù)據(jù)控制 : Data Control Language(DCL),3,（2）SQL特點,綜合統(tǒng)一：集數(shù)據(jù)定義語言（DDL）、數(shù)據(jù)操縱語言（DML）和數(shù)據(jù)控制語言DCL于一體，綜合管理功能于一身。 高度非過程化：用戶只需提出“做什么”，而不需指明“怎么做”。 面向集合的操作方式：用戶查詢、修改、的數(shù)據(jù)不是一條記錄，而是記錄的集合。,4,SQL特點（2）,以同一語法結(jié)構(gòu)提供兩種使用方式：用戶可以從鍵盤使用SQL命令，也可以將SQL語句嵌入其他高級語言中。(PHP VC示例) 語言簡潔、易于使用：語句很少， 數(shù)據(jù)定義：CTEATE DROP ALTER 數(shù)據(jù)操縱：INSERT UPDATE DELETE 數(shù)據(jù)查詢：SELECT 數(shù)據(jù)控制：GRANT REVOTE 可以對基本表和視圖進行查詢,5,關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu),6,SQL（ Structured Query Language) 1 建立表 CREATE TABLE table_name (column_name data-type consraint, ),7,建立表,CREATE TABLE employee (eid CHAR(6), name VARCHAR(8) NOT NULL, age NUMERIC(3,0), country VARCHAR(10) NULL) / NUMERIC(3,0) - NUMERIC,8,建立表,CREATE TABLE employee (eid CHAR(6) NOT NULL PRIMARY KEY, name VARCHAR(8) NOT NULL, age NUMERIC(3,0), country VARCHAR(10) NULL ) drop table employee3 刪除數(shù)據(jù)表,9,SQL SERVER中常用的數(shù)據(jù)類型,學(xué)號、身份證號等，使用字符型數(shù)據(jù),10,2 插入記錄,INSERT INTO 表名 （列名表） VALUES （值表） INSERT INTO employee (eid, name, age, country) VALUES (3018, zhang, 28, China),11,INSERT INTO employee VALUES (3056, wang, 35, USA) INSERT INTO employee (eid, name, country) VALUES (4025, li, Russia),2、 插入記錄,12,3、修改記錄,UPDATE 表名 SET 字段名=表達式 WHERE 條件 UPDATE employee SET name=Li Ming WHERE eid=4025,13,UPDATE employee SET name=Zhang Li, age=36, country=USA WHERE eid=3056 可以修改多個字段的值。如果不帶WHERE修改所有的記錄。,14,4、刪除記錄,DELETE 表名 WHERE 條件 DELETE employee WHERE eid=3018,15,5、 查詢記錄,SELECT 表達式 FROM 表名表 WHERE 條件 GROUP BY 字段名表 HAVING 條件 ORDER BY 字段名表,16,（1） 舉例,SELECT pub_id, pub_name, country FROM publishers SELECT * FROM publishers,17,（2） 刪除重復(fù)的行DISTICT,SELECT city, state FROM authers SELECT DISTICT city, state FROM authers,18,（3） 計算表達式,SELECT title_id, type, price, price_price*0.3 FROM title SELECT au_lname+.+au_fname, city+.+state FROM authors,19,（4）篩選條件,（a）條件： 比較= = != price45.3 country=China 邏輯運算 NOT AND OR Country=China AND age35,20,（b）舉例,SELECT title_id, type, price, price - price*0.3 FROM titles WHERE price - price*0.318 SELECT pub_id, pub_name FROM publishers WHERE country=USA AND state=CA,21,(c) LIKE,列名 NOT LIKE 模式 %: 任意字符串, _:任意一個字符 SELECT au_lname+.+au_fname, city+.+state FROM authors WHERE au_fname LIKE D%,22,SELECT au_lname+.+au_fname, city+.+state FROM authors WHERE au_id LIKE 72_-%,23,24,25,其他的條件查詢,26,(5) 結(jié)果排序,ORDER BY 字段表 通常返回的結(jié)果是隨機排列的。 SELECT stor_name, city FROM stores ORDER BY store_name,27,SELECT stor_name, city FROM stores ORDER BY store_name DESC SELECT stor_name, city FROM stores ORDER BY store_name ASC,SELECT stor_name, city FROM stores ORDER BY stor_name， city 用多個列排列。 SELECT stor_name, city FROM stores ORDER BY state 用不出現(xiàn)的列。,29,(6) 統(tǒng)計函數(shù),30,SELECT AVG(price) FROM titles SELECT MAX(price) FROM titles WHERE type=business,31,SELECT AVG(qty), SUM(qty), COUNT(DISTICT stor_id) FROM sales SELECT COUNT（*） FROM publishers WHERE state=CA,32,(7) 結(jié)果分組,GROUP BY 分組列名表 SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE type in (business, mod_cook, trad_cook) GROUP BY type,SELECT type, pub_id, AVG(price), SUM(price), COUNT(*) FROM titles WHERE type in (business, mod_cook, trad_cook) GROUP BY type， pub_id,GROUP BY 分組列名表 HAVING 條件 對GROUP的分組結(jié)果再進一步篩選。 SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE price$10 GROUP BY type,使用HAVING,SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE price$10 GROUP BY type HAVING AVG(price)$20,使用HAVING,36,(8) 多表查詢,（a）笛卡爾乘積 SELECT title_id, au_fname FROM titles, authors SELECT titles.title_id, titles.title, titleauthor.au_id FROM titles, titleauthor,37,（b ）別名,SELECT t.title_id, t.title, ta.au_id FROM titles t, titleauthor ta SELECT t.title_id, t.title, ta.au_id FROM titles t, titleauthor ta WHERE t.title_id=ta.title_id,38,（c）篩選,SELECT t.title_id, a.au_id FROM titles t, titleauthor ta, authors a WHERE t.title_id=ta.title_id and ta.au_id=a.au_id,39,6 視圖,(1）視圖的定義 SQL建立視圖的語句格式為： CREATE VIEW (，) AS ,40,CREATE VIEW au_view AS SELECT au_id, au_fname, au_lname FROM authors CREATE VIEW author_title AS SELECT t.title_id, a.au_id FROM titles t, titleauthor ta, authors a WHERE t.title_id=ta.title_id and ta.au_id=a.au_id,41,(2)視圖查詢,SELECT * FROM au_view SELECT t.title_id, a.au_id FROM titles t, titleauthor ta, authors a WHERE t.title_id=ta.title_id and ta.au_id=a.au_id,42,（3）刪除視圖,DROP VIEW 視圖名 DROP VIEW a_authors,43,7、索引,索引可以極大地提高查詢的速度 索引可以建在一列或幾列上 有ASC(升序)和DESC(降序) （1）索引類型 按照存儲位置：聚集索引和非聚集索引。 按照關(guān)鍵字值是否唯一：唯一索引和非唯一所以。,44,（2）建立索引,CREATE UNIQUE CLUSTERED | NOCLUSTERED INDEX 索引名 ON 表名 （列名表） CREATE UNIQUE INDEX id_index ON authors (au_id),45,CREATE INDEX id_index ON authors ( au_fname DESC,au_id) CREATE CLUSTERED INDEX id_index ON authors ( au_fname DESC,au_id),46,（3）刪除索引,DROP INDEX 表名.索引名 DROP INDEX authors.id_index,47,8、數(shù)據(jù)控制,數(shù)據(jù)控制也稱為數(shù)據(jù)保護 通過對數(shù)據(jù)庫用戶的使用權(quán)限加以限制而保證數(shù)據(jù)安全的重要措施。 SQL語言提供一定的數(shù)據(jù)控制功能，能在一定程度上保證數(shù)據(jù)庫中數(shù)據(jù)的完全性和完整性，并提供了一定的并發(fā)控制及恢復(fù)能力。 SQL的數(shù)據(jù)控制語句包括授權(quán)(Grant)、收權(quán)(Revoke)和拒絕訪問(Deny)三種，其權(quán)限的設(shè)置對象可以是數(shù)據(jù)庫用戶或用戶組。,48,（1）授權(quán),GRANT語句向用戶授予操作權(quán)限 GRANT , ON TO ， | PUBLIC WITH GRANT OPTION； /PUBLIC