补充SQL应用举例.ppt
SQL 应用举例,SQL 应用举例,2）插入数据declare counter smallintset counter=1while counter=300begin insert into t values(1+RAND(counter),2+RAND(2*counter),3+RAND(3*counter),4+RAND(4*counter)set counter=counter+1end,构建表T(a，b，c，d)，其中a，b，c，d全部定义为number型，用程序随机插入300行数据，然后对字段c降序排序后，取第2130条记录显示。,1）创建表结构create table T(a numeric(20,17),b numeric(20,17),c numeric(20,17),d numeric(20,17),1,SQL 应用举例,构建表T(a，b，c，d)，其中a，b，c，d全部定义为number型，用程序随机插入300行数据，然后对字段c降序排序后，取第2130条记录显示。,1,3）查询select top 10*from(select top 30*from T order by C desc)as temptableorder by C asc,SQL 应用举例,点评：,1）随机函数RAND(seed)：返回 0 到1 之间的随机float 值。seed：种子，整数值或整型表达式（tinyint、smallint 或 int）。引入目的：技能测试时，方便录入库表数据2）select top n*显示前n条记录的所有字段信息 select top n sname,sex 显示前n条记录的姓名和性别字段 select top n percent*显示前n%条记录的所有字段信息 select top n percent sname,sex 显示前n%条记录的姓名和性别字段,SQL 应用举例,点评：,3）其它数学函数如下表，请自行查阅SQL SERVER 2000 帮助：,SQL 应用举例,2,1）创建表create table table1(mc char(1),sl int)create table table2(mc char(1),sl int),SQL 应用举例,2,2）插入数据insert into table1 values(A,100)insert into table1 values(B,120)insert into table1 values(A,90)insert into table1 values(B,60)insert into table2 values(A,10)insert into table2 values(A,20)insert into table2 values(B,10)insert into table2 values(B,20)insert into table2 values(B,30),SQL 应用举例,2,3）查询select X.商品名称,进库量,出库量,进库量-出库量 as 库存量from(select table1.mc as 商品名称,sum(table1.sl)as 进库量 from table1 group by table1.mc)as X,(select table2.mc as 商品名称,sum(table2.sl)as 出库量 from table2 group by table2.mc)as Ywhere X.商品名称=Y.商品名称,SQL 应用举例,点评：,导出表的灵活运用：导出表可以作为子查询块，嵌入在from子句、where子句和having子句中。,SQL 应用举例,表pages有四个字段，为id，url，title，body；分别记录网页ID，网页的url地址，网页的title和网页的内容，用一个sql查询将url匹配的排在最前，title匹配的其次，body匹配最后，没有任何字段匹配的，不返回。,3,以查询“baidu”为例:方法1：SELECT*FROM pages WHERE url like%baidu%or title like%baidu%or body like%baidu%ORDER BY CHARINDEX(baidu,url)DESC,CHARINDEX(baidu,title)DESC,CHARINDEX(baidu,body)DESC,SQL 应用举例,表pages有四个字段，为id，url，title，body；分别记录网页ID，网页的url地址，网页的title和网页的内容，用一个sql查询将url匹配的排在最前，title匹配的其次，body匹配最后，没有任何字段匹配的，不返回。,3,以查询“baidu”为例:方法2：select temptable.id,temptable.markfrom(select pages.id,100 as mark from pages where pages.url like%baidu%union select pages.id,50 as mark from pages where pages.title like%baidu%union select pages.id,10 as mark from pages where page.body like%baidu%)as temptableorder by mark desc,SQL 应用举例,点评：,1）函数CHARINDEX：返回字符串中指定表达式的起始位置。CHARINDEX(expression1,expression2,start_location)expression1 表达式，包含要寻找的字符的次序，是一个短字符数据类型分类的表达式。expression2 表达式，通常是一个用于搜索指定序列的列，属于字符串数据类型分类。start_location 在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location，而是一个负数或零，则将从 expression2 的起始位置开始搜索。2）并、交、差运算：引入以解决特定问题,SQL 应用举例,点评：,3）其它字符串函数如下表，请自行查阅SQL SERVER 2000 帮助：,SQL 应用举例,表S（SNO，SNAME），C（CNO，CNAME）和SC（SNO，CNO，GRADE），删除所有选修人数少于10人的课程信息。,4,1）创建表结构方法2：create table S(SNO char(4)primary key,SNAME char(8)create table C(CNO char(4)primary key,CNAME char(16)create table SC(SNO char(4)foreign key references S ON DELETE NO ACTION,CNO char(4)foreign key references C ON DELETE NO ACTION,GRADE int CHECK(GRADE between 0 and 100),primary key(SNO,CNO),SQL 应用举例,表S（SNO，SNAME），C（CNO，CNAME）和SC（SNO，CNO，GRADE），删除所有选修人数少于10人的课程信息。,4,2）插入数据insert into S values(S001,张0001)insert into S values(S011,张0011)insert into C values(C001,数据结构)insert into C values(C002,操作系统)insert into SC values(S001,C001,97)insert into SC values(S002,C001,NULL)insert into SC values(S011,C001,67)insert into SC values(S007,C002,73)insert into SC values(S010,C002,NULL)insert into SC values(S011,C002,61),SQL 应用举例,表S（SNO，SNAME），C（CNO，CNAME）和SC（SNO，CNO，GRADE），删除所有选修人数少于10人的课程信息。,4,3）信息删除在表SC中，将选修人数少于10人的“选修信息”删除 delete from SCwhere CNO in(select CNO from SC Group by CNO having count(SNO)10)在表C中，将没有人选修的课程信息删除delete from Cwhere CNO not in(select distinct CNO from SC),SQL 应用举例,点评：,SQL 应用举例,计算当月的天数。提示：需要用到datepart，dateadd，year，getdate，month，datetime等函数或常量，函数的帮助信息可在“SQL Server联机丛书目录Transact-SQL 参考函数”中查询。,5,Select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()as varchar)+-+cast(month(getdate()as varchar)+-01 as datetime)其它，诸如：1）查询本月的第一天 SELECT DATEADD(mm,DATEDIFF(mm,0,getdate(),0)2）查询本周的星期一 SELECT DATEADD(wk,DATEDIFF(wk,0,getdate(),0)3）查询本年度的第一天 SELECT DATEADD(yy,DATEDIFF(yy,0,getdate(),0)4）查询本季度的第一天 SELECT DATEADD(qq,DATEDIFF(qq,0,getdate(),0),SQL 应用举例,5）查询上个月的最后一天 SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate(),0)6）查询去年的最后一天 SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate(),0)7）查询本月的最后一天SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()+1,0)8）查询本月的第一个星期一 SELECT dateadd(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate(),getdate(),0)9）查询本年的最后一天 SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()+1,0),计算当月的天数。提示：需要用到datepart，dateadd，year，getdate，month，datetime等函数或常量，函数的帮助信息可在“SQL Server联机丛书目录Transact-SQL 参考函数”中查询。,5,SQL 应用举例,点评：,DATEADD，DATEDIFF，DATENAME，DATEPARTDAY，GETDATE，GETUTCDATE，MONTH，YEAR请自行查阅SQL SERVER 2000帮助,1）数据类型转换函数：将某种数据类型的表达式显式转换为另一种数据类型,CAST(expression AS data_type)CONVERT(data_type(length),expression,style)请自行查阅SQL SERVER 2000帮助,2）日期和时间函数：,SQL 应用举例,6,SQL 应用举例,6,select X.id as 前驱数,MIN(Y.id)AS 后继数from test AS X,test AS Ywhere Y.id X.idgroup by X.idhaving MIN(Y.id)X.id+1order by X.id;-Order by子句可有可无,SQL 应用举例,用一组SQL语句，自动生成不重复流水号，格式yyyymmdd-aaaa，其中yyyymmdd为当前年月日，aaaa为4位数字，注意mm、dd及aaaa不足位时前面填0，如：20050314-0001，20050314-0002，,7,2）生成流水号declare seqNo char(15)declare temp char(15)declare seqNnum intset seqNo=CONVERT(char(8),getdate(),112)set temp=(select max(id)from tmpTable where left(id,8)=seqNo)if temp is NULL set seqNnum=1else set seqNnum=(right(temp,4)+1set temp=left(0000,4-len(seqNnum)+cast(seqNnum as char)set seqNo=CONVERT(char(8),getdate(),112)+-+tempinsert into tmpTable values(seqNo)select*from tmpTable,1）创建表结构create table tmpTable(id char(13)primary key),SQL 应用举例,8,1）创建并初始化表create table Test2(id char(3),tname char(2);insert into Test2 values(101,aa);insert into Test2 values(101,bb);insert into Test2 values(102,cc);,insert into Test2 values(103,dd);insert into Test2 values(104,ee);insert into Test2 values(101,ff);insert into Test2 values(102,gg);,SQL 应用举例,8,2）删除相应纪录，并加入主键约束delete from test2 where tname not in(select min(tname)from test2 group by id);alter table test2 ALTER COLUMN id char(3)not null;-不能在可以为空的字段上定义主键约束alter table test2 add primary key(id);,SQL 应用举例,9,1）创建并初始化表 create table SC_Temp(SNAME char(8),CNAME char(16),GRADE int)insert into SC_Temp values(张三,语文,20);insert into SC_Temp values(张三,数学,30);insert into SC_Temp values(张三,英语,50);insert into SC_Temp values(李四,语文,70);insert into SC_Temp values(李四,数学,60);insert into SC_Temp values(李四,英语,90);,2）查询实现select A.SNAME as 姓名,A.GRADE as 语文,B.GRADE as 数学,C.GRADE as 英语 from SC_Temp as A,SC_Temp as B,SC_Temp as C where A.SNAME=B.SNAME and B.SNAME=C.SNAME and A.CNAME=语文 and B.CNAME=数学 and C.CNAME=英语,SQL 应用举例,10,首先建立表结构，并初始化数据：create table Product(PNAME Char(6),PCOLOR Char(4),PNUM int),insert into Product values(产品1,红色,123);insert into Product values(产品1,蓝色,126);insert into Product values(产品2,蓝色,103);insert into Product values(产品2,红色,NULL);insert into Product values(产品2,红色,89);insert into Product values(产品1,红色,203);,SQL 应用举例,10,方法 1）查询实现：select PNAME as 产品,sum(case when PCOLOR=红色 then PNUM else 0 end)sum(case when PCOLOR=蓝色 then PNUM else 0 end)as 差额from Productgroup by PNAMEhaving sum(case when PCOLOR=红色 then PNUM else 0 end)sum(case when PCOLOR=蓝色 then PNUM else 0 end)方法 2）查询实现select PNAME as 产品,红色=sum(case when PCOLOR=红色 then PNUM else 0 end),蓝色=sum(case when PCOLOR=蓝色 then PNUM else 0 end)from Product group by PNAME,
