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
Ïà¹ØÎĵµ£º
Ò»¡¢ SQLCLRȨÏÞ¼¯¼¶±ð
¡¡¡¡µ±ÄãʹÓÃCREATE ASSEMBLYÓï¾ä°ÑÒ»¸ö³ÌÐò¼¯¼ÓÔص½Ò»¸öÊý¾Ý¿âÖÐʱ£¬SQL ServerÌṩÁËÈýÖÖȨÏÞ¼¯¼¶±ð£ºSAFE£¬EXTERNAL_ACCESSºÍUNSAFE¡£ÕâЩȨÏÞ¼¯ÐγÉÈçͼ3ºÍͼ5£¨¾ùÇë²Î¿¼µÚ¶þƪ£©ËùʾµÄ AppDomain²ßÂÔ¼¶±ð¡£
¡¡¡¡ÏÂÃæÊÇÒ»¸öµäÐ͵ÄÓï¾ä£¬ËüʵÏÖ°²×°Î»ÓÚFileLoader.dllÎļþÄÚµ ......
ÔÚÍøÉÏÕÒÁ˺öà¹ØÓÚSqlServer2005תaccessµÄ·½·¨£¬´ó¶à¶¼ÊÇÓÃsqlÓï¾ä£¬¶øÎÒ²»ÏëÓÃÄÇÖÖ·½·¨£¬ÓÚÊÇÎÒ×Ô¼ºÃþË÷³öÀ´ÈçºÎ½«SqlServerÊý¾Ý¿âת»»³Éaccess£¬·½·¨¿ÉÄÜÓе㱿£¬²»¹ýÍê³ÉÁËת»»µÄ¹¦ÄÜ¡£ÏÂÃæÊǾßÌå²½Ö裺
1.´ò¿ªAccess£¬½¨Á¢Ò»¸öºó׺ÃûΪ.mdbµÄ¿ÕµÄÊý¾Ý¿âÎļþ£¨Ä¬ÈϺó׺¿ÉÄÜÊÇ.accdb,ÎÒÃÇ°ÑËü¸ÄΪ.mdb¾Í¿ÉÒÔÁË£©£¬È»ºó ......
תÌù×Ôteched½²Ê¦: Å£¿É
»ù±¾¸ÅÄî:
µÚÒ»²ã ·þÎñÖ÷ÃÜÔ¿
±¸·Ý·þÎñÖ÷ÃÜÔ¿
backup service master key to file='c:\smk.bak'
encryption by password='P@ssw0rd'
restore service master key from file='c:\smk.bak'
decryption by password='P@ssw0rd'
µÚ¶þ²ã Êý¾Ý¿âÖ÷ÃÜÔ¿
1)±ØÐëÏÈÔÚ ......
CREATE OR REPLACE PACKAGE BODY PACK_RISK_FUNCTION AS
--- 1 ½«·ûºÅÌæ»»³É#ºÅ »òÐí¿ÉÒÔÓÃÕýÔò±í´ïʽ£¬µ«ÊÇÏÓÂé·³»¹ÊÇÖ±½ÓÓÃÌæ»»
FUNCTION CHANGE_OPERATOR(FORMULA VARCHAR2)
RETURN VARCHAR2
AS
V_FORMULA VARCHAR2(100);
BEGIN
V_FORMULA := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FORMULA,'(',''), ......
sql serverÈÕÆÚʱ¼äת×Ö·û´®
ÈÕÆÚʱ¼äת×Ö·û´®
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(1 ......