Make a Database
First, let's make sure we're not creating a database that already exists — we'll do this by listing the names of existing databases (user entries are in green):
mysql> show databases;
Type the green text above and press Enter. If your MySQL installation is new, the list should be short:
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+
Now enter this:
mysql> create database tutorial;
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || tutorial |+--------------------+
So we've succeeded in creating a database to hold the content of this tutorial. Now let's try generating an error — what happens if we try to create a database that already exists? Type this:
mysql> create database tutorial;
ERROR 1007 (HY000): Can't create database 'tutorial'; database exists
I should tell you that's one of the more informative MySQL error messages — most messages require the user to figure out what went wrong.
Now that we have created our database, let's tell MySQL to use it for subsequent entries:
mysql> use tutorial;
NOTE: There's an alternative to telling MySQL which database to use — one simply prepends the database name to each table reference, like this: "database_name.table_name". When dealing with multiple databases, this is often more convenient.
Make a Table
In serious database work, one spends a fair amount of time in advance of table creation deciding what fields each table record should have, and what data types the fields should contain. But for this first effort, we'll limit ourselves to a simple table. In the entry below I have included a common error, to make the reader aware of an issue having to do with spaces. Watch what happens:
mysql> create table trivia (Name text, Age integer, Favorite Color text);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Color text)' at line 1
Apart from showing the first of the dreaded and uninformative MySQL error messages, what's wrong? Well, think about this — can MySQL figure out which parts of our entry are field names, and which are data types? The first field name is "Name", and it has the data type "text". The field name and data type are separated by a space. But the field name "Favorite Color" has a space within it, and MySQL can't decide whether "Color" is part of the name or a data type, and there's no data type called "Color". So — error message, and no table.
Here is the solution — enclose problem field names in backticks (`):
mysql> create table trivia (Name text, Age integer, `Favorite Color` text);
mysql> describe trivia;
+----------------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------+---------+------+-----+---------+-------+| Name | text | YES | | NULL | || Age | int(11) | YES | | NULL | || Favorite Color | text | YES | | NULL | |+----------------+---------+------+-----+---------+-------+
The result produced by "describe (tablename)" has these fields:
- "Field": field name
- "Type": the data type supported by this field
- "Null": whether this field may be NULL, or undefined.
- "Key": whether this field represents a table key (see below)
- "Default": what value this field should have by default
- "Extra": special properties appear here
Okay, on examining this description, it seems obvious that our table shouldn't allow blank fields, so let's try again, this time requiring specific values for each field:
mysql> drop table trivia;
mysql> create table trivia (Name text not null, Age integer not null, `Favorite Color` text not null);
mysql> describe trivia;
+----------------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------+---------+------+-----+---------+-------+| Name | text | NO | | NULL | || Age | int(11) | NO | | NULL | || Favorite Color | text | NO | | NULL | |+----------------+---------+------+-----+---------+-------+
NOTE: Do be careful with commands like "drop (tablename)". This table happens to be empty, but the same command works with a table having a million records — it just goes away with no warning.
Now we have a table ready to accept records describing people by name, age and favorite color. It's time to insert some real data. Again, I have deliberately introduced an error:
mysql> insert into trivia values(Bruce Callow,13,I haven't decided yet.);
The above example is truly pathological in its errors, and I won't try to even list the possible outcomes, depending on what the reader types to try to recover. But here's the correct entry:
mysql> insert into trivia values('Bruce Callow','13','I haven''t decided yet.');
mysql> select * from trivia;
+--------------+-----+------------------------+| Name | Age | Favorite Color |+--------------+-----+------------------------+| Bruce Callow | 13 | I haven't decided yet. |+--------------+-----+------------------------+
Let's take a closer look at the corrected values for this successful entry:
- The name "Bruce Callow" is a string, a sequence of characters, so it must be enclosed in quotes of one kind or another. Examples: 'Bruce Callow', "Bruce Callow", but not `Bruce Callow` (backticks are reserved for field, table and database names).
- The age "13" is a number, so it doesn't need to be quoted. But quoting a number is not an error, and in some cases, for consistency, all values might be enclosed in quotes.
- The string "I haven't decided yet" contains an apostrophe. Unfortunately the apostrophe is also sometimes used as a quotation mark, which will confuse MySQL about where the string ends. One solution is to use double quotation marks around this specific field: "I haven't decided yet". Another solution is to "escape" the apostrophe in one of these ways: 'haven''t', 'haven\'t' — either will work.
NOTE: To save time and typing, readers are encouraged to copy the green entries from the example boxes and paste them into your MySQL command-line session:
- In this display, drag your mouse cursor across the phrase you want to capture, highlighting it.
- Press Ctrl+C or select menu item "Edit ... Copy".
- In the MySQL command-line window, select "Paste" (how to do that unfortunately depends on the platform).
- Press Enter.
Okay, let's add two more records to our table:
mysql> insert into trivia values('Frank Wright',37,'Red .. no, wait ...');
mysql> insert into trivia values('Seymour Hawthorne',82,'None of your business.');
mysql> select * from trivia;
+-------------------+-----+------------------------+| Name | Age | Favorite Color |+-------------------+-----+------------------------+| Bruce Callow | 13 | I haven't decided yet. || Frank Wright | 37 | Red .. no, wait ... || Seymour Hawthorne | 82 | None of your business. |+-------------------+-----+------------------------+
NOTE: If something goes wrong and you want to start over entering records into the table, you may want to delete all existing records first. Here's how:
mysql> delete from trivia;
mysql> select * from trivia;
Empty set (0.00 sec)
Again, as with "delete (tablename)", this is a risky command that requires caution. And please notice that "delete from trivia" (note the bold word) deletes the records and keeps the table, but "delete trivia" deletes the table and its data.