SQL Server基本操作积累
一、基本操作
1、将数据绑定到DataGridVirw控件上显示的数据列标题将会是数据库中的字段名称，可以在使用select语句时使用AS关键字将转化为列名的别名
select name AS 姓名 from tb_Student;
使用空格也行的
select name 姓名 from tb_Student;
2、在列上进行计算
select stu_id as 学号,stu_math as 数学,stu_english as 英语,stu_chinese as 语文,stu_math+stu_english+stu_chinese as 总成绩 from Students;
3、查询空数据is null
select * from Student where stu_birthday IS NULL;
4、利用变量查询日期型数据,方便查错
string sqlStr=string.Format("select stu_name,stu_age,stu_birthday from students where stu_age={0}",age);
5、查询第10名到第20名的数据top
select top 10 * from (select top 20 * from Students order by 总分 DESC) as 中等名次 order by 总分 ASC ;
6、根据随机生成数查询记录rand
select * from Students where stu_id=20101810+Floor(Rand()*10); --Rand()函数用于产生0-1之间的随机数，Floor()函数用于对小数数值向下取整
7、根据查询数值的符号显示具体的文本 case
select myValue as 数值, case when sign(myValue)=1 then '正数' else case when sign(myVaule)=-1 then '负数' else '零' end end as 判断数值 from Students;
select stu_name, stu_math, case when stu_math>=60 then '通过' when stu_math=60 then '刚及格' else '不及格' end as 及格否 from Students;
8、实现字符串大小的转换lower upper
select friendNmae as 字符串, Lower(friendName) as 转换为小写, Upper(friendName) as 转换为大写 from Students;
9、返回字符在字符串中出现的次数Len
select stu_name,len(stu_name) as 字符数量 from students;
10、返回字符串中的子串substring
select 学生姓名,所在学院,substring(所在学院,3,2) as 所在学院的子串 from Students;--substring(所在学院,3,2)索引从1开始，截取从第3个字符开始的长度为2字符串
11、删除字符串中的子串stuff
select stuff(所在学院,2,3,'') as 删除后的字符串 from Students;
12、获取指定字符所在的索引charindex
select 所在学院,charindex('学院',所在学院) as 索引位置 from Students;
13、替换字符串replace
select 所在学院,replace(所在学院,'学院','xueyuan') as 替换后的信息 from Students;
14、比较两个字符串
select 性别 ,case when 性别='男' then '男同学' else '女同学' end as 性别 from Students;
15、使用In引入子查询限定查询范围，还有Not in
select 学生姓名,性别,年龄 from tb_Student where 学生编号 not in (select 学生编号 from tb_Grade where 总分>500 and 总分<690)；
16、交叉表查询：直观、方便的显示了数据之间的关系
Transform SUM(数量) as 库存数量 select 语言类别 from 图书排行 where 语言类别 in ('C','Java','VB') group by 语言类别 pivot 分析时间 ;
17、在查询语句中使用格式化函数
select 员工姓名,出生时间,format(出生时间,'yyyy年mm月dd日') as 格式化后的出生日期 from 员工生日表 ;
select 员工姓名,format(出生日期,'yyyy年mm月dd日') as 出生日期,datediff('yyyy',出生日期,Date()) as 年龄 from 员工表 ;
18、索引： 用的好的话能够大幅度提高查询效率
数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引是对数据库表中一个或多个列（例如，employee 表的姓氏 (lname) 列）的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，在索引中查找，但索引是经过某种算法优化过的，查找次数要少的多的多。可见，索引是用来定位的。
select * from Students where stu_id=001;
这条查询语句这样执行，遍历整个数据表，直到找到stu_id=001的这行数据，接下来采用使用索引的方式查询该条数据
create unique index index_Stu_id on Students(stu_id);
建立多重索引
create index myIndex on Students(stu_id,stu_name);
删除索引
drop index Students.index_Students_stu_name;
创建聚簇索引:聚簇索引可以按照物理磁盘相近的位置索引数据记录，而且在一个数据表中只允许有一个聚簇索引
create clustered index index_Student on Students(学生编号);
优缺点：
建立索引的目的是加快对表中记录的查找或排序。
19、存储过程：这个很好用，需要灵活运用，提高执行效率
数据库中的存储过程是一种已经编译好的程序，存储过程可以由一系列的代码组成，并实现指定的功能。
存储过程的优点：
存储过程具有一定的安全性，当存储过程被创建后，只有具有了权限的用户才可以使用它；存储过程可以提高数据查询速度，一般的查询语句每执行一次则需要编译一次，存储过程只有在创建时才进行编译，以后再次执行时则不需要编译。
create proc proc_GetStudent--创建存储过程 as select st.Stu_id,st.Stu_name,st.Stu_age,gr.Stu_score from Students as st inner join Grade as gr on st.Stu_id=gr.Stu_id;--设置数据表连接规则
修改存储过程名称：exec sp_rename 'proc_Select','proc_SelectAll'--前面一个是原存储过程名，后面一个是newProcName
use testDB--创建存储过程 go create procedure pro_Student as select distinct Students.stu_id as 学号,Students.stu_name as 姓名,Students.stu_age as 年龄 from Students where Students.stu_address like'%郑州%'; go exec pro_Student--调用存储过程 go
use testDB go if exists (select name from sysobjects where name='pro_Student' and type='P')--判断该存储过程是否存在，所以我们该注意命名规范的问题 drop procedure pro_Student go create procedure pro_Students as select Students.stu_address as 地址 from Students inner join Score on Students.stu_id=Score.stu_id go exec pro_Students go
声明带参数的存储过程
use testDB; go create procedure proc_InsertStudents @stu_name varchar(20), @stu_sex bit, @stu_age int, @stu_birthday date, @stu_address varchar(50) as insert into Students(stu_name,stu_sex,stu_age,stu_birthday,stu_address) values(@stu_name,@stu_sex,@stu_age,@stu_birthday,@stu_address); go
调用该存储过程
execute proc_InsertStudents '张三09',1,22,'1990-09-09','江苏省昆山市'; execute proc_InsertStudents @stu_name='你好哦',@stu_sex=0,@stu_age=21,@stu_birthday='1989-08-09',@stu_address='河北省济源市';
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace ProcedureInsert { public partial class Frm_Main : Form { public Frm_Main() { InitializeComponent(); } private void Frm_Main_Load(object sender, EventArgs e) { dgv_Message.DataSource = GetMessage();//设置数据源 } private void btn_Add_Click(object sender, EventArgs e) { CreateProcedure();//创建存储过程并查询 } /// <summary> /// 创建存储过程添加数据记录并查询 /// </summary> private void CreateProcedure() { string P_Str_ConnectionStr = string.Format(//创建数据库连接字符串 @"server=WIN-GI7E47AND9R\LS;database=db_TomeTwo;uid=sa;pwd="); string P_Str_SqlStr = string.Format(//创建SQL查询字符串 @"SELECT CASE WHEN EXISTS (SELECT * FROM sysobjects WHERE id=object_id('dbo.proc_InsertStudent') AND XTYPE='p') THEN '存在' ELSE '不存在' END"); using (SqlConnection sc = //创建数据库连接对象 new SqlConnection(P_Str_ConnectionStr)) { sc.Open();//打开数据库连接 SqlCommand P_cmd =//创建数据库命令对象 new SqlCommand(P_Str_SqlStr, sc); SqlDataReader sdr =//得到数据读取器 P_cmd.ExecuteReader(); sdr.Read();//读取一条记录 if (sdr[0].ToString() == "存在")//判断存储过程是否存在 { sdr.Close();//关闭数据读取器 P_cmd.CommandType = CommandType.StoredProcedure;//设置命令类型 P_cmd.CommandText = "proc_InsertStudent";//设置存储过程 P_cmd.CommandType = CommandType.StoredProcedure;//设置命令类型 P_cmd.Parameters.Add("学生编号", SqlDbType.Int).Value =//添加参数 int.Parse(txt_Id.Text); P_cmd.Parameters.Add("学生姓名", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Name.Text; P_cmd.Parameters.Add("性别", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Sex.Text; P_cmd.Parameters.Add("出生年月", SqlDbType.SmallDateTime).Value =//添加参数 txt_BirthDay.Text; P_cmd.Parameters.Add("年龄", SqlDbType.Int).Value =//添加参数 int.Parse(txt_Age.Text); P_cmd.Parameters.Add("所在学院", SqlDbType.NVarChar, 50).Value =//添加参数 txt_School.Text; P_cmd.Parameters.Add("所学专业", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Subject.Text; P_cmd.Parameters.Add("家庭住址", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Address.Text; P_cmd.Parameters.Add("统招否", SqlDbType.Bit).Value =//添加参数 txt_Yes.Text == "是" ? 1 : 0; P_cmd.Parameters.Add("备注信息", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Message.Text; P_cmd.ExecuteNonQuery();//添加数据 dgv_Message.DataSource = GetMessage();//设置数据源 } else { sdr.Close();//关闭数据读取器 string P_Str_Cmd = string.Format(//创建SQL字符串 @"CREATE PROC proc_InsertStudent @学生编号 int, @学生姓名 nvarchar(50), @性别 nvarchar(50), @出生年月 smalldatetime, @年龄 int, @所在学院 nvarchar(50), @所学专业 nvarchar(50), @家庭住址 nvarchar(50), @统招否 bit, @备注信息 nvarchar(50) AS INSERT INTO tb_Student(学生编号,学生姓名,性别,出生年月,年龄,所在学院,所学专业,家庭住址,统招否,备注信息) values(@学生编号,@学生姓名,@性别,@出生年月,@年龄,@所在学院,@所学专业,@家庭住址,@统招否,@备注信息)"); P_cmd.CommandText = P_Str_Cmd;//设置执行的SQL语句 P_cmd.ExecuteNonQuery();//添加存储过程 P_cmd.CommandType = CommandType.StoredProcedure;//设置命令类型 P_cmd.CommandText = "proc_InsertStudent";//设置存储过程 P_cmd.CommandType = CommandType.StoredProcedure;//设置命令类型 P_cmd.Parameters.Add("学生编号", SqlDbType.Int).Value =//添加参数 int.Parse(txt_Id.Text); P_cmd.Parameters.Add("学生姓名", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Name.Text; P_cmd.Parameters.Add("性别", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Sex.Text; P_cmd.Parameters.Add("出生年月", SqlDbType.SmallDateTime).Value =//添加参数 txt_BirthDay.Text; P_cmd.Parameters.Add("年龄", SqlDbType.Int).Value =//添加参数 int.Parse(txt_Age.Text); P_cmd.Parameters.Add("所在学院", SqlDbType.NVarChar, 50).Value =//添加参数 txt_School.Text; P_cmd.Parameters.Add("所学专业", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Subject.Text; P_cmd.Parameters.Add("家庭住址", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Address.Text; P_cmd.Parameters.Add("统招否", SqlDbType.Bit).Value =//添加参数 txt_Yes.Text == "是" ? 1 : 0; P_cmd.Parameters.Add("备注信息", SqlDbType.NVarChar, 50).Value =//添加参数 txt_Message.Text; P_cmd.ExecuteNonQuery();//添加数据 dgv_Message.DataSource = GetMessage();//设置数据源 } } } private void btn_Remove_Click(object sender, EventArgs e) { string P_Str_ConnectionStr = string.Format(//创建数据库连接字符串 @"server=WIN-GI7E47AND9R\LS;database=db_TomeTwo;uid=sa;pwd="); string P_Str_SqlStr = string.Format(//创建SQL字符串 @"SELECT CASE WHEN EXISTS (SELECT * FROM sysobjects WHERE id=object_id('dbo.proc_InsertStudent') AND XTYPE='p') THEN '存在' ELSE '不存在' END"); using (SqlConnection sc =//创建数据库连接对象 new SqlConnection(P_Str_ConnectionStr)) { sc.Open();//打开数据库连接 SqlCommand P_cmd =//创建数据库命令对象 new SqlCommand(P_Str_SqlStr, sc); SqlDataReader sdr =//得到数据读取器 P_cmd.ExecuteReader(); sdr.Read();//读取一条记录 if (sdr[0].ToString() == "存在") { sdr.Close();//关闭数据读取器 P_cmd.CommandText =//设置执行的SQL语句 "drop proc proc_InsertStudent"; P_cmd.ExecuteNonQuery();//删除存储过程 } } } /// <summary> /// 查询数据库信息 /// </summary> /// <returns>方法返回DataTable对象</returns> private DataTable GetMessage() { string P_Str_ConnectionStr = string.Format(//创建数据库连接字符串 @"server=WIN-GI7E47AND9R\LS;database=db_TomeTwo;uid=sa;pwd="); string P_Str_SqlStr = string.Format(//创建SQL查询字符串 @"SELECT * FROM tb_Student"); SqlDataAdapter P_SqlDataAdapter = new SqlDataAdapter(//创建数据适配器 P_Str_SqlStr, P_Str_ConnectionStr); DataTable P_dt = new DataTable();//创建数据表 P_SqlDataAdapter.Fill(P_dt);//填充数据表 return P_dt;//返回数据表 } } }
20、视图 这篇博文很好 http://www.cnblogs.com/CareySon/archive/2011/12/07/2279522.html 重点看
视图是存在于数据库中的虚拟数据表，对于视图的操作与数据表的操作基本相同。
简单的视图创建与调用
use testDB go create view V_select as select Students.stu_id as 学号,stu_name as 姓名,stu_age as 年龄,stu_birthday as 出生年月,Score.stu_chinese as 语文,Score.stu_english as 英语,
Score.stu_math as 数学
from Students inner join Score on Students.stu_id=Score.stu_id go select * from V_select;
视图的查询
select * from sysobjects where xtype='v'; select id as 视图编号, name as 视图名称,crdate as 创建时间,refDate as 最后修改时间 from sysobjects where xtype='v';
将上面这篇优美的博文中的总结摘抄如下：
视图用在多表查询很方便，如果用在通过视图来修改数据的话，如果涉及修改的数据来自多个表，那么会出现“视图或函数 'V_select' 不可更新，因为修改会影响多个基表”的报错，如果涉及修改的数据来自一个表，是可以实现更新的。
21、触发器
触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。
百度上找的几个例子
SQL支持三种类型的触发器：Insert（插入）、UPDATE（更新）和DELETE（删除）,当向表中插入数据、更新数据、删除数据时，触发器就被自动调用
insert 触发器示例
create trigger tri_insert--创建触发器 on student for insert--用于插入数据 as declare @student_id char(10) select @student_id=s.student_id from student s inner join inserted i on s.student_id=i.student_id if @student_id='0000000001' begin raiserror('不能插入1的学号!',16,8) rollback tran end go
update触发器示例
create trigger tri_update on student for update as if update(student_id) begin raiserror('学号不能修改!',16,8) rollback tran end go
delete触发器示例
create trigger tri_delete on student for delete as declare @student_id varchar(10) select @student_id=student_id from deleted if @student_id='admin' begin raiserror('错误',16,8) rollback tran end
写个触发器的实例测试一下
use testDB; select * from MyStudent --insert 触发器 if exists(select name from sysobjects where name='[tjy_insertMS]' and xtype='tr') drop trigger [tjy_insertMS] go create trigger trig_insertMS on MyStudent for insert as if exists(select * from inserted as a where a.Fid in (select MyStudent.Fid from MyStudent)) begin raiserror('数据库中存在该Fid的学生',16,1) rollback transaction end go insert into MyStudent(Fid,FName,FAge,FGender,FMath,FEnglish) values(2,'jiang',12,'男',100,123);
使用递归触发器实现每次删除一条记录的功能
if exists (select name from sysobjects where name='delete_employee' and type='tr' )--判断是否存在名为‘delete_employee’的触发器 drop trigger delete_employee--存在的话就删除 go create trigger delete_employee on Employee after delete--说明触发器是在指定操作都成功完成执行后触发 as declare @rowcount int selete @rowcount=@@rowcount if @rowcount>1 begin rollback transaction print('当前要删除的数据记录的条数大于一条，然而一次只允许删除一条') end if @rowcount=1 begin declare @Department varchar(50) select @Department=department from deleted delete from Employee where Department=@Department end go
在写触发器的过程中我们常看到inserted、deleted这两张表，这两张表是执行触发器时系统创建的两个特殊的逻辑表
inserted逻辑表：当向表中插入数据时，insert触发器触发执行，新的记录插入到触发器表和inserted表中；
deleted逻辑表: 用于保存已从表中删除的记录，当触发一个delete触发器时，被删除的记录存放在deleted逻辑表中
修改一条记录等于插入一条新记录，同时删除旧记录，当对定义了update触发器的表记录修改时，表中原纪录移动到deleted表中，修改过的记录插入到inseted表中，触发器可检查deleted表、inserted表以及被修改的表
注意： 使用触发器的限制都有哪些
* create trigger 必须是批处理中的第一条语句，并且只能应用到一个表中
22、游标：关于游标我觉得用着很扯淡，在自动取款中取1000元钱，每次取100，其实可以一次取1000的，但是使用游标它只能每次取100，占内存啊
这里有篇很好的博文http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html，我自己也测试了下，觉得游标不咋好用，可能是我不知道咋用而已。
23、事务：同生共死 ACID
事务具有原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。http://www.cnblogs.com/CareySon/archive/2012/01/29/2331088.html
class SqlHelper { public static bool ExecDataBySqls(List<string> strSqls, string strConn) { SqlConnection sqlConn = new SqlConnection(strConn); bool boolIsSucceed = false; SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; if (sqlConn.State == ConnectionState.Closed) { sqlConn.Open(); } SqlTransaction sqlTran = sqlConn.BeginTransaction(); try { sqlCmd.Transaction = sqlTran; foreach (string item in strSqls) { sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandText = item; sqlCmd.ExecuteNonQuery(); } sqlTran.Commit(); boolIsSucceed = true; } catch { sqlTran.Rollback(); boolIsSucceed = false; } finally { sqlConn.Close(); strSqls.Clear(); } return boolIsSucceed; } public static DataSet DataBanding(string connStr,string selectCmdText) { using (SqlConnection conn = new SqlConnection(connStr)) { if (conn.State == ConnectionState.Closed) { conn.Open(); } SqlDataAdapter sda = new SqlDataAdapter(selectCmdText, conn); DataSet ds = new DataSet(); sda.Fill(ds, "Student"); return ds; } }
private void button1_Click(object sender, EventArgs e) { List<string> strSqls = new List<string>(); string strDelete =string.Format("delete from student where stuNumber='{0}'",txtDStuNumber.Text); string strInsert = string.Format("insert into student values('{0}','{1}',{2})", txtStuNumber.Text, txtStuName.Text, txtStuAge.Text); string strSelect = "select * from student where stuNumber='2010181055'"; strSqls.Add(strDelete); strSqls.Add(strInsert); strSqls.Add(strSelect); if (SqlHelper.ExecDataBySqls(strSqls, connStr)) { MessageBox.Show("事务执行成功"); } dataGridView1.DataSource = SqlHelper.DataBanding(connStr, selectCmdText).Tables[0]; MessageBox.Show("加载成功"); }
方法一：
（1）SqlConnection类的BeginTransaction方法：该方法用来开始数据库事务
SqlTransaction sqlTran=sqlConn.BeginTransaction();
（2）SqlConnection类的Transaction属性：该属性用来获取或设置将在其中执行的SqlCommand的SqlTransaction
sqlCmd.Transaction=sqlTran;
（3）SqlTransaction类的Commit方法：SqlTransaction类表示要在SQL Server数据库中处理的Transaction-SQL事务，其Commit方法用来提交数据库事务
sqlTran.Commit();
（4）SqlTransaction类的Rollback方法：该方法用来从挂起状态回滚事务
sqlTran.Rollback();
--Top操作：用于规定要返回的记录的数目
--Like操作: 用于在where子句中搜索列中的指定模式
--通配符：在搜索数据库中的数据时，SQL通配符可以替代一个或多个字符
--In操作：允许我们在where子句中规定多个值
select stu_name from students where stu_age in (12,13,14,15,16);
--Between操作：Between...And操作选取介于两个值之间的数据范围，这些值可以是数值、文本或者日期
--As操作符：通过As操作符可以为列名称和表名称指定列名
--Join：用于根据两个或多个表中的列之间的关系，从这些表中查询数据
--Inner Join：在表中存在至少一个匹配时，Inner Join关键字返回行
--Left Join：从左表那里返回所有的行，即使在右表中没有匹配的行
--Round()函数：对小数数值进行四舍五入
--sin()--cos():三角函数计算数值
--绝对值：ABS()
