BuildQuery A Simple SQL Query Tool
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BuildQuery
{
/// <summary>
/// ʹÓÃÌṩµÄÊý¾Ý½¨Á¢Ò»¸öSQL²éѯ
/// </summary>
public class BuildQuery
{
#region ÀàµÄ±äÁ¿
int numFieldsCount, dispos; //×Ö¶ÎÊýÁ¿£¬¼Ç¼¶ººÅµÄÊýÁ¿
//
//½«ËùÓеÄ×ֶκÍÖµ·ÅÈëµ½Dictionary¼¯ºÏÀÌá¸ß²Ù×÷ЧÂÊ
//
Dictionary<string, string> dicFields = new Dictionary<string, string>(); //×ֶμ¯ºÏ
Dictionary<string, string> dicData = new Dictionary<string, string>(); //ÖµµÄ¼¯ºÏ
Dictionary<string, bool> dicQuotes = new Dictionary<string, bool>(); //ÊÇ·ñÐèÒªÒýºÅµÄ¼¯ºÏ
//
//Ìõ¼þ×ֶμ°ÖµµÄ¼¯ºÏ
//
List<string> listIDFields = new List<string>(); //×ֶμ¯ºÏ
List<string> listIDValues = new List<string>(); //ÖµµÄ¼¯ºÏ
List<bool> listIDQuotes = new List<bool>(); //ÒýºÅ¼¯ºÏ
List<string> listIDOperators = new List<string>();//²Ù×÷·û¼¯ºÏ
string strTable; //±íÃû
Boolean IdentityCheck; //ÊÇ·ñÐèÒª·µ»ØÒ»¸öеÄʵÌåÀà
/// <summary>
/// ·â×°Ò»¸öÊÇ·ñÐèҪеÄʵÌåÀà
/// </summary>
public Boolean GetIdentity
{
get { return IdentityCheck; }
set { IdentityCheck = value; }
}
/// <summary>
/// ·â×°·µ»ØÒ»¸ö²éѯµÄ½á¹û
/// </summary>
public string Table
{
get { return strTable; }
set { strTable = value; }
}
#endregion
/// <summary>
/// ÀàµÄʵÀý»¯¹¹Ôì·½·¨
/// </summary>
public BuildQuery()
{
}
/// <summary>
/// ½¨Á¢Ò»¸ö²éѯǰÇå³ýDICTIONARY¼¯ºÏµÄËùÓÐÄÚÈÝ
/// </summary>
publ
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
×î½üÕýÔÚѧϰÓÃSQL×öһЩ¶«Î÷£¬ÔÚÒ»¸öÌû×ÓÉÏÃæ¿´µ½ÕâÆª¶ÔºÜÓаïÖú£¬¾Í×ªÔØµ½ÁË×Ô¼ºµÄBlogÀϣÍû¶Ô´ó¼ÒÒ²ÓаïÖú¡£
SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù× ......
À´Ô´:SQL°ïÖúÎĵµ
CASE
¼ÆËãÌõ¼þÁÐ±í²¢·µ»Ø¶à¸ö¿ÉÄܽá¹û±í´ïʽ֮һ¡£
CASE ¾ßÓÐÁ½ÖÖ¸ñʽ£º
¼òµ¥ CASE º¯Êý½«Ä³¸ö±í´ïʽÓëÒ»×é¼òµ¥±í´ïʽ½øÐбȽÏÒÔÈ·¶¨½á¹û¡£
CASE ËÑË÷º¯Êý¼ÆËãÒ»×é²¼¶û±í´ïʽÒÔÈ·¶¨½á¹û¡£
Á½ÖÖ¸ñʽ¶¼Ö§³Ö¿ÉÑ¡µÄ ELSE ²ÎÊý¡£
Óï·¨
¼òµ¥ CASE º¯Êý£º
CASE input_expression
&n ......
#region µÃµ½ËùÓб¾µØÍøÂçÖпÉʹÓõÄSQL·þÎñÆ÷Áбí
/// <summary>
/// µÃµ½ËùÓб¾µØÍøÂçÖпÉʹÓõÄSQL·þÎñÆ÷Áбí
/// </summary>
/// <param name="p_strServerList">·þÎñÆ÷Áбí</param& ......
1) ͳ¼Æ¸÷¸öϵµÄѧÉúÐÅÏ¢
select count(Sname) ×ÜÈËÊý,Sdept from Student group by Sdept
2) ²éѯÐŹÜϵѧÉúµÄ×î´óÄêÁäºÍ×îСÄêÁä
select MAX(Sage) ×î´óÄêÁä,MIN(Sage) ×îСÄêÁä from Student where
Sdept='ÐŹÜϵ'
3) ²éѯÐŹÜϵ×î´óÄêÁäºÍ×îСÄêÁäµÄѧÉúµÄÐÕÃû
select Sname from St ......