delete from bank where customerName='张三' select * from bank
rollback ----回滚（撤销） --commit ------提交
delete from bank where customerName='张三' commit
rollback --回滚是回滚到最后事物提交 select * from bank
set implicit_transactions off -----回到自动提交事物模式
----------------------------------------------------------- ----------同时操作时，上锁
delete from bank where customerName='张三' ----------用rollback 和commit 解锁 rollback
select * from bank
-------------------------------------------------------------索引 --------------------------------------------------------非聚集索引 -------------------------------------------------------最多可建249个 select * from student
create nonclustered index my_index
on student(stuname) -----------------要建在哪个表的哪个字段 ---------------------------------------------------删除索引 drop index student.my_index ------------------需要指定表名
-----------------------------------创建非聚集索引，有填充因子 create nonclustered index my_index on student(stuname) with fillfactor =40
-----------------------------------唯一值索引 create unique index my_index on student(stuno) with fillfactor =40
---------------------------------聚集索引 create clustered index my_index on student(stuno) with fillfactor =40
select * from sysindexes ------检索系统表中的索引
--------------------------------------------------------视图 view -------------------------------------------create view as select create view stu_sc_view
as select Info.snumb,Info.sname,Info.birthday,score.score from info,score
where info.snumb=score.snumb ---------使用视图查询
select * from stu_sc_view where score >=60
---------------------------------删除视图 drop view stu_sc_view
----------------------针对视图所做的修改不一定会成功，如果成功，修改的是基表中的数据
---------------------(能够追溯会原表中) ---------------------
update stu_sc_view set score =90 where sname='房祖名'
---------------------------------------------修改视图 alter view stu_sc_view as
select Info.snumb,Info.sname,score.score,Info.birthday,info.address,info.tel from info,score
where info.snumb=score.snumb
----------------------------------- 当视图中的字段不能追溯回原表的时候，就不能对视图进行增删改查，
------------------------------------如果要改数据，只能通过原表
insert into view_name--------------错误的，该视图不能进行增删改查操作 values ('ssss.aaaaa')
-----------------------------------------------------------------面试题 -- S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
-- C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
-- SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
create database stu use stu go
create table S (
sno int ,
sname varchar(10)
)
create table C (
cno int ,
cname varchar(20), cteacher varchar(10) )
create table SC (
sno int , cno int, scgrade int )
insert into S(sno,sname) values (1,'张宇')
insert into S(sno,sname) values (2,'王王')
insert into S(sno,sname) values (3,'陈诚')
insert into S(sno,sname) values (4,'黄玉')
insert into C(cno,cname,cteacher) values (1,'C#','李明')
insert into C(cno,cname,cteacher) values (2,'C','李雨明')
insert into C(cno,cname,cteacher) values (3,'SQL','张三')
insert into C(cno,cname,cteacher) values (4,'C++','李无')
insert into SC(sno,cno,scgrade) values (1,1,45)
insert into SC(sno,cno,scgrade) values (2,2,80)
insert into SC(sno,cno,scgrade) values (3,1,56)
insert into SC(sno,cno,scgrade) values (4,3,56)
insert into SC(sno,cno,scgrade) values (1,4,55)
insert into SC(sno,cno,scgrade) values (2,3,55)
---找出没有选修过“李明”老师讲授课程的所有学生姓名
select sno, sname from S WHERE S.sno NOT IN (
select s.sno from S ,C ,SC
where S .sno=SC.sno and C.cno=SC.cno and C.cteacher ='李明' )
---2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩 select AVG(SC.scgrade) as 平均分,S.sname from S, sc, (
select sno from sc where sc.scgrade <60 group by SC .sno
having COUNT (SC.sno )>=2 ) as xx
where s.sno=SC .sno and xx.sno=SC .sno group by S .sname
---3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名 select sno ,sname from S,SC where SC .cno=1 and SC .cno=2
----用自连接来实现
---4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号 select sc1.*,sc2.* from sc sc1,sc sc2 where sc1 .cno=1 and sc2 .cno =2 and sc1 .sno =sc2 .sno
AND sc1.scgrade>sc2 .scgrade
------------------------------------------------------------------用户自定义函数
create function GetMessage(@name as varchar(20)) ---------创建函数 returns varchar(30) ------指定返回类型 as -------------------------------as 后面是函数体 begin --------------------------方法体 开始 declare @msg varchar(30)
set @msg= 'hell0,my name is '+@name return @msg end go
select dbo.GetMessage('陈瑞莲') ------------调用函数,要加上?dbo.函数名（参数）?
drop function dbo.GetMessage
sp_helptext 'GetMessage' ------------------ 查看数据库对象的原代码
------------------编写函数获取学生的平均分，并返回 create function GetAvg() returns int begin
declare @avger int
select @avger=avg(score) from score return @avger end go
DROP FUNCTION dbo.GetAvg ----------------删除自定义函数
--------------------------------------------------------------------存储过程
create proc getstudentInfo -----------存储过程的创建 as
select * from Info go
exec getstudentInfo ------------执行存储过程 方法一 getstudentInfo ------------执行存储过程 方法二
--------------------------------------------------------------------存储过程的参数
------------------------------------------------------------------1、in 类型参数（默认的参数类型）
create proc getstuBySex ----创建存储过程 @snumb int , ----参数一 @sex char(2) as
select sname,sex,birthday,address from INFO where snumb=@snumb and sex=@sex go
drop proc getstuBySex ----------------------删除执行过程
exec getstuBySex @snumb=2,@sex=0 ------------执行存储过程 方法一
exec getstuBySex @sex=0,@snumb=2 ------------执行存储过程 方法二 与顺序无关
exec getstuBySex 2,0 ------------执行存储过程 方法三 参数的顺序一定要对应,顺序不对就会报错
exec getstuBySex 0,2 ------错误的调用方法