SQL_server_2000数据库(中)

SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 1页 第五章 数据查询（1） 数据查询是数据库应用程序系统中使用率最高的一项功能，在本章将学习如 下内容： 理解查询的机制 使用 select语句进行条件查询 在查询中返回限制行、进行查询排序 在查询中使用表达式、运算符和 函数 excel方差函数excelsd函数已知函数     2 f x m x mx m      2 1 4 2拉格朗日函数pdf函数公式下载 一、在企业管理器中查询数据： 方法：展开企业管理器，展开组，展开服务器，展开数据库，选择表选项， 选择要查询数据的表，右击，在菜单中选择“打开表”，选择“查询”项，可以选 择执行多个表，字段时行查询输出，系统会自动生成 SQL语句。 二、SQL查询基础 1）查询和记录集： 查询是针对表中己经存在的数据行而言的，可以简单的理解为“筛选”，比如： 学员信息表： 学员编号 姓名 地址 班级 …… 001 张明 湖南 S201 002 李菲 湖北 S202 003 于谦 香港 S201 004 周接轮 台湾 S205 005 刘国正 北京 S202 查询班级“S202”得到如下：“记录集” 002 李菲 湖北 S202 005 刘国正 北京 S202 数据表在接受查询请求的时候，可以简单地理解为它将逐行选取，判断是否 符合查询的条件，如果符合就提取出来，然后把所有的被选择的行组织在一起， 形成另外一个“类似于表的结构”，这便是查询的结果，我们通常称之为“记录集” 由于记录集的结构实际上和表的结构是相同的，都是由行组成的，因此，在 记录集上依然可以进行再次查询。 2）使用 select语句进行查询 语句格式： select <列名> from <表名> [where <查询条件表达式>] [order by <排序的列名> [asc或 desc]] 其中：where及条件是可选的，如果不限制，则查询返回所有行的数据项 SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 2页 order by 是用来排序的，数据表中的记录是无序的，并不按照一定的次序存 储，例如要按学员的考试成绩排序以看到高分的情况，则需要按照分数列的值进 行排序。 asc 表示排升序 desc表示排降序 查询的具体用法如下： a) 查询所有的数据行和列 把表中的所有行和列都列举出来比较简单，这时需要用到“*”通配符来表示 所有的列： select * from userinfo b)查询部分行列---条件查询 查询部分列需要列举不同的列名，而查询部分行需要使用 where 子句进行条 件限制： select xb,xb,zy from userinfo where zy=’软件开发’ 以上查询，只是将专业为“软件开发”的学员的姓名、性别、专业列出来， 想想以下的查询结果： select xb,xb,zy from userinfo where zy<>’软件开发’ c)在查询中使用列名 as子句可以用来改变结果集列的名称，也可以为组合或者计算出的列指定名 称，还有一种情况是要让标题列的信息更易懂，比如把 xm列名查询后显示为“姓 名”: select xm as 姓名，xb as 性别，zy as 专业 from userinfo where zy=’软件开发’ 使用计算、合并得到新列的命名： select firstname+’.’+lastname as ‘姓名’ from employees 重新命名列名还有一种方法，就是采用“=”来命名： select ‘姓名’=firstname+’.’+lastname from employees d)查询空行 在 SQL语句中采用“is null”或者“is not null”来判断是否为空行，因此， 如果要查询学员信息中没有填写 email信息的学员，则可以： select xb from userinfo where email is null SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 3页 e)在查询中使用常量列 有的时候，一些常量的缺省信息需要添加到查询输出中，以方便统计或计算， 例如查询学员信息的时候，学校名称统一都是“湖北文达”，查询输出的语句： select 姓名=firstname+'.'+lastname,'湖北文达' as 学校名称 from employees f)查询返回限制的行数 一些查询需要返回限制的行数，例如，一个销售记录数据库中，有成千上万 条销售记录，现在需要找出销售排行前十位的销售记录，这种情况下就不需要把 所有的记录列出来，就需要用到限制返回行数的查询： SQL中，限制行数用 TOP关键字来约束，比如： select top 5 * from userinfo where xb=’男’ 还有一种情况是需要从表中按一定的百分比提取记录，这时需要用到 percent 关键字来限制，例如要提取 20%的女生数据： select top 20 percent * from userinfo where xb=’女’ 3) 查询排序 如果需要按照一定的顺序排列查询语句选中的行，需要使用 order by子句， 并且排序可以是升序（asc）或者降序（desc），默认为升序排列，例如： select * from userinfo order by id desc 表示，把 userinfo表中所有的记录按 id字段的值降序输出 如果，在查询学员成绩的时候，如果把有成绩都降低 10%后加上 5分，再按 照及格成绩的高低来进行排序，则： select xb as 姓名，(cj*0.9+5) as 综合成绩 from xscj where (cj*0.9+5)>60 order by cj 将两个表中的姓名字段信息查询并合并后，再按姓名字段降序排列： select au_lname+’.’+au_fname as emp from authors union select fname+’.’+lname as emp from employee order by emp desc 还可以按照多个字段进行排序，例如要在学员成绩的基础上，再按照课程 ID 进行排序的语句： select stu_id as 学员编号,score as成绩 from score where score>60 order by score,kcid SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 4页 4)在查询中使用函数 SQL Server 也提供了一些内部函数，不同类别的函数可以和 SQL Server 的 select 语句联合使用，也可以与 update和 insert一起使用。 以下，把函数分为四类，分别是：字符串函数，日期函数，数学函数和系统 函数。 a)字符串函数 字符串函数用于控制返回给用户的字符串，这些功能仅能用于字符型数据， 见下表： 部分常用的字符串函数 函数名 描述 举例 charindex 用来寻找一个指定的 字符串在另一个字符 串中的起始位置 Select charindex(‘xsc’,’my xsc computer’,1) 返回:4 len 返回传递给它的字符 串长度 Select len(‘sql server课程’) 返回：12 lower 把传递给它的字符串 转换为小写 Select lower(‘SQL server课程’) 返回：sql server课程 upper 把传递给它的字符串 转换为大写 Select upper(‘sql server课程’) 返回：SQL SERVER课程 ltrim 清除字符左边的空格 Select ltrim(‘张三 ‘) 返回：张三 rtrim 清除字符右边的空格 Select rtrim(‘张三 ‘) 返回： 张三 right 从字符串右边返回指 定数目的字符 Select right(‘买卖提.吐尔松’,3) 返回：吐尔松 replace 替换一个字符串中的 字符 Select replace(‘莫乐可切.杨可’,’可’,’兰’) 返回：莫乐可切.杨兰 stuff 在一个字符串中，删 除指定长度的字符， 并在该位置插入一个 新的字符串 Select stuff(‘abcdefg’,2,3,’我的音乐世界’) 返回：a我的音乐世界 efg b)日期函数 日期函数用于操纵日期值，我们不能直接对日期运用数学函数，例如，如果 执行一个诸如“当前日期+1”的语句，SQL Server无法理解要增加的是一日，一 月还是一年，日期函数帮助提取日期值中的日、月和年，以便分别操纵它们。 SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 5页 部分常用的日期函数 函数名 描述 举例 getdate 取得当前的系统日期 Select getdata() 返回：今天的日期 dateadd 将指定的数值添加到指 定的日期部分后的日期 Select dateadd(mm,4,’01/01/99’) 返回：以当前的日期格式返回 05/01/99 datediff 两个日期之间的指定日 期部分的区别 Select datediff(mm,’01/01/99’,’05/01/99’) 返回：4 datename 日期中指定日期部分的 字符串形式 Select datename(dw,’01/15/2000’) 返回：Saturday datepart 日期中指定日期部分的 整数形式 Select datepart(day,’01/15/2000’) 返回：15 c)数学函数 数学函数用于对数值进行代数运算。 部分常用的数学函数 函数名 描述 举例 abs 取数值表达式的绝对值 Select abs(-43) 返回：43 ceiling 取大于或等于指定数 值、表达式的最小整数 Select ceiling(43.5) 返回：44 floor 取小于或等于指定表达 式的最大整数 Select floor(43.5) 返回：43 power 取数值表达式的幂值 Select power(5,2) 返回：25 round 将数值表达式四舍五入 为指定精度 Select round(43.534,1) 返回：43.5 sign 对正数返回+1,对于负 数返回-1，对于 0 则返 回 0 Select sign(-43) 返回：-1 sqrt 取浮点表达式的平方根 Select sqrt(9) 返回：3 d)系统函数 系统函数用来获取有关 SQL Server中对象和设置的系统信息。 部分常用的系统函数 函数名 描述 举例 convert 用来转变数据类型 Select convert(varchar(5),12345) 返回：字符串的 12345 current_user 返回当前用户的名字 Select current_user SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 6页 返回：你登录的用户名 datalength 返回于指定表达式的字节数 Select datalength(‘中国 A联盟’) 返回：5 host_name 返回当前用户所登录的 计算机名称 Select host_name 返回：你所登录的计算机的名字 system_user 返回当前所登录的用户 名称 Select system_user 返回：你当前所登录的用户名 user_name 从给定的用户 ID 返回 用户名 Select user_name(1) 返回：从任意数据库中返回“dbo” 上面所有这些函数，可以在 t-sql中混合使用，得到符合特殊要求的查询输出。 三、数据查询的案例分析 案例 1：更新用户卡信息： 某公司印了一批充值卡，卡的密码是随机生成的，现在出现了这个问题：卡 里面的“O和 0”“ I和 1”，用户反映说看不清楚，公司决定，把存储在数据库中 的密码中所有的“O”改成“0”，所有的“I”改成“1”。 数据库表名：card 密码字段名：password 分析： 1、这是更新语句，需要用到 update语句来执行。 2、因为牵涉到字符串的替换，需要用到 SQL Server中的函数 replace 方法： update card set password=replace（password，‘O’，‘0’） update card set password=replace（password，‘I’，‘1’） 以上使用了两个 SQL 语句，在大多数情况下要求只能用一条 SQL 来实现， 因此，可以使用 replace的连续操作来实现： 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 分析： 1、这是查询语句，需要使用 select语句 2、需要用到 order by进行排序 3、前半部分的数字，可以从先找到“-”符号的位置，然后取其左半部分， SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 7页 最后再使用 convert函数将其转换为数字： Convert(int,left(listnumber,charindex(‘-‘,listnumber)-1)) 4、后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到 该位置的全部字符替换为空格，最后再使用 convert函数将其转换为数字： convert(int,stuff(listnumber,1,charindex(‘-‘,listnumber),’’)) 方法： select listnumber from sellrecord order by convert(int,left(listnumber,charindex(‘-‘,listnumber)-1)), convert(int,stuff(listnumber,1,charindex(‘-‘,listnumber),’’)) 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf ： 1. 查询将逐行筛选表中的数据，最后符合要求的记录重新组合成“记录集”，记 录集的结构类似于表结构。 2. 判断一行中的数据项是否为空，使用 in null。 3. 使用 order by进行查询记录集的排序，并且可以按照多个列进行排序。 4. 在查询中，可以使用常量、表达式、运算符。 5. 在查询中使用函数，能够像在程序中那样处理查询得到的数据项。 练习： 在前面创建的学生档案数据库中，分别录入相关数据，然后进行如下查询练习： 1、按学生成绩由高到低显示。 2、显示姓名为“张三”的姓名、性别、地址。 3、显示所有男生的资料。 4、显示所有“软件开发”专业的学生。 5、显示所有非“软件开发”专业的学生。 6、显示姓“张”的学生。 7、显示地址为空的学生信息。 8、显示“1985-01-01”以后出生的学生。 作业： 在前面的 netbar数据库中，编写查询语句实现以下的要求： 1、由于最近屡次发生卡密码丢失事件，因此机房规定：要求密码与姓名或者 卡号不能一样，请编写 SQL语句，查出密码与姓名或者卡号一样的人的姓名，以 方便通知。 2、编号为 B01计算机坏了，请通过查询得到这台机器最近一次上机的卡号。 3、为了提高上门率，上个月举行了优惠活动，周六和周日每小时上机的费用 为半价，请统一更新一下数据表中的费用信息。 4、编写查询显示本月上机时间最长的前三名用户卡号。 SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 8页 第六章 数据查询（2） 目标： 使用 like、between、in进行模糊查询 在查询中使用聚合函数 使用 group by进行分组查询 进行多表联接查询 一、模糊查询： 模糊查询提取的数据不一定是确切的，查询者对查询条件也是模糊的， 例如：只查询张姓学员的信息，只查询分数在 60-80 分之间的，只查询北京 上海广州地区的学员等，这种查询不是指定某个人的姓名，一个具体的分数 或者某个固定的地区，因此，都属于模糊查询。 1、使用 like进行模糊查询： 在前面介绍过用 like字句来编写约束，like运算符用于匹配字符串 或字符串一部分（称为子串），由于该运算符只用于字符串，所以仅与 char或 varchar数据类型联合使用。 在数据更新、删除或者查询的时候，依然可以使用 like关键字来进 行匹配查询，例如： select * from userinfo where name like ‘张%’ 或者查询不是八月份发行的 A卡或 C卡： select * from card where id like ‘00[^8]%[A,C]%’ 2、使用 between在某个范围内进行查询： 使用关键字 between可以查找那些介于两个己知值之间的一组未知 值，要实现这种查询，必须知道开始查找的初值和终值，这个最大值和 最小值用单词 and分开，例如： select * from score where score between 60 and 80 此外，between查询在查询日期范围的时候使用的比较多，例如：查询不 在 1992年 8月 1日到 1993年 8月 1日之间订购的图 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf 列表： select * from sales where ord_date not between ‘1992-8-1’and ‘1993-8-1’ 3、使用 in在列举值内进行查询： 查询的值是指定的某些值之一，可以使用带列举值的 in关键字来进行查 询，列举值放在圆括号里，用逗号分开，例如： select name as 学员姓名 from students where address in (‘北京’,’广州’,’上海’) order by address 还可以把 in关键字和 not关键字合起来使用，这样可以得到所有不匹配 列举值的行。 练习： 1、查询联系地址中含有“宜昌”的记录 SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 9页 2、查询学习专业中含有“电”的记录 3、查询入学时间在 7月到 9月之间的记录 4、查询“SQL”成绩在 60到 80分之间的记录 5、查询专业为“电子商务”、“软件开发”、“办公文秘”三者之一的记录 二、聚合函数： 在查询中还会经常碰到的要求是取某些列的最大值、最小值、平均值等 信息，有时候还需要计算出究竟查询到多少行数据项，这个时候，查询的“统 计数据”是用户比较关心的，SQL Server提供了“聚合函数”，聚合函数能够 基于列进行计算，并返回单个值。 1、 SUM SUM 返回表达式中所有数值的总和，SUM 只能用于数字类型的列，不 能够汇总字符、日期等其他数据类型。 例如：我们需要汇总成绩表中所有成绩字段的总和： select sum(cj) from cj 例如：在一个销售表中，我们需要统计总的销售量和销售金额： select sum(xsl),sum(xse) from xsb 注意：这种查询只返回一个数值，因此，不能够直接与可能返回多行的 列一起使用来进行查询，例如： select sum(xsl),mc from xsb 将会产生错误。但是，在一个查询中可以使用多个聚合函数。 2、AVG AVG函数返回表达式中所有数值的平均值，AVG函数也只能用于数字类 型的列，例如：要查询成绩表中及格线以上的人的平均成绩： Select avg(cj) as 平均成绩 from cj where cj>=60 3、MAX和MIN MAX 返回表达式中的最大值，MIN 返回表达式的最小值，它们都可以 用于数字型、字符型以及日期/时间型的列。 例如：要查询成绩表中的平均成绩、最高分、最低分等： select avg(cj) as 平均成绩, max(cj) as 最高分, min(cj) as 最低分 from cj where cj>=60 4、 COUNT COUNT返回提供的表达式中非空值的计数，COUNT可以用于数字和字 符类型的列。 SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 10页 另外也可以使用“*”作为 COUNT的表达式，使用星号可以不必指定特 定的列而计算所有的行数。 例如：在成绩表中统计及格的人数： select count(*) as 及格人数 from cj where cj>=60 三、分组查询： 1、使用 GROUP BY进行分组查询： 学生成绩表中存储了所有课程的成绩，在这种情况下，可能就需要 统计不同课程的平均成绩，也就是说，需要对不同的成绩首先按照课程 来进行分组，分组以后再进行聚合计算，得到累计信息。 这种情况也很普遍，比如一个电器销售商行，销售的有电视，空调， 冰箱等，所有销售记录肯定存在一张销售表中，月底，我们就需要分别 统计电视，空调，冰箱等电器的销售额，这时，就需要先按商品进行分 类，然后分别进行汇总和统计输出。 例如： 销售表 id spmc xsl dj Xse 1 电视 2 1500 3000 2 空调 5 1800 9000 3 电视 4 1500 6000 4 空调 2 1800 3600 select spmc,sum(xse) as 销售额 from xsb group by spmc 返回： spmc 销售额 电视 9000 空调 12600 分组查询，有时还要按照多个列来进行分组，比如，在上表添加一 个销售员(ygid)字段，我们需要看到每个不同的员工，销售每种不同的商 品的销售额： select spmc,ygid,sum(xse) from xsb group by spmc,ygid 在使用 group by关键字时，在 select列表中可以指定的项目是有限 制的，select语句中仅允许以下几项： 1）被分组的列 2）为每个分组返回一个值的表达式，例如用一个列名作为参数的聚 合函数。 2、使用 having子句进行分组筛选： having 子句是对分组统计后的数据进行条件限制的，比如，在上面 的销售表中，需要统计当天销售记录在两笔以上的商品的销售额，这种 情况用 where条件是不可能实现的， SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 11页 例如： select mc,ygid,sum(xse) from xsb group by mc,ygid having count(mc)>1 思考：在学生成绩表中，保存有第一次学员的考试成绩，以及第一 次考试没有通过的人员的补考成绩，现在需要只显示补考过的学员名单。 having 子句和 where 子句可以在同一个 select 语句中一起使用，使 用的顺序按照下图所示的次序： where group by having 在 select语句中，where、group by、having子句和统计函数的执行 次序如下：where 子句从数据源中去掉不符合其搜索条件的数据，group by 子句搜集数据行到各个组中，统计函数为各个组计算统计值，having 子句去掉不符合其组搜索条件的各组数据行。 例如： select mc,ygid,sum(xse) from xsb where xse>3000 group by mc,ygid having count(mc)>1 首先用 where条件语句选出单条记录销售额大于 3000的记录，再用 group by语句进行分组，然后用 having语句把分组后的不满足条件的记 录去掉。 数据查询的案例分析： 案例：汇总一张表中的奇数行和偶数行： 数据表：tbl 字段名称：a 主键字段：idkey(标识列，标识种子：1，标识增长量：1) 分析： 1、只能依靠标识列的值来进行判断和选取 2、但是，因为数据行可能存在增加、修改和删除，因此标识列的 数据值并不“完全可靠”，例如标识列值为 3，并不一定就是第 三行，因为如果第二行被删除了，它就是第二行，实际上也是 偶数行。 3、根据我们前面使用过的 select……into，可以创建一张新表，顺 便创建新的标识列，再在新的标识列上执行奇偶判断。 4、奇偶判断依据为：标识列值%2不等于 0，偶数的判断依据为： SQL Server2000教学课件 授课教师:李善华 QQ：36235856 e-mail:xscdn@126.com 共 12页第 12页 标识列值%2等于 0。 代码如下： select a,identity(int,1,1) as id into newtbl from tbl select sum(a) as 奇数列汇总 from newtbl where id%2<>0 select sum(a) as 偶数列汇总 from newtbl where id%2=0 最后再把 newtbl表删除。 总结： 使用 like、between、in关键字，能够进行模糊查询—条件不明确的 查询。 聚合函数能够对列生成一个单一的值，对于分析和统计非常有用。 分组查询是针对表中不同的组分类统计和输出，group by子句通常 会结合聚合函数一起来使用。  having子句能够在分组的基础上，再次进行筛选。 作业： 1、 查询表 A中的第二条记录（数据表：A，列名 ID、C1、C2，主键字 段：ID） 2、 在前面的 netbar数据库中： 一位家长想看看他儿子这个月的上机次数，己知他儿子的卡号为 0023_ABC，请编写 SQL查询。 3、 在学员的考试成绩表中，有如下的数据： 姓名 课程 成绩 张千 语文 80 张千 数学 77 李万 语文 66 李万 数学 91 王亿 语文 84 王亿 数学 100 王亿 英语 90 杨兆 英语 86 杨兆 数学 93 请用一条 SQL语句查询出每门课都大于 80分的学生姓名。