Creating tables · 创建表
Two kinds of SQL command
SQL has two halves:
- DDL (Data Definition Language) changes the structure — the tables themselves.
- DML (Data Manipulation Language) works with the data inside the tables — reading it with
SELECT, and adding, changing, or removing it withINSERT/UPDATE/DELETE. Everything so far (SELECT) is DML.
CREATE TABLE is the main DDL command. It lists each column with a data type and marks the primary key:
两类 SQL 命令
SQL 分为两半:
- DDL(数据定义语言)改变结构 —— 也就是表本身。
- DML(数据操纵语言)处理表里的数据 —— 用
SELECT读取,用INSERT/UPDATE/DELETE增加、修改或删除。到目前为止学的(SELECT)都是 DML。
CREATE TABLE 是主要的 DDL 命令。它列出每一列及其数据类型,并标出主键:
CREATE TABLE customer (
id INTEGER PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(15)
);
Data types and foreign keys
Common data types:
INTEGER— whole numbersVARCHAR(n)/CHAR(n)— text, up to / exactlyncharactersDECIMAL(p, s)— a number withsdecimal placesDATE,BOOLEAN
To link to another table, add a FOREIGN KEY:
The checker reads back your table's structure to see which columns you created.
数据类型与外键
常见的数据类型:
INTEGER—— 整数VARCHAR(n)/CHAR(n)—— 文本,最多 / 恰好n个字符DECIMAL(p, s)—— 带s位小数的数DATE、BOOLEAN
要连接到另一张表,加上 FOREIGN KEY:
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
FOREIGN KEY (customer_id) REFERENCES customer(id)
);
检查器会读回你建的表的结构,看看你创建了哪些列。
Common mistakes
- Every column needs a data type (e.g.
INTEGER,TEXT). - Declare the PRIMARY KEY when you create the table.
常见错误
- 每一列都要有数据类型(如
INTEGER、TEXT)。 - 建表时声明主键。
Create a table called teacher with three columns: id (INTEGER, the primary key), name (VARCHAR(20)), and subject (VARCHAR(15)). · 创建一张名为 teacher 的表,有三列:id(INTEGER,主键)、name(VARCHAR(20))和 subject(VARCHAR(15))。
Click Run to see the output here. · 点击“运行”查看此处输出。
Design another table. Create event with id (INTEGER PRIMARY KEY), title (VARCHAR(30)), and price (DECIMAL(6,2) — a money column with 2 decimal places). · 再设计一张表。创建 event,包含 id(INTEGER PRIMARY KEY)、title(VARCHAR(30))和 price(DECIMAL(6,2) —— 一个带两位小数的金额列)。
Click Run to see the output here. · 点击“运行”查看此处输出。