【杂记】SQL篇

43、group by多个字段

查询每个班级男女生各多少人

Select count(id),xingbie,banji from tablename group by  xingbie,banji

42、SQL Server 导入 MDF LDF文件

EXEC  sp_attach_db  @dbname  =  'OA',

@filename1  =  'C:\OA.mdf',

@filename2  =  'C:\OA_log.ldf'

41、快速删除表

//用这句删除表中的数据是非常快的

 string sql = "truncate table KeyWordsRank";

40、循环更新

  declare @temp int

  set @temp=1

  while @temp<21

  begin

  --insert into Person (TNmae,TXingBie,Tlike) values('name001',1,'lpay')

  update Person set TNmae='name'+CONVERT(nvarchar, @temp)

  where ID=@temp

  set @temp=@temp+1

  end

39、 MSSQL 时间转换

SELECT  REPLACE(CONVERT(varchar, Date, 111 ),'/','-') date

  FROM Logs

  where ISNULL(SiteName,'')>''

38、oracle 转换成时间

  to_date('2016/4/21 9:19:05', 'yyyy-mm-dd hh24:mi:ss'),

37、谁能介绍一下 oracle 中检查点及SCN的用法。

简单来说就是你一个存储过程当中创建了一个表 table_a 然后要用insert into将其他的数据插入到这个table_a当中，但是因为你在创建过程的时候 table_a还不存在，过程就会显示有编译错误，因为table_a不存在必然导致过程无法执行，所以无法编译成功，而把insert into语句加如到 execute immediate之后则oracle不会再去理会这个对象是否存在，因此可以成功编译和执行。

35、oracle nvl 等同于 sql server isnull()

35、SQL,根据不同条件拼接不同SQL，非if拼接改为SQL where形式

（参数=0）or(参数=1 and 其他条件)or(参数=2 and 其他条件)

34 oracle连接字符串

<！--<configuration>-->

  <connectionStrings>

    <!--<add name="connStr" connectionString="Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.68)(PORT = 1521)))(CONNECT_DATA =(SID =orcl)(SERVER =geothermal)));Integrated Security=no;User ID=geothermal;Password=geothermal;Unicode=True;Max Pool Size=75; Min Pool Size=5" providerName="System.Data.OracleClient"/>-->

    <add name="connStr" connectionString="Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.122)(PORT = 1521)))(CONNECT_DATA =(SID =orcldb)(SERVER =orcname)));Integrated Security=no;User ID=username;Password=pwd;Unicode=True;Max Pool Size=75; Min Pool Size=5" providerName="System.Data.OracleClient" />

  </connectionStrings>

33、插入时返回自增ID

--执行这个SQL,就能查出来刚插入记录对应的自增列的值

insert into mytable values('李四')

select @@identity  --不推荐用这个  用scope_identity() 这个是区分作用域的  而上一个不区分只返回最后一个，微软的EF用的也是这个方法

insert into tname(name) output

inserted.ID  --inserted触发器

values('dfdf')

32、判断data中 row中某个字段是否为空

userInfo.UserName = row["UserName"] != DBNull.Value ? row["UserName"].ToString() : string.Empty;

31、SQLServer 辅助功能

set statistics time on

-- 设置SQLSever 显示详细执行消息

--CPU 时间 = 0 毫秒，占用时间 = 0 毫秒

Ctrl+L 对比两个语句执行所消耗的不同

30、创建临时表备份表

create table tablename1 as select  * from tablename2  where 1=1;--如果是1=2是指复制表结构，不复制数据，1=1可不加

 select * into test2 from PersonList

29、存储过程

--创建存储过程

create proc trim

    @str1 varchar(10)

as

begin

    --begin end内 存储过程内容

    select LTRIM(RTRIM(@str1))

end

--执行存储过程'abc'

exec trim '  abc  '

--编辑存储过程,查询表中的总数据,及当前页的数据

--pageindex,pagesize

alter proc GetPageList

    @pageIndex int,

    @pageSize int,

    @rowsCount int output

as

begin

    SET NOCOUNT ON;//不显示：（2行受到影响）

    select @rowsCount=COUNT(*) from StudentInfo where IsDelete=0

    select * from

    (select *,ROW_NUMBER() over(order by sid desc) as rowIndex

    from StudentInfo where IsDelete=0) as t1

    where rowindex between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize

end

调用

declare @temp int

exec GetPageList 1,2,@temp output

print @temp

C#调用和正常执行一样，指定名称和类型即可

//指定命令类型为存储过程
cmd.CommandType = CommandType.StoredProcedure;

28、SQL取diff

DATEDIFF(DAY, getdate(), EndTime) EndDayNum

27、判断是否为空

select * from 表 where isnull(字段,'')=''

CASE WHEN ISNULL(a.RemarkName,'')='' THEN b.NickName ELSE a.RemarkName END DispName

26、Oracle虚拟表

--dual oracle 虚表

select rownum, t3.*

  from (select count(*)

          from (select *

                  from TRIPINFO t

                 where t.starttime between 20130905001339 and 20130905202651) t2

         group by t2.starthour

         order by t2.starthour) t3

  from dual

25、查询数据库表的总数

--SQL Server 查询当前数据库表的总数

select count(*) as TableCount

from sysobjects

where type='u' and status>=0

24、MySql字符串拼接

不能直接 ' '+' ' 需要用concat(' ',' ')

UPDATE p_exp SET expdoc=REPLACE(expdoc,'/static/resources/img',CONCAT('/static/resources/img/',expcode))

这段代码是更新expdoc字段，将expdoc字段内的‘/static/resources/img’ 替换成‘/static/resources/img’+expcode。

结果： /static/resources/img/20150909

23、大小写转换

LOWER('adc') --大写转小写,sqlserver

UPPER（'text') --小写转大写,sqlserver

UCASE('adc') --大写转小写,其他

LCASE（'text') --小写转大写,其他

SELECT LCASE(column_name) FROM table_name;

22、简单易懂解释左联右联

/*例表a

aid adate

     a1

     a2

     a3

表b

bid bdate

     b1

     b2

     b4

--inner join  两个表a,b相连接,要取出id相同的字段 */

select * from a inner join b on a.aid = b.bid这是仅取出匹配的数据.

/*此时的取出的是:

a1 b1

a2 b2 */

--那么left join 指:

select * from a left join b on a.aid = b.bid

/*首先取出a表中所有数据,然后再加上与a,b匹配的的数据

此时的取出的是:

a1 b1

a2 b2

a3 空字符 */

/*同样的也有right join

指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据

此时的取出的是:

a1 b1

a2 b2

空字符 b4*/

21、事务

-- tran 是TRANSACTION  简写，没区别

begin try

    begin tran--设置反悔点,开启事务

    delete from UserInfo where UserId>5

    delete from ClassInfo

    commit tran--不反悔,提交事务

end try

begin catch

    rollback tran--反悔啦,回滚事务

end catch

--也可以不等保存提交回滚，直接回滚，适合调试，设置执行以下，看看报错不

begin tran--设置反悔点,开启事务

    delete from UserInfo where UserId>5

    delete from ClassInfo

rollback tran--反悔啦,回滚事务 没有结果

20、try catch

--异常处理

begin try

    delete from ClassInfo

end try

begin catch

    print @@error

end catch

19、循环语句 if/while

--选择语句

declare @id int

set @id=10

if @id>5

begin

    --满足条件时,执行如下代码

    print 'ok'

end

else

begin

    --不满足条件时,执行如下代码

    print 'no'

end

--循环

declare @id int

set @id=1

while @id<10

begin

    print @id

    set @id=@id+1

end

--输出1-10之间的所有偶数

declare @num int

set @num=1

while @num<11

begin

    if @num%2=0

    begin

        print @num

    end

    set @num=@num+1

end

18、变量

declare @name nvarchar(10)--声明变量，MySQL oracle于此有所不同

set @name='武大头帖'--赋值

print @name--使用输出

--全局变量：使用双 @ 符号

--@@version --数据库版本

--@@identity --进行插入后调用，返回最亲的标识值

insert into ClassInfo values('四不像123');

select @@IDENTITY--最近的insert语句的标识

--@@servername --服务器名称

--@@error --返回执行的上一个 Transact-SQL 语句的错误号，如果没有错误则返回0

--@rowcount --返回受上一语句影响的行数

17、case when

/*判不等语法：

    case

        when 条件1 then ...

        when 条件2 then ...

        when 条件3 then ...

        else ...

    end*/

//列转行

select sName 姓名,

--如果当前的STitle的值是"语文",则输出ScoreValue

max(case sTitle when '语文' then scoreValue end) 语文,

min(case when sTitle='数学' then scoreValue end) 数学,

sum(case sTitle when '英语' then scoreValue end) 英语

 from Student_Score

 group by sName

16、子查询（= in exists）

exists 效率比in高

--查询参与了考试的学生信息 exists in

select * from StudentInfo

where sId in(select distinct stuid from ScoreInfo)

select * from StudentInfo

where exists

(select * from ScoreInfo where ScoreInfo.stuId=StudentInfo.sid)

15、视图

--创建视图

create view VStudent_Class--alter

as

select StudentInfo.*,ClassInfo.cTitle from StudentInfo

inner join ClassInfo on StudentInfo.cid=ClassInfo.cId

--视图中存储的是select语句,而不是结果集数据

select * from VStudent_Class

where IsDelete=0 and cTitle='白虎'

--删除

drop view VStudent_Class

14、SQLhelper

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

namespace King.CMS.DAL

{

    public class SQLHelper

    {

        private static readonly string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        public static DataTable GetTable(string sql, CommandType type, params SqlParameter[] pars)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                using (SqlDataAdapter apter = new SqlDataAdapter(sql, conn))

                {

                    apter.SelectCommand.CommandType = type;

                    if (pars != null)

                    {

                        apter.SelectCommand.Parameters.AddRange(pars);

                    }

                    DataTable da = new DataTable();

                    apter.Fill(da);

                    return da;

                }

            }

        }

        public static int ExecuteNonquery(string sql, CommandType type, params SqlParameter[] pars)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                using (SqlCommand cmd = new SqlCommand(sql, conn))

                {

                    cmd.CommandType = type;

                    if (pars != null)

                    {

                        cmd.Parameters.AddRange(pars);

                    }

                    conn.Open();

                    return cmd.ExecuteNonQuery();

                }

            }

        }

        public static object ExecuteScalare(string sql, CommandType type, params SqlParameter[] pars)

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                using (SqlCommand cmd = new SqlCommand(sql, conn))

                {

                    cmd.CommandType = type;

                    if (pars != null)

                    {

                        cmd.Parameters.AddRange(pars);

                    }

                    conn.Open();

                    return cmd.ExecuteScalar();

                }

            }

        }

    }

}

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace t2_StudentInfo

{

    public static partial class SqlHelper

    {

        private static string connStr = ConfigurationManager.ConnectionStrings["dbtest"].ConnectionString;

        public static int ExecuteNonQuery(string sql,params SqlParameter[] ps)

        {

            using (SqlConnection conn=new SqlConnection(connStr))

            {

                SqlCommand cmd=new SqlCommand(sql,conn);

                cmd.Parameters.AddRange(ps);

                conn.Open();

                return cmd.ExecuteNonQuery();

            }

        }

        public static object ExecuteScalar(string sql, params SqlParameter[] ps)

        {

            using (SqlConnection conn=new SqlConnection(connStr))

            {

                SqlCommand cmd=new SqlCommand(sql,conn);

                cmd.Parameters.AddRange(ps);

                conn.Open();

                return cmd.ExecuteScalar();

            }

        }

        public static DataTable ExecuteTable(string sql,params SqlParameter[] ps)

        {

            using (SqlConnection conn=new SqlConnection(connStr))

            {

                SqlDataAdapter adapter=new SqlDataAdapter(sql,conn);

                //用于进行select操作，可以通过SelectCommand属性获取此操作的SqlCommand对象

                adapter.SelectCommand.Parameters.AddRange(ps);

                DataTable dt=new DataTable();

                adapter.Fill(dt);

                return dt;

            }

        }

        public static SqlDataReader ExecuteReader(string sql,params SqlParameter[] ps)

        {

            SqlConnection conn=new SqlConnection(connStr);

            SqlCommand cmd=new SqlCommand(sql,conn);

            cmd.Parameters.AddRange(ps);

            conn.Open();

            return cmd.ExecuteReader(CommandBehavior.CloseConnection);

        }

    }

}

传参调用

string sql ="";

            if (string.IsNullOrEmpty(label1.Text))

            {

                //添加

                sql = "insert into studentinfo(sname,sgender,sbirthday,cid) values(@name,@gender,@birthday,@cid)";

            }

            else

            {

                //修改

                sql = "update studentinfo set sname=@name,sgender=@gender,sbirthday=@birthday,cid=@cid where sid=" +label1.Text;

            }

            SqlParameter[] ps =

            {

                new SqlParameter("@name",textBox1.Text),

                new SqlParameter("@gender",radioButton1.Checked),

                new SqlParameter("@birthday",dtpBirthday.Value),

                new SqlParameter("@cid",cboClassInfo.SelectedValue),

            };

            int result=SqlHelper.ExecuteNonQuery(sql, ps);

            if (result > )

            {

                FreshForm();

                this.Close();

            }

            else

            {

                MessageBox.Show("保存失败");

            }

2019年8月4日

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

/// <summary>

/// 数据库基本操作类

/// </summary>

public class SQLHelper

{

    //数据库连接字符串

    private string connSTR;

    //数据库连接对象

    private SqlConnection myConnection;

    //数据库操作命令对象

    private SqlCommand myCommand;

    /// <summary>

    /// 构造函数，创建连接

    /// </summary>

    public SQLHelper()

    {

        connSTR = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;

        myConnection = new SqlConnection(connSTR);

    }

    #region 执行带参数的sql语句（插入、删除、修改）,返回-1表示执行失败

    public int ExcuSqlWithPara(string cmdText, SqlParameter[] para)

    {

        //创建Command

        myCommand = new SqlCommand(cmdText, myConnection);

        //传递参数

        for (int i = ; i < para.Length; i++)

        {

            myCommand.Parameters.Add(para[i]);

        }

        //定义返回值

        int nResult = -;

        try

        {

            //打开链接

            myConnection.Open();

            //执行SQL语句

            nResult = myCommand.ExecuteNonQuery();

        }

        catch (SqlException ex)

        {

            //抛出异常

            throw new Exception(ex.Message, ex);

        }

        finally

        {   //关闭链接

            myConnection.Close();

        }

        //返回nResult

        return nResult;

    }

    #endregion

    #region  执行带参数的sql语句（select语句），返回数据流

    public SqlDataReader GetDRWithPara(string cmdText, SqlParameter[] para)

    {

        //创建Command

        myCommand = new SqlCommand(cmdText, myConnection);

        for (int i = ; i < para.Length; i++)

        {

            myCommand.Parameters.Add(para[i]);

        }

        ///定义返回值

        SqlDataReader dr = null;

        try

        {

            ///打开链接

            myConnection.Open();

            ///执行SQL语句

            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

        }

        catch (SqlException ex)

        {

            ///抛出异常

            throw new Exception(ex.Message, ex);

        }

        return dr;

    }

    #endregion

    #region  执行带参数的sql语句（select语句），返回数据表

    /// <summary>

    /// 执行带参数的sql语句（select语句），返回数据表

    /// </summary>

    /// <param name="cmdText">带参数的SQL语句</param>

    /// <param name="para">参数列表</param>

    public DataTable GetDTWithPara(string cmdText, SqlParameter[] para)

    {

        //创建Command

        myCommand = new SqlCommand(cmdText, myConnection);

        for (int i = ; i < para.Length; i++)

        {

            myCommand.Parameters.Add(para[i]);

        }

        SqlDataAdapter da = new SqlDataAdapter();

        DataSet ds = new DataSet();

        try

        {

            ///打开链接

            myConnection.Open();

            ///执行SQL语句

            da.SelectCommand = myCommand;

            da.Fill(ds);

        }

        catch (SqlException ex)

        {

            ///抛出异常

            throw new Exception(ex.Message, ex);

        }

        //返回dr时不能关闭连接

        finally

        {   ///关闭链接

            myConnection.Close();

        }

        //返回nResult

        return ds.Tables[];

    }

    #endregion

    #region 执行不带参数的sql语句（select语句），返回数据表

    public DataTable GetDataTable(string cmdText)

    {

        ///定义返回值

        DataTable dt = null;

        try

        {

            myConnection.Open();

            SqlDataAdapter da = new SqlDataAdapter(cmdText, myConnection);

            DataSet ds = new DataSet();

            da.Fill(ds, "Table1");

            dt = ds.Tables["Table1"];

            da.Dispose();

        }

        catch (SqlException ex)

        {

            throw new Exception(ex.Message, ex);

        }

        finally

        {

            myConnection.Close();

        }

        return dt;

    }

    #endregion

}

13、cast 函数

convert cast convert就是比cast多了第三个参数，定义格式的，且都是西方的，我们根本用不到，就当做一样就可以了

elect cast(89.000000 as decimal(4,1))  --结果 89.0

select convert(decimal(4,1),89.000000) --结果 89.0

数字与字符串拼接的时候会用到

select cast(1 as char(1))+''    --错误demo select 1+'1'

12、插入时重另一个表查询值

Insert into S_Citytest(CityName) Select CityName from  S_City

11、开窗函数 OVER

--将统计信息分布到行中

求科目一的平均分

错误

Select ScorrInfo.*, avg(scorrvalue) from ScoreInfo where subId=1

正确

Select ScorrInfo.*, avg(scorrvalue) over() from ScoreInfo where subId=1

效果

10、事务实例

BEGIN TRY

    BEGIN TRANSACTION  --开始事务

    DECLARE @myMemberID NVARCHAR(36) ,--用户ID

        @myBizID NVARCHAR(36) ,

        @myBizType NVARCHAR(36) ,

        @myZanNum BIGINT

 --业务ID

    SET @myMemberID = '773415785c964cf89ad40528228f111b'

    SET @myBizID = 'B6C0738D-CDDC-4361-964B-870FAA795FD2'

    SET @myBizType = ''

--根据点赞状态更新主表点赞数量

    UPDATE  [D_Activity]

    SET     ZanNum = CASE WHEN ( SELECT IsDel

                                 FROM   dbo.D_MemberAdmire

                                 WHERE  MemberID = @myMemberID

                                        AND BizID = @myBizID

                               ) = 0 THEN ZanNum - 1--记录为0,-1

                          ELSE ISNULL(ZanNum + 1, 1)

                     END --没有记录记录为1,+1

    WHERE   ID = @myBizID

--如果存在点赞记录更新,否者插入

    IF EXISTS ( SELECT  1

                FROM    D_MemberAdmire

                WHERE   MemberID = @myMemberID

                        AND BizID = @myBizID )

        UPDATE  dbo.D_MemberAdmire

        SET     IsDel = CASE WHEN IsDel = 1 THEN 0

                             ELSE 1

                        END--1变0,0变1

        WHERE   MemberID = @myMemberID

                AND BizID = @myBizID;     

    ELSE

        INSERT  INTO D_MemberAdmire

                ( ID ,

                  BizType ,

                  BizID ,

                  MemberID ,

                  Status ,

                  IsDel

                )

        VALUES  ( NEWID() ,

                  @myBizType ,

                  @myBizID ,

                  @myMemberID ,

,

                )

    COMMIT TRANSACTION --提交事务

END TRY

BEGIN CATCH --抓取异常

    ROLLBACK TRANSACTION --回滚事务

END CATCH

9、MySQL数据库优化

SQL 优化

慢查询分析分表分区读写分离数据库集群
5.1.1
慢查询
查询优化
开启慢查询日志
EXPLAIN 工具分析

存储引擎
考虑业务场景
银行类必须用事务类引擎 innodb 较为安全效率低、
MyISAM 非事务类效率高

分表
垂直拆分：
把一个表的字段分多个表存储
水平拆分
按照ID或者其他唯一条件分到多个相同表结构中存储

分区
配置一下即可比较简单

读写分离
多数是从写数据库同步到读数据库保证数据同步

数据库集群
推荐 MyCat 搭建开源的稳定的性能强大

硬件优化
内存优化
更大内存
IO优化
IO 比如SSD告诉硬盘或者购买磁盘阵列
CPU优化
在BIOS 开启最大性能

数据库三范式列不可拆分：唯一标识：引用主键

8、项目实例声明包创建存储过程，调用

--声明包

create or replace package pkg_region_to_region

as

 type  type_cursor is ref cursor;

 procedure read_rows (header varchar2, result out type_cursor);

end pkg_region_to_region;

--创建存储过程

create or replace package body pkg_region_to_region

as

 procedure read_rows(header varchar2, result out type_cursor)

 is

  sqlText varchar2(5000);

 begin

   open result for

   select  * from (

select count(*), br2.name, br2.code,header startcode

  from (select *

          from (select R.name      startname,

                       R.code      startcode,

                       B.stationid startstationid

                  from Region R

                  left join BASECELLSTATION B

                    on B.region = R.code) br

          left join (select t.*, t.DESTINATIONSTATION stationid

                      from TRIPINFO t) TR

            on tr.stationid = br.startstationid

         where startcode = header

         order by startcode) TT1

  left join (select R.name name, R.code code, B.stationid stationid

               from Region R

               left join BASECELLSTATION B

                 on B.region = R.code) br2

    on br2.stationid = TT1.ORIGINSTATION

 GROUP by br2.code, br2.name

 order by br2.code);

  --dbms_output.put_line(sqlText);

   --sqlText;

 end read_rows;

end pkg_region_to_region;

--调用存储过程

var result refcursor

exec pkg_region_to_region.read_rows('',:result);

print result

1.连接池，知道原理就好，没办法控制

close()之后把连接放到连接池，并不是真正关闭，然后再次连接的时候，数据库直接去连接池中取这个连接，效率大大提高，sqlserver默认是开始的，如果加“looping=false”,就手动关闭，不是特殊需要，不要关闭

2.参数化SQL语句，防止SQL注入

//using 释放资源

using (SqlConnection conn = new SqlConnection("server=.;database=dbtest;uid=sa;pwd=123"))

            {

                string sql = "insert into userinfo values(@name,'202cb962ac59075b964b07152d234b70')";

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.Add(new SqlParameter("@name", textBox1.Text));

                conn.Open();

                int i = cmd.ExecuteNonQuery();

                MessageBox.Show(i.ToString());

            }

创建存储过程

create proc trim

    @str1 varchar(10)

as

begin

    select LTRIM(RTRIM(@str1))

end

--'abc'

--trim 定义的存储过程名称     @str1  参数  

--执行存储过程

exec trim '  abc  '

创建存储过程

--编写存储过程,查询表中的总数据,及当前页的数据

--pageindex,pagesize

alter proc GetPageList

    @pageIndex int,

    @pageSize int,

    @rowsCount int output

as

begin

    SET NOCOUNT ON;

    select @rowsCount=COUNT(*) from StudentInfo where IsDelete=

    select * from

    (select *,ROW_NUMBER() over(order by sid desc) as rowIndex

    from StudentInfo where IsDelete=) as t1

    where rowindex between (@pageIndex-)*@pageSize+ and @pageIndex*@pageSize

end

declare @temp int

exec GetPageList ,,@temp output

print @temp

调用存储过程

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Windows.Forms.VisualStyles;

namespace t1_ProcTest

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private int pageIndex,pageSize;

        private void Form1_Load(object sender, EventArgs e)

        {

            pageIndex = ;//设置默认是第一页

            pageSize = ;

            LoadList();

        }

        private void LoadList()

        {

            string sql = "GetPageList";//存储过程的名称

            SqlParameter pCount = new SqlParameter("@rowsCount", SqlDbType.Int);

            pCount.Direction = ParameterDirection.Output;//将参数设置为输出

            using (SqlConnection conn = new SqlConnection("server=.;database=dbtest;uid=sa;pwd=123"))

            {

                SqlCommand cmd=new SqlCommand(sql,conn);

                //指定命令类型为存储过程

                cmd.CommandType = CommandType.StoredProcedure;

                //根据存储过程来构造参数

                SqlParameter pIndex=new SqlParameter("@pageIndex",pageIndex);

                SqlParameter pSize = new SqlParameter("@pageSize",pageSize);

                //为cmd添加参数

                cmd.Parameters.Add(pIndex);

                cmd.Parameters.Add(pSize);

                cmd.Parameters.Add(pCount);

                conn.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                List<StudentInfo> list=new List<StudentInfo>();

                while (reader.Read())

                {

                    list.Add(new StudentInfo()

                    {

                        Sid = Convert.ToInt32(reader["sid"]),

                        SName = reader["sname"].ToString()

                    });

                }

                dataGridView1.DataSource = list;

            }

            //当整个操作执行完成后，连接关闭了，再去获取参数的返回值

            txtCount.Text = pCount.Value.ToString();

        }

        private void 上一页ToolStripMenuItem_Click(object sender, EventArgs e)

        {

            pageIndex--;

            if (pageIndex < )

            {

                pageIndex = ;

            }

            LoadList();

        }

        private void 下一页ToolStripMenuItem_Click(object sender, EventArgs e)

        {

            pageIndex++;

            int rowsCount = int.Parse(txtCount.Text);

            //1.1

            //计算总页数

            int pageCount = (int) Math.Ceiling(rowsCount*1.0/pageSize);

            //修正页索引，不能超出总页数

            if (pageIndex > pageCount)

            {

                pageIndex = pageCount;

            }

            LoadList();

        }

    }

}

分页存储过程

GO

/****** Object:  StoredProcedure [dbo].[execByPage]    Script Date: 2015/9/9 9:55:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[execByPage]

      @sqlQuery NVARCHAR(MAX), --//输入参数：SQL检索语句或表名

      @pageSize INT, --//输入参数：每页显示记录条数

      @pageIndex INT, --//输入参数：当前页码

      @order NVARCHAR(100)='' --排序字段 ：例如ordercol desc

AS

      SET NOCOUNT ON

      SET ANSI_WARNINGS OFF

      DECLARE @tmpTableName VARCHAR(50)

      SET @tmpTableName = '##TB1516_' + REPLACE(CAST(NEWID() AS VARCHAR(36)),

                                                '-', '') --//生成随机临时表名称

      IF ( @order != '' )

         SET @order = ' order by ' + @order

      SET @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' +

          @tmpTableName + ' from ( ' + @sqlQuery + ') t' + @order

          print(@sqlQuery);

      EXEC sp_executesql @sqlQuery --//建立并初始化临时表数据

      DECLARE @indexStart VARCHAR(20),

              @indexEnd VARCHAR(20)

      SET @indexStart = CAST(( ( @pageIndex - 1 ) * @pageSize + 1 ) AS VARCHAR(20)) --//数据起始行ID

      SET @indexEnd = CAST(( @pageIndex * @pageSize ) AS VARCHAR(20)) --//数据结束行ID

      EXEC ('select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' and ' + @indexEnd ) --//检索该页数据

      EXEC('select (case when max(ID1516)>0  then max(ID1516) else 0 end) as recordCount from ' + @tmpTableName) --//提取总条数

      EXEC('drop table ' + @tmpTableName) --//删除临时表

go

ALTER PROC [dbo].[execByPageList]

      @sqlQuery NVARCHAR(MAX), --//输入参数：SQL检索语句或表名

      @pageSize INT, --//输入参数：每页显示记录条数

      @pageIndex INT --//输入参数：当前页码

AS

BEGIN

    SET NOCOUNT ON

    SET ANSI_WARNINGS OFF

    declare @tmpTableName varchar(50)

    set @tmpTableName = '##TB1516_' + replace(cast(newid() as varchar(40)),'-','') --//生成随机临时表名称

    declare @subIndex int

    set @subIndex = charindex('from',@sqlQuery)

    if (@subIndex > 0)

    begin --//带FROM的标准检索语句

    declare @sqlQuery1 varchar(8000)

    declare @sqlQuery2 varchar(8000)

    set @sqlQuery1 = substring(@sqlQuery,1,@subIndex - 1)

    set @sqlQuery2 = substring(@sqlQuery,@subIndex,len(@sqlQuery))

    set @sqlQuery = @sqlQuery1 + ',IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' ' + @sqlQuery2

    end

    else --//不带FROM的表名

    begin

    set @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' from' + @sqlQuery

    end

    exec(@sqlQuery) --//建立并初始化临时表数据

    declare @indexStart varchar(20),@indexEnd varchar(20)

    set @indexStart = cast((@pageIndex-1)*@pageSize+1 as varchar(20)) --//数据起始行ID

    set @indexEnd = cast(@pageIndex * @pageSize as varchar(20)) --//数据结束行ID

    exec('select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' and ' + @indexEnd) --//检索该页数据

    exec('select (case when max(ID1516)>0  then max(ID1516) else 0 end) as recordCount from ' + @tmpTableName) --//提取总条数

    exec('drop table ' + @tmpTableName) --//删除临时表

动态类型

public IEnumerable<dynamic> RunDynamicList(string sql)

        {

            var table = RunSelectQuery(sql);

            if (table == null || table.Rows.Count < 1)

                return null;

            return table.AsEnumerable().Select(x => new DynamicRow(x));

        }

linq 去重

索引、触发器

CREATE TRIGGER BeforeInsertTest

  ON student

  FOR INSERT

AS

DECLARE

  @major varchar(20) ;

BEGIN

 -- 取得 专业名.

 SELECT @major = major FROM INSERTED;

 -- 更新专业的人数.

 UPDATE

  major

 SET

  snumber  = snumber + 1

 WHERE

  name = @major;

END;

go

参考地址：http://www.runoob.com/sql/sql-hosting.html

巴特西

【杂记】SQL篇

43、group by多个字段

42、SQL Server 导入 MDF LDF文件

41、快速删除表

40、循环更新

39、 MSSQL 时间转换

38、oracle 转换成时间

37、谁能介绍一下 oracle 中检查点及SCN的用法。

35、SQL,根据不同条件拼接不同SQL，非if拼接改为SQL where形式

34 oracle连接字符串

33、插入时返回自增ID

32、判断data中 row中某个字段是否为空

31、SQLServer 辅助功能

30、创建临时表备份表

29、存储过程

28、SQL取diff

27、判断是否为空

26、Oracle虚拟表

25、查询数据库表的总数

24、MySql字符串拼接

23、大小写转换

22、简单易懂解释左联右联

21、事务

20、try catch

19、循环语句 if/while

18、变量

17、case when

16、子查询（= in exists）

15、视图

14、SQLhelper

13、cast 函数

12、插入时重另一个表查询值

11、开窗函数 OVER

10、事务实例

9、MySQL数据库优化

8、项目实例声明包创建存储过程，调用

最新文章

热门文章

巴特西

【杂记】SQL篇

43、group by多个字段

42、SQL Server 导入 MDF LDF文件

41、 快速删除表

40、 循环更新

39、 MSSQL 时间转换

38、oracle 转换成时间

37、谁能介绍一下 oracle 中检查点 及SCN的用法。

35、SQL,根据不同条件拼接不同SQL，非if拼接 改为SQL where形式

34 oracle连接字符串

33、插入时返回自增ID

32、判断data中 row中某个字段是否为空

31、SQLServer 辅助功能

30、创建临时表备份表

29、存储过程

28、SQL取diff

27、判断是否为空

26、Oracle虚拟表

25、 查询数据库表的总数

24、MySql字符串拼接

23、大小写转换

22、简单易懂 解释左联右联

21、事务

20、try catch

19、循环语句 if/while

18、变量

17、case when

16、子查询 （= in exists）

15、视图

14、SQLhelper

13、cast 函数

12、插入时重另一个表查询值

11、开窗函数 OVER

10、事务实例

9、MySQL数据库优化

8、项目实例 声明包 创建存储过程，调用

最新文章

热门文章

41、快速删除表

40、循环更新

37、谁能介绍一下 oracle 中检查点及SCN的用法。

35、SQL,根据不同条件拼接不同SQL，非if拼接改为SQL where形式

25、查询数据库表的总数

22、简单易懂解释左联右联

16、子查询（= in exists）

8、项目实例声明包创建存储过程，调用