你的位置:首页 > 数据库

[数据库]SQL Server 2012 学习笔记3 增查改删


现在举例几个“增查改删”的语句

select * from UserInfor  --查找所有字段select username,UserId from UserInfor -- 查找username,UserId两个字段select top 2* from UserInfor where (Major='计算机' or Major='土木工程') and Sex=0 order by Age desc --降序select top 2* from UserInfor where major in('计算机','土木工程') and Sex=0 order by Age       --升序select * from UserInfor where Age > -- max() min() Avg()(select Avg(Age) from UserInfor) select sum(Age) from UserInfor  -- 年龄总和select count(*) as UserCount from UserInfor where Major='计算机' -- count(*) 代表某一列有多少个, as 代表别名select count(*) as UserCount,Major from UserInfor group by major -- 按照major计算分别有多少人select count(*) as UserCount,Major from UserInfor group by Major having count(*) >2 -- 这里having是配合group by使用,这里不能用whereselect * from UserInfor where RealName like '%张%' -- 模糊查询 “张%”以张开头;“%张”以张结尾;“%张%”包含张select distinct realname from userinfor -- 只显示列中不同的值,不重复的数据select top 2 * -- 分页语句from ( select row_number() over(order by userId) as rownumber,* from userinfor -- row_number() over找出行号, 以userid排序,别名 rownumber) Awhere rownumber > 2 -- >0第一页;>2第二页 ...select UserID,Age,  -- case when 语句casewhen age>=20 and age<=24 then '大一'when age>=25 and age<=28 then '大二'else '大三'end as usergrade from userinforselect * from UserInfor where age between 20 and 24 -- betwwen and的用法select UserId ,username, ISNULL (classname,'四班') from UserInfor -- 判断classname字段有没有NULL,有NULL的设定值为'四班'select year(getdate()) -- 获取当前时间的年 Month(getdate()) day(getdate())select * from UserInfor where year(getdate())-year(Birthday )>25 -- 找出年龄大于25岁的员工信息select DATEADD (yy,100,getdate()) -- yy代表年, 当前时间加上100年;mm代表月;dd代表天select DATEDIFF (yy,getdate(),'2018/12/31') -- yy代表年,当前时间距离2018/12/31多少年;mm代表月;dd代表天select UI.userid,UI.username,UI.qq,UI.realname,SS.Scoreid,ss.chinesescore --联合查询的三种方法,以左边为准,右边没有的用NULL补from UserInfor UI left join stuscore SS onUI.UserId =SS.UserId select UI.userid,UI.username,UI.qq,UI.realname,SS.Scoreid,ss.chinesescore --联合查询的三种方法,以右边为准,左边没有的用NULL补from UserInfor UI right join stuscore SS onUI.UserId =SS.UserId select UI.userid,UI.username,UI.qq,UI.realname,SS.Scoreid,ss.chinesescore --联合查询的三种方法,两边都有的值from UserInfor UI inner join stuscore SS onUI.UserId =SS.UserId select UIR.*,PP.MotherName,PP.fathername from(select UI.userid,UI.username,UI.qq,UI.realname,SS.Scoreid,ss.chinesescore  --三个表的联合查询,超过四个表的联合查询,就是需求有问题,要用到缓存from UserInfor UI inner join stuscore SS onUI.UserId =SS.UserId ) UIR inner join Parent PP onUIR.UserId =pp.UserId insert into parent values(5,'kk','')--主键是自增类型,不需要赋值,后面所以字段可以为空值,但是不能没有对应值insert into parent(UserId ,FatherName ) values(6,'ss') -- 添加某些列,其他补NULLupdate parent set FatherName ='GG',MotherName ='HH' where UserId =6 --修改表delete parent where Userid=5 or userid=6 --删除表insert into CopyParent(userid,copymothername,copyfathername) select UserId,Mothername,fathername from Parent --表的复制