本文語句大部分SQL語句來自《數據庫系統概論》（第四版）王珊&薩師煊 ，是我們上課用的教材，感覺很不錯，總結了其中第三章的SQL語句，比較實用，希望對大家有幫助。總結如下，可以用來學習基礎的SQL語句。

建立數據庫

CREATE DATABASE DB_Student

建立表

CREATE TABLE Student

(Sno CHAR(9) PRIMARY KEY,--主碼

Sname CHAR(20) UNIQUE,--唯一值

Ssex CHAR(2),

Sage SMALLINT,

Sdept CHAR(20)

);

CREATE TABLE Course

(Cno CHAR(4) PRIMARY KEY,

Cname char(40),

Cpno CHAR(4),

Ccredit SMALLINT,

FOREIGN KEY (Cpno) REFERENCES Course(Cno)

);

CREATE TABLE SC

(Sno CHAR(9),

Cno CHAR(4),

Grade SMALLINT,

PRIMARY KEY (Sno,Cno),

FOREIGN KEY (Sno) REFERENCES Student(Sno),--外碼

FOREIGN KEY (Cno) REFERENCES Course(Cno)

);

數據

表操作

alter table Student add S_entrance date--增加列

alter table student alter column Sage int--修改字段類型

alter table course add unique (Cname)--增加唯一性約束

drop table Student--刪除基本表

drop table student cascade--刪除基本表及相關依賴對象

創建索引

drop index stusname

查詢數據

select sno,sname from student

select sname,sno,sdept from student

select sname,2004-sage from student

select sname,'Year of Birth:',2004-sage, lower(sdept) from student--查詢結果第二列是一個算數表達式

select sname name,'Year of Birth:' BIRTH,2004-sage birthday,LOWER(sdept) department from student--LOWER()小寫字母

select sno from sc

select distinct sno from sc--消除重復行

select sno from sc

select all sno from sc

select sname from student where sqept='CS'

--=、>、<、>=、<=、!=、<>、!>、!< 比較的運算符

select sname,sage from student where sage<20

select distinct sno from sc where sage<20

select sname,sdept,sage from student where sage between 20 and 23

select sname,sdept,sage from student where sage not between 20 and 23

select sname,ssex from student where sdept in ('CS','MA','IS')

select sname,sage from student where sdept not in('CS','MA','IS')

select * from student where sno like '200215121'

select * from student where sno='200215121'

--字符匹配

--% 任意長度字符串，_ 任意單個字符，ESCAPE 轉義字符

select sname,sno,ssex from student where sname like '劉%'

select sname from student where sname like '歐陽__'

select sname,sno from student where sname like '__陽%'

select sname,sno,ssex from student where sname not like '劉%'

select cno,ccredit from course where cname like 'DB/_design' escape '/'

select * from course where cname like 'DB/_%i__' escape '/'

select sno,cno from sc where grade is null --null 空值

select sno,cno from sc where grade is not null

select sname from student where sdept='CS' and sage<20

select sname,sage from studnet where sdept='CS' or sdept='MA' or sdept='IS'

select sno,grade from sc where cno='3' order by grade desc -- order by 排序

select * from student order by sdept,sage desc --空值最大

--聚集函數

select count(*) from student -- count() 行數

select count(distinct sno) from sc

select avg(grade) from sc where cno='1' -- avg() 平均數

select max(grade) from sc where cno='1' -- max() 最大值

select sum(Ccredit) from sc,course where sno='200215012' and sc.cno=course.cno -- sum() 總數

--分組

select cno,count(sno) from sc group by cno

select sno from sc group by sno having count(*) >3 --having 給出選擇組的條件

--連接查詢

select student. *,SC.* FROM STUDENT,SC where student.sno=sc.sno

select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno =sc.sno

select first.cno,second.cpno from course first,course second fwhere first.cpno=second.cno -- 自身連接

select student.sno,sname,ssex,sage,sdept,cno,grade from student left out join sc in (student.sno=sc.sno)--外連接

--from student left out join sc using (sno)

select student.sno,sname from student,sc where student.sno=sc.sno and sc.cno='2' and sc.grade>90

select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno

select sname from student where sno in (select sno from sc shere con='2')

select sdept from student where sname='劉晨'

select sno.sname,sdept from student where sdept='CS'

--嵌套查詢

select sno,sname,sdept from student where sdept in (select sdept from studnet where sname='劉晨')

select sno,sname,sdept from student where sdept in ('CS')

select s1.sno,s1.sname,s1.sdept from student s1,student s2 where s1.sdept =s2.sdept and s2.sname='劉晨'

select sno,sname from student where sno in (select sno from sc where cno in(select cno from course where cname='信息系統'))

select student.sno,sname from student ,sc,course where student.sno=sc.sno and sc.cno =course.cno and course.cname='信息系統'

--內查詢的結果是一個值，因此可以用=代替in

select sno,sname,sdept from student where sdpet=(se3lect sdept from studnet where sname='劉晨')

select sno,sname,sdept from student where(select sdept from student where sname='劉晨')=sdept

select sno,cno from sc x where grade >=(select avg(grade) from sc y where y.sno=x.sno)

select avg(grade) from sc y where y.sno='200215121'

select sno,cno from sc x where grade>=88

select sname,sage from student where sage <ANY (SELECT sage from student where sdept='CS') and sdept <>'CS'

select sname,sage from student where sage<(select max(sage) from student where sdept='CS') and sdept <> 'CS'

select sname,sage from student where sage < all (select sage from student where sdept ='CS')

select sname,sage from student where sage<(select min(sage) from student where sdept='CS') and sdept <>'CS'

select sname from student where exists(select * from sc where sno=student.sno and cno='1')

select sname from student where not exists (select * from sc where sno=student.sno and cno='1')

select sno.sname,sdept from student s1 where exists(select * from studetn s2 where s2.sdept=s1.sdept and s2.sname='劉晨')

select sname from student where not exists (select * from course where not exists(select * from sc where sno=student.sno and cno=course.cno))

select distinct sno frome sc scx where not exists (select * from sc scy where scy.sno='200215122' and not exists(select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno))

http://zxlovenet.cnblogs.com

集合查詢

select * from student where sdept ='CS' union select * from student where sage<=19 --union并操作

se