sql基本查询语言

二 建立表

 1 数值型

  Int    float  

 2 日期时间型:datetime   如: 2012-02-28  17:28:31

  小日期型:  samlldatetime 如: 2012-02-28

 3 字符串:

nchar(n) :字长字符串(如定义姓名，性别，籍贯)

varchar(n): 变长，串的实际上长度就是串长（一般定义如

不方便事先确定长度的内容，如备注信息） 最长为1024

text: 长篇大文内容，如论坛的贴子内容， 最长2^31

4 bit型: 存放逻辑值true,false

5 binnary:二进制流数据. 所有的二进制流都可以

6 image：照片数据

 

一:表的结构的修改

1 表的新添字段

Alter table 表名  add新字段名类型[(宽度，小数位)]

例如：向学生表中添加一个身高字段3位宽,2位小数

alter table 学生表add身高numeric(3,2)

例：一次性向学生表中添加两个（多个）字段数学，英语(smallint)

alter table 学生表add 数学smallint, 英语smallint

2 删除表的已有字段

Altertable 表名 drop 字段名

注：删除一个字段名时，该字段列的值也删除了。

alter table 学生表drop column数学

注意：有些版本SQL必须指定column （列，卷）

例：一次性地删除两个字段:

alter table 学生表drop column英语,化学

3 修改已有字段的属性（可修改类型，宽度，小数位。不能修改字段名字）

Alter table表名alter/modify 字段名  新类型（新位数，新小数位）

例：把原表中的语文字段的类型改为小数，4位宽，1位小数.

 alter table学生表alter column语文numeric(4,1)

特别注意：如果人为地改变字段类型和缩小宽度，则可能造成数据的丢失，或精度的丢失(一般在应用中修改类型时，往往是向宽和增加精度方向修改)

4 修改已有字段的字段名字

高版本不直接支持，但可曲线方式实现

alter table 学生表add专业名称nchar(14)

update 学生表set专业名称=专业

alter table 学生表drop column专业

 

5 删除整个表

Drop  table 表名

 

二 数据记录的操作(数据操纵功能)

1 向表中插入新记录.

 Insert  into  表名(字段名)  values(值)

例：向学生表中添加一个人（00110001 ，张六二，19，四川成都

1.75,56,计算机应用)

insert into 学生表(学号,姓名,年龄,家庭住址,身高,语文,专业名称)values('00110001' ,'张六二',19,'四川成都',1.75,56,'计算机应用')

说明：如果表以前已经建立过PK（主码）而且对这个主码作过“索引”—排序，则新插入的新记录不会在表最后面，而是会自动根据索引排序来决定新记录的真正位置的。

 

例：向学生表添加一个生日字段，插入数据

（00110002，李六，1988-12-21）

 

alter table 学生表add生日smalldatetime

 

insert into 学生表(学号,姓名,生日)

 values('00110003','李六','1988-12-21')

注意：日期在输入时当串对待的。而且日期实际上计算机当成一个整数对待的，表示今天是离1900-1-1间隔的天数.

例：向表中添加一个团员否字段，bit型

插入一个新人（'00110004','李七',1）

Altertable 学生表add团员否 bit

insert into 学生表(学号,姓名,团员否) VALUES('00110004','李七',1)

说明：bit叫位型，一个二进制位,只能存放1或0, 这种数据又叫逻辑型.在数据显示时不会显示成1,0 ，要分别显示为True, False

例：当表中全部列要参与值插入时，字段名可以省掉.

insert into 学生表

 VALUES('00110006','李九',18,'成都双流',1.71,67,'化学','1978-1-5',0)

此时，默认把后面的值一个一个对应向表中填写，故所给值的顺序与个数及类型必须与表的字段一致

 

 

2 删除记录

Deletefrom 表名[where条件]

delete from 学生表where 年龄<17

删除表中所有17岁以下的人

 

例：删除学生表中19以下，且姓李的所有人

delete from 学生表where年龄<19and left(姓名,1)='李'

 

Select 数据项 from表[where 条件]

数据项可以是常量,变量,表达式,表的字段名,可以是多个

表可是一个或多个(如果为多表,则是表间操作)

1 数据项:

select 5+3

declare @x int  //定义一个整型变量x

set @x=100  //给x赋值

select @x+2  //查询时使用变量x

select GETDATE()+100  //求今天后100是哪一天

select year(GETDATE())  //取出今天的年份 

select 姓名, year(GETDATE())-YEAR(出生日期) from 学生

// 求出学生表中所有人的姓名和年龄

update 学生 set年龄=YEAR(GETDATE())-YEAR(出生日期)

//计算学生表中所有人的年龄并填充到年龄字段中

设学生表中的出生日期字段,选出生日是今天的人的姓名

Select 姓名

select 姓名 from学生 whereMONTH(出生日期)=month(getdate())  and DAY(出生日期)=day(getdate())

例:求张立比李勇大多少岁?

declare @x int

declare @y int

select@x=year(getdate())-year(出生日期) from学生 where 姓名='张立'

select@y=year(getdate())-year(出生日期) from学生 where 姓名='李勇'

select @x-@y

例:求学生全班人的平均年龄是多大?

declare @pj float

select@pj=avg(year(getdate())-year(出生日期)) from学生

select '平均年龄是:', @pj

select '欢迎您的到来,今天是:',getdate()

P41 第四章 检索数据(数据查询)

Select 数据项 from表[where 条件]

Where 条件:结果是逻辑值都可以作为条件,如果为真,表示该记录

参与操作,为假则不操作.

 A AND B: 逻辑与运算,表示只有,A,B同时成立时,结果才为真

 A or b: 逻辑或运算,表示只要a,b中至少有一个成立时,结果就成立

 Not a: 逻辑非运算,表示求A的反条件

A nor b:逻辑异或.相异则成立

Select 数据项 from表[where 条件] [order by 列名][asc/desc]

说明:order by 列名: 表示查询的结果按指定的列排序,默认为ASC升序, 如果要降序则加上desc.

select 姓名 from学生order by 姓名 desc

排序依据:

(1)字符串:按拼音顺序(新华字典顺序)

(2)数值:按数学大小顺序排

select 姓名,身高 from 学生 order by 身高 desc

(3)日期:是今天比昨天大.(跟年龄相反)

select 姓名,身高,出生日期 from 学生 order by 出生日期  desc  //按出生日期降序排(年龄越大则在上面)

select top 3 姓名,身高,出生日期 from 学生 order by 出生日期  desc

//取年龄最大的前三名

select top 50 percent  姓名,身高,出生日期 from 学生 order by 出生日期  desc

 求年龄较大的一半人的情况,50%

(4)逻辑值:真比假大

select  姓名,身高,出生日期,党员否 from 学生 order by 党员否  //按党员否逻辑值排序

注意事项:

1 * 可代表所有字段列:

select * from 学生 order by党员否

附: select 姓名+性别 from 学生 //字段名是以拼凑的

Select 姓名,语文+数学+英语 from 成绩  // 求每个人的总成绩

 张三  215

 李四  276

2 as 关键字:给查询值或表另取一个名字(别名).

select 姓名,年龄+10 as 十年后年龄 from 学生

select 姓名,年龄+10 十年后年龄  from 学生

//给 年龄+10的计算结果另取名字”十年后年龄”.注:as 是可以省掉的.

Select 姓名,语文+数学+英语 as 总分 from 成绩

 

补:用select 查询的结果去向表中填充数据.

insert into 学生(学号,姓名,性别) select 学号,姓名,性别 from学生 where 性别='男'

 

13 新建两个表”学生1”,学生2,,把学生表中的所有男生复制到学生1表中;

把所有女生团员复制到学生2表中

答案:

Create table 学生1(学号nchar(5),姓名 nchar(6))

Create table 学生2(学号nchar(5),姓名 nchar(6))

Insert into 学生1(学号,姓名) select 学号,姓名 from 学生 where 性别=’男’

Insert into 学生2(学号,姓名) select 学号,姓名 from 学生 ;

where 性别=’女’and 团员否=1

如何把学生1,学生2两个表合并成学生3?

Create table 学生3(学号nchar(5),姓名 nchar(6))

Insert into 学生3 select * from学生1

Insert into 学生3 select * from学生2

一:查询时的一些控制杂项

1 distinct 可以控制查询结果没有重复值

例:查询出学生表中性别有几类

selectdistinct 性别 from学生

例:问学生表中有哪几种姓氏?

selectdistinct( LEFT(姓名,1)) as姓氏 from 学生

selectdistinct  LEFT(姓名,1)  as 姓氏 from 学生

例:问学生表中有几种姓氏?

select COUNT(distinct( LEFT(姓名,1))) as姓氏数 from 学生

补:groupby 字段列名

此子句功能是分组计算,统计数据.

例:统计学生表中的男女生的各自人数

Select 姓名 from学生  ‘显示出全班每个人的性别 8个男女

select count(性别) from学生’性别数就是人数

select 性别,count(性别) as 人数 from 学生 group by 性别

 按性别分组group by性别,每一组内要分别实现count( )运算.

例:统计学生表中党员和非党员各自人数

Select 党员否,count(党员否) from 学生 group by 党员否

例:问学生表中各种姓氏有几人?

createtable  cnt(姓氏nchar(2),人数 int)

insert intocnt(姓氏) select LEFT(姓名,1) as 姓氏 from 学生

select 姓氏,COUNT(姓氏) as 人数 from cnt group by 姓氏

drop tablecnt

为什么要利用中间表cnt? 因为group by 表内的字段名,而姓氏不是字段名(

姓名才是字段名),故把每个人的姓氏选出来存放到CNT临时表中,再对CNT临时

表来groupby 姓氏

 

二.查询条件的常用运算符

 

关系比较:= >  <  <>  !=  >=  <=

范围运算: [not] between A and B

   判断指定的值是否在A,B之间

例:查出身高在[1.70,1.80)的学生情况

select * from 学生 where身高between 1.70 and 1.80

select * from 学生 where身高>= 1.70 and 身高<=1.80

例:查出所以介于’王’和’张’之间的姓的人(汉字是以拼音顺序为准的)

select * from 学生 where姓名between '王' and '张'

select * from 学生 where姓名 notbetween '王' and '张'

集合运算: [not] in(集合) :等效于or逻辑运算

例:查出学生表中所有姓王,张,杨的人

select * from 学生 where left(姓名,1)='王' or LEFT(姓名,1)= '张';

 or LEFT(姓名,1)='杨'

select * from 学生 where left(姓名,1)in('王','张','杨')

例:查出成绩表中所有选了c01,c03,c05课程的学生成绩情况

select * from 成绩 where课程号in('c01','c03','c05')

例: 查出成绩表中所有选了c01,c03,c05课程的各自人数

select 课程号,COUNT(学号) as 人数 from 成绩 where ;

课程号in('c01','c03','c05') group by课程号

 

例:求成绩表中每个学生选了几门课?

select 学号,COUNT(*)as门数 from 成绩 group by 学号

 

例: 查出成绩表中选课门数在3门以上的学生姓名有哪能些?

select 学号,COUNT(*)as门数 from 成绩 group by 学号;

 having COUNT(*)>=3

注意:

(1)只要有了分组groupby ,而且使用查询统计函数(COUNT,AVG,SUM…)

 则每一个小组要分别统计计算.常用于分类汇总.

例:求学生表中男女生的各自平均成绩

select 性别,avg(语文) from 学生表 group by 性别

例:成绩表中每科的平均成绩.

select 课程号,AVG(成绩) as 平均 from 成绩表 group by 课程号

 

select 学号,SUM(成绩) from 成绩表 group by 学号

求每个学生生所有选课的总分

select 性别, max(身高) as 最高 from 学生表group by 性别

分别求男女生的最高

select 性别, max(身高) as 最高,MIN(身高) as 最低,AVG(身高) as 平均  from 学生表 group by 性别

一次性可多项统计.

例:求李小芳同学的所有选修课程的平均成绩

方法1

declare @xh nchar(11)

select @xh=学号 from学生表where 姓名='李四方'

select 学号,AVG(成绩) from 成绩表 where 学号=@xhgroup by 学号

 

方法2

declare @xh nchar(11)

declare @ms int

declare @s int

select @xh=学号 from学生表where 姓名='李四方'

select @ms=COUNT(*) from 成绩表 where 学号=@xh

select @s=SUM(成绩) from成绩表where 学号=@xh

select @s/@ms

方法3:

select 学号,AVG(成绩) from 成绩表 where 学号 =

(select 学号 from学生表 where 姓名='李四方')group by 学号

例:求出”计算机网络基础”课程的最高分

declare @kch nchar(3)

select @kch=课程号 from课程表where 名称='计算机网络基础'

select 课程号,MAX(成绩)from 成绩表 where ;

课程号=@kch group by 课程号

例:求李四方的大学英语成绩

declare @kchnchar(3)

declare @xhnchar(11)

select @kch=课程号 from课程表 where 名称='大学英语'

select @xh=学号 from学生表 where 姓名='李四方'

select 学号+'李四方'+课程号+':大学英语:'+;

CONVERT(nchar(5),成绩) from成绩表 where 课程号=@kch and 学号=@xh

说明:having  统计结果条件: 对分组统计后的结果进行条件限制.

(having不能对表中原始字段进行条件限制,因为表中字段的限制使用

Where 字段条件)

 

select 课程号,COUNT(*)from成绩表 group by 课程号;

 having COUNT(*)>6

 

例:求出成绩表中平均成绩在85以上的有哪些课程号?

 

select 课程号,AVG(成绩) from 成绩表 group by 课程号;

 having AVG(成绩)>=85

 

例:求学生表中姓氏在2人以内的有哪些姓氏?

create table cnt(姓氏 nchar(2))

insert into cnt select LEFT(姓名,1)from学生

select 姓氏,COUNT(姓氏) fromcnt group by 姓氏;

 having count(姓氏)<=2

drop table cnt

一 查询时的一些控制杂项

1 distinct 限制查询结果的重复值(常用于求各类数据)

例:求学生表中性别有哪几类

select distinct 性别from学生表

例：selectdistinct课程号from成绩表

查出所以被选课程号有哪些？

例：select distinct 学号from成绩表

查出哪些学生有选修课（不管一个人选了几门）

例：查出哪些学生（学生表的信息）选了课

select  * from 学生表where 学号in(select distinct 学号from 成绩表)

例：求出学生表中有哪几种姓氏？

select  distinct  left(姓名,1)  from  学生表

例：求出学生表不同姓氏多少种？

补count(字段名 ) 求出个数

select  count(distinct left(姓名,1)) from 学生表

例： 求出学生表中身高在1.75以上的人数

select count(学号) from 学生表where 身高>=1.75

例：求学生表中语文成绩在70—85之间的人数

 

select count(学号)from 学生表where 语文>=70 and 语文<=85

 

2 group by 字段名:分组统计，计算

  例： 求学生表中男女生各自人数.

select 性别, count(*) as人数from学生表 group by 性别

说明：分组后，每个组要分别求值统计

例：求出成绩表中每个学生选了几门课？

select 学号,count(课程号) from 成绩表group by 学号

例：求出成绩表中每门课程的选课人数？

select 课程号,count(课程号) as 人数from 成绩表groupby 课程号

例：求出成绩表中每个学生的重修门数（不及格的）

select 学号,count(课程号) as 不及格门数from 成绩表where成绩<60 group by 学号

例：求学生表中各种姓氏的人数

方法1:直接求解

select left(姓名,1) as姓氏, count(left(姓名,1)) as 人数from 学生表group by left(姓名,1)

方法2:间接使用中间临时表求解

create table tmp(姓氏nchar(2))

insert into tmp select left(姓名,1) from学生表

select 姓氏,count(姓氏) from tmp group by 姓氏

drop table tmp

查询条件常用运算

1关系运算：> >= <  <=  <>   !=   =

2SQL专用的范围运算: [not]between A  and  B

判断某值是否是介于A，B之间

select * from 学生表 where年龄>=18 and 年龄<=20

select * from 学生表 where年龄  between 18 and 20

select * from 学生表wherenot年龄between 18 and 20

3 集合运算  [not] in

  字段名 in (集合) ：判断指定值是否在给定的集合中

 例：查出学生表中姓王，张，杨的所有人

select * from 学生表where left(姓名,1)='王' or left(姓名,1)='李' or left(姓名,1)='杨'

select * from 学生表where left(姓名,1) in('王','李' ,'杨')

例：查出成绩表中选了101，103，104的选课情况

select * from 成绩表where课程号in ('101','103','104' )

例：查出成绩表中选了101，103，104的学生人数

select 课程号,count(*) as选课人数from 成绩表where 课程号in('101','103','104' ) group by 课程号

例：求101课程的各成绩分数段人数:90-100，80-89，70-79，60-69

0-59

alter table 成绩表add段int

update 成绩表set段=成绩/10

update 成绩表set段=5 where 段<6

select 段*10 as分数段,count(*) as 人数from 成绩表group by 段order by 人数desc

1 把学生表复制成”xs”表,对xs表进行结构操作练习

答:

Create table xs(参考学生表的字段)

Insert into xs  select * from 学生表

或者: select * into xs from学生表 where1<>1

Insert into xs select * from学生表

2 在xs表中添加新字段:婚否 bit  , 籍贯 字符(12), 高考成绩 (数值 5,1)

答: alter table xs add 婚否 bit  add 籍贯 nchar(12) add 高考成绩 numeric(5,1)

3 修改高考成绩字段为 数值 6,2

答: alter table xs alter 高考成绩 numeric(6,2)

4 想办法实现把婚否字段更名为”是否已婚”,如果原表中已经有大量数据,不得丢失数据(可能需要多步实现)

答:

Altertable xs add 是否已婚 bit

Updatexs set 是否已婚=婚否

Altertable xs drop column  婚否

二   简单查询操作

1 从学生表中查询出所有学生的学号和生日

答: select 学号,生日 from 学生表

2 从课程表中查询出所有记录的所有字段的数据

答:select * from 课程表

3 从成绩表中查询出101号课程的成绩在>=60的记录情况

答: select * from 成绩表 where 课程号=’101’ and 成绩>=60

4 从学生表中查出所有1989年以后出生的男生情况

答: select * from 学生表 where  YEAR(生日)>=1989 and性别='男'

select* from 学生表 where生日>='1989-1-1'and 性别='男'

补:

select * from 学生表 where 生日>=1989-1-1 and 性别='男'

 select CONVERT(int, GETDATE()- 生日) from 学生表

‘测试出每个人已经存活的天数

5 从学生表中查出学号,姓名,身高, 且结果中的身高 列重命列名为”高度”

答: select 学号,姓名,身高 as 高度 from 学生表

select学号,姓名,身高   高度 from 学生表

6 查出成绩表中有哪些同学选了课

答: select distinct 学号 from 成绩表

select 学号,姓名 from 学生表 where 学号 in(select distinct 学号 from 成绩表)

select 学号,姓名 from 学生表 where 学号 in(select   学号from 成绩表)

7 查出成绩表中有哪些被选课程

答:  select distinct 课程号 from 成绩表

select * from 课程表 where课程号 in(selectdistinct 课程号 from 成绩表)

8 查出成绩表中101和103课程成绩在70以上的情况

答: select * from 成绩表 where 课程号in('101','103') and 成绩>=70

select * from 成绩表 where (课程号='101'or 课程号= '103') and 成绩>=70

9从课程表中查询出课程名中含有“计算机”三字的情况

答:

select * from 课程表 where LEFT(名称,3)='计算机'

select * from 课程表 where名称 like '%计算机%'

注:在比较串时, %代表任意的字符串,包括空串

10 查出成绩表中的学号和每个人的所有课程的平均分,单人最高成绩

答:

select 学号,AVG(成绩) as  平均成绩,MAX(成绩) from 成绩表 group by 学号

select 学号,AVG(成绩) as  平均成绩,MAX(成绩) from 成绩表 as 单人最高group by 学号 order by 平均成绩 desc

注: order by 2 desc    ( 按查询的结果的第2列排序)

11 在学生表中把李四方的生日提前10天,且身高增加10厘米

答:

update 学生表 set生日=生日-10,身高=身高+0.1 where 姓名='李四方'

三、 复杂查询

1 查出学生表中语文成绩最高前四名

答: select top 4 * from 学生表 order by 语文 desc

注: 如果有多个人满足条件,如最高的4个值成绩中,77最低的,但77有两个人,如何去留? 一般使用第二,第三字段来限制重复

如: select top 4 * from 学生表 order by 语文 desc,身高 desc

如果语文相同,则按身高来排

2 查出学生表中语文成绩较差的50%

答: select top 50 percent * from 学生表 order by 语文

3  查询出所有成都地区的学生的学号,姓名,性别,生日

答: select * from 学生表 where 家庭住址 like '%成都%'

4 查出”大学英语”和”高等数学”两门课程的成绩情况:学号,课程号,成绩(注意课程表中的高等数学要分上下,如何处理?)

答:

select * from 成绩表 where课程号 in(select 课程号 from 课程表  where 名称 in('大学英语','高等数学上','高等数学下'))

 

select * from 成绩表 where课程号 in (‘105,’106’,’107’)

5从成绩表中查询出成绩在85和95之间的人的情况（between … and …的用法）

答: select * from 成绩表 where 成绩 between 85 and 95

6 查询成绩表,结果先按课程号升序排序，同一门课程的内部再按成绩降序排列

答: select * from 成绩表 order by 课程号,成绩 desc

7 在 学生表中根据性别进行分类,分别求男女生各自语文和身高的平均值

答: select 性别,AVG(语文),AVG(身高) from 学生表 group by 性别

8 查出成绩表中哪些课程选课人数>=5

答:

select 课程号,COUNT(*) as人数from 成绩表

   group by 课程号

    having COUNT(*)>=5

9 统计成绩表中各个学生选课门数

答: select 学号,COUNT(*) as 门数 from 成绩表

group by 学号

四 综合应用:

0 想办法实现:把学生表中的每个人的性别互换(即原男换成女,原女换成男)

答:

alter table 学生表 add bz int

UPDATE 学生表 set bz=1 where性别='男'

UPDATE 学生表 set bz=2 where性别='女'

update 学生表 set性别='女' where bz=1

update 学生表 set性别='男' where bz=2

alter table 学生表 drop column bz

1 新建两个表”课程101”,”课程102”,表头结构与”成绩表”相同,然后从成绩表中分别把101选课和102课程选课情况复制到以上两个新表中

答: select * into课程 from成绩表 where1<>1

select * into 课程 from成绩表 where 1<>1

insert into 课程 select *from成绩表 where 课程号='101'

insert into 课程 select *from成绩表 where 课程号='102'

老师答案：方法1create 建立表的结构 ,再insertinto 新表 select … from 源表

create table 课程(学号 nchar(10),课程号 nchar(3),成绩 numeric(6,2)   )

insert into 课程select * from成绩表 where 课程号='101'

select * from课程

方法2: 使用into 新表名子句

select * into课程 from成绩表 where 课程号='102'

select * from课程

注:into 子句只能放在查询字段名和from 之间

2 学生表中有哪几种姓氏?

答: select distinct left(姓名,1) as姓氏 from 学生表

老师答案：select distinct LEFT(姓名,1) from 学生表

select distinct substring(姓名,1,1) from学生表

declare @s nchar(100)

set @s='abcdsafdafdsxxxxxyyyyzzfd'

select substring(@s,3,LEN(@s))//len是指取完整个字符串。

注: substring(s,n1,n2),如果n2超过有效范围,则表示从n1开始取完.

Left(s,3)=substring(s,1,3)

例:s1=”abcdefghi”

S2=’cde’

S3=’***’

（用s3替换s1中含有s2的内容的值）

3 学生表中有几种姓氏?

答: select count(distinct left(姓名,1))as共有姓氏数 from 学生表

老师答案：select count(distinct LEFT(姓名,1)) from 学生表

 

4 统计学生表中团员和非团员各自人数

答: select 团员否 ,count(*) as 人数from 学生表 group by 团员否

老师答案：select 团员否, COUNT(*) as 人数 from 学生表 group by 团员否

预习如何把团员否的0,1转换为非团员,团员

 

5 查出身高在[1.75,1.80)的学生情况

答: select * from学生表 where身高 between1.75 and 1.80

6 查出学生表中所有姓王,张,杨的人

答: select * from学生表 where LEFT(姓名,1)in ('王','杨','张')

老师答案：select * from 学生表 where left(姓名,1) in('王','张','杨')

举例：在网页注册时选择地址

SF（省份表）

江西  1

四川  2

湖南 3 

 

CI（城市表）

成都市  2

德阳    2

绵阳市  2

九江    1

南昌    1

 

7:查出成绩表中所有选了101,103,105课程的学生成绩情况

答:

老师答案：select * from 成绩表 where 课程号in('101','103','105')

      或者：select * from成绩表 where 课程号 like  '10[1,3,5]'

    注：[]中是表示一个字符，可以多个值并列，也可以一个值。

8: 查出成绩表中所有选了101,103,105课程的各自人数

答: select 课程号, COUNT( 学号)as 人数 from 成绩表 where 课程号      in ('101','103','105') group by 课程号

老师答案：select 课程号, COUNT(*) as 人数 from 成绩表

          where 课程号 in('101','103','105')  group by 课程号

          注：此时的in不能够换成come。

 9 查出成绩表中选课门数在3门以上的学生姓名有哪能些?

答:

老师答案：select 姓名 from 学生表

where 学号 in (select学号from 成绩表 group by 学号 havingCOUNT(*)>=3)

或者：select姓名 from 学生表

 where 学号 = some (select 学号 from 成绩表 group by 学号 having COUNT(*)>=3)

注：come后（）中是子查询，而in后（）中可以是子查询也可以是值。

10 求计算机基础和大学英语两门课程的成绩情况

答: select * from成绩表 where 课程号 in(select 课程号 from 课程表  where  名称 in ('计算机基础','大学英语') )

老师答案：select * from 成绩表 where 课程号in

(select 课程号 from课程表 where 名称 in('大学英语','计算机基础'))

11 在学生表中,求男生团员占全班人数的百分比是多大

答:

老师答案：declare @a int , @b float

select@a=COUNT(*) from 学生表 where性别='男' and 团员否=1

select@b=COUNT(*) from 学生表

selectCONVERT(nchar(4), @a/@b*100)+'%'

附:

select CONVERT(nchar(6),convert(numeric(5,2),@a/@b*100))+'%'

注:先把a/b转换为固定小数位(5,2),再转换为字符串

 

12         求李大方同学的所有选修课程的平均成绩(要求使用至少两种方法求解)

答:

老师答案：(1) declare @xh nchar(10)

select @xh=学号 from学生表 where 姓名='李大方'

select AVG(成绩) from成绩表 where 学号=@xh

(2)

select AVG(成绩) from成绩表 where 学号=

 (select 学号 from 学生表 where 姓名='李大方')

 

或者：

select 学号, AVG(成绩) from 成绩表 where 学号=

(select 学号 from学生表 where 姓名='李大方')group by 学号

 

13         求出”大学英语”课程的最高分(要求使用变量法)

答:

老师答案：方法1：

 declare @kch nchar(3)

select @kch=课程号 from课程表 where 名称='大学英语'

select MAX(成绩) from成绩表 where 课程号=@kch

方法2:

declare @kch nchar(3)

select @kch=课程号 from课程表 where 名称='大学英语'

select 课程号,MAX(成绩) from成绩表 where 课程号=@kch group by 课程号

select top 1 * from 成绩表where课程号=@kch order by 成绩 desc

方法3: 

declare @kch nchar(3),@cj float

select @kch=课程号 from课程表 where 名称='大学英语'

select @cj=MAX(成绩) from成绩表 where 课程号=@kch group by 课程号

select * from成绩表 where课程号=@kch and 成绩=@cj

 

14 求李大方的计算机基础的成绩

答:

老师答案：declare @kchnchar(3),@xh nchar(10)

select @kch=课程号 from课程表where 名称='计算机基础'

select @xh=学号 from学生表where 姓名='李大方'

select * from 成绩表 where课程号=@kchand 学号=@xh

 

15 求出成绩表中平均成绩在90以上的有哪些课(查询结果包括课程号,课程名称,学分)

答: select 姓名，学分 from 课程表

where 课程号 in (select课程号 from 成绩表 group by 课程号 having vag（成绩）>90)

 

第8周内容:

1 表的复制补充:一般有两种办法

(1)建立一新表结构,再insert into新表 select from 原表

(2)在高版本的sql中支持直接复制功能,如下

select 学号,姓名,性别intomy1from学生表 where性别='男'

into 新表名:子句只能放在select的字段名后面.

2 约束特例:如控制区号是三位或四位数字的方法:

create table test2(区号 varchar(5),constraint区号CHECK(区号 LIKE'[0-9][0-9][0-9]'or区号LIKE'[0-9][0-9][0-9][0-9]'),地区名char(10))

or

create tabletest4(区号varchar(5),地区名char(10)notnullunique,

CHECK (区号 LIKE'[0-9][0-9][0-9]'

or 区号LIKE'[0,1,2,3,4,5,6,7,8,9][0-9][0-9][0-9]' and len(地区名)>=3))

 

%  _  [ ]

select *from学生表where家庭住址like'[^四,成]%'

地址不以四或成开头的

[^….]表示不在指定的范围内

 

  % 可匹配任意的多个字符的串(包含空串)

 

  _ 可匹配任意的一个字符（必须是一个有效字符，不能是空串）

 3 top n

 

说明：如果n是常量可直接写出，如果n是变量，必须加括号

 

4 查出1988年２月出生的人

5 导入execl数据表

第九周  T_SQL编程

多表查询

1 内连接

 引例:

 查出选修了101课程的所有学生的学号,姓名

 select 学号,姓名 from 学生表 where学号in

(select学号from成绩表where 课程号='101')

注:先查询的成绩表,再查询学生表,故是单表操作

 

查出选修了101课程的所有学生的学号,姓名,成绩

   select 学生表.学号,姓名,成绩 from 学生表,成绩表 ‘错

 会生成两个表的笛卡尔积,无意义

 注:由于两个表中都有学号,故要指明学号来自于哪个表

  select 学生表.学号,姓名,成绩 from 学生表,成绩表

  where  学生表.学号=成绩表.学号  ‘对

 说明:“where 学生表.学号=成绩表.学号”表示两个表在操作时,强制按两表的学号相等来生成新记录查询结果

  例:使用成绩表和课程表查询出:学号,课程号,名称,成绩

select 学号,成绩表.课程号,名称,成绩from成绩表,课程表

 where 成绩表.课程号=课程表.课程号

 

补: 对查询结果进行判断后选择性地输出结果,要用条件多分支

   Case 表达式值

      When 匹配值1  then 结果值1

      When 匹配值2  then 结果值2

      When 匹配值3  then 结果值3

      ……

      [else  结果值其它]

  End

 

declare @nint,@snchar(10)

set @n=7  ‘根据n来判断结果

select @s=case@n

              when 1 then '你操作的是第个情况'

              when 2 then '你选的是第个情况'

              when 3 then '第种情况'

              else '其它情况'

           end

print @s

declare  @s nchar(10)

select 学号,姓名,团员否,团员否=case团员否

                        when 1 then'团员'

                        when 0 then'非团员'

                       end

 from 学生表

例: 使用成绩表和课程表查询出:学号,课程号,名称,成绩,学分

 (要求本课成绩及格才能得到学分,否则学分为0)

select 学号,成绩表.课程号,名称,成绩,学分,

   实得学分=case CONVERT(int,成绩/100+0.4)

             when 1then 学分

             when 0then  0

            end

 from 成绩表,课程表

 where 成绩表.课程号 =课程表.课程号

 

select 学号,成绩表.课程号,名称,成绩,学分,

   实得学分=case round(成绩/100-0.1,0)

             when 1then 学分

             when 0then  0

            end

 from 成绩表,课程表

 where 成绩表.课程号 =课程表.课程号

 

(2)用 inner join实现内连接

  select学号,成绩表.课程号,名称,成绩,学分

 from 成绩表 innerjoin  课程表

on 成绩表.课程号 =课程表.课程号

 等效于以下:

select 学号,成绩表.课程号,名称,成绩,学分

 from 成绩表 , 课程表

    where 成绩表.课程号 =课程表.课程号

例:使用学生,成绩,课程,三个表查出数据:学号,姓名,课程号,名称,成绩

 方法1:

select 学生表.学号,姓名,成绩表.课程号,名称,成绩

from 学生表,成绩表,课程表

 where 学生表.学号=成绩表.学号 and 成绩表.课程号=课程表.课程号

方法2:

select a.学号,姓名,b.课程号,名称,成绩

  from 学生表 a,成绩表 b,课程表 c

    where a.学号=b.学号 and b.课程号=c.课程号

注:from表时取别名,以后可用别名代替原表名,达到简化书写目的

方法3:

select 学生表.学号,姓名,成绩表.课程号,名称,成绩

  from 学生表 innerjoin 成绩表 on 学生表.学号=成绩表.学号

     inner join 课程表 on 成绩表.课程号=课程表.课程号

 

以上所有方法叫内连接,根据两个表共同有关联字段值时来形成新查询结果了.

 

2 左连接

 select 学生表.学号,姓名,课程号 ,成绩

  from 学生表 leftjoin 成绩表 on 学生表.学号=成绩表.学号

 a left join b: 生成的结果以a表为准,如果b表中不存放相应的记录,则对应值填写为Null

 

3 右连接

 select 学生表.学号,姓名,课程号,成绩

 from 成绩表rightjoin学生表on 学生表.学号=成绩表.学号

  生成结果以右表为准

4 完全连接

 select 学生表.学号,姓名,课程号,成绩

 from 成绩表fulljoin学生表on 学生表.学号=成绩表.学号

   生成结果完全合并左右两个表

 查询结果中包括两个表的所有记录,哪怕不匹配的记录也要生成

 

实际应用: 对比学生表和成绩表,查出哪些学生没有选课

 方法1:

select *from学生表

   where 学号 not in(select学号from成绩表)

 方法2:

  select学生表.*from学生表leftjoin成绩表

      on 学生表.学号 =成绩表.学号

        where 成绩 is null

 

例 :找出比李大方所有选修课的成绩最高值还要高的成绩情况

   select*from成绩表where成绩>(selectmax(成绩)from成绩表where学号=(select学号from学生表where姓名='李大方'))

 

 

一 变量的定义及引用

 Declare @变量名

 Set @变量名=值

 Select @为题名=值  from

 

二 输出语句

   Print 量

Select 量  

例:

declare @nint

 set @n=100

print '张三的成绩是:'+convert(nchar(4),@n)

三 逻辑控制语句

  1 if(条件)

Begin 

    …..

End 

Else

   Beging

     ….

   End

说明:如果不是语句块,则可省掉begin end

例1:编程实现:查询出杨春的语文成绩,差判断它是否及格

 

注意:当只有一句时可以不用begin end,当是一个语句块多句时要用

例:编程实现:查询出杨春的语文成绩,判断等级:90以上为优良,80-90为良好,70以上为中等,70以下为差等

 

 

 2 while循环

 While (条件)

   语句/语句块

   [break]

 例:求s=1+2+3+…+100

 

 

 

 3 case多分支

  Case 表达式

     When 表达式值1  then  结果1

     When 表达式值2 then  结果2

…..

 [else  其它结果]

End

 如: select学号,姓名,团员情况=

 case 团员否

   when 1 then '是团员'

   else '非团员'

  end

 from 学生表

循环:while(条件)

      Begin

         循环体

         [if ( )  break ]

      end

例:求s=1+2+3+,,,,100

declare @i int, @s int

set @i=1

set @s=0

while (@i<=100)

 begin

  set @s=@s+@i

  set @i=@i+1

 end

 print @s

case when then

格式:

case 表达式(有具体结果)

  when 值 then 结果

  when 值 then 结果

  when 值 then 结果

  else  结果

end

 

select 学号,姓名,团员情况=

  case 团员否

    when 1 then '是团员'

    when 0 then '非团员'

  end

 from 学生表

 

 

下面列举实例

 

编程实例:

1  求学生表中语文平均分,如果平均在80以上,则输出成绩较好的前3名,如果全班平均在80以下,则输出成绩较差的前3名

declare @pj int

select @pj=AVG(语文)from学生表

if(@pj>=80)

 begin

 print '平均在以上,输出前三名'

 select top3 * from学生表orderby语文desc

 end

 else

 begin

 print '平均在以下,输出差三名'

  select top3 * from学生表orderby语文

 end

2 把学生表复制到新表”学生表2”中,试探着多次给每个学生语文成绩都累加5,直到最差的学生都能及格. (最高不超过100分)

Select * into 学生表2 from学生表

declare @noint

set @no=0

declare @botfloat

select  @bot=MIN(语文)from学生表

while(@bot<60)

begin

  set @no=@no+1

  update  学生表 set 语文=语文+5

  select @bot=MIN(语文)from学生表

end

update 学生表set语文=100where语文>100

print @no

3  从学生表中使用casewhen then查出学号,姓名,语文情况(其中语文情况值为:当语文>=75时为合格,否则为不合格)

select 学号,姓名,语文,语文情况=

 case ROUND( 语文/100-0.25,0)  //关键地方

  when 1 then'合格'

  when 0 then'不合格'

 end

 from 学生表

 

4 把上面第3题改为: 查出学号,姓名,语文情况(90以上为优,80以上为良好,70以上为中等,60以上为合格,60以下为差)

 select学号,姓名,语文,语文情况=

 case convert(int,语文/10)

  when 10 then'优秀'

  when 9 then'优秀'

  when 8 then'良好'

  when 7 then'中等'

  when 6 then'及格'

  else '不及格'

  end

 from 学生表

5                 编程实现:查询出杨春的语文成绩,差判断它是否及格

 select学号,姓名,语文,及格否=

 case round(语文/100-0.1,0)

 when 1 then'及格'

 else '不及格'

 end

 from 学生表where姓名='杨春'

视图 view

 视图是一个虚拟表,它的数据来源于数据库原表(或视图),视图依赖于原表存在的,不能单独存在.

create viewmyv1asselect*from学生表where性别='男'

把学生表中的男生提取出来形成一个视图myv1

select *frommyv1

视图myv1可以当成表一样的使用

视图与一般表的区别:如果b表是由a表复制出来的数据,则a,b不再存在关系,数据变化互不影响;

 如果b是a表建立的视图,则当a表数据变化时,直接影响b视图的数据结果

例:使用成绩表和课程表建立一个视图myv2

create viewmyv2as

select 成绩表.*,名称,学分from成绩表innerjoin课程表

 on 成绩表.课程号=课程表.课程号

 把两个表中的数据提取出,列向上合并成一个新视图myv2

 select *from myv2

例:把学生表,成绩,课程三种数据逻辑上建立一个视图.

create view综合as

select 学生表.*,名称,成绩from学生表innerjoin 成绩表

      on 学生表.学号=成绩表.学号

      inner join 课程表 on成绩表.课程号=课程表.课程号

select *from综合

 

存储过程: 只看用户自定义存储过程

1 无参数传递最简单的存储过程

 编写一个过程能查询杨春

create proceduremypro1

as

 select * from 学生表where姓名='杨春'

  建立过程的格式:

Create procedure 过程名 [参数]

As

 (过程体语句,可以很复杂)

 

然后可以使用:exec mypro1来运行这个过程

2 (mypro2 )有参数从外部传入到过程内部

  如:可实验输入一个姓名,查询该人

create proceduremypro2(@xmnchar(8))

as

 select * from 学生表where姓名=@xm

执行: exec mypro2  ‘杨春’

 

 

3 以上第2例的完善:是否有人?

  

create proceduremypro3(@xmnchar(8),@renchar(20) output)

as

 declare @no int

 select @no=COUNT(*)from学生表where姓名=@xm

 if (@no>=1)

 begin

   select *from 学生表 where 姓名=@xm

   set @re='找到了'

 end

 else

  set @re='无此人'

运行:

declare @re nchar(20)

exec mypro3'杨春dd',@reoutput

print @re

4 把参数传进过程,同时需要过程传出来给调用处一个结果

 例:编写一个求n!的过程mypro5 ,它接收一个n,返回n!结果