mysql前10天数据库_第二十天MySQL数据库入门(多表查询和子查询)

#A表 B表

#多表联合查询

# 1.左连接 [left join]

#select查询 A 左连接 B 先把A表里面的数据全部取出，然后让A表里面的数据去一个个匹配B里面的数据。

#2.右连接[right join]

#select 查询 A 右连接 B 先把B表里面的数据全部取出，然后让A表里面的数据去一个个匹配A里面的数据。

#3.内连接 [join ] [ inner join ]

# 获取两个表的交集部分

#准备两张表

#员工表

create tableemp(

eid int(5) primary key auto_increment,

ename varchar(100) not null,

sex char(1) default '男',

salary double(8,2),

deptId varchar(100),

birthday timestamp,

income timestamp defaultnow(),

leaderId int)engine=innodb charsetutf8;

#部门表：

create tabledept(

deptId int,

dname varchar(100),

createtime timestamp

)engine=innodb charsetutf8;

#1001 董事会

#1002 开发部 张三(经理) 张三丰 张无忌(员工)

#1003 测试部 范冰冰(经理) 范伟 范可欣(员工)

#1004 行政综合部 刘亦菲(经理) 刘能

#1005 财务部 苍井空 (经理) 刘德华

insert intoemp(ename, sex, salary,deptId,leaderId, birthday,income)values('马云','男',1,1001,null,'1978-2-25','2003-7-8');

insert intoemp(ename, sex, salary,deptId,leaderId, birthday,income)values('张三','男',30009,1002,1,'1988-06-25','2003-9-18');

insert intoemp(ename, sex, salary,deptId,leaderId, birthday,income)values('张三丰','男',20000,1002,2,'1998-2-25','2013-7-8');

insert intoemp(ename, sex, salary,deptId,leaderId, birthday,income)values('张无忌','男',10000,1002,2,'1988-05-25','2013-6-18');

#统计年龄大于28(年龄按向下取整)的各部们有多少人

select deptId, count(t) from(selectfloor(datediff(now(),birthday)/365) t, deptId fromemp) e wheret>28 group by deptId;

create tableteacher(

tid int(5) primary key auto_increment,

tname varchar(100) not null,

age int,

phone char(11)

)engine=innodb charsetutf8;

create tablecourse(

cid int(5) primary key auto_increment,

cname varchar(100),

score int(3),

tid int(5),

constraintfk_tid_course foreign key(tid) referencesteacher(tid)

)engine=innodb auto_increment=1001 default charsetutf8;

#插入数据

insert intoteacher values(1,'张三',54,'18739804568'),(2,'李四',24,'18745704568'),(3,'王五',21,'13545704568'),(4,'灰太狼',35,'13545745568');

insert intocourse values(1001,'c++编程基础',3,1),(1002,'java编程基础',3,1),(1003,'数据结构基础',5,2),(1005,'高等数学',5,3),(1006,'跳伞',1,null),(1007,'蹦极',1,null);

#+-----+-----------+------+-------------+

#| tid | tname | age | phone |

#+-----+-----------+------+-------------+

#| 1 | 张三 | 54 | 18739804568 |

#| 2 | 李四 | 24 | 18745704568 |

#| 3 | 王五 | 21 | 13545704568 |

#| 4 | 灰太狼 | 35 | 13545745568 |

#+-----+-----------+------+-------------+

#+------+--------------------+-------+------+

#| cid | cname | score | tid |

#+------+--------------------+-------+------+

#| 1001 | c++编程基础 | 3 | 1 |

#| 1002 | java编程基础 | 3 | 1 |

#| 1003 | 数据结构基础 | 5 | 2 |

#| 1005 | 高等数学 | 5 | 3 |

#| 1006 | 跳伞 | 1 | NULL |

#| 1007 | 蹦极 | 1 | NULL |

#+------+--------------------+-------+------+

select* fromteacher;

#内连接：只匹配两张表中都存在的数据

#方式一:

selectc.cname, t.tname fromcourse c inner jointeacher t onc.tid= t.tid;

#方式二:

selectc.cname, t.tname fromcourse c,teacher t wherec.tid= t.tid;

#+--------------------+--------+

#| cname | tname |

#+--------------------+--------+

#| c++编程基础 | 张三 |

#| java编程基础 | 张三 |

#| 数据结构基础 | 李四 |

#| 高等数学 | 王五 |

#+--------------------+--------+

#左外连接

#左外连接以left关键字左边的表为准，左边表存在的数据全部显示，右边表不存在的则以null来表示。

#方式一:

selectc.*,t.* fromteacher t left joincourse c ont.tid= c.tid;

#方式二:

selectc.*,t.* fromcourse c right jointeacher t onc.tid= t.tid;

#+-----+-----------+------+-------------+------+--------------------+-------+------+

#| tid | tname | age | phone | cid | cname | score | tid |

#+-----+-----------+------+-------------+------+--------------------+-------+------+

#| 1 | 张三 | 54 | 18739804568 | 1001 | c++编程基础 | 3 | 1 |

#| 1 | 张三 | 54 | 18739804568 | 1002 | java编程基础 | 3 | 1 |

#| 2 | 李四 | 24 | 18745704568 | 1003 | 数据结构基础 | 5 | 2 |

#| 3 | 王五 | 21 | 13545704568 | 1005 | 高等数学 | 5 | 3 |

#| 4 | 灰太狼 | 35 | 13545745568 | NULL | NULL | NULL | NULL |

#+-----+-----------+------+-------------+------+--------------------+-------+------

#右外连接

#右外连接：显示以right关键字左边的表为准，右边表存在的数据全部显示，左边表不存在的则以null来表示。

#方式一

selectc.* , t.* fromteacher t right joincourse c ont.tid= c.tid;

#方式二

selectc.*, t.* fromcourse c left jointeacher t onc.tid= t.tid;

#+------+--------------------+-------+------+------+--------+------+-------------+

#| cid | cname | score | tid | tid | tname | age | phone |

#+------+--------------------+-------+------+------+--------+------+-------------+

#| 1001 | c++编程基础 | 3 | 1 | 1 | 张三 | 54 | 18739804568 |

#| 1002 | java编程基础 | 3 | 1 | 1 | 张三 | 54 | 18739804568 |

#| 1003 | 数据结构基础 | 5 | 2 | 2 | 李四 | 24 | 18745704568 |

#| 1005 | 高等数学 | 5 | 3 | 3 | 王五 | 21 | 13545704568 |

#| 1006 | 跳伞 | 1 | NULL | NULL | NULL | NULL | NULL |

#| 1007 | 蹦极 | 1 | NULL | NULL | NULL | NULL | NULL |

#+--

#全连接 把两张表合并显示

select* fromteacher union all select* fromcourse;

select* fromteacher union select* fromcourse;

# +------+--------------------+------+-------------+

# | tid | tname | age | phone |

# +------+--------------------+------+-------------+

# | 1 | 张三 | 54 | 18739804568 |

# | 2 | 李四 | 24 | 18745704568 |

# | 3 | 王五 | 21 | 13545704568 |

# | 4 | 灰太狼 | 35 | 13545745568 |

# | 1001 | c++编程基础 | 3 | 1 |

# | 1002 | java编程基础 | 3 | 1 |

# | 1003 | 数据结构基础 | 5 | 2 |

# | 1005 | 高等数学 | 5 | 3 |

# | 1006 | 跳伞 | 1 | NULL |

# | 1007 | 蹦极 | 1 | NULL |

# +------+--------------------+------+-------------+

# union all 不会过滤重复的数据，union会过滤。

# 建议使用union all，除非业务一定强调了数据不能重复。

# union 效率低下

#子查询：查询里嵌套查询

#一种是where型子查询 select 列名1，列名2，列名3.... from 表名 where (select语句);

#一种是from型子查询 select 列名1，列名2， 列名3.... from 表名, (select语句) 表名2 where ;