MySQL Reference Book

Before you begin reading my code please take note of the following conventions.
Wherever I expect you to replace my generic code with your own specific code I have placed square brackets around the term, such as this [replaceMe]. Also please note that it is conventional to place SQL commands in upper-case letters to distinguish those terms from your input.


Ubuntu Commands:

Start MySQL server:

mysql -u [user_name] -p

SQL Commands:

Show all databases on server:

SHOW DATABASES;

Delete a database:

DROP DATABASE [database_name];

Note this function will return the number of tables that were removed. This corresponds to the number of .frm files removed.

Select database for use:

USE [database_name];

Show tables in a database:

SHOW TABLES;

Show a table's schema:

DESCRIBE [table_name];

Add a column to an existing table:

ALTER TABLE [table_name] ADD COLUMN [column_name] varchar(255); 

Note in this case the varchar(255) data type can be replaced by any valid SQL data type. Also further restrictions can be placed after the data type just like in a CREATE statement for foreign keys, NOT NULL, etc...

Java and MySQL
In order to connect your Java program to a MySQL database there are a couple of things you need to handle first. Obviously you need a MySQL database server to be running somewhere, locally or remotely. Next you will need the MySQL driver mysql-connector-java-5.1.14-bin.jar file to be included in the class path of your Java project. Please take care that you don't skip the last step and you will never be able to achieve a connection to the database without the driver properly installed, and most unfortunately the compiler will not return any meaningful errors other than a connection to the database is not possible. Next you'll need some Java code which I provide below and a table in the database which you can query. Here is a sample program that connects to a database called taskManager and performs a query to return all of the data from the singleTask table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


public class MysqlDriver {
 
 public static void main (String[] args) {
  MysqlDriver driver = new MysqlDriver();
 }
 
 private String db_path = "localhost:3306";
 private String db_name = "taskManager";
 private String db_user = "root";
 private String db_pass = "secret";
 private String db_url = "jdbc:mysql://"+db_path+"/"+db_name;
 
 public MysqlDriver() {
  
  try {
   Class.forName("com.mysql.jdbc.Driver");
   Connection db = DriverManager.getConnection(db_url, db_user, db_pass);
    System.out.println("Connected to the database");
   
   String statement = "SELECT * FROM singleTask";
   PreparedStatement db_statement = db.prepareStatement(statement);
   ResultSet result_set = db_statement.executeQuery();
   
   while(result_set.next()) {
    String oid = result_set.getString("oid");
    String desc = result_set.getString("description");
    System.out.println("ObjectID: "+oid+"\tDescription: "+desc);
   }
   
  }
  catch(Exception e) {
   System.out.println("We could not connect to the database located at: " + db_path);
  }
 }
 
}

References:

- LAMP Setup on Ubuntu
- MySQL and JDBC
- MySQL Command Refernce
- SQL Syntax Index