MySQL
Data Operations in MySQL
for more information on MySQL
For more info and tutorials on mysql, see:
http://www.mysql.com
or
http://mysql.linuxwired.net (a mirror)
or
http://www.tcx.se
Warning: above sites document the current version of MySQL, which
is not accurate for the Skunkware 98 version of MySQL.
How to view the user manual accurate for the Skunware 98 version of MySQL
After installing MySQL as described below,
use a browser to view:
/usr/local/mysql/manual_toc.html which points into
/usr/local/mysql/manual.html
You can view a text file version of the manual
/usr/local/mysql/manual.txt
Another way to view the text file version
man mysql
Here are some books on MySQL:
MySQL by Paul DuBois, 2000 by New Riders Publishing
ISBN 0-7357-0921-1
MySQL & mSQL by Randy Yarger, George Reese, Tim King
1999 by O'Reilly & Assoc
ISBN 1-56592-434-7
When is MySQL free?
MySQL is free when you use it on your own UNIX system.
If you sell a product or service that involves MySQL, see
manual chapter 3: Licensing or When do I have/want to pay for MySQL?
Installation from SCO Skunkware 98 cdrom
Ignore this chapter of the manual:
4 Compiling and installing MySQL
because Skunkware 98 alreadys contains a version of MySQL
already compiled for SCO
Use custom to install mysql from the Skunkware 98 cd-rom
Afterwards custom will not show that you loaded it but
swconfig will. custom will show a Skunkware 98 item. If
you expand it, it will show all of the Skunkware packages
even though they have not all been loaded.
All the MySQL files will be installed into:
/usr/local/mysql
To start mysql manually:
/usr/local/mysql/bin/mysql.server start
If several users use mysql, make sure the same user starts
mysql each time. Better still, automatically start mysql
daemon on every boot as described below.
To test that mysql is running: (from any account)
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.21.26-gamma-log
Type 'help' for help.
mysql> quit
Bye
$
Set (or change) the mysql root password:
$ mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD('manager')
-> WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> quit;
$
Expect an error on FLUSH PRIVILEGES since that command is only
available on later versions of MySQL. If it does give an error,
then run this command to make the change effective (or reboot
UNIX)
$ mysqladmin -uroot reload
$
If root previously had a password, use this syntax:
$ mysqladmin -uroot -p reload
Enter password: *******
$
Important notes on above:
1. When you first install mysql, anyone can run mysql -u root since
no password is set for root. You should set a mysql root
password right away!
3. Change manager above to your desired root password.
4. You must also modify the mysql.server file like this:
|
Enter the root password in the mysql.server script:
cd /usr/local/mysql/bin
cp -ip mysql.server mysql.server.org
# say no if cp asks to overwrite the destination
# Choose a different destination name.
vi mysql.server
# find these lines
'stop')
# Stop deamon
$bindir/mysqladmin shutdown
;;
|
# add -prootpassword like this:
|
'stop')
# Stop deamon
$bindir/mysqladmin -pmanager shutdown
;;
To stop mysql manually:
/usr/local/mysql/bin/mysql.server stop
(although the doc implies this step is not really necessary
before a shutdown since mysql.server will stop when it receives
a TERM signal from shutdown.)
To automatically start mysql daemon on every boot as follows:
cp -ip /usr/local/mysql/bin/mysql.server /etc/init.d/.
cd /etc/rc2.d
ln -s /etc/init.d/mysql.server S99mysql
cd /etc/rc0.d
ln -s /etc/init.d/mysql.server K20mysql
In general, it would be better if you ran the mysql server
daemon as a non-root user. You would have to make all the mysql
files owned by that user and use an su command to start the
mysql server from that account. MySQL 3.22 supports a --user
option so that mysql can be more easily started as a non-root
user, however the current (as of 6/2000) Skunkware MySQL is
only rev 3.21.26.
How to check that mysql started correctly at the last reboot:
# cd /etc/rc2.d/messages
# cat S99mysql.log
Starting mysqld demon with databases from /opt/K/SKUNK98/Mysql/3.21.26/usr/local/mysql/data
#
Note that successfully stopping the mysql server produces no messages
so /etc/rc0.d/messages/K20mysql.log should be empty
Some background info on how the MySQL server daemon runs
MySQL uses a client/server model. Clients send all requests to a
MySQL server daemon, which is started by S99mysql as created
above:
# ps -ef|grep mysql
root 660 1 0 04:16:31 ? 00:00:00 /usr/local/mysql/bin/safe_m
ysqld -l
root 676 660 0 04:16:31 ? 00:00:00 /opt/K/SKUNK98/Mysql/3.21.26/usr/local/mysql/bin/mysqld --basedir=/opt/K/SKUNK9
The MySQL server daemon listens on tcp port 3306 by default.
This tcp port is not listed in /etc/services.
netstat -a will show the following lines related to mysql:
Active Internet connections (including servers)
Proto Recv-Q Send-Q Local Address Foreign Address (state)
...
tcp 0 0 *.3306 *.* LISTEN
...
Active UNIX domain sockets
Address Type Recv-Q Send-Q Conn Addr
...
fce2b0f0 stream 0 0 0 /tmp/mysql.sock
...
fce2cc80 stream 0 0 fce2c530
fce2c530 stream 0 0 fce2cc80
The last two lines above are added to the netstat -a output
when one user is connected to the mysql server. They disappear when
that user quits out of mysql.
# ls -ld /tmp/mysql.sock
prwxrwxrwx 1 root root 0 Jun 4 04:16 /tmp/mysql.sock
# who -b
. system boot Jun 4 04:14
#
Above shows that /tmp/mysql.sock, mentioned in netstat -a, is a
named pipe.
Setup each user who will access MySQL
Add /usr/local/mysql/bin to your PATH, e.g.
PATH=$PATH:/usr/local/mysql/bin
Test by running this command:
mysqlshow
This command should display output like this:
$ mysqlshow
+-----------+
| Databases |
+-----------+
| mysql |
| test |
+-----------+
$
Checking the status of the MySQL server daemon
Use the status command in mysql like this:
$ mysql
...
mysql> status
--------------
mysql Ver 9.11 Distrib 3.21.26-gamma, for pc-sco3.2v5.0.4 (i586)
Connection id: 3
Current database:
Server version 3.21.26-gamma-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 16 hours 43 min 53 sec
Running threads: 2 Questions: 14 Reloads: 1 Open tables: 3
--------------
mysql>
|
In the above example, 2 running threads means that two UNIX users
are currently connected to the mysql server daemon. Each time you
connect to mysql, you get a new (incremented) Connection id
number.
If the MySQL server is not running, trying to start mysql client
will show an error like this:
|
$ mysql
ERROR 2002: Can't connect to local MySQL server
$
Viewing databases and data
|
The following shows how to start mysql and view the available
databases on your system.
|
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 3.21.26-gamma-log
Type 'help' for help.
mysql> show DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)
mysql>
|
How to select a database to access.
|
mysql> use test;
Database changed
mysql>
|
How to view all the tables in the current database. A table is a
set of data stored in rows and columns. Three UNIX files are used
to hold the data for one table.
|
mysql> show TABLES;
+----------------+
| Tables in test |
+----------------+
| albums |
+----------------+
1 row in set (0.02 sec)
mysql>
If albums does not exist, you can create it like this:
mysql> CREATE TABLE albums
-> (
-> title VARCHAR(100),
-> artist VARCHAR(100),
-> released DATE
-> )
-> ;
Query OK, 0 rows affected (0.03 sec)
|
How to view all the data in the albums table. In this case, there
are only 4 record albums in our table. The * below means select all
fields to show.
|
mysql> select * from albums;
+------------------------------+---------------------+------------+
| title | artist | released |
+------------------------------+---------------------+------------+
| Selling England by the Pound | Genesis | 1973-01-01 |
| Wind & Wuthering | Genesis | 1976-01-01 |
| Doo Doo Wap | Percy Knickerbocker | 1992-07-04 |
| Zebra's Revenge | Genesis | 1971-02-03 |
+------------------------------+---------------------+------------+
4 rows in set (0.05 sec)
mysql>
|
How to view a description of each column in the albums table.
varchar(100) means a variable length string character field up to
100 characters in length.
|
mysql> describe albums;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| title | varchar(100) | YES | | NULL | |
| artist | varchar(100) | YES | | NULL | |
| released | date | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql>
|
How to view just the title and released columns where artist is Genesis
|
mysql> select title,released from albums where artist = 'Genesis';
+------------------------------+------------+
| title | released |
+------------------------------+------------+
| Selling England by the Pound | 1973-01-01 |
| Wind & Wuthering | 1976-01-01 |
| Zebra's Revenge | 1971-02-03 |
+------------------------------+------------+
3 rows in set (0.00 sec)
Viewing rows in order by field
|
How to view all rows where artist is Genesis and order the output
by released date. The three line command below could have been
input on one line.
|
mysql> select * from albums
-> where artist = 'Genesis'
-> order by released;
+------------------------------+---------+------------+
| title | artist | released |
+------------------------------+---------+------------+
| Zebra's Revenge | Genesis | 1971-02-03 |
| Selling England by the Pound | Genesis | 1973-01-01 |
| Wind & Wuthering | Genesis | 1976-01-01 |
+------------------------------+---------+------------+
3 rows in set (0.01 sec)
|
How to
view all rows in order of artist. If the same artist, order by
released date but in descending order
|
mysql> select * from albums
-> order by artist, released desc;
+------------------------------+---------------------+------------+
| title | artist | released |
+------------------------------+---------------------+------------+
| Wind & Wuthering | Genesis | 1976-01-01 |
| Selling England by the Pound | Genesis | 1973-01-01 |
| Zebra's Revenge | Genesis | 1971-02-03 |
| Doo Doo Wap | Percy Knickerbocker | 1992-07-04 |
+------------------------------+---------------------+------------+
4 rows in set (0.02 sec)
Viewing rows using wildcard matches
View all rows using "like" for wildcard matching:
% matches any number of characters
_ matches exactly one character
Notice you must have two underscores before 73 to match 19.
|
mysql> select * from albums
-> where artist like 'G%'
-> and released like '__73%';
+------------------------------+---------+------------+
| title | artist | released |
+------------------------------+---------+------------+
| Selling England by the Pound | Genesis | 1973-01-01 |
+------------------------------+---------+------------+
1 row in set (0.01 sec)
|
View all rows where title matches the given regular expression.
Here we show any line whose title does not start with V through Z.
|
mysql> select * from albums where title regexp '^[V-Z]';
+------------------+---------+------------+
| title | artist | released |
+------------------+---------+------------+
| Wind & Wuthering | Genesis | 1976-01-01 |
| Zebra's Revenge | Genesis | 1971-02-03 |
+------------------+---------+------------+
2 rows in set (0.02 sec)
Hint: read regexp as "contains."
|
View all rows that match two regular expression conditions.
|
mysql> select * from albums
-> where title regexp '^[V-Z]'
-> and released regexp '76';
+------------------+---------+------------+
| title | artist | released |
+------------------+---------+------------+
| Wind & Wuthering | Genesis | 1976-01-01 |
+------------------+---------+------------+
1 row in set (0.07 sec)
Add data (row) to an existing table
$ mysql test # test is the database name
mysql> insert into albums(title,released,artist)
-> values('Zebra\'s Revenge','1971-02-03','Genesis');
Query OK, 1 row affected (0.00 sec)
Note a backslash is required before any single quote in the data.
Note that the fields may be specified in any order.
Note that any missing field values will list as: NULL
Note that the list of field names may be omitted if values are
supplied in the correct order.
|
Delete data (row) from an existing table
$ mysql test # test is the database name
mysql> delete from albums
-> where title = 'Ziggy';
Query OK, 1 row affected (0.01 sec)
|
Warning:
if the where clause is omitted, all rows are deleted!!!
|
Modify data in an existing field
$ mysql test # test is the database name
mysql> update albums
-> set released = '1992-07-04'
-> where title = 'Doo Doo Wap';
Query OK, 1 row affected (0.00 sec)
Note that it is possible to calculate the new value from
a field in the row.