查缺补漏（1）- Linux查找文件、SQL(分组排序、case-when)、复合函数求导

问题1：

在linux中，在不知道文件处于哪个目录下时，怎么查找文件名带有“hello”的文件？

find $HOME/. -name "*hello*"

(更详细的find的用法见：http://man.linuxde.net/find)

 

问题2：

一个学生表(Grades)，有学号(Sno)，课程名(Cno)，成绩(Grade)三列。列出每个学生的学号和第二高成绩的课程。

学号	课程名	成绩
A1	Chem	78
A1	Math	98
A2	Chem	80
A2	Math	85

Select Sno, Cno, Grade 
FROM
(
    Select Grades.*, ROW_NUMBER() OVER (Partition By Sno ORDER by Grade) as row_num FROM Grades 
) as Second_grade 
WHERE row_num = 2;

以上是sql server的写法。

mysql中不提供row_number()函数，需要用迂回一点的方法写：

select Sno, Cno, Grade from
(SELECT a.*, count(*) as row_number 
from Grades a LEFT JOIN Grades b 
ON a.Sno = b.Sno AND b.Grade >= a.Grade
Group by a.Cno, a.Sno) temp
where row_number = 2;

(其他答案可以看讨论： https://stackoverflow.com/questions/1895110/row-number-in-mysql )

 

(mysql向user表插入时出现错误，ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

把my.ini的sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"改成了sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，就可以用了。）

参考：

http://www.cnblogs.com/joeblackzqq/p/4526589.html

http://www.cnblogs.com/bruceleeliya/archive/2011/02/16/1955969.html

)

 

问题3：
求$y=x^x$的导数$\frac{\partial y}{\partial x}$:

\[\ln y=x^x \cdot \ln x\]

\begin{eqnarray*}
\frac{\partial \ln y}{\partial x} & = & 1\cdot \ln x + x \cdot \frac{1}{x}\\
& = & \ln x + 1.
\end{eqnarray*}

\[\frac{\partial \ln y}{\partial x} = \frac{1}{y} \cdot \frac{\partial y}{\partial x}\]

\begin{eqnarray*}
\therefore \frac{\partial y}{\partial x} & = & (\ln x +1) \cdot y \\
& = & x^x (\ln x + 1)
\end{eqnarray*}

 

问题4：

用case-when为表增加列，表示某列在某一个范围的标签：

内容来源：khanacademy

CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,
    heart_rate INTEGER);

/* CASE */
SELECT type, heart_rate,
    CASE 
        WHEN heart_rate > 220-30 THEN "above max"
        WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"
        WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"
        ELSE "below target"
    END as "hr_zone"
FROM exercise_logs;