- # 建表
- create table department(
- id int,
- name varchar(20)
- );
- create table employee(
- id int primary key auto_increment,
- name varchar(20),
- sex enum('male','female') not null default 'male',
- age int,
- dep_id int
- );
- # 插入数据
- insert into department values
- (200,'技术'),
- (201,'人力资源'),
- (202,'销售'),
- (203,'运营');
- insert into employee(name,sex,age,dep_id) values
- ('egon','male',18,200),
- ('alex','female',48,201),
- ('wupeiqi','male',38,201),
- ('yuanhao','female',28,202),
- ('liwenzhou','male',18,200),
- ('jingliyang','female',18,204)
- ;
内连接:
select * from employee inner join department on employee.dep_id=department.id;
左连接: 在内连接的基础上保留左表的记录
select * from employee left join department on employee.dep_id=department.id;
右连接:
select * from employee right join department on employee.dep_id=department.id;
全外连接: 在内连接的基础上保留左右两表没有对应关系的记录
- select * from employee left join department on employee.dep_id=department.id
- union
- select * from employee right join department on employee.dep_id=department.id;
- # 查询平均年龄大于 30 岁的部门
- select department.name,avg(age) from employee inner join department on employee.dep_id=department.id
- group by department.name
- having avg(age)>30;
SELECT 语句关键字的定义顺序
SELECT 语句关键字的执行顺序
执行 FROM
执行 ON
执行外部行
来源: http://www.bubuko.com/infodetail-3422976.html