SQL常用查询总结

SQL常用查询总结（精选7篇）

1.SQL常用查询总结 篇一

实验三SQL查询3

（子查询、分组查询）

一、实验目的

使用SQL语言完成数据定义、数据查询、数据更新等功能。

二、实验要求

1.掌握使用SQL定义、删除和修改基本表。2.掌握使用SQL完成单表查询、多表查询。3.掌握连接查询和嵌套查询。

4.掌握使用SQL完成数据插入、数据修改、数据删除等功能。

三、实验内容

在“FLY飞翔公司数据库”数据库中，完成以下SQL查询实验。（以下的SQL查询实验可能会多种SQL命令描述方式，建议同学多思考、多尝试）

实验1 使用SQL命令，查询公司机构C1和C2中基本工资低于3000的员工信息。

提示：使用两个IN函数，其中一个IN函数是IN(“C1”, “C2”)，另外一个IN函数是嵌套SELECT语句

实验2使用SQL命令，查询公司机构C1和C2中基本工资低于3000的员工信息。

实验3使用SQL命令，查询显示基本工资大于2000，且不超过3000元的员工编号及其所在公司名称。

实验4使用SQL命令，查询显示查询供应商“兆海公司”未供应的商品类型名称。提示：使用NOT IN 函数实现

实验5使用SQL命令，查询显示员工的姓名，所属公司名称和工资收入（工资收入＝基本工资＋任务工资＋奖金-住房公积金－扣款）。提示：产生新字段要使用As

实验6使用SQL命令，分组统计供应了商品的供应商所提供的商品种类数量，显示供应商名称和商品种类数量。提示：使用COUNT函数 实验7使用SQL命令，分组统计每笔订单的净收益，显示订单编号和金额为200到500之间的净收益。（净收益＝（销售价格-购入价格）*数量*折扣）。

提示：group by是分组，having是判断，同学选做。

实验8使用SQL命令，查询与员工王芳签订过订单的零售商名称和订单编号，并将结果存在一个新表New_temp中。

提示： Select 零售商名称, 订单编号 Into New_temp 是将所选字段存入新表New_temp中。

实验9使用SQL命令，查询未与员工E3签订过订单的零售商名称和所在城市。提示：使用Not IN函数

实验10自己设计一个查询，并用SQL命令实现（要求：对设计要有合理的解释，且最好有新意）。

2.SQL常用查询总结 篇二

关键词：SQL,参数化查询,参数,参数绑定,ADO,ADO.NET,ODBC

1 概述

大多数程序都会或多或少地使用SQL语句和数据库打交道。在程序代码中, SQL语句以字符串的形式存在, 如“SE-LECT column1, column2, …FROM table1 WHERE param_column1=value1 AND param_column2=‘value2’”。这个例子以数据列param_column1、param_column2的值分别为value1、value2作为过滤条件, 从数据表table1中查询column1、column2等数据列。其中param_column1为整数类型, param_column2为字符串类型。

很多时候, value1与value2的值在程序运行时才能确定, 这意味着SQL语句只能在程序运行中动态生成。不少开发人员喜欢采用字符串拼凑的方式生成SQL语句, 形如“SELECT column1, column2, …FROM table1 WHERE param_column1=”+int_to_str (int_value) +“AND param_column2=‘”+str_value+“’”。C程序员可能更倾向于选择格式化字符串的方式, 其中格式类似于“SELECT column1, column2, …FROM table1 WHERE param_column1=%d AND param_column2=‘%s’”, 在实际的值代替%后, SQL语句发送给数据库引擎。

从SQL执行的角度看, 字符串格式化与字符串拼凑并没有太大的差别。每次查询条件稍有变化, 都会向数据库提交不同的SQL语句。这些SQL语句之间虽然差别可能很小, 但查询引擎每次都得重新解析, 造成执行效率的降低。另外, 如果上面的例子中, 如果param_column1或param_column2是二进制类型的数据, 则要生成SQL语句, 显然很麻烦。更为重要的是, 这种生成SQL语句的方式存在安全隐患, 很容易导致SQL注入式攻击。又以上面的SQL语句为例, 如果攻击者为param_column2指定的值为“’OR‘’=‘” (不包含双引号) , 则得到的SQL语句为“SELECT column1, column2, …FROM table1 WHERE param_column1=value1 AND param_column2=‘’OR‘’=‘’”, 使WHERE子句形同虚设, table1中所有的记录都会查询出来。如果攻击者更缺德一点, 为param_column2指定的值为“’;DELETE FROM table1WHERE‘’=‘” (不包含双引号) , 则得到的SQL语句为“SELECT column1, column2, …FROM table1 WHERE param_column1=0 AND param_column2=‘’;DELETE FROM table1WHERE‘’=‘’”, 执行之后, 数据表table1就被清空了。

以上的问题都可以通过参数化查询解决。上面的SQL语句如果采用参数化查询的方式, 则表示为“SELECT column1, column2, …FROM table1 WHERE param_column1=?AND param_column2=?”。其中两个问号 (?) 是待定的数据列的值。在有些开发平台, 问号的地方可能用有指定标记的命名参数代替, 如“SELECT column1, column2, …FROM table1 WHERE param_column1=@param1 AND param_column2=@param2”。不管是问号还是命名参数, 咋一看都似乎与字符串格式化差不多。不过, 在参数化查询中, 程序直接把这个包含待定值的SQL语句提交给数据库, 由数据库引擎为之生成查询计划。之后, 程序以参数的方式提供这些值。数据库引擎把参数中的值应用到查询计划中, 然后执行查询结果。如果改变参数的值后再执行, 则得到不同的查询结果, 但SQL语句不需要重新提交, 查询计划也不需要重新生成, 参数的修改对查询计划没有影响。哪怕参数中包含精心设计的特殊字符, 也不会形成SQL注入式攻击。参数改变的次数越多, 则查询计划的重用性越高。

与程序设计语言中的函数或过程类似, SQL语句的参数也可以分为输入参数、输出参数及输入输出参数。其中输入参数最为常见。上面的查询例子中的两个参数都是输入参数。参数的类型可以是整数、浮点数、字符串乃至二进制数据。采用参数化查询, 可以很方便地导入二进制数据。

参数化查询得到各种主流编程语言和开发工具的广泛支持。下面分别介绍ADO、ADO.NET、ODBC这3种数据访问模型中的参数化查询方式。

2 ADO中的参数化查询

在ADO中, 查询的一般过程是创建Connection对象连接数据库, 再创建Command对象执行SQL语句或存储过程。如果SQL语句有返回数据, 则用Recordset对象接收, 然后以游标方式遍历Recordset中的每一行, 读取查询结果 (也可把整个Recordset保存到文件或数据流中) 。在一般场合, Command对象可以省略, 其职能由Connection对象和Recordset对象代替。不过如果采用参数化查询, 则少不了Command对象。该对象创建后, 先指定SQL语句 (或存储过程) , 然后创建SQL语句中待定的各个参数, 给参数赋值后再执行Command对象。

待定的参数用Parameter对象代表。在Parameter对象中, Name属性可设置或返回参数名称, Value属性可设置或返回参数值, Direction属性指示是输入参数、输出参数还是输入输出参数, Type属性定义参数的数据类型, 其他几个属性可设置或返回参数特性。

Command对象有一个Parameters属性, 它是Parameters集合类型, 其中包含SQL语句 (或存储过程) 的所有参数。在Parameters集合中, 可以通过名称或序号访问各个参数。通常用Command对象的CreateParameter方法创建每个Parameter对象, 再用Parameters集合的Append方法添加进来。Command对象还有一个布尔类型的Prepared属性, 该属性如果为True, 则表示该指令在第一次执行之前必须保存查询语句。

为了使大家对ADO中的参数化查询有更清晰的认识, 这里举一个例子。在这个例子中, 应用程序访问Access数据库。数据库路径为d:db.mdb, 里面的数据表table1包含两个数据列, 其中column1为数字类型, column2为文本类型。下面的代码向数据表依次插入“西施”、“昭君”、“貂蝉”3位古代美女, 然后查询插入的第二条记录, 如果执行成功, 会弹出对话框显示插入到表中的数据“昭君”。

上面的代码是用VB编写的。稍作修改, 就可以移植到ASP脚本中。这里也给出在VC中的等效代码:

如果程序中使用数据库存储过程, 则更应该使用参数化查询。很多数据库在使用存储过程时, 其OLEDB驱动程序能自动解析所需的参数, 根本不需要明确调用CreateParameter方法, 直接给对应的参数赋值即可执行。下面的例子中, 程序访问到本机MS SQL Server服务器的pubs数据库, 执行数据库中的ByRoyalty存储过程。该存储过程返回给定忠诚度的顾客信息, 忠诚度为该存储过程唯一的输入参数 (这个例子来自MSDN并适当修改) 。

以上介绍的是原生的ADO参数化查询方式。Visual Basic、Delphi等可视化的快速开发环境在封装ADO的同时, 也保留甚至改进了参数化查询。SQL语句和参数既可以在可视化界面设定, 也可在运行中动态赋值。

ADO中的Command对象在VCL中对应TADOCommand控件, 其SQL语句中的问号被以冒号开头的命名参数代替。如SQL语句“SELECT column1, column2, …FROM table1 WHERE param_column1=?AND param_column2=?”在VCL中表示为“SELECT column1, column2, …FROM table1 WHERE param_column1=:Param_column1 AND param_column2=Param_column2”。由此, VCL可从中解析出参数名称。VCL也试图从SQL语句中自动解析出参数的数据类型及其他属性 (解析时需要连接数据库) , 但不一定总是正确的。这时可以手工修正。顺便说一句, 除了ADO控件外, VCL中其他数据访问模型通常也支持参数化查询。

Visual Basic的数据环境设计器对参数化查询提供了强大的支持。读者如果不熟悉数据环境设计器, 可参考Visual Basic的联机手册。在数据环境设计器中, 命令对象被映射为数据环境设计器实例的子过程, 命令的参数被映射为子过程的参数, 命令的返回结果集被映射为数据环境设计器实例的属性。以本节开头的例子, 假定在数据环境设计器中创建了cmd1和cmd2两个命令, 其SQL语句分别为"INSERT INTO table1VALUES (?, ?) "和"SELECT column2 FROM table1 WHERE column1=?"。创建命令并设置好参数后, 按F2进入对象浏览器中, 则可看到在本工程创建的数据环境设计器对象实例下面, 有cmd1和cmd2两个子过程, cmd1有两个参数, cmd2有一个参数, 这与SQL语句刚好对应。另外, 数据环境设计器实例还有一个Recordset类型的rsCmd2属性, 每次运行cmd2子过程后, 查询结果将保存到rsCmd2中。

3 ADO.NET中参数化查询

在ADO.NET中, 查询的一般过程是创建实现IDbConnection接口的连接对象, 登录到相应数据库, 再创建实现IDbCommand接口的命令对象执行SQL命令或存储过程。如果SQL语句有返回结果, 则可以整表的方式接收数据或以逐行推进的方式遍历各条记录。在参数化查询中, 创建命令对象后, 为其指定SQL语句 (或存储过程) , 然后创建SQL语句包含的各个参数, 给参数赋值后再执行命令对象。

与命令对象关联的参数对象实现IDbDataParameter接口。在参数对象中, ParameterName属性可设置或返回参数名称, Value属性可设置或返回参数值, Direction属性指示是输入参数、输出参数还是输入输出参数, DbType属性定义参数的数据类型, 其他几个属性可设置或返回参数特性。

命令对象有一个Parameters属性, 它是实现IDataParameterCollection接口的集合类型, 其中包含SQL语句 (或存储过程) 的所有参数。在集合中, 可以通过名称或序号访问各个参数。通常用命令对象的CreateParameter方法创建每个参数, 再用IDataParameterCollection集合的Add方法添加进来。命令对象还有一个Prepare方法, 用于生成并保存查询计划。

为了使大家对ADO.NET中的参数化查询有更清晰的认识, 这里举一个特地简化的例子。在这个例子中, 应用程序以信任连接的方式访问本地SQL Server服务器的School数据库 (如果对信任连接不了解, 可参考SQL Server联机手册) 。数据库里面有一个数据表名为Students, 包含3个数据列, 其中Id列为数字类型, Name列为文本类型, Photo列为任意长的二进制数据 (SQL Server 2005及以上版本支持任意长的二进制数据, 在代码中, 用-1表示长度不受限制) 。示例代码向数据表插入2条记录, 然后再查询其中一条记录。为了便于阅读, 省略了处理异常的代码。

在.NET中访问SQL Server数据库有两种方式, 一种是使用专为.NET开发的驱动程序, 另一种是用SQL Server的OLEDB驱动程序 (在.NET中也可以用ODBC驱动程序, 不过微软不推荐) 。下面分别演示。先是专为.NET开发的驱动程序。

再来看OLEDB驱动程序的方式:

两段代码在结构上基本相同, 毕竟用到的各个类都实现相同的接口。除了数据库连接方式不同外, 两部分代码在表示SQL语句中的参数时也有所不同。SQL Server专为.NET设计的驱动程序要求使用@开头的命名参数, 就像Transact-SQL中的变量那样。而SQL Server的OLEDB驱动程序则偏爱使用问号。对于其他数据库, 对参数的表示方式可能又是另外的方式。例如Access的OLEDB驱动程序可同时支持前面的两种方式。

另外一个不同之处是, 前一段代码把变量声明为具体类的实例, 而后一段代码则声明变量为接口类型。这只是编程风格问题, 与驱动程序无关。事实上, 前一段代码也可用接口, 后一段代码也可声明为具体的类, 甚至可以一段代码中具体类与接口混用, 不过最好保持风格的一致性。采用接口使代码更容易移植到其他数据库, 不过也可能限制发挥特定数据库驱动程序的专长。

4 ODBC中参数化查询

使用ODBC访问数据库的基本步骤, 是依次创建环境句柄 (SQLHENV) 、连接句柄 (SQL_HANDLE_DBC) 、语句句柄 (SQLHSTMT) , 用这3种句柄分别调用SQLSetEnvAttr函数设置驱动环境, SQLConnect函数连接ODBC数据源, SQLExecDirect函数执行SQL语句, 然后以游标方式读取查询结果, 如果有数据返回的话。如果采用参数化查询, 则通过SQLPrepare函数提交SQL语句进行预处理, 然后多次调用SQLBindParameter函数绑定各个参数, 而执行时用SQLExecute函数代替

SQLExecDirect。

SQLBindParameter函数较为复杂, 下面是该函数原型:

各个参数说明如下:

StatementHandle [in] 由SQLAllocHandle函数创建的语句句柄。

ParameterNumber [in] 参数在SQL语句中的位置, 起始为1。

InputOutputType [in] 参数输入输出类型, ODBC中有定义。

ValueType [in] 参数在C语言中的类型, ODBC中有定义。

ParameterType [in] 参数在SQL中的类型, ODBC中有定义。

ColumnSize [in] 数据列长度, 如果为变长数据的话。

DecimalDigits [in] 小数位数, 仅在浮点数时需要设置。

ParameterValuePtr [deferred in] 指向输入或输出参数缓冲区地址。

BufferLength [in] 参数缓冲区长度, 可选。仅在接收输出参数时需要设置。

StrLen_or_IndPtr [deferred in, out] 指向参数数据长度。

这里解释一下[deferred in]是什么意思。大家通常都知道[in]和[out]分别表示输入和输出参数。[deferred in]表示当前只传入输入参数的地址, 后面再把实际数据填充到缓冲区上面。而[deferred out]是指不能在函数返回的时候就得到输出参数的值。比如这里的ParameterValuePtr, 应用程序只要在调用SQLExecute函数前向该参数指向的缓冲区填充好数据就可以了, 同时应用程序必须保证缓冲区在调用SQLExecute函数时都还有效。同样, StrLen_or_IndPtr指向的数据是在执行SQLExecute函数时使用或更新的。

调用SQLBindParameter函数需同时指定参数在C语言中的类型。由于要支持绑定整数、浮点数、字符串、二进制数据等, SQLBindParameter函数的接口变得非常晦涩。实际上该函数无非是指示各个参数的数据类型及数据在内存中的位置。如果我们理解各种数据类型的变量在内存中是如何保存的, 则可举一反三、触类旁通。

可以利用C++的过载 (Overload) 及默认参数特性, 使该函数的接口变得友好得多, 易于使用又不容易出错。示例如下:

现在举例说明。数据库中有一个名为Users数据表存储用户信息, 包含账号 (Account) 、密码 (Pwd) 、年龄 (Age) 3个数据列, 数据类型分别为字符串、二进制数据、整数。现在向该数据表增加2条记录, 然后再根据账号和密码查询其中的记录。下面的示例代码假定语句句柄 (SQLHSTMT) 已经事先创建, 并且使用上面过载 (Overload) 版本的SQLBindParameter。

以上介绍的是原生的ODBC参数化查询方式。在MFC中, ODBC API用CDatabase、CRecordset等封装后, 用起来更为方便。不过MFC ODBC对参数化查询的支持比较有限, 当然可以直接操纵对象中的ODBC句柄以获得完全的自由。这里介绍一下如何使用MFC ODBC中的CRecordset类进行参数化查询。

在MFC ODBC中, CRecordset类表示从数据源选定的一组记录。其中的m_nParams成员变量表示SQL语句的参数数量, 而m_strFilter成员变量则对应SQL的WHERE子句。使用MFC ODBC的应用程序中通常定义CRecordset类的派生类表示某种类型的记录, 实现所谓对象关系映射。在派生类中, 用成员变量分别表示参数和查询结果中的各个数据列。假如有一个包含学生信息的数据表, 里面有学生的编号、姓名、年级等数据列, 如果要查询“哪些学生是高年级的?”, 则生成的派生类大致如下, 其中最后一个成员变量为查询参数。

通常在派生类的构造函数中指定参数数量, 而在GetDefaultSQL成员函数中修改m_strFilter成员变量, 并在DoFieldExchange成员函数中将查询参数与对应的成员变量绑定。

5 注意事项

初学者使用参数化查询易犯的错误是仍以字符串格式化变量的观点理解查询参数。有的程序员老想把数据列名称也作为可变的参数, 结果要么执行出错, 要么输出数据出乎意料。比如为SQL语句“SELECT column1, ?FROM table1”指定参数“column2”, 查询的结果的第2列并不是数据表table1的column2数据列的数据, 而是在每条记录中都是字符串column2本身。

要知道, 数据表、数据列的名称在SQL语句中视为元数据, 而数据列中的值才是某种类型的数据, 可以作为查询参数。这就跟程序设计语言中函数、类的名称与字符串变量是两码事一样。事实上, 如果数据列、数据表、数据库的名称甚至关键字可以在参数中改变, 则查询计划没法预先生成, 并且参数化查询的安全性也不再存在。

不少数据库中可使用TOP或LIMIT关键字限制查询结果输出的行数。如果应用程序可动态指定输出行数的多少, 有时确实很方便。不过, 由于行数并不是数据列的值, 绝大多数数据库都还不支持TOP?或LIMIT?形式的参数化查询。实际上如果数据库引擎稍作改进, 是完全可以支持这种查询方式的。在目前, 只能用字符串拼凑的方法设置输出的行数。

另外这里也客观地指出, 应用程序中调试参数化查询比调试不含参数的SQL查询要困难一些。使用者最好充分使用A-DO、ADO.NET、ODBC及开发工具中的诊断功能, 以尽快发现和定位故障。

6 补充说明

SQL中一个常见的特殊值就是NULL, 它既不是0, 也不等同于空字符串。初学数据库编程时千万别把它与VB中的Nothing、C#中Null和VC中的NULL混为一谈, 更不能用字符串“NULL”作为参数来代替。在VB中, 如果参数为NULL, 把关键字Null赋值给参数即可, 在.NET中则专门用DBNull类表示。对于VC程序员, 如果用ADO访问数据库, 则参数视为VARIANT类型, 该类型在成员vt为VT_NULL时表示SQL中的NULL值。若使用ODBC接口, 当参数为NULL的情况下, 应在调用SQLBindParameter函数时, 让最后一个参数指向含值SQL_NULL_DATA的地址。

如前所述, 参数可以是输入参数, 也可以是输出参数或输入输出参数。所有例子中的参数都是输入参数, 毕竟输入参数要常见得多。使用输出参数与使用输入参数不同的地方, 是在执行SQL语句前不必赋值, 而在执行后读取参数的值。

前面已经举例说明如何使用二进制数据类型的参数, 不过当数据非常长时, 例子中的方式并不适用。好在ADO、ADO NET和ODBC中都专门提供了大数据字段的输入方式, 有需要的读者可查阅相应的文档。注意不是所有的驱动程序都支持大数据的高效输入。

比输入大数据字段更常见的是大批量的数据输入。如果需要输入很多行的数据, 参数化方式尽管节省了编译SQL语句的时间, 但还是要逐行地执行。ADO、ADO.NET和ODBC同样也都提供了一种或多种批量输入的解决方案, 这比逐行添加要高效得多。不过如果数据量非常大的话, 最高效的方式应该是使用数据库开发商提供的专用接口。

参考文献

[1]Visual Basic 6.0联机手册Microsoft.

[2]Visual Studio.Net 2005联机手册中关于数据访问技术的章节 (http://msdn.microsoft.com/zh-cn/library/wzabh8c4 (v=VS.80) .aspx) Microsoft.

[3]Visual C++.Net 2005联机手册中关于数据访问技术的章节 (http://msdn.microsoft.com/zh-cn/library/7wtdsdkh (v=VS.80) .aspx) Microsoft.

[4]Visual C++.Net 2005联机手册中关于编译器对COM技术支持的章节 (http://msdn.microsoft.com/en-us/library/h31ekh7e (v=VS.80) .aspx) Microsoft.

[5]Windows Platform SDK中关于ADO和ODBC的章节 (http://msdn.microsoft.com/en-us/library/aa968814 (v=VS.85) .as-px) Microsoft.

3.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 姓名

4.SQL结构化查询语句 篇四

一、数据定义功能

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

删除表：Droptable表名

二、数据操纵功能

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

三、查询功能

SELECT*FROM表名

WHERE条件

ORDERBY 字段排序

GROUPBY 字段分组

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

INTOTABLE表

INTOCURSOR临时表

5.一个常用的报表统计SQL语句 篇五

-02-02二种sql分页查询语句分享

-10-10SQL Server Bulk Insert 只需要部分字段时的方法

-12-12实例学习SQL的Select命令

-01-01sqlserver获取各种形式的时间

-06-06sqlserver数据库中的表、字段sql语句

2010-11-11sqlserver中更改数据库所属为dbo的方法

2011-06-06数据库更新Sqlserver脚本总结

2013-01-01常用SQL语句(嵌套子查询/随机等等)详细整理

6.SQL常用查询总结 篇六

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课堂练习

7.SQL提高查询速度经验谈 篇七

1 建立合理的索引设计

一般说来, 索引可分为聚集索引和非聚集索引。一个表中只能有一个聚集索引。对一个表建立一个聚集索引后, 数据库会调整记录的顺序, 使表按照索引的顺序重新排列。而非聚集索引不会改变表的顺序。建立索引的有以下几种情况:

1) 主键列、经常有范围查找和ORDER BY或GROUP BY的列, 可以考虑建立聚集索引。2) 在使用频繁的列且经常需要使用函数的时候, 可以考虑使用函数索引。

索引虽有助于提高性能, 但并不是索引越多越好, 恰好相反过多的索引会导致系统低效。因为用户在表中增加一个索引, 维护索引集合就要做相应的更新工作, 同时每次有数据改变的时候就需要维护索引。

2 优化WHERE条件语句

举个例子说明更加明了:

Se le ct*from us e r_t w he re s e rial_num be r=‘13039333333’and re m ove_tag=‘0’;

Se le ct*from us e r_t w he re re m ove_tag=‘0’and s e rial_num be r=‘13039333333’;

一般我们执行这种简单的语句, 不会计较条件的先后问题。但如果re m ove_tag字段是一个聚合索引, 那么后一句就仅仅从rem ove_tag=‘0’的记录中查找;而前一句, 却要先在整表中查找s erial_num be r=‘13039333333’的记录, 然后再根据条件rem ove_tag=‘0’来提取结果。如果在记录数巨大的情况下, 提取速度就就不一样了。

3 减少格式转换

Se le ct*from us e r_t w he re to_char (in_date, ‘yyyym m dd’) be tw e e n‘20110101’and‘20110301’

s e le ct*from us e r_t w he re in_date be tw e e n to_date (‘20110101’, ‘yyyym m dd’) and to_date (‘20110301’, ‘yyyymmdd’)

后一句SQL查询速度要快于前一句。因为第一句数据库必须将每行记录的日期都转换一次, 而第二句只需对常量做转换。

4 谨慎使用is null和is notnull

不能用null做索引, 任何包含null值的列都将不会被包含在索引中。即使在列上建立索引, 只要这些列中含有null, 该列就会从索引中删除。也就是说, 如果某列存在空值, 即使对该列建索引也不会提高性能。同时, 任何在where子句中使用is null或者is notnull优化器是不允许使用索引的。

5 尽量少使用带通配符‘%’的like语句

要求在表中查询出包含‘130’的电话号, 可以采用如下查询语句:select*from user_twhere serial_numberlike‘%130%’, 这里由于通配符%在搜寻首出现, 所以即使serial_number建立了索引, 有些数据库也将不使用name的索引。

当然通配符出现在字符串其他位置时, 优化器可以利用索引。如:s e le ct*from us e r_t w he re s e rial_num be rlike‘130%’。

6 需要查询多少字段, 就查询多少字段

我们每少提取一个字段, 查询速度就会有相应的提升。所以要避免像select*from这样的查询。

7 尽量少使用distinct

Dis tinct会在很多SQL语句中出现, 但是尽量不要多用。在实际应用中, 发现有时数据库出现了多笔重复的问题, 但被该关键字给掩盖了。或者有时候查询出很多重复的数据, 有些人也许会用该关键字来解决这一问题。但是, 我觉得重复数据的出现肯定是有原因的, 如果盲目使用这种方法去解决将会遗留下很多问题。所以我的观点是:该关键字要用, 但是在数据重复原因明确的情况下使用。这样才能保证问题不会被掩盖, 保证数据库运行一段时间后不会再出现难以弥补的问题。

小技巧:去除重复值还有一个办法。用groupby依据指定的分组列对所有行进行分组, 而且每一组返咽一个行, 这与distinct得到的结果相同。

8 尽量使用内连接

根据性能测试发现内连接的性能高于外连接, 如:

Se le ct*from us e r_t a, cus t_t b, account_t c w he re a.cus t_id=b.cus t_id and b.acct_id=c.acct_id;---内连接

Se le ct*from us e r_t a le ft join (s e le ct*from cus t_t b) as b on a.cus t_id=b.cus t_id le ft join (s e le ct*from account_t c) as c on b.acct_id=c.acct_id;---外连接

完成相同的任务, 但是内连接的效率明显要高于外连接。

配置P42.4G 1G内存测试结果:

300次运行平均数据

客户端处理累计时间3.5062ms (内连接) 32.5059ms (外连接)

服务器应答累计等待时间351.62ms (内连接) 524.854ms (外连接)

CPU消耗22 (内连接) 64 (外连接)

I/O消耗107 (内连接) 118 (外连接)

但是有的时候不得已还要使用外连接, 所以要看情况, 能使用内连接时, 不要使用外连接。如果一定要用外连接, 就选择表比较小, 而且最好有建立索引的进行连接。

9 in和exists

in是把外表和内表作has h连接, 而e xis ts是对外表作loop循环, 每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当, 那么用in和exists差别并不大。如果两个表中一个较小, 一个较大, 则子查询表大的用exis ts, 子查询表小的用in.举个例子:

us e r_t (小表) , cus t_t (大表)

s e le ct*from us e r_t w he re cus t_id in (s e le ct cus t_id from cus t_t) ;效率低, 用到了us e r_t表上的cus t_id列的索引

s e le ct*from us e r_t a w he re e xis ts (s e le ct b.cus t_id from b.cus t_t w he re a.cus r_id=b.cus t_id) ;效率高, 用到了cus t_t表上的cus t_id列的索引。

1 0 硬件优化