A Live Developer Journal

Exploring SQL

Notes based on Head First SQL

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.


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 USE 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

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', 'jill_anderson@breakneckpizza.com', '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.