SQL basics

Knowledge Videos

Short animated explainers, organised by subject.

SQL basics

SQL is the language for asking a database: CREATE TABLE, INSERT INTO, SELECT with WHERE and ORDER BY, then UPDATE and DELETE (always with WHERE).

Try it

Transcript

S Q L is the language for talking to a database. Create table makes a table, with its columns and their types.

Insert into adds a row. You name the table, the columns, and the values.

Select gets rows back. Where keeps only the rows that match, and order by sorts them.

Update changes rows, and delete removes them. Always use where, or the change applies to every row.

Remember: create table, insert into, select with where, and update and delete, always with where.

More in this series

1:23

40How a web server works

A web server waits on a port for requests and sends responses. Handlers, http.HandleFunc and ListenAndServe, and seeing the request in the browser.

Goserverporthandlerhttpbeginner
1:30

41Routes, methods and status codes

A route connects a path and a method to a handler. GET, POST and DELETE, the status codes 200, 201, 400, 404 and 500, and how Go answers 404.

GorouteGETPOSTstatus code404beginner
1:19

42A JSON API in Go

An API returns data instead of pages. A Go struct becomes JSON with struct tags, a handler writes it with json.NewEncoder, and the page loads it with fetch.

GoAPIJSONstruct tagfetchbeginner
1:21

43POST and saving data

A POST request carries data in its body. Decode the JSON into a struct, keep it in a slice (lost on restart), and protect the shared slice with a mutex.

GoPOSTrequest bodymutexJSONbeginner
1:17

44What is a database?

A database keeps data in tables of rows and columns, like a strict spreadsheet. Primary keys, why data in a file survives a restart, and asking questions with SQL.

databasetablerowprimary keySQLbeginner
1:14

46Go and databases

Go talks to a SQLite database with database/sql and a driver. Exec changes data, Query reads rows into structs, and ? placeholders pass values safely.

Godatabase/sqlSQLiteQueryExecplaceholderbeginner
1:20

47Input, validation and safety

Never trust a request: validate empty and too-long values and answer 400, why ? placeholders stop SQL injection, and keep secrets and database files out of Git.

GovalidationSQL injectionsafetygitignorebeginner