Skip to content

June 28, 2012

Quick Introduction to MySQL (Part 1)

by noise

This is a quick tutorial on how to use MySQL Database. Examples from this tutorial were tested using MySQL 5.5.x.

1. MySQL Installation

We will not cover MySQL installation on a specific platform which would be trivial and depend on your OS. Just visit www.mysql.com go to Download section and from there the version you need for your OS

If you are using Windows you might instead look for an already build WAMP package which is easy to install and configure, and you’ve got your whole package for Web development. You can get WAMP from www.wampserver.com.

If you are using Linux, FreeBSD or other UNIX-like OS you might install MySQL by using a package manager from respective distribution.

2. MySQL Server Configuration

Before starting to work with MySQL we will do some simple configurations to make MySQL more secure by adding a password for MySQL root user.

In this tutorial we will assume you are using Linux or a UNIX-like OS. We will connect to MySQL server running a MySQL client from command line (bash or unix shell) by running:

mysql

You will get the following MySQL client console:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.24-log Source distribution
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

Now, before doing anything we will add a password for MySQL root user. Note that MySQL root user is different from your OS root account (root being the superuser in Linux/UNIX). To exit from MySQL client press CTRL+C.

We will add a password for our MySQL root user:

  mysqladmin password mypass
    where:

  • password is an option for mysqladmin command
  • mypass is your password for MySQL root user

If you later want to change your MySQL root password run the following command:

  mysqladmin -u root -p password newpass
    where:

  • password is an option for mysqladmin command
  • newpass is your new password

Now we can connect to the MySQL server using our mysql client and our MySQL root password:

  mysql -u root -p

Before staring to create a database we will do one more configuration that will allow us to see the database name after we run mysql use command from MySQL client. In order to get such nice prompt that is not always available by default (depending on your OS) locate on your OS my.cnf file, edit it and add the following line on section [mysql] from your my.cnf file, then restart your MySQL client:

prompt=(\\u@\\h) [\\d]>\\_

3. First MySQL Database Example (Example 1)

3.1 Creating and deleting a database and a table

To learn MySQL we will create a simple database called myinfo in which we will store my_id,name address, email, zip code and birth date.

    Fields in our database will be:

  • my_id of type int, auto incremented and with unique values
  • name of type varchar(25), where 25 is size
  • address of type varchar(40)
  • email of type varchar(30)
  • zip of type decimal of size 6 (digits)
  • birth of type date

So in order to create a database (which we’ve called myinfo) run mysql client:

  mysql -u root -p

Then create myinfo database using the following mysql command (see the command after mysql> prompt:

mysql> create database myinfo;
Query OK, 1 row affected (0.00 sec)

So first term you have to grasp is database. We’ve created a database called myinfo but we do not have any tables it it. Tables are databases’s structure that holds informations by fields. So the fields from our example must be created within a table from our database. And yes, of course we can have as many tables as we want in our database.

Second term to grasp is table which we kind of explained previously. In order to work with our database we must give a command to our MySQL client to use it using MySQL use command:

mysql> use myinfo;
Database changed

Now we will create people table with the following fields (as discussed previously): my_id, name, address, email, zip code and birth date:

mysql> create table if not exists people (my_id int auto_increment,
  primary key(my_id),
    name varchar(25) not null,
    address varchar(40) not null,
    email varchar(20) not null,
    zip decimal(6) not null,
    birth date not null);
 
Query OK, 0 rows affected (0.01 sec)

So by looking to our return message (Query OK, 0 rows affected (0.01 sec) we notice everything went ok. To check if our table was created we’ll run:

mysql> show tables;
+------------------+
| Tables_in_myinfo |
+------------------+
| people           |
+------------------+
1 row in set (0.00 sec)

To see the structure of people table, run describe command:

mysql> describe people;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| my_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(25)  | NO   |     | NULL    |                |
| address | varchar(40)  | NO   |     | NULL    |                |
| email   | varchar(3)   | NO   |     | NULL    |                |
| zip     | decimal(6,0) | NO   |     | NULL    |                |
| birth   | date         | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

To delete a database we will use drop command:

mysql> drop database mydata;
 
Query OK, 0 rows affected (0.18 sec)

To delete a table we will use drop command on a table:

mysql> drop table mytable;
 
Query OK, 0 rows affected (0.18 sec)

3.2 Modify structure of our table

To modify the structure of our table we will use alter command. From our previous table example let’s say we want to add a new field of type varchar(30) called web

mysql> alter table people add web varchar(30);
 
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

If we want to delete a column from our table we will use drop parameter:

mysql> alter table people drop web;
 
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

It is possible to rename a column (or change it’s type) using change parameter:

mysql> alter table mypeople change web web varchar(20);
 
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

We can also change name of the table using alter command with rename option (following command will rename mypeople table into myppl table:

mysql> alter table mypeople rename myppl;
 
Query OK, 0 rows affected (0.00 sec)

Then after renaming we will get:

mysql> show tables;
+------------------+
| Tables_in_myinfo |
+------------------+
| myppl            |
| people           |
+------------------+
2 rows in set (0.00 sec)

3.3 Inserting data in our table

Now let’s insert some values in our database. In order to insert data we will use insert mysql command:

mysql> insert into people values(default, 'John','Main Street 28',
  'john@example.com', '700701', '20120628');
 
Query OK, 1 row affected, 1 warning (0.07 sec)

We will run previous command again adding more people to our database:

mysql> insert into people values(default, 'Mary','Second Street 10',
  'mary@example.com', '700502', '20120528');
 
mysql> insert into people values(default, 'Dan','Rue Noir 7',
  'dan@example.com', '100202', '20120520');

3.4 List records from our table

Now to see what data we’ve added to our people table from our myinfo database we will run MySQL’s select command:

mysql> select * from people;
+-------+------+------------------+-------+--------+------------+
| my_id | name | address          | email | zip    | birth      |
+-------+------+------------------+-------+--------+------------+
|     1 | John | Main Street 28   | joh   | 700701 | 2012-06-28 |
|     2 | Mary | Second Street 10 | mar   | 700502 | 2012-05-28 |
|     3 | Dan  | Rue Noir 7       | dan   | 100202 | 2012-05-20 |
+-------+------+------------------+-------+--------+------------+

If we want to see only my_id, name and zip fields we will run:

mysql> select my_id,name,zip from people;
+-------+------+--------+
| my_id | name | zip    |
+-------+------+--------+
|     1 | John | 700701 |
|     2 | Mary | 700502 |
|     3 | Dan  | 100202 |
+-------+------+--------+
3 rows in set (0.00 sec)

If we want to see only first two records from our database we will run:

mysql> select * from people limit 2;
+-------+------+------------------+-------+--------+------------+
| my_id | name | address          | email | zip    | birth      |
+-------+------+------------------+-------+--------+------------+
|     1 | John | Main Street 28   | joh   | 700701 | 2012-06-28 |
|     2 | Mary | Second Street 10 | mar   | 700502 | 2012-05-28 |
+-------+------+------------------+-------+--------+------------+
2 rows in set (0.00 sec)

If we want to see only record for John we will run:

mysql> select * from people where name='John';
+-------+------+----------------+-------+--------+------------+
| my_id | name | address        | email | zip    | birth      |
+-------+------+----------------+-------+--------+------------+
|     1 | John | Main Street 28 | joh   | 700701 | 2012-06-28 |
+-------+------+----------------+-------+--------+------------+
1 row in set (0.00 sec)

Note: in previous examples I’ve truncated email column so the output table will fit on the page for this tutorial. The complete values for email fields are (I’ve also added name field too):

select name,email from people;
+------+------------------+
| name | email            |
+------+------------------+
| John | john@example.com |
| Mary | mary@example.com |
| Dan  | dan@example.com  |
+------+------------------+
3 rows in set (0.00 sec)

3.5 Copy structure and data of a table to another table

To copy the structure of a table (for example people table) we will use like command:

mysql> create table mypeople like people;
 
Query OK, 0 rows affected (0.08 sec)

Note that this will copy only the structure of people table and will not copy the data.

To copy the data too from people to mypeople table we will use insert command:

mysql> insert into mypeople select * from people;
 
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

3.6 Delete records from our table

To delete all records from our table we will run:

mysql> delete from mypeople;
 
Query OK, 3 rows affected (0.02 sec)

If we want to delete record for John we will run:

mysql> delete from mypeople where name='John';
 
Query OK, 1 row affected (0.05 sec)

3.7 Update records from our table

To modify a value for a column from our table we will use update command. So for example if we want to change value stored in email column for name John we will use the following command:

mysql> update people set email='john2@example.com' where name='John';
 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3.8 Work with indexes to speed up query time

Last topic on this tutorial is how to create an index a table by a field to speed up query time.

To index a table by a column we will use index statement. For example to index our people table by name column we will use (we’ve choose to call our index nameindex):

mysql> create index nameindex on people (name);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

To display all indexes from our table we will use show index command:

mysql> show index from people;

To delete an index we will use drop index command (where indexname is our index that we want to delete, and people is our table):

mysql> drop index nameindex on people;

Note that index will speed up query for a record only for exact matches (direct querries), that means it will work only when we use where field_name=’value’. Index will not work for like or limit statements. So you will notice index will not work (in fact it will slow query for the following statements:

mysql> select * from people limit 100,5;
Empty set (0.00 sec)
 
mysql> select name,zip from people where zip like '700%';
+------+--------+
| name | zip    |
+------+--------+
| John | 700701 |
| Mary | 700502 |
+------+--------+
2 rows in set (0.00 sec)

To make it clear previous example that uses limit and like statements will not work with index of course for large values. For our 3-row table of this example is useless to even create an index.

This concludes first tutorial from a series of tutorials on MySQL.

Read more from MySQL

Leave a Reply

required
required

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments