SQL网络培训(共60张).pptx

《SQL网络培训(共60张).pptx》由会员分享，可在线阅读，更多相关《SQL网络培训(共60张).pptx（60页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL初级培训初级培训 周小强 2006-2-18软件概述 数据库：SQLSERVER帐套数据库：UFDATA_XXX_XXXX系统库：UFSYSTEM模板库：UFMODEL软件概述Sqlserver的文件:主文件（*.mdf）-极为重要日志文件（*.Ldf）Access的文件：只有一个文件*.mdbMicrosoft Access和和SqlServer的区别的区别 Access不支持存储过程等sql高级功能 Access在网络应用方面要进行文件共享（是打开文件数据），而sqlserver是进行连接控制（连接数据）查询-SelectSelect语句语句SELECTFORMWHERE例：SELE

2、CT*FROMYOURTABLENAME函数：对数据的进一步处理 汇总函数SUMSUM 就如同它的本意一样它返回某一列的所有数值的和。SELECT SUM(COLUMAN) FROM YOURTABLECOUNT该函数将返回满足WHERE 条件子句中记录的个数。最简单的例子就是您想知道某一个表里的记录数：SELECT COUNT(*) FROM YOURTABLE函数：对数据的进一步处理MAX返回表达式的最大值。语法语法MAX ( ALL | DISTINCT expression )参数参数ALL对所有的值进行聚合函数运算。ALL 是默认设置。DISTINCT指定每个唯一值都被考虑。DIST

3、INCT 对于MAX无意义，使用它仅仅是为了符合 SQL-92 兼容性。expression常量、列名、函数以及算术运算符、按位运算符和字符串运算符的任意组合。MAX可用于数字列、字符列和 datetime 列，但不能用于 bit 列。不允许使用聚合函数和子查询。返回类型返回类型返回类型与 expression 相同。字符函数LEFT返回从字符串左边开始指定个数的字符。语法语法LEFT ( character_expression , integer_expression ) 参数参数character_expression字符或二进制数据表达式。character_expression 可以

4、是常量、变量或列。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返回从字符串右边开始指定个数的字符

5、。其参数语法与LEFT函数类似SUBSTRING返回字符、binary、text 或 image 表达式的一部分。语法语法SUBSTRING ( ( expression , , start , , length ) ) 参数参数Expression：是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。Start：是一个整数，指定子串的开始位置。Length：是一个整数，指定子串的长度（要返回的字符数或字节数）。 说明说明 由于在 text 数据上使用 SUBSTRING 时 start 和 length 指定字节数，因此 DBCS 数据（如日本汉字

6、）可能导致在结果的开始或结束位置拆分字符。此行为与 READTEXT 处理 DBCS 的方式一致。然而，由于偶而会出现奇怪的结果，建议对 DBCS 字符使用 ntext 而非 text。返回类型返回类型如果 expression 是支持的字符数据类型，则返回字符数据。如果 expression 是支持的 binary 数据类型，则返回二进制数据。注释注释在字符数中必须指定使用 ntext、char 或 varchar 数据类型的偏移量（start 和 length）。在字节数中必须指定使用 text、image、binary 或 varbinary 数据类型的偏移量。 示例示例 在字符串上使用

7、在字符串上使用 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示例示例下面的示例选定字符个数和位于芬兰的公司的

8、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)参数参数expre

9、ssion是任何有效的 Microsoft SQL Server 表达式。data_type目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。lengthnchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。 style日期格式样式，借以将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者字符串格式样式，借以将 float、real、money 或 smal

10、lmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。具体的值见帮助。此类函数使用也比较多。SQL 中的子句Select Select From From Where Where Order by 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

11、 type HAVING HAVING 子句子句HAVING 使用使用 HAVING HAVING 子句选择行子句选择行HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。下面的查询得到本年度截止到目前的销售额超过 $40,000 的出版商：USE pubs SELECT pub_id, total = S

12、UM(ytd_sales) FROM titles GROUP BY pub_id HAVING SUM(ytd_sales) 40000 理解应用理解应用 WHEREWHERE、GROUP BY GROUP BY 和和 HAVING HAVING 子句的正确序列对编写高效子句的正确序列对编写高效的查询代码会有所帮助：的查询代码会有所帮助： WHERE WHERE 子句用来筛选子句用来筛选 FROM FROM 子句中指定的操作所产生的行。子句中指定的操作所产生的行。GROUP BY GROUP BY 子句用来分组子句用来分组 WHERE WHERE 子句的输出。子句的输出。HAVING HAV

13、ING 子句用来从分组的结果中筛选行。子句用来从分组的结果中筛选行。 表的联合（Join）INNERINNER指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型，则这是默认设置。FULL OUTERFULL OUTER指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充。LEFT OUTERLEFT OUTER指定在结果集中包含左表中所有不满足联接条件的行，且在由内联接返回所有的行之外，将另外一个表的输出列设为 NULL。RIGHT OUTERRIGHT OUTER指定在结果集中包含右表

14、中所有不满足联接条件的行，且在由内联接返回的所有行之外，将与另外一个表对应的输出列设为 NULL。表的联合（Join）所需的资料并不是放在同一个资料表中, 在这个时候, 你就要用到 Join ，如我们U8中的主子表 当然 Join 如何将不同的数据库的资料结合, 还要看你如何使用它。一共有四种不同的 Join 的方式：INNER JoinFULL OUTER JoinLEFT OUTER JoinRIGHT OUTER JoinUnion运算符运算符 UNION 运算符运算符将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中的所有查询的全部行。这与使用联接组合两个表中的列不同。使用

15、 UNION 组合两个查询的结果集的两个基本规则是： 所有查询中的列数和列的顺序必须相同。数据类型必须兼容。 子查询：内嵌的SQL 子句if exists (select * from dbo.sysobjects where id = object_id(Ndbo.PO_Podetails) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.PO_PodetailsGO操作数据（INSERT UPDATE DELETE）INSERT INTOINSERT INTO子句子句INSERT INTO INSERT INTO titles

16、 (title_id, title, type, pub_id, price) titles (title_id, title, type, pub_id, price) VALUES (BU9876, Creating Web Pages, business, VALUES (BU9876, Creating Web Pages, business, 1389, 29.99)1389, 29.99) 操作数据（INSERT UPDATE DELETE）UPDATEUPDATE子查询子查询子查询可以嵌套在子查询可以嵌套在 UPDATEUPDATE、DELETE DELETE 和和 INSERT

17、 INSERT 语句以及语句以及 SELECT SELECT 语句中。语句中。下面的查询使由下面的查询使由 New Moon Books New Moon Books 出版的所有书籍的价格加倍。该查询更出版的所有书籍的价格加倍。该查询更新新 titles titles 表；其子查询引用表；其子查询引用 publishers publishers 表。表。UPDATE titles UPDATE titles SET price = price SET price = price * * 2 2 WHERE pub_id IN (SELECT pub_id FROM publishers WHE

18、RE pub_name WHERE pub_id IN (SELECT pub_id FROM publishers WHERE pub_name = New Moon Books) = New Moon Books) 下面是使用联接的等效下面是使用联接的等效 UPDATE UPDATE 语句：语句：UPDATE titles UPDATE titles SET price = price SET price = price * * 2 2 FROM titles FROM titles INNER JOIN publishers INNER JOIN publishers ON titles

19、.pub_id = publishers.pub_id AND pub_name = New ON titles.pub_id = publishers.pub_id AND pub_name = New Moon Books Moon Books 操作数据（INSERT UPDATE DELETE）DELETEDELETE子句子句通过下面嵌套的查询，可以删除商业书籍的所有销售记通过下面嵌套的查询，可以删除商业书籍的所有销售记录：录：DELETE sales DELETE sales WHERE title_id WHERE title_id IN (SELECT title_id FROM

20、titles WHERE type = IN (SELECT title_id FROM titles WHERE type = business) business) 操作数据（INSERT UPDATE DELETE）Update使用注意（where后的条件一定要验证）Select * from accinformation where cid = 99 and cvalue = 8.60Update accinformation set cvalue = 8.50where cid = 99 and cvalue = 8.60操作数据（Select Into）Select into 子句

21、子句SELECT Shippers.*, Link.Address, Link.City, Link.Region, Link.PostalCodeINTO NewShippersFROM Shippers JOIN LinkServer.DB.dbo.Shippers AS Link ON (Shippers.ShipperID = Link.ShipperID)CREATE DATABASECREATE DATABASE创建一个新数据库及存储该数据库的文件，或从先前创建的数据库的文件中附加数据库。语法语法CREATE DATABASE database_name ON , ,.n , ,

22、, ,.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的帮助。CREA

23、TE 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 表中有三个字

24、段NAME，ACCOUNT和ACCOUNT_ID。其中NAME 字段为字符类型可以存储长度30 的字符串，而AMOUNT和AMOUNT_ID 则只参存储整数。ALTER TABLE 语句语句加入已经存在的表中的某一列ALTER TABLE tablname ADDADD | column_name AS computed_column_expression , ,.n 以上例子是在MYTABLE里增加一个类型为VARCHAR(20) ，允许为NULL的列NEWcolumn 。例子：ALTER TABLE MYTABLEADD NEWcolumn VARCHAR(20) NULL创建和操作表删除

25、一列到已经存在的表中ALTER TABLE tablname DROPDROP CONSTRAINT constraint_name | COLUMN column , ,.n 以上例子是在MYTABLE里删除列NEWcolumn 。例子：ALTER TABLE MYTABLEDROP COLUMN NEWcolumn创建和操作表修改已经存在的表中的某一列ALTER TABLE tablname ALTER COLUMNALTER COLUMN column_name new_data_type ( ( precision , , scale ) ) COLLATE NULL | NOT NU

26、LL | ADD | DROP ROWGUIDCOL 以上例子是在MYTABLE里将列NEWcolumn 的类型改变成INT。例子：ALTER TABLE MYTABLE ALTER COLUMNALTER COLUMNNEWcolumn INT注：你可以增加或减少某一列的长度，但是你不能将它减少到使修改后的长度小于其中的已有数据长度。修改前一定要慎重！创建和操作表DROP TABLE 语句语句SQL 提供了一个可以从数据库去彻底地移去某个表的命令。DROP TABLE 可以从数据库中删除一个指定的表。 一旦这个命令发出以后就没有办法撤消它（在事务提交前除外，关于事务我们将在后面讲解）。它最常

27、用在你创建一个临时表并且已经进行完毕了你的全部计划工作的时候。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_

28、statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 创建视图和索引使用简单的使用简单的 CREATE VIEW下例创建具有简单 SELECT 语句的视图。当需要频繁地查询列的某种组合时，简单视图非常有用。USE pubs GOIF EXISTS ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = titles_view) DROP VIEW titles_view GO CREATE VIEW titles_vie

29、w AS SELECT title, type, price, pubdate FROM titles GO 创建视图和索引删除视图语句删除视图语句就像每一个CREATE 语句一样CREATE VIEW 语句对应的也与DROP VIEW 语句相对应。其语法形式如下： DROP VIEW view_name在使用它的时候需要记住DROP VIEW 命令会使所有与DROP 视图相关联的视图不能正常运行，此时如果你访问这个数据库，将返回如下错误：由于绑定错误，未能使用视图或函数 对应名称。一些数据库系统甚至会将所有与要DROP 的视图相关联的视图也删除掉创建视图和索引具体创建索引的语句可以参见帮助C

30、REATE INDEX，删除索引DROP INDEX。高级SQL-游标建立（定义）一个游标： DECLARE au_lnameDECLARE au_lnameVARCHAR(30)VARCHAR(30)DECLAREDECLARE authors_cursor authors_cursor CURSORCURSOR FORFOR SELECT au_lnameSELECT au_lnameFROM authorsFROM authorsOPENOPEN authors_cursor authors_cursor打开游标：FETCHFETCH NEXTNEXT FROMFROM authors_

31、cursor INTO authors_cursor INTO au_lnameau_lname取游标记录：浏览游标记录：WHILE FETCH_STATUS = 0 BEGIN print au_lnameau_lnameFETCHFETCH NEXTNEXT FROMFROM authors_cursor INTO authors_cursor INTO au_lnameau_lnameENDEND关闭游标：CLOSECLOSE authors_cursor authors_cursor释放游标：DEALLOCATE DEALLOCATE authors_cursorauthors_cur

32、sor高级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_nameon table_namefor insert, update, deleteas SQL_Statements基本的事

33、务控制（ TRANSACTION ）BEGIN TRANSACTIONBEGIN TRANSACTIONBEGIN TRAN SACTION BEGIN TRAN SACTION transaction_nametransaction_name | | tran_name_variabletran_name_variable WITH MARK WITH MARK descriptiondescription COMMIT TRANSACTIONCOMMIT TRANSACTIONCOMMIT TRAN SACTION COMMIT TRAN SACTION transaction_namet

34、ransaction_name | | tran_name_variable tran_name_variable ROLLBACK TRANSACTIONROLLBACK TRANSACTIONROLLBACK TRAN SACTION ROLLBACK TRAN SACTION transaction_nametransaction_name | | tran_name_variabletran_name_variable| | savepoint_namesavepoint_name | | savepoint_variable savepoint_variable 基本的事务控制（ T

35、RANSACTION ）Transaction应用应用use ufdata_887_2004select * from accinformation where cid = 99begin TRANSACTION testupdate accinformation set cvalue = 8.50 where cid = 99rollback transaction test存储过程的执行 EXECUTE （exec） EXEC UTE ( ( string_variable | N tsql_string + + .n ) ) EXECUTE proc_calculate_taxes p2

36、 = A 存储过程的执行下例使用 xp_cmdshell 扩展存储过程列出文件扩展名为 .exe 的所有文件的目录。USE masterEXECUTE xp_cmdshell dir *.exe系统存储过程系统存储过程sp_executesqlsp_executesqlsp_executesql sp_executesql stmtstmt = = stmt , , paramsparams = = NNparameter_name data_type , ,.n , , param1 = = value1 , ,.n 下面的示例创建并执行一个简单的 SELECT 语句，其中包含名为 leve

37、llevel 的嵌入参数。execute sp_executesql Nselect * from pubs.dbo.employee where job_lvl = level, Nlevel tinyint, level = 35 系统存储过程sp_executesql我们U8的例子use ufdata_887_2004exec sp_executesql Nselect * from accinformation where cid = cid, Ncid int,cid = 99系统表SysdatabasesSysdatabasesSysobjectsSysobjectsSyscolu

38、mnsSyscolumnsSysindexesSysindexesSysmessagesSysmessages系统表SysdatabasesSysdatabases（要在要在mastermaster库中使用）库中使用）NameNamesysnamesysname数据库的名称。数据库的名称。DbidDbidsmallintsmallint数据库数据库 IDID（大于大于6 6为用户数据库）为用户数据库）库在哪里？库在哪里？用户自己建立的数据库用户自己建立的数据库Select Select * * from master.sysdatabases where dbid 6 from master.

39、sysdatabases where dbid 6看看看看SqlserverSqlserver自带的数据库自带的数据库Select Select * * from master.sysdatabases where dbid = 6 from master.sysdatabases where dbid 0系统表Sysmessages（ master库中使用）select * from master.sysmessagesSQL 日常使用技巧1.1.SqlserverSqlserver企业管理器企业管理器2.2.SqlserverSqlserver查询分析器查询分析器3.3.Sqlserver

40、Sqlserver事件探察器事件探察器SQL 日常使用技巧1.1.SqlserverSqlserver企业管理器的日常使用技巧企业管理器的日常使用技巧注册其他机器的sqlserver，有时可用于远程测试SQL 日常使用技巧本机属性本机属性端口号1433；混合认证模式；手工内存分配SQL 日常使用技巧编辑注册属性编辑注册属性SQL 日常使用技巧Sa密码修改密码修改SQL 日常使用技巧导表操作导表操作SQL 日常使用技巧Sqlserver中中bak文件的引入文件的引入SQL 日常使用技巧SqlserverSqlserver查询分析器查询分析器对象浏览器中的模板使用对象浏览器中的模板使用SqlSql脚本都要在查询分析器中执行脚本都要在查询分析器中执行结束The End演讲完毕，谢谢观看！