Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 75 ยท Getting Started with MySQL

Your First Database and Table

Create your first MySQL database and table, insert and select rows, understand utf8mb4 and collation defaults, and see how MySQL differs from SQLite.

You have a server and a client. Now you will create something in it: a database to hold your tables, a table to hold rows, and a few rows to read back. This is also the first lesson where MySQL's differences from SQLite become visible, and we will point each one out so you always know what the live examples prove and what they do not.

The table examples run live, in SQLite's dialect, which accepts most of this portable SQL. The CREATE DATABASE statement and MySQL-specific column syntax are shown read-only.

What a database is in MySQL

In MySQL, a database is a named container for tables, views and other objects. The word schema is a synonym: CREATE DATABASE and CREATE SCHEMA do exactly the same thing. (In PostgreSQL they are different concepts, which sometimes confuses people moving between the two.) One server can host many databases, and an account can have different privileges on each.

SQL
CREATE DATABASE bookshop;
USE bookshop;

That is the whole statement. In practice you should always state the character set, and IF NOT EXISTS makes scripts safe to re-run:

SQL
CREATE DATABASE IF NOT EXISTS bookshop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

You can see what exists with SHOW DATABASES;, and remove one with DROP DATABASE bookshop;. The drop deletes every table inside it immediately and cannot be undone, so be careful which server your prompt points at.

SQLite has no CREATE DATABASE. A database there is simply a file, and our live examples start with a fresh empty one every run.

Character sets and collations

Every text column has a character set (which characters can be stored and how many bytes each takes) and a collation (how they are compared and sorted). In MySQL 8 the defaults are:

  • Character set utf8mb4: real UTF-8 with up to four bytes per character, so emoji and every language fit.
  • Collation utf8mb4_0900_ai_ci: based on Unicode 9.0 rules, accent-insensitive and case-insensitive.

Case-insensitive means 'Ada' = 'ada' is true, and accent-insensitive means 'e' = 'รฉ' is true. This surprises people coming from SQLite, whose default comparison is exact. Both settings can be changed per database, table or column. Character Sets and Collations goes deeper, so for now remember the practical rule: always use utf8mb4, and never the old utf8 alias, which stores at most three bytes per character.

Three levels of container

Server One mysqld process.
Database A named group of tables.
Table Rows and columns of data.

In MySQL, database and schema mean the same thing.

1 / 2

Creating a table

A table needs a name and at least one column. Each column has a name and a type, and can have options such as NOT NULL. Here is a table in MySQL's own style, read-only because of AUTO_INCREMENT and the table options:

SQL
CREATE TABLE books (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title      VARCHAR(200) NOT NULL,
  author     VARCHAR(100) NOT NULL,
  price      DECIMAL(8,2) NOT NULL,
  published  DATE NULL,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

Reading it piece by piece:

  • INT UNSIGNED is a 4-byte integer with no negative values. AUTO_INCREMENT makes MySQL assign 1, 2, 3 and so on when you do not supply an id.
  • VARCHAR(200) is variable-length text with a character limit of 200.
  • DECIMAL(8,2) is an exact number with 8 digits in total, 2 after the decimal point, the right choice for money.
  • NULL means "may be missing", and NOT NULL means a value is required.
  • PRIMARY KEY (id) makes id the unique identity of each row.
  • ENGINE = InnoDB names the storage engine. It is already the default, but spelling it out documents the intent.

The portable version, which runs in this page's SQLite engine, differs in the syntax of the id and the types:

SQL Create a table and insert rows

Press Run (or Ctrl + Enter) to see the result here.

You see three rows with ids 1, 2 and 3, assigned automatically, exactly as AUTO_INCREMENT would do on MySQL. Notice that we listed the columns in the INSERT. That is good practice in both systems: the statement keeps working if someone later adds a column.

Inserting and selecting

INSERT adds rows, and SELECT reads them. MySQL accepts the multi-row form used above. It also has a lesser-known INSERT ... SET form, which is read-only here because SQLite does not support it:

SQL
INSERT INTO books SET title = 'Cold Storage', author = 'Noor Haddad', price = 22.00;

After an insert you can ask which id the server generated:

SQL
SELECT LAST_INSERT_ID();

This returns the value for your connection only, so concurrent clients never see each other's ids. SQLite's equivalent is last_insert_rowid(). Run a few queries against the table in the live editor:

SQL Select, filter and sort

Press Run (or Ctrl + Enter) to see the result here.

The editor shows the result of the last statement, one row for Tiny Servers. If you move the grouped query to the end, it returns Mara Jensen with an average of 27.75, and Noor Haddad with 18.75. Both queries run on MySQL too, except that you would write LAST_INSERT_ID() there.

Differences to keep in mind

The simple examples hide real differences. These are the ones you will meet first:

Topic MySQL SQLite
Types Strict: a DECIMAL(8,2) column stores a decimal, VARCHAR(200) enforces its limit Flexible: any value can go in any column, the declared type is only a hint
Text comparison Case- and accent-insensitive by default Exact by default
Auto ids AUTO_INCREMENT INTEGER PRIMARY KEY (optionally AUTOINCREMENT)
Quoting names Backticks `name` (double quotes only in ANSI_QUOTES mode) Double quotes, backticks and brackets all work
Strings Single or double quotes Single quotes (double quotes are for identifiers)
Storage Server, engine per table One file

The type difference deserves one demonstration, because it is where SQLite stand-ins can mislead you. In MySQL, putting text into a numeric column fails, or at least warns. SQLite stores it as-is:

SQL SQLite is lenient: MySQL would complain

Press Run (or Ctrl + Enter) to see the result here.

SQLite happily keeps not a number in the integer column, and typeof reports text. A MySQL server in its default strict mode rejects that insert with an error such as Incorrect integer value. The next-but-one lesson, SQL Modes and Strictness, explains why.

Be careful about trusting a live SQLite result as proof of MySQL behavior. When this tutorial marks a block as a stand-in, the logic carries over, but details such as type checks, collations and error messages come from the real server, so test them there.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the relationship between a database and a schema in MySQL?

    In MySQL the two words name the same object. PostgreSQL uses them differently, which is a common source of confusion.
  2. 2. Which character set should you choose for new MySQL text columns?

    utf8mb4 is real four-byte UTF-8 and the default in MySQL 8. The utf8 alias cannot store all characters, such as many emoji.
  3. 3. What does AUTO_INCREMENT do on an integer primary key?

    When an insert does not supply a value for the column, MySQL generates the next integer. SQLite does the same for an INTEGER PRIMARY KEY.
  4. 4. Why does 'Ada' = 'ada' return true in a default MySQL 8 database?

    utf8mb4_0900_ai_ci compares without regard to case and accents (ai = accent-insensitive, ci = case-insensitive).

Summary

  • A MySQL database (schema) holds tables. Create it with CREATE DATABASE IF NOT EXISTS name CHARACTER SET utf8mb4;.
  • Columns have strict types such as INT UNSIGNED, VARCHAR(n) and DECIMAL(8,2), and AUTO_INCREMENT generates ids.
  • The multi-row INSERT ... VALUES and SELECT examples are portable and run live; LAST_INSERT_ID() is the MySQL name for SQLite's last_insert_rowid().
  • MySQL 8 defaults to utf8mb4 with a case- and accent-insensitive collation.
  • SQLite is far more lenient about types than MySQL, so confirm type and collation behavior on a real server.

Next: SQL Modes and Strictness.