sql结构化查询语言

sql结构化查询语言（共10篇）

sql结构化查询语言 篇1

实验三 SQL语言——数据查询操作

一、实验时间

二、实验地点：317室

三、实验条件与环境

1． 通用PC机

2． PC机必须安装Windows 2000系列、Windows XP系列或Windows NT操作系统平台

3． PC机还须安装Microsoft SQL Server 2000 任意一版本（个人版、标准版、企业版）

4． 最好有一台公用的SQL Server 2000 服务器

四、实验目的1． 通过上机实验进一步加深对SQL查询语句的基本语法格式的掌握

2． 通过上机实验掌握单表查询的实现过程

3． 通过上机实验掌握多表关联查询的实现过程

4． 通过上机实验掌握嵌套查询的实现过程

5． 通过上机实验掌握集函数与谓词在Select语句中的使用

6． 通过上机实验加深了解Select语句与关系代数运算（如选择、投影、连接、除法等）两者之间的联系与区别

五、实验要求：

1． 本实验所有实验内容要求在规定时间（2课时）内完成2． 实验内容中涉及到的各查询操作均通过SQL语言来实现

3．实验完成后需要书写实验报告

六、实验内容：

1．查询为工程J1供应零件的供应商号码SNO

2．查询为工程J1供应零件P1的供应商号码SNO

3．查询为工程J1供应红色零件的供应商号码SNO

4．查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号JNO

5．查询至少选用了供应商S1所供应的全部零件的工程号JNO

6．找出所有供应商的名称和所在城市

7．找出所有零件的名称、颜色和重量

8．找出使用供应商S1所供应零件的工程号码

9．找出工程项目J2使用的各种零件的名称及其重量

10．找出上海厂商供应的所有零件号码

11．找出使用上海产的零件的工程名称

12．找出没有使用天津产的零件的工程号码

13．找出重量最轻的红色零件的零件编号PNO

14．找出供应商与工程所在城市相同的供应商提供的零件号码

15．找出所有这样的一些＜CITY，CITY，PNAME＞三元组，使得第一个城市的供应商为第二个城市的工程供应零件的名称为PNAME

16．重复第15题，但不检索两个CITY值相同的三元组

17．找出供应商S1为工程名中含有“厂”字的工程供应的零件数量总和

18．找出为工程供应零件的总数量不低于500的供应商号码及供应总数量，结果按供应商号码分类并且按供应总数量降序排列

七、实验总结：

数据查询是一种极为重要的面向数据库的数据操纵方式。实验完成后，请同学们总结一下SELECT语

句实现各种类型查询任务的一般过程和操作实现方法，并对利用SELECT语句实现各类查询任务的解题规律与方法作一总结。

sql结构化查询语言 篇2

XML是一种半结构化数据描述语言, 它克服了传统Web描述语言HTML只能够表达数据的内容, 无法表达Web数据的结构特征, 不便于半结构化数据查询这一不足, 正在逐步替代HTML, 成为新一代Web数据描述与数据交换标准。如何有效存储、处理XML文档和从大量XML文档中检索有用信息, 已成为数据库研究领域的一个重要研究课题。本文以SQL2005来处理对XML半结构化数据的查询及优化。

(二) 系统描述

系统的基本结构如图1所示

XML文档管理器提供应用程序以结点和全文档方式存取文档的机制, 它负责检查文档的有效性;查询管理器提供基于离散模式匹配查询的实现, 具体做法是将匹配查询转换成CSP问题;模式管理器维护系统的模式集, 由于式本身也可表达为树状模型, 因此, 它本身的存储也与XML文档的存储采用一样的方式。

(三) 半结构化XML数据查询

对Web上大量半结构化数据的访问成为近年来数据库界研究的一个热点。半结构化的数据通常没有严格的类型定义, 描述数据结构的信息包含在数据当中。为此, 在很多研究中采用了基于有向图的半结构化数据模型, 按照这样的模型, 半结构化数据可以用标注的有向图表示, 图中节点表示对象, 边表示对象之间的联系。借鉴传统数据库系统中模式的概念可以定义并建立半结构数据的模式, 在此基础上实现查询的表达和查询处理。

采用半结构化的数据模型处理Web数据时, 首先要从文本格式的数据中抽取结构信息。由于目前在web上普遍使用的HTML是面向显示的, 主要用于描述数据的格式、显示样式以及数据的位置等, 无法直接定义数据的结构, 因此从基于HTML的Web数据中抽取结构信息的难度很大, 通常只能根据数据的格式 (段落、字体、缩进距离等) 并结合启发性规则来进行推测, 在可抽取的数据类型、正确性以及通用性等方面都不够理想。

(四) SQL 2005查询XML数据优化

SQLserver2005为XML数据处理提供了广泛的支持, 一是XML数据可以自然地存储在XML数据类型列中, 管理XML本原数据格式提供以下功能:

l.创建XML索引, 提高数据的访问效率;

2. 支持XQuery语法, 让传统的SQL语言增加了访问XML数据的功能。二是利用CLOB或者varchar类型来保存XML数据。

实验创建了对应的两个表 (图2) PublisherXML表的的book字段采用xml类型, Publisher表的book字段采用varchar类型, 定义如下:

(五) 查询优化

在XML类型的列上进行查询效率低下的主要原因有:

1. XML类型的数据往往非常的大, 结构比较复杂, 直接利用XQuery尽完成查询需要复杂的结构连接计算, 这要反复的进行磁盘I/O而耗费大t的时间;

2.建立的索引不恰当, SQL server 2005支持在XML类型上建立XPath、ProPerty和value三种索引, 应该根据不同的查询需求建立相应的索引。

针对这两个问题, 采取以下两个措施进行改进:

l.对包含XML列的表建立附加列或者附加表, 并建立索引。

如果针对少量元素和属性值进行查询, 可以将这些数据提升到关系列中, 即建立附加列。当检索整个XML实例, 只对一部分XML数据进行查询, 并且将被提升的列编入索引。附加列是同一表中的计算列。如:

(1) 为PublisherXML表book列的title元素创建用户自定义函数:

Create function udf_get_book_title (@xDataxml)

(2) 向PublisherXML表添加由上述自定义函数创建的计算列:

Alter table PublisherXMLadd title as dbo.udf_get_book_title (book)

(3) 在publisherXML表的计算列title上建立索引

Crete index idxTitle on PublisherXML (title)

2.对查询习惯进行分析, 根据不同的查询习惯产生对应的XML索引类型。

定义查询窗口, 利用XQuery对XML类型数据进行查询的总次数为t, 某种类型的查询为q (xpath、property或者value) , 则查询窗口w=q/t (<=w<=l) 。

算法:根据查询类型的不同分别建立XPath、property或者value索引以提高查询效率, 输入:XML数据的查询和一个包含XML列的表;输出:Xpath、property、value索引。

(六) 结束语

在XML数据源中对大量的信息进行有效地查询, 对用户而一言, 就显得非常重要, 它能帮助用户更方便更有效更快捷地获取信息。在SQLserver2005中存放本原xML数据, 对这类数据的优化是一个新的课题。该文分析了传统优化方法在对基于SQLServer2005的xML数据查询优化过程中存在的误区, 给出了利用建立附加表或者附加列和查询窗口的方法和策略进行优化, 有效提高了查询XML数据的效率。

摘要：SQLScrver2005关系数据库管理系统提供了强大的管理XML数据的功能, 但它用传统的通过建立索引等方式进行查询效果并不理想。文章分析了SQLserver2005在查询XML数据过程中低效的原因, 提出了通过建立附加表或者附加列以及相应索引、利用查询窗口等措施来优化查询。实验结果表明, 该方法能有效提高查询XML数据的效率。

关键词：半结构化,XML数据,附加表

参考文献

[1]孟小峰, 王宇, 王小峰.XML查询优化研究[J].2006.软件学报.

[2]覃遵跃, 蔡国民, 黄云.SQL Server2005查询XML数据优化研究[J].东莞理工学院学报.

SQL查询和清除重复数据 篇3

选择重复，消除重复和选择出序列

有例表：emp

emp_no name  age

001     Tom   17

002     Sun   14

003     Tom   15

004     Tom   16

要求：

列出所有名字重复的人的记录

(1)最直观的思路：要知道所有名字有重复人资料，首先必须知道哪个名字重复了：

select name from emp   group by name  having count(*)>1

所有名字重复人的记录是:

select * from emp

where name in (select name from emp group by name having count(*)>1)

(2)稍微再聪明一点，就会想到，如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的 ，就有

select * from emp where (select count(*) from emp e  where e.name=emp.name) >1

--注意一下这个>1，想下如果是 =1，如果是 =2 如果是>2 如果 e 是另外一张表 而且是=0那结果 就更好玩了:)

这个过程是 在判断工号为001的 人 的时候先取得 001的 名字（emp.name） 然后和原表的名字进行比较 e.name

注意e是emp的一个别名，

再稍微想得多一点，就会想到，如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求：

select * from emp

where exists

(select * from emp e  where e.name=emp.name and e.emp_noemp.emp_no)

此思路的join写法：

select emp.*   from emp,emp e

where emp.name=e.name and emp.emp_noe.emp_no

/*  这个语句较规范的 join 写法是

select emp.* from emp inner join emp e  on emp.name=e.name and emp.emp_noe.emp_no

但个人比较倾向于前一种写法，关键是更清晰  */

b、有例表：emp

name  age

Tom   16

Sun    14

Tom   16

Tom   16

----------------------------------------------------清除重复----------------------------------------------------

过滤掉所有多余的重复记录

(1)我们知道distinct、group by 可以过滤重复，于是就有最直观的

select distinct * from emp  或  select name,age from emp group by name,age

获得需要的数据，如果可以使用临时表就有解法：

select distinct * into #tmp  from emp

delete from emp

insert into emp select * from #tmp

(2)但是如果不可以使用临时表，那该怎么办？

我们观察到我们没办法区分数据（物理位置不一样，对 SQL Server来说没有任何区别），思路自然是想办法把数据区分出来了，既然现在的所有的列都没办法区分数据，唯一的办法就是再加个列让它区分出来，加什么列好？最佳选择是identity列：

alter table emp add chk int identity(1,1)

表示例：

name age chk

Tom  16  1

Sun   14  2

Tom  16  3

Tom  16  4

重复记录可以表示为：

select * from emp where (select count(*) from emp e where e.name=emp.name)>1

要删除的是：

delete from emp

where (select count(*) from emp e  where e.name=emp.name and e.chk>=emp.chk)>1

再把添加的列删掉，出现结果，

alter table emp drop column chk

(3)另一个思路：

视图

select min(chk) from emp group by name having count(*) >1

获得有重复的记录chk最小的值,于是可以

delete from emp where chk not in (select min(chk) from emp group by name)

写成join的形式也可以:

(1)有例表：emp

emp_no  name  age

001      Tom   17

002      Sun   14

003      Tom   15

004      Tom   16

◆要求生成序列号

(1)最简单的方法，根据b问题的解法：

alter table emp add chk int identity(1,1) 或

select *,identity(int,1,1) chk into #tmp from emp

◆如果需要控制顺序怎么办？

select top 100000 *,identity(int,1,1) chk into #tmp from emp order by age

(2) 假如不可以更改表结构，怎么办？

如果不可以唯一区分每条记录是没有办法的，在可以唯一区分每条记录的时候,可以使用a 中的count的思路解决这个问题

select emp.*,(select count(*) from emp e where e.emp_no<=emp.emp_no)

from emp

order by (select count(*) from emp e where e.emp_no<=emp.emp_no)

sql结构化查询语言 篇4

Access课堂练习六

姓名 学号

SQL命令查询书面练习

已知教师管理系统中的2个表分别为：

教师（教师编号，姓名，性别，部门编号，出生时间，工作时间，职称，基础工资，岗位工资，绩效工资）部门（部门编号，部门名称，担任职务，办公室电话(0571-6018845)，业余爱好）

完成下列问题的SQL命令的表示：

1.查询教师表中出所有教师的姓名、性别和职称。

2.查询所有教师的姓名，性别，部门名称，担任职务和职称。3.查询教师表中姓名，性别，工龄和职称。4.查询教师表中姓名，性别，职称和工资总额。5.查询所有教授的姓名，部门名称和担任职务。6.查询55岁及上的教师姓名和性别。

7.查询教师表中工龄在10年到20的教师信息。

8.查询部门名称为“人事处”、“英语系”、“计算机系”的教师信息。9.查询按基础工资排序的教师信息。10.查询按职称分类的平均绩效工资。11.查询业余爱好中含有“运动”的教师信息。

12.查询部门表中办公室电话升级后(0571-86018845)的部门信息。

1.select 姓名,性别,职称 from 教师

2.select 姓名,性别,部门名称,担任职务,职称 from 教师,部门 3.select 姓名,性别,year(date(0))-year(工作时间)as 工龄 from 教师

4.select 姓名,性别,职称,基础工资+岗位工资+绩效工资 as 工资总额from 教师 5.select 姓名,部门名称,担任职务 from 教师,部门where 职称=”教授” 6.select 姓名,性别 from 教师 where year(date(0)-year(出生时间))>=55 浙江大学城市学院

Access课堂练习

SQL语言（模版） 篇5

1.数据定义：

a)Create 创建

b)Drop 删除对象

数据表

 表的创建：create table 表名(字段名1 类型 primary key注：主键,字段名2 类

型,……, check 表级约束)

例：S(Sno,Sname,Sex,Age)

Create table S(Sno char(8)primary key, Sname char(4), Sex char(1), age smallint)说明：

文本->char()

数值->numeric(m,n), int, smallint

日期/时间->date

备注->memo Create table C(Cno char(6)Primary key, Cname char(10), Tno char(6)) 删除表

Drop table 表名

例：drop table S

 修改表结构

Alter table 表名 add/drop/alter 字段名

例题

S表 添加出生年月

Alter table S add Borndate date

S表删除年龄

Alter table S drop Age

S表 Sno 文本长度为6

Alter table S alter Sno Char(6)

2.数据查询：

Select 字段名 From 表名 [Where 条件 ] [Group by分组字段名 [ having 分组条件] ]

[order by 排序字段[asc/deac]]

例：查询女同学的姓名

Select Sname from S where Sex=”女”

查询学生表中的所有内容

Select * from 学生

查询男生和女生的人数（按性别统计人数）

Select 性别,count(学生编号)from 学生 group by 性别

查询每门课程的平均分

Select 课程编号,avg(成绩)from 选课成绩 group by 课程编号

查询平均分大于85分的课程

Select 课程编号from 选课成绩 group by 课程编号 having avg(成绩)>85

查询教师信息，要求结果按照年龄排序

Select *from 教师 order by 工作时间 asc 按性别对教师工作时间排序查询

Select *from 教师 order by 性别,工作时间

3.数据操纵：——> 针对表中记录

 插入数据

Insert into 表名(字段名列表)values(字段值列表)例 Insert into S(Sno,Sname)values(“155161”,”张三”) 修改/更新数据

Upate 表名 set 字段值=……

例 update 学生 set 年龄=年龄+1

 删除数据

Delete from 表名 [where 条件]

例：Delete from 选课成绩 where 成绩<70 Delete from S

巧用SQL的查询技术 篇6

在日常管理中,作为系统管理员或系统开发人员经常同各种数据库打交道,比如SQL SERVER、MY SQL、ACCESS、ORACLE等数据库进行查询操作,而SELECT语句作为SQL中的核心,具有非常丰富的成份和强大的查询功能[1]。在SQL SERVER中对数据的查询或统计都是通过SELECT语句来完成的,而在软件开发过程中有关数据库的查询操作都是借助SELECT查询技术。

1 SELECT语句格式

SELECT的完整语句结构比较复杂,这里只列举它的主要子句。SELECT语句的主要子句格式如下[2]:

其中,[]表示可选项,SELECT子句是必选的,其他子句都是可选的。

2 查询技术应用

2.1 聚合与分组

在SQL中聚合是统计的基础,常用的聚合函数有SUM(求和)、COUNT(计数)、AVG(平均值)等,在查询统计时可以配合WHERE子句、GROUP BY子句、HAVING子句等来应用[3]。

在每个学期或每学年开学初需要统计学生的交费注册情况,如果学籍状态为未注册表示该学生没有交清学费,要求统计各专业男女学生注册人数。

学生基本情况表(xsjbqkb):学号,姓名,性别,班级,专业,院系名称,学籍状态,年级

Select年级,专业,性别,count(*)注册人数from XSJBQKB where学籍状态='注册'and(年级='2008'or年级='2009'or年级='2010')group by年级,专业,性别

从上面语句执行的结果可以看出,一个专业占用两行,男生一行,女生一行,而且该年级的专业有多少人还是需要计算,是否都已注册,因此对查询进行改进。

SELECT年级。专业,COUNT(*)注册人数,COUNT(DECODE(性别,'男',学号,NULL))男生,COUNT(DECODE(性别,'女',学号,NULL))女生FROM XSJBQKB WHERE学籍状态='注册'AND(年级='2008'OR年级='2009'OR年级='2010')GROUP BY年级,专业

其中DECODE(性别,'男',学号,NULL)表示当“性别”为“男”的有多少个学生的学号(即人数)。DECODE函数是ORACLE PL/SQL是功能强大的函数之一,目前还只有ORACLE公司的SQL提供了此函数,其他数据库厂商的SQL实现还没有此功能[4]。

2.2 交集与差集查询

以两表为例,有教学任务表(jxrwb)、教学任务临时表(jxrwlsb)分别包含以下字段。

教学任务表(jxrwb):学年,学期,年级,课程代码,专业,课程名称,班级

教学任务临时表(jxrwlsb):学年,学期,年级,课程代码,专业,课程名称,班级

要求找出某一学期教学任务表中存在而教学任务临时表中不存在的课程记录。

从要求上看,这个不是一个简单的查询,需要查找出两个集合中第一个集合与第二个集合元素的差集。

SELECT*FROM(SELECT学年,学期,年级,专业,课程代码,班级名称FROM JXRWB WHERE学年='2010-2011'AND学期='2'GROUP BY学年,学期,年级,专业,课程代码,班级名称)A WHERE NOT EXISTS(SELECT*FROM JXRWLSB B WHERE A.学年=B.学年AND A.学期=B.学期AND A.年级=B.年级AND A.专业=B.专业AND A.课程代码=B.课程代码AND B.学年='2010-2011'AND B.学期

在查询中,使用EXISTS和NOT EXISTS引入子查询可以轻松查找两种集合中元互素的交集与差集,其中EXISTS指定一个子查询,检测行的存在。两个集合的交集是同属于两个集合的元素,两个集合的差集是只属于第一个集合的元素[5]。

2.3 连接查询

通过连接运算符可以实现多个表的查询。在关系数据库中,表建立时各数据之间的关系不必确定,常把一个实体的所有信息存放在一个表中。当查找数据时,通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性,他们可以在任何时候增加新的数据类型。为不同实体创建新的表,尔后通过连接进行查询。

连接类型可分为三种:内连接、外连接和交叉连接。但是无论哪种连接都不能对text、ntext和image数据类型列进行直接连接,但可以对这三种列进行间接连接。

2.3.1 内连接

内连接查询操作列出与连接条件匹配的数据行,它使用比较运算符比较被连接列的列值。例,下面使用等值连接查询XUESHENGB和JINGUANB来自同一城市的学生信息。

SELECT*FROM XUESHENGB AS A INNER JOINJINGUANB AS P ON A.CITY=P.CITY

2.3.2 外连接

内连接返回的查询结果集合仅仅是符合查询条件(WHERE条件或HAVING条件)和连接条件的记录行。而外连接是返回查询结果集合中不仅包含符合连接条件的行,还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行[6]。

如下面使用左外连接将学生成绩和学生信息连接起来:

下面使用全外连接将专业表(ZHUANYEB)中的所有专业以及课程表(KECHENGB)中的所有课程,以及其他信息。

2.3.3 交叉连接

交叉连接返回被连接的两个表所有数据行的笛卡尔积,返回的结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数,交叉连接不带WHERE子句[7]。

例,图书分类表(TSFLB)中有10类图书,而出版社表(CPSB)中有20家出版社,则下列交叉连接查询到的记录数将等于10*20=200行。

2.4 联合查询

通常在做报表时发现数据表的记录行太长,不能在一行显示所有记录,需要将一行变成两行或多行来进行打印,那么,就要用到UNION或UNION ALL指令。

Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;

Union All:对两个结果集进行并集操作,包括重复行,不进行排序;

UNION和UNION ALL的区别是,UNION会自动压缩多个结果集合中的重复结果,而UNION ALL则将所有的结果全部显示出来,不管是不是重复[8]。

例,有数据表(lmdjb)字段:ID,BH,LB1,YW1,YW2,YW3,YW4,LB2,YW11,YW21,YW31,YW41

查询BH中含有字符“A”的所有行,结果集合将一行分成两行显示。

3 结束语

SELECT语句作为SQL的核心,其强大的功能受到各类程序员和数据库管理员的青睐。本文从分组、聚合、交集、差集、连接、联合查询方面介绍SELECT语句在查询方面的应用技巧,希望能提供给大家一些参考。

参考文献

[1]李方元,毕好昌.SQL查询技术在报表统计中的应用[J];电脑开发与应用,2006,(9).

[2]郑英姿.谈SELECT语句的使用[J];中国高新技术企业,2009,(4).

[3]彭学君.SELECT语句在SQL中的应用实例[J].电脑学习,2002,(1).

[4]郭郑州,陈军红.Oracle完全学习手册[M];北京:清华大学出版社,2010.153-183

[5]黄志真,杨武.数据库SQL查询优化方法的研[J].兵工自动,2001,(1).

[6]王翀,姚东辉,林泽东.连接查询在单表中的妙用[J];福建电脑,2009,(7).

[7]云廷进.交叉查询[N].电脑报,2004-05-17.

SQL语言学习工作总结 篇7

暑假过的真快总感觉昨天才刚放假，还想着这个暑假怎么过时，暑假就已经去了。

这个暑假一开始我们看了浙大的sql的视频，老师给我们的要求是只要看一遍就行。刚开始看的时候还能听懂一些，看到后面的时候基本就听不懂了，幸好老师对我们的要求不高，于时只好硬着头皮继续。

sql的视频其实总的说来无非讲的是对数据库、表、字段等的创建、修改和删除。我们不懂的也就是怎样使用代码来实现这些操作。我们现在只要对这些有了解就够了，以后还会有锻炼机会的。

之后我们参照着原代码做了《学生信息管理系统》，大概用了七天的时间。刚开始做的时候，觉得这个工程肯定会挺复杂的。但是做着做着感觉也就是那么回事。无非就是对几张表的操作。做这个工程最大的收获就是对刚学的sql知识有了一次熟练的机会，巩固了刚学的知识。也可以说是趁热打铁吧。

然后米老师给了我们更大的考验，让我们做《机房收费系统》，而且这次和上一次的《学生信息管理系统》不同的是，这次我们除了只有一个程序和数据库外，没有一个字的原码。

在得知我们可以把这次的工程做成任何样子时，我打算做一个类似于网吧那样的收费系统。也是一个cs系统。在网上查了一些主机如何与客户端通信的资料后，就开工了。虽然遇到了一些困难，但是到最后都一一解决了这三次的学习困难程度可以说是一次比一次高，米老师也是用心良苦呀。

经过这个暑假，对sql已经有了深一步的了解，还有就是将米老师的学习方法又应用了一次。可谓是受益匪浅。

sql结构化查询语言 篇8

一．实习目的及要求：

本次实验了解SQL的数据定义语句：

（1）创建数据库、表、索引

（2）增加、删除表结构

（3）修改表结构

二 实习内容：用SQL语句建立SPJ数据库(见书P74，5)，数据库名取自己的名字，如：张

三。

2在建好了的SPJ数据库中建立S、P、J、SPJ共4个表的表结构。

要求：(1)为属性选择合适的数据类型；

(2)为SPJ表增加一供应日期列，列名为SUPDATE，日期型；

(3)定义每个表的主码;

(4)定义上述4个表的表级约束：定义SPJ、S、P、J表之间的FOREIGN

KEY（外码）约束。

(5)为S、P、J表的SNAME、PNAME、JNAME列定义UNIQUE约

束；约束名分别命名为UQ_SNAME, UQ_PNAME,UQ_JNAME;

(6)实现DATE属性的Check（检查）约束: SUPDATE

(7)删除P表PNAME列的唯一性约束

(8)将P表中PNAME列的数据类型改为可变字符串型；用SQL语言在SPJ表上建立一个唯一性索引。

要求：按属性QTY的降序建立索引。使用企业管理器(Enterpriser Manager)，在数据库SPJ上建立一个关系图。

检查SPJ、S、P、J四表的关系是否正确。

sql结构化查询语言 篇9

XML(eXtensible Markup Language,可扩展标记语言)[1]是一种通用标记语言,能够非常灵活地标记Web上多种不同数据源中的信息,具有开放性、灵活性、易读性和平台无关性等特点。随着基于Internet商业应用的迅速发展,XML已经成为Internet上数据表示和数据交换的标准格式,就现状和发展趋势来看,关系数据库经过多年发展已经非常成熟,在商业数据管理领域中占据着主导地位。XML在过去几年的时间里得到了极大地发展,它从最开始的为文档保留语义的标记语言变成了异构系统间交换数据的格式。不仅如此,越来越多的研究项目把纯XML数据库作为他们的研究对象[2]。随着有效地存储和查询XML的需求不断升温,这样就涌现出许多查询语言来解决存在的一系列问题。其中最有代表性的XML查询语言就是XQuery。

1、XQuery优化背景

过去几年里,在以文档为中心和以数据为中心的环境里,作为信息的一种格式化语言,XML迅速得到了广泛的欢迎。基于XML的标准(XML-based standards)爆炸似地增长,表明XML受到了许许多多不同技术联盟(Communities)的关注。现在,许多应用程序使用XML来传送消息,如SOAP、XML-RPC消息;或者作为数据的永久性存储,如XML数据库、内容管理系统。一个以XML为基础的Web,将替代以HTML为基础的Web,听起来不再是幻想。

由于Web数据的特点,传统的数据库技术和信息检索技术已经不能满足用户的要求,而随着存储在XML文档中信息量的增长,能有效并且高效地存取XML信息就变得越来越重要。要做到这一点,必须要有一个让你能够准确地获得所需信息、更新XML数据源中数据的可表达的查询语言。XQuery正是这样的语言。有了相应的查询语言,但在XML数据查询处理研究中,需要关注下列问题:

(1)如何定义完善的查询代数。

(2)复杂路径表达式是XML查询语句的核心,必须将复杂、不确定的路径表达式转换为系统可识别的、明确的形式。

(3)XML数据信息统计和代价计算。

基于上面的各种问题,为了满足用户查询的需要,高效的查询性能对于查询来说非常重要。因此,本文主要针对XQuery的查询优化技术进行研究。

2、本文主要研究内容

本文讨论的是XQuery语言的查询优化问题。基于这样的前提,文章对问题采用XQuery到SQL的查询语言转换方案[32,33,34,35,36,37,38,39],目的不仅是实现XQuery在关系数据库中的查询处理,更具现实意义的是能够充分利用关系引擎的已有功能,降低问题难度并可提高解决问题的效果。

二、XQuery相关知识

1、XQuery语言

XQuery是一个从XML格式的文档中获取数据的查询语言,起源于XML数据查询语言Quilt[40]。

(1)查询结构和表达式

XQuery遵循一定的查询结构,一个完整的查询包含两个部分:查询序言(Query Prolog)和查询体(Query Body)。查询序言包括一系列处理查询体的定义声明;查询体是一段查询表达式,是XQuery的主体部分。当查询体需要用到名字空间、模式信息、或函数时,就需要在查询序言部分有相应的声明。XQuery表达式是XQuery中每一个元素的最高程度的抽象,并且是XQuery中的基本构建块,是XQuery查询的基础。

XQuery定义的表达式由关键字、符号以及操作数组成。通常,表达式中的操作数也是表达式。XQuery是函数化的语言。XQuery也是一种强结构化的语言,表达式中的操作数、算子、函数必须遵循已经定义好的模式。XQuery中共有七种表达式[44]:路径表达式、构造器(Constructor)、FLWOR表达式、包含算子和函数的表达式、条件(Conditional)表达式、量词(Quantified)表达式、检查和修改数据类型的表达式。

三、基于关系数据库的XQuery查询处理

1、XQuery查询技术概述

随着SQL/XML标准的出现,一些数据库管理系统通过使用XMLType表和XMLType列,将XML存储在关系数据库系统中,甚至用户通过使用SQL/XML发布函数,例如XMLElement()、XMLConcat()等,将关系数据转化成XMLType视图。但是随着技术的发展,将XML查询执行嵌入到关系数据库中已成为研究的热点,其中最有代表的就是在SQL中使用XQuery,即一种新的SQL函数XMLQuery()以及from语句的构造函数XMLTable的出现。

为了支持XMLQuery()以及XMLTable构造函数,一个很直接的方法就是在已有的处理器上再加入一个协作处理器,即将它嵌入到XQuery处理器中。但是这种方法会遭到性能问题的困扰。在这里将介绍一种新的机制,即本地XQuery编译,优化以及执行。具体来说就是将XQuery重写成SQL,然后将重写后的结果送入底层的关系优化器进行优化,最终在关系执行引擎中执行。

2、SQL/XML混合执行策略

上面讲到的XMLQuery()函数和XMLTable构造函数的重写发生在SQL查询编译期间。在SQL解析时,依据语法在内建的XQSeq()表函数中将XMLTable转化成XMLQuery()函数,然后在SQL语法分析、类型检查以及视图扩展完毕后开始进行XMLQuery()函数的重写。具体就是重写驱动程序将静态XQuery表达式进行解析,也就是在XQuery上进行静态分析和类型检查,并将它转化成本地带有XML扩展操作符的SQL数据结构,并将原先的XMLQuery()函数进行替换。若XQuery表达式不能重写成SQL,那么将XMLQuery()函数完整无缺的保留。在重写语句完成后,SQL直接进行各种转化,比如操作符树的优化、视图合并、子查询分解嵌套等,然后将结果输入到优化器中产生一个最优的执行计划。

3、XQuery重写成SQL

每一种XQuery表达式都与它能被编译成的SQL表达式相对应。每一个表达式能被转化成一个新的SQL操作符、操作符树或一个子查询块。例如FLWOR表达式重写成SQL子查询,XQuery构造表达式重写成SQL/XML函数等等。表1给出了一些常用的XML/XQuery内部SQL操作符。

上面的这些函数对于进行XQuery重写有很大的贡献。下面就分别介绍XQuery重写的内容。

(1)FLWOR表达式的重写

FLWOR表达式的重写实质上是构造一个SQL子查询成为重写结果。其中FLWOR表达式中的for语句转变成SQL语句中的from语句,而where语句重写成SQL中的where语句,order语句重写成SQL中的order语句,返回语句重写成SQL中的select列表。

(2)构造函数的重写

这种重写实质上是构造一个SQL操作符树作为重写。这棵树由SQL/XML函数组成,例如XMLElement()、XMLAttributes()、XMLPI()、XMLComment()。增强上述这些函数的功能来处理标记名。但是这些标记名的值仅仅在运行时才可用,因为仅仅在构造操作进行时重写才是必需的。

(3路径表达式的重写

这种重写实质上是构造XQExtract()SQL操作符,它是对XPath进行运算并返回XML结果。构造操作符完成后在XQExtract()操作符上对XPath进行重写,重写成SQL/XML和对象关系型原始操作符。

(4)Literals重写

这种重写实质上是重写成SQL literal,然后用Mk XQFromSQL()函数将结果进行封装。

(5)条件表达式的重写

这种重写实质上是构造一个SQL CASE操作符作为重写结果。

(6)存在量词表达式的重写

这种重写实质上是构造一个SQL EXIST/NOT EXISTS子查询。

(7)聚集函数表达式重写

这种重写实质上是构造相应的SQL聚集函数,例如min()、max()、count()等。然后将重写结果用Mk XQFrom SQL()函数进行封装。

(8)XQuery序列构造重写

这种重写实质上是构造一个新的XQConcat()操作符作为重写结果。

(9)算术、逻辑、比较运算重写

这种重写实质上就是构造相应的SQL算术、逻辑以及比较运算操作符作为重写结果。对于一般的比较运算符,将它们重写成EXISTS子查询。因为XQuery允许带有内建类型的基本算术和比较函数的多次使用,所以如果输入的类型在静态类型检查原语中有不同的内建类型可供选择,则需要构造多态SQL算术和比较运算符。

(10)范围表达式的重写

这种重写实质上是构造XQRange()操作符作为重写结果

(11)Cast和构造函数的重写

这种重写实质上就是使用底层SQL casting函数,然后用Mk XQFrom SQL()函数将结果进行封装。

(12)序列类型表达式重写

这种重写实质上是用SQL CASE操作符来构造XQ-Typ Check()操作符作为重写结果。

(13)验证表达式重写

这种重写实质上是构造内部XMLValidate()操作符作为重写结果

(14)XQuery函数/操作符重写

将XQuery函数和操作符映射成为存在的SQL函数或操作符。对于某一个XQuery函数或操作符没有等价的SQL函数或操作符相对应的情况,新的SQL操作符在RDBMS引擎中被创建,并实现相应的XQuery操作符的语义。对于fn:doc()和fn:collection()函数而言,将它们重写成SQL查询块。当然可以通过SQL/XML函数将关系视图转化成XML,从而直接查询XML,例如使用ora:view()函数。

(15)用户自定义XQuery函数重写

最常见的就是将自定义的函数重写成某一个RDBMS中的某一类内建函数。例如在Oracle中就是重写成PL/SQL函数。

4、混合执行策略的代数优化

XMLTable构造和XMLQuery()函数重写成SQL的语法转化,常导致一个复杂的SQL语句块的产生。例如每一个XML-Query()本质上都要变成带有很大一棵操作符树的一组嵌套查询块。接下来就要对这棵庞大的操作符树进行削减优化。但是没有嵌套的子查询和视图合并机制能简化这SQL语句块的结果,因此必须增强当前的代数规则,以致于用这种增强的规则来处理由XQuery重写产生的SQL函数以及操作符。

5、实验分析

本实验在Oracle 10g上运用SQL/Plus实现。

(1)运行XQuery表达式访问XML文档

首先打开SQL PLUS工具,以OE身份登录到数据库

(1)运行下面的第一个XQuery将选择所有XML文档,代码如下:

得到的结果如图1所示,返回所有XML文档的结果。

(2)接下来运行第二个XQuery将选择特定的订货单,代码如下:

则得到的结果如图2所示,且相应的执行计划如图3所示。

(3)运行第三个XQuery,将列出包含特定部件ID的所有订货单,代码如下:

则得到的结果如图4所示,相应的执行计划如图5所示。

(未使用索引的执行计划)

(4)运行第四个XQuery将列出特定订货单中每行项目的描述,代码如下:

则得到的结果如图6所示,相应的执行计划如图7所示。

(2)性能分析

对比图5和图3-14,可以明显地看到使用索引前后,Rows、Bytes以及Cost(%CPU)的值相差是很大的。关注SELECT STATEMENT行,在图5中Rows值是2,而在图3-14中相应的值是1,同样Bytes值在图5中的值是36770,而在图3-14中的值是226,并且Cost(%CPU)值在图5中25,而在图3-14中只有6。从各方面的参数可以清楚地看到使用索引后的值都偏小。说明在使用索引后可以大大改进查询性能。

四、结束语

本章就文章所针对的问题加以分析,对基于关系数据库的XQuery查询进行阐述,主要运用SQL/XML来进行查询和查询优化。首先给出XQuery查询技术的一般概念,其中重点对协处理器执行方案进行阐述,同时给出相应的XQuery查询实例,最后给出一种SQL/XML混合执行策略,并通过实验进行性能的分析。

参考文献

[1]Extensible Markup Language(XML)1.0(Second Edi-tion).W3C Reconmmendation(6 October2000)[EB/OL].http://www.w3.org/TR/REC-xml

[2]Jason Mchugh,Jennifer Widom.Query Optimization for XML[C].In:Proceedings of the 25th International Conference on VeryLarge Data Bases.315~326

[3]刘政怡,谢荣传,李伟.XQuery查询语言的规范化[J].微机发展,2003,13(6),50~52,56.

[4]李延东,李相枢,陈鹏.基于XSD实现XML与关系数据库之间的数据转换[J].计算机应用,2003,24(3),97~99.

[5]郑仕辉,周傲英等.基于SQL的XML查询的有效实现[J].计算机研究与发展,2001,38(4),50~58;422~429.

[6]万常选,刘云生,徐升华,林大海.基于X-RESTORE查询XML视图[J].小型微型计算机系统,2004年,25(10),1000~1220

sql结构化查询语言 篇10

为了及时、准确地完成教育部就业签约数据及时统计与上报工作, 笔者结合工作实际使用SQL查询技术和数据库原理对解决数据上报过程中毕业生信息不匹配问题、如何高效地维护好毕业生就业签约数据、如何准确地导入符合上报标准的数据进行了有益地探索与实践, 对各高校在毕业生就业签约数据上报工作有一定的借鉴与参考价值。

2 SQL查询技术

通过T-SQL的查询语句SELECT, 可以从表或视图中迅速方便地检索数据。SELECT语句是T-SQL的核心, 它既可以实现对单表的数据查询, 也可以完成复杂的多表连接查询和嵌套查询, 其功能十分强大。利用SQL的自连接运算, 可以快速比较来自两个不同数据表的信息, 获得所需的查询结果。使用聚合函数COUNT () 可以迅速取得表的数据记录的相关统计信息, 实现快速统计。使用GROUP BY子句结合聚合函数COUNT () 的使用可以实现分类别统计数据信息。使用ORDER BY子句可以根据一个或多个字段的数据进行排序查询输出结果。

3 基于毕业生就业签约数据库的构建

为了便于数据的上报与统计, 以贵州省为例, 在SQL Server2005中创建一名为bys2014的数据库, 分别导入以下几个数据表:高校预计毕业生信息表 (YJBYS) 、毕业生就业签约数据表 (BYSJYSJ) 、地区公共代码表, 以上三个数据表可以从全国就业监测系统下载。毕业生就业监测数据 (BYSJYFA) 可通过贵州省就业信息管理系统下载, 全国各地身份证省市代码表 (SFZDM) 通过网上搜集整理。这些下载的数据, 如果是DBF格式, 需要用Visual Fox Pro转换为电子表格格式。

在实际应用中, 可以根据各地方就业管理部门制定的待就业毕业生报到单位方案, 对地区公共代码表中的“派遣地址”作适当修改后使用。最后将上述文件, 通过SQL server的导入工具, 导入bys2014数据库中备用。

4 SQL查询技术在工作中的应用

4.1 查找考生号与身份证号、姓名不匹配的数据

为统一贵州就业信息管理系统与全国就业监测系统中毕业生的数据信息, 需要查找出两个系统中不匹配的相关数据, 匹配的原则是以毕业生的考生号为主要关键字段、以毕业生的身份证号、姓名、专业进行数据连接运算。具体操作的SQL语句如下:

读者可以结合实际需要修改上述语句, 按照一定的规则, 查找考生号匹配, 但姓名不一致、考生号、姓名匹配, 但专业不一致等信息。这些错误信息的快速查找将有助于及时查找原因, 并更正不相符的数据。

4.2 毕业生生源地信息的高效快速录入

借助于“全国就业监测系统”中导出的“地区公共代码”数据表和全国各地身份证省市代码表。根据毕业生的身份证号信息, 结合SQL查询技术, 可以快速地得到毕业生生源所在地的代码信息, 经实践准确率达95%以上。部分毕业生因户口迁移, 需要进一步确认。这种方法需要确保毕业生的身份证信息准确无误。毕业生的准确身份证号可由各高校教务管理部门通过学籍信息网导出后使用, 也可采用“全国就业监测系统”中下载的当年预计毕业生数据表。

具体实现的语句如下:

上述语句执行后, 将自动以毕业生的身份证号前六位数字作为毕业生的生源地代码, 并筛选出生源地代码信息需要进一步核实的学生名单。这部分毕业生的生源地信息可以根据全国各地身份证省市代码表进行检索查询到, 经分析原因是:原有的生源地行政区划发生了变更导致。这部分信息可依靠人工根据正确的行政区划在地区分共代码数据表中可以查询。

4.3 毕业生派遣信息高效维护的实现

待就业毕业生派遣方案的批量数据维护, 根据规定待就业毕业生的报到证开具到毕业生的生源所在地。具体思路如下:首先根据毕业生生源所在地代码, 在公共代码数据表中检索匹配的派遣单位名称。根据检索到的派遣单位名称, 更新待就业毕业生就业数据库中的派遣单位名称字段内容、更新报到证编号、报到起始时间等信息。

具体查询与更新语句如下:

本例中生成的BYS1数据表, 可根据数据上报格式的要求, 进行适当筛选处理后通过SQL Server中的导出数据功能, 导出为电子表格文件, 根据需要进行技术处理后, 可直接上报全国就业监测系统。

4.4 毕业生就业去向的统计

在SQL数据库中新建一视图, 并以“毕业生就业去向统计”作为视图文件名为保存。输入如下查询语句, 可根据毕业去向代码, 得出毕业生的就业去向统计情况。

5 结语

利用SELECT表达式可以迅速地获得简单或复杂的查询结果, 而包含一对多、多对多的表间连接结果也能快速地产生, 而且这些查询结果均可进行搜索、分组及统计。这些功能的实现只需要一个SELECT表达式即可完成, 不仅效率高, 易修改、简单易用。在毕业生就业数据管理实践工作中, 能大幅提高工作效率, 起到事倍功半的作用。

参考文献

[1]关于调整全国普通高等学校毕业生就业方案数据结构及标准的通知[教学司函 (2014) 1号].

[2]贵州佰仕佳信息工程有限公司贵州省大中专毕业生就业信息管理系统使用说明书 (学校就业指导中心版) .

[3]郑阿奇主编.SQL Server教程[M].北京:清华大学出版社, 2005:70.