MySQL - QUB



[pic]

Introduction to MySQL for student projects

MySQL is a widely used open source relational database management system, developed on the client/server model. The MySQL client is an interactive command-line program that allows you to connect to a MySQL server, run queries and view results (it can also be used in batch mode). PhpMyAdmin is a web-based interactive client providing similar functionality in a much more user-friendly interface.

The SQL part of MySQL refers to “Structured Query Language” which is the most common language used to access and query databases. There is an abundance of information on the web relating to both MySQL and SQL. As a starting point please refer to and visit the Developer Zone.

Computer Science runs a shared MySQL server on a server named csweb2.cs.qub.ac.uk. It should be noted that this server is fire-walled by information Services and is not accessible from outside the University campus. We provide MySQL accounts for student projects on request. To access a MySQL account you will need a pre-assigned database name, username and a password. Please use the Computer Science Helpdesk at to request accounts and provide your student number and the name of your project supervisor.

This document is broken into three main sections.

1. Using the Web-based Interface to manage your database

A brief description of the main features of this interface is provided.

2. Using the command-line tools available on each machine in the Computer Labs

Because of the nature of the command-line tools a short set of tutorials are provided explaining how to use these to create, query and manage your database.

3. Connecting to a MySQL database from other programs such as a Java application.

A brief overview of how to connect a Java application to a MySQL database with example code is provided.

1. Using the Web-based Interface

a) Starting the MySQL phpMyAdmin interface

An intuitive web-based interface known as phpMyAdmin is available at

Open this URL in any web browser and you will be presented with the login screen.

[pic]

Log in with the username and password assigned to you by the system administrator.

b) The entrance screen

[pic]

This screen has a selectable list of the tables in your database in the left-hand column and the available view and interaction options in the main column.

c) Database management screen

[pic]

From the Databases option you can:

o add tables

o design and modify the structure of tables and data fields

o view, insert, update and delete data

o search the database

o prepare and execute an SQL query entered in a text field or loaded from a file

o build an SQL query using the GUI tool provided

o export data from your database

d) Logging out

It is important to logout when you are finished. Click on the “Home” link in the left-hand column. Then click on the “Log out” option on the main screen.

2. Using the command-line tools on the Computer Lab PCs

1. Getting Started

From the Start menu choose Programs > Accessories > Command Prompt.

You will have been given a username, a password and a database name. At the command prompt type either of the following commands:

mysql.bat yourusername

or

mysql –h csweb2.cs.qub.ac.uk –u yourusername -p

You will be prompted for a password. Once you have the mysql> prompt you are ready to send commands to the MySQL server to manage and display your database.

Changing your password

It is recommended that you change your password regularly for your own security. To do this type the following:

SET PASSWORD = PASSWORD(‘newpassword’);

And press Return.

2. Connect to your database

The final step before you can type your own SQL statements is to connect to the database. Type the following command

mysql> CONNECT databasename;

3. Displaying a table

mysql> DESCRIBE tablename

4. Running SQL commands

You can run the full range of SQL commands from the mysql prompt. This is what simple statement would look like.

mysql> SELECT * FROM tablename;

Note: When typing long statements you can spread them over multiple lines. MySQL determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. (In other words, MySQL accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.)

5. Running batch commands from textfiles

You can run mysql in batch mode. To do this, put the commands you want to run in a file, then type the following :

mysql> source filename;

There are good reasons for using a script If you run a query repeatedly (say, every day or every week), making it a script allows you to avoid retyping it each time you execute it.

3. Connecting to a MySQL database from other programs such as a Java application

When writing applications that connect to your MySQL database you use the database connectivity API provided by the programming environment to connect and send SQL statements to the database server. The first step is defining the connection string.

a) Defining the connection string

Java or J2EE (JSP/Servlets)

You can use the pre-installed JDBC database drivers.

The connection string takes the form of

jdbc:mysql://csweb2.cs.qub.ac.uk/MyDatabase?user=MyUsername&password=MyPassword

Microsoft .NET, or ASP

You can use the preinstalled ODBC drivers.

Driver={mySQL ODBC 3.51 Driver};Server=csweb2.cs.qub.ac.uk;Port=3306;Option=4;

Database=MyDatabase;Uid=MyUsername;Pwd=MyPassword;

PHP

Pass the MySQL server address and your account details to the appropriate methods:

$mysqli = new mysqli(‘csweb2.cs.qub.ac.uk’, ‘MyUsername‘,‘MyPassword‘);

$mysqli->select_db(‘MyDatabase‘);

b) A Java database connectivity example

import java.sql.*;

public class testconn {

public static void main(String [] args) {

String db = "yourDatabase";

String user = "yourUsername";

String pass = "yourPassword";

String connStr = "jdbc:mysql://csweb2.cs.qub.ac.uk/"

connStr += db + "?user=" + user + "&password=" + pass;

System.out.println("Testing MySQL Java Connection");

try {

// register the driver

Class.forName("com.mysql.jdbc.Driver").newInstance();

System.out.println("MySQL Driver loaded successfully");

try {

// open the connection

Connection conn = DriverManager.getConnection(connStr);

System.out.println("MySQL connection opened successfully");

Statement stmt = null;

ResultSet rs = null;

try {

stmt = conn.createStatement();

rs = stmt.executeQuery("SELECT * FROM person");

if (rs != null) {

System.out.println("Records were successfully retrieved");

}

else {

System.out.println("No resulting records were retrieved");

}

rs.close(); rs = null;

} catch (Exception e){

System.out.println("Could not execute query");

} finally {

stmt.close(); stmt = null;

conn.close(); conn = null;

}

} catch (SQLException ex) {

// handle any errors

System.out.println("SQLException: " + ex.getMessage());

System.out.println("SQLState: " + ex.getSQLState());

System.out.println("VendorError: " + ex.getErrorCode());

}

} catch (Exception ex) {

System.out.println("Exception: " + ex.getMessage());

}

} // end main

}

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download