数据库的查询
1 pivot 和unpivot
其中pivot将行值转换为列名,unpivot相反;
%查询XSB表中1990年1月1日以前出生的学生的姓名和总学分,并列出是属于计算机专业还是通信工程专业,1表示是,0表示否。 SELECT 姓名,总学分,通信工程 FROM XSB PIVOT ( COUNT(学号) FOR 专业 IN(计算机,通信工程) ) AS pvt WHERE 出生时间<'1990-01-01'
%将KCB表中的开课学期和学分列转换为行输出 SELECT 课程号,课程名,选项,内容 FROM KCB UNPOVIT ( 内容 FOR 选项 IN (学分,开课学期) )
2 连接
连接是二元运算,可以对两个或者多个表进行查询,结果同城是含有连接运算的多个表的指定列的表。在T-SQL中,连接查询有两大类表示形式,一种是符合SQL标准连接谓词的表现形式,一是T-SQL扩展的使用关键字JOIN的表示形式。
2.1 连接谓词
连接谓词中的两个列成为连接字段,他们必须是科比的。不同表中的字段名,需要再字段名前加上表名以示区别。连接谓词的比较符可以是<,<=,=,>,>=,!=,<>,!<,!>。如果比较符为‘=’就是等值连接,若在目标劣种去除相同的字段名就是自然连接。
%自然连接 SELECT XSB.*,CJB.课程号,CJB.成绩 FROM XSB CJB WHERE XSB.学号=CJB.学号
2.2 以JOIN关键字指定的连接
使表的连接能力有了增强,关键字:
ON:用于指定连接条件
APPLY:可以为实现查询操作的外部表达式返回的每个行调用表值函数。
CROSS JOIN:表示交叉连接。
以JOIN关键字指定的连接有三种类型:内连接,外连接,交叉连接。
(1)内连接按照ON所指定的条件合并连个表,返回满足条件的行。系统默认的,可以省略INNER关键字。
%查询PXSCJ数据库每个学生的情况以及选修的课程情况 SELECT * FROM XSB INNER JOIN CJB ON XSB.学号=CJB.学号 %用FROM自居和JOIN关键字,查找选修了206课程且成绩在80分以上的学生姓名及成绩 SELECT 姓名,成绩 FROM XSB JOIN CJB ON XSB.学号=CJB.学号 WHERE 课程号=‘206’ AND 成绩>=80
(2)外连接;指定OUTER关键字,外连接的结果表不但包含满足连接条件的行,还包括相应表中的所有行。外连接有三种,左外连接,右外连接,完全外连接。
左外连接(LEFT OUTER JOIN):结果表中除了包含满足条件的行外,还包括左表的所有行;
右外连接(RIGHT OUTER JOIN):结果表中除了包含满足条件的行外,还包括右表的所有行;
全外连接(FULL OUTER JOIN):结果表中除了包含满足条件的行外,还包括两个表的所有行;
其中OUTER关键字可以省略;
%查询所有学生的情况以及他们选修的课程号,如果学生为选修任何课,也要包括其中; %结果中若有学生没选课,相应的字段为NULL SELECT XSB.*,课程号 FROM XSB LEFT OUTER JOIN CJB ON XSB.学号=CJB.学号 %查询被选了的课程的选修情况和所有课程的课程名 SELECT CJB.*,课程名 FROM CJB RIGHT JOIN KCB ON CJB.课程号=KCB.课程号
(3)交叉连接;实际上是将两个表进行笛卡尔积运算,结果表示有第一个表的每行与第二个表的每一行连接后形成的表,因此结果表的行数等于两个表行数之积;交叉连接也可以使用WHERE字句进行条件限定
%列出所有学生可能的选课情况 SELECT 学号,姓名,课程号,课程名 FROM XSB CROSS JOIN KCB
3 GROUP BY字句
GROUP BY字句主要是用于根据字段进行分组;
SELECT 专业 FROM XSB GROUP BY 专业 %求各专业的学生 SELECT 专业,COUNT(*) AS '学生数' FROM XSB GROUP BY 专业
带ROLLUP操作符的GROUP BY字句:指定的结果集内不仅包括由GROUP BY提供的正常行,还包括汇总行(GROUP BY···WITH ROLLUP)。
带CUBE操作符的GROUP BY字句:对GROUP BY子句中的各列的所有可能组合均产生汇总行(GROUP BY···WITH CUBE)。
4 HAVING子句
使用GROUP BY子句和聚合函数对数据进行分组后,还可以使用HAVING对分组数据进行下一步的刷选。所有HAVING是伴随着GROUP BY出现的。
%查询选修课课程吵过两门且成绩都在80分以上的学生的学号 SELECT 学号 FROM CJB WHERE 成绩>=80 GROUP BY 学号 HAVING count(*)>=2 %查询平均成绩在85分以上的学生的学号和平均成绩 SELECT 学号,AVG(成绩) AS '平均成绩' FROM CJB GROUP BY 学号 HAVING AVG(成绩)>=85
5 ORDER BY字句
对查询结果进行排序,ASC表示升序,DESC表示降序。
6 SELECT语句的其他语法
6.1 INTO:将SELECT查询的IE过保存到新建的表中。
6.2 UNION:将多个SELECT查询的结果合并成一个结果集。
注意,所有查询的列数和列的顺序必须相同,数据类型必须兼容
6.3 EXCEPT和INSERSECT:用于比较两个查询的结果,返回非重复值。
6.4 CTE:指定临时结果集。
%由XSB表创建计算机系学生表,包括学号和姓名 SELECT 学号,姓名 INTO 计算机系学生表 FROM XSB WHERE 专业=‘计算机’ %查找学号为01和学号为02的学生 SELECT * FROM XSB WHERE 学号=‘01’ UNION SELECT * FROM XSB WHERE 学号=‘01’ %查找专业为计算机但是性别不为男的学生的信息 USE PXSCJ GO SELECT * FROM XSB WHERE 专业=‘计算机’ EXCEPT SELECT * FROM XSB WHERE 性别=‘男’ %查找总学分大于42且性别为男的学生的信息 SELECT * FROM XSB WHERE 总学分>42 INSERSECT SELECT * FROM XSB WHERE 性别=‘男’
7 视图
视图与表不同,视图是个虚表,是从一个或者多个表(视图)导出的表,视图所对应的数据不进行实际存储,数据库中只存储视图 的定义,对视图的数据进行操作时候,系统根据视图的定义去操作与视图相关联的基本表。
注意,不能把规则,默认值和触发器与视图相关联。
当视图所依赖的基本表有多个的时候,不能向该视图插入数据,同时也不能删除。因为这会影响多个基表。若一个视图依赖多个表,一次修改只能变动一个基本表中的数据。
加密:WITH ENCRYPTION
8 游标
游标提供了一种从表中检索数据并进行操作的灵活手段,游标主要用在服务器上,处理由客户端发送给服务器端的SQL语句,或者是批处理,存储过程,触发器中的数据处理请求。游标的优点在于它可以定位到结果集的某一行,并可以对该行数据执行特定操作。游标可以看做是一种特殊的指针,使用游标可以在查询数据的时候对数据进行处理。
81. 声明游标
DECLARE cursor_name [INSENSITIVE][CSROLL] CURSOR FOR select_statement [FOR {READ ONLY|UPDATE[OF column_name[,...n]]}] [;]
8.2 打开游标
8.3 读取数据
8.4 关闭游标
%定义游标,然后打开游标,输出行数 DECLARE XS_CUR CURSOR LOCAL SCROLL SCROLL_LOCKS FOR SELECT 学号,姓名,总学分 FROM XSB FOR UPDATE OF 总学分 OPEN XS_CUR SELECT '游标的数据行数'=@@CURSOE_ROWS %读取数据 FETCH FIRST FROM XS_CURS%读取第一行 FETCH NEXT FROM XS_CUR%读取下一行 FETCH PRIOR FROM XS_CUR%读取上一行 FETCH LAST FROM XS_CUR%读取最后一行 FETCH RELATIVE-2 FROM XS_CUR%%读取当前行的上两行 SELECT 'FELECT执行结果'=@@FETCH_STATUS CLOSE XS_CUR
8.5 删除游标
DEALLOCATE 游标名