面试官 求求别再问我MySQL了＜二＞
目录
SQL分类
一. DQL查询表中数据
1.数据准备
2.简单查询
3.条件查询
(1) 比较运算符
(2) 逻辑运算符
4.排序
(1)单列排序
(2) 组合排序
5.函数
单行函数
(1)字符串函数
(2)数值函数 （Math）
(3)日期与时间函数
(4) 流程函数（ IF SWITCH）
(5)JSON函数
(6)其他函数
多行函数
6.分组
7.limit关键字
二. 多表
1.多表的概述
2.单表的缺点
单表存在的问题
表关系分析
多表设计上的问题
3. 外键约束
三. 多表关系设计
1.一对多关系(常见)
2.多对多关系
3.一对一关系(了解)
四. 多表查询
1.什么是多表查询
2.数据准备
(1)创建分类表与商品表
(2) 插入数据
3.笛卡尔积
4.多表查询的分类
(1)内连接查询
隐式内连接
显示内连接
(2)外连接查询
左外连接
右外连接
(3)内连接和外连接的总结
附加：思维导图（图片不清楚，下面附加了网址哦）
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 入职时间，datedept_name 部门名称，varchar# 创建wuwukai数据库create database wuwukai;# 选择wuwukaiuse 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;-- 使用去重查询 关键字 distinctselect 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 性别: xselect 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; -- 需要考虑nullselect eid, ename, salary, ifnull(salary, 0) * 12+30000 '年薪' from emp;#3 查询emp表所有数据, 薪资 >=3000 加把劲 >=5000 加油哦 >=9000 坚持住 >= 15000 优秀 其他 不及格select eid, ename, salary, casewhen salary >= 15000 then '优秀'when salary >= 9000 then '坚持住'when salary >= 5000 then '加油哦'when salary >= 3000 then '加把劲'else '努力奋斗吧骚年'endfrom 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) 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) = 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_locationeid为主键并且自增添加五条测试数据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_idinsert 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 priceselect c.cname,p.pname, p.pricefrom category c inner join products p on c.cid = p.cidwhere 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.cidgroup 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
创作打卡挑战赛 
赢取流量/现金/CSDN周边激励大奖