Oracle命令行工具基本操作及SQL 命令

经典的SQL语句

1. 行列转换--普通

假设有张学生成绩表(CJ)如下
Name    Subject     Result
张三    语文        80
张三    数学        90
张三    物理        85
李四    语文        85
李四    数学        92
李四    物理        82

想变成    
姓名   语文   数学   物理
张三   80     90     85
李四   85     92     82

declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
 from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)

2. 行列转换--合并

有表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

创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A

3. 如何取得一个数据表的所有列名

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid

是不是太简单了？ 呵呵 不过经常用阿.

4. 通过SQL语句来更改用户的密码

修改别人的,需要sysadmin  role    
EXEC  sp_password  NULL,  'newpassword',  'User'

如果帐号为SA执行EXEC  sp_password  NULL,  'newpassword',  sa 

5. 怎么判断出一个表的哪些字段不允许为空？

select  COLUMN_NAME  from  INFORMATION_SCHEMA.COLUMNS  where  IS_NULLABLE='NO'  and  TABLE_NAME=tablename 

6. 如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
SELECT  b.name  as  TableName,a.name  as  columnname  
From  syscolumns    a  INNER  JOIN    sysobjects  b    
ON  a.id=b.id    
AND  b.type='U'    
AND  a.name='你的字段名字' 

b. 未知列名查所有在不同表出现过的列名
Select  o.name  As  tablename,s1.name  As  columnname  
From  syscolumns  s1,  sysobjects  o  
Where  s1.id  =  o.id  
   And  o.type  =  'U'  
   And  Exists  (  
       Select  1  From  syscolumns  s2    
       Where  s1.name  =  s2.name    
       And  s1.id  <>  s2.id  
       )

7. 查询第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  absolute  [number]  from  [cursor_name]  
行数为绝对行数

8. SQL Server日期计算
a. 一个月的第一天
SELECT  DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0)  
b. 本周的星期一
SELECT  DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0) 
c. 一年的第一天
SELECT  DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0)  
d. 季度的第一天
SELECT  DATEADD(qq,  DATEDIFF(qq,0,getdate()),  0)  
e. 上个月的最后一天 
SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0))  
f. 去年的最后一天
SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0)) 
g. 本月的最后一天
SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))  
h. 本月的第一个星期一
select  DATEADD(wk,  DATEDIFF(wk,0,                                                          
                               dateadd(dd,6-datepart(day,getdate()),getdate())        
                                                                                                 ),  0)      
i. 本年的最后一天
SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate())+1,  0))。

1. 说明：复制表(只复制结构，源表名：a，新表名：b)

SQL: select * into b from a where 1<>1;

2. 说明：拷贝表(拷贝数据，源表名：a，目标表名：b) 

SQL: insert into b(a, b, c) select d, e, f from b;

3. 说明：显示文章、提交人和最后回复时间

SQL: select a.title, a.username, b.adddate 
     from table a,(
          select max(adddate) adddate 
          from table where table.title=a.title) b 　

4. 说明：外连接查询(表名1：a，表名2：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f 
     from a LEFT OUT JOIN b ON a.a = b.c; 　

5. 说明：日程安排提前五分钟提醒

SQL: select * 
     from 日程安排 
     where datediff(''minute'', f开始时间, getdate())>5 　　
　　

6. 说明：两张关联表，删除主表中已经在副表中没有的信息

SQL: delete from info 
     where not exists(
           select * 
           from infobz 
           where info.infid=infobz.infid );

7. 说明：——

SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE 
     FROM TABLE1,(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, ''YYYY/MM'') 
                              || ''/01'',''YYYY/MM/DD'') - 1, ''YYYY/MM'') ) Y, 
                   WHERE X.NUM = Y.NUM （+）AND X.INBOUND_QTY 
                                 + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B 
                   WHERE A.NUM = B.NUM;　
8. 说明：——

SQL: select * 
     from studentinfo 
     where not exists(select * from student where studentinfo.id=student.id) 
           and 系名称=''"&strdepartmentname&"''
           and 专业名称=''"&strprofessionname&"''