Practical 2: Viewing Databases, Creating One and Listing Its Tables

Chapter Thirty-Two

Syllabus topic Module 2, Practical 2: "Viewing all databases", "Creating a Database", "Viewing all Tables in a Database"

Pages 115 to 117 of 206

Aim

To view all the databases on the server, to create a database, and to view all the tables in it.

Viewing all databases

SHOW DATABASES;

That lists every database the user you logged in as is allowed to see. On a college server the list is long; on your own machine it is short. Four of them are there on every MySQL installation and are not yours to touch:

information_schema holds a description of every database, table and column on the server.

mysql holds the server's own accounts and privileges.

performance_schema holds measurements of what the server is doing.

sys holds easier-to-read views over performance_schema.

information_schema is worth remembering rather than avoiding: it is how a program finds out what tables exist, and it is used at the end of this chapter.

Because the full list differs from machine to machine, narrow it when you are looking for something in particular:

SHOW DATABASES LIKE 'library%';

Nothing came back, so nothing beginning with library exists here yet. LIKE uses % to mean "any characters", which is the same pattern language as the WHERE ... LIKE of [Practical 4: Simple Queries].

Creating a database

CREATE DATABASE librarydb;

Nothing is printed, which means it worked.

Run it a second time and the server refuses, because the name is taken:

CREATE DATABASE librarydb;

ERROR 1007 (HY000): Can't create database 'librarydb'; database exists

That error has a number, 1007, and a five character SQLSTATE, HY000. Both are worth noticing: a program that talks to MySQL tests the number, not the English, because the English changes between versions and languages.

When you do not care whether it already exists, say so:

CREATE DATABASE IF NOT EXISTS librarydb;

That prints nothing and does nothing, because the database is already there. IF NOT EXISTS turns the error into a warning, which is what you want in a script that may be run twice.

What the server actually created

You asked for a name and nothing else, and the server filled in two more things.

SELECT default_character_set_name AS charset,
       default_collation_name     AS collation
FROM information_schema.schemata
WHERE schema_name = 'librarydb';

+---------+--------------------+
| charset | collation          |
+---------+--------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
+---------+--------------------+

A character set decides what characters may be stored. A collation decides how they sort and compare.

utf8mb4 is the character set that can hold every character in Unicode, Devanagari and emoji included, in up to four bytes each. It is the sensible default and it is what modern MySQL chooses. The older utf8 in MySQL is a three byte version that cannot hold all of Unicode, which is why the name with mb4 exists at all.

The ai_ci on the end of the collation name means accent insensitive, case insensitive, so Mumbai and mumbai compare equal. That is why WHERE title = 'learn sql' finds Learn SQL later in this book, and it surprises students who expect SQL to behave like C.

Practical 2: Viewing Databases, Creating One and Listing Its Tables

SHOW CREATE DATABASE librarydb; prints the same two facts as one long line, in the form of the statement that would recreate the database. It is the quicker thing to type and the harder thing to read.

To choose them yourself rather than take what you are given:

CREATE DATABASE librarydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Selecting it, and confirming which one you are in

USE librarydb;
SELECT DATABASE() AS now_using;

+-----------+
| now_using |
+-----------+
| librarydb |
+-----------+

USE is one of the few statements the interactive client accepts without a semicolon. Write the semicolon anyway, so that the same line also works inside a script. SELECT DATABASE() is the question "where am I", and it is the first thing to type when a statement says a table does not exist.

Viewing all the tables in it

SHOW TABLES;

Nothing, because the database is new. Once tables exist, the same statement lists them, and [Practical 2: Creating a Table, and Choosing Its Data Types] makes some.

You can look inside another database without leaving this one:

SHOW TABLES FROM othername;

And you can narrow the list exactly as with databases:

SHOW TABLES LIKE 'b%';

The same question asked of information_schema

Everything SHOW tells you is also stored as ordinary rows in information_schema, and it can be queried like any table.

SELECT schema_name AS found
FROM information_schema.schemata
WHERE schema_name = 'librarydb';

+-----------+
| found     |
+-----------+
| librarydb |
+-----------+

That matters for two reasons. It is how a program asks the question, because a program can put a WHERE on a query and cannot put one on SHOW. And it is a fair viva question: SHOW is a convenience for a person; information_schema is the same information as data.

Removing a database

DROP DATABASE librarydb;

There is no confirmation and no undo. Every table and every row in it is gone. On a shared college server, check twice which database you are naming, and never type it with a wildcard or from memory. DROP DATABASE IF EXISTS name; is the form that does not complain when there is nothing to drop.

The statements in this practical

Statement	What it does
`SHOW DATABASES;`	lists every database you may see
`SHOW DATABASES LIKE 'pattern';`	the same list, narrowed
`CREATE DATABASE name;`	makes one
the same with `IF NOT EXISTS`	makes one, and says nothing when it is already there
`SHOW CREATE DATABASE name;`	the statement that would recreate it, character set and all
`USE name;`	selects it for the rest of the session
`SELECT DATABASE();`	which one am I in
`SHOW TABLES;`	lists the tables in the current database
`SHOW TABLES FROM name;`	lists another database's tables
`DROP DATABASE name;`	deletes it and everything in it

Practical 2: Viewing Databases, Creating One and Listing Its Tables

What beginners get wrong

Expecting CREATE DATABASE to select the database. It does not; USE does.

Running SHOW TABLES with no database selected. The server answers ERROR 1046 (3D000): No database selected.

Being surprised that a second CREATE DATABASE fails. Use IF NOT EXISTS when you do not care.

Typing DROP DATABASE on a shared server without checking the name.

Assuming table names behave the same everywhere. On Linux they are case sensitive; on Windows they are not, so a query that works in the laboratory may fail at home.

Treating information_schema, mysql, performance_schema or sys as spare space. They belong to the server.

Quick revision

SHOW DATABASES; lists them; four system databases are always there.
CREATE DATABASE name; makes one, and IF NOT EXISTS stops the error on a second run.
SHOW CREATE DATABASE name; shows the character set and collation it was given.
utf8mb4 holds every Unicode character; the collation ending ai_ci compares without regard to accents or case.
USE name; selects; SELECT DATABASE(); asks which is selected.
SHOW TABLES; lists the current database's tables; SHOW TABLES FROM other; lists another's.
information_schema holds the same information as queryable rows.
DROP DATABASE has no undo.

What goes in your journal

Aim, every statement with the server's reply written underneath it exactly as it appeared, including the error from the second CREATE DATABASE. That error is the evidence that you tried it rather than copied it, and an examiner notices.

Test yourself

1. Which four databases exist on every MySQL server? information_schema, mysql, performance_schema and sys.

2. What does CREATE DATABASE print when it succeeds? Nothing. Silence after a statement that changes something means it worked.

3. What is the difference between CREATE DATABASE x; and CREATE DATABASE IF NOT EXISTS x;? The first is an error if x already exists. The second does nothing and carries on.

4. What is a collation? The set of rules for comparing and sorting the characters of a character set. A collation ending ai_ci ignores accents and case, so Mumbai and mumbai compare equal.

5. How do you list the tables of a database you are not currently in? SHOW TABLES FROM thatdatabase;

6. Why would a program query information_schema instead of using SHOW? Because information_schema holds the same information as ordinary rows, so a query can filter, join and sort it. SHOW cannot take a WHERE.

The rest of this subject

These notes are cut from the University's printed syllabus. Open the syllabus itself for the same subject.

Syllabus for Major Practical 1

Report or request