视图综合实训

查询价格大于10元，小于60元的商品名称

SELECT goods.`g_name` FROM goods WHERE goods.price BETWEEN 10 AND 60;

糖类价格大于5元的名称

SELECT goods.`g_name` FROM goods WHERE goods.price > 5 AND goods.type = '糖类';

查询goods表中商品名称、价格、库存数，传递到前端页面，只显示3条

SELECT goods.g_name, goods.price,   goods.num FROM goods LIMIT 3;

查询goods表中所有记录，并按照价格从低到高排序，如价格相同按商品数量从低到高排序

SELECT * FROM goods ORDER BY goods.price, goods.num;

查询员工工资小于总经办所有员工工资的信息

SELECT * FROM staff WHERE staff.money < (SELECT MIN(staff.money) FROM staff INNER JOIN section ON section.section_id = staff.section_id WHERE section.section_title = '总经办')；

查询每个部门员工平均工资

SELECT section.section_title AS '部门' , AVG(staff.money) AS '平均工资' FROM staff INNER JOIN section ON section.section_id = staff.section_id GROUP BY section_title;

创建视图，工资在12000以上的员工姓名部门岗位名称

CREATE VIEW money AS SELECT staff.name AS '员工姓名', section.section_title AS '部门名称', positions.positions_title AS '岗位名称' FROM staff INNER JOIN section ON section.section_id = staff.section_id INNER JOIN positions ON positions.positions_id = staff.positions_id WHERE staff.money > 12000;