一些sqlserver小知識

1、往表中插入多條資料使用union關鍵字
insert into Users([name],password,nickName)
select 'aaa','bbbb','cccc' union
select 'dfdf','dfdf','dfdsf' union
select 'edtrer','dfgg','dfgfg'
使用 union all時將把以下和union資料相同的行覆蓋了

2、delete truncate drop的區別
delete時不寫條件將把表中的資料都刪了,刪除一條往記錄檔裡寫一條記錄,表行數很多速度會很慢
truncate table就把表清空了,速度快
drop刪除表時沒有這個表將會報錯
delete刪除表時沒有這個表將會顯示影響行數為0

3、關於日期的函數
getDate()獲得當前日期
dateAdd(yyyy,2,'2008-11-10') 將指定日期加2年,也可以加月mm,加天dd等
dateDiff(yyyy,'2005-10-5','2007-10-5')返回兩個日期的相隔的年月或日
dateName(dw,'2008-11-4')獲得星期幾(也可用w)結果為星期二
datePart(day,'2007-12-21')獲得日期的指定整數形式
與datePart類似還有
Day()、Month()、Year()函數

4、一些常用的sqlServer函數
len('df大幅度') 獲得實際長度5
datalength('df大幅度')獲得位元組數8
charindex('mn','mmmmn')獲得第一個字串在第二個字串中的起始位置4
lower() 返回字串的小寫
upper() 返回字串的大寫
ltrim() 清除字串左邊的空格
rtrim() 清除字串右邊的空格
right('sdfd',2) 從字串右邊截取指定數目的字元
left('dfds',2) 從字串左邊截取指定數目的字元
replace('dfdsfd','d','a') 將字串中字元d都替換為字元a
stuff('addfgdg',2,3,'AAAAA') 從字串第二個位置開始截取3個字元用AAAAA代替
numeric(5,3)
decimal(5,3) 這兩個功能一樣,精度為5,即總位元是5,小數位元為3
cast(123 AS varchar(5)) 轉換函式,將整數轉化為字串
convert(int,'123') 將字串轉化為整數
convert(varchar(10),getdate(),121) 使用了樣式,獲得日期的年月日,沒有時分秒
isnull(classess,'未分班') 將所有classess列為null的用未分班代替

5、一些常用的預存程序
sp_help users 列出表結構
sp_tables 查出當前庫裡的所有表
xp_cmdshell 'mkdir d:\bb'  建立檔案夾
EXEC sp_addlogin  'adminMaster','theMaster' --建立sqlserver登陸帳號
--在master庫下
EXEC sp_grantdbaccess 'adminMaster','adminMaster'--建立資料庫使用者
USE bbsDB
--給使用者賦許可權，對主貼表bbsTopic和回帖表bbsReply有查看、刪帖的許可權
GRANT select,delete ON bbsTopic TO adminMaster
GRANT select,delete ON bbsTopic TO adminMaster
--對使用者表bbsUsers有修改許可權
GRANT update ON bbsUsers TO adminMaster

scope_identity() 獲得真正的最後一次插入的資料的標識值,因為多使用者在insert時,別人也有可能insert
@@identity 獲得最後一次插入的資料的標識值
sp_who 查詢出有幾個使用者連著資料庫
kill spid這個列的值   刪除佔用資源的使用者

print @@IDENTITY   --在多各使用者都執行插入時，有可能得到的不是真正的標識值

print @@MAX_CONNECTIONS --可以建立同時串連的最大數目

print @@ROWCOUNT   --受上一個SQL語句的影響行數

print @@SERVERNAME --本機伺服器的名稱  

print @@TIMETICKS --當前電腦每刻度的微秒數 

print @@TRANCOUNT --當前開啟的事物數

print @@VERSION   --SQL Server的版本 
ups 不斷電供應系統

6、建庫、建表、加約束
在master庫下
if exists(select * from sysdatabases where name='mytest')
 drop database mytest
create database mytest
on
(
 name='mytest',
 filename='e:\test.mdf'
)
在mytest庫下
if exists(select * from sysobjects where name='stu')
 drop table stu
create table stu
(
 id int identity,
 name varchar(50),
 calsses varchar(50)
)
加約束
alter table stuInfo
 add constraint ck_stuNo check(stuNo like 's253__') 加check約束
alter table stu
 add constraint df_name default('aaa') for [name] 加預設約束
alter table stu
 add constraint uq_name(name) 加唯一約束
alter table stu
 add constraint pk_id primary key 加主鍵約束
alter table stu
 add constraint fk_id foreign key(外鍵列) references 主鍵表(主鍵列) 加外鍵約束

7、小知識
SET NOCOUNT ON --不顯示SQL語句影響的行數
往識別欄位插入顯示值必須
set identity_insert 表名 on
set identity_insert 表名 off 關閉插入顯示值
修改表中某一列的資料類型
alter table 表名
 alter column 列名 類型(大小)
往表中插入一列
alter table 表名
 add 列名 類型(大小)

8、表變數
declare @myTable table
(
 id int identity,
 orderid varchar(20),
 customerid varchar(20)
)

9、執行內容是字串的sql語句
declare @strsql varchar(800)
set @strsql='select * from orders'
exec(@strsql)
case when then的用法:
 CASE WHEN writtenExam IS NULL THEN '缺考'
 ELSE CAST(labExam AS VARCHAR)
 END

10、事物
BEGIN TRANSACTION
DECLARE @userID int,@sumError int
SET @sumError = 0
SELECT @userID = UID FROM bbsUsers WHERE Uname = '可卡因'
--可卡因發帖
INSERT INTO bbsTopic(TsID,TuID,Tface,Ttopic,Tcontents,Ttime,TclickCount,Tstate,TlastReply)
VALUES(2,@userID,4,'.NET配置問題','我的',GETDATE(),200,0,'2009-1-8')
SET @sumError = @sumError + @@ERROR
--發貼之後.NET版塊表加1
IF(@sumError = 0)
 UPDATE bbsSection SET StopicCount = StopicCount + 1 WHERE Sname LIKE '%.NET%'
SET @sumError = @sumError + @@ERROR
--判斷錯誤是否是0，不是發帖不成功
IF(@sumError <> 0)
 BEGIN
  print '您所發的文章不成功!'
  ROLLBACK TRANSACTION
 END
--錯誤號碼不是0說明發帖成功
ELSE
 BEGIN
  print '發帖成功'
  COMMIT TRANSACTION
 END
事物會鎖定表,獨佔此表,只有執行完後,才能對此表進行操作
11、索引
if exists(select * from sysobjects where name='myIndex')
 drop index myIndex
create nonclustered myIndex --非叢集索引
on 表名(列名)

12、視圖
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'myview')
 DROP VIEW myview
GO
CREATE VIEW myview
AS
(
 SELECT * from orders
)
視圖不存放資料,僅僅是映射,即引用原表中的資料而已
對視圖執行update語句,原表中的資料會改變,而虛擬表不會

13、觸發器
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'trig_bank')
 DROP TRIGGER trig_bank
GO
CREATE TRIGGER trig_bank
ON bank
FOR INSERT
AS
......
往bank表中插入資料時執行觸發器的begin到end

14、預存程序
F EXISTS(SELECT * FROM sysobjects WHERE name = 'proc_stu')
 DROP PROCEDURE proc_stu
GO
CREATE PROCEDURE proc_stu
 @notpassnum int OUTPUT,--輸出參數，未通過人數
 @writtenPass int = 60, --輸入參數筆試及格線
 @labPass int =60 --輸入參數，機試及格線
AS
......

15、建立一個自訂函數
CREATE FUNCTION fun_myfun
( 
 @num int --參數
)
returns int --傳回型別
AS --begin和end不能省
BEGIN
 SET @num = @num + 1
 RETURN @num --傳回值
END
SELECT dbo.fun_myfun(5) --調用函數

16、往一個表裡插入資料的兩種方法
<1>select A.列,A.列 into B  --B表事先不存在
   from A
<2>insert into B(列,列)
   select 列,列 from A  --B表事先要存在,並且資料類型和個數都要匹配

17、char和varchar的區別
char:若定義一個字元大小為10,若唯寫一個字元1,在1後就有9個空格
varchar:若定義一個字元大小為10,若唯寫一個字元1,它就只佔一個字元的位置
   所以varchar可節省磁碟空間
但是要想吧字元1改成1111,就必須吧原來的往後移動,騰出空間,所以效率低
一般：50個字元以內用char
 50個字元以上用varchar
update 表名 set 列名=null --將這個列的資料都置為null

18、在結果集中改變列名的三種方法
<1>select codeId as '學員編號' --可不加引號
<2>select codeId '學員編號' --因為as可以省略
<3>select '學員編號'=codeId

查詢是按百分比提取用關鍵字percent
select top 20 percent * from student
應該是<1>where <2>group by <3>having的使用順序
where和having都是過濾條件
區別是條件中有彙總函式時,只能用having,否則用where