Наши партнеры

UnixForum

Библиотека сайта rus-linux.net

Серверы Linux. Часть II. База данных MySQL

Оригинал: Introduction to sql using mysql
Автор: Paul Cobbaut
Дата публикации: 24 мая 2015 г.
Перевод: A.Панин
Дата перевода: 11 июля 2015 г.

Глава 3. Вводная информация о структурированном языке запросов SQL и сервере базы данных MySQL

3.4. Таблицы базы данных MySQL

3.4.1. Вывод списка таблиц

Вы можете ознакомиться со списком таблиц текущей базы данных, воспользовавшись командой show tables. Наша база данных с именем famouspeople пока не содержит таблиц. 

mysql> use famouspeople;
Database changed
mysql> show tables;
Empty set (0.00 sec)

3.4.2. Создание таблицы

Команда create table позволяет создать новую таблицу в базе данных.

В примере ниже показана методика создания таблицы с именем country для хранения информации о странах. Мы используем поле countrykey в качестве первичного ключа (все коды стран являются уникальными). Большинство кодов стран состоит из двух или трех букв, поэтому благодаря использованию типа данных char(3), соответствующего трехсимвольной строке с фиксированной длиной вместо типа данных varchar(3), соответствующего трехсимвольной строке с переменной длиной, удается сэкономить небольшой объем памяти. В полях countryname и cauntrycapital, предназначенных для хранения названий страны и столицы соответственно, используется тип данных varchar, соответствующий строке с переменной длиной. В поле population, предназначенном для хранения информации о численности населения страны, используется целочисленный тип данных integer. 

mysql> create table country (
    -> countrycode char(3) NOT NULL,
    -> countryname varchar(70) NOT NULL,
    -> population int,
    -> countrycapital varchar(50),
    -> primary key (countrycode)
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> show tables;
+------------------------+
| Tables_in_famouspeople |
+------------------------+
| country                |
+------------------------+
1 row in set (0.00 sec)

mysql>

Вы можете вводить команду для создания таблицы в базе данных create table в формате одной длинной строки, но администраторы серверов баз данных зачастую используют переносы на новые строки для улучшения читаемости вводимых команд. 

mysql> create table country ( countrycode char(3) NOT NULL, countryname\
 varchar(70) NOT NULL, population int, countrycapital varchar(50), prim\
ary key (countrycode) );
Query OK, 0 rows affected (0.18 sec)

3.4.3. Вывод описания таблицы

Для ознакомления с описанием структуры таблицы базы данных следует выполнить команду describe $имя_таблицы таким образом, как показано ниже. 

mysql> describe country;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| countrycode    | char(3)     | NO   | PRI | NULL    |       |
| countryname    | varchar(70) | NO   |     | NULL    |       |
| population     | int(11)     | YES  |     | NULL    |       |
| countrycapital | varchar(50) | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.4.4. Удаление таблицы

Для удаления таблицы из базы данных следует выполнить команду drop table $имя_таблицы таким образом, как показано ниже. 

mysql> drop table country;
Query OK, 0 rows affected (0.00 sec)

3.5. Записи в базе данных MySQL

3.5.1. Создание записей

Используйте запрос insert для добавления информации в базу данных. В примере ниже показан процесс исполнения нескольких запросов insert, благодаря которым в таблицу базы данных были добавлены значения в соответствии с порядком их следования. 

mysql> insert into country values ('BE','Belgium','11000000','Brussels');
Query OK, 1 row affected (0.05 sec)

mysql> insert into country values ('DE','Germany','82000000','Berlin');
Query OK, 1 row affected (0.05 sec)

mysql> insert into country values ('JP','Japan','128000000','Tokyo');
Query OK, 1 row affected (0.05 sec)

Некоторые администраторы серверов баз данных предпочитают записывать ключевые слова SQL в верхнем регистре. Клиентское приложение mysql принимает запросы в обоих форматах. 

mysql> INSERT INTO country VALUES ('FR','France','64000000','Paris');
Query OK, 1 row affected (0.00 sec)

Учтите, что использование уже существующего в рамках таблицы базы данных первичного ключа при добавлении новой записи в таблицу базы данных приведет к генерации сообщения об ошибке. 

mysql> insert into country values ('DE','Germany','82000000','Berlin');
ERROR 1062 (23000): Duplicate entry 'DE' for key 'PRIMARY'

3.5.2. Ознакомление со списком всех записей

Ниже приведен пример использования простого запроса select для вывода списка всех записей таблицы базы данных. 

mysql> select * from country;
+-------------+---------------+------------+----------------+
| countrycode | countryname   | population | countrycapital |
+-------------+---------------+------------+----------------+
| BE          | Belgium       |   11000000 | Brussels       |
| CN          | China         | 1400000000 | Beijing        |
| DE          | Germany       |   82000000 | Berlin         |
| FR          | France        |   64000000 | Paris          |
| IN          | India         | 1300000000 | New Delhi      |
| JP          | Japan         |  128000000 | Tokyo          |
| MX          | Mexico        |  113000000 | Mexico City    |
| US          | United States |  313000000 | Washington     |
+-------------+---------------+------------+----------------+
8 rows in set (0.00 sec)

3.5.3. Обновление записей

Рассмотрите следующий запрос insert. Но ведь столицей Испании является не Барселона, а Мадрид. 

mysql> insert into country values ('ES','Spain','48000000','Barcelona');
Query OK, 1 row affected (0.08 sec)

С помощью запроса update данная запись может быть обновлена. 

mysql> update country set countrycapital='Madrid' where countrycode='ES';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Мы можем воспользоваться запросом select для проверки корректности данного обновления. 

mysql> select * from country;
+-------------+---------------+------------+----------------+
| countrycode | countryname   | population | countrycapital |
+-------------+---------------+------------+----------------+
| BE          | Belgium       |   11000000 | Brussels       |
| CN          | China         | 1400000000 | Beijing        |
| DE          | Germany       |   82000000 | Berlin         |
| ES          | Spain         |   48000000 | Madrid         |
| FR          | France        |   64000000 | Paris          |
| IN          | India         | 1300000000 | New Delhi      |
| JP          | Japan         |  128000000 | Tokyo          |
| MX          | Mexico        |  113000000 | Mexico City    |
| US          | United States |  313000000 | Washington     |
+-------------+---------------+------------+----------------+
9 rows in set (0.00 sec)

3.5.4. Вывод определенных записей

Благодаря возможности использования определения where в рамках запроса select вы можете явно указать серверу базы данных на то, какую запись или записи вы хотели бы получить. 

mysql> SELECT * FROM country WHERE countrycode='ES';
+-------------+-------------+------------+----------------+
| countrycode | countryname | population | countrycapital |
+-------------+-------------+------------+----------------+
| ES          | Spain       |   48000000 | Madrid         |
+-------------+-------------+------------+----------------+
1 row in set (0.00 sec)

А это другой пример использования определения where. 

mysql> select * from country where countryname='Spain';
+-------------+-------------+------------+----------------+
| countrycode | countryname | population | countrycapital |
+-------------+-------------+------------+----------------+
| ES          | Spain       |   48000000 | Madrid         |
+-------------+-------------+------------+----------------+
1 row in set (0.00 sec)

3.5.5. Первичный ключ в рамках определения where?

Первичный ключ таблицы базы данных представлен полями, которые уникальным образом идентифицируют каждую из записей (строк) этой таблицы. В случае использования другого поля в рамках определения where вполне вероятен вывод нескольких записей. 

mysql> insert into country values ('EG','Egypt','82000000','Cairo');
Query OK, 1 row affected (0.33 sec)

mysql> select * from country where population='82000000';
+-------------+-------------+------------+----------------+
| countrycode | countryname | population | countrycapital |
+-------------+-------------+------------+----------------+
| DE          | Germany     |   82000000 | Berlin         |
| EG          | Egypt       |   82000000 | Cairo          |
+-------------+-------------+------------+----------------+
2 rows in set (0.00 sec)

3.5.6. Упорядочивание записей

Мы уже знаем о том, что запрос select позволяет вывести список всех записей таблицы базы данных. Рассмотрим следующую таблицу. 

mysql> select countryname,population from country;
+---------------+------------+
| countryname   | population |
+---------------+------------+
| Belgium       |   11000000 |
| China         | 1400000000 |
| Germany       |   82000000 |
| Egypt         |   82000000 |
| Spain         |   48000000 |
| France        |   64000000 |
| India         | 1300000000 |
| Japan         |  128000000 |
| Mexico        |  113000000 |
| United States |  313000000 |
+---------------+------------+
10 rows in set (0.00 sec)

Благодаря использованию определения order by мы можем изменить порядок, в котором будут выведены строки таблицы базы данных. 

mysql> select countryname,population from country order by countryname;
+---------------+------------+
| countryname   | population |
+---------------+------------+
| Belgium       |   11000000 |
| China         | 1400000000 |
| Egypt         |   82000000 |
| France        |   64000000 |
| Germany       |   82000000 |
| India         | 1300000000 |
| Japan         |  128000000 |
| Mexico        |  113000000 |
| Spain         |   48000000 |
| United States |  313000000 |
+---------------+------------+
10 rows in set (0.00 sec)

3.5.7. Группировка записей

Рассмотрите следующую таблицу с информацией об известных людях. В примере ниже показана методика использования функции avg для расчета среднего значения для столбца с их годами рождения. 

mysql> select * from people;
+-----------------+-----------+-----------+-------------+
| Name            | Field     | birthyear | countrycode |
+-----------------+-----------+-----------+-------------+
| Barack Obama    | politics  | 1961      | US          |
| Deng Xiaoping   | politics  | 1904      | CN          |
| Guy Verhofstadt | politics  | 1953      | BE          |
| Justine Henin   | tennis    | 1982      | BE          |
| Kim Clijsters   | tennis    | 1983      | BE          |
| Li Na           | tennis    | 1982      | CN          |
| Liu Yang        | astronaut | 1978      | CN          |
| Serena Williams | tennis    | 1981      | US          |
| Venus Williams  | tennis    | 1980      | US          |
+-----------------+-----------+-----------+-------------+
9 rows in set (0.00 sec)

mysql> select Field,AVG(birthyear) from people;
+----------+-------------------+
| Field    | AVG(birthyear)    |
+----------+-------------------+
| politics | 1967.111111111111 |
+----------+-------------------+
1 row in set (0.00 sec)

Благодаря возможности использования определения group by мы можем получить средние значения годов рождения для групп людей, занимающихся определенной деятельностью. 

mysql> select Field,AVG(birthyear) from people group by Field;
+-----------+--------------------+
| Field     | AVG(birthyear)     |
+-----------+--------------------+
| astronaut |               1978 |
| politics  | 1939.3333333333333 |
| tennis    |             1981.6 |
+-----------+--------------------+
3 rows in set (0.00 sec)

3.5.8. Удаление записей

Вы можете использовать запрос delete для необратимого удаления записи из таблицы базы данных. 

mysql> delete from country where countryname='Spain';
Query OK, 1 row affected (0.06 sec)

mysql> select * from country where countryname='Spain';
Empty set (0.00 sec)

3.6. Объединение двух таблиц

3.6.1. Оператор внутреннего объединения inner join

С помощью оператора внутреннего объединения вы можете получить значения из двух таблиц и скомбинировать их в рамках одного результата. Рассмотрите таблицы с информацией о странах и известных людях из предыдущего раздела для лучшего понимания приведенного ниже примера использования оператора внутреннего объединения inner join. 

mysql> select Name,Field,countryname
    -> from country
    -> inner join people on people.countrycode=country.countrycode;
+-----------------+-----------+---------------+
| Name            | Field     | countryname   |
+-----------------+-----------+---------------+
| Barack Obama    | politics  | United States |
| Deng Xiaoping   | politics  | China         |
| Guy Verhofstadt | politics  | Belgium       |
| Justine Henin   | tennis    | Belgium       |
| Kim Clijsters   | tennis    | Belgium       |
| Li Na           | tennis    | China         |
| Liu Yang        | astronaut | China         |
| Serena Williams | tennis    | United States |
| Venus Williams  | tennis    | United States |
+-----------------+-----------+---------------+
9 rows in set (0.00 sec)

В данном случае оператор inner join позволяет вывести лишь строки с совпадающими названиями стран из столбцов countrycode двух таблиц.

3.6.2. Оператор левого внешнего объединения left join

Оператор левого внешнего объединения left join отличается от оператора внутреннего объединения inner join тем, что он позволяет получить все строки из левой таблицы вне зависимости от наличия совпадений в правой таблице. 

mysql> select Name,Field,countryname from country left join people on people.countrycode=country.countrycode;
+-----------------+-----------+---------------+
| Name            | Field     | countryname   |
+-----------------+-----------+---------------+
| Guy Verhofstadt | politics  | Belgium       |
| Justine Henin   | tennis    | Belgium       |
| Kim Clijsters   | tennis    | Belgium       |
| Deng Xiaoping   | politics  | China         |
| Li Na           | tennis    | China         |
| Liu Yang        | astronaut | China         |
| NULL            | NULL      | Germany       |
| NULL            | NULL      | Egypt         |
| NULL            | NULL      | Spain         |
| NULL            | NULL      | France        |
| NULL            | NULL      | India         |
| NULL            | NULL      | Japan         |
| NULL            | NULL      | Mexico        |
| Barack Obama    | politics  | United States |
| Serena Williams | tennis    | United States |
| Venus Williams  | tennis    | United States |
+-----------------+-----------+---------------+
16 rows in set (0.00 sec)

Очевидно, что в результирующем выводе присутствуют строки, относящиеся к странам, в которых не нашлось известных людей из таблицы people.

3.7. Триггеры базы данных MySQL

3.7.1. Использование триггера, запускаемого до связанного с ним события (before trigger)

Рассмотрите следующую команду для создания базы данных create table. Значением последнего поля (amount) должно являться произведение значений двух полей с именами unitprice и unitcount. 

mysql> create table invoices (
    -> id char(8) NOT NULL,
    -> customerid char(3) NOT NULL,
    -> unitprice int,
    -> unitcount smallint,
    -> amount int );
Query OK, 0 rows affected (0.00 sec)

Мы можем делегировать серверу базы данных MySQL полномочия по проведению описанных расчетов путем создания триггера, запускаемого до связанного с ним события (before trigger). В примере ниже показана методика создания триггера, который будет рассчитывать значение поля amount путем перемножения значений из двух полей добавляемой в таблицу записи. 

mysql> create trigger total_amount before INSERT on invoices
    -> for each row set new.amount = new.unitprice * new.unitcount ;
Query OK, 0 rows affected (0.02 sec)

Проверим работоспособность созданного триггера, добавив новую запись в таблицу базы данных без указания значения поля amount. 

mysql> insert into invoices values ('20090526','ABC','199','10','');
Query OK, 1 row affected (0.02 sec)

Для того, чтобы убедиться работоспособности триггера, достаточно извлечь добавленную запись из базы данных. 

mysql> select * from invoices;
+----------+------------+-----------+-----------+--------+
| id       | customerid | unitprice | unitcount | amount |
+----------+------------+-----------+-----------+--------+
| 20090526 | ABC        |       199 |        10 |   1990 | 
+----------+------------+-----------+-----------+--------+
1 row in set (0.00 sec)

3.7.2. Удаление триггера

В том случае, если триггер больше не требуется, вы можете удалить его с помощью команды drop trigger. 

mysql> drop trigger total_amount;
Query OK, 0 rows affected (0.00 sec)
Предыдущий раздел: Оглавление Следующий раздел:
Глава 3. Вводная информация о структурированном языке запросов SQL и сервере базы данных MySQL   Глава 4. Вводная информация о серверах DNS