数据库及应用程序开发关系数据库标准语言SQL.ppt

1,第4章 数据库及应用程序开发 关系数据库标准语言SQL,西安交通大学 计算机教学实验中心,软件开发技术基础,2,4.1.3关系数据库标准语言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 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） 删除重复的行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) 结果排序,ORDER BY 字段表 通常返回的结果是随机排列的。 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 用不出现的列。,29,(6) 统计函数,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) 结果分组,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的分组结果再进一步筛选。 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）索引类型 按照存储位置：聚集索引和非聚集索引。 按照关键字值是否唯一：唯一索引和非唯一所以。,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 ， | PUBLIC WITH GRANT OPTION； /PUBLIC