mySQL Example 1: A users table

In this example I will create a table of users, add two users, and delete a user. The table consists of a username, an encrypted password, and a unique user id number for each user.

To create a users table with username, password, and user id:

mysql> create table users
     > (
     >   username varchar(20) not null,
     >	 password varchar(20) binary not null,
     >	 id mediumint(10) default '0' not null auto_increment,
     >	 primary key(id)			
     > );

Now we can examine what the table looks like using:

mysql>describe users;

This will result in a description of the table:

+----------+--------------------+------+-----+---------+----------------+
| Field    | Type               | Null | Key | Default | Extra          |
+----------+--------------------+------+-----+---------+----------------+
| username | varchar(20)        |      |     |         |                |
| password | varchar(20) binary |      |     |         |                |
| id       | mediumint(10)      |      | PRI | NULL    | auto_increment |
+----------+--------------------+------+-----+---------+----------------+

Users can now be added to the database. To add a user named User, with password PassWord:

mysql>insert into users values('User',password('PassWord'),null);

To check that the values were inserted, we can display the entire table:

mysql>select * from users;

+----------+------------------+----+
| username | password         | id |
+----------+------------------+----+
| User     | 47b0bc39746cce07 |  1 |
+----------+------------------+----+

A second user can now be added, and the table redisplayed:

mysql>insert into users (username,password) values('User2',password('WordPass'));
mysql>select * from users;

+----------+------------------+----+
| username | password         | id |
+----------+------------------+----+
| User     | 47b0bc39746cce07 |  1 |
| User2    | 2185a751371d88ee |  2 |
+----------+------------------+----+

To select only username User2:

mysql> select * from users where username='User2';

+----------+------------------+----+
| username | password         | id |
+----------+------------------+----+
| User2    | 2185a751371d88ee |  2 |
+----------+------------------+----+

To select only username User2 and only display the user ID number:

mysql> select id from users where username='User2';

+----+
| id |
+----+
|  2 |
+----+

We can remove User by ID number (1). To make sure the wrong username is not removed a select statement can be used to test the conditions:

mysql> select * from users where id='1';

+----------+------------------+----+
| username | password         | id |
+----------+------------------+----+
| User     | 47b0bc39746cce07 |  1 |
+----------+------------------+----+

mysql> delete from users where id='1';

Query OK, 1 row affected (0.00 sec)

mysql> select * from users;

+----------+------------------+----+
| username | password         | id |
+----------+------------------+----+
| User2    | 2185a751371d88ee |  2 |
+----------+------------------+----+