# MySQL/MySQL Practical Guide

## Installing MySQL

### All in one solutions

As MySQL alone isn\'t enough to run a real database server, the more
practical way to install it is to deploy an all in one
pack in this purpose,
including all the needed additional elements:
Apache and
PHP.

1.  On Linux: XAMP or LAMP "wikilink").
2.  On Windows: XAMP, WAMP, or
    EasyPHP.

**Attention on Windows 10:**

-   The server IIS is launched by default, which forces Apache to change
    its port (888 instead of 80). To resolve this, just untick *Internet
    Information Services* in *Programs and functionalities*, *Activate
    or deactivate the Windows functionalities.* In the same way, the
    MySQL port can change from 3306 to 3388.
-   Moreover, *EasyPHP development server* (alias *Devserver*, the red
    version) doesn\'t work properly (*MSVCR110.dll is missing*) but
    *EasyPHP hosting server* (alias *Webserver*, the blue one) yes.
    However, it launched automatically at each boot which slows the
    system significantly. To avoid this, execute *services.msc*, and
    toggle the three services below in manual start. Then to launch them
    on demand (as an administrator), create a script called *MySQL.cmd*,
    containing the following lines:

``` dos
net start ews-dbserver
net start ews-httpserver
net start ews-dashboard
pause
net stop ews-dashboard
net stop ews-httpserver
net stop ews-dbserver
```

### Single installation

*This guide is written from the perspective of using the Linux Shell
with Ubuntu and
apt-get*1.

_If you want to solely use the Terminal_:

Make sure you have the MySQL Client and Server installed. To install the
client and the server under apt-get distributions (for example Debian
and Ubuntu), Execute:

    apt-get install mysql-client mysql-client-5.0 mysql-server mysql-server-5.0

_About the MySQL package:_

2

_Having a secure installation_:

If all your answers are \"yes\" to what follows, this cleans up your
installation, forces you to set a root password, asks you to test for
anonymous users and makes your database internal.

Just be careful. Be sure that you are configuring MySQL to the
specifications you want.

Here\'s the code:

    mysql_secure_installation

## Creating your own MySQL account and database:

Now that MySQL is installed, you wouldn\'t necessarily have your own
account, so you have to log in as root.

To do this type:

    sudo mysql -u root -p

(This means that you\'re logging on as the user \"root\" (**-u root**)
and that you\'re requesting the password for \"root\" (**-p**) )

Once you\'ve managed to log in, your command-line should look like this:
**mysql\>**

By the way, if your command-line ends up looking like this: **-\>**
theres an explanation behind it.

In MySQL each command you do has to end with **;** . This way it knows
that everything behind **;** is a command.

So to get out of there, simply type **;** There will be more on this
later.

Now you can check what databases (if any) are available to your user (in
this case \"root\" ):

    show databases;

Let\'s get straight to the chase and create our own database. Let\'s
call it **people**. While we\'re doing this we can also create our own
user account. Two birds with one stone.

So first create the database:

    create database people; 

(NOTE: in this particular case, you have to be \"root\" to create new
databases.)

Now we want to grant ( **GRANT** ) all user rights ( **ALL** ) from (
**ON** ) the entire ( **\*** ) **people** database to ( **TO** ) your
account ( *yourusername***\@localhost** ) with your user password being
*stuffedpoodle* ( **IDENTIFIED BY \"stuffedpoodle\"** ).

So we\'d input this as:

    GRANT ALL ON people.* TO yourusername@localhost IDENTIFIED BY "stuffedpoodle";

Tada! You now have your own user account. Let\'s say you chose **ted**
as your username. You\'ve configured MySQL to say that **ted** can play
around with the **people** database in whatever ways he wishes.

Now get out of MySQL by typing

    exit

To start working with the **people** database, you can now login as
**ted**:

    mysql -u ted -p

## Creating tables with information in your database:

In MySQL information is stored in tables. Tables contain columns and
rows.

**Ted** has now created a **people** database. So we want now to enter
some information into a table.

Login as **ted**.

Firstly, we need to make sure we\'re working with the **people**
database. So typing:

     select database(); 

will show you what database you\'re currently using. You should see a
**NULL** , meaning that you\'re working with nothing at the moment.

So to start using the people database, type:

    \u people

(NOTICE: Typing: **USE people** OR logging in as **mysql people -u ted
-p** is also acceptable.)

So how to create a table.

Keep in mind that we need to set all the column values (like surname,
age etc.).

Now, remember that annoying **-\>** symbol? MySQL reads your command as
just one command, not a series. So, **-\>** enables you to enter your
inputs in a nicer way than just writing everything on one line. (NOTE:
The problem with this method is that if you screw up on a line and press
ENTER to go to the next line, you can\'t go back and fix your mistake.
That\'s why a nice way to do this is using something like *SciTE Text
Editor* (set language to **SQL**) to write your code and just copy/paste
that into the shell.)

Another thing is that you must separate your lines with **,** at the end
of each line except when you\'ve written your two last lines. On the
second to last line, **don\'t** add **,** and the last line always ends
with **;** .

First I have to explain a few things so you\'re not blown away by an
unfamiliar bunch of code.

If you don\'t know, we use brackets **()** to **encapsulate** code.
(Often called *parenthesis*).

The first thing we will be writing after the **CREATE TABLE
\'\'tableName***and the first bracket will be the*database ID\'\'
number(we use integers 3) of
each person, mainly known as the **Primary Key**. It\'s kinda like a
passport ID number. Each number is unique to its owner and it has to be
to prevent duplication and imposters.

Now, any variable in SQL is created as

     variableNAME variableTYPE otherVariableAttributes

. So in order to **define** the Primary Key variable, we need to type
for example:

**peopleID**(variableNAME) **int**(variableTYPE - short for \"integer\")
**unsigned**(means we want our integer value to always be a positive
number) **not null**(we want each row to have a value, so obviously the
value can\'t be empty(NULL) ) **auto_increment**(this ensures that each
new row that is created will be a unique value) **primary key**(we are
saying that this particular variable will be our Primary Key for this
Table.)**,** (a reminder that the **,** symbol indicates the end of this
line so MySQL knows to go to the next line)

You already know about the **int** variable. There is another which is
kinda like *String* (for example: if you\'ve programmed in Java before).
It\'s called **varchar** which stands for *variable characters*. You set
the amount of characters someone is able to input into a **varchar**
variable. Like this: **nameOfFattestMooseAlive varchar(30)** So
**nameOfFattestMooseAlive** can have a maximum of 30 characters.

Okay, so let\'s see an example of how to create a table relating to the
**people** database:

    CREATE TABLE peopleInfo

    (

    peopleID int unsigned not null auto_increment primary key,

    firstName varchar(30),

    lastName varchar(30),

    age int,

    gender varchar(13)

    );

Just a note that I set the maximum value of **gender** to 13 because
\"hermaphrodite\" has 13 characters. :)

Now you can type: **CREATE TABLE peopleInfo** and press ENTER if you\'d
like to start **-\>** and write the rest of the code or you can use
SCITE and copy/paste it into your shell.

Great. We now completed our first Table.

_Now comes the part when we have to get some actual people
into our **peopleInfo** Table._

Since your already using the **people** database, you can type

    show tables;

to see what tables are currently in your database.

To see the *properties* of your table type:

    describe peopleInfo;

So, how to fill in our **peopleInfo** table with people\...

This is done by telling MySQL **what** *rows* you are filling in and the
**actual information/data** you want to fill in.

So we want to **insert into** our table (specifying the rows) and
inputting the **values**(actual data) that we want. (NOTE: We are not
filling in the primary key.)

To create our first person you would type this:

    INSERT INTO peopleInfo

    (firstName, lastName, age, gender)

    values

    ("Bill", "Harper", 17, "male");

Great. Now if you want to printout to the screen all the information
about your table, type:

    select * from peopleInfo;

and there you have it. Your table now has one person stored in it.

_ Inserting lots of information into your table:_

A brief point that shall be covered later, MySQL backs-up itself in .sql
files. The reason this is smart is because it backs-up the actual code
inside the text file.

Keeping this in mind, let\'s say we want to add 10 other people into
your peopleInfo table. It would be one hell of a hassle typing each
person into existence. What if there were a 1000?

So I\'ve graciously typed out the code of filling in 10 other people to
a database. :) Create a blank .txt file and copy/paste this information
into it, saving it as **tenPeople.sql** .

    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mary", "Jones", 21, "female");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jill", "Harrington", 19, "female");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Bob", "Mill", 26, "male");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Alfred", "Jinks", 23, "male");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Sandra", "Tussel", 31, "female");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mike", "Habraha", 45, "male");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("John", "Murry", 22, "male");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jake", "Mechowsky", 34, "male");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Hobrah", "Hinbrah", 24, "hermaphrodite");
    INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Laura", "Smith", 17, "female");

Excellent. Now we want to get all these people in our table. **exit**
MySQL and go to the directory where you saved the **tenPeople.sql**
file.

Once there, to get all the data into your database, type:

    mysql -u ted -p people <tenPeople.sql

and enter your password.

Now log into MySQL and remember to select the database your using. **\\u
people**

Now check again what information you have. There ya go.

## Manipulating your database:

Now that we have a database full of people. We can display that
information anyway we want. A brief example would be

    select firstName, lastName, gender from peopleInfo;

This would display to the screen only people\'s names, surnames, and
genders. You\'ve not specified that you want people\'s Database IDs,
Numbers, or Ages to be displayed. And the great thing is you can choose
whatever you want from the database to be displayed.

Now, if you want to delete your table, simply type:

    drop table peopleInfo;

Extra conditions:

You can also you extra conditions (filters) through when displaying
data.

    select * from peopleInfo where gender = 'female';

will display everyone who is female.

(NOTE: letters are enclosed with **\'** while numbers are plain.)

You can also compare numbers. For example:

    select * from peopleInfo where age > 17;

will show everyone in your table who is older than 17.

Little index here:

    >   greater than
    <   less than
    >=  greater or equal to
    <=  less than or equal to
    <>  not equal to

Let\'s say we wanted to display all people whose first names began with
the letter \"j\". We would use the LIKE condition. (Makes sense, is your
name LIKE the letter \"j\", well it start with j so yes. :) )

About the LIKE condition.

4

    select * from peopleInfo where firstName LIKE "j%";

(NOTE: LIKE \'s evil opposite cousin is NOT LIKE)

## Backing up and restoring your MySQL database:

There is a function called **mysqldump**. This is a way to backup your
database.

Remember how you managed to get information into your database from
**tenPeople.sql**? Well that\'s how you restore information to a
database.

(In this particular case you gotta make sure that in your database you
have a table called \"peopleInfo\")

Now\...

To backup your database (in this case backup the **people** database):

We first have to create the .txt file that we will be backing it up to.
Open a blank .txt file and save it as **backupfile.sql** .

Now we can type:

    mysqldump -u ted -p people > backupfile.sql

Congratulations. You have now backuped your people**database.**

`<b>`{=html}WARNING!`</b>`{=html} `mysqldump` is one of the worst ways
to backup production databases for the following reasons:

-   it will take quite a lot of time to dump data
-   even more time to restore. Depends on datasize, it can be counted in
    days!
-   locking problem with MyISAM tables or mixed environment

Better solutions are based on binary copy. It allows you to perform
non-locking, consistent backups.

For MyISAM or mixed environment:

-   LVM snapshots

For InnoDB:

-   LVM snapshots
-   ZFS snapshots (for Solaris systems)
-   InnoDB Hot Backup
-   XtraBackup (similar to InnoDB Hot Backup but free)

## phpMyAdmin

This graphic interface allows the generation of SQL code by selecting
some options with the mouse. This software has its own wiki on
<http://wiki.cihar.com/pma/Welcome_to_phpMyAdmin_Wiki>.

## Hello world

To enter the SQL commands:

-   Launch MySQL in shell:
    -   Linux: `mysql` -h localhost -u root MyDB
    -   Windows:
        `"C:\Program Files (x86)\EasyPHP\binaries\mysql\bin\mysql.exe"`
        -h localhost -u root MyDB
-   Or open an SQL window in PhpMyAdmin (eg:
    <http://localhost/modules/phpmyadmin/#PMAURL-1:server_sql.php?server=1>).

``` mysql
select "hello world";
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)
```

