Tutorial: Getting Started With MySQL (The Basics)

[deadserious]

        //
       //  
      //Tutorial by deadserious - © [url]http://www.webdesigntalk.net[/url]
     //© 2003 [url]http://www.webdesigntalk.net[/url] 
    //REPUBLICATION OF THE TUTORIAL REQUIRES OUR PERMISSION.
   //[email protected] 
  //
 //
//

If you need to install MySQL:
Installing MySQL on Windows
Dowonload the latest stable version of MySQL from the MySQL Download page or optionally you can start the download by clicking here. Unzip the file you just downloaded to a temporary directory. If you're using a program such as Winzip and it gives you the option to install now go ahead and do that leaving all the default settings selected. If not browse to the temporary directory where you unzipped the files and click on the setup.exe file to start the installation. Continue through the installation with all the default settings until you're done. Browse to your C:mysqlbin directory and Click on the winmysqladmin.exe file. This should launch a graphical user interface that you can use to edit configuration files and maintain and monitor your MySQL server. You should see a screen with:
Create the mysql.ini file with default values and the user below. Reccomended to novice user. Enter a username and password that you will remember. Click Ok. You'll see a stoplight icon in your taskbar, you can stop the MySQL server and/or the WinMySQLAdmin tool by clicking on the icon.

This tutorial is meant to help you get started using MySQL, show you the basics of how it works, and show you some of the basic SQL (Structured Query Lanuage) syntax. There's other database software out there besides MySQL that also use the SQL syntax. Some of them are: MS-SQL, Microsoft Access, PostgreSQL, and Oracle. The reason MySQL is so popular is because of it's incredible performance and it's FREE!

To login to the MySQL server from the command line:

Go to the Start menu -- > selct run.
Type dosprmpt in the input box to open up the command-line interface or if that doesn't work try typing cmd in the input box. That should open up a dos like screen. Change your directory to the bin directory of MySQL by typing:

C:WINDOWSDesktop> cd c:mysqlbin

Remember to press enter after each command.

Now you can type mysql and press enter to start the MySQL monitor.
C:mysqlbin> mysql

You should see something like:

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 4.0.14

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

Note: You may need to have winmysqladmin.exe running before you're able to connect. You can start it by browsing to your C:mysqlbin directory and clicking on the winmysqladmin icon.

Getting Started with MySQL:

SELECT
To retrieve data from your database you use the Select key word. Try typing the following command at the mysql prompt:
mysql> select now();
You should see an organized table with the data you requested that looks something like this:

+---------------------+
| now()               |
+---------------------+
| 2003-08-30 10:00:50 |
+---------------------+
1 row in set (0.00 sec)

Now() is an SQL function that returns the current date and time.

In MySQL keywords such as Select are not case sensitive. The following commands should have idnentical results:
mysql> SELECT VERSION();
mysql> select version();

In MySQL most commands need to have a semicolon at the end in order for it to be executed.
Try entering the follwoing command:
mysql> select current_date(),

You should see something like this:
mysql> select current_date()
->

If the command doesn't have a semicolon at the end MySQL will move on to next the line with the -> prompt. This allows you to combine your commands on multiple lines which can be useful if you have a really long command.

To retrieve the info from the previous example simply type a semicolon at the -> prompt:
mysql> select current_date()

-> ;

+----------------+
| current_date() |
+----------------+
| 2003-08-30     |
+----------------+
1 row in set (0.00 sec)

You can also enter c to cancel your command and return to the mysql> prompt.

You can select multiple values with a single command by separating them with a comma:
mysql> select current_date(), version(), current_time();

+----------------+-----------+----------------+
| current_date() | version() | current_time() |
+----------------+-----------+----------------+
| 2003-08-30     | 3.23.54   | 11:48:43       |
+----------------+-----------+----------------+

mysql> select current_date(),
-> version(),
-> current_time();

+----------------+-----------+----------------+
| current_date() | version() | current_time() |
+----------------+-----------+----------------+
| 2003-08-30     | 3.23.54   | 11:51:14       |
+----------------+-----------+----------------+
1 row in set (0.00 sec)

You can also combine multiple commands together on one line:
mysql> select current_date(); select current_time(); select version();

+----------------+
| current_date() |
+----------------+
| 2003-08-30     |
+----------------+
1 row in set (0.00 sec)

+----------------+
| current_time() |
+----------------+
| 12:49:21       |
+----------------+
1 row in set (0.00 sec)

+-----------+
| version() |
+-----------+
| 3.23.54   |
+-----------+
1 row in set (0.00 sec)

 

[deadserious]

MySQL tutorial continued...

Creating a Database
To create a database you use the CREATE command:
mysql> create database Databasename;

Databasename is what you want to name the database that you're creating.

Try creating a database named myfriends:
mysql> create database myfriends;
Query OK, 1 row affected (0.00 sec)

You can view all of the databases on your server with SHOW databases;
mysql> show databases;

+-----------+
| Database  |
+-----------+
| myfriends |
| mysql     |
| test      |
+-----------+
3 rows in set (0.00 sec)

You can select which database you want to use with USE Databasename; Databasename will be the name of the database you want to use.
mysql> use myfriends;
Database changed

Creating a Table
To put information in the database you will need to create a table to store the information. You need to plan how you're going to create a table very carefully for maximum efficiency. For the purpouse of this table and example we'll create a table named friends that will hold our friends first name, lastname, birthday, and sex:
mysql> create table friends (firstname varchar(20), lastname varchar(20),
-> birthday date, sex char(1));
Query OK, 0 rows affected (0.11 sec)

You can check the table with show tables;

First we create a table named friends, then inside the set of parentheses we put in the fields followed by the data type. So we have firstname and lastname with the varchar data type, birthday with the date data type, and sex with the char data type. Varchar means varying characters and the number 20 in parentheses means it can hold up to 20 characters. Both Varchar and Char can take in a maximum of 255 characters, the main difference is they allocate a different amount of space. You should use char when the data is small and you know it's exact size and varchar when you are not sure how many characters will be. The date data type is in the format year-month-day.

You can verify your table was created the way you expected and view the fields and data types in the table with Describe TableName;
mysql> describe friends;

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| firstname | varchar(20) | YES  |     | NULL    |       |
| lastname  | varchar(20) | YES  |     | NULL    |       |
| birthday  | date        | YES  |     | NULL    |       |
| sex       | char(1)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

More info on data types can be found here: http://www.mysql.com/doc/en/Column_types.html

Inserting Data Into Tables
To insert data into a table you use Insert Into TableName;
mysql> insert into friends values ('Mike', 'Metzger', '1975-02-03', 'm');
Query OK, 1 row affected (0.06 sec)

First we use insert into followed by the table name followed by the word values, then inside the parentheses we enter the values to insert into the table. The values must be entered in the exact same order as the order you specified when creating the table. So Mike would be entered in the firstname column, Metzger would be in lastname, 1975-02-03 would be in birthday, and m would be in sex.

You can aslo specify which fields you want to insert data into and in which order:
mysql> insert into friends (lastname, sex)
-> values ('Simpson', 'f');
Query OK, 1 row affected (0.00 sec)

First you enter the field names in the order you will be inserting into them, then the values you will be inserting into the fields you specified. So Simpson would go in the lastname column and f would go in the sex column.

And another way to insert data:
mysql> insert into friends set
-> firstname = 'Bart', lastname = 'Simpson',
-> birthday = '1985-04-12', sex = 'f';
Query OK, 1 row affected (0.00 sec)

Retriving Data From Tables
mysql> select * from friends;

+-----------+----------+------------+------+
| firstname | lastname | birthday   | sex  |
+-----------+----------+------------+------+
| Mike      | Metzger  | 1975-02-03 | m    |
| NULL      | Simpson  | NULL       | f    |
| Bart      | Simpson  | 1985-04-12 | f    |
+-----------+----------+------------+------+
3 rows in set (0.06 sec)

Here we select all the columns from the table friends. The asterik * is basically a wildcard that means anything and everything. You can also specify what you want to retrive. Say you want to select the firstname of all your male friends:

mysql> select firstname from friends where sex = 'm';

+-----------+
| firstname |
+-----------+
| Mike      |
+-----------+
1 row in set (0.11 sec)

Changing Data
You can use UPDATE to change the values in your table. If you wanted to change the sex from male to female if the lastname is simpson you can do it like this:
mysql> update friends set sex = 'm' where lastname = 'simpson';
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Now your table should look like this:
mysql> select * from friends;

+-----------+----------+------------+------+
| firstname | lastname | birthday   | sex  |
+-----------+----------+------------+------+
| Mike      | Metzger  | 1975-02-03 | m    |
| NULL      | Simpson  | NULL       | m    |
| Bart      | Simpson  | 1985-04-12 | m    |
+-----------+----------+------------+------+
3 rows in set (0.00 sec)

Now try entering more rows into the table. See if you can figure out how to make each row have a value for firstname, lastname, birthday, and sex.

Deleting Data From a Table
You can delete the every row where the firstname field is Mike like this:
mysql> delete from friends where firstname = 'Mike';

You can make it more specific like this:
mysql> delete from friends where firstname = 'Mike' AND lastname = 'Metzger';

To delete all the data in your table:
mysql> delete from friends;

To delete your entire table from the database:
mysql> drop table friends;

To delete your entire database and all the tables in it:
mysql> drop database myfriends;

To exit the mysql you can type quit:
mysql> quit
Bye

To exit out of MS-DOS you can type exit.

That's all for now. To learn some more about MySQL a good place to start is by reading the MySQL Documentation: http://www.mysql.com/documentation/index.html and there's a good tutorial provided by MySQL here: http://www.mysql.com/doc/en/Tutorial.htm

Don't forget to keep checking back from time to time for more MySQL and other tutorials and feel free to reply to this thread if you have any questions, comments, etc... :webdev:

 

[bobbi]

This tutorial is a good start for people who don't know where to start with mysql. It is easy to understand and it shows you how the databases you create in mysql work. Good tutorial

 

[Phoenix]

Brilliant Tutorial! Could you do one like this on ASP? :D