mysql 值有交叉

mysql 表连接

连接包括交叉连接，内连接，外链接。其中，外连接分为左外连接，右外连接，全外连接。

1.交叉连接是指不设定任何条件的连接结果，通常也叫做”笛卡尔积”。

语法：from table1 join table 2;

下面建表，举例子解释说明。

#员工表
create table emp(
empid smallint auto_increment,
empName varchar(12) not null comment '员工姓名',
empBirthday date  not null comment "员工出生日期",
empGender tinyint unsigned not null comment '性别 0 男性 1 女性',
empEntryTime date not null comment '员工入职时间',
status tinyint default 0 not null comment '是否离职 0 否 1 是',
empLeaveTime date  comment '员工离职时间',
empDiploma char(15)  not null comment '员工学历',
graduate date not null comment '毕业时间',
empIdentify char(18) not null comment '员工身份证号',
empDepart tinyint unsigned  not null comment '员工所在部门',
primary key(empid),
index empName(empName),
index empDiploma(empDiploma),
unique index empIdentify(empIdentify),
index empDepart(empDepart)
)engine = myisam default charset = utf8;

#部门表
create table emp_department(
depId tinyint auto_increment,
departName varchar(64) not null comment '部门',
departmentAddr varchar(120) not null comment '部门地址',
departNumber char(15) not null comment '部门联系电话',
departManger varchar(64) not null comment '部门主管',
primary key(depId)
)engine = myisam default charset = utf8;

交叉连接实例

连接结果片段

可以看到查询结果是：左边的表的每一行，跟右边的表的每一行，两两互相“横向对接”后所得到的所有数据行的结果。

2.内连接
内连接可以理解为是有连接条件的交叉连接。
语法： from table1 join table2 on tab1.col = tab2.col;

select * from emp join emp_department on emp.empDepart = emp_department.depId;

实例：查处开发部部门所有员工信息

select emp.*,emp_department.departName from emp join emp_department on emp.empDepart = emp_department.depId where emp_department.departName like '开发部';

3.外连接

3.1 左外连接

左外连接是在内连接的结果基础上，加上左表不满足条件的行/记录。
语法： from tab1 left join tab2 on tab1.col = tab2.col;
说明：left 是关键字，连接条件是和内连接一样的

为了表述明白些，稍微对emp 表和 emp_depart表中添加几条数据。

实例：

3.2 右外连接

右连接跟做连接相反。
语法：from tab1 right join tab2 on tab1.col = tab2.col;

实例：

3.3 全外连接(mysql 不支持)

语法：form tab1 full join tab2 on tab1.col = tab2.col;
说明：内连接的结果，加上左表中不满足条件的所有行（右边对应补null），再加上，右表中不满足条件的所有行（左边对应补null）；mysql 不支持全外连接

mysql 连接查询结果图解传送门：

4.综合实例
有一个留言板，用mysql 做数据库，用户信息包括：用户名，密码，email，留言内容包括，留言id，标题，内容，发表时间，状态（审核，未审核）。
用一个sql 语句查询出发表留言数量大于1条的用户名及其留言数量，查询结果按文章数量降序排列。

用户表

#用户表
create table user(
id smallint auto_increment,
username varchar(64) not null ,
password char(32) not null,
email varchar(32) not null ,
primary key(id),
index name(username)
)engine = myisam default charset = utf8;

insert into user(username,password,email)values('zhangsan',md5('zhangsan'),'[e-mail]'),
('lisi',md5('lisi'),'[e-mail]'),('wangwu',md5('wangwu'),'[e-mail]'),('zhaoliu',md5('zhaoliu'),'[e-mail]');

留言表

#留言表
create table message(
msgId smallint auto_increment,
userId smallint unsigned not null,
title varchar(512) not null,
content text,
publish_time datetime not null,
status tinyint unsigned not null,
primary key(msgId),
index userId(userId)
)engine = myisam default charset = utf8;


insert into message (userId,title,content,publish_time,status) values
(1,'this is a test article,I am learning mysql ,I love programming ,love life,love write.....','我的邮箱是:[e-mail]交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。  ','2018-02-05 15:40',1),
(2,'this is a test article,I am learning mysql ,I love programming ,love life,love write.....','我的邮箱是:[e-mail]交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。  ','2018-02-05 15:41',0),
(3,'this is a test article,I am learning mysql ,I love programming ,love life,love write.....','我的邮箱是:[e-mail]交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。  ','2018-02-05 15:42',0),
(4,'this is a test article,I am learning mysql ,I love programming ,love life,love write.....','我的邮箱是:[e-mail]交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。  ','2018-02-05 15:43',1),
(5,'this is a test article,I am learning mysql ,I love programming ,love life,love write.....','我的邮箱是:[e-mail]交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。  ','2018-02-05 15:44',1),
(6,'this is a test article,I am learning mysql ,I love programming ,love life,love write.....','我的邮箱是:[e-mail]交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。  ','2018-02-05 15:46',0),
(7,'this is a test article,I am learning mysql ,I love programming ,love life,love write.....','我的邮箱是:[e-mail]交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。  ','2018-02-05 15:48',1);

select username,count(*) as msg_number from user join message on user.id = message.userId group by message.userId having msg_number >1 order by msg_number desc;