SQL入门教程初学

数据库原理与应用,SQLServer,2,目录,第一部分SQLServer2005的安装与管理器第二部分数据库管理第三部分数据表管理第四部分数据管理第五部分视图与索引第六部分T-SQL编程第七部分事务第八部分游标第九部分存储过程第十部分触发器第十一部分权限以及安全第十二部分数据库设计,第一部分SQLServer2005的安装与管理器,1.数据库的应用2.数据库相关概念3.关系数据库4.SQLServer20055.SQLServer2005的启动,总结,数据库的应用,超市收银员扫描条码，就能调出商品价格，便于快速结账。火车售票员录入出发地和目的就能调出车次、价格及车票剩余数量，利于快速售票。到营业厅输入手机号和时间段就能打印出通话记录单。录入你的游戏账号和密码就能调出玩家的信息。网站发布的新闻、可转载的网络小说、网络视频、博客文章。,都在使用数据库,数据库和应用程序2-1,数据库服务器,响应和提供数据,应用程序,操作和查询,数据库,数据库和应用程序2-2,应用程序作用：响应操作并显示结果、向数据库请求数据要求：美观、操作简单方便,数据库作用：存储数据、检索数据、生成新的数据要求：统一、安全、性能等,数据库优点,存储大量数据，方便检索和访问保持数据信息的一致、完整共享和安全通过组合分析，产生新的有用信息,数据库的相关概念3-1,数据Data描述事物的符号可以有多种形式：数字、文字、图形、图像、声音等数据库存放数据的地方需要长期存放在计算机内的、有组织的、可共享的数据集合物理形式是存储在磁盘上的一个或多个数据文件数据库管理系统是操纵和管理数据库的大型软件可建立、使用和维护数据库它对数据库进行统一管理和控制，保证数据库的安全性和完整性,数据库的相关概念3-2,数据库管理员管理和维护数据库的人数据库系统一般指数据库、数据库管理系统以及运行硬件、应用程序、数据库管理员和用户的集合数据模型指数据库管理系统中数据的存储结构,数据库的相关概念3-3,11,数据库的发展史,初级阶段第一代数据库出现了层次模型、网状模型的数据库中级阶段第二代数据库关系型数据库和结构化查询语言高级阶段新一代数据库“面向对象”型数据库,定义,疑问,使用数据表存储数据!,关系数据库,某公司的员工信息,行(记录),列（字段）,那么实体和属性具体指的是什么呢？,关系数据库数据表,关系数据库实体,关系数据库属性,例如,属性（字段）,不同的员工属于不同的实体,16,SQLServer2005概述2-1,MSSQLServer2005是Microsoft开发的基于关系数据库的管理系统采用“请求-应答”工作方式,SQLServer2005针对不同用户群体提供多个版本。易用性好，受广大用户青睐。,版本企业版(EnterpriseEdition)标准版(StandardEdition)开发板(DeveloperEdition)工作组版(WorkgroupEdition)精简版(ExpressEdition),SQLServer2005概述2-2,SQLServer2005安装要求,最少：1.0GHz奔腾以上处理器。推荐：2.0GHz或速度更快的处理器。,最小：512MB。推荐：2.048GB或更大的内存。,完全安装1.7GB。,硬件要求,软件要求,SQLServer2005的安装,演示整个安装过程,SQLServer2005管理工具,MicrosoftSQLServerManagementStudio,开发人员和DBA最常用的管理工具，使用它可以执行许多SQLServer的核心管理任务。例如：创建数据库、创建表、视图、存储过程、执行SQL语句等。,SQLServerProfiler,SQLServer2005外围应用配置器,能帮助系统管理员监视数据库和服务器的行为,比如死锁的数量，致命的错误，跟踪DELETE、INSERT、UPDATE等Transact-SQL语句和存储过程。便于后期一步一步分析。,提供对SQLServer2005本地实例和远程实例的外围应用的更多控制，提高了可管理性和安全性。,SQLServer配置管理器,通过SQLServer配置管理器是对MicrosoftSQLServer提供的各种服务进行登录身份配置、网络协议配置及服务的停止启动配置；另外还提供一些高级选项。其中登录身份和各种服务停止和启动配置是较为常用.,21,停止和启动SQLServer,在对SQLServer数据库进行任何操作之前，必须启动本地或远程SQLServer服务器使用操作系统服务管理器使用SQLServerConfigurationManager(配置管理)使用SQLServerMangementStudio(管理平台)启动SQLServerMangementStudioWindows身份验证SQLServer身份验证,演示整个启动/停止过程,连接到SQLServer2005,演示连接到服务器,选择服务器,默认本机,录入登录名和密码,单击连接,连接成功后，显示SQLServer企业管理器,23,第一部分总结,掌握技能SQLServer2005的安装SQLServer服务的启动/停止SQLServer管理平台的进入了解的概念两种身份验证数据库相关概念以及相应的英文拼写4种数据模型,层次模型,网状模型,关系模型2-1,27,关系模型2-2,记录和记录之间通过属性之间的关系来进行连接保证数据独立性，并形成数据集之间的关系,伸缩性和扩展性，处理复杂的数据模型和关系模型。利用继承的方法实现数据的重用，避免数据冗余。提高了对大对象（文本、图像、视频）信息的描述、操纵和检索能力。,缺乏像关系型数据模型那样坚实成熟的理论基础。糟糕的运行效率。,优点：,缺点：,面向对象模型,第二部分数据库管理,1.数据库分类2.数据库文件3.数据库的创建、修改（管理平台）4.数据库的分离和附加5.数据库的脱机和联机6.数据库的删除7.数据库的创建、删除（SQL语句）,总结,数据库的分类,系统数据库,用户数据库,数据库,创建数据库-数据库文件的构成,数据库,必备文件,可选文件,一个数据库只能有一个主数据文件,一个数据库可有多个日志文件,一个数据库可有多个次数据文件,32,采用多个数据库文件来存储数据的优点,数据库文件可以不断扩充而不受操作系统文件大小的限制可以将数据库文件存储在不同的硬盘中，这样可以同时对几个硬盘做数据存取提高了数据处理的效率，对于服务器型的计算机尤为有用,创建数据库-Systop数据库,主数据文件,日志文件,次数据文件,可分别存储在不同的磁盘中,演示创建数据库,管理和维护：分离数据库,演示分离数据库,管理和维护：附加数据库,演示附加数据库,管理和维护：脱机和联机,演示设置数据库的脱机和联机状态,管理和维护：删除数据库,演示删除数据库,回顾,数据库发展经历的4种数据模式，分别是什么？一个SQLServer数据库一般可包含哪几种文件？扩展名分别是什么？数据库服务的启动和关闭数据库的链接的身份验证模式分别是什么？数据库的创建、删除数据库的分离、附加、脱机、联机,为什么使用T-SQL操作数据库,管理数据库有两种方法：使用SqlServerManagementstudio管理数据库使用Transact-Sql语言管理数据库将数据库移植到客户的计算机，如果考虑SQLSERVER各种版本的兼容性，最好的办法就是编写比较通用的SQL语句创建数据库。,结构化查询语言SQLStructuredQueryLanguageSQL是RDBMS的标准语言，遵循ANSI(美国国家标准协会)所制定的标准。SQL分类数据查询语言（DQL）：Select*FROMABC数据操作语言（DML）：UpdateABCSETA=“CH”数据控制语言（DCL）：RevokeCreateTableFROMU1数据定义语言（DDL）：CreatedatabaseABC通用命令语言（CCL）：DeclareCSCursorFor,SQL的分类,创建数据库的语法2-1,数据库的数据文件主要参数,可以省略,语法,创建数据库的语法2-2,数据库的日志文件主要参数,创建数据库示例1,数据文件的主要参数,日志文件的主要参数,教员演示,创建数据库示例2,主数据文件,次要数据文件,创建数据库示例2,日志文件,教员演示,创建数据库时的错误提示,如果SqlServer中已经存在myschool数据库，再次运行代码创建该数据库，会出现什么情况呢？,演示：重复创建数据库,系统数据表Sysdatabases,SqlSever2005安装完成后，系统自带master系统数据库，sysdatabases是其中一个系统数据表，存放所有数据库的一些信息。,删除数据库,语法,示例,删除数据库时的错误提示,删除某个数据库前，一定要确保该数据库存在；否则，系统就会提示错误。,演示：删除数据库,完整的删除数据库的SQL语句,检测某个记录是否存在,完整的创建数据库的SQL,注意事项,SQL语句在书写的时候不区分大小写一条语句可以写在多行上但不能将多条语句写在一行上,回顾,SQL的全拼是什么？T-SQL的分类创建数据库的createdatabase语句实训课习题,上机出现问题,格式的书写FileName属性的赋值：包含路径和文件名Size和MaxSize属性的赋值默认单位是MB若不是MB作为单位，赋值时必须给设置对应的单位多个文件的分隔数据库的逻辑文件名不能重复文件组的应用,问题,创建了数据库后，发现需要再添加一个数据库文件，怎样实现？,提问,修改数据库的语法,语法,57,第02章总结,掌握概念数据库包含哪几种文件？应用技能用户数据库的创建、删除数据库与系统的分离与附加数据库与系统的脱机与联机,58,系统数据库,临时数据库，临时数据可被自动删除，不需要特殊权限，系统中仅有一个，空间不足可自动扩展,系统数据库,用户数据库,示例数据库,主控数据库，用于管理其他数据库和保存SQLServer系统信息,模板数据库，当创建一个用户数据库时其内容自动复制到新数据库中,来存储计划信息以及与备份和恢复相关的信息，SQLServer代理使用其安排报警、作业并记录操作员,59,数据库文件组,将多个数据文件存放在不同的磁盘上并把多个数据文件组成一个或多个文件组数据库根据组内数据文件的大小，按比例地写入组内所有数据文件，使多个磁盘同时并行工作、提高读写速度在一个数据库中可以创建多个文件组，而一个数据文件只能属于一个文件组事务日志文件不能属于任何文件组一个数据文件或文件组只被一个数据库使用,第三部分数据表的管理,1.管理平台创建数据表2.数据类型3.数据冗余4.数据完整性5.约束6.语句创建数据表,总结,本章任务,创建BooksManager数据库中Authors和Books数据表,创建Authors和Books数据表,作者表,图书表,本章目标,掌握数据表的创建过程能够创建约束来满足数据完整性的要求掌握主外键的作用以及引用完整性的实施步骤,在SQLServer中建立数据表,图书销售系统一般应用于图书批发行业，是用来存储图书信息、作者信息、库存信息和销售情况的，不同的信息需要不同的数据表来分别存储。请问要建立一个表来存储作者信息，那么这个表应包含哪些列？,提问,作者姓名,年龄,性别,电话,电子邮件,作者表,列名应该有意义，再考虑图书表有哪些列呢？,简要介绍图书销售系统,在SQLServer中建立数据表,图书表结构,那么如何在SQLServer2005的BooksManager数据库中创建表呢？,创建数据库表Authors,演示创创建表的过程.,数据类型,小结,简述创建数据表的过程？真假信息使用什么数据类型？图片信息使用什么数据类型？年龄信息使用什么数据类型？,提问,数据表存在的问题(一)数据冗余,数据冗余,新增类别表(Categories),新增出版商表(Publishers),图书表和作者表设计完成后，小新向图书表中插入了几条图书信息，观察下面数据，能否发现一些问题呢？,作者表(Authors),数据表存在的问题(二)数据完整性,2、网状模型,请看下面作者表中存储的信息，能否发现不合理的数据？,提问,数据表中存在不正确、不准确的数据，数据库“失去了完整性”,数据表存在的问题(二)数据完整性,什么是数据完整性?,数据完整性的问题大多是由于设计引起的，为了避免上述问题的发生我们应重视表的前期设计。,下面列举一些常用的数据完整性要求,数据完整性,数据表中是否存在重复数据？作者表中一个作者只能出现一次。图书表中每本图书只能描述一次。是否符合特定规则的要求。会员信誉等级为90以上，才能成为版主。,数据是否在允许的范围内？性别信息只能是“男”或“女”。数据类型是否正确？生日必须是日期类型。输入的格式是否正确？电子邮件中应包含“”。,数据完整性实体完整性,约束方法：主键约束、标识列、唯一约束,主键约束,完善Authors表，增加主键约束，避免作者重复问题。,演示增加主键约束.,主键约束,设置AuthorID为主键后，再录入重复信息将给予提示。,演示效果.,选择主键的原则,最少性尽量选择单个键作为主键。稳定性尽量选择数值更新少的列作为主键。,标识列,有时在设计表的时候，表中各列都会出现重复信息的可能，不知道用哪个字段作为主键好，怎么办？,疑问,分析,在表中增加标识列。标识列也称自增字段，一般为数字类型，本身没有实际意义，不属于实体属性，只是用来区别不同行信息。,演示增加标识列.,标识列,思考,标识列必须是数字类型吗？标识列只能是数字类型。如果标识种子是2，标识增量是3，那么增加2行数据，如果删除最后那条数据后，再插入信息时，标识列值应该是多少？应该是8.删除数据后，标识列的数值依然继续递增。,数据完整性域完整性,约束方法：限制数据类型、检查约束、外键约束、默认值、非空约束,检查约束,设置检查约束，以避免作者表中年龄数据在不合理的范围。,演示增加检查约束.,非空约束,如果表中某些字段为必填项，可设置非空约束。,演示增加非空约束.,默认约束,演示增加默认约束.,数据完整性引用完整性,李小旭,100,168901,4,高好,1,168901,3,纪晓岚,50,178922,2,李云龙,5,165212,1,和珅,10,169876,1,订购人,订购数量,图书编号,订单编号,约束方法：外键约束,外键约束,演示设置外键.,建立关系图,主表,子表,演示建立关系图.,引用关系规则,不能将主表中关联列不存在的数据，插入到子表中。图书表中不能够出现作者信息表中不存在的作者编号。不能更改主表中的数据，而导致子表中数据的孤立。如果作者表中的作者编号改变了，图书表中作者编号也应随之改变。不能删除主表中的数据，而导致子表中数据的孤立如果删除了作者表中作者信息，那么图书表中对应的作者信息也应随之删除。删除主表前，先删子表先删图书表、再删除作者表。,逐一演示规则,可以设置级联更新,可设置级联删除,数据完整性自定义完整性,约束方法：规则、存储过程、触发器,回顾,管理平台创建数据表的步骤数据完整性指数据库中数据的准确性，从数据表中取得的数据是准确的和可靠的。实体完整性域完整性引用完整性自定义完整性各种约束,表达式,创建数据表的语法2-1,语法,创建数据表的语法2-2,语法,创建数据表的示例,教员演示,系统数据表sysobjects,每个数据库中都有一个系统表sysobjects，存储了本数据库所有的数据表、约束等信息。,删除数据表,如果当前数据库已经存在Authors表，再次创建时会引发错误，应该怎么办？,完整的创建数据表的SQL,回顾SQLSERVER中的约束,添加约束的目的是什么？常用的约束类型有哪几种？,提问,创建表时添加约束,教员演示,现有数据表中添加约束的语法,语法,现有数据表添加约束的示例,删除约束,语法,示例,99,修改表,ALTERTABLE表名ADD列名数据类型(长度)NULL|NOTNULL|ALTERCOLUMN列名数据类型(长度)NULL|NOTNULL|DROPCOLUMN字段名,n|ADDCONSTRAINT约束定义,n|DROPCONSTRAINT约束名,n|NOCHECKCONSTRAINT约束名|CHECKCONSTRAINT约束名,100,使用ADD子句添加列,ALTERTABLE表名ADD列名数据类型(长度)NULL|NOTNULL若向已存在记录的表中添加列，新添加的列可以设置允许为空；若不允许为空，则需指定默认值,101,使用DROPCOLUMN子句删除列,ALTERTABLE表名DROPCOLUMN字段名,在删除列时，必须先删除基于该列的索引和约束后，才能删除该列,102,使用ALTERCOLUMN子句修改列属性,ALTERTABLE表名ALTERCOLUMN列名数据类型(长度)NULL|NOTNULL在默认状态下，列可以被设置为空值，将一个原来允许为空值的列改为不允许为空值时，必须满足列中没有存放空值的记录以及在列上没有创建索引,103,使用NOCHECKCONSTRAINT子句使约束无效,ALTERTABLE表名NOCHECKCONSTRAINT约束名|CHECKCONSTRAINT约束名该子句只对外键约束和检查约束起作用,104,删除表,DROPTABLE表名,ndroptable语句不能删除系统表如果一个表被其他表通过外键约束引用，那么必须先删除设置了外键约束的表，或删除其外键约束,总结,创建数据库或表时一般需要预先检测是否存在该对象，如何检测？数据库从master系统数据库的sysdatabases表中查询表从该数据库的系统表sysobjects表中查询创建约束有哪两种方式？,第3章总结,需要创建一个数据表，你如何创建，需要考虑哪些问题？在数据表中数据重复的现象就是数据冗余，解决数据冗余常用的办法是什么？数据完整性的概念是什么？通过哪些约束来保证数据的实体完整性？,条件运算符,常用的条件运算符,通配运算符,常与LIKE关键字配合使用，表示一个模糊的范围。,经常用于检查约束及数据查询过滤条件,逻辑运算符,逻辑运算符,回顾,请写出Email的检查约束表达式？什么是数据冗余？其解决方法是什么？请说出引用完整性的约束规则？,第四部分数据的管理,1.插入数据2.更新数据3.删除数据4.查询数据,总结,本章任务,使用T-SQL语句对Authors和Books表数据进行增、删、改、查操作。,本章目标,理解T-SQL的及其作用。掌握T-SQL运算符和表达式的使用。掌握使用T-SQL语句实现增、删、改、查操作。,为什么要学习SQL语言,购买火车票时，当你告知售票员出发地和目的地后，售票系统立刻能查询出有哪些车次，及每个车次的剩余票数，而这些信息是存储在数据库中的。请问售票软件是怎样从数据库中取出这些数据的？,数据库服务器,发送的就是SQL指令,T-SQL语言,SQL：专门对数据库操作的指令集SQL语言的分类DML：DataManipulationLanguage数据操纵语言insert、update、deleteDQL：DataQueryLanguage数据查询语言，selectDDL：DataDefinitionLanguage数据定义语言createdatabse、createtable、altertable、droptable等DCL：DataControlLanguage数据控制语言revoke、grantT-SQL语言标准SQL语言CCL：通用命令语言,T-SQL表达式,变量：一般是列名运算符：算术运算符、逻辑运算符、关系运算符、通配符运算符的优先级Not算术运算符条件运算符AndOr,使用Insert插入数据-1,INSERTINTO(列名)VALUES,语法,其中：“”代表可选的；“”代表必需的。如果有多个列名和多个列值需要用逗号隔开。,使用Insert插入数据-2,注意1:值列表的个数必须与列名数目保持一致。,执行错误,使用Insert插入数据-3,注意2:值列表中值的数据类型、精度要与对应的列类型保持一致。,执行错误,使用Insert插入数据-4,注意3:可以不指定列名，但值列表中值的顺序应该与表中字段顺序保持一致。,使用Insert插入数据-5,注意4:不应为标识列字段指定值，因为它是系统控制自动增长的。,执行错误,使用Insert插入数据-6,注意5:值列表中的数据项，应符合对应列的检查约束要求。,执行错误,使用Insert插入数据-7,方法1:可以使用DEFAULT（缺省）关键字来代替插入的数值。,如何使用Insert语句向具有缺省值的列插入数据？,方法2:Insert语句中不指定具有默认值的列名和列值。,小结,假设你自己是一位有名的作者，把你的信息插入到Authors表中？,编码,使用InsertSelect语句插多行数据,INSERTINTO列名SELECTFROM,语法,注意：1、必须事先存在。如果不存在，执行时将会出现错误。2、查询出的字段数目、数据类型、字段顺序，与插入列保持一致。,要将Authors表中所有作者信息保存到AuthorsInfo表中。,示例,使用SelectInto语句插入多行数据,SELECTINTOFROM,语法,注意：不能事先存在，它是在执行该语句时系统自动创建的。,要将Authors表中“作者姓名、年龄、电子邮件、所在城市”数据存储到一个新表中,示例,该语句可以执行两次吗？,使用SelectInto语句插入标识列数据,SELECTIDENTITY(数据类型,标识种子,标识增量)as列名INTOFROM,语法,要将Authors表中“作者姓名、年龄、电子邮件”数据存储到一个新表中，并且要求插入新表时自动生成标识列字段。,示例,使用Union关键字插入多行数据,INSERTINTO列名SELECTUNIONSELECTUNIONSELECT,语法,向Authors表中一次插入多位作者信息。,示例,注意：列名：可以省略，如果省略列名，那么的数据个数、顺序、数据类型必须与表中字段顺序、数据类型保持一致。,使用Union注意的问题,Union语句中不能使用DEFAULT关键字,语法错误,小结,编写代码将Books表中数据保通过一条SQL语句存到newBooks中，newBooks表与Books表结构类似，要求事先存在。,编码,使用T-SQL中Update语句修改数据,UPDATESETWHERE,语法,注意：1、为必选项，用于更新表中某列数据，在SET后面可以出现多个，需用逗号隔开。2、Where关键字是可选的，用来限定条件，如果Update语句不限定条件，表中所有数据行都将被更新。,Update语句的使用,示例,将作者表中所有作者的居住城市都更改成“北京”,将作者表中AuthorID为2的作者年龄更改成36岁,更新语句中还可以使用表达式,使用Update语句注意的问题1,可以更新主键列数据，但应保证更新后的主键列数据不能出现重复信息，否则将更新失败！,图书表中已经存在图书编号为”169876”的图书，违反了主键约束,使用Update语句注意的问题2,可以更新表中外键列数据，但应保证新数据在主键表的中应事先存在,违反了外键约束，因为Categories表中CatagoryID列不存在类别为12的编号信息。,使用T-SQL中Delete语句删除数据,DELETEFROMWHERE,语法,示例,Delete语句注意问题,李小旭,100,168901,4,高好,1,168901,3,纪晓岚,50,178922,2,李云龙,5,165212,1,和珅,10,169876,1,订购人,订购数量,图书编号,订单编号,执行错误！因为图书表中169876被引用,使用TruncateTable语句删除数据,示例,等同于,TruncateTable,语法,注意：1、TruncateTable只删除表中的数据行，不会删除表结构及各种约束。2、TruncateTable不能删除具有引用关系的数据表。,执行效率更高,TruncateTable和Delete的区别,Delete删除的数据可以通过日志文件进行恢复TruncateTable删除的数据不能进行恢复Delete删除时，标识列取值保留原使用中最大值TruncateTable删除时，标识列恢复到最初设置的标识种子值Delete删除数据时是一条记录一条记录删除的TruncateTable删除数据时，是以数据页的形式删除的,本次课总结,Insert语句使用注意要点一次插入多行数据，有哪几种SQL语句。更新语句的使用，以及注意要点？Delete语句作用？以及注意要点？TruncateTable语句作用？以及注意要点？,查询和记录集的概念,数据库服务器,SELECT*FROMTICKET,售票员查询从北京到石家庄的车次。,查询将产生一个虚拟表，这些数据是从数据库中现有表中过滤出来的，类似表的一个结构体，称为“记录集”,数据库中是如何查询的？,下面就来学习SQL中的查询语句,SELECT查询语句,语法,SELECTFROMWHEREORDERBYASC或DESC,示例,演示效果.,基础查询5-1,1、查询数据表中部分列,2、使用“*”查找所有列,3、使用WHERE过滤部分行数据,基础查询5-2,4、使用As子句将列转别名,转别名还可以使用“=”,注意：1、应使用英文半角2、如果是中间不包含空格可以不写引号,5、使用“+”连接多个字段，合并成一列,注意：1、+前后类型应兼容2、如果+连接数值类型，结果是数值之和。3、如果+连接字符串类型，结果是字符串的连接。,基础查询5-3,6、使用Top关键字查询表中限定行数,使用PERCENT按百分比取数据,7、在查询结果中使用常量列,基础查询5-4,9、使用ISNULL查询空数据,如果填写了生日信息，然后又删了，如何查询出来?,8、使用DISTINCT关键字屏蔽重复数据,基础查询5-5,10、UNION联合查询,排序,1、查询所有图书信息并按照价格字段升序显示,2、查询单价在50元以上的图书信息，要求价格以8折显示，并按价格降序排列,排序-按多列排序,3、查询结果还可以按多列进行排序,先按“图书类别”进行主排序，然后在“图书类别”排序的基础上按“单价”列进行次排序,小结,查询电话号码会员信息，要求显示会员名称、居住城市、会员等级。编写代码查询积分在500分以上的会员信息，并按积分降序排列。,提问,模糊查询,查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容。,LIKE进行模糊查询,查询作者表中作者姓名以“张”开头的作者信息,语法,WHERENOTLIKE,通常LIKE与通配符配合使用,示例,IN进行模糊查询,查询居住地是“北京”、“上海”、“西安“、“成都”的作者,语法,WHEREIN,其中：中各常量值用逗号隔开,示例,考虑用以前学的逻辑运算符能够查询出来？,BETWEEN进行模糊查询,查找单价在20到99元的图书信息,语法,WHERENOTBETWEENAnd,注意：和的顺序不能颠倒,示例,Between语句还经常用于查找指定日期范围内的数据,小结,查询电话号码以139开头的会员信息，并按照会员积分升序排列。,提问,常用的SQL内置函数,函数就相当与Java中的方法，每个函数实现了一个特定的功能。,字符串函数,日期函数,数学函数,类型转换函数,字符串函数,数学函数,日期函数,转换函数,小结练习,在BooksManager数据库中，有会员表(Customers),现在要查询注册时间大于5年并且本月过生日的会员，要求显示会员姓名、居住城市、出生日期、联系电话和会员年龄。,分析,在要解决这个问题需要使用查询语句SELECT。要求注册时间大于5年，WHERE条件中需要用到日期函数YEAR。并且本月过生日的会员，需要用到日期函数中的MONTH函数。查询结果中显示会员年龄，当前年份-出生日期。,YEAR(GETDATE()YEAR(RegDate)5,MONTH(GETDATE()=MONTH(Birthday),YEAR(GETDATE()YEAR(Birthday),案例分析1要求,某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；请编写SQL语句实现以上要求；数据库表名：Card密码字段名：PassWord,案例分析1分析,这是更新语句，需要使用UPDATE语句牵涉到字符串的替换，需要使用到SQLServer中的函数Replace,案例分析1T-SQL,两行SQL语句,UPDATECardSETPassWord=Replace(PassWord,O,0)UPDATECardSETPassWord=Replace(PassWord,i,1),一行SQL语句,UPDATECardSETPassWord=Replace(Replace(PassWord,O,0),i,1),案例分析2要求,在数据库表中有以下字符数据，如：13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样：13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2数据库表名：SellRecord字段名：ListNumber,案例分析2分析,查询语句：使用SELECT语句排序：ORDERBY在ORDERBY的排序列中，需要重新计算出排序的数字前半部分的数字：找到“-”符号的位置取其左半部分使用Convert函数将其转换为数字：Convert(int,Left(ListNumber,CharIndex(-,ListNumber)-1)后半部分的数字：找到“-”符号的位置把从第一个位置到该位置的全部字符替换为空格使用Convert函数将其转换为数字：Convert(int,Stuff(ListNumber,1,Charindex(-,ListNumber),),案例分析2T-SQL,SELECTListNumberFROMSellRecordORDERBYConvert(int,Left(ListNumber,CharIndex(-,ListNumber)-1),Convert(int,Stuff(ListNumber,1,Charindex(-,ListNumber),),本次总结,什么是记录集？SELECT语句中的as作用是什么？请说出2个日期函数及2个字符串函数的作用？,回顾,编写SQL语句查询一个月前的订单信息。查询作者表中姓名以“李”开头的作者信息。查询前20%的图书信息。,本节目标,掌握聚合函数的使用。掌握GroupBy进行分组统计。,聚合函数,会员表中存储了所有会员的积分，如果想计算出会员的最高分、平均积分、积分大于300的会员个数，怎么办？,会员表Customers,聚合函数-SUM、AVG、MAX、MIN,1、SUM,2、AVG,3、MAX和MIN,聚合函数-COUNT,4、COUNT(表达式)返回结果集的非空行数。,MAX和MIN除了计算数值列，还可以用于计算字符型以及日期时间类型数据列,其中“表达式”可以是“*”，“列名”。（1）COUNT(*)：返回表中所有数据行的记录数。（2）COUNT(列名)：返回指定列非空值个数。,示例,聚合函数,如果想统计居住在不同城市的人员个数，编写下面代码正确吗？,执行出现错误,解决这样的问题需要使用GROUPBY分组统计,什么是分组统计,一家水果店的一天销售情况，要统计各类水果的销售额。,示例,按水果的种类分组,然后分别汇总,分组统计,分组统计牢记：SELECT语句中除聚合函数外，所有列应写在GroupBy语句后面,否则将出现错误。,语法,SELECT聚合函数FROM表WHERE条件GROUPBY,其中可以有多个字段，各字段用逗号隔开，它表示按哪些列进行分组。,分组统计应用,统计会员表中居住在不同城市的人员个数？,提问,分析,应该按照City列进行分组分组后使用Count()聚合函数汇总行数。,统计结果,多列分组统计,统计不同城市不同性别的会员个数？,提问,分析,统计结果集应包含“城市、性别和人数3列信息”。很明显应该按照City和Sex两列进行分组。分组后仍使用Count()聚合函数汇总行数。,统计结果,多列分组统计,如果代码写成这样，结果会怎样？,所以牢记：SELECT语句中除聚合函数外，所有列应写在GroupBy语句后面。,执行错误,增加Sex才正确.,Sex,使用HAVING子句对分组结果再次过滤,继续考虑上面的查询，如果“要统计不同城市不同性别的会员个数，并且只显示会员个数大于2个的记录”如何处理呢？,需要对分组后的数据进行筛选，筛选条件Count(*)=2。对分组后的数据进行筛选需要使用Having子句。,统计结果：,分组查询对比,WHERE子句：从数据源中去掉不符合其搜索条件的数据GROUPBY子句：搜集数据行到各个组中，统计函数为各个组计算统计值HAVING子句：去掉不符合其组搜索条件的各组数据行,问题,成绩表中存储了所有学员的成绩，想知道：学员的总成绩、平均成绩?怎么办？,问题,如果不是统计所有人所有课程的总成绩而是想求每一门课的平均绩或者某个人的所有课的总成绩怎么办？,分组汇总,第一门课6个成绩取平均值,第二门课6个成绩取平均值,第三门课5个成绩取平均值,分组查询GROUPBY,SELECTCourseID,AVG(Score)AS课程平均成绩FROMScoreGROUPBYCourseID,分组查询思考,SELECTStudentID,CourseID,AVG(Score)AS课程平均成绩FROMScoreGROUPBYCourseID,思考：执行以下的T-SQL：,结果如何？,分组查询多列分组,怎么样来统计每次的内部测试不同学员的成绩？,分组查询再看看,StudentID和CourseID的组合存在重复,需要按照这两个来进行分组，避免StudentID和CourseID同时一样,分组查询多列分组,SELECTStudentIDAS学员编号,CourseIDAS课程编号,AVG(Score)AS内部测试平均成绩FROMScoreGROUPBYStudentID,CourseID,分组查询问题,在以上统计内部测试成绩的基础上，如果只想看补考的学员的成绩，怎么办,分组查询再看看,增加条件：要求该学员的CourseID在分组内出现过一次以上,SELECTStudentIDAS学员编号,CourseIDAS内部测试,AVG(Score)AS内部测试平均成绩FROMScoreGROUPBYStudentID,CourseID,分组查询HAVING,SELECTStudentIDAS学员编号,CourseIDAS内部测试,AVG(Score)AS内部测试平均成绩FROMScoreGROUPBYStudentID,CourseIDHAVINGCOUNT(Score)1,分组查询思考,SELECT部门编号,COUNT(*)FROM员工信息表WHERE工资=2000GROUPBY部门编号HAVINGCOUNT(*)1,思考：分析以下T-SQL的含义,多表关联查询,为什么要用多表关联查询？,这是从图书表中查询出的信息。如果时间长了你还能记得作者编号1、4、11、12都代表是哪个作者吗？,实际上最好显示作者姓名，而作者姓名是存储在作者表中，如何同时从这两个表中取得数据呢？,多表关联查询,内联接(INNERJOIN)外联接左外联接(LEFTJOIN)右外联接(RIGHTJOIN)完整外联接(FULLJOIN)交叉联接(CROSSJOIN),Authors,内连接,Books,内连接查询出的是两个表公共列共有的记录。,内连接,语法,SELECTFROM表1INNERJOIN表2ON表1.列名条件运算符表2.列名WHERE条件ORDERBY排序列,其中“ON表1.列名条件运算符表2.列名”中，条件运算符常用的是：=、。表1.列名和表2.列名,分别是两个表的公共列。,示例,Books与Authors内连接SQL语句,内连接另外一种写法,SQLServer2005还兼容以前版本的内连接语法，即使用WHERE子句进行内连接。,多表内连接,语法,SELECTFROM表1INNERJOIN表2ON表1.列名条件运算符表2.列名INNERJOIN表3ON表1.列名条件运算符表3.列名WHERE条件ORDERBY排序列,示例,左外连接,语法,SELECTFROM左表LEFTOUTERJOIN右表ON左表.列名条件运算符右表.列名WHERE条件ORDERBY排序列,示例,使用左外连接查询Books与Authors表中的数据,右表Authors(从),左外连接,左表：Books(主),左外连接是以左表为主表，去关联右表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。,左外连接,如果把上面的语句改如下代码，查询结果一样吗？为什么？,右外连接,语法,SELECTFROM左表RIGHTOUTERJOIN右表ON左表.列名条件运算符右表.列名WHERE条件ORDERBY排序列,示例,使用右外连接查询Books与Authors表中的数据,右表Authors(主),右外连接,左表：Books(从),右外连接是以右表为主表，去关联左表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。,完全连接,语法,SELECTFROM左表FULLOUTERJOIN右表ON左表.列名条件运算符右表.列名WHERE条件ORDERBY排序列,示例,使用完全连接查询Books与Authors表中的数据,完全连接左表和右表中所有行，当某行数据在另一个表中没有匹配时，则另一个表的选择列值为NULL,案例分析,在查找作者表中哪些作者还没有出过书，并将这些作者信息保存到新表TempAuthors中。,分析,先查找内容，然后插入到新表中，可以使用SELECT.INTO.语句。关键是能查询出哪些作者还没有出过书，这涉及到2个表，一个是作者表，另一个是图书表。查询出作者表中作者编号在图书表中没有出现过的，以作者表为主表、图书表为从表，使用左外连接查询。,最后将查询出的信息，插入到TempAuthors表中。,为什么使用子查询,用所学知识点，找出比喜来乐积分高的客户,数据表：Customers,问题,实现方法,什么是子查询,SELECTFROM表1WHERE字段1比较运算符（子查询）或者:UPDATE表1SETWHERE字段1比较运算符（子查询）,语法,select,(,),简单子查询,查询“喜来乐”的所有订单信息Select*fromordersWherecustomerid=(selectcustomeridfromcustomerswherecustomername=喜来乐),简单子查询,假如某个客户“喜来乐”信誉不好，要删除该客户信息，因表关联，需要先删除该客户的订单信息，此时怎样操作？,当子查询的结果多于一个时，就不能使用比较运算符了，要使用IN关键字。,简单子查询,查询没有订单的客户信息检查每个客户今年的订单总额如果超过100元，则积分增加100,子查询,从customers表中检索数据，以查询一次订货金额大于等于100的客户编号和姓名SELECTa.customerid,a.customernameFROMcustomersaWHERE100=ALL(SELECTunitpriceFROMbooks),218,子查询,如果一个SELECT语句能够返回一个单值或一列值并嵌套在一个SELECT、INSERT、UPDATE或DELETE语句中，则称之为子查询或内层查询，而包含一个子查询的语句则称为主查询或外层查询子查询可实现多层嵌套，且要用圆括号括起来常把子查询用在外层查询的WHERE子句或HAVING子句中，与比较或逻辑运算符一起构成查询条件子查询又分为嵌套子查询和相关子查询,219,嵌套子查询和相关子查询,嵌套子查询总是最先执行最内层查询相关子查询的执行过程依赖于值的外部查询。相关子查询的WHERE子句引用外部查询的FROM子句中的表。这就是说子查询是以循环的方式进行处理的对外部查询选择的每行都执行一次,220,使用子查询进行比较测试,通过比较运算符（，=，!=，!，!upoint的记录。,数据表：Customers找出比喜来乐积分高的所有客户信息,参考代码,教员演示,代码改错,代码改错,SELECT赋值语句不能和SELECT语句同时使用,全局变量,全局变量不需要用户声明，是服务器级定义的，作用范围是任何程序。全局变量以开头，局部变量名称不能和全局变量相同。,输出语句,输出语句有两种：SELECT查询语句PRINT语句,PRINT表达式,语法,PRINT一般用于观察T-SQL程序运行时的中间结果。,PRINT输出语句,课堂练习,查询比“贺敏”年龄大1岁和小1岁的学员信息,控制语句,T-SQL中的控制语句和其他编程语言类似，主要有：,语句块,提问,C#、JAVA中的复合语句是用什么表示的？,语法,BEGINEND,语句块,条件语句,IFELSE条件表达式,语法,条件语句,统计并显示C#编程的平均成绩：如果在70分以上，则显示“成绩优秀”，并显示前3名的学生的考试信息。如果在70分以下，则显示“成绩较差”，并显示后3名的考试信息。,课程表：score,条件语句,定义变量，存放“C#编程”的课程编号定义变量，存入平均成绩使用条件语句进行判断。,分析,教员演示,循环语句,WHILEBEGINBREAKCONTINUE命令行或程序块END,语法,循环语句,本次的数据结构考试成