SQL Intro: Creating Tables
Now, we can start creating our tables. Start by creating a demo_users table. Each user should have a unique id, which will be the primary key of the table. In this case, we will just automatically increment the id value every time we insert into the table. Copy/paste the following at the prompt:
CREATE TABLE demo_users (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(15) NOT NULL );
You will see output similar to the snippet below. Notice that the mysql> text is the actual prompt, and the -> symbol indicates a multi-line command and appears automatically after you press Enter. You will also get a status message after each command. In this case, we see that the query was okay. If there was an issue, you’ll see an error instead.
mysql> CREATE TABLE demo_users (
-> id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(15) NOT NULL );
Query OK, 0 rows affected (0.12 sec)
Verify that the demo_users table was created correctly with the SHOW TABLES and DESCRIBE statements. Below gives both the statements and the expected output, so make sure not to copy/paste the output into the mysql prompt:
SHOW TABLES;
+-------------------+
| Tables_in_user104 |
+-------------------+
| demo_users |
+-------------------+
1 row in set (0.00 sec)
DESCRIBE demo_users;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(15) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
If all looks good, then we can start inserting values into our table. We can insert multiple values at once using the following syntax:
INSERT INTO demo_users (name) VALUES
('Cathy'), ('Alice'), ('Emily'), ('Billy'), ('David');
To see all of the rows you entered into your table, use the SELECT * FROM demo_users statement:
SELECT * FROM demo_users;
+----+-------+
| id | name |
+----+-------+
| 1 | Cathy |
| 2 | Alice |
| 3 | Emily |
| 4 | Billy |
| 5 | David |
+----+-------+
5 rows in set (0.00 sec)
To sort the rows, use the ORDER BY clause:
SELECT * FROM demo_users ORDER BY name ASC;
+----+-------+
| id | name |
+----+-------+
| 2 | Alice |
| 4 | Billy |
| 1 | Cathy |
| 5 | David |
| 3 | Emily |
+----+-------+
5 rows in set (0.00 sec)
Use the DESC keyword if you want to sort in descending order instead.
Now, create the demo_phones table:
CREATE TABLE demo_phones (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
area CHAR(3) NOT NULL DEFAULT '555',
number CHAR(8) NOT NULL,
description VARCHAR(15),
userid INTEGER NOT NULL);
Make sure everything looks right. You can combine two statements together as long as you have the ; semi-colons in the right places:
SHOW TABLES; DESCRIBE demo_phones;
+-------------------+
| Tables_in_user104 |
+-------------------+
| demo_phones |
| demo_users |
+-------------------+
2 rows in set (0.00 sec)
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| area | char(3) | NO | | 555 | |
| number | char(8) | NO | | NULL | |
| description | varchar(15) | YES | | NULL | |
| userid | int(11) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
Now, time to insert a row into this table. This time, lets insert 1 row at a time:
INSERT INTO demo_phones
(area, number, description, userid)
VALUES ('555', '111-1111', 'Work', 1);
When inserting, you can mix up the order of the columns:
INSERT INTO demo_phones
(userid, area, number, description)
VALUES (1, '555', '222-2222', 'Cell');
When inserting, you can take advantage of the default value for area:
INSERT INTO demo_phones
(number, description, userid)
VALUES ('333-3333', 'Home', 2);
You can verify the default value did get set correctly by looking at only the last row:
SELECT * FROM demo_phones WHERE id=3;
+----+------+----------+-------------+--------+
| id | area | number | description | userid |
+----+------+----------+-------------+--------+
| 3 | 555 | 333-3333 | Home | 2 |
+----+------+----------+-------------+--------+
1 row in set (0.00 sec)
You can also insert a NULL into the description column:
INSERT INTO demo_phones
(area, number, description, userid)
VALUES ('555', '444-4444', null, 4);
You can skip specifying the columns, but then you have to provide all columns (including ones that are AUTO_INCREMENT, may be NULL, or have default values):
INSERT INTO demo_phones
VALUES (5, '555', '555-5555', 'Cell', 5);
Lets make sure everything looks right so far:
SELECT * FROM demo_phones;
+----+------+----------+-------------+--------+
| id | area | number | description | userid |
+----+------+----------+-------------+--------+
| 1 | 555 | 111-1111 | Work | 1 |
| 2 | 555 | 222-2222 | Cell | 1 |
| 3 | 555 | 333-3333 | Home | 2 |
| 4 | 555 | 444-4444 | NULL | 4 |
| 5 | 555 | 555-5555 | Cell | 5 |
+----+------+----------+-------------+--------+
5 rows in set (0.00 sec)
Actually, lets get rid of that NULL value. We can update a row as follows:
UPDATE demo_phones
SET description='Home'
WHERE ISNULL(description);
You should see the following messages, indicating 1 row was changed:
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Now to double-check our work:
SELECT * FROM demo_phones WHERE id=4;
+----+------+----------+-------------+--------+
| id | area | number | description | userid |
+----+------+----------+-------------+--------+
| 4 | 555 | 444-4444 | Home | 4 |
+----+------+----------+-------------+--------+
1 row in set (0.00 sec)