November 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
252627282930  

Categories

November 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
252627282930  
«  
  »

MySQL 5.5

TABLE OF CONTENTS (HIDE)

1.  Introduction
1.1  Relational Database and Structure Query Language (SQL)
1.2  SQL By Examples
1.3  MySQL Relational Database Management System (RDBMS)
2.  How to Install MySQL 5.5 and Get Started
2.1  Step 1: Download and Install MySQL
2.2  Step 2: Create the Configuration File
2.3  Step 3: Start the Server
2.4  Step 4: Start a Client
2.5  Step 5: Set the Password for Superuser “root” and Remove the Anonymous User
2.6  Step 6: Create a New User
2.7  Step 7: Create a new Database, a new Table in the Database, Insert Records, Query and Update
2.8  Summary of Frequently-Used Commands:
2.9  Exercise
3.  Many-to-many Relationship
4.  Java Database Programming
4.1  Installing the MySQL JDBC Driver
4.2  Writing a Java Database Program
5.  Backup and Restore Databases
5.1  Backup via “mysqldump” Utility Program
5.2  Restore via “source” command in a mysql client
6.  (For Advanced Users) NetBeans and MySQL
7.  (For Advanced Users) MySQL GUI Tool – MySQL Workbench
8.  (For Advanced Users) Running MySQL as a Windows Service

1.  Introduction

1.1  Relational Database and Structure Query Language (SQL)

A relational database organizes data in tables. A table has rows (or records) and columns (or fields). Tables can be related based on common columns to eliminate data redundancy.

Popular Relationship Database Management System (RDBMS) includes the commercial Oracle, IBM DB2, Microsoft SQL Server and Access, SAP SyBase and Teradata; and the free MySQL, Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice’s Base.

A high-level language, called Structure Query Language (SQL), had been designed for structuring relational databases; and for creating, updating, reading and deleting (CURD) records. SQL defines a set of commands, such as SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, and etc.

1.2  SQL By Examples

Suppose we have created a table called class101, in a database called studentdb, with 3 columns: id, name and gpa. Each column has a data type. We choose: INT (integer) for column id, FLOAT (floating-point number) for gpa, and VARCHAR(50) (variable-length strings of up to 50 characters) for name. There are three records in the table as follows:

Database: studentdb
Table: class101
+-----------+----------------+-------------+
| id (INT)  | name (VARCHAR) | gpa (FLOAT) |
+-----------+----------------+-------------+
|   11      | Tan Ah Teck    |  4.4        |
|   33      | Kumar          |  4.8        |
|   44      | Kevin          |  4.6        |
+-----------+----------------+-------------+

We can issue these SQL commands, which are intuitive, to query the database or manipulate data:

-- SELECT columns FROM tableName WHERE criteria
SELECT name, gpa FROM class101
SELECT * FROM class101             
  -- The wildcard * denotes all the columns
SELECT name, gpa FROM class101 WHERE gpa >= 2.0
  -- You can compare numbers using =, >, <, >=, <=, <> (!=)
SELECT name, gpa FROM class101 WHERE name = 'Tan Ah Teck'
SELECT name, gpa FROM class101 WHERE name LIKE 'T_n%'
  -- Strings are enclosed in single quotes
  -- You can compare strings with full-match (= or !=)
  -- You can also use operator "LIKE" for pattern-matching, with
  --     wildcard % matches zero or more (any) characters;
  --     wildcard _ matches one (any) character;
SELECT * FROM class101 WHERE gpa > 3.0 OR name LIKE '%T_n%' ORDER BY gpa DESC, name ASC
  -- Order the results in DESC (descending) or ASC (Ascending)

-- DELETE FROM tableName WHERE criteria
DELETE FROM class101  // Delete ALL rows!!!
DELETE FROM class101 WHERE id = 33

-- INSERT INTO tableName VALUES (columnValues)
INSERT INTO class101 VALUES (88, 'Mohammed Ali', 4.88)
INSERT INTO class101 (name, gpa) VALUES ('Kumar', 4.55)

-- UPDATE tableName SET columnName = value WHERE criteria
UPDATE class101 SET gpa = 5.0  // ALL rows
UPDATE class101 SET gpa = gpa + 1.0 WHERE name = 'Tan Ah Teck'

-- CREATE TABLE tableName (columnDefinitions)
CREATE TABLE class101 (id INT, name VARCHAR(50), gpa FLOAT)
 
-- DROP TABLE tableName
DROP TABLE class101  // Delete the table. No UNDO!!!

SQL keywords, names, strings may or may not be case-sensitive, depending on the implementation. In most of the implementations, the keywords are not case-sensitive. For programmers, it is best to treat the names and strings case-sensitive. SQL strings are enclosed in single quotes, but most implementations also accept double quotes.

1.3  MySQL Relational Database Management System (RDBMS)

MySQL is the most used, and possibly the best free, open-source and industrial-strength Relational Database Management System (RDBMS). MySQL was developed by Michael “Monty” Widenius and David Axmark in 1995. It was owned by a Swedish company called MySQL AB, which was bought over by Sun Microsystems in 2008. Sun Microsystems was acquired by Oracle in 2010.

MySQL is successful, not only because it is free and open-source (there are many free and open-source databases, such as Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice’s Base), but also for its speed, ease of use, reliability, performance, connectivity (full networking support), portability (run on most OSes, such as Unix, Windows, Mac), security (SSL support), small size, and rich features. MySQL supports all features expected in a high-performance relational database, such as transactions, foreign key, replication, subqueries, stored procedures, views and triggers.

MySQL is often deployed in a LAMP (Linux-Apache-MySQL-PHP), WAMP (Windows-Apache-MySQL-PHP), or MAMP (Mac-Apache-MySQL-PHP) environment. All components in LAMP is free and open-source.

The mother site for MySQL is www.mysql.com. The ultimate reference for MySQL is the “MySQL Reference Manual”, available at http://dev.mysql.com/doc/. The reference manual is huge – the PDF has over 3700 pages!!!

 

1.  Introduction

1.1  Relational Database and Structure Query Language (SQL)

A relational database organizes data in tables. A table has rows (or records) and columns (or fields). Tables can be related based on common columns to eliminate data redundancy.

Popular Relationship Database Management System (RDBMS) includes the commercial Oracle, IBM DB2, Microsoft SQL Server and Access, SAP SyBase and Teradata; and the free MySQL, Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice’s Base.

A high-level language, called Structure Query Language (SQL), had been designed for structuring relational databases; and for creating, updating, reading and deleting (CURD) records. SQL defines a set of commands, such as SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, and etc.

1.2  SQL By Examples

Suppose we have created a table called class101, in a database called studentdb, with 3 columns: id, name and gpa. Each column has a data type. We choose: INT (integer) for column id, FLOAT (floating-point number) for gpa, and VARCHAR(50) (variable-length strings of up to 50 characters) for name. There are three records in the table as follows:

Database: studentdb
Table: class101
+-----------+----------------+-------------+
| id (INT)  | name (VARCHAR) | gpa (FLOAT) |
+-----------+----------------+-------------+
|   11      | Tan Ah Teck    |  4.4        |
|   33      | Kumar          |  4.8        |
|   44      | Kevin          |  4.6        |
+-----------+----------------+-------------+

We can issue these SQL commands, which are intuitive, to query the database or manipulate data:

-- SELECT columns FROM tableName WHERE criteria
SELECT name, gpa FROM class101
SELECT * FROM class101             
  -- The wildcard * denotes all the columns
SELECT name, gpa FROM class101 WHERE gpa >= 2.0
  -- You can compare numbers using =, >, <, >=, <=, <> (!=)
SELECT name, gpa FROM class101 WHERE name = 'Tan Ah Teck'
SELECT name, gpa FROM class101 WHERE name LIKE 'T_n%'
  -- Strings are enclosed in single quotes
  -- You can compare strings with full-match (= or !=)
  -- You can also use operator "LIKE" for pattern-matching, with
  --     wildcard % matches zero or more (any) characters;
  --     wildcard _ matches one (any) character;
SELECT * FROM class101 WHERE gpa > 3.0 OR name LIKE '%T_n%' ORDER BY gpa DESC, name ASC
  -- Order the results in DESC (descending) or ASC (Ascending)

-- DELETE FROM tableName WHERE criteria
DELETE FROM class101  // Delete ALL rows!!!
DELETE FROM class101 WHERE id = 33

-- INSERT INTO tableName VALUES (columnValues)
INSERT INTO class101 VALUES (88, 'Mohammed Ali', 4.88)
INSERT INTO class101 (name, gpa) VALUES ('Kumar', 4.55)

-- UPDATE tableName SET columnName = value WHERE criteria
UPDATE class101 SET gpa = 5.0  // ALL rows
UPDATE class101 SET gpa = gpa + 1.0 WHERE name = 'Tan Ah Teck'

-- CREATE TABLE tableName (columnDefinitions)
CREATE TABLE class101 (id INT, name VARCHAR(50), gpa FLOAT)
 
-- DROP TABLE tableName
DROP TABLE class101  // Delete the table. No UNDO!!!

SQL keywords, names, strings may or may not be case-sensitive, depending on the implementation. In most of the implementations, the keywords are not case-sensitive. For programmers, it is best to treat the names and strings case-sensitive. SQL strings are enclosed in single quotes, but most implementations also accept double quotes.

1.3  MySQL Relational Database Management System (RDBMS)

MySQL is the most used, and possibly the best free, open-source and industrial-strength Relational Database Management System (RDBMS). MySQL was developed by Michael “Monty” Widenius and David Axmark in 1995. It was owned by a Swedish company called MySQL AB, which was bought over by Sun Microsystems in 2008. Sun Microsystems was acquired by Oracle in 2010.

MySQL is successful, not only because it is free and open-source (there are many free and open-source databases, such as Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice’s Base), but also for its speed, ease of use, reliability, performance, connectivity (full networking support), portability (run on most OSes, such as Unix, Windows, Mac), security (SSL support), small size, and rich features. MySQL supports all features expected in a high-performance relational database, such as transactions, foreign key, replication, subqueries, stored procedures, views and triggers.

MySQL is often deployed in a LAMP (Linux-Apache-MySQL-PHP), WAMP (Windows-Apache-MySQL-PHP), or MAMP (Mac-Apache-MySQL-PHP) environment. All components in LAMP is free and open-source.

The mother site for MySQL is www.mysql.com. The ultimate reference for MySQL is the “MySQL Reference Manual”, available at http://dev.mysql.com/doc/. The reference manual is huge – the PDF has over 3700 pages!!!

MySQL RDBMS operates as a client-server system over TCP/IP network. The server runs on a machine with an IP address, on a chosen TCP port number. The default TCP port number for MySQL is 3306, but you are free to choose another port number between 1024 and 65535. Users can access the server via a client program, connecting to the server at the given IP address and TCP port number.

A MySQL database server contains one or more databases (aka schemas). A database contains one or more tables. A table consists of rows (records) and columns (fields).

2.  How to Install MySQL 5.5 and Get Started

2.1  Step 1: Download and Install MySQL

  1. Download MySQL from www.mysql.com.
    1. From the top-level tabs, select the “Downloads (GA)” ? “MySQL Community Server” ? “General Available (GA) Release”, “MySQL Community Server 5.5.xx”, where xx is the latest update number.
    2. Select “Microsoft Windows”; or your target platform.
    3. Choose the ZIP Archive (without MSI Installer) “mysql-5.5.xx-win32.zip (143M)”. (DO NOT use “mysql-5.5.xx.zip” (28M), which is the source distribution and requires compilation.)
    4. Click “No thanks, just start my downloads!”.
  2. UNZIP into a directory of your choice. DO NOT unzip into your desktop (because it is hard to locate the path). I suggest that you unzip into “d:\myproject“. MySQL will be unzipped as “d:\myproject\mysql-5.5.xx-win32“. For ease of use, we shall shorten and rename the directory to “d:\myproject\mysql“.

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 1.

I recommend using the “ZIP” version, instead of the “Installer” version. You can simply delete the entire MySQL directory when it is no longer needed (without running the un-installer). You are free to move or rename the directory. You can also install (unzip) multiple copies of MySQL in the same machine on different directories.

I shall assume that MySQL is installed in directory “d:\myproject\mysql“. But you need to TAKE NOTE OF YOUR MySQL INSTALLED DIRECTORY. Hereafter, I shall denote the MySQL installed directory as <MYSQL_HOME> in this article.

MySQL distribution includes:

  1. A SQL server;
  2. A command-line client;
  3. Utilities (for database administration, backup/restore, and others);
  4. Client libraries for you to write your own client.

2.2  Step 2: Create the Configuration File

(Note) Programmers need to view the file extension (such as .txt, .ini). To display the file extension in Windows, run “Control Panel” ? “Folder Options” ? Select tab “View” ? Uncheck “Hide extensions for known file types”.

 

Use a text editor to create the following configuration file called “my.ini” and save it in your MySQL INSTALLED DIRECTORY (e.g., “d:\myproject\mysql“). You can ignore lines beginning with #, which are comments. IMPORTANT: You need to modify the highlighted lines for your MySQL installed directory!!!

# Save as my.ini in your MySQL installed directory
[mysqld]
# Set MySQL base (installed) directory
# @@ Change to your MySQL installed directory @@
basedir=d:/myproject/mysql

# Set MySQL data directory
# @@ Change to sub-directory "data" of your MySQL installed directory  @@
datadir=d:/myproject/mysql/data

# Run the server on this TCP port number
port=8888
   
[client]
# MySQL client connects to the server running on this TCP port number
port=8888
Explanation
  • [mysqld]
    [client]
    The MySQL operates as a client-server system, and consists of a server program and a client program. There are two sections in the configuration: [mysqld] for the server program, and [client] for the client program.
  • basedir=…
    datadir=…
    “basedir” and “datadir” specify the MySQL installed directory and data directory, respectively. Make sure that you set their values according to your own installation. You need to use Unix-style Forward-Slash '/' as the directory separator, as shown (instead of Windows-style backward-slash '\').
  • port=8888
    MySQL is a TCP/IP application. The default TCP port number for MySQL is 3306. However, it may crash with a MySQL server already running in some lab machines. You may choose any port number between 1024 to 65535, which is not used by an existing application. I choose 8888 for our server.
  • This configuration file specifies the bare minimum. There are many more configuration options. Sample configuration files are provided in <MYSQL_HOME>.

(For Notepad Users) If you use Notepad, make sure that you double-quote the filename "my.ini" when saving the file; otherwise, it may be saved as "my.ini.txt". Worse still, you will not see the file extension “.txt” unless you enable displaying of file extension. Nonetheless, “my.ini.txt” has file-type description of “Text Document”; while “my.ini” has “Configuration Settings”.
Don’t use Notepad for programming!!! At the minimum, you should have Notepad++.

(For Mac Users) If you use the DMG installer, skip this step. MySQL server will be running on the default port number of 3306.
You could read “How to Install MySQL 5 on Mac” Step 2 on how to configure the port number.

(For Advanced Users Only) Where to place my.ini (or my.cnf) configuration file?
In Windows, the options are combined from the following files in this order: c:\Windows\my.ini, c:\Windows\my.cnf, c:\my.ini, c:\my.cnf, <MYSQL_HOME>\my.ini, <MYSQL_HOME>\my.cnf. If an option is specified in more than one place, the last setting takes effect. Our my.ini is kept in <MYSQL_HOME>, which overrides all the previous settings.
Alternatively, you can keep the my.ini in any location, and use the startup option --defaults-file=filename to specify the location of the configuration file and bypass all the files listed above.

2.3  Step 3: Start the Server

The MySQL is a client-server system. The database is run as a server application. Users access the database server via a client program, locally or remotely, as illustrated:

  1. The database server program is called “mysqld.exe” (with a suffix 'd', which stands for daemon – a daemon is a program/process running in the background).
  2. The interactive client program is called “mysql.exe” (without the 'd').

The MySQL executable programs are kept in the “bin” sub-directory of the MySQL installed directory.

Startup Server

To start the database server, launch a CMD shell. Set the current directory to “<MYSQL_HOME>\bin“, and run command “mysqld --console“.

-- Change the current directory to MySQL's "bin" directory.
-- Assume that the MySQL installed directory is "d:\myproject\mysql".
Prompt> d:                   -- Change the current drive
D:\...> cd \                 -- Change Directory (cd) to the ROOT directory of drivce 'd'
D:\> cd myproject\mysql\bin  -- Change Directory (cd) to the MySQL's bin directory
   
-- Start the MySQL Database Server
D:\myproject\mysql\bin> mysqld --console

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 3 on how to start the server.

You shall see these messages in the console. Take note of the TCP port number.

......
......
XXXXXX XX:XX:XX [Note] mysqld: ready for connections.
Version: '5.5.xx-community'  socket: ''  port: 8888  MySQL Community Server (GPL)

The MySQL database server is now started, and ready to handle clients’ requests.

Explanation
  • The --console option directs the server output messages to the console. Without this option, you will see a blank screen.
  • Observe that the database server runs on TCP port 8888, as configured in the “my.ini“.

(Skip Unless …) “Anything that can possibly go wrong, does.” Read “Common Problems in Starting the MySQL Server after Installation“.

Shutdown Server

The quickest way to shut down the database server is to press control-c (or control-break) to initiate a normal shutdown. DO NOT KILL the server via the “close” button.

Observe these messages from the MySQL server console:

XXXXXX XX:XX:XX [Note] mysqld: Normal shutdown
......
XXXXXX XX:XX:XX  InnoDB: Starting shutdown...
XXXXXX XX:XX:XX  InnoDB: Shutdown completed; log sequence number 0 44233
......
XXXXXX XX:XX:XX [Note] mysqld: Shutdown complete

(For Advanced Users Only) Alternatively, you could shutdown the server via the utility program “mysqladmin“. Start another CMD shell. Run the “mysqladmin” (with user root) as follows:

-- If root has no password
d:\myproject\mysql\bin> mysqladmin -u root shutdown
-- If root has a password
d:\myproject\mysql\bin> mysqladmin -u root -ppassword shutdown

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 3 on how to shutdown the server.

2.4  Step 4: Start a Client

Recall that the MySQL is a client-server system. Once the server is started, one or more clients can be connected to the database server. A client could be run on the same machine (local client); or from another machine (remote client).

To login to a MySQL server, you need to provide a username and password. During the installation, MySQL provides a privileged user (aka superuser) called “root” WITHOUT setting its password.

Let’s start a local client (on the same machine) with this superuser “root“. First, make sure that the server is running (see previous step to re-start the server if it has been shutdown).

Start a Client

Recall that the server program is called “mysqld” (with a suffix 'd' for daemon), while the client program is called “mysql” (without the 'd'). Start another CMD shell to run the client:

-- Change the current directory to <MYSQL_HOME>\bin.
-- Assume that the MySQL is installed in "d:\myproject\mysql".
Prompt> d:                   -- Change the current drive
D:\...> cd \                 -- Change Directory (cd) to the ROOT directory
D:\> cd myproject\mysql\bin  -- Change Directory to YOUR MySQL's "bin" directory
   
-- Starting a client with superuser "root"
D:\myproject\mysql\bin> mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

-- Client started. The prompt changes to "mysql>".
-- You can now issue SQL commands.
mysql>

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 4 on how to start/stop a client.

(Skip Unless…) Read “Common Problems in Starting the MySQL Client“.

Before we proceed, let’s issue a “status” command. Check the TCP Port Number. Make sure that you are connecting to YOUR server (running on port 8888), because there could be many MySQL servers running in your machine (on different port numbers installed by other people).

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.51, for Win32 (ia32)
......
Current user:           root@localhost
TCP port:               8888
......

2.5  Step 5: Set the Password for Superuser “root” and Remove the Anonymous User

As mentioned earlier, the MySQL installation provides a privileged user called “root” WITHOUT setting its password. “root” is a superuser that can do anything, including deleting all the databases. Needless to say, you have to set a password for root. The root’s password shall only be made available to the database administrator, not the common users.

Set Password for “root”

Let’s continue with our client session:

-- Query all the users and their passwords from database "mysql" table "user"
mysql> select host, user, password from mysql.user;
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | root |          |
| 127.0.0.1 | root |          |
| ::1       | root |          |
| localhost |      |          |
+-----------+------+----------+
4 rows in set (0.09 sec)
   
-- Set password for 'root'@'127.0.0.1'. Replace xxxx with your chosen password
-- Take note that strings are to be enclosed by a pair of single-quotes.
mysql> set password for 'root'@'127.0.0.1'=password('xxxx');
Query OK, 0 rows affected (0.00 sec)
   
-- Set password for 'root'@'localhost'
mysql> set password for 'root'@'localhost'=password('xxxx');
Query OK, 0 rows affected (0.00 sec)
  
-- Set password for 'root'@'::1'
mysql> set password for 'root'@'::1'=password('xxxx');
Query OK, 0 rows affected (0.00 sec)
  
-- Query the users and passwords again.
-- Passwords are not stored in clear text. 
mysql> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| 127.0.0.1 | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| ::1       | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| localhost |      |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)
   
-- logout and terminate the client program
mysql> quit
Bye
Explanation
  • select host, user, password from mysql.user;
    This command lists all the existing users, kept in the system database “mysql” table “user“. Observe that four user accounts are installed by default: 'root'@'localhost', 'root'@'127.0.0.1', 'root'@'::1' and ''@'localhost' (this is an anonymous user with empty string as the username), all WITHOUT passwords (or empty string as password). Take note that a MySQL user is identified by three attributes: username, password, and the IP address (or hostname) of the client’s machine. For example, the user root can only login from the localhost, or IPv4 127.0.0.1, or IPv6 ::1; but NOT from other IP addresses (or hostnames).
  • A command can span more than one line. You have to terminate each command with a semicolon ';'. Otherwise, a “->” prompt will appear to prompt for the rest of the command. Enter a ';' to complete the command; or '\c'to cancel the command. For example,
    mysql> select host, user, password from mysql.user;     
......
 
mysql> select host, user, password from mysql.user
    -> ;
......
 
mysql> select 
    -> host, user, password
    -> from mysql.user;
...... 
   
mysql> select host, u \c
mysql>
    		 
    -- Semicolon terminates the command,
-- and sends the command to the server for processing.
 
-- This command is not complete, to be continued in the next line.
-- The semicolon terminates the command, and sends it to the server.
   
  
-- A command can span several lines, terminated by a semicolon.
  
   
   
  
-- "\c" cancels the command, and does not send it to the server.
  • set password for ‘root’@’localhost’=password(‘xxxx’);
    You can set the password for 'root'@'localhost', 'root'@'127.0.0.1' and 'root'@'::1' using the “set password” command, where ‘xxxx‘ denotes your chosen password.
  • “localhost” is a special hostname, meant for local loop-back (i.e., the server is running in the same machine as the client). It has the IPv4 address of 127.0.0.1, and IPv6 address of ::1.
  • Issue “quit“, “exit“, or press control-c to terminate the client.
  • During a client session, you can issue “help” or “\h” for help.
Start a client with Password

We have just set a password for root and exited the client. Start a client and login as root again. BUT now, you need to include a "-p" option, which prompts the user for the password:

-- Change the current working directory to <MYSQL_HOME>\bin
D:\myproject\mysql\bin> mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.
......
  
-- client started, ready to issue SQL command
mysql>
Remove the Anonymous User

For security reason, remove the anonymous user (identified by an empty-string username) as follows:

mysql> drop user ''@'localhost';
   
mysql> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| 127.0.0.1 | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| ::1       | root | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)
   
mysql> quit

2.6  Step 6: Create a New User

The superuser “root” is privileged, which is meant for database administration and is not meant for common use. We shall create a new user – let’s call it “myuser” – with a lesser privilege. To create a new user, start a client with superuser “root“:

D:\myproject\mysql\bin> mysql -u root -p
Enter password: ********
   
-- Create a new user called "myuser", which can login from localhost, with password "xxxx"
mysql> create user 'myuser'@'localhost' identified by 'xxxx';
Query OK (0.01 sec)
   
-- Grant permission to myuser
mysql> grant all on *.* to 'myuser'@'localhost';
Query OK (0.01 sec)
   
-- Query all users and passwords
mysql> select host, user, password from mysql.user;
+-----------+--------+-------------------------------------------+
| host      | user   | password                                  |
+-----------+--------+-------------------------------------------+
| localhost | root   | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| 127.0.0.1 | root   | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| ::1       | root   | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| localhost | myuser | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
+-----------+--------+-------------------------------------------+
4 rows in set (0.00 sec)
   
mysql> quit
Explanation
  • create user ‘myuser’@’localhost’ identified by ‘xxxx’
    We use the command “create user” to create a new user called 'myuser'@'localhost', who can login to the server locally from the same machine (but not remotely from another machine), with password “xxxx“.
  • grant all on *.* to ‘myuser’@’localhost’
    The newly created user has no privilege to perform any database operation such as select, insert and update. We use the “grant” command to grant “all” the privileges (including select, insert, delete, and so on) to this new user for all the databases and all the tables (“on *.*“). For production, you should grant only the necessary privileges (e.g., “grant select, insert, update on...“, without the “delete“) on selected databases and selected tables (e.g., "on studentdb.*” – all the tables of the database studentdb).

2.7  Step 7: Create a new Database, a new Table in the Database, Insert Records, Query and Update

A MySQL server contains many databases (aka schema). A database consists of many tables. A table contains rows (records) and columns (fields).

Let’s create a database called “studentdb“, and a table called “class101” in the database. The table shall have three columns: id (of the type INT – integer), name (of the type VARCHAR(50) – variable-length string of up to 50 characters), gpa (of the type FLOAT – floating-point number).

CAUTION: Programmers don’t use blank and special characters in names (such as database names, table names, column names). It is either not supported, or will pose you many more challenges.

TIPS: Before we proceed, here are some tips on using the client:

  • You need to terminate your command with a semicolon ';', which sends the command to the server for processing.
  • A command can span several lines, and terminated by a semicolon ';'.
  • You can use \c to cancel (abort) the current command.
  • You can use up/down arrow keys to retrieve the previous commands (history commands).
  • You should enable copy/paste functions of CMD shell. [To enable copy/paste, click the CMD’s icon ? Properties ? Options ? Edit Options ? Check “QuickEdit Mode”. You can then select the desired texts and use a “right-click” to copy the selected text; another “right-click” to paste.]

Let’s start a client with our newly-created user “myuser“.

D:\myproject\mysql\bin> mysql -u myuser -p
Enter password: ********
   
-- Create a new database called "studentdb"
mysql> create database if not exists studentdb;
Query OK, 1 row affected (0.08 sec)
   
-- list all the databases in this server
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| studentdb          |
| test               |
+--------------------+
5 rows in set (0.07 sec)
   
-- Use "studentdb" database as the current (default) database
mysql> use studentdb;
Database changed
   
-- Remove the table "class101" in the current database if it exists
mysql> drop table if exists class101;
Query OK, 0 rows affected (0.15 sec)
   
-- Create a new table called "class101" in the current database 
--  with 3 columns of the specified types
mysql> create table class101 (id int, name varchar(50), gpa float);
Query OK, 0 rows affected (0.15 sec)
   
-- List all the tables in the current database "studentdb"
mysql> show tables;
+---------------------+
| Tables_in_studentdb |
+---------------------+
| class101            |
+---------------------+
1 row in set (0.00 sec)
   
-- Describe the "class101" table (list its columns' definitions)
mysql> describe class101;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| gpa   | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
   
-- Insert a row into "class101" table.
-- Strings are to be single-quoted. No quotes for INT and FLOAT.
mysql> insert into class101 values (11, 'Tan Ah Teck', 4.8);
Query OK, 1 row affected (0.03 sec)
   
-- Insert another row
mysql> insert into class101 values (22, 'Mohamed Ali', 4.9);
Query OK, 1 row affected (0.03 sec)
   
-- Select all columns (*) from table "class101"
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.8 |
| 22 | Mohamed Ali |  4.9 |
+----+-------------+------+
2 rows in set (0.00 sec)
  
-- Select columns from table "class101" with criteria
mysql> select name, gpa from class101 where gpa > 4.85;
+-------------+------+
| name        | gpa  |
+-------------+------+
| Mohamed Ali |  4.9 |
+-------------+------+
1 rows in set (0.00 sec)
  
-- Update selected records
mysql> update class101 set gpa = 4.4 where name = 'Tan Ah Teck';
Query OK, 1 row affected (0.03 sec)
   
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.4 |
| 22 | Mohamed Ali |  4.9 |
+----+-------------+------+
2 rows in set (0.00 sec)
   
-- delete selected records
mysql> delete from class101 where id = 22;
Query OK, 1 row affected (0.03 sec)
   
mysql> select * from class101;
+----+-------------+------+
| id | name        | gpa  |
+----+-------------+------+
| 11 | Tan Ah Teck |  4.4 |
+----+-------------+------+
1 rows in set (0.00 sec)
   
-- You can store SQL commands in a text file (called SQL script) and run the 
--   script, instead of entering each of the SQL commands from the prompt.
-- For example, use a text editor to create a SQL script called "mycommands.sql" 
--   containing the following three SQL statements. Save the file under "d:\myproject".
insert into class101 values (33, 'Kumar', 4.8); insert into class101 values (44, 'Kevin', 4.6); Select * from class101;

-- Use the following "source" command to run the SQL script.
-- You need to provide the full path to the script.
-- Use Unix-style forward slash '/' as directory separator, 
--   instead of Windows-style back slash '\'.

mysql> source d:/myproject/mycommands.sql
Query OK, 1 row affected (0.00 sec)   // INSERT command output
Query OK, 1 row affected (0.00 sec)   // INSERT command output
+------+-------------+------+         // SELECT command output
| id   | name        | gpa  |
+------+-------------+------+
|   11 | Tan Ah Teck |  4.4 |
|   33 | Kumar       |  4.8 |
|   44 | Kevin       |  4.6 |
+------+-------------+------+
3 rows in set (0.00 sec)
Try:
  1. Select records with names starting with letter 'K'. (Hints: name like 'K%')
  2. Select records with names NOT starting with letter 'K'. (Hints: name NOT like ...)
  3. Select records with gpa between 4.35 and 4.65. (Hints: gpa >= 4.3 AND ...)
  4. Select records with names having a letter 'e'. (Hints: name like '%e%')
  5. Select records with names having a letter 'e' or 'a'. (Hints: ... OR ...)
  6. Select records with names NOT having a letter 'e' or 'a'. (Hints: NOT (... OR ...))
  7. Select records with names having a letter 'e' and gpa ? 4.5.

(Skip Unless… ) Read “Common Problems in Using the mysql Client“.

(For Advanced Users only) Remote Login: If a user is permitted to login remotely from another machine, you can use command “mysql -h serverHostname -u username -p” to login remotely by specifying server’s hostname or (IP address) via the '-h' option.

2.8  Summary of Frequently-Used Commands:

Starting MySQL Server and Client
// Start the Server
> cd path-to-mysql-bin
> mysqld --console
 
// Start a Client
> cd path-to-mysql-bin
> mysql -u userid        // Without password
> mysql -u userid -p     // To prompt for password
Frequently-used MySQL Commands

MySQL commands are NOT case sensitive.

// General
STATUS      // Displays status such as port number
;           // Sends command to server for processing (or \g)
\c          // Cancels (aborts) the current command
\G          // Displays the row vertically
 
// Database-level
DROP DATABASE databaseName                 // Deletes the database
DROP DATABASE IF EXISTS databaseName       // Deletes only if it exists
CREATE DATABASE databaseName               // Creates a new database
CREATE DATABASE IF NOT EXISTS databaseName // Creates only if it does not exists
SHOW DATABASES                             // Shows all databases in this server
   
// Set current (default) database.
// Otherwise you need to use the fully-qualified name, in the form 
//   of "databaseName.tableName", to refer to a table.
USE databaseName
SELECT DATABASE();       // show the current database
   
// Table-level
DROP TABLE tableName
DROP TABLE IF EXISTS tableName
CREATE TABLE tableName (column1Definition, column2Definition, ...)
CREATE TABLE IF NOT EXISTS tableName (column1Definition, column2Definition, ...)
SHOW TABLES              // Shows all the tables in the default database
DESCRIBE tableName       // Describes the columns for the table
DESC tableName           // Same as above
   
// Record-level (CURD - create, update, read, delete)
INSERT INTO tableName VALUES (column1Value, column2Value,...)
INSERT INTO tableName (column1Name, ..., columnNName) 
   VALUES (column1Value, ..., columnNValue)
DELETE FROM tableName WHERE criteria
UPDATE tableName SET columnName = expression WHERE criteria
SELECT column1Name, column2Name, ... FROM tableName 
   WHERE criteria
   ORDER BY columnAName ASC|DESC, columnBName ASC|DESC, ...
  
// Running a script of MySQL statements
SOURCE full-Path-Filename

2.9  Exercise

  1. Show all the databases.
  2. Create a new database called “ABCTrading“.
  3. Set the “ABCTrading” database as the default database.
  4. Show all the tables in the default database.
  5. Create a new table called “products” with the columns and type indicated below. Show the table description. Insert the following records: 
    +-------+----------+-------------+----------+---------+
| id    | category | name        | quantity | price   |
| (INT) | CHAR(3)  | VARCHAR(20) | (INT)    | (FLOAT) |
+-------+----------+-------------+----------+---------+
| 1001  | PEN      | Pen Red     |     5000 |  1.23   |
| 1002  | PEN      | Pen Blue    |     8000 |  1.25   |
| 1003  | PEN      | Pen Black   |     2000 |  1.25   |
| 1004  | PCL      | Pencil 2B   |    10000 |  0.49   |
| 1005  | PCL      | Pencil HB   |    10000 |  0.48   |
+-------+----------+-------------+----------+---------+
  6. Try issuing some SELECT, UPDATE, and DELETE commands.

3.  Many-to-many Relationship

A book is written by one or more authors. An author may write zero or more books. This is known as a many-to-many relationship. Can you model many-to-many relationship in one single table? Try it out!

The many-to-many relationshop between books and authors can be modeled with 3 tables, as shown:

Exercise
  1. Create a database called “mybookstore“, and use it as the default database.
  2. Create 3 tables “books“, “authors“, and “books_authors” in the database “mybookstore“.
  3. Insert the respective records into the tables. Check the contents of each of the tables (via SELECT * from tableName command).
  4. Try this query and explain the output: 
    SELECT books.title, books.price, authors.name
   FROM books, books_authors, authors
   WHERE books.isbn = books_authors.isbn 
      AND authors.authorID = books_authors.authorID
      AND authors.name = 'Tan Ah Teck';
  5. Issue a query to display all the books (title, price, qty) by “Tan Ah Teck” with price less than 20.
  6. Issue a query to display all the authors (name and email) for the book title “Java for Dummies”.
  7. Issue a query to display the books (title, price, qty) and all the authors (name and email) for books with title beginning with “Java” (Hints: title LIKE 'Java%').

4.  Java Database Programming

Instead of using the “mysql” client program provided (as in the previous section), you can write your own client program (in Java or other programming languages) to access the MySQL server. Your client program shall create a TCP socket to connect to the database server at the given IP address and TCP port number 8888, issue the SQL commands, and process the results received.

Before you proceed, I shall assume that you are familiar with Java Programming and have installed the followings:

  1. JDK (Read “How to install JDK and Get Started“).
  2. A programming text editor, such as TextPad or Notepad++ (Read “Programming Text Editor“); or a Java IDE such as Eclipse or NetBeans (Read “How to Install Eclipse” or “How to Install NetBeans“).

4.1  Installing the MySQL JDBC Driver

You need to install an appropriate JDBC (Java Database Connectivity) driver to run your Java database programs. The MySQL’s JDBC driver is called “MySQL Connector/J”.

  1. Download MySQL JDBC driver from www.mysql.com. Select top-level tab “Downloads” ? “Connectors” ? “Connector/J” ? 5.1.xx ? ZIP version (“mysql-connector-java-5.1.xx.zip”, 3.9MB).
  2. UNZIP the download file (into “d:\myproject“).
  3. Copy “mysql-connector-java-5.1.xx-bin.jar” to your JDK’s extension directory at “<JAVA_HOME>\jre\lib\ext” (e.g., “c:\program files\java\jdk1.7.xx\jre\lib\ext“).

(For Mac Users) Read “How to Install MySQL 5 on Mac” Step 5 on how to install MySQL JDBC Driver.

(For Advanced User Only) You can compile Java database programs without the JDBC driver. But to run the JDBC programs, the driver’s JAR-file must be included in the CLASSPATH, or the JDK’s extension directory. You could set the CLASSPATH via “Control Panel” ? System ? (Vista/7 only) Advanced system settings ? Switch to “Advanced” tab ? “Environment variables” ? Choose “System Variables” (for all users in this system) or “User Variables” (for this login user only) ? Choose “New” or “Edit”? In “Variable Name”, enter “classpath” ? In “Variable Value”, enter “.;path-to\mysql-connector-java-5.1.xx-bin.jar“, where “path-to” refers to the full path to the driver’s JAR-file. You could also include the CLASSPATH in the command-line “java -cp .;path-to\mysql-connector-java-5.1.xx-bin.jar JdbcProgramClassname” to run your program.

4.2  Writing a Java Database Program

A Java database program consists of the following steps:

  1. Allocate a Connection object to the database server.
  2. Create a Statement object inside the Connection.
  3. Write a SQL query and execute the query, via the Statement and Connection created.
  4. Process the query results.
  5. Close the Statement and Connection.

The Java program uses a so-called database-URL to connect to the server. The database-URL for MySQL is in the form of “jdbc:mysql://localhost:8888/studentdb“, where "localhost” (with IP address of 127.0.0.1) is the hostname for local loop-back; "8888” is the server’s TCP port number; and “studentdb” is the default database (equivalent to the MySQL command “use databaseName“). You need to provide the username and password as well.

Example 1: SQL CREATE TABLE, INSERT, DELETE, UPDATE – executeUpdate()

The following program creates a table called “class202” in the database “studentdb” (which has been created in the earlier exercise; otherwise, you need to create this database using the “mysql” client before running this program). It also issues INSERT, DELETE and UPDATE commands.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
import java.sql.*;                // Using JDBC package
 
public class MySQLCreateTest {          // Requires JDK 6 and above
   public static void main(String[] args) throws SQLException {
      Connection conn = null;  // Declare a Connection object called conn
      Statement stmt = null;   // Declare a Statement object called stmt
      try {
         // Step 1: Allocate a database "Connection"
         conn = DriverManager.getConnection( "jdbc:mysql://localhost:8888/studentdb", "myuser", "xxxx");  // <== check
         // database-url(protocol://hostname:port/defaultDatabase), userid, password
         // Use IP address 127.0.0.1 if localhost does not work!
 
         // Step 2: Allocate a "Statement" from the "Connection"
         stmt = conn.createStatement();
 
         // Step 3 & 4: Query and process the query result
         String sqlStr;   // SQL string
         int returnCode;  // return-code of the database server running the SQL statement
 
         // Remove table "class202" from the current database "studentdb"
         // (specified in the database-url)
         sqlStr = "drop table if exists class202";
         System.out.println(sqlStr);  // Echos the command for debugging
         returnCode = stmt.executeUpdate(sqlStr);  // Send to database server
         if (returnCode == 0) {       // Check the return code
            System.out.println("Result: Table 'class202' dropped");
         }
         System.out.println();
 
         // Create table "class202" in the current database "studentdb"
         sqlStr = "create table class202 (id int, name varchar(50), gpa float)";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         if (returnCode == 0) {
            System.out.println("Result: Table 'class202' created");
         }
         System.out.println();
 
         // Insert a few records
         sqlStr = "insert into class202 values (3333, 'Kumar', 4.4)";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println("Result: " + returnCode + " row(s) inserted");
 
         sqlStr = "insert into class202 values (4444, 'Kevin Jones', 4.6), (5555, 'Peter Jones', 4.3)";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println("Result: " + returnCode + " row(s) inserted");
         System.out.println();
 
         // Update record
         sqlStr = "update class202 set gpa=4.9 where id=5555";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println("Result: " + returnCode + " row(s) updated");
         System.out.println();
 
         // Delete record
         sqlStr = "delete from class202 where id=3333";
         System.out.println(sqlStr);  // echo command for debugging
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println("Result: " + returnCode + " row(s) deleted");
 
      } catch (SQLException ex) {
         ex.printStackTrace();
      } finally {
         // Step 5: Always close the resources
         if (stmt != null) stmt.close();
         if (conn != null) conn.close();
      }
   }
}

In the above example, we invoke the method executeUpdate() to run the DROP TABLE, CREATE TABLE, INSERT, UPDATE and DELETE commands. The executeUpdate() returns an int, indicating the result of the operation (e.g., number of rows affected).

Start a “mysql” client (as in the previous section) to verify the result of the above program:

> cd path-to-mysql-bin
> mysql -u myuser -p
password: xxxx
mysql> show databases;
......
mysql> use studentdb;
......
mysql> show tables;
......
mysql> select * from class202;
......
TRY:
  1. Insert a new record (8888, 'Tan Ah Teck', 4.4). Check the result via “mysql” client.
  2. Increase the GPA of 'Tan Ah Teck' by 0.1. Check the result via “mysql” client.
  3. Remove records with names containing “Jones”. Check the result via “mysql” client.
Example 2: SQL SELECT – executeQuery() and ResultSet
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
import java.sql.*;                // Using JDBC package
 
public class MySQLSelectTest {          // Requires JDK 6 and above
   public static void main(String[] args) throws SQLException {
      Connection conn = null;  // Declare a Connection object called conn
      Statement stmt = null;   // Declare a Statement object called stmt
      try {
         // Step 1: Allocate a database "Connection"
         conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:8888/studentdb", "myuser", "xxxx");  // <== check
         // database-url(protocol://hostname:port/defaultDatabase), user, password
         // Use IP address 127.0.0.1 if localhost does not work!
 
         // Step 2: Allocate a "Statement" from the "Connection"
         stmt = conn.createStatement();
 
         // Step 3 & 4: Query and process the query result
         String sqlStr;   // SQL string
 
         // Query the database
         sqlStr = "select id, name from class202";
         System.out.println(sqlStr);     // Echo the command for debugging
         ResultSet rset = stmt.executeQuery(sqlStr);  // Send to database server
         // Process the returned "ResultSet", i.e., resultant table.
         // The cursor initially points before the first record.
         while (rset.next()) {   // Move the cursor to the next record
            // Retrieve the fields for each record
            int id = rset.getInt("id");
            String name = rset.getString("name");
            System.out.printf("%4d %-30s%n", id, name);
         }
         rset.close();
 
      } catch (SQLException ex) {
         ex.printStackTrace();
      } finally {
         // Step 5: Always close the resources
         if (stmt != null) stmt.close();
         if (conn != null) conn.close();
      }
   }
}

We invoke the executeQuery() method (instead of executeUpdate() method) to run the SELECT command. The executeQuery() returns a ResultSet object, which stores the resultant table. The ResultSet is associated with a so-called cursor. The cursor initially points before the first record in the ResultSet. We use rset.next() method to advance the cursor to the next record, and rset.getXxx("columnName") to retrieve the field. rset.next() returns false if the cursor is pointing at the last record.

TRY:
  1. Select and display all the 3 columns. (Hints: retrieve the column via getFloat("gpa"), and modify your print statement to print the gpa.)
  2. Select records with names starting with letter 'K'. (Hints: name like 'K%')
  3. Select records with names having a letter 'J'. (Hints: name like '%J%')
  4. Select records with gpa ? 4.5. (Hints: gpa >= 4.5)
  5. Select records with names having a letter 'J' and gpa ? 4.5. (Hints: name like '%J%' AND gpa >= 4.5)

 

Read “Java Database Programming (JDBC) Basics” for more about Java Database programming.

(Skip Unless…) Read “Common Errors in JDBC Programming on MySQL“.

(For JDK prior to JDK 6) You need to explicitly load the MySQL driver in your program, before allocating a Connection.

try {
   // Load Database driver
   Class.forName("com.mysql.jdbc.Driver");         // for MySQL
// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  // for MS Access
} catch (ClassNotFoundException ex) {
   ex.printStackTrace();
}

(For Advanced Users Only) JDK 7: JDK 7 introduces a new try-with-resources syntax, which automatically closes the opened resources in the try clause. The above example can be re-written as follow:

import java.sql.*;                 // Use JDBC package
public class MySQLJdbcTestJDK7 {   // Need JDK 7 to compile and run
   public static void main(String[] args) {
 
      try (
         // Step 1: Create a database "Connection"
         Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost:8888/studentdb", "myuser", "xxxx");
               // Database-url(host, port, default database name), user, password
  
         // Step 2: Create a "Statement" from the "Connection"
         Statement stmt = conn.createStatement()
      ) {
         // Step 3 & 4: Query and Process the query result
         String sqlStr;   // SQL statement string
         int returnCode;  // return code of the database server running the SQL statement
 
         // Remove table "class202" from the current database "studentdb" 
         // (specified in the database-url)
         sqlStr = "drop table if exists class202";
         System.out.println(sqlStr);  // Echos the command for debugging
         returnCode = stmt.executeUpdate(sqlStr);  // Send to database server
         if (returnCode == 0) {  // Check the return code
            System.out.println("Table 'class202' dropped");
         }
         System.out.println();
 
         // Create table "class202" in the current database "studentdb"
         sqlStr = "create table class202 (id int, name varchar(50), gpa float)";
         System.out.println(sqlStr);
         returnCode = stmt.executeUpdate(sqlStr);
         if (returnCode == 0) {
            System.out.println("Table 'class202' created");
         }
         System.out.println();
 
         // Insert a few records
         sqlStr = "insert into class202 values (3333, 'Kumar', 4.4)";
         System.out.println(sqlStr);
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println(returnCode + " row inserted");
         sqlStr = "insert into class202 values (4444, 'Kevin Jones', 4.6)";
         System.out.println(sqlStr);
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println(returnCode + " row inserted");
         sqlStr = "insert into class202 values (5555, 'Peter Jones', 4.3)";
         System.out.println(sqlStr);
         returnCode = stmt.executeUpdate(sqlStr);
         System.out.println(returnCode + " row inserted");
         System.out.println();
 
         // Query the database
         sqlStr = "select * from class202";
         System.out.println(sqlStr);  // Echos the command for debugging
         ResultSet rset = stmt.executeQuery(sqlStr); // Send to database server
         // Process the returned "ResultSet", i.e., resultant table.
         // A cursor initially points before the first record.
         while (rset.next()) {   // Move the cursor to the next record
            // Retrieve the fields from each record
            int id = rset.getInt("id");
            String name = rset.getString("name");
            System.out.printf("%4d %-30s%n", id, name);
         }
      } catch (SQLException ex) {
         ex.printStackTrace();
      }
      // Step 5: Close the resources - done by try-with-resources automatically
   }
}

(For Advanced Users Only) UTF-8 Support: To request for UTF-8 support, use the following database-URL:

jdbc:mysql://hostname:port/studentdb?useUnicode=yes&characterEncoding=UTF-8

Your Eclipse/NetBeans should also be configured to use UTF-8 encoding.

5.  Backup and Restore Databases

5.1  Backup via “mysqldump” Utility Program

You can use the "mysqldump" utility program to back up the entire server (all databases), selected databases, or selected tables of a database. The “mysqldump” program generates a SQL script that can later be executed to re-create the databases, tables and their contents.

For example, the following command backups the entire “studentdb” database to a SQL script “d:\myproject\backup_studentdb.sql“.

-- Change the current working directory to <MYSQL_HOME>\bin
D:\myproject\mysql\bin> mysqldump -u myuser -p --databases studentdb > "d:\myproject\backup_studentdb.sql"

Study the output file, which contains CREATE DATABASE, CREATE TABLE and INSERT statements to re-create the database and tables dumped.

Syntax
-- Dump selected databases with --databases option
Prompt> mysqldump -u username -p --databases database1Name [database2Name ...] > backupFile.sql
-- Dump all databases in the server with --all-databases option, except mysql.user table (for security)
Prompt> mysqldump -u root -p --all-databases --ignore-table=mysql.user > backupServer.sql
  
-- Dump all the tables of a particular database
Prompt> mysqldump -u username -p databaseName > backupFile.sql
-- Dump selected tables of a particular database
Prompt> mysqldump -u username -p databaseName table1Name [table2Name ...] > backupFile.sql

5.2  Restore via “source” command in a mysql client

You can restore from the backup by running the “source” command in an interactive client. For example, to restore the studentdb backup earlier:

-- Start and login to client
D:\myproject\mysql\bin> mysql -u username -p
......
-- Run the backup script to recreate the database
mysql> drop database if exists studentdb;
mysql> source d:/myproject/backup_studentdb.sql

6.  (For Advanced Users) NetBeans and MySQL

NetBeans provides direct support to MySQL server. You can use NetBeans as a GUI client to access a MySQL server, as well as an administrative tool (e.g., starting and stopping the server).

Read NetBeans’ article “Connecting to a MySQL Database” @ http://netbeans.org/kb/docs/ide/mysql.html.

Configuring NetBeans to Support MySQL

From NetBeans “Window” menu ? Select “Services”. The “Services” tab shall appear on the left panel.

  1. Right-click on the “Databases” node ? “Register MySQL Server”. (If you have already registered a MySQL server, you can right-click on Server node “MySQL Server at hostname:port” ? Properties, to modify its properties.)
  2. Select the “Basic Properties” tab, enter the hostname, port number, root user and password.
  3. Select the “Admin Properties” tab:
    1. Leave the “Path/URL to admin tool” empty.
    2. In “Path to start command”, enter “<MYSQL_HOME>\bin\mysqld.exe“; in the “Arguments”, enter “--console“
    3. In “Path to stop command”, enter “<MYSQL_HOME>\bin\mysqladmin.exe“, in the “Arguments”, enter “-u root -ppassword shutdown“.
  4. A server node “MySQL Server at hostname:port” appears.
Database Administration – Start/Stop the Server and Create Databases
  1. You can start the MySQL server by right-clicking on the server node ? select “start”. [There seems to be a problem here. If a “connection refused: connect” error occurs, enter the password again.]
  2. Once the MySQL server is started and connected, you can see the list of databases by expanding the MySQL server node. You can create a new database by right-clicking on it and choose “Create Database…”.
Create a new Connection

You need a connection to manipulate data. You can create multiple connections with different users and default databases.

  1. Right-click on the “Databases” ? “New Connection…” ? Select the driver “MySQL Connector/J” ? Next ? Enter hostname, port number, default database, a general username and password ? “Test Connection” (make sure that MySQL is started) ? Finish.
  2. A connection node “jdbc:mysql://hostname:port/defaultDatabase” appears.
Manipulating Data via a Connection
  1. Right-click on a connection node (e.g., “jdbc:mysql://hostname:port/defaultDatabase“) ? Choose “Connect” (if not connected, provided that the MySQL server has been started).
  2. You can expand the connection node to view all the databases.
  3. Expand an existing database. There are three sub-nodes “Tables”, “View” and “Procedures”. Right-click on the “Tables” to create table or execute command. Similarly, right-click on the “View” and “Procedures”.
  4. To view/manipulate the records in a table, right-click on the selected table ? You can choose to “View Data…”, “Execute Command…”, etc.
  5. You can right-click on the connection to “connect” or “disconnect” from the server.
Create a SQL Script and Run the Script

You can create a SQL script by right-clicking on a project ? New ? “SQL File”. You can run the script by right-clicking on the SQL script ? “Run File” ? Select an existing connection (or create a new connection) to run the script. You could also run a single statement (right-click on the statement ? Run Statement) or a selected group of statements (highlight the statements ? Right-click ? Run Selection).

7.  (For Advanced Users) MySQL GUI Tool – MySQL Workbench

“MySQL Workbench (GUI tool)” provides a graphical user interface for interacting with the MySQL server. To install:

  1. From http://www.mysql.com/downloads/, select “MySQL Workbench (GUI Tool), Generally Available Release: 5.2.xx”. Download the ZIP version WITHOUT installer (e.g., “mysql-workbench-gpl-5.2.xx-win32-noinstall.zip”).
  2. UNZIP the downloaded files (into “d:\myproject“).

MySQL Workbench supports three main functions:

  1. MySQL Development: Connect to existing databases and run SQL queries, scripts, edit data and manage database objects.
  2. MySQL Server Administration: Configure your database server, setup user accounts, browse status variables and server logs.
  3. Data Modeling: Create and manage database models, forward and reverse engineering, compare and synchronize database schemas, and report.

To launch the MySQL Workbench, run “MySQLWorkbench.exe“, which can be found under the installed directory.

SQL Development
  • Select “Open connection to start querying”, enter “127.0.0.1” or “localhost” as hostname; “8888” as port number; “myuser” as username; the password; and “studentdb” as the default schema (default database).
  • Enter a SQL statement, and push the “execute” button.
Server Administration
  • “New Server Instance” ? “Localhost” ? Next ? Enter “localhost” as hostname; “8888” as port number; “root” as username; the password; and leave the default schema (default database) empty. You may need to specify the MySQL installed directory and the my.ini configuration file.
Data Modeling

To create the database diagram for an existing database (reverse engineering), select “Create EER model from existing database”, …

Read the MySQL Workbench documentation, available at http://dev.mysql.com/doc/workbench/en/index.html.

MySQL Workbench graphical tool, in my opinion, is not mature and is quite hard to use. There are others famous web-based MySQL graphical tools, such as “SQL Buddy” and “phpMyAdmin”. Unfortunately, they are written in PHP and need to be run under a web server (such as Apache). They are often used in a so-called WAMP (Windows-Apache-MySQL-PHP) or LAMP (Lunix-Apache-MySQL-PHP) environment.

NetBeans also provides a GUI interface to MySQL Server (described above).

8.  (For Advanced Users) Running MySQL as a Windows Service

In a production environment, it is more convenient to run MySQL as a Windows “service”. A Windows service is a background process, which does not interact with the users. It is called daemon in Unix. This is because a service can start automatically whenever the system is started, and re-start automatically after an unexpected interruption.

To install MySQL as a service, start a CMD shell (with administrator right) and run:

<MYSQL_HOME>\bin> mysqld --install
Service successfully installed.

A MySQL service named “mysql” would be installed and will start automatically whenever the system is started. Check “Control Panel” ? “Administrative Tools” ? “Services” ? Service “mysql“.

You can start the MySQL service via the “Control Panel” ? “Administrator Tools” ? “Services” ? Service “mysql” ? “Start”; or issue the following command from a CMD shell:

<MYSQL_HOME>\bin> net start mysql
The MySQL service is starting.
The MySQL service was started successfully.

You can stop MySQL service via the “Control Panel”; or issue the following command:

<MYSQL_HOME>\bin> net stop mysql
The MySQL service is stopping.
The MySQL service was stopped successfully.

To uninstall MySQL service, use:

<MYSQL_HOME>\bin> mysqld --remove
Service successfully removed.

Link to MySQL References & Resources

 

September 25th, 2012 | Category: MYSQL

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>