MySQL多表操作

MySQL多表操作

一、数据查询进阶

1、分组

根据指定的指定进行分组

select * from 表名 where 条件 group by  字段名(指定分组的字段名);
select * from 表名 group by  字段名(指定分组的字段名);
-- 查询得到的结果是每个分组的第一条数据

-- 分组
select * from t1 group by gander;
select * from t1 where grade>90 group by gander;

-- 分组统计出现的次数
select gander , count(name) from t1 group by gander;

select gander , count(name) from t1 where gander='女' 
group by gander;

select * from t1 group by grade;

2、分页（限制查询返回条数）

limit 子句限制查询返回的数据条数

select * from 表名 where 条件 limit 返回条数;
select * from 表名 where 条件 limit 起点 , 返回条数;

select * from t1;
select * from t1 limit 5;
select * from t1 limit 0 , 5;
select * from t1 limit 4 , 3;
select * from t1 where gander='男' limit 4 , 3;

3、聚合筛选

having 对分组之后的数据进行筛选 ， where只能操作表中的字段 ， having可以和聚合函数联合的

注意：having 必须跟group by 一起使用（avg ， max , min , sum）

select * from 表名 where 条件 group by  字段名(指定分组的字段名) having 条件;

select gander , count(name) from t1  
group by gander;
select gander , count(name) from t1  
group by gander having avg(grade) > 85;

4、子查询

在select语句中， 嵌套另一个select语句

在where子句中 ， 嵌套一个select语句

as：对表名或者字段名进行重命名（区别名）

-- 在select语句中， 嵌套另一个select语句 
-- 查询得到的结果作为一个新的表 吗交给外层查询 ， 此时得到的新版用as对器进行取名 ， 方便后续操作
select * from (select * from 表名 where 条件) as 表名 where 表名.条件;

-- 在where子句中 ， 嵌套一个select语句
-- 查询得到的结果必须是一个确切的数据 ， 不能是多行多列的表格 ， 返回的结果就是交给外层进行条件筛选
select * from 表名 where 条件(select * from 表名 where 条件);

select * from t1 where gander='男';
select * from (select * from t1 where gander='男') as t
where t.grade > 95;

select grade from t1 where name='卢本伟';

select * from t1 
where grade<(select grade from t1 where name='卢本伟');

二、表与表的关系

1、多表之间的关系

1、一对一
	身份证号：一个人有且只能对应一个身份证号 ，
	DNA
2、一对多/多对一
	一个班级可以多个学生

3、多对多
	大学生——选课
	城市 —— 人

2、外键约束

foreign key : 建立表与表之间的某种约束关系，这个关系的存在 ， 可以让表与表之间的数据广联性更强 ， 数据更完整。

进行连接或者被连接的字段设置为主键

主表：被外键连接（要有主键 ， 提供给外键进行连接）

从表：设置外键进行连接

注意：主表必须先创建 ， 才能创建从表进行外键约束连接

foreign key (外键字段名) , references 主表名(字段名)

在外键约束中

restrict(默认)
on delete restrict
on update restrict
当主表删除/修改数据的时候 ， 从表有数据关联就无法删除/修改

cascade
on delete cascade
on update cascade
当主表数据进行删除/修改的时候 ， 从表的数据也会跟着同步更新

create table t2(
	id int primary key auto_increment,
	name varchar(5)
);

create table t3(
	id int primary key auto_increment,
	age INT,
	class char(2),
	-- 创建一个字段作为外键约束，让表之间成为一对一的关系
	t3_id int unique,
	foreign key (t3_id) references t2(id)
);

insert into t2(name) VALUES
('床前明月'),
('李泽'),
('卢昭玮'),
('わかだ'),
('星辰'),
('溪岳'),
('胡桃'),
('陈冠希'),
('吴彦祖'),
('秋水');

insert into t3(age , class , t3_id)VALUES
(19 , '一班' , 15),
(20 , '二班' , 14),
(21 , '一班' , 12),
(15 , '三班' , 13),
(25 , '二班' , 11),
(26 , '一班' , 16),
(24 , '二班' , 20),
(20 , '一班' , 19),
(19 , '三班' , 18);


delete from t2 where id=11;
delete from t2 where id=17;

create table t4(
	id int primary key auto_increment,
	dep_name varchar(5),
	job char(4)
);

create table t5(
	id int primary key auto_increment,
	name char(5),
	t5_id int,
	foreign key (t5_id) references t4(id)
	on delete cascade
	on update cascade
);

insert into t4(dep_name , job) VALUES
('研发部','做开发'),
('教学部','上课的'),
('人力部','招人的');

insert into t5(name , t5_id) VALUES
('初雪' , 2),
('七零' , 2),
('梁山伯' , 3),
('豆浆油条' , 1);


update t4 set id=0 where id=3;