MySQL Basics

Source (excerpts)

12. If you're upgrading and followed our instructions at the top of this tutorial, now it's time to import your old data. These steps assume upgrading from 4.1 to 5.0.
    1. C:\Program Files\mysql50\bin> mysql -u root < C:\secure\myolddata.sql
    2. C:\Program Files\mysql50\bin> mysql -u root -f mysql < ..\share\mysql_fix_privilege_tables.sql This SQL file was in the scripts directory prior to version 5.0.38.
       You can ignore all of the query error messages (for example Unknown column, Duplicate column name, etc).
       
    3. Log into the server using the permissions from your old MySQL installation:
       C:\Program Files\mysql50\bin> mysql -u root -p mysql
    4. The upgrade script gives some privilges that you probably don't want most users having. Let's change them back (adjusting the "otherimportantusers..." below as needed for your system):
       mysql 5.0.51b-community-nt> UPDATE user SET Create_tmp_table_priv='N' WHERE user NOT IN ('root', 'otherimportantusers...');
    5. Exit the client and restart the server:
       mysql 5.0.51b-community-nt> exit
       C:\Program Files\mysql50\bin> net stop mysql50
       C:\Program Files\mysql50\bin> net start mysql50
    6. If you've never done this tutorial's "Tighten MySQL's Security" steps, below, check them out now. If you've already done them, you can jump down to the Start the Client section.
13. Tighten MySQL's Security MySQL has good security controls, but the default installation is wide open. So, if you're doing a new installation, let's close things up before we go any further.
    In the following commands, don't forget that if you changed the ports in the my.cnf file, you'll have to adjust the port numbers here.
    
    • MySQL 5.0.x: Activate the "MySQL 50 Shortcut" we created earlier then type in:
      mysql -u root mysql
      
    • MySQL 4.1.x: Activate the "MySQL 41 Shortcut" we created earlier then type in:
      mysql -u root -P 3341 mysql
      
    (If you get the following error message:
    ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
    That means the servers didn't stay started. The most likely reason is the my.cnf file has errors in it. Go back to the Start the services step and carefully read the section about checking the Event Viewer logs.)
    Once you are logged in, copy the following queries to Notepad and change NewPw to something unique. Now copy that and paste it into the command prompt window(s) you just opened.
    

    delete from user where Host <> 'localhost' and User <> 'root';
    delete from db;
    update user set Password=password('NewPw') where User='root';
    flush privileges;
    exit

14. Tada!

Start the Client and Log In

We'll be using MySQL 5.0.x here. Adjust the shortcut and port as necessary.

Activate the "MySQL 50 Shortcut" we created earlier and type in
mysql -u root -p
then enter your password when prompted. You will then see the following output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.51b
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

If your root account doesn't have a password on it, the above command won't work for you. You really need to get on top of security. Log in without the password:

mysql -u root mysql

Then set a password (changing "NewPw", of course):

update user set Password=password('NewPw') where User='root';
flush privileges;

If you get an error saying Client does not support authentication protocol requested by server; consider upgrading SQL client when trying to connect, that means your client is from before version 4.1 while the server you are connecting to is using version 4.1 or later. The best solution is to install a current version of the MySQL client.

Creating a Simple Database and Displaying its Structure

Instruct MySQL to setup a new database

mysql 5.0.51b> create database database01;

Database "database01" created.

All that really does is create a new subdirectory in your M:\mysql50\data directory.

Open the database

mysql 5.0.51b> use database01

Database changed

Create a table

mysql 5.0.51b> 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 5.0.51b> show tables;

+----------------------+
| Tables in database01 |
+----------------------+
| table01              |
| table02              |
+----------------------+

List the fields in a table

mysql 5.0.51b> 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 5.0.51b> 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 5.0.51b> select * from table01;

+---------+---------+
| field01 | field02 |
+---------+---------+
|       1 | first   |
+---------+---------+

Excellent!

Adding Fields

...one field at a time

mysql 5.0.51b> 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 5.0.51b> 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 5.0.51b> 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 5.0.51b> create table table33 (field01 integer,field02 char(30));

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

Don't break up words:

Valid	Invalid
mysql 5.0.51b> create table table33 -> (field01 -> integer, -> field02 -> char(30));	mysql 5.0.51b> 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 5.0.51b> insert into table33 (field02) -> values -> ('Who thought of foo?');	Line Break Stored in Record mysql 5.0.51b> insert into table33 (field02) -> values -> ('Pooh thought -> of foo.');
Results mysql 5.0.51b> 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 5.0.51b> 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 5.0.51b> 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 5.0.51b> 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 5.0.51b> 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 5.0.51b> 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 5.0.51b> update table01 set field05=152901 where field04>19990101;

Query OK, 3 rows affected (0.00 sec)

Survey says...

mysql 5.0.51b> 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 5.0.51b> delete from table01 where field01=3;

Query OK, 1 row affected (0.01 sec)

mysql 5.0.51b> 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 5.0.51b> 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.

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.

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. In addition, the archives of the main list and the Win32 list are tremendous resources. The NYPHP user group has started a MySQL interest group you may find helpful.

If you're curious about database portability, you may find the Building Truly Portable Database Applications in PHP presentation interesting.

If you'll be developing hypertext interfaces to your databases using PHP, check out our SQL Solution™. It's a powerful, user friendly, platform independent API that will make your job a snap! Similarly, you might want to examine PEAR DB, a popular open source database abstraction layer (of which I'm the lead developer).

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