EOS入门必备之SQL Server篇.doc

EOS入门必备之SQL Server篇PRIMETON TECHNOLOGIES, LTD.上海普元信息技术有限责任公司EOS入门必备之SQL Server篇 No part of this document may be reproduced, stored in any electronic retrieval system, or transmitted in any form or by any means, mechanical, photocopying, recording, otherwise, without the written permission of the copyright owner.COPYRIGHT 2004 by Primeton Technologies, Ltd. ALL RIGHTS RESERVED./ 第19页共19页欢迎使用本手册描述了使用普元EOS所需要掌握的Sql Server基本技能，主要是针对数据库表及数据的创建、添加、修改、删除和查询的基本操作。本出版物包含Primeton的专利信息，它在许可协议下提供，并受版权法保护，本出版物包含的信息不包括任何产品保证。通过您当地的Primeton代表或分部可订购出版物，或致购出版物当您发送信息给Primeton后，即授予Primeton非专有权，Primeton对于您所提供的任何信息，有权利以任何它认为适当的方式使用或散发，而不必对您负任何责任 Copyright Primeton Technologies Corporation 2002, 2003. All rights reserved.本书的相关文档您可能会发现下列资料对您有用：格式使用约定本书对文本格式的使用有如下约定：粗体： 表示突出显示，或可视化操作中的文字【*】 可视化操作中的选项*： Sql文件内容 目录1SQL Server简介42数据表的操作62.1表的操作62.1.1创建表CREATE TABLE62.1.2关键字和约束62.1.3修改表ALTER TABLE92.1.4删除表DROP TABLE92.2数据的操作102.2.1添加INSERT102.2.2删除DELETE102.2.3修改UPDATE103数据查询113.1SELECT语句查询113.1.1基本语句113.1.2使用SELECT子句选取字段和记录113.2WHERE子句123.3GROUP BY子句133.4HAVING子句143.5ORDER BY子句143.6子查询153.6.1NOTIN子查询153.6.2NOTEXISTS子查询153.6.3比较子查询163.7使用集合函数163.7.1SUM函数163.7.2AVG函数163.7.3COUNT函数173.7.4MAX/MIN函数174事务181 SQL Server简介Microsoft SQL Server是一种基于客户机/服务器的关系型数据库管理系统，它使用Transact-SQL语言在服务器和客户机之间传送，如图1-1所示。图1-1SQL Server用来对存放在计算机中的数据库进行组织、管理和检索。SQL Server是一个综合的、通用的、功能极强的关系数据库语言，它包括数据定义、数据操纵、数据管理、存取保护和处理控制等多种功能。利用表、索引、关键字、行和列等来确定存储位置。SQL Server语言本身并不是一个很完整的编程语言，如它不支持流控制等。它的主要特点如下：1. 一体化的特点SQL Server语言能完成定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，用SQL可以实现数据库生命期当中的全部活动。2. 统一的语法结构，多种使用方式SQL Server有两种使用方式，一种是联机使用方式，另一种是嵌入程序方式。大多数程序接口都采用嵌入的SQL语言。3. 高度非过程化在SQL Server中，只需用户提出“干什么”，而无须指出“怎么干”，存取路径的选择和SQL语句操作的过程由系统自动完成。4. 语言简洁1) 数据定义语言（Data Definition Language, DDL），用于定义、撤消和修改数据库。2) 数据查询语言（Query Language, QL）用于查询数据。3) 数据操纵语言（Data Manipulation Language, DML）用于增、删、修改数据。4) 数据控制语言（Data Control Language, DCL）用于数据访问权限的控制。5. Client/Server结构SQL Server能使应用程序采取分布式客户机/服务器结构。交互式查询、报表打印和应用程序称为数据库的“前端”，在个人机上运行，存储和数据管理的后端数据库引擎在服务器上运行，在此情况下，SQL Server作为用于与用户交互的前端工具和用于数据库管理的后端引擎之间的通信桥梁。6. 隐含的并发控制能力SQL Server利用动态的锁定功能防止用户在查询或更新并发操作时相互之间发生冲突，动态锁定是隐含的，用户不需要关心锁定过程。2 数据表的操作2.1 表的操作2.1.1 创建表CREATE TABLE语法格式：CREATE TABLE 表名 表约束(列名1 数据类型 缺省值1，列约束1(列名2 数据类型 缺省值2，列约束2列名n 数据类型 缺省值n，列约束nTABLESPACE 表空间名称STORAGE (存贮的子句）ENABLE 约束名DISABLE 约束名例如：建立由id和name两列组成的TBL表。CREATE TABLE TBL ( id char(3)Name char(20)2.1.2 关键字和约束 主键约束PRIMARY KEY通过建立唯一索引保证指定列的实体完整性，在使用主键约束时，列的空值属性必须定义为NOT NULL，创建主键约束可以通过企业管理器和PRIMARY KEY子句来完成。 外键约束FOREIGN KEY FOREIGN KEY 为表中一列或多列数据提供参照完整性，该约束限制插入到表中被约束列的值必须在被参照的表中已经存在。FOREIGN KEY可以是单列键或多列键。 惟一约束UNIQUE UNIQUE 约束能够保证一列或多列的实体完整性，用于强制非主键列的唯一性。 默认约束DEFAULTDEFAULT约束用于指定一个字段的默认值，当字段值为空时，该字段中将自动添入这个默认值。 检查约束CHECK CHECK约束限制输入到一列或多列中的可能值，从而保证了数据库中的域完整性。在CHECK约束中可以包含搜索条件，但不能包含子查询，在一个表中可以定义多个CHECK约束，对于列也可以定义多个CHECK约束，但是每个CREAT TABLE语句中只能为每列定义一个CHECK约束。 完整的表定义下例显示 pubs 数据库中所创建的三个表（jobs、employee 和 publishers）的完整表定义，其中包含所有的约束定义。/* * jobs table * */CREATE TABLE jobs( job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, job_desc varchar(50) NOT NULL DEFAULT New Position - title not formalized yet, min_lvl tinyint NOT NULL CHECK (min_lvl = 10), max_lvl tinyint NOT NULL CHECK (max_lvl = 250)/* * employee table * */CREATE TABLE employee ( emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT CK_emp_id CHECK (emp_id LIKE A-ZA-ZA-Z1-90-90-90-90-9FM or emp_id LIKE A-Z-A-Z1-90-90-90-90-9FM), /* Each employee ID consists of three characters that represent the employees initials, followed by a five digit store_number ranging from 10000 through 99999 and then the employees gender (M or F). A (hyphen) - is acceptable for the middle initial. */ fname varchar(20) NOT NULL, minit char(1) NULL, lname varchar(30) NOT NULL, job_id smallint NOT NULL DEFAULT 1 /* Entry job_id for new hires. */ REFERENCES jobs(job_id), job_lvl tinyint DEFAULT 10, /* Entry job_lvl for new hires. */ pub_id char(4) NOT NULL DEFAULT (9952) REFERENCES publishers(pub_id), /* By default, the Parent Company Publisher is the company to whom each employee reports. */ hire_date datetime NOT NULL DEFAULT (getdate() /* By default, the current system date is entered. */)/* * publishers table * */CREATE TABLE publishers( pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN (1389, 0736, 0877, 1622, 1756) OR pub_id LIKE 990-90-9), pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT(USA)2.1.3 修改表ALTER TABLE1. 使用ALTER COLUMN子句修改字段定义语法格式：ALTER TABLE 表名ALTER COLUMN 字段名 新数据类型 字段长度例如：ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL2. 使用ADD子句添加字段语法格式：ALTER TABLE 表名ADD字段定义|字段名 AS 字段表达式,n例如：CREATE TABLE doc_exa ( column_a INT) GOALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL3. 使用DROP COLUMN子句删除字段语法格式：ALTER TABLE 表名DROP COLUMN 字段名,n例如：CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) GOALTER TABLE doc_exb DROP COLUMN column_b2.1.4 删除表DROP TABLE语法格式：DROP TABLE 表名,.n例如：删除数据库中的TBL表DROP TABLE TBL2.2 数据的操作2.2.1 添加INSERT语法格式：INSERT INTO 表名(列名1,)VALUES(值1，值2，,值n)例如：在TBL表中插入一条记录，id=013，name= the name of part 013INSERT INTO TBL (id, name) VALUES (013, the name of part 013)2.2.2 删除DELETE语法格式：DELETE FROM 目标表名WHERE 例如：删除TBL表中仓库号=1号的记录。DELETE FROM TBLWHERE Sto_No=1号2.2.3 修改UPDATE语法格式：UPDATE 目标表名SET 字段名1=表达式1, 字段名2=表达式2,. 字段名n=表达式nWHERE 例如：对TBL表中仓库号=1号的零件价格更新为原价的2倍。UPDATE TBLSET price = price * 2WHERE Sto_No=1号3 数据查询SELECT 语句是SQL Server中最基本和最重要的语句之一。查询可以分为两类，一类是用于检索数据的选择查询，另一类是用于更新数据的行为查询。SELECT语句除了用于查询外，还有其他一些用途，比如对记录进行排序、对字段进行汇总以及用检索倒的记录创建新表等。3.1 SELECT语句查询3.1.1 基本语句语法格式：SELECT DISTINCT|ALL 列名INTO 新表名FROM 表名WHERE 搜索条件 HAVING 搜索条件GROUP BY 分组表达式ORDER BY 排序表达式 ASC|DESC3.1.2 使用SELECT子句选取字段和记录1. 选择所有列表示选择指定表或视图中的所有列。下面语句显示TBL表中的所有列：SELECT * FROM TBL2. 选择部分字段查询时可以在选择列表中指定需要显示部分的列名，而过滤掉表中不需要的列，所选列之间用逗号分开，查询结果集合中数据的排列顺序与选择列表中所指定列名排列顺序相同。下面语句显示TBL表中的编号、名称和库存量：SELECT id, name, store_num FROM TBL3. 设置字段别名显示选择查询的结果时，通常在第一行显示各个输出字段的名称，为了方便阅读，可以用更容易理解的字段名称来取代原有字段。下面语句将原表中部分的英文字段名替换为中文别名：SELECT id as 编号, name as 名称, store_num as 库存量 FROM TBL4. 限制返回行数如果SELECT语句返回的结果集合中的行数太多，可以使用TOPn选项来限制返回的数据行数，TOPn说明返回查询结果集合中的前n行数据，下面语句说明只显示TBL数据表中的前4条记录。SELECT top4 * FROM TBL3.2 WHERE子句1. 比较运算符在TBL表中选择编号1和库存量不等于0的记录，并按照降序排列：SELECT * FROM TBL WHERE id=1 and ( store_num0)Order by id DESC2. 范围运算符使用BETWEEN运算符来设定范围。在TBL表中选择库存量在30和60之间的记录：SELECT * FROM TBL WHERE store_num between 30 and 603. 模式匹配符在TBL表中选择库存量不等于0和名称为零件1的所有记录：SELECT * FROM TBL WHERE (store_num0) and name like零件14. 列表运算符IN运算符用于测试给定的值是否在一个子查询或项目列表中。在TBL表中选择库存量为30和60的记录，显示字段为名称和库存量：SELECT name, store_num from TBL WHERE store_num in (30,60)5. 空值运算符空值意味着没有记录，它既不代表空格，也不代表0，空值与任何数据运算或比较时，其结果仍然为空。在TBL中查询名称为空的记录：SELECT * from TBL WHERE name is null6. 逻辑运算符逻辑运算符（AND、OR、NOT）在WHERE子句中连接多个查询条件，NOT用于对一个布尔表达式的值取反，它通常与LIKE、BETWEEN、IN、NULL等关键字一起使用。如用AND查询说明在TBL表中选择编号为1，名称为螺丝的记录。SELECT * from TBL WHERE (id=1) and (name=螺丝)3.3 GROUP BY子句分组表达式是执行分组时的一个表达式，通常是一个字段名，在字段列表中指定的字段别名不能作为分组表达式来使用，在SELECT语句中，同时使用GROUP BY子句和集合函数对数据库进行分组统计。例如：在TBL数据库中查询编号和库存量，并按照仓库号Sto_No分组。SELECT id, store_num FROM TBLGROUP BY Sto_No1. GROUP BY子句中使用WITH CUBE在GROUP BY子句中使用WITH CUBE时，将在由GROUP BY子句提供的分组记录的基础上，针对结果集合中分组的每一种可能的集合都会返回一条汇总记录。2. GROUP BY子句中使用WITH ROLLUP在GROUP BY子句中使用WITH ROLLUP时，结果集合中除了由GROUP BY子句提供的记录外，还会将一些附加的汇总记录引入结果集合之中，各个组以分层形式汇总，排列顺序时从最低层到最高层。3.4 HAVING子句HAVING子句用于指定一组或一个集合的搜索条件，HAVING子句与GROUP BY子句的关系和WHERE与SELECT子句的关系类型。例如：下列WHERE子句仅限定以高于$100的单价销售产品的订单，而HAVING子句进一步将结果限制为只包括 100 件以上的订单：SELECT OrdD1.OrderID AS OrderID, SUM(OrdD1.Quantity) AS Units Sold, SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS RevenueFROM OrdD1WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID FROM Order Details AS OrdD2 WHERE OrdD2.UnitPrice $100)GROUP BY OrdD1.OrderIDHAVING SUM(OrdD1.Quantity) 1003.5 ORDER BY子句语法格式：ORDER BY ASC|DESCASC设定字段的值按照升序排列，DESC指定排序方式为降序排序。例如：从TBL表中检索所有记录，并按照id进行升序排列。SELECT * FROM TBL ORDER BY id ASC3.6 子查询子查询是嵌套在SELECT、INSERT、UPDATE或DELETE语句的WHERE子句或HAVING子句中的SELECT查询语句，它也可以嵌套在另一个子查询中。例如：从TBL数据表中使用子查询检索仓库号为1号的零件信息以及退货数量。SELECT *FROM TBLWHERE (Sto_NO= (SELECT sto_no FROM tbl_no WHERE sto_no=1号)3.6.1 NOTIN子查询NOTIN子查询引出的子查询的结果集合可以包含多个值，在使用IN子查询时，如果该表达式的值与此列中的任何一个值相等，则集成员测试返回TRUE，否则返回FALSE。例如：以下查询查找没有出版过商业书籍的出版商的名称。SELECT pub_nameFROM publishersWHERE pub_id NOT IN (SELECT pub_id FROM titles WHERE type = business)3.6.2 NOTEXISTS子查询例如，要查找不出版商业书籍的出版商的名称：SELECT pub_nameFROM publishersWHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = business)3.6.3 比较子查询例如：下面语句列出退货数量大于平均退货数量的原料信息：SELECT *FROM TBLWHERE (ExitAmou (SELECT AVG(ExitAmou) FROM TBL）3.7 使用集合函数3.7.1 SUM函数SUM函数用于统计数值型字段的和，它只用于数值型字段，而NULL值将被忽略。语法格式：SUM（ALL|DISTINCT表达式）ALL表示SUM函数对所有字段求和DISTINCT说明SUM函数仅对唯一的值求和。例如：从TBL表中查询仓库号1号的零件，并计算价格总和。SELECT SUM(DISTINCT price)FROM TBLWHERE Sto_No= 1号3.7.2 AVG函数AVG函数用于计算一个数值型字段的平均值，该字段中的NULL值在计算过程中被忽略。语法格式：AVG（ALL|DISTINCT表达式）例如：从TBL表中查询仓库号1号的零件，并计算价格平均值。SELECT AVG(DISTINCT price)FROM TBLWHERE Sto_No= 1号3.7.3 COUNT函数 COUNT函数用于统计字段中选取的项目数，或查询输出的记录行数。语法格式：COUNT（ALL|DISTINCT表达式|*）1) COUNT(*)：返回组中的项目数，包括NULL值和重复的值在内。2) COUNT(ALL表达式)：返回组中取非空值的数目。3) COUNT(DISTINCT表达式)：针对组中的每条记录计算表达式，并返回唯一非空值的数目。例如：下面统计TBL数据表中的记录数SELECT COUNT(*) as 记录总数 FROM TBL3.7.4 MAX/MIN函数MAX、MIN函数用于返回表达式中的最大、最小值，在计算过程中遇到NULL值时予以忽略。语法格式：MAX|MIN(ALL|DISTINCT表达式)例如：需要在TBL数据表中列出仓库号为1号，进货数量最多和进货数量最少的记录。SELECT MAX(InAmou) AS 最大进货量, MIN(InAmou) AS 最少进货量FROM TBLWHERE Sto_No=1号4 事务事务是SQL Server系统中的单个逻辑工作单元，一个事务内部的所有语句被作为整体执行，在SQL Server系统中；有下面三种