Mysql日常总结

**

sqlyog时备份数据库链接列表的方法

**
一般在本机C:\Documents and Settings\Administrator\Application Data\SQLyog
这个目录下有个sqlyog.ini文件。把这个文件备份一下就ok了，等重新安装sqlyog后重新覆盖这个文件，那些之前保存的链接数据库的记录都出来了。

SELECT
  d.Name AS Department,
  e.Name AS Employee,
  e.Salary
FROM
  Employee e
  LEFT JOIN Department d
    ON e.DepartmentId = d.Id
WHERE (DepartmentId, Salary) IN
  (SELECT
    DepartmentId,
    MAX(Salary)
  FROM
    Employee
  GROUP BY DepartmentId)

    SELECT
  Email
FROM
  Person
GROUP BY Email
HAVING COUNT(Email) > 1

SELECT
  MAX(id),
  Email
FROM
  Person
GROUP BY Email
HAVING COUNT(Email) > 1

SELECT
  n.Id
FROM
  Weather AS n
  JOIN Weather w
    ON DATEDIFF(n.RecordDate, w.RecordDate) = 1
    AND n.Temperature > w.Temperature

UPDATE user_info SET STATUS = '1' WHERE USER_ID IN (SELECT USER_ID FROM user_info WHERE NAME != '');

但是报了如下错误：

You can’t specify target table ‘user_info’ for update in FROM clause

因为在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录，

解决办法是，将select得到的结果，再通过中间表select一遍，这样就规避了错误，

这个问题只出现于mysql，mssql和oracle不会出现此问题。

所以只要将语句做如下修改即可：

UPDATE user_info SET STATUS = '1' WHERE USER_ID IN
 (SELECT USER_ID FROM (SELECT USER_ID FROM user_info WHERE NAME != '') t1);

IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，
则返回expr3的值。

select name,if(sex=0,'女','男') as sex from student;

select ifNull(sex,'') as sex

如果expr1的值为null 则返回空，否则返回本身

查询取消订单率

SELECT
  Request_at AS `Day`,
  ROUND(
    COUNT(IF(`Status` != 'completed', 1, NULL)) / COUNT(*),
    2
  ) AS `Cancellation Rate`
FROM
  (SELECT
    Trips.*
  FROM
    Trips
     JOIN Users u1
      ON Client_Id = u1.Users_Id
     JOIN Users u2
      ON Driver_Id = u2.Users_Id
      AND u1.Banned = 'No'
      AND u2.Banned = 'No'
      AND Request_at >= '2013-10-01'
      AND Request_at <= '2013-10-03') AS Temp
GROUP BY Temp.Request_at