SQL知识整理

一：Select语句：
       select 字段名 from 表名 where 条件 order by 排序

see:select distinct 从多个相同字段中抓唯一值    

see:查找ld_det_andy表中有数据但ld_det_temp表中没数据的数据
        select * from ld_det_andy a where
       (not exists (select * from ld_det_temp where ld_loc = a.ld_loc and ld_part = a.ld_part))

see:选出累计访问量最大的10个ip地址，并按访问量降序排列。
        select top 10 ip, countip from
       (select ip,count(*) as countip from records group by ip) a
       order by countip desc

see:表student(id,name,score)根据分数列（score）每10分为一段，查询每段分数的人数
        select ScoreRank,count(*) from
       (select ScoreRank = case
         when score >= 0   and score < 10   then '0-9'
         when score >= 10 and score < 20   then '10-19'
         when score >= 20 and score < 30   then '20-29'
         when score >= 30 and score < 40   then '30-39'
         when score >= 40 and score < 50   then '40-49'
         when score >= 50 and score < 60   then '50-59'
          when score >= 60 and score < 70   then '60-69'
         when score >= 70 and score < 80   then '70-79'
         when score >= 80 and score < 90   then '80-89'
         when score >= 90 and score < 100 then '90-99'
         else '100'  
         end   from student) a group by ScoreRank

see:表table1(用户名name)，请将同名的数据显示出来（要全部字段信息）
       select * from table1 where
       (name in (select name from
       (select count(*) as a,name from table1 as table1a group by name having (count(*)>1) as a))

二：Update语句：
       update 表名 set 字段名=值 where 条件
see:根据身份证更新出生日期字段
       十五位身份证
       update sy set 出生日期字段=convert(datetime,('19'+substring(身份证号码,7,6)))
       where 身份证号码 is not null and len(身份证号码)=15
       十八位身份证
       update sy set 出生日期字段=convert(datetime,substring(身份证号码,7,8))
       where 身份证号码 is not null and len(身份证号码)=18

see:依据表二字段内容更新表一对应字段内容
       update Table1 set Table1.字段=Table2.字段
       from Table1,Table2 where Table1.条件字段=Table2.条件字段

see:在SQL数据库上通过游标方式完成更新
       declare @CustomerCode varchar(5)                          --定义变量
       declare @CustomerName varchar(50)                       --定义变量
       declare cursor1 cursor for select * from table2           --定义游标
       open cursor1                                                               --打开游标
       --通过游标将表记录字段内容存储进对应变量
       fetch next from cursor1 into @CustomerCode,@CustomerName
        while @@fetch_status=0                                           --判断是否成功抓取记录字段值
       begin                                                                         --语句块开始
             update table1 set 字段=@CustomerName where 字段= @CustomerCode
             fetch next from cursor1 into @CustomerCode,@CustomerName   --继续抓下一条记录值
       end
       close cursor1                    --关闭游标
       deallocate cursor1            --释放游标

三：Delete语句：
       delete from 表名 where 条件
四：Insert语句
       insert into 表名 values (对应字段的值)
五：删除表
       drop table 表名
六：清空表
       truncate table 表名

ADO.NET数据库编程时，连接字符串见下参考：
SQL 2000版：server=(local);database=AndyERP;uid=sa;pwd=sa
SQL 2005版：@"server=./SQLEXPRESS;database=PosDB;Integrated Security=True"
ACCESS 版：
    Main.sOLEDBcn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
    Main.sOLEDBcn += Application.StartupPath.ToString() + @"/DB/Stock.mdb";

通常将数据库连接字符串放在App.config文件中，格式见下参考：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
    <add key="sqlcn" value="server=(local);database=AndyERP;uid=sa;pwd=sa"/>
</appSettings>
</configuration>

 

SQL存储过程演示：

Create Procedure Andy_Study1 @QQNumber varchar(10) as

Declare @sqlText varchar(100)

set @sqlText="Select UserName,Password from QQUsers where Number=" + @QQNumber

exec(@sqlText)

go

 

C#程序调用代码：

cmd1.CommandText="Andy_Stuey1";

cmd1.CommandType=CommandType.StoredProcedure;

cmd1.Parameters.Add(new SqlParameter("@QQNumber","80001");

程序中代码调用见下参考：
        ClassDB classDB1 = new ClassDB();
        string sSqlText = "select usercode,username from WebUsers where usercode=@UserCode and password=@Password";
        string[] sParameterName =new string[]{"@UserCode","@Password"};
        string sPassword=FormsAuthentication.HashPasswordForStoringInConfigFile(TextBox2.Text,"MD5");
        string[] sParameterValue=new string[]{TextBox1.Text,sPassword};
        if (classDB1.ReadTable(sSqlText,sParameterName,sParameterValue))
        {
            if (classDB1.Table1.Rows.Count > 0)
            {
                Session["UserCode"] = classDB1.Table1.Rows[0][0].ToString();
                Session["UserName"] = classDB1.Table1.Rows[0][1].ToString();
                Response.Redirect("Default.aspx");
            }
            else
            {
                Response.Write("<script language='javascript' type='text/javascript'>");
                string s1 = "alert('注意：用户名或密码错误。请重新登录！');";
                Response.Write(s1);
                Response.Write("</script>");
            }
        }

如果调用时不需要进行参数传递，参考下面的代码：
        ClassDB classDB1 = new ClassDB();
        string[] a1=new string[0];
        string[] a2=new string[0];
        if (classDB1.ReadTable(sSqlText, a1, a2))
        {
            GridView1.DataSource = classDB1.Table1;
            GridView1.DataBind();
        }
        if (classDB1.Table1.Rows.Count == 0)
        {
            Label1.Visible = true;
            Label1.Text = "提示：数据库表中无任何符合条件之数据记录！";
        }
        else
        {
            Label1.Visible = false;
        }

SQL字段值内容含单引号处理：
SQL 若插入的字符串若有单引号，可用二个单引号转义，见下代码参考
string s1=字符串.Replace("'", "''");