Skip to content · ⁨Pular para o conteúdo⁩
Subjects · ⁨Matérias⁩
  • 1 Querying basics · ⁨Noções básicas de consulta⁩
    1.1

    SELECT y FROM

    English

    A database 数据库 keeps data in tables 表. A query 查询 reads data with SELECT. SELECT * returns every column; FROM names the table.

    • Each row 行 is one record 记录. SQL keywords are written in UPPERCASE by habit.
    • A statement ends with a semicolon ;.
    Português

    Una base de datos 数据库 guarda los datos en tablas 表. Una consulta 查询 lee los datos con SELECT. SELECT * devuelve todas las columnas; FROM nombra la tabla.

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
    SELECT * FROM student;
    
    Una tabla almacena registros: cada fila es un registro, cada columna es un campo
    Una tabla almacena registros: cada fila es un registro, cada columna es un campo
    • Cada fila 行 es un registro 记录. Las palabras clave SQL se escriben en MAYÚSCULAS por costumbre.
    • Una sentencia termina con punto y coma ;.
    1.2

    Elegir columnas

    English

    List the columns 列 you want, separated by commas. Use AS to rename a column in the result (an alias 别名).

    A selected column can also be a calculation — pair it with AS to name the new column:

    • SELECT DISTINCT col removes duplicate values from the result.
    Português

    Enumere las columnas 列 que desea, separadas por comas. Use AS para renombrar una columna en el resultado (un alias 别名).

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
    SELECT name, score AS mark FROM student;
    

    Una columna seleccionada también puede ser un cálculo; combínela con AS para nombrar la nueva columna:

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
    SELECT name, score + 5 AS bonus FROM student;
    
    • SELECT DISTINCT col elimina valores duplicados del resultado.
    1.3

    Filtrado con WHERE

    English

    WHERE keeps only the rows that match a condition 条件. Compare with =, <> (not equal), <, >, <=, >=. Put text in single quotes.

    • SQL runs the parts in this order: FROM → WHERE → SELECT.

    Common mistakes

    • Put text values in single quotes: WHERE name = 'Ann'; column names have no quotes.
    • SELECT * returns every column — name only the columns you need.
    • WHERE filters rows; it comes after FROM.
    Português

    WHERE conserva solo las filas que coinciden con una condición 条件. Compare con =, <> (diferente de), <, >, <=, >=. Ponga texto entre comillas simples.

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71), (3, 'Ana', 95);
    SELECT name, score FROM student WHERE score >= 80;
    
    • SQL ejecuta las partes en este orden: FROM → WHERE → SELECT.

    Erros comuns

    • Ponga valores de texto entre comillas simples: WHERE name = 'Ann'; los nombres de columna no llevan comillas.
    • SELECT * retorna todas as colunas — nomeie apenas as colunas que você precisa.
    • WHERE filtra filas; viene después de FROM.
  • 2 Filtering & logic · ⁨Filtragem & lógica⁩
    2.1

    AND, OR, NOT

    English

    Combine conditions with AND, OR, NOT. AND needs all sides true; OR needs any side true; NOT reverses one. Use brackets to set the precedence 优先级.

    Português

    Combine condiciones con AND, OR, NOT. AND requiere que todos los lados sean verdaderos; OR requiere que cualquier lado sea verdadero; NOT invierte uno. Use paréntesis para establecer la precedencia 优先级.

    CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
    INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
    SELECT name FROM student WHERE form = '11A' AND score >= 90;
    
    AND necesita todos verdaderos; OR necesita alguno; NOT invierte uno
    AND necesita todos verdaderos; OR necesita alguno; NOT invierte uno
    2.2

    LIKE, IN y BETWEEN

    English

    LIKE matches a text pattern 模式: % stands for any text and _ for one character — these are wildcards 通配符.

    Pattern Matches
    'M%' starts with M
    '%a' ends with a
    '%an%' contains "an"
    'M_i' M, then exactly one character, then i

    IN matches a set 集合 of values; BETWEEN matches a range 范围 (both ends included).

    Português

    LIKE coincide con un patrón de texto 模式: % representa cualquier texto y _ representa un solo carácter; estos son comodines 通配符.

    CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
    INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
    SELECT name FROM student WHERE name LIKE 'M%';        -- starts with M
    
    Patrón Coincide con
    'M%' comienza con M
    '%a' termina con a
    '%an%' contiene "an"
    'M_i' M, luego exactamente un carácter, luego i

    IN coincide con un conjunto 集合 de valores; BETWEEN coincide con un rango 范围 (ambos extremos incluidos).

    CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
    INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
    SELECT name, score FROM student
    WHERE form IN ('11A', '11C') AND score BETWEEN 80 AND 100;
    
    2.3

    NULL

    English

    NULL marks a missing value 缺失值 — nothing was stored in that cell. NULL is not 0 and not an empty string. A comparison with = or <> never matches it; test with IS NULL or IS NOT NULL.

    • WHERE score = NULL returns no rows at all — not even Sam's row.
    • A NULL in arithmetic gives NULL: score + 5 stays NULL for Sam.

    Common mistakes

    • Test for an empty value with IS NULL, never = NULL.
    • In LIKE, % matches any text and _ matches one character: 'A%' means "starts with A".
    • IN (1, 2, 3) is shorter than many ORs; BETWEEN a AND b includes both ends.
    Português

    NULL marca um valor ausente 缺失值 — nada foi armazenado naquela célula. NULL não é 0 nem uma string vazia. Uma comparação com = ou <> nunca corresponde a ele; teste com IS NULL ou IS NOT NULL.

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
    SELECT name FROM student WHERE score IS NULL;
    
    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
    SELECT name, score FROM student WHERE score IS NOT NULL;
    
    • WHERE score = NULL no devuelve ninguna fila — ni siquiera la fila de Sam.
    • Un NULL en aritmética da NULL: score + 5 permanece NULL para Sam.

    Erros comuns

    • Pruebe un valor vacío con IS NULL, nunca = NULL.
    • En LIKE, % coincide con cualquier texto y _ coincide con un carácter: 'A%' significa "comienza con A".
    • IN (1, 2, 3) es más corto que muchos ORs; BETWEEN a AND b incluye ambos extremos.
  • 3 Sorting & limiting · ⁨Ordenação & limitação⁩
    3.1

    ORDER BY y LIMIT

    English

    ORDER BY sorts 排序 the result rows. Add DESC for descending 降序 (high to low); the default is ascending 升序 (low to high).

    Sort by more than one column

    List several columns. A tie 平局 in the first column is broken by the next.

    LIMIT (top-N)

    LIMIT n keeps only the first n rows — pair it with ORDER BY for a top-N 前 N 名 list.

    • ORDER BY can also sort by an alias or a calculation: ORDER BY avg_score DESC.
    • Text sorts alphabetically: ORDER BY name runs A → Z.

    Common mistakes

    • ORDER BY sorts ascending by default; add DESC for descending.
    • ORDER BY comes near the end, after WHERE and GROUP BY.
    • LIMIT caps how many rows come back, but only after sorting.
    Português

    ORDER BY ordena 排序 as linhas resultantes. Adicione DESC para descendente 降序 (alto para baixo); o padrão é ascendente 升序 (baixo para alto).

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
    SELECT name, score FROM student ORDER BY score DESC;
    

    Ordenar por más de una columna

    Enumere varias columnas. Un empate 平局 en la primera columna se resuelve con la siguiente.

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',88),(3,'Ana',95);
    SELECT name, score FROM student ORDER BY score DESC, name ASC;
    

    LIMIT (top-N)

    LIMIT n conserva solo las primeras n filas — combínelo con ORDER BY para una lista top-N 前 N 名.

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
    SELECT name, score FROM student ORDER BY score DESC LIMIT 2;
    
    • ORDER BY también puede ordenar por un alias o un cálculo: ORDER BY avg_score DESC.
    • El texto se ordena alfabéticamente: ORDER BY name ejecuta de A → Z.

    Erros comuns

    • ORDER BY ordena ascendente por defecto; agregue DESC para descendente.
    • ORDER BY viene cerca del final, después de WHERE y GROUP BY.
    • LIMIT limita cuántas filas se devuelven, pero solo después del ordenamiento.
    ORDER BY ordena; LIMIT conserva las primeras n después del ordenamiento
    ORDER BY ordena; LIMIT conserva las primeras n después del ordenamiento
  • 4 Aggregates & grouping · ⁨Agregados & agrupamento⁩
    4.1

    Funciones agregadas

    English

    An aggregate function 聚合函数 turns many rows into one summary 汇总 value. Wrap an average in ROUND(x, 2) to tidy it.

    Function Gives
    COUNT(*) how many rows
    SUM(col) the total
    AVG(col) the mean average
    MIN(col) / MAX(col) the smallest / largest value
    Português

    Una función agregada 聚合function convierte muchas filas en un único valor resumen 汇总. Envuelva un promedio en ROUND(x, 2) para limpiarlo.

    Función Da
    COUNT(*) cuántas filas
    SUM(col) el total
    AVG(col) el promedio medio
    MIN(col) / MAX(col) el valor más pequeño / más grande
    CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
    INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
    SELECT COUNT(*), ROUND(AVG(score), 2), MAX(score) FROM student;
    
    4.2

    GROUP BY y HAVING

    English

    GROUP BY makes one summary row per group 组. HAVING filters those groups — it is like WHERE, but it runs after grouping.

    Whatever order you write them in, SQL always runs the clauses of a query in the same fixed order:

    Step Clause
    1 FROM (and any JOIN)
    2 WHERE — filter rows
    3 GROUP BY — form groups
    4 HAVING — filter groups
    5 SELECT — compute the output columns
    6 ORDER BY, then LIMIT

    Common mistakes

    • You cannot select a plain column beside an aggregate unless it is in GROUP BY.
    • Filter rows with WHERE (before grouping) and filter groups with HAVING (after).
    • COUNT(*) counts rows; COUNT(col) skips NULLs in that column.
    Português

    GROUP BY crea una fila resumen por grupo 组. HAVING filtra esos grupos — es como WHERE, pero se ejecuta después del agrupamiento.

    CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
    INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
    SELECT form, COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
    FROM student
    GROUP BY form
    HAVING COUNT(*) > 1;
    
    GROUP BY agrupa filas en un solo cubo por valor; cada cubo se convierte en una fila resumen
    GROUP BY agrupa filas en un solo cubo por valor; cada cubo se convierte en una fila resumen

    Cualquiera que sea el orden en que los escriba, SQL siempre ejecuta las cláusulas de una consulta en el mismo orden fijo:

    Paso Cláusula
    1 FROM (y cualquier JOIN)
    2 WHERE — filtrar filas
    3 GROUP BY — formar grupos
    4 HAVING — filtrar grupos
    5 SELECT — calcular las columnas de salida
    6 ORDER BY, luego LIMIT

    Erros comuns

    • No puede seleccionar una columna simple junto a una agregada a menos que esté en GROUP BY.
    • Filtre filas con WHERE (antes del agrupamiento) y filtre grupos con HAVING (después).
    • COUNT(*) cuenta filas; COUNT(col) omite NULLs en esa columna.
  • 5 Joins
    5.1

    Claves y relaciones

    English

    A primary key 主键 uniquely names each row in a table. A foreign key 外键 in one table points to the primary key of another — that builds a relationship 关系 between them.

    Português

    Una clave primaria 主键 nombra de forma única cada fila en una tabla. Una clave foránea 外键 en una tabla apunta a la clave primaria de otra; eso construye una relación 关系 entre ellas.

    CREATE TABLE class (id INTEGER, name TEXT);
    CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
    INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
    INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
    SELECT * FROM student;
    
    5.2

    INNER JOIN

    English

    A join 连接 combines rows from two tables. INNER JOIN ... ON ... keeps rows where the keys match. A short alias 别名 (s, c) keeps the query readable.

    Português

    Un join 连接 combina filas de dos tablas. INNER JOIN ... ON ... conserva filas donde las claves coinciden. Un alias corto 别名 (s, c) mantiene la consulta legible.

    CREATE TABLE class (id INTEGER, name TEXT);
    CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
    INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
    INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
    SELECT s.name, c.name AS class
    FROM student s INNER JOIN class c ON s.class_id = c.id;
    
    INNER JOIN hace coincidir cada clave foránea con una clave primaria y combina las filas coincidentes
    INNER JOIN hace coincidir cada clave foránea con una clave primaria y combina las filas coincidentes
    5.3

    Joining con grouping

    English

    Join first, then GROUP BY to summarise across the joined rows.

    Português

    Haga el join primero, luego GROUP BY para resumir a través de las filas unidas.

    CREATE TABLE class (id INTEGER, name TEXT);
    CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
    INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
    INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2), (3, 'Ana', 1);
    SELECT c.name AS class, COUNT(*) AS n
    FROM student s INNER JOIN class c ON s.class_id = c.id
    GROUP BY c.name;
    
    5.4

    LEFT JOIN

    English

    An INNER JOIN keeps only matched rows. A LEFT JOIN 左连接 keeps every row of the left (first) table; where there is no match, the right table's columns come back as NULL.

    • Sam has no class, but the row still appears — with class as NULL.
    • To find only the unmatched rows, add WHERE c.id IS NULL.

    Common mistakes

    • A join with no ON condition pairs every row with every row (a cross join).
    • Match the foreign key to the primary key: ON orders.customer_id = customers.id.
    • An INNER JOIN drops rows that have no match on the other side; use a LEFT JOIN to keep them.
    Português

    Un INNER JOIN conserva solo filas coincidentes. Un LEFT JOIN left join 左连接 conserva todas las filas de la tabla izquierda (primera); donde no hay coincidencia, las columnas de la tabla derecha vuelven como NULL.

    CREATE TABLE class (id INTEGER, name TEXT);
    CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
    INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
    INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', NULL);
    SELECT s.name, c.name AS class
    FROM student s LEFT JOIN class c ON s.class_id = c.id;
    
    • Sam no tiene clase, pero la fila aún aparece — con class como NULL.
    • Para encontrar solo las filas no coincidentes, agregue WHERE c.id IS NULL.

    Erros comuns

    • Un join sin condición ON empareja cada fila con cada fila (un cross join).
    • Haga coincidir la clave foránea con la clave primaria: ON orders.customer_id = customers.id.
    • Un INNER JOIN elimina filas que no tienen coincidencia en el otro lado; use un LEFT JOIN para conservarlo.
  • 6 Modifying data · ⁨Modificando dados⁩
    6.1

    INSERT

    English

    INSERT INTO ... VALUES ... adds new rows 行. Name the columns, then give the values in the same order. (Each block below ends with a SELECT so you can see the result.)

    • INSERT INTO t VALUES (...), (...); adds several rows in one statement.
    Português

    INSERT INTO ... VALUES ... agrega nuevas filas 行. Nombre las columnas, luego dé los valores en el mismo orden. (Cada bloque abajo termina con un SELECT para que pueda ver el resultado.)

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student (id, name, score) VALUES (1, 'Mei', 88);
    INSERT INTO student VALUES (2, 'Sam', 71);
    SELECT * FROM student;
    
    • INSERT INTO t VALUES (...), (...); agrega varias filas en una sola sentencia.
    INSERT agrega una nueva fila a una tabla
    INSERT agrega una nueva fila a una tabla
    6.2

    UPDATE

    English

    UPDATE ... SET ... WHERE ... changes existing rows. Always add WHERE, or every row changes.

    Português

    UPDATE ... SET ... WHERE ... cambia filas existentes. Siempre agregue WHERE, o cambiará cada fila.

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
    UPDATE student SET score = 75 WHERE name = 'Sam';
    SELECT * FROM student;
    
    6.3

    DELETE

    English

    DELETE FROM ... WHERE ... removes rows. Without WHERE it empties the whole table 表.

    Common mistakes

    • UPDATE and DELETE without a WHERE change every row — always add the WHERE.
    • In INSERT, the values must line up with the column list in order and type.
    • Test a risky DELETE first as a SELECT with the same WHERE.
    Português

    DELETE FROM ... WHERE ... elimina filas. Sin WHERE vaciará toda la tabla 表.

    CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
    INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
    DELETE FROM student WHERE score < 80;
    SELECT * FROM student;
    

    Erros comuns

    • UPDATE y DELETE sin un WHERE cambian cada fila — siempre agregue el WHERE.
    • En INSERT, los valores deben alinearse con la lista de columnas en orden y tipo.
    • Pruebe un DELETE riesgoso primero como un SELECT con el mismo WHERE.
  • 7 Defining tables · ⁨Definindo tabelas⁩
    7.1

    CREATE TABLE & tipos de datos

    English

    CREATE TABLE defines a table's schema 表结构: the column names and their data types 数据类型. The main SQLite types are INTEGER, TEXT, and REAL (a decimal number).

    Português

    CREATE TABLE define o esquema de uma tabela 表结构: os nomes das colunas e seus tipos de dados 数据类型. Os principais tipos do SQLite são INTEGER, TEXT e REAL (um número decimal).

    CREATE TABLE student (id INTEGER, name TEXT, score REAL);
    INSERT INTO student VALUES (1, 'Mei', 88.5);
    SELECT * FROM student;
    
    CREATE TABLE nomeia colunas e seus tipos
    CREATE TABLE nomeia colunas e seus tipos
    7.2

    Chaves & restrições

    English

    A constraint 约束 is a rule on a column: PRIMARY KEY (a unique id), NOT NULL (must have a value), UNIQUE, and DEFAULT (a fallback value).

    Declare a foreign key with REFERENCES — it records that the column points at another table's primary key:

    • The long form is FOREIGN KEY (class_id) REFERENCES class(id) on its own line.
    Português

    Uma restrição 约束 é uma regra sobre uma coluna: PRIMARY KEY (um ID único), NOT NULL (deve ter um valor), UNIQUE e DEFAULT (um valor padrão).

    CREATE TABLE student (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        score INTEGER DEFAULT 0
    );
    INSERT INTO student (id, name) VALUES (1, 'Mei');
    SELECT * FROM student;
    

    Declarar uma chave estrangeira com REFERENCES — ela registra que a coluna aponta para a chave primária de outra tabela:

    CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE student (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        class_id INTEGER REFERENCES class(id)
    );
    INSERT INTO class VALUES (1, 'Maths');
    INSERT INTO student VALUES (1, 'Mei', 1);
    SELECT s.name, c.name AS class
    FROM student s INNER JOIN class c ON s.class_id = c.id;
    
    • A forma longa é FOREIGN KEY (class_id) REFERENCES class(id) na sua própria linha.
    7.3

    ALTER TABLE

    English

    ALTER TABLE ... ADD COLUMN ... changes the schema of a table that already exists. A DEFAULT fills the new column in the old rows.

    • ALTER TABLE student RENAME TO pupil; renames the whole table.
    • DROP TABLE student; deletes the table completely — structure and data.

    Common mistakes

    • Every column needs a data type (e.g. INTEGER, TEXT).
    • A PRIMARY KEY must be unique and cannot be NULL.
    • A foreign key value must exist in the table it points to.
    Português

    ALTER TABLE ... ADD COLUMN ... altera o esquema de uma tabela que já existe. Um DEFAULT preenche a nova coluna nas linhas antigas.

    CREATE TABLE student (id INTEGER, name TEXT);
    INSERT INTO student VALUES (1, 'Mei');
    ALTER TABLE student ADD COLUMN score INTEGER DEFAULT 0;
    SELECT * FROM student;
    
    • ALTER TABLE student RENAME TO pupil; renomeia toda a tabela.
    • DROP TABLE student; exclui a tabela completamente — estrutura e dados.

    Erros comuns

    • Toda coluna precisa de um tipo de dado (ex. INTEGER, TEXT).
    • Uma PRIMARY KEY deve ser única e não pode ser NULL.
    • Um valor de chave estrangeira deve existir na tabela a que aponta.
  • 8 Database design · ⁨Design de banco de dados⁩
    8.1

    Relações e diagramas ER

    English

    Tables connect through relationships 关系. One-to-many 一对多 is the most common: one class has many students. Many-to-many 多对多 needs a join table 连接表 in the middle. An entity-relationship diagram 实体关系图 (ER diagram) draws each entity 实体 as a box and each relationship as a line.

    Relationship Example
    one-to-one a person and their passport
    one-to-many a class and its students
    many-to-many students and clubs

    The join table holds one row per link. Here Mei is in two clubs, and Chess has two members:

    Português

    As tabelas se conectam através de relações 关系. One-to-many 一对多 é o mais comum: uma turma tem muitos alunos. Many-to-many 多对多 precisa de uma tabela join 连接表 no meio. Um diagrama entidade-relacionamento 实体关系图 (ER diagram) desenha cada entidade 实体 como uma caixa e cada relação como uma linha.

    Relação Exemplo
    one-to-one uma pessoa e seu passaporte
    one-to-many uma turma e seus alunos
    many-to-many alunos e clubes
    O lado "many" carrega a chave estrangeira; um link many-to-many precisa de uma tabela join
    O lado "many" carrega a chave estrangeira; um link many-to-many precisa de uma tabela join

    A tabela join contém uma linha por link. Aqui Mei está em dois clubes, e Chess tem dois membros:

    CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE club (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE membership (student_id INTEGER, club_id INTEGER);
    INSERT INTO student VALUES (1, 'Mei'), (2, 'Sam');
    INSERT INTO club VALUES (1, 'Chess'), (2, 'Art');
    INSERT INTO membership VALUES (1, 1), (1, 2), (2, 1);
    SELECT s.name, c.name AS club
    FROM membership m
    INNER JOIN student s ON m.student_id = s.id
    INNER JOIN club c ON m.club_id = c.id;
    
    8.2

    Normalização

    English

    Normalisation 范式化 organises tables to avoid redundancy 冗余 (the same data repeated) and the update mistakes it causes. The first three normal forms 范式:

    • 1NF: every cell holds one atomic 原子 value — no lists inside a cell.
    • 2NF: no column depends on only part of a composite key 复合主键.
    • 3NF: no column depends on another non-key column.
    Unnormalised (bad) Normalised (better)
    student(name, club1, club2) student(name) + membership(student, club)

    Common mistakes

    • Split repeating groups into their own table (normalisation) instead of many similar columns.
    • Each table should describe ONE kind of thing.
    • Link tables with a foreign key that points to another table's primary key.
    Português

    Normalização 范式化 organiza tabelas para evitar redundância 冗余 (os mesmos dados repetidos) e os erros de atualização que isso causa. As três primeiras formas normais 范式:

    • 1NF: cada célula contém um valor atômico 原子 — nenhuma lista dentro de uma célula.
    • 2NF: nenhuma coluna depende apenas de parte de uma chave composta 复合主键.
    • 3NF: nenhuma coluna depende de outra coluna que não seja de chave.
    Não normalizado (ruim) Normalizado (melhor)
    student(name, club1, club2) student(name) + membership(student, club)

    Erros comuns

    • Dividir grupos repetitivos em sua própria tabela (normalização) em vez de muitas colunas semelhantes.
    • Cada tabela deve descrever UM tipo de coisa.
    • Conectar tabelas com uma chave estrangeira que aponta para a chave primária de outra tabela.

Log in or create account · ⁨Entrar ou criar conta⁩

IGCSE, A-Level & AP