目录
SQL分类
数据查询语言(Data Query Language,DQL):
DQL主要用于数据的查询,其基本结构是使用SELECT子句,FROM子句和WHERE子句的组合来查询一条或多条数据
数据操作语言(Data Manipulation Language,DML):
DML主要用于对数据 增加、修改和删除的操作
主要包括: INSERT:增加数据 UPDATE:修改数据 DELETE:删除数据
数据定义语言(Data Definition Language,DDL):
DDL主要用针对是数据库对象(数据库、表、索引、视图)进行创建, 修改和删除操作
主要包括: CREATE:创建 ALTER:修改 DROP:删除
数据控制语言(Data Control Language,DCL):DCL用来授予或回收访问数据库的权限
主要包括: GRANT:授予用户某种权限 REVOKE:回收授予的某种权限
事务控制语言(Transaction Control Language,TCL):TCL用于数据库的事务管理。
主要包括: START TRANSACTION:开启事务 SET TRANSACTION:设置事务的属性
COMMIT:提交事务 ROLLBACK:回滚事务
一. DQL查询表中数据
数据查询语言(Data Query Language,DQL):DQL主要用于数据的查询,其基本结构是使用SELECT子句,FROM子句和WHERE子句的组合来查询一条或多条数据
1.数据准备
# 创建员工表
表名 emp
表中字段:
eid 员工id,int
ename 姓名,varchar
sex 性别,char
salary 薪资,
double hire_date 入职时间,date
dept_name 部门名称,varchar
# 创建wuwukai数据库
create database wuwukai;
# 选择wuwukai
use wuwukai;
# 创建员工表
create table emp(
eid int,
ename varchar(20),
sex char(1),
salary double,
hire_date date,
dept_name varchar(20)
);
插入数据
# 添加测试数据
insert into emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部');
insert into emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
insert into emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部');
insert into emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部');
insert into emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部');
insert into emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部');
insert into emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
insert into emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部');
insert into emp VALUES(9,'吴承恩','男',20000,'2000-03-14',null);
insert into emp VALUES(10,'孙悟饭','男', 10,'2020-03-14','财务部');
insert into emp VALUES(11,'兔八哥','女', 300,'2010-03-14','财务部');
insert into emp VALUES(12,'Tom','男', null,'2010-03-14','财务部');
2.简单查询
查询不会对数据库中的数据进行修改, 只是一种显示数据的方式
语法格式
select 字段名 from 表名
需求1: 查询emp中所有的数据
-- 将要查询的字段全部列出
select eid ,ename, sex, salary, hire_date, dept_name from emp;
--查询所有的字段可以使用 * , * 代表所有的字段
select * from emp;
需求2: 查询emp表中所有的记录, 只显示eid和ename字段
select eid, ename from emp;
需求3: 查询所有的员工信息, 将字段名显示为中文
select
eid as '编号', -- as可以省略
ename as '姓名',
sex as '性别',
salary as '薪资',
hire_date as '入职日期',
dept_name as '部门名称'
from emp;
需求4: 查询有多少个部门
-- 查询所有的部门(出现重复部门)
select dept_name from emp;
-- 使用去重查询 关键字 distinct
select distinct dept_name from emp;
需求5: 将所有的员工薪资加1000显示
-- 支持算术运算符 + - * / % 的运算
select eid, ename, salary, salary+1000 from emp;
3.条件查询
·如果查询语句中没有设置条件, 就会查询所有的行信息
·在实际应用中, 一定要指定查询的条件, 对记录进行过滤
语法格式
select 列名 from 表名 where 条件
(1) 比较运算符
(2) 逻辑运算符
需求1: 精确查询
#1 查询员工姓名为黄蓉的员工信息
#2 查询薪水价格为5000的员工信息
#3 查询薪水价格不是5000的所有员工信息
#4 查询薪水价格大于6000元的所有员工信息
#5 查询薪水价格在5000到10000之间所有员工信息
#6 查询薪水价格是3600或7200或者20000的所有员工信息
SQL实现
#1 查询员工姓名为黄蓉的员工信息
select * from emp where ename = '黄蓉';
#2 查询薪水价格为5000的员工信息
select * from emp where salary = 5000;
#3 查询薪水价格不是5000的所有员工信息
select * from emp where salary != 5000;
select * from emp where salary <> 5000;
#4 查询薪水价格大于6000元的所有员工信息
select * from emp where salary > 6000;
#5 查询薪水价格在5000到10000之间所有员工信息
select * from emp where salary >= 5000 and salary <= 10000;
select * from emp where salary between 5000 and 10000;
#6 查询薪水价格是3600或7200或者20000的所有员工信息
select * from emp where salary = 3600 or salary = 7200 or salary = 20000;
select * from emp where salary in (3600, 7200, 20000);
需求2: 模糊查询
#1 查询含有'八'字的所有员工信息
#2 查询以'孙'字开头的所有员工信息
#3 查询第二个字为'兔'的所有员工信息
#4 查询没有部门的员工信息
#5 查询有部门的员工信息
模糊查询 通配符
SQL实现
#1 查询含有'八'字的所有员工信息
select * from emp where ename like '%八%';
#2 查询以'孙'字开头的所有员工信息
select * from emp where ename like '孙%';
#3 查询第二个字为'兔'的所有员工信息
select * from emp where ename like '_兔%';
#4 查询没有部门的员工信息
select * from emp where dept_name is null;
#5 查询有部门的员工信息
select * from emp where dept_name is not null;
4.排序
· 通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影 响真实的数据
语法结构
select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc 升序(默认)
-- desc 降序
(1)单列排序
· 只按照某一个字段进行排序
需求1: 查询所有的员工信息, 使用saraly进行排序
SQL实现
-- 升序排序(默认 asc)
select * from emp order by salary;
-- 降序排序(desc)
select * from emp order by salary desc;
(2) 组合排序
·同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推
需求2: 查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序
SQL实现
-- 组合排序
select * from emp order by salary, hire_date desc;
5.函数
单行函数
单行函数是指对每一条记录的值进行计算,并得到相应的计算结果,然后返回给用户,也就是说,每条记录作为一个输入参数,经过函数计算得到每条记录的计算结果。常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数
使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据
多行函数
我们之前做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用多行函数查询是纵向查询,它是对某一列的值进行计算,然后返回一个单一的值
·多行函数会忽略null空值
·多行函数也称为分组函数, 聚合函数
单行函数
(1)字符串函数
-- 字符串函数的操作
#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
#2 查询emp表所有数据, 将ename第二个字符都换为 某
#3 查询emp表所有数据, 显示ename的长度
#4 查询emp表所有数据, 将 ename有英文的改为都是大写
#5 查询emp表所有数据, 将 ename有英文的改为都是小写
#6 查询emp表所有数据, ename只显示姓
#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat('编号:', eid), concat('姓名:', ename), concat('性别:', sex) from emp;
#2 查询emp表所有数据, 将ename第二个字符都换为 某
select eid, insert(ename, 2, 1, 某'), sex from emp;
#3 查询emp表所有数据, 显示ename的长度
select eid, ename, length(ename), sex from emp;
#4 查询emp表所有数据, 将 ename有英文的改为都是大写
select eid, ename, upper(ename), sex from emp;
#5 查询emp表所有数据, 将 ename有英文的改为都是小写
select eid, ename, lower(ename), sex from emp;
#6 查询emp表所有数据, ename只显示姓
select eid, ename, substring(ename, 1, 1), sex from emp;
(2)数值函数 (Math)
MySQL提供的一张虚拟表中进行演示,该表名为“dual”,是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。 在使用dual表时,如果没有where子句,则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表
-- 数值函数的操作
select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25) from dual;
select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25);
(3)日期与时间函数
-- 日期与时间函数的操作
select curdate(), curtime(), now(), sysdate();
select curdate(), curtime(), now(), sleep(2), sysdate();
(4) 流程函数( IF SWITCH)
-- 流程函数的操作
#1 查询emp表所有数据, 薪资 >= 10000 高工资 <10000 低工资
#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
#3 查询emp表所有数据, 薪资 >= 15000 优秀 >=9000 坚持住 >=5000 加油哦
>= 3000 加把劲 其他 努力奋斗吧骚年
#1 查询emp表所有数据, 薪资 >= 10000 高工资 其他 低工资
select eid, ename, salary, if(salary >= 10000, '高工资', '低工资') from emp;
#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, salary, salary * 12+30000 '年薪' from emp; -- 需要考虑null
select eid, ename, salary, ifnull(salary, 0) * 12+30000 '年薪' from emp;
#3 查询emp表所有数据, 薪资 >=3000 加把劲 >=5000 加油哦 >=9000 坚持住 >= 15000 优秀 其他 不及格
select eid, ename, salary,
case
when salary >= 15000 then '优秀'
when salary >= 9000 then '坚持住'
when salary >= 5000 then '加油哦'
when salary >= 3000 then '加把劲'
else '努力奋斗吧骚年'
end
from emp;
(5)JSON函数
(6)其他函数
-- 其它函数
select database(),user(),version(),inet_aton("192.168.10.1"),inet_ntoa(3232238081);
多行函数
·多行函数会忽略null空值
·多行函数也称为分组函数, 聚合函数
需求:
#1 查询员工的总数
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ...
#3 查询薪水大于4000员工的个数
#4 查询部门为'教学部'的所有员工的个数
#5 查询部门为'市场部'所有员工的平均薪水
#6 查询部门的个数
SQL实现
#1 查询员工的总数
-- 使用某个字段查询,聚合函数会忽略null, 需要注意为null的字段
select count(eid) from emp;
-- 所有字段匹配查询
select count(*) form emp;
-- 增加一列
select 1 from emp;
-- 效率更高推荐使用
select count(1) from emp;
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ...
select
sum(salary) '总薪水',
max(salary) '最高薪水',
min(salary) '最小薪水',
avg(salary) '平均薪水'
from emp;
#3 查询薪水大于4000员工的个数
select count(1) from emp where salary > 4000;
#4 查询部门为'教学部'的所有员工的个数
select count(1) from emp where dept_name = '教学部';
#5 查询部门为'市场部'所有员工的平均薪水
select avg(salary) from emp where dept_name = '市场部';
#6 查询部门的个数
select dept_name from emp; -- 9个
select count(dept_name) from emp; -- 8个
-- 部门去重之后, 统计个数
select count(distinct dept_name) from emp; -- 3个
6.分组
·分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组
语法格式
select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件]
需求1: 通过性别分组
SQL实现
# 按照性别分组查询
select * from emp group by sex; -- 能查到结果, 但是没有意义
select sex from emp group by sex; -- 正确操作
分析: group by 分组过程
注意事项:
·分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作
·查询其他字段没有意义
需求1:
#1 查询每个部门的名称
#2 查询每个部门的平均薪资
#3 查询每个部门的平均薪资, 部门名称不能为空
SQL实现
#1 查询每个部门的名称
select dept_name from emp group by dept_name;
#2 查询每个部门的平均薪资
select dept_name, avg(salary) from emp group by dept_name;
#3 查询每个部门的平均薪资, 部门名称不能为空
select dept_name from emp where dept_name is not null group by dept_name;
需求2:
#4 查询每个部门的平均薪资, 只显示平均工资在4000以上的
SQL实现
select dept_name, avg(salary) from emp where avg(salary) > 4000 group by dept_name;
-- Invalid use of group function 报错
分析:
1) 需要在分组后, 对数据进行过滤, where的作用是在分组前过滤
2) select语句的执行顺序
from -- where -- group by -- having –- select -- order by
3)分组操作中的having子语句, 适用于对分组后的数据进行过滤的, 作用类似于 where
SQL实现:
#4 查询每个部门的平均薪资, 只显示平均工资在4000以上的
select dept_name, avg(salary) from emp group by dept_name having avg(salary) > 4000;
where 和 having的区别
group by 和 having练习
#1 统计每个部门中的最小工资, 列出最小工资小于2000的部门名称
#2 统计平均工资大于3000的部门名称
#3 统计人数小于4的部门的平均工资
#4 统计每个部门最高工资, 排除最高工资小于3000的部门
#1 统计每个部门中的最小工资, 列出最小工资小于4000的部门名称
-- 每个部门的最小工资
select dept_name, min(salary) from emp group by dept_name;
select dept_name, min(salary) from emp group by dept_name having min(salary) < 4000;
#2 统计平均工资大于6000的部门名称
-- 每个部门的平均工资
select dept_name, max(salary) from emp group by dept_name;
select dept_name, max(salary) from emp group by dept_name having max(salary) > 6000;
#3 统计人数小于4个人部门的平均工资
-- 每个部门的平均工资和人数
select dept_name, avg(salary), count(1) from emp group by dept_name;
select dept_name, avg(salary), count(1) from emp group by dept_name having count(1) < 4;
#4 统计每个部门最高工资, 排除最高工资小于10000的部门
-- 每个部门的最高工资
select dept_name, max(salary) from emp group by dept_name
select dept_name, max(salary) from emp group by dept_name having max(salary) >= 10000;
7.limit关键字
作用:
·limit是限制的意思, 限制返回的查询结果的函数(通过limit函数,控制查询返回多少行数据)
·limit 语法是 MySql的方言, 用来完成分页
语法结构
select 字段1, 字段2 ... from 表名 limit offset, length;
参数说明
1) offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据
2) length 返回的行数
需求1:
#1 查询emp表中的前5条数据
#2 查询emp表中 从第4条开始, 查询6条
SQL实现
#1 查询emp表中的前5条数据
select * from emp limit 5; -- 不指定从哪行还是, 默认从0开始
select * from emp limit 0, 5;
#2 查询emp表中 从第4条开始, 查询6条
select * from emp limit 3, 6; -- 从0开始, 所以第四条数据为3
二. 多表
1.多表的概述
实际开发中, 一个项目通常需要很多张表才能完成
例如一个商城项目的数据库, 需要很多张表: 如 用户表, 分类表, 商品表. 订单表...
2.单表的缺点
数据准备
创建一张员工表 emp
字段: eid, ename, age, dep_name, dep_location
eid为主键并且自增
添加五条测试数据
create table emp(
id int primary key auto_increment,
ename varchar(10),
age int,
dep_name varchar(10),
dep_location varchar(10)
);
-- 添加数据
insert into emp (ename, age, dep_name, dep_location) values('张百万', 20, '研发部', '广州');
insert into emp (ename, age, dep_name, dep_location) values('赵四', 21, '研发部', '广州');
insert into emp (ename, age, dep_name, dep_location) values('广坤', 20, '研发部', '广州');
insert into emp (ename, age, dep_name, dep_location) values('小斌', 20, '销售部', '深圳');
insert into emp (ename, age, dep_name, dep_location) values('艳秋', 22, '销售部', '深圳');
insert into emp (ename, age, dep_name, dep_location) values('大玲子', 18, '销售部', '深圳');
单表存在的问题
冗余, 同一个字段出现大量重复的数据
解决方案
(1)设计为两张表
多表方式设计
employee 员工表: eid ename age
department 部门表: depid, dep_name, dep_location
(2)创建员工和部门表
-- 创建员工表
create table employee(
eid int primary key auto_increment,
ename varchar(10),
age int,
dep_id int
);
-- 创建部门表
create table department(
dep_id int primary key auto_increment,
dep_name varchar(10),
dep_location varchar(10)
);
(3)插入数据
-- 添加2个部门
insert into department values(default, '研发部','广州'),(default, '销售部', '深圳');
select * from department;
-- 添加5个人员工
insert into employee values(default, '张百万', 20, 1),
(default, '赵四', 21, 1),
(default, '广坤', 20, 1),
(default, '小斌', 20, 2),
(default, '艳秋', 22, 2),
(default, '大玲子', 18, 2);
select * from employee;
表关系分析
1)员工表中有一个字段dep_id与部门表中的主键对应字段,这个字段就叫做关系属性
2) 拥有关系属性的员工表被称为 从表, 与外键对应的主键所在的表叫做 主表
多表设计上的问题
当我们在员工表的dep_id里面输入了不存在的部门编号, 数据依然可以添加, 显然这是不合理的
例如:
-- 插入一条 不存在部门的数据
insert into employee values(default, '张亿万', 20, 100),
应该保证员工表所添加的dep_id, 必须在部门表dep_id中存在
解决方案:
使用外键约束, 约束员工表中的dep_id必须在部门表dep_id中存在
3. 外键约束
1) 外键
外键指的是在主表中与从表的主键对应的的那个字段, 如员工表的dep_id, 就是外键
2) 外键约束
使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表的引用的完整性
创建外键约束
语法格式:
1. 新建表时添加外键约束
constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段);
2. 为已创建好的表添加外键约束
alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名)
references 主表名(主键字段名);
需求: 为employee表的 dep_id字段添加外键约束
1. 为已经创建好的employee表添加外键约束
alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(dep_id);
2. 新创建employee表时, 为dep_id添加外键约束
create table employee(
eid int primary key auto_increment,
ename varchar(10),
age int,
dep_id int,
-- 添加外键约束
constraint emp_dep_fk foreign key(dep_id) references department(dep_id)
);
3. 测试数据
-- 插入正常数据(从表的外键对应着主表的主键)
insert into employee values(default, '张百万', 20, 1), (default, '艳秋', 22, 2);
-- 插入不存在的dep_id
insert into employee values(default, '张亿万', 20, 100);
-- Cannot add or update a child row: a foreign key constraint fails
删除外键约束
语法格式:
alter table 从表名 drop foreign key 外键约束的名称
需求: 删除employee表中的外键约束
alter table employee drop foreign key emp_dep_fk;
外键约束的注意事项
(1) 从表的外键类型必须和主表的主键类型保持一致
(2) 添加从表数据时
从表中添加的外键值, 必须在主表的主键中存在
(3)删除和变更数据主表数据时
先删除从表中的数据或将外键设置为null, 再删除主表中的数据
-- 删除部门表中主键为1的部门信息
delete from department where dep_id = 1;
-- 报错信息如下
-- Cannot delete or update a parent row: a foreign key constraint fails
选项 | 作用 |
RESTRICT(默认) | 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 |
No action | 同RESTRICT, 都是立即检查外键约束 |
CASCADE | 在主表上update/delete记录时,同步update/delete掉从表匹配的记录 |
SET NULL | 在父表上update/delete记录时,将子表上匹配记录的列设为null 注意: 子表的外键列不能为not null |
三. 多表关系设计
实际的开发过程中, 一个项目通常需要很多张表才能完成. 例如: 一个商城项目就需要分类表(category), 商品表(products), 订单表(orders)等多张表. 而且这些表的数据之间存在一定的关系, 接下来我们一起学习以下多表设计方面的知识
表与表之间的3种关系 | 例如 |
一对多关系(最常见) | 员工表和部门表 学生表和班级表 |
多对多关系 | 学生表和课程表 用户表和角色表 |
一对一关系(使用较少) | 一对一的关系可以设计成一张表 |
1.一对多关系(常见)
·一对多关系(1 : n)
例如: 班级和学生, 部门和员工, 客户和订单, 类别和商品
·一对多建表原则
在从表(多方)创建一个字段, 该字段作为外键指向主表的主键
2.多对多关系
·多对多关系(m : n)
例如: 老师和学生, 学生和课程, 用户和角色
·多对多关系建表原则
多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多
3.一对一关系(了解)
·多对多关系(1 : 1)
在实际的开发过程中应用不多, 因为一对一的关系可以设计为一张表
四. 多表查询
1.什么是多表查询
·DQL: 查询多张表, 获取到需要的数据
·比如: 我们要查询家电分类下都有哪些商品, 那么我们就需要查询分类与商品两张表
2.数据准备
(1)创建分类表与商品表
#分类表 (一方 主表)
create table category (
cid varchar(32) primary key,
cname varchar(50)
);
#商品表 (多方 从表)
create table products(
pid varchar(32) primary key,
pname varchar(50),
price int,
flag varchar(2), # 是否上架标记为:1表示上架、0表示下架
cid varchar(32),
-- 添加外键约束
foreign key (cid) references category (cid)
);
(2) 插入数据
#分类数据
insert into category(cid,cname) values('c001','家电');
insert into category(cid,cname) values('c002','鞋服');
insert into category(cid,cname) values('c003','化妆品');
insert into category(cid,cname) values('c004','汽车');
#商品数据
insert into products values('p001','小米电视机',5000,'1','c001');
insert into products values('p002','格力空调',3000,'1','c001');
insert into products values('p003','美的冰箱',4500,'1','c001');
insert into products values('p004','篮球鞋',800,'1','c002');
insert into products values('p005','运动裤',200,'1','c002');
insert into products values('p006','T恤',300,'1','c002');
insert into products values('p007','冲锋衣',2000,'1','c002');
insert into products values('p008','神仙水',800,'1','c003');
insert into products values('p009','大宝',200,'1','c003');
3.笛卡尔积
交叉连接(CROSS JOIN)是对两个或者多个表进行笛卡儿积操作,所谓笛卡儿积就是关系代数里的一个概念,表示两个表中的每一行数据任意组合的结果
笛卡尔积不管是否匹配,都连接。没有实际意义,有理论意义
笛卡尔积便于理解连接查询的原理
使用语法
select 字段名 from 表1 cross join 表2;
使用交叉连接, 查询分类表与商品表
select * from products cross join category;
4.多表查询的分类
(1)内连接查询
内连接的特点
·通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上就不显示
·如: 按照从表的外键 = 主表的主键进行匹配 | inner join on, inner可以省略
隐式内连接
from子句后面直接写多个表名 使用where指定连接条件的 这种连接方式是隐式内连接. 使用where条件过滤无用的数据
语法格式
select 字段名 from 表1, 表2 where 连接条件;
需求1: 查询分类信息以及对应的商品信息
select * from category, products where category.cid = products.cid;
需求2: 查询分类信息以及对应的商品信息, 只显示类别名称, 商品名称, 价格
-- 可以为表定义别名, 方便查询
select c.cname, p.pname, p.price from category c, products p where c.cid = p.cid;
显示内连接
使用 inner join ...on 这种方式, 就是显式内连接
语法格式
select 字段名 from 表1 [inner] join 右表 on 条件
需求1: 查询分类信息以及对应的商品信息
select * from category c inner join products p on c.cid = p.cid;
需求2: 查询鞋服分类下, 价格大于500的商品名称和价格以及对应的分类名称
# 思路分析
-- 1.查询哪几张表 category products
-- 2.表之间的连接条件 主表主键 = 从表外键
-- 3.查询条件 p.price > 500 and c.cname = '鞋服'
-- 4.查询的字段 cname pname price
select
c.cname,
p.pname,
p.price
from category c inner join products p on c.cid = p.cid
where p.price > 500 and c.cname = '鞋服';
注意事项:
·内连接只会显示匹配的数据
·内连接不能实现不匹配数据的显示
on和where的区别
先抛开where条件,看On条件。left join时,不管on条件是否成了,左表的所有行数据都会select出来(不管右表条件是否成立),右表成立就select对应数据,不成立就置null。有可能是一对多。
①当两张表是在left join来关联时,条件跟在on后面:无论on条件是否成立,都会返回左表的值;
②当两张表是在left join来关联时,当条件放在where后面时:只有满足条件的记录会返回。
(2)外连接查询
通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上也可以显示
左外连接
·使用 left outer join , outer 可以省略
语法格式
select 字段名 from 表1 left join 表2 on 条件;
需求1: 查询分类信息以及对应的商品信息
select * from category c left join products p on c.cid = p.cid;
左外连接的特点:
1) 以左表为主, 左表中的数据全部显示
2) 右表匹配到数据就显示匹配到的数据
3) 右表没有匹配的数据显示为null
需求2: 使用左外连接查询每个分类下的商品名称, 商品个数
# 思路分析
-- 1.查询哪几张表 category products
-- 2.表之间的连接条件 主表主键 = 从表外键
-- 3.查询条件 每个类别 需要分组
-- 4.查询的字段 类别名称 商品数量
select c.cname, count(p.cid) from category c left join products p on c.cid = p.cid
group by c.cname;
右外连接
·使用 right outer join, outer可以省略
语法格式
select 字段名 from 表1 right join 表2 on 条件;
需求1: 查询商品信息以及对应的分类信息
select * from products c right join category p on c.cid = p.cid;
左外连接的特点:
1) 以右表为主, 右表中的数据全部显示
2) 左表匹配到数据就显示匹配到的数据
3) 左表没有匹配的数据显示为null
(3)内连接和外连接的总结
内连接: inner join: 只获取两张表中 交集部分的数据.
左外连接: left join: 以左表为基准, 查询左表的所有数据, 以及与右表有交集的部分
右外连接: right join: 以右表为基准, 查询右表的所有的数据, 以及与左表有交集的部分
附加:思维导图(图片不清楚,下面附加了网址哦)
MySQL:关系型数据库管理系统(2) | 八九. | 思维导图(新) | ProcessOn
密码:1Il4