MySQL - Part 1 - Step by Step

Start - Stop - Restart MySQL Server on Linux 

sudo  /etc/init.d/mysqld  stop

sudo  /etc/init.d/mysqld  start

sudo  /etc/init.d/mysqld  restart

Start the MySql Database

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld

Listing the Databases/schemas available using root user.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlshow -u root

+--------------------+

Databases

+--------------------+

information_schema

mysql

test

+--------------------+

Here three Databases/schemas are available as seen listed above.

Listing content of Schema test, by the command below.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlshow -u root test

Database: test

+--------+

Tables

+--------+

+--------+

Its not having any table.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlshow -u root information_schema

Database: information_schema

+---------------------------------------+

Tables

+---------------------------------------+

CHARACTER_SETS

COLLATIONS

COLLATION_CHARACTER_SET_APPLICABILITY

COLUMNS

COLUMN_PRIVILEGES

ENGINES

EVENTS

FILES

GLOBAL_STATUS

GLOBAL_VARIABLES

KEY_COLUMN_USAGE

PARTITIONS

PLUGINS

PROCESSLIST

PROFILING

REFERENTIAL_CONSTRAINTS

ROUTINES

SCHEMATA

SCHEMA_PRIVILEGES

SESSION_STATUS

SESSION_VARIABLES

STATISTICS

TABLES

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TRIGGERS

USER_PRIVILEGES

VIEWS

+---------------------------------------+

A lot of tables are available in information_schema.

Selecting the status of MySql Database on your machine

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqladmin version status proc

mysqladmin Ver 8.42 Distrib 5.1.31, for Win32 on ia32

Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

Server version 5.1.31-community

Protocol version 10

Connection localhost via TCP/IP

TCP port 3308

Uptime: 15 min 10 sec

Threads: 1 Questions: 31 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.34

Uptime: 910 Threads: 1 Questions: 31 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second

avg: 0.34

mysqladmin: process list failed; error: 'Access denied; you need the PROCESS privilege for this operation'

Using test database.

Open test database

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql test

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 21

Server version: 5.1.31-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Create table shop in test database.

mysql> CREATE TABLE shop (

-> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,

-> dealer CHAR(20) DEFAULT '' NOT NULL,

-> price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,

-> PRIMARY KEY(article, dealer));

Query OK, 0 rows affected (0.03 sec)

Insert records in shop table

mysql> INSERT INTO shop VALUES

-> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),

-> (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

Query OK, 7 rows affected (0.03 sec)

Records: 7 Duplicates: 0 Warnings: 0

Select records from shop table.

mysql> select * from shop;

+---------+--------+-------+

article dealer price

+---------+--------+-------+

0001 A 3.45

0001 B 3.99

0002 A 10.99

0003 B 1.45

0003 C 1.69

0003 D 1.25

0004 D 19.95

+---------+--------+-------+

7 rows in set (0.00 sec)

mysql>exit

Listing tables present in test database. The shop table created above.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlshow test

Database: test

+--------+

Tables

+--------+

shop

+--------+

MySQL - Part 2 - with JDBC

Logging in with root user to creat database and new user

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql --user=root mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 28

Server version: 5.1.31-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Create Database E_COM_DB

mysql> CREATE DATABASE E_COM_DB;

Query OK, 1 row affected (0.02 sec)

Create user and giving the Grants.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON E_COM_DB.*

-> TO 'ECOMDEV1'@'LOCALHOST' IDENTIFIED BY 'ECOMDEV1';

Query OK, 0 rows affected (0.00 sec)

mysql> \q

Bye

Loggin into the DB MY_SQL_DB with user ECOMDEV1(-uECOMDEV1) and its password (-pECOMDEV1) ECOMDEV1
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uECOMDEV1 -pECOMDEV1  E_COM_DB

Critical steps in using JDBC

There are five critical steps in using JDBC to manipulate a database:

1. Load and register the JDBC driver classes (programming interface) for the database server that you intend to use.

2. Get a Connection object that represents a connection to the database server (analogous to logging onto the server).

3. Get one or more Statement objects for use in manipulating the database.

4. Use the Statement objects to manipulate the database.

5. Close the connection to the database.

Getting JDBC Driver for mysql. Formally konw as Connector/J 5.1.

Use the link below to download for windows.

http://mysql.oss.eznetsols.org/Downloads/Connector-J/mysql-connector-java-5.1.7.zip

Extract the zip file. mysql-connector-java-5.1.7-bin.jar will be contained in zip. Put the jar file in classpath.

Exceute the main method of the class below to do the same operations as given in the bolg entry above.

package com.spring.test.mysql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class Jdbc11 {

public static void main(String args[]){

System.out.println(

"Copyright 2004, R.G.Baldwin");

try {

Statement stmt=null;

//Register the JDBC driver for MySQL.

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

//Define URL of database server for

// database named mysql on the localhost

// with the default port number 3306.

String url = "jdbc:mysql://localhost:3308/mysql";

//Get a connection to the database for a

// user named root with a blank password.

// This user is the default administrator

// having full privileges to do anything.

Connection con = DriverManager.getConnection(url,"root", "");

//Display URL and connection information

System.out.println("URL: " + url);

System.out.println("Connection: " + con);

//Get a Statement object

stmt = con.createStatement();

//Create the new database

stmt.executeUpdate("CREATE DATABASE E_COM_DB");

//Register a new user named auser on the

// database named JunkDB with a password

// drowssap enabling several different

// privileges.

stmt.executeUpdate(

"GRANT SELECT,INSERT,UPDATE,DELETE," +

"CREATE,DROP " +

"ON JunkDB.* TO 'ECOMDEV1'@'localhost' " +

"IDENTIFIED BY 'ECOMDEV1';");

con.close();

}catch( Exception e ) {

e.printStackTrace();

}//end catch

}//end main

}//end class Jdbc11

Refer the link below for more information. Info above are extracted for this link only.

http://www.developer.com/java/data/article.php/3417381