经典SQL语句学习.

1、说明：复制表(只复制结构，源表名：a新表名：b select * into b from a where 11说明：拷贝表(拷贝数据，源表名：a目标表名：b insert into b(a, b, c select d,e,f from b;说明：显示文章、提交人和最后回复时间select a.title,a.username,b.adddatefrom table a,(select max(adddate adddate from table where table.title=a.title 说明：夕卜 连接查询(表名 1: a 表名 2： b select a.a, a.b, a.c,

2、 b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c说明：日程安排提前五分钟提醒 select * from日程安排where datediff(minute,f开始时间,getdate(5说明：两张关联表，删除主表中已经在副表 中没有的信息 delete from info where not exists ( select * from infobz where info.infid=infobz.infid 说明：-SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TAB

3、LE1, (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,YYYY/MM = TO_CHAR(SYSDATE, YYYY/MM X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,YYYY/MM = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, YY

4、YY/MM | /01,YYYY/MM/DD - 1, YYYY/MM Y, WHERE X.NUM = Y.NUM(+) AND X.IN BOUND_QTY + NVL(Y.STOCK_ONHAND,O X.STOCK_ONHAND B WHERE A.NUM = B.NUM 说明：-select * from studentinfo where not exists(select * from stude nt where stude ntin fo.id=stude nt.id an系名称 =&strdepartme ntn ame& and 专业名称=&strprofessio nn

5、 ame& order by 性别，生源 地，高考总成绩 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两 个表来源)SELECT a.userper, a.tel, a.sta ndfee, TO_CHAR(a.telfeedate, yyyy AS telyear, SUM(decode(TO_CHAR(a.telfeedate, mm, 01, a.factratio n AS JAN, SUM(decode(TO_CHAR(a.telfeedate, mm, 02, a.factratio n AS FRI, SUM(decode(TO_CHAR(a.telfeedate,

6、 mm, 03, a.factratio n AS MAR, SUM(decode(TO_CHAR(a.telfeedate, mm, 04, a.factratio n AS APR, SUM(decode(TO_CHAR(a.telfeedate, mm, 05, a.factratio n AS MAY, SUM(decode(TO_CHAR(a.telfeedate, mm, 06, a.factratio n AS JUE,SUM(decode(TO_CHAR(a.telfeedate, mm, 07, a.factratio n AS JUL, SUM(decode(TO_CHAR

7、(a.telfeedate, mm, 08, a.factratio n AS AGU, SUM(decode(TO_CHAR(a.telfeedate, mm, 09, a.factratio n AS SEP, SUM(decode(TO_CHAR(a.telfeedate, mm, 10, a.factratio n AS OCT, SUM(decode(TO_CHAR(a.telfeedate, mm, 11, a.factratio n AS NOV, SUM(decode(TO_CHAR(a.telfeedate, mm, 12, a.factratio n AS DEC FROM

8、 (SELECT a.userper, a.tel, a.sta ndfee, b.telfeedate, b.factratio n FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax a GROUP BY a.userper, a.tel, a.sta ndfee, TO_CHAR(a.telfeedate, yyyy说明：四表联查问题 select * from a leftinner join b on a.a=b.b right inner join c on a.a=c.c inn er jo in d on a.a=d.d wh

9、ere . 说明：得至U表中最小的未使用的 ID号SELECT (CASE WHEN EXISTS(SELECT * FROM Han dle b WHERE b.Ha ndlelD = 1 THEN MIN(Ha ndlelD + 1 ELSE 1 END as Ha ndlelD FROM Han dle WHERE NOT Ha ndlelD IN (SELECT a.Ha ndlelD - 1 FROM Handle a一个SQL语句的问题：行列转换select * from v_temp上面的视图结果如下： user_name role_name系统管理员 管理员 feng 管理员

10、feng 般用户test 一般用户 想把结果变成这样:user_name role_name系统管理员管理员feng管理员，一般用户test 一般用户= create table a_test (n ame varchar(20,role2 varchar(20 in sert into a_test value李(,管理员 insert into a_test values张,管理员insert into a_test values张,一般用户insert into a_test values(常,一般用户create function join_str(content varchar(10

11、0 returns varchar(2000 as begi n declare str varchar(2000 set str= select str=str+,+rtrim(role2 from a_test where n ame=c ontent select str=right(str,le n(str-1 return str end go -调用： selectn ame,dbo.join_str( name role2 from a_test group by n ame -select disti net name,dbo.uf_test( name from a_tes快

12、速比较结构相同的两表 结构相同的两表，一表 有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？ = 给你一个测试方法，从 northwind 中的 orders表取数据。 select * into n1 from orders select * into n2 from orders select * from n1select * from n2 -添加主键，然后修改 n1中若干字段的若干条 alter table n1 add con stra int pk_n1d primary key (OrderlD alter table n2 add con stra in

13、t pk_n 2_id primary key (OrderID select OrderID from (select * from n1 union select * from n2 a group by OrderlD having count(* 1应该可以，而且将不同的记录的ID显示出来。下面的 适用于双方记录一样的情况，select * from n1 where orderid in (select OrderlD from(select * from n1 union select * from n2 a group by OrderlD hav ing coun t(* 1

14、至于双 方互不存在的记录是比较好处理的-删除n1,n2中若干条记录delete from n1 whereorderlD in (10728,10730 delete from n2 where orderlD in (11000,11001-*双方都有该记录去卩不完全相同 select * from n1 where orderid in (select OrderlD from (select * from n1 union select * from n2 a group by OrderlD having count(* 1 union -n2 中存 在但在 n1 中不存的在 1072

15、8,10730 select * from n1 where OrderlD not in (select OrderlD from n2 union -n1 中存在但在 n2 中不存的在 11000,11001 select * from n2 where OrderlD not in (select OrderlD from n1 四种方法取表里 n 至U m 条纪录：1. select top m * into 临时表(或表变量 from table name orderby columnname -将 top m 笔插入 set rowcount n select * from 表变量

16、 order by colu mnn ame desc 2. select top n * from (select top m * from table name order by colu mnn ame a order by colu mnn ame desc 3 口 果 table name 里没有其他 ide ntity 列，那 么： select ide ntity(i nt id0,* into #temp from table name 取 n 至U m 条的语句为： select * from #temp where id0 =n and id0 1 exec(sql ope

17、 n cur_rows fetch cur_rows into id,max while fetch_status=0 beg in select max = max -1 set rowco unt max select type = xtype from syscolu mns where id=object_id(t_ name and n ame=f_key if type=56 select sql = delete from +t_name+ where + f_key+ = + id if type=167 select sql = delete from +t_name+ wh

18、ere + f_key+ = + id + exec(sql fetch cur_rows into id,max end close cur_rows deallocate cur_rows set rowco unt 0 end select * from systypes select * from syscolu mns where id = object_id(a_dis查询数 据的最大排序问题(只能用一条语句写)CREATE TABLE hard (qu char (11 ,cochar (11 ,je numeric(3, 0 in sert into hard values (

19、A,1,3 in sert into hard values (A,2,4 insert into hard values (A,4,2 in sert into hard values (A,6,9 in sert into hard values (B,1,4 insert into hard values (B,2,5 insert into hard values (B,3,6 insert into hard values (C,3,4 in sert into hard values (C,6,7 in sert into hard values (C,2,要求查询 出来的结果如下

20、：qu co jeA 6 9 A 2 4 B 3 6 B 2 5 C 6 7 C 3 4就是要按qu分组，每组中取je最大的前2位！而且只能用一句sql语句！ ！ select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je 求删除重复记录的sql语句？怎样把具有相同字段的纪录删除，只留下一条。例如，表test里有id,name字段如果有name相同的记录只留下 一条，其余的删除。name的内容不定，相同的记录数不定。有没有这样的sql语句？ = A: 一个完整的解决方案：将重

21、复的记录记入 temp1 表：select 标志字段 id,count(* into temp1 from 表名group by 标志 字段 id having count(*1 2、将不重复的记录记入 temp1 表：insert temp1 select 标 志字段 id,count(* from 表名group by 标志字段 id having count(*=1 3、作一个包 含所有不重复记录的表：select * into temp2 from 表名where标志字段id in (select标志字段id from temp1 4、删除重复表：delete 表名5、恢复表：inse

22、rt 表名 select * from temp2 6、删除临时表:drop table temp1 drop table temp2B: create table a_dist(id in t, name varchar(20 in sert into a_dist values(1,abc insert into a_dist values(1,abc insert into a_dist values(1,abc in sert into a_dist values(1,abc exec up_disti net a_dist,id select * from a_dist create

23、 procedure up_disti nct(t_n ame varchar(30,f_key varchar(30 - f_key 表示是分组字段，即主键字段 as begin declare max integer,id varchar(30 ,sql varchar(7999 ,type in teger select sql = declare cur_rows cursor for select +f_key+ ,co un t(* from +t_ name + group by +f_key + hav ing coun t(* 1 exec(sql ope n cur_row

24、s fetch cur_rows into id,max while fetch_status=0 beg in select max = max -1 set rowco unt max select type = xtype from syscolu mns where id=object_id(t_ name and n ame=f_key if type=56 select sql = delete from +t_name+ where + f_key+ = + id if type=167 select sql = delete from +t_name+ where + f_ke

25、y+ = + id + exec(sql fetch cur_rows into id,max end close cur_rows deallocate cur_rows set rowco unt 0 end select * from systypes select * from syscolumns where id = object_id(a_dis行歹U转换-普通 彳假设有 张学生成绩表(CJ如下Name Subject Result张三语文80张三数学90张三物理85 李四语文85李四数学92李四物理82想变成姓名语文数学物理张三80 90 85 李四 85 92 82 decl

26、are sql varchar(4000 set sql = select Name select sql = sql + ,sum(case Subject when ”+Subject+” then Result end +Subject+ from (select distinet Subject from CJ as a select sql = sql+ from test group by n ame exec(s行歹U转 换-合并有表 A, id pid 1 1 1 2 1 3 2 1 2 2 3 1 如何化成表 B: id pid 1 1,2,3 2 1,2 3 1 仓U 建一

27、个合并的函数 create function fmerg(id int returns varchar(8000 as begin declare str varchar(8000 set str= select str=st叶,+cast(pid as varchar fro表 A where id=id set str=right(str,le n( str-1 return(str End go -调用自定义函数得到结果 select dist inct id,dbo.fmerg(idfrom表A如何取得一个数据表的所有列名 方法如下：先从SYSTEMOBJECT系统 表中取得数据表的S

28、YSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列 名。SQL 语句如下： declare objid in t, objn ame char(40 set objn ame =table name select objid = id from sysobjects where id = object_id( objn ame select Colu mn_n ame = n ame from syscolu mns where id = objid order by colic或 SELECT* FROM INFORMATION_SCHEMA.COLUMNS WHERE TAB

29、LE_NAME =users 通 过SQL语句来更改用户的密码 修改别人的，需要sysadmin role EXEC sp_password NULL, newpassword, User如果帐号为 SA 执行 EXEC sp_password NULL,n ewpassword, sa怎么判断出一个表的哪些字段不允许为空？selectCOLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=NO and TABLE_NAME=table name 如何在数据库里找到含有相同 字段的表？ a.查已知列名的情况 SELECT b.

30、name as TableName, as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type=U AND =你的字段名字未知列名查所有在不同表出现过的列名 Select o. name As table name,s1. name As colu mnn ame From syscolu mns s1, sysobjects o Where s1.id = o.id And o.type = U And Exists ( Select 1 From syscolumn

31、s s2 Where = And s1.id s2.i(查询第 xxx 行数据 假设 id 是主键：select * from (select top xxx * from yourtable aa where not exists(select 1 from (select top xxx-1* from yourtable bb where aa.id=bb.id 如果使用游标也是可以的 fetch absolutenumber from cursor_name行数为绝对行数 SQL Server日期计算a. 一个月的第一 天 SELECT DATEADD(

32、mm, DATEDIFF(mm,O,getdate(, 0 b.本周的星期一 SELECT DATEADD(wk, DATEDIFF(wk,O,getdate(, 0 c. 一年的第一天 SELECT DATEADD(yy, DATEDIFF(yy,O,getdate(, 0 d.季度的第一天 SELECT DATEADD(qq, DATEDIFF(qq,O,getdate(, 0 e.上个月的最后一天 SELECT dateadd(ms,- 3,DATEADD(mm, DATEDIFF(mm,O,getdate(, 0 f.去年的最后一天 SELECT dateadd(ms,-3,DATEA

33、DD(yy, DATEDIFF(yy,O,getdate(, 0 g.本月的最后一天 SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate(+1,0 h.本月的第一 个星期一 select DATEADD(wk, DATEDIFF(wk,O, dateadd(dd,6- datepart(day,getdate(,getdate( , 0 本年的最后一天 SELECT dateadd(ms,- 3,DATEADD(yy, DATEDIFF(yy,0,getdate(+1, 0。获取表结构把sysobjects替换 成 tablename即可

34、SELECT CASE IsNull(I.name, When Then Else * End as IsPK, Object_Name(A.id as t_name, A.name as c_name, IsNull(SubString(M.text, 1,254, aspbc_i nit, T.n ame as F_DataType, CASE lsNull(TYPEPROPERTY(T. name, Scale, ”WHEN The n Cast(A.precas varchar ELSE Cast(A.prec as varchar + , + Cast(A.scale as varchar END as F_Scale, A.is nullable as F_isNullAble FROM Syscolum ns as A JOIN Systypes as T ON (A.