cgb2106-day05

一,视图

–1,概述

可以把SQL的查询结果缓存起来,存入视图中.
好处: 简化了SQL的编写
坏处: 没法做SQL的优化,占用空间.

–2,测试

#1.创建视图:缓存了查询结果
CREATE VIEW `cgb2106`.`emp_view` 
AS
#以下的SQL执行的结果会被缓存到视图中
(SELECT * FROM emp WHERE ename LIKE '%a%');

#2.使用视图
#SELECT * FROM emp WHERE ename LIKE '%a%'
SELECT * FROM emp_view 
#直接查的视图,视图就是一张特殊的表,最好只做查询

二,SQL练习

–1,测试

SELECT * FROM emp #低效
SELECT empno,ename,job,sal FROM emp #高效
SELECT * FROM emp WHERE empno=100
SELECT * FROM emp WHERE job LIKE '总%'#高效
SELECT * FROM emp WHERE job LIKE '_总'#_通配一个字符
SELECT * FROM emp WHERE job LIKE '%总%'#%通配n个字符
SELECT * FROM emp WHERE empno>100 AND empno<300
SELECT * FROM emp WHERE empno BETWEEN 100 AND 300#包含
SELECT * FROM emp 
#where ename='rose' and job='副总' #并且关系
#where ename='jack' or job='员工' #或者关系
WHERE ename='jack' OR ename='rose' #或者关系
#WHERE ename in('jack','rose')

#查询>45岁的人的名字
SELECT sname FROM students 
#where里不能用聚合函数max min sum avg count
WHERE YEAR(NOW())-YEAR(sbirthday) > 45

#按照年龄排序
SELECT *,YEAR(NOW())-YEAR(sbirthday) AS age 
FROM students
ORDER BY age
#查询年龄最大的
#desc limit 1
SELECT sname,MIN(sbirthday) FROM students
GROUP BY sname
#查询高于平均工资的员工信息
SELECT * FROM emp WHERE sal > (
  SELECT AVG(sal) FROM emp
)
#查询部门地址在二区的员工信息
SELECT deptno FROM dept WHERE loc='二区'
SELECT * FROM emp WHERE