连接、插入、更新、删除、 exists纯在性测试-第九、十次课

1 数据库的表间连接操作—join

1）何时要使用到多表操作 

（1）嵌套查询可能用到。查出选了d01课程的学生数据：学号,姓名，性别。

这些数据来自于学生表，但要根据成绩表中查哪些选了D01课程的学生的学号，涉及了两个表操作。

注意：SQL嵌套IN查询不能超过3层，例如下面：

Select 学号,姓名,性别 from 学生表 where 学号 in(select 学号 from 成绩表 where 课程号=’d01’)

（2）数据查询结果（不是条件）来自于多个表，如，查出选了D01课程的学生数据：学号，姓名，性别，课程号，成绩值

第一种情况数据来源单一表学生表，虽然使用了成绩表，但结果没有成绩表的数据，成绩表的数据仅仅作为主查询的条件。

可以使用连接也可以使用嵌套，把子查询的结果作为主查询的条件.此时，并非同时操作两个表，先操作的成绩表，把子查询结果列出为作常量集合（成绩表已经关闭），再打开学生表提取数据。虽然使用到了两个表，但是一前一后打开使用的.故，查询结果只能来自于学生表，不能出现成绩表的数据.

第二种情况数据来源于成绩表和学生表同时提供，这两个是并列操作，必须使用连接.

2）连接的概念

在逻辑上把两个及以上的物理表或视图，在列向上合并（不是记录合并，是列属性集合合并）,在逻辑上把两个表的查询结果当成一个视图。

 连接时，一般需要两表关键字有对应关系，如上两个表都有学号列，否则可能结果是迪卡尔积。（如所有学生把所有课堂一个不漏地选一次）

 

例：Select 学号,姓名,性别,课程号 from 学生表 where 学号 in  (select 学号 from 成绩表 where 课程号='101')

 错了，数据查询来源是学生表，学生表中没有课程号列。

 

如何修改？实现连接查询

Select 学生表.学号,姓名,性别,课程号,成绩 

   from 学生表,成绩表

没有语法错误，但结果是两个表的迪卡尔积，共生成了m*n个新记录的视图。把每一个学生表的记录行无条件地与成绩表的每一行都匹配生成新记录。

Select 学生表.学号,姓名,性别,课程号,成绩

   from 学生表,成绩表 where 学生表.学号=成绩表.学号 

正确了。两表按学号关键字对应匹配生成新记录。把学生表每个人按学号值去成绩表匹配相同学号，应对生成一个学生一门课数据。如果该生选了五门课，要匹配成功五次学号，结果中就该生的五条记录.

2 各种链接操作

(1) 内连接

  a inner join b 要求两表能对应的记录才出现的结果中，不对应的记录被忽略掉。如学生表中x同学没有选课，则他在成绩表中无记录，内连接查询时，结果无x同学数据。 

上例其实是内连接的另一种早期的写法，等同于下面：

 Select 学生表.学号,姓名,性别,课程号,成绩 

   from 学生表,成绩表 where 学生表.学号=成绩表.学号

标准化内连接:

Select 学生表.学号,姓名,性别,课程号,成绩 

   from 学生表 inner join 成绩表 

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

以上子句也可以使用

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

  from 学生表 a inner join 成绩表 b on a.学号=b.学号

    where  课程号 in (101,102,103) order by 课程号

a  inner join b 可省写为:a  join b

 

例题1：查出选了大学英语的男生的数据情况：学号，课程号，成绩，使用两种方法实现：

方法1: 用嵌套

select 学号,课程号,成绩 from 成绩表 

 where 学号 in(select 学号 from 学生表 where 性别='男')

    and 课程号in(select 课程号 from 课程表 where 名称='大学英语')

方法2:使用表的连接实现

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

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

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

where 性别='男' and 名称='大学英语'

注：三表及以上如何内连接

  A inner join b  on a.key1=b.key1

Inner join c  on b.key2=c.key2

Inner join  d  on c.key3=d.key4

内连接时，表的左右位置无所谓

 

例2 ：改写上例，查出选了大学英语的男生的数据情况：学号，姓名，性别，课程号，课程名称，成绩

查询结果来源于三个表都有数据

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

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

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

where 性别='男' and 名称='大学英语'

说明：举一个夸张例子，把上面三个表全部列投影出来

可见：在逻辑上把三个表列向上合并起来，实际应用中，再从这个视图中投影一部分列出来生成需要的视图

create view myv12 as 

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

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

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

select * from myv12 where 性别='男' and 名称='大学英语'

(2)外连接的左连接

  A left join b ，生成的结果以左表a为准，a表的所有记录都列出来,如果b中不存在a中的部分记录，即可能部分a中的记录在b中无对应，则b中的数据以空值代表

 例：在上例基础上改为学生表左连接

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

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

上结果要增加可读性，让人好理解 

Select 学生表.学号,姓名,性别, 

 case when 课程号 is NULL then '未选' 

   else 课程号

 end  as 课程号

,case when 成绩 is NUll then 0

   else  成绩

 end   as 成绩 

   from  成绩表  right  join 学生表 

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

 

实例：带HTML代码查询输出，大大简单前台程序劳动

select 学生表.学号,姓名,性别,

case when 课程号 IS NULL then '未选'  

 else 课程号 

end as 课程号

,成绩,'<a href=dele?id='+学生表.学号+'>删除</a>' as 删除 from 

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

 

例：在上例基础上，改为：列出没有选课的学生数据

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

 

新办法：

select * from 

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

 where 课程号 is NULL

 

几个说明：

1 多表连接操作时，如果查询列在多个表中不唯一如“学号”，一定要指明该重复列的表的来源（否则要提示“不唯一”），如：学生表.学号

2 表名太复杂，常用别名方法：

select a.学号,姓名,性别,c.课程号,名称,成绩 from 

    成绩表 a inner join 学生表 as b on a.学号=b.学号

    inner join 课程表 c on a.课程号=c.课程号

where 性别='男' and 名称='大学英语'

3 表连接操作不仅是用于表的查询，这种连接可以应用于删除和修改等（非常复杂）

4 查询结果的列在多个表中不重复，则不需要指明表的来源。

a.学号,姓名,性别   不能理解为：a.学号,a.姓名,a.性别

应该理解：学号指定是a表的，姓名和性别是三个表无重复，它们在哪个表就去哪个表提取

(3)外连接的右连接

 A  right join b 与上同理，查询结果以右边b表数据为准，如果a表中无记录对应，则以null值代表

select * from 

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

    where 课程号 is NULL

改为： 

select * from 

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

 where 课程号 is NULL   是错的，无结果

应该改为：

select * from

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

where 课程号 is NULL

(4)全连接： a full join b

两个表的所有数据都要列出来，凡是对方表中无对应的记录，则都以Null值代表

 比方：inner join 是要求a,b两表记录条件相交

   Full join 要求a,b两表记录相并

如有A,B,C三个表，连接查询时，AB之间以B数据为准，BC连接时以C为数据为准。

 …from  a right join b on a.key1=b.key1

   Right join c on b.key2=c.key2

（5）自连接

 一个表自己与自己连接，一个物理表被打开两次，冠以不同别名，当成两个独立的表使用。

 …from 表1  as  a  inner join 表1  as b  on a.key=b.key

 例：从学生表中找到与李大方同性别的其它人。

方法1: 用嵌套

  select 姓名,性别 from 学生表 where 性别=

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

  and 姓名!='李大方'

方法2:用自连接

 把学生表打开两次，内连接起来。其中一个表作为数据输出的表，另一个表作为条件限制的表。

select a.学号,a.姓名,a.性别

 from 学生表 as a inner join 学生表 as b

   on a.性别=b.性别

where a.姓名!='李大方' and b.姓名='李大方'

  

select b.学号,b.姓名,b.性别

 from 学生表 as a inner join 学生表 as b

   on a.性别=b.性别 where a.姓名='李大方' and b.姓名!='李大方'

 

补例1：查出101课程中，高于本101课程平均分有哪些同学，列出他们的学号和姓名。

  Select 学号 from 成绩表 where 成绩>(Select avg(成绩) from 成绩表 where 课程号=’101’) and 课程号=’101’

上面查出哪些学生选了101且该科成绩大于101课程的平均分

Select 学号,姓名 from 学生表 where 学号 in (

 Select 学号 from 成绩表 where 成绩>(Select avg(成绩) from 成绩表 where 课程号=’101’) and 课程号=’101’)

 

补例2: 从成绩表中查出考试平均成绩高于全体学生的总平均成绩的学生学号和平均分。

分析：这样理解：学生的考试平均成绩是该生所有几门选课中的平均成绩.  全体学生的总平均成绩是指某一科课程的所有学生的平均成绩.

select 学号,AVG(成绩) as 平均分 from 成绩表

  group by 学号

    having AVG(成绩)>(select AVG(成绩) from 成绩表 

3  存在性测试

 使用子查询时，把子查询的结果作为主查询的条件。如果子查询的结果不存在咋办？

  Where [not] exists（子查询）

 

 例1:查询选了101课程的学生的姓名

 方法1: 嵌套查询

  select 姓名 from 学生表 where 学号 in

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

方法2:存在性判断

  select 姓名 from 学生表 where exists

  (select * from 成绩表 where 学号=学生表.学号

     and 课程号='101')

可改写为：

  select 姓名 from 学生表 where exists

  (select 学号 from 成绩表 where 学号=学生表.学号

     and 课程号='101')  接近于连接查询概念， 两表是同时存在的

 

例2：查出没有选101课程的学生的姓名和学号，性别

方法1:多表连接实现查询

 select distinct a.学号,姓名,性别 from 学生表 a inner join    成绩表 b on a.学号=b.学号 where 课程号!='101'

方法2: 用嵌套子查询2

 select 学号,姓名,性别 from 学生表

 where 学号  not in(select distinct 学号 from

        成绩表 where 课程号='101')

方法3:在子查询中使用存在性判断

select 学号,姓名,性别 from 学生表

  where  not  exists(select * from 成绩表 where 学号=

     学生表.学号 and 课程号='101')

方法5:在主查询中存在性判断否定

select 学号,姓名,性别 from 学生表

 where   exists(select 学号 from 成绩表 where 学号=学生表.学号 and 课程号!='101')

4 向数据库插入新记录

命令格式：

Insert into 表名(属性列表) values（对应属性列值表）

注意：values不是value

属性列和值的个数，顺序，数据类型都要完全一致时；

仅当插入数据时，按表的原始属性列顺序插入所有值时，“属性列表”可省写。

例：

向课程表中插入新数据

insert into 课程表(课程号,名称,学时,类别,开课学期,学分)

 values('115','机械力学',45,'选修课','第学期',3)

说明：学时45表中是整数型，但插入数据时使用字符文本串，系统有容错能力，把数值文本转换为数值。

 日期常量也要加引号，否则会当成一个数学计算表达式如：‘1989-12-1’，如果写成1989-12-1会当成一个减法表达式，逻辑上是错误的.

上例可以省掉属性列说明，因为全列插入，改为：

insert into 课程表

 values('116','量子物理',45,'选修课','第学期',3)

 

如果值不是全列插入，前面的属性列表不能省的，如：

insert into 课程表

 values('117','食品加工',60,'选修课')   错的

因为省了属性列，默认要全列插入数据，而后面的值个数不匹配。

 

以上方法是数据来源于手工写的常量

如何把其它的查询结果当成新数据插入到表中？

格式：insert into 表名1（属性列）  select …from 表名2….

把从表2查询得到的数据插入到表1中。子查询的结果列与目标表的列完全一致。

实例：

表1                           表2

学号  姓名  性别  生日    学号 名字  性别   出生日期

…

如何把表2中的所有男生选出来插入到表1？

Insert into 表1(学号,姓名,性别,生日)  

select 学号,名字 as 姓名,性别,出生日期 as 生日

          From 表2)

可见，源数据与目标表的表头结构要一致

5  数据更新 update

 格式：

update  表名 set 列1=表达式1[,列2= 表达式2,……] [where 条件]

用法1:直接根据条件修改某一个，某一些记录的属性值

   对已经事先存在的记录进行某些列的值的修改

例： 把表中杨春同学的性别修改为男，生日提前10天

 

update 学生表 set 性别='男',生日=生日-10 where 姓名='杨春'

 

说明：生日=生日-10  赋值操作，先算的右边，原来的生日-10的结果再赋值给生日(i=i-10)

 

用法2: 横向计算应用

学生成绩表结构:

学号  姓名  语文  数学  英语  总分  平均分

01   aaa    89     67    74

计算所有学生的总分，平均分

Update 学生成绩 set  总分=语文+数学+英语,平均分=总分/3

上面命令不能修改为：

Update 学生成绩 set 平均分=总分/3,总分=语文+数学+英语

在SQL中，逗号并列的计算，从左向右的，上例不能得到平均分的

用法3:实际开发APP时，如用户初步注册（调用你的QQ，微信号初步简单的注册，当用户进一步使用本APP其它功能时，再完善个人资料），基本理论：

 Insert into 用户表(用户ID)values(微信号)，在系统中给新用户占个位

以后完善个人资料时，使用update更新其它数据

注意：update是支持多表连接操作

例：给所有姓李的同学的确102课程加1分

 方法1: 嵌套查询

update 成绩表 set 成绩=成绩+1 where 课程号='102'

   and 学号 in(select 学号 from 学生表 where LEFT(姓名,1)='李')

方法2:

 update 成绩表 set 成绩=成绩+1

   from 学生表 a inner join 成绩表 b on a.学号=b.学号

  where 课程号='102' and lefT(姓名,1)='李'

方法2使用表的连接并不是用于查询，而是作为主句update 的约束条件

6 删除

1 删除表  drop table 表名

  drop table 教学表

 小心，数据库删除数据和表是无法恢复

练习：先复制一个表，来删除备份表（

select * into b from a  ,再在B表练习删除操作）

 

2 删除表记录元组

 格式：delete  from 表名  where 条件

不加条件，表要清空，小心

    delete from 学生表  where 姓名 like '杨%'

小结：已经学了数据定义，数据库和表的结构建立和修改维护

  数据操纵，最核心的数据增，删除，查，修改

   Insert  delete  select  update

应用实例：发布新贴子，回复贴子都是insert； 删除贴子或回复是delete；

普通用户翻阅贴子是select ，点赞或维护贴子是update。

（解释：回复贴子为什么是insert？ 一般情况下，如新闻贴子，贴子本身在一个表，所有贴子的回复是在另一个表，两个表是按贴子IP关联约束的。

贴子表

贴子ID  标题  内容  发布者ID  点击数  点赞数   

1   aa    aaaa   a1         5      2

2   bb    bbb    b1

。。。。。

回复表

ID  贴子ID   回复人ID  回复内容

1    1       b1

2    1       c1

3    2       a1

 

 

 

 

 以C#为例，实现与sqlserver 的连接

整个过程全代码化，不用鼠标手工

string cnstr = "server=8-337-Teacher; database=学生管理; uid=sasa;pwd=sasasa;";

 

 

1 打开VS，

 

 

2

文件---新建---C#---Web窗体（就是建立一个新网名，默认名为default.aspx）

3 建立前台界面（html+服务器控件结合用---效率不高）

 

 

 

4 双击控件命令按钮：进入服务器后台代码界面（这些代码是服务器上运行的，又叫后台程序）

5 定义命令串，连接串

string cnstr = "server=8-337-Teacher; database=学生管理; uid=sasa;pwd=sasasa;";

(1) sqlserver服务器名字

(2) 数据库名字

(3) 登陆账号和密码 :远程服务器必须允许用户远程网络账号登陆，如何设置呢？

 

 

<1>允许服务器远程登陆（混合登陆）

右击sqlserver服务器名字---属性：

（2设置远程账号sa及密码 

 

 

“状态”---启用。允许SA远程登陆

（3）打开SQLSERVER配置管理器，允许internet  tcp/ip协议及连接

 

 

 

 

（4）完成以上操作后，重新启动服务器（控制面板---管理工具—服务 ）

 

 

右击它，停止一次，再启动。

（5）重新打开sqlserver

成功后，把上一节课最后连接数据库的代码去测试，看看能否成功，成功后发出来平时成绩加2分）

 

 

 

完整示例：

protected void Button1_Click(object sender, EventArgs e)

    {

        string cnstr = "server=LAPTOP-D6A7VHU8; database=StudentDB; uid=sa;pwd=sasasa;";

        SqlConnection cn = new SqlConnection(cnstr);

        cn.Open();

        string sql = "select * from 学生表 where 姓名='杨春'";

        SqlCommand cmd = new SqlCommand(sql, cn);

         SqlDataReader dr = cmd.ExecuteReader();

        dr.Read();

        Response.Write(dr["性别"].ToString());

        for (int i = 0; i < dr.FieldCount; i++)

            Response.Write("<br/>" + dr[i].ToString() + "  ");

        dr.Close();

        cn.Close();

    }

 课后补充作业：

1 熟悉VS下的WEB站点建立

2 在一个网页中制作一个增加新学生数据的界面：

学号：

姓名：

性别：

身高：

家庭住址：   省     县        

生日：   年    月   日

爱好：可多选复选

 提交按钮

3 点击”提交按钮“后，在后台程序中取出以上数据，把数据结果随意展示出来。

（以上作业为后继学习数据入库做准备）