１，创建分区表　　（１）创建分区函数　　（２）创建分区方案　　（３）创建分区表２，为分区表输入数据

回顾 １，创建分区表　　（１）创建分区函数　　（２）创建分区方案　　（３）创建分区表２，为分区表输入数据 [练习] 创建分区表，表结构如下,要求按照产品编号列进行分区，三个分区分别放入文件组fg1,fg2,fg3 中。 1、分区要求：(1)产品编号<1000;(2)产品编号>=1000 and 产品编号<2000;(3)产品编号>=2000。 2、表结构：产品（产品编号 int primary key, 产品名称 varcha(30)）

第四章数据库的查询和视图 4.1 数据库的查询 4.2 视图

4.1数据查询 数据查询：SELECT语句具有数据查询、统计、分组和排序的功能语句格式： SELECT <目标列组> FROM <数据源> [WHERE <元组选择条件>] [GROUP BY <分列组> [HAVING <组选择条件>组]] [ORDER BY <排序列1> ASC|DESC [，…]]

4.1数据查询——子句介绍 SELECT子句：用于指明查询结果集的目标列。对两表中相同的列名，要在列名前加“表名.”,例：学生.学号。 FROM子句：用于指明查询的数据源，数据源可以是基本表或视图。如果数据源不在当前数据库中，须在表名或视图名前加“数据库名”。 WHERE子句：描述选择条件。 GROUP BY子句：将查询结果的各行按一列取值相等的原则进行分组，如果有HAVING短语，则查询结果只是满足指定条件的组。 ORDER BY子句：查询结果按一定顺序排序。

4.1 单表查询 例1：求数学系学生的学号和姓名 SELECT 学号,姓名 FROM 学生 WHERE 所在系='数学' 例2：求选修了课程的学生学号 SELECT DISTINCT 学号 FROM 选课例3：查询计算机系全体学生的基本信息 SELECT * FROM 学生 WHERE 所在系='计算机' 例4：查询全体学生的学号、姓名以及出生年份 SELECT 学号,姓名,2007-年龄 AS 出生年份 FROM 学生例5：查询考试成绩有不及格的学生的学号 SELECT DISTINCT 学号 FROM 选课 WHERE 成绩<60

4.1 单表查询 例6：查询计算机系，数学系，物理系三个系的全体学生的学号，姓名，所在系 SELECT 学号,姓名,所在系 FROM 学生 WHERE 所在系 IN('数学','计算机','物理') 例7：查所有姓李的学生学号，姓名 SELECT 学号,姓名 FROM 学生 WHERE 姓名 LIKE '李%' 例8：查所有姓李的且为单名的学生学号，姓名 SELECT 学号,姓名 FROM 学生 WHERE 姓名 LIKE ‘李_'

4.1 单表查询 NOT LIKE：使用NOT LIKE与LIKE的作用相反。

4.1 单表查询 【例4.20】查询XSB表中学号中倒数第3个数字为1且倒数第1个数在1到5之间的学生学号，姓名及专业。 SELECT xh,xm,zy FROM XSB WHERE xh LIKE '%1_[12345]' 【例4.21】查询XSB表中名字包含下画线的学生学号和姓名。 SELECT xh,xm FROM XSB WHERE xm LIKE '%#_%' ESCAPE '#'

4.1 单表查询 例10：求缺少成绩的学生学号和课程号 SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL 例11：求选修了课程号为“c1”且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出 SELECT 学号,成绩*0.8 FROM 选课 WHERE 课程号='c1' AND 成绩 BETWEEN 80 AND 90 例12：检索数学系或计算机系姓“陈”的学生的信息 SELECT * FROM 学生 WHERE 所在系 IN('数学','计算机') AND 姓名 LIKE '陈%'

4.1 单表查询 注意1：替换查询结果中的数据要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为： CASE WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 …… ELSE 表达式 END

【例】查询XSB中按以下规则进行替换： SELECT xh, xm, CASE WHEN zxf IS NULL THEN '尚未选课' WHEN zxf < 30 THEN '不及格' WHEN zxf >=30 and zxf<=32 THEN '合格' ELSE '优秀' END As dengji FROM XSB WHERE zy='计算机‘ 练习：将 CJB中成绩由百分制转化为五等级制 4.1 单表查询

4.1 单表查询 注意2：计算列值【例】按120分计算成绩显示。 SELECT xh, kch, 成绩120=cj*1.20 FROM CJB

4.1 单表查询 注意3：限制结果集返回行数 TOP选项的基本格式为： TOP n [ PERCENT ] 其中n是一个正整数，表示返回查询结果集的前n行。若带PERCENT关键字，则表示返回结果集的前n%行。【例4.11】对PXSCJ数据库的XSB选择姓名、专业名和总学分，只返回结果集的前6行。 SELECT TOP 6 xm，zy，zxf FROM XSB

2、连接查询 连接查询的数据源为多个表，连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND衔接。例13：查询学生的学号，姓名及所选修的课程号、成绩 SELECT 学生.学号,姓名,课程号,成绩 FROM 学生,选课 WHERE 学生.学号=选课.学号例14：查询学生的学号，姓名及所选修的课程名及成绩 SELECT 学生.学号,姓名,课程名,成绩 FROM 学生,课程,选课 WHERE 学生.学号=选课.学号 AND 课程.课程号=选课.课程号例15：查询考试成绩有不及格的学生的学号、姓名 SELECT DISTINCT 学生.学号,姓名 FROM 学生,选课 WHERE 成绩<60 AND 学生.学号=选课.学号

3、对查询结果进行排序 使用ORDER BY子句例19：查询选修了C1课程的学生学号，成绩，并求要结果按成绩的降序排列 SELECT 学号,成绩 FROM 选课 WHERE 课程号='c1' ORDER BY 成绩 DESC NULL为最小， ORDER BY子句中可以用SELECT子句中的列的序号来表示列名例如： SELECT 学号,成绩 FROM 选课 WHERE 课程号='c1' ORDER BY 3 DESC

4、使用集函数 集函数 (count、sum、avg、max、min)A.count (属性名)：求字段的行数count (*)：求元组个数count (distinct 属性名)：不计重复的值 B. sum(数值表达式)：求数值表达式的总和 C. avg(数值表达式)：求数值表达式的平均值 D. max(属性名)：返回指定列中的最大值 E. min(属性名)：返回指定列中的最小值除count(*)外，列中的空值先去掉再计算

4、使用集函数 例20：求学生的总人数 SELECT COUNT(*) FROM 学生例21：求选课的总人次数 SELECT COUNT(*) FROM 选课例22：求选课的总人数 SELECT COUNT(DISTINCT 学号) FROM 选课例23：求C1课程的最高分，最低分，平均分 SELECT MAX(成绩),MIN(成绩),AVG(成绩) FROM 选课 WHERE 课程号='c1'

5、对查询结果进行分组 使用GROUP BY 子句例24：求各门课的课程号，最高分，最低分，平均分 SELECT 课程号,MAX(成绩) AS 最高分,MIN(成绩) AS 最低分, AVG(成绩) AS 平均分 FROM 选课 GROUP BY 课程号例25：求每个学生的学号、姓名及平均分 SELECT 学生.学号,姓名,AVG(成绩) AS 平均分 FROM 选课,学生 WHERE 学生.学号=选课.学号 GROUP BY 学生.学号,姓名例26：求各门课的课程号及相应的选课人数 SELECT 课程号,COUNT(学号) FROM 选课 GROUP BY 课程号例27：查询选了一门以上课程的学生学号 SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(*)>1

5、对查询结果进行分组 WHERE和HAVING 的区别：作用的对象不同，WHERE子句作用于数据源，从中选择满足条件的元组，HAVING短语作用于结果集的分组中选择满足条件的组， HAVING短语必须与GROUP BY子句合用，不能单独使用例如上例不能写成： SELECT 学号 FROM 选课 GROUP BY 学号 WHERE COUNT(*)>1

6、嵌套查询 • 一个SELECT…FROM…WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询叫嵌套查询或子查询。有以下4种子查询 • 带IN谓词的子查询 • 带比较运算符的子查询 • 带ANY或ALL的子查询 • 带EXISTS谓词的子查询例28：查询“陈力”所在系的全体学生的学号，姓名 SELECT 学号,姓名 FROM 学生 WHERE 所在系 IN(SELECT 所在系 FROM 学生 WHERE 姓名='陈力')

6、嵌套查询 例29：求选修了“计算机导论”的学生学号，姓名 SELECT 学生.学号,姓名 FROM 学生,选课,课程 WHERE 学生.学号=选课.学号 AND 选课.课程号=课程.课程号 AND 课程名='计算机导论' 或： SELECT 学号,姓名 FROM 学生 WHERE 学号 IN(SELECT 学号 FROM 选课 WHERE 课程号 N (SELECT 课程号 FROM 课程 WHERE 课程名='计算机导论')) 例30：求c1课程成绩高于“王红”的学生学号，成绩 SELECT 学号,成绩 FROM 选课 WHERE 课程号='c1' AND 成绩> (SELECT 成绩 FROM 选课 WHERE 课程号='c1' AND 学号= (SELECT 学号 FROM 学生 WHERE 姓名='王红'))

6、嵌套查询 例31：检索所有课程成绩都在80分及80分以上的学生学号、姓名如下语句是否正确: (1)SELECT 学号,姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 选课 WHERE 成绩 > 80 ) (2)SELECT 学号,姓名 FROM 学生 WHERE 学号 NOT IN (SELECT 学号 FROM 选课 WHERE 成绩 < 80 ) （1）的含义是：检索有成绩在80分及80分以上的学生学号、姓名（2）的含义是：检索没有成绩出现在80分以下的学生学号、姓名正确语句: SELECT distinct 学生.学号,姓名 FROM 学生,选课 WHERE 学生.学号=选课.学号 AND 学号 NOT IN (SELECT 学号 FROM 选课 WHERE 成绩 < 80)

6、嵌套查询 例32：求其他系中比计算机系某一学生年龄小的学生的信息（即求年龄小于计算机系年龄最大者的学生） SELECT * FROM 学生 WHERE 年龄<ANY (SELECT 年龄 FROM 学生 WHERE 所在系='计算机') AND 所在系<>'计算机' 此例也可用MAX()： SELECT * FROM 学生 WHERE 年龄< (SELECT MAX(年龄) FROM 学生 WHERE 所在系=‘计算机’) AND 所在系<>'计算机' 例33：求其他系中比计算机系学生年龄都小的学生的信息（即求其他系中比计算机系所有学生年龄都小的学生名单） SELECT * FROM 学生 WHERE 年龄<ALL (SELECT 年龄 FROM 学生 WHERE 所在系='计算机') AND 所在系<>'计算机'

6、嵌套查询 例34：求选课门数最多的学生的学号 SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(学号)>=ALL(SELECT COUNT(学号) FROM 选课 GROUP BY 学号) 例35：求选课门数最多的学生的学号、姓名 SELECT 学号,姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(学号)>=ALL(SELECT COUNT(学号) FROM 选课 GROUP BY 学号)) 或： SELECT 学生.学号, 姓名 FROM 学生,选课 WHERE 学生.学号 = 选课.学号 GROUP BY 学生.学号, 姓名 HAVING COUNT(学生.学号) >= ALL (SELECT COUNT(学号) FROM 选课 GROUP BY 学号)

6、嵌套查询 例40：求选修了C1课程或选修了C2课程的学生的学号 SELECT 学号 FROM 选课 WHERE 课程号 = 'C1' OR 课程号 = 'C2 ' 或：SELECT 学号 FROM 选课 WHERE 课程号 = 'C1' UNION SELECT 学号 FROM 选课 WHERE 课程号 = 'C2 ' 例41：求选修了C1课程又选修了C2课程的学生的学号此题不可以用下面的查询来实现： SELECT 学号 FROM 选课 WHERE 课程号 = 'C1' AND 课程号 = 'C2 ' 应该为 SELECT 学号 FROM 选课 WHERE 课程号 = 'C1' AND 学号 IN (SELECT 学号 FROM 选课 WHERE 课程号 = 'C2')

6、嵌套查询 注意4： COMPUTE子句作用：COMPUTE子句用于分类汇总格式为： [ COMPUTE { 聚合函数名（列名）} [ ,…n ] [ BY 列名 [ ,…n ] ] ] 效果: COMPUTE将产生额外的汇总行。

6、嵌套查询 【例4.66】查找通信工程专业学生的学号、姓名、出生时间，并产生一个学生总人数行。 SELECT XH, XM, CSSJ FROM XSB WHERE ZY= '通信工程' COMPUTE COUNT(XH) 【例4.67】将学生按专业排序，并汇总各专业人数和平均学分。 SELECT XH, XM, CSSJ, ZXF FROM XSB ORDER BY ZY COMPUTE COUNT(XH), AVG(ZXF) BY ZY

6、嵌套查询 注意5：EXISTS子查询。 EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXISTS刚好相反。其格式为：[ NOT ] EXISTS ( subquery ) 【例4.32】查找选修206号课程的学生姓名。 SELECT XM FROM XSB WHERE EXISTS ( SELECT * FROM CJB WHERE XH= XSB.XHAND KCH= '206')

6、嵌套查询 注意6：利用derived_table中间表子查询可以用在FROM子句中，derived_table表示由子查询中SELECT语句的执行而返回的表，但必须使用AS关键字为子查询产生的中间表定义一个别名。【例4.38】从XSB表中查找总学分大于30的男同学的姓名和学号。 SELECT XM,XH,ZXF FROM ( SELECT XM,XH,XB,ZXF FROM XSB WHERE ZXF>30 ) AS STUDENT WHERE XB=1

6、嵌套查询 【例4.39】在XSB表中查找1990年1月1日以前出生的学生的姓名和专业，分别使用别名stu_name和speciality表示。 SELECT m.stu_name, m.speciality FROM ( SELECT XM,ZY FROM XSB WHERE CSSJ<'19900101‘ ) AS m(stu_name, speciality)

6、嵌套查询 注意7：PIVOT 关系运算符（行转换为列）作用：PIVOT运算符将输入表的行旋转为列，并能同时对行执行聚合运算。格式： Select column1,column2…… From table_source PIVOT ＜pivot_clause＞ table_alias＜pivot_clause＞= ( aggregate_function ( value_column ) FOR pivot_column IN ( ＜column_list＞ ) )

格式（中文版）： Select 列1，列2，…… From 输入表 PIVOT ( 聚合函数 (进行计算的值列 ) FOR 透视列 IN (新列名1，新列名2，……) ) as 输出表的别名

使用范例：

转换语句： SELECT 产品编号,[5]AS五月,[6]AS六月,[7]AS七月 FROM 产品销售表１PIVOT ( SUM (销售额) FOR 销售月份 IN( [5], [6], [7] ) ) AS pvt ORDER BY 产品编号;

执行过程： a．PIVOT首先按值列之外的列（产品编号和销售月份）对输入表进行分组汇总。 SELECT 产品编号,销售月份,SUM(销售额)AS销售总额 FROM 产品销售表１GROUP BY 产品编号,销售月份; b．PIVOT根据FOR 销售月份 IN指定的值5、6、7，首先在结果集中建立名为5、6、7的列，然后从中间结果中取出销售月份列中取出相符合的值，分别放置到5、6、7的列中。此时得到的结果集的别名为pvt。 c．最后根据SELECT ……FROM的指定，从别名pvt结果集中检索数据，并分别将名为5、6、7的列在最终结果集中重新命名为五月、六月、七月。注意FROM表示从经PIVOT关系运算符得到的pvt结果集中检索数据，而不是从产品销售表1中检索数据。

【例4.40】 查找XSB表中1990年1月1日以前出生的学生的姓名和总学分，并列出其属于计算机专业还是通信工程专业的情况，1表示是，0表示否。 SELECT XM,ZXF,计算机,通信工程 FROM XSB PIVOT ( COUNT(XH) FOR ZY IN(计算机,通信工程) )AS pvt WHERE CSSJ<'1990-01-01'

练习：假设有张学生成绩表(tb1) select 姓名,语文,数学,物理from tb1 pivot ( max(分数) for 课程 in(语文,数学,物理) )as t

select姓名, max(case 课程 when '语文' then 分数 else 0 end) as 语文, max(case 课程 when '数学' then 分数 else 0 end) as 数学, max(case 课程 when '物理' then 分数 else 0 end) as 物理, avg(分数) as 平均分, sum(分数) as 总分 from tb1 group by 姓名

6、嵌套查询 注意8：unPIVOT 关系运算符（列转换为行）作用：UNPIVOT与PIVOT执行几乎完全相反的操作，将列转换为行。但并不完全是逆操作。原因：数据已经被进行了分组汇总格式： Select column1,column2…… From table_source unPIVOT ＜unpivot_clause＞ table_alias＜unpivot_clause＞= ( value_column FOR pivot_column IN ( ＜column_list＞ ) )

格式（中文版）： Select 列1，列2，…… From 输入表 unPIVOT ( 新列名 FOR 透视列 IN (将被提取到透视列中的所有列名 ) ) as 输出表的别名

使用范例：

转换代码： SELECT 产品编号, 销售月份, 销售额 FROM 产品销售表２UNPIVOT ( 销售额 FOR 销售月份 IN (五月, 六月, 七月) ) AS unp;

执行过程： a．首先建立一个临时结果集的结构，该结构中包含产品销售表2中除IN (五月, 六月, 七月)之外的列，以及销售额 FOR 销售月份中指定的值列（销售额）和透视列（销售月份）。 b．将在产品销售表2中逐行检索数据，将表的列名称（在IN (五月, 六月, 七月)中指定）放入销售月份列中，将相应的值放入到销售额列中。最后得到想要的结果集。

【例4.41】 将KCB表中的开课学期和学分列转换为行输出。 SELECT KCH,KCM,选项,内容 FROM KCB UNPIVOT ( 内容 FOR 选项 IN(XF,KKXQ) )as unpvt

练习：假设有张学生成绩表(tb2) select姓名,课程,分数 from tb2 unpivot ( 分数 for课程 in(语文,数学,物理) )t

7．CONTAINS谓词 如何新建全文索引

7．CONTAINS谓词 CONTAINS谓词的语法格式： CONTAINS ( { column | * } , ‘<要搜索的文本和匹配条件>’ [ , 语言 ] ) 说明：（1）column表示在指定的列中搜索，可以指定多个列，类型为char、varchar、nchar、nvarchar、text、ntext、image、xml 和varbinary(max) 的列是可进行全文搜索的有效列。（2）*表示在所有列中搜索，

7．CONTAINS谓词 【例4.25】使用CONTAINS谓词搜索XSB表中包含字符“工程”的所有行。 SELECT * FROM XSB WHERE CONTAINS(*,'工程') 练习：查找姓名中有“林”的学生的姓名和学号，使用CONTAINS谓词搜索。

8．FREETEXT谓词 FREETEXT谓词也用于在一个表中搜索单词或短语，并要求表已建全文索引，与CONTAINS谓词类似。格式为： FREETEXT ( { column | * }, ‘<要搜索的文本和匹配条件>’ [ , 语言 ] ) 其中freetext_string是要搜索的字符串。 FREETEXT的查询精度没有CONTAINS高，并不要求对它们进行严格的模式匹配。FREETEXT对所查询的串也没有写法要求，因此FREETEXT也称为自由式查询。【例4.26】使用FREETEXT谓词搜索XSB表中包含字符“李”的所有的行。 SELECT * FROM XSB WHERE FREETEXT(*,'李')

１，创建分区表 （１）创建分区函数 （２）创建分区方案 （３）创建分区表 ２，为分区表输入数据