DBA笔记-第二部分

--------查询优化语句技巧-----

emp_no   salary    from_date       to_date

10002    69366    1999-08-03    2000-08-02
10002    71963    2000-08-02    2001-08-02
10002    72527    2001-08-02    9999-01-01
10003    40006    1995-12-03    1996-12-02
10003    43616    1996-12-02    1997-12-02
10003    43466    1997-12-02    1998-12-02
10003    43636    1998-12-02    1999-12-02
10003    43478    1999-12-02    2000-12-01
10003    43699    2000-12-01    2001-12-01
10003    43311    2001-12-01    9999-01-01
10004    40054    1986-12-01    1987-12-01
10004    42283    1987-12-01    1988-11-30
10004    42542    1988-11-30    1989-11-30
10004    46065    1989-11-30    1990-11-30
10004    48271    1990-11-30    1991-11-30
10004    50594    1991-11-30    1992-11-29
10004    52119    1992-11-29    1993-11-29
10004    54693    1993-11-29    1994-11-29
10004    58326    1994-11-29    1995-11-29
10004    60770    1995-11-29    1996-11-28

求出上表员工最新的工资（表为数据例子，建议使用MySQL :: Other MySQL Documentation得官方employees库配合练习）

1.一般通俗易懂写法需要查询两遍工资表 查询时间：1.853s  共三十万条数据

SELECT
    emp_no,
    salary 
FROM
    salaries 
WHERE
    ( emp_no, from_date, to_date ) IN ( SELECT emp_no, MAX( from_date ), MAX( to_date ) FROM salaries GROUP BY emp_no );

2.进阶装逼函数写法 查询时间：0.007s  共三十万条数据

使用SUBSTRING_INDEX与GROUP_CONCAT函数 查询一遍工资表 加快速度 

SELECT
    emp_no,
    SUBSTRING_INDEX( GROUP_CONCAT( salary ORDER BY from_date DESC, to_date DESC ), ',', 1 ) AS last_salary 
FROM
    salaries 
GROUP BY
    emp_no

---------navicat 优化SELECT * FROM 输入甜品级小技巧，当然也有插件可以代替

navicat工具 右边新建片段首行输入ssf  代码输入SELECT * FROM 并保存

接下来新建查询输入ssf后按TAB键，神奇的事情出现了！！！！

-------------面试常问关于SQL注入，Prepare语句

什么是sql注入

例子：假设网站需要传入参数 10080 到 ？ 占位符中

SELECT * FROM employees where emp_no = ？

但是某些坏兄弟传入的是  10080 or 1=1  ，那么完犊子，你的数据全展示了，where没生效

SELECT * FROM employees where emp_no = 10080 or 1=1;

那么怎么避免呢，可以使用Prepare语句避免，你面试用这个例子你又过了！

举个栗子

set @sql='SELECT * FROM employees where emp_no = ?';/*使用 SET 语句定义包含占位符的 SQL 查询字符串*/
set @a=100080;
PREPARE stmt FROM @sql;/*创建一个名为 stmt 的准备语句，该语句可以在之后被执行*/
EXECUTE stmt USING @a;/*使用 EXECUTE 语句执行准备好的SQL语句，并将实际的参数值传递给它，这里，@emp_no 的值 100080 会替代 SQL 查询中的 ? 占位符，生成最终的 SQL*/
DEALLOCATE PREPARE stmt;/*释放缓存，防止内存泄漏，啥是内存泄露呢？后续再说，我现在也不知道*/