First start with
MySQL you will have mostly trouble with the syntax of orders although you know
how it should work. Very good is the chapter 3 of the MySQL documentation of
the distribution.
First read the doc
files of your Linux distribution. I searched long till I found a readme file in
one of the folders (where are the SUSE docs for more distributions) of my
SUSE/NOVELL Linux 9.3. There I had to start as root a special script before
first run of mysql was possible after full installation.
As root you only
type „rcmysql start“ after the prompt in the terminal session to start the
database on your computer.
First steps are easy practised in the mysql terminal session. You launch it
only by typing „mysql“ in the terminal. So easy it is only if you are only
running one computer with the database and a single user. If somebody before
you worked with the database then some administrated users are created with
closed surroundings and more parameters are necessary. Your administrator then
should give you a database only for you for practicing with
„grant all on
testDBname_foryou to user1@localhost“
The full command for
launching may be then
„mysql –h hostname
–u username –p name_of_database“.
Option „–p“ means
password that will be asked after you have typed in all and pressed return, so
illogical you have here the name of the database after the option „-p“.
With „quit“ you quit
database console input. But the database is further active. You shut down the
database you have to use the command „rcmysql stop“ (as root). Sometimes you
have to add some options in a network. Administrator could have added
extensions that you cannot shutdown databases only by simple typing one wrong
word.
You should open the
mysql database terminal as user in a user terminal and as root in a root
terminal to look at the differences. Your database should be nearly empty but
some information is possible to pull out. Try following orders:
select version()
select version();
select version();
current_date();
select version(),
current_date();
select version(), select
current_date();
That should give you
a feeling for the syntax. 3rd does not work, and 5 may be not too. You can type
SQL commands in capital (that is the default) letters too. If you call
functions, variables, parameters and procedures then you have to take care
about capital letters. There are some differences between different relational databases.
Orders you should
try:
|
select version() |
Displays version of
mysql database |
|
select
current_date() |
Displays/prints
current date |
|
select sin(pi()/4) |
Demonstrates that
you can do some calculations on command level |
|
select now() |
prints current date
and time |
|
select user() |
prints user |
|
show databases |
displays available
databases |
|
show variables like
`have%` |
prints all available
modes and add ons |
|
show variables
`version` |
prints same as
select version() |
|
show variables `%` |
a list of all
variables and their values |
|
show status |
displays a lot of
information. |
|
status |
displays a lot of
information. |
In the table above
all these orders don’t change anything in your database.
You should open the
mysql database terminal as user in a user terminal and as root in a root
terminal to look at the differences. Now let us do some work in your database?
It is possible to use the database test, which exists in mysql and is
accessible for everybody. If more people using this database you should create your
own.
|
show databases |
First check that
your databasename is not used already. |
|
create database
test2 |
Creates your
database with name test2. |
|
use test2 |
Before zou are not
using this command you have no access to this database. |
|
show tables |
Displays tables in
database and should be empty for this fresh created database. |
|
create table pets
(name VARCHAR(20), owner VARCHAR(20), birth DATE) |
Creates table pets.
You have to tell there the structure of the fields. Here maximum field length
20 chars variable. |
|
show tables |
Now there should be
printed out at least one table name – pets. |
|
describe pets |
shows structure of
the table pets |
|
select * from pets |
shows full table,
which now is empty and not filled with data. The wildcat * stands for using
all rows. |
Be careful with the
command „delete“. If you omitted one word you would delete more than you would
want in most cases. Now the database in empty and you have to fill data in.
|
insert into pets
values (‚Timber’, NULL, ‚1999-03-30’) |
This fills your
first line of data in your database. NULL you have to type for empty fields.
If you tape this two times the entry is then two times in your database. |
|
select * from pets |
should now show your
data in the table |
|
update pets set
birth=’1998-08-31’ where name=’Timber’ |
This changes only
one field of one set in your database |
|
delete from pets |
deletes full table
contents |
|
load data local
infile ‚/home/user1/mydata/pets.txt’ |
loads table pets wit
data from a file. |
|
example file: \N is used for empty
fields in file |
name owner birth Timber Sonya 1999-03-30 Malla Martin \N |
How to ask a database
for special information?
|
select * from pets
where birth > `1999-01-20` |
prints data of all
pets which are born after that date. |
|
sellect owner * from
pets where birth > `1999-01-20` |
prints only the name
of the owner. |
|
select * into
outfile ‚/home/user1/mydata/petsoutput.txt’ |
saves your
database/table into a file |
This you can logical
combine (and, or, ...., with numbers in tables you can calculate too) and asking
many things to your database.
|
drop table pets |
kills your table
pets |
|
drop database test2 |
kills your database |
For big databases you
need to create ident numbers for references. This is an example for the syntax:
CREATE TABLE test(it
INT NOT NULL AUTO_INCREMENT, choice TINYINT NOT NULL, ts TIMESTAMP, PRIMARY
KEY(id));
In database design you
have to decide where this is used for gaining more speed.
MySQL 5 has only
binary update logging. MySQL developers dropped test logging, but with
mysqlbinlog you have a tool to read this. Binary logging slows your database
only one percent.
With SET SQL_LOG_BIN=0
you can switch of logging SQL commands access (queries which don’t change
data). Logging of slow queries, which need longer than one second is possible.
MySQL 5 has no direct
XML interface. It is possible to dump contents into a XML file.
mysqldump –u root p
–xml mylibrary –execute=SELECT * FROM pets ; /tmp/mylibrary.xml
MySQL 5 supports GIS
(www.opengis.org) a geometric data standard. This standard defines areas (dot,
line, polygon...) and geometric data. One example for different geometric data
from literature (was not working on my computer - cs2cs error in options)
echo „11.4 47.2667“ | cs2cs +proj=latlong +to +proj=utm
+zone=32V
All what you did on
your mysql terminal session you could put into a script file. This file you can
use as input. Important is to mention that you have to take care that all „;“
are set. For one special order you have to use a special command to tell the
interpreter to ignore „;“ between „()“.
When the database is
running other applications can access the database. There are three ways of
data exchange (in a very simple description):
-
Starting mysql
terminal sessions with a script input and launch output (change stdout).
-
Using commands
from shell level and redirect output (stdout).
-
Using public
available objects/methods from mysql for data exchange (SW interface)
Third method is
common. ODBC, JDBC are tools or (standardized) connectors to access databases
by other applications which use this doors for accessing mysql.
I left out
administration for access, user rights and so on.
Many office
applications are able to read in tables, which are exported, from MySQL. On the
other hand they are able to write in a readable form for MySQL. If similar
tables on both sides are already created and only data transferred, it will
work in most cases. If the table structure needs to be interpreted for
automatic creating, things will not be so easy.
For SQL operations you
have first you have to choose your connection type. ODBC for Microsoft Office
and JDBC for Office from Sun (OpenOffice, StarOffice).... You have to look up
the configuration in manuals/tutorials of your office application (GUI
options/menus).
If you use a Linux
distribution (Suse 9.3) and you installed Apache with all supplements suggested
by the installer then you will only start Apache as root with „/etc/init.d/apache2
start“. You should consider that apache
is not administrated now and you are recommended only working without
connection to the www because you are easy to be hacked now.
SELECT numhours *
hoursalary FROM practicants_table WHERE pract_name=”Charly”
Tricky added a done
multiplication for the output on screen. Please test before use.
Microsoft offers a
free relational database called MS SQL Server or MSDE. You can download it from
the MSN homepage. After installation you open a DOS Windows to set a password
after installation. Don`t forget to go with “cd” into the folder where are the
executable files from the database.
|
setup SAPWD=”yourpassword” |
set password (only)
after installation |
|
osql –E |
starts terminal
session |
Terminal session of MS
database is less nice. After every command you have to press return key, then
to type GO and then press return key again. Therefore mark “;” is not used to
end command lines. The outputs of MySQL more structured. USE dbname doesn`t
print the info that you changed database on screen.
Cross reference:
|
sp_databases |
SHOW DATABASES |
|
sp_help tablename |
DESCRIBE tablename |
|
sp_tables |
SHOW TABLES |
One really important
phrase you should always keep in mind is that if you create databases which
designs uses commons of MS database and MySQL you will be able to convert to
any other products (oracle, cache…) without big problems.
Literature/Bibliography:
MySQL documentation
files of MySQL in the SUSE 9.3 distribution (/etc/shares/mysql...)
MySQL 5, Michael Kofler, ISBN 1-59059-535-1
http://functionx.com/sql/Lesson01.htm
http://functionx.com/sqlserver/Lesson01.htm and following lessons