Database Foundations

Creating Database

1CREATE DATABASE `shop` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

You can also use the keyword SCHEMA to create a database.

1CREATE SCHEMA `shop`;

Once a database is cerated you can start using it with the following command

1USE `shop`;

Get info about the database

1SHOW ENGINES

The default engine is InnoDB.

Creating Tables

1CREATE TABLE actors (name VARCHAR(50));

CREATE is the command telling the database what to do. TABLE is telling the database what to create. actors is the name of that table. name is the column the table will have. The type of data it’ll have is VARCHAR. (50) means we can store up to 50 characters.

1CREATE TABLE movies (title VARCHAR(200), year INTEGER);

Same as above, but this time create two columns, one is called title and contains VARCHAR data up to 200 characters long and the other is year which contains INTEGERs (aka numbers).

Here is another example:

1CREATE TABLE IF NOT EXISTS `products` (
2`sku` int(11) DEFAULT NULL,
3`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
4`img` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
5`price` decimal(10,2) DEFAULT NULL,
6`paypal` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL
7) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

NULL and NOT NULL determine the default for values. By default it is NULL. But if you want to force the user to always enter a value, you can set it to NOT NULL so it must always be entered by the user. If you want a required field, set it’s value to NOT NULL in the database.

Temporary Tables

A temporary table is visible only to the current connection, and is dropped automatically when the connection is closed.

CRUD (Create, Read, Update, Delete)

Each of these operations has a set of keywords associated with them.

Inserting Data

1INSERT INTO movies VALUES("Avatar", 2009);

INSERT INTO tells what table to insert into, movies is the table’s name and VALUES tells the list of values that we want to add to the table.

Avatar has " " around it because it is a string, 2009 has no quotes because it’s an integer.

The order of the values is important. It should be the same order as you have defined your columns. If you don’t know/remember the ordering of the columns in the table, you can specify them in the command, like so:

1INSERT INTO movies (title, year) VALUES("Avatar", 2009);

Even in this case, the order of the columns specified should match the order of the values. If, in the command, you have mentioned the title column first, then the values should have the title value first.

You can add values to multiple rows separated by a comma in the same command. Like this:

1INSERT INTO movies (title, year) VALUES("Avatar", 2009), ("The Godfather", NULL);

If you don’t know or don’t want to enter a value, use NULL.

You can also insert data using the SET keyword

1INSERT INTO movies SET title="Back to the Future", year = 1985;

Here is another example:

1INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(109, 'Get Coding Shirt', 'Gray', 'img/shirt-coding.png', 25.00, '7BNDYJX65WD364')

The basic format of the command is

1INSERT INTO `products` (column1, column2) VALUES(value1, value2)

Reading and Retrieving data

SELECT is the keyword for reading data

1SELECT * FROM movies;

SELECT is for reading data, * means all columns, FROM means from, and the table is movies. The above statement says ‘read data from all columns in the movies table’.

You can also select which columns to show like this:

1SELECT movies.title, movies.year FROM movies;

The order of the columns will be determined by the order in your query. Meaning, you can switch columns around.

1SELECT movies.year, movies.title FROM movies;

You can drop the table names from columns and it’ll still work, like so:

1SELECT title, year FROM movies;

Querying Tables and Getting selective results

WHERE is the clause that limits the result set because you are constraining it to rows that meet a specified criteria. Using the WHERE clause you and add text or a condition to filter the results to only bring back rows fulfilling that condition.

1SELECT * FROM movies WHERE year = 1999;

SELECT all (*) rows FROM the movies table WHERE (the data in) the year (column) was 1999.

1SELECT * FROM movies WHERE year != 1999;

will select all movies where the years was not equal to 1999.

Combining Multiple Conditions

1SELECT * FROM movies WHERE year < 1999 AND title = "The Matrix";

SELECT all (*) rows FROM the movies table WHERE the year column value is greater than (<) 1999 AND the title column value is The Matrix. Since the title data type is string, we’ve wrapped what we are looking for in " ".

In other words, select all movies released after 1999 AND with the title The Matrix.

1SELECT * FROM movies WHERE year = 1998 OR year = 2000;

Show all movies released in 1998 OR 2000.

1SELECT * FROM movies WHERE year BETWEEN 1988 AND 2000;

Show all movies released BETWEEN 1988 and 2000.

Search on a string based on part of a string

For example, look for all movies that have the word Godfather in the title.

1SHOW * FROM movies WHERE title LIKE "godfather"

LIKE is case-insensitive.

Wildcards are used to determine if something starts, contains or ends with a particular string. If a search string doesn’t yield anything, wrap the string in wildcards and try again.

1SHOW * FROM movies WHERE title LIKE "%godfather%"

You can also look for something that does not contain a particular string

1select * FROM runs WHERE info is null or info NOT LIKE '%wrong%';

Ordering results ORDER BY

1SHOW * FROM movies ORDER BY year;

will show all movies in ascending order of the year column. If you want you can explicitly specify ASC for the order. But it is ascending by default (Low to High).

1SHOW * FROM movies ORDER BY year DESC;

will show all movies in descending order of the year.

You can chain ordering together by adding a comma , after each column and their ordering keyword.

1SHOW * FROM movies ORDER BY year ASC, title DESC;

will show the movies by ascending order of the year and in every year the title would be in descending (reverse) order.

The ordering of the chain matters. Whatever you want sorted first needs to be included first.

Limiting the Result Set

1SELECT * FROM movies LIMIT 10 OFFSET 20;

is the same as

1SELECT * FROM movies LIMIT 20,10;

Keep in mind that the OFFSET of the initial row is 0, not 1.

There is a default LIMIT of 1000 in some software (like MySQL Bench). Be aware of it when you are working with results in excess of 1000+. If you are working with a programming language (like PHP) this default limit won’t be there.

Dealing with NULL

1SELECT * FROM movies WHERE year IS NULL;

Select all movies with the “year” column that hasn’t been populated yet. In other words, show all movies with no year given.

Null affects your sort order as it’ll always come at top.

1SELECT * FROM movies WHERE year IS NOT NULL ORDER BY year;

will exclude the empty results. Select all movies that have data in the “year” column.

Updating Data

1UPDATE movies SET year=2015;

The command above will change the year to 2015 for all rows in the year column. To specify which row to update the year for, specify title as well. Make the command target more specific:

1UPDATE movies SET year=2015 WHERE title="Avatar 2";

Update existing values

1UPDATE movies SET year=2016, title="Avatar Reloaded" WHERE title="Avatar 2";

will update the existing Avatar 2 row to Avatar Reloaded and change the year to 2016.

Delete Data

1DELETE FROM movies;

will delete everything in the movies table. Not a good idea to do unless that’s what you really want. You can use WHERE to narrow down your target.

1DELTE FROM moviews WHERE title = "Avatar Reloaded";

to be extra safe, mention as many values for the row as you know.

1DELTE FROM moviews WHERE title = "Avatar Reloaded" AND year=2016;

NOTES

Follow convention

1update movies set year=2015;

is the same as

1UPDATE movies SET year=2015;

both will get the job done.

You don’t have to uppercase all SQL keywords, but you should because it is convention to do so. Also, it make your code more readable.

Turn safe updates off

1SET SQL_SAFE_UPDATES = 0;

To turn them back on, change the 0 to 1. Safe mode lets you UPDATE and DELETE a little more safely.

Resources

• Treehouse - Database Foundations
• MySQL Docs: SELECT Syntax