sqlÓÅ»¯Ö®:¸ÄÉÆSQLÓï¾ä(ϵÁÐÈý)
¶þ¡¢¸ÄÉÆSQLÓï¾ä
¡¡¡¡ºÜ¶àÈ˲»ÖªµÀSQLÓï¾äÔÚSQL SERVERÖÐÊÇÈçºÎÖ´Ðеģ¬ËûÃǵ£ÐÄ×Ô¼ºËùдµÄSQLÓï¾ä»á±»SQL SERVERÎó½â¡£±ÈÈ磺
¡¡¡¡select * from table1 where name='zhangsan' and tID > 10000
¡¡¡¡ºÍÖ´ÐÐ:
¡¡¡¡select * from table1 where tID > 10000 and name='zhangsan'
¡¡¡¡Ò»Ð©È˲»ÖªµÀÒÔÉÏÁ½ÌõÓï¾äµÄÖ´ÐÐЧÂÊÊÇ·ñÒ»Ñù£¬ÒòΪÈç¹û¼òµ¥µÄ´ÓÓï¾äÏȺóÉÏ¿´£¬ÕâÁ½¸öÓï¾äµÄÈ·ÊDz»Ò»Ñù£¬Èç¹ûtIDÊÇÒ»¸ö¾ÛºÏË÷Òý£¬ÄÇôºóÒ»¾ä½ö½ö´Ó±íµÄ10000ÌõÒÔºóµÄ¼Ç¼ÖвéÕÒ¾ÍÐÐÁË;¶øÇ°Ò»¾äÔòÒªÏÈ´ÓÈ«±íÖвéÕÒ¿´Óм¸¸öname='zhangsan'µÄ£¬¶øºóÔÙ¸ù¾ÝÏÞÖÆÌõ¼þÌõ¼þtID& gt;10000À´Ìá³ö²éѯ½á¹û¡£
¡¡¡¡ÊÂʵÉÏ£¬ÕâÑùµÄµ£ÐÄÊDz»±ØÒªµÄ¡£SQL SERVERÖÐÓÐÒ»¸ö“²éѯ·ÖÎöÓÅ»¯Æ÷”£¬Ëü¿ÉÒÔ¼ÆËã³öwhere×Ó¾äÖеÄËÑË÷Ìõ¼þ²¢È·¶¨ÄĸöË÷ÒýÄÜËõС±íɨÃèµÄËÑË÷¿Õ¼ä£¬Ò²¾ÍÊÇ˵£¬ËüÄÜʵÏÖ×Ô¶¯ÓÅ»¯¡£
¡¡¡¡ËäÈ»²éѯÓÅ»¯Æ÷¿ÉÒÔ¸ù¾Ýwhere×Ó¾ä×Ô¶¯µÄ½øÐвéѯÓÅ»¯£¬µ«´ó¼ÒÈÔÈ»ÓбØÒªÁ˽âһϓ²éѯÓÅ»¯Æ÷”µÄ¹¤×÷ÔÀí£¬Èç·ÇÕâÑù£¬ÓÐʱ²éѯÓÅ»¯Æ÷¾Í»á²»°´ÕÕÄúµÄ±¾Òâ½øÐпìËÙ²éѯ¡£
¡¡¡¡ÔÚ²éѯ·ÖÎö½×¶Î£¬²éѯÓÅ»¯Æ÷²é¿´²éѯµÄÿ¸ö½×¶Î²¢¾ö¶¨ÏÞÖÆÐèҪɨÃèµÄÊý¾ÝÁ¿ÊÇ·ñÓÐÓá£Èç¹ûÒ»¸ö½×¶Î¿ÉÒÔ±»ÓÃ×÷Ò»¸öɨÃè²ÎÊý(SARG)£¬ÄÇô¾Í³ÆÖ®Îª¿ÉÓÅ»¯µÄ£¬²¢ÇÒ¿ÉÒÔÀûÓÃË÷Òý¿ìËÙ»ñµÃËùÐèÊý¾Ý¡£
¡¡¡¡SARGµÄ¶¨Ò壺ÓÃÓÚÏÞÖÆËÑË÷µÄÒ»¸ö²Ù×÷£¬ÒòΪËüͨ³£ÊÇÖ¸Ò»¸öÌØ¶¨µÄÆ¥Å䣬һ¸öÖµµÃ·¶Î§Ä򵀮¥Åä»òÕßÁ½¸öÒÔÉÏÌõ¼þµÄANDÁ¬½Ó¡£ÐÎʽÈçÏ£º
¡¡¡¡ÁÐÃû ²Ù×÷·û <³£Êý »ò ±äÁ¿>
¡¡¡¡»ò
¡¡¡¡<³£Êý »ò ±äÁ¿> ²Ù×÷·ûÁÐÃû
¡¡¡¡ÁÐÃû¿ÉÒÔ³öÏÖÔÚ²Ù×÷·ûµÄÒ»±ß£¬¶ø³£Êý»ò±äÁ¿³öÏÖÔÚ²Ù×÷·ûµÄÁíÒ»±ß¡£È磺
¡¡¡¡Name=’ÕÅÈý’
¡¡¡¡¼Û¸ñ>5000
¡¡¡¡5000<¼Û¸ñ
¡¡¡¡Name=’ÕÅÈý’ and ¼Û¸ñ>5000
¡¡¡¡Èç¹ûÒ»¸ö±í´ïʽ²»ÄÜÂú×ãSARGµÄÐÎʽ£¬ÄÇËü¾ÍÎÞ·¨ÏÞÖÆËÑË÷µÄ·¶Î§ÁË£¬Ò²¾ÍÊÇSQL SERVER±ØÐë¶ÔÿһÐж¼ÅжÏËüÊÇ·ñÂú×ãWHERE×Ó¾äÖеÄËùÓÐÌõ¼þ¡£ËùÒÔÒ»¸öË÷Òý¶ÔÓÚ²»Âú×ãSARGÐÎʽµÄ±í´ïʽÀ´ËµÊÇÎÞÓõġ£
½éÉÜÍêSARGºó£¬ÎÒÃÇÀ´×ܽáÒ»ÏÂʹÓÃSARGÒÔ¼°ÔÚʵ¼ùÖÐÓöµ½µÄºÍijЩ×ÊÁÏÉϽáÂÛ²»Í¬µÄ¾Ñ飺
¡¡¡¡1¡¢LikeÓï¾äÊÇ·ñÊôÓÚSARGÈ¡¾öÓÚËùʹÓõÄͨÅä·ûµÄÀàÐÍ
¡¡¡¡È磺name like ‘ÕÅ%’ £¬Õâ¾ÍÊôÓÚSARG
¡¡¡¡¶ø£ºname like ‘%ÕÅ’ ,¾Í²»ÊôÓÚSARG¡£
¡¡¡¡ÔÒòÊÇͨÅä·û%ÔÚ×Ö·û´®µÄ¿ªÍ¨Ê¹µÃË÷ÒýÎÞ·¨Ê¹Óá£
¡¡¡¡2¡¢or »áÒýÆðÈ«±íɨ
Ïà¹ØÎĵµ£º
index.jsp
<%@ page language="java" import="java.sql.*" import="java.lang.*" import="java.util.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%!
& ......
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£ºÊ ......
import java.sql.*;
/*
* JAVAÁ¬½ÓACCESS£¬SQL Server,MySQL,OracleÊý¾Ý¿â
*
* */
public class JDBC {
public static void main(String[] args)throws Exception {
Connection conn=null;
//====Á¬½ÓACCESSÊý¾Ý¿â ......
ÈçºÎ½«sql²éѯ½á¹û£¨²»Ö¹Ò»Ï²åÈëµ½Ò»ÕűíÖУ¬ÎÒÃÇÒ»°ã¿ÉÒÔÓÃÈçÏÂÐÎʽµÄsql
insert into table1
select field1,field2,... from table2
where ...
ËüÒªÇó¶ÔÓ¦µÄ²åÈëÏîÀàÐͱØÐëÒ»Ö¡£
ÁíÍ⣬µ±selectµÄ²éѯ½á¹ûÊÇtable1µÄËùÓÐ×ֶεÄʱºò£¬¿ÉÒÔÊ¡ÂÔtable1µÄÁÐ×ֶΣ¬Ð´³ÉÈçÏÂÐÎʽ£º
insert into table1(fie ......
±¾ÎÄ×ªÔØ×Ô http://database.ctocio.com.cn/tips/272/7588272.shtml
΢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý(clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý)ºÍ·Ç¾Û¼¯Ë÷Òý(nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý)……
¡¡¡¡(Ò»)ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£ ......