1. MySQLæ¯æenum,åsetç±»åï¼SQL Serverä¸æ¯æ
2. MySQLä¸æ¯ænchar,nvarchar,ntextç±»å
3. MySQLçéå¢è¯å¥æ¯AUTO_INCREMENTï¼èMS SQLæ¯identity(1,1)
4. MS SQLé»è®¤å°å¤è¡¨å建è¯å¥çé»è®¤å¼è¡¨ç¤ºæ¯((0)),èå¨MySQLéé¢æ¯ä¸å
许带两æ¬å·ç
5. MySQLéè¦ä¸ºè¡¨æå®åå¨ç±»å
6. MS SQLè¯å«ç¬¦æ¯[],[type]表示ä»åºå«äºå
³é®åï¼ä½æ¯MySQLå´æ¯ `ï¼ä¹å°±æ¯æé®1左边çé£ä¸ªç¬¦å·
7. MS SQLæ¯ægetdate()æ¹æ³è·åå½åæ¶é´æ¥æï¼ä½æ¯MySQLéé¢å¯ä»¥åæ¥æç±»ååæ¶é´ç±»åï¼è·åå½åæ¥ææ¯current_date ()ï¼å½åå®æ´æ¶é´æ¯ now()å½æ°
8. MS SQLä¸æ¯æreplace into è¯å¥ï¼ä½æ¯å¨ææ°çsql20008éé¢ï¼ä¹æ¯æmergeè¯æ³
9. MySQLæ¯æinsert into table1 set t1 = „‟, t2 = „‟ ,ä½æ¯MS SQLä¸æ¯æè¿æ ·å
10. MySQLæ¯æinsert into tabl1 values (1,1), (1,1), (1,1), (1,1), (1,1), (1,1), (1,1)
11. MySQLå¨å建表æ¶è¦ä¸ºæ¯ä¸ªè¡¨æå®ä¸ä¸ªåå¨å¼æç±»åï¼èMS SQLåªæ¯æä¸ç§åå¨å¼æ
12. MySQLä¸æ¯æé»è®¤å¼ä¸ºå½åæ¶é´çdatetimeç±»åï¼MS SQLå¾å®¹æåå°ï¼ï¼å¨MySQLéé¢ æ¯ç¨timestampç±»å
13. MS SQLéé¢æ£æ¥æ¯å¦æè¿ä¸ªè¡¨åå é¤ï¼éè¦è¿æ ·ï¼if exists (select * from dbo.sysobjects where id = object_id(N'uc_newpm') and OBJECTPROPERTY(id,N'IsUserTable')=1) ä½æ¯å¨MySQLéé¢åªéè¦ DROP TABLE IF EXISTS cdb_forums;
14. MySQLæ¯ææ 符å·åçæ´æ°ï¼é£ä¹æ¯ä¸æ¯ææ 符å·åçMS SQLå°±è½å¤åºä¸åçæå¤§æ° åå¨
15. MySQLä¸æ¯æå¨MS SQLéé¢ä½¿ç¨é常æ¹ä¾¿çvarchar(max)ç±»åï¼è¿ä¸ªç±»åå¨MS SQLé é¢æ¢å¯åä¸è¬æ°æ®åå¨ï¼ä¹å¯ä»¥åblobæ°æ®åå¨
16. MySQLå建éèéç´¢å¼åªéè¦å¨å建表çæ¶åæå®ä¸ºkeyå°±è¡ï¼æ¯å¦ï¼KEY displayorder (fid,displayorder) å¨MS SQLéé¢å¿
é¡»è¦ï¼create unique nonclustered index index_uc_protectedmembers_username_appid on dbo.uc_protectedmembers (username asc,appid asc)
17. MySQL textå段类åä¸å
许æé»è®¤å¼
18. MySQLçä¸ä¸ªè¡¨çæ»å
±å段é¿åº¦ä¸è¶
è¿65XXXã
19. ä¸ä¸ªå¾è¡¨é¢çåºå«å°±æ¯MySQLçå®è£
ç¹å«ç®åï¼èä¸æ件大å°æ110Mï¼éå®è£
çï¼ï¼ç¸ æ¯å¾®è½¯è¿ä¸ªåºç¶å¤§ç©ï¼å®è£
è¿åº¦æ¥è¯´ç®ç´å°±æ¯.....
20. MySQLçåå¨è¿ç¨åªæ¯åºç°å¨ææ°ççæ¬ä¸ï¼ç¨³å®æ§åæ§è½å¯è½ä¸å¦MS SQLã
21. åæ ·çè´è½½ååï¼MySQLè¦æ¶èæ´å°çCPUåå
åï¼MS SQLçç¡®æ¯å¾èèµæºã
22. mysqlçifnull()å½æ°å¯¹åºsqlçisnull()å½æ°;
23. mysqlçåå¨è¿ç¨ä¸åéçå®ä¹å»æ@;
24. mysqlçæ¯å¥ç»æè¦ç¨";"
25. SQLServeråå¨è¿ç¨çASå¨MySqlä¸éè¦ç¨begin .....endæ¿æ¢
26. å符串è¿æ¥ç¨concat()å½æ°;å¦ SQLServer: Temp=‟select * from ‟+‟tablename‟+â¦+⦠MySql:Temp=concat(‟select * from‟, ‟tablecname‟,â¦,â¦)
27. mysqlçuuid()对åºmssqlçGUID();
28. MySqlçout对åºSQLServerçoutput,ä¸mysql çoutè¦æ¾å¨åéçåé¢ï¼SQLServer çoutputæ¾å¨åéåé¢
29. MySql out,in,inoutçåºå«ââMySQL åå¨è¿ç¨ âinâ åæ°ï¼è· C è¯è¨çå½æ°å æ°çå¼ä¼ éç±»ä¼¼ï¼ MySQL åå¨è¿ç¨å
é¨å¯è½ä¼ä¿®æ¹æ¤åæ°ï¼ä½å¯¹ in ç±»ååæ°çä¿®æ¹ï¼å¯¹è°ç¨è
ï¼callerï¼æ¥è¯´æ¯ä¸å¯è§çï¼not visibleï¼ãMySQL åå¨è¿ç¨ âoutâ åæ°ï¼ä»åå¨è¿ç¨å
é¨ä¼ å¼ç»è°ç¨è
ãå¨åå¨è¿ç¨å
é¨ï¼è¯¥åæ°åå§å¼ä¸º nullï¼æ 论è°ç¨è
æ¯å¦ç»åå¨è¿ç¨åæ°è®¾ç½®å¼ãMySQL åå¨è¿ç¨ inout åæ°è· out 类似ï¼é½å¯ä»¥ä»åå¨è¿ç¨å
é¨ä¼ å¼ç»è°ç¨è
ãä¸åçæ¯ï¼è°ç¨è
è¿å¯ä»¥éè¿ inout åæ°ä¼ éå¼ç»åå¨è¿ç¨ã
30. MySQLçifè¯å¥ä¸º if (æ¡ä»¶) then end if; æè
If (æ¡ä»¶) then Else End if æè
Ifï¼æ¡ä»¶ï¼then Elseif (注æä¸è½åæ Else if ) Elseif ⦠End if
31. MysqlçExecute对åºSqlServerçexec; (注æï¼å¿
é¡»æ³ä¸é¢è¿æ ·è°ç¨) Set @cnt=‟select * from 表å‟; Prepare str from @cnt; Execute str;
32. MySqlåå¨è¿ç¨è°ç¨å
¶ä»åå¨è¿ç¨ç¨call Call å½æ°åï¼å³SQLServerçåå¨è¿ç¨åï¼ï¼‟åæ°1‟,‟åæ°2‟,â¦â¦ï¼
33. mysqlçæ¥æ
1) è·å¾å½åæ¥æå½æ°ï¼curdate()ï¼current_date()
2) è·å¾å½åæ¶é´å½æ°ï¼curtime();
3) è·å¾å½åæ¥æ+æ¶é´ï¼now();
4) MySQL dayof... å½æ°ï¼dayofweek(), dayofmonth(), dayofyear()åå«è¿åæ¥æå æ°ï¼å¨ä¸å¨ãä¸æãä¸å¹´ä¸çä½ç½®ã
5) (注ï¼å¨æ¥=1ï¼å¨ä¸=2ï¼å¨äº=3ï¼â¦â¦)
6) è¿åæ¬æç天æ°ï¼select day(last_day(now()));
7) MySQL 为æ¥æå¢å ä¸ä¸ªæ¶é´é´éï¼date_add()
8) select date_add(CURRENT_DATE(),interval „è¦å¢å ç天æ°‟ day) as Fdate
9) MySQL 为æ¥æåå»ä¸ä¸ªæ¶é´é´éï¼date_sub()
10) select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);
11) MySQL æ¥æãæ¶é´ç¸åå½æ°ï¼datediff(date1,date2), timediff(time1,time2)
12) MySQL æ¼åæ¥æãæ¶é´å½æ°ï¼makdedate(year,dayofyear), maketime(hour,minute,second)
13) ä¾ï¼select makedate(2001,31); -- '2001-01-31'
14) select makedate(2001,32); -- '2001-02-01'
15) æ¬å¨æ¶é´ï¼èµ·å§ï¼
16) select date_sub(CURRENT_DATE(),interval dayofweek(curdate())-2 day) as Fdate
17) æ¬å¨æ¶é´ï¼ç»æï¼
18) select date_add(CURRENT_DATE(),interval dayofweek(curdate())+3 day) as Fdate
19) ä¸å¨æ¶é´ï¼èµ·å§ï¼
20) select date_sub(CURRENT_DATE(),interval dayofweek(curdate())+5 day) as Fdate
21) ä¸å¨æ¶é´ï¼ç»æï¼
22) select date_sub(CURRENT_DATE(),interval dayofweek(curdate())-1 day) as Fdate
23) æ¬ææ¶é´(èµ·å§)
24) select DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) as Fdate
25) æ¬ææ¶é´ï¼ç»æï¼
26) Select date_add(current_date(),interval day(last_day(CURDATE())) -day(CURDATE()) day) as Fdate
27) ä¸ææ¶é´ï¼èµ·å§ï¼
28) select DATE_SUB(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE()) DAY),interval day(last_day(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE()) DAY)))- 1 day) as Fdate
29) ä¸ææ¶é´ï¼ç»æï¼
30) select DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE()) DAY) as Fdate
31) ä»å¹´æ¶é´ï¼èµ·å§ï¼
32) select makedate(year(curdate()),1) as FDate
33) ä»å¹´æ¶é´ï¼ç»æï¼
34) select DATE_SUB(makedate(year(curdate())+1,1) ,INTERVAL 1 day) as Fdate
35) å»å¹´æ¶é´ï¼èµ·å§ï¼
36) select makedate(year(curdate())-1,1) as Fdate
37) å»å¹´æ¶é´ï¼ç»æï¼
38) select DATE_SUB(makedate(year(curdate()),1) ,INTERVAL 1 day) as FDate
39) DATE_FORMAT(date,format)ï¼æ ¹æ®formatåç¬¦ä¸²æ ¼å¼ådateå¼ãä¸å修饰符 å¯ä»¥è¢«ç¨å¨formatå符串ä¸
40) %M æåå(Januaryâ¦â¦December) %W ææåå(Sundayâ¦â¦Saturday) %D æè±è¯åç¼çæ份çæ¥æ(1st, 2nd, 3rd, ççãï¼ %Y å¹´, æ°å, 4 ä½ %y å¹´, æ°å, 2 ä½ %a 缩åçææåå(Sunâ¦â¦Sat) %d æ份ä¸ç天æ°, æ°å(00â¦â¦31) %e æ份ä¸ç天æ°, æ°å(0â¦â¦31) %m æ, æ°å(01â¦â¦12) %c æ, æ°å(1â¦â¦12) %b 缩åçæ份åå(Janâ¦â¦Dec) %j ä¸å¹´ä¸ç天æ°(001â¦â¦366) %H å°æ¶(00â¦â¦23) %k å°æ¶(0â¦â¦23) %h å°æ¶(01â¦â¦12) %I å°æ¶(01â¦â¦12) %l å°æ¶(1â¦â¦12) %i åé, æ°å(00â¦â¦59) %r æ¶é´,12 å°æ¶(hh:mm:ss [AP]M) %T æ¶é´,24 å°æ¶(hh:mm:ss) %S ç§(00â¦â¦59) %s ç§(00â¦â¦59) %p AMæPM
41) %w ä¸ä¸ªææä¸ç天æ°(0=Sunday â¦â¦6=Saturday ï¼ %U ææ(0â¦â¦52), è¿éææ天æ¯ææç第ä¸å¤© %u ææ(0â¦â¦52), è¿éææä¸æ¯ææç第ä¸å¤© %% ä¸ä¸ªæåâ%âã
42) ä¾ï¼ææçå
¶ä»å符ä¸å解é被å¤å¶å°ç»æä¸ã mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); ->'Saturday October 1997' mysql>select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
43) -> '22:23:00'
44) mysql>select DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');
45) ->'4th 97 Sat 04 10 Oct 277'
46) mysql>select DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
47) ->'22 22 10 10:23:00 PM 22:23:00 00 6'
34. MySqlåå¨è¿ç¨ä¸æ²¡æreturnå½æ°ï¼å¨MySqlä¸å¯ä»¥ç¨å¾ªç¯åoutåæ°ä»£æ¿ If EXISTS(SELECT * FROM T_Chance WHERE FCustID=CostomerID) return 0 æ¹åä¸ºï¼ ï¼å¨åæ°ä¸å®ä¹ä¸ä¸ªoutåéï¼out temp varchar(100);ï¼ BEGIN Loop1:loop SELECT count(*) FROM T_Chance WHERE FCustID=CostomerID int @cnt If @cnt>0 then begin set temp=0; leave loop1; end; end if end loop loop1;
35. (13) select @a=count(*) from VW_Action å¨mySqlä¸ä¿®æ¹ä¸ºï¼select count(*) from VW_Action into @a;
36. (14)MySQLä¸æ²¡ætopå
³é®åï¼éè¦ç¨limit代æ¿ä¸æ¾å¨åé¢ æ³¨æï¼å¨MySQLä¸çlimitä¸è½æ¾å¨åæ¥è¯¢å
ï¼limitä¸åä¸SQLServer,å®å¯ 以è§å®èå´ limit a,bââèå´a-b SQL SERVER : select top 8 * from table1 MYSQL: select * from table1 limit 5;
37. (15)å³ä½¿åå¨è¿ç¨æ²¡æåæ°ä¹è¦åæ¬å·âï¼ï¼â
38. (16) å½ä¸ä¸ªåå¨è¿ç¨ä¸æå建临æ¶è¡¨æ¶ create procedure up_test () begin drop table if exists tb1; create TEMPORARY table tb1//注ææ·»å TEMPORARY table ( id int, name varchar(20) );//注ææåå åå· insert tb1 values('1','jim'); select * from tb1; end
39. ï¼17ï¼å»ºè¡¨ä¸èªå¢é¿é®é¢ï¼ create table user ( Id varchar(10) primary key auto_increment not null, Name varchar(20) not null, Password varchar(20), create_date datetime ); auto_increment èªå¢é¿
40. (18) "Unable to convert MySQL date/time value to System.DateTime"è¿æ¯å 为å¨æ¥æ åä¸æ"0000-00-00"æ°æ®å¼ï¼è¦ä¿®æ£è¿ä¸ªé®é¢ï¼ä½ å¯ä»¥æè¿äºæ°æ®è®¾ä¸ºnullï¼æè
å¨è¿æ¥å符串ä¸è®¾ç½®"Allow Zero Datetime=True" ã 41. (19) MySQLè§å¾çFROMåå¥ä¸å
许åå¨åæ¥è¯¢ï¼å æ¤å¯¹äºSQL Serverä¸FROM åå¥å¸¦æåæ¥è¯¢çè§å¾ï¼éè¦æå·¥è¿è¡è¿ç§»ãå¯éè¿æ¶é¤FROMåå¥ä¸çåæ¥è¯¢ï¼æå°FROMåå¥ä¸çåæ¥è¯¢éæ为ä¸ä¸ªæ°çè§å¾æ¥è¿è¡è¿ç§»ã
温馨提示:答案为网友推荐,仅供参考