--运行scott数据库
usescott
go
--表的插入
insertintostudent(student_name,student_age,schoolid)
values('李时',21,2)
select*fromstudent
select*fromschool
--删除指定的数据
deletefromstudentwherestudentid=3
--删除所有数据不删除表结构
truncatetablestudent
--删除整个表
droptablestudent
--表的更新有where更新一条没有where所有数据都更新
updatestudentsetstudent_age=50wherestudent_namelike'张%'
--表的连接
selectstudent_nameas学生名,school_nameas名fromstudentasst,schoolassc
wherest.schoolid=sc.schoolid
--表的左外连接
selectstudent_nameas学生名,school_nameas名fromstudentasstleftouterjoinschoolassc
onst.schoolid=sc.schoolid
----全连接
select*fromstudentfulljoinschool
onstudent.schoolid=school.schoolid
--分组函数groupbyhaving是对分组函数的约束条件相当于where
--先后顺序
where->groupby->having
selectcount(student_name),school_namefromstudent,schoolwherestudent.schoolid=school.schoolid
groupbyschool.schoolid,school_namehavingschool.schoolid>1
--orderby排序表名asc升序desc降序
select*fromstudentorderbystudent_agedesc
--in('','','')找出数据库里包含在in里的数据
select*fromstudentwherestudent_namein('张山','刘备','关羽')
--like匹配%李以李结尾的李%以李开头的%李%所有含李的
select*fromstudentwherestudent_namelike'%李%'
--在10到20之间的数between25and50
select*fromstudentwherestudent_agebetween25and50
--不在10到20之间的数notbetween10and20
select*fromstudentwherestudent_agenotbetween25and50
--将student表拷到新表liu3里selectinto新表from表
selectstudentid,student_name,student_ageintoliu3fromstudent
select*fromstudent
--创建表
createtableer(
student_idint,
student_namevarchar(32),
sutdent_ageint,
schoolidint)
select*fromer
--将表student复制到一个表结构和student一样的表里
insertintoer
selectstudentid,student_name,student_agefromstudent
select*fromer
truncatetableer
select*fromer
droptableliu3
--使用变量
--局部变量用@做前缀
--声明方法declare局部变量类型
declare@namevarchar(20)
--局部变量赋值的两种方法set和select
set@name=value
或
select@name=value
--全局变量用@@做前缀
declare@namevarchar(23)
select@name='张三'
print'您输入的是：'+@name
declare@dataint
declare@data1int
set@data=23
set@data1=10
print@data+@data1
print'服务器名'+@@servername
select@@servernameas服务器
--事物要么全成功要么全失败
--begintransaction开始事物
--committransaction提交事务
--rollbacktransaction回滚事务
begintransaction
updatestudentsetstudent_name='牛逼1'wherestudentid=3
rollbacktransaction
select*fromschool
select*fromstudent
--全连接
select*fromstudentfulljoinschool
onstudent.schoolid=school.schoolid
select*fromstudentleftouterjoinschoolonstudent.schoolid=school.schoolid
select*fromstudentwherestudent_namelike'[a-z]%'
--加入标识列
select*fromstudent
selectidentity(int,1,1)asstudentidintoliu4fromstudent
droptableliu4
--存储过程(Procedure)create/修改alterproc存储过程名
--创建存储过程convert(varchar(20),@age)转换数据类型
alterprocliu
@namevarchar(50),@ageintoutput
as
print'你输入的名字'+@name+char(10)+'年龄'+convert(varchar(20),@age)
--或print'年龄'+convert(var)
go
--运行存储过程execliu
execliu'装',23
alterprocliu
@namevarchar(200),@ageint
as
updatestudentsetstudent_name=@name,student_age=@agewherestudentid=5
print'成功'
go
execliu'狗屎',400
createPROCliu
--输入参数
@namevarchar(10),--默认值
@ageint,
@schoolidintoutput--输出参数
as
insertintostudent(student_name,student_age,schoolid)values(@name,@age,@schoolid)
print'成功'
go
execliu'iu',23,2
select*fromstudent
--//删除存储过程
dropprocliu
--触发器
createtriggerliu
onstudent
afterinsert
as
print'成功插入一行'
go
createtriggerliu1
onstudent
insteadofdelete
as
print'不能删除'
go
createprocliu
@namevarchar(30),@ageint,@schoolidint
as
insertintostudent(student_name,student_age,schoolid)
values(@name,@age,@schoolid)
print'插入成功'
go
execliu'是你的',23,3
select*fromstudent
selectstudent_name,student_age,schoolidintoasdf
fromstudent
createtableqwe(
namevarchar(33),
ageint
,idint)
insertintoqwe
selectstudent_name,student_age,schoolidfromstudent
select*fromqwe
select*fromasdf
truncatetableqwe
droptableqwe
select*fromstudentorderbystudent_agedesc
select*fromstudentorderbystudent_ageasc
select*fromstudentwherestudent_agebetween24and50
select*fromstudentwherestudent_agenotbetween24and50
select*fromstudentwherestudent_namein('a','李四','狗屎','关羽'
select*fromstudentwherestudent_namelike'李%'
select*fromstudentwherestudent_name='李四'andstudentid=19
select*fromstudentwherestudent_name='李四'orstudent_name='狗屎'
