Chapter 2: SQL Tables
Chapter 2: SQL Tables κ΄λ ¨
The CREATE TABLE statement is used to create a new table in a database.
How to use the CREATE TABLE statement
To create a table, use the CREATE TABLE statement followed by the name of the table and the fields you want in the table.
CREATE TABLE employees (id INTEGER, name TEXT, age INTEGER, is_manager BOOLEAN, salary INTEGER);
Each field name is followed by its datatype. We'll get to data types in a minute.
It's also acceptable and common to break up the CREATE TABLE statement with some whitespace like this:
CREATE TABLE employees(
id INTEGER,
name TEXT,
age INTEGER,
is_manager BOOLEAN,
salary INTEGER
);
How to Alter Tables
We often need to alter our database schema without deleting it and re-creating it. Imagine if Twitter deleted its database each time it needed to add a feature, that would be a disaster! Your account and all your tweets would be wiped out on a daily basis.
Instead, we can use use the ALTER TABLE statement to make changes in place without deleting any data.
How to use ALTER TABLE
With SQLite an ALTER TABLE statement allows you to:
1. Rename a table or column, which you can do like this
ALTER TABLE employees
RENAME TO contractors;
ALTER TABLE contractors
RENAME COLUMN salary TO invoice;
2. ADD or DROP a column, which you can do like this:
ALTER TABLE contractors
ADD COLUMN job_title TEXT;
ALTER TABLE contractors
DROP COLUMN is_manager;
Intro to Migrations
A database migration is a set of changes to a relational database. In fact, the ALTER TABLE statements we did in the last exercise were examples of migrations.
Migrations are helpful when transitioning from one state to another, fixing mistakes, or adapting a database to changes.
Good migrations are small, incremental and ideally reversible changes to a database. As you can imagine, when working with large databases, making changes can be scary. We have to be careful when writing database migrations so that we don't break any systems that depend on the old database schema.
Example of a bad migration
If a backend server periodically runs a query like SELECT * FROM people, and we execute a database migration that alters the table name from people to users without updating the code, the application will break. It will try to grab data from a table that no longer exists.
A simple solution to this problem would be to deploy new code that uses a new query:
SELECT * FROM users;
And we would deploy that code to production immediately following the migration.
SQL Data Types
SQL as a language can support many different data types. But the datatypes that your database management system (DBMS) supports will vary depending on the specific database you're using.
SQLite only supports the most basic types, and we're using SQLite in this course.
SQLite Data Types
Let's go over the data types supported by SQLite: and how they are stored.
NULL- Null value.INTEGER- A signed integer stored in 0,1,2,3,4,6, or 8 bytes.REAL- Floating point value stored as an 64-bit IEEE floating point number.TEXT- Text string stored using database encoding such as UTF-8BLOB- Short for Binary large object and typically used for images, audio or other multimedia.
Example
CREATE TABLE employees (
id INTEGER,
name TEXT,
age INTEGER,
is_manager BOOLEAN,
salary INTEGER
);
Boolean values
It's important to note that SQLite does not have a separate BOOLEAN storage class. Instead, boolean values are stored as integers:
0=false1=true
It's not actually all that weird β boolean values are just binary bits after all!
SQLite will still let you write your queries using boolean expressions and true/false keywords, but it will convert the booleans to integers under-the-hood.