MSSQL中的select语句复习

Masion · 发表于 2015-8-25 19:06:16

随着项目越来越多，突然发现select语句基础不太好，很多时候需要百度翻资料。
我特意做了一个学生选课的示例数据库表，把一些经典的select语句写进来，用于以后查阅

数据库里包含三个表 student，class和score三个表

建表语句如下：

//创建学生信息表
create table student (
fStudentNo varchar(100),
fName varchar(100),
fSex varchar(100),
fBirthDate datetime,
fCellphone varchar(100)
)
//插入学生信息记录
insert into student(fStudentNo,fName,fSex,fBirthdate,fCellphone)
select 'H001','杜成瑜','女','1970-2-20','13458177275' union
select 'H002','孙杨','男','1970-9-17','13866940214' union
select 'H003','刘禹歆','男','1970-11-3','13732104892' union
select 'H004','徐雪媛','女','1971-4-1','13527485441' union
select 'H005','谢莹','男','1971-5-22','13921260310' union
select 'H006','张丰薏','女','1971-8-27','13518975910' union
select 'H007','曲直','女','1971-10-10','13484380480' union
select 'H008','刘赢皓','男','1971-12-23','13203960372' union
select 'H009','刘松雨','男','1972-3-20','13230406859' union
select 'H010','于美华','女','1972-4-10','13235222827' union
select 'H011','吕林','男','1972-6-8','13616797867' union
select 'H012','赵帅淇','女','1973-3-12','13212267348' union
select 'H013','徐镜涵','男','1973-7-17','13357326953' union
select 'H014','洪景润','男','1973-8-21','13643383312' union
select 'H015','陈海瑞','女','1973-9-1','13496373036' union
select 'H016','刘丽欣','女','1974-3-6','13789398804' union
select 'H017','王雨轩','男','1974-3-27','13925781459' union
select 'H018','尚昱君','男','1974-4-2','13906254449' union
select 'H019','杨家宝','男','1974-4-12','13598980477' union
select 'H020','刘畅','男','1974-11-14','13511057278' union
select 'H021','单文韬','男','1974-11-19','13643535428' union
select 'H022','高尚','男','1975-1-6','13925870144' union
select 'H023','李天聪','女','1975-1-12','13822213580' union
select 'H024','邵春瑞','女','1975-1-20','13745478133' union
select 'H025','杜佳宁','男','1975-3-7','13454816082' union
select 'H026','杨惠中','女','1975-4-12','13282350919' union
select 'H027','殷睿','男','1975-7-26','13763739320' union
select 'H028','甄奇','女','1975-8-17','13663578216' union
select 'H029','文渝涵','男','1975-8-19','13419918185' union
select 'H030','王天艺','男','1975-9-25','13437870324' union
select 'H031','陈旭','男','1975-9-25','13249497196' union
select 'H032','石博宇','男','1975-9-26','13538604532' union
select 'H033','陈俊儒','男','1975-11-1','13184663223' union
select 'H034','董书言','男','1975-12-14','13545741556' union
select 'H035','杨家欣','女','1976-1-20','13671325703' union
select 'H036','邢立营','男','1976-1-28','13722584029' union
select 'H037','杨博涵','女','1976-2-4','13561244024' union
select 'H038','王新智','男','1976-5-9','13332077968' union
select 'H039','徐仕文','男','1976-5-11','13527001463' union
select 'H040','薛宇彤','女','1976-6-8','13823946719' union
select 'H041','单雪晴','女','1976-8-26','13814783675' union
select 'H042','王宏钰','男','1977-2-13','13427865669' union
select 'H043','崔雨蒙','女','1977-3-2','13324575802' union
select 'H044','王棋','女','1977-3-15','13575912690' union
select 'H045','刘敬博','男','1977-4-7','13751970800' union
select 'H046','卢柄任','男','1977-7-1','13473676012' union
select 'H047','于林名','男','1977-9-11','13683016568' union
select 'H048','文一鸣','女','1978-1-27','13929829856' union
select 'H049','赵天宁','男','1978-2-11','13918417992' union
select 'H050','陈杞洋','男','1978-2-19','13747990571' union
select 'H051','苏扬','男','1978-2-23','13758902379' union
select 'H052','郭文博','男','1978-8-22','13455137997' union
select 'H053','毛悦群','男','1978-12-28','13362291100' union
select 'H054','田欣慧','女','1979-1-27','13855314506' union
select 'H055','李文超','男','1979-3-22','13887640441' union
select 'H056','隋永凯','男','1979-9-14','13551849956' union
select 'H057','孙柳絮','女','1979-10-6','13693523077' union
select 'H058','董璇','女','1980-1-17','13804916876' union
select 'H059','聂子琪','男','1980-2-9','13601471515' union
select 'H060','毕彦澎','男','1980-6-6','13385397776' union
select 'H061','李萱琦','女','1980-9-8','13762960637' union
select 'H062','焦浩轩','男','1980-12-18','13727177715'

复制代码

Masion · 发表于 2015-8-25 19:13:16

消除结果集中重复的行，需要在select后面加上distinct
如：查询所有被选修课程

select distinct fClassNo from score

Masion · 发表于 2015-8-25 19:07:44

/创建课程信息表
create table class (
fClassNo varchar(100),
fClassName varchar(100),
fTeacher varchar(100),
fPreClass varchar(100)
)
//插入课程信息
insert into class(fClassNo,fClassName,fTeacher,fPreClass)
select 'C01','语文','戴宗玉','' union
select 'C02','数学','刘庆辉','C01' union
select 'C03','英语','张福奎','C01' union
select 'C04','物理','李瑞福','C02' union
select 'C05','化学','张凤丽','C04'
//创建成绩表
create table score (
fClassNo varchar(100),
fStudentNo varchar(100),
fScore integer
)

复制代码

Masion · 发表于 2015-8-25 19:08:17

//插入成绩信息
insert into score(fStudentNo,fClassNo,fScore)
select 'H001','C01',85 union
select 'H002','C01',132 union
select 'H003','C01',110 union
select 'H004','C01',143 union
select 'H005','C01',118 union
select 'H006','C01',108 union
select 'H007','C01',149 union
select 'H008','C01',84 union
select 'H009','C01',72 union
select 'H010','C01',89 union
select 'H011','C01',78 union
select 'H012','C01',102 union
select 'H013','C01',93 union
select 'H014','C01',126 union
select 'H015','C01',111 union
select 'H016','C01',92 union
select 'H017','C01',136 union
select 'H018','C01',94 union
select 'H019','C01',142 union
select 'H020','C01',114 union
select 'H021','C01',110 union
select 'H022','C01',99 union
select 'H023','C01',80 union
select 'H024','C01',135 union
select 'H025','C01',83 union
select 'H026','C01',94 union
select 'H027','C01',90 union
select 'H028','C01',125 union
select 'H029','C01',94 union
select 'H030','C01',144 union
select 'H031','C01',141 union
select 'H032','C01',90 union
select 'H033','C01',90 union
select 'H034','C01',101 union
select 'H035','C01',71 union
select 'H036','C01',118 union
select 'H037','C01',116 union
select 'H038','C01',100 union
select 'H039','C01',98 union
select 'H040','C01',140 union
select 'H041','C01',108 union
select 'H042','C01',115 union
select 'H043','C01',117 union
select 'H044','C01',106 union
select 'H045','C01',93 union
select 'H046','C01',112 union
select 'H047','C01',129 union
select 'H048','C01',128 union
select 'H049','C01',114 union
select 'H050','C01',96 union
select 'H051','C01',77 union
select 'H052','C01',108 union
select 'H053','C01',85 union
select 'H054','C01',76 union
select 'H055','C01',148 union
select 'H056','C01',105 union
select 'H057','C01',149 union
select 'H058','C01',133 union
select 'H059','C01',116 union
select 'H060','C01',77 union
select 'H061','C01',120 union
select 'H062','C01',79 union
select 'H001','C02',87 union
select 'H002','C02',97 union
select 'H003','C02',81 union
select 'H004','C02',94 union
select 'H005','C02',122 union
select 'H006','C02',113 union
select 'H007','C02',133 union
select 'H008','C02',79 union
select 'H009','C02',140 union
select 'H010','C02',76 union
select 'H011','C02',147 union
select 'H012','C02',132 union
select 'H013','C02',94 union
select 'H014','C02',81 union
select 'H015','C02',69 union
select 'H016','C02',140 union
select 'H017','C02',127 union
select 'H018','C02',139 union
select 'H019','C02',128 union
select 'H020','C02',73 union
select 'H021','C02',81 union
select 'H022','C02',122 union
select 'H023','C02',88 union
select 'H024','C02',85 union
select 'H025','C02',94 union
select 'H026','C02',97 union
select 'H027','C02',111 union
select 'H028','C02',79 union
select 'H029','C02',78 union
select 'H030','C02',125 union
select 'H031','C02',147 union
select 'H032','C02',150 union
select 'H033','C02',101 union
select 'H034','C02',115 union
select 'H035','C02',108 union
select 'H036','C02',135 union
select 'H037','C02',94 union
select 'H038','C02',104 union
select 'H039','C02',77 union
select 'H040','C02',141 union
select 'H041','C02',147 union
select 'H042','C02',131 union
select 'H043','C02',117 union
select 'H044','C02',126 union
select 'H045','C02',80 union
select 'H046','C02',112 union
select 'H047','C02',127 union
select 'H048','C02',123 union
select 'H049','C02',115 union
select 'H050','C02',99 union
select 'H051','C02',135 union
select 'H052','C02',112 union
select 'H053','C02',144 union
select 'H054','C02',71 union
select 'H055','C02',127 union
select 'H056','C02',133 union
select 'H057','C02',149 union
select 'H058','C02',105 union
select 'H059','C02',103 union
select 'H060','C02',133 union
select 'H061','C02',142 union
select 'H062','C02',132 union
select 'H001','C03',69 union
select 'H002','C03',120 union
select 'H003','C03',84 union
select 'H004','C03',88 union
select 'H005','C03',125 union
select 'H006','C03',106 union
select 'H007','C03',107 union
select 'H008','C03',113 union
select 'H009','C03',140 union
select 'H010','C03',135 union
select 'H011','C03',116 union
select 'H012','C03',98 union
select 'H013','C03',96 union
select 'H014','C03',94 union
select 'H015','C03',90 union
select 'H016','C03',69 union
select 'H017','C03',104 union
select 'H018','C03',86 union
select 'H019','C03',98 union
select 'H020','C03',107 union
select 'H021','C03',144 union
select 'H022','C03',116 union
select 'H023','C03',143 union
select 'H024','C03',80 union
select 'H025','C03',123 union
select 'H026','C03',84 union
select 'H027','C03',122 union
select 'H028','C03',95 union
select 'H029','C03',143 union
select 'H030','C03',88 union
select 'H031','C03',71 union
select 'H032','C03',113 union
select 'H033','C03',72 union
select 'H034','C03',77 union
select 'H035','C03',79 union
select 'H036','C03',124 union
select 'H037','C03',139 union
select 'H038','C03',98 union
select 'H039','C03',90 union
select 'H040','C03',131 union
select 'H041','C03',126 union
select 'H042','C03',89 union
select 'H043','C03',122 union
select 'H044','C03',81 union
select 'H045','C03',86 union
select 'H046','C03',120 union
select 'H047','C03',129 union
select 'H048','C03',86 union
select 'H049','C03',83 union
select 'H050','C03',133 union
select 'H051','C03',126 union
select 'H052','C03',108 union
select 'H053','C03',122 union
select 'H054','C03',134 union
select 'H055','C03',78 union
select 'H056','C03',90 union
select 'H057','C03',150 union
select 'H058','C03',76 union
select 'H059','C03',106 union
select 'H060','C03',148 union
select 'H061','C03',114 union
select 'H062','C03',79 union
select 'H001','C04',147 union
select 'H002','C04',104 union
select 'H003','C04',82 union
select 'H004','C04',105 union
select 'H005','C04',143 union
select 'H006','C04',115 union
select 'H007','C04',146 union
select 'H008','C04',100 union
select 'H009','C04',70 union
select 'H010','C04',99 union
select 'H011','C04',126 union
select 'H012','C04',147 union
select 'H013','C04',97 union
select 'H014','C04',149 union
select 'H015','C04',95 union
select 'H016','C04',93 union
select 'H017','C04',71 union
select 'H018','C04',106 union
select 'H019','C04',124 union
select 'H020','C04',106 union
select 'H021','C04',98 union
select 'H022','C04',82 union
select 'H023','C04',86 union
select 'H024','C04',134 union
select 'H025','C04',110 union
select 'H026','C04',75 union
select 'H027','C04',73 union
select 'H028','C04',145 union
select 'H029','C04',96 union
select 'H030','C04',73 union
select 'H031','C04',97 union
select 'H032','C04',99 union
select 'H033','C04',143 union
select 'H034','C04',127 union
select 'H035','C04',104 union
select 'H036','C04',86 union
select 'H037','C04',77 union
select 'H038','C04',85 union
select 'H039','C04',117 union
select 'H040','C04',77 union
select 'H041','C04',139 union
select 'H042','C04',87 union
select 'H043','C04',139 union
select 'H044','C04',83 union
select 'H045','C04',86 union
select 'H046','C04',99 union
select 'H047','C04',99 union
select 'H048','C04',98 union
select 'H049','C04',71 union
select 'H050','C04',142 union
select 'H051','C04',125 union
select 'H052','C04',146 union
select 'H053','C04',95 union
select 'H054','C04',107 union
select 'H055','C04',121 union
select 'H056','C04',100 union
select 'H057','C04',74 union
select 'H058','C04',121 union
select 'H059','C04',137 union
select 'H060','C04',150 union
select 'H061','C04',72 union
select 'H062','C04',111 union
select 'H001','C05',145 union
select 'H002','C05',113 union
select 'H003','C05',109 union
select 'H004','C05',72 union
select 'H005','C05',90 union
select 'H006','C05',127 union
select 'H007','C05',81 union
select 'H008','C05',120 union
select 'H009','C05',148 union
select 'H010','C05',70 union
select 'H011','C05',127 union
select 'H012','C05',80 union
select 'H013','C05',98 union
select 'H014','C05',75 union
select 'H015','C05',119 union
select 'H016','C05',120 union
select 'H017','C05',116 union
select 'H018','C05',146 union
select 'H019','C05',89 union
select 'H020','C05',136 union
select 'H021','C05',112 union
select 'H022','C05',77 union
select 'H023','C05',120 union
select 'H024','C05',105 union
select 'H025','C05',111 union
select 'H026','C05',86 union
select 'H027','C05',129 union
select 'H028','C05',89 union
select 'H029','C05',88 union
select 'H030','C05',133 union
select 'H031','C05',93 union
select 'H032','C05',143 union
select 'H033','C05',127 union
select 'H034','C05',150 union
select 'H035','C05',80 union
select 'H036','C05',116 union
select 'H037','C05',69 union
select 'H038','C05',120 union
select 'H039','C05',134 union
select 'H040','C05',70 union
select 'H041','C05',110 union
select 'H042','C05',109 union
select 'H043','C05',91 union
select 'H044','C05',86 union
select 'H045','C05',101 union
select 'H046','C05',108 union
select 'H047','C05',74 union
select 'H048','C05',134 union
select 'H049','C05',104 union
select 'H050','C05',105 union
select 'H051','C05',86 union
select 'H052','C05',117 union
select 'H053','C05',116 union
select 'H054','C05',150 union
select 'H055','C05',133 union
select 'H056','C05',116 union
select 'H057','C05',121 union
select 'H058','C05',143 union
select 'H059','C05',132 union
select 'H060','C05',114 union
select 'H061','C05',122 union
select 'H062','C05',111

复制代码

		自动登录	找回密码
密码			注册

[分享] MSSQL中的select语句复习