mssql查询(高级).doc

1.UNION,EXCEPT,INTERSECT运算符 A,UNION 运算符 UNION 运算符通过组合其他两个效果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个效果表。 当 ALL 随 UNION 一起运用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 便是来自 TABLE2。 B, EXCEPT 运算符 EXCEPT 运算符通过包含全部在 TABLE1 中但不在 TABLE2 中的行并消除全部重复行而派生出一个效果表。 当 ALL 随 EXCEPT 一起运 用时 (EXCEPT ALL)，不消除重复行。 C,INTERSECT 运算符 INTERSECT 运算符通过只包含 TABLE1 和 TABLE2 中都有的行并消除全部重复行而派生出一个效果表。 当 ALL 随 INTERSECT 一起运用时 (INTERSECT ALL)，不消除重复行。 注：运用运算词的几个查询效果行有必要是一起的。 - 2.外联接 A、left outer join： 左外联接（左联接）：效果集几包含联接表的匹配行，也包含左联接表的全部行。 sql: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c B：right outer join: 右外联接(右联接)：效果集既包含联接表的匹配联接行，也包含右联接表的全部行。 C：full outer join： 全外联接：不只包含符号联接表的匹配行，还包含两个联接表中的全部记载。 - 其次，我们来看一些不错的sql语句 1、说明：拷贝表(只拷贝规划,源表名：a 新表名：b) (Access可用) 法一：select * into b from a where 11 法二：select top 0 * into b from a 2、说明：仿制表(仿制数据,源表名：a 政策表名：b) (Access可用) insert into b(a, b, c) select d,e,f from b; 3、说明： 跨数据库之间表的仿制(具体数据运用绝对路径) (Access可用) insert into b(a, b, c) select d,e,f from b in 具体数据库 where 条件 比如：.from b in &Server.MapPath(.)&data.mdb & where. 4、说明：子查询(表名1：a 表名2：b) select a,b,c from a where a IN (select d from b ) 或许: select a,b,c from a where a IN (1,2,3) 5、说明：闪现文章、提交人和结束回复时辰 select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 6、说明：外联接查询(表名1：a 表名2：b) select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 7、说明：在线视图查询(表名1：a ) select * from (SELECT a,b,c FROM a) T where t.a 1; 8、说明：between的用法,between捆绑查询数据规划时包含了距离值,not between不包含 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值1 and 数值2 9、说明：in 的运用办法 select * from table1 where a not in (值1,值2,值4,值6) 10、说明：两张关联表，删去主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 11、说明：四表联查疑问： select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where . 12、说明：日程安排提前五分钟提示 sql: select * from 日程安排 where datediff(minute,f初步时辰,getdate()5 13、说明：一条sql 语句搞定数据库分页 select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a, 表名 b where b.主键字段 = a.主键字段 order by a.排序字段 14、说明：前10条记载 select top 10 * form table1 where 规划 15、说明：选择在每一组b值一样的数据中对应的a最大的记载的全部信息 (类似这样的用法可以用于论坛每月排行榜,每月热销商品分析,按类别成绩排名,等等.) select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 16、说明：包含全部在 TableA 中但不在 TableB和TableC 中的行并消除全部重复行而派生出一个效果表 (select a from tableA ) except (select a from tableB) except (select a from tableC) 17、说明：随机取出10条数据 select top 10 * from tablename order by newid() 18、说明：随机选择记载 select newid() 19、说明：删去重复记载 Delete from tablename where id not in (select max(id) from tablename group by col1,col2,.) 20、说明：列出数据库里全部的表名 select name from sysobjects where type=U 21、说明：列出表里的全部的 select name from syscolumns where id=object_id(TableName) 22、说明：列示type、vender、pcs字段，以type字段摆放，case可以便当地结束多重选择，类似select 中的case。 select type,sum(case vender when A then pcs else 0 end), sum(case vender when C then pcs else 0 end), sum(case vender when B then pcs else 0 end) FROM tablename group by type 闪现效果： type vender pcs 电脑 A 1 电脑 A 1 光盘 B 2 光盘 A 2 手机 B 3 手机 C 3 23、说明：初始化表table1 TRUNCATE TABLE table1 24、说明：选择从10到15的记载 select top 5 * from (select top 15 * from table order by id asc) table_别号 order by id desc 随机选择数据库记载的办法（运用Randomize函数，通过SQL语句结束） 对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们可以太慢了些。 你不能需要ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环： Randomize RNumber = Int(Rnd*499) +1 While Not objRec.EOF If objRec(ID) = RNumber THEN . 这里是实行脚本 . end if objRec.MoveNext Wend 这很简单知道。首要，你取出1到500规划之内的一个随机数（假定500便是数据库内记载的总数）。 然后，你遍历每一记载来检验ID 的值、查看其是不是匹配RNumber。满意条件的话就实行由THEN 关键字初步的那一块代码。 假定你的RNumber 等于495，那么要循环一遍数据库花的时辰可就长了。 尽管500这个数字看起来大了些，但比较更为安靖的公司解决方案这仍是个小型数据库了，后者一般在一个数据库内就包含了不可胜数条记载。 这时分不就死定了？ 选用SQL，你就可以很快地找出准确的记载而且翻开一个只包含该记载的recordset，如下所示： Randomize RNumber = Int(Rnd*499) + 1 sql = SELECT * FROM Customers WHERE ID = & RNumber set objRec = ObjConn.Execute(SQL) Response.WriteRNumber & = & objRec(ID) & & objRec(c_email) 不用写出RNumber 和ID，你只需要查看匹配情况即可。 只需你对以上代码的作业满意，你自可按需操作“随机”记载。 Recordset没有包含其他内容，因此你很快就能找到你需要的记载这样就大大降低了处置时辰。 - 再谈随机数 如今你下定决心要榨干Random 函数的结束一滴油，那么你可以会一次取出多条随机记载或许想选用一定随机规划内的记载。 把上面的标准Random 示例扩展一下就可以用SQL应对上面两种情况了。 为了取出几条随机选择的记载并存放在同一recordset内，你可以存储三个随机数，然后查询数据库获得匹配这些数字的记载： sql = SELECT * FROM Customers WHERE ID = & RNumber & OR ID = & RNumber2 & OR ID = & RNumber3 假定你想选出10条记载（也许是每次页面装载时的10条联接的列表）， 你可以用BETWEEN 或许数学等式选出第一条记载和恰当数量的递加记载。 这一操作可以通过好几种办法来结束，可是 SELECT 语句只闪现一种可以（这里的ID 是自动生成的号码）： sql = SELECT * FROM Customers WHERE ID BETWEEN & RNumber & AND & RNumber & + 9 注意：以上代码的实行目的不是查看数据库内是不是有9条并发记载。 随机读取若干条记载， 检验过 Access语法：SELECT top 10 * From 表名 ORDER BY Rnd(id) sql server:select top n * from 表名 order by newid() mysqlelect * From 表名 Order By rand() Limit n Access左联接语法(迩来开发要用左联接,Access帮忙啥都没有,网上没有Access的SQL说明,只需自个检验, 如今记下以备后查) 语法elect table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where . 运用SQL语句 用.代替过长的字符串闪现 语法： SQL数据库： select case when len(field)10 then left(field,10)+. else field end as news_name,news_id from tablename Access数据库：SELECT iif(len(field)2,left(field,2)+.,field) FROM tablename; Conn.Execute说明 Execute办法该办法用于实行SQL语句。根据SQL语句实行后是不是回来记载集，该办法的运用格式分为以下两种： 1实行SQL查询语句时，将回来查询得到的记载集。 用法为：Set 方针变量名=联接方针.Execute(SQL 查询言语) Execute办法调用后，会自动创建记载集方针，并将查询效果存储在该记载方针中， 通过Set办法，将记载集赋给指定的方针保管，往后方针变量就代表了该记载集方针。 2实行SQL的操作性言语时，没有记载集的回来。此时用法为： 联接方针.Execute SQL 操作性语句 , RecordAffected, Option RecordAffected 为可选项，此出可放置一个变量，SQL语句实行后，所收效的记载数会自动保管到该变量中。 通过访问该变量，就可知道SQL语句队多少条记载进行了操作。 Option 可选项，该参数的取值一般为adCMDText，它用于告诉ADO，大约将Execute办法之后的第一个字符解释为指令文本。 通过指定该参数，可使实行更高效。 BeginTrans、RollbackTrans、CommitTrans办法 这三个办法是联接方针供应的用于事务处置的办法。BeginTrans用于初步一个事物；RollbackTrans用于回滚事务； CommitTrans用于提交全部的事务处置效果，即供认事务的处置。 事务处置可以将一组操作视为一个整体，只需全部语句都成功实行后，事务处置才算成功； 若其中有一个语句实行失利，则整个处置就算失利，并恢复到处里前的情况。 BeginTrans和CommitTrans用于符号事务的初步和结束，在这两个之间的语句，便是作为事务处置的语句。 区分事务处置是不是成功，可通过联接方针的Error集结来结束，若Error集结的成员个数不为0，则说明有差错发生，事务处置失利。 Error调会合的每一个Error方针，代表一个差错信息。 - transact-sql高级查询（下） 5:运用having关键字来选择效果 6:运用compute和compute by子句 7:运用嵌套查询 8:分布式查询 E:运用having关键字来选择效果 当结束对数据效果的查询和核算后,可以运用having关键字来对查询和核算的效果进行一步的选择 例:检索出work表中学历是大专或许是中专的人数 select 学历,count(学历) from work group by 学历 having 学历 in(大专,中专) 说明:1:having关键字都与group by用在一起. 2:having不支持对列分配的别号 例如:select 学历,大于5的人数=count(学历) from work group by 学历 having 大于5的人数5 错错 改为:select 学历,大于5的人数=count(学历) from work group by 学历 having count(学历)5 F:运用compute和compute by 运用compute子句容许一起查询查询所得到各列的数据的细节以及核算各列数据所发生的汇总列 select * from work 查询所得到的各列的数据的细节 compute max(底子薪酬),min(底子薪酬) 核算之后的效果 这个比如中没有运用by关键字,回来的效果是结束添加了一行底子薪酬的最大值和最小值,也可增加by关键字. 例:select * from work order by 学历 compute max(底子薪酬),min(底子薪酬) by 学历 比较:select 学历,max(底子薪酬),min(底子薪酬) from work group by 学历 说明:1:compute子句有必要与order by子句用在一起 2:compute子句可以回来多种效果集.一种是体现数据细节的数据集,可以按分类需要进行正确的分类；另一种在分类的基础上进行汇总发生效果. 3:而group by子句对每一类数据分类之后只能发生一个效果,不能知道细节 G:运用嵌套查询 查询中再查询,一般是以一个查询作为条件来供另一个查询运用 例:有work表和有些表 A:检索出在有些表中挂号的全部有些的职工底子资料 select * from work where 有些编号 in not in(select 有些编号 from dbo.有些) B:检索出在work表中每一个有些的最高底子薪酬的员薪酬料 select * from work a where 底子薪酬=(select max(底子薪酬) from work b where a.有些称谓=b.有些称谓) 说明:由外查询供应一个有些称谓给内查询,内查询运用这个有些称谓找到该有些的最高底子薪酬,然后外查询根据底子薪酬区分是不是等于最高薪酬,如果是的,则闪现出来. 相当于:select * from work,(select 有些称谓,max(底子薪酬) as 底子薪酬 from work group by 有些称谓 as t) where work.底子薪酬=t.底子薪酬 and work.有些称谓=t.有些称谓 C:用嵌套work表和嵌套有些表,在嵌套work表中检索出姓名和职工号都在嵌套有些存在的员薪酬料 select * from 嵌套work where 职工号 in (select 职工号 from 嵌套有些) and 姓名 in (select 姓名 from 嵌套有些) 调查效果,分析缘由 改:select * from 嵌套work a,嵌套有些 b where a.职工号=b.职工号 and a.姓名=b.姓名 改:select * from 嵌套work where 职工号=(select 职工号 from 嵌套有些) and 姓名=(select 姓名 from 嵌套有些) 行吗?为啥,分析缘由? 在嵌套中运用exists关键字存在 例:1:用嵌套work表和嵌套有些表,在嵌套work表中检索出姓名和职工号都在嵌套有些存在的员薪酬料 select * from 嵌套work a where exists (select * from 嵌套有些 b where a.姓名=b.姓名 and a.职工号=b.职工号) 2:在work表检索出在有些表没有的职工 select * from work where not exists (select * from 有些 where 有些.有些编号=work.有些编号) 能否改成:select * from work where exists (select * from 有些 where 有些.有些编号work.有些编号) 在列清单中运用select 例:1:在work1表和有些表中检索出全部有些的有些称谓和底子薪酬总和 select 有些称谓,(select sum(底子薪酬) from work1 b where a.有些编号=b.有些编号) from 有些 a 2:检索各有些的职工人数 select 有些编号,有些称谓,(select count(职工号) from work1 a where a.有些编号=b.有些编号) as 人数 from 有些 b 3:在商品表和出售表中查询每一职工的姓名,所属有些,出售总量 select 姓名,所属有些,(select sum(出售量) from 商品出售 a where a.职工号=b.职工号) as 出售总量 from 嵌套有些 b H:分布式查询 我们从前的查询都只是根据一个服务器中的一个数据库的查询,如果一个查询是要跨过一个服务器,像这样的查询便是分布式查询,那么我们以看到分布查询便是数据源自于两个服务器.要进行分布式查询有必要先创建一个“联接服务器”,以便让本地的用户可以映射到进程服务器. “联接服务器”的创建 A:在“联接服务器”里面输入往后为了便当访问该联接服务器的称谓尽情 B:在“供应程序称谓”里面选择“Microsoft OLE DB Provider for SQL Server” C:在“数据源”里面输入服务器的网络名 D:本地登录,远程用户和远程暗码里面分别输入一个本地登录用户,远程登录和远程暗码以便让本地SQL Server登录映射为联接服务器上的用户 E:访问办法:格式:联接服务器的称谓.数据库名.dbo.表名 联接服务器有两个特征: 1:通过联接服务器不能删去联接源服务器的任何对像. 2:能过联接服务器可以对联接源服务器的表进行insert,updae,delete操作. 视图 1:啥是视图 2:视图和查询的区别 3:视图的利益 4:怎样创建和处理视图 5:怎样通过视图批改底子表的数据 6:怎样通过视图结束数据的安全性 A:啥是视图: 视图(view):从一个或几个底子表中根据用户需要而做成一个虚表 1:视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据 2:视图只在刚刚翻开的一瞬间,通过定义从基表中搜集数据,并展现给用户 B:视图与查询的区别: 视图和查询都是用由sql语句组成,这是他们一样的当地,可是视图和查询有着实质区别: 它们的区别在于:1:存储上的区别:视图存储为数据库描写的一有些,而查询则不是. 2:更新捆绑的需要不一样 要注意:因为视图来自于表,所以通过视图可以直接对表进行更新,我们也可以通过update语句对表进行更新,可是对视图和查询更新捆绑是不一样的,以下我们会知道尽管通过视图可以直接更新表可是有许多捆绑. 3:排序效果:通过sql语句,可以对一个表进行排序,而视图则不可. 比如:创建一个富含order by子句的视图,看一下可以成功吗? C:视图的利益: 为啥有了表还要引入视图呢？这是因为视图具有以下几个利益: 1:能切开数据,简化观念 可以通过select和where来定义视图,然后可以切开数据基表中某些关于用户不关心的数据,运用户把注意力会合到所关心的数据列.进一步简化阅览数据作业. 2:为数据供应一定的逻辑独立性 如果为某一个基表定义一个视图,即使往后底子表的内容的发生改变了也不会影响“视图定义”所得到的数据 3:供应自动的安全保护功用 视图能像底子表一样颁布或撤消访问容许权. 4:视图可以直接对表进行更新,因此视图的更新便是表的更新 D:视图的创建和处理 视图的创建 1:通过sql语句 格式:create view 视图名 as select 语句 试一试:分别创建关于一个表或多个表的视图因为视图可以来自于多表 2:通过公司处理器 说明:1:在结束视图的创建之后,就可以像运用底子表一样来运用视图 2:在创建视图时,并非全部的select子查询都可用 如:compute和compute by,order by除非与top一起连用 3:但在查询时,依然都可以用在创建时禁用的select子查询 4:在视图创建时,有必要为没有标题列指定标题思考:能否不用select语句来创建一个视图 视图的删去: 1:通过sql语句:drop view 视图名 2:通过公司处理器 说明:与删去表不一样的是,删去视图后只是删去了视图了定义,并没有删去表中的数据.查看关联性 批改视图的定义 1:通过公司处理器 2:通过sql语句: 格式:alter view 视图名 as 新的select语句 阅览视图信息 sp_helptext 视图名 查看视图创建的语句 E:怎样通过视图批改底子表的数据. 1:在视图上运用insert语句 通过视图刺进数据与直接在表中刺进数据一样,但视图终究不是底子表.因此在进行数据刺进时仍是有一定的捆绑 1:如果视图上没有包含底子表中特色为not null不能为空的列,那么刺进操作会因为那些列是null值而失利. 2:如果某些列因为某些规则或捆绑的捆绑而不能直接接受从视图刺进的列时,刺进会失利 3:如果在视图中包含了运用核算函数的效果,或是包含核算列,则刺进操作会失利 4:不能在运用了distinct语句的视图中刺进值 5:不能在运用了group by语句的视图中刺进值 2:运用update更新视图中的数据 1:更新视图与更新表格一样,可是在视图中运用了多个底子表联接的情况下,每次更新操作只能更新来自底子表的一个数据列 例如:创建以下视图:create view del as select 职工号,姓名,有些称谓,负责人 from work1,有些 where work1.有些编号=有些.有些编号 如果再实行下面的语句时: update del set 职工号=001,有些称谓=wenda where 职工号=01出现差错 只可以改成:update del set 职工号=001 where 职工号=01 update del set 有些称谓=wenda where 职工号=01 2:不能在运用了distinct语句的视图中更新值 3:不能在运用了group by语句的视图中更新值 3:运用delete删去视图中数据. 通过视图删去数据结束体现为从底子表中删去数据 格式:delete 视图名 whe