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使用，這裡不能用where

select * 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
) A
where rownumber > 2  -- >0第一頁；>2第二頁 ...


select UserID,Age,   -- case when 語句
case
when age>=20 and age<=24 then '大一'
when age>=25 and age<=28 then '大二'
else '大三'
end as usergrade from userinfor


select * 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 on
UI.UserId =SS.UserId 

select UI.userid,UI.username,UI.qq,UI.realname,SS.Scoreid,ss.chinesescore  --聯合查詢的三種方法，以右邊為準，左邊沒有的用NULL補
from UserInfor UI right join stuscore SS on
UI.UserId =SS.UserId 

select UI.userid,UI.username,UI.qq,UI.realname,SS.Scoreid,ss.chinesescore  --聯合查詢的三種方法，兩邊都有的值
from UserInfor UI inner join stuscore SS on
UI.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 on
UI.UserId =SS.UserId  ) UIR inner join Parent PP on
UIR.UserId =pp.UserId 

insert into parent values(5,'kk','')--主鍵是自增類型，不需要賦值，後面所以欄位可以為空值，但是不能沒有對應值

insert into parent(UserId ,FatherName ) values(6,'ss') -- 添加某些列,其他補NULL

update 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 --表的複製