结构化查询语言sql

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

篇1：结构化查询语言sql

SQL结构化查询语句

一、数据定义功能

创建表：Createtable 表名（字段名……）修改表：Altertable表名

删除表：Droptable表名

二、数据操纵功能

数据插入：Insertinto 表名values(…，…，)数据更新：Update表名Set表达式 Where 条件 数据删除：DeleteFrom 表名Where 条件

三、查询功能

SELECT*FROM表名

WHERE条件

ORDERBY 字段排序

GROUPBY 字段分组

函数：SUM()AVG()COUNT()MAX()MIN()去向：

INTOTABLE表

INTOCURSOR临时表

INTOARRAY数组

篇2：结构化查询语言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语

篇3：结构化查询语言sql

关键词：运动目标,时空查询语言,SQL

运动目标时空查询语言主要有两种实现途径。一种是独立于SQL标准进行时空查询语言设计与开发,这种方法开发难度和工作量都比较大,一般用于初级的实验系统。另一种是基于SQL的对象关系特性进行时空扩展,这种方法可以利用现有SQL强大的关系操作,且容易和对象关系型数据库集成[1]。

1 SQL

SQL Multimedia(简称SQL/MM)是结构化查询语言SQL针对多媒体应用的规范。SQL/MM国际标准主要包含空间拓展(Spatial)、全文索引(Full-Text)、静态图像(Still Image)和数据挖掘(Data Mining)等针对不同领域的内容。SQL-2008标准在这四个领域定义新的结构化查询类,使得空间等四各方面的SQL查询效率大幅提高,并使基于SQL/MM的查询更加规范、兼容性好且易于管理。特别是空间拓展支持包括点、线、面等二维空间数据,以及部分三维拓展功能和不同空间参考坐标系的支持。并且在几何操作、方位坐标、拓扑关系等方面都提供了强大的支持。

2 基于SQL的时态和空间查询语言

随着SQL新标准的修订,新特性和新技术的引入,SQL支持用户自定义类型,以便用它来获取和操纵复杂数据。SQL的这种可扩展特性为特殊领域的非结构化数据应用提供了支持,比如时态数据和空间数据等。

2.1 基于SQL的时态查询语言

SQL/Temporal[2]是对SQL标准进行时态扩展形成SQL的建议标准。SQL/Temporal是在SQL标准的基础上,增加了NOSE-QUENCED、VALIDTIME和TRANSACTIONTIME等关键词[3] [4]。时态查询语言是基于常用时间类型的查询语言,并且时间模型不完善。因此,该时态查询语言的功能非常有限,离实用还有很长一段距离。

2.2 基于SQL的空间查询语言

空间查询语言的基本要求是,采用更贴近人们对空间理解的概念,为空间数据提供更高层次的抽象。OGC[5]标准对SQL进行了空间特性的扩展,制定了空间数据类型及其相关的运算,它们对空间数据的应用具有指导意义。利用SQL的对象关系特性,把OGC指定的空间数据类型及其对应的空间操作函数作为SQL的空间扩展,使之可以进行空间数据查询和分析。OGC标准,空间操作分成三类:基本函数、拓扑/集合运算和空间分析。

3 基于SQL扩展的运动目标时空查询语言

因为对于运动目标时空查询来说,除了基本的时态、空间查询之外,更重要的是时空联合查询。从上述基于SQL扩展的时间和空间查询语言的总结和分析来看,二者都不能满足运动目标时空数据查询的要求。所以,需要在SQL、时态和空间查询语言基础上,针对运动目标时空查询的特点进行时空谓词扩展。

3.1 运动目标时空查询的分类

运动目标时空查询中常用的查询方式主要有四类:即Who(What)、When、Where和How查询,简称为4W查询。

3.1.1 Who查询——已知时间和空间条件或运动状态,查询运动目标

1) 时间点查询:Qwho=(S,t) ,其中S是一个空间查询条件,即一个空间窗口,查询t时刻与空间窗口相交的所有运动目标。

2) 时间段查询:Qwho=(S,t1,t2) ,如果S是n维空间的超立方体,S和时间区间 [t1,t2] 构成n + 1维的超立方体。

3) 移动查询:Qwho=(S1,S2,t1,t2) 是指运动目标的空间属性在t1和t2时刻分别满足S1和S2,移动查询也称为时空隧道查询。

4) 最邻近查询:QKNN=(D,t) ,即查询最邻近的n个目标。这类查询也是非常有用的。

3.1.2 When查询——已知运动目标和空间条件,查询时间信息

When查询可以用Qwhen=(Si,Oi) 表达,其中Si表示给定的空间条件,可以是通过空间谓词连接的空间对象,Oi是已知的运动目标。根据给定查询条件的不同,返回结果可能是(过去、现在或未来的)时间点或段。

3.1.3 Where查询——已知运动目标和时间条件,查询空间信息

Where查询可以用Qwhere=(Ti,Oi) 表达,其中Ti表示给定的(过去、现在或未来)时间条件,可以是通过时间谓词连接的时间对象,Oi是已知的运动目标。根据给定的查询条件的不同,返回结果可能是目标对应的空间的位置、范围或某一段轨迹。

3.1.4 How查询——已知运动目标和时空条件,查询其运动信息

How查询可以用Qhow=(Si,Ti,Oi) 表达,其中Si表示给定的空间条件,Ti表示给定的(过去、现在或未来)时间条件,可以是通过时间谓词连接的时间对象,Oi是已知的运动目标。

3.2 运动目标时空谓词扩展

运动目标时空数据的操作和分析是通过STQL语句(主要是指数据操纵语句)中时空谓词完成的。所以,运动目标时空谓词的扩展是实现运动目标时空数据库查询语言的核心任务。运动目标时空谓词主要包括数据库一致性、拓扑关系、投影、几何特征、距离和方向、集合和变化率等七类操作。

3.2.1 运动目标时空数据库一致性操作

主要是指检核运动目标时空数据的有效性和运动目标的类型变异,以保证运动目标时空数据库的一致性,如表1所示。

3.2.2运动目标时空拓扑和其它关系谓词操作

主要是指关于给定空间条件和运动目标之间距离和拓扑关系的操作谓词,如表2所示。

其中,f_relate操作谓词允许用户通过mask参数输入拓扑关系类型的关键词有:ANYINTERACT(相交)、CONTAINS(包含且不相切)、COVEREDBY(被包含且相切)、COVERS(包含且相切)、DISJOINT(不相交)、EQUAL(相等)、INSIDE(包含且不接触)、OVERLAPBDYDISJOINT(重叠且边界不相交)、OVERLAPBDYINTERSECT(重叠且边界至少一处相交)、TOUCH(相触且无内部公共点)。

另外,mask的值也可以是上述关键词的逻辑组合。如INSIDE+TOUCH根据情况的不同可以返回’INSIDE+ TOUCH’或FALSE。

总之,f_relate函数返回的结果包括以下三种情况:

1) 当输入mask的值是一个或多个关系的关键词时,如果二者的关系是真返回关系的关键词,否则返回FALSE。

2) 当输入mask的值是DETERMINE时,函数返回一个能反映二者关系的最佳关键词。DETERMINE只能用在relate作为谓词出现在SQL语言的SELECT从句中。

3) 如果输入mask的值是ANYINTERACT时,如果二者不是分离的将返回TRUE,否则返回FALSE。

3.2.3运动目标时间/空间域的投影/操作

主要是指运动目标在时间/空间域上的投影和相交操作,如表3所列。二维平面运动点目标在时间域上的投影如图1所示。

其中,f_traversed是计算运动目标运动过程中所经过的区域或线路。图2就是Moving_Rectangle和Moving_Circle在t1和t2之间经过的区域。

3.2.4运动目标几何特征操作

关于面积、长度和几何组成部分的操作和相应的实现函数,如下表所示。

3.2.5运动目标距离和方向操作

主要完成运动目标间距离和方向计算,包含两个函数:f_distance和f_direction。两个运动目标之间的距离实质上是两个运动目标之间的最短距离,即两个最邻近点或线段之间的距离。f_direction只是对Moving_Point定义的,用来计算用户指定时刻通过第一和第二个Moving_Point线段的角度(单位:度),其中0≤angle < 360。

3.2.6运动目标集合操作

主要完成运动目标间的并、交、差和异或等集合操作,如表6所列。

3.2.7运动目标变化率操作

主要完成运动目标速度、速率和变化率等计算,如表7所列。其中,速率只有大小没有方向,速度是向量同时具有大小和方向。

4小结

本文基于SQL进行时空谓词扩展实现运动目标时空查询语言,既可以利用现有强大的SQL关系操作,也可以很容易地与商业数据库集成。STQL继承了标准SQL的全部功能,在语言的语法表示上与标准SQL保持统一,提供了数据类型、数据表示和数据操作的可重用性。

参考文献

[1]单国慧.面向CJOP的运动目标时空数据模型研究[D].郑州:解放军信息工程大学,2008.

[2]Abraham Silberschatz,Henry F Korth,Sudarshan S.数据库系统概念[M].杨冬青,唐世渭,等,译.北京:机械工业出版社,2003.

[3]Richard T Snodgrass,Michael H Bohlen,Christian S.AddingValid Time to SQL/Temporal[S].ANSI Experts Contribution,ANSIX3H2—96—501r1,ISO/IECJTC1/SC21/WG3DBLMAD—146r2,International Organization for Standardization,1996.

[4]Richard T Snodgrass,Michael H Bhlen,Christian S Jensen.Adding Transaction Valid Time to SQL/Temporal[S].ANSI Ex-perts Contribution,ISO/IECJTC1/SC21/WG3DBLMCI—147r2,International Organization for Standardization,1996.

[5]Open GIS Consortium[EB/OL].http://www.opengis.org.

[6]王宏勇.空间运动对象时空数据模型的研究[D].郑州:解放军信息工程大学,2005.

篇4：结构化查询语言sql

关键词：SELECT语句查询连接

在关系数据库管理系统中，SQL查询语句——SELECT的使用是非常重要的一部分内容，是任何SQL语言中使用频率最高的语句，它是SQL语言的灵魂。SELECT语句可以使数据库服务器根据客户的要求搜索所需要的信息，并按规定的格式返回给客户。使用T—SQL的SELECT语句，不仅可以查询普通数据库中的表格和视图信息，还可以查询SQL Server的系统信息。在Transact-SQL中绝大多数的语句都是由SELECT构成的。

然而笔者在近几年对关系数据库管理软件（如Access、SQL Server、Oracle等）的教学中，发现对于这部分的内容学生掌握得并不是很好。为此，笔者根据多年的教学经验，将SQL查询语句的一些使用技巧列出来，供学生参考和同行借鉴。

由于SELECT语句的完整语句比较复杂，故在这里只列举出它的主要子句。SELECE语句的主要子句格式如下：

SELECT [ALL|DISTINCT][TOP n] select_list

[INTO new_table]

[FROM table_condition]

[WHERE search_condition]

[GROUP BY group_by_expression]

[HAVING search_condition]

[ORDER BY order_by_expression [ASC|DESC]]

[COMPUTE expression]

下面笔者就结合简单查询、联合查询和连接查询三方面通过举例的形式来谈谈有关Transact-SQL语句的用法。（注：以下所有例子的数据表来自示例数据库“图书管理系统”或“northwind”，在进行查询前，先用USE关键字将相关数据库打开。）

1 简单查询

Transact-SQL的簡单查询包括选择列表、FROM子句和WHERE子句三部分内容。由此说明要查询的列、所查询的表或视图以及检索条件等。

例如，从数据库“图书管理系统”的“图书明细表”中查询定价大于50的“图书编号”、“图书名称”、“出版日期”和“定价”字段的信息。

SELECT图书编号，图书名称，出版日期，定价

FROM 图书明细表

WHERE 定价>50

1.1 选择列表

简单查询中的选择列表(select_list)指出要查询的字段列，可以由一组字段名列表、星号、表达式或变量(包括局部变量和全局变量)等构成。

1.1.1 查询表中所有的字段列

星号（*）在选择列表中有特殊含义，它代表列表中的所有行。

例如，从数据库“图书管理系统”中查询“出版社信息表”的所有行。

SELECT *

FROM 出版社信息表

1.1.2 查询表中指定的列

可以从表中检索指定的列，只需将这些列放在选择列表中。

例如，从数据库“图书管理系统”的“作者表”中查询列为“作者姓名”、“性别”和“电话”的作者信息。

SELECT作者姓名，性别，电话

FROM作者表

1.1.3 改变列名

在选择列表中，通过使用AS关键字创建更具可读性的别名来取代默认的列名。

定义格式为：列标题AS列名或列名列标题

注意：如果指定的列名不是标准的标识符格式，应使用引号定界符。

例如，从数据库“图书管理系统”的“图书明细表”中，查询列“图书名称”和“定价”为八折后的“新价格”的图书信息。

SELECT图书名称，定价*0.8 AS新价格

FROM图书明细表

1.1.4 消除重复行

如果希望一个列表没有重复值，则可以使用DISTINCT子句来消除结果集中的重复行。

例如，从数据库“northwind”的“suppliers”表中检索所有的行，但每个国家只显示一次。

SELECT DISTINCT country

FROM suppliers

1.1.5 使用TOPn列出前n个记录

用TOPn[PERCENT]关键字列出结果集中前n个记录。其中TOPn表示返回结果集中前n行记录，而TOP n PERCENT中的n表示一百分数，指定返回的记录数等于总记录数的百分之几。

例如，从数据库“northwind”的“order details”表中查询出前5条记录，只显示orderid、productid、quantity三列内容。

SELECT TOP 5 orderid，roductid，quantity

FROM [order details]

1.2 FROM子句

1.2.1 FROM子句指定从中查询行和列所属的源表或视图。可以指定多个，最多达256个，其间用“，”分隔。当FROM子句同时指定多个表或视图时，如果在选择列表中有相同的列，则应使用对象名限定这些列所属的表或视图。

例如，在数据库“northwind”的“orders”和“customers”两个表中均有customerid(顾客id)列，在查询两个表中的顾客id时应使用下面语句格式来限定。

SELECT DISTINCT customerid，companyname，orderdate，

FROM orders，customers

WHERE orders. customerid = customers. customerid

1.2.2 在FROM子句中可以为表或视图指定别名。格式：<表名> as <别名>或<表名> <别名>

例如，上面例中的查询语句也可表示为如下形式：SELECT DISTINCT customerid，companyname，orderdate，

FROM orders a，customers b

WHERE a.customerid = b. customerid

1.3 WHERE子句

1.3.1 使用WHERE子句，可以根据给定的搜索条件检索特定的行。

例如，下面的查询将从数据库“northwind”中的“employees”表中检索所有居住在美国的职员的姓和居住的城市。

SELECT lastname，city

FROM employees

WHERE country=USA

1.3.2 在WHERE子句中可包括以下一些运算符：①比较运算符(用于比较大小)：>（大于)、>=（大于或等于）、=（等于）、<（小于）、<=（小于或等于）、<>（不等于）。②范围运算符(检索在指定取值范围内的行)：BETWEEN…AND…和NOT BETWEEN…AND…。例：unitprice BETWEEN 10 AND 30等价于unitprice>=10 AND unitprice<=30。③列表运算符(检索与指定值列表相匹配的行)：IN (项1，项2，……)和NOT IN (项1，项2，……)。例：country IN ('Germany'，'China')。④字符串比较符(通过字符串比较来选择符合条件的行)：LIKE和NOT LIKE，适用于char、nchar、varchar、nvarchar、binary、varbinary、datetime或smalldatetime等数据类型的查询，以及在特定条件下对text、ntext和image数据类型进行的查询。

采用以下四种通配符来形成字符串搜索条件：a百分号%：包含零个或更多字符的任意字符串。b下划线_：匹配任何单个字符。c方括号[]：指定的范围或集合内的任何单个字符。d[^]：不在指定的范围或集合内的任何单个字符。

例如，用LIKE ‘N%来表示以N开头的任意字符串；

用LIKE ‘N[xy]%表示以N开头，第二个字符是x或y的字符串；

用LIKE ‘N[^xy]%表示以N开头，第二个字符不是x或y的字符串；

用LIKE ‘N_M%表示以N开头，第三个字符是M，第二个为任意一个字符的字符串。

⑤空值判断符(检索那些指定列中遗漏信息的行)：IS NULL和NOT IS NULL。

例如，从数据库“northwind”的“suppliers”表中检索fax列为空的公司列表。

SELECT companyname，fax

FROM suppliers

WHERE fax IS NULL

⑥逻辑运算符(用于组合多个条件，简化查询处理)：NOT、AND和OR，优先级从左到右依次降低。

1.4 ORDER BY子句

用ORDER BY子句对结果集中的行进行升序（ASC）或降序（DESC）排列。

格式：ORDER BY {order_by_expression [ASC|DESC]} [，…n]

注意：不可以在ORDER BY子句中使用ntext、text或image类型的列。

例如，下面从数据库“northwind”的 “products”表中检索每个产品的产品标识、产品名、类别和单价。先将结果集按照类别进行升序排列，对于同一类别，再按照单价进行降序排列。

SELECT productid，productname，categoryid，unitprice

FROM products

ORDER BY categoryid，unitprice DESC

2 联合查询

使用UNION子句的查询称为联合查询。它可以将两个或更多个SELECT语句的返回结果组合到一个单个结果集中，该结果集包含了联合查询中所有查询结果集中的全部行数据。

联合查询的语法格式如下：

select_statement

UNION [ALL] select_statement

[UNION [ALL] select_statement][…n]

其中，select_statement为待联合的SELECT查询语句。ALL选项表示将所有行都合并到结果集中，若缺省，则被联合查询结果集合中的重复行将只保留一行。

注意：①在用UNION子句查询时，查询结果的列标题为第一个查询语句的列标题。因此，必须在第一个SELECT语句中指定列标题；②SQL Server要求所引用的表必须具有相似的数据类型、相同的列数，且每个查询中的选择列表也必须具有相同的列顺序；③如果希望结果集中的行按一定顺序排列，则必须在最后一个有UNION操作符的语句中包含ORDER BY子句，以指定排序方式。

例如：从数据库“经销商”的“顾客信息表”中，查询姓王的顾客的姓名和家庭住址，并为其增加一个类型列TYPE，列的内容为“顾客”；从“销售人员表”中，查询姓王的销售人员的姓名和家庭住址，并增加一个列，列的内容为“营业员”；然后，将两个查询结果合并在一起。

SELECT姓名，家庭住址，顾客AS TYPE

FROM顾客信息表

WHERE姓名LIKE‘王%

UNION

SELECT姓名，家庭住址，营业员

FROM销售人员表

WHERE姓名LIKE ‘王%

3 连接查询

连接操作可以同时查询两个或多个表中的数据，所生成的结果集将多个表中的行和列合并在一起。

连接可以在SELECT 语句的FROM子句或WHERE子句中建立，建议在FROM子句中指定连接，这样有助于将指定的连接条件与WHERE子句中的搜索条件区分开来。

SQL-92标准所定义的FROM子句的连接语法格式为：

FROM table_source1 join_type table_source 2

[ON (join_condition)]

其中，table_source1和table_source 2指定要查询的表；join_type 指定所执行的连接类型，可分为三类：内连接（Inner Join）、外连接（Outer Join）、交叉连接（Cross Join）；join_condition指定连接条件。

3.1 内连接

内连接通过比较两个表共同拥有的列的值，把两个表连接起来。SQL Server将只返回满足连接条件的行。它是SQL Server默认的连接方式。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。

3.1.1 等值连接即在连接条件中使用“=”运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括重复列。

例如，列出数据库“图书管理系统”的“作者表”和“出版社信息表”中位于同一城市的作者和出版社信息。

SELECT *

FROM 作者表AS a INNER JOIN 出版社信息表AS b

ON a.籍贯=b.出版社所在城市

3.1.2 不等连接即在连接条件中使用除等于运算符外的其它比较运算符来比较被连接的列的列值。

3.1.3 自然连接即在连接条件中使用等于运算符比较被连接列的列值，但它使用选择列表指出查询。

例：列出数据库“图书管理系统”的“作者表”和“出版社信息表”中位于同一城市的作者和出版社，并删除重复列“籍贯”。

SELECT a.*，b.出版社编号，b.出版社名称，b.出版社电话

FROM 作者表AS a INNER JOIN 出版社信息表AS b

ON a.籍贯=b.出版社所在城市

3.2 外连接

若要创建一个查询，以返回一个或多个表中的所有行（无论在另外的表中是否含有相匹配的行），则需要使用外连接。外连接有三种类型：左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和完全外连接(FULL OUTER JOIN或FULL JOIN)。

例如，在数据库“图书管理系统”中，以在同一个城市的出版社和作者为条件，对“出版社信息表”和“作者表”进行左外连接查询。

SELECT a.姓名，a.性别，a.籍贯as 所在城市，b.出版社名称

FROM 作者表as a LEFTJOIN 出版社信息表as b

ON a. 籍贯=b. 出版社所在城市

ORDER BY 姓名

在查询结果窗口中，显示左表中指定列的所有行和对应连接列的所有行，在左表中没有找到相匹配的右表的对应位置填上NULL。

例如，在数据库“图书管理系统”中，以在同一个城市的出版社和作者为条件，对“出版社信息表”和“作者表”进行完全外连接查询。

SELECT a.姓名，a.性别，a.籍贯as 所在城市，b.出版社名称

FROM 作者表as a FULLJOIN 出版社信息表as b

ON a.籍贯=b.出版社所在城市

ORDER BY 姓名

在查询结果窗口中显示相连接的两个表的所有记录，在没有找到相匹配的位置上填上NULL。

3.3 交叉连接

交叉连接(CROSS JOIN)不带WHERE子句，返回的是被连接的两个表所有数据行的笛卡尔积，即返回到结果集中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

例如，下面将显示数据库“northwind”中“shippers”和“suppliers”表交叉连接后的结果集，以列出供应商运输其产品的所有可能方式。

SELECT suppliers.companyname,shippers.companyname

FROM suppliers CROSS JOIN shippers

3.4 自连接

在连接查询时，當table_source1和table_source 2是同一个表时，即对同一个表进行连接操作，则称此连接为自连接。

例如，使用自连接在数据库“图书管理系统”的“作者表”中查找籍贯是“河北石家庄”的作者。

SELECT a.姓名，a.性别，b. 姓名，b.性别

FROM 作者表as a INNER JOIN作者表as b

ON a.籍贯=b.籍贯

WHERE a.籍贯=河北石家庄

ORDER BY 姓名

篇5：结构化查询语言sql

Student(S#,Sname,Sage,Ssex) 学生表

Course(C#,Cname,T#) 课程表

SC(S#,C#,score) 成绩表

Teacher(T#,Tname) 教师表

问题：

1、查询“001”课程比“002”课程成绩高的所有学生的学号;

代码如下:

select a.S# from (select s#,score from SC where C#=‘001‘) a,(select s#,score

from SC where C#=‘002‘) b

where a.score>b.score and a.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩;

代码如下:

select S#,avg(score)

from sc

group by S# having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩;

代码如下:

select Student.S#,Student.Sname,count(SC.C#),sum(score)

from Student left Outer join SC on Student.S#=SC.S#

group by Student.S#,Sname

4、查询姓“李”的老师的个数;

代码如下:

select count(distinct(Tname))

from Teacher

where Tname like ‘李%‘;

5、查询没学过“叶平”老师课的同学的学号、姓名;

代码如下:

select Student.S#,Student.Sname

from Student

where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=‘叶平‘);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

代码如下:

select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=‘001‘and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=‘002‘);

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

代码如下:

select S#,Sname

from Student

where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=‘叶平‘ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=‘叶平‘));

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

代码如下:

Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=‘002‘) score2

from Student,SC where Student.S#=SC.S# and C#=‘001‘) S_2 where score2

9、查询所有课程成绩小于60分的同学的学号、姓名;

代码如下:

select S#,Sname

from Student

where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

10、查询没有学全所有课的同学的学号、姓名;

代码如下:

select Student.S#,Student.Sname

from Student,SC

where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

代码如下:

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#=‘1001‘;

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

代码如下:

select distinct SC.S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in (select C# from SC where S#=‘001‘);

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

代码如下:

update SC set score=(select avg(SC_2.score)

from SC SC_2

where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname=‘叶平‘);

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;

代码如下:

select S# from SC where C# in (select C# from SC where S#=‘1002‘)

group by S# having count(*)=(select count(*) from SC where S#=‘1002‘);

15、删除学习“叶平”老师课的SC表记录;

代码如下:

Delect SC

from course ,Teacher

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=‘叶平‘;

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、

号课的平均成绩;

代码如下:

Insert SC select S#,‘002‘,(Select avg(score)

from SC where C#=‘002‘) from Student where S# not in (Select S# from SC where C#=‘002‘);

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

代码如下:

SELECT S# as 学生ID

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘004‘) AS 数据库

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘001‘) AS 企业管理

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘006‘) AS 英语

,COUNT(*) AS 有效课程数, AVG(t.score) AS平均成绩

FROM SC AS t

GROUP BY S#

ORDER BY avg(t.score)

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

代码如下:

SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分

FROM SC L ,SC AS R

WHERE L.C# = R.C# and

L.score = (SELECT MAX(IL.score)

FROM SC AS IL,Student AS IM

WHERE L.C# = IL.C# and IM.S#=IL.S#

GROUP BY IL.C#)

AND

R.Score = (SELECT MIN(IR.score)

FROM SC AS IR

WHERE R.C# = IR.C#

GROUP BY IR.C#

);

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

代码如下:

SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS平均成绩

,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数

FROM SC T,Course

where t.C#=course.C#

GROUP BY t.C#

ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

20、查询如下课程平均成绩和及格率的百分数(用“1行”显示): 企业管理(001)，马克思(002)，OO&UML (003)，数据库(004)

代码如下:

SELECT SUM(CASE WHEN C# =‘001‘ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘001‘ THEN 1 ELSE 0 END) AS 企业管理平均分

,100 * SUM(CASE WHEN C# = ‘001‘ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘001‘ THEN 1 ELSE 0 END) AS 企业管理及格百分数

,SUM(CASE WHEN C# = ‘002‘ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘002‘ THEN 1 ELSE 0 END) AS 马克思平均分

,100 * SUM(CASE WHEN C# = ‘002‘ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘002‘ THEN 1 ELSE 0 END) AS 马克思及格百分数

,SUM(CASE WHEN C# = ‘003‘ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘003‘ THEN 1 ELSE 0 END) AS UML平均分

,100 * SUM(CASE WHEN C# = ‘003‘ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘003‘ THEN 1 ELSE 0 END) AS UML及格百分数

,SUM(CASE WHEN C# = ‘004‘ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘004‘ THEN 1 ELSE 0 END) AS 数据库平均分

,100 * SUM(CASE WHEN C# = ‘004‘ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘004‘ THEN 1 ELSE 0 END) AS 数据库及格百分数

FROM SC

21、查询不同老师所教不同课程平均分从高到低显示

代码如下:

SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS平均成绩

FROM SC AS T,Course AS C ,Teacher AS Z

where T.C#=C.C# and C.T#=Z.T#

GROUP BY C.C#

ORDER BY AVG(Score) DESC

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理(001)，马克思(002)，UML (003)，数据库(004)

[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

代码如下:

SELECT DISTINCT top 3

SC.S# As 学生学号,

Student.Sname AS 学生姓名 ,

T1.score AS 企业管理,

T2.score AS 马克思,

T3.score AS UML,

T4.score AS 数据库,

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分

FROM Student,SC LEFT JOIN SC AS T1

ON SC.S# = T1.S# AND T1.C# = ‘001‘

LEFT JOIN SC AS T2

ON SC.S# = T2.S# AND T2.C# = ‘002‘

LEFT JOIN SC AS T3

ON SC.S# = T3.S# AND T3.C# = ‘003‘

LEFT JOIN SC AS T4

ON SC.S# = T4.S# AND T4.C# = ‘004‘

WHERE student.S#=SC.S# and

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)

NOT IN

(SELECT

DISTINCT

TOP 15 WITH TIES

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)

FROM sc

LEFT JOIN sc AS T1

ON sc.S# = T1.S# AND T1.C# = ‘k1‘

LEFT JOIN sc AS T2

ON sc.S# = T2.S# AND T2.C# = ‘k2‘

LEFT JOIN sc AS T3

ON sc.S# = T3.S# AND T3.C# = ‘k3‘

LEFT JOIN sc AS T4

ON sc.S# = T4.S# AND T4.C# = ‘k4‘

ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

代码如下:

SELECT SC.C# as 课程ID, Cname as 课程名称

,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]

,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]

,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]

,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

FROM SC,Course

where SC.C#=Course.C#

GROUP BY SC.C#,Cname;

24、查询学生平均成绩及其名次

代码如下:

SELECT 1+(SELECT COUNT( distinct平均成绩)

FROM (SELECT S#,AVG(score) AS平均成绩

FROM SC

GROUP BY S#

) AS T1

WHERE平均成绩 > T2.平均成绩) as 名次,

S# as 学生学号,平均成绩

FROM (SELECT S#,AVG(score)平均成绩

FROM SC

GROUP BY S#

) AS T2

ORDER BY平均成绩 desc;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

代码如下:

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

FROM SC t1

WHERE score IN (SELECT TOP 3 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

26、查询每门课程被选修的学生数

代码如下:

select c#,count(S#) from sc group by C#;

27、查询出只选修了一门课程的全部学生的学号和姓名

代码如下:

select SC.S#,Student.Sname,count(C#) AS 选课数

from SC ,Student

where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;

28、查询男生、女生人数

代码如下:

Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex=‘男‘;

Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex=‘女‘;

29、查询姓“张”的学生名单

代码如下:

SELECT Sname FROM Student WHERE Sname like ‘张%‘;

30、查询同名同性学生名单，并统计同名人数

代码如下:

select Sname,count(*) from Student group by Sname having count(*)>1;

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

代码如下:

select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age

from student

where CONVERT(char(11),DATEPART(year,Sage))=‘1981‘;

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

代码如下:

Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

代码如下:

select Sname,SC.S# ,avg(score)

from Student,SC

where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;

34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数

代码如下:

Select Sname,isnull(score,0)

from Student,SC,Course

where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname=‘数据库‘and score <60;

35、查询所有学生的选课情况;

代码如下:

SELECT SC.S#,SC.C#,Sname,Cname

FROM SC,Student,Course

where SC.S#=Student.S# and SC.C#=Course.C# ;

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

代码如下:

SELECT distinct student.S#,student.Sname,SC.C#,SC.score

FROM student,Sc

WHERE SC.score>=70 AND SC.S#=student.S#;

37、查询不及格的课程，并按课程号从大到小排列

代码如下:

select c# from sc where scor e <60 order by C# ;

38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

代码如下:

select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#=‘003‘;

39、求选了课程的学生人数

代码如下:

select count(*) from sc;

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

代码如下:

select Student.Sname,score

from Student,SC,Course C,Teacher

where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname=‘叶平‘ and SC.score=(select max(score)from SC where C#=C.C# );

41、查询各个课程及相应的选修人数

代码如下:

select count(*) from sc group by C#;

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

代码如下:

select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# B.C# ;

43、查询每门功成绩最好的前两名

代码如下:

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

FROM SC t1

WHERE score IN (SELECT TOP 2 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

44、统计每门课程的学生选修人数(超过10人的课程才统计)，

50条SQL查询技巧、查询语句示例

，

要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列

代码如下:

select C# as 课程号,count(*) as 人数

from sc

group by C#

order by count(*) desc,c#

45、检索至少选修两门课程的学生学号

代码如下:

select S#

from sc

group by s#

having count(*) > = 2

46、查询全部学生都选修的课程的课程号和课程名

代码如下:

select C#,Cname

from Course

where C# in (select c# from sc group by c#)

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

代码如下:

select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname=‘叶平‘);

48、查询两门以上不及格课程的同学的学号及其平均成绩

代码如下:

select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;

49、检索“004”课程分数小于60，按分数降序排列的同学学号

代码如下:

select S# from SC where C#=‘004‘and score <60 order by score desc;

50、删除“002”同学的“001”课程的成绩

代码如下:

篇6：SQL查询和清除重复数据

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

有例表：emp

emp_nonameage

001Tom17

002Sun14

003Tom15

004Tom16

要求：

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

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

selectnamefromempgroupbynamehavingcount(*)>1

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

select*fromemp

where namein(selectnamefromemp groupbyname having count(*)>1)

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

select*fromempwhere(selectcount(*)fromempewheree.name=emp.name)>1

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

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

注意e是emp的一个别名，

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

select*fromemp

whereexists

(select*fromempewheree.name=emp.nameande.emp_noemp.emp_no)

此思路的join写法：

selectemp.*fromemp,emp e

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

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

select emp.* fromempinner join empeon emp.name=e.name and emp.emp_noe.emp_no

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

b、有例表：emp

nameage

Tom16

Sun14

Tom16

Tom16

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

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

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

selectdistinct*fromemp或selectname,agefromempgroupbyname,age

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

selectdistinct*into#tmpfromemp

deletefromemp

insertintoempselect*from#tmp

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

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

altertableempaddchkintidentity(1,1)

表示例：

nameagechk

Tom161

Sun142

Tom163

Tom164

重复记录可以表示为：

select*fromemp where (selectcount(*)fromempewheree.name=emp.name)>1

要删除的是：

deletefromemp

where (selectcount(*)fromempewheree.name=emp.nameande.chk>=emp.chk)>1

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

altertableempdropcolumnchk

(3)另一个思路：

视图

selectmin(chk) fromemp groupbyname havingcount(*)>1

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

delete fromemp where chknotin (select min(chk) fromemp groupbyname)

写成join的形式也可以:

(1)有例表：emp

emp_nonameage

001Tom17

002Sun14

003Tom15

004Tom16

◆要求生成序列号

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

altertableempaddchkintidentity(1,1)或

select*,identity(int,1,1)chkinto#tmpfromemp

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

selecttop100000*,identity(int,1,1)chkinto#tmpfromemporderbyage

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

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

selectemp.*,(selectcount(*)fromempewheree.emp_no<=emp.emp_no)

fromemp

orderby(selectcount(*)fromempewheree.emp_no<=emp.emp_no)