SQL is a programming language designed to manipulate and manage data stored in relational databases.
A relational database is a database that organizes information into one or more tables.

CREATE TABLE creates a new table.
INSERT INTO adds a new row to a table.
SELECT queries data from a table.
UPDATE edits a row in a table.
ALTER TABLE changes an existing table.
DELETE FROM deletes rows from a table.

SELECT * FROM celebs;

CREATE TABLE celebs (
id INTEGER,
name TEXT,
age INTEGER
);

INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);

UPDATE celebs
SET age = 22
WHERE id = 1;
SELECT * FROM celebs;

ALTER celebs ADD COLUMN twitter_handle text;
SELECT * FROM celebs;
The ALTER TABLE statement added a new column to the table. You can use this command when you want to add columns to a table.

UPDATE celebs
SET twitter_handle = '@taylorswift13'
WHERE id = 4;
DELETE FROM celebs WHERE twitter_handle IS NULL;
SELECT * FROM celebs;

CREATE TABLE awards (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
recipient TEXT NOT NULL,
award_name TEXT DEFAULT "Grammy"
);
PRIMARY KEY columns can be used to uniquely identify the row.
PRIMARY KEY 約束唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)表中的每條記錄蔬将。
主鍵必須包含唯一的值。
主鍵列不能包含 NULL 值。
每個(gè)表都應(yīng)該有一個(gè)主鍵，并且每個(gè)表只能有一個(gè)主鍵。
primary key = unique + not null

一、作為Primary Key的域/域組不能為null，而Unique Key可以唉侄。
二、在一個(gè)表中只能有一個(gè)Primary Key野建，而多個(gè)Unique Key可以同時(shí)存在属划。

SELECT DISTINCT genre FROM movies;
SELECT DISTINCT is used to return unique values in the result set. It filters out all duplicate values.

SELECT * FROM movies WHERE imdb_rating > 8;
SELECT * FROM movies
WHERE name LIKE 'Se_en';
-->name can be "Se7en" or "Seven"

1. LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

2. name LIKE Se_en is a condition evaluating the name column for a specific pattern.

3. Se_en represents a pattern with a wildcard character. The _ means you can substitute any individual character here without breaking the pattern. The names Seven and Se7en both match this pattern.

SELECT * FROM moviesWHERE name LIKE 'A%';
SELECT * FROM movies WHERE name LIKE '%man%';
% is a wildcard character that matches zero or more missing letters in the pattern.
A% matches all movies with names that begin with "A"
%a matches all movies that end with "a"

AND
SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';【指的name首字母 names that begin with letters "A" up to but not including "J".】
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;【years between 1990 up to and including 2000.】
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000 AND genre = 'comedy';

OR
SELECT * FROM movies WHERE genre = 'comedy' OR year < 1980;

SELECT * FROM movies ORDER BY imdb_rating DESC;
DESC is a keyword in SQL that is used with ORDER BY to sort the results in descending order (high to low or Z-A).
降序DESC 升序ASC
SELECT * FROM movies ORDER BY imdb_rating ASC LIMIT 3;
query that only returns the three lowest rated movies. LIMIT is a clause that lets you specify the maximum number of rows the result set will have.

小結(jié)
SELECT is the clause you use every time you want to query information from a database.
WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.
LIKE and BETWEEN are special operators that can be used in a WHERE clause
AND and OR are special operators that you can use with WHERE to filter the query on two or more conditions.
ORDER BY lets you sort the results of the query in either ascending or descending order.
LIMIT lets you specify the maximum number of rows that the query will return. This is especially important in large tables that have thousands or even millions of rows.

SELECT COUNT() FROM fake_apps;
SELECT COUNT() FROM fake_apps WHERE price = 0;
SELECT price, COUNT() FROM fake_apps GROUP BY price;
即price是0.99的有43條

Count() GroupBy.png

SElECT category, SUM(downloads) FROM fake_apps GROUP BY category;
SElECT MAX(downloads) FROM fake_apps;
這個(gè)直接找downloads的最大，很簡(jiǎn)單
MAX() is a function that takes the name of a column as an argument and returns the largest value in that column.

SELECT MIN(downloads) FROM fake_apps;
SELECT AVG(downloads) FROM fake_apps;
The AVG() function works by taking a column name as an argument and returns the average value for that column.
SELECT price, AVG(downloads) FROM fake_apps
GROUP BY price;
SELECT price, ROUND(AVG(downloads), 2) FROM fake_apps GROUP BY price;
downloads的平均數(shù)顯示兩位小數(shù)
ROUND() is a function that takes a column name and an integer as an argument. It rounds the values in the column to the number of decimal places specified by the integer. Here, we pass the column AVG(downloads) and 2 as arguments. SQL first calculates the average for each price and then rounds the result to two decimal places in the result set.
SELECT price, ROUND(AVG(downloads)) FROM fake_apps GROUP BY price;
Round the average number of downloads to the nearest integer for each price.

ROUND().png

小結(jié)
Aggregate functions combine multiple rows together to form a single value of more meaningful information.
COUNT takes the name of a column(s) as an argument and counts the number of rows where the value(s) is not NULL.
GROUP BY is a clause used with aggregate functions to combine data from one or more columns.
SUM() takes the column name as an argument and returns the sum of all the values in that column.
MAX() takes the column name as an argument and returns the largest value in that column.
MIN() takes the column name as an argument and returns the smallest value in that column.
AVG() takes a column name as an argument and returns the average value for that column.
ROUND() takes two arguments, a column name and the number of decimal places to round the values in that column.

JOIN的部分來(lái)啦
SELECT
*
FROM
albums
JOIN artists ON
albums.artist_id = artists.id;

SELECT
*
FROM
albums
LEFT JOIN artists ON
albums.artist_id = artists.id;

LEFT JOIN 關(guān)鍵字會(huì)從左表 (albums) 那里返回所有的行候生，即使在右表 (artists) 中沒(méi)有匹配的行同眯。
every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table.

The left table is simply the first table that appears in the statement. Here, the left table is albums. Likewise, the right table is the second table that appears. Here, artists is the right table.

幫助理解的鏈接：http://www.w3school.com.cn/sql/sql_join_left.asp

SELECT
albums.name AS 'Album',
albums.year,
artists.name AS 'Artist'
FROM
albums
JOIN artists ON
albums.artist_id = artists.id
WHERE
albums.year > 1980;
AS is a keyword in SQL that allows you to rename a column or table using an alias. The new name can be anything you want as long as you put it inside of single quotes.

DROP TABLE IF EXISTS albums;
CREATE TABLE IF NOT EXISTS albums(
id INTEGER PRIMARY KEY,
name TEXT,
artist_id INTEGER,
year INTEGER
);

DROP TABLE IF EXISTS albums;
CREATE TABLE IF NOT EXISTS albums(
id INTEGER PRIMARY KEY,
name TEXT,
year INTEGER,
artist_id INTEGER,
FOREIGN KEY(artist_id) REFERENCES artist(id)
);
鏈接：http://www.w3school.com.cn/sql/sql_foreignkey.asp

小結(jié)
Primary Key is a column that serves a unique identifier for row in the table. Values in this column must be unique and cannot be NULL.
Foreign Key is a column that contains the primary key to another table in the database. It is used to identify a particular row in the referenced table.
Joins are used in SQL to combine data from multiple tables.
INNER JOIN will combine rows from different tables if the join condition is true.
LEFT OUTER JOIN will return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table.
AS is a keyword in SQL that allows you to rename a column or table in the result set using an alias.

貓老師的文章
我總會(huì)收到不少朋友，問(wèn)自己對(duì)現(xiàn)在的工作不滿意唯鸭，是否該換一個(gè)工作须蜗，希望有更體面的工作環(huán)境，能從事更有自我價(jià)值感的工作等等。我的答案通常是：我是問(wèn)這個(gè)問(wèn)題的最差人選明肮，因?yàn)榱馀已劬铮瑤缀跛械墓ぷ魇凉溃际切腋５难矗捕际侵档酶卸鞯摹?/p>

我們當(dāng)下所從事的工作，永遠(yuǎn)都不可能是“最好”的工作秫舌。所謂“最好的工作”只厘，只是我們大腦里構(gòu)建出來(lái)的一個(gè)幻覺(jué)，一個(gè)可以投射不滿而逃避當(dāng)下的夢(mèng)想舅巷。而我們此時(shí)此刻，真實(shí)從事的工作河咽，都是我們“應(yīng)該”做的工作钠右。也就是，因緣和合忘蟹，社會(huì)發(fā)展飒房，以及個(gè)人前面所有的思考行動(dòng)和選擇，而共同走到了這一點(diǎn)媚值，于是狠毯，我們正做著現(xiàn)在自己正做著的工作。

無(wú)論你現(xiàn)在正在做的工作是什么褥芒，這就是“真實(shí)的當(dāng)下”嚼松。對(duì)當(dāng)下的狀況不滿意，希望改變的第一步锰扶，是充分地認(rèn)識(shí)當(dāng)下献酗，充分地認(rèn)識(shí)從“過(guò)去到現(xiàn)在”的因果關(guān)系。并對(duì)這些因緣真的全盤接受坷牛，我們才會(huì)開始漸漸看清楚罕偎，自己的命運(yùn)為何來(lái)到此時(shí)此刻。而真正地看清楚之后京闰，幾乎每個(gè)人颜及，都會(huì)對(duì)自己現(xiàn)在所從事的工作，充滿感恩：我看清楚了蹂楣，自己如何從過(guò)去來(lái)到這里俏站。也才會(huì)明白，此時(shí)此刻如何做出不同的選擇捐迫，才可以達(dá)到一個(gè)真正不同的未來(lái)乾翔。

而這個(gè)過(guò)程，恰恰培養(yǎng)自己有一雙“高維度”眼睛的過(guò)程。我們不再把工作當(dāng)作反浓，養(yǎng)家糊口萌丈，生活所迫，上班下班的苦役雷则。而是充分地認(rèn)識(shí)到辆雾，所有工作都是我們認(rèn)識(shí)自己，服務(wù)他人的機(jī)會(huì)月劈。也就開始積極地在各個(gè)崗位上度迂，積極磨練自己，主動(dòng)為他人服務(wù)猜揪。心念一轉(zhuǎn)惭墓，工作就不再是苦役，而是讓我們的生命得到成長(zhǎng)和發(fā)展的機(jī)會(huì)而姐。讓我們不斷完善人格和品性腊凶，進(jìn)入更高境界的道場(chǎng)。

在全盤接受當(dāng)下拴念，感恩現(xiàn)狀的時(shí)候钧萍，生命這場(chǎng)游戲，才真正開始政鼠。我們不會(huì)再假裝認(rèn)為自己“有選擇”风瘦，可以找到一份更好的工作，而是開始接受自己沒(méi)有選擇公般，當(dāng)下已經(jīng)是最該有的選擇万搔，就從這一個(gè)當(dāng)下開始，真正去玩修煉自己官帘，服務(wù)他人的游戲了蟹略。而無(wú)論你在哪一個(gè)角色，都可以在這個(gè)游戲中遏佣，玩出無(wú)限的成就挖炬，無(wú)限的快樂(lè)。

世間之人状婶，大概只有兩種心態(tài)意敛，第一種是，這世界配不上我膛虫。我的工作配不上我草姻。我的原生家庭配不上我。我的朋友也配不上我稍刀。第二種是撩独，我配不上這世界敞曹，擁有這么好的原生家庭，這樣好的工作综膀，這樣的朋友澳迫，我受寵若驚，誠(chéng)惶誠(chéng)恐剧劝。

如果你認(rèn)為橄登，我值得更好的。那么讥此，無(wú)論得到什么拢锹，幾乎都會(huì)延續(xù)這個(gè)思維慣性，配不上我萄喳，我值得更好的卒稳。那么人，就會(huì)一直活在匱乏和不被滿足的狀態(tài)中他巨。

而如果你認(rèn)為展哭，我已經(jīng)非常感恩擁有現(xiàn)在的一切了。那么闻蛀，接下來(lái)無(wú)論你得到什么，也都會(huì)持續(xù)感恩您市，持續(xù)地讓自己處在感恩觉痛，珍惜的狀態(tài)中。

真正的改變茵休，從來(lái)不是從厭惡現(xiàn)狀開始的薪棒，唯有珍視已擁有的一切，重新把目光放到自己的生活上來(lái)榕莺，我們才會(huì)發(fā)現(xiàn)俐芯，自己已經(jīng)擁有了如此多的資源和機(jī)會(huì)，便會(huì)開始有意識(shí)地用好它們钉鸯，創(chuàng)造新的可能性吧史。