SQL学习笔记5
多表查询
1、多表关系
MySQL是一个关系型数据库,数据库中表与表之间存在关联。它们的关系根据一张表包含另外一张表数据的多少可以分为:(使用外键建立关系的方法不常用)
一对多或多对一:在多的一方加入外键对应少的一方的主键
多对一:在两张表中加入一张中间表,中间表中加入两个外键对应两张表的主键
一对一:在一张表加入另一张表的外键,且将外键约束为唯一
2、多表查询概述
多表查询的语法:select 字段 from 表1,表2;
select * from cusinfo,serinfo;
但这种方法对导致产生笛卡尔积的问题,即将表A中的集合与表B中的集合全部组合一遍,因此多表查询的目的是去除多余的笛卡尔积
多表查询的方法可分为:
连接查询和子查询
连接查询分为:
内连接:查询两张表之间的交集
外连接:分为左外连接和右外连接,查询左(右)表的所有数据和两张表的交集
自连接:表与自身连接查询,必须使用表别名
子查询分为:
标量子查询
列子查询
行子查询
表子查询
3、连接查询
内连接
隐式内连接(语法简单,容易编写):select 字段名 from 表1,表2 where 条件
select cusinfo.name,serinfo.level from cusinfo,serinfo where cusinfo.serinfo_id=serinfo.id;
select c.name,s.level from cusinfo as c,serinfo as s where c.serinfo_id=s.id;
显式内连接(能减少扫描次数,提高效率,在同时查询多张表时更常用):select 字段名 from 表1 [inner] join 表2 on 条件
select cusinfo.name,serinfo.level from cusinfo inner join serinfo on cusinfo.serinfo_id=serinfo.id;
外连接
左外连接(左外更常用,且左右外可以通过改写达到一样的目的,此时表1是主表):select 字段名 from 表1 left [outer] join 表2 on 条件
select cusinfo.name,serinfo.level from cusinfo inner join serinfo on cusinfo.serinfo_id=serinfo.id;
右外连接(此时表2是主表):select 字段名 from 表1 right [outer] join 表2 on 条件
select cusinfo.* from cusinfo left join serinfo on cusinfo.serinfo_id=serinfo.id;
select serinfo.*,cusinfo.* from cusinfo right join serinfo on serinfo.id=cusinfo.serinfo_id;
自连接
自连接可以用内连接的语法查询,也可以用外连接的语法查询,且必须起别名:
select 字段 from 表 别名1 表 别名2 where 条件
select 字段 from 表 别名1 left join 表 别名2 on 条件
alter table cusinfo add column coopid int comment '小组id';
update cusinfo set coopid=2 where id=1;
update cusinfo set coopid=1 where id=2;
update cusinfo set coopid=4 where id=3;
update cusinfo set coopid=3 where id=4;
update cusinfo set coopid=6 where id=5;
update cusinfo set coopid=5 where id=6;
update cusinfo set coopid=8 where id=7;
update cusinfo set coopid=7 where id=8;
update cusinfo set coopid=null where id=9;select a.name as '姓名',b.name as '同组成员' from cusinfo a ,cusinfo b where a.id=b.coopid;
select a.name as '姓名',b.name as '同组成员' from cusinfo a left join cusinfo b on a.id =b.coopid
4、联合查询
联合查询,就是把多张表的查询结果合并为一个查询结果
语法:select 字段名 from 表名 where 条件
union [all]
select 字段列表 from 表名 where 条件
select name from cusinfo
union all
select level from serinfo;
联合查询应注意:联合查询需保证两张表列数一致,联合查询若使用union all语法,则仅对查询结果拼接,若使用union语法,还会对重复的内容进行去重
select name from cusinfo where sex = '男'
union
select name from cusinfo where address = '北京'
5、子查询
子查询又称嵌套查询,即在表操作语句中嵌套一条查询语句select;子查询可以嵌套外部语句update、insert、select、delete中
子查询根据子查询的位置可分为from之后、where之后、select之后
子查询根据查询返回的结果大小分为:
标量子查询:查询结果为一类,语法:select 字段 from 表名 where 条件 运算符 子查询
标量子查询可用的运算符为:>,<,!=,<=,>=
select name as '年龄小于李四的男性用户' from cusinfo where age <(select age from cusinfo where name='李四') and sex='男';select name as '和李四同一个用户等级的女性用户' from cusinfo where cusinfo.serinfo_id =(select cusinfo.serinfo_id from cusinfo where name='李四') and sex='女';
列子查询:查询结果为一列,语法为select 字段 from 表名 where 条件 操作符 子查询
操作符可以是
in:结果在指定范围内
not in:结果不在指定范围内
any:只要满足子查询列表任一结果
all:需满足子查询列表所有结果
select name as '比上海的用户等级高的用户' from cusinfo where cusinfo.serinfo_id > all (select cusinfo.serinfo_id from cusinfo where address = '上海')
行子查询:查询结果为一行,查询语句为:select 字段名 from 表明 where (条件) 操作符(子查询条件)
操作符可以是 = ,!=,in,not in
select name as '李四一个城市,一个用户等级,一个年龄的用户' from cusinfo where (address,serinfo_id,age) = (select address,serinfo_id,age from cusinfo where name='李四');
表子查询:查询结果多行多列,常用的操作是将表子查询的结果作为一张临时表与其他表做连接查询,语法为:select 字段名 from (子查询) 连接语法 on 操作符 条件
常用的操作符为:on
select name as '与张三、李四年龄,性别相同的用户' from cusinfo where (age,sex) in (select age,sex from cusinfo where name='张三' or name='李四');select e.name ,serinfo.level from (select * from cusinfo where age>19) as e left join serinfo on e.serinfo_id=serinfo.id;