【教学课件】第4章数据库及应用程序开发-关系数据库标准语言SQL.ppt

1,第4章 数据库及应用程序开发 关系数据库标准语言SQL,西安交通大学计算机教学实验中心,软件开发技术基础,2,关系数据库标准语言SQL,结构化查询语言(SQL，Structured Query Language)是关系数据库的标准语言。它的主要功能包括：数据定义:Data Defination Language(DDL)数据查询:Data Query 数据操纵:Data Manipulation Language(DML)数据控制:Data Control Language(DCL),3,（2）SQL特点,综合统一：集数据定义语言（DDL）、数据操纵语言（DML）和数据控制语言DCL于一体，综合管理功能于一身。高度非过程化：用户只需提出“做什么”，而不需指明“怎么做”。面向集合的操作方式：用户查询、修改、的数据不是一条记录，而是记录的集合。,4,SQL特点（2）,以同一语法结构提供两种使用方式：用户可以从键盘使用SQL命令，也可以将SQL语句嵌入其他高级语言中。(PHP VC示例)语言简洁、易于使用：语句很少，数据定义：CTEATE DROP ALTER数据操纵：INSERT UPDATE DELETE数据查询：SELECT数据控制：GRANT REVOTE可以对基本表和视图进行查询,5,关系数据库的三级模式结构,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 删除数据表,9,SQL SERVER中常用的数据类型,学号、身份证号等，使用字符型数据,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 employeeSET name=Li MingWHERE eid=4025,13,UPDATE employeeSET name=Zhang Li,age=36,country=USAWHERE eid=3056可以修改多个字段的值。如果不带WHERE修改所有的记录。,14,4、删除记录,DELETE 表名WHERE 条件DELETE employeeWHERE eid=3018,15,5、查询记录,SELECT 表达式 FROM 表名表WHERE 条件GROUP BY 字段名表HAVING 条件ORDER BY 字段名表,16,（1）举例,SELECT pub_id,pub_name,countryFROM publishersSELECT*FROM publishers,17,（2）删除重复的行DISTICT,SELECT city,stateFROM authersSELECT DISTICT city,stateFROM authers,18,（3）计算表达式,SELECT title_id,type,price,price_price*0.3FROM titleSELECT au_lname+.+au_fname,city+.+stateFROM authors,19,（4）筛选条件,（a）条件：比较=!=price45.3country=China 逻辑运算NOT AND OR Country=China AND age35,20,（b）举例,SELECT title_id,type,price,price-price*0.3 FROM titlesWHERE price-price*0.318SELECT pub_id,pub_nameFROM publishersWHERE country=USA AND state=CA,21,(c)LIKE,列名 NOT LIKE 模式%:任意字符串,_:任意一个字符SELECT au_lname+.+au_fname,city+.+stateFROM authorsWHERE au_fname LIKE D%,22,SELECT au_lname+.+au_fname,city+.+stateFROM authorsWHERE au_id LIKE 72_-%,23,24,25,其他的条件查询,26,(5)结果排序,ORDER BY 字段表通常返回的结果是随机排列的。SELECT stor_name,cityFROM storesORDER BY store_name,27,SELECT stor_name,cityFROM storesORDER BY store_name DESCSELECT stor_name,cityFROM storesORDER BY store_name ASC,SELECT stor_name,cityFROM storesORDER BY stor_name，city 用多个列排列。SELECT stor_name,cityFROM storesORDER BY state用不出现的列。,29,(6)统计函数,30,SELECT AVG(price)FROM titlesSELECT MAX(price)FROM titlesWHERE type=business,31,SELECT AVG(qty),SUM(qty),COUNT(DISTICT stor_id)FROM salesSELECT COUNT（*）FROM publishersWHERE state=CA,32,(7)结果分组,GROUP BY 分组列名表SELECT type,AVG(price),SUM(price),COUNT(*)FROM titlesWHERE type in(business,mod_cook,trad_cook)GROUP BY type,SELECT type,pub_id,AVG(price),SUM(price),COUNT(*)FROM titlesWHERE type in(business,mod_cook,trad_cook)GROUP BY type，pub_id,GROUP BY 分组列名表HAVING 条件对GROUP的分组结果再进一步筛选。SELECT type,AVG(price),SUM(price),COUNT(*)FROM titlesWHERE price$10GROUP BY type,使用HAVING,SELECT type,AVG(price),SUM(price),COUNT(*)FROM titlesWHERE price$10GROUP BY typeHAVING AVG(price)$20,使用HAVING,36,(8)多表查询,（a）笛卡尔乘积SELECT title_id,au_fnameFROM titles,authorsSELECT 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 taSELECT t.title_id,t.title,ta.au_id FROM titles t,titleauthor taWHERE t.title_id=ta.title_id,38,（c）筛选,SELECT t.title_id,a.au_id FROM titles t,titleauthor ta,authors aWHERE t.title_id=ta.title_id and ta.au_id=a.au_id,39,6 视图,(1）视图的定义SQL建立视图的语句格式为：CREATE VIEW(，)AS,40,CREATE VIEW au_viewASSELECT au_id,au_fname,au_lnameFROM authorsCREATE VIEW author_titleASSELECT t.title_id,a.au_id FROM titles t,titleauthor ta,authors aWHERE t.title_id=ta.title_id and ta.au_id=a.au_id,41,(2)视图查询,SELECT*FROM au_viewSELECT t.title_id,a.au_id FROM titles t,titleauthor ta,authors aWHERE 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）索引类型按照存储位置：聚集索引和非聚集索引。按照关键字值是否唯一：唯一索引和非唯一所以。,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、数据控制,数据控制也称为数据保护通过对数据库用户的使用权限加以限制而保证数据安全的重要措施。SQL语言提供一定的数据控制功能，能在一定程度上保证数据库中数据的完全性和完整性，并提供了一定的并发控制及恢复能力。SQL的数据控制语句包括授权(Grant)、收权(Revoke)和拒绝访问(Deny)三种，其权限的设置对象可以是数据库用户或用户组。,48,（1）授权,GRANT语句向用户授予操作权限 GRANT,ON TO，|PUBLICWITH GRANT OPTION；/PUBLIC，即全体用户。/WITH GRANT OPTION获得权限的用户可以再授予别的用户。,49,数据控制对象与权限,50,GRANT SELECT ON TABLE teacherTO U1；GRANT UPDATE(Sno)，SELECT ON TABLE studentTO U2，U3；,51,(2)收回权限,REVOKE，ON FROM，；REVOKE UPDATE(Sno)ON TABLE studentFROM U2；REVOKE SELECT ON TABLE teacherFROM U1；,