数据库学习——第三天

今日内容：

• 连接查询：内连接/左连接/右连接/自关联
• 子查询：查询语句中包含另一个查询语句
• 数据库高级：数据库ER模型/外键/索引
  

内连接

-- 需求1: 查询所有存在商品分类的商品信息
SELECT * FROM goods;
SELECT * FROM category;
-- 内连接: select * from 表1 inner join 表2 on 表1.列=表2.列
-- 显示效果: 两张表中有对应关系的数据都会显示出来, 没有对应关系的数据
-- 均不再显示
SELECT * FROM goods
INNER JOIN category ON goods.typeId = category.typeId;
-- 扩充: 给表起别名(1> 缩短表名利于编写 2> ⽤别名给表创建副本)
SELECT * FROM goods go
INNER JOIN category ca ON go.typeId = ca.typeId;
-- 扩展: 内连接的另⼀种写法(旧式写法)
-- SELECT * FROM 表名1,表名2 WHERE 表名1.列1=表名2.列2;
SELECT * FROM goods,category WHERE goods.typeId=category.typeId;

左连接

-- 需求2: 查询所有商品信息,包含商品分类
-- 左连接: select * from 表1 left join 表2 on 表1.列=表2.列
-- 注意: 如果要保证⼀张数据表的全部数据都存在, 则⼀定不能选择内连接,
-- 可以选择左连接或右连接
-- 说明:
-- 以 left join 关键字为界, 关键字左侧表为主表(都显示), ⽽关键字右侧
-- 的表为从表(对应内容显示, 不对应为 null)
SELECT * from goods go
LEFT JOIN category ca ON go.typeId=ca.typeId;
-- 扩充需求: 以分类为主展示所有内容(以哪张表为主表, 显示结果上是有区别
-- 的!)
SELECT * FROM category ca
LEFT JOIN goods go ON ca.typeId=go.typeId;

右连接

-- 需求3: 查询所有商品分类及其对应的商品的信息
-- 右连接: select * from 表1 right join 表2 on 表1.列=表2.列
-- 说明:
-- 以 right join 关键字为界, 关键字右侧表为主表(都显示), ⽽关键字左侧
-- 的表为从表(对应内容显示, 不对应为 null)
SELECT * FROM  goods go
RIGHT JOIN category ca ON ca.typeId=go.typeId;
-- 扩充需求: 查询所有商品信息及其对应分类信息
SELECT * FROM category ca 
RIGHT JOIN goods go ON ca.typeId =go.typeId;

补充: 存在左右连接的必要性

说明: 能够体现左右连接必要性的场景为: ⾄少为三张表进⾏连接查询
注意: 实际⼯作中, 最多也就三张表连接查询

自关联

前提: 1> 数据表只有⼀张 2> 数据表中⾄少有两个字段之间有某种联系
⽅式: 通过给表起别名的形式, 将原本只有⼀张的数据表变为两张, 然后通过对
应字段实现连接查询

查询河南省下所有市的信息

-- 需求4: 查询河南省所有的市
-- 说明: ⽆论是使⽤内连接还是左连接, 都只影响中间数据表的内容多少, 由
-- 于最终的过滤条件相同, 因此查询结果⼀致
-- 使⽤内连接

SELECT * FROM areas;

SELECT * FROM areas a1
INNER JOIN areas a2 ON a1.aid=a2.pid
WHERE a1.atitle='河南省';

-- 使⽤左连接
SELECT * FROM areas a1
LEFT JOIN areas a2 ON a1.aid=a2.pid
WHERE a1.atitle='河南省';

-- 使用右连接
SELECT * FROM areas a1
RIGHT JOIN areas a2 ON a1.aid=a2.pid
WHERE a1.atitle='河南省';

查询河南省下所有的市和区的信息

-- 需求5: 查询河南省的所有的市和区
-- 说明: 想要实现三级⾏政单位显示, 需要分别处理省和市及市和区(三表连
-- 查)
SELECT * from areas a1
LEFT JOIN areas a2 ON a1.aid=a2.pid
LEFT JOIN areas a3 ON a2.aid=a3.pid
WHERE a1.atitle ='河南省';

子查询

⼦查询: 在⼀个 select 语句中，嵌⼊了另外⼀个 select 语句，那么嵌⼊的
select 语句称之为⼦查询语句
作⽤: ⼦查询是辅助主查询的，要么充当[条件]，要么充当[数据源]

子查询语句充当条件

-- 需求6: 查询价格⾼于平均价的商品信息
-- ⼦查询语句充当条件:
-- 求取平均价
SELECT * FROM goods;

SELECT AVG( price) FROM goods;
-- 说明: 充当⼦查询的语句需要使⽤括号括起来(运算优先级括号最⾼!)否则报
-- 错
SELECT * FROM goods WHERE price > (SELECT AVG(price) FROM goods);

子查询语句充当数据源

-- 需求7: 查询所有来⾃并夕夕的商品信息, 包含商品分类
-- ⼦查询语句充当数据源:

SELECT * FROM goods;
SELECT * FROM category;

SELECT * FROM goods go
LEFT JOIN category ca ON go.typeId=ca.typeId
WHERE company='pxx';
-- 问题: 连接查询的结果中, 表和表之间的字段名不能出现重复, 否则⽆法直
-- 接使⽤
-- 解决: 将重复字段使⽤别名加以区分(表名.* : 当前表的所有字段)
SELECT * FROM (SELECT go.*,ca.id cid ,ca.typeId ctypeId,
ca.cateName cname FROM goods go LEFT JOIN category ca ON
go.typeId=ca.typeId ) new
WHERE new.company='pxx';

数据库⾼级扩展内容(了解)

ER 模型

E 表示 entry，实体: 描述具有相同特征事物的抽象[数据表]
属性: 每个实体的具有的各种特征称为属性[数据(表内的字段)]
R 表示 relationship，联系: 实体之间存在各种关系，关系的类型包括包括⼀
对⼀、⼀对多、多对
多[表和表之间的联系]

外键

说明: 通过外部数据表的字段, 来控制当前数据表的数据内容变更, 以避免单⽅
⾯移除数据, 导致关联表数据产⽣垃圾数据的⼀种⽅法
注意: 如果⼤量增加外键设置, 会严重影响除数据查询操作以外的其他操作(增/
删/改)的操作效率, 因此在实际项⽬中很少会被采⽤, 但是在⾯试中容易被问
到.

索引

说明: 可以⼤幅度提⾼查询语句的执⾏效率
注意: 如果⼤量增加索引设置, 会严重影响除数据查询操作以外的其他操作(增/
删/改)的操作效率, 不⽅便过多添加.
-- 开启运⾏时间监测
set profiling=1;
-- 查找第⼀万条数据 10000
select * from test_index where num='10000';
-- 查看执⾏的时间
show profiles;
-- 为表 text_index 的 num 列创建索引
create index test_index on test_index(num);
-- 执⾏查询语句
select * from test_index where num='10000';
-- 再次查看执⾏的时间
show profiles;

声明：是学习黑马测试教程的，有兴趣可以点击链接学习学习入口