ʵÑéÎå SQL²éѯ£¨ÅÅÐò¡¢¼¯º¯ÊýºÍ·Ö×é×Ó¾äµÄÓ¦Óã©
ÊÔÑéÄ¿µÄ:
Ò»¡¢Ñ§Ï°²éѯ½á¹ûµÄÅÅÐò
¶þ¡¢Ñ§Ï°Ê¹Óü¯º¯ÊýµÄ·½·¨£¬Íê³Éͳ¼Æ
µÈ²éѯ¡£
Èý¡¢Ñ§Ï°Ê¹Ó÷Ö×é×Ó¾ä
Ò»¡¢Ñ§Ï°²éѯ½á¹ûµÄÅÅÐò
1¡¢²éѯȫÌåѧÉúÐÅÏ¢£¬½á¹û°´ÕÕÄêÁä½µ
ÐòÅÅÐò
select *
from student
order by sage desc
2¡¢²éѯѧÉúÑ¡ÐÞÇé¿ö£¬½á¹ûÏȰ´ÕտγÌ
ºÅÉýÐòÅÅÐò£¬ÔÙ°´³É¼¨½µÐòÅÅÐò
select *
from sc
order by cno,grade desc
˼¿¼Ì⣺²éѯѧÉúÐÕÃû£¬¼°ÆäÑ¡Ð޵ĿÎ
³ÌÃûºÍ³É¼¨£¬½á¹ûÏȰ´ÕտγÌÃûÉýÐòÅÅ
Ðò£¬ÔÙ°´³É¼¨½µÐòÅÅÐò
1¡¢ÅÅÐòÊǶԲéѯ½á¹ûµÄÅÅÐò£¬ÅÅÐò·½·¨
ÓÐÁ½ÖÖ£¬ÉýÐò£¨Ä¬ÈÏ asc£©ºÍ½µÐò
£¨desc£©¡£
2¡¢µ±ÅÅÐòµÄÊôÐÔÓжà¸öʱ£¬ÓÅÏÈÅÅÐòµÄ
·ÅÔÚÇ°Ãæ£¬ÒÀ´ÎÁгö£¬ÖмäÓöººÅ¸ô¿ª
¡£
3¡¢µ±²éѯ½á¹ûÊǶà¸ö±íʱ£¬ÀûÓÃÁ¬½Ó²é
ѯ£¨where Ìõ¼þÄڰѹ²Í¬ÁÐÏàµÈ£©£¬È»
ºó¶Ô²éѯ½á¹ûÅÅÐò¡£
¶þ¡¢¼¯º¯ÊýµÄÓ¦ÓÃ
1¡¢²éѯstudent±íÄÚѧÉúÈËÊý
select count(sno)
from student
select count(*)
from student
2¡¢²éѯÓÐÑ¡Ð޿γ̵ÄѧÉúÈËÊý
select count(distinct sno)
from sc
3¡¢²éѯ3ºÅ¿Î³ÌµÄ³É¼¨×Ü·Ö
select sum(grade)
from sc
where cno='3'
4¡¢²éѯѧÉúÄêÁäºÍ
select sum(sage)
from student
5¡¢²éѯѧÉú200215122ËùÑ¡Ð޵Ŀγ̵Ä
ƽ¾ù·Ö
select avg(grade)
from sc
where sno='200215122'
6¡¢Çó3ºÅ¿Î³ÌµÄ×î¸ß·Ö
select max(grade)
from sc
where cno = '3'
Á·Ï°£º
1¡¢²éѯÓм¸ÃÅÑ¡Ð޿α»Ñ¡ÐÞ
select count(distinct cno)
from sc
2¡¢²éѯѧÉúÖÐÄÐÉúÈËÊý
select count(sno)
from student
where ssex = 'ÄÐ'
3¡¢²éѯѧÉú200215121ËùÑ¡Ð޵Ŀγ̳É
¼¨×Ü·Ö
select sum(grade)
from sc
where sno='200215121'
4¡¢²éѯ3ºÅ¿Î³ÌµÄƽ¾ù·Ö
select avg(grade)
from sc
where cno='3'
˼¿¼Ì⣺
1¡¢²éѯѧÉúÖи÷ÄêÁä¶ÎÈËÊý
select sage,count(sno)
from student
group by sage
2¡¢²éѯ3ºÅ¿Î³ÌµÄƽ¾ù·ÖºÍ×î¸ß·Ö
select avg(grade) ƽ¾ù·Ö,max(grade)
×î¸ß·Ö
from sc
where cno='3'
×ܽ᣺
1¡¢¼¯º¯Êýcount(ÊôÐÔÃû)¼ÆËã¸öÊý£¬
×¢Òâdistinct ÊôÐÔÃûµÄÓ¦Óã¬È¡ÏûÖØ¸´
µÄÐС£ÊôÐÔÃûÁбí¿ÉÒÔÊǶà¸öÓÃ×Ö·û´®
Á¬½Ó£¨+£©¡£
2¡¢sum(),avg()ÕâÁ½¸öÊǶÔÊýÖµÐÍÊô
ÐÔ½øÐеÄͳ¼Æ²Ù×÷¡£
3¡¢max(),min()¸ù¾ÝÊôÐÔ£¨ÊýÖµÐ͵Ä
»ò×Ö·ûÐ͵ͼ¿ÉÒÔ£©Ñ¡Ôñ×î´óµÄ»ò×îС
µÄ¡£
Èý¡¢·Ö×égroup by×Ó¾äµÄÓ¦ÓÃ
1¡¢²éѯ¿Î³ÌºÅ¼°ÏàÓ¦µÄÑ¡ÐÞÈËÊý
select cno,count(*)
f
Ïà¹ØÎĵµ£º
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+"/";
%>
<%!
& ......
ÓÐЩÈË¿ÉÄܶÔÖ÷¼üºÍ¾Û¼¯Ë÷ÒýÓÐËù»ìÏý£¬ÆäʵÕâÁ½¸öÊDz»Í¬µÄ¸ÅÄÏÂÃæÊÇÒ»¸ö¼òµ¥µÄÃèÊö¡£²»Ïë¿´ÈÆ¿ÚÎÄ×ÖÕߣ¬Ö±½Ó¿´Á½ÕßµÄ¶Ô±È±í¡£ÓÈÆäÊÇ×îºóÒ»ÏîµÄ±È½Ï¡£
Ö÷¼ü£¨PRIMARY KEY £©
À´×ÔMSDNµÄÃèÊö£º
±íͨ³£¾ßÓаüº¬Î¨Ò»±êʶ±íÖÐÿһÐеÄÖµµÄÒ»ÁлòÒ»×éÁС£ÕâÑùµÄÒ»Áлò¶àÁгÆÎª±íµÄÖ÷¼ü (PK)£¬ÓÃÓÚÇ¿ÖÆ±íµÄʵÌåÍêÕûÐÔ¡£ÔÚ´´½ ......
SELECT COUNT(*) from USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER('pow_role')
ͳ¼Æpow_role±íµÄÁÐÊý
ÆäÖÐ USER_TAB_COLUMNS ÊÇϵͳ×Ô´øµÄ±í¡£
pow_roleÊÇҪͳ¼ÆµÄ±íÃû¡£
Èç¹ûҪͳ¼ÆÆäËûµÄ±íµÄÁÐÊýÖ»ÐèÒª°Ñ±íÃû»»Ò»Ï¾ͿÉÒÔÁË£¬Èç
SELECT COUNT(*) from USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER('POW_ROLE_GROUP')&nb ......
http://topic.csdn.net/u/20091016/09/fea7e9d3-53c4-4b00-9abe-dbf8dd55b05b.html?seed=1546300155&r=60454236#r_60454236
ÔÎÊÌ⣺
Êý¾ÝÈçÏÂ, ÎÒÏëÈ¡³öTABLEÖеÄÊý¾Ý, °´ÕÕÏÂÃæµÄÌõ¼þ.
Èç¹ûF2ÏàͬµÄÇé¿öÏÂ, È¡F3Êý¾Ý½ÏСµÄÄÇÌõ, Èç¹ûF3»¹Ïàͬ, ¾ÍÈ¡F4½ÏСµÄÄǸö, ÒÀ´Î...
Çë½Ì¼òµ¥µãЧÂʸߵãµÄSQL, ÒòΪÊý¾Ý½Ï¶à, ......
declare @str varchar(8000),@sql varchar(8000)
set @str=''
select @str=@str+','+name from syscolumns WHERE ID=OBJECT_ID('±íÃû') AND NAME!='ÒªÅųýµÄÁÐÃû'
set @str=stuff(@str,1,1,'')
set @sql='select '+@str+' from ±íÃû'
exec (@sql) ......