sql - Sqlite扑克牌表

Question

我正在使用Sqlite学习SQL。我想使用Sqlite创建一个简单的扑克牌。我的尝试在下面。

因为我不断收到语法错误，所以我尝试让DB browser for Sqlite通过手动添加字段来创建代码来创建表。现在，我剩下某种混合语法了，通过语法检查器将其弄不清楚是什么问题。

如果我尝试只插入一行：

INSERT INTO cards (name, suit, suit_symbol, score) VALUES (

    -- Spades
    ("A", "spades", "♠", 1)
  );

我得到 Uncaught Error: 1 values for 4 columns

如果我尝试运行整个程序，则会得到 Uncaught Error: near ")": syntax error

我猜我的代码至少存在两个问题，部分困难是我已经学到了一点点MySQL，这似乎有些微妙和令人困惑。

我们非常感谢您提供任何在Sqlite中成功建立我的表并了解其为何无法正常工作的帮助。

--CREATE DATABASE IF NOT EXISTS cards;

CREATE TABLE IF not exists "cards" (
    "id"    INTEGER PRIMARY KEY AUTOINCREMENT,
    "name"  TEXT,
    "suit"  TEXT,
    "suit_symbol"   TEXT,
    "score" INTEGER
);

INSERT INTO cards (name, suit, suit_symbol, score) VALUES (

    -- Spades
    ("A", "spades", "♠", 1), 
    ("2", "spades", "♠", 2), 
    ("3", "spades", "♠", 3), 
    ("4", "spades", "♠", 4), 
    ("5", "spades", "♠", 5), 
    ("6", "spades", "♠", 6), 
    ("7", "spades", "♠", 7), 
    ("8", "spades", "♠", 8), 
    ("9", "spades", "♠", 9), 
    ("10", "spades", "♠", 10), 
    ("J", "spades", "♠", 11), 
    ("Q", "spades", "♠", 12), 
    ("K", "spades", "♠", 13), 

    -- Hearts
    ("A", "hearts", "♥", 1), 
    ("2", "hearts", "♥", 2), 
    ("3", "hearts", "♥", 3), 
    ("4", "hearts", "♥", 4), 
    ("5", "hearts", "♥", 5), 
    ("6", "hearts", "♥", 6), 
    ("7", "hearts", "♥", 7), 
    ("8", "hearts", "♥", 8), 
    ("9", "hearts", "♥", 9), 
    ("10", "hearts", "♥", 10), 
    ("J", "hearts", "♥", 11), 
    ("Q", "hearts", "♥", 12), 
    ("K", "hearts", "♥", 13), 

    -- Clubs
    ("A", "clubs", "♣", 1), 
    ("2", "clubs", "♣", 2), 
    ("3", "clubs", "♣", 3), 
    ("4", "clubs", "♣", 4), 
    ("5", "clubs", "♣", 5), 
    ("6", "clubs", "♣", 6), 
    ("7", "clubs", "♣", 7), 
    ("8", "clubs", "♣", 8), 
    ("9", "clubs", "♣", 9), 
    ("10", "clubs", "♣", 10), 
    ("J", "clubs", "♣", 11), 
    ("Q", "clubs", "♣", 12), 
    ("K", "clubs", "♣", 13), 

    -- Diamonds
    ("A", "diamonds", "♦", 1), 
    ("2", "diamonds", "♦", 2), 
    ("3", "diamonds", "♦", 3), 
    ("4", "diamonds", "♦", 4), 
    ("5", "diamonds", "♦", 5), 
    ("6", "diamonds", "♦", 6), 
    ("7", "diamonds", "♦", 7), 
    ("8", "diamonds", "♦", 8), 
    ("9", "diamonds", "♦", 9), 
    ("10", "diamonds", "♦", 10), 
    ("J", "diamonds", "♦", 11), 
    ("Q", "diamonds", "♦", 12), 
    ("K", "diamonds", "♦", 13), 
    );

Answer 1

在元组周围不应有用于值的外部括号。只有一个元组被圆括号括起来，多个元组被逗号分隔。

尝试：

INSERT INTO cards
            (name,
             suit,
             suit_symbol,
             score)
            VALUES ('A', 'spades', '♠', 1), 
                   ('2', 'spades', '♠', 2),
                   ...
                   ('K', 'diamonds', '♦', 13);

顺便说一句：SQL中字符串文字的正确引号是单引号。双引号用于标识符。尽管某些DBMS接受了互换它们的建议，但是习惯于采用标准方式是个好主意，这种方式更可能是跨平台的。