sqlite3

sqlite3 Chinook_Sqlite.sqlite

.tables                 -- 查看所有表
.schema                 -- 查看所有表的建表语句
.schema Artist          -- 查看 Artist 表结构
.databases              -- 查看已连接的数据库

SELECT * FROM Artist LIMIT 10;   -- 查看前10个艺术家
SELECT * FROM Album LIMIT 10;    -- 查看前10张专辑
SELECT * FROM Track LIMIT 10;    -- 查看前10首歌曲

SELECT Title FROM Album
WHERE ArtistId = 1;

SELECT Artist.Name, COUNT(Album.AlbumId) AS AlbumCount
FROM Artist
JOIN Album ON Artist.ArtistId = Album.ArtistId
GROUP BY Artist.Name
ORDER BY AlbumCount DESC
LIMIT 10;

SELECT Name, UnitPrice
FROM Track
ORDER BY UnitPrice DESC
LIMIT 5;

SELECT InvoiceId, Total
FROM Invoice
ORDER BY Total DESC
LIMIT 10;

SELECT Customer.FirstName, Customer.LastName, SUM(Invoice.Total) AS TotalSpent
FROM Customer
JOIN Invoice ON Customer.CustomerId = Invoice.CustomerId
GROUP BY Customer.CustomerId
ORDER BY TotalSpent DESC
LIMIT 10;

SELECT Country, COUNT(*) AS CustomerCount
FROM Customer
GROUP BY Country
ORDER BY CustomerCount DESC;

// chinook_query.c
#include <stdio.h>
#include "sqlite3.h"
 
#ifdef _WIN32
#include <windows.h>
#endif
 
int main(int argc, char **argv)
{
#ifdef _WIN32
    SetConsoleOutputCP(CP_UTF8);
#endif
    const char *dbfile = (argc > 1) ? argv[1] : "Chinook_Sqlite.sqlite";
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc;
 
    rc = sqlite3_open(dbfile, &db);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }
    printf("Opened database: %s\n\n", dbfile);
 
    // 1. 查询前10个艺术家
    const char *sql1 = "SELECT ArtistId, Name FROM Artist LIMIT 10;";
    rc = sqlite3_prepare_v2(db, sql1, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "Failed to prepare statement: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    printf("--- 前10个艺术家 ---\n");
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        int id = sqlite3_column_int(stmt, 0);
        const unsigned char *name = sqlite3_column_text(stmt, 1);
        printf("ArtistId=%d, Name=%s\n", id, name ? (const char *)name : "NULL");
    }
    sqlite3_finalize(stmt);
 
    // 2. 每位艺术家的专辑数量
    const char *sql2 =
        "SELECT Artist.Name, COUNT(Album.AlbumId) AS AlbumCount "
        "FROM Artist JOIN Album ON Artist.ArtistId = Album.ArtistId "
        "GROUP BY Artist.ArtistId "
        "ORDER BY AlbumCount DESC "
        "LIMIT 10;";
    rc = sqlite3_prepare_v2(db, sql2, -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        fprintf(stderr, "Prepare failed: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    printf("\n--- 每位艺术家的专辑数量（前10） ---\n");
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW)
    {
        const unsigned char *name = sqlite3_column_text(stmt, 0);
        int count = sqlite3_column_int(stmt, 1);
        printf("%s: %d\n", name ? (const char *)name : "NULL", count);
    }
    sqlite3_finalize(stmt);
 
    sqlite3_close(db);
    printf("\nClosed database.\n");
    return 0;
}

gcc -o chinook_query.exe chinook_query.c sqlite3.c -I .

.\chinook_query.exe

npm init -y
npm install better-sqlite3

// query_chinook_sync.js
const Database = require('better-sqlite3');
const path = require('path');
 
const db = new Database(path.join(__dirname, 'Chinook_Sqlite.sqlite'), { readonly: true });
 
// 前10个艺术家
console.log('--- 前10个艺术家 ---');
const artists = db.prepare('SELECT ArtistId, Name FROM Artist LIMIT 10').all();
artists.forEach(a => console.log(`ArtistId=${a.ArtistId}, Name=${a.Name}`));
 
// 每位艺术家的专辑数量（前10）
console.log('\n--- 每位艺术家的专辑数量（前10） ---');
const counts = db.prepare(`
    SELECT Artist.Name, COUNT(Album.AlbumId) AS AlbumCount
    FROM Artist
    JOIN Album ON Artist.ArtistId = Album.ArtistId
    GROUP BY Artist.ArtistId
    ORDER BY AlbumCount DESC
    LIMIT 10;
`).all();
counts.forEach(c => console.log(`${c.Name}: ${c.AlbumCount}`));
 
db.close();

# query_chinook.py
import sqlite3
 
# 连接数据库（只读模式）
conn = sqlite3.connect("Chinook_Sqlite.sqlite")
conn.text_factory = str  # 保证 UTF-8 显示特殊字符
cursor = conn.cursor()
 
# 1. 前10个艺术家
print('--- 前10个艺术家 ---')
cursor.execute("SELECT ArtistId, Name FROM Artist LIMIT 10;")
for row in cursor.fetchall():
    print(f"ArtistId={row[0]}, Name={row[1]}")
 
# 2. 每位艺术家的专辑数量（前10名）
print('\n--- 每位艺术家的专辑数量（前10） ---')
cursor.execute("""
    SELECT Artist.Name, COUNT(Album.AlbumId) AS AlbumCount
    FROM Artist
    JOIN Album ON Artist.ArtistId = Album.ArtistId
    GROUP BY Artist.ArtistId
    ORDER BY AlbumCount DESC
    LIMIT 10;
""")
for row in cursor.fetchall():
    print(f"{row[0]}: {row[1]}")
 
# 3. 最贵的5首歌曲及对应艺术家
print('\n--- 最贵的5首歌曲及艺术家 ---')
cursor.execute("""
    SELECT Track.Name, Artist.Name AS Artist, Track.UnitPrice
    FROM Track
    JOIN Album ON Track.AlbumId = Album.AlbumId
    JOIN Artist ON Album.ArtistId = Artist.ArtistId
    ORDER BY Track.UnitPrice DESC
    LIMIT 5;
""")
for row in cursor.fetchall():
    print(f"{row[0]} - {row[1]}: ${row[2]:.2f}")
 
# 4. 每个国家的顾客数量
print('\n--- 每个国家的顾客数量 ---')
cursor.execute("""
    SELECT Country, COUNT(*) AS CustomerCount
    FROM Customer
    GROUP BY Country
    ORDER BY CustomerCount DESC;
""")
for row in cursor.fetchall():
    print(f"{row[0]}: {row[1]}")
 
# 关闭连接
conn.close()

python query_chinook.py