《SQL SERVER 数据库培训教程》

SQL SERVER 数据库 使用,2011-02-14 北京思路创新科技有限公司,SQL SERVER 数据库基础 创建和管理数据库 创建和管理数据表 增、删、改数据 数据检索 创建和管理视图 T-SQL编程 创建和管理存储过程,内 容,SQL SERVER 数据库,SQL Server 2005是一种高性能的关系型数据库管理系统，该系统是一个全面的数据库平台，使用集成的商业智能工具提供企业级的数据管理，可以为不同规模的企业提供不同的数据解决管理方案。 SQL Server 2005是企业信息系统客户服务器体系结构的首选产品之一。,4,SQL Server 2005的性能与体系结构,2.1.2 SQL Server 2005的体系结构 1、CS结构 2、服务器端是安装了SQL Server 2005服务器组件的计算机。 3、客户端是安装了SQL Native Client的计算机,SQL SERVER 数据库安装,SQL SERVER 2005 安装教程,数据库存储结构,数据库存储结构,系统数据库 SQL Server 2005包含master、model、msdb、 tempdb4个系统数据库。,master： 数据库记录SQL Server 2005实例的所有系统级信息。master数据库还记录所有其他数据库是否存在以及这些数据库文件的位置。另外，master还记录了SQL Server的初始化信息。,Model： 数据库用作SQL Server 2005实例上创建的所有数据库的模板。如果修改model数据库，之后创建的所有数据库都将继承这些修改。,msdb ： msdb数据库由SQL Server代理用来计划警报和作业。,Tempdb： 数据库是连接到SQL Server 2005实例的所有用户都可用的全局资源，它保存所有临时表和临时存储过程。另外，它还用来满足所有其他临时存储要求。,启动、停止数据库服务,9,SQL Server 的安全性,SQL server 2005使用两层安全机制来确认用户的有效性-即身份验证和权限验证两个阶段。 1.身份验证模式 Windows认证： 用户对SQL server 访问的控制由Windows账号或用户组完成，当进行连接时，用户不需要提供SQL server 登陆账号。 SQL server认证： SQL server管理员必须建立SQL server 登陆账号和口令。当用户要连接到SQL server 时，必须同时提供SQL server 的登陆账号和口令。 混合认证模式：两种模式同时工作，用户即能使用Windows 2005认证模式又能使用SQL Server认证模式连接到SQL Server服务器。,10,SQL Server 2005的安全性,2.权限验证 身份验证成功，连接到SQL Server数据库后，用户必须使用特定的用户账号才能对数据库进行访问。 SQL Server采取三个步骤来确认权限。 1)当用户执行一项操作时，例如用户执行了一条插入一条记录的指令，客户端将用户T-SQL 语句发给SQL Server。 2)当SQL Server接收到该命令语句后，立即检查该用户是否有执行这条指令的权限。 3)如果用户具备这个权限，SQL Server将完成相应的操作，如果用户没有这个权限，SQL Server系统将返回一个错误给用户。,管理数据库,在开始菜单中打开“所有程序Microsoft SQL Server 2005 SQL Server Management Studio”，如现如下登录页面：,SQL SERVER 数据库基础 创建和管理数据库 创建和管理数据表 增、删、改数据 数据检索 创建和管理视图 T-SQL编程 创建和管理存储过程,内 容,SQL Server 是Microsoft公司推出的关系型数据库管理系统。从SQL Server 1.0版到目前的SQL Server 2008 R2 经历了20年的发展过程。 1988年第一次提出SQL Server 产品名称 1989年SQL Server 1.0版本发布 1990年SQL Server 1.1版本发布 1991年SQL Server 1.11版本发布 1995年SQL Server 6.5版本发布 1998年SQL Server 7.0版本发布 2000年SQL Server 2000版本发布 2005年SQL Server 2005版本发布 2008年SQL Server 2008版本发布 2010年SQL Server 2008 R2版本发布,2.创建和管理数据库,创建数据库,若要创建数据库，必须确定数据库的名称、所有 者、大小以及存储该数据库的文件或文件组。 创建数据库时，根据数据库中预期的最大数据量， 应创建尽可能大的数据文件 创建数据库有两种方式： .使用脚本创建数据库 .使用图形界面创建数据库,使用脚本创建数据库,语法：,CREATE DATABASE database_name ON PRIMARY ,.n , ,.n LOG ON ,.n COLLATE collation_name ; := ( NAME=logical_file_name , FILENAME = os_file_name , SIZE=size KB|MB|GB|TB , MAXSIZE=max_size KB|MB|GB|TB | UNLIMITED , FILEGROWTH =growth_increment KB|MB|GB|TB|% ) ,.n := FILEGROUP filegroup_name DEFAULT ,.n ,Database_name:数据库名称，必须唯一 NAME=logical_file_name 指定文件的逻辑名称 FILENAME= os_file_name 指定操作系统（物理）文件名称,1.使用默认设置创建数据库 CREATE DATABASE DBTEST 2.创建自定义数据路径的数据库 CREATE DATABASE DBTEST2 ON ( NAME=DBTEST3, FileNAME=c:DBTEST02.mdf,size=5MB ),在图形界面下创建数据库,在“Microsoft SQL Server Management Studio”中创建数据库的过程如下。 启动“Microsoft SQL Server Management Studio”，在“数据库”节点上右击，选择快捷菜单中的“新建数据库”命令。弹出“新建数据库”对话框，在“常规”数据页的“数据库名称”文本框中，输入要创建的数据库的名称，如图。,删除数据库,语法： DROP DATABASE database_name | database_snapshot_name ,.n ; 如：删除DBTEST数据 DROP DATABASE DBTEST,SQL SERVER 数据库基础 创建和管理数据库 创建和管理数据表 增、删、改数据 数据检索 创建和管理视图 T-SQL编程 创建和管理存储过程 SQL SERVER 企业管理器使用,内 容,3.创建和管理表,表的基本概念 设计表 数据类型 创建表 删除表,3.1 表的基本概念,在数据库中，表是由数据按一定的顺序和格式构成的数据集合，是数据库的主要对象。每一行代表一条记录，每一列代表记录的一个字段。,3.2 设计表,对于具体的某一个表，在创建之前，需要确定表的下列特征： 表要包含的数据的类型； 表中的列数，每一列中数据的类型和长度（如果必要）； 哪些列允许空值； 是否要使用以及何处使用约束、默认设置和规则； 所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。,.3 数据类型,SQL Server 2005常用数据类型： 数字相关： Int bit decimal(p,s) Money float(n) 日期和时间： Datetime 字符串相关： char(n) varchar(n) nvarchar(n) Text 二进制字 Image,3.4 创建表,语法如下： CREATE TABLE table_name (column_name column_property) ,. 参数: table_name: 新表的名称 column_name:表中列的名称 column_property:列的属性,CREATE TABLE TESTTABLE ( ID int primary key, NAME nvarchar(50), CLASS nvarchar(100), DATE DATETIME ),创建如下表,3.5 删除表,语法： DROP TABLE 表名 如： DROP TABLE TESTTABLE,SQL SERVER 数据库基础 创建和管理数据库 创建和管理数据表 增、删、改数据 数据检索 创建和管理视图 T-SQL编程 创建和管理存储过程,内 容,4.1 数据增加,语法： INSERT INTO table_name(column_list) VALUES(data_values,n) 参数： column_list是用逗号分隔的一些列名称，可 用来指定为其提供数据的列。 VALUES关键字为表的某一行指定值。,在表TESTTABLE表加一条记录： INSERT INTO TESTTABLE(ID,NAME,CLASS,DATE) VALUES(10,test1,202,2008-01-10) 同 INSERT INTO TESTTABLE(NAME,CLASS,DATE,ID) VALUES(test1,202,2008-01-10,10) 简写为： INSERT INTO TESTTABLE VALUES(10,test1,202,2008-01-10) VALUES提供的值与表的字段数量和顺序必须相同,4.2 数据修改,语法： UPDATE table_name SET 列名=表达式 WHERE 表达式 参数： SET子句指定要更改的列和这些列的新值,修改ID为了10的NAME值为NEWNAME: UPDATE TESTTABLE SET NAME=NEWNAME WHERE ID=10 修改ID为了10的DATE值为当前时间和CLASS的值为2008: UPDATE TESTTABLE SET CLASS=2008 AND DATE=GetDate() WHERE ID=10 修改的值来自其它表中： UPDATE dbo.TESTTABLE SET NAME=dbo.test.NAME FROM test WHERE TESTTABLE.ID=test.ID AND TEST.ID=10,4.3 删除数据,语法简化形式为： DELETE FROM table_name WHERE search_condition 参数： table_name:表名 search_condition:搜索表达式,删除表中等于10的数据： DELETE TABLE TESTTABLE WHERE ID=10 删除表中所有的数据： DELETE TABLE TESTTABLE 或 TRUNCATE TABLE TRUNCATE TABLE与不含有WHERE子句的DELETE语句在功能上相同。但是，TRUNCATE TABLE速度更快，并且使用更少的系统资源和事务日志资源。,SQL SERVER 数据库基础 创建和管理数据库 创建和管理数据表 增、删、改数据 数据检索 创建和管理视图 T-SQL编程 创建和管理存储过程,内 容,.数据查询,所谓查询，就是对已经存在于数据库中的数 据按特定的组合、条件或次序进行检索。查询功 能是数据库最基本也是最重要的功能。,描述结果集的列，它指定了结果集中要包含的列的名称，是一个逗号分隔的表达式列表,所有列时，使用*代替,SELECT语句的语法如下： SELECT select_list INTO new_table_name FROM table_list WHERE search_conditions GROUP BY group_by_list HAVING search_conditions ORDER BY order_list ASC | DESC ,SELECT 语法：,指定使用结果集来创建新表。new_table_name指定新表的名称。,WHERE子句是一个筛选条件，它定义了源表中的行要满足SELECT语句的要求所必须达到的条件。,GROUP BY子句根据group_by_list列中的值将结果集分成组。,HAVING子句是应用于结果集的附加筛选，用来向使用GROUP BY子句的查询中添加数据过滤准则。,ORDER BY子句定义了结果集中行的排序顺序。,指定要检索数据的表名或视图名。,选择查询,一般查询都不是针对全表所有行的查询，只是从整 个表中选出满足指定条件的内容，这就要用到WHERE 子句。 以下是选择行的基本语法： SELECT FROM WHERE ,比较运算符,逻辑运算符,运算符,特殊运算符：,示例数据库结构,范围搜索条件,范围搜索返回介于两个指定值之间的所有值 ，可分为包括范围和排他范围两种类型。,查询年龄在17-18之间的学生： SELECT * FROM dbo.学生表 WHERE 年龄=17 and 年龄=18 SELECT * FROM dbo.学生表 WHERE 年龄between 17 and 18,列表搜索条件,IN关键字使用户可以选择与列表中的任意值匹 配的行。,查询年龄在17-18之间的学生： SELECT * FROM dbo.学生表 WHERE 年龄 IN (17,18),搜索条件中的模式匹配,LIKE关键字搜索与指定模式匹配的字符串、 日期或时间值。模式包含要搜索的字符串，字符 串中可包含4种通配符的任意组合，搜索条件中 可用的通配符如表所示。,查询所有姓“王”的学生: SELECT * FROM dbo.学生表 WHERE 姓名LIKE 王% 查询所有姓“王”的学生，但名字长度为两个字: SELECT * FROM dbo.学生表 WHERE 姓名LIKE 王_ 查询所有姓王的学生，但是名字中不包含“洁”字： SELECT * FROM dbo.学生表 WHERE 姓名LIKE 王%洁 查询所有姓王的学生，但是名字中包含“山”字： SELECT * FROM dbo.学生表 WHERE 姓名LIKE 王%山,常用函数,AVG：用于计算查询结果的平均值 MAX：该函数用于查找最大值 IN： 该函数用于查找最小值 SUM：该函数计算查询到的数据值的总 COUNT： 该函数可以计算查询到的结果的数目 GetDate：获得当前时间 LEN：获取字符串长度 SUBSTRING：获回字符串的部分内容 CONVERT：数据类型转换,数据分组,使用GROUP BY子句可以按一定的条件对查询到的结果进行分组，再对每一组数据计算统计信息。 SELECT 性别,count(*) as 数量 FROM dbo.学生表 GROUP BY 性别,表连接,在数据库应用中，经常需要从多个相关的表中查询数据，这 就需要进行表连接。,表连接,left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 inner join(等值连接) 只返回两个表中联结字段相等的行 最常见的表连接形式： SELECT 选择字段 FROM 表名1 INNER JOIN 表名2 ON 表名1.列名1=表名2.列名2,编写查询语句，显示每个学生的成绩，返回结果如下：,数据初始化脚本,SQL练习,1、查询“学生表”中年龄大于或等于23岁的学生 2、查询“学生表”中姓名包括“朋”的学生 3、查询“学生表”中姓名包含李，且名字长度为两个汉字的学生 4、查询平均成绩大于60分的学生 5、统计各学生的平均成绩，且按成绩从高到低排序,数据初始化脚本,SQL SERVER 数据库基础 创建和管理数据库 创建和管理数据表 增、删、改数据 数据检索 创建和管理视图 T-SQL编程 创建和管理存储过程,内 容,创建和管理视图,视图是基于某个查询结果的虚表。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。数据库中只存储视图的定义，即视图是从某个或某些基本表（或视图）导出的，视图不生成所选数据库行和列的永久拷贝。,创建视图,CREATE VIEW 语句来创建视图，对应的语法如下： CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement ; WITH CHECK OPTION := ENCRYPTION SCHEMABINDING ,参数的含义： schema_name： 视图所属架构的名称 view_name： 视图的名称 Column： 视图中的列使用的名称 AS： 指定视图要执行的操作 select_statement： 定义视图的SELECT语句,使用SQL练习语句创建视图,删除视图,在创建视图后，如果不再需要该视图，或想清除视图定义以及与之相关联的权限，可以删除该视图。删除视图后，表和视图所基于的数据并不受到影响 删除视图对应的T-SQL语句为： DROP VIEW语句，语法为： DROP VIEW ,SQL SERVER 数据库基础 创建和管理数据库 创建和管理数据表 增、删、改数据 数据检索 创建和管理视图 T-SQL编程 创建和管理存储过程,内 容,概述,Transact-SQL语言是微软公司在Microsoft SQL Server系统中使用的语言，是对SQL语言的一种扩展形式 在Microsoft SQL Server 2005系统中，根据Transact-SQL语言的功能特点，可以把Transact-SQL语言分为5种类型，即数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加的语言元素,数据定义语言,数据定义语言用于创建数据库和数据库对象，为数据库操作提供对象。例如，数据库以及表、触发器、存储过程、视图、索引、函数、类型、用户等都是数据库中的对象，都需要通过定义才能使用。在DDL中，主要的Transact-SQL语句包括CREATE语句、ALTER语句、DROP语,数据操纵语言,数据操纵语言主要是用于操纵表、视图中数据的语句。当我们创建表对象之后，初始状态时该表是空的，没有任何数据。如何向表中添加数据呢？这时需要使用INSERT语句。如何检索表中数据呢？可以使用SELECT语句。如果表中数据不正确的，那么可以使用UPDATE语句进行更新。当然，也可以使用DELETE语句删除表中的数据。实际上，DML语言正是包括了INSERT、SELECT、UPDATE、DELETE等语句。,数据控制语言(DCL),数据控制语言(DCL)主要用来执行有关安全管理的操作，该语言主要包括GRANT语句、REVOKE语句和DENY语句。GRANT语句可以将指定的安全对象的权限授予相应的主体，REVOKE语句则删除授予的权限，DENY语句拒绝授予主体权限，并且防止主体通过组或角色成员继承权限。,注释,注释是程序代码中不被执行的文本字符串（也称为备注）。 SQL Server 2005支持以下两种类型的注释字符。 -（双连字符）。这些注释字符可与要执行的代码处在同一行，也可另起一行。从双连字符开始到行尾的内容均为注释。 /* . */（斜杠-星号字符对）。这些注释字符可与要执行的代码处在同一行，也可另起一行，甚至可以在可执行代码内部。开始注释对(/*)与结束注释对(*/)之间的所有内容均视为注释,常量和变量,常量：也称为文字值或标量值，是在程序运行过程中值保持 不变的量，它是表示一个特定数据值的符号。 变量是指在程序运行过程中值可以改变的量。 声明变量的语法如下： DECLARE variable_name datatype 其中， variable_name是局部变量的名字，必须以“”符号开头 Datatype是为该局部变量指定的数据类型。,流控制语句,Transact-SQL提供称为控制流语言的特殊 关键字，用于控制Transact-SQL语句、语句块 和存储过程的执行流。这些关键字可用于临时 Transact-SQL语句、批处理和存储过程中。,BEGINEND,包括一系列的Transact-SQL语句，从而可以执行一组 Transact-SQL语句。 语法如下： BEGIN sql_statement | statement_block END 参数 ： sql_statement | statement_block 使用语句块定义的任何有效的Transact-SQL语句或语句组。,IFELSE,语法： IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block 参数含义： Boolean_expression：返回TRUE或FALSE的表达式。 sql_statement | statement_block ：任何Transact-SQL语句或语句块,WHILE,设置重复执行SQL语句或语句块的条件。 语法如下： WHILE Boolean_expression sql_statement | statement_block 参数含义： Boolean_expression：表达式，返回TRUE或FALSE。 sql_statement | statement_block ：Transact-SQL语句或语句块。,BREAK与CONTINUE,可以使用BREAK和CONTINUE关键字在循环内 部控制WHILE循环中语句的执行。 BREAK用于退出最内层的WHILE循环； CONTINUE用于重新开始下一次WHILE循环，在CONTINUE关键字之后的语句都不会被执行，而是跳