MYSQL(CLASS 7)

Basic MySQL Commands
This document provides an introduction to the MySQL-client binary. Feel free to forward questions or clarification requests to portal@aero.und.edu.
· Login to a machine with mysql client capabilities
· ssh to shell
· login to a linux lab machine
The Short Answer
We recommend that all new MySQL users read the remainder of this document. For the impatient we will make this command available.
mysql -h mysql.aero.und.edu -p -u $USER
Linux Environment
Let's begin by exploring our local Linux environment as if we were lost in a dark room, and have to find the light. After all, your terminal session is white on black… so this aids in the effect :)
$ which mysql
/usr/bin/mysql
$ rpm -qa grep -i mysql
MySQL-python-0.9.1-6
MySQL-shared-standard-4.1.15-0.rhel3
libdbi-dbd-mysql-0.6.5-5
php-mysql-4.3.2-25.ent
perl-DBD-MySQL-2.1021-3
MySQL-client-4.1.15-0.glibc23
MySQL-devel-4.1.14-0.glibc23
$ rpm -qf `which mysql`
MySQL-client-4.1.15-0.glibc23
Let's find out what kind of system we're logged in to.
$ cat /etc/redhat-release
Red Hat Enterprise Linux AS release 3 (Taroon Update 5)
If we were inclined, we could download the appropriate package here http://dev.mysql.com/downloads/mysql/4.1.html.
MySQL Server -- Where art thou?
First let's investigate the local system shell.aero.und.edu.
$ grep mysql /etc/services
mysql 3306/tcp # MySQL
mysql 3306/udp # MySQL
When installed, the MySQL server runs on port 3306.
$ telnet localhost 3306
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused
No, MySQL is not running locally. This is confirmed with the following command.
$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
The MySQL server is running somewhere other than shell. Luckily we remember that our friendly System Administrators gave us the necessary information in the reply to our MySQL Account Request.
The Connection
We're left wondering, “How do we connect to the MySQL server at mysql.aero.und.edu?”
After investigating the manual page by running “man mysql” we see the following.
mysql(1) MySQL database mysql(1)

NAME
mysql - text-based client for mysqld, a SQL-based relational database
daemon

USAGE
mysql [OPTIONS] [Database]

SYNOPSIS
mysql [-B--batch] [-#--debug= logfile] [-T--debug-info] [-e--exec=
command] [-f--force] [-?--help] [-h--host= hostname]
[-n--unbuffered] [-p[pwd]] [--password=[pwd]] [-P--port= pnum]
[-q--quick] [-r--raw] [-s--silent] [-S--socket= snum] [-u--user=
uname] [-v--verbose] [-V--version] [-w--wait]

DESCRIPTION
The mysql program provides a curses-based interface to the SQL-based
database server daemon, mysqld(1). Full fuller documentation, refer to
the HTML documents installed with the package.
Here are the interesting “OPTIONS”
· -h (hostname)
· -p (password)
· -u (username)
mysql -h mysql.aero.und.edu -p -u caylan
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1266841 to server version: 4.1.12-standard

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

mysql>
Excellent!
Basic Queries
Show Databases
mysql> show databases;
+----------+
Database
+----------+
caylan
test
+----------+
2 rows in set (0.00 sec)
Use DB & Show Tables
We must “use” or “select” a database before we can go any further.
mysql> use caylan
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
Tables_in_caylan
+------------------+
notes
+------------------+
1 row in set (0.00 sec)

mysql> select * from notes;
+----+------------+-----------+
id date content
+----+------------+-----------+
1 2005-11-28 Test Note
4 2005-11-28 My Note
+----+------------+-----------+
2 rows in set (0.01 sec)
Simple Insert
mysql> insert into notes (id, date, content) values (null, now(), "Hello World");
Query OK, 1 row affected (0.00 sec)

mysql> select * from notes;
+----+------------+-------------+
id date content
+----+------------+-------------+
1 2005-11-28 Test Note
4 2005-11-28 My Note
7 2005-12-05 Hello World
+----+------------+-------------+
3 rows in set (0.00 sec)
Loading Schema from File
Sometimes a database schema is preserved in a file, and the user copy/pastes into their terminal with the mysql client open. Although this is fine in practice, it soon gets tedious. Enter the world of the command-line.
$ mysql -h mysql.aero.und.edu -p -u $USER caylan < my_schema.sql
Enter password:
drop table if exists notes;

CREATE TABLE `notes` (
`id` int(11) NOT NULL auto_increment,
`date` date default NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into notes (id, date, content) values (null, now(), "Test Note");
insert into notes (id, date, content) values (null, now(), "Test Sioux");
insert into notes (id, date, content) values (null, now(), "Sioux Note");
insert into notes (id, date, content) values (null, now(), "My Note");
Additional Reading