Basic MySQL Queries for Beginners

All qeueries being used in this article are compiled into one BookLibrary.sql file.

This article shows you basic MySQL queries to create a simple database and table, and to insert and select rows. The goal of this article is to let you learn…

• How to create a basic database structure which contains one to many and many to many relationships.
• How to SELECT data in a way that you want to see it.

We are going to make a book library database as shown below.

Although the author has checked briefly the facts to give readers a sense of traditional database, the data of books shown below is nothing accurate. These books are from the collection of the author, however, there are too many editions of eash book, and the author made no effort to clearify which edition of book he refers to. For example, pubslished date was simply obtained mainly from amazon.com and for some book the author use the published data of the different edition from his collection. Please do understand that inaccuracy of data is nothing intended to deceive readers or despise anyone who involved in the publication of the books.

Prerequiste

Please have MySQL installed on your computer and be ready to run SQL queries. The author, who is a Mac user, uses MAMP when he run queries. For Windows users, there is WAMP as well. You can also work from CLI.

If you are using MAMP or WAMP, please start its server and access to phpMyAdmin at http://localhost/phpmyadmin, assuming that your server is run at port:80. If your server is run at a different port, please add the proper port number after the host name.

That is all you need to prepare. Now let the querying begin.

CREATE

In this section shows you how to create a new database and tables in MySQL. It also shows you how to add new columns to an existing table by using ALTER.

If you work from phpMyadmin, please from now on choose `library` database on the left sidebar and run the following query commands from “SQL” tab.. For those of you who work from MySQL CLI, please switch database by running USE library;.

Create `library` database

CREATE DATABASE `library`;

Create `books` table

CREATE TABLE `books` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `price` decimal(5,2),
  `publish_date` date,
  PRIMARY KEY (`id`)
);

Create `authors` table

CREATE TABLE `authors` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Create `publishers` table

CREATE TABLE `publishers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Create `genres` table

CREATE TABLE `genres` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Create `genre_book` table

CREATE TABLE `genre_book` (
  `genre_id` int,
  `book_id` int,
  INDEX `idx_fk_genre` (`genre_id` ASC),
  INDEX `idx_fk_book` (`book_id` ASC),
  CONSTRAINT `fk_genre`
    FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`),
  CONSTRAINT `fk_book`
    FOREIGN KEY (`book_id`) REFERENCES `books` (`id`)
);

Alter `books` table

ALTER TABLE `books`
ADD `author_id` int,
ADD `publisher_id` int,
ADD INDEX `idx_fk_author` (`author_id` ASC),
ADD INDEX `idx_fk_publisher` (`publisher_id` ASC),
ADD CONSTRAINT `fk_author`
  FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`),
ADD CONSTRAINT `fk_publisher`
  FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`id`);

INSERT (and UPDATE)

After creating tables that we need, we are going to start inserting data into those tables with INSERT. You might expect to insert books first, but since authors and publishers will be refered as foreign keys by books later, we insert those data first. we are using UPDATE as well.

Insert one author

INSERT INTO `authors` (`name`) VALUES ('Alice Schroeder');

Insert multiple authors at once

INSERT INTO `authors` (`name`)
VALUES
  ('Walter Isaacson'), ('Malcolm Gladwell'),
  ('Peter F. Drucker'), ('Jane Austen'),
  ('Plato'), ('Benjamin Graham'),
  ('Al Gore'), ('Inazo Nitobe'),
  ('Elizabeth Gilbert'), ('Shunryu Suzuki'),
  ('Ed Catmull'), ('Lauren Weisberger'),
  ('Susan Cain'), ('Stephen R. Covey'),
  ('Paul Rand'), ('Thomas Piketty'),
  ('Clayton M. Christensen');

Insert all publishers at once

INSERT INTO `publishers` (`name`)
VALUES
  ('Simon & Schuster, Inc.'),
  ('Penguin Books'),
  ('Harper Collins Publisher'),
  ('Oxford University Press'),
  ('Little, Brown and Company'),
  ('Hachette Books Group, Inc.'),
  ('Harvard University Press'),
  ('Chronicle Books LLC.'),
  ('Random House, Inc.'),
  ('Penguin Books Ltd.');

Insert books, leaving `author_id` and `publisher_id` empty

INSERT INTO `books` (`title`, `price`, `publish_date`)
VALUES
  ('David and Goliath', 29.00, '2013-10-01'),
  ('Steve Jobs', 35.00, '2011-10-24'),
  ('Benjamin Franklin', 30.00, '2003-07-01'),
  ('Pride and Prejudice', 7.95, '2002-12-31'),
  ('Republic', 9.95, '2008-00-00'),
  ('The Devil Wears Prada', 21.95, '2004-04-13'),
  ('The Future', 30.00, '2013-10-08'),
  ('Queit', 17.00, '2013-01-29'),
  ('The Seven Habit of Highly Effective People', 16.99, '2013-11-19'),
  ('Capital in the Twenty-First Century', 39.95, '2014-00-00');

Update `author_id` of book one by one

UPDATE `books`
SET `author_id` = (SELECT `id` FROM `authors` `auth` WHERE `auth`.`name` = 'Malcolm Gladwell')
WHERE `title` = 'David and Goliath';

UPDATE `books`
SET `author_id` = (SELECT `id` FROM `authors` WHERE `name` = 'Walter Isaacson')
WHERE `title` = 'Steve Jobs';

UPDATE `books`
SET `author_id` = (SELECT `id` FROM `authors` WHERE `name` = 'Walter Isaacson')
WHERE `title` = 'Benjamin Franklin';

UPDATE `books`
SET `author_id` = (SELECT `id` FROM `authors` WHERE `name` = 'Jane Austen')
WHERE `title` = 'Pride and Prejudice';

UPDATE `books`
SET `author_id` = (SELECT `id` FROM `authors` WHERE `name` = 'Plato')
WHERE `title` = 'Republic';

In this arctile, we use `title` or `name` to obtain the `id` of specific row. This is to avoid mixing up `id`s when the reader inserts data other than those shown here. In practical application like using PHP, usually `id` is already given. Thus, you set given `id` directory.

Update `publisher_id` for multiple books at once

UPDATE `books`
SET `publisher_id` = 
  (CASE
    WHEN `title` = 'David and Goliath' THEN (SELECT `id` FROM `publishers` WHERE `name` = 'Little, Brown and Company')
    WHEN `title` = 'Steve Jobs' THEN (SELECT `id` FROM `publishers` WHERE `name` = 'Simon & Schuster, Inc.')
    WHEN `title` = 'Benjamin Franklin' THEN (SELECT `id` FROM `publishers` WHERE `name` = 'Simon & Schuster, Inc.')
    WHEN `title` = 'Pride and Prejudice' THEN (SELECT `id` FROM `publishers` WHERE `name` = 'Oxford University Press')
    WHEN `title` = 'Republic' THEN (SELECT `id` FROM `publishers` WHERE `name` = 'Oxford University Press')
  END)
WHERE `title` IN ('David and Goliath', 'Steve Jobs', 'Benjamin Franklin', 'Pride and Prejudice', 'Republic');

Update `author_id` and `publisher_id` for one book

UPDATE `books`
SET
  `author_id` = (SELECT `id` FROM `authors` WHERE `name` = 'Lauren Weisberger'),
  `publisher_id` = (SELECT `id` FROM `publishers` WHERE `name` = 'Random House, Inc.')
WHERE `title` = 'The Devil Wears Prada';

Update `author_id` and `publisher_id` for multiple books at once

UPDATE `books`
SET
  `author_id` = (CASE
    WHEN `title` = 'The Future' THEN
      (SELECT `id` FROM `authors` WHERE `name` = 'Al Gore')
    WHEN `title` = 'Queit' THEN
      (SELECT `id` FROM `authors` WHERE `name` = 'Susan Cain')
    WHEN `title` = 'The Seven Habit of Highly Effective People' THEN
      (SELECT `id` FROM `authors` WHERE `name` = 'Stephen R. Covey')
    WHEN `title` = 'Capital in the Twenty-First Century' THEN
      (SELECT `id` FROM `authors` WHERE `name` = 'Thomas Piketty')
  END),
  `publisher_id` = (CASE
    WHEN `title` = 'The Future' THEN
      (SELECT `id` FROM `publishers` WHERE `name` = 'Random House, Inc.')
    WHEN `title` = 'Queit' THEN
      (SELECT `id` FROM `publishers` WHERE `name` = 'Hachette Books Group, Inc.')
    WHEN `title` = 'The Seven Habit of Highly Effective People' THEN
      (SELECT `id` FROM `publishers` WHERE `name` = 'Simon & Schuster, Inc.')
    WHEN `title` = 'Capital in the Twenty-First Century' THEN
      (SELECT `id` FROM `publishers` WHERE `name` = 'Harvard University Press')
  END)
WHERE `title` in ('The Future', 'Queit', 'The Seven Habit of Highly Effective People', 'Capital in the Twenty-First Century');

Insert one book with `author_id` and `publisher_id`

INSERT INTO `books`
  (`title`, `price`, `publish_date`, `author_id`, `publisher_id`)
VALUES (
  'Management', 29.99, '2008-04-22',
  (SELECT id FROM `authors` WHERE `name` = 'Peter F. Drucker'),
  (SELECT id FROM `publishers` WHERE `name` = 'Harper Collins Publisher')
);

Insert multiple books with `author_id` and `publisher_id`

INSERT INTO `books`
  (`title`, `price`, `publish_date`, `author_id`, `publisher_id`)
VALUES (
  'The Innovator’s Dilemma', 17.99, '2011-10-04',
  (SELECT id FROM `authors` WHERE `name` = 'Clayton M. Christensen'),
  (SELECT id FROM `publishers` WHERE `name` = 'Harvard University Press')
), (
  'Creativity, Inc.', 20.00, '2014-04-08',
  (SELECT id FROM `authors` WHERE `name` = 'Ed Catmull'),
  (SELECT id FROM `publishers` WHERE `name` = 'Random House, Inc.')
), (
  'Thoughts on Design', 19.95, '2014-08-19',
  (SELECT id FROM `authors` WHERE `name` = 'Paul Rand'),
  (SELECT id FROM `publishers` WHERE `name` = 'Chronicle Books LLC.')
), (
  'The Snowball', 40.00, '2009-10-27',
  (SELECT id FROM `authors` WHERE `name` = 'Alice Schroeder'),
  (SELECT id FROM `publishers` WHERE `name` = 'Random House, Inc.')
), (
  'The Intelligent Investor', 22.99, '2006-02-21',
  (SELECT id FROM `authors` WHERE `name` = 'Benjamin Graham'),
  (SELECT id FROM `publishers` WHERE `name` = 'Harper Collins Publisher')
), (
  'Eat, Pray, Love', 15.00, '2007-01-30',
  (SELECT id FROM `authors` WHERE `name` = 'Elizabeth Gilbert'),
  (SELECT id FROM `publishers` WHERE `name` = 'Penguin Books Ltd.')
), (
  'Zen Mind', 18.95, '',
  (SELECT id FROM `authors` WHERE `name` = 'Shunryu Suzuki'),
  null
), (
  'Bushido', 19.00, '',
  (SELECT id FROM `authors` WHERE `name` = 'Inazo Nitobe'),
  null
), (
  'Outliers', 8.00, '2011-06-07',
  (SELECT id FROM `authors` WHERE `name` = 'Malcolm Gladwell'),
  (SELECT id FROM `publishers` WHERE `name` = 'Hachette Books Group, Inc.')
);

Insert genres

INSERT INTO `genres` (`name`)
VALUES
  ('Biographies & Memoirs'), ('Business & Money'),
  ('Health, Fitness & Dieting'), ('Literature & Fiction'),
  ('Romance');

Insert one genre-book relationship

INSERT INTO `genre_book` (`genre_id`, `book_id`)
VALUES (
  (SELECT id FROM `genres` WHERE `name` = 'Business & Money'),
  (SELECT id FROM `books` WHERE `title` = 'David and Goliath')
);

Insert multiple genre-book relationships

INSERT INTO `genre_book` (`genre_id`, `book_id`)
VALUES (
  (SELECT id FROM `genres` WHERE `name` = 'Biographies & Memoirs'),
  (SELECT id FROM `books` WHERE `title` = 'Steve Jobs')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Biographies & Memoirs'),
  (SELECT id FROM `books` WHERE `title` = 'Benjamin Franklin')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Literature & Fiction'),
  (SELECT id FROM `books` WHERE `title` = 'Pride and Prejudice')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Romance'),
  (SELECT id FROM `books` WHERE `title` = 'Pride and Prejudice')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Literature & Fiction'),
  (SELECT id FROM `books` WHERE `title` = 'The Devil Wears Prada')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Business & Money'),
  (SELECT id FROM `books` WHERE `title` = 'The Future')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Health, Fitness & Dieting'),
  (SELECT id FROM `books` WHERE `title` = 'Queit')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Business & Money'),
  (SELECT id FROM `books` WHERE `title` = 'Capital in the Twenty-First Century')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Business & Money'),
  (SELECT id FROM `books` WHERE `title` = 'The Innovator’s Dilemma')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Business & Money'),
  (SELECT id FROM `books` WHERE `title` = 'Creativity, Inc.')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Biographies & Memoirs'),
  (SELECT id FROM `books` WHERE `title` = 'The Snowball')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Business & Money'),
  (SELECT id FROM `books` WHERE `title` = 'The Snowball')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Business & Money'),
  (SELECT id FROM `books` WHERE `title` = 'The Intelligent Investor')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Biographies & Memoirs'),
  (SELECT id FROM `books` WHERE `title` = 'Eat, Pray, Love')
), (
  (SELECT id FROM `genres` WHERE `name` = 'Romance'),
  (SELECT id FROM `books` WHERE `title` = 'Eat, Pray, Love')
);

SELECT

In the queries below, the backticks (``) are eliminated. Also, although AS is added to queries so that the reader can understand which terms are aliases, you can eliminate them if you want to.

Show all books

SELECT * FROM books;

Show first 10 books

SELECT * FROM books LIMIT 10;

Show 10 books offsetting first 5 books

SELECT * FROM books LIMIT 10,5;
-- or
SELECT * FROM books LIMIT 10 OFFSET 5;

Show all books with author’s and publisher’s name, ordered by book title

SELECT books.id, books.title, authors.name AS author, publishers.name AS publisher FROM books
JOIN authors ON books.author_id = authors.id
JOIN publishers ON books.publisher_id = publishers.id
Order BY books.title ASC;

Show author and book title, ordered by author’s name

SELECT authors.name, books.title FROM authors
JOIN books ON authors.id = books.author_id
ORDER BY authors.name ASC;

Show publisher and book title, ordered by publisher’s name

SELECT publishers.name, books.title FROM publishers
JOIN books ON publishers.id = books.publisher_id
ORDER BY publishers.name ASC;

Show books, price of which are higher than or equal 30, ordered by price descending

SELECT books.title, authors.name AS author, books.price FROM books
JOIN authors ON books.author_id = authors.id
WHERE books.price >= 30
ORDER BY books.price DESC;

Show authors and number of books they published, ordered by its number descending

SELECT authors.id, authors.name, COUNT(books.author_id) AS book_published FROM authors
JOIN books on authors.id = books.author_id
GROUP BY author_id
ORDER BY COUNT(author_id) DESC;

Authors who have more than 1 book published.

SELECT authors.id, authors.name, COUNT(books.author_id) AS book_published FROM authors
JOIN books on authors.id = books.author_id
GROUP BY author_id
HAVING COUNT(author_id) > 1
ORDER BY authors.name;

Publishers ordered by number of books published and its name

SELECT publishers.id, publishers.name, count(books.publisher_id) AS book_published FROM publishers
JOIN books on publishers.id = books.publisher_id
GROUP BY publisher_id
ORDER BY COUNT(publisher_id) DESC, name;

Books and its genre

SELECT books.id, books.title, genres.name AS genre FROM books
JOIN genre_book ON books.id = genre_book.book_id
JOIN genres on genre_book.genre_id = genres.id
ORDER BY books.id;

Books not yet categorized in genre

SELECT books.id, books.title FROM books
WHERE books.id NOT IN (SELECT book_id FROM genre_book GROUP BY book_id);

Genres and its count ordered by count descending

SELECT genres.id, genres.name, COUNT(t.genre_id) AS count FROM genres
JOIN genre_book AS t on genres.id = t.genre_id
GROUP BY genre_id
ORDER BY COUNT(t.genre_id) DESC;

Books categorized as 'Business & Money' genre

SELECT books.id, books.title, genres.name AS genre FROM books
JOIN genre_book ON books.id = genre_book.book_id
JOIN genres on genre_book.genre_id = genres.id
WHERE genres.name = 'Business & Money'
ORDER BY books.id;

DELETE & DROP

Please be careful when you run the following queries.

Delete all rows from `books`

DELETE FROM books

Drop table

DROP TABLE books;

Drop database

DROP DATABASE library;

That’s it

And that is—I believe—all basic queries that you need to know in order to make a common database structure. Surely there are lots of queries and optinal commands that you would need to design complex data, or to optimize your database performance. Nevertheless, these are most frequentry used queries and you would run it time after time when you mark up your database.

Reference

• SQL Tutorial at w3schools.com
• MySQL official website