經(jīng)典T_SQL簡單的語法知識VIP

一、簡單的語法知識 1 先舉個例子Codeuse myshopdeclare ident intinsert into orders(customerid,orderdate)values(25,dateadd(day,-1,getdate()-當(dāng)前時間減去1天，就是昨天select ident = identityinsert into details(orderid,productid,unitprice,quantity)values(ident,1,50,25)select the orderid of the inserted row is + convert(varchar(8),ident)把數(shù)據(jù)插入定單表,得到新插入的記錄的id然后插入定單明細(xì)表2 定義變量給變量賦值declare myval int;set myval = 10;set myval1 = myval2*12;-下面語句是在查詢中賦值變量select myval = max(id)+1 form mytable3 if和elseCodeuse xlandif not exists(select id from mytable) print 暫時沒有數(shù)據(jù)else print cast(rowcount as varchar)+被檢索到上面語句執(zhí)行結(jié)果是 0行被檢索到4 begin和end如果條件內(nèi)部要跟代碼塊(多條語句)就要用到begin和end 5 case語句第一種case語句 Codeuse AdventureWorksselect SalesOrderID ,Position = Case SalesOrderID%10 when 1 then First when 2 then Second when 3 then third else some thing elseend from Sales.SalesOrderHeader第二種case語句Codeuse AdventureWorksselect SalesOrderID ,Position = Case when SalesOrderID%10 = 1 then First when SalesOrderID%10 = 2 then Second when SalesOrderID%10 = 3 then third else some thing elseend from Sales.SalesOrderHeader第一種CASE when子句中可以跟表達(dá)式第二種case when子句中表達(dá)式要返回true 或者false當(dāng)前面的條件和后面的條件同時滿足時按前面的條件處理,不需要break語句6 循環(huán)語句while 1=1begin waitfor time 01:00update mytable set num = 12 where id = 13end上面語句是在每天凌晨1點執(zhí)行指定的操作while允許break和continue的存在Codeuse xlandif not exists(select id from mytable) print 沒?有?檢?索?到?數(shù)?據(jù)?else begin select id from mytable print cast(rowcount as varchar)+行?被?檢?索?到? end上面語句執(zhí)行結(jié)果是:2行被檢索到二、簡單的增 刪 改 查 一：insert語句into 關(guān)鍵字是可選的values關(guān)鍵字前面的（）是可選的，這里是要接收數(shù)據(jù)的列values后面，有兩種方式提供值1：顯式的給出值 2：從select語句中導(dǎo)出值insert語句注意幾點1：不要理標(biāo)志列，系統(tǒng)會給你插入的2：給出實際的值，如果沒有，那就null3：給出默認(rèn)的值，default關(guān)鍵字，告訴數(shù)據(jù)庫取默認(rèn)值insert into . select什么時候會這么用,當(dāng)成批的數(shù)據(jù)來自1：數(shù)據(jù)庫中的另一個表2：同一臺服務(wù)器完全不同的數(shù)據(jù)庫3：另一個SQLSERVER的數(shù)據(jù)庫4：來自同一個表中的數(shù)據(jù)說實在的2和3我從來沒用過好，看一個例子declare mytable table( id int, sortid int);insert into mytable (id,sortid) select id,classid from product;select * from mytable;注意我定義了一個表的對象每一句之間是用分號隔開的(id,sortid) 是可以忽略的二：update語句看例子update e set e.flag = m from employee e join contact ce on e.id = ce.employeeid where = jo這里用到了join子句，當(dāng)然是可以用的如果修改不止一列 只要加一個逗號set num = num * 1.2當(dāng)然可以寫表達(dá)式三：delete語句delete from actors from actors a left join film f on a.filmid = f.filmid where f.filmid is nullouter連接將在沒有匹配的一端返回null，這里也就是film表沒有匹配的行是null注意 is null 的寫法四: select語句這里還是不說了,還是多說說子句吧五:where子句1：= = = != ! !其中 與 !=都是不相等的意思!與!5group by得到了一個記錄的集合然后通過having子句，再在這個集合上做篩選三、復(fù)雜一點的查詢 一:inner joininner join 是在做排除，如果任一行在兩個表中不匹配，則注定將從最終的結(jié)果中排除掉例子1：select * from employee e inner join employee m on e.managerid = m.employeeid這是從一個表里查詢了兩次得到的一行記錄將包括兩個employee的信息 前面的是經(jīng)理 后面的是打工的注意from哪個表 哪個表的信息就在前面其中e和m分別是表的別名，這里的別名和列的別名不同，不用寫as例子2 select e.employeeid, ce.firstname, m.employeeid as managerid, cm.firstname as managerfirst from employee e inner join employee m on e.managerid = m.employeeidinner join contact ce on e.contactid = ce.contactidinner join contact cm on m.contactid = cm.contactid第一個聯(lián)接是把一個表與他自身進(jìn)行連接 這個叫自引用(注意表的別名)第二個連接得到經(jīng)理的名字第三個連接得到雇員的名字 看似很復(fù)雜的連接 其實很簡單最后說一點inner join 是默認(rèn)的連接類型 inner 關(guān)鍵字是可選的 二:outer join先看例子 select e.employeeid, m.employeeid as managerid from employee e left outer join employee m on e.managerid = m.employeeid無論左側(cè)表(e)中的行是否與右側(cè)表中的行相匹配都要顯示如果左側(cè)表中的行在右側(cè)表中找不到相匹配的數(shù)據(jù), 那么右側(cè)表的數(shù)據(jù)為nullright outer join 也類似 outer是可以忽略的三：full join 與 cross join這兩個其實都不必多說full join 是 left join 和 right join 的結(jié)合full join將包含位于連接兩側(cè)的表的所有行不存在的行就用null補齊cross join 沒有on操作符得到的是兩測表中所有行的 笛卡兒積就是把兩冊的行排列組合一下一般不會存在null的行這是相當(dāng)罕見的用法只有科學(xué)家或者來搞樣本數(shù)據(jù)的才會用到這個用法四：unionunion更像是從一個查詢直接向另一個查詢進(jìn)行的數(shù)據(jù)追加（差別還是有的）join更像是水平的合并數(shù)據(jù)（添加更多的列），union是垂直的合并數(shù)據(jù)（添加更多的行）先看例子：select col2 from table1 union all select col4 from table21：select 列表中字段的數(shù)量必須相同2：字段的數(shù)據(jù)類型必須隱士兼容3：返回的結(jié)果集的標(biāo)頭取自第一個查詢4：默認(rèn)返回方式是distinct，union alll返回全部的集合五：子查詢返回單個值先看例子：declare hits intselect hits = min(hitnum) from articlesselect distinct A.title from articles A join users U on A.uid = U.id where A.hitnum = hits這是完全可行的，但是我們可以用另一種形式：select distinct A.title from articles A join users U on U.id = A.uid where A.hitnum = (select min(hitnum) from articles )這就是子查詢六：子查詢返回多個值接著看例子（子查詢里返回多個值）use databaseselect A.title from articles A join users U on A.uid = U.id where A.id in (select id from articles where istop = true)再看一個例子（用子查詢找出孤立的記錄）select A.title from article A join users U on A.uid = U.id where A.id not in (select id from articles where istop = true)這個例子寫的有點牽強但是這里注意 not in 子查詢得到的字段 不能有null直 存在，如果有 那么整個句子將返回空細(xì)心的人大概看出來了，前面提到的兩個子查詢的例子幾乎都可以用內(nèi)聯(lián)（join）的方式替換掉出于性能上的考慮，我們應(yīng)該首先選擇聯(lián)結(jié)的解決方案 而不是子查詢七：any some 和 allany與some功能上是相同的，推薦用someany與some一般都是和比較運算符一起使用的(= = ! 等 )some (1,2,3)意味著大于1 some就是大于最小的一個值=some和in 起的作用是一樣的not in (a,b,c)展開為 a and b and csome (a,b,c)展開為 a or b or call (1,2,3)意味著大于3八：where子句中的相關(guān)子查詢（外部查詢和內(nèi)部查詢）先看個例子Codeuse adventureworksdrop table #MinOrderdatesselect CustomerID, min(OrderDate)as orderdate into #MinOrderdatesfrom Sales.SalesOrderHeadergroup by CustomerIDorder by CustomerIDselect o.CustomerID,o.SalesOrderID,o.OrderDatefrom Sales.SalesOrderHeader ojoin #MinOrderdates ton o.CustomerID = t.CustomerIDand o.OrderDate = t.orderdateorder by o.CustomerIDdrop table #MinOrderdates 每個顧客在系統(tǒng)中的第一張定單的orderid 和orderdate用到了臨時表，執(zhí)行了兩次查詢看下面的例子Codeselect o1.CustomerID,o1.SalesOrderID,o1.OrderDatefrom Sales.SalesOrderHeader o1where o1.OrderDate = (select min(o2.OrderDate) from Sales.SalesOrderHeader o2where o2.CustomerID = o1.CustomerID )order by CustomerID 執(zhí)行了一次查詢注意內(nèi)部查詢對外部查詢有一個顯示的引用 o2.CustomerID = o1.CustomerID 當(dāng)然外部查詢也可以引用內(nèi)部查詢中的列第二個例子 Codeselect c.LastName,(select min(OrderDate) from Sales.SalesOrderHeader owhere o.ContactID = c.ContactID) as orderdatefrom Person.Contact c 九：派生表先看例子： 訂購過某種特定產(chǎn)品的顧客列表Codeuse AdventureWorks;select c.FirstName,c.LastNamefrom Person.Contact As cjoin Sales.SalesOrderHeader as sohon c.ContactID = soh.ContactIDjoin Sales.SalesOrderDetail as sodon soh.SalesOrderID = sod.SalesOrderIDjoin Production.Product as pon sod.ProductID =p.ProductIDwhere p.Name = Minipump查找顧客的名字以及首次訂購的日期90行受影響現(xiàn)在我們想查找即訂購過Minipump又訂購過AWC Logo Cap的顧客如果最后是where p.Name = Minipump or p.Name = AWC Logo Cap2412行受影響這樣做是錯誤的這樣得到的結(jié)果是訂購過Minipump和訂購過AWC Logo Cap的顧客下面來看我們的解決方法 Codeuse AdventureWorksselect distinct c.FirstName,c.LastNamefrom Person.Contact As cjoin 四、約束 一：類型約束的類型一共分三種域約束：涉及一個或多個列，（限制某一列的數(shù)據(jù)大于0）實體約束：相同的值不能存在于其他的行中引用完整性約束：一個表中的一個列與某個表中的另一個列的值匹配二：命名約束是可以命名的 一般這樣命名：pk_customer_*pk代表主鍵 customer代表主鍵所在的表 后面是你自己定義的（要確保整個名稱的唯一性）三：主鍵約束主鍵約束：一般就是id, 一個表中最多有一個主鍵例子1use accountingcreate table employee(id int identity not null,firstname varchar(20) not null)例子2use accountingalter table employeeadd constraint pk_employeeidprimary key (id)四：外鍵約束外鍵約束用在確保數(shù)據(jù)完整性和兩個表之間的關(guān)系上先看例子create table orders(id int identity not null primary key,customerid int not null foreign key references customer(id),orderdate smalldatetime not null,eid int not null)注意：這個表的外鍵必須是另一個表的主鍵！在現(xiàn)有表上添加外鍵alter table ordersadd constraint fk_employee_creator_orderforeign key (eid) references employee(employeeid)使用表自引用表內(nèi)至少要有一行數(shù)據(jù)才可以這么做alter table employeeadd constraint fk_employee_has_managerforeign key (managerid) references employee(employeeid)創(chuàng)建表的時候做表自引用 就可以忽略 foreign key 語句表自引用的外鍵列 必須允許為null 要不是不允許插入的（避免對最初行的需要）一個表與另一個表有約束，這個表是不能被刪除的 級聯(lián)操作先看例子create table orderdetails( orderid int not null , id int not null , description varchar(123) not null, -設(shè)置主鍵 constraint pkOrderdetails primary key (orderid,id), -設(shè)置外鍵，級聯(lián)操作 constraint fkOrderContainsDetails foreign key (orderid) references orders(orderid) on update no action on delete cacade)on delete cacade 當(dāng)刪除父記錄時 同時刪除該記錄也就是當(dāng)刪除orders表中的一條記錄，與之相關(guān)的orderdetails表中的記錄也將被刪除級聯(lián)的深度是沒有限制的,但是每個外鍵都必須設(shè)置on delete cacade no action是可選的五：unique約束unique約束與主鍵約束類似，同樣也是要求指定的列有唯一的值但是一個表中可以有多個unique約束的列，同時這個列允許存在null值。（最多有一個null值）看例子：create table shippers( id int indentity not null primery key,zip varchar(10) not null ,phoneno varchar(14) not null unique)例子二：alter table employeeadd constraint ak_employeeSSNunique(ssn)六：check約束check不局限于一個特定的列，可以約束一個列，也可以通過某個列來約束另一個列定義check約束使用的規(guī)則與where子句中的基本一樣下面我寫幾個between 1 and 12like 0-90-90-9-0-90-90-90-90-90-90-9in (ups,fed ex,usps)price =0shipdate = orderdate看例子：alter table customersadd constraint cn_customerDateinsystemcheck(dateinsystem 0;sp_bindrule SalaryRule , Employee.Salary第一句定義了一個規(guī)則叫SalaryRule進(jìn)行比較的事物是一個變量這個變量的值是所檢查的列的值第二句把規(guī)則綁定到某個表的一個列上規(guī)則和ckeck約束很相似，但是規(guī)則只作用在一個列上一個規(guī)則可以綁定在多個列上，但是它不會意識到其他列的存在check可以定義column1=column2取消規(guī)則exec sp_unbindrule Employee.Salary刪除規(guī)則Drop rule SalaryRule十：默認(rèn)值默認(rèn)值與default約束類似（有區(qū)別的，但是我說不清楚）先看例子：create default salarydefaultas 0;exec sp_binddefault salarydefault , employee.salary;取消默認(rèn)值：exec sp_unbinddefault employee.salary刪除默認(rèn)值：drop default salarydefault五、create drop與alter 一：dropuse accounting drop table customers刪除表use masterdrop database accounting刪除數(shù)據(jù)庫這里刪除數(shù)據(jù)庫 use 的是master一定要關(guān)閉外部連接 才能刪除數(shù)據(jù)庫二：create先看例子create database accounting on (name = accounting, filename = c:databaseaccountingdata.mdf, size = 10, maxsize = 50, filegrowth = 5)log on (name = accountinglog, filename = c:databaseaccountinglog.ldf, size = 5MB, maxsize = 25MB, filegrowth = 5MB )gocreate database完整的語法有很多子句的我上面寫的那個例子只涉及到幾個常用的下面解釋一下:on指存儲數(shù)據(jù)文件的位置，可以把數(shù)據(jù)存儲到多個文件上（這只應(yīng)用在超大型數(shù)據(jù)庫上）name是文件的邏輯名稱，調(diào)整數(shù)據(jù)庫文件大小時要用到這個名稱filename可選的參數(shù)，如果不使用這個參數(shù)，那么這個文件就存在MSSQL.1MSSQLDATA目錄下 如果是數(shù)據(jù)文件那么名稱與數(shù)據(jù)庫名稱相同，擴展名是.mdf如果是日志文件那么名稱是數(shù)據(jù)庫名后跟一個_log，擴展名是.ldfsize數(shù)據(jù)庫的大小，可以帶單位也可以直接寫整數(shù)，不能寫小數(shù)maxsize數(shù)據(jù)庫能增大到的最大大小，如果不設(shè)置這個參數(shù)，那么就是磁盤的大小，建議設(shè)置一個數(shù)字比磁盤小一點filegrowth數(shù)據(jù)庫每次增長將增長多少，可以是整數(shù)，百分?jǐn)?shù)log on 是設(shè)置日志的，默認(rèn)設(shè)置日志文件是數(shù)據(jù)文件大小的25%日志文件最好不要和數(shù)據(jù)文件在同一個磁盤上，避免競爭，安全保障Create table先看例子use accountingcreate table customers(id int idetity not null primary key,username varchar(30) not null)就不多說了說一下命名規(guī)范1：保持名稱簡短，長度要足以使名稱具有描述性2：當(dāng)基于其他表來構(gòu)造表時，新表的名稱中其包含父表的名稱3：當(dāng)名稱中有兩個單詞時，不要使用任何分隔符，每個單詞首字母大寫三：ALTERalter database accounting modify file (name = accounting , size = 100MB)上面是把數(shù)據(jù)庫文件擴展到100MBalter table customers add fedid varchar(9) nullalter table customers add contact varchar(25) null,lastraisedate datetime not null default 2005-12-4下面看個關(guān)于主鍵的alter語句use accountingalter table employee add constraint pk_employeeidprimary key (employeeid)要向表中添加約束，添加約束的名字約束的類型將約束應(yīng)用到的列六、視圖 一:簡單的視圖use xlandgocreate view my_vwas select a.id, a.title,u.username from mytable a join user u on u.id = a.uid執(zhí)行這個視圖use xlandgoselect * from my_vw結(jié)果跟直接運行select a.id, a.title,u.username from mytable a join user u on u.id = a.uid是一樣的二:刪除和修改視圖alter view yourviewname as.drop view yourviewname as.三:加密視圖alter view yourviewname with encryption as.加密了之后連你自己也看不到原代碼了七、存儲過程和用戶自定義函數(shù) 一:存儲過程的簡單創(chuàng)建,修改與刪除1.創(chuàng)建簡單的存儲過程use AdventureWorksgocreate proc spEmployeeasselect * from HumanResources.Employee執(zhí)行上面的代碼就創(chuàng)建了一個存儲過程如果想運行這個存儲過程可以直接執(zhí)行exec spEmployee這個語句2.更改存儲過程ALTER proc dbo.spEmployeeasselect top 13 * from HumanResources.Employee3.刪除存儲過程drop p