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='王二麻子';