当前位置: 澳门新濠3559 > 数据库 > 正文

用一条SQL语句查询出每门课都大于80分的学生姓名

时间:2019-10-06 19:49来源:数据库
编程萌新,因为遇到这么个SQL查询的问题:在一张表A里有如下字段:学生姓名、学科名、学科成绩。写一条SQL语句查出各科平均成绩并按学生姓名分组,按如下格式显示:学生姓名|语

  编程萌新,因为遇到这么个SQL 查询的问题:在一张表A里有如下字段:学生姓名、学科名、学科成绩。写一条SQL 语句查出各科平均成绩并按学生姓名分组,按如下格式显示:学生姓名|语文|数学|英语。一开始遇到的时候挺懵的,有印象但是却忘了怎么写了,接下来就是查阅资料并亲自上手测试是否可行~

1. 用一条SQL语句查询出每门课都大于80分的学生姓名 

多表联结查询效率问题一直是我们开发人员讨论的一个问题,下面我把我在使用多表查询时的一些测试也分享给各位朋友,希望对大家所有帮助。

上节课介绍了MySQL数据库中如何使用SQL语言查询和变更数据。

  第一次代码修修改改如下,此时还是在只用一张表来查询(无法实现上述目标结果,贴图只是为了说明下思考路线):

name   kecheng    fenshu
张三     语文     81
张三     数学     75
李四     语文     76
李四     数学     90
王五     语文     81
王五     数学     100
王五     英语     90

最近做网站的时候遇到一个问题,我需要输出B表和C表中的两个字段,不过纠结的是,程序所得到的参数是A表中的id,而A表与B表及C表关联的是另一个字段。如此一来,我就需要从3个表中读取数据,刚开始我想用SQL的多表联结查询,可是又担心效率太低。如果我先读出A表中与B、C关联的字段的值,再运行另一条SQL语句使用该值读取B、C表中的值效率是否会高一些?思索良久,最终决定写一个程序来测试。

这节课来介绍如何通过PHP调用SQL语句实现数据管理功能网页。

    

思路:这里不能直接用分数>80这样的比较条件来查询的到结果,因为要求没门成绩都大于80。我们可以反过来思考,如果有一门成绩小于80,那么就不符合要求。先找出成绩表中成绩<80的多有学生姓名,不能重复,然后再用not in找出不再这个集合中的学生姓名。

以下代码以Discuz!X2.5的数据库为例,查询tid为1的主题发表者的username。

通过PHP调用SQL语句连接到数据库

 1 select UName,
 2 (select AVG(Grades) from UserInfo where GName = '语文') as '语文',
 3 (select AVG(Grades) from UserInfo where GName = '数学') as '数学',
 4 (select AVG(Grades) from UserInfo where GName = '英语') as '英语' 
 5 from UserInfo
 6 where UName = '小明'
 7 group by UName
 8 /*having GName = '语文' and GName = '数学' and GName = '英语'*/
 9 order by AVG(Grades)
10 go

 

 代码如下

前面教程当中通过PHP编程语言来读写数据文本文件的方式,实现了一个学生分数信息管理功能。

 

create table #成绩(姓名varchar(20),课程名称varchar(20),分数int)
insert into #成绩values
('张三',     '语文',       81),
('张三',     '数学',       75),
('李四',     '语文',       76),
('李四',     '数学',       90),
('王五',     '语文',      81),
('王五',     '数学',       100),
('王五',     '英语',       90)

select distinct(姓名) from #成绩 where 姓名 not in(select distinct(姓名) from #成绩 where 分数<=80)

复制代码

可以实现一览查询,新增,编辑和删除功能。

  然后查阅资料也没有头绪,就想只用一张表是否真的可行,要不用两张表试一下?接下来就是在原表A(实际为如下代码中 UserInfo表)基础上插入一个 学生id 的字段,然后新建一张学生表(T_User),拥有两个字段:id、姓名,学生表的id匹配表A里的学生id,查询语句通过连接实现(因为是思考一个问题,就不考虑内外或者交叉连接的问题了),代码如下:

经澳门新濠3559,luofer提示还有一种思路,是用group by hvaing,这绝对是一种好方法。我估计出这个题的人就是要考察这个知识,代码如下:

<?php
    $start = time();
    for($i = 1; $i < 10000; $i ){
     $writerInfo = DB::fetch_first("SELECT a.username FROM
     pre_common_member AS a, pre_forum_thread AS b
     WHERE b.tid='1' AND a.uid=b.authorid");
    }
    echo(time() - $start);
    ?>

通过读写数据文件的方式有很多局限性,最好的方式是通过PHP编程语言来调用SQL语句,从而将数据的查询和变更都通过数据库来实现。

1 select UName,
2 (select AVG(Grades) from UserInfo where GName = '语文' and UName = tu.name) as '语文',
3 (select AVG(Grades) from UserInfo where GName = '数学' and UName = tu.name) as '数学',
4 (select AVG(Grades) from UserInfo where GName = '英语' and UName = tu.name) as '英语' 
5  from UserInfo as ui join T_User as tu
6  on ui.UName = tu.name
7 group by ui.UName,tu.name
8 go
select 姓名 from #成绩 
group by 姓名
having min(分数)>80

这段代码在执行SQL查询前先保存当前时间戳,然后执行一万次多表联结查询,然后用执行完毕后的时间戳减去开始前的时间戳,得到运行时间。
    然后将循环块中代码改为如下:

首先要实现通过PHP编程语言连接到数据库,然后才能调用相应的SQL语句来访问和变更数据。

 

还有一种方法类似于第一种

 代码如下

打开之前编写过的studentlist_do.php文件,修改为如下代码:

  运行测试如下:

select distinct a.姓名 from #成绩 a where not exists (select 1 from #成绩 where 分数<80 and 姓名=a.姓名)

复制代码

澳门新濠3559 1image

澳门新濠3559 2

 

$tid = DB::fetch_first("SELECT authorid FROM pre_forum_thread WHERE tid='1'");

其中最重要的下面2句代码:

  成功!研究并解决差不多花了1个多小时,可能资质不行,但是会继续努力。从简单入手,从萌新出发~

 

$user = DB::fetch_first("SELECT username FROM pre_common_member WHERE uid='$tid[authorid]'");

/连接数据库服务器/

 

这次把SQL语句分为两次进行执行。
    为了让结果准确,两段代码分别执行了三次,结果第一段代码的执行时间分别为13、13和15秒,而第二段代码执行时间分别为23、21、22。
    可以看出,将多表联结的SQL分为多次执行,时间将比一次多表联结要长大概80%左右,结论是直接执行一行多表连接效率更高

澳门新濠3559 3dbserver,澳门新濠3559 4dbpassword) or die('数据库连接错误!');

2. 学生表如下:

下面讲一些题外话了,不过也是关于多表联合查询的

/打开数据库/

自动编号   学号   姓名  课程编号  课程名称  分数
1      2005001 张三  0001   数学    69
2      2005002 李四  0001   数学    89
3      2005001 张三 0001   数学    69
删除除了自动编号不同,其他都相同的学生冗余信息

inner join,full outer join,left join,right jion
内部连接 inner join 两表都满足的组合
full outer 全连 两表相同的组合在一起,A表有,B表没有的数据(显示为null),同样B表有
A表没有的显示为(null)
A表 left join  B表 左连,以A表为基础,A表的全部数据,B表有的组合。没有的为null
A表 right join B表 右连,以B表为基础,B表的全部数据,A表的有的组合。没有的为null

mysql_select_db(澳门新濠3559 5dbh);

 

查询分析器中执行:
--建表table1,table2:
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70

前面一句是调用mysql_connect函数,传递的参数是:数据库服务器地址,数据库用户名,数据库密码。

思路:这个和上面的一样,也不能直接删除,而是要先找出自动编号不相同,其他都相同的行,这个要使用group by语句,并且将其他的字段都放在group by后面,这样找出来的行都是没有冗余的行,然后随便保留其中一个自动编号,删除其他的行。

如表

后面的or die是当mysql_connect如果调用成功,则返回true则后面的or die不会执行。

 

table1|table2|

idname|idscore|
1lee|190|
2zhang|2100|

如果mysql_connect调用不成功,比如服务器地址错误或者密码错误等,则or die会继续执行;

create table #成绩(自动编号 int, 学号 int,姓名 varchar(20),课程编号 int,课程名称 varchar(20),分数 int)
insert into #成绩 values
(1,2005001 ,'张三',  1,   '语文',       81),
(2,2005001 ,'李四',  1,   '语文',       81),
(3,2005001 ,'张三',  1,   '语文',       81),
(4,2005001 ,'张三',  1,   '语文',       81)

select * from #成绩
drop table #成绩

delete from #成绩 where 自动编号 not in
(select MIN(自动编号) from #成绩 group by 学号,姓名,课程编号,课程名称,分数)

4wang|370|

以下均在查询分析器中执行

一、外连接
用一条SQL语句查询出每门课都大于80分的学生姓名 ,多表联结查询效率问题一直是我们开发人员讨论的一个问题。1.概念:包括左向外联接、右向外联接或完整外部联接

2.左连接:left join 或 left outer join
(1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
(2)sql语句

 代码如下

复制代码

select * from table1 left join table2 on table1.id=table2.id
-------------结果-------------

die这个函数的作用就是结束整个php网页的继续执行,将"数据库连接错误"这个字符串返回到浏览器显示出来。

经广岛之恋的提醒发现另外一种思路,代码如下:

idnameidscore

1lee190
2zhang2100

后面一句mysql_select_db函数的作用是连接到具体的数据库,例如这里就是之前创建好的数据库student。

delete from #成绩 where 自动编号 not in
(select distinct(a.自动编号) from #成绩 a join #成绩 b on a.自动编号>b.自动编号 
where a.学号=b.学号 and a.姓名=b.姓名 and a.课程编号=b.课程编号 and a.分数=b.分数)

4wangNULLNULL

注释:包含table1的所有子句,根据指定条件返回table2相应的字段,不符合的以null显示

3.右连接:right join 或 right outer join
(1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
(2)sql语句

 代码如下

复制代码

select * from table1 right join table2 on table1.id=table2.id
-------------结果-------------

由于上节课,学哥的数据库是安装在Window7里面的phpStudy环境中,因此将student的这些php文件都部署到了phpStudy环境里面,访问网页地址:localhost/studentlist.php:

如果不考虑自动编号,还可以这样

idnameidscore

1lee190
2zhang2100
NULLNULL370


注释:包含table2的所有子句,根据指定条件返回table1相应的字段,不符合的以null显示

4.完整外部联接:full join 或 full outer join
(1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
(2)sql语句

 代码如下

复制代码

select * from table1 full join table2 on table1.id=table2.id
-------------结果-------------

澳门新濠3559 6image

 

idnameidscore

1lee190
2zhang2100
4wangNULLNULL
NULLNULL370


注释:返回左右连接的和(见上左、右连接)

二、内连接
1.概念:内联接是用比较运算符比较要联接列的值的联接

2.内连接:join 或 inner join

3.sql语句

 代码如下

复制代码

select * from table1 join table2 on table1.id=table2.id
-------------结果-------------

发现看到的结果和之前学哥将php文件部署在ubuntu操作系统下面显示的结果不一样。

--注意identity用法,只能用在有into的select语句中
select identity(int,1,1) as id, 学号,姓名,课程编号,课程名称,分数  
into #temp
from #成绩 group by 学号,姓名,课程编号,课程名称,分数
truncate table #成绩
insert into #成绩 select * from #temp

idnameidscore

1lee190
2zhang2100


注释:只返回符合条件的table1和table2的列

4.等价(与下列执行效果相同)

 代码如下

复制代码

A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * from table1 cross join table2 where table1.id=table2.id 

 (注:cross join后加条件只能用where,不能用on)

三、交叉连接(完全)

1.概念:没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。(table1和table2交叉连接产生3*3=9条记录)

2.交叉连接:cross join (不带条件where...)

3.sql语句

 代码如下

复制代码

select * from table1 cross join table2
-------------结果-------------

网页中显示了很多Notice和Warning。

 

idnameidscore

1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370


注释:返回3*3=9条记录,即笛卡尔积

4.等价(与下列执行效果相同)

 代码如下

复制代码

A:select * from table1,table2

...

关闭PHP错误提示

3. 一个叫department的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球对,现在四个球对进行比赛,用一条sql语句显示所有可能的比赛组合。

由于PHP可以显示很多错误提示来帮助开发者更好的调试代码,而PHP错误提示有很多个级别,是可以通过配置文件进行设置的。

思路:这是一个组合问题,就是说四个不同的元素有多少种不同的两两组合。现在要把这个问题用sql语句实现。既然这四个元素是不相同的,我们可以将这个表当成两个集合,求他们的笛卡尔积,然后再从笛卡尔积中找到那些元素不相同的,并且不重复的组合。

1种方法是修改PHP目录里面的php.ini配置文件设置相应的错误提示级别。

create table #department(taname char(1))
insert into #department values
('a'),('b'),('c'),('d')

--下面两条语句都可以,多谢wanglinglong提醒
select a.taname,b.taname from #department a,#department b where a.taname < b.taname
select a.taname,b.taname from #department a,#department b where a.taname > b.taname

第2种方法可以在PHP代码中临时设置当前这个php文件的错误提示级别。

  

下面我们使用第2种方法来将Notice提示和Warning提示关闭,不显示到网页中。

4.怎么把这样一个表
year  month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year  m1  m2  m3  m4
1991  1.1   1.2   1.3   1.4
1992  2.1   2.2   2.3   2.4

studentlist_do.php文件修改如下:

思路:这个很明显是一个行列转换,首先会想到pivot。结果中有m1,m2,m3,m4四个新的列,他们需要从原来的行中转换。

澳门新濠3559 7image

create table #sales(years int,months int,amount float)
insert into #sales values
(1991,   1,     1.1),
(1991,   2,     1.2),
(1991,   3,     1.3),
(1991,   4,     1.4),
(1992,   1,     2.1),
(1992,   2,     2.2),
(1992,   3,     2.3),
(1992,   4,     2.4)

select pt.years,[1] as m1,[2] as m2,[3] as m3,[4] as m4 
from (select sod.amount,sod.months,sod.years as years from  #sales sod)  so 
pivot
(min(so.amount) for so.months in ([1], [2],[3],[4])) as pt

刷新网页:

注意[1],[2],[3],[4]中括号不可缺少,否则会出错。还有一种写法是使用子查询,这个要新建4个子查询进而得到新的列:

澳门新濠3559 8image

select a.years,
(select m.amount from #sales m where months=1 and m.years=a.years) as m1,
(select m.amount from #sales m where months=2 and m.years=a.years) as m2,
(select m.amount from #sales m where months=3 and m.years=a.years) as m3,
(select m.amount from #sales m where months=4 and m.years=a.years) as m4
from #sales a group by a.years

可以看到之前的错误提示信息都不见了。

还可以这样写,大同小异:

为何之前学哥在ubuntu系统下部署运行php程序没有这个问题呢,这是因为ubutnu系统里面的php和Window7下面的phpStudy的php的配置不一样。

select  a.years,
sum(case months when 1 then amount else 0 end) as m1,
sum(case months when 2 then amount else 0 end) as m2,
sum(case months when 3 then amount else 0 end) as m3,
sum(case months when 4 then amount else 0 end) as m4
from #sales a group by a.years

一般来说作为编程者知道此问题即可,知道如何打开和关闭错误提示即可。需要调试代码时打开,正式运行时关闭。

  

使用PHP调用SQL语句查询一览数据

 

接下来就可以使用已经连接好的数据库来将一览页面的数据从数据库种查询出来并显示,就不再需要读取studentdatas.txt文件了。

5.有两个表A和B,均有key和value两个字段,如果B的key在A中也有,就把B的value换为A中对应的value。这道题的SQL语句怎么写?

studentlist_do.php文件修改如下:

思路:这个问题看似简单,只要一个update语句,然后找到相同的key,更新value字段就可以了。可能你首先会写成这样:update #b set #b.value=(select #a.value from #a where #a.keys=#b.keys)。但是要注意的是如果仅仅找相同的key会有很多匹配,更新的时候会出现错误,所以要在外层限制。

澳门新濠3559 9image

create table #a(keys int , value varchar(10))
insert into #a values
(1,'aa'),
(2,'ab'),
(3,'ac')
create table #b(keys int , value varchar(10))
insert into #b values
(1,'aa'),
(2,'a'),
(3,'a')

update #b set #b.value=(select #a.value from #a where #a.keys=#b.keys) where #b.keys in
(select #b.keys from #b,#a where #a.keys=#b.keys and #a.value<>#b.value)

刷新网页,然后点击查询按钮:

在luofer的提醒之,有了第二个思路

澳门新濠3559 10image

update #b set #b.value=s.value
from (select * from #a except select * from #b) s where s.keys=#b.keys

可以看到显示了2条数据。可以通过数据库客户端软件打开数据库,打开表,确认确实是存在这2条数据记录。

luofer是牛人啊!

澳门新濠3559 114-12-7.jpg

再举一个例子,已知有一个课程表PressErp..Course,里面已经有一些数据,现在要向教师表[Press].[dbo].[Teacher]中添加新数据,但是有的老师课程可能不在这个已知的课程表中,这时候就可以用到这个except了,它的作用就是向这个课程表中添加以前不存在的课程名称。注意关键字distinct和except。

关键的代码如下:

insert into Course   select distinct(课程) from  [Press].[dbo].[Teacher] a except select b.CourseName from Course b  

$query="select * from studentscore";

 

这一句是定义一个查询的SQL语句,这里是不带检索条件查询出表studentscore里的所有记录。

  

澳门新濠3559 12query,$dbh);

 

这一句是通过mysql_query执行一句SQL语句,第二个参数$dbh就是前面通过mysql_connect函数返回的数据库连接实例变量。

6. 两张关联表,删除主表中已经在副表中没有的信息。

while(澳门新濠3559 13result)){

思路:这个就是存在关系,可以使用in,也可以使用exists。

这一句是将查询的结果澳门新濠3559 14record。

create table #zhubiao(id int,name varchar(5))
insert into #zhubiao values
(1,'aa'),
(2,'ab'),
(3,'ac')
create table #fubiao(id int, grade varchar(5))
insert into #fubiao values
(1,'aa'),
(2,'ab')

delete from #zhubiao where id not in(select b.id from #fubiao b)
delete from #zhubiao where not exists(select 1 from #fubiao where #zhubiao.id=#fubiao.id)

record->id;

  

这一句就可以通过访问$record变量数组的键来获得一条记录的某个字段的数据值。

7. 原表:

注意这里的id要和数据库的字段名完全匹配上。

courseid coursename score

其它的辅助代码就是将读取的表记录的字段数据值存放到二维数组变量$alldatas当中,和之前的定义一致,这样后面的代码就不需要修改了。

1   java          70

使用where的SQL语句实现条件查询

2      oracle       90

之前要实现条件查询,如果读取数据文件方式,只能自己通过写PHP代码进行数据过滤筛选的方式,有了SQL语句之后,就简单了。

3      xml            40

可以通过根据传递的条件进行判断,生成相应的SQL语句来进行数据库查询,获得满足条件的数据记录结果集。

4      jsp             30

如果要实现姓名的模糊查询,对应的SQL语句是这样的:

5      servlet     80

select * from studentscore where name like '%张%';

为了便于阅读,查询此表后的结果显式如下(及格分数为60):

那么就要根据传递过来的姓名条件参数,将where后面的这串字符串拼装好,拼接到$query变量中。

courseid coursename score mark

studentlist_do.php文件修改如下:

1        java         70  pass

澳门新濠3559 16image

2        oracle     90  pass

刷新网页,然后在姓名条件输入“张”,然后点击查询按钮:

3        xml          40  fail

澳门新濠3559 17image

4         jsp          30  fail

可以看到显示了1条满足条件的数据。同时可以看到具体的SQL语句显示在网页上,这是为了调试用,全部代码完成之后再注释掉。

5    servlet    80     pass

接下来,实现其它3个查询条件,注意多个查询条件之间要用and进行连接。

思路:这个就很直接了,使用case语句判断一下。

studentlist_do.php文件修改如下:

create table #scores(course int,coursename varchar(10),score int)
insert into #scores values
(1, 'java', 70 ),
(2, 'oracle', 90),
(3, 'xmls', 40),
(4, 'jsp', 30), 
(5, 'servlet', 80 )

select course,coursename,
case when score>60 then 'pass' else 'fail' end as mark
from #scores

澳门新濠3559 18image

  

[图片上传失败...(image-4783ac-1539918280654)]

  1. 原表:

澳门新濠3559 19image

id proid proname

刷新网页,然后在姓名条件输入“张”,性别选择“男”,分数段选择“75-90”,科目勾选“语文”和“数学”,然后点击查询按钮:

1 1 M
1 2 F
2 1 N
2 2 G
3 1 B
3 2 A
查询后的表:

澳门新濠3559 204-12-12.jpg

id pro1 pro2

可以看到显示了1条满足条件的数据。同时可以通过查看SQL语句判断程序逻辑是否正确的拼装好了SQL语句。

1 M F
2 N G
3 B A

如果将检索条件分数段选择修改为“90分以上”,点击查询按钮后会看到没有检索到数据。

思路:依旧是行列转换,这个在面试中的几率很高。这个语句还是有两种写法,如下:

可以将这段调试用的SQL语句复制粘贴到数据库客户端软件里面去运行,同样也查询不到数据。

create table #table1(id int,proid int,proname char)
insert into #table1 values
(1, 1, 'M'),
(1, 2, 'F'), 
(2, 1, 'N'), 
(2, 2, 'G'), 
(3, 1, 'B'), 
(3, 2, 'A')

select id, 
(select proname from #table1 where proid=1 and id=b.id) as pro1,
(select proname from #table1 where proid=2 and id=b.id) as pro2
from #table1 b group by id

select d.id,[1] as pro1,[2] as pro2 from
(select b.id,b.proid,b.proname from #table1 b) as c
pivot
(min(c.proname) for c.proid in([1],[2])) as d

由于很多代码都没有用了,因此可以将注释的代码全部删除进行精简,同时将调试语句注释掉。

  

studentlist_do.php文件修改后完整代码如下:

  1. 如下

澳门新濠3559 21image澳门新濠3559 224-12-14.jpg

表a
列    a1 a2
记录  1  a 
      1  b
      2  x
      2  y
      2  z
用select能选成以下结果吗?
1 ab
2 xyz

通过PHP调用SQL语句新增一条记录

思路:这个开始想使用行列转换来写,没有成功,后来没有办法只好用游标,代码如下:

接下来,将新增一个记录的代码也修改为调用SQL语句插入记录到数据库,而不再写入到studentdatas.txt文件。

create table #table2(id int , value varchar(10))
insert into #table2 values
(1,'a'),
(1,'b'),
(2,'x'),
(2,'y'),
(2,'z')
create table #table3(id int,value varchar(100) );insert into #table3(id,value) select distinct(id),'' from #table2

declare @id int,@name varchar(10)
declare mycursor cursor for select * from #table2
open mycursor
fetch next from mycursor into @id,@name
while (@@Fetch_Status = 0)
begin
update #table3 set value=value @name where id=@id
fetch next from mycursor into @id,@name 
end
close mycursor
deallocate mycursor

select * from #table3

studentcreate_do.php文件修改完整代码如下:

 

澳门新濠3559 23image澳门新濠3559 244-12-16.jpg

有两个要注意的地方,

其中关键性的代码是调用函数mysql_query执行一句SQL语句,然后根据返回值判断是否成功执行。

a.#table3里面的value字段初始值如果不设置的话默认是null,后面更新的时候null 'a'任然是null,最后得到的value永远是null。所以默认是''

另外需要说明的是getMaxId自定义函数用于查询数据库表,得到某个表的某个字段的最大的值,返回值加1。

b.第二个fetch语句一定要放在begin和end之间,要不然会死循环的,不常用的语句写起来很不爽快

它的作用在这里就是用于取得studentscore表的最大的ID的值,然后加1返回。这样插入数据库的时候ID就不会重复了。

经 scottshen提醒,使用for xml更加的简单,看下面的语句:

由于getMaxId具有共通性,所以将它做成一个函数,未来可以将这个函数独立放到某个共通文件,可以在多个php文件中去引用。

SELECT id,
(SELECT value   '' FROM #table2 WHERE id=a.id FOR XML PATH('')) AS [values]
FROM #table2 AS a GROUP BY a.id
--或者这样写
select distinct a.id,
(select b.value '' from #table2 b where b.id=a.id for XML path('')) as value
from #table2 a

打开一览页面,点击查询按钮:

下面这一句帮助我们理解for xml的工作原理

澳门新濠3559 25image

select '' a.value from #table2 a where id=2 for xml path('')

可以看到目前总共2条记录,点击链接“新建学生分数信息”,进入新建页面:

 

澳门新濠3559 26image

10.一个业务有多个访谈信息,要求每次添加访谈信息的时候都要更新业务中的当前添加的访谈信息的ID,这样可以快速查找当前业务的最新状态。

姓名输入“段誉”,性别选择“男”,科目选择“英语”,分数输入“99”,点击“保存”按钮:

update MaintainMessage set CurrentCommunicateID=(select MAX(a.ID) from Communicate a  where a.MaintainId=MaintainMessage.ID) 

澳门新濠3559 274-12-19.jpg

注意MaintainMessage.ID中的MaintainMessage不能省略。

看到显示了提示信息“创建数据成功”,并且可以看到调试用的SQL语句,可以查看SQL语句是否正确。

 

点击“确定”按钮,然后关闭此窗口。

回到一览页面,点击“查询”按钮:

澳门新濠3559 28image

可以看到了新增到数据库的记录被查询出来了。

通过PHP调用SQL语句修改和删除一条记录

接下来,将修改和删除一个记录的代码也修改为调用SQL语句插入记录到数据库,而不再写入到studentdatas.txt文件。

studentedit_do.php文件修改完整代码如下:

澳门新濠3559 294-12-21.jpg澳门新濠3559 30image

打开一览页面,点击查询按钮:

澳门新濠3559 314-12-23.jpg

可以看到目前总共3条记录,点击链接“段誉”,进入编辑页面:

澳门新濠3559 32image

姓名修改为“王语嫣”,性别选择“女”,科目选择“音乐”,分数输入“96”:

澳门新濠3559 334-12-25.jpg

点击“保存”按钮:

澳门新濠3559 34image

看到显示了提示信息“编辑数据成功”,并且可以看到调试用的SQL语句,可以查看SQL语句是否正确。

点击“确定”按钮,然后关闭此窗口。

回到一览页面,点击“查询”按钮:

澳门新濠3559 354-12-27.jpg

可以看到了第3条记录的数据被修改好并查询出来了。

点击链接“王语嫣”,进入编辑页面:

澳门新濠3559 36image

点击“删除”按钮:

澳门新濠3559 374-12-29.jpg

确认页面点击“确定”:

澳门新濠3559 38image

看到显示了提示信息“删除数据成功”,并且可以看到调试用的SQL语句,可以查看SQL语句是否正确。

点击“确定”按钮,然后关闭此窗口。

回到一览页面,点击“查询”按钮:

澳门新濠3559 39image

可以看到了第3条记录不见了。

此时,可以到数据库客户端软件中打开表studentscore查看数据,确认确实数据记录被删除了。

编辑:数据库 本文来源:用一条SQL语句查询出每门课都大于80分的学生姓名

关键词: 澳门新濠3559