5.21日总结

学习了sql语句

SELECT j.jname 
FROM spj 
LEFT JOIN j ON j.jno = spj.jno 
LEFT JOIN s ON s.sno = spj.sno 
LEFT JOIN p ON p.pno = spj.pno
WHERE p.pname != '螺丝刀' AND p.color != '红';

SELECT 
    COUNT(DISTINCT jno) AS 种类数,
    AVG(weight) AS 平均重量
FROM 
    spj
WHERE 
    pno = (SELECT pno FROM p WHERE pname = '螺丝刀');

SELECT p.pname, COUNT(spj.sno) AS 数量 
FROM spj 
LEFT JOIN p ON p.pno = spj.pno 
WHERE spj.sno = 's1' 
GROUP BY p.pname;