Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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 <score;
9


Ïà¹ØÎĵµ£º

linq »ñÈ¡Êý¾Ý¿âʱ¼ä »òÕßÖ´ÐÐÆäËû´ø·µ»ØÖµµÄsql²éѯ

 using (LongXingDBDataContext db = new LongXingDBDataContext())
             {
                 DeviceInfo di;
        ......

ÈçºÎ²é¿´SQL Server 2005°æ±¾ MS2005°æ±¾ MS2000°æ±¾

Ҫȷ¶¨ËùÔËÐÐµÄ SQL Server 2005 µÄ°æ±¾£¬ÇëʹÓà SQL Server Management Studio Á¬½Óµ½ SQL Server 2005£¬È»ºóÔËÐÐÒÔÏ Transact-SQL Óï¾ä£º
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
½á¹û£º
RTM 2005.90.1399
SQL Server 2005 Service Pack 1 2005.90. ......

ÔÚSQL Server 2005 ÖÐʹÓÃ.net³ÌÐò¼¯µÄÒ»Ïî×¢Òâ

ÔÚʹÓÃCLR´æ´¢¹ý³ÌÖÐÓöµ½µÄһЩÎÊÌ⣬ÔÚÕâÀï½øÐмǼ£º
´ò¿ªCLRµÄÖ§³Ö
--ÔÚSql ServerÖÐÖ´ÐÐÕâ¶Î´úÂë¿ÉÒÔ¿ªÆôCLR
exec sp_configure 'show advanced options', '1';
go
reconfigure;
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
exec sp_c ......

C# ±àдSQL SERVER 2005 µÄ´æ´¢¹ý³Ì


ÒÔÏÂÊÇÒ»¸ö²éѯIPµØÖ·¹éÊôµØµÄCLR´æ´¢¹ý³Ì£¬Èý²½£º
1¡¢ÓÃC#À´×öDLL£¬´úÂëÈçÏ£º
//====================================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.Sq ......

SQLʹÓÃÁ´½Ó·þÎñÆ÷Ö´ÐÐÔ¶³ÌÊý¾Ý¿âÉϵĴ洢¹ý³Ì

 
--´´½¨Á´½Ó·þÎñÆ÷
exec sp_addlinkedserver 'server_tmp','','SQLOLEDB','Ô¶³Ì·þÎñÆ÷Ãû»òipµØÖ·'
exec sp_addlinkedsrvlogin 'server_tmp','false',null,'Óû§Ãû','ÃÜÂë'
exec sp_serveroption 'server_lnk','rpc out','true' --Õâ¸öÔÊÐíµ÷ÓÃÁ´½Ó·þÎñÆ÷ÉϵĴ洢¹ý³Ì
go
 
--µ÷Óãº
exec server_tmp.Êý¾ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ