Using mySQL under Ubuntu Linux
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.
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
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.
You can then interactively type in SQL commands (-terminated by a semi-colon) and any output will be written to your screen.
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
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;
DROP TABLE IF EXISTS myTable;
CREATE TABLE myTable (
keyID integer unsigned PRIMARY KEY auto_increment,
shortDesc varchar (100),
mySeq integer unsigned,
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.