多表查询

-- 查看每个老师的授课  两表查询
select name,course_name FROM teacher,course  -- 表之间使用 , 隔开
WHERE course.teacher_id=teacher.id     -- 两个表中之间的对应关系 course表中的 teacherid 与teacher表中的id一致。
-- 查看年龄大于30岁的老师，以及对应的授课名称
select name,course_name FROM teacher,course  -- 表之间使用 , 隔开
WHERE course.teacher_id=teacher.id 
AND teacher.age>30

三表查询

-- 查询学生姓名和学生的选课课程名
SELECT name,course_name FROM student,course,student_course
WHERE student.id = student_course.student_id  -- 对应的联系
AND course.id = student_course.course_id

在做多表查询的时候，首要找到多表之间的关联的字段。

临时表作为多表中的一个

下面有两个表
customers

订单表 orders

现在要统计顾客的订单数

-- orders表中可以使用分组查询的方式统计数据
SELECT cust_id, COUNT(*) as 订单数 FROM orders GROUP BY cust_id;

MySQL-04 - 图6
根据这个结果表，如果直接与上面的顾客表进行关联，
关联对象位 cust_id, 使用多表查询

select cust_name,订单数 from customers, tmp
where cusetomers.cust_id = tmp.cust_id

对应的语句

SELECT customers.cust_id, cust_name, 订单数 from  customers,(SELECT cust_id, COUNT(*) as 订单数 FROM orders GROUP BY cust_id) as tmp
WHERE customers.cust_id = tmp.cust_id

inner join 内连接

多个表查询的时候
主要语法
inner join 表 on 表之间的关系语句。

-- INNER JOIN 表 ON  
-- 两个表关系
select * from teacher
INNER JOIN course
ON teacher.id = course.teacher_id
SELECT name,course_name FROM teacher
INNER JOIN course
ON teacher.id = course.teacher_id
-- 三个表之间关联
SELECT name,course_name FROM student  -- 学生表
INNER JOIN student_course    -- 学生选课表
ON student.id = student_course.student_id 
INNER JOIN course           -- 对应的课程表
ON course.id = student_course.course_id

多表查询中添加条件过滤

-- 年龄大于20 并且姓名 3个字
SELECT name,course_name,age FROM  student_course -- 学生表
INNER JOIN   course   -- 学生选课表
ON course.id = student_course.course_id
INNER JOIN    student         -- 对应的课程表
ON student.id = student_course.student_id 
where age>20 and name like "___"  -- 过滤年龄大于20岁 并且 姓名三个字

-- 查询 商品名，产地名
SELECT prod_name,vend_name from products
INNER JOIN vendors
ON products.vend_id = vendors.vend_id

左连接

left join on
以左表为主，左表的数据都会显示。

SELECT vendors.vend_id,vend_name,prod_name from vendors  -- 左表
LEFT JOIN products
ON products.vend_id = vendors.vend_id

左表中的所有数据为主，第二表中没有数据与第一个表关联，则显示 Null。

下面两张表

-- 使用左连接方式 查询 课程名，老师的名字。 如果课程没有对应的老师，老师名字下面显示为Null
select course_name as 课程,name as 名字 from course
LEFT JOIN teacher
on course.teacher_id = teacher.id

三个表中也可以使用左连

从上面三个表中进行查询，学生选课信息

-- 学生姓名，课程名， 如果该学生没有选课内容，课程名显示为 Null
SELECT name,course_name FROM student
LEFT JOIN student_course
ON student.id = student_course.student_id
LEFT JOIN course
ON course.id = student_course.course_id

右联结

以右边的表为依据进行连接。

-- 右连接 以右边表为主表。
select course_name as 课程,name as 名字 from course
RIGHT JOIN teacher
on course.teacher_id = teacher.id

在工作过程中，大部分人一直使用左联的方式进行sql编写。

union

两个表中都有age 字段，

-- union  在连接两个表的时候如果数据有相同，会自动去重
SELECT age FROM student
UNION
SELECT age from teacher;

union All 连接两个表的时候，不会将重复数据去重。

SELECT  age FROM student
UNION ALL
SELECT  age from teacher;

作业

所有的表都在数据库中

外网地址：rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com
端口号：3306
用户名： fanmao60
密码：        ABCfanmao60
数据库： lianxi   （lainxi_zh）表的名称为中文

基本语句复习

查询年龄大于 38 的教师信息
查询身份证号包含 X 的教师信息
查询年龄大于 18 和姓氏王的学生信息
查询年龄大于 15 或姓氏王的学生信息
age 在 30 和 50 之间的教师
teacher 表为例，将查询出来的所有结果集按照年龄 age 从小到大排序
teacher 表为例，将查询出来的结果集按照 age 从大到小排序之后，再按照 id 字段从小到大排序
教师表，对 name 字段默认排序
求出全部教师平均年龄
统计全部学生数量
所有教师年龄总和
教师表中取年龄最小值的数据
教师表中取年龄最大值的数据

联结练习

内联结查看学生姓名，对应的选课名称
左连接查看学生姓名，对应的选课名称（学生表为主）
查询 products（商品表），orderitems (订单详情表) 中订单详情，统计每个商品的销售情况 order_num（订单号），prod_id(商品id)，prod_price(商品价格), total_price（总价）
```
SELECT order_num as 订单号,  orderitems.prod_id as 商品id, prod_price as 商品单价, (quantity*prod_price) as 总价
FROM orderitems
INNER JOIN products
ON
orderitems.prod_id = products.prod_id
```
查询orders（订单表）,customers(会员表)，orderitems(订单详情表) 统计每个订单的 order_time(订单时间)，order_num（订单号），cust_name(会员名称), total_price (此订单总额)

--1 每个订单的 总额
select order_num, sum(quantity*item_price) as 订单总额 FROM orderitems
GROUP BY order_num;  -- 结果作为临时表数据

select order_date, orders.order_num, cust_name, 订单总额 
FROM (select order_num, sum(quantity*item_price) as 订单总额 FROM orderitems GROUP BY order_num) as tmp
INNER JOIN orders
ON orders.order_num = tmp.order_num
INNER JOIN customers
ON orders.cust_id = customers.cust_id
ORDER BY 订单总额;