sql笔记 | The War Of Mine

照着sqlteaching这个网站过了一遍练习教程

前面的的比较简单就不写了…(其实是忘记写了…..结果写过的貌似记录没了,, 懒得打了= =)
这里把结果截下图把= =

SELECT

WHERE

LIMIT

DESC

ORDER BY

GROUP BY

NESTED QUERIES

IS NOT NULL

IS NULL

DATA we can use < or > directly

SELECT character.name, actor.name FROM character INNER JOIN character_actor ON character.id = character_actor.character_id INNER JOIN actor ON character_actor.actor_id = actor.id;

这个有点难理解

SELECT c1.name AS employee_name, c2.name AS boss_name 
FROM employees AS c1 
INNER JOIN employees AS c2 
ON c1.boss_id = c2.id;

注意这里的 = 相当于赋值

不能直接理解为位置

SELECT * FROM robots WHERE name LIKE "%Robot 20__";

用LIKE正则匹配
%表示0-N
-表示单一字符

另外注意正则要用””而正常字符是’’

SELECT *, CASE WHEN species = 'human' THEN 'talk' WHEN species = 'cat' THEN 'meow' ELSE 'bark' END AS sound FROM friends_of_pickles;

CASE WHEN …. WHEN …. ELSE … END AS sth

用来创建新COL

SELECT * FROM robots WHERE SUBSTR(location, -2) = 'NY';

eg:
SUBSTR(name, -4)

In SQL, you can search for the substring of a given value. Perhaps a location is stored in the format “city, state” and you just want to grab the state.

SUBSTR is used in this format: SUBSTR(column_name, index, number_of_characters)

index is a number that denotes where you would start the substring. 1 would indicate the first character, 2 would indicated the second character, etc. The index could also be negative, which means you would count from the end of the string. -1 would denote the last character, -2 would denote the 2nd to last character, etc.

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.

Suppose we wanted to find the most powerful weapon that a combatant has on hand. If value of gun is not null, that is the value returned. Otherwise, the value of sword is returned. Then you would run:
SELECT name, COALESCE(gun, sword) as weapon FROM fighters;

Suppose that a fighter’s tank could count as a weapon, and it would take precedence over the gun and the sword. Could you find each fighter’s weapon in that scenario?

coalesce联合取代

感觉这单词好奇怪…..

前面的忘记记录了

一共31课全部码完~~~ 感觉SQL也还行不是很难

然后打算开始看部分数据库了我看到ML的实习位置一般要数据库知识和hadoop或者spark知识啊~~ 现在还不怎么会