话不多说,这一章肯定是重中之重!!!
前面我们讲解的mysql表的查询都是对一张表进行查询,在实际开发中这远远不够。
下面我们用一个简单的公司管理系统,有三张表EMP,DEPT,SALGRADE来演示如何进行多表查询。
为什么需要多表查询
实际开发中往往数据来自不同的表,所以需要多表查询。
例:
显示雇员名、雇员工资以及所在部门的名字
因为上面的数据来自EMP和DEPT表,因此要联合查询。
mysql> select * from EMP, DEPT;
其实我们只要emp表中的deptno = dept表中的deptno字段的记录
mysql> select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;
如何显示部门号为10的部门名,员工名和工资
mysql> select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;
显示各个员工的姓名,工资,及工资级别
mysql> select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;
自连接
自连接是指在同一张表连接查询
显示员工FORD的上级领导的姓名
这个没有用多表,用的是子查询:
mysql> select ename from EMP where empno=(select mgr from EMP where ename='FORD');
使用多表查询:
子查询
什么是子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
单行子查询
返回一行记录的子查询
显示SMITH同一部门的员工
mysql> select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');
多行子查询
返回多行记录的子查询,使用关键字in
如何查询和10号部门的工作相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的
mysql> select * from EMP where job in ( select distinct job from EMP where deptno=10) and deptno <>
10;
在多行子查询中使用all操作符
显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
mysql> select ename, sal, deptno from EMP where sal > all(select sal from EMP where deptno=30);
在多行子查询中使用any操作符
显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号
mysql> select ename, sal, deptno from EMP where sal > any(select sal from EMP where deptno=30);
多列子查询
单行子查询是指子查询只返回单列,单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的,而多列
子查询则是指查询返回多个列数据的子查询语句
例:查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人
mysql> select ename from EMP where (deptno, job)=(select deptno, job from EMP where
ename=’SMITH’) and ename <> ‘SMITH’;
在from子句中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧,把一个子查询当做一个临时表使用。
例:如何显示高于自己部门平均工资的员工的信息
– 获取各个部门的平均工资,将其看作临时表
mysql> select ename, deptno, sal, asal from EMP,
-> (select avg(sal) asal, deptno dt from EMP group by deptno) tmp
-> where EMP.sal > tmp.asal and EMP.deptno=tmp.dt;
查找每个部门工资最高的人的详细资料
mysql> select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
-> (select max(sal) ms, deptno from EMP group by deptno) tmp
-> where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;
显示每个部门的信息(部门名,编号,地址)和人员数量。
方法1:使用多表
mysql> select dname, DEPT.deptno, loc,count(*) '部门人数' from EMP, DEPT
-> where EMP.deptno=DEPT.deptno
-> group by DEPT.deptno;
方法2:使用子查询
– 1. 对EMP表进行人员统计
select count(*), deptno from EMP group by deptno;
– 2. 将上面的表看作临时表
mysql> select DEPT.deptno, dname, mycnt, loc from DEPT,
-> (select count(*) mycnt, deptno from EMP group by deptno) tmp
-> where DEPT.deptno=tmp.deptno;
自我复制(蠕虫复制)
上面使用了多表和子查询两种方式进行查询,到底哪个效率高呢?我们需要弄大量数据来进行测试。可以使用自我复制创建海量数据。
示例:
把EMP表的数据快速变成160000
mysql> create table tmp like EMP; -- 为了做测试,创建tmp表,
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tmp select * from EMP;--将EMP表的数据插入到tmp
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> alter table tmp drop primary key; -- 删除主键属性
mysql> insert into tmp select * from tmp; -- 自我复制
mysql> insert into tmp select * from tmp; -- 自我复制
mysql> select count(*) from tmp; -- 快速的创建了20多万条记录
删除表中的的重复复记录
创建一张表:
mysql> create table tt(id int, name varchar(20));
数据如下:
mysql> select * from tt;
要求:重复的数据只能有一份
思路:
1. 创建一张空表tmp_tt,空表的结构和tt一样
mysql> create table tmp_tt like tt;
2. 将tt表进行distinct,把数据导入空表tmp_tt
mysql> insert into tmp_tt select distinct * from tt;
3. 删除tt表
mysql> drop table tt;
4. 将tmp_tt改名成tt
mysql> alter table tmp_tt rename tt;
合并查询
在实际应用中,为了合并多个select的执行结果,可以使用集合操作符 union,union all
1. union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
例:将工资大于25000和职位是MANAGER的人找出来
mysql> select ename, sal, job from EMP where sal>2500 union
-> select ename, sal, job from EMP where job='MANAGER';--去掉了重复记录
2. union all
该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。
mysql> select ename, sal, job from EMP where sal>2500 union all
-> select ename, sal, job from EMP where job='MANAGER';
外键
外键用于定义主表和从表之间的关系:外键约束主要定义在从表上,主表则必须是有主键约束或unique约束。当定义外键后,要求外键列数据必须在主表的主键列存在或为null。
语法:
foreign key (字段名) references 主表(列)
对上面的示意图进行设计:
先创建主键表
create table myclass (
id int primary key,
name varchar(30) not null comment'班级名'
);
再创建从表
create table stu (
id int primary key,
name varchar(30) not null comment '学生名',
class_id int,
foreign key (class_id) references myclass(id)
);
正常插入数据
mysql> insert into myclass values(10, 'C++大牛班'),(20, 'java大神班');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into stu values(100, '张三', 10),(101, '李四',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
我们现在要插入一个班级号为30的学生,因为没有这个班级,所以插入不成功
mysql> insert into stu values(102, 'wangwu',30);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`mytest`.`stu`, CONSTRAINT `stu_ibfk_1`
FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))
但是可以让班级id为null,比如来了一个学生,目前还没有分配班级
mysql> insert into stu values(102, 'wangwu', null);
综合案例
有一个商店的数据,记录客户及购物情况,有以下三个表组成:
1. 商品goods(商品编号goods_ id,商品名goods_ name, 单价unitprice, 商品类别category, 供应商provider)
2. 客户customer(客户号customer_ id,姓名name,住址address,邮箱email,性别sex,身份证card_id)
3. 购买purchase(购买订单号order_ id,客户号customer_ id,商品号goods_ id,购买数量nums)
要求:
每个表的主外键
客户的姓名不能为空值
邮箱不能重复
客户的性别(男,女)
-- 创建goods表
create table goods(
goods_id int unsigned primary key auto_increment,
goods_name varchar(100) not null default '',
unitprice decimal(10,2) not null default 0.0,
category smallint not null default 0,
provider varchar(100) not null default ''
);
--创建customer
create table customer(
customer_id int unsigned primary key auto_increment,
name varchar(50) not null default '',
address varchar(100) not null default '',
email varchar(60) not null unique,
sex enum('男','女') not null default '男',
card_id varchar(20) not null unique
);
--创建purchase
create table purchase (
order_id varchar(30) not null primary key,
customer_id int unsigned,
goods_id int unsigned,
nums int not null default 0,
foreign key(customer_id) references customer(customer_id),
foreign key(goods_id) references goods(goods_id);
—知识未完,待看下篇!!!