常用SQL語句

select a.sno,a.sname,a.ssex,a.sage,a.sdept,b.sno,b.cno,b.grade from student a,sc b  where a.sno=b.sno

select a.sno,a.sname,a.ssex,a.sage,a.sdept,b.sno,b.cno,b.grade from student a  left join sc b on a.sno=b.sno

注意以上兩個(gè)語句的區(qū)別，后一個(gè)語句的結(jié)果大于第一個(gè)語句的結(jié)果

select * from student where sdept='cs' or sage<19  等同

select * from student where sdept='cs' union select * from student where sage< 19

取并集并去掉重復(fù)組

1.create table student (sno char(5) not null unique,

                       sname char(20),

                       ssex  char(2),

                       sage  int,

                       sdept char(15))

2.alter table student add scome date

3.alter table student modify sage smallint

4.alter table student drop unique(sno)

5.drop table student

6.create unique index stusno on student(sno)

 create unique index coucno on course(cno)

 creat  unique index scno on sc(sno asc,cno desc)

7.drop index stusname

8. select sname name ,'year of birth:' birth,2010-sage birthday,islower(sdept)  department

  from student

9.select distinct sno from sc

10. select sname,sage from student where sage<20

11.select sname,sdept,sage from student where sage between 20 and 23

12.select sname,sdept,sage from student where sage not between 20 and 23

13.select sname,sdept,sage from student where sdept in ('is','ma','cs') 等同于

                                       where sdept ='is' or sdept='ma' or sdept='cs'

14.select sname , ssex from student where sdept not in ('is','ma','cs')

15.字符匹配 %（百分號(hào)）代表任意長度（長度可以為零）的字符串。 _(下橫線)代表任意單個(gè)字符。如果用戶要查詢的匹配字符串本身就含有%或_，比如要查名字為DB_Design

的課程的學(xué)分，這時(shí)就要使用ESCAPE‘換碼字符’短語對(duì)通配符進(jìn)行轉(zhuǎn)義了。

select cno,ccredit from course where cname like 'DB\_Design' Escape '\'

ESCAPE '\' 短語表示\為換嗎字符，這樣陪陪串中緊跟在\后面的字符‘_’不在具有通配符的含義，而是取其本身含義。

16。select * from student where sno like '95001'

17.select * from student where sname like '劉%'

18.select * from student where sname like '歐陽_ _'

19.sekect * frin stydebt where sname not like '劉%'

20..涉及空值查詢 is null 和　is not null

select * from sc where grade is null  注意這里的'is' 不能用等號(hào)（‘=’）代替。

21.select sno,grade from sc where cno='3' order by grade desc

22.select sno,sdept,sage from sc where cno='3' order by sdept,sage desc

23.使用集函數(shù)

count([distinct/all] *)       統(tǒng)計(jì)元組個(gè)數(shù)

count([distinct/all] <列名>)  統(tǒng)計(jì)一列中值的個(gè)數(shù)

sum([distinct/all] <列名>)    統(tǒng)計(jì)一列值的總和（此列必須是數(shù)值型）

AVG([distinct/all] <列名>)    計(jì)算一列值的平均值

MAX([distinct/all] <列名>)    求一列值中的最大值

MIN([distinct/all] <列名>)    求一列值中的最小值

24 .select cno,count(sno) from sc group by cno

select max(grade) from sc where cno='1'

select sno from sc where dept='si' group by sno having count(*) >3

注明:where 子句與Having 短語的根本區(qū)別在于作用對(duì)象不同。where 子句作用與基本表和視圖，Having短語作用于組。

25. select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno

在本查詢中由于sname,ssex,sage,sdept,cno和grade屬性列在表student 與sc表中是唯一的，因此引用時(shí)可以去掉表明前綴。

--------導(dǎo)供應(yīng)商資料(prov為從EXCEL表里導(dǎo)入的臨時(shí)表)------------------------------------------------------------------------

insert providers (provno,provname,fullname,provpycode)

select convert(varchar(8),provno),convert(varchar(120),provname),convert(varchar(120),fullname),convert(varchar(120),provpycode)

from prov

----------------導(dǎo)藥品類別(kinds為從EXCEL表里導(dǎo)入的臨時(shí)表kindno varchar(12),Kindid int(4),kindname varchar(20) ---------------------------------------------------------------------------------------

Insert  MainKinds(KindNO,KindId,KindName)

select convert(varchar(12),KindNO),convert(varchar(4),KindId),convert(varchar(20),KindName)

from kinds

------------導(dǎo)藥品資料(goods為從EXCEL表里導(dǎo)入的臨時(shí)表)---------------------------------------------------------------------------------------------------

insert goodses (GoodsID ,name,pym,Specs,PassNo,BarCode,Makername,PDCADDR, UseFulUnit,Unit1,conv,mainkind)

select convert(varchar(16),GoodsID) ,convert(varchar(100),name),convert(varchar(64),pym),

convert(varchar(120),Specs),convert(varchar(80),PassNo),

convert(varchar(13),BarCode),convert(varchar(80),Makername),convert(varchar(12),PDCADDR),

'月',convert(varchar(8),Unit1),1,1

from goods

insert goodses (GoodsID ,Alias ,name,pym,Specs,DoseType,PassNo,BarCode,Makername,PDCADDR,Usefullife, UseFulUnit,Unit1,hypokind,conv,mainkind)

select convert(varchar(16),GoodsID) ,convert(varchar(100),Alias) ,convert(varchar(100),name),convert(varchar(64),pym),

convert(varchar(120),Specs),convert(varchar(16),DoseType),convert(varchar(80),PassNo),

convert(varchar(13),BarCode),convert(varchar(80),Makername),convert(varchar(12),PDCADDR),

convert(int,Usefullife), '月',convert(varchar(8),Unit1),convert(varchar(20),hypokind),1,k.kindid

from goods G join MainKinds K on G.MainKind=K.Kindid -----k.kindno(類別編號(hào))

-- 從一個(gè)臨時(shí)表中導(dǎo)數(shù)據(jù)到藥品表中（過濾掉重復(fù)的行）

declare @GoodsID varchar(16), @BarCode varchar(13), @Name varchar(100),

       @Specs varchar(120), @Maker varchar(100), @PassNo varchar(80),

       @Unit1 varchar(8), @Pym varchar(8), @MainKind int

declare cur_Goods cursor for

 select GoodsID, BarCode, Name, Specs, Maker, PassNo, Unit1, Pym, MainKind from Goods

open cur_Goods

fetch next from cur_Goods

 into @GoodsID, @BarCode, @Name, @Specs, @Maker, @PassNo, @Unit1, @Pym, @MainKind

while @@fetch_status=0

begin

 if not exists(select 1 from Goodses where GoodsID=@GoodsID)

   insert into Goodses(GoodsID, BarCode, Name, Specs, Maker, PassNo, Unit1, Pym, MainKind,UseFulUnit)

   values(@GoodsID, @BarCode, @Name, @Specs, @Maker, @PassNo, @Unit1, @Pym, @MainKind, '月')

 fetch next from cur_Goods

   into @GoodsID, @BarCode, @Name, @Specs, @Maker, @PassNo, @Unit1, @Pym, @MainKind

end

close cur_Goods

deallocate cur_Goods

-- 將goods表中的InPrice更新到藥品價(jià)格表的進(jìn)價(jià)主單位價(jià)格

-- 將goods表中的OutPrice更新到藥品價(jià)格表的零售價(jià)主單位價(jià)格

update goodsprices set price1=goods.inprice  from goods where goodsprices.goodsid=goods.goodsid and modeno=1

update goodsprices set price1=goods.outprice from goods where goodsprices.goodsid=goods.goodsid and modeno=3

update GoodsPrices set P.Price1=G.Price1

from GoodsPrices P join price G on P.GoodsID=G.GoodsID and P.ModeNo=1

update goodsprices set P.Price1=G.price1

from GoodsPrices P join price G on P.GoodsID=G.GoodsID and P.ModeNo=3

----------------------常用查詢--------------------------------------------------------

----select  top 0 *  into goods from goodses

----select top 5 * into goods from goodses

----truncate table goods1

----drop table goods2

select kindname,count(kindname)  from kinds  group by kindname

select GoodsID from goods group by GoodsID  having count(1)>1

select billno, count(billno)  記錄,goodsid from retaildtl  group by billno,goodsid having count(billno)=1 and goodsid='xxx'

select goodsid from goods where goodsid not in (select goodsid from goodses)

UPDATE Goodses SET GoodsID=LEFT(GoodsID,LEN(GoodsID)-2)

update goodses set PDCADDR=substring(PDCADDR,1,6)

select * from goods where len (mainkind) >6

select * from goods order by mainkind

backup database XXXX to disk='C:\DB.BAK'

restore database xxxx from disk='C:\DB.BAK'

select sname,sage from student

          where sage < any

                      (select sage from student where sdept='is')

                and sdept<>'is'

select sno,sname,sdept from student

             where sdept=( select sdept from student where sname='liuchen')

dbcc checkdb('數(shù)據(jù)庫')

-----------------------------------------------------------------------------------------------------------------

-- 從一個(gè)臨時(shí)表中導(dǎo)數(shù)據(jù)到藥品表中（過濾掉重復(fù)的行）

declare @GoodsID varchar(16), @BarCode varchar(13), @Name varchar(100),

       @Specs varchar(120), @Maker varchar(100), @PassNo varchar(80),

       @Unit1 varchar(8), @Pym varchar(8), @MainKind int

declare cur_Goods cursor for

 select GoodsID,Name, Specs, Maker, PassNo, Unit1, MainKind from Goods

open cur_Goods

fetch next from cur_Goods

 into @GoodsID, @Name, @Specs, @Maker, @PassNo, @Unit1, @MainKind

while @@fetch_status=0

begin

 if not exists(select 1 from Goodses where GoodsID=@GoodsID)

   insert into Goodses(GoodsID,Name, Specs, Maker, PassNo, Unit1, MainKind,UseFulUnit)

   values(@GoodsID, @Name, @Specs, @Maker, @PassNo, @Unit1 , @MainKind, '月')

 fetch next from cur_Goods

   into @GoodsID, @Name, @Specs, @Maker, @PassNo, @Unit1, @MainKind

end

close cur_Goods

deallocate cur_Goods

-----------------------------------------------------------------------------------------------

update [aaaa]..goodses set name=goodses.name from goodses where [aaaa]..goodses.goodsid=goodses.goodsid

select D.BillNo,D.GoodsID,G.Name,G.Alias,G.Specs,D.Qty,D.Unit,D.Price,D.Amount

from RetailDtl D

 join Goodses G on D.GoodsID=G.GoodsID

 join (select BillNo from RetailDtl group by BillNo having count(1)=1) T on D.BillNo=T.BillNo