MySQL is a database package which uses SQL (Structured Query Language). There are many other database packages that use SQL; MySQL happens to be a robust and popular open-source choice for small-to-medium-sized applications (e.g., smaller than Amazon or eBay). SQL itself is a powerful language for manipulating data; the details of the language are outside the scope of this howto, although a few very basic examples are given below.
There are two major components: The database server, and the client. The client is what you use at the command line to interact with databases directly. There are also a variety of command-line utilities to do common tasks, such as mysqladmin, but there is nothing that these utils do that you can't do manually with the regular "mysql" client program. It's just much easier and less error-prone to use the utilities.
Programs such as CGI scripts use their own libraries/APIs to interact with MySQL databases. A commonly-used one is the Pear DB libraries, available for Perl and PHP. PHP has a large library of built-in MySQL functions which have, IMHO, cleaner syntax than their DB equivalents. But the Pear stuff is nice for porting Perl scripts to PHP without doing a huge amount of rewriting.
MySQL has its own internal structure and permissions model, completely separate from the server on which it runs. The details of which users can access which databases are stored in the "mysql" database; in other words, MySQL keeps track of itself by using a database within itself. The MySQL "root" user can do anything, including modify the permissions for any and all databases. This has nothing to do with the actual server's root user (in the case of Unix). Anyone with the mysql root password can connect to the mysql server regardless of whatever privilege limitations he or she has on within the machine's own permissions scheme.
The structure of MySQL can be thought of as having layers, as follows:
It's like a 3D (or 4D?) spreadsheet. The results of SQL queries are displayed like a spreadsheet, where each entry of a table is one row, and the fieldnames are the column headings.
Installing
Install from precompiled binary packages (e.g. RPMs) whenever possible. The MySQL RPMs for RedHat Linux, and the .pkgs from Mark Liyanage (see refs) for OS X, do a lot of post-installation setup and configuration that is tedious to do by hand, and those particular steps are important for basic security and functionality.
The minimum required components are the mysql-server package, the plain mysql (client) package, and the "mysql-common" or "shared" package. Install them simultaneously with one RPM command. If you will be building scripts to run against the database, in Perl or PHP for example, then you will also need to install the appropriately-named bridging packages (e.g. "mysql-php".)
There are a variety of freely-available online resources describing the installation process in great detail, and also how to install from source if you cannot use precompiled binaries. At a bare minimum, you will need to do three post-installation steps:
/etc/init.d/mysql startOlder versions may be "mysqld" instead of "mysql".
You should see "mysqld-safe" when you do a subsequent process listing; if not, consult the manual (see refs).
mysqladmin -u root password 'sekrit' mysqladmin -u root -h hostname password 'sekrit'You need to run both of these. The first is for local (non-socket) connections, and the second is for TCP connections.
/etc/init.d/mysql restart
Connecting
Start the mysqld server if you haven't already. From the command line:
mysql -u root -p
Note: If you need to embed mysql commands in a script and you're not using an API (e.g. you're writing a simple bash shell script), you can pass the mysql password as part of the command by entering it right after the -p, no spaces. Like so:
mysql -u root -pMySecretPassword databasenameFor more info about executing queries from a shell script, see below.
Some notes about the mysql "shell":
How databases are stored
On a standard Linux installation, each database is represented by a directory under /var/lib/mysql/databasename/ and each table of the database has three associated files in that directory. Each of the three files is a binary-format file. You CAN back up a database by manually copying the contents of its directory, but this is a poor idea in the long run, since different versions of MySQL may use different binary formats and you could run into a compatibility problem if you work with raw copies. The best way to back up a database and/or its contents is with the mysqldump utility (which you run as a separate program from your regular command shell, NOT from within mysql itself). Type mysqldump --help for a plethora of options. The most basic use of it is like this:
$bash> mysqldump -u root -p databasename > database-backup.dump (enter password)The resulting file will be a plain-text file with the original "create table" statement, and all the data, formatted within INSERT statements. This dumpfile can be read back in to the regular mysql client, to populate another database (or restore a lost database):
mysql -u root -p newdatabase < database-backup.dump (enter password)
NOTE: If you need to dump a MySQL 4.1 database in such a way as to make it compatible with an older MySQL 3.23 database, do it like this:
mysqldump --compatible=mysql323 -u root -p databasename > database-backup.dump
SQL Overview
Most (pretty much all) sql commands have to end with a semicolon.
When you first connect, type
mysql> show databases;The output will look something like this:
+----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.06 sec)At minimum you should see a database called "mysql", and you will probably also see a database called "test". DO NOT USE OR DROP THE "mysql" DATABASE, doing so can render the MySQL server nonfunctional. The "test" database is there for learning how to use MySQL; you can't hurt anything by messing with test, its default permissions are to allow anyone to connect and use it from anywhere with no password, and you can even drop (delete) it if you want.
To select a database to use, type
mysql> use nameofdatabase;It should say "database changed".
Now to see what tables are in that database:
mysql> show tables;(The "test" database will not have any tables to start with; you will get "empty set" as the output if a database has no tables.)
The rest of this SQL information is pretty moot without some actual data. Therefore, we're going to create some real quick, and not really discuss the details right now, so that we can talk about queries and syntax for awhile.
create table myFirstTable (
id int(11) not null default 0 auto_increment primary key,
name varchar(255) default 'blank',
dateEntered datetime,
favColor enum('red','blue','green','yellow')
);
If you want, you can copy and paste that create statement into your mysql client. (Make sure to include the final closing paren and semicolon.) We'll use this table for the rest of these examples.
To find out about the structure of a table, type
mysql> describe tablename;This will give you field headings and their types (whether they're integers, or text, or date types, or whatever) of the table called tablename. There are a trillion zillion data types in mysql, you really only need about 10 of those IMHO. Here's what we get with the myFirstTable example:
mysql>describe myFirstTable;
+-------------+-------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------------------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | blank | |
| dateEntered | datetime | YES | | NULL | |
| favColor | enum('red','blue','green','yellow') | YES | | NULL | |
+-------------+-------------------------------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)
Now we'll cram some data in there...
mysql> insert into myFirstTable values ('','Joe',now(),'red');
Query OK, 1 row affected (0.03 sec)
mysql> insert into myFirstTable values ('','Sam',now(),'blue');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myFirstTable values ('','Beth',now(),'red');
Query OK, 1 row affected (0.00 sec)
mysql> insert into myFirstTable values ('','Tom',now(),'green');
Query OK, 1 row affected (0.00 sec)
Note carefully the use of single quotes in these INSERT statements. Because the first field of this sample database table is an auto-increment, I want the database to automatically assign in the next available id number (starting with 1 unless you specify otherwise), so that's why the first item in my parenthesized list is just a pair of single quotes; to mysql, this means "I'm not explicitly giving you data for this field, so you fill it in with the default value." In other words, the next id increment. Also note the third data item -- "now()" with no quotes around it. That's because now() is a built-in MySQL function. We don't want to literally insert the word "now" with parens into the dateEntered field, we want MySQL to look at its clock and stamp the record with that timestamp.
To see what's in an actual table, do:
mysql> select * from tablename;The * is a wildcard. This will give you all the contents of all the fields in tablename. With myFirstTable:
mysql> select * from myFirstTable; +----+------+---------------------+----------+ | id | name | dateEntered | favColor | +----+------+---------------------+----------+ | 1 | Joe | 2004-11-24 15:11:51 | red | | 2 | Sam | 2004-11-24 15:11:58 | blue | | 3 | Beth | 2004-11-24 15:12:05 | red | | 4 | Tom | 2004-11-24 15:12:13 | green | +----+------+---------------------+----------+ 4 rows in set (0.00 sec)If a table has a lot of data, this will scroll forever and annoy you, so you can alter it this way:
mysql> select * from tablename limit 10;this will show you only 10 of them (it will randomly choose which 10). If you don't want just any 10, alter the statement further:
mysql> select * from tablename order by id limit 10;NOTE: This assumes that the table has a field called "id" which contains an incrementing number (sort of like a line number). Most sql tables have this because it's the only thing you can guarantee to be unique, and which can therefore be used as a "key" in some other table entry in a relational database.
You can "order by" any field -- if that field contains a number, it will show results in numerical order, and if it contains words, it will go alphabetical etc. If your table has such an id field, and you want to see the most recent 5 entries, you'd do:
mysql> select * from tablename order by id desc limit 5;Note the word desc -- "descending" -- to reverse sort. This kind of query is useful to see the most recent entries when I'm testing stuff.
The order of words in sql queries usually needs to be exact. In other words, this would not work:
mysql> select * from tablename limit 10 order by id;It's very picky about syntax. "order by" comes before "limit."
If you don't want to see _all_ the fields of a table, but just some of them or one of them, do:
mysql> select fieldname from tablename limit 10 order by id; mysql> select fieldname,fieldname2,fieldname3 from tablename limit 10 order by id;
To select things based on matching something (which is where this stuff actually becomes useful), the format of the queries is:
mysql> select * from tablename where fieldname = 'somethingtomatch';The thing you're searching for (in this case, somethingtomatch) needs to be in single quotes most of the time. There are some exceptions, but in general, if you use single quotes, it will work.
Searching with "select":
If I want to see the entries for people whose names contain the letter E:
mysql> select * from myFirstTable where name like '%e%'; +----+------+------+--------------+ | id | name | age | dateRecorded | +----+------+------+--------------+ | 1 | Joe | 25 | 2004-11-24 | | 3 | Beth | 25 | 2004-11-24 | +----+------+------+--------------+ 2 rows in set (0.00 sec)% is the wildcard for matching strings, not to be confused with * for returning all fields. Also Note the use of "like" instead of "=" for wildcard matches.
If I want to see the entries for people whose names contain the letter B, AND whose age is over 25:
mysql> select * from myFirstTable where name like '%B%' and age > 25; +----+------+------+--------------+ | id | name | age | dateRecorded | +----+------+------+--------------+ | 5 | Bill | 30 | 2004-11-24 | +----+------+------+--------------+ 1 row in set (0.00 sec)
Altering a table structure:
mysql> alter table tablename add column columnname columntype [position of column, optional, default is last];Adding a column to myFirstTable:
mysql> alter table myFirstTable add column age int(3) after name;
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> describe myFirstTable;
+-------------+-------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------------------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | blank | |
| age | int(3) | YES | | NULL | |
| dateEntered | datetime | YES | | NULL | |
| favColor | enum('red','blue','green','yellow') | YES | | NULL | |
+-------------+-------------------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select * from myFirstTable;
+----+------+------+---------------------+----------+
| id | name | age | dateEntered | favColor |
+----+------+------+---------------------+----------+
| 1 | Joe | NULL | 2004-11-24 15:11:51 | red |
| 2 | Sam | NULL | 2004-11-24 15:11:58 | blue |
| 3 | Beth | NULL | 2004-11-24 15:12:05 | red |
| 4 | Tom | NULL | 2004-11-24 15:12:13 | green |
+----+------+------+---------------------+----------+
4 rows in set (0.04 sec)
Updating data in a table:
mysql> update tablename set columnname = newvalue where somecolumn = somecondition;For example, let's say I want to designate all the people who love red as 25-year-olds:
mysql> update myFirstTable set age = '25' where favColor = 'red'; Query OK, 2 rows affected (0.08 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from myFirstTable; +----+------+------+---------------------+----------+ | id | name | age | dateEntered | favColor | +----+------+------+---------------------+----------+ | 1 | Joe | 25 | 2004-11-24 15:11:51 | red | | 2 | Sam | NULL | 2004-11-24 15:11:58 | blue | | 3 | Beth | 25 | 2004-11-24 15:12:05 | red | | 4 | Tom | NULL | 2004-11-24 15:12:13 | green | +----+------+------+---------------------+----------+ 4 rows in set (0.05 sec)
Changing a column type/structure:
mysql> alter table myFirstTable modify column dateEntered date; Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from myFirstTable; +----+------+------+-------------+ | id | name | age | dateEntered | +----+------+------+-------------+ | 1 | Joe | 25 | 2004-11-24 | | 3 | Beth | 25 | 2004-11-24 | | 5 | Bill | 30 | 2004-11-24 | +----+------+------+-------------+ 3 rows in set (0.00 sec)
Changing just the NAME of a column:
mysql> alter table myFirstTable change column dateEntered dateRecorded date; Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from myFirstTable; +----+------+------+--------------+ | id | name | age | dateRecorded | +----+------+------+--------------+ | 1 | Joe | 25 | 2004-11-24 | | 3 | Beth | 25 | 2004-11-24 | | 5 | Bill | 30 | 2004-11-24 | +----+------+------+--------------+ 3 rows in set (0.00 sec)Note three pitfalls:
Deleting records:
mysql> delete from tablename where condition;To get rid of anyone who does NOT like red:
mysql> delete from myFirstTable where favColor != 'red'; Query OK, 2 rows affected (0.00 sec) mysql> select * from myFirstTable; +----+------+------+---------------------+----------+ | id | name | age | dateEntered | favColor | +----+------+------+---------------------+----------+ | 1 | Joe | 25 | 2004-11-24 15:11:51 | red | | 3 | Beth | 25 | 2004-11-24 15:12:05 | red | +----+------+------+---------------------+----------+ 2 rows in set (0.00 sec)Note that the id of Beth's record did NOT get renumbered, even though hers is now the second record. Ids 2 and 4 will NOT be reused unless you override the auto-increment by explicitly including an id number in your insert (or update) statement. Notice what happens when we now add a new record, letting the auto-increment do its thing:
mysql> insert into myFirstTable values ('','Bill','30',now(),'yellow');
Query OK, 1 row affected (0.00 sec)
mysql> select * from myFirstTable;
+----+------+------+---------------------+----------+
| id | name | age | dateEntered | favColor |
+----+------+------+---------------------+----------+
| 1 | Joe | 25 | 2004-11-24 15:11:51 | red |
| 3 | Beth | 25 | 2004-11-24 15:12:05 | red |
| 5 | Bill | 30 | 2004-11-24 15:26:30 | yellow |
+----+------+------+---------------------+----------+
3 rows in set (0.00 sec)
Deleting an entire column:
mysql> alter table myFirstTable drop column favColor; Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from myFirstTable; +----+------+------+---------------------+ | id | name | age | dateEntered | +----+------+------+---------------------+ | 1 | Joe | 25 | 2004-11-24 15:11:51 | | 3 | Beth | 25 | 2004-11-24 15:12:05 | | 5 | Bill | 30 | 2004-11-24 15:26:30 | +----+------+------+---------------------+ 3 rows in set (0.00 sec)Deleting an entire table:
drop table myFirstTable;Deleting an entire database:
drop database test;
MySQL and shell scripts:
mysql -u root -p"$MYSQLPASS" mydatabase <<EOF (query) EOFKey things to note:
mysql -u root -p"$MYSQLPASS" mydatabase <<EOF update sometable set somefield = '$VARIABLE' where someotherfield = '$ANOTHERVARIABLE' EOFI'm not sure about escaping special characters (single quotes, semicolons, asterisks, and percent signs are all special to mysql), but it probably involves the backslash.
To assign/use the return value of a MySQL query:
RUNIDGRAB=`mysql -u root -p"$MYSQLPASS" databasename <<EOF
select max(id) from tablename
EOF
` # This trailing backtic is needed here...do not delete
# Strip out the rest of mysql's answer, we just want the number
REALRUNID=`echo $RUNIDGRAB | awk '{ print $2 }'`
Obviously, you'd need to do some trickier parsing if you were selecting several fields or rows, and the parsing specifics would depend on the structure and content of the database.
That's just barely scratching the surface. You can do some really intricate select statements with multiple matches and fields, put them into a temporary scratch table so they come back in the right format, etc. and that's where the real power of SQL is. But this is a start.
References: