APPENDIX A: MYSQL [45]

GENERAL INFORMATION

Code, Manual, FAQ: http://www.tcx.se/

SQL SERVER: For System Administrator Only

Start Server Daemon: gold% mysqld &

Or the ``Safe Way'': gold% safe_mysqld &

SECURITY

Privileges are assigned based on HOST, DB, USER (system tables).

mysql -p mysql
Enter password: *******
mysql> select * from host \g

+-----------+----+-------------+-------------+-------------+-------------+-------------+-----------+
| Host      | Db | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
+-----------+----+-------------+-------------+-------------+-------------+-------------+-----------+
| localhost | %  | Y           | Y           | Y           | Y           | Y           | Y         |
| gold      | %  | Y           | Y           | Y           | Y           | Y           | Y         |
| %         | %  | Y           | Y           | Y           | Y           | Y           | Y         |
+-----------+----+-------------+-------------+-------------+-------------+-------------+-----------+

mysql> select * from db \g

+------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+
| Host | Db     | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
+------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+
| %    | test   |      | Y           | Y           | Y           | Y           | Y           | Y         |
| %    | test_% |      | Y           | Y           | Y           | Y           | Y           | Y         |
| %    | suppDB |      | Y           | N           | N           | N           | N           | N         |
| %    | 1001DB | 1001 | Y           | Y           | Y           | Y           | Y           | Y         |
+------+--------+------+-------------+-------------+-------------+-------------+-------------+-----------+

mysql> select * from user \g

+------+---------+----------+------+------+------+------+------+----+------+--------+-------+----+
| Host | User    | Password |Select|Insert|Update|Delete|Create|Drop|Reload|Shutdown|Process|File|
+------+---------+----------+------+------+------+------+------+----+------+--------+-------+----+
| %    | any     | 7cdeca72 | N    | N    | N    | N    | N    | N  | N    | N      | N     | N  |
| %    | billard | xxxxxxxx | Y    | Y    | Y    | Y    | Y    | Y  | Y    | Y      | Y     | Y  |
| %    | 1001    | yyyyyyyy | N    | N    | N    | N    | N    | N  | N    | N      | N     | N  |
+------+---------+----------+------+------+------+------+------+----+------+--------+-------+----+

mysql> insert into user values('%','any',password('any'),'N','N','N','N','N','N','N','N','N','N')
    -> /g /q

unix% mysqladmin -p reload          # important: need to take effect

USER table is OR'ed with DB table

'any' user must provide password 'any'

'any' user can only do SELECT on suppDB [read-only]

'test' database is open for reading/writing by 'any'

'1001DB' is open for read-write by user 1001

CREATE/DROP DATABASE [46]

   unix% mysqladmin -p -u 1001 -h gold create 1001DB
   unix% mysqladmin -p -u 1001 drop 1001DB         #to start over again

MYSQL CLIENT MONITOR

Establish connection to host ``gold'' as user ``any'' with password ``any''

Interactive:

unix% mysql -p -h gold -u any suppDB
Enter password: any 
Welcome to the mysql monitor. Commands end with ; or \g.
Type 'help' for help.

mysql> select *      
    -> from S
    -> where STATUS >= 20  \g
4 rows in set (0.14 sec)

+------+-------+--------+--------+
| S_NO | SNAME | STATUS | CITY   |
+------+-------+--------+--------+
| S1   | Smith |     20 | London |
| S3   | Blake |     30 | Paris  |
| S4   | Clark |     20 | London |
| S5   | Adams |     30 | Athens |
+------+-------+--------+--------+

mysql> \q

Batch File:

unix% mysql -p -u 1001 -h gold 1001DB <1001DD.sql  # create tables
unix% mysql -p -u any -h gold suppDB <suppQ.sql

But the batch file needs to have the password in it.

SQL SYNTAX [47]

CREATE TABLE:

   create table SP (
     S_NO char(8),
     P_NO char(8),
     QTY int,
     primary key (S_NO, P_NO),
     foreign key (S_NO) references S,
     foreign key (P_NO) references P
   )
   \g

SHOW INFORMATION:

   show tables \g
   show columns from SP \g

DROP A TABLE:

   drop table SP \g                 # useful to redo just one table

INSERT VALUES:

   insert into SP values('S1','P1',300) \g

LOAD FILE:

   load data infile 'mySP.text' into table SP  \g

UPDATE TABLE:

   update SP set QTY=400, CITY = 'paris' where S_NO = 'S1' \g

DELETE FROM TABLE:

   delete from SP where QTY > 200 \g

INDEXES:

Primary keys automatically have a B-tree index for performance.

Add secondary keys to enable faster searching:

   alter table J add index myindex (JNAME, CITY) \g
   alter table J drop index myindex \g