MySQL - Tutorial

Lars Vogel

Version 0.7

07.10.2009

Revision History
Revision 0.1 - 0.2	03.02.2008	Lars Vogel
created
Revision 0.3	09.01.2009	Lars Vogel
Added installation, Reworked
Revision 0.5	25.05.2008	Lars Vogel
Moved Java part to own article at http://www.vogella.de/articles/MySQLJava/article.html
Revision 0.6	13.09.2009	Lars Vogel
General rework
Revision 0.7	07.10.2009	Lars Vogel
Moved SQL commands to own tutorial

MySQL

This article explains how to install MySQL and how to create and query a database.

This article uses Java 1.6 and MySQL 5.1.

Table of Contents

1. Overview

2. Installation

2.1. Windows
2.2. Linux (Ubuntu)

3. Connecting to MySQL

4. Create example database

5. Further SQL commands

6. Thank you

7. Questions and Discussion

8. Links and Literature

1. Overview

MySQL is a famous open source database which can be used free of charge. MySQL is a relational database. Web applications frequently are using MySQL for storing persistence data.

This article covers the installation of MySQL and the usage of the database.

To use MySQL with Java see MySQL and Java Tutorial .

2. Installation

2.1. Windows

Under Windows download the community edition from http://dev.mysql.com/downloads/ . For windows you have installer program available which guides you through the installation process.

Tip

If you had MySQL previously installed the installation may fail with the following error message:

"Access denied for user 'root'@'localhost'".

In this case de-install MySQL and remove any MySQL data directory from your computer and try again.

To de-install MySQL use "Add/Remove Programs" in the "Control Panel". That will also remove the windows services.

The data directory is located in the folder "C:\Documents and Settings\All Users\Application Data\MySQL".

2.2. Linux (Ubuntu)

Under Linux (Ubuntu) you can use the following command:

				
sudo apt-get install mysql-server

The installation will asked you questions regarding your desired installation, e.g. the password to use, etc.

3. Connecting to MySQL

Once MySQL is installed and running you can connect to your database and perform commands in MySQL.

MySQL offers the option to connect to the database via the command line. Under Window, select Start -> Programs -> MySQL -> "your version" ->MySQL Command Line Client. This will a console and ask you for the password of the DB admin.

Under Unix / Linux you can use the following command:

			
mysql db_name -u user -pmypassword

4. Create example database

Start the MySQL command line client and paste the following SQL script into it to create a sample database which we can use as an example.

			
create database feedback;
use feedback;
CREATE USER sqluser IDENTIFIED BY 'sqluserpw'; 
grant usage on *.* to sqluser@localhost identified by 'sqluserpw'; 
grant all privileges on feedback.* to sqluser@localhost;

CREATE TABLE COMMENTS (
		id INT NOT NULL AUTO_INCREMENT, 
		MYUSER VARCHAR(30) NOT NULL,
		EMAIL VARCHAR(30), 
		WEBPAGE VARCHAR(100) NOT NULL, 
		DATUM DATE NOT NULL, 
		SUMMERY VARCHAR(40) NOT NULL,
		COMMENTS VARCHAR(400) NOT NULL,
		PRIMARY KEY (ID)
	);

INSERT INTO COMMENTS values (default, 'lars', 'myemail@gmail.com','http://www.vogella.de', '2009-09-14 10:33:11', 'Summery','My first comment' );

5. Further SQL commands

Please see SQL Tutorial for the usage of SQL commands.

6. Thank you

Thank you for practicing with this tutorial.

I maintain this tutorial in my private time. If you like the information please help me by using flattr or donating or by recommending this tutorial to other people.

7. Questions and Discussion

Before posting questions, please see the vogella FAQ . If you have questions or find an error in this article please use the www.vogella.de Google Group . I have created a short list how to create good questions which might also help you. .

8. Links and Literature

http://www.mysql.com MySQL homepage

http://dev.mysql.com/downloads/ Download link for MySQL

http://forums.mysql.com/ MySQL forum