基础回顾 Sqlite3 知识点

Question

使用 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>

增

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);

查

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)

联

.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;

左

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

当

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;

删

DELETE FROM teams WHERE id = 6;

组

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;

有

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;

子

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;

1. Back to Basics: Writing SQL Queries
2. How to properly format sqlite shell output?

数据库表连接的简单解释

只返回两张表匹配的记录，这叫内连接（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;