11.10【SQL】

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 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
primary key = unique + not null

一、作为Primary Key的域/域组不能为null，而Unique Key可以。
二、在一个表中只能有一个Primary Key，而多个Unique Key可以同时存在。

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.

小结
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 price, COUNT() FROM fake_apps WHERE downloads > 20000 GROUP BY price;
SELECT SUM(downloads) FROM fake_apps;这是原始table中downloads的总和
SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column.

SElECT category, SUM(downloads) FROM fake_apps GROUP BY category;
SElECT MAX(downloads) FROM fake_apps;
这个直接找downloads的最大，很简单
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的平均数显示两位小数
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

小结
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的部分来啦
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 关键字会从左表 (albums) 那里返回所有的行，即使在右表 (artists) 中没有匹配的行。
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

小结
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.

猫老师的文章
我总会收到不少朋友，问自己对现在的工作不满意，是否该换一个工作，希望有更体面的工作环境，能从事更有自我价值感的工作等等。我的答案通常是：我是问这个问题的最差人选，因为，我眼睛里，几乎所有的工作，都是幸福的，也都是值得感恩的。

我们当下所从事的工作，永远都不可能是“最好”的工作。所谓“最好的工作”，只是我们大脑里构建出来的一个幻觉，一个可以投射不满而逃避当下的梦想。而我们此时此刻，真实从事的工作，都是我们“应该”做的工作。也就是，因缘和合，社会发展，以及个人前面所有的思考行动和选择，而共同走到了这一点，于是，我们正做着现在自己正做着的工作。

无论你现在正在做的工作是什么，这就是“真实的当下”。对当下的状况不满意，希望改变的第一步，是充分地认识当下，充分地认识从“过去到现在”的因果关系。并对这些因缘真的全盘接受，我们才会开始渐渐看清楚，自己的命运为何来到此时此刻。而真正地看清楚之后，几乎每个人，都会对自己现在所从事的工作，充满感恩：我看清楚了，自己如何从过去来到这里。也才会明白，此时此刻如何做出不同的选择，才可以达到一个真正不同的未来。

而这个过程，恰恰培养自己有一双“高维度”眼睛的过程。我们不再把工作当作，养家糊口，生活所迫，上班下班的苦役。而是充分地认识到，所有工作都是我们认识自己，服务他人的机会。也就开始积极地在各个岗位上，积极磨练自己，主动为他人服务。心念一转，工作就不再是苦役，而是让我们的生命得到成长和发展的机会。让我们不断完善人格和品性，进入更高境界的道场。

在全盘接受当下，感恩现状的时候，生命这场游戏，才真正开始。我们不会再假装认为自己“有选择”，可以找到一份更好的工作，而是开始接受自己没有选择，当下已经是最该有的选择，就从这一个当下开始，真正去玩修炼自己，服务他人的游戏了。而无论你在哪一个角色，都可以在这个游戏中，玩出无限的成就，无限的快乐。

世间之人，大概只有两种心态，第一种是，这世界配不上我。我的工作配不上我。我的原生家庭配不上我。我的朋友也配不上我。第二种是，我配不上这世界，拥有这么好的原生家庭，这样好的工作，这样的朋友，我受宠若惊，诚惶诚恐。

如果你认为，我值得更好的。那么，无论得到什么，几乎都会延续这个思维惯性，配不上我，我值得更好的。那么人，就会一直活在匮乏和不被满足的状态中。

而如果你认为，我已经非常感恩拥有现在的一切了。那么，接下来无论你得到什么，也都会持续感恩，持续地让自己处在感恩，珍惜的状态中。

真正的改变，从来不是从厌恶现状开始的，唯有珍视已拥有的一切，重新把目光放到自己的生活上来，我们才会发现，自己已经拥有了如此多的资源和机会，便会开始有意识地用好它们，创造新的可能性。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,332评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,508评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,812评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,607评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,728评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,919评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,071评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,802评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,256评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,389评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,032评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2赞 350

推荐阅读更多精彩内容