SQL入门教程初学ppt课件.ppt

数据库原理与应用,SQL Server,2,目录,第一部分 SQL Server 2005的安装与管理器第二部分 数据库管理第三部分 数据表管理第四部分 数据管理第五部分 视图与索引第六部分 T-SQL编程第七部分 事务第八部分 游标第九部分 存储过程第十部分 触发器第十一部分 权限以及安全第十二部分 数据库设计,第一部分SQL Server 2005的安装与管理器,1. 数据库的应用2. 数据库相关概念3. 关系数据库4. SQL Server 20055. SQL Server 2005的启动,总结,数据库的应用,超市收银员扫描条码，就能调出商品价格，便于快速结账。火车售票员录入出发地和目的就能调出车次、价格及车票剩余数量，利于快速售票。到营业厅输入手机号和时间段就能打印出通话记录单 。录入你的游戏账号和密码就能调出玩家的信息 。网站发布的新闻、可转载的网络小说、网络视频、博客文章。,都在使用数据库,数据库和应用程序 2-1,数据库服务器,响应和提供数据,应用程序,操作和查询,数 据 库,数据库和应用程序 2-2,应用程序作用：响应操作并显示结果、向数据库请求数据要求：美观、操作简单方便,数据库作用：存储数据、检索数据、生成新的数据要求：统一、安全、性能等,数据库优点,存储大量数据，方便检索和访问保持数据信息的一致、完整共享和安全通过组合分析，产生新的有用信息,数据库的相关概念3-1,数据Data描述事物的符号可以有多种形式：数字、文字、图形、图像、声音等数据库存放数据的地方需要长期存放在计算机内的、有组织的、可共享的数据集合物理形式是存储在磁盘上的一个或多个数据文件数据库管理系统是操纵和管理数据库的大型软件可建立、使用和维护数据库它对数据库进行统一管理和控制，保证数据库的安全性和完整性,数据库的相关概念3-2,数据库管理员管理和维护数据库的人数据库系统一般指数据库、数据库管理系统以及运行硬件、应用程序、数据库管理员和用户的集合数据模型指数据库管理系统中数据的存储结构,数据库的相关概念3-3,11,数据库的发展史,初级阶段第一代数据库出现了层次模型、网状模型的数据库中级阶段第二代数据库关系型数据库和结构化查询语言高级阶段新一代数据库“面向对象”型数据库,定义,疑问,使用数据表存储数据!,关系数据库,某公司的员工信息,行(记录),列（字段）,那么实体和属性具体指的是什么呢？,关系数据库数据表,关系数据库实体,关系数据库属性,例如,属性（字段）,不同的员工属于不同的实体,16,SQL Server 2005概述2-1,MS SQL Server 2005是Microsoft开发的基于关系数据库的管理系统采用“请求-应答”工作方式,SQL Server 2005针对不同用户群体提供多个版本。易用性好，受广大用户青睐。,版本企业版(Enterprise Edition) 标准版(Standard Edition) 开发板(Developer Edition) 工作组版(Workgroup Edition) 精简版(Express Edition),SQL Server 2005概述2-2,SQL Server2005安装要求,最少：1.0GHz 奔腾以上处理器。推荐：2.0GHz或速度更快的处理器 。,最小：512MB。推荐：2.048GB或更大的内存 。,完全安装1.7GB。,硬件要求,软件要求,SQL Server 2005的安装,演示整个安装过程,SQL Server2005管理工具,Microsoft SQL Server Management Studio,开发人员和DBA最常用的管理工具，使用它可以执行许多SQL Server的核心管理任务。例如：创建数据库、创建表、视图、存储过程、执行SQL语句等。,SQL Server Profiler,SQL Server2005外围应用配置器,能帮助系统管理员监视数据库和服务器的行为, 比如死锁的数量，致命的错误，跟踪DELETE、 INSERT、 UPDATE 等Transact-SQL 语句和存储过程 。 便于后期一步一步分析。,提供对SQL Server2005本地实例和远程实例的外围应用的更多控制，提高了可管理性和安全性。,SQL Server 配置管理器,通过SQL Server配置管理器是对Microsoft SQL Server 提供的各种服务进行登录身份配置、网络协议配置及服务的停止启动配置；另外还提供一些高级选项。其中登录身份和各种服务停止和启动配置是较为常用.,21,停止和启动SQL Server,在对SQL Server数据库进行任何操作之前，必须启动本地或远程SQL Server服务器使用操作系统服务管理器使用SQL Server Configuration Manager(配置管理)使用SQL Server Mangement Studio(管理平台)启动SQL Server Mangement StudioWindows身份验证SQL Server身份验证,演示整个启动/停止过程,连接到SQL Server2005,演示连接到服务器,选择服务器,默认本机,录入登录名和密码,单击连接,连接成功后，显示SQL Server企业管理器,23,第一部分总结,掌握技能SQL Server 2005的安装SQL Server服务的启动/停止SQL Server管理平台的进入了解的概念两种身份验证数据库相关概念以及相应的英文拼写4种数据模型,层次模型,网状模型,关系模型2-1,27,关系模型2-2,记录和记录之间通过属性之间的关系来进行连接保证数据独立性，并形成数据集之间的关系,伸缩性和扩展性，处理复杂的数据模型和关系模型。利用继承的方法实现数据的重用，避免数据冗余。提高了对大对象（文本、图像、视频）信息的描述、操纵和检索能力。,缺乏像关系型数据模型那样坚实成熟的理论基础。糟糕的运行效率。,优点：,缺点：,面向对象模型,第二部分数据库管理,1. 数据库分类2. 数据库文件3. 数据库的创建、修改（管理平台）4. 数据库的分离和附加5. 数据库的脱机和联机6. 数据库的删除7. 数据库的创建、删除（SQL语句）,总结,数据库的分类,系统数据库,用户数据库,数据库,创建数据库-数据库文件的构成,数 据 库,必备文件,可选文件,一个数据库只能有一个主数据文件,一个数据库可有多个日志文件,一个数据库可有多个次数据文件,32,采用多个数据库文件来存储数据的优点,数据库文件可以不断扩充而不受操作系统文件大小的限制可以将数据库文件存储在不同的硬盘中，这样可以同时对几个硬盘做数据存取提高了数据处理的效率，对于服务器型的计算机尤为有用,创建数据库-Systop数据库,主数据文件,日志文件,次数据文件,可分别存储在不同的磁盘中,演示创建数据库,管理和维护：分离数据库,演示分离数据库,管理和维护：附加数据库,演示附加数据库,管理和维护：脱机和联机,演示设置数据库的脱机和联机状态,管理和维护：删除数据库,演示删除数据库,回顾,数据库发展经历的4种数据模式，分别是什么？一个SQL Server数据库一般可包含哪几种文件？扩展名分别是什么？数据库服务的启动和关闭数据库的链接的身份验证模式分别是什么？数据库的创建、删除数据库的分离、附加、脱机、联机,为什么使用T-SQL操作数据库,管理数据库有两种方法：使用Sql Server Management studio管理数据库 使用Transact-Sql语言管理数据库 将数据库移植到客户的计算机，如果考虑SQL SERVER各种版本的兼容性，最好的办法就是编写比较通用的SQL语句创建数据库。,结构化查询语言 SQL Structured Query Language SQL是RDBMS的标准语言，遵循ANSI(美国国家标准协会)所制定的标准。SQL分类数据查询语言（DQL）：Select * FROM ABC数据操作语言（DML）：Update ABC SET A = “CH”数据控制语言（DCL）：Revoke Create Table FROM U1数据定义语言（DDL）：Create database ABC通用命令语言（CCL）：Declare CS Cursor For ,SQL 的分类,创建数据库的语法2-1,数据库的数据文件主要参数,可以省略,语法,创建数据库的语法2-2,数据库的日志文件主要参数,创建数据库示例1,数据文件的主要参数,日志文件的主要参数,教员演示,创建数据库示例2,主数据文件,次要数据文件,创建数据库示例2,日志文件,教员演示,创建数据库时的错误提示,如果Sql Server中已经存在myschool数据库，再次运行代码创建该数据库，会出现什么情况呢？,演示：重复创建数据库,系统数据表Sysdatabases,Sql Sever2005安装完成后，系统自带master系统数据库，sysdatabases 是其中一个系统数据表，存放所有数据库的一些信息。,删除数据库,语法,示例,删除数据库时的错误提示,删除某个数据库前，一定要确保该数据库存在；否则，系统就会提示错误。,演示：删除数据库,完整的删除数据库的SQL语句,检测某个记录是否存在,完整的创建数据库的SQL,注意事项,SQL语句在书写的时候不区分大小写一条语句可以写在多行上但不能将多条语句写在一行上,回顾,SQL的全拼是什么？T-SQL的分类创建数据库的create database语句实训课习题,上机出现问题,格式的书写FileName属性的赋值：包含路径和文件名Size和MaxSize属性的赋值默认单位是MB若不是MB作为单位，赋值时必须给设置对应的单位多个文件的分隔数据库的逻辑文件名不能重复文件组的应用,问题,创建了数据库后，发现需要再添加一个数据库文件，怎样实现？,提问,修改数据库的语法,语法,57,第02章总结,掌握概念数据库包含哪几种文件？应用技能用户数据库的创建、删除数据库与系统的分离与附加数据库与系统的脱机与联机,58,系统数据库,临时数据库，临时数据可被自动删除，不需要特殊权限，系统中仅有一个，空间不足可自动扩展,系统数据库,用户数据库,示例数据库,主控数据库，用于管理其他数据库和保存SQL Server系统信息,模板数据库，当创建一个用户数据库时其内容自动复制到新数据库中,来存储计划信息以及与备份和恢复相关的信息，SQL Server代理使用其安排报警、作业并记录操作员,59,数据库文件组,将多个数据文件存放在不同的磁盘上并把多个数据文件组成一个或多个文件组数据库根据组内数据文件的大小，按比例地写入组内所有数据文件，使多个磁盘同时并行工作、提高读写速度在一个数据库中可以创建多个文件组，而一个数据文件只能属于一个文件组事务日志文件不能属于任何文件组一个数据文件或文件组只被一个数据库使用,第三部分数据表的管理,1. 管理平台创建数据表2. 数据类型3. 数据冗余4. 数据完整性5. 约束6. 语句创建数据表,总结,本章任务,创建BooksManager数据库中Authors和Books数据表,创建Authors和Books数据表,作者表,图书表,本章目标,掌握数据表的创建过程能够创建约束来满足数据完整性的要求掌握主外键的作用以及引用完整性的实施步骤,在SQL Server中建立数据表,图书销售系统一般应用于图书批发行业，是用来存储图书信息、作者信息、库存信息和销售情况的，不同的信息需要不同的数据表来分别存储。请问要建立一个表来存储作者信息，那么这个表应包含哪些列？,提问,作者姓名,年龄,性别,电话,电子邮件,作者表,列名应该有意义，再考虑图书表有哪些列呢？,简要介绍图书销售系统,在SQL Server中建立数据表,图书表结构,那么如何在SQL Server2005的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,回顾SQL SERVER中的约束,添加约束的目的是什么？常用的约束类型有哪几种？,提问,创建表时添加约束,教员演示,现有数据表中添加约束的语法,语法,现有数据表添加约束的示例,删除约束,语法,示例,99,修改表,ALTER TABLE 表名 ADD 列名 数据类型(长度)NULL|NOT NULL| ALTER COLUMN 列名 数据类型(长度)NULL|NOT NULL| DROP COLUMN 字段名 , n| ADD CONSTRAINT 约束定义 , n| DROP CONSTRAINT 约束名 , n| NOCHECK CONSTRAINT 约束名 | CHECK CONSTRAINT 约束名,100,使用ADD子句添加列,ALTER TABLE 表名ADD 列名 数据类型(长度) NULL | NOT NULL若向已存在记录的表中添加列，新添加的列可以设置允许为空；若不允许为空，则需指定默认值,101,使用DROP COLUMN子句删除列,ALTER TABLE 表名DROP COLUMN 字段名 , 在删除列时，必须先删除基于该列的索引和约束后，才能删除该列,102,使用ALTER COLUMN子句修改列属性,ALTER TABLE 表名ALTER COLUMN 列名 数据类型(长度) NULL | NOT NULL在默认状态下，列可以被设置为空值，将一个原来允许为空值的列改为不允许为空值时，必须满足列中没有存放空值的记录以及在列上没有创建索引,103,使用NOCHECK CONSTRAINT子句使约束无效,ALTER TABLE 表名NOCHECK CONSTRAINT 约束名 |CHECK CONSTRAINT约束名该子句只对外键约束和检查约束起作用,104,删除表,DROP TABLE 表名 , ndrop table 语句不能删除系统表如果一个表被其他表通过外键约束引用，那么必须先删除设置了外键约束的表，或删除其外键约束,总结,创建数据库或表时一般需要预先检测是否存在该对象，如何检测？数据库从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：Data Manipulation Language数据操纵语言 insert、update、delete DQL：Data Query Language数据查询语言，selectDDL：Data Definition Language数据定义语言 create databse、create table、alter table、drop table等DCL：Data Control Language数据控制语言 revoke、grantT-SQL语言标准SQL语言CCL：通用命令语言,T-SQL表达式,变量：一般是列名运算符：算术运算符、逻辑运算符、关系运算符、通配符运算符的优先级Not 算术运算符 条件运算符 And Or,使用Insert插入数据-1,INSERT INTO (列名) VALUES ,语法,其中：“”代表可选的；“”代表必需的。如果有多个列名和多个列值需要用逗号隔开。,使用Insert插入数据-2,注意1:值列表的个数必须与列名数目保持一致。,执行错误,使用Insert插入数据-3,注意2:值列表中值的数据类型、精度要与对应的列类型保持一致。,执行错误,使用Insert插入数据-4,注意3:可以不指定列名，但值列表中值的顺序应该与表中字段顺序保持一致 。,使用Insert插入数据-5,注意4:不应为标识列字段指定值，因为它是系统控制自动增长的 。,执行错误,使用Insert插入数据-6,注意5:值列表中的数据项，应符合对应列的检查约束要求 。,执行错误,使用Insert插入数据-7,方法1:可以使用DEFAULT（缺省）关键字来代替插入的数值。,如何使用Insert语句向具有缺省值的列插入数据？,方法2:Insert语句中不指定具有默认值的列名和列值 。,小结,假设你自己是一位有名的作者，把你的信息插入到Authors表中？,编码,使用Insert Select语句插多行数据,INSERT INTO 列名 SELECT FROM ,语法,注意：1、必须事先存在。如果不存在，执行时将会出现错误。 2、查询出的字段数目、数据类型、字段顺序，与插入列保持一致。,要将Authors表中所有作者信息保存到AuthorsInfo表中。,示例,使用Select Into语句插入多行数据,SELECT INTO FROM ,语法,注意： 不能事先存在，它是在执行该语句时系统自动创建的。,要将Authors表中“作者姓名、年龄、电子邮件、所在城市”数据存储到一个新表中,示例,该语句可以执行两次吗？,使用Select Into语句插入标识列数据,SELECT IDENTITY(数据类型, 标识种子, 标识增量) as 列名 INTO FROM ,语法,要将Authors表中“作者姓名、年龄、电子邮件”数据存储到一个新表中，并且要求插入新表时自动生成标识列字段。,示例,使用Union关键字插入多行数据,INSERT INTO 列名 SELECT UNIONSELECT UNION SELECT ,语法,向Authors表中一次插入多位作者信息 。,示例,注意：列名：可以省略，如果省略列名，那么的数据个数、顺序、数据类型必须与表中字段顺序、数据类型保持一致。,使用Union注意的问题,Union语句中不能使用DEFAULT关键字,语法错误,小结,编写代码将Books表中数据保通过一条SQL语句存到newBooks中，newBooks表与Books表结构类似，要求事先存在。,编码,使用T-SQL中Update语句修改数据,UPDATE SET WHERE ,语法,注意：1、为必选项，用于更新表中某列数据，在SET后面可以出现多 个，需用逗号隔开。2、Where关键字是可选的，用来限定条件，如果Update语句不限定条件，表中所有数据行都将被更新。,Update语句的使用,示例,将作者表中所有作者的居住城市都更改成“北京”,将作者表中AuthorID为2的作者年龄更改成36岁,更新语句中还可以使用表达式,使用Update语句注意的问题1,可以更新主键列数据，但应保证更新后的主键列数据不能出现重复信息，否则将更新失败！,图书表中已经存在图书编号为”169876”的图书，违反了主键约束,使用Update语句注意的问题2,可以更新表中外键列数据，但应保证新数据在主键表的中应事先存在,违反了外键约束 ，因为Categories表中CatagoryID列不存在类别为12的编号信息。,使用T-SQL中Delete语句删除数据,DELETE FROM WHERE ,语法,示例,Delete语句注意问题,李小旭,100,168901,4,高好,1,168901,3,纪晓岚,50,178922,2,李云龙,5,165212,1,和珅,10,169876,1,订购人,订购数量,图书编号,订单编号,执行错误！因为图书表中169876被引用,使用Truncate Table语句删除数据,示例,等同于,Truncate Table ,语法,注意：1、Truncate Table只删除表中的数据行，不会删除表结构及各种约束。2、Truncate Table不能删除具有引用关系的数据表。,执行效率更高,Truncate Table和Delete的区别,Delete删除的数据可以通过日志文件进行恢复Truncate Table删除的数据不能进行恢复Delete删除时，标识列取值保留原使用中最大值Truncate Table删除时，标识列恢复到最初设置的标识种子值Delete删除数据时是一条记录一条记录删除的Truncate Table删除数据时，是以数据页的形式删除的,本次课总结,Insert语句使用注意要点一次插入多行数据，有哪几种SQL语句。更新语句的使用，以及注意要点？Delete语句作用？以及注意要点？Truncate Table 语句作用？以及注意要点？,查询和记录集的概念,数据库服务器,SELECT * FROM TICKET,售票员查询从北京到石家庄的车次。,查询将产生一个虚拟表，这些数据是从数据库中现有表中过滤出来的，类似表的一个结构体，称为“记录集”,数据库中是如何查询的？,下面就来学习SQL中的查询语句,SELECT查询语句,语法,SELECT FROM WHERE ORDER BY ASC或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、使用IS NULL查询空数据,如果填写了生日信息，然后又删了，如何查询出来?,8、使用DISTINCT关键字屏蔽重复数据,基础查询5-5,10、UNION联合查询,排序,1、查询所有图书信息并按照价格字段升序显示,2、查询单价在50元以上的图书信息，要求价格以8折显示，并按价格降序排列,排序-按多列排序,3、查询结果还可以按多列进行排序,先按 “图书类别”进行主排序，然后在“图书类别”排序的基础上按“单价”列进行次排序,小结,查询电话号码是13920298734的会员信息，要求显示会员名称、居住城市、会员等级。编写代码查询积分在500分以上的会员信息，并按积分降序排列。,提问,模糊查询,查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容。,LIKE进行模糊查询,查询作者表中作者姓名以“张”开头的作者信息,语法,WHERE NOT LIKE ,通常LIKE与通配符配合使用,示例,IN进行模糊查询,查询居住地是“北京”、“上海”、“西安“、“成都”的作者,语法,WHERE IN ,其中：中各常量值用逗号隔开,示例,考虑用以前学的逻辑运算符能够查询出来？,BETWEEN进行模糊查询,查找单价在20到99元的图书信息,语法,WHERE NOT BETWEEN And ,注意：和的顺序不能颠倒,示例,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语句牵涉到字符串的替换，需要使用到SQL Server中的函数Replace,案例分析1T-SQL,两行SQL语句,UPDATE Card SET PassWord = Replace(PassWord ,O,0)UPDATE Card SET PassWord = Replace(PassWord ,i,1),一行SQL语句,UPDATE Card SET PassWord = 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语句排序：ORDER BY在ORDER BY的排序列中，需要重新计算出排序的数字前半部分的数字：找到“-”符号的位置取其左半部分使用Convert函数将其转换为数字：Convert(int, Left(ListNumber, CharIndex(-, ListNumber)-1)后半部分的数字：找到“-”符号的位置把从第一个位置到该位置的全部字符替换为空格使用Convert函数将其转换为数字：Convert(int, Stuff(ListNumber,1, Charindex(-, ListNumber), ),案例分析2T-SQL,SELECT ListNumberFROM SellRecordORDER BY Convert(int, Left(ListNumber, CharIndex(-, ListNumber)-1),Convert(int, Stuff(ListNumber,1, Charindex(-, ListNumber), ),本次总结,什么是记录集？SELECT语句中的as作用是什么 ？请说出2个日期函数及2个字符串函数的作用？,回顾,编写SQL语句查询一个月前的订单信息。查询作者表中姓名以“李”开头的作者信息。查询前20%的图书信息。,本节目标,掌握聚合函数的使用。掌握Group By进行分组统计。,聚合函数,会员表中存储了所有会员的积分，如果想计算出会员的最高分、平均积分、积分大于300的会员个数，怎么办？,会员表Customers,聚合函数-SUM、AVG、MAX、MIN,1、SUM,2、AVG,3、MAX和MIN,聚合函数-COUNT,4、COUNT(表达式) 返回结果集的非空行数。,MAX和MIN除了计算数值列，还可以用于计算字符型以及日期时间类型数据列,其中“表达式”可以是“*”，“列名”。 （1）COUNT(*)：返回表中所有数据行的记录数。 （2）COUNT(列名)：返回指定列非空值个数。,示例,聚合函数,如果想统计居住在不同城市的人员个数，编写下面代码正确吗？,执行出现错误,解决这样的问题需要使用GROUP BY分组统计,什么是分组统计,一家水果店的一天销售情况，要统计各类水果的销售额 。,示例,按水果的种类分组,然后分别汇总,分组统计,分组统计牢记： SELECT语句中除聚合函数外，所有列应写在Group By语句后面, 否则将出现错误。,语法,SELECT 聚合函数 FROM 表 WHERE 条件 GROUP BY ,其中可以有多个字段，各字段用逗号隔开，它表示按哪些列进行分组。,分组统计应用,统计会员表中居住在不同城市的人员个数？,提问,分析,应该按照City列进行分组分组后使用Count()聚合函数汇总行数。,统计结果,多列分组统计,统计不同城市不同性别的会员个数 ？,提问,分析,统计结果集应包含“城市、性别和人数3列信息”。很明显应该按照City和Sex两列进行分组。分组后仍使用Count()聚合函数汇总行数。,统计结果,多列分组统计,如果代码写成这样，结果会怎样？,所以牢记： SELECT语句中除聚合函数外，所有列应写在Group By语句后面。,执行错误,增加Sex才正确.,Sex,使用HAVING子句对分组结果再次过滤,继续考虑上面的查询，如果“要统计不同城市不同性别的会员个数，并且只显示会员个数大于2个的记录”如何处理呢？,需要对分组后的数据进行筛选， 筛选条件Count(*) =2。对分组后的数据进行筛选需要使用Having子句。,统计结果：,分组查询对比,WHERE子句：从数据源中去掉不符合其搜索条件的数据GROUP BY子句：搜集数据行到各个组中，统计函数为各个组计算统计值HAVING子句：去掉不符合其组搜索条件的各组数据行,问题,成绩表中存储了所有学员的成绩，想知道：学员的总成绩、平均成绩?怎么办？,问题,如果不是统计所有人所有课程的总成绩而是想求每一门课的平均绩或者某个人的所有课的总成绩怎么办？,分组汇总,第一门课6个成绩取平均值,第二门课6个成绩取平均值,第三门课5个成绩取平均值,分组查询GROUP BY,SELECT CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseID,分组查询思考,SELECT StudentID, CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseID,思考：执行以下的T-SQL：, 结果如何？,分组查询多列分组,怎么样来统计每次的内部测试不同学员的成绩？,分组查询再看看,StudentID和CourseID的组合存在重复,需要按照这两个来进行分组，避免StudentID和CourseID 同时一样,分组查询多列分组,SELECT StudentID AS 学员编号,CourseID AS 课程编号, AVG(Score) AS 内部测试平均成绩FROM ScoreGROUP BY StudentID,CourseID,分组查询问题,在以上统计内部测试成绩的基础上，如果只想看补考的学员的成绩，怎么办,分组查询再看看,增加条件：要求该学员的CourseID在分组内出现过一次以上,SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩FROM ScoreGROUP BY StudentID,CourseID,分组查询HAVING,SELEC