基础回顾：SQL

[bingoohuang]

使用Sqlite3回顾一下基础的SQL知识。

# ~/.sqliterc
.mode column
.headers on
.separator ROW "\n"
.nullvalue NULL

$  sqlite3 back_to_basics
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> CREATE TABLE players (id INTEGER PRIMARY KEY ASC, name TEXT, seasons_played INTEGER);
sqlite> CREATE TABLE teams (id INTEGER PRIMARY KEY ASC, name TEXT);
sqlite> CREATE TABLE players_teams (player_id INTEGER, team_id INTEGER, won_championship BOOLEAN);
sqlite> .tables
players        players_teams  teams
sqlite> .explain on
sqlite>

1. 增

   INSERT INTO players (name, seasons_played) VALUES ('Nolan Ryan', 27);
   INSERT INTO players (name, seasons_played) VALUES ('Jim Sundberg', 16);
   INSERT INTO players (name, seasons_played) VALUES ('Ivan Rodriguez', 21);

2. 查

   SELECT * FROM players;
   SELECT name FROM players;
   SELECT * FROM players ORDER BY seasons_played;
   SELECT * FROM players ORDER BY seasons_played DESC;
   
   INSERT INTO teams (name) VALUES ('Texas Rangers');
   INSERT INTO teams (name) VALUES ('Florida Marlins');
   INSERT INTO teams (name) VALUES ('New York Mets');
   INSERT INTO teams (name) VALUES ('California Angels');
   INSERT INTO teams (name) VALUES ('Milwaukee Brewers');
   INSERT INTO teams (name) VALUES ('New York Yankees');
   
   INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (1, 4, 0);
   INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (1, 3, 1);
   INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (2, 1, 0); 
   INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (2, 5, 0);
   INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (3, 1, 0);
   INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (3, 2, 1)

3. 联

   .width 2 30

   SELECT * FROM players
   INNER JOIN players_teams 
   ON players.id = players_teams.player_id;
   
   SELECT * FROM teams
   INNER JOIN players_teams 
   ON teams.id = players_teams.team_id;
   
   SELECT * FROM players
   INNER JOIN players_teams ON players.id = players_teams.player_id
   INNER JOIN teams ON players_teams.team_id = teams.id;
   
   SELECT players.name, teams.name
   FROM players
   INNER JOIN players_teams ON players.id = players_teams.player_id
   INNER JOIN teams ON players_teams.team_id = teams.id;

4. 左

   SELECT * FROM teams LEFT OUTER JOIN players_teams ON teams.id = players_teams.team_id;

5. 当

   SELECT * FROM players WHERE seasons_played > 20;
   SELECT * FROM players WHERE seasons_played = 16;
   SELECT *  FROM players WHERE seasons_played BETWEEN 20 and 22;
   SELECT * FROM players WHERE seasons_played IN (16, 27);
   
   SELECT * FROM players
   INNER JOIN players_teams ON players.id = players_teams.player_id
   WHERE won_championship = 1;
   
   SELECT *
   FROM players
   INNER JOIN players_teams ON players.id = players_teams.player_id
   WHERE players_teams.won_championship = 1
   AND players.seasons_playerd > 21;

6. 删

   DELETE FROM teams WHERE id = 6;

7. 组

   SELECT teams.name, COUNT(players.name)
   FROM teams
   INNER JOIN players_teams ON teams.id = players_teams.team_id
   INNER JOIN players ON players.id = players_teams.player_id
   GROUP BY teams.name;
   
   SELECT teams.name, players.name
   FROM teams
   INNER JOIN players_teams ON teams.id = players_teams.team_id
   INNER JOIN players ON players.id = players_teams.player_id
   ORDER BY teams.name;

8. 有

   SELECT teams.name, COUNT(players.id)
   FROM teams
   INNER JOIN players_teams ON teams.id = players_teams.team_id
   INNER JOIN players ON players.id = players_teams.player_id
   GROUP BY teams.name
   HAVING COUNT(players.id) > 1;

9. 子

   SELECT *
   FROM players
   INNER JOIN (
       SELECT player_id, MAX(won_championship)
       FROM players_teams
       GROUP BY player_id) sub_query_players_teams 
   ON players.id = sub_query_players_teams.player_id;

10. Back to Basics: Writing SQL Queries

11. How to properly format sqlite shell output?

[bingoohuang]

数据库表连接的简单解释

只返回两张表匹配的记录，这叫内连接（inner join）。 返回匹配的记录，以及表 A 多余的记录，这叫左连接（left join）。 返回匹配的记录，以及表 B 多余的记录，这叫右连接（right join）。 返回匹配的记录，以及表 A 和表 B 各自的多余记录，这叫全连接（full join）。

上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用 null 填充。

这四种连接，又可以分成两大类：内连接（inner join）表示只包含匹配的记录，外连接（outer join）表示还包含不匹配的记录。所以，左连接、右连接、全连接都属于外连接。

这四种连接的 SQL 语句如下。

SELECT * FROM A  
INNER JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
LEFT JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
RIGHT JOIN B ON A.book_id=B.book_id;

SELECT * FROM A  
FULL JOIN B ON A.book_id=B.book_id;