SQL Recover

• A table for example:

  +----+--------------+---------------------------+-------+---------+
  | id | name         | url                       | alexa | country |
  +----+--------------+---------------------------+-------+---------+
  | 1  | Google       | https://www.google.cm/    | 1     | USA     |
  | 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
  | 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
  | 4  | 微博          | http://weibo.com/         | 20    | CN      |
  | 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
  +----+--------------+---------------------------+-------+---------+

SELECT

SELECT FROM

• 一列中的全部值： SELECT name FROM websites
• 一列中的uniqe值： SELECT DISTINCT country FROM websites

SELECT * FROM

• 有条件的筛选某些行： SELECT * FROM website WHERE country = 'CN' SELECT * FROM website WHERE id = 1
• 多个筛选条件： SELECT * FROM website WHERE country = "CN" AND alexa > 50 SELECT * FTOM website WHERE country = "CN" OR country = "USA" SELECT * FROM website WHERE alexa > 50 AND (country = "CN" OR country = "USA")

ORDER BY 排序

• 给整个表格内容排序：SELECT * FROM website ORDER BY alexa SELECT * FROM website ORDER BY alexa DESC
• 给整个表格按多列中的内容排序： SELECT * FROM website ORDER BY alexa, country

INSERT INTO

• 给列表中的 某几列 对应添加值 INSERT INTO website (name, url, alexa, country) VALUES ('Netflex', "www.netflex.com", "4", "USA")

UPDATE

• 先给想更新的field赋值，随后选中要改变的行 UPDATE website SET alexa = "5000", country = "USA" WHERE name = '菜鸟教程'

DELETE

• 和 SELECT类似 DELETE * FROM website WHERE name = "百度"

JOIN

+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+

INNER JOIN

• 只要两张表在 ON语句中有能对应起来的数据，就把那一行 SELECT中的那几项返回回来

  SELECT websites.id, websites.name, access_log.count, access_log.date 
  FROM websites
  INNER JOIN access_log
  ON website.if = access_log.site_id;

• 返回所有网站的浏览记录

  SELECT websites.name, access_log.count, access_log.data
  FROM websites 
  INNER JOIN access_log
  ON websites.id = access_log.site_id

LEFT JOIN

• 即使 右侧表中没有匹配 也返回左侧表中全部的行
• 回所有网站及他们的访问量（如果有的话）

  SELECT websites.name, access_log.count, access_log.date
  FROM websites
  LEFT JOIN access_log
  ON websites.id = access_log.site_id

RIGHT JOIN

• 即使 左侧表中没有匹配 也返回右侧标表全部的行
• 返回全部网站的访问记录

  SELECT websites.name, access_log.count, access_log.date
  FROM websites
  RIGHT JOIN access_log
  ON website.id = access_log.site_id

FULL JOIN

• 只要其中一个表 存在匹配 返回那一行