Êý¾Ý²É¼¯Öг£ÓõÄSQLÓï¾ä¼°¼¼ÇÉ
Êý¾Ý²É¼¯Öг£ÓõÄSQLÓï¾ä
ÏàͬµÄSQLÓï¾äÔËÓõ½²»Í¬Êý¾Ý¿âÖлáÓÐÂÔ΢µÄ²î±ð£¬¶Ô×Ö·û±äÁ¿µÄÒªÇó£¬Ïà¹Øº¯ÊýµÄ±ä»¯£¬ÒÔ¼°Óï·¨¹æÔòµÄ²»Í¬µÈµÈ£¬ÀýÈ磺oracleÊý¾Ý¿âÖжÔ×Ö¶ÎÃüÃû±ðÃûʱ²»ÐèÒªas ×Ö·û£¬Ã»ÓÐmonth()£¬year£¨£©µÈʱ¼äº¯ÊýµÈµÈ£¬accessÊý¾Ý¿âÖÐÔÚʹÓÃinner joinÖ´ÐÐÄÚ²¿ÁªºÏʱÌõ¼þÐèÓ㨣©£¬µ±È»»¹ÓкܶàµÄϸ΢²î±ð£¬´ó¼Ò¿ÉÒÔ×Ô¼ºÈ¥Ñ°ÕÒ×ܽᡣÏÂÃæµÄʾÀýÒÔSQL SERVERΪ»ù´¡±àд¡£
1. ³éÈ¡·ÇÖظ´Êý¾Ý
select distinct var1 from tableName;
2. ³éȡij¸öʱ¼ä¶Î¼äµÄÊý¾Ý
select var1,var2 from Êý¾Ý±í where ×Ö¶ÎÃû between ʱ¼ä1 and ʱ¼ä2;
3. Á¬½Ó¶à¸ö±äÁ¿
select '123'+cast(456 as varchar);
select '123'+cast(456 as varchar)+'789';
4. ÓÃSQLÓï¾äÕÒ³ö±íÃûΪTable1ÖеĴ¦ÔÚID×Ö¶ÎÖÐ1-200Ìõ¼Ç¼ÖÐName×ֶΰüº¬wµÄËùÓмǼ
select * from Table1 where id between 1 and 200 and Name like '%w%';
5. ÕÒ³öÓµÓг¬¹ý10Ãû¿Í»§µÄµØÇøµÄÁбí
select country from test group by country having count(customerId)>10;
6. ¹ØÓÚÈ¡³öÿ¸ö²¿ÃŹ¤×Ê×î¸ßµÄÇ°ÈýÈË
select * from table t where ¹¤×Ê in (select top 3 ¹¤×Ê from table where ²¿ÃÅ = t.²¿ÃÅ order by ¹¤×Ê desc);
7. Á½¸ö½á¹¹ÍêÈ«ÏàͬµÄ±íaºÍb£¬Ö÷¼üΪindex£¬Ê¹ÓÃSQLÓï¾ä£¬°Ña±íÖдæÔÚµ«ÔÚb±íÖв»´æÔÚµÄÊý¾Ý²åÈëµÄb±íÖÐ
insert into b select * from a where not exists(select * from b where "index"=a."index");
8.´ÓÒ»¸öÊý¾Ý¿âÖеĶà¸öÊý¾Ý±íÌáÈ¡Ïà¹Ø±äÁ¿
Select table1.var1,table2.var2,table2.var3,
from table1 inner join table2
On tabel1.var1=table2.var1
Inner join table3
On tabel1.var2=table3.var2
(order by ……)
SQL²éѯÏà¹ØС¼¼ÇÉ
·Ê¹ÓÃANDʱ£¬½«²»ÎªÕæµÄÌõ¼þ·ÅÔÚÇ°Ãæ
Êý¾Ý¿âϵͳ×ñÑÔËËã·ûµÄÓÅÏȼ¶£¬²¢ÇÒÔËËã¹ý³ÌÊÇ´Ó×óÖÁÓҵģ¬½«Ìõ¼þ²»ÎªÕæµÄ·ÅÔÚÇ°Ã棬ÔòÄܹ»Ê¡È¥andºóÃæµÄÏà¹ØÔËË㣬ÒÔ´ïµ½¼õÉÙÊý¾Ý¿âϵ
Ïà¹ØÎĵµ£º
SQLÖÐobject_idº¯ÊýµÄÓ÷¨ ÊÕ²Ø
int object_id('objectname');
´Ë·½·¨·µ»ØÊý¾Ý¿â¶ÔÏó±êʶºÅ¡£
ÆäÖУ¬²ÎÊýobjectname ±íʾҪʹÓõĶÔÏó£¬ÆäÊý¾ÝÀàÐÍΪnchar»òchar£¨Èç¹ûΪchar£¬ÏµÍ³½«Æäת»»Îªnchar£©
·µ»ØÀàÐÍΪint£¬±íʾ¸Ã¶ÔÏóÔÚϵͳÖеıàºÅ¡£
±ÈÈ磺
use wf_timesheet
select object_id('usp_check_excess ......
ÔÚƽʱµÄ¹¤×÷¹ý³ÌÖУ¬×÷ΪDBA½ÇÉ«¹ÜÀíÊý¾Ý¿â£¬Í·ÄÔÖеÄÓ¡ÏóÍùÍùÊÇÊý¾Ý¿âʵÀýÃû³Æ£¬¶ø²»»áÈ¥¹ØÐÄServerµÄIP£¬¶ø×÷ΪDeveloperµÄ½ÇÉ«£¬ËûÃÇÍùÍùÏëÖªµÀÖªµÀServer IpºÍ¶Ë¿ÚºÅ¡£ËùÒÔ£¬DBA»á¾³£±»Îʼ°µ½£ºXXXʵÀýµÄIPºÍ¶Ë¿ÚºÅÊÇʲô£¿
Õâ¸öÎÊÌ⣬µ±È»ÎÒÃÇ¿ÉÒÔLoginµ½OS²é¿´IP¡¢Ê¹ÓÃÅäÖƹÜÀí¹¤¾ß»ñÈ¡µ½¶Ë¿ÚºÅ¡£µ«ÊÇ£¬Õâ¸ö·½·¨·Ç ......
select sql_text, spid, v$session.program, process
from v$sqltext, v$session, v$process
where v$sqltext.address = v$session.sql_address
and v$sqltext.hash_value = v$session.sql_hash_value
and v$session.paddr = v$process.addr
and v$process.spid in (4335);
×¢Ò ......
ʹÓÃTranact-SQL ±àд´úÂëÀ´´´½¨Ò»¸öÐÂ±í£º
USE [OnlineJudge]
GO
/****** Object: Table [dbo].[User1] Script Date: 05/17/2010 14:05:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User2](/*notice convert the tablename*/
[num] [int]IDENTITY(1,1) NOT N ......
ÉùÃ÷×Ö¶ÎÓ³Éä
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface FiledRef
{
String fieldName();
}
ÉùÃ÷±íÓ³Éä
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface TableRef
{
& ......