SQL server×Ó²éѯ
exec xp_cmdshell 'md E:\project'
--ÏÈÅжÏÊý¾Ý¿âÊÇ·ñ´æÔÚÈç¹û´æÔÚ¾Íɾ³ý
if exists(select * from sysdatabases where name='bbsDB')
drop database bbsDB
--´´½¨Êý¾Ý¿âÎļþ
create database bbsDB
--Ö÷Êý¾Ý¿âÎļþ
on primary
(
name='bbsDB_data',--ΪÖ÷ÒªÊý¾Ý¿âÎļþÃüÃû
filename='E:\project\bbsDB_data.mdf',--Ö÷Êý¾Ý¿âÎļþµÄ·¾¶
size=10mb--³õʼ´óС
)
log on(
--ÈÕÖ¾Îļþ
name='bbsDB_log',
filename='E:\project\bbsDB_log.ldf',
size=3mb,
maxsize=20mb--×î´óÔö³¤Á¿Îª
)
go
use bbsDB
drop table BBSUsers
create table BBSUsers(
UID int identity(1,1) primary key not null,--±êʶÁÐ×ÔÔö³¤
UName varchar(15) not null,--Óû§Ãû£¬êdzÆ
UPassword varchar(16) not null,--ÃÜÂë²»ÄÜÉÙÓÚ6λÊýĬÈÏΪ£¨888888£©
USex bit not null,--ÐÔ±ð1´ú±íÄÐ
UEmail varchar(20) null, --µç×ÓÓʼþ±ØÐë°üº¬@ĬÈÏֵΪ£¨P@P.com£©
UClass int not null,--Óû§µÄµÈ¼¶(1)
UregDate datetime null,--×¢²áÈÕÆÚ£¨×¢²áʱϵͳʱ¼ä£©
Uremark varchar(255) null, --±¸×¢ÐÅÏ¢£¨±¸×¢£©
Upoint int not null,--Óû§µÄ»ý·Ö£¬µãÊý£¨20£©
UBirthday datetime null,--Óû§ÉúÈÕ
Ustate int null--״̬£¨0ĬÈÏΪÀëÏߣ©
)
go
select UPassword from BBSUsers
alter table BBSUsers-- ΪÃÜÂëÌí¼Ó¼ì²éÔ¼Êø³¤¶È´óÓÚ=6µÄ³¤¶È
add constraint CK_upassword check(len(UPassword)>=6)
alter table BBSUsers--ΪÃÜÂëÌí¼ÓĬÈÏÔ¼Êø(888888)
add constraint DE_upassword default ('888888') for UPassword
alter table BBSUsers--Email¼ì²éÔ¼Êø@
add constraint CK_uemail check (UEmail like '%@%')
alter table BBSUsers--ΪE-mailÌí¼ÓĬÈÏÔ¼ÊøP@P.com
add constraint DE_uemail default('P@P.co
Ïà¹ØÎĵµ£º
ʹÓÃ×Ô¶¨Òå±íÀàÐÍ£¨SQL Server 2008£©
http://tech.ddvip.com 2009Äê09ÔÂ19ÈÕ À´Ô´£º²©¿ÍÔ° ×÷Õߣº³ÂÏ£ÕÂ
¡¡¡¡ÔÚ SQL Server 2008 ÖУ¬Óû§¶¨Òå±íÀàÐÍÊÇÖ¸Óû§Ëù¶¨ÒåµÄ±íʾ±í½á¹¹¶¨ÒåµÄÀàÐÍ¡£Äú¿ÉÒÔʹÓÃÓû§¶¨Òå±íÀàÐÍΪ´æ´¢¹ý³Ì»òº¯ÊýÉùÃ÷±íÖµ²ÎÊý£¬»òÕßÉùÃ÷ÄúÒªÔÚ ......
Ò»£®¸ÅÊö
´æ´¢¹ý³ÌÊÇÒ»×éÔ¤ÏȱàÒëºÃµÄT--SQL´úÂ룬ǿµ÷Ò»µã£ºÕâЩ´úÂëÊÇÔ¤±àÒëºÃµÄ£¬¼´ÔÚÖ´ÐÐʱ¾Í²»ÓÃÔÚ½øÐбàÒëÁË£¬ÕâÑù»áÌá¸ß³ÌÐòµÄÔËÐÐЧÂÊ¡£ËüÀàËÆÓÚÆäËû¸ß¼¶ÓïÑÔÀïµÄ¹ý³Ì£¬¼´Íê³ÉÌØ¶¨¹¦Äܵĺ¯Êý¡£´æ´¢¹ý³Ì·ÖÁ½ÀࣺϵͳÌṩµÄ´æ´¢¹ý³Ì£¬Óû§×Ô¼º¶¨ÒåµÄ´æ´¢¹ý³Ì£¬ÏµÍ³¶¨ÒåµÄÒÔ sp_Ϊǰ׺¡£
¶ ......
sql unionÓ÷¨ºÍsql union allÓ÷¨,sql unionЧÂÊ
1¡¢sql unionÓ÷¨
sql unionÔÚ²éѯÖпÉÒÔ½«Á½¸öSQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£Èç¹ûÕâÑù¿´µÄ»°£¬ UNION ¸ú JOIN ÊÇÏàËÆµÄ£¬Á½¸öÖ¸Áî¶¼¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£
sql unionµÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬Î ......
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
select * from ±íÃû
Èç¹ûÊÇÉú³Éexcel時ÓÃbcp
--µ¼³ö²éѯµÄÇé¿ö
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname from pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c -/S"·þÎ ......