MySQL Basics





1        MySQL Intro


1.1      First Start of MySQL


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.

1.2       First Terminal Session work with MySQL


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.


displays a lot of information.


In the table above all these orders don’t change anything in your database.

1.3      My first 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



2        Further Database Opportunities



For big databases you need to create ident numbers for references. This is an example for the syntax:


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 ( 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.


3        Using MySQL from Outside


3.1      Using from office applications

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).


3.2      Connection with Apache

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.





4        Some useful SQL combinations


SELECT numhours * hoursalary FROM practicants_table WHERE pract_name=”Charly”

Tricky added a done multiplication for the output on screen. Please test before use.




5        Compared with MS SQL Server


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_help tablename

DESCRIBE tablename






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. 







MySQL documentation files of MySQL in the SUSE 9.3 distribution (/etc/shares/mysql...)


MySQL 5, Michael Kofler, ISBN 1-59059-535-1 and following lessons