MySQL start

Примечание: Обратите внимение, что это не официальная документация, а попытка начинающего админа объяснить материал начинающим админам.

Теория

Виды баз данных

• Реляционные - (SQL) — база, где данные хранятся в формате таблиц, они строго структурированы и связаны друг с другом.

В них, обычно, планируются несерьезные, неглобальные изменения. Могут использовать сложные запросы и набор отношений.

• Нереляционные (NoSQL) — хранит данные без четких связей друг с другом и четкой структуры.

Могут/ не могут иметь отношения, не имеют четкой структуры. Меняются в процессе.

Структура

БД
└─СУБД
  └─пользователь

Между базой данных и пользователем используется СУБД - система управления базами данных - ПО обеспечивающее взаимодействие пользователя и БД.

Реляционная БД

Состоит из:

• сущность (таблица) - тип объектов, хранящихся в БД
  • поле - часть таблицы, за которой закреплен определенный тип данных. (содержит название и описание)
    • запись - строка таблицы (поле) БД, содержащая набор значений и свойств размещенный в полях.

Примечание: В поле необходимо всегда указывать тип записи. При этом нельзя указывать одинаковые записи - т.к. это идентификатор.

БД
├───таблица
│   ├───поле
│   │   └запись
│   ├───поле
│   │   └запись
│   └───поле
│       └запись
├───таблица
│   ├───поле
│   │   └запись
│   ├───поле
│   │   └запись
│   └───поле
│       └запись
└───таблица
    ├───поле
    │   └запись
    ├───поле
    │   └запись
    └───поле
        └запись

БД
├───автор
│   ├───библиография
│   ├───биография
│   ├───дата рождения
│   └───имя
├───книги
├───абонементы
└───сотрудник

В данном примере:

• БД - база данных;
• автор, книги, абонементы, сотрудник - таблицы;
• библиография, биография, дата рождения, имя - поля.

Типы данных

строковые

• char - строка фиксированной длины
  • пример char(20)
    • максимум 20 символов
    • при меньшем количестве переменная заполняется пробелами
    • больше 20 заполнить нельзя
• varchar - строка переменной длины
  • пример varchar(20)
  • будет записано 10 символов
  • в памяти будет хранится 10 символов и 1 говорящий о кол-ве используемых символов
• tiny - текст до 255 байт
• text - текст до 65 кб

числовые

• целые

tinyint     | 1 byte   -128 to 127                                  0 to 255
smallint    | 2 bytes  -32768 to 32767                              0 to 65535
mediumint   | 3 bytes  -8388608 to 8388607                          0 to 16777215
int/integer | 4 bytes  -2147483648 to 2147483647                    0 to 4294967295
bigint      | 8 bytes  -9223372036854775808 to 9223372036854775807  0 to 18446744073709551615

• дробные (указывается в скобках (всего, точность))
  • decimal(3,2) => (xxx,yy) => -999,99 - 999,99
  • float(a,b)
  • double(a,b)

Практика

Установка MySQL

Согласно MySQL:

$ su -
# apt-get install MySQL-server
# service mysqld enable
# service mysqld start

Создание пароля:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'you_own_pass';

Shell СУБД

Запуск

$ su -
# mysql -u root -p
mysql>

Создание/удаление

• Вывести список БД:

show databases;

• Создать БД:

create database name;
create database if not exists name;

• Удалить БД:

drop database name;
drop database  if exists name;

Работа с сущностями

База данных
├подключиться к базе данных: use ''name'';
├отобразить список баз данных: show databases;
├создать базу данных: create database ''name''; или create database if not exists ''name'';
├удалить базу данных: drop database ''name''; или drop database if exists ''name'';
└── Сущность
    ├ вывести список сущностей (таблиц) (из-под подключенной БД): show tables;
    ├ создать сущность (таблицу) (из-под подключенной БД): create table ''name''(''запись'' ''тип'', ''запись'' ''тип'');
    ├ удалить сущность (таблицу) (из-под подключенной БД): drop table ''name'';
    └── Записи
        ├ ввести данные соответственно типам: insert into ''имя таблицы'' values (''значение записи в соответствии с типом'', ''значение записи в соответствии с типом'');
        ├ вывести записи таблицы: describe ''имя таблицы'';
        ├ вывести все значения таблицы: select * from ''имя таблицы'';
        ├ вывести определенные значения таблицы: select ''имя столбца'', ''имя столбца'' from ''имя таблицы'' where '''(условие)''';
        ├ изменить все значения записи: update ''имя таблицы'' set ''запись'' = ''значение'';
        ├ изменить определенные значения записи: update ''имя таблицы'' set ''запись'' = ''значение'' where '''(условие)''';
        ├ удаление всех записей: delete from  ''имя таблицы'' 
        ├ удаление определенной записи: delete from  ''имя таблицы'' where '''(условие)''';
        └ 

Создание БД

• смотрим, что у нас нет никаких БД
• создаем БД test
• подключаемся к ней.

>show databases;
>create database test;
>use test;

• Создаем таблицу "авторы"

>create table authors (id int, name varchar(30), lastname varchar(30), middlename varchar(30), books text, rating tinytext);

ИЛИ:

mysql> create table authors(
    -> id int,
    -> name varchar(30),
    -> lastname varchar(30),
    -> middlename varchar(30),
    -> books text,
    -> rating tinytext);

• смотрим список таблиц и выводим содержимое таблицы

>show tables;
>describe authors;

Ввод данных

Смотрим содержимое таблицы:

mysql> describe authors;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | YES  |     | NULL    |       |
| name       | varchar(30) | YES  |     | NULL    |       |
| lastname   | varchar(30) | YES  |     | NULL    |       |
| middlename | varchar(30) | YES  |     | NULL    |       |
| books      | text        | YES  |     | NULL    |       |
| rating     | tinytext    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0,00 sec)

По типам данных вводим данные:

> insert into authors values (1, 'Лев', 'Толстой', 'Николаевич', 'Война и мир', '5');
> insert into authors values (2, 'Федор', 'Достоевский', 'Михаилович', 'Преступление и наказание', '4');

Так же, если не нужно заполнять все данные, можно это сделать выборочно:

> insert into authors(name, lastname, rating) values('noname','noname',3);

и множественно:

> insert into authors(name, lastname, rating) values('noname','noname',3), ('noname2', 'noname2',2);

Вывести новые значения таблицы:

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    1 | Лев        | Толстой                | Николаевич           | Война и мир                                    | 5      |
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       | 3      |
| NULL | noname     | noname                 | NULL                 | NULL                                           | 3      |
| NULL | noname     | noname                 | NULL                 | NULL                                           | 3      |
| NULL | noname2    | noname2                | NULL                 | NULL                                           | 2      |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
5 rows in set (0,00 sec)

Обновление данных

Все

Изменить все значения записи:

update authors set rating = 1;

Вывести новые значения таблицы:

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    1 | Лев        | Толстой                | Николаевич           | Война и мир                                    |      1 |
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       |      1 |
|    0 | noname     | noname                 | NULL                 | NULL                                           |      1 |
|    0 | noname     | noname                 | NULL                 | NULL                                           |      1 |
|    0 | noname2    | noname2                | NULL                 | NULL                                           |      1 |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
5 rows in set (0,00 sec)

Определенные

Изменить опр. значения записи:

update authors set rating = 1 where (условие);

Например:

update authors set rating = 5 where lastname='Толстой';

или

update authors set rating = 4 where rating<5;

или

update authors set rating = 3, middlename='nomiddlename' where lastname='noname';

Вывести новые значения таблицы:

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    1 | Лев        | Толстой                | Николаевич           | Война и мир                                    |      5 |
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       |      4 |
|    0 | noname     | noname                 | nomiddlename         | NULL                                           |      3 |
|    0 | noname     | noname                 | nomiddlename         | NULL                                           |      3 |
|    0 | noname2    | noname2                | NULL                 | NULL                                           |      4 |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
5 rows in set (0,00 sec)

Удаление данных

Удаление определенной записи:

delete from authors where name='Лев';

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       |      4 |
|    0 | noname     | noname                 | nomiddlename         | NULL                                           |      3 |
|    0 | noname     | noname                 | nomiddlename         | NULL                                           |      3 |
|    0 | noname2    | noname2                | NULL                 | NULL                                           |      4 |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
4 rows in set (0,00 sec)

Удаление определенной записи с логическим условием:

delete from authors where rating=3 AND name='noname';

mysql> select * from authors;
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
| id   | name       | lastname               | middlename           | books                                          | rating |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
|    2 | Федор      | Достоевский            | Михаилович           | Преступление и наказание                       |      4 |
|    0 | noname2    | noname2                | NULL                 | NULL                                           |      4 |
+------+------------+------------------------+----------------------+------------------------------------------------+--------+
2 rows in set (0,00 sec)

Удаление всей таблицы:

delete from authors;

mysql> select * from authors;
Empty set (0,00 sec)

Выборка данных

select 'имя столбца', 'имя столбца' from 'имя таблицы' where '(условие)';

mysql> select name, books from authors where rating=3;
+------------+------------------------------------------------+
| name       | books                                          |
+------------+------------------------------------------------+
| Федор      | Преступление и наказание                       |
| noname     | NULL                                           |
| noname     | NULL                                           |
+------------+------------------------------------------------+
3 rows in set (0,00 sec)

Условия

• =(условие)
• !=(условие)
• >, <, >=, <=
• (условие)AND(условие), (условие)OR(условие)
• between (значение) AND (значение);
• in (значение)
• not (значение)

Параметры

(пишутся в конце)

• limit n - где вместо n кол-во выводимых строк, вместо 1000 по-умолчанию;
• order by столбец

MySQL Workbench

Еще одним удобным СУБД для MySQL является программа MySQL Workbench.

Для ее установки нужно поставить следующий пакет:

# apt-get install mysql-workbench-community

А чтобы подключиться с локального компьютера к локальной БД нужно выполнить следующую настройку MySQL.

В файле /etc/my.cnf.d/server.cnf нужно закомментировать строку skip-networking.

# mcedit /etc/my.cnf.d/server.cnf

Привести строчку к следующему виду:

#skip-networking

И перезапустить службу:

# service mysqld restart

Коды

Вместо того, чтобы писать все построчно в консоли, в Workbench можно ввести целый скрипт и запустить его. Например:

-- создаем БД, если она не существует и подключаемся к ней
drop database if exists test2;
show databases;
create database if not exists test2;
use test2;
-- создаем таблицу для пользователей
create table if not exists users
(
id int,
username varchar(30),
email varchar (40),
password_hash varchar(200),
age tinyint,
index (username, email)
);

-- вводим значения
insert into users values
(0, 'Petr', 'user@mail.ru', 'f0dlws', '34'),
(1, 'Sergey', 'user2@mail.ru', 'hdjrbk', '22');
insert into users(username, email) values
('Grigory','grig@mail.ru'),
('Vladimir','vlad@mail.ru'),
('Mikhail','miha@mail.ru');

-- обновляем и удаляем значения
update users set id = 0 where username='Vladimir';

update users set username = 'Alexander' where username= 'Petr';

delete from users where username='Vladimir';

-- выводим значения
select * from users;