一、多表查询
1.1 联合查询
联合查询:是多表查询的一种方式,在保证多个select语句的查询字段数相同的情况下,合并多个查询结果。常用在分表操作中。
基本语法
select ...
union[all|distinct] select...
[union[all|distinct] select...];
union是实现联合查询的关键字
all和distinct是联合查询的选项
distinct是默认值,可以省略,表示去除完全重复的记录
举个例子
在goods表中,以联合查询的方式获取category_id为9的商品id、name和price,以及category_id为6的商品id、name和keyword。
select id,name,pprice from goods where category_id=9
select id,name,keyword from goods where category_id=6;
若要对联合查询的记录进行排序等操作,需要用()包裹每一个select语句,在select语句内或者在联合查询的最后添加order by语句。若要排序生效,必须在order by 后添加limit限定联合查询排序的数量
举个例子
以联合方式查询,对goods表中category_id为3的商品按价格升序排序,其他类型的产品按价格降序排序,查询的商品信息为id、name和price
(select id,name.price from goods where category_id<>3 order by price desc limit 7) union
(select id,name,price from goods where category_id=3 order by price asc limit 3);
1.2 连接查询
1.2.0准备
建立部门表
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(20) UNIQUE
);
建立员工表
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(20) UNIQUE NOT NULL,
job VARCHAR(20) NOT NULL,
mgr INT ,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
) ;
插入部门表数据
INSERT INTO dept
VALUES
(10, '总裁办'),
(20, '研究院'),
(30, '销售部'),
(40, '运营部');
插入员工表数据
insert into emp values
(1,'张三','销售经理',110,5000,600,20),
(2,'张四','销售经理',110,4000,600,30),
(3,'张五','前台',111,3000,500,30),
(4,'张六','前台',113,3500,550,50),
(5,'张七','前台',112,4000,600,50);
1.2.1 交叉连接cross join
交叉连接产生的结果是笛卡尔积,没有实际应用的意义。
基本语法格式:
select 查询字段 from 表1 cross join 表2;
或
select 查询字段 from 表1,表2;
cross join用于连接两个要查询的表,通过该语句可以查询两个表中所有的数据组合。
1.2.2 内连接inner join
内连接根据匹配条件返回第一个表和第二个表所有匹配成功的记录。
基本语法格式:
select 查询字段 from 表1
[inner] join 表2 on 匹配条件;
on用于指定内连接的查询条件
举个例子
查询已经分配了部门(部门号不为NULL)的员工的信息,员工信息只需要显示员工姓名和对应部门的名称
SELECT ename,dname FROM emp e JOIN dept d ON e.deptno=d.deptno;
1.2.3 左外连接
用于返回连接关键字(left join)左表中所有的记录,以及右表中符合连接条件的记录。当左表的某行记录在右表中没有匹配记录时,右表中相关记录将设为NULL。
基本语法格式:
select 查询字段
from 表1 left [outer] join 表2 on 匹配条件;
关键字left[outer]join左边的表称为左表(主表),右边的表叫右表(从表)。outer在查询时可以省略。
举个例子
查询所有部门名称及部门对应员工的姓名。因为需要查询出所有部门的名称,查询时可以使用左连接查询,将部门表作为查询中的左表
SELECT d.dname,e.ename FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;
1.2.4 右外连接
用于返回连接关键字(right join)右表中所有的记录,以及左表中符合连接条件的记录。当右表的某行记录在左表中没有匹配记录时,左表中相关记录将设为NULL。
select 查询字段
from 表1 right [outer] join 表2 on 匹配条件;
举个例子
查询所有员工姓名及对应部门的名称,没有分配部门的员工也需要查询出来。因为需要查询出所有员工的名称,查询时可以使用右连接查询,将员工表作为查询中的右表
SELECT d.dname,e.ename FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;
多学一招(并不常用)
在连接查询时,若数据表连接的字段同名,则连接时的匹配条件可以使用using代替on。
基本语法格式:
select 查询字段 from 表1 [cross|inner|left|right] join 表2 using(同名的字段连接列表);
二、子查询
2.1 什么是子查询?
在含有子查询的语句中,子查询必须书写在圆括号内。sql语句首先会执行子查询中的语句,然后再将返回的结果作为外层sql的过滤条件,执行顺序是从最里层的子查询开始执行。
2.2 子查询的分类
2.2.1 标量子查询
子查询返回的结果是一个数据,即一行一列。
基本语法格式:
where 条件判断 {=|<>} (select 字段名 from 数据源 [where][group by][having][order by][limit]);
2.2.2 列子查询
子查询返回的结果是一个字符段符合条件的所有数据,即一列多行。
基本语法格式:
where 条件判断 {in|not in} (select 字段名 from 数据源 [where][group by][having][order by][limit]);
2.2.3 行子查询
子查询的结果是一条包含多个字段的记录。
基本语法格式:
where (指定字段名1,指定字段名2)=(select 字段名1,字段名2···from 数据源 [where][group by][having][order by][limit]);
2.2.4表子查询
子查询的返回结果用于from数据源。
基本语法格式:
select 字段列表 from (select语句) [as] 别名 [where][group by][having][order by][limit];
数据源后的都是表名;
2.3 子查询关键字
2.3.1 带exists关键字的子查询
其返回结果只有2种情况:0和1;0代表不成立,1代表成立。
EXISTS关键字用于判断子查询的结果集是否为空,若子查询的结果集不为空,返回TRUE,否则返回FALSE。
使用EXISTS关键字结合子查询进行查询时,会先执行外层查询语句,再根据EXISTS关键字后面子查询的查询结果,判断是否保留外层语句查询出的记录,EXISTS的判断结果为TRUE时,保留对应的记录,否则去除记录。
基本语法格式:
where exists(子查询语句);
举个例子
查询工资大于2900的员工所在的部门信息。
select *from dept
where exists(select * from emp where emp.deptno=dept.deptno and emp.sal>2900);
2.3.2带any关键字的子查询
表示给定的判断条件,只要符合any子查询结果中的任意一个,就返回1,否则返回0;
ANY关键字表示“任意一个”的意思,必须和比较操作符一起使用,例如ANY和>结合起来使用,表示大于任意一个。
ANY关键字结合子查询时,表示子查询的查询结果集中的任一查询结果,例如“值1>ANY(子查询)”,比较值1是否大于子查询返回的结果集中任意一个结果。
基本语法格式:
where 表达式 比较运算符 any (子查询语句);
举个例子
查询部门编号为20的员工信息,要求查询到的员工信息中,工资都高于部门编号为30的部门中的最低工资。
SELECT * FROM emp WHERE deptno=20 AND
sal>ANY(SELECT sal FROM emp WHERE deptno=30);
2.3.3带all关键字的子查询
表示给定的判断条件只有全部符合all子查询的结果时,才返回1,否则返回0;
ANY关键字表示“所有”的意思,ALL关键字结合子查询时,表示子查询的所有查询集中是所有结果,例如“值1>ALL(子查询)”,比较值1是否大于子查询返回的结果集中所有结果。
基本语法格式:
where 表达式 比较运算符 all(子查询语句);
举个例子
查询部门编号为20的员工信息,要求查询到的员工信息中,工资都高于部门编号为30的部门中的最高工资
SELECT * FROM emp WHERE deptno=20 AND
sal>ALL(SELECT sal FROM emp WHERE deptno=30);
注意:
带any、some、all关键字的子查询,不能使用"<=>"比较运算符;
若子查询结果与条件匹配时有NULL,那此条记录不参与匹配;
2.3.4带IN关键字的子查询
举个例子
查询工资大于2900的员工所属部门。
SELECT * FROM dept WHERE deptno IN(SELECT deptno FROM emp WHERE sal>2900);
查询工资小于2900的员工所在的部门信息
SELECT * FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp WHERE sal>2900);
2.3.5比较运算符结合子查询
举个例子
查询与张三职位相同的员工信息。
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='张三')
AND ename !='张三';
三、外键约束
3.1 添加外键约束
3.3.1添加外键约束
在mydb数据库中,以员工表employees和部门表department为例,讲解如何在create table和alter table时添加外键约束的两种方式。
基本语法格式:
ALTER TABLE 从表名 ADD CONSTRAINT [外键名称] FOREIGN KEY(外键字段名)
REFERENCES 主表名(主键字段名);
上述语法格式中,ADD CONSTRAINT表示添加约束;外键名称是可选参数,用来指定添加的外键约束的名称;FOREIGN KEY表示外键约束;使用REFERENCES指定创建的外键引用哪个表的主键。
(1)创建数据表(create table)时添加外键约束
在从表添加外键约束时,首先要保证数据库中已经存在主表,否则会报错“不能添加外键约束”的错误。
举个例子
#(1)在mydb数据库下创建主表
create table mydb.department(
id int unsigned primary key auto_increment comment "部门编号",
name varchar(255) not null comment "部门名称"
)default charset=utf98;
#(2)在mydb数据库下创建从表,添加外键约束
create table mydb.employees(
id int unsigned primary key auto_increment comment "员工编号",
name varchar(255) not null comment "员工姓名",
dept_id int unsigned not null comment "部门编号",
constraint FK_ID foreign key(dept_id) references department(id)#设置外键约束
on delete restrict on update cascade
)default charset=utf8;
注意定义外键约束名称(如FK_ID)时,不可以加单引号或者双引号,constraint ‘FK_ID’ 或constraint “FK_ID”均会报错。
(2)修改数据表(alter table)时添加外键约束
对于已经创建的数据表,使用alter table添加外键约束
举个例子
若mydb数据库中已经有2个数据表department和employees,employees表在创建时未添加外键约束,此时可以通过以下alter table方式实现
alter table mydb.employees
add constraint FK_ID foreign key(dept_id) references department(id)
on delete restrict on update cascade;
3.3.2查看外键约束
可以使用desc查看数据表employees中添加了外键约束的字段信息,如下:
desc mydb.employees dept_id;
#dept_id 为字段名
使用show create table 查看employees表的详细结构,如下:
show create table mydb.employees\G;
3.2 关联表操作
3.2.1添加数据
举个例子
为department中添加id为3,分类名为“研发部”的记录,具体sql语句如下:
insert into mydb.department(id,name) values(3,'研发部');
注意
若提前使用use mydb,则可以不用写mydb.depatment,可直接写department;
3.2.2更新数据
若对主表进行更新操作,从表将按照其建立外键约束设置的on update参数自动执行相应的操作。
举个例子
update mydb.department srt id=1 where name="研发部";
从上可知,仅将主表department中名为“研发部”的id修改为1后,employees中的相关用户的外键dept_id也同时被修改为1.
3.2.3删除数据
若要删除具有on delete restrict 约束关系的主表记录时,一定要先删除从表中对应的数据,然后再删除主表中的数据。
delete from mydb.employees where dept_id =1;
delete from mydb.department where id=1;
3.3 删除外键约束
需要解除两个表之间的关联关系时,就要删除外键约束,基本语法格式如下:
alter table 表名 drop foreign key 外键名;
若要在删除外键约束后,同时删除系统为外键创建的普通索引,则需要通过手动删除索引的方式完成,具体sql语句如下:
alter table mydb.employees drop foreign key FK_ID;
完成上述操作后,可以使用desc查看已删除的外键字段的key值为空,show create table时表中的外键约束以及普通索引全部已删除。