《SQL网络培训》PPT课件.ppt

SQL初级培训 周小强 2006-2-18 软件概述 数据库：SQLSERVER 帐套数据库：UFDATA_XXX_XXXX 系统库：UFSYSTEM 模板库：UFMODEL 软件概述 Sqlserver的文件: 主文件（*.mdf）-极为重要 日志文件（*.Ldf） Access的文件：只有一个文件*.mdb Microsoft Access和SqlServer的区别 Access不支持存储过程等sql高级功能 Access在网络应用方面要进行文件共享 （是打开文件数据），而sqlserver是进行 连接控制（连接数据） 查询-Select语句 SELECT FORM WHERE 例： SELECT* FROMYOURTABLENAME 函数：对数据的进一步处理 汇总函数 SUM SUM 就如同它的本意一样它返回某一列的所有数值的和。 SELECT SUM(COLUMAN) FROM YOURTABLE COUNT 该函数将返回满足WHERE 条件子句中记录的个数。 最简单的例子就是您想知道某一个表里的记录数： SELECT COUNT(*) FROM YOURTABLE 函数：对数据的进一步处理 MAX 返回表达式的最大值。 语法 MAX ( ALL | DISTINCT expression ) 参数 ALL 对所有的值进行聚合函数运算。ALL 是默认设置。 DISTINCT 指定每个唯一值都被考虑。DISTINCT 对于MAX无意义，使用它仅仅是为了符合 SQL-92 兼 容性。 expression 常量、列名、函数以及算术运算符、按位运算符和字符串运算符的任意组合。MAX可用于数 字列、字符列和 datetime 列，但不能用于 bit 列。不允许使用聚合函数和子查询。 返回类型 返回类型与 expression 相同。 字符函数 LEFT 返回从字符串左边开始指定个数的字符。 语法 LEFT ( character_expression , integer_expression ) 参数 character_expression 字符或二进制数据表达式。character_expression 可以是常量、变量或列。character_expression 必 须是可以隐式地转换为 varchar 的数据类型。否则，请使用 CAST 函数显式转换 character_expression。 integer_expression 是正整数。如果 integer_expression 为负，则返回空字符串。 返回类型 varchar 示例 A. 对列使用 LEFT 函数 下面的示例返回每个书名最左边的 5 个字符。 USE pubs GO SELECT LEFT(title, 5) FROM titles ORDER BY title_id RIGHT 返回从字符串右边开始指定个数的字符。其参数语法与LEFT函数类似 SUBSTRING 返回字符、binary、text 或 image 表达式的一部分。 语法 SUBSTRING ( expression , start , length ) 参数 Expression：是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。 Start：是一个整数，指定子串的开始位置。 Length：是一个整数，指定子串的长度（要返回的字符数或字节数）。 说明 由于在 text 数据上使用 SUBSTRING 时 start 和 length 指定字节数，因此 DBCS 数据（如日本汉字）可能导致 在结果的开始或结束位置拆分字符。此行为与 READTEXT 处理 DBCS 的方式一致。然而，由于偶而会出现奇 怪的结果，建议对 DBCS 字符使用 ntext 而非 text。 返回类型 如果 expression 是支持的字符数据类型，则返回字符数据。如果 expression 是支持的 binary 数据类型，则返回二进 制数据。 注释 在字符数中必须指定使用 ntext、char 或 varchar 数据类型的偏移量（start 和 length）。在字节数中必须指定使用 text、image、binary 或 varbinary 数据类型的偏移量。 示例 在字符串上使用 SUBSTRING 下例显示如何只返回字符串的一部分。该查询在一列中返回 authors 表中的姓氏，在另一列中返回 authors 表中的 名字首字母。 USE pubs SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors ORDER BY au_lname LEN 返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。 语法 LEN ( string_expression ) 参数 string_expression 要计算的字符串表达式。 返回类型 int 示例 下面的示例选定字符个数和位于芬兰的公司的 CompanyName 数据。 USE Northwind GO SELECT LEN(CompanyName) AS Length, CompanyName FROM Customers WHERE Country = Finland CAST 和 CONVERT 将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似 的功能。 语法 使用 CAST： CAST ( expression AS data_type ) 使用 CONVERT： CONVERT (data_type(length), expression , style) 参数 expression 是任何有效的 Microsoft SQL Server 表达式。 data_type 目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类 型。 length nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。 style 日期格式样式，借以将 datetime 或 smalldatetime 数据转换为字符数据（nchar、 nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者字符串格式样式，借以将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、 nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。具体的值见帮助。 此类函数使用也比较多。 SQL 中的子句 Select From Where Order by SQL 中的子句 Group by 子句 GROUP BY ALL group_by_expression ,.n WITH CUBE | ROLLUP USE pubs SELECT type, AVG(price) FROM titles WHERE advance $5000 GROUP BY type HAVING 子句 HAVING 使用 HAVING 子句选择行 HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组 操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。 HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数 。HAVING 子句可以引用选择列表中出现的任意项。 下面的查询得到本年度截止到目前的销售额超过 $40,000 的出版 商： USE pubs SELECT pub_id, total = SUM(ytd_sales) FROM titles GROUP BY pub_id HAVING SUM(ytd_sales) 40000 理解应用 WHERE、GROUP BY 和 HAVING 子句的正确序列对编写高效 的查询代码会有所帮助： WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。 GROUP BY 子句用来分组 WHERE 子句的输出。 HAVING 子句用来从分组的结果中筛选行。 表的联合（Join） INNER 指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型， 则这是默认设置。 FULL OUTER 指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个 表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充 。 LEFT OUTER 指定在结果集中包含左表中所有不满足联接条件的行，且在由内联接返回所 有的行之外，将另外一个表的输出列设为 NULL。 RIGHT OUTER 指定在结果集中包含右表中所有不满足联接条件的行，且在由内联接返回的 所有行之外，将与另外一个表对应的输出列设为 NULL。 表的联合（Join） 所需的资料并不是放在同一个资料表中, 在这个时候, 你 就要用到 Join ，如我们U8中的主子表 当然 Join 如何将不同的数据库的资料结合, 还要看你如 何使用它。 一共有四种不同的 Join 的方式： INNER Join FULL OUTER Join LEFT OUTER Join RIGHT OUTER Join Union运算符 UNION 运算符 将两个或更多查询的结果组合为单个结果集，该结果集包含联合 查询中的所有查询的全部行。这与使用联接组合两个表中的列不 同。 使用 UNION 组合两个查询的结果集的两个基本规则是： 所有查询中的列数和列的顺序必须相同。 数据类型必须兼容。 子查询：内嵌的SQL 子句 if exists (select * from dbo.sysobjects where id = object_id(Ndbo.PO_Podetails) and OBJECTPROPERTY(id, NIsUserTable) = 1) drop table dbo.PO_Podetails GO 操作数据（INSERT UPDATE DELETE） INSERT INTO子句 INSERT INTO titles (title_id, title, type, pub_id, price) VALUES (BU9876, Creating Web Pages, business, 1389, 29.99) 操作数据（INSERT UPDATE DELETE） UPDATE子查询 子查询可以嵌套在 UPDATE、DELETE 和 INSERT 语句以及 SELECT 语句中。 下面的查询使由 New Moon Books 出版的所有书籍的价格加倍。该查询更新 titles 表；其子查询引用 publishers 表。 UPDATE titles SET price = price * 2 WHERE pub_id IN (SELECT pub_id FROM publishers WHERE pub_name = New Moon Books) 下面是使用联接的等效 UPDATE 语句： UPDATE titles SET price = price * 2 FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id AND pub_name = New Moon Books 操作数据（INSERT UPDATE DELETE） DELETE子句 通过下面嵌套的查询，可以删除商业书籍的所有销售记 录： DELETE sales WHERE title_id IN (SELECT title_id FROM titles WHERE type = business) 操作数据（INSERT UPDATE DELETE） Update使用注意（where后的条件一定要验证） Select * from accinformation where cid = 99 and cvalue = 8.60 Update accinformation set cvalue = 8.50 where cid = 99 and cvalue = 8.60 操作数据（Select Into） Select into 子句 SELECT Shippers.*, Link.Address, Link.City, Link.Region, Link.PostalCode INTO NewShippers FROM Shippers JOIN LinkServer.DB.dbo.Shippers AS Link ON (Shippers.ShipperID = Link.ShipperID) CREATE DATABASE 创建一个新数据库及存储该数据库的文件，或从先前创建的数据库的文 件中附加数据库。 语法 CREATE DATABASE database_name ON ,.n , ,.n LOG ON ,.n COLLATE collation_name FOR LOAD | FOR ATTACH := PRIMARY ( NAME = logical_file_name , FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n := FILEGROUP filegroup_name ,.n 以上就是具体的语法，其中的参数可以参照SQL的帮助。 CREATE TABLE 创建新表。 和创建数据库一样，我们也可以利用图形界面工具（如企业管理器来创建），但理解其具体的操作并不是一件坏 事。 创建表的最基本语法（详细的语法见帮助）就是 CREATE TABLE table_name ( field1 datatype NOT NULL ， field2 datatype NOT NULL ， field3 datatype NOT NULL ， 。 ) 我们来看一个简单的例子，如下： CREATE TABLE BILLS ( NAME CHAR(30)， AMOUNT INT， ACCOUNT_ID INT ) 分析 该语句创建了一个名字叫BILLS的表，在BILLS 表中有三个字段NAME，ACCOUNT和ACCOUNT_ID。 其中NAME 字段为字符类型可以存储长度30 的字符串，而AMOUNT和AMOUNT_ID 则只参存储整数。 ALTER TABLE 语句 加入已经存在的表中的某一列 ALTER TABLE tablname ADD | column_name AS computed_column_expression ,.n 以上例子是在MYTABLE里增加一个类型为VARCHAR(20) ，允许为NULL的列NEWcolumn 。 例子： ALTER TABLE MYTABLE ADD NEWcolumn VARCHAR(20) NULL 创建和操作表 删除一列到已经存在的表中 ALTER TABLE tablname DROP CONSTRAINT constraint_name | COLUMN column ,.n 以上例子是在MYTABLE里删除列NEWcolumn 。 例子： ALTER TABLE MYTABLE DROP COLUMN NEWcolumn 创建和操作表 修改已经存在的表中的某一列 ALTER TABLE tablname ALTER COLUMN column_name new_data_type ( precision , scale ) COLLATE NULL | NOT NULL | ADD | DROP ROWGUIDCOL 以上例子是在MYTABLE里将列NEWcolumn 的类型改变成INT。 例子： ALTER TABLE MYTABLE ALTER COLUMNNEWcolumn INT 注：你可以增加或减少某一列的长度，但是你不能将它减少到使修改后的长度小于其中的已有数据长度。 修改前一定要慎重！ 创建和操作表 DROP TABLE 语句 SQL 提供了一个可以从数据库去彻底地移去某个表的命令。DROP TABLE 可以从数据 库中删除一个指定的表。 一旦这个命令发出以后就没有办法撤消它（在事务提交前除外，关于事务我们将在后面讲解）。它最常用在你创建一个 临时表并且已经进行完毕了你的全部计划工作的时候。 DROP TABLE 语句的语法格式如下 DROP TABLE table_name 下边是如何删掉一个叫NEW_BILLS 表的实例 DROP TABLE NEW_BILLS 注意：系统没有给出你提示该命令不会问你Are you sure? Y/N 。 但是删除操作已经执行，表已经永远地删除了。 删除一个数据库的命令也类似：DROP DATABASE，这里就不再解说。 创建视图和索引 创建视图的语法如下： CREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 创建视图和索引 使用简单的 CREATE VIEW 下例创建具有简单 SELECT 语句的视图。当需要频繁地查询列的 某种组合时，简单视 图非常有用。 USE pubs GO IF EXISTS ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = titles_view) DROP VIEW titles_view GO CREATE VIEW titles_view AS SELECT title, type, price, pubdate FROM titles GO 创建视图和索引 删除视图语句 就像每一个CREATE 语句一样CREATE VIEW 语句对应的也与DROP VIEW 语句相 对应。其语法形式如下： DROP VIEW view_name 在使用它的时候需要记住DROP VIEW 命令会使所有与DROP 视图相关联 的视图不 能正常运行，此时如果你访问这个数据库，将返回如下错误： 由于绑定错误，未能使用视图或函数 对应名称。 一些数据库系统甚至会将所有与要DROP 的视图相关联的视图也删除掉 创建视图和索引 具体创建索引的语句可以参见帮助CREATE INDEX，删除索引DROP INDEX。 高级SQL-游标 建立（定义）一个游标： DECLARE au_lnameVARCHAR(30) DECLARE authors_cursor CURSOR FOR SELECT au_lname FROM authors OPEN authors_cursor打开游标： FETCH NEXT FROM authors_cursor INTO au_lname 取游标记录： 浏览游标记录： WHILE FETCH_STATUS = 0 BEGIN print au_lname FETCH NEXT FROM authors_cursor INTO au_lname END 关闭游标： CLOSE authors_cursor 释放游标： DEALLOCATE authors_cursor 高级SQL-存储过程 CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 高级SQL-触发器 语法： create trigger trigger_name on table_name for insert, update, delete as SQL_Statements 基本的事务控制（ TRANSACTION ） BEGIN TRANSACTION BEGIN TRAN SACTION transaction_name | tran_name_variable WITH MARK description COMMIT TRANSACTION COMMIT TRAN SACTION transaction_name | tran_name_variable ROLLBACK TRANSACTION ROLLBACK TRAN SACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 基本的事务控制（ TRANSACTION ） Transaction应用 use ufdata_887_2004 select * from accinformation where cid = 99 begin TRANSACTION test update accinformation set cvalue = 8.50 where cid = 99 rollback transaction test 存储过程的执行 EXECUTE （exec） EXEC UTE ( string_variable | N tsql_string + .n ) EXECUTE proc_calculate_taxes p2 = A 存储过程的执行 下例使用 xp_cmdshell 扩展存储过程列出文件扩展名为 .exe 的所有文件的目录。 USE master EXECUTE xp_cmdshell dir *.exe 系统存