#建表 #部门表
create table department(
id int,
name varchar(20)
);
#员工表,之前我们学过foreign key,强行加上约束关联,但是我下面这个表并没有直接加foreign key,这两个表我只是让它们在逻辑意义上有关系,并没有加foreign key来强制两表建立关系,为什么要这样搞,是有些效果要给大家演示一下 #所以,这两个表是不是先建立哪个表都行啊,如果有foreign key的话,是不是就需要注意表建立的顺序了。那我们来建表。
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,‘运营’); #注意这一条数据,在下面的员工表里面没有对应这个部门的数据
#再看一个需求,我要查出技术部的员工的名字 mysql> select name from employee,department where employee.dep_id=department.id and department.name=‘技术’; ERROR 1052 (23000): Column ‘name’ in field list is ambiguous #上面直接就报错了,因为select后面直接写的name,在两个表合并起来的表中,是有两个name字段的,直接写name是不行的,要加上表名,再看: mysql> select employee.name from employee,department where employee.dep_id=department.id and department.name=‘技术’; ±----------+ | name | ±----------+ | egon | | liwenzhou | ±----------+ 2 rows in set (0.09 sec) 结果就没问题了
#示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25;
#首先从部门表里面找到技术部门对应的id
mysql> select id from department where name='技术';
+------+
| id |
+------+
| 200 |
+------+
1 row in set (0.00 sec)
#那我们把上面的查询结果用括号括起来,它就表示一条id=200的数据,然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的name mysql> select name from employee where dep_id = (select id from department where name=‘技术’); ±----------+ | name | ±----------+ | egon | | liwenzhou | ±----------+ 2 rows in set (0.00 sec) 上面这些就是子查询的一个思路,解决一个问题,再解决另外一个问题,你子查询里面可不可以是多个表的查询结果,当然可以,然后再通过这个结果作为依据来进行过滤,然后我们学一下子查询里面其他的内容,往下学。
#查询员工平均年龄在25岁以上的部门名,可以用连表,也可以用子查询,我们用子查询来搞一下
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#连表来搞一下上面这个需求
select department.name from department inner join employee on department.id=employee.dep_id
group by department.name
having avg(age)>25;
总结:子查询的思路和解决问题一样,先解决一个然后拿着这个的结果再去解决另外一个问题,连表的思路是先将两个表关联在一起,然后在进行group by啊过滤啊等等操作,两者的思路是不一样的
#查看技术部员工姓名 select name from employee where dep_id in (select id from department where name=‘技术’);
#查看不足1人的部门名(子查询得到的是有人的部门id) select name from department where id not in (select distinct dep_id from employee);
2、带比较运算符的子查询
#比较运算符:=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
#查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
#department表中存在dept_id=203,Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205,False mysql> select * from employee -> where exists -> (select id from department where id=204); Empty set (0.00 sec)
练习:通过连表的方式来查询每个部门最新入职的那位员工
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
#创建表,只需要创建这一张表 create table employee( id int not null unique auto_increment, name varchar(20) not null, sex enum(‘male’,‘female’) not null default ‘male’, #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int );
#查看表结构 mysql> desc employee; ±-------------±----------------------±-----±----±--------±---------------+ | Field | Type | Null | Key | Default | Extra | ±-------------±----------------------±-----±----±--------±---------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum(‘male’,‘female’) | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | ±-------------±----------------------±-----±----±--------±---------------+
SELECT
*
FROM
emp AS t1
INNER JOIN ( #和虚拟表进行连表
SELECT
post,
max(hire_date) as max_date #给这个最大的日期取个别名叫做max_date,先将每个部门最近入职的最大的日期的信息筛选出来,通过这个表来和我们上面的总表进行关联
FROM
emp
GROUP BY
post
) AS t2 ON t1.post = t2.post #给虚拟表取个别名叫做t2
WHERE
t1.hire_date = t2.max_date; #然后再通过where来过滤出,入职日期和最大日期相等的记录,就是我们要的内容
五 综合练习
表结构为
#创建表及插入记录
CREATE TABLE class (
cid int(11) NOT NULL AUTO_INCREMENT,
caption varchar(32) NOT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO class VALUES (1, ‘三年二班’), (2, ‘三年三班’), (3, ‘一年二班’), (4, ‘二年九班’);