06 Oct 2019
Notes based on Head First SQL
- A database is a container that holds tables and other SQL structures related to those tables.
- In diagrams and flow charts, databases are depicted as cylinders
- A table is the structure inside you database that contains data, which is organised into columns and rows.
- Each category becomes a column in your table.
- A table row contains all the information about one object in your table.
- All the tables in a database should be connected in some way.
Create a new database
CREATE DATABASE unicorn sightings;
Use the database by running the command
USE unicorn_sightings which now means that everything we do will happen inside of the database.
- The SQL languade requires that all tables, even if you just have one to be in a database. A good reason for this is that it's easier to grant permissions to databases as a whole than individual tables.
- You don't have to use uppercase letters for the commands because it is case insensitive. However it is helpful to follow this convention so that your SQL is easier to distinguish between commands and names.
CREATE TABLE doughnut_list ( doughnut_name VARCHAR(10), doughnut_type VARCHAR(6) );
The code above creates a table called 'doughnut_list', and then adds two columns to that table, whilst specifying the type of data that will be contained within each of those columns. 'VARCHAR(10)' stands for 'VARiable CHARarter', and is used to hold textual information up to 10 characters long in the first column and 6 characters long in the second column.
Create a new table inside of a database
First, we'll create a new database and go inside it:
CREATE DATABASE gregs_list
Then we'll create a table with multiple columns that each hold their own data that has a datatype specified
CREATE TABLE my_contacts ( last_name VARCHAR(30), first_name VARCHAR(20), email VARCHAR(50), birthday DATE, profession VARCHAR(50), location VARCHAR(50), status VARCHAR(20), interests VARCHAR(100), seeking VARCHAR(100) );
To see how the data in the table looks, you can run the
DESC my_contacts to view it. DESC is short for DESCRIBE.
Some data types you can use
- CHAR or CHARACTER: are rigid and prefer their data to be a set length.
- INT or INTEGER: are for whole positive or negative numbers.
- BLOB: are for large gobs of text data.
- DATE: keeps track of dates but not times.
- TIME: keeps track of times but not dates.
- VARCHAR: holds text data up to 255 characters. This is a flexible datatype that can adapt to the length of your data up to the number of characters you specify.
- DATETIME or TIMESTAMP: keeps track of both the date and time.
- DEC or DECIMAL: gives you all of the decimal places you ask for until full.
However, there are no universally accepted names for various data types, so your SQL relational databese might use different names for one or more of these types, so it is worth becoming familiar with these distinctions if there are any.
Deleting database tables
If you want to add a new column to your database, you'll have to delete
DROP TABLE my_contacts and write your database again from scratch (that sounds pretty awful - not that great for the ETC (Easy To Change) principle).
The DROP table command is irreversible. You won't be able to get the data stored in the table back again once you have deleted it this way.
Adding data to your tables
INSERT INTO your_table (column_name1, column_name2,...) VALUES ('value1', 'value2', ...);
INSERT INTO my_contacts (last_name, first_name, email, gender, birthday, profession, location, status, interests, seeking) VALUES ('Anderson', 'Jillian', 'firstname.lastname@example.org', 'F', '1980-09-05', 'Technical Writer', 'Palo Alto, CA', 'Single', 'Kayaking, Reptiles', 'Relationship, Friends');
When adding data to databases, the values need to be in the same order as the column names. Your columns don't have to be in the same order as the columns in your table, as long as the values are in the same order as your columns.
You can leave out the list of column names and just add the values in the insert statement. However if you do this, then the values must be in the same order as the columns in your database.
You can leave some columns out, as long as the values are in the same order as the columns you leave in your insert statement. You can add in missing data later if you need to.