黑马程序员--表的练习

------------------ Windows Phones 7手机开发、.Net培训、期待与您交流！ ----------------------

--创建表的语句
CREATE TABLE [dbo].[MyStudent](
 [Fid] [int] IDENTITY(1,1) NOT NULL,
 [FName] [nvarchar](10) NULL,
 [FAge] [int] NULL,
 [FGender] [char](2) NULL,
 [FMath] [int] NULL,
 [FEnglish] [int] NULL,
 [FClassId] [int] NULL,
 [FBirthday] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
 [Fid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET IDENTITY_INSERT mystudent ON
Go

insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 987         ,    N'张雁凡'                                                                                                                                                                                                                                                           ,    79          ,    '女'                                                                                                                                                                                                                                                              ,    NULL        ,    138         ,    1           ,    '1996-09-11 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 988         ,    N'吕亦竹'                                                                                                                                                                                                                                                           ,    62          ,    '女'                                                                                                                                                                                                                                                              ,    65          ,    31          ,    1           ,    '1987-01-22 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 989         ,    N'水子默'                                                                                                                                                                                                                                                           ,    27          ,    '男'                                                                                                                                                                                                                                                              ,    67          ,    49          ,    2           ,    '1996-05-10 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 990         ,    N'房子默'                                                                                                                                                                                                                                                           ,    19          ,    '男'                                                                                                                                                                                                                                                              ,    124         ,    132         ,    2           ,    '1994-07-19 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 991         ,    N'经文博'                                                                                                                                                                                                                                                           ,    39          ,    '男'                                                                                                                                                                                                                                                              ,    121         ,    19          ,    3           ,    '1995-07-05 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 992         ,    N'马若云'                                                                                                                                                                                                                                                           ,    71          ,    '女'                                                                                                                                                                                                                                                              ,    12          ,    111         ,    2           ,    '1989-01-24 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 993         ,    N'滑晟睿'                                                                                                                                                                                                                                                           ,    89          ,    '男'                                                                                                                                                                                                                                                              ,    32          ,    102         ,    2           ,    '1999-02-25 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 994         ,    N'毛哲瀚'                                                                                                                                                                                                                                                           ,    12          ,    '男'                                                                                                                                                                                                                                                              ,    119         ,    30          ,    3           ,    '1991-10-16 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 995         ,    N'计楷瑞'                                                                                                                                                                                                                                                           ,    21          ,    '男'                                                                                                                                                                                                                                                              ,    45          ,    77          ,    1           ,    '1991-10-07 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 996         ,    N'余寻冬'                                                                                                                                                                                                                                                           ,    17          ,    '女'                                                                                                                                                                                                                                                              ,    111         ,    104         ,    3           ,    '1993-07-17 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 997         ,    N'张绮琴'                                                                                                                                                                                                                                                           ,    29          ,    '女'                                                                                                                                                                                                                                                              ,    NULL        ,    128         ,    2           ,    '1986-08-25 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 998         ,    N'庄初曼'                                                                                                                                                                                                                                                           ,    27          ,    '女'                                                                                                                                                                                                                                                              ,    129         ,    7           ,    3           ,    '1990-05-12 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 999         ,    N'张雅容'                                                                                                                                                                                                                                                           ,    96          ,    '女'                                                                                                                                                                                                                                                              ,    NULL        ,    115         ,    3           ,    '1994-02-14 00:00:00.000' )
insert mystudent(Fid,FName,FAge,FGender,FMath,FEnglish,FClassId,FBirthday) values( 1000        ,    N'巴瑾瑜'                                                                                                                                                                                                                                                           ,    67          ,    '男'                                                                                                                                                                                                                                                              ,    28          ,    147         ,    1           ,    '1984-10-15 00:00:00.000' )
Go
SET IDENTITY_INSERT mystudent OFF
Go

====================================================================================================
显示表的所有内容

select*from  MyStudent

显示按英语成绩降序排列

select * from MyStudent order by fmath

按班级分组并且显示大于4的

select fclassid,count(*) from MyStudent group by fclassid
having count(*)>4

删除表中的一列

delete from MyStudent where fname='吕亦竹'

------------------ Windows Phones 7手机开发、.Net培训、期待与您交流！ ----------------------