sql´æ´¢¹ý³ÌÓï·¨
Ò».×¢ÊÍ
-- µ¥ÐÐ×¢ÊÍ£¬´ÓÕâµ½±¾ÐнáÊøΪעÊÍsql Óï·¨£¬ÀàËÆC++£¬c#ÖÐ//
/* ¡ */ ¶àÐÐ×¢ÊÍ£¬ÀàËÆC++£¬C#ÖÐ/* ¡ */
¶þ.±äÁ¿£¨int£¬ smallint£¬ tinyint£¬ decimal£¬float£¬real£¬ money £¬smallmoneysql Óï·¨£¬ text £¬image£¬ char£¬ varchar¡£¡£¡£¡£¡£¡££©
Óï·¨£º
DECLARE
{
{@local_variable data_type}
} [£¬...n]
ÀýÈ磺
declare @ID int --ÉêÃ÷Ò»¸öÃûΪ@IDµÄ±äÁ¿£¬ÀàÐÍΪintÐÍ
Èý.ÔÚSQL Server´°¿ÚÖдòÓ¡³ö±äÁ¿µÄÖµ
Óï·¨£º
PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr
ËÄ.±äÁ¿¸³Öµ
ÀýÈ磺
--´ÓÊý¾Ý±íÖÐÈ¡³öµÚÒ»ÐÐÊý¾ÝµÄID£¬¸³Öµ¸ø±äÁ¿@id£¬È»ºó´òÓ¡³öÀ´
Declare @ID int
Set @ID = (select top(1) categoryID from categories)
Print @ID
ÔÚSQLÖУ¬ÎÒÃDz»ÄÜÏñ´úÂëÄÇÑùÖ±½Ó¸ø±äÁ¿¸³Öµ£¬ÀýÈç@id = 1£¬Èç¹ûÒª´ïµ½ÕâÑùµÄ¹¦ÄÜ£¬¿ÉÒÔÕâÑùд£º
Declare @ID int
Set @ID = (select 1) -- ÀàËÆ @ID=1
Select @id=1 -- ÀàËÆ @ID=1
Print @ID
Îå.±äÁ¿ÔËËã(+£¬-£¬*sql Óï·¨£¬/£¬¡¡)
ÒÔϱØҪʱºòÊ¡ÂÔ±äÁ¿ÉêÃ÷
Set @ID = (select 1+5) --ÀàËÆ @ID=1+5
Set @ID=(select 1-@ID) --ÀàËÆ @ID=1-@ID
Áù.±È½Ï²Ù×÷·û
? >(greater than).
? <(less than).
? = (equals).
? <= (less than or equal to).
? >= (greater than or equal to).
? != (not equal to).
? <>(not equal to).
? !< (not less than).
? !> (not greater than).
ûʲô˵µÄ
Æß.Óï¾ä¿é£ºBegin ¡ end
½«¶àÌõÓï¾ä×÷Ϊһ¸ö¿é£¬ÀàËÆÓëC++£¬C#ÖеÄ{ }
ÀýÈ磺
Begin
Set @ID1 = (select 1)
Set @ID2 = (select 2)
End
°Ë.If£¬ if¡else¡
Óï·¨£º
IF Boolean_expression
{sql_statement | statement_block}
[ELSE
{sql_statement | statement_block}]
ÀýÈ磺
If @id is not null
Print ¡®@id is not null
if @ID = 1
begin
Set @ID = (select 1 + 1)
end
else
begin
set @ID=(select 1+2)
end
ÉÏÃæµÄÀý×ÓÓõ½Á˱ȽϲÙ×÷·û£¬Óï¾ä¿é£¬ºÍIFµÄÓï·¨¡£
¾Å.Ö´ÐÐÆäËû´æ´¢¹ý³Ì EXEC
ÀýÈç
EXEC dbo.[Sales by Ye
Ïà¹ØÎĵµ£º
1. SQLÓÅ»¯µÄÔÔòÊÇ£º
½«Ò»´Î²Ù×÷ÐèÒª¶ÁÈ¡µÄBLOCKÊý¼õµ½×îµÍ,¼´ÔÚ×î¶ÌµÄʱ¼ä´ïµ½×î´óµÄÊý¾ÝÍÌÍÂÁ¿¡£
µ÷Õû²»Á¼SQLͨ³£¿ÉÒÔ´ÓÒÔϼ¸µãÇÐÈ룺
¼ì²é²»Á¼µÄSQL£¬¿¼ÂÇÆäд·¨ÊÇ·ñ»¹ÓпÉÓÅ»¯ÄÚÈÝ
¼ì²é×Ó²éѯ ¿¼ÂÇSQL×Ó²éѯÊÇ·ñ¿ÉÒÔÓüòµ¥Á¬½ÓµÄ·½Ê½½øÐÐÖØÐÂÊéд  ......
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ÂëÖµ£¬CHAR£¨£© ·µ»ØNULL ¡£
3¡¢LOWER()ºÍ ......
ʮһ¡¢ÒÔÉϺ¯ÊýµÄ²¿·ÖʵÀý
1:replace º¯Êý
µÚÒ»¸ö²ÎÊýÄãµÄ×Ö·û´®£¬µÚ¶þ¸ö²ÎÊýÄãÏëÌæ»»µÄ²¿·Ö£¬µÚÈý¸ö²ÎÊýÄãÒªÌæ»»³Éʲô
select replace('lihan','a','b')
& ......
º¯Êý£º
XMLPARSE£º½âÎö×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý£¬²úÉúXML
XMLSERIALIZE£º½«XMLֵת»»Îª×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý
XMLVALIDATE£º¸ù¾ÝXML schemaУÑéXMLÖµµÄÓÐЧÐÔ
XMLEXISTS£º¼ì²âXQueryÊÇ·ñ·µ»Ø½á¹û£¨ÈçÓÉÒ»¸ö»ò¶à¸öÏîËù×é³ÉµÄÐòÁУ©
XMLQUERY£ºÖ´ÐÐÒ»¸öXQuery²¢ÒÔÒ»¸öÐòÁзµ»Ø½á¹û
XMLTABLE£ºÖ´ÐÐÒ»¸öXQuery£¬ÒÔ¹ØÏ ......
µÚÒ»ÖÖ£º
public void deleteUser(String byemail) {
Session session = getSession();
Transaction transaction = null;
String hql = "delete from Register where email=?
";
try {
transaction = session.beginTransactio ......