1. 新建學(xué)生-課程數(shù)據(jù)庫的三個表:
學(xué)生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno為主碼;
課程表:Course(Cno,Cname,Cpno,Credeit) Cno為主碼;
學(xué)生選修表:SC(Sno,Cno,Grade) Sno,Cno,為主碼;

Student

Course:

SC:

數(shù)據(jù)庫生成語句：

Sql代碼

1. create database stu_course   
2.   
3. use stu_course   
4.   
5. create table student(   
6.     sno varchar(32),   
7.     sname varchar(32),   
8.     ssex varchar(32),   
9.     sage int,   
10.     sdept varchar(32)   
11. )   
12.   
13. create table Course(   
14.     Cno varchar(32),   
15.     Cname varchar(32),   
16.     Cpno varchar(32),   
17.     credit int  
18. )   
19.   
20. create table SC(   
21.     Sno varchar(32),   
22.     Cno varchar(32),   
23.     Grade int  
24. )  

create database stu_course
use stu_course
create table student(
sno varchar(32),
sname varchar(32),
ssex varchar(32),
sage int,
sdept varchar(32)
)
create table Course(
Cno varchar(32),
Cname varchar(32),
Cpno varchar(32),
credit int
)
create table SC(
Sno varchar(32),
Cno varchar(32),
Grade int
)

一:查詢表中的列和行

1:查詢?nèi)w學(xué)生的學(xué)號與姓名

select sno,sname from student

2:查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。

select sno,sname,sdept from student

3:查詢?nèi)w學(xué)生的詳細(xì)記錄

select * from student

4:查詢?nèi)w學(xué)生的姓名及出生年份

select sname,DATEPART(yy, GETDATE()) - sage + 1 from student （SQLServer）

5:查詢?nèi)w學(xué)生的姓名,出生年份及所在系,要用小寫字母表示系名

select sname,DATEPART(yy, GETDATE()) - sage + 1,lower(sdept) from student

6:查詢選修了課程的學(xué)生學(xué)號
select sno,cno from sc

7:查詢選修了課程的學(xué)生姓名

select distinct sname from student,sc where student.sno=sc.sno

二:條件查詢:

常用的查詢條件

查詢條件謂詞
比較=,<,>,>=,<=,!=,<>,!>,!<;
not+上述比較運(yùn)算符
確定范圍Between and,Not between And,
確定集合IN,not IN
字符匹配Like,Not Like
空值IsNull,ISNOTNULL
多重條件AND,OR

1:查詢計算機(jī)系全體學(xué)生的姓名

select sname from student where sdept=”CS”

2:查詢所有年齡在20歲以下的學(xué)生姓名及其年齡

select sname,sage from student where sage<20

3:查詢考試成績有不及格的學(xué)生的學(xué)號

select sno from sc where grade<60

4:查詢年齡在20到23間的學(xué)生的姓名,系別及年齡

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

5: 查詢年齡不在20到23間的學(xué)生的姓名,系別及年齡

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

6:查詢信息系(IS),數(shù)學(xué)系(MA)和計算機(jī)系(CS)學(xué)生的姓名和性別

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

7:查詢不是信息系(IS),數(shù)學(xué)系(MA)和計算機(jī)系(CS)學(xué)生的姓名和性別

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

8:查詢學(xué)號為”95001”的學(xué)生詳細(xì)情況

select * from student where sno=95001

9:查詢所有姓劉的學(xué)生的姓名,學(xué)號和性別(where name like ‘劉%’)

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

10:查詢姓”歐陽”且命名為三個漢字的學(xué)生的姓名

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

11:查詢名字中第2個字為”陽”字的學(xué)生姓名和學(xué)號(where sname like '_陽%')

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

12:查詢所有不姓劉的學(xué)生姓名

select sname from student where sname not like '劉%'

13:查詢DB_Design課程的課程號和學(xué)分(where cname like 'Db\_Design' Escape'\')

select cno,gredit from course where cname like 'Db\_Design' Escape '\'

14:查詢以”DB_”開頭,且倒數(shù)第3個字符為i的課程的詳細(xì)情況(where cname like ‘DB\_%i__’escape’\’)
‘DB\_%i__’escape’\’)

select cno,gredit from course where cname like ‘Db\_%i__’escape’\’

15:查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號(where grade is not null)

select sno,cno from sc where grade is null

16:查詢有成績的學(xué)生學(xué)號和課程號

select sno,cno from sc where grade is not null

17:查詢計算機(jī)系年齡在20歲以下的學(xué)生姓名

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

18:查詢選修了3號課程的學(xué)生的學(xué)號及其成績,分?jǐn)?shù)降序排列

select student.sno,grade from student,sc

where student.sno=sc.sno and sc.cno=3 order by grade desc

19:查詢?nèi)w學(xué)生情況,結(jié)果按所在系的號升序排列,同一系中的學(xué)生按年齡降序

select * from student order by sdept,sage desc

三:使用集函數(shù)

count,sum,avg,max,min

1:查詢學(xué)生的總?cè)藬?shù)

select count(sno) from student

2:查詢選修了課程的學(xué)生人數(shù)(select count(distinct sno))

select count(distinct sno) from SC

3:計算1號課程的學(xué)生平均成績

select avg(grade) from SC where cno='1'

4:查詢選修1號課程的學(xué)生最高分?jǐn)?shù)

select max(grade) from SC where cno='1'

5:求各個課程號及相應(yīng)的選課人數(shù)

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

6:查詢選修了3門以上的課程的學(xué)生學(xué)號

select sno
from sc
group by sno

having count(*)>3

四:連接查詢:

<1>等值與非等值的連接查詢
在連接查詢中用來連接兩個有的條件稱為連接條件或連接謂詞,,當(dāng)連接運(yùn)算符號為”=”時,稱為等值連接,使用如,=,<,>,<=,>=,!=連接時稱非等值連接

1:查詢每個學(xué)生及其選修課程的情況

select student.*,sc.*
from student,sc
where student.sno=sc.sno

<2>自身連接

連接操作在同一個表中進(jìn)行連接查詢

2:查詢每一門課的間接先修課(即先修課的先修課)

select first .cno,second.cpno
from course first ,course second
where first.cpno=second.cno

五：復(fù)合條件連接

1:查詢選修2號課程且成績在90分以上的所有學(xué)生。

Select student,sname
form student, sc
Where student.sno=sc.sno And
Sc.cno=’2’ and sc.grade>90

六：嵌套查詢

1：帶有謂詞in的子查詢

<1>查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生

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

或：

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

<2>查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名

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

select sno,sname
   from student,sc,course
  where student.sno=sc.sno and
       sc.cno=course.cno and
       course.cname='信息系統(tǒng)')

2:帶有Any 或all謂詞的子查詢

<1>查詢其他系中比信息系中某一學(xué)生年齡小的學(xué)生姓名和年齡

select sname, sage
from student
where sage <any(select  sage
       from student
where sdept=’is’)
and sdept<>’is’

或用集函數(shù)：

select sname, sage
from student
where sage<
(select max(sage)
from student
where sdept=’is’)
and sdept<>’is’

<2> 查詢其他系中比信息系所有學(xué)生年齡都小的學(xué)生姓名及年齡

select sname, sage
from student
where sage<all
        (select sage
        from student
       where sdept=’is’)
    and sdept<>’is’

3 帶有Exitst謂詞的子查詢
<1>查詢所有選修了1號課程的學(xué)生姓名

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

<2>查詢沒有選修1號課程的學(xué)生姓名
select sname
form student
where not exists
          (select *
            form sc
             where sno=stuedent.sno and cno=’1’)

<2>查詢選修所有全部課程的學(xué)生姓名

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

<3>查詢只選修了學(xué)生95002選修的全部課程的一部分的學(xué)生號碼

select distinct sno
from sc scx
where not exists
       ( select *
          from sc scy
           where scy.sno=’95002’ and
              not exists
                  ( select *
                     from sc scz
                      where scz.sno=scx.sno and
                         scz.cno=scy.cno)

       )

二：

題一：表Ａ數(shù)據(jù)如下：

FYear FNum
2006  1
2006  2
2006  3
2007  4
2007  5
2007  6

按如下格式顯示：

年度  2006  2007
匯總　6     15

方案一：
create table 表名(FID varchar(10), Field1 varchar(100))
go

insert into 表名 select 1,'A'
insert into 表名 select 1,'B'
insert into 表名 select 1,'C'
insert into 表名 select 2,'D'
insert into 表名 select 2,'E'
insert into 表名 select 2,'F'
go

--創(chuàng)建一個合并的函數(shù)
create function f_merge(@name varchar(100))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(Field1 as varchar(100)) from 表名 where FID = @name
  set @str = stuff(@str , 1,1,'')
  return(@str)
End
go

--select * from 表名

--調(diào)用自定義函數(shù)得到結(jié)果：
select FID ,dbo.f_merge(FID) as tel from 表名 group by FID

drop table 表名
drop function f_merge


方案二：
select '匯總' as  年度
,[2006],[2007]
from
(select fyear,fnum from T)as sourceTable
pivot
(
sum(fnum)
for fyear in ([2006],[2007])
)
as pivotTable

回頭發(fā)現(xiàn)可以用SQL2005 pivot 的方法很簡單

題二：
表A數(shù)據(jù)如下：
FID  Field1
1    A
1    B
1    C
2    D
2    E
2    F
要求按如下格式顯示：
FID  Field1
1    A,B,C
2    D,E,F 
如何做到?

create table 表名(FID varchar(10), Field1 varchar(100))
go

insert into 表名 select 1,'A'
insert into 表名 select 1,'B'
insert into 表名 select 1,'C'
insert into 表名 select 2,'D'
insert into 表名 select 2,'E'
insert into 表名 select 2,'F'
go

--創(chuàng)建一個合并的函數(shù)
create function f_merge(@name varchar(100))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(Field1 as varchar(100)) from 表名 where FID = @name
  set @str = stuff(@str , 1,1,'')
  return(@str)
End
go

--select * from 表名

--調(diào)用自定義函數(shù)得到結(jié)果：
select FID ,dbo.f_merge(FID) as tel from 表名 group by FID

drop table 表名
drop function f_merge