Ref: tutorial_MySQL_WampServer.

doc

The DATABASES in WampServer

1 General Definitions

A database (DB) allows for recording, storing, organizing

data in an organized and hierarchical manner.
SQL is the language that allows for the manipulation of databases.
DBMS (Database Management Systems) are programs
that allow you to manage your data directly without using a script
PHP.
The best known are:
MySQL: free and open source, it is probably the most well-known DBMS. We
we will use it
PostgreSQL: free and open like MySQL, with more features but
a little less known.
SQLite: free and open-source, very limited in features.
Oracle: used by businesses, one of the most complete DBMS but requires payment.
Microsoft SQL Server: Microsoft's DBMS.

1.1 Database Structure

Based in SQL language, allows to classify the information contained
in the tables.
Unetablecontains different data. A database contains a maximum of
256 tables. This is where the data is stored, in the form of a
tableau
In this table, the columns are called fields, and the rows are
called entries.

TIC – UIAD Creation of website 2thyear Daniel Gaudin – Jean Claude Chêne page 1/31
 Réf : tuto_MySQL_WampServer.doc

1.2 Database Management

The database manages the information in the form of tables, MySQL
record information, write it and save it in files!
These files are somewhere on your hard drive. It is always necessary to use
MySQL will be responsible for extracting and modifying the information in these
files.
Each DBMS has its own way of recording data
For example, with MySQL on Windows if you are using WAMP, you should
find the files where the information is stored in
C:\wamp\mysql\data.
We will have to communicate with the database to give it the order to retrieve or
to record data. To "talk" with the database, we use SQL language.
Structured Query Language

SQL allows the construction of powerful relational databases.

regardless of the database used.

2 USE with PHPMyADMIN

We will therefore use MySQL which is provided in WampServer.

PhpMyAdmin is the interface for using MYSQL, based on PHP and HTML.
of WampServer

When we launch WampServer we have

the icon in our taskbar. If we
clicking on this icon we open a
command window in which we
we can click on 'phpMyAdmin'.

TIC - UIAD Website creation 2emeyear Daniel Gaudin – Jean Claude Chêne page 2/31
 Ref: tuto_MySQL_WampServer.doc

We then open the following window:

2.1 CREATE A DATABASE

Launch Wamp server and open phpMyAdmin, we then have in the window (1)
a field(2)Create a database :

Give a name to the database: 'base_essai' and click on create

TIC – UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 3/31
 Ref: tuto_MySQL_WampServer.doc

We then have confirmation of the creation in a frame surrounded by green and

just below the line of SQL code that was generated to perform this
operation.
This information is very important as it will allow us afterwards to
copy/paste
In the left column in blue we have our base with parentheses
the number of built-in tables; 0 for now since we don't have any
still created none!

Note: the field 'Interclassification' pertains to various languages; example:

We let WampServer handle that.

TIC – UIAD Website Creation 2thyear Daniel Gaudin - Jean Claude Chêne page 4/31
 Ref: tuto_MySQL_WampServer.doc

2.2 Create and Manage Tables

To do this, we select our database by clicking on it:

We name the table and define the desired number of columns (fields)
here table_name and 2 columns.

The following screen opens:

We fill in each field

An indexId, integer type that will serve as the primary key, size/value 4 digits,
In index, put INDEX and check the AUTO_INCREMENT box.
A fieldName that can contain a string with the type
VARCHAR of 30 characters in the size/value field
We ignore the other fields.

TIC –UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 5/31
 Réf : tuto_MySQL_WampServer.doc

Then we SAVE (if we execute, we create a new field) the table is

registered and appears in the list

The SQL instruction appears well under the green-framed message of

execution confirmation.
Note that the column NULL indicates the information Nonce which translates
in the SQL code lines by NOT NULL and means that the fields do not
must not be empty. If we had wanted to accept empty fields, it would have
Check the box NULL (green line) in the column creation screen.
In the same way, let's create a second table "table_activities" with 3
colonnes « tennis » « petanque » « bridge » :

We now have our database with two tables.

TIC – UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 6/31
 Ref: tutorial_MySQL_WampServer.doc

2.3 The Management Tabs

We see navigation tabs at the top of the screen. Each tab has
a function. The one we are on, Structure: displays the various
fields above.

SQL: allows you to run a statement directly in SQL

Search: a record, various parameters, allows for making

Requests (a cross-search between the fields of different tables)
We will see this in more detail.

TIC – UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 7/31
 Ref: tuto_MySQL_WampServer.doc

Insert: this command is used to insert data into a table of our

First, you should open the table and click on the 'Insert' tab.
Note: if our table is empty we cannot display it and we have this
screen

A data entry window opens. It offers us two fields of

fields in which we can enter data. The 'Id' field is not
not to be filled in since it "self-increments".

Then you need to click on 'Execute' to insert the data into the table.
ATTENTION, if we only enter data in the second field and that
clicking on execute will implement the two fields in the table, the
first being empty.
Once the entry is executed, we obtain this screen and the table is no longer empty.
we can display it.

…….
We then see our entries and the "Id" has been automatically incremented, there
It starts at 5 because we did tests beforehand and erased them.

TIC – UIAD Website creation 2èmeyear Daniel Gaudin – Jean Claude Chêne page 8/31
 Ref: tuto_MySQL_WampServer.doc

Exporter: the base, equivalent to 'save as' to save

It is necessary to check that all the tables are properly selected and that the choice is
Of course SQL then click on Execute.
A window opens and one chooses the location to save the file
in .sql !

TIC - UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 9/31
 Ref: tuto_MySQL_WampServer.doc

Importer: a database, is used to 'open' and implement in WampServer

one or more tables in a database, existing or to be created before the import,
starting from a .sql file (or another if needed such as .zip for example)

Choose the file in the location with this command and click on
Execute.

Operations: allows creating, modifying, renaming, and emptying databases and tables.

The Privileges tab allows for user management. Generally, one

work under Root but it may be useful to create several users when
we manage several databases mainly from a website !.

TIC –UIAD Website creation 2thyear Daniel Gaudin - Jean Claude Chêne page 10/31
 Ref: tuto_MySQL_WampServer.doc

To do this, you need to click on Add a user and fill in the fields then
click on Run.

The use of the Generate a password button is not recommended because the word
The generated password is very long!

Finally, the Delete tab is used to completely erase a table or a database.

To be used with caution!

TIC –UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 11/31
 Ref: tuto_MySQL_WampServer.doc

3 DATA MANIPULATION in WampServer

Before handling the data entered in our table, we will clarify

a certain number of possible features used by MySQL.

3.1 MySQL field types

Let's go to the 'Structure' tab of our table and add a 'column'.

at the end of the table. When we click on 'Execute' we open this
window

Let's go through the list of types that MySQL offers on the 'Type' line:

… …

While PHP offers only a few data types (int, string, bool...)
MySQL offers a very large number of data types.

TIC - UIAD Website Creation 2thyear Daniel Gaudin – Jean Claude Chêne page 12/31
 Ref: tuto_MySQL_WampServer.doc

In fact, these are categorized by categories:

NUMERIC: these are the numbers. There are types dedicated to small ones.
integer numbers (TINYINT), to large integer numbers (BIGINT), to numbers
decimals (DOUBLE), etc.
DATE and TIME: these are the dates and times. Many types
different types allow you to store a date, a time, or both at the same time.
these are strings. Once again, there are types
suitable for all sizes.
SPATIAL: this concerns spatial databases, useful for those who
map making. This will not be our case, so we will not talk about it.
here.
Attention: if a numerical field has 0 at the beginning, it will not be accepted.
into account. It is therefore necessary to use a character field to, for example, store
phone numbers.
In fact, phpMyAdmin had the good idea to suggest at the very beginning of this
list the 4 most common types of data:
INT: integer.
VARCHAR: text field (between 1 and 255 characters).
long text (you can easily store a novel there).
DATE: date (day, month, year).

We will only need to juggle between these 4 types, so those are the ones he
must remember. This will cover 99% of needs. You can also keep in mind
the DOUBLE type which allows storing decimal numbers.
A small remark about VARCHAR: it is a type suited for short
texts, you must indicate the maximum field size (between 1 and 255). If
If you don't do it, you won't be able to create the table.

TIC - UIAD Website creation 2emeyear Daniel Gaudin – Jean Claude Chêne page 13/31
 Ref: tuto_MySQL_WampServer.doc

3.2 The primary keys

Every table must have a field that serves as the primary key. The key
primary allows to uniquely identify an entry in the table. In
Generally, the 'Id' field is used as the default primary key.
Each record on your site must be identifiable in a way
unique. The simplest way to do this is to give it a unique number,
in a field named 'Id'. There cannot be two records with
the same ID in the same table or in two tables of the same database
data!
It is essential that each table has its primary key. Tables without a key
primary will have their performances extremely reduced. Create each time
this field "Id" giving it the PRIMARY index, which will have the effect of
create a primary key, check the AUTO_INCREMENT box so that this field
manages the new values automatically with each new entry.

3.3 The REQUESTS

We saw that the 'Search' tab allows us to make queries.

in our tables.

A SQL query is a text that gives an order to execute to a engine.

database on a database. We use the SELECT command.
It can involve one record or several, it can come into play.
one table or several. The query is composed of keywords, the commands
SQL (which are currently generated automatically by WampServer).
It should also be clarified that SQL commands are not case-sensitive.
case insensitive in writing but the fields are sensitive to syntax.

TIC –UIAD Website creation 2emeyear Daniel Gaudin – Jean Claude Chêne page 14/31
 Ref: tuto_MySQL_WampServer.doc

For this example, we are importing a new table, "video_game", into

our "base_test" in order to have more numerous and more
varied so that our demonstration is more meaningful.
This table has 50 'Entries', each with 7 columns.
aspect :

We then go to the 'Search' tab and we get this view:

We see that all the columns of the structure of our table are
displayed and that for each of them we have a possible choice
of "Operator".

TIC – UIAD Website Creation 2emeyear Daniel Gaudin – Jean Claude Chêne page 15/31
 Ref: tuto_MySQL_WampServer.doc

This choice varies depending on the TYPE of data in our column.

Numeric TYPES (int, double,…) and alphanumeric TYPES (varchar,

text,…)

………..…………

Comparison operators allow for comparing two values, and

return a result depending on whether they are identical (true) or not (false).
MySQL offers some options in addition to the usual operators.

Attention: not all are automatically offered by WampServer and all

does not apply to all types of data; we will not use a
string comparison like REGEXP with a value
digital for example.

Example of requests:
Let's search our table for all games that run on PC:

TIC –UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 16/31
 Ref: tuto_MySQL_WampServer.doc

Who will give us the following result:

Recherchons maintenant si « Michel » a testé des jeux sous PC :

Here is the result:

Note the SQL code generated by WampServer:

SELECT *
FROM `video_games` WHERE `owner`='michel'
AND `console` LIKE 'PC'
LIMIT 0, 30

TIC –UIAD Website creation 2thyear Daniel Gaudin - Jean Claude Chêne page 17/31
 Ref: tuto_MySQL_WampServer.doc

Finally, as a last example, let's search in the comments for the word 'best':

And let's see the result:

There are also other operators allowing for other functions to be used.
always with the command SELECT, such as ORDER BY which
allows sorting a list of results ... we will see all this in the section
next devoted to the syntax of the SQL language!

TIC - UIAD Website creation 2emeyear Daniel Gaudin – Jean Claude Chêne page 18/31
 Ref: tuto_MySQL_WampServer.doc

4 THE SQL LANGUAGE

Let's not forget that the goal is to access the data in the database.
in order to manipulate them to achieve the result that interests us.
We have therefore learned to create, implement, and manipulate databases.
data with SQL through the MySQL DBMS provided in
WampServer.
We will now study a little more in detail the SQL language itself.
to be able to manipulate our databases later using PHP scripts
inclus dans des pages HTML via notre site.
A SQL command is also called a SQL statement or SQL query.
SQL provides a set of commands for a variety of tasks, including:

the creation and modification of the database schema,

the interrogation of the database,
the insertion, updating, and deletion of data in the database
of data
the definition of views,
access control to data,
the creation of indexes to speed up queries...

We will therefore type SQL instructions directly from a window of

online command such as that offered by WampServer through the 'SQL' tab:

Since all SQL commands are in English, we will do this first.

a little reminder of the main definitions, commands, and vocabulary to
our arrangement.

TIC –UIAD Website Creation 2thyear Daniel Gaudin – Jean Claude Chêne page 19/31
 Ref: tuto_MySQL_WampServer.doc

4.1 Definitions

Abbreviation Meaning Abbreviation Meaning Remark

English English French French

DB Database BD or BDD Database

DBMS Data Base DBMS System of

Management Basic Management
System of Data

SQL Structured Query SQL Language Language

Language Structured of of Extraction
Data Data

DDL Data Definition DDL Language of The Commands

Language Definition of Main
Data

DML Data DML Language of The Commands

Manipulation Manipulation of Operations on
Language Data the Data

TABLE Table

ROW Line

COLUMN Column

DATATYPE Data Type

4.2 The Command Vocabulary

Command Definition Syntax Example

Creation of a CREATE 'entity' CREATE DATABASE

CREATE
SQL entity name my_base

SELECT "title of SELECT console, prix

Interrogation of the
SELECT column FROM table FROM video_games
Database
WHERE "chosen value" WHERE price < 20

Delete one DROP "entity" "name"

DROP DROP TABLE my_table
entity

DELETE FROM "entity"

Delete from WHERE DELETE FROM my_table
DELETE
data « champ »=« valeur à WHERE id=1
delete

TIC - UIAD Website Creation 2a thyear Daniel Gaudin – Jean Claude Chêne page 20/31
 Ref: tuto_MySQL_WampServer.doc

INSERT INTO "entity" INSERT INTO my_table

Insertion of ["champ1","champ2"] (‘nom’, ‘prénom’)
INSERT
data values values
(‘valeur1’,’valeur2’) (‘Gaudin’,’Daniel’)

UPDATE 'entity' SET

UPDATE my_table SET
Update of « champ »=«nouvelle
UPDATE name='Jules' WHERE
data value" WHERE
nom=’Gaudin’
« champ » = « valeur »

ALTER "entity" ADD

Or CHANGE
ALTER TABLE
Modification of a Or MODIFY `ma_table` ADD
ALTER
entity Age INT(2) NOT
Or DROP
NULL
Or RENAME "action to
to lead

Select the USE database USE ma_base

USE
of work Order ... SELECT ...

Note: the use of * with SELECT in the form "SELECT * ..." leads to
the display of all columns from all tables mentioned after 'FROM'.

4.3 Comparison Operators

Operator(s) Return "true" if...

<>ou!= ...the two values are not equal

< ...the left value is strictly

lower than that of the right

> ...the left value is strictly

higher than that on the right

<= ...the left value is strictly

less than or equal to that on the right

>= ...the left value is strictly

greater than or equal to that on the right

BETWEEN…AND ...the tested value is located between

two given values

IN ...the tested value is located in a

list of given values

NOT IN ...the tested value is not located

in a given list of values

TIC – UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 21/31
 Ref: tuto_MySQL_WampServer.doc

LIKE ...the value on the left corresponds to

the one on the right (the one on the right can)
use the % character to simulate
any number of characters,
and _ for a single character

NOT LIKE ...the two values do not match

password

REGEXP or RLIKE ...the left value corresponds to

the given regular expression

NOT REGEXP ...the left value does not match

not to the given regular expression

IS NULL the tested value is null

IS NOT NULL ...the tested value is not null

4.4 The syntax of command lines

It is common to find oneself more easily in the line of code, certain

can be very long, to capitalize the COMMANDS and in
lowercase the information. But MySQL is case insensitive.

Base and table names cannot contain spaces or characters.

special, that's why we insert underscores "_". Although they are
accepted we will avoid accented characters in base names and
tables. However, we can use them without problem in the headings of
columns.

The names given to bases, tables, columns, fields, ... must be put
between ‘ (single quote) in most instructions.

The values created or given in an instruction are enclosed in "(...)" and

separated by commas ",".

Each instruction must end with a semicolon ";".

TIC - UIAD Website Creation 2thyear Daniel Gaudin – Jean Claude Chêne page 22/31
 Ref: tuto_MySQL_WampServer.doc

This gives us the following basic commands:

¾ To create a database: CREATE DATABASE base_essai ;

To create a table in our database:

¾ You must select the database with: USE base_test;

Créer en nommant la table : CREATE TABLE nom_de_table (pas de « ; » là car

the attributes in parentheses directly follow this command
Put at least one line to be able to create the table: (line_name
VARCHAR(25)); (here we specify that this line will be of character type
varied from 25 fields
Our command line will have the following structure:

¾ CREATE TABLE table_name (row_name VARCHAR(25));

Now we need to expand our table by adding rows and structures!

Let's add a column:

¾ ALTER table table_test ADD ref varchar(10);

Let's insert some data in it:

¾ INSERT INTO table_test VALUES ('value1', 'value2');

And let's make a request:

¾ SELECT ref FROM table_test;

TIC – UIAD Website Creation 2eighthyear Daniel Gaudin – Jean Claude Chêne page 23/31
 Ref: tuto_MySQL_WampServer.doc

5 Examples of SQL codes

5.1 Single Table Database

Let's create our database:

¾ CREATE DATABASE employees;

Let's create a table with 3 columns:

¾ CREATE TABLE personal_info (first_name char(20) not null, last_name
char(20) not null, employeeid int not null);

Let's add a column:

¾ ALTER TABLE personal_info ADD salary DOUBLE NULL;

Let's increment our initial values in the table:

¾ INSERT INTO personal_info values('bart','simpson',1000,45000);

Let's now insert several values at the same time:

¾ INSERT INTO `employees`.`personal_info` (`first_name`, `last_name`,
`employeeid`, `salary`) VALUES ('mickey', 'mouse', '1001', '32000'),
('donald', 'duck', '1002', '26000'), ('peter', 'pan', '1003', '64000'), ('fritz',
the cat

If we want to sort the list and display it in a specific order:

¾ ALTER TABLE `personal_info` ORDER BY `employeeid` ;

To display a column:
¾ SELECT last_name FROM personal_info;

To display the values of a column with a condition:

¾ SELECT * FROM personal_info WHERE salary > 40000 ;

To update all the values in a column:

¾ UPDATE personal_info SET salary = salary * 1.03 ;

TIC - UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 24/31
 Ref: tuto_MySQL_WampServer.doc

To change a value in a column:

UPDATE personal_info SET salary = salary + 5000 WHERE employeeid = 1002;

To delete data:
¾ DELETE FROM personal_info WHERE employeeid = 1003;

To empty a table of all its content:

¾ TRUNCATE TABLE `personal_info`;

To delete a table:
¾ DROP TABLE personal_info;

To delete a database:
¾ DROP DATABASE employees ;

TIC –UIAD Website creation 2thyear Daniel Gaudin - Jean Claude Chêne page 25/31
 Réf : tuto_MySQL_WampServer.doc

5.2 Multi-table database*

We will use the previous example by creating an additional table:

(if you have overwritten the 'employees' database, recreate it using the codes from
the previous example)
¾ CREATE TABLE disciplinary_action (action_id int not null, employeeid int
not null, comments char(255));

We will now input some data:

¾ INSERT INTO employees.disciplinary_action (`action_id`,
`employeeid`, `comments`) VALUES ('1', '1002', 'absence irrégulière'),
('2', '1004', 'mise à pied'), ('3', '1000', 'ivresse à son poste');

Let’s now look for which employees have had a sanction:

¾ SELECT personal_info.first_name, personal_info.last_name,
disciplinary_action.comments FROM personal_info, disciplinary_action
WHERE personal_info.employeeid = disciplinary_action.employeeid ;

For this, you need to be in the 'employees' database and on the SQL tab.

Lorsqu’on exécute l’instruction SQL nous renvoi bien …

TIC –UIAD Website creation 2emyear Daniel Gaudin – Jean Claude Chêne page 26/31
 Ref: tuto_MySQL_WampServer.doc

… a list with the Names and First Names of the employees along with the sanction received.

Si nous voulons rajouter un critère supplémentaire à notre requête nous

let's use the complement operator AND like this " AND
personal_info.salary > 40000 » which will give us :
¾ SELECT personal_info.first_name, personal_info.last_name,
disciplinary_action.comments FROM personal_info, disciplinary_action
WHERE personal_info.employeeid = disciplinary_action.employeeid AND
personal_info.salary > 40000 ;

And we send back:

Note well the syntax used to indicate to MySQL that we want

search in two tables :
SELECT table_name1.column_name, table_name2.column_name

FROM table_name1, table_name2 WHERE table_name1.column_name = ... ;

At each instruction in the query, the referenced table is added in front of the
column name with a liaison point.

TIC - UIAD Website Creation 2èmeyear Daniel Gaudin – Jean Claude Chêne page 27/31
 Ref: tuto_MySQL_WampServer.doc

Let's now add a field to our personal_info table:

¾ ALTER TABLE personal_info ADD (reportsto INTEGER NULL);
And let's fill them in:
¾ UPDATE `personal_info` SET `reportsto` = '220000' WHERE
`employeeid` = '1000' ;
¾ UPDATE `personal_info` SET `reportsto` = '300000' WHERE
`employeeid` = '1001' ;
¾ UPDATE `personal_info` SET `reportsto` = '180000' WHERE
`employeeid` = '1002' ;
¾ UPDATE `personal_info` SET `reportsto` = '130000' WHERE
`employeeid` = '1003' ;
¾ UPDATE `personal_info` SET `reportsto` = '230000' WHERE
`employeeid` = '1004' ;

Let's create a third table:

¾ CREATE TABLE employeeterritories (employeeid INTEGER NOT NULL,
territoryid INTEGER NOT NULL, state VARCHAR(5), city TINYTEXT);
We obtain:

TIC – UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 28/31
 Ref: tuto_MySQL_WampServer.doc

Let's now fill our table:

¾ INSERT INTO `employeeterritories` (`employeeid`, `territoryid`,
`state`, `city`) VALUES ('1000', '100', 'CA', 'Sacramento'), ('1001', '200',
'AZ', 'Phoenix'), ('1002', '300', 'TX', 'Austin'), ('1003', '400', 'GE',
'Atlanta'), ('1004', '500', 'FL', 'Tallahassee');

We obtain:

We will now make a query on our three tables that can

to express oneself this way:

Which employees (first name and last name) work in which place (city and ...
state) that have a turnover greater than $200,000 and have undergone a
disciplinary sanction?

¾ SELECT DISTINCT personal_info.first_name, personal_info.last_name,

[Link] [Link],
personal_info.reportsto, disciplinary action .comments FROM
personal_info, employeeterritories disciplinary action WHERE
personal_info.reportsto >200000 AND personal_info.employeeid =
[Link] AND personal_info.employeeid =
disciplinary_action. employeeid ;

It's up to you to find new examples!

TIC - UIAD Website creation 2thyear Daniel Gaudin - Jean Claude Chêne page 29/31
 Ref: tuto_MySQL_WampServer.doc

5.3 The join

Let's now introduce a new command, the JOIN command.

JOIN!
Still with our database 'employees', let's try this instruction:
¾ SELECT first_name, last_name, city, reportsto
¾ FROM personal_info
¾ JOIN employeeterritories
¾ ON personal_info.employeeid = [Link]
¾ WHERE reportsto >200000 ;
We obtain:

Let's make a query on our three tables:

¾ SELECT first_name, last_name, city, reportsto, comments FROM
personal_info
¾ JOIN employeeterritories ON personal_info.employeeid =
[Link]
¾ JOIN disciplinary action ON personal_info.employeeid =
disciplinary_action.employeeid
¾ WHERE reportsto > 200000
We obtain the same result as on the previous page:

TIC – UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 30/31
 Ref: tuto_MySQL_WampServer.doc

You can copy and paste the codes used in these examples but in
paying attention not to copy the bullet points at the beginning of the line!

Similarly, it can happen that the police is misinterpreted by WampServer and

that you have to retype the entire instruction.

TIC –UIAD Website creation 2thyear Daniel Gaudin – Jean Claude Chêne page 31/31