![[Top Bar]](../../common/images/Topbar-ru.gif){kind=small}
![[Bottom Bar]](../../common/images/Bottombar-ru.gif){kind=small}
![[Photo not available]](../../common/images/Who.gif) Jose M. Fernández Contents: Introduction Main Characteristics Installation First Steps. Administration (Security) SQL Language under MySQL Conclusions |
MySQL |
| Tables |
|---|
| db |
| host |
| user |
| Field | Type | Null | Key | Default | Extra |
| Host | char(60) | PRI | |||
| Db | char(32) | PRI | |||
| User | char(16) | PRI | |||
| Select_priv | char(1) | N | |||
| Insert_priv | char(1) | N | |||
| Update_priv | char(1) | N | |||
| Delete_priv | char(1) | N | |||
| Create_priv | char(1) | N | |||
| Drop_priv | char(1) | N |
| Field | Type | Null | Key | Default | Extra |
| Host | char(60) | PRI | |||
| Db | char(32) | PRI | |||
| Select_priv | char(1) | N | |||
| Insert_priv | char(1) | N | |||
| Update_priv | char(1) | N | |||
| Delete_priv | char(1) | N | |||
| Create_priv | char(1) | N | |||
| Drop_priv | char(1) | N |
| Field | Type | Null | Key | Default | Extra |
| Host | char(60) | PRI | |||
| User | char(16) | PRI | |||
| Password | char(16) | ||||
| Select_priv | char(1) | N | |||
| Insert_priv | char(1) | N | |||
| Update_priv | char(1) | N | |||
| Delete_priv | char(1) | N | |||
| Create_priv | char(1) | N | |||
| Drop_priv | char(1) | N | |||
| Reload_priv | char(1) | N | |||
| Shutdown_priv | char(1) | N | |||
| Process_priv | char(1) | N | |||
| File_priv | char(1) | N |
If can be decided whether to authorize or not SELECT, INSERT, UPDATE, and DELETE files in a table.
It is also possible to allow or not to CREATE or DROP (delete) tables or databases.
Another interesting permission available is access to the administrative commands like shutdown, reload, process, etc.
The current permissions can be inspected with the script mysqlaccess.
A HOST must always be a host local, an IP number or an SQL expression. If in the table db the host column is empty it means any host#148; in the table of host. If on the other hand in the table host or user the column host is empty it means that any HOST can establish a TCP connection with our server.
Db is the name of the database.
An empty USER column means any user name.
The fastest way to launch the server is running the following command:
mysql.server startThe same operations can be performed with the script safe_mysql, as indicated in the installation guide, but one way or another the file result is to execute the deamon mysqld.
As it can be easily understood, it is necessary to launch the server to perform any operation with the database; with the server running we can run operations like mysqladmin whose syntax is:
mysqladmin [OPTIONS] command command &where OPTIONS can be :
where command can be one or more of the following:
For instance running:
mysqladmin create newdatabasecreates a new database with the name "newdatabase"
we can see the processes running on the server by running
mysqladmin processlistAnother important command is mysqlshow which let us see the databases available, for example executing that command without options gives:
> mysqlshow![[Ilustration]](../../common/images/illustration21.jpg)
+-----------+
| Databases |
+-----------+
| mysql |
| people |
| test |
+-----------+
In the introduction we already indicated how this server is considered one of the fastest within its class for large and small sets of data, and we also mentioned that this performance came at the expense of not implementing a number of features of SQL that in my opinion are important. Two important features left out are the Triggers and the Transactional Logic.
Triggers are nothing but a small portion of code that gets "fired" --executed-- when a given operation is executed on the database (an update, delete, etc..). Obviously the test for the trigger condition as well as its management is something that consumes resources of the system and this is the only reason why they are not implemented.
The consistency among the tables in a relational database is very important. SQL provides a more or less simple tool to provide for this consistency: "Transactional Logic". It is the server that should provide the mechanisms for blocking files as well as consolidation and regresion of operations in the database. Well, MySQL does not support the transactions in order to improve the speed of the server (at least that is what the documentation says), the only aid we have is to use the commands LOCK tables / UNLOCK tables that permit to block tables from other users use but not allowing us to remove the operations already performed with the data.
Taking into account the limitations of the server we will next review a number of SQL commands, not with the goal of analysing SQL commands per se but to see how this server implements them.
Aftern launching the server we are ready to send instructions. For example, let us create a database named "people" that is made of three tables "clients" "states" "counties". It is very simple and not very useful example but it gives us an idea how to manipulate the data in a real case. First we must say that these operations can be performed in several ways: through an API in C, C++ JAVA or though a ODBC if we were working under Windows95 (Pardon me again), we can also use the shell provided by the distribution. I will opt for the last method because for the purpose of this article it is enough and we avoid describing the specifics of other programing languages.
The Mysql shell can be launched running:
mysql databasenameafter receiving the shell prompt we can start sending commands to the server.
It is also possible to use the shell in batch mode by running:
mysql -e (SQL command && )databasenamethis sends an SQL command to the server.
To create the database "people" of our example we execute the command:
mysqladmin create peopleThen run the shell as
mysql peoplenow from the shell we can start to send commands to the proper server, for instance to view the tables available within the database:
> show tables /gthe system responds with:
+-------------+
| Tables |
+-------------+
| clients |
| counties |
| states |
+-------------+
All commands sent to the server from the shell finish with /g, which indicates the end of command and submits it to the server for processing.
Naturally, the only way we could have gotten the response above is if we had previously created the corresponding tables with the command CREATE. A typical CREATE command has the following look:
CREATE TABLE clients
CREATE TABLE states
CREATE TABLE counties
(Cod_state INT(2) NOT NULL,
Cod_county INT(3) NOT NULL,
Description_c CHAR(30) NOT NULL,
PRIMARY KEY(Cod_state,Cod_county)) /g
If we next run
> show colums from
clients from people /g
> show columns
from states from people /g
> show columns from
counties from people /g
we would obtain:
Database: people Table: clients Rows: 4 +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | NIF | char(9) | |PRI | | | | Name | char(15) | YES | | | | | Family_name | char(35) | | | | | | Address | char(50) | | | | | | City | int(5) | | | 0 | | | State | int(2) | | | 0 | | | Phone | int(9) | YES | | | | | Date | date | YES | | | | +--------------+----------+------+-----+---------+-------+
Database: people Table: states Rows: 3 +-----------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key| Default | Extra | +-----------------+----------+------+-----+---------+-------+ | Cod_state | int(2) | | PRI | 0 | | | Descripcion_s | char(30) | | | | | +-----------------+----------+------+-----+---------+-------+
Database: people Table: counties Rows: 9 +------------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key| Default | Extra | +------------------+----------+------+-----+---------+-------+ | Cod_state | int(2) | | PRI | 0 | | | Cod_county | int(3) | | PRI | 0 | | | Descripcion_c | char(30) | | | | | +------------------+----------+------+-----+---------+-------+
Afterwards we would proceed to insert data in each of the tables.
Let us use the SQL command INSERT without any participation of
other programming languages nor API routine:
To insert a record in the clients, counties and states tables
do this:
To conclude our excursion with the SQL commands we will select
the rows inserted in the tables of the current database. Let us select
records from the clients table varying the condition for selection and
selecting first from the table of counties:
> SELECT ALL
Cod_state, Cod_county, Description_c from counties
where Cod_state = 28 /g
Cod_state Cod_county Description_c 28 1 Fuenlabrada 28 2 Pozuelo 28 3 Madrid
> SELECT ALL
NIF,Name,Family_name,Address from clientes
where City =
28001
NIF Name Family_name Address 23198623N JUAN ANDRES RUIZ MORALES C/ CATEDRAL 12 2B 5316828K PEDRO ROMERO DIAZ C/ HOLA 9 52312844J LUIS ALBERTO LISTO JIMENEZ C/ ROSA DE LOS VIENTOS 129 3I
We opened this article saying that our purpose with this article
was to show the fundamental characteristics of a specific SQL server,
we did not want to reduce the article to a list of recipes and commands
to use MySQL but instead we wished to study the possibilities and
limitations of this software; only knowing in depth an application
like this one we can truly get the greatest advantages it has to offer.
MySQL omits the implementation of triggers and transactional logic
and therefore makes the management of data (inserting,
modifying, deleting records) very complex from multiuser applications and using
numerous interelated tables. Nevertheless, I recommend this server
for applications requiring very fast access of large
databases.
Finally I would like to mention that most of the information in
this article was obtained from the MySQL documentation included in the
distribution, from several articles in technical magazine as well as
from an already yellowish IBM manual about SQL.
| This website is maintained by Miguel Angel Sepulveda © Jose M. Fernández 1998 LinuxFocus 1998 |