sql ·þÎñÆ÷½ÇÉ« Êý¾Ý¿â½ÇÉ«
sql ·þÎñÆ÷½ÇÉ« Êý¾Ý¿â½ÇÉ«
·þÎñÆ÷½ÇÉ«
ËùÓеķþÎñÆ÷½ÇÉ«¶¼ÊÇ“¹Ì¶¨µÄ”½ÇÉ«£¬²¢ÇÒ£¬´ÓÒ»¿ªÊ¼¾Í´æÔÚÓÚÄÇÀï——×Ô°²×°ÍêSQL ServerµÄÄÇÒ»¿ÌÆð£¬Ä㽫ӵÓеÄËùÓзþÎñÆ÷½ÇÉ«¾ÍÒѾ´æÔÚÁË¡£
½Ç¡¡¡¡É«
ÌØ¡¡¡¡ÐÔ
sysadmin
¸Ã½ÇÉ«Äܹ»Ö´ÐÐSQL ServerÉϵÄÈκβÙ×÷¡£±¾ÖÊÉÏ£¬ÈκξßÓÐÕâÖÖ½ÇÉ«³ÉÔ±Éí·ÝµÄÈ˶¼ÊÇÄǸö·þÎñÆ÷ÉϵÄsa¡£ÕâÖÖ·þÎñÆ÷½ÇÉ«µÄ´´½¨ÎªÎ¢ÈíÌṩÁËijһÌìÈ¥³ýsaµÇ¼µÄÄÜÁ¦——ʵ¼ÊÉÏ£¬Áª»ú´ÔÊé°Ñsa³Æ×÷±¾ÖÊÉÏΪÒÅÁôÎïµÄ¶«Î÷
Öµ
µÃ×¢ÒâµÄÊÇ£¬ÔÚSQL
ServerÉÏ£¬WindowsµÄAdministrators×é±»×Ô¶¯Ó³Éäµ½sysadmin½ÇÉ«ÖС£ÕâÒâζ×Å·þÎñÆ÷µÄAdministrators×é
ÖеÄÈκγÉԱͬʱҲ¾ßÓжÔSQLÊý¾ÝµÄsa¼¶±ðµÄ·ÃÎÊȨÏÞ¡£Èç¹ûÐèÒª£¬Äã¿ÉÒÔ´Ósysadmin½ÇÉ«ÖÐɾ³ýWindowsµÄadministrators
×飬ÒÔÌá¸ß°²È«ÐÔ¡¢·À·¶Â©¶´
serveradmin
¸Ã½ÇÉ«ÄÜÉèÖ÷þÎñÆ÷·¶Î§µÄÅäÖÃÑ¡Ïî»ò¹Ø±Õ·þÎñÆ÷¡£¾¡¹ÜËüÔÚ·¶Î§ÉÏÏ൱ÓÐÏÞ£¬µ«ÊÇ£¬ÓɸýÇÉ«µÄ³ÉÔ±Ëù¿ØÖƵŦÄܶÔÓÚ·þÎñÆ÷µÄÐÔÄÜ»á²úÉú·Ç³£ÖØ´óµÄÓ°Ïì
setupadmin
¸Ã½ÇÉ«½öÏÞÓÚ¹ÜÀíÁ´½Ó·þÎñÆ÷ºÍÆô¶¯¹ý³Ì
securityadmin
¶ÔÓÚרÃÅ´´½¨³öÀ´ÓÃÓÚ¹ÜÀíµÇ¼Ãû¡¢¶ÁÈ¡´íÎóÈÕÖ¾ºÍ´´½¨Êý¾Ý¿âÐí¿ÉȨÏ޵ĵǼÃûÀ´Ëµ£¬¸Ã½ÇÉ«·Ç³£±ãÀû¡£Ôںܶ෽Ã棬¸Ã½ÇÉ«ÊǵäÐ͵Äϵͳ²Ù×÷Ô±½ÇÉ«——ËüÄܹ»´¦Àí¶àÊýµÄÈÕ³£ÊÂÎñ£¬µ«ÊÇ£¬È´²»¾ß±¸Ò»¸öÕæÕýÎÞËù²»Äܵij¬¼¶Óû§ËùÓµÓеÄÄÇÖÖÈ«¾Ö·ÃÎÊ
processadmin
Äܹ»¹ÜÀíSQL ServerÖÐÔËÐеĽø³Ì——±ØÒªµÄ»°£¬¸Ã½ÇÉ«Äܹ»ÖÕÖ¹³¤Ê±¼äÔËÐеĽø³Ì
dbcreator
¸Ã½ÇÉ«½öÏÞÓÚ´´½¨ºÍ¸ü¸ÄÊý¾Ý¿â
diskadmin
¹ÜÀí´ÅÅÌÎļþ£¨Ö¸ÅɸøÁËʲôÎļþ×é¡¢¸½¼ÓºÍ·ÖÀëÊý¾Ý¿â£¬µÈµÈ£©
bulkadmin
¸Ã
½ÇÉ«ÓÐЩ¹ÖÒì¡£Ëü±»Ã÷È·´´½¨³öÀ´£¬ÓÃÓÚÖ´ÐÐBULK INSERTÓï¾äµÄȨÏÞ£¬·ñÔòµÄ»°£¬Ö»ÄÜÓɾßÓÐsysadminȨÏÞµÄÈËÀ´Ö´ÐÐBULK
INSERTÓï¾ä¡£Ì¹°×µØ˵£¬ÎÒ²»Ã÷°×Ϊʲô¸ÃÓï¾ä²»ÄÜÏñÆäËûÊÂÇéÄÇÑùͨ¹ýGRANTÃüÁîÀ´ÊÚÓèȨÏÞ£¬µ«ËüµÄȷûÓС£Òª¼Çס£¬¼´Ê¹°ÑÒ»¸öÓû§¼ÓÈëµ½ÁË
bulkadmin×éÖУ¬Ò²Ö»ÊǸøÁËËûÃÇ·ÃÎÊÄǸöÓï¾äµÄȨÏÞ£¬¶ÔÓÚÔËÐиÃÓï¾äµÄ±í£¬²¢Ã»ÓÐÊÚÓèÓû§·ÃÎÊÄǸö±íµÄȨÏÞ¡£ÕâÒâζ×Ų»½öÐèÒª°ÑÓû§Ìí¼Óµ½
bulkadminÖУ¬¶øÇÒ£¬¶ÔÓÚÏëÒªÓû§ÄÜÔÚÆäÉÏÖ´ÐÐBULK
INSERTµÄ±í£¬»¹ÒªÊÚÓ裨GRANT£©Óû§INSERTÐí¿ÉȨÏÞ¡£´ËÍ⣬¶ÔÓÚ½«ÔÚBULK
INSERTÓï¾äÖÐÒýÓõÄËùÓÐ±í£¬»¹ÒªÈ·±£Óû§ÓµÓÐÕýÈ·µÄµ½ÄÇЩ±íµÄSELECT·ÃÎÊȨÏÞ
¶ÔÓÚÔÚ·þÎñÆ÷Éϳе£¹
Ïà¹ØÎĵµ£º
1 ±ÜÃâÎ޼ƻ®µÄÈ«±íɨÃè
ÈçÏÂÇé¿ö½øÐÐÈ«±íɨÃ裺
- ¸Ã±íÎÞË÷Òý
- ¶Ô·µ»ØµÄÐÐÎÞÈ˺ÍÏÞÖÆÌõ¼þ£¨ÎÞWhere×Ӿ䣩
- ¶ÔÓÚË÷ÒýÖ÷ÁУ¨Ë÷ÒýµÄµÚÒ» ......
ʹÓà CONVERT£º
CONVERT (data_type[(length)], expression [, style])
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 ) ......
--3.3.1 ʹÓÃÓα귨½øÐÐ×Ö·û´®ºÏ²¢´¦ÀíµÄʾÀý¡£
--´¦ÀíµÄÊý¾Ý
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--ºÏ²¢´¦Àí
--¶¨Òå½á¹û¼¯±í±äÁ¿
DECLARE @t TABLE(col1 varchar(10),col2 varch ......
Use DatabaseName
--DB shrink
--»ñÈ¡database ¿ÕÓà¿Õ¼ä£¬¡¡¾ö¶¨ÊÇ·ñ×÷shrinkDB
exec [DBNAME].dbo.sp_spaceused
DBCC ShrinkDB(DBNAME)
--Log file shrink
Use DatabaseName
GO
Alter Database DatabaseName Set Recovery Simple
GO
Alter Database DatabaseName Set Recovery Full
GO
DBCC SHRINKFILE ('Log ......
ͬһ±í¶à×Ö¶ÎͬʱÖظ´¼Ç¼µÄSQL²éѯ¼°´¦ÀíÊý
±ÈÈçÏÖÔÚÓÐÒ»ÈËÔ±±í £¨±íÃû£ºpeosons£©
ÈôÏ뽫ÐÕÃû¡¢Éí·ÝÖ¤ºÅ¡¢×¡Ö·ÕâÈý¸ö×Ö¶ÎÍêÈ«ÏàͬµÄ¼Ç¼²éѯ³öÀ´
select p1.* from persons p1,persons p2 where p1.idp2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address ......