MysQL inner join

CREATE DATABASE dt55_homework CHARACTER SET utf8;
USE dt55_homework;
CREATE TABLE publisher(
    P_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '出版社编号',
    P_NAME VARCHAR(20) UNIQUE NOT NULL COMMENT '出版社名',
    p_LINKMAN VARCHAR(20) NOT NULL COMMENT '出版社联系人',
    P_TEL VARCHAR(20) COMMENT '电话',
    P_ADDRESS VARCHAR(50) COMMENT '地址'
)
CREATE TABLE book(
    B_ID BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '图书编号',
    B_NAME VARCHAR(20) COMMENT '图书名称',
    P_ID BIGINT(20) COMMENT '出版社编号',
    B_AUTHOR VARCHAR(20) COMMENT '图书作者',
    B_PRICE FLOAT COMMENT '价格'
)
#往表中添加数据
#查询出版社的所有信息
SELECT * FROM `publisher`;

#查询所有的图书信息
SELECT * FROM book;

 

#查询B_NAME="java"的所有信息，包括出版社的信息
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM book b INNER JOIN `publisher` p ON b.`P_ID`=p.P_ID
WHERE b.`B_NAME`='java';

#查询所有人民出版的所有书籍
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM book b INNER JOIN `publisher` p ON b.`P_ID`=p.P_ID
WHERE p.p_NAME='人民'

#将出版社p_LINKMAN='聂小虎'的手机号改为xxxx
UPDATE `publisher` SET p_tel='15902738715' WHERE p_LINKMAN='聂小虎';

#查询book表中b_AUTHOR以"王"开头的所有书籍
SELECT * FROM `book` WHERE B_AUTHOR LIKE '王%'

#查询book表中b_AUTHOR以"a"结尾的所有书籍
SELECT * FROM `book` WHERE B_AUTHOR LIKE '%a'

#查询book表中b_AUTHOR中包含"a"的所有书籍
SELECT * FROM `book` WHERE B_AUTHOR LIKE '%a%'

#内连接：inner join
SELECT 字段名1,字段名2....字段n FROM 表1 别名1 INNER JOIN 表2 别名2 ON 条件  WHERE 条件

SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM `publisher` p INNER JOIN book b  ON b.`P_ID`=p.P_ID

#-----------------外连接-------------------
#左外连接:left join
#左外链接当条件不满足时，以左边的表为主
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM `publisher` p LEFT JOIN book b  ON b.`P_ID`=p.P_ID

#右外链接:right join
#右外链接当条件不满足时，以右边的表为主
SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM `publisher` p RIGHT JOIN book b  ON p.P_ID=b.`P_ID`

SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR`
FROM book b LEFT JOIN `publisher` p  ON p.P_ID=b.`P_ID`

#---------------------------3表查询（*****）----------------------------------
#求出teacherId=4的平均得分
#得到总分数
SELECT SUM(score) AS total FROM teacher_question WHERE teacherId=4

SELECT COUNT(*) AS num FROM `teacher_question` WHERE questionId=4 AND teacherId=4

SELECT temp1.total/temp2.num AS 平均分 FROM
(SELECT SUM(score) AS total FROM teacher_question WHERE teacherId=4) temp1,
(SELECT COUNT(*) AS num FROM `teacher_question` WHERE questionId=4 AND teacherId=4) temp2

#需求：想知道王二麻子具体买了哪些商品(商品名，价格，客户名，客户手机号)
SELECT g.goodName,g.price,c.customerName,c.phone
FROM goods g INNER JOIN goods_customer gc INNER JOIN customer c
ON g.id=gc.goodId AND gc.customerId=c.id WHERE c.customerName='王二麻子';