SQL_ѧÉú
µÚ¶þ²¿·Ö
1.ËùÓÐÄÐÉúµÄÐÕÃû¡¢ÄêÁä:
Select Sname£¬Sage
from student;
2.ËùÓÐÄêÁä´óÓÚ20£¬¼ÆËã»ú¿ÆѧϵѧÉúÃûµ¥:
SELECT Sname
from Student
WHERE Sage>20 AND Sdept='CS';
3.³É¼¨´óÓÚ60µÄѧÉúѧºÅ:
SELECT Sno
from SC
WHERE Grade>60;
4.³É¼¨ÔÚ70µ½80Ö®¼äµÄѧÉúѧºÅ:
SELECT Sno
from SC
WHERE Grade>70 AND Grade<80;
SELECT Sno ѧºÅ
from SC
WHERE Grade BETWEEN 70 AND 80;
5.1985µ½1989Äê³öÉúµÄѧÉúÐÕÃû:
SELECT Sname ÐÕÃû
from Student
WHERE 2009-Sage BETWEEN 1985 AND 1989;
SELECT Sname
from Student
WHERE 2009-Sage>1985 AND 2009-Sage<1989;
6.²éѯ³É¼¨²»ÔÚ70µ½85µÄ¼°¸ñѧÉúÃûµ¥:
SELECT DISTINCT Sname
from Student,SC
WHERE Student.Sno=SC.Sno AND Grade NOT BETWEEN 70 AND 80 AND Grade>60;
7.²éѯ³É¼¨ÊÇ70¡¢80¡¢90µÄѧÉúѧºÅ:
SELECT Sno
from SC
WHERE Grade IN(70,80,90);
8.²éѯ1985¡¢1989ÄêµÄѧÉúѧºÅºÍÐÕÃû:
SELECT Sno,Sname
from Student
WHERE 2009-Sage IN('1985','1989');
9.²éѯѧ·Ö²»ÊÇ3¡¢4¡¢5µÄ¿ÎºÅ:
SELECT Cno
from Course
WHERE Ccredit NOT IN(3,4,5);
10.²éѯ2002¼¶ËùÓÐѧÉúÇé¿ö:
SELECT *
from Student
WHERE Sno LIKE '2002%';
SELECT *
from Student
WHERE Sno='2002%';
11.²éѯÐÕÁõ'CS'ϵµÄÐÕÃûºÍÐÔ±ð:
SELECT Sname,Ssex
from Student
WHERE Sdept='CS' AND Sname LIKE 'Áõ%';
12.²éѯÐÕÁõ¡¢ÕÅ¡¢ÀîµÄѧÉúÃûµ¥:
SELECT Sname
from Student
WHERE Sname LIKE '[ÁõÕÅÀî]%';
13.²éѯ¿Î³ÌÃûº¬ÓÐÊý¾ÝµÄ¿Î³ÌÃû³ÆºÍѧ·Ö:
SELECT Cname,Ccredit
from Course
WHERE Cname LIKE '%Êý¾Ý%';
14.²éѯûÓвμӿ¼ÊÔ,¿Î³ÌºÅΪ001µÄѧÉúµÄѧºÅ:
SELECT Sno
from SC
WHERE Cno LIKE '001' AND Grade IS NULL;
15.ËùÓвμÓÁ˿γ̺ÅΪ002µÄѧÉúѧºÅ£¬³É¼¨¡£
SELECT Sno,Grade
from SC
WHERE Cno LIKE '%2';
16.²éѯËùÓÐ2002¼¶ÄÐÉúºÍMAϵµÄѧÉúѧºÅºÍÐÕÃû:
SELECT Sno,Sname
from Student
WHERE Sno LIKE '2002%' AND Ssex LIKE 'ÄÐ' OR Sdept LIKE 'MA';
17.²éѯËùÓÐ4ѧ·ÖºÍÏÈÐпÎΪ6µÄ¿Î³ÌºÅºÍ¿Î³ÌÃû:
SELECT Cno,Cname
from Course
WHERE Ccredit LIKE '4' AND Cpno LIKE '6';
18.²éѯ'CS'ϵѧÉúÃûµ¥,²¢°´ÄêÁä½µÐò,°´ÐÔ±ðÉýÐò:
SELECT Sname,Sage,Ssex
from Student
WHERE Sdept LIKE 'CS'
ORDER BY
Ïà¹ØÎĵµ£º
General Overview
FeatureSQL Server 2008 (RC0)MySQL 5.1/6PostgreSQL 8.3/PostGIS 1.3/1.4
OS
Windows XP, Windows Vista, Windows 2003, Windows 2008
Windows XP, Windows Vista, (haven't tested on 2008), Linux, Unix, Mac
Windows 2000+ (including Vista and 2003, haven't tested on 2008), Linux, Unix, Ma ......
select case when b.name is null and c.name is null then 'ºÏ¼Æ' when b.name is null and c.name is not null then 'С¼Æ' else b.name end as mtrname,
sum(a.number),c.name as cname from x_sell a join x_material b on a.mtr=b.fid join p_organi c on c.fid=a.customer
where a.stime>'2009-10-01'
GROUP B ......
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
SQLÖеÄʱ¼äº¯Êý·Ç³£ÓÐÓã¬ÌرðÊÇÔÚÎÒÃǽøÐгõʼ¸³Öµ¡¢¸´ÔÓ²éѯµÄʱºò£¬¾ÍÏÔµÃÌر𷽱㡣
1¡¢»ñµÃϵͳµ±Ç°Ê±¼ä
select getdate()
2¡¢DateName ( datepart , date )·µ»Ø±íʾָ¶¨ÈÕÆÚµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄ×Ö·û´®¡£
--½ñÌìÊÇ2009-2-24--ÐÇÆÚ¶þ
SELECT DATENAME(year, getdate()) AS 'Year&nbs ......
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éÑ ......