SQL Pivot & UnPivot
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('ÕÅÈý','ÓïÎÄ',20)
insert into students values ('ÕÅÈý','Êýѧ',90)
insert into students values ('ÕÅÈý','Ó¢Óï',50)
insert into students values ('ÀîËÄ','ÓïÎÄ',81)
insert into students values ('ÀîËÄ','Êýѧ',60)
insert into students values ('ÀîËÄ','Ó¢Óï',90)
select * from students
pivot(
max(grade)
FOR [class] IN ([ÓïÎÄ],[Êýѧ],[Ó¢Óï])
) AS pvt
/*
ÀîËÄ 81 60 90
ÕÅÈý 20 90 50
*/
--=========================================================================
create table students (
name varchar(25),
ÓïÎÄ int,
Êýѧ int,
Ó¢Óï int
)
GO
INSERT INTO students values ('ÀîËÄ',81,60,90)
INSERT INTO students values ('ÕÅÈý',20,90,50)
select *
from
students
unpivot
(
grade
for class in
([ÓïÎÄ],[Êýѧ],[Ó¢Óï])
) AS upvt
/*
ÀîËÄ 81 ÓïÎÄ
ÀîËÄ 60 Êýѧ
ÀîËÄ 90 Ó¢Óï
ÕÅÈý 20 ÓïÎÄ
ÕÅÈý 90 Êýѧ
ÕÅÈý 50 Ó¢Óï
*/
Ïà¹ØÎĵµ£º
http://www.cnblogs.com/dwjaissk/archive/2006/07/25/459476.aspx
Ôö¼Ó×Ö¶Î
alter table docdsp add dspcode char(200)
ɾ³ý×Ö¶Î
ALTER TABLE
table_NAME DROP COLUMN column_NAME
ÐÞ¸Ä×Ö¶ÎÀàÐÍ
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type
¸ÄÃû
sp_rename
¸ü¸Äµ±Ç°Êý¾Ý¿âÖÐ ......
¡¡²Ù×÷·ûÓÅ»¯
¡¡¡¡IN ²Ù×÷·û
¡¡¡¡ÓÃINд³öÀ´µÄSQLµÄÓŵãÊDZȽÏÈÝÒ×д¼°ÇåÎúÒ×¶®£¬Õâ±È½ÏÊʺÏÏÖ´úÈí¼þ¿ª·¢µÄ·ç¸ñ¡£
¡¡¡¡µ«ÊÇÓÃINµÄSQLÐÔÄÜ×ÜÊDZȽϵ͵쬴ÓORACLEÖ´ÐеIJ½ÖèÀ´·ÖÎöÓÃINµÄSQLÓë²»ÓÃINµÄSQLÓÐÒÔÏÂÇø±ð£º
¡¡¡¡ORACLEÊÔͼ½«Æäת»»³É¶à¸ö±íµÄÁ¬½Ó£¬Èç¹ûת»»²»³É¹¦ÔòÏÈÖ´ÐÐINÀïÃæµÄ×Ó²éѯ£¬ÔÙ²éѯÍâ²ãµÄ±í¼Ç¼£ ......
ÔÚwindows XP SP3 ÖÐÊÇ¿ÉÒÔ°²×° SQL Server 2005 µÄ²»»áÓмæÈÝÎÊÌâ ÎÒÔÚ°²×°µÄʱºò¾ÍʲôÎÊÌⶼû¿´µ½ °²×°ºÃºó¸½¼ÓÒ»¸öÊý¾Ý¿â ÓÃBorland JBuilder 2006 Enterprise ºÍeclipse ҲûÎÊÌâ
SQL2005 ·ÖÎå¸ö°æ±¾£¬ÈçÏÂËùÁУ¬
¡¡¡¡1.Enterprise(ÆóÒµ°æ),
2.Development(¿ª·¢°æ),
¡¡¡¡3.Workgroup,(¹¤×÷Ⱥ°æ)
¡¡¡¡4.St ......
SQL like×Ó¾äµÄÁíÒ»ÖÖʵÏÖ·½·¨,ËٶȱÈlike¿ì(ת)
Ò»°ãÀ´ËµÊ¹ÓÃÄ£ºý²éѯ£¬´ó¼Ò¶¼»áÏëµ½LIKE
select * from table where a like '%×Ö·û%'
Èç¹ûÒ»¸öSQLÓï¾äÖÐÓöà¸ö likeÄ£ºý²éѯ£¬²¢ÇҼǼÌõÊýºÜ´ó£¬ÄÇËÙ¶ÈÒ»¶¨»áºÜÂý¡£
ÏÂÃæÁ½ÖÖ·½·¨Ò²¿ÉʵÏÖÄ£ºý²éѯ£º
select * from table where patindex('%×Ö·û%',a)>0 ......
declare @SchemaName nvarchar(50)
set @SchemaName='dbo'
declare @CommandName nvarchar(50)
set @CommandName='spName'
SELECT
DB_NAME() AS [PROCEDURE_CATALOG],
  ......