《俗人笔记》之《mysql精华语句总结下》

– 查询商品的单价大于10的商品，其总价大于30，并降序显示总价
select product as 商品,sum(price) as 总价
from orders
where price > 10
group by 商品
having sum(price) > 30
order by 总价 desc;
– 笛卡尔积
select * from a,b;
– 内连接
select * from a inner join b where a.id=b.id; – on和where都可以
select * from a inner join b on a.id=b.id;
select * from a ,b where a.id=b.id;
– 外连接
– 1. 左外连接
– 需求 : 列出所有的水果的信息和价格
select a.id ,name,price from a left outer join b on a.id=b.id;
– 2. 右外连接
– 需求 : 列出所有的价格信息和水果
select * from a right outer join b on a.id=b.id;
– 3. 全外连接
– 需求 : 查询所有的水果的信息，和价格的信息
select * from a left outer join b on a.id=b.id – 只能用on
UNION
select * from a right outer join b on a.id=b.id;

– 子查询练习
– 需求 : 查询数学成绩比语文成绩高的所有学生信息
select student.* from student,(select student_id,score from student_course where course_id in(select id from course where name =‘语文’)) as 语文,
(select student_id,score from student_course where course_id in(select id from course where name =‘数学’)) as 数学
where 语文.student_id=数学.student_id and 语文.score < 数学.score and 语文.student_id = student.id;

列出所有员工的姓名及其直接上级的姓名。
select emp.ename ,emp2.ename from emp LEFT OUTER JOIN emp as emp2 on emp.mgr=emp2.empno;-- 前面的值为外键，后面为主键，不能反

删除10号部门薪水最高的员工
DELETE from emp
where
sal in(select * from (select max(sal) from emp where deptno=10) as tp)-- 套一层必须要as一下
and deptno=10;

查询员工姓名，工资和 工资级别
– (工资 >=3000 为3级，工资 >=2000 为2级，工资<2000 为1级)
select ename,sal,case when sal>=3000 then ‘3级’-- 没有符号隔开，要记住
when sal>=2000 then ‘2级’
else ‘1级’
end as 等级 FROM emp;