A Refresher on SQL and Databases
What is SQL?
SQL stands for ‘structured query language’. It is language used by anyone interacting with a database to pull certain data. A good visualization of a database is a basic Excel sheet. It has columns, data, and unique identifiers for each row. And why is C3PO’s head the featured image? Because all of the stock photos for ‘database’ and ‘SQL’ are pretty lame.
How is it used?
A website, sales team, hospital, or other entity with a need to store lots of data, will have their own “database” full of information. A database or relational database organizes information into a series of tables with rows and columns. Interfacing tools like MySQL Workbench allow people to connect to a particular database, then use SQL to ‘query’ it for information.
What are basic commands?
In the example above, I used a simple SQL statement to ask the database for data. There are a handful of useful basic statements to interact with a database, the power is in mastering the basics.
- SELECT is the clause used every time you query information
- WHERE a command used to filter the results of a query based on certain specifications
- LIKE and BETWEEN are special operators that can be used with WHERE
- AND and OR are conditional statements that you can also use with WHERE
- ORDER BY lets you sort the results of a query by ASC (ascending) or DESC (descending) order
- LIMIT lets you specify a maximum number of rows that a query will return
- COUNT takes the name of a column(s) as an argument and counts the number of rows
- GROUP BY is a clause used to combine data from one or more columns
- SUM() takes a column name as an argument and returns the sum of all values in that column
- MAX() and MIN() takes a column name as an argument and returns the largest or smallest value
- AVG() takes a column name as an argument and returns the average values
- ROUND() takes two arguments, a column name and the number of decimal places to round the values
- Primary Key a column that serves as a unique identifier for a row – values cannot be NULL
- Foreign Key a column that contains the primary key to another table in a database – used to build relationships
- Joins used to combine data from multiple tables
- INNER JOIN will combine rows from different tables in the join condition is true
- LEFT JOIN will return every row in the left table, and if the join condition is not met, NULL values are used to fill in the right
- AS is a keyword that allows you to rename a column or table creating an alias
Creating and Altering Tables
- CREATE TABLE creates a new table
- INSERT INTO adds a new row to a table
- SELECT queries data from a table
- UPDATE edits a row in a table
- ALTER TABLE changes an existing table
- DELETE FROM deletes rows from a table
What SQL resources are out there?
There are tons of resources to learn SQL for free and in a relatively short amount of time. This post is meant to be more of a ‘cheat sheet’/personal reminder, than a learning device. If you want to learn or practice SQL, check out these awesome links.