Self Summary: Hot SQL command

Following images come from: http://www.w3school.com.cn/sql/

Create a new table to store the information that you want to store: select into

Example:

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P

From here, we know: we can use . to get a specific column of a particular table like persons.lastname and orders.orderno. 

Usually, INNER JOIN is combined with ON to use: 

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。If we only use 'JOIN' as the key work in our SQL sentence, the default settlement is INNER JOIN. The 'ON' which following the INNER JOIN is like WHERE key word in SELECT. It tells INNER JOIN to list the rows that satisfies the matching on two different rows from different tables. 

Also there are others JOIN like LEFT JOIN, RIGHT JOIN and FULL JOIN:

LEFT JOIN 关键字会返回左表 (table_name1) 所有的行，即使在该行的foreign key与右表 (table_name2) 的primary key没有匹配。

For example:

We can see George Bush did not have a match in order_id, however, it still appeared in the result. 

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

The  FULL JOIN's result is like:

LIKE in SQL is to set the output format, and the most important is LIKE always combines with WHERE like JOIN and ON: 

Here are some examples of LIKE: 

Since we have mentioned ‘Wildcard’ at above, let us get to know it now:

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。

Here are some examples using the wildcards above:

Now, since we have learned the LIKE which is often used with WHERE, let us learn the other two which are often used with WHERE too: IN and BETWEEN.

IN is to give a list of option for where condition while BETWEEN gives a range of options. 

IN:

BETWEEN:

重要事项：不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 "Adams" 和 "Carter" 之间的人，但不包括 "Adams" 和 "Carter" ；某些数据库会列出介于 "Adams" 和 "Carter" 之间并包括 "Adams" 和 "Carter" 的人；而另一些数据库会列出介于 "Adams" 和 "Carter" 之间的人，包括 "Adams" ，但不包括 "Carter" 。

Some instructions about the display of select result:

DISTINCT: 将select出来的列中的重复元素去掉，SELECT DISTINCT 'COLUMN NAME' FROM 'TABLE NAME'

ORDER BY：将select出来的列（所组成的行）按照某一列的内容进行排序，默认是升序。降序需要指定DESC。

如果该列内容为字符串，则默认按字母升序。若为数字，则按数字升序。

反方向：

Above commands, except the select ... into ... can create a new table in database, others commands just extract content we need.The following commands are for changing the content of dataset: 

1) INSERT INTO: For inserting a new row to the table. 

2) UPDATE: For changing the existing value of existing row.

Usually, update must used with where in order to locate a particular row of the table. 

3) DELETE: Deletes a particular row of a table. Usually be used with where to locate a particular row.

Also, we can delete the whole table by using: 

DELETE FROM table_name

AS, alias in SQL:

Now, let's talk about using alias in sql:

Using alias just for making a shorter statement or when you are using as with into statement. You can create a new name for a specific column for the new table. Let's consider the example:

Making a shorter statement:

Creating a new column name of a new table:

Common Functions in SQL:

avg(求均值)， count(求个数), first(通常结合orderby 排序后取第一个), last(结合orderby 排序后取最后一个)， max, min, sum, ucase(字母都变为大写), lcase(字母都变为小写), mid(column_name, start[,length]) 提起字符串中的从起始长度（默认为1，而不是0的）的长度为多少的子字符串, len求字符串长度，round(column_name, 返回多少位小数).

对于一些aggregation function（合计函数）：如avg(求均值)， count(求个数), first(通常结合orderby 排序后取第一个), last(结合orderby 排序后取最后一个)， max, min, sum. select 函数(column) from table group by column2. 这是一个常用的一句。用来分组合计。

另外如果想对分组合计的情况中，加以合计函数的判断条件可以用having。 因为where是不能和合计函数一起使用的。