Server Topic
   >  Introduction to Webservers
   >  Installing the Webserver
   >  Controlling the Webserver
   >  Installing PHP Support
   >  Using PHP
   >  Installing mySQL Database
   >  Using mySQL

 

Using mySQL under Ubuntu Linux

Introduction

There are many sites out there devoted solely to using mySQL, such as:

As a result, we shall leave you to explore the above links and will instead attempt to give you the briefest of overviews on how to use mySQL, so you can see what setting up and using a database entails.


Running mySQL Interactively

Until you are familiar with the SQL commands, it may be beneficial to run mySQL interactively, so you can see and correct any problems as you go. To do this, simply type:

Until you are familiar with the SQL commands, it may be beneficial to run mySQL interactively, so you can see and correct any problems as you go

mysql -u root -p

You will be asked for the password you entered during the installation. If all goes well, you will be left at the mysql> prompt:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.1.49-1ubuntu8.1 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

You can then interactively type in SQL commands (-terminated by a semi-colon) and any output will be written to your screen.


Running mySQL in Batch Mode

Once they start to get more familiar with SQL commands, most users tend to write their SQL commands directly into a text file and then invoke mySQL redirecting the mySQL input to this file, for example:

mysql -u root -p < <SQL command file>

This allows them to edit and re-run the commands much more easily. For example, to execute the SQL commands in the file "mySQL.txt", you might type:

$ mysql -u root -p < mySQL.txt
Enter password: 
$

MySQL will execute the SQL in the file, write any output (-or errors) to the screen, then exit back to the command line once the file has been processed. Here is a simple database creation script, that could be written into such a text file:

CREATE DATABASE myDB;
USE myDB;

DROP TABLE IF EXISTS myTable;
CREATE TABLE myTable (
	keyID	          integer unsigned PRIMARY KEY auto_increment,
	shortDesc        varchar (100),
	mySeq             integer unsigned,
              myType           char
);

CREATE USER 'myuser'@'localhost' IDENTIFIED BY ' <password>';
GRANT SELECT ON *.* to 'myuser'@'localhost';

INSERT INTO myTable VALUES (1, 'My first entry', 100, 's');
SELECT * from myTable;

This creates a new database called "myDB", with a user called 'myuser' (-on the local server) with read access to all tables. The database itself has a single table (-collection of fields) called "myTable", containing four attributes (-fields) of varying types.

The penultimate line (INSERT), inserts one row into "myTable", containing the values:

  • keyID = 1
  • shortDesc = 'My first entry'
  • mySeq = 100
  • myType = 's'

The final line (SELECT), selects all columns for all rows (-of course, we only inserted one row) in the "myTable" table and lists them on the screen.


HomeSite IndexDesktop GuideServer GuideHints and TipsHardware CornerVideo SectionContact Us

 sitelock verified Firefox Download Button