MySQL多表查询的基本操作

连接查询
指两个或两个以上的表按某个条件连接起来,从中选取需要的数据。
连接查询是同时查询两个或两个以上的表时使用的，当不同的表中存在相同意义的字段时，可以通过字段连接几个表。

内连接查询
显示两个表中符合连接条件的记录
通过join/cross join/inner join连接表
通过on条件连接
例：查询用户表user中id,username,email,sex
        查询省份表province中proname
select u.id,u.username,u.email,u.sex,p.proname
from user as u
inner join province as p
on u.proid = p.id;
 
查询user表中id,username,sex,province表中的proname,性别为男，
根据proname分组，显示username详情，组中总人数，组中人数大于1，按照id降序，限制显示两条
select u.id,u.username,u.sex,p.proname count(*) as totalUsers ,group_concat(username)
from user as u
join province as p
on u.proid = p.id
where u.sex = '男'
group by p.proname
having count(*) >= 1
order by u.id desc
limit 0,2;

外连接查询
左外连接 left[outer] join 显示左表的全部记录及右表符合条件的记录
右外连接 right[outer] join  显示右表的全部记录及左表符合条件的记录
select u.id,u.username,u.email,u.sex,p.username
from user as u
left join province as p
on u.proid = p.id;

外键
外键是表的一个特殊字段，被参照的表是主表，外键所在的表是子表。设计外键的原则是依赖于数据库中以存在的表的主键。
外键的作用是建立该表与其父表的关联关系。父表中对记录作操作时，字表中与其对应的信息也应有相应的改变。
作用：保持数据的一致性和完整性。
注意点：
1父表和字表必须使用相同的数据引擎，而且禁止使用临时表
2数据表的存储引擎必须为innodb
3外键列和参照列必须具有相似的数据类型，其中数据的长度或是否有符号位必须相同，而字符的长度可以不同
4外键列和参照列必须创建索引。如果外键列不存在索引mysql将自动创建索引
外键约束的参照操作：
cascade 从父表删除或更新且自动删除或更新子表中匹配的行
set null 从父表删除或更新行，并设置子表中的外键列为null,如果使用该选项必须保证子表列没有指定not null
destrict 拒绝对父表的删除或更新操作
on action 标注SQL关键字，在mysql中与restrict相同
创建主表部门表department
create table department(
 id tinyint unsigned auto_increment key,
 depname varchar(20) not null unique
)engine=innodb;
创建子表员工表employee
create table employee(
 id smallint unsigned auto_increment key,
 username varchar(20) not null unique,
 depid tinyint unsigned,
foreign key(depid) references department on delete cascade on update cascade;
)engine=innodb;
删除或更新父表时子表也会对应改变
detele from department where id = 1;
删除外键
alter table employee drop foreign key emp_fk_dep;
添加外键
alter table employee add constraint 外键名称 foreign key(depid) reference department(id);
当删除或更新父表时子表值为null，前提为子表列没有指定为not null
foreign key(depoid) references department on delete set null on update set null ;

联合查询
查询多个表记录，并把结果合并到一起
union
union all
区别：union 去掉相同记录，union all 是简单的合并到一起
select username from employee union select username from user;

子查询
是将一个查询语句嵌套在另一个查询语句中。内层查询语句的结果，可以作为外层查询语句提供条件
使用in的子查询
select id,username from employee where depid [not]in(select id from department);
使用运算符的子查询
创建学员表student
字段 id，username，score
奖学金表
字段 id，level
select id,username from student where score >=(selecct level from scholarship where id=1);
使用exists子查询,如果为真外层语句被执行，否则将不被执行
select id,username from employee where exists(select * from department where id=5);
使用any,some,all的子查询
运算符    any        some           all
>、〉=    最小值     最小值        最大值
<、〈=    最大值     最大值        最小值
=         任意值     任意值
<>、!=                             任意值
查询所有获得奖金的学生
select id,username from student where score >= any(select level from scholarship);

将查询结果写入到数据表
insert [into] table_name [(col_name,...)] select...
创建text
id,num
insert text(id,num) select id,score from student;
创建数据表时将查询结果写入到数据表
create table text1(id tinyint unsigned auto_increment key,
score tinyint unsigned
)select id,score from student;