MySQL Basics -- A Helpful MySQL Tutorial

The Analysis and Solutions Company. More than just answers. Solutions. ^sm https://www.analysisandsolutions.com/code/mysql-tutorial.htm © 2012. Modified 2012-05-27 15:29. · and items that require appropriate substitutions

File names appear in this fashion.
mysql\bin
bin
C:\secure\myolddata.sql
Setup.exe.
Setup.exe
Setup.exe
C:\Documents and  Settings\All Users\Start Menu
Start Menu\Programs\Accessories
Command Prompt
Command Prompt
MySQL 50 Prompt
M:
mkdir mysql50
mkdir mysql50\data
mkdir mysql50\data\mysql
mkdir mysql50\ibdata
mkdir mysql50\iblogs

mkdir mysql41
mkdir mysql41\data
mkdir mysql41\data\mysql
mkdir mysql41\ibdata
mkdir mysql41\iblogs

copy "C:\program files\mysql50\data\mysql" mysql50\data\mysql

copy "C:\program files\mysql41\data\mysql" mysql41\data\mysql

my.cnf
.cnf
copy mysql50\data\my.cnf "C:\program files\mysql50\data"

copy mysql41\data\my.cnf "C:\program files\mysql41\data"

copy mysql50\data\my.cnf "C:\program files\mysql50\data"

copy mysql50\data\my.cnf mysql41\data
copy mysql50\data\my.cnf "C:\program files\mysql41\data"

my.cnf
# This config file contains settings for MySQL versions
# 5.0.x and 4.1.x.  Be aware that only one server
# instance can be on a given port.  This sample puts
# version 5.0.x on MySQL's default port, 3306.  Adjust
# the port numbers to suit your situation.

[mysql]
# Displaying the version number in the prompt
# helps when running multiple instances.
prompt = mysql\\_\v>\\_

[mysqld-5.0]
port = 3306
basedir = C:/Program Files/mysql50/
datadir = M:/mysql50/data/
innodb_data_home_dir = M:/mysql50/ibdata/
innodb_log_group_home_dir = M:/mysql50/iblogs/
innodb_data_file_path = ibdata1:10M:autoextend
# Make table names case sensitive.
# Ensures portability when moving applications to other os's.
set-variable = lower_case_table_names=0
# Roll back entire transactions that time out,
# OPTION AVAILABLE SINCE 5.0.32.
innodb_rollback_on_timeout = 1

[mysqld-4.1]
port = 3341
basedir = C:/Program Files/mysql41/
datadir = M:/mysql41/data/
innodb_data_home_dir = M:/mysql41/ibdata/
innodb_log_group_home_dir = M:/mysql41/iblogs/
innodb_data_file_path = ibdata1:10M:autoextend
# Make table names case sensitive.
# Ensures portability when moving applications to other os's.
set-variable = lower_case_table_names=0

cacls M:\mysql50 /t /g administrators:f system:f mysql:c
cacls M:\mysql50\data\my.cnf /t /g administrators:f system:r
cacls "C:\program files\mysql50" /t /g administrators:f system:f mysql:r "authenticated users":r
cacls "C:\program files\mysql50\data" /t /g administrators:f

cacls M:\mysql41 /t /g administrators:f system:f mysql:c
cacls M:\mysql41\data\my.cnf /t /g administrators:f system:r
cacls "C:\program files\mysql41" /t /g administrators:f system:f mysql:r "authenticated users":r
cacls "C:\program files\mysql41\data" /t /g administrators:f

C:\my.cnf
C:\my.cnf
C:\my.cnf
[mysql...]
scripts
delete from user where Host <> 'localhost' and User <> 'root';
delete from db;
update user set Password=password('NewPw') where User='root';
flush privileges;
exit

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.

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

Database "database01" created.

M:\mysql50\data
Database changed

Query OK, 0 rows affected (0.00 sec)

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

+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| field01 | int(11)  | YES  |     |         |       |
| field02 | char(10) | YES  |     |         |       |
+---------+----------+------+-----+---------+-------+

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

+---------+---------+---------+---------+---------+
| field01 | field02 | field03 | field04 | field05 |
+---------+---------+---------+---------+---------+
|       1 | first   | NULL    | NULL    | NULL    |
+---------+---------+---------+---------+---------+

+---------+---------------------+
| field01 | field02             |
+---------+---------------------+
|    NULL | Who thought of foo? |
|    NULL | Pooh thought
of foo. |
+---------+---------------------+

Query OK, 1 row affected (0.00 sec)

+---------+-----------+------------------+------------+----------+
| 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 |
+---------+-----------+------------------+------------+----------+

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+---------+-----------+------------------+------------+----------+
| 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 |
+---------+-----------+------------------+------------+----------+

Query OK, 3 rows affected (0.00 sec)

+---------+-----------+------------------+------------+----------+
| 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 |
+---------+-----------+------------------+------------+----------+

Query OK, 1 row affected (0.01 sec)

+---------+---------+----------+------------+----------+
| field01 | field02 | field03  | field04    | field05  |
+---------+---------+----------+------------+----------+
|       1 | first   | new info | 1999-10-22 | 15:29:01 |
|       2 | second  | another  | 1999-10-23 | 15:29:01 |
+---------+---------+----------+------------+----------+

Bye

MySQL Basics -- A Helpful MySQL Tutorial

Introduction

Contents

Windows 2000, XP and NT 4.0 Installation

Backup Old Data

Get and Install a Recent Version

Quick Tips for Other Windows Operating Systems

Start the Client and Log In

Creating a Simple Database and Displaying its Structure

Instruct MySQL to setup a new database

Open the database

Create a table

List the tables

List the fields in a table

Putting Data into a Table

Insert a record

List all the records in a table

Adding Fields

...one field at a time

...more than one at a time

Did it work?

Multi-line Command Entry

Insert Some More Records into the Table

Add this record

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

Is it in there?

Updating Existing Records

Modify one field at a time

Change multiple fields at once

So, what's up with our data?

Update multiple records in one stroke

Survey says...

Deleting Records

The delete command

Time to Call it Quits

In Closing