SQL中函数介绍

SQL 中STUFF函数用法

作用：　　

　　删除指定长度的字符，并在指定的起点处插入另一组字符。

语法：

STUFF ( character_expression , start , length ,character_expression )

示例：

　　以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串　　

参数：　　　

　　character_expression：一个字符数据表达式。character_expression 可以是常量、变量，也可以是字符列或二进制数据列。

　　start：一个整数值，指定删除和插入的开始位置。如果 start 或 length 为负，则返回空字符串。如果 start 比第一个 character_expression长，则返回空字符串。start 可以是 bigint 类型。

　　length：一个整数，指定要删除的字符数。如果 length 比第一个 character_expression长，则最多删除到最后一个 character_expression 中的最后一个字符。length 可以是 bigint 类型。

返回类型：　　

　　如果 character_expression 是受支持的字符数据类型，则返回字符数据。如果 character_expression 是一个受支持的 binary 数据类型，则返回二进制数据。

备注：　　

　　如果结果值大于返回类型支持的最大值，则产生错误。

SQL中 pwdencrypt 和 pwdcompare 用法

pwdencrypt：实现对输入数据进行加密后返回二进制形式的加密内容；同样密码加密后生成结果都是不同的，并且不可逆

pwdcompare：用于检查明文是否与加密的二进制数据内容相等，没有解密函数。

这是两个SQLServer未公开的函数，主要是用于SQLServer内部自己调用。优点是调用方便，缺点是这二个函数没有公开，就意味着可能改变，并且不兼容原来的，在使用上存在风险。(只在sqlserver验证了一下)

CREATE TABLE [dbo].[UserInfo]
(
    [id] [int] NOT NULL IDENTITY(1, 1),
    [name] [varchar] (50),
    [pwd] [varbinary] (50) NULL
) 
go

insert into dbo.UserInfo values ('张三1', pwdencrypt('123456'))
insert into dbo.UserInfo values ('张三2', pwdencrypt('123456'))
insert into dbo.UserInfo values ('张三3', pwdencrypt('456456'))

比较数据是否相等

SQL中 varchar 和 Nvarchar 的区别

Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示

varchar(n)：长度为 n 个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为输入数据的字节的实际长度，而不是 n 个字节。

nvarchar(n)：包含 n 个字符的可变长度 Unicode 字符数据。n 的值必须介于 1 与 4,000 之间。字节的存储大小是所输入字符个数的两倍。

示例：　　

1、字符串：我和coffee　　

　　那么varchar字段占2×2+6=10个字节的存储空间，而nvarchar字段占8×2=16个字节的存储空间。　　

　　如字段值只是英文可选择varchar，而字段值存在较多的双字节（中文、韩文等）字符时用nvarchar　　

2、varchar（10）与nvarchar（10）　　

　　前者是非unicode型，存储字符按1个算(内部空间存储占1字节)，存储汉字的话按2个算，就是可以存10个字符或者5个汉字　　

　　后者是unicode型，存储什么都是按1个算(内部空间存储占2字节)，是可以存10个字符或10个汉字　　

　　varchar（10）与nvarchar（10）就是分别占10个字节和20个字节

SQL中 count(1)、count(*)与 count(列名) 的执行区别

COUNT() 函数返回匹配指定条件的行数

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：不包含值为NULL的记录

COUNT(*) 函数返回表中的记录数：

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

count(1) 和 count(字段)两者的主要区别是：

（1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录

（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录

执行效率上：  

1. 列名为主键，count(列名)会比count(1)快  
2. 列名不为主键，count(1)会比count(列名)快  
3. 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  
4. 如果有主键，则 select count（主键）的执行效率是最优的  
5. 如果表只有一个字段，则 select count（*）最优

Parameters.AddWithValue(“@参数”, value)方法

以前用command方法执行存储过程增加参数时，总是先用cmd.Parameters.Add方法来设置参数和参数类型，再用Parameters[0].Value来给参数赋值。以前的一个动作代码示例：

            string strConn = "Data Source=.;Initial Catalog=HISDB;Integrated Security=True";

            using( SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("AuditMessageInsert", conn);
                //设置命令类型为存储过程,没有设置的话会执行失败
                cmd.CommandType = CommandType.StoredProcedure;

                //设置参数名和类型
                cmd.Parameters.Add("@Target", SqlDbType.NChar);
                cmd.Parameters.Add("@Description", SqlDbType.NChar);
                cmd.Parameters.Add("@Actor", SqlDbType.NChar);
                cmd.Parameters.Add("@Time", SqlDbType.DateTime);
                cmd.Parameters.Add("@Computer", SqlDbType.NChar);

                //给参数赋值
                cmd.Parameters[0].Value = "ATarget";
                cmd.Parameters[1].Value = "Description";
                cmd.Parameters[2].Value = "Actor";
                cmd.Parameters[3].Value = DateTime.Now;
                cmd.Parameters[4].Value = "PC-Computer";

                cmd.ExecuteNonQuery();
            }

如果用Parameters.AddWithValue方法就不用这么麻烦了，可以直接加参数名和其值，不用再设置参数的类型，示例代码：

            string strConn = "Data Source=.;Initial Catalog=HISDB;Integrated Security=True";

            using( SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("AuditMessageInsert", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                //增加参数：名称与类型 且与表中参数的顺序没关系
                cmd.Parameters.AddWithValue("@Actor", "Actor");
                cmd.Parameters.AddWithValue("@Target", "Target");
                cmd.Parameters.AddWithValue("@Description", "Description");
                cmd.Parameters.AddWithValue("@Computer", "Computer");
                cmd.Parameters.AddWithValue("@Time", DateTime.Now);

                cmd.ExecuteNonQuery();
            }