SQL学习笔记

一．SQL teaching 操作学习

Note：斜体为自己思考内容 已经过实际操作证明

下划线为提示内容 重点标注内容

加粗为语句关键词

SQL语句中以分号“；”来分隔多条语句（有点DBMS不需要，但都加上无坏处）

MySQL中粘贴只能用鼠标操作，不能用Ctrl+V

有些语句在其他DBMS中并不适用

1、输入SELECT *FROM family_members;
    注释：The * above means that all of the columns will be returned

*与from之间有无空格无影响 大小写无区别 多个空格也无影响

2、输入SELECT name, num_books_read FROM family_members;

注释：select special columns（列）用逗号隔开

3、过滤数据

输入SELECT * FROM family_members WHERE species = 'human';

注释：In order to select particular rows（行）from this table, we use the WHERE keyword.

输入SELECT * FROM family_members WHERE num_books_read > 0;

注释：In order to select family members based on a numerical field, we can also use the WHERE keyword.

补充：= "equal to"
> "greater than"
< "less than"
>= "greater than or equal to"

<= "less than or equal to"

      < >或！= "no equal "（不匹配检查 可为字符串型数据）
BETWEEN 在指定的两个值之间

IS NULL 为NULL值（空值检查）

4、组合where与and（两个条件以上时每个条件检都要加and）或or结合使用可筛选同时符合两个或者其中一个条件

当同时含有and和or时的求值顺序：and优于or

Eg：输入SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’ AND prod_price >= 10;：

注释：由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DLL01制造的所有产品，而不管其价格如何。

可利用圆括号进行操作分组解决

5、输入SELECT * FROM friends_of_pickles WHERE species IN ('cat', 'human');

注释：in用于筛选出一个条件里的多个数值，也可使用not in代替

In与or有相同的功能（in的最大优点是可以包含其他select语句，可更加动态）

6、输入SELECT DISTINCT gender, species FROM friends_of_pickles WHERE height_cm < 100;

注释：By putting DISTINCT after SELECT, you do not return duplicates（返回不同的值）（复制品，一样的东西）只会显示符合where条件的gender和species列（按原有序列号排列）

Note：distinct必须直接放在列名的前面，并且不能部分使用

7、输入SELECT * FROM friends_of_pickles ORDER BY name;

注释：In order to sort the rows by some kind of attribute, you can use the ORDER BY keyword 上述语句只返回按字母表顺序的排列的name列，即系统默认的都为升序，可在语句最后加入DESC以降序排列

8、输入SELECT * FROM friends_of_pickles ORDER BY height_cm LIMIT 2;

    注释：系统默认的顺序为升序，即使用limit筛选是从最小的开始；若想从最大值开始筛选可用DESC加上limit + 个数

  注：LIMIT m,n : 表示从第m+1条开始，取n条数据；

  LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。 

9、输入SELECT COUNT(*) FROM friends_of_pickles;

注释：to explore a table is to check the number of rows（行）in it.

Note：Count（column）返回指定列的行数，此时忽略为null的行

而count（*）返回标的行数，不忽略null的行

10、输入SELECT COUNT(*) FROM friends_of_pickles WHERE species = 'human';

注释：combine COUNT(*) with WHERE to return the number of rows that matches the WHERE clause（从句/分句）

11、输入select sum(num_books_read) from family_members;

注释：use the SUM keyword in order to find the sum of a given value.

Note：Because of the way computers handle numbers, averages will not always be completely exact.（准确）

12、AVG 、max与min用法同sum语句

13、输入SELECT COUNT(*)（可替换）, species FROM friends_of_pickles GROUP BY species;

注释： would return the number of rows for each species.

      can use aggregate functions such as COUNT, SUM, AVG, MAX, and MIN with the GROUP BY clause. When you GROUP BY something, you split the table into different piles based on the value of each row.即按group by分组，再进一步的对数据提取

14、输入SELECT * FROM family_members WHERE num_legs = (SELECT MIN(num_legs) FROM      family_members);

注释：nested（嵌套的）queries

15、输入select * from family_members where favorite_book is not null;（自己修改）

注释：In order to find the rows where the value for a column is or is not NULL, you would use IS NULL or IS NOT NULL

16、输入SELECT * FROM celebs_born WHERE birthdate < '1985-08-17';

注释：returns a list of celebrities that were born before August 17th, 1985.

时间格式为‘年4-月2-日2’

*较简单

17、输入SELECT character.name, character_tv_show.tv_show_name（新表所显示内容）

FROM character（新表row顺序以character表中row顺序显示）

INNER JOIN character_tv_show 

ON character.id = character_tv_show.character_id;（通过此处建立对应关系）

注释：Different parts of information can be stored in different tables, and in order to put them together, we use INNER JOIN ... ON.

“actor.name”表示表名为actor中name那列

** 较复杂 更符合实际情况

18、输入SELECT character.name, tv_show.name（新表要显示的column）

FROM character

INNER JOIN character_tv_show（要显示的内容所在的表且以前面表row的顺序展示）ON character.id = character_tv_show.character_id

INNER JOIN tv_show 

ON character_tv_show.tv_show_id = tv_show.id;（嵌套语句 从后向前推）

疑问：想要显示actor.name 和tv_show.name 或者想一起显示三者对应关系，怎么操作？？？

联结多个表：SELECT prod_name, vend_name, prod_price, quantity

FROM OrderItems, Products, Vendors

WHERE Products.vend_id = Vendors.vend_id

AND OrderItems.prod_id = Products.prod_id

AND order_num = 20007;

注释：***逻辑思维要清晰

19、输入SELECT character.name, tv_show.name
         FROM character
         INNER JOIN character_tv_show
         ON character.id = character_tv_show.character_id
         INNER JOIN tv_show
         ON character_tv_show.tv_show_id = tv_show.id

 WHERE character.name !=    'Barney Stinson'

AND tv_show.name != 'Buffy the Vampire Slayer';

注释：带有筛选条件的建立新表

20、inner join与left join的区别：a LEFT JOIN returns all of the data from the first (or "left") table, and if there isn't corresponding data for the second table, it returns NULL for those columns.此外还有RIGHT JOIN 和 OUTER JOIN（没有讲）

21、table与column的重新命名：add AS *alias_name* after the table name.

  注释：actor AS a 即把actor重新命名为a

二者比较（命名位置不同）：

一、 SELECT c.name, t.name
FROM character AS c
LEFT JOIN character_tv_show AS ct
ON c.id = ct.character_id
LEFT JOIN tv_show AS t
ON ct.tv_show_id = t.id;

二、SELECT character.name AS character, tv_show.name AS name
FROM character
LEFT JOIN character_tv_show
ON character.id = character_tv_show.character_id
LEFT JOIN tv_show
ON character_tv_show.tv_show_id = tv_show.id;

注释：列别名与表别名区别：表别名只在查询执行中使用，不返回到客户端；列别名可输出

22、self join（即对同一个表多次命名）

Eg：SELECT r1.name AS object, r2.name AS beats

FROM rps AS r1

INNER JOIN rps AS r2

ON r1.defeats_id = r2.id;

23、Like操作符（用来匹配值的一部分的特殊字符）：

输入SELECT * FROM robots WHERE name LIKE "%20%";

注释：use the LIKE command in order to search through text-based values. With LIKE, there are two special characters: % and _.

通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索。
 其中： The percent sign (%通配符) represents zero, one, or multiple characters.
       The underscore (_通配符) represents one character.

但是以空格结尾的数据在使用%通配符是要注意，也可使用函数去掉空格（后面讲）

%唯一不能匹配的是为null的行

方括号（[]）通配符（只有access和SQL server支持）：用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。Eg：[JM]匹配以字母J和M开头的（只能匹配单个字符）可加上[JM]%表示其后面可有任意字符

Eg：根据邮件地址的一部分来查找电子邮件：WHERE email LIKE 'b%@forta.com’

24、输入SELECT *,

CASE WHEN species = 'human' THEN 'talk'

when species=’cat' then 'meow'

when species='dog' then 'bark'（或者用else）

END AS sound

FROM friends_of_pickles;

注释：can use a CASE statement to return certain values when certain scenarios are true（类似于IF语句）

25、SUBSTR函数用法：SUBSTR(column_name, index, number_of_characters)

      注释：column_name指定要筛选的column

index指从column_name的那个位置开始筛选

 number_of_characters指筛选的位数

        Eg;SELECT * FROM robots WHERE SUBSTR(location, -2) LIKE 'NY';（二者要一致）

           SELECT * FROM robots WHERE SUBSTR(name, -4) LIKE '20__';

Note: In other versions of SQL, you could use RIGHT to do this.

26、输入SELECT name, COALESCE(tank,gun,sword) as weapon FROM fighters;

注释：COALESCE  takes a list of columns, and returns the value of the first column that is not null.（括号中内容有顺序区别）

 Grab抓取variant变量query质疑/问题pickle腌菜/麻烦pile堆/放置entities独立实体artificial人工的/虚假的clause分句/从句alias化名/别名nickname绰号scenarios情节/剧本/脚本