SQL知识大杂烩

$$$为每节的开始

-------------------------------------------------------------------------------------------------------------------------------------------------

$$$ SQL中系统临时表的使用和禁忌 及 Exec(SQLScript) 的使用

 

以前在SQL 中写SP 时，如比较复杂时，喜欢中间使用临时表来暂存相关记录，这样的好处有很多，提高效率，提高程序的可读性等。当然后临时表的使用，一般均会使用用户临时表，即 #TempTable， 但有一些情况下，偶尔也会使用系统临时表，即 ##TempTable。 

 

两种临时表的的使用语法差不多，可用几种方法来建立，可 Create ，也可 Select Into 。 

 

当然关键的是系统临时表和用户临时表的区别:(如下) 

1)用户临时表:用户临时表的名称以单个数字符号(#)开头; 

用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的. 

当创建它的进程消失时这个临时表就自动删除. 

2)系统临时表:系统临时表的名称以数字符号(##)开头 

全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除. 

 

明白了这些就知道了他们的用途和限制，但有一些地方还是容易出问题，故在此专门列出。 

 

1， 在使用 Exec(SQLScript) 执行Script 时，其间也相当于单独有一个进程处理，故执行期间内如果创建用户临时表的话，在执行完成后也就结束了，即执行完成后，你不可以使用在 SQLScript 中生成的用户临时表，可以用系统临时表代替。 

 

2， 在使用用户临时表时，有一个问题要注意，就是最好在建立时指定其用户为 dbo ，以避免可能的问题； 

 

3， 在使用系统临时表时，一定要考虑到，不可以将其用于多用户使用的环境功能或系统中，否则就可能出现冲突的问题，导致结果不可预料。 

 

如果在多用户使用的环境中使用系统临时表，则可能会出现多个用户同时对同一系统临时表进行处理，从而导致冲突和数据的错误。以前没有注意这一点，我就因此而浪费过不少的时间。 

 

有时需要使用Exec(SQLScript)方式产生数据，但其中只能使用系统临时表，如何处理呢？ 

 

1， 可以预先定义好一个用户临时表，然后使用 Insert #TempTable Exec(SQLScript) 的方式，即可将Exec 产生的结果记录加入用户临时表，从而避免使用系统临时表； 

 

2， 从根本上避免使用 Exec() ，可用其它方式代替。 

 

Exec() 的使用是因为有一些 Script 比较复杂，其中需要一些组合字符，如 in ('','','') 或其它可能的情况，在此情况，无法直接使用一般的Script 产生记录，只能先产生一个组合的Script ，然后用Exec 执行。

 

全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全局临时表的会话

结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动除去此表。 

在存储过程或触发器中创建的本地临时表与在调用存储过程或触发器之前创建的同名临时表不同。如果查询引用临时表，而同时有两个同名的临时表，则不定义针对哪个表解析该

查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。嵌套存储过程中对表名的所有引用都被解释为是针对该嵌套过程所创建的表，例如：

CREATE PROCEDURE Test2

AS

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (2)

SELECT Test2Col = x FROM #t

GO

CREATE PROCEDURE Test1

AS

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (1)

SELECT Test1Col = x FROM #t

EXEC Test2

GO

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (99)

GO

EXEC Test1

GO

下面是结果集：

(1 row(s) affected)

Test1Col    

----------- 

1          

(1 row(s) affected)

Test2Col    

----------- 

2          

当创建本地或全局临时表时，CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其它所有约束定义。如果在临时表中指定 FOREIGN KEY 约束，该语句将返回警告信息，指出此约

束已被忽略，表仍会创建，但不具有 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。

考虑使用表变量而不使用临时表。当需要在临时表上显式地创建索引时，或多个存储过程或函数需要使用表值时，临时表很有用。通常，表变量提供更有效的查询处理。

 

=======================================================================================================================================================

表和表变量有什么区别?

什么时候用表好,什么时候用表变量好?

 

--------------------------------

表是实体，数据保存在数据库文件内。

表变量是一种特殊变量，只对当前会话有效。

 

-----------------------------------------

对于表变量，《联机丛书》如下描述：

一种特殊的数据类型，用于存储结果集以供后续处理。该数据类型主要用于临时存储一组行，这些行将作为表值函数的结果集返回。

尽可能使用表变量而不使用临时表。table 变量有以下优点：

·table 变量的行为类似于局部变量，有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。

在其作用域内，table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的地方。但是，table 不能用在下列语句中：

　　INSERT INTO table_variable EXEC 存储过程。

　　SELECT select_list INTO table_variable 语句。

在定义 table 变量的函数、存储过程或批处理结束时，自动清除 table 变量。

·表类型声明中的 CHECK 约束、DEFAULT 值和计算列不能调用用户定义函数。

·在存储过程中使用 table 变量与使用临时表相比，减少了存储过程的重新编译量。

·涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。

·不支持在表变量之间进行赋值操作。

　　declare @t1 table(t1 int)

　　declare @t2 table(t2 int)

　　set @t1=@t2 --错误

·另外，由于表变量作用域有限，并且不是持久数据库的一部分，因而不受事务回滚的影响。

 

 

$$$ SET NOCOUNT ON/OFF 

当 SET NOCOUNT 为 ON 时，不返回计数（表示受Transact-SQL 语句影响的行数）。 

当 SET NOCOUNT 为 OFF 时，返回计数(默认为OFF)。

即使当 SET NOCOUNT 为 ON 时，也更新 @@ROWCOUNT 函数。

当 SET NOCOUNT 为 ON 时，将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时，在 Transact-SQL 语句（如 SELECT、INSERT、UPDATE 和 DELETE）结束时将不会在查询结果中显示"nn  行受影响"。

如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。

SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。

注释当SET NOCOUNT 为ON 时，不返回计数（表示受Transact-SQL 语句影响的行数）。当SET NOCOUNT 为OFF 时，返回计数。 ... 结论：我们应该在存储过程的头部加上SET NOCOUNT ON 这样的话，在退出存储过程的时候加上SET NOCOUNT OFF这样的话，以达到优化

 

 

$$$ SQLServer游标(Cursor)简介和使用说明

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

　　1.游标的组成

　　游标包含两个部分：一个是游标结果集、一个是游标位置。

　　游标结果集：定义该游标得SELECT语句返回的行的集合。游标位置：指向这个结果集某一行的当前指针。

　　2.游标的分类

　　游标共有3类：

　　API服务器游标 

Transaction-SQL游标 

API客户端游标。

其中前两种游标都是运行在服务器上的，所以又叫做服务器游标。

　　API服务器游标

　　API服务器游标主要应用在服务上，当客户端的应用程序调用API游标函数时，服务器会对API函数进行处理。使用API函数和方法可以实现如下功能：

　　打开一个连接。 

设置定义游标特征的特性或属性，API自动将游标影射到每个结果集。 

执行一个或多个Transaction-SQL语句。 

使用API函数或方法提取结果集中的行。

API服务器游标包含以下四种：静态游标、动态游标、只进游标、键集驱动游标（Primary key）

　　静态游标的完整结果集将打开游标时建立的结果集存储在临时表中，（静态游标始终是只读的）。静态游标具有以下特点：总是按照打开游标时的原样显示结果集；不反映数据库中作的任何修改，也不反映对结果集行的列值所作的更改；不显示打开游标后在数据库中新插入的行；组成结果集的行被其他用户更新，新的数据值不会显示在静态游标中；但是静态游标会显示打开游标以后从数据库中删除的行。

　　动态游标与静态游标相反，当滚动游标时动态游标反映结果集中的所有更改。结果集中的行数据值、顺序和成员每次提取时都会改变。

　　只进游标不支持滚动，它只支持游标从头到尾顺序提取数据行。注意：只进游标也反映对结果集所做的所有更改。

　　键集驱动游标同时具有静态游标和动态游标的特点。当打开游标时，该游标中的成员以及行的顺序是固定的，键集在游标打开时也会存储到临时工作表中，对非键集列的数据值的更改在用户游标滚动的时候可以看见，在游标打开以后对数据库中插入的行是不可见的，除非关闭重新打开游标。

　　Transaction-SQL游标

　　该游标是基于Declare Cursor 语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。

　　在存储过程或触发器中使用Transaction-SQL游标的过程为：

　　声明Transaction-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转换得到的数据类型。

　　使用Declare Cursor语句将Transaction-SQL游标与Select语句相关联。还可以利用Declare Cursor定义游标的只读、只进等特性。　 

使用Open语句执行Select语句填充游标。

　　使用Fetch Into语句提取单个行，并将每列中得数据移至指定的变量中。注意：其他Transaction-SQL语句可以引用那些变量来访问提取的数据值。Transaction-SQL游标不支持提取行块。

　　使用Close语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使用Open命令打开继续使用，只有调用Deallocate语句才会完全释放。

　　客户端游标

　　该游标将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都在客户端的高速缓存中进行。注意：客户端游标只支持只进和静态游标。不支持其他游标。

 

3.游标的生命周期

　　游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。

　　声明游标是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据，它只是为游标指明了相应的Select 语句。

　　Declare 游标名称 Cursor 参数

　　声明游标的参数

　　Local与Global:Local表示游标的作用于仅仅限于其所在的存储过程、触发器以及批处理中、执行完毕以后游标自动释放。Global表示的是该游标作用域是整个会话层。由连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。

　　Forward_only与Scroll:前者表示为只进游标，后者表示为可以随意定位。默认为前者。

　　Static、Keyset与Dynamic: 第一个表示定义一个游标，其数据存放到一个临时表内，对游标的所有请求都从临时表中应答，因此，对该游标进行提取操作时返回的数据不反映对基表所作的修改，并且该游标不允许修改。Keyset表示的是，当游标打开时，键集驱动游标中行的身份与顺序是固定的，并把其放到临时表中。Dynamic表示的是滚动游标时，动态游标反映对结果集内所有数据的更改。

　　Read_only 、Scroll_Locks与Optimistic：第一个表示的是只读游标，第二个表示的是在使用的游标结果集数据上放置锁，当行读取到游标中然后对它们进行修改时，数据库将锁定这些行，以保证数据的一致性。Optimistic的含义是游标将数据读取以后，如果这些数据被更新了，则通过游标定位进行的更新与删除操作将不会成功。

标准游标：

　　Declare MyCursor Cursor 

       For Select * From Master_Goods

　　只读游标

　　Declare MyCusror Cursor

　　For Select * From Master_Goods

　　For Read Only

　　可更新游标

 

　　Declare MyCusror Cursor

　　For Select * From Master_Goods

　　For UpDate

　　打开游标使用Open语句用于打开Transaction-SQL服务器游标，执行Open语句的过程中就是按照Select语句进行填充数据，打开游标以后游标位置在第一行。

 

 

打开游标

　　全局游标：Open Global MyCursor 

　　局部游标: Open MyCursor

　　读取游标数据：在打开游标以后，使用Fetch语句从Transaction-SQL服务器游标中检索特定的一行。使用Fetch操作，可以使游标移动到下一个记录，并将游标返回的每个列得数据分别赋值给声明的本地变量。

　　Fetch [Next | Prior | First | Last | Absolute　n　| Relative　n　]  From MyCursor

　　Into @GoodsID,@GoodsName

其中:Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为Next

　　Prior表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。

　　First表示返回结果集中的第一行，并且将其作为当前行。

　　Last表示返回结果集中的最后一行，并且将其作为当前行。

　　Absolute　n　如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0，则返回当前行。

　　Relative　n　如果n为正数，则返回从当前行开始的第n行，如果n为负,则返回从当前行之前的第n行，如果为0，则返回当前行。

　　关闭游标调用的是Close语句，方式如下：

　　Close Global MyCursor               Close MyCursor

　　释放游标调用的是Deallocate语句，方法如下：

　　Deallocate Glboal MyCursor       Deallocate MyCursor

　　游标实例:

　　Declare MyCusror Cursor Scroll

　　   For Select * From Master_Goods Order By GoodsID

　　   Open MyCursor

　　   Fetch next From MyCursor

   Into @GoodsCode,@GoodsName

　　   While(@@Fetch_Status = 0)

      Begin

　　             Begin

                   Select @GoodsCode = Convert(Char(20),@GoodsCode)

                   Select @GoodsName = Convert(Char(20),@GoodsName)

                   PRINT @GoodsCode + ':' + @GoodsName

             End

　　             Fetch next From MyCursor

             Into @GoodsCode,@GoodsName

　　      End

　Close MyCursor

　Deallocate MyCursor

　　修改当前游标的数据方法如下:

　　UpDate Master_Goods Set GoodsName = 'yangyang8848' Where Current Of MyCursor;

删除当前游标行数据的方法如下:

　　Delete From Master_Goods Where Current Of MyCursor

Select @@CURSOR_ROWS 可以得到当前游标中存在的数据行数。注意：此变量为一个连接上的全局变量，因此只对应最后一次打开的游标。

 

 

$$$ COMMIT TRANSACTION 

标志一个成功的隐性事务或显式事务的结束。如果 @@TRANCOUNT 为 1，COMMIT TRANSACTION 使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放事务所占用的资源，并将 @@TRANCOUNT 减少到 0。如果 @@TRANCOUNT 大于 1，则 COMMIT TRANSACTION 使 @@TRANCOUNT 按 1 递减并且事务将保持活动状态。

 

 

$$$ with(tablockx)和普通的SELECT

with(tablockx)先执行，普通的SELECT将等待前者完成.

 

普通的SELECT先执行，  with(tablockx)也将等待前者结束才执行，如果前者长时间不结束，with(tablockx)将失败。

 

 

 

with(tablockx)和带事务的SELECT或者with(tablockx) 

 

不管谁先执行，都要等待先执行的事务提交后，后一个才能执行。

 

$$$ SET QUOTED_IDENTIFIER OFF是什么意思？

 

这些是 sql-92 设置语句，使 sql server 2000/2005 遵从 sql-92 规则。 

当 set quoted_identifier 为 on 时，标识符可以由双引号分隔，而文字必须由单引号分隔。当 set quoted_identifier 为 off 时，标识符不可加引号，且必须符合所有 transact-sql 标识符规则。 

sql-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 false。当 set ansi_nulls 为 on 时，即使 column_name 中包含空值，使用 where column_name = null 的 select 语句仍返回零行。即使 column_name 中包含非空值，使用 where column_name <> null 的 select 语句仍会返回零行。 

当 set ansi_nulls 为 off 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 sql-92 标准。使用 where column_name = null 的 select 语句返回 column_name 中包含空值的行。使用 where column_name <> null 的 select 语句返回列中包含非空值的行。此外，使用 where column_name <> xyz_value 的 select 语句返回所有不为 xyz_value 也不为 null 的行。

 

$$$ SQL 基础知识回顾

                           SQL-Structured Query Language

 

--(开启SQL服务：net start mssqlserver)

 

--(在命令行中输入'sqlwb'命令可打开SQL管理器 )

 

--(如果要执行多条命令中的一条,鼠标选定后再按F5执行)

 

create database sales                           --创建一个名为sales的数据库

on

(

name='sales_data',

filename='d:/sales_data.mdf',

size=10,

maxsize=50,

filegrowth=5

)

log on

(

name='sales_log',

filename='d:/sales_log.ldf',

size=10,

maxsize=50,

filegrowth=5

)

 

drop database sales                             --删除所创建的数据库

 

sp_helpdb sales                                 --查看数据库的相关信息

 

sp_helpfile                                     --查看当前数据库数据文件与日志文件的相关信息

 

 

sp_detach_db sales                              --转移数据库时分离数据库

 

sp_attach_db sales,@filename1='数据文件路径名'  --整合分离的数据库                  

                  ,@filename2='日志文件路径名'

 

--(如何减小数据库日志文件的大小:  a.分离数据库 b.转移日志文件 c .整合数据库但不指定日志文件)

 

--数据库的备份

 

sp_addumpdevice 'disk','mydisk','d:/sales.bak'  --添加设备。disk表示目标设备类型,mydisk表示目标设备逻辑名称,d:/sales.bak表示目标设备物理名称

 

backup database sales to mydisk                 --向设备写入数据.其中的sales指数据库名,mydisk为自定的设备标示符

 

restore database sales from mydisk              --恢复数据库

 

sp_dropdevice mydisk                            --删除设备

 

EXEC sp_dboption 'sales','read only','true'     --设数据库为只读

 

EXEC sp_dboption 'sales',autoshrink,true        --设数据库为自动压缩

 

EXEC sp_dboption 'sales','single user'          --设数据库为单用户

 

--(以上命令中单引号可加可不加,但名字中出现空格的一定要加.大小写不分)

 

DBCC shrinkdatabase (sales,10)                  --将数据库中的文件减小,使数据库中有10%的可用空间

 

---------------------------------------------------------------------------------------------------------------

 

create table goods                              --建表的第一种约束语法

(

gid int primary key,

gname varchar(10) unique,

price money check(price>300),

ldate datetime default getdate()

)

 

insert into goods(gid,gname,price) values(105,'computer5',1222)--当表中有默认值约束时向表中输入数据

 

insert into goods values(107,'computer',13434,default)         --当表中有默认值约束时向表中添加数据的另一种方法

 

sp_help goods                                   -- 用来查询表的信息

 

select *from goods    --用来查询表中的内容

 

create table goods                              --建表的第二种约束语法

(

gid int constraint pg_id primary key,           --用constraint 给表中的列约束起名

gname varchar(10) constraint uq_name unique,

price money constraint ck_price check(price>300),

ldate datetime constraint df_date default getdate()

)

 

alter table goods drop 约束名                   --用来删除约束

 

 create table goods                             --建表的第三种约束语法

(

gid int not null,

gname varchar(10),

price money,

ldate datetime

)

 

alter table goods add constraint pk_id primary key(gid)

alter table goods add constraint uq_name unique(gname)

alter table goods add constraint cj_price check(price>300 and price<1000)

alter table goods add constraint df_ldate default getdate() for ldate

 

create table gp                                 --创建引用goods的表gp

(

wno int identity(1001,1) primary key,           --identity为设定自动增长列标示,1001是起始数字,references为引用

      --在插入数据时不能给自动增长列赋值，插入字符型数据与日期型数据时要用单引号

gno int constraint fk_id foreign key            --定义gno为表的外键

references goods(gid)  

)

drop table gp

create table gp

(

wno int identity(1001,1) primary key,

gno int

)

 

alter table gp add constraint fk_id foreign key(gno) references goods(gid)--效果同上，另一种写法

 

alter table 表名 add 列名 数据类型               --为表加上一列

 

alter table 表名 drop column 列名                --删除一列

 

delete from 表名 where 条件(如：gid=1001)        --删除符合where条件的一行

 

insert into 表名 values (default)                --为表附默认值

 

insert into 表名(列名) values()                  --同上

 

--默认值约束不影响历史数据！

 

--当为包含有自动增长列的表添加数据时不须为自动增长列附值

 

delete from 表名                                 --全删表中数据

 

delete from 表名 where gid=1001                  --删除符合条件（gid=1001)的数据

 

truncate table 表名                              --截断表，不可带条件，不能截断被外键引用的表，不管该表中是否有数据

 

update 表名 set 列名=列值                        --用来更新数据

 

where gid=1000 or gid=1001

 

update 表名 set 列名=列值                        --同上

 

where gid in(1000,1001)

 

update 表名 set 列名1=列值1，列名2=列值2,......  --为多列更新值

 

where 条件

 

--事务可分为3种:1。显式事务 2。隐式事务 3。自动提交事务（系统默认为自动提交事务）    

 

 

select * from 表名                               --查询表中数据

 

begin tran t1            --开始一个显式事务 

 

update 表名 set 列名=列值                        --更新数据

 

where not 条件                                   --更新条件

 

rollback t1                                     --回滚一个事务    

 

commit t1                                       --提交事务（以后不能再回滚了）

 

--隐式事务通过 SET IMPLICIT_TRANSACTIONS ON语句将隐式事务设为开,当连接以隐式事务操作时，

--将在提交或回滚后自动启动新事务，无须描述事务的开始，只须提交或回滚事务

 

SET IMPLICIT_TRANSACTIONS ON

 

 

select * into 新表名 from 旧表名                 --备份现有表数据到新表中，它能复制表的结构，数据。

--还可以加上条件过滤如果只想复制到指定列，用列名代替*即可

 

--如果只想复制表的结构而不想复制数据，加上永不成立条件。（如where 1>3) 

 

--该语句自动创建新表，但原表的约束关系不能被复制，但not null与identity属性可复制

 

select 列名1，列名2，列名3,...... 

into 新表名 from 旧表名    --备份现有表中的部分数据到新表中

 

alter table gp add constraint gp_id foreign key(gno) references

goods(gid) on delete cascade on update no action --这是用来进行级连更新和删除的语法，

      --在on的后面可加上: 1.delete cascade 2.delete no action 3.update cascade 4.update no action

 

--------------------------------------------------------------------------------------------------------------------------------------------------

 

create table gp

(

pid int identity(100,1) primary key,

pname varchar(10),

ptel varchar(12) check(ptel like '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

)

 

                                                --这是电话号码约束的方法

 

select host_name()        --查看本机名

 

select getdate()                                --获取当前时间

 

select user                                     --查看当前用户

 

xp_cmdshell 'dir'                               --执行DOS命令dir，将DOS命令写在''中间即可

 

xp_cmdshell 'net user EKIN 1234 /add'           --添加windows用户，EKIN为用户名，1234为密码

 

 

xp_cmdshell 'net user EKIN /delete'             --删除windows用户

 

 

xp_cmdshell 'net user administrator 9527'       --修改管理员密码

 

Uniqueidentifier              --这是数据类型的一种，全球唯一的标示符，用newid()函数给这个类型的数据提供值

 

 

select *from gp INNER JOIN goods on gp.gno=goods.gid               --内联接 仅显示两个联接表中的匹配行的联接

 

select *from gp LEFT OUTER JOIN goods on gp.gno=goods.gid          --左向外联接  包括第一个命名表（“左”表，出现在JOIN子句的最左边）中的所有行。不包括“右”表中的不匹配行   

 

select *from gp right OUTER JOIN goods on gp.gno=goods.gid         --右向外联接  包括第二个命名表（“右”表，出现在JOIN子句的最右边）中的所有行。不包括“左”表中的不匹配行  

 

select *from gp full OUTER JOIN goods on go.gno=goods.gid          --完整外部联接  包括所有联接表中的所有行，不管它们是否匹配

 

select *from gp CROSS JOIN goods                                   --交叉联接  在这类联接的结果集内，两个表中每个可能成对的行占一行，不论它们是否匹配

 

 

select *from goods where price between 1300 and 1800               --区间查询。查价格在1300-1800间的货物

 

select *from goods where gid in(1001,1003)                         --查询货物ID为1001和1003的产品，不是查1001与1003之间的货物！in前加not指除了1001和1003以外的货物

 

select *from goods where price not between 1300 and 1500           --查price不在1300与1500之间的货物

 

select *from goods where price is null                             --查询价格为空的货物

 

select *from goods where gname like ' '                            --模糊查询。''中加 % 代表gname中的任意字符， _ 代表gname中的一个字符，[ ]代表一个区间，[^]代表不在这区间

 

                   --比如:select *from Renyuan where age like '2[^1-4]'

 

--在SQL中一个汉字与一个符号或字母都只占一个字符，  用  nchar  可录汉字。

 

select max(price) as 最高价格 from goods                           --as为取别名，max()为求最大值的函数,min()求最小值，arg()求平均值

 

 

select sum(price) from goods                                       --求price 的总和，sum()用来求总和的

 

--单行与聚合不能一起使用，除非依据单行进行分组。（比如：select gid, max(price）from goods) 

 

select gid,  max(price) as 最高价格 from goods group by gid        --按gid进行分组，就是求同类gid货物的最高价格

 

--在where子句中不能出现聚合函数（比如：where max(price)>1300)

 

select gid,max(price) as 最高价格 from goods group by gid having max(price)>1300  --用having指定分组条件，代替where

 

create table info

(

 ino int,age int

)

 

insert into info values(12,22)

select *from info order by ino asc,age desc                        --order by指定排序条件，asc表示升序，desc表示降序.

 

--以上这个程序结果为编号按照升序排列，在编号相同的情况下，按age降序排列

 

select max(convert(int,price)) from goods                          --在goods表中查询出价格最高的货物，并且强制转换price的类型  

 

select top 1 * from goods where price>4000      --在goods表中查询出价格大于4000的货物资料，并只显示第一条记录