Simple MySQL Commands

Let's run through some simple SQL commands. You already know the create table command.

create table users
(
    id              int auto_increment not null,
    nickname        varchar(16) not null,
    password        varchar(16) not null,
    socks           int,
    favorite_number int,
    primary key     (user_id),
    unique          (nickname)
);

What if we just want user nicknames and their favorite number?

select nickname, favorite_number from users;

This gives us:

+----------+-----------------+
| nickname | favorite_number |
+----------+-----------------+
| GdayMate |              42 |
+----------+-----------------+
|   Javier |             945 |
+----------+-----------------+
|     Rolo |               8 |
+----------+-----------------+

And if we want all nicknames of users with less than 10 pairs of socks and whose favorite number is greater than 100?

select nickname from users where socks < 10 and 
    favorite_number > 100;

+----------+
| nickname |
+----------+
|   Javier |
+----------+

So how do you insert a row of data into the table? Simple.

insert into users (nickname, socks)
    values ('Cowlick', 0);

Uh oh, the row that's created is missing the password column! Remember the line used to create the password column in the users table?

create table users
(
    ...
    password varchar(16) not null,
    ...
);

The not null means you must have some value for the column. So MySQL gives an error in this case. We should use instead:

insert into users (nickname, password, socks) 
    values ('Cowlick', 'udder', 0);

This results in a row like this:

+----+----------+----------+-------+-----------------+
| id | nickname | password | socks | favorite_number |
+----+----------+----------+-------+-----------------+ 
|  4 |  Cowlick |    udder |     0 |            NULL |
+----+----------+----------+-------+-----------------+

But wait! Why didn't we have to specify an id? That's not null also. The line from the create table users command:

create table users
(
    id int auto_increment not null, 
    ...
);

In the case of the id column, we have specified auto_increment and MySQL creates the value for us by adding 1 to the greatest value it finds in that column (Rolo has an id of 3).

We've forgotten to include Cowlick's favorite number. Which is -1, by the way. For this we use the update command.

update users set favorite_number = -1 
  where id = 4;

We could have also used:

update users set favorite_number = -1 
  where nickname = 'Cowlick';

But what if there were more than one user with the nickname Cowlick? In our example, there can't be, because in the create table command we've specified

create tables users
(
    ...
    unique (nickname)
);

If we try and insert another user with the nickname Cowlick, we'd get an error from MySQL.

Let's say you've built up a large community of sock aficionados/numerologists and you've been having a lot of problems with Javier. He keeps talking off subject about high jumping and hunting for truffles. The other members of the community are up in arms because they see Javier as a trouble maker and his posts as noise.

delete from users where nickname = 'Javier';

Of course, this doesn't stop Javier from re-registering and continuing his annoying banter.

Let's say you're sick of all the talk about gold toe vs. tube and Western-Lucky-7 vs. Chinese-Good-Fortune-8. You realize there is a demand for a site about high jumping and hunting for truffles. You want to revamp the site.

drop table users;

The table definition and all the data are now gone. Be very careful with this command.