一.视图
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
#查询高于平均工资的员工信息
SEL