MySQL in 3 minutes


This is a quick-start MySQL tutorial. You’ll create a simple database and do some useful read / write operations.

Step 1. Download MySQL

Download MySQL from mysql.com → downloads → Take me to the community downloads! → Scroll down to Windows downloads → Windows Essentials (x86) → Pick a mirror → No thanks, just take me to the downloads → Greece [National Technical University of Athens] HTTP → mysql-essential-5.1.31-win32.msi

Step 2. Install MySQL

Installing MySQL is very easy. Just follow the steps one-by-one. After installation completes, make sure to select the option “Configure the MySQL Server now” and click “Finish”.

Make sure the "Configure the MySQL Server now" is selected.

In the configuration wizard keep pressing next until you the dialog about character encoding. Here, you should choose “Best Support For Multilingualism”.

When defining default character set, make sure "Best Support for Multilingualism" is selected.

Press next; the last important setting is about security. Provide a root password and write it on a paper.

Step 3. Start MySQL

In order to start using MySQL go to Start → Programs → MySQL → MySQL Server 5.1 → MySQL Command Line Client → and give the root password from the previous step.

To start MySQL in an older version of Windows (e.g. ME) go to Start → Run and write "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --console. Then go to Start → Programs → MySQL → MySQL Server 5.1 → MySQL Command Line Client → and give the root password.

Step 4. Create a database

To create a database in MySQL just type:

create database hello;

For confirmation state

show databases;

and then:

use hello;

Step 5. Create a table

This simple database is about persons. For every person we are interested about 1. the name, 2. the age and 3. a unique number (identifier).

To create a table in MySQL write

CREATE TABLE persons (name VARCHAR(255), age SMALLINT,
             id BIGINT AUTO_INCREMENT, primary key (id));

To confirm write,

show tables;
Displaying the tables of a database

Displaying the tables of a database

and then:

describe persons;

Step 6. Store information (insert data)

To insert some data to your table simply say:

INSERT INTO persons (name, age) VALUES ('Michael Jordan', 45);

Let’s store Jennifer Lopez also:

INSERT INTO persons (name, age) VALUES ('JLo', 39);

How about Al Pacino?

INSERT INTO persons (name, age) VALUES ('Al Pacino', 68);

Step 7. Get information (select data)

To get all persons:

SELECT * FROM persons;
Selecting the data of a table

Selecting the data of a table

To get all persons that are older than 50 years:

SELECT * FROM persons WHERE age > 50;

To get all ‘Michaels’:

SELECT * FROM persons WHERE name LIKE '%Michael%';

Step 8. Change some info (update data)

We have the business requirement to change the age of Jennifer Lopez! To accomplish this task, we may use her id,

UPDATE persons SET age = 29 WHERE id = 2;

or directly her name:

UPDATE persons SET age = 29 WHERE name = 'JLo';

To confirm that her age is now proper:

SELECT * FROM persons;
Updating a value in the table

Updating a value in the table

Step 9. Exit MySQL

To exit MySQL simply type

exit

To shut down MySQL in older versions of Windows (e.g. ME) go to Start → Run
and write "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" shutdown

All the action about MySQL is at mysql.com. Finally, you may get more info about SQL at w3schools.com.

12 Responses to MySQL in 3 minutes

  1. Kathleen says:

    Hi, great intro tutorial…what if you make a syntax error; how do you get out of it? (meaning back to the mysql prompt to continue)? Thanks

  2. ansuman manna says:

    too goog and short term course

  3. Ronald says:

    why 3 minutes….

  4. […] [upmod] [downmod] MySQL in 3 minutes « Nikos’ Java blog (nikojava.wordpress.com) 1 points posted 2 months, 4 weeks ago by SixSixSix tags imported […]

  5. […] For more information you may refer to MySQL in 3 minutes. […]

  6. […] To create it in MySQL you may refer to MySQL in 3 minutes. […]

  7. hkfletch says:

    Hi,
    My question is about the INSERT command for “Multiple Tables.” I am brand new to programming and have purchased and read 5 books on using php and mysql to build dynamic websites. I am building one because I think I have an idea that can save lives or certainly improve lives of people who have suffered loss, and also because I am out of work and hope to bring in some income, so I am motivated to get this done. I am not at liberty to discuss my concept at this time though.

    All the books I have read emphasize how important it is to normalize and break up your tables. So I did my best to do this, expecting I will be able to insert my data from forms, which is a critical part of my concept into these broken up tables. I thought there must be a way to use INSERT for my users to enter their data into “multiple tables.” Examples using INSERT for one table are all over the place but not Multiple tables. I was expecting when I moved into the PHP SQL coding step that examples would abound. But none of my books, or the books at the library or in Barnes and Noble tell me how to enter data in multiple tables. Searches on the internet provide one or two sketchy ideas but nothing I can understand. I need some simple way to do this and well documented so I, a beginner, can understand it.

    I want to collect information from my users when they fill in HTML forms and when they hit the submit button fill in my MySql tables. Seems like it should be the logical thing to be able to expect to do, but I can’t find anything but the sketchiest examples of how to do it … anywhere. Even on the MySql documentation. At least not in a way this beginner understands. This seems like a fundamental thing. Am I so far off track in what I think I should be able to do that I don’t even realized how lost I am? What is wrong with my expectations? If all the books tell me to break up tables why can’t we insert to them? Am I trying to do the wrong thing? Why can I not find it?

    I am asking you to please help me navigate this question to someone out there who may have the answer to this seemingly fundamental essential question. It seems so fundamental I can’t believe it is this hard to find a way to do it. If there is no way then I suppose I will have to recombine my 8 main tables and 20 linking tables into one big table which is exactly opposite of what all my books tell me to do. It feels like all the writers of these book have led me down a blind alley unless I am just missing something very fundamental which is entirely possible. I’d really appreciate some help and I am sure there would have to be others out there who would too. I put this here so others new to MySQL might not go through all that normalizing if it is all for nothing. Unless I am just that lost. Please help. Anyway, Thanks.
    Keith Fletcher

  8. philpbvc3232 says:

    Thanks for your explanation .

  9. Hi Keith,

    what you describe is called a “transaction”. A transaction is a group of SQL statements, a group of operations that have to be applied to some data. These data may expand to multiple tables.

    Example 1: Registration of a user
    * Save the login details (username, password, status, date of registration) in the user_login table. (INSERT)
    * Save the basic user details (name, surname, age) in the user_details table. (INSERT)
    * Save the address (street, country) in the user_address table. (INSERT)

    Example 2: Transfer of money between two accounts in the same bank.
    * Get the current available funds in the account of the sender from table account_credit. (SELECT)
    * Debit the amount from the account of the sender in table account_credit. (UPDATE)
    * Credit the amount from the account of the receiver in table account_credit. (UPDATE)
    * Add an entry in the history of the account of sender in the account_history table. (INSERT)
    * Add an entry in the history of the account of receiver in the account_history table. (INSERT)

    If one of the statements of a group fails, then all the statements of the transaction should be undone (ROLLBACK). However, if they all succeed, then the transaction has fulfilled its purpose successfully and its changes should be made permanent to the database (COMMIT).

    A possible strategy is as you have described it:
    * Design the database structure in an optimal way.
    * Create the simple SQL statements for every table.
    * Group various statements in transactions.

    As a developer you may implement these transactions in various ways.
    * As stored procedures inside the database.
    * Using the API of an object-oriented language.

    As for the first, MySQL has its stored routines, Oracle has the powerful PL/SQL, Informix provides procedures also.

    As for the second, Java provides a robust basis (JDBC) on which a lot of excellent technologies have been developed: Spring-JDBC, MyBatis, Hibernate, Oracle TopLink and many more.

    I’m sorry I have not worked with PHP yet. However, the essence of these data manipulation technologies is the concept of Transaction.

  10. Hi,

    I found Mysql is the best databse for small application and it suits great with linux and php . very good mysql tutorial i must say , I too have shared my experience with mysql in my blogpost mysql tutorial

    hope this helps.

    Thanks
    Javin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: