SQL Basics

SQL (Structured Query Language) is the standard language for querying and managing relational databases. It lets you read, insert, update, and delete data using structured commands that work across different database systems. SQL is used with databases such as MySQL, PostgreSQL, SQLite, and SQL Server.

Database Management Systems

• MySQL: run on servers separate from those running a website
• PostgreSQL: run on servers separate from those running a website
• SQLite: a lighter-weight system that can store all of its data in a single file

Column Types

• TEXT: For strings of text (Ex. a person’s name) NUMERIC: A more general form of numeric data (Ex. A date or boolean value)
• INTEGER: Any non-decimal number (Ex. a person’s age)
• REAL: Any real number (Ex. a person’s weight)
• BLOB (Binary Large Object): Any other binary data that we may want to store in our database (Ex. an image)

Constraints

• CHECK: Makes sure certain constraints are met before allowing a row to be added/modified
• DEFAULT: Provides a default value if no value is given
• NOT NULL: Makes sure a value is provided
• PRIMARY KEY: Indicates this is the pimary way of searching for a row in the database
• UNIQUE: Ensures that no two rows have the same value in that column.