SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- ¿ªÊ¼ ±¸·Ý
BACKUP DATABASE pubs TO testBack
4¡¢ËµÃ÷£º´´½¨Ð±í
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
¸ù¾ÝÒÑÓÐµÄ±í´´½¨ÐÂ±í£º
A£ºcreate table tab_new like tab_old (ʹÓÃ¾É±í´´½¨Ð±í)
B£ºcreate table tab_new as select col1,col2… from tab_old definition only
5¡¢ËµÃ÷£ºÉ¾³ýбídrop table tabname
6¡¢ËµÃ÷£ºÔö¼ÓÒ»¸öÁÐ
Alter table tabname add column col type
×¢£ºÁÐÔö¼Óºó½«²»ÄÜɾ³ý¡£DB2ÖÐÁмÓÉϺóÊý¾ÝÀàÐÍÒ²²»ÄÜ¸Ä±ä£¬Î¨Ò»Ä ......
ÓÃselectÓï¾ä£¬²éѯÖظ´¼Ç¼
¼ÙÉ裬±íÃûΪ T1 ×Ó¶ÎΪ A,B,C
select count(*) ,A,B,C from T1
group by A,B,C having count(*) > 1
²âÊÔÊý¾Ý£º
A100 B100 C100
A101 B101 C101
A102 B102 C102
A102 B102 C100
A102 B102 C102 &n ......
Ìá¸ßÊý¾Ý¿âÐÔÄܵķ½Ê½ÓÐÁ½ÖÖ
Ò»¡¢Ò»ÖÖÊÇDBAͨ¹ý¶ÔÊý¾Ý¿âµÄ¸÷¸ö·½Ãæµ÷ÓÅ
µ÷ÕûÊý¾Ý¿â:¹²Ïí³Ø,java³Ø,¸ßËÙ»º´æ,´óÐͳØ,java³Ø
Õë¶ÔÓÚwindow²Ù×÷ϵͳ 32λ,oracleÄÚ´æÕ¼Óã¬×î´óΪ1.7G,³¬¹ýÔò²»×÷ÓÃ,Òò´ËÕ⼸ÏîÖµÖ®ºÍ²»Ó¦³¬¹ý1.7G
Ä¿Ç°¸÷³Ø²ÎÊýΪ:
¹²Ïí³Ø:512MB
¸ßËÙ»º´æ:904MB
´óÐͳØ:64MB
java³Ø:40MB
PGA:312MB
¶þ¡¢¶ÔsqlÓï¾äµÄÓÅ»¯
1 ×ܸÙ
l ½¨Á¢±ØÒªµÄË÷Òý
Õâ´Î´«ÊڵĽµÁúÊ®°ËÕÆ£¬×ܸÙÖ»ÓÐÒ»¾ä»°£º½¨Á¢±ØÒªµÄË÷Òý£¬Õâ¾ÍÊǺóÃæ½µÁúÊ®°ËÕƵÄÄÚ¹¦»ù´¡¡£ÕâÒ»µã¿´ËÆÈÝÒ×ʵ¼ÊÈ´ºÜÄÑ¡£ÄѾÍÄÑÔÚÈçºÎÅжÏÄÄЩË÷ÒýÊDZØÒªµÄ£¬ÄÄЩÓÖÊDz»±ØÒªµÄ¡£ÅжϵÄ×îÖÕ±ê×¼ÊÇ¿´ÕâЩË÷ÒýÊÇ·ñ¶ÔÎÒÃǵÄÊý¾Ý¿âÐÔÄÜÓÐËù°ïÖú¡£¾ßÌåµ½·½·¨ÉÏ£¬¾Í±ØÐëÊìϤÊý¾Ý¿âÓ¦ÓóÌÐòÖеÄËùÓÐSQLÓï¾ä£¬´ÓÖÐÍ ......
SQL Server 2005 Êý¾ÝÀàÐÍ
´´½¨Êý¾Ý¿â±íʱ£¬±ØÐëΪ±íÖеÄÿÁзÖÅäÒ»ÖÖÊý¾ÝÀàÐÍ¡£
1. ×Ö·û´®ÀàÐÍ
×Ö·û´®ÀàÐÍ°üÀ¨varchar,char,nvarchar,nchar,textºÍntext.ÕâЩÊý¾ÝÀàÐÍÓÃÓÚ´æ´¢×Ö·ûÊý¾Ý¡£VarcharºÍcharÀàÐÍÖ®¼äµÄ²î±ðÊÇÊý¾ÝÌî³ä¡£Èç¹ûÒª½ÚÊ¡¿Õ¼ä£¬ÎªÊ²Ã´ÓÐʱºò»¹Ê¹ÓÃcharÊý¾ÝÀàÐÍÄØ£¿Ê¹ÓÃvarcharÀàÐͽ«ÉÔ΢Ôö¼ÓһЩ¿ªÏú¡£ÓÐЩDBAÈÏΪ£¬Ó¦×î´ó¿ÉÄܵؽÚÊ¡¿Õ¼ä¡£µ«Ò»°ãÀ´Ëµ£¬×îºÃÔÚµ¥Î»ÕÒµ½Ò»¸öºÏÊʵÄãÐÖµ£¬µÍÓÚ¸ÄÖµµÄ²ÉÓÃcharÊý¾ÝÀàÐÍ£¬·´Ö®²ÉÓÃvarcharÊý¾ÝÀàÐÍ¡£
NvarcharÊý¾ÝÀàÐͺÍncharÊý¾ÝÀàÐ͵Ť×÷ÔÀíÓë½ãÃÃÊý¾ÝÀàÐÍvarcharÊý¾ÝÀàÐͺÍcharÊý¾ÝÀàÐÍ£¬µ«ÕâÁ½ÖÖÊý¾ÝÀàÐÍÄܹ»´¦Àí¹ú¼ÊÐÔUnicode×Ö·û£¬²»¹ýÐèҪһЩ¶îÍ⿪Ïú¡£¼øÓÚÕâЩ¶îÍ⿪ÏúºÍ¿Õ¼ä£¬ËùÒÔÓ¦¸Ã¿ÉÄܱÜÃâʹÓÃUnicodeÁУ¬³ý·Çȷʵ´æÔÚÐèҪʹÓÃËüÃǵÄÒµÎñ»òÓïÑÔÐèÇó¡£
TextÊý¾ÝÀàÐÍÓÃÓÚ´æ´¢´óÐÍÊý¾Ý¡£Ó¦¸Ã¿ÉÄÜÉÙµÄʹÓÃËüÃÇ£¬ÒòΪËûÃÇ¿ÉÄÜÓ°ÏìÐÔÄÜ¡£
2. ÊýÖµÊý¾ÝÀàÐÍ
ÊýÖµÊý¾ÝÀàÐÍ°üÀ¨bit,tinyint,smallint,int,bigint,numberic,decimal,money,floatºÍreal¡£ËùÓÐÕâЩÊý¾ÝÀàÐͶ¼ÓÃÓÚ´æ´¢²»Í¬ÀàÐ͵ÄÊý×ÖÖµ¡£
³£¼ûµÄÊýÖµÊý¾ÝÀàÐÍ
Êý ......
±ístuinfo£¬ÓÐÈý¸ö×Ö¶Îrecno(×ÔÔö),stuid,stuname
½¨¸Ã±íµÄSqlÓï¾äÈçÏ£º
CREATE TABLE [StuInfo] (
[recno] [int] IDENTITY (1, 1) NOT NULL ,
[stuid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[stuname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
1.--²éijһÁÐ(»ò¶àÁÐ)µÄÖظ´Öµ(Ö»Äܲé³öÖظ´¼Ç¼µÄÖµ£¬²»ÄÜÕû¸ö¼Ç¼µÄÐÅÏ¢)
--Èç:²éÕÒstuid,stunameÖظ´µÄ¼Ç¼
select stuid,stuname from stuinfo
group by stuid,stuname
having(count(*))>1
2.--²éijһÁÐÓÐÖظ´ÖµµÄ¼Ç¼(ÕâÖÖ·½·¨²é³öµÄÊÇËùÓÐÖظ´µÄ¼Ç¼,Ò²¾ÍÊÇ˵Èç¹ûÓÐÁ½Ìõ¼Ç¼Öظ´µÄ£¬¾Í²é³öÁ½Ìõ)
--Èç:²éÕÒstuidÖظ´µÄ¼Ç¼
select * from stuinfo
where stuid in (
select stuid from stuinfo
group by stuid
having(count(*))>1
)
3.--²éijһÁÐÓÐÖظ´ÖµµÄ¼Ç¼(Ö»ÏÔʾ¶àÓàµÄ¼Ç¼,Ò²¾ÍÊÇ˵Èç¹ûÓÐÈýÌõ¼Ç¼Öظ´µÄ£¬¾ÍÏÔʾÁ½Ìõ)
--ÕâÖÖ·½³É¼¨µÄÇ°ÌáÊÇ£ºÐèÓÐÒ»¸ö²»Öظ´µÄÁÐ,±¾ÀýÖеÄÊÇrecno
--Èç:²éÕÒstuidÖظ´µÄ¼Ç¼
select * from stuinfo s1
where recno not in (
select max(recno) from stuinfo s2
where s1.stui ......
/// <summary>
/// ·µ»Ø·ÖÒ³SQLÓï¾ä
/// </summary>
/// <param name="selectSql">²éѯSQLÓï¾ä</param>
/// <param name="PageIndex">µ±Ç°Ò³Âë</param>
/// <param name="PageSize">Ò»Ò³¶àÉÙÌõ¼Ç¼</param>
/// <returns></returns>
public static string getPageSplitSQL(string selectSql, int PageIndex, int PageSize)
{
string StartSelectSql = @" select * from (select aa.*, rownum r from (";
int CurrentReadRows = PageIndex * PageSize;
&n ......
/// <summary>
/// ·µ»Ø·ÖÒ³SQLÓï¾ä
/// </summary>
/// <param name="selectSql">²éѯSQLÓï¾ä</param>
/// <param name="PageIndex">µ±Ç°Ò³Âë</param>
/// <param name="PageSize">Ò»Ò³¶àÉÙÌõ¼Ç¼</param>
/// <returns></returns>
public static string getPageSplitSQL(string selectSql, int PageIndex, int PageSize)
{
string StartSelectSql = @" select * from (select aa.*, rownum r from (";
int CurrentReadRows = PageIndex * PageSize;
&n ......