Oracle SQLµÄÓÅ»¯
SQLµÄÓÅ»¯Ó¦¸Ã´Ó5¸ö·½Ãæ½øÐе÷Õû£º
1.È¥µô²»±ØÒªµÄ´óÐͱíµÄÈ«±íɨÃè
2.»º´æСÐͱíµÄÈ«±íɨÃè
3.¼ìÑéÓÅ»¯Ë÷ÒýµÄʹÓÃ
4.¼ìÑéÓÅ»¯µÄÁ¬½Ó¼¼Êõ
5.¾¡¿ÉÄܼõÉÙÖ´Ðмƻ®µÄCost
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQLÓï¾äµÄÓÅ»¯ÔÚʱ¼ä³É±¾ºÍ·çÏÕÉϵĴú¼Û¶¼ºÜµÍ£»
¿ÉÒÔÓв»Í¬µÄд·¨£»Ò×ѧ£¬ÄѾ«Í¨¡£
SQLÓÅ»¯£º
¹Ì¶¨µÄSQLÊéдϰ¹ß£¬ÏàͬµÄ²éѯ¾¡Á¿±£³ÖÏàͬ£¬´æ´¢¹ý³ÌµÄЧÂʽϸߡ£
Ó¦¸Ã±àдÓëÆä¸ñʽһÖµÄÓï¾ä£¬°üÀ¨×ÖĸµÄ´óСд¡¢±êµã·ûºÅ¡¢»»ÐеÄλÖõȶ¼ÒªÒ»ÖÂ
ORACLEÓÅ»¯Æ÷£º
ÔÚÈκοÉÄܵÄʱºò¶¼»á¶Ô±í´ïʽ½øÐÐÆÀ¹À£¬²¢ÇÒ°ÑÌض¨µÄÓï·¨½á¹¹×ª»»³ÉµÈ¼ÛµÄ½á¹¹£¬Õâô×öµÄÔÒòÊÇ
Ҫô½á¹û±í´ïʽÄܹ»±ÈÔ´±í´ïʽ¾ßÓиü¿ìµÄËÙ¶È
ҪôԴ±í´ïʽֻÊǽá¹û±í´ïʽµÄÒ»¸öµÈ¼ÛÓïÒå½á¹¹
²»Í¬µÄSQL½á¹¹ÓÐʱ¾ßÓÐͬÑùµÄ²Ù×÷£¨ÀýÈ磺= ANY (subquery) and IN (subquery)£©£¬ORACLE»á°ÑËûÃÇÓ³Éäµ½Ò»¸öµ¥Ò»µÄÓïÒå½á¹¹¡£
1 ³£Á¿ÓÅ»¯£º
³£Á¿µÄ¼ÆËãÊÇÔÚÓï¾ä±»ÓÅ»¯Ê±Ò»´ÎÐÔÍê³É£¬¶ø²»ÊÇÔÚÿ´ÎÖ´ÐÐʱ¡£ÏÂÃæÊǼìË÷ÔÂн´óÓÚ2000µÄµÄ±í´ïʽ£º
sal > 24000/12
sal > 2000
sal*12 > 24000
Èç¹ûSQLÓï¾ä°üÀ¨µÚÒ»ÖÖÇé¿ö£¬ÓÅ»¯Æ÷»á¼òµ¥µØ°ÑËüת±ä³ÉµÚ¶þÖÖ¡£
ÓÅ»¯Æ÷²»»á¼ò»¯¿çÔ½±È½Ï·ûµÄ±í´ïʽ£¬ÀýÈçµÚÈýÌõÓï¾ä£¬¼øÓÚ´Ë£¬Ó¦¾¡Á¿Ð´Óó£Á¿¸ú×ֶαȽϼìË÷µÄ±í´ïʽ£¬¶ø²»Òª½«×Ö¶ÎÖÃÓÚ±í´ïʽµ±ÖС£·ñÔòûÓа취ÓÅ»¯£¬±ÈÈçÈç¹ûsalÉÏÓÐË÷Òý£¬µÚÒ»ºÍµÚ¶þ¾Í¿ÉÒÔʹÓ㬵ÚÈý¾ÍÄÑÒÔʹÓá£
2 ²Ù×÷·ûÓÅ»¯£º
ÓÅ»¯Æ÷°ÑʹÓÃLIKE²Ù×÷·ûºÍÒ»¸öûÓÐͨÅä·ûµÄ±í´ïʽ×é³ÉµÄ¼ìË÷±í´ïʽת»»ÎªÒ»¸ö“=”²Ù×÷·û±í´ïʽ¡£
ÀýÈ磺ÓÅ»¯Æ÷»á°Ñ±í´ïʽename LIKE 'SMITH'ת»»Îªename = 'SMITH'
ÓÅ»¯Æ÷Ö»ÄÜת»»Éæ¼°µ½¿É±ä³¤Êý¾ÝÀàÐ͵ıí´ïʽ£¬Ç°Ò»¸öÀý×ÓÖУ¬Èç¹ûENAME×ֶεÄÀàÐÍÊÇCHAR(10)£¬ ÄÇôÓÅ»¯Æ÷½«²»×öÈκÎת»»¡£
Ò»°ãÀ´½²LIKE±È½ÏÄÑÒÔÓÅ»¯¡£
ÆäÖУº
~~IN ²Ù×÷·ûÓÅ»¯£º
ÓÅ»¯Æ÷°ÑʹÓÃIN±È½Ï·ûµÄ¼ìË÷±í´ïʽÌ滻ΪµÈ¼ÛµÄʹÓÓ=”ºÍ“OR”²Ù×÷·ûµÄ¼ìË÷±í´ïʽ¡£
ÀýÈ磬ÓÅ»¯Æ÷»á°Ñ±í´ïʽename IN ('SMITH','KING','JONES')Ì滻Ϊ
ename = 'SMITH' OR ename = 'KIN
Ïà¹ØÎĵµ£º
Oracleº¯ÊýºÍmysqlº¯Êý±È½Ï
1. OracleÖеÄto_number()ת»»³ÉÊý×Ö£»
Oracle> Select to_number(‘123’) from dual; ----- 123;
&nbs ......
character-set-server = GB2312
collation-server = latin1_general_ci
MySQL×Ö·û¼¯ GBK¡¢GB2312¡¢UTF8Çø±ð ½â¾ö MYSQLÖÐÎÄÂÒÂëÎÊÌâ ÊÕ²Ø
MySQLÖÐÉæ¼°µÄ¼¸¸ö×Ö·û¼¯
character-set-server/default-character-set£º·þÎñÆ÷×Ö·û¼¯£¬Ä¬ÈÏÇé¿öÏÂËù²ÉÓõġ£
character-set-database£ºÊý¾Ý¿â×Ö·û¼¯¡£
character-set-table£ºÊ ......
mysql ´ó¶ÔÏó´æÈ¡:
¡¡¡¡ÀàÐÍÒ»°ãÓ¦¸ÃÓÃmediumblod,
¡¡¡¡blobÖ»ÄÜ´æ2µÄ16´Î·½¸öbyte,
¡¡¡¡mediumblodÊÇ24´Î·½,
¡¡¡¡Ò»°ãÀ´Ëµ¹»ÓÃÁË.longblobÊÇ32´Î·½ÓÐЩ´ó.
¡¡¡¡MYSQLĬÈÏÅäÖÃÖ»ÄÜ´æ1M´óСµÄÎļþ,ÒªÐÞ¸ÄÅäÖÃ,WIN°æ±¾µÄÔÚmysql.iniÎļþÖÐ
¡¡¡¡ÐÞ¸Ämax_allowed_packet,net_buffer_lengthµÈ¼¸¸ö²ÎÊý,»òÖ±½ÓSET GLOBAL va ......
Sql´úÂë
--²ÉÓÃSQLÓï¾äʵÏÖsql2005ºÍExcel Êý¾ÝÖ®¼äµÄÊý¾Ýµ¼Èëµ¼³ö£¬ÔÚÍøÉÏÕÒÀ´Ò»--Ï£¬ÊµÏÖ·½·¨ÊÇÕâÑùµÄ£º
--Excel---->SQL2005 µ¼È룺
select * into useinfo from O ......
1.Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)¡¡¡¡
¡¡¡¡ SQLSERVERµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬Òò´Ëfrom×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±ídriving table£©½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏ£¬±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í£¬µ±SQLSERVER´¦Àí¶à¸ö±íʱ£¬»áÔËÓÃÅÅÐò¼°ºÏ²¢µÄ·½Ê½Á ......