1、4.1.3关系数据库标准语言SQL,SQL（ Structured Query Language) 1 建立表 CREATE TABLE table_name (column_name data-type consraint, ),建立表,CREATE TABLE employee (eid CHAR(6), name VARCHAR(8) NOT NULL, age NUMERIC(3,0), country VARCHAR(10) NULL),建立表,CREATE TABLE employee (eid CHAR(6) NOT NULL PRIMARY KEY, name VARCHAR(
2、8) NOT NULL), age NUMERIC(3,0), country VARCHAR(10) NULL ),2 插入记录,INSERT INTO 表名 列名表） VALUES （值表）INSERT INTO employee (eid, name, age, country) VALUES (3018, zhang, 28, China),INSERT INTO employee VALUES (3056, wang, 35, USA)INSERT INTO employee (eid, name, country) VALUES (4025, li, Russia),二、 插入记录
3、,3、修改记录,UPDATE 表名 SET 字段名=表达式 WHERE 条件 UPDATE employee SET name=Li Ming WHERE eid=4025,UPDATE employee SET name=Zhang Li, age=36, country=USA WHERE eid=3056可以修改多个字段的值。如果不带WHERE修改所有的记录。,4、删除记录,DELETE 表名 WHERE 条件DELETE employee WHERE eid=3018,5、 查询记录,SELECT 表达式 FROM 表名表 WHERE 条件 GROUP BY 字段名表 HAVING 条
4、件 ORDER BY 字段名表,（1） 举例,SELECT pub_id, pub_name, country FROM publishersSELECT * FROM publishers,（2） 删除重复的行DISTICT,SELECT city, state FROM authersSELECT DISTICT city, state FROM authers,（3） 计算表达式,SELECT title_id, type, price, price_price*0.3 FROM titleSELECT au_lname+.+au_fname, city+.+state FROM aut
5、hors,（4）筛选条件,（a）条件： = = != price45.3 country=China NOT AND OR Country=China AND age35,（b）举例,SELECT title_id, type, price, price - price*0.3 FROM titles WHERE price - price*0.318SELECT pub_id, pub_name FROM publishers WHERE country=USA AND state=CA,(c) LIKE,列名 NOT LIKE 模式 %: 任意字符串, _:任意一个字符SELECT au_
6、lname+.+au_fname, city+.+state FROM authors WHERE au_fname LIKE D%,SELECT au_lname+.+au_fname, city+.+state FROM authors WHERE au_id LIKE 72_-%,(5) 结果排序,ORDER BY 字段表 通常返回的结果是随机排列的。 SELECT stor_name, city FROM stores ORDER BY store_name,SELECT stor_name, city FROM stores ORDER BY store_name DESCSELEC
7、T 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 用不出现的列。,SUM（expression） AVG（ expression ） MIN（ expression ） MAX（ expression ） COUNT（ expression ） COUNT（*）,(6) 统计函数,SELECT AVG(pr
8、ice) FROM titlesSELECT MAX(price) FROM titles WHERE type=business,SELECT AVG(qty), SUM(qty), COUNT(DISTICT stor_id) FROM salesSELECT COUNT（*） FROM publishers WHERE state=CA,(7) 结果分组,GROUP BY 分组列名表SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE type in (business, mod_cook, trad_cook)
9、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 GO,GROUP BY 分组列名表 HAVING 条件对GROUP的分组结果再进一步筛选。 SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE price$10 GROUP BY type,使用HAVING,SELECT typ
10、e, AVG(price), SUM(price), COUNT(*) FROM titles WHERE price$10 GROUP BY type HAVING AVG(price)$20,使用HAVING,(8) 多表查询,（a）笛卡尔乘积 SELECT title_id, au_fname FROM titles, authorsSELECT titles.title_id, titles.title, titleauthor.au_id FROM titles, titleauthor,（b ）别名,SELECT t.title_id, t.title, ta.au_id FROM
11、 titles t, titleauthor taSELECT t.title_id, t.title, ta.au_id FROM titles t, titleauthor ta WHERE t.title_id=ta.title_id,（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,6 视图,(1）视图的定义 SQL建立视图的语句格式为： CREATE VIEW (，) AS ,CREATE
12、 VIEW au_view AS SELECT au_id, au_fname, au_lname FROM authorsCREATE 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,(2)视图查询,SELECT * FROM au_viewSELECT t.title_id, a.au_id FROM titles t, titleauthor ta, autho
13、rs a WHERE t.title_id=ta.title_id and ta.au_id=a.au_id,（3）删除视图,DROP VIEW 视图名DROP VIEW a_authors,7、索引,（1）索引类型 按照存储位置：聚集索引和非聚集索引。 按照关键字值是否唯一：唯一索引和非唯一所以。,（2）建立索引,CREATE UNIQUE CLUSTERED | NOCLUSTERED INDEX 索引名 ON 表名 （列名表）CREATE UNIQUE INDEX id_index ON authors (au_id),CREATE INDEX id_index ON authors ( au_fname DESC,au_id)CREATE CLUSTERED INDEX id_index ON authors ( au_fname DESC,au_id),（3）删除索引,DROP INDEX 表名.索引名DROP INDEX authors.id_index,8、数据库安全性,（1）添加数据库用户 SP_GRANTDBACCESS 有登录权的帐户名SP_GRANTDBACCESS lixin（2）删除数据库用户 SP_REVOKEDBACCESS 帐户名SP_REVOKEDBACCESS lili,
