select * from TblStudent where tsid not in (select tsid from TblStudent)
第二种,通过左外连接查询实现。
select t1.*, t2.* from TblStudent as t1 left join TblScore as t2 on t1.tsid=t2.tsid where t2.tscoreId is null
第三种,通过右外连接查询实现。
--右外连接,显示右表的所有记录(方法和左外连接一样,只是调换左表和右表的位置) select t1.*, t2.* from TblScore as t2 right join TblStudent as t1 on t1.tsid=t2.tsid where t2.tscoreId is null
--右外连接,显示右表的所有记录(不调换左表和右表的位置) select t1.*, t2.* from TblStudent as t1 right join TblScore as t2 on t1.tsid=t2.tsid
select t1.*, t2.* from TblStudent as t1 left join TblScore as t2 on t1.tsid=t2.tsid -- and t1.tsgender = '男' --on条件后面跟and可能会有问题,left join会查询出TblStudent表中所有数据 where t1.tsgener = '男' --筛选条件要放到where子句中
下面这种写法可以正常显示要查询的数据。
select t1.*, t2.* from TblStudent as t1 inner join TblScore as t2 on t1.tsid=t2.tsid and t1.tsgender = '男' --正常显示要查询的数据。因为inner join不需要增加外部列,而外连接是对笛卡儿积筛选完之后,再增加外部列
//多条件查询,要根据用户输入的内容来动态拼接SQL语句。 //1.假设用户没有输入任何条件,那么就查询出所有的记录 StringBuilder sbSql = new StringBuilder("select * from Books"); //在wheres集合中保存查询的sql条件 List<string> wheres = new List<string>(); //把参数也放到一个集合当中 List<SqlParameter> listParameters = new List<SqlParameter>(); //2.如果用户输入了条件,则根据用户输入的条件动态拼接SQL语句 if (txtBookName.Text.Trim().Length > 0) { wheres.Add(" BookName like @bkName "); listParameters.Add(new SqlParameter("@bkName", SqlDbType.NVarChar, 100) { Value = "%" + txtBookName.Text.Trim() + "%" }); } if (txtAuthor.Text.Trim().Length > 0) { wheres.Add(" Author like @author "); listParameters.Add(new SqlParameter("@author", SqlDbType.NVarChar, 100) { Value = "%" + txtAuthor.Text.Trim() + "%" }); } if (txtPub.Text.Trim().Length > 0) { wheres.Add(" Pub like @pub "); listParameter.Add(new SqlParameter("@Pub", SqlDbType.NVarChar, 100) { Value = "%" + txtAuthor.Text.Trim() + "%" }); } //拼接SQL语句 //如果wheres集合当中的记录条数大于0,证明用户输入了条件 if (wheres.Count > 0) { sbSql.Append(" where ");//只要有查询条件就拼接一个where //然后把后面的查询条件拼接起来。 sbSql.Append(string.Join(" and ", wheres)); } SqlParameter[] pms = listParameters.ToArray(); //SqlHelper.ExecuteReader(sbSql.ToString(), pms); }
视图
视图是一张虚拟表,它表示一张表的部分数据或多张表的综合数据,其结构和数据是建立在对表的查询基础上。
视图和数据表存在本质的不同:数据表是实际存储记录的地方,然而视图并不保存任何记录。
视图的目的是方便查询,所以一般情况下不能对视图进行增删改。
创建视图
语法:create View 视图名 as 查询语句。
create view vw_TblArea as select t1.AreaId 城市编号 t2.AreaName 城市名称 t3.AreaName 省份名称 --如果视图中的查询语句中包含了重名的列,此时必须为重名的列起别名 from TblArea as t1 inner join TblArea as t2 on t1.AreaId=t2.AreaPid
update bank set balance = balance - 100 where cid = '0002' update bank set balance = balance + 100 where cid = '0001'
通过事务执行转账
--1.打开一个事务 begin transaction declare @sum int = 0 --在转账之前,最好通过if-else判断,不要让程序发生异常或者错误。 --第一条SQL语句 update bank set balance = balance - 100 where cid = '0002' set @sum = @sum + @@error
--第二条SQL语句 update bank set balance = balance + 100 where cid = '0001' set @sum = @sum + @@error
--只要有任何一条SQL语句执行出错,那么最后的@sum就不是0 if @sum <> 0 begin --表示程序执行出错 --回滚 rollback end else begin --如果没有出错,则提交事务 commit end
--先删除存储过程,再创建存储过程 drop proc usp_select_tblStudent create proc usp_select_tblStudent as begin select * from TblStudent where tsgender = '男' end
--直接使用alter修改存储过程 alter proc usp_select_tblStudent as begin select * from TblStudent where tsgender = '女' end
创建带参数的存储过程
创建一个带两个参数的存储过程。
--创建一个带两个参数的存储过程 create proc usp_add_number @n1 int, @n2 int as begin select @n1 + @n2 end
--执行存储过程 exec usp_add_number 100,500 --600
根据参数条件查询表中的数据。
create proc usp_select_tblStudent_by_condition @gender char(2), @age int as begin select * from tblStudent where tsgender = @gender and tsage >= @age end
create proc usp_show_students @gender char(2), @recordcount int output --输出参数 as begin select * from MyStudent where fgender = @gender --把查询语句查询到的记录的条数赋值给变量@recordcount set @recordcount = (select count(*) from MyStudent where fgender = @gender) end
create proc usp_getMyStudentsDataByPage @pagesize int = 7, --每页记录条数 @pageindex int = 1, --当前要查看第几页的记录 @recordcount int output, --总的记录的条数 @pagecount int output --总的页数 as begin --1.编写查询语句,把用户要的数据查询出来 select t.fid, t.fname, t.fage, t.fgender, t.fmath, t.fclassid, t.fbirthday from (select *, rn = row_number() over(order by fid asc) from MyStudent) as t where t.rn between (@pageindex - 1) * @pagesize + 1 and @pagesize * @pageindex
--2.计算总的记录条数 set @recordcount = (select count(*) from MyStudent)
--3.计算总页数 set @pagecount = ceiling(@recordcount * 1.0 / @pagesize) end
--创建存储过程实现转账 create proc usp_transfer_bank @from char(4), @to char(4), @balance money, --转账金额 @resultNumber int output --转账是否成功(1表示成功,2表示失败,3表示余额不足) as begin --1.判断金额是否足够转账 declare @money money select @money = balance from bank where cid = @from if @money - @balance >= 10 begin --开始转账 begin transaction declare @sum int = 0 --2.账户1扣钱 update bank set balance = balance - @balance where cid = @from set @sum = @sum + @@error --3.账户2加钱 update bank set balance = balance + @balance where cid = @to set @sum = @sum + @@error --4.判断执行是否成功,进行提交或者回滚 if @sum <> 0 begin set @resultNumber = 2 --转账失败 rollback end else begin set @resultNumber = 1 --转账成功 commit end end else begin set @resultNumber = 3 --余额不足 end end
--1.向TblClass表中插入数据 create proc usp_insert_TblClass @name varchar(50), @desc varchar(50) as begin insert into TblClass values(@name, @desc) end
--2.删除TblClass表中的记录(根据主键ID) create proc usp_delete_TblClass @tsclassid int as begin delete from TblClass where tsClassId = @tsclassid end
--3.更新TblClass表 create proc usp_update_TblClass @tsclassid int @name varchar(50), @desc varchar(50), as begin update TblClass set tClassName = @name, tClassDesc = @desc where tsClassId = @tsclassid end
--4.查询 create proc usp_selectAll_TblClass as begin select * from TblClass end
T-SQL中的try…catch语句
语法:begin try … end try begin catch … end catch
begin try update bank set balance = -100 end try begin catch print '出异常了' end catch
问题:同一个SqlParameter参数不能同时用于多个Command对象
staticvoidMain(string[] args) { SqlParameter[] pms = new SqlParameter[] { new SqlParameter("@name", System.Data.SqlDbType.Varchar, 50), new SqlParameter("@desc", System.Data.SqlDbType.Varchar, 50) };
语法:create trigger 触发器名 on 表名 after(for) | instead of update|insert|delete (insert,update,delete) as begin … end
创建一个删除触发器。删除一个表的数据时,把删除的数据插入到另一个表中。
create trigger tri_delete_tblclass on TblClass after delete as begin --从deleted表中读取删除的数据 insert into TblClassBackup(tsClassName, tsClassdesc) select tsClassName, tsClassDesc from deleted --deleted表只有在触发器里面才能访问 --rollback --可以在触发器中使用rollback,使用后数据回滚 end