day05

视图

可以吧SQL的查询结果缓存起来存入视图中
好处：简化了SQL的编写
坏处：没法做SQL优化，占用内存

CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `db3`.`emp_view` 
    AS
    #一下的SQL的执行的结果会被缓存到视图中
(SELECT * FROM emp WHERE ename LIKE '%a%');

多表链接的练习

#笛卡尔积/join/子查询
#练习1:查询李军的总得分
#练习2:查询易天负责的课程名称

SELECT sname,SUM(degree) FROM scores a,students b WHERE a.sno = b.sno AND b.sname = '李军';
SELECT sname,SUM(degree) FROM scores a LEFT JOIN students b ON a.sno = b.sno WHERE b.sname = '李军';
SELECT SUM(degree) FROM scores WHERE sno = (SELECT sno FROM students WHERE sname = '李军');

SELECT tname,cname FROM courses a ,teachers b WHERE a.tno = b.tno AND b.tname = '易天';
SELECT tname,cname FROM courses a LEFT JOIN teachers b ON a.tno = b.tno WHERE b.tname = '易天';
SELECT cname FROM courses WHERE tno = (SELECT tno FROM teachers WHERE tname = '易天');

什么是索引，索引的好处和坏处（整理成话术）

SQL的优化

1.尽量使用字段代替*
2.做表设计的时候，字段的类型最好是varchar代替char
3.字段里的值，最好用数字代替字符串
4.尽量把过滤条件精细，能用and不用or
5.索引的设计，最多5个，不能太多
6.模糊查询，尽量要确定开始元素，让索引失效
7.数据库堆数字没有严格的要求，name=123索引失效，name=‘123’索引失效
8.无用的索引最好及时删除，会占用内存