MYSQL基础6

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) UNIQUE NOT NULL COMMENT '图书名',

P_ID BIGINT(20) COMMENT '出版社编号',

B_AUTHOR VARCHAR(20) COMMENT '图书作者',

B_PRICE FLOAT COMMENT '价格'

)

#查询出版社的所有信息

SELECT * FROM `publisher`

#查询B_NAME='计算机基础'的所有信息，包括出版社的信息

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='计算机基础';

#查询所有清华大学出版社出版的所有书籍

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=王伟的手机号修改

UPDATE `publisher` SET P_TEL='11111111111' WHERE P_LINKMAN='王伟';

 

#查询book表中B_AUTHOR以王**开头的所有书籍

SELECT * FROM book WHERE B_AUTHOR='王五';

SELECT * FROM book WHERE B_AUTHOR LIKE '王%';

 

#查询book表中B_AUDHOR以 a 结尾的素有书籍

SELECT * FROM book WHERE B_NAME LIKE '%A';

 

#查询book表中B_AUDHOR包含 C 结尾的素有书籍

SELECT * FROM book WHERE B_NAME LIKE '%C%';

 

#内连接 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 book b INNER JOIN `publisher` p 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 book b LEFT JOIN `publisher` p 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 book b RIGHT JOIN `publisher` p ON b.P_ID=p.P_ID

 

 

#-------------------3表查询--------------------------------

#求出teacherId=4的平均得分

#得到总分数

SELECT SUM(score) AS total FROM `teacher_question` WHERE teacherId=3;

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

 

SELECT

temp1.total / temp2.num AS 平均分

FROM

(SELECT SUM(score) AS total FROM `teacher_question` WHERE teacherId = 3) temp1,

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

 

#需求：想知道1号顾客具体买了哪些商品（商品名，价格，客户名，手机号）

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 = '张三'