Technical Diary - With Andrii Grytsenko

Here I systematized some of my MYSQL knowledges.

First I decide to make some test database to do everything step-by-step. This will be companies database contains information about all employers, projects and departments.

CREATE DATABASE company

Go in:

USE company

And make 4 tables.

This table consists relation between employer and projects:

Let’s create its in DB:

CREATE TABLE employers (
employer_id int primary key auto_increment,
name varchar(15),
surname varchar(25),
hire_date date,
department_id int,
fired int);

CREATE TABLE projects (
project_id int primary key auto_increment,
name varchar(255));

CRETE TABLE departments (
department_id int primary key auto_increment,
name varchar(255));

CREATE TABLE project_involved (
employer_id int,
project_id int);

And insert some data into them. I will show you only few of them:

INSERT INTO projects (departments) VALUES ('General Project');

In this insert I use subquery:

INSERT INTO employers
(name,surname,hire_date,department_id,fired)
VALUES ('Jonh','Smith','2007-10-30',
(SELECT department_id FROM departments WHERE name='Department A'),
0);

Format of date should be the next:

YYYY-MM-DD

There is also some example of SELECT query in the UPDATE statement:

INSERT INTO projects (name) VALUES ('General Project');

INSERT INTO project_involved (employer_id,project_id)
VALUES (
(SELECT employer_id FROM employers WHERE name='Jonh' AND surname='Smith'),
(SELECT project_id FROM projects WHERE name='General Project')
);

In the ending of filling db up I’ve got next results:

SELECT * FROM employers;
+-------------+------+---------+------------+---------------+-------+
| employer_id | name | surname | hire_date  | department_id | fired |
+-------------+------+---------+------------+---------------+-------+
|           1 | Jonh | Smith   | 2007-10-30 |             1 |     0 |
|           2 | Yan  | Brown   | 2008-11-23 |             2 |     0 |
+-------------+------+---------+------------+---------------+-------+
2 rows in set (0.00 sec)

SELECT * FROM project_involved;
+-------------+------------+
| employer_id | project_id |
+-------------+------------+
|           1 |          1 |
|        NULL |          2 |
+-------------+------------+
2 rows in set (0.00 sec)

SELECT * FROM projects;
+------------+-----------------+
| project_id | name            |
+------------+-----------------+
|          1 | General Project |
|          2 | Second Project  |
+------------+-----------------+
2 rows in set (0.00 sec)

SELECT * FROM departments;
+---------------+---------------+
| department_id | name          |
+---------------+---------------+
|             1 | departament A |
|             2 | departament B |
+---------------+---------------+
2 rows in set (0.00 sec)

If you need to to remove something from your table(Department D from departments for example):

DELETE FROM departments WHERE name='Department D';

I’ve totally forgot about some column should be at employers table. Column position has to be added. To change the structure of the table use ALTER:

ALTER TABLE employers
ADD COLUMN position varchar(255) AFTER surname;

in the same way you can remove needed column from the table:

ALTER TABLE employers DROP COLUMN position;

To rename column name run:

ALTER TABLE table_name CHANGE old_name new_name INTEGER

Now update the data:

UPDATE employers SET position='system engineer'
WHERE surname='Smith' AND name='Jonh';

There are few examples with subqueries. If you need to select all data from emloyers table and resolve department name from departments table. Run:

SELECT employers.name, employers.surname, employers.hire_date, departments.name
FROM employers INNER JOIN departments USING (department_id);

To simplify a query use alias:

SELECT e.name, e.surname, e.hire_date, d.name FROM employers e
INNER JOIN departments d USING (department_id);
+------+---------+------------+---------------+
| name | surname | hire_date  | name          |
+------+---------+------------+---------------+
| Jonh | Smith   | 2007-10-30 | departament A |
| Yan  | Brown   | 2008-11-23 | departament C |
+------+---------+------------+---------------+
2 rows in set (0.00 sec)

Here is more complex query which is involves all 4 tables:

SELECT e.name, e.surname, e.position, e.hire_date, d.name department, p.name project
FROM projects p
INNER JOIN project_involved USING (project_id)
INNER JOIN employers e USING (employer_id)
INNER JOIN departments d USING (department_id);
+------+---------+-----------------+------------+---------------+-----------------+
| name | surname | position        | hire_date  | department    | project         |
+------+---------+-----------------+------------+---------------+-----------------+
| Jonh | Smith   | system engineer | 2007-10-30 | departament A | General Project |
| Jonh | Smith   | system engineer | 2007-10-30 | departament A | Second Project  |
+------+---------+-----------------+------------+---------------+-----------------+
2 rows in set (0.00 sec)

To remove database or table use DROP:

DROP [database|table]

For example:

DROP employers;

To see database or table structure use SHOW CREATE:

SHOW CREATE TABLE table;

SHOW CREATE DATABASE company;

SHOW CREATE DATABASE company;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| company  | CREATE DATABASE `company` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+

BACKUPS

To make backups of your database use mysqldump :

mysqldump -u root -p --database company > company.sql

To restores from backups :

mysql -u root -p -e 'source company.sql' company

CREATE USER

GRANT [privileges] ON [resource] TO [identity]

In example I create user company_admin which has all privileges on all tables in database company from host localhost with password secret-password:

GRANT ALL PRIVILEGES ON company.* TO company_admin@localhost
IDENTIFIED BY 'secret_password';

There are some of the privileges, almost all of them is self-explained:

ALTER
CREATE
DELETE
DROP
GRANT
INSERT
SELECT
UPDATE

For more details concerning GRANT go to ofdocs.

MYISAM VS INNODB

InnoDB is set by default. And it’s speed and mature way to store simple data.

Advantages of InnoDB:

1. transaction support
2. row-level locking
3. foreign key constraints

To disadvantages we can attribute the next:

1. don’t support full-text searching
2. some troubles with AUTO_INCREMENT columns

To change of the table engine run:

ALTER TABLE table ENGINE=[engine]

ALTER TABLE employers ENGINE=InnoDB

FOREIGN KEY

This feature help you to prevent your data from human mistake and keep it integrity.
First, you need to convert your tables into InnoDB:

ALTER TABLE table ENGINE=InnoDB;

and set dependency:

ALTER TABLE employers ADD CONSTRAINT dep_id_cons
FOREIGN KEY (department_id) REFERENCES departments (department_id);

Now when we try to set employers.department_id which is not at departments.department_id. As result changes is not committed and we get an error:

INSERT INTO employers (name,surname,department_id)
VALUES ('test','test','123');
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`company/employers`,
CONSTRAINT `dep_id_cons` FOREIGN KEY (`department_id`)
REFERENCES `departments` (`department_id`))

To delete foreign key run:

ALTER TABLE employers DROP FOREIGN KEY dep_id_cons;