Sqlserver数据库学习

数据库相关技术学习

1.sqlserver创建索引

CREATE INDEX PersonIndex ON 表名 (字段名)

2.调用Sqlserver存储过程

 /// <summary>
        /// 运行存储过程,返回DataSet
        /// </summary>
        /// <param name="ProcedureName">存储过程名称</param>
       /// <param name="sps">参数</param>
        /// <returns></returns>
        public static DataSet runProcedure(string ProcedureName, List<sp_param> sps)
        {
            string connstr = Properties.Settings.Default.lswxConnectionString;
            SqlCommand cm = new SqlCommand();
            cm.Connection = new SqlConnection(connstr);
            cm.Connection.Open();
            cm.CommandText = ProcedureName;

            if (sps != null && sps.Count > 0)
            {
                foreach (var sp in sps)
                {
                    SqlParameter sparam = new SqlParameter(sp.param_name, sp.param_value);
                    cm.Parameters.Add(sparam);
                }
            }

            cm.CommandType = CommandType.StoredProcedure;
            DataSet DS = null;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DS = new DataSet();
                da.Fill(DS);
                da.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cm.Connection.Close();
                cm.Dispose();
            }
            return DS;
        }

3.创建触发器

Insert触发器

create trigger stu_insert
on student
for insert
as
update class set class_num=class_num+1
where class_id=(select class_id from inserted)

Delete触发器

create trigger stu_delete
on student
for delete
as
update class set class_num=class_num-1
where class_id=(select class_id from deleted)

Update触发器

create trigger stu_update
on student
instead of update
as
print '修改学生表'
drop trigger stu_update
--测试
update student set stu_id='0601004'where stu_name='鲁斌'

4.查询相关

查询分页

  select * from 表 where 1=1 order by 索引字段  offset ((页码-1)*10) rows fetch next 每页查询数量 rows only; 

修改相关

根据一个表里的数据插入一个另外一个表，此处要注意插入的数据列明要和被插入表保持一直

 insert  被插入表
 select  2209384 as 插入列的列明,
 10052093462002 as 插入列的列明,
 数据源表里的某个字段 as 插入列的列明,
 数据源表里的某个字段 as 插入列的列明,
 getdate()as 插入列的列明,
 数据源表里的某个字段 as 插入列的列明,
 8306232 as 插入列的列明
 from 数据源表 where 数据源表过滤条件