MySQL Basics -- A Tutorial

Introduction

If you're looking for in depth information on MySQL, get a copy of MySQL, by Paul DuBois. The book has received excellent reviews, and rightfully so. It's thorough, well written and nicely organized. Please purchase it here to help defray my costs incurred providing this important page. Thanks!

Welcome. These instructions provide new MySQL users with very basic, step by step, instructions on how to get started.

MySQL runs under a broad array of operating systems. The MySQL commands discussed here apply to all platforms. The installation instructions in particular are guaranteed to work if the following conditions are true:

• You're using Windows NT 4.0 machine and MySQL's Windows executables. I've been told these instructions also work for Windows 2000.
• MySQL is installed on the c: drive in the mysql directory.
• MySQL is set to use English.
• Using MySQL 3.23.51.

If your conditions are different, the instructions should still work, but keep an eye out for slight differences, like which working directory you'll need to be in.

The commands you need to type are displayed in the bold fixed width font. Resulting screen text is shown here in standard fixed width font.

Contents

• Windows 2000, XP and NT 4.0 Installation
• Quick Tips for Other Windows Operating Systems
• Creating a Simple Database and Displaying its Structure
• Putting Data into a Table
• Adding Fields
• Multi-line Command Entry
• Insert Some More Records into the Table
• Updating Existing Records
• Deleting Records
• Time to Call it Quits
• In Closing
• Don't Bug Us!

Windows 2000, XP and NT 4.0 Installation

If you're upgrading your existing installation, do these steps first:

1. Open up a Command Prompt and type in net stop mysql
2. If you have a recent version of MySQL:
   1. Use the unistall feature: Start | Settings | Control Panel | Add/Remove Programs. Find the "MySQL Servers and Clients" line and click the Add/Remove button.
3. If you're using a much older version that doesn't have an uninstall routine:
   1. cd into your mysql\bin directory.
   2. Copy your data directory to another location, just in case there are problems with the new install.
   3. Remove the existing service by typing in mysqld --remove
   4. Remove the files in the bin and shared directories via Windows Explorer command prompt.

If you don't already have MySQL on your system, you'll need to start by getting and installing it.

1. Download MySQL-Win32 version of the program.
2. Open up Windows Explorer.
3. Find the downloaded file and copy it into a temporary directory.
4. Double click on the file. At this point, if you have a zip utility, it should start. If you don't have a program to unzip files, you'll need to get one. I'm partial to PKZIP for Windows.
5. Once the files are extracted, go back into Explorer and double click on Setup.exe.
6. Follow the instructions within the installation program.
7. If you want the data directory to be in yet another place, use Windows Explorer to go into the mysql directory and move the data directory where you like.
8. If you will be using transactions and/or InnoDB tables, create two directories: ibdata and iblogs. The standard location for them is c:\. Feel free to put those directories wherever you like. But, if you're putting them in alternate locations, make sure to uncomment and set the innodb_data_home_dir and innodb_log_group_home_dir lines in your configuration files in the next step...
9. If you're customizing your installation, such as having your mysql/data and/or mysql/bin directories in non-default locations, do the following:
   1. Again, in Explorer, copy one of the *.cnf files from the mysql directory to the c:\ drive and change the name of the copy to my.cnf.
   2. Open up c:\my.cnf in Notepad (Start Menu: Program | Accessories | Notepad. Then in Notepad: File | Open).
   3. Alter the file as needed. I've stripped my configuration file down to only a few commands:
      [mysqld]
datadir=x:/mysql/
basedir=x:/Program Files/mysql/
set-variable = lower_case_table_names=0
10. Open up a Command Prompt window. The default location for starting such windows is: Start Menu | Programs | MS-DOS Prompt.
11. Note: for the rest of this tutorial, the c: drive and the mysql directory are used for demonstration purposes. If you've moved the MySQL programs to a different location, substitute those as appropriate.
12. Switch to the c: drive if you're not already there: L:\>c:.
13. Switch into the MySQL bin directory: C:\>cd mysql\bin
14. Now, there are several programs in that directory. You need to determine which version you want to use.
    1. NT/2000/XP. Transactions. Named pipes.
       c:\mysql\bin\ mysqld-max-nt --install
    2. NT/2000/XP. Transactions.
       c:\mysql\bin\ mysqld-max --install
    3. NT/2000/XP. Named pipes.
       c:\mysql\bin\ mysqld-nt --install
    4. Transactions.
       c:\mysql\bin\ mysqld --install
    5. Optimised binary with no support for transactional tables.
       c:\mysql\bin\ mysqld-opt --install
15. Close the DOS Prompt window: c:\mysql\bin\exit
16. Open up the Services Manager
    • NT 4.0: Start Menu | Settings | Control Panel | Services
    • 2000: Start Menu | Programs | Administrative Tools | Services
17. Highlight the line with "MySql" on it.
18. If the "Startup" column says "Manual" or "Disabled, click on the "Startup" button, hit the "Automatic" radio button and click OK.
19. Security can be improved by creating a special account for MySQL via the User Manger and setting "This Account" to the special user. This necessitates properly setting permissions for the program and data directories. The details of this entire process is too involved to be covered here.
20. Click on the Start button.
21. Close the Services Manager and the Control Panel.
22. Want to save some space? In the mysql\share directory, you can kill the directories for languages you don't need. English is the default. If you're using another language, do the deletes after you've set a language parameter in the my.cnf file.
23. Tada!

Quick Tips for Other Windows Operating Systems

If you're using Windows 95, 98 or ME do not run mysqld --install. These operating systems don't have the ability to host a "service." So, you need to run MySQL as a standalone application by executing the command mysqld --standalone.

I haven't run MySQL on these systems myself, so, for more information, check out the MySQL Mailing List Archive.

Creating a Simple Database and Displaying its Structure

Open up a MS-DOS Prompt window

The default location for starting such windows is: Start Menu | Programs | MS-DOS Prompt.

Get to the working directory

F:\>c:
C:\>cd mysql\bin

Instruct MySQL to setup a new database

All this really does is create a new subdirectory in your c:\mysql\data directory.

C:\mysql\bin>mysqladmin create database01
Database "database01" created.

Startup MySQL

C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 3.21.29a-gamma-debug

Type 'help' for help.

Open the database

mysql> use database01
Database changed

Create a table

mysql> create table table01 (field01 integer,field02 char(10));
Query OK, 0 rows affected (0.00 sec)

Enclose entire list of field names between one pair of parentheses.
Commas are used between each field.
A space may be used after the comma between fields.
A comma is not used after last field.
This, and all SQL statements, are concluded by a semicolon ";".

List the tables

mysql> show tables;
+----------------------+
| Tables in database01 |
+----------------------+
| table01              |
| table02              |
+----------------------+

List the fields in a table

mysql> show columns from table01;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| field01 | int(11)  | YES  |     |         |       |
| field02 | char(10) | YES  |     |         |       |
+---------+----------+------+-----+---------+-------+

Congratulations! Pretty straightforward, eh?

Putting Data into a Table

Insert a record

mysql> insert into table01 (field01,field02) values (1,'first');
Query OK, 1 row affected (0.00 sec)

Enclose entire list of field names between one pair of parentheses.
Enclose the values to be inserted between another pair of parentheses.
Commas are used between each field and between each value.
A space may be used after the comma between fields.

List all the records in a table

mysql> select * from table01;
+---------+---------+
| field01 | field02 |
+---------+---------+
|       1 | first   |
+---------+---------+

Excellent!

Adding Fields

...one field at a time

mysql> alter table table01 add column field03 char(20);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

...more than one at a time

mysql> alter table table01 add column field04 date,add column field05 time;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

The "add column" must be restated for each column.
Commas are used between each add column statement.
A space may be used after these commas.

The MySQL Manual fully explains each possible column data type.

Did it work?

mysql> select * from table01;
+---------+---------+---------+---------+---------+
| field01 | field02 | field03 | field04 | field05 |
+---------+---------+---------+---------+---------+
|       1 | first   | NULL    | NULL    | NULL    |
+---------+---------+---------+---------+---------+

Now we're getting somewhere!

Multi-line Command Entry

The MySQL command line interface allows you to put a statement on one line or spread it across multiple lines. There's no difference in syntax between the two. Using multiple lines allows you to break down the SQL statement into steps you may more easily comprehend.

In multiple line mode, the interpreter appends each line to the prior lines. This continues until you enter a semicolon ";" to close out the SQL statement. Once the semicolon is typed in and you hit enter, the statement is executed.

Here's an example of the same exact SQL statement entered both ways:

Single Line Entry mysql> create table table33 (field01 integer,field02 char(30));

Multiple Line Entry mysql> create table table33 -> (field01 -> integer, -> field02 -> char(30));

Don't break up words:

Valid	Invalid
mysql> create table table33 -> (field01 -> integer, -> field02 -> char(30));	mysql> create table table33 -> (field01 inte -> ger, -> field02 -> char(30));

When inserting or updating records, do not spread a field's string across multiple lines, otherwise the line breaks are stored in the record:

Standard Operation mysql> insert into table33 (field02) -> values -> ('Who thought of foo?');	Line Break Stored in Record mysql> insert into table33 (field02) -> values -> ('Pooh thought -> of foo.');
Results mysql> select * from table33; +---------+---------------------+ | field01 | field02 | +---------+---------------------+ | NULL | Who thought of foo? | | NULL | Pooh thought of foo. | +---------+---------------------+

Insert Some More Records into the Table

Add this record

mysql> insert into table01 (field01,field02,field03,field04,field05) values
    -> (2,'second','another','1999-10-23','10:30:00');
Query OK, 1 row affected (0.00 sec)

Quotes must go around text values.

Standard date format is "yyyy-mm-dd".
Standard time format is "hh:mm:ss".
Quotes are required around the standard date and time formats, noted above.
Dates may also be entered as "yyyymmdd" and times as "hhmmss". If entered in this format, values don't need to be quoted.

Numeric values do not need to be quoted. This holds true regardless of the data type a column is formatted to contain (e.g. text, date, time, integer).

MySQL has a useful command buffer. The buffer stores the SQL statements you've entered thus far. Using it keeps you from having to retype the same commands over and over. Let's use this next step as an example.

Add another record using the command buffer (and optional date and time formats)

1. Hit the up arrow key twice.
2. Hit the ENTER key.
3. Type in the new values between a pair parentheses and stick a closing semicolon on the end.
   (3,'a third','more foo for you',19991024,103004);
4. Hit the ENTER key.

Voilà!

Is it in there?

mysql> select * from table01;
+---------+-----------+------------------+------------+----------+
| field01 | field02   | field03          | field04    | field05  |
+---------+-----------+------------------+------------+----------+
|       1 | first     | NULL             | NULL       | NULL     |
|       2 | second    | another          | 1999-10-23 | 10:30:00 |
|       3 | a third   | more foo for you | 1999-10-24 | 10:30:01 |
+---------+-----------+------------------+------------+----------+

It's in there!

Now, we're almost done...

Updating Existing Records

Modify one field at a time

Again, be careful with syntax. Quote marks need to go around text but not around numbers.

mysql> update table01 set field03='new info' where field01=1;
Query OK, 1 row affected (0.00 sec)

Change multiple fields at once

Remember to put commas between each field you're updating.

mysql> update table01 set field04=19991022, field05=062218 where field01=1;
Query OK, 1 row affected (0.00 sec)

So, what's up with our data?

mysql> select * from table01;
+---------+-----------+------------------+------------+----------+
| field01 | field02   | field03          | field04    | field05  |
+---------+-----------+------------------+------------+----------+
|       1 | first     | new info         | 1999-10-22 | 06:22:18 |
|       2 | second    | another          | 1999-10-23 | 10:30:00 |
|       3 | third one | more foo for you | 1999-10-24 | 10:30:01 |
+---------+-----------+------------------+------------+----------+

Update multiple records in one stroke

mysql> update table01 set field05=152901 where field04>19990101;
Query OK, 3 rows affected (0.00 sec)

Survey says...

mysql> select * from table01;
+---------+-----------+------------------+------------+----------+
| field01 | field02   | field03          | field04    | field05  |
+---------+-----------+------------------+------------+----------+
|       1 | first     | new info         | 1999-10-22 | 15:29:01 |
|       2 | second    | another          | 1999-10-23 | 15:29:01 |
|       3 | third one | more foo for you | 1999-10-24 | 15:29:01 |
+---------+-----------+------------------+------------+----------+

Wee haw!

Deleting Records

The delete command

mysql> delete from table01 where field01=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from table01;
+---------+---------+----------+------------+----------+
| field01 | field02 | field03  | field04    | field05  |
+---------+---------+----------+------------+----------+
|       1 | first   | new info | 1999-10-22 | 15:29:01 |
|       2 | second  | another  | 1999-10-23 | 15:29:01 |
+---------+---------+----------+------------+----------+

Time to Call it Quits

mysql> quit
Bye

In Closing

Now you know some rudimentary commands for running a database in MySQL. Since MySQL is operated by executing SQL calls, you have a broad array of very powerful tools at your disposal. For instance, you're able to display data from several tables at once by joining related fields.

If you're looking for in depth information on MySQL, get a copy of MySQL, by Paul DuBois. The book has received excellent reviews, and rightfully so. It's thorough, well written and nicely organized. Please purchase it here to help defray my costs incurred providing this important page. Thanks!

Similarly, SQL permits complex displays, updates or deletions of multiple records which fit specific criteria. So, your next step toward mastery is learning all about SQL.

James Hoffman has put a tutorial page up on the web entitled Introduction to Structured Query Language. The General SQL Information of the MySQL Manual lists books recommended by many members of their mailing list.

Another thing to note is MySQL offers good security features you'll need to use when operating on networks.

To learn more about MySQL and how to use it, the manual should be your first stop. Also, Paul DuBois' book, MySQL, comes highly recommended and you can buy it now using this link. In addition, the MySQL Mailing List Archive is a tremendous resource.

If you'll be developing hypertext interfaces to your databases, check out our SQL Solution™. It's a powerful, user friendly, platform independent API that will make your job a snap!

Also, if your scripts accept user input, the Form Solution™ is a handy tool for generating XML compliant date/time form elements, cleaning user input, validating and formatting date/time inputs, formatting addresses to US Postal Service standards and holding all variables submitted by a form. The result is improved security and data quality.

Don't Bug Us!

Folks, please read this disclaimer:

1. We are not the company that makes MySQL. It's made by TcX.
2. We are not on the development team.
3. We put this page up here to help people. It gets over 600 hits per day. That's a lot of help.
4. We are unable to respond to inquiries seeking more free help.

Of course, if you're interested in hiring our firm, we'll be glad to hear from you.