mysql 之多表

mysql之多表

已知2张基本表：部门表：dept （部门号，部门名称）;员工表 emp（员工号，员工姓名，年龄，入职时间，收入，部门号）

CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charset=utf8;

INSERT into dept VALUES ('101','财务');

INSERT into dept VALUES ('102','销售');

INSERT into dept VALUES ('103','IT技术');

INSERT into dept VALUES ('104','行政');

CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;

insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');

insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');

insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');

insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');

insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');

insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');

insert into emp VALUES ('1880','老九',55,'1971/10/20',8000,'105');

dept.dept1=emp.dept2

一、

1.普通内连接===》只能取到2表交集部分数据

Select * from dept inner join emp on dept.dept1=emp.drpt2

2.基本内连接===》只能取到2表交集部分数据

Select * from dept,emp where dept.dept1=emp.dept2

3.左连接===》以左表为主，左表的数据会全部显示出来，右边的数据不够会补null处理

Select * from dept left join emp on dept.dept1=emp.dept2

4.右连接===》以右表为主，右表的数据会全部显示出来，左边的数据不够会补null处理

Select * from dept right join emp on dept.dept1=emp.dept2

5.左独有数据===》左表中单独存在的数据会显示出来

Select * from dept left join emp on dept.dept1=emp/dept2 where name is null

6.右独有数据===》右表中单独存在的数据单独显示

Select * from dept right join emp on dept.dept1=emp.dept2 where dept1 is null

7.全外连接（union）

1）内连接+左独有+右独有

select * from dept INNER JOIN emp on dept.dept1=emp.dept2

UNION

select * from dept left JOIN emp on dept.dept1=emp.dept2 where name is null

UNION

select * from dept right JOIN emp on dept.dept1=emp.dept2 where dept_name is NULL;

2）左连接+右独有

select * from dept left JOIN emp on dept.dept1=emp.dept2

UNION

select * from dept right JOIN emp on dept.dept1=emp.dept2 where dept_name is NULL;

3）右连接+左独有

select * from dept right JOIN emp on dept.dept1=emp.dept2

UNION

select * from dept left JOIN emp on dept.dept1=emp.dept2 where name is null;

二、子查询

定义：一个查询中嵌套另一个查询

1.、子查询的分类

（1）标量子查询

（2）列子查询

（3）行子查询

（4）表子查询（运用多）

2、子查询详解

（1）标量子查询（返回一个值）

标量子查询：把一个sql 执行返回的一个值，作为另一个sql的条件，得到的结果是一行一列，一般出现在where之后，

备注：标量子查询允许使用的比较运算符号：=，！=，>,<,>=,<=

例子：select  dept1 from dept  where dept_name="财务";

select  sum(incoming) from  emp  where  dept2=(select  dept1 from dept where dept_name="财务");

（2）列子查询（返回的结果是一个列）

定义:返回的是一列值

注意点：通常在where之后使用，使用是in或not in ，不运行使用比较运算符，因为他有多个值。

select  dept_name  from  dept  where dept1 in (select  dept2 from  emp where name="张三" or name="牛八")

（3）列子查询

定义：返回的结果是一行多列，一般出现在where的后面

select  * from  emp where(age,incoming ) in (select  age,incoming from  emp  where name="老九");查询年纪，收入和老九一样的所有人的信息

select  sid from  emp where(age,incoming ) in (select  age,incoming from emp  where name="老九"); 查询年纪，收入和老九相同人的sid.

1. 表子查询

定义：返回的结果是多行多列（返回的就是一个表），一般接在from的后面，返回的是一个表。零时表： as  零时表名

select  * from (select  *  from  dept  INNER JOIN  emp  on dept.dept1=emp.dept2)as s where s.dept2>101