MySQL is one of the most powerful and widely used databases available. Here is a really quick guide to creating a database, creating a table, inserting, selecting and deleting the data, then table, then database. This will not go into too much depth as there are plenty of resources out there already that can provide more information on each step.
I’ll be using the MySQL command line tool on a Linux (Debian!) platform. Assuming I already have mysql running and a passworded root user account:
mysql>
Now we’re ready to go. First we need to create a database:
Now we list all databases:
mysql>
Now we need to specify which database we’re working with:
I’ll add a user called ‘test_users’ with a password ‘test_password’ and allow him full access to test_database:
mysql>
mysql> \q
Bye
ns3:~#
Now I’ll relog as ‘test_users’
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> use test_database;
Database changed
mysql>
Here I create a new table called ‘test_table’. I’ve created three fields, test_id, test_firstname and test_lastname. There are many different field types and table settings that you can apply, these are beyond the scope of this article.
mysql> show tables;
+————————-+
| Tables_in_test_database |
+————————-+
| test_table |
+————————-+
1 row in set (0.00 sec)
mysql> describe test_table;
+—————-+——————+——+—–+———+—————-+
| Field |
Type
| Null | Key | Default |
Extra |
+—————-+——————+——+—–+———+—————-+
| test_id | int(10) unsigned
| NO | PRI | NULL | auto_increment |
| test_firstname | varchar(30) |
YES | | NULL
|
|
| test_lastname | varchar(30) |
YES | | NULL
|
|
+—————-+——————+——+—–+———+—————-+
3 rows in set (0.01 sec)
mysql>
Inserting records is this easy..
mysql> INSERT INTO test_table (test_firstname, test_lastname) VALUES (’Fred’, ‘Flintstone’);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test_table (test_firstname, test_lastname) VALUES (’Foo’, ‘Bar’);
Query OK, 1 row affected (0.00 sec)
mysql>
Pulling out data is even easier:
mysql> SELECT test_lastname FROM test_table
-> ;
+—————+
| test_lastname |
+—————+
| Palmer |
| Flintstone |
| Bar |
+—————+
3 rows in set (0.00 sec)
mysql> SELECT test_firstname, test_lastname FROM test_table ORDER BY test_lastname ASC;
+—————-+—————+
| test_firstname | test_lastname |
+—————-+—————+
| Foo
| Bar |
| Fred | Flintstone |
| Adam | Palmer |
+—————-+—————+
3 rows in set (0.00 sec)
mysql> SELECT test_firstname, test_lastname FROM test_table ORDER BY test_lastname ASC LIMIT 2;
+—————-+—————+
| test_firstname | test_lastname |
+—————-+—————+
| Foo
| Bar |
| Fred | Flintstone |
+—————-+—————+
2 rows in set (0.00 sec)
Now to delete the data. Note that DELETE FROM will delete ALL data in the table unless you constrain it with WHERE conditions. We use the ‘%’ character as a wildcard. %blah% will match ‘helloblah’ and ‘blahhello’ and ‘helloblahhello’
mysql> DELETE FROM test_table WHERE test_lastname LIKE ‘%e%’;
Query OK, 2 rows affected (0.00 sec)
Delete the table:
mysql>
Delete the database:
mysql> \q
Bye
We’re done. I hope that this has helped. The purpose of the article is to provide a really quick guide to getting started. This blog as a whole is geared towards the user that can take the information above and work the rest out, by trial and error and by google. For that reason I haven’t elaborated on each step or gone into much explaination.