mysql5 表连接核心内连接等

 

///mysql5表连接 两个表的交叉关系//

内连

左连

右连

全连

// 查询学生姓名和学生分数

// 表连接

SELECT * FROM student INNER JOIN score ON student.id= score.student_id

第二种写法

SELECT * FROM student,score

WHERE student.id=score.student_id

// 左外连接

SELECT * FROM student LEFT JOIN score ON student.id= score.student_id

// 首先先进行匹配 匹配上的展示，没有匹配上右侧的 补一条左边student表出来

// 右外连接

SELECT * FROM student RIGHT JOIN score ON student.id= score.student_id

// 先取匹配 先拿到内连接 再补一条右边score的数据

//oracle 里面有一个auter join 外连接 既有左外 也有右外 mysql不支持

SELECT * FROM student OUTER JOIN score ON student.id= score.student_id

// 学生姓名 课程名 分数

SELECT student.name,coures.name,score.grade FROM student,course,score

WHERE score.student_id = student.id AND score.coures_id= course.id;

// 第二种写法

SELECT student.name,coures.name,score.grade

FROM score INNER JOIN student ON score.student_id=student.id

INNER JOIN coures ON score.course_id=course.id

// NC

// 无限分类 自身连接

www.jd.com

//商品分类表

CREATE TABLE category(

id int PRIMARY KEY AUTO_INCREMENT NOT NULL,

name VARCHAR(64),

parent_id int

)

// 查询所有顶级分类下面的类别的数量

// 过滤顶级分类

SELECT c1.id,COUNT(*) FROM category c1 INNER JOIN category c2 ON c1.id=c2.parent_id

WHERE c1.parent_id=0 // 查找顶级分类

// 查找父亲id为0的

GROUP BY c1.id;

 

// 要把所有的父亲ID 变成名称

SELECT c1.id,c1.name,c2.name 父分类名称 FROM category c1 INNER JOIN category c2 ON c1.parent_id=c2.id;

// 这里面c2代表的是父亲

 

// 删除重复记录

// 找到要删除的id ，删除掉

SELECT * FROM category c1 LEFT JOIN

// 按照名字分类 查出来分类数量大于1的

(SELECT id,name from category group by name having count(*)>1) c2

on c1.name=c2.name WHERE c1.id!=c2.id

// 拿到了要删除的记录

 

// 可以用IN NOT IN 也可以实现

SELECT * FROM category c1

WHERE c1.name IN

(SELECT name from category group by name having count(*)>1)

AND c1.id NOT IN

(SELECT MIN(id) from category group by name having count(*)>1)

 

//id不是最小id 的出来

// 真正的删除

DELETE FROM category

WHERE name IN

// 需要从临时表中查t1 t2

(select name from (SELECT name from category group by name having count(*)>1) t1)

AND id NOT IN

(select id from (SELECT MIN(id) id from category group by name having count(*)>1) t2)

 

// 多表更新

省份表单拎出来 修改的话 只需要修改省份表即可

select * from student;

create table province(

id int PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(64)

)

// 从学生表中拿出省份往province表里面插入

INSERT INTO province(name) SELECT DISTINCT province FROM student;

// 更新省份 多表联合更新

UPDATE student INNER JOIN province ON student.province = province.name

SET student.province=province.id// 把字符串变成了id

// 改数据类型

ALTER TABLE student MODIFY province int NOT NULL;

// 改名

ALTER TABLE `studb`.`student`

CHANGE COLUMN `province` `province_id` int(11) NOT NULL AFTER `city`;

select * from student;

// AFTER的意思是在哪列之后

mongo 是不能指定顺序的 mysql 可以指定

单表连接 和多表连接其实是一样的

// 根据某张表的字段 填充到另一个表中去的需求

// 从学生表中拿出省份往province表里面插入

INSERT INTO province(name) SELECT DISTINCT province FROM student;

// 相当于把student表复制了一部分到province表中

 

create table tab1(name varchar(64));

create table tab2(name varchar(64));

create table tab3(name varchar(64));

insert into tab2(name) select name from tab1;

// 把tab1 name 插入tab3中 mysql不支持这种写法

select name from tab1 into tab3