mysql数据表的连接实例

mysql数据表之间的连接
   本例以student表和teacher表为例；
   student：id,name,birthday,sex,goal,tid (id为主键，tid为外键);
   teacher: tid,name,sex,birthday;
1. 显示student表中学生的id，name，教师编号，以及教师编号所对应的教师姓名；
   student表中和teacher表中name相同必须注明是具体哪个表中的；
   select id,student.name,student.tid,teacher.name from student,teacher
   where student.tid=teacher.tid;
   用别名连接也可以： 别名使用： (表名 别名);
   select e.id,e.name,e.tid,d.name from student e,teacher d where    e.tid=d.tid;
   99年出的自然连接:前面一种在mysql下用不了查出来是空的，后面一种能够查出来！
   select e.id, e.name,tid,d.name from student e natural join teacher d;
   select e.id,e.name, tid,d.name from student e join teacher d using(tid);
   现在常见的用法是：
   select e.id,e.name,e.tid,d.name from student e join teacher d    on e.tid=d.tid;
2. 成绩高于90的学生编号，姓名，成绩，老师编号，姓名；
   select e.id,e.name,e.goal,e.tid,d.name from student e ,teacher d where 
   e.tid=d.tid and e.goal>90;
   select id,student.name,goal,student.tid,teacher.name from student,teacher
   where student.tid=teacher.tid and goal>90;
   select e.id,e.name,e.goal,e.tid,d.name from student e join teacher d on
   e.tid=d.tid and e.goal>90;
3.非等值连接
   select e.id,e.name,e.goal,g.level from student e,grade g where e.goal       between g.lower and g.heiger;
4. 自连接
   select e.id,e.name,m.monitor,m.name from student e,student m where e.monitor=m.id 
   order m.monitor;
5. 外连接
   左连接：(等右叫左，等式右边有加号叫左连接！);
   select e.id,e.name,e.goal,e.tid,d.name from student e left join teacher d 
   on e.tid=d.tid;
   右连接：(等左叫右，等式左边有加号叫右连接!)；
   select e.id,e.name,e.goal,e.tid,d.name from student e right join teacher  
   d on e.tid=d.tid;
   全连接：mysql 中并不支持全链接，但可以将两个连接合并，可以达到相同的效果
   即：左连接 union 右连接
   select e.id,e.name,e.goal,e.tid,d.name from student e left join teacher d
   on e.tid=d.tid
   union 
   select e.id,e.name,e.goal,e.tid,d.name from student e right join teacher    d on e.tid=d.tid;
6. 子查询:
    单行子查询:后面的查询必须只有一个结果集！
    select id,name,sex ,goal from student where goal>(select goal from
    student where name='赵铁刚');
     mysql中聚合函数min(),max(),avg(),sum(),count(),不能嵌套使用
    通过表链接，from里面加上查询子句
    查询每个老师下面的学生的平均成绩，平均成绩要求大于最低平均成绩;
    最低平均成绩查询：student1 为每个老师带的学生平均成绩表的别名；
    goal1为平均分的别名：
    select min(goal1) from (select avg(goal)goal1 from student group by tid)     student1;
    平局成绩大于最低平均成绩查询：(注意判断条件里面的tid不要在前面加上e.或d.
    会导致查询出错Unkonwn e.tid);
     select e.tid,d.name,avg(e.goal) from student e join teacher d on             e.tid=d.tid group by
     e.tid having avg(e.goal)>(select min(goal1) from (select avg(goal)           goal1 from student group by tid)  student1); 
    查询与赵铁刚分数相同的人
    select id,name,sex,goal from student where goal=(select goal from            student where name='赵铁刚');
    或者查询与赵铁刚同一个老师的同学；
    select id,name,sex,goal,tid from student where tid=(select tid from
    student where name='赵铁刚');
    多行子查询：any>(大于最小)，any<(小于最大),all>(大于最大),all<(小于最小);
    查询分数比最低平均分数低的学生信息 (小于最小的问题)
    select id,name,sex,goal from student  where goal<(select
    min(goal1) from (select avg(goal) goal1 from student group by tid)        student1);
    用<all也可以
    select id,name,sex,goal from student where goal<all(select avg(goal)     from    student group by tid);
    查询不必最低平局分数还低的学生信息
    select id,name,sex,goal from student where goal>any(select avg(goal)     from student group by tid);
    查询不比最高平均分还高的学生信息
    select id,name,sex,goal from student where goal<any(select avg(goal) 
    from student group by tid);
    查询比最高分还高的的学生信息
    select id,name,sex,goal from student where goal>all(select avg(goal)     from student group by tid);
7.  limit用法
    获取的是前5行的数据
    select id,name,sex,goal from student limit 0,5;
8.  差集： 
    NOT IN 表示差集 
    SELECT * FROM table1 WHERE name NOT IN (SELECT name FROM table2);
    交集：
    SELECT * FROM table1 AS a JOIN table2 AS b ON a.name =b.name;