Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология
Образование Политология Производство Психология Стандартизация Технологии


Основные характеристики MySQL

Основные характеристики MySQL

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

Клиентские программы могут работать не только в режиме командной строки. Есть и графические клиенты, например MySQL GUI, PhpMyAdmin и др. Но они – тема отдельного курса.

MySQL взаимодействует с базой данных на языке, называемом SQL (Structured Query Language — язык структурированных запросов).

SQL предназначен для манипуляции данными, которые хранятся в Системах управления реляционными базами данных (RDBMS). SQL имеет команды, с помощью которых данные можно извлекать, сортировать, обновлять, удалять и добавлять. Стандарты языка SQL определяет ANSI (American National Standards Institute). В настоящее время действует стандарт, принятый в 2003 году (SQL-3).

SQL можно использовать с такими RDBMS как MySQL, mSQL, PostgreSQL, Oracle, Microsoft SQL Server, Access, Sybase, Ingres. Эти системы RDBMS поддерживают все важные и общепринятые операторы SQL, однако каждая из них имеет множество своих собственных патентованных операторов и расширений.

SQL является общим языком запросов для нескольких баз данных различных типов. Данный курс рассматривает систему MySQL, которая является RDBMS c открытым исходным кодом, доступной для загрузки на сайте MySQL.com.

Вот как характеризуют MySQL её разработчики.

  • MySQL - это система управления базами данных.

База данных представляет собой структурированную совокупность данных. Эти данные могут быть любыми - от простого списка предстоящих покупок до перечня экспонатов картинной галереи или огромного количества информации в корпоративной сети. Для записи, выборки и обработки данных, хранящихся в компьютерной базе данных, необходима система управления базой данных, каковой и является ПО MySQL. Поскольку компьютеры замечательно справляются с обработкой больших объемов данных, управление базами данных играет центральную роль в вычислениях. Реализовано такое управление может быть по-разному - как в виде отдельных утилит, так и в виде кода, входящего в состав других приложений.

  • MySQL - это система управления реляционными базами данных.

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

  • Программное обеспечение MySQL - это ПО с открытым кодом.

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

  • Технические возможности СУБД MySQL

ПО MySQL является системой клиент-сервер, которая содержит многопоточный SQL-сервер, обеспечивающий поддержку различных вычислительных машин баз данных, а также несколько различных клиентских программ и библиотек, средства администрирования и широкий спектр программных интерфейсов (API).

  • Безопасность

Система безопасности основана на привилегиях и паролях с возможностью верификации с удаленного компьютера, за счет чего обеспечивается гибкость и безопасность. Пароли при передаче по сети при соединении с сервером шифруются. Клиенты могут соединяться с MySQL, используя сокеты TCP/IP, сокеты Unix или именованные каналы (named pipes, под NT)

  • Вместимость данных

Начиная с MySQL версии 3.23, где используется новый тип таблиц, максимальный размер таблицы доведен до 8 миллионов терабайт (263 bytes). Однако следует заметить, что операционные системы имеют свои собственные ограничения по размерам файлов. Ниже приведено несколько примеров:

- 32-разрядная Linux-Intel – размер таблицы 4 Гб.

- Solaris 2.7 Intel - 4 Гб

- Solaris 2.7 UltraSPARC - 512 Гб

- WindowsXP - 4 Гб

Как можно видеть, размер таблицы в базе данных MySQL обычно лимитируется операционной системой. По умолчанию MySQL-таблицы имеют максимальный размер около 4 Гб. Для любой таблицы можно проверить/определить ее максимальный размер с помощью команд SHOW TABLE STATUS или myisamchk -dv table_name. Если большая таблица предназначена только для чтения, можно воспользоваться myisampack, чтобы слить несколько таблиц в одну и сжать ее. Обычно myisampack ужимает таблицу по крайней мере на 50%, поэтому в результате можно получить очень большие таблицы.

 

Команды для создания базы данных в Windows и Linux одинаковы.

Создадим базу данных с именем employees, которая содержит данные о сотрудниках некой компании BigFoot. Предполагается хранить имя, зарплату, возраст, адрес, e-mail, дату рождения, увлечения, номера телефонов, и т.д. сотрудников.

Использование базы данных

База данных employees уже создана. Для работы с ней, необходимо её "активировать" или "выбрать". В приглашении mysql выполните команду:

SELECT DATABASE();

На экране увидим ответ системы, как показано на рис. 3.5


Рис. 3.5. Выбор базы данных

Это говорит о том, что ни одна база данных не была выбрана. На самом деле всякий раз при работе с клиентом mysql необходимо определять, какая база данных будет использоваться.

Определить текущую базу данных можно несколькими способами:

  • определение имени базы данных при запуске

Введите в приглашении системы следующее:

mysql employees (в Windows)mysql employees -u manish -p (в Linux)
  • определение базы данных с помощью оператора USE в приглашении mysql
mysql>USE employees;
  • Определение базы данных с помощью \u в приглашении mysql
mysql>\u employees;

При работе необходимо определять базу данных, которая будет использоваться, иначе MySQL будет порождать ошибку.

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

После выбора базы данных employees, выполните в приглашении mysql команду CREATE TABLE.

CREATE TABLE employee_data(emp_id int unsigned not null auto_increment primary key,f_name varchar(20),l_name varchar(20),title varchar(30),age int,yos int,salary int,perks int,email varchar(60));

Примечание: нажатие клавиши Enter после ввода первой строки изменяет приглашение mysql на ->. Это означает, что mysql понимает, что команда не завершена и приглашает ввести дополнительные операторы. Помните, что каждая команда mysql заканчивается точкой с запятой, а каждое объявление столбца отделяется запятой. Можно также при желании ввести всю команду на одной строке.

Вывод на экране должен соответствовать рис. 3.6.


Рис. 3.6. Создание таблицы

Удаление таблиц

Для того, чтобы удалить таблицу, убедимся сперва что она существует. Это можно проверить с помощью команды SHOW TABLES, как показано на рис. 3.7.


Рис. 3.7. Просмотр таблиц в базе

Для удаления таблицы используется команда DROP TABLE, как показано на рис. 3.8.


Рис. 3.8. Удаление таблицы

Теперь команда SHOW TABLES; этой таблицы больше не покажет.

Запись данных в таблицы

Оператор INSERT заполняет таблицу данными. Вот общая форма INSERT.

INSERT into table_name (column1, column2, ...)values (value1, value2...);

где table_name является именем таблицы, в которую надо внести данные; column1, column2 и т.д. являются именами столбцов, а value1, value2 и т.д. являются значениями для соответствующих столбцов.

Следующий оператор вносит первую запись в таблицу employee_data, которую мы рассматривали в лекции 3.

INSERT INTO employee_data(f_name, l_name, title, age, yos, salary, perks, email)values("Михаил", "Петров", "директор", 28, 4, 200000,50000, "misha@yandex.ru");

Как и другие операторы MySQL, эту команду можно вводить на одной строке или разместить ее на нескольких строках.

Несколько важных моментов:

  • Значениями для столбцов f_name, l_name, title и email являются текстовые строки, и они записываются в кавычках.
  • Значениями для age, yos, salary и perks являются числа (целые), и они не имеют кавычек.
  • Можно видеть, что данные заданы для всех столбцов кроме except emp_id. Значение для этого столбца задает система MySQL, которая находит в столбце наибольшее значение, увеличивает его на единицу, и вставляет новое значение.

Если приведенная выше команда правильно введена в приглашении клиента mysql, то программа выведет сообщение об успешном выполнении, как показано на рис. 5.1.


Рис. 5.1. Ввод данных в таблицу.

Создание дополнительных записей требует использования отдельных операторов INSERT. Чтобы облегчить эту работу можно поместить все операторы INSERT в файл

 

Это должен быть обычный текстовый файл с оператором INSERT в каждой строке.

Заполнение таблицы employee_data данными с помощью файла employee.dat

В системе Windows

1) Поместите файл в каталог c:\mysql\bin.

2) Проверьте, что MySQL работает.

3) Выполните команду

mysql employees <employee.dat

Пусть таблица содержит теперь 21 запись (20 из файла employee.dat и одну, вставленную оператором INSERT в начале лекции).

Задания

1. Напишите оператор SQL для создания новой базы данных с именем addressbook

2. Какой оператор используется для получения информации о таблице? Как используется этот оператор?

3. Как получить список всех баз данных, доступных в системе?

4. Напишите оператор для записи следующих данных в таблицу employee_data

Имя: Рудольф Фамилия: Курочкин Должность: Программист Возраст: 34 Стаж работы в компании: 2 Зарплата: 95000 Надбавки: 17000 email: rudolf@yandex.ru

5. Приведите две формы оператора SELECT, которые будут выводить все данные из таблицы employee_data.

6. Как извлечь данные столбцов f_name, email из таблицы employee_data?

7. Напишите оператор для вывода данных из столбцов salary, perks и yos таблицы employee_data.

8. Как узнать число строк в таблице с помощью оператора SELECT?

9. Как извлечь данные столбцов salary, l_name из таблицы employee_data?

Возможные решения

1. create database addressbook;

или

CREATE DATABASE addressbook;

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

2. Оператор DESCRIBE, например:

DESCRIBE employee_data;

3. SHOW DATABASES; (в приглашении mysql)

4. INSERT INTO employee_data(f_name, l_name, title, age, yos, salary, perks, email)values("Рудольф", "Курочкин", "программист", 34, 2, 95000, 17000, "rudolf@yandex.ru");

Примечание: Текстовые строки заключаются в кавычки.

5. SELECT emp_id, f_name, l_name, title, age, yos, salary, perks, email from employee_data;

или

SELECT * from employee_data;

Вторая форма лучше. Ее легче использовать и труднее ошибиться.

6. Чтобы вывести данные столбцов f_name и email, используем следующий оператор.

select f_name, email from employee_data;

7. SELECT salary, perks, yos from employee_data;

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

9. select salary, l_name from employee_data;

Операторы больше и меньше

Давайте получим имена и фамилии всех сотрудников, которые старше 32 лет.

SELECT f_name, l_name from employee_data where age > 32;

Результат запроса приведен на рис. 5.7.


Рис. 5.7. Выборка столбцов с условием "больше" для поля "возраст"

Попробуем найти сотрудников, которые получают зарплату больше 120000.

SELECT f_name, l_name from employee_data where salary > 120000;

Результат запроса приведен на рис. 5.8.


Рис. 5.8. Выборка столбцов с условием "больше" для поля "зарплата"

Теперь перечислим всех сотрудников, которые имеют стаж работы в компании менее 3 лет.

SELECT f_name, l_name from employee_data where yos < 3;

Результат запроса приведен на рис. 5.9.


Рис. 5.9. Выборка столбцов с условием "меньше" для поля "стаж"

Операторы <= и >=

Используемые в основном с целочисленными данными операторы меньше или равно (<=) и больше или равно (>=) обеспечивают дополнительные возможности.

select f_name, l_name, age, salaryfrom employee_data where age >= 32;

Результат запроса приведен на рис. 5.10.


Рис. 5.10. Выборка столбцов с условием "больше или равно" для поля "возраст"

Выборка содержит имена, возраст и зарплаты сотрудников, которым больше 32 лет.

select f_name, l_name from employee_data where yos <= 2;

Результат запроса приведен на рис. 5.11.


Рис. 5.11. Выборка столбцов с условием "меньше или равно" для поля "стаж"

Запрос выводит имена сотрудников, которые работают в компании меньше 3 лет.

Задания

1. Напишите оператор SELECT для извлечения идентификационного номера сотрудников, которые старше 30 лет.

2. Напишите оператор SELECT для извлечения имен и фамилий всех Web-разработчиков.

3. Что выведет следующий оператор SELECT:

SELECT * from employee_data where salary <=100000;

4. Как вывести зарплаты и надбавки сотрудников, которые получают в качестве надбавок более 16000?

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

Возможные решения

1. select emp_id from employee_data where age > 30;

2. select f_name, l_name from employee_data where title='web designer';

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

SELECT * from employee_data where salary <=100000;

4. select salary, perks from employee_data where perks >= 16000;

5. select l_name, f_name from employee_data where title = 'бухгалтер';

Задания

1. Перечислить всех сотрудников, фамилии которых начинаются с буквы P.

2. Вывести имена всех сотрудников в отделе продаж.

3. Что выведет следующий оператор

SELECT f_name, l_name, salary fromemployee_data where f_name like '%к%';

4. Перечислить фамилии и должности всех программистов

Возможные решения

1. select l_name, f_name from employee_data where l_name like 'P%';

2. select f_name, l_name from employee_data where title like '%продавец%';

3. Этот оператор выводит имена, фамилии и заплаты всех сотрудников, у которых имя содержит букву 'к'.

SELECT f_name, l_name, salary from employee_data where f_name like '%к%';

4. SELECT l_name, title from employee_data where title like '%программист%';

Предложение HAVING

Чтобы вывести среднюю зарплату сотрудников в различных подразделениях (должностях), используется предложение GROUP BY, например:

select title, AVG(salary)from employee_dataGROUP BY title;

Результат запроса приведен на рис. 5.16.


Рис. 5.16. Вывод средней зарплаты сотрудников по подразделениям

Предположим теперь, что требуется вывести только те подразделения, где средняя зарплата более 100000. Это можно сделать с помощью предложения HAVING.

select title, AVG(salary)from employee_dataGROUP BY titleHAVING AVG(salary) > 100000;

Результат запроса приведен на рис. 5.17.

Рис. 5.17. Вывод средней зарплаты определённого диапазона по подразделениям

Задание

Вывести подразделения и средний возраст, где средний возраст больше 30.

Возможное решение

mysql> select title, AVG(age) -> from employee_data -> GROUP BY title -> HAVING AVG(age) > 30;

Результат запроса приведен на рис. 5.18.


Рис. 5.18. Вывод подразделения и среднего возраста, где средний возраст больше 30 лет

Удаление записей из таблицы

Для удаления записей из таблицы можно использовать оператор DELETE.

Оператор удаления DELETE требует задания имени таблицы и необязательных условий.

DELETE from имя_таблицы [WHERE условия];

Примечание: Если никакие условия не будут заданы, то удаляются все данные в таблице.

Предположим, один из специалистов по мультимедиа 'Василий Пупкин' уволился из компании. Надо удалить его запись.

DELETE from employee_dataWHERE l_name = 'Пупкин';

Результат запроса приведен на рис. 5.19.


Рис. 5.19. Результат удаления записи из таблицы

 

Булевы (логические) операторы.
  1. AND
  2. OR
  3. NOT
Использовать их очень просто. Ниже показан оператор SELECT, который выводит имена сотрудников, которые получают более 70000, но меньше 90000. SELECT f_name, l_name from employee_datawhere salary > 70000 AND salary < 90000; На рис. 6.1. приведен результат запроса. Рис. 6.1. Имена сотрудников, которые получают более 70000, но меньше 90000 Давайте выведем список сотрудников, фамилии которых начинаются с буквы К или Л. SELECT l_name from employee_data wherel_name like 'К%' OR l_name like 'Л%'; На рис. 6.2. приведен результат запроса. Рис. 6.2. Сотрудники, фамилии которых начинаются с буквы К или Л Вот более сложный пример ... список имен и возраста сотрудников, фамилии которых начинаются с К или Л, и которые младше 30 лет. SELECT f_name, l_name , age from employee_datawhere (l_name like 'К%' OR l_name like 'Л%') AND age < 30; На рис. 6.3. приведен результат запроса. Рис. 6.3. Список имен и возраста сотрудников, фамилии которых начинаются с К или Л, и которые младше 30 лет Обратите внимание на использование скобок в представленном выше операторе. Скобки предназначены для выделения различных логических условий и удаления двусмысленностей. Оператор NOT поможет при поиске всех сотрудников, которые не являются программистами. (Программисты включают старших программистов, программистов мультимедиа и программистов). SELECT f_name, l_name, title from employee_datawhere title NOT LIKE "%программист%"; На рис. 6.4. приведен результат запроса. Рис. 6.4. Поиск всех сотрудников, которые не являются программистами И последний пример перед упражнениями. Показать всех сотрудников со стажем работы в компании более 3 лет, которые старше 30 лет. select f_name, l_name from employee_datawhere yos > 3 AND age > 30; На рис. 6.5. приведен результат запроса. Рис. 6.5. Все сотрудники, которые старше 30 лет, и имеют стаж работы более 3 лет Задания 1. Вывести имена и фамилии всех сотрудников, которые получают зарплату не более 90000 и не являются программистами, старшими программистами или программистами мультимедиа. 2. Что делает следующий оператор? SELECT l_name, f_name from employee_datawhere title NOT LIKE '%продавец%'AND age < 30; 3. Вывести все идентификационные номера и имена сотрудников в возрасте от 32 до 40 лет. 4. Выберите имена всех сотрудников в возрасте 32 лет, которые не являются программистами. Возможные решения
1. mysql> select f_name, l_name from employee_data -> where salary <= 90000 -> AND title NOT LIKE '%программист%';
  1. Команда выводит имена (фамилию за которой следует имя) сотрудников, которые не работают в отделе продаж и моложе 30 лет.
mysql> SELECT l_name, f_name from employee_data -> where title NOT LIKE '%продавец%' -> AND age < 30;   3. mysql> select emp_id, f_name, l_name from -> employee_data where age > 32 -> and age < 40;   4. mysql> select f_name, l_name from employee_data -> where age = 32 AND -> title NOT LIKE '%программист%'; Операторы IN и BETWEEN Чтобы найти сотрудников, которые являются разработчиками Web или системными администраторами, можно использовать оператор SELECT следующего вида: SELECT f_name, l_name, title from -> employee_data where -> title = 'разработчик Web' OR -> title = 'системный адм.'; На рис. 6.6. приведен результат запроса. Рис. 6.6. Поиск сотрудников, которые являются разработчиками Web или системными администраторами В SQL имеется более простой способ сделать это с помощью оператора IN (в множестве). Его использование не представляет никаких трудностей. SELECT f_name, l_name, title from -> employee_data where title -> IN ('разработчик Web', 'системный адм.'); Результат будет аналогичен рис. 6.6. Использование NOT перед IN позволяет вывести данные, которые не входят в множество, определяемое условием IN. Следующий оператор выводит список сотрудников, которые не занимают должность программиста или системного администратора. SELECT f_name, l_name, title from -> employee_data where title NOT IN -> ('программист', 'системный адм.'); Оператор BETWEEN используется для определения целочисленных границ. Поэтому вместо age >= 32 AND age <= 40 можно использовать age BETWEEN 32 AND 40. select f_name, l_name, age from -> employee_data where age BETWEEN -> 32 AND 40; На рис. 6.7. приведен результат запроса. Рис. 6.7. Поиск сотрудников, возраст которых лежит в промежутке от 32-х до 40 лет NOT также можно использовать вместе с BETWEEN, как в следующем операторе, который выводит сотрудников, зарплата которых меньше 90000 или больше 150000. select f_name, l_name, salary -> from employee_data where salary -> NOT BETWEEN -> 90000 AND 150000; Задания 1. Найдите всех сотрудников, которые занимают должность "старший программист" и "программист мультимедиа". 2. Выведите список имен сотрудников, зарплата которых составляет от 70000 до 90000. 3. Что делает следующий оператор? SELECT f_name, l_name, title fromemployee_data where title NOT IN('программист', 'старший программист','программист мультимедиа'); 4. Вот более сложный оператор, который объединяет BETWEEN и IN. Что он делает? SELECT f_name, l_name, title, agefrom employee_data wheretitle NOT IN('программист', 'старший программист','программист мультимедиа') AND ageNOT BETWEEN 28 and 32; Возможные решения 1. mysql> select l_name, f_name, title -> from employee_data where -> title IN -> ('старший программист', -> 'программист мультимедиа');2. 2. mysql> select f_name, l_name, salary from -> employee_data where salary BETWEEN -> 70000 AND 90000;
  1. Выводятся имена и должности всех, кто не является программистом.
mysql> SELECT f_name, l_name, title from -> employee_data where title NOT IN -> ('программист', 'старший программист', -> 'программист мультимедиа');
  1. Этот оператор выводит список тех, кто не является программистом, и которые младше 28 или старше 32 лет.
mysql> SELECT f_name, l_name, title, age from -> employee_data where title NOT IN -> ('программист', 'старший программист', -> 'программист мультимедиа') AND Упорядочивание данных Рассмотрим вопрос о том, как можно изменить порядок вывода данных, извлеченных из таблиц MySQL, используя предложение ORDER BY оператора SELECT. Извлекаемые до сих пор данные всегда выводились в том порядке, в котором они были сохранены в таблице. В действительности SQL позволяет сортировать извлеченные данные с помощью предложения ORDER BY. Это предложение требует имя столбца, на основе которого будут сортироваться данные. Давайте посмотрим, как можно вывести имена сотрудников с упорядоченными по алфавиту фамилиями сотрудников (в возрастающем порядке). SELECT l_name, f_name fromemployee_data ORDER BY l_name; А вот так сотрудников можно отсортировать по возрасту. SELECT f_name, l_name, agefrom employee_dataORDER BY age; Предложение ORDER BY может сортировать в возрастающем порядке (ASCENDING или ASC) или в убывающем порядке (DESCENDING или DESC) в зависимости от указанного аргумента. Чтобы вывести список сотрудников в убывающем порядке, можно использовать следующий оператор. SELECT f_name from employee_dataORDER by f_name DESC; Примечание: Возрастающий порядок (ASC) используется по умолчанию. Задания 1. Вывести список сотрудников в порядке, определяемом зарплатой, которую они получают. 2. Выведите список сотрудников в убывающем порядке их стажа работы в компании. 3. Что делает следующий оператор? SELECT emp_id, l_name, title, agefrom employee_data ORDER BYtitle DESC, age ASC; 4. Вывести список сотрудников (фамилию и имя), которые занимают должность "программист" или "разработчик Web" и отсортировать их фамилии по алфавиту. Возможные решения 1. mysql> SELECT f_name, l_name, salary -> from employee_data -> ORDER BY salary;2. mysql> SELECT f_name, l_name, yos -> from employee_data -> ORDER by yos;
  1. Оператор выводит список сотрудников с идентификационным номером, должностью, и возрастом, отсортированный по должности в убывающем порядке и по возрасту в возрастающем порядке.
mysql> SELECT emp_id, l_name, title, age -> from employee_data ORDER BY -> title DESC, age ASC; Примечание: Сначала сортируются должности в убывающем порядке. Затем для каждой должности сортируется возраст сотрудников в возрастающем порядке. 4. mysql> SELECT l_name, f_name from employee_data -> where title IN ('программист', -> 'разработчик Web') ORDER BY l_name; -> age NOT BETWEEN 28 AND 32; Ограничение количества извлекаемых данных Далее рассмотрим, как ограничить число записей, выводимых оператором SELECT. По мере увеличения таблиц возникает необходимость вывода только подмножества данных. Это можно добиться с помощью предложения LIMIT. Например, чтобы вывести из таблицы имена только первых пяти сотрудников, используется оператор LIMIT с аргументом равным 5. SELECT f_name, l_name fromemployee_data LIMIT 5; На рис. 6.8. приведен результат запроса. Рис. 6.8. Имена первых пяти сотрудников Это первые пять записей таблицы. Можно соединить оператор LIMIT с оператором ORDER BY. Таким образом, следующий оператор выведет четверых самых старых сотрудников компании. SELECT f_name, l_name, age fromemployee_data ORDER BY age DESCLIMIT 4; На рис. 6.9. приведен результат запроса. Рис. 6.9. Четверо самых старых сотрудников компании Аналогично можно вывести двух самых младших сотрудников. SELECT f_name, l_name, age fromemployee_data ORDER BY ageLIMIT 2; На рис. 6.10. приведен результат запроса. Рис. 6.10. Двое самых младших сотрудников Извлечение подмножеств LIMIT можно использовать также для извлечения подмножества данных, используя дополнительные аргументЫ. Общая форма оператора LIMIT имеет следующий вид: SELECT (что-нибудь) from таблица LIMIT начальная строка, извлекаемое число записей; SELECT f_name, l_name fromemployee_data LIMIT 6,3; На рис. 6.11. приведен результат запроса. Рис. 6.11. Извлечение трёх строк начиная с 6-й Будут извлечены три строки, начиная с шестой. Задания
  1. Найдите имена 5 самых молодых сотрудников компании.
  2. Извлеките 5 записей, начиная с 10 строки.
  3. Выведите имена и зарплату сотрудников, которые получают самую большую зарплату.
  4. Что делает следующий оператор?
SELECT emp_id, age, perksfrom employee_data ORDER BYperks DESC LIMIT 10; Возможные решения 1. mysql> SELECT f_name, l_name, age from -> employee_data ORDER BY age -> LIMIT 5;2. mysql> SELECT * from employee_data -> LIMIT 10, 5;3. mysql> select f_name, l_name, salary -> from employee_data -> ORDER BY salary DESC -> LIMIT 1;
  1. Этот оператор выводит идентификационный номер, возраст и надбавки 10 сотрудников, которые получают наибольшие надбавки.
mysql> SELECT emp_id, age, perks -> from employee_data ORDER BY -> perks DESC LIMIT 10; Ключевое слово DISTINCT Рассмотрим теперь, как выбрать и вывести записи таблиц MySQL с помощью ключевого слова DISTINCT (РАЗЛИЧНЫЙ), использование которого исключает появление повторяющихся данных. Чтобы вывести все должности базы данных компании, можно выполнить следующий оператор: select title from employee_data; На рис. 6.12. приведен результат запроса. Рис. 6.12. Все должности базы данных компании Можно видеть, что список содержит повторяющиеся данные. Предложение SQL DISTINCT выводит только уникальные данные. Вот как оно используется. select DISTINCT title from employee_data; На рис. 6.13. приведен результат запроса. Рис. 6.13. Все должности базы данных компании без повторов Из этого можно видеть, что в компании имеется 11 уникальных должностей. Уникальные записи можно также отсортировать с помощью ORDER BY. select DISTINCT age from employee_dataORDER BY age; На рис. 6.14. приведен результат запроса. Рис. 6.14. Значения возраста сотрудников компании без повторов DISTINCT часто используется вместе с функцией COUNT, которая будет рассмотрена далее. Задания
  1. Сколько уникальных вариантов зарплаты имеется в компании BigFoot? Представьте их в убывающем порядке.
  2. Сколько различных имен имеется в базе данных?
Возможные решения 1. select distinct salary fromemployee_data order by salary DESC; 2. mysql> select distinct f_name from employee_data; Изменение записей Команда UPDATE выполняет изменение данных в таблицах. Она имеет очень простой формат. UPDATE имя_таблицы SETимя_столбца_1 = значение_1,имя_столбца_2 = значение_2,имя_столбца_3 = значение_3, ...[WHERE условия]; Как и все другие команды SQL можно вводить ее на одной строке или на нескольких строках. Рассмотрим несколько примеров. Предположим, директор увеличил свою зарплату на 20000 и надбавки на 5000. Его предыдущая зарплата была 200000, а надбавки были 50000. UPDATE employee_data SETsalary=220000, perks=55000WHERE title='директор'; На рис. 6.15. приведен результат запроса. Рис. 6.15. Данные обновлены Можно проверить эту операцию, выводя данные из таблицы. select salary, perks fromemployee_data WHEREtitle = 'директор'; На рис. 6.16. приведен результат запроса. Рис. 6.16. Данные обновлены В действительности предыдущую зарплату знать не требуется. Можно воспользоваться арифметическими операторами. Следующий оператор сделает то же самое, при этом исходные данные знать заранее не требуется. UPDATE employee_data SETsalary = salary + 20000,perks = perks + 5000WHERE title='директор'; Результат запроса аналогичен рис. 6.15. В качестве другого примера можно попробовать изменить название должности "разработчик Web" на "программист Web". mysql> update employee_data SET -> title = 'программист Web' -> WHERE title = 'разработчик Web'; На рис. 6.17. приведен результат запроса. Рис. 6.17. Данные обновлены Важно также перед выполнением изменений внимательно изучить часть оператора с условием, так как легко можно изменить не те данные. Оператор UPDATE без условий изменит все данные столбца во всех строках. Надо быть очень осторожным при внесении изменений. Задания
  1. Измените фамилию Чащина на Петрова. Внесите соответствующие изменения в базу данных.
  2. Название должности "программист мультимедиа" необходимо изменить на "специалист по мультимедиа".
  3. Увеличьте зарплату всем сотрудниками (кроме директора) на 10000.
Возможные решения 1. mysql> update employee_data SET -> l_name = 'Петрова' -> WHERE l_name = 'Чашина'; Примечание: Если бы были еще сотрудники с фамилией Чащина, то эти записи также были бы изменены. В таком случае может помочь столбец emp_id, так как он содержит уникальные значения. Лучше использовать значение emp_id вместо l_name, как в следующем примере: mysql> update employee_data SET -> l_name = 'Петрова' -> WHERE emp_id = 4;2. mysql> update employee_data set -> title = 'специалист по мультимедиа' -> where title = 'программист мультимедиа';3. mysql> update employee_data set -> salary = salary + 10000 -> where title != 'директор';   Поиск минимального и максимального значений В MySQL имеются встроенные функции для вычисления минимального и максимального значений. SQL имеет 5 агрегатных функций.
  1. MIN(): минимальное значение
  2. MAX(): максимальное значение
  3. SUM(): сумма значений
  4. AVG(): среднее значений
  5. COUNT(): подсчитывает число записей
В этом параграфе мы рассмотрим поиск минимального и максимального значений столбца. Минимальное значение select MIN(salary) from employee_data; На рис. 7.1. приведен результат запроса. Рис. 7.1. Поиск минимальной зарплаты Максимальное значение select MAX(salary) from employee_data; На рис. 7.2. приведен результат запроса. Рис. 7.2. Поиск максимальной зарплаты Задания
  1. Найдите минимальные надбавки.
  2. Найдите максимальную зарплату среди всех "программистов".
  3. Найдите возраст самого старого "продавца".
  4. Найдите имя и фамилию самого старого сотрудника.
Возможные решения 1. mysql> select MIN(perks) from employee_data; На рис. 7.3. приведен результат запроса. Рис. 7.3. Минимальные надбавки 2. mysql> select MAX(salary) from employee_data -> where title = 'программист'; На рис. 7.4. приведен результат запроса. Рис. 7.4. Максимальная зарплата среди программистов 3. mysql> select MAX(age) from employee_data -> where title = 'продавец'; На рис. 7.5. приведен результат запроса. Рис. 7.5. Возраст самого старого продавца 4. Вот один из способов сделать без использования агрегатных функций. mysql> select f_name, l_name, age -> from employee_data -> order by age DESC limit 1; На рис. 7.6. приведен результат запроса. Рис. 7.6. Имя и фамилия самого старого сотрудника Поиск среднего значения и суммы Суммирование значений столбца с помощью функции SUM Агрегатная функция SUM() вычисляет общую сумму значений в столбце. Для этого необходимо задать имя столбца, которое должно быть помещено внутри скобок. Давайте посмотрим, сколько компания BigFoot тратит на зарплату своих сотрудников. select SUM(salary) from employee_data; На рис. 7.7. приведен результат запроса. Рис. 7.7. Сумма всех зарплат Аналогично можно вывести общую сумму надбавок, выдаваемых сотрудникам. select SUM(perks) from employee_data; На рис. 7.8. приведен результат запроса. Рис. 7.8. Сумма всех надбавок Можно найти также общую сумму зарплаты и надбавок. select sum(salary) + sum(perks) from employee_data; На рис. 7.9. приведен результат запроса. Рис. 7.9. Общая сумма зарплаты и надбавок Здесь показаны также дополнительные возможности команды SELECT. Значения можно складывать, вычитать, умножать или делить. В действительности можно записывать полноценные арифметические выражения. Вычисление среднего значения Агрегатная функция AVG() используется для вычисления среднего значения данных в столбце. select avg(age) from employee_data; На рис. 7.10. приведен результат запроса. Рис. 7.10. Средний возраст сотрудников Пример выше вычисляет средний возраст сотрудников компании BigFoot, а следующий выводит среднюю зарплату. select avg(salary) from employee_data; На рис. 7.11. приведен результат запроса. Рис. 7.11. Средняя зарплата сотрудников Задания
  1. Вывести сумму всех возрастов сотрудников, работающих в компании BigFoot.
  2. Как вычислить общее количество лет стажа работы сотрудников в компании BigFoot?
  3. Вычислите сумму зарплат и средний возраст сотрудников, которые занимают должность "программист".
  4. Что делает следующий оператор?
select (SUM(perks)/SUM(salary) * 100)from employee_data; Возможные решения 1. mysql> select SUM(age) from employee_data; На рис. 7.12. приведен результат запроса. Рис. 7.12. Сумма всех возрастов сотрудников 2. mysql> select SUM(yos) from employee_data; На рис. 7.13. приведен результат запроса. Рис. 7.13. Общее количество лет стажа работы сотрудников 3. mysql> select SUM(salary), AVG(age) -> from employee_data where -> title = 'программист'; На рис. 7.14. приведен результат запроса. Рис. 7.14. Сумма зарплат и средний возраст программистов 4. Этот оператор выводит процент зарплаты, которую сотрудники BigFoot получают в качестве надбавок. mysql> select (SUM(perks)/SUM(salary) * 100) -> from employee_data; На рис. 7.15. приведен результат запроса. Рис. 7.15. Процент зарплаты, которую сотрудники получают в качестве надбавок Именование столбцов MySQL позволяет задавать имена для выводимых столбцов. Поэтому вместо f_name или l_name и т.д. можно использовать более понятные и наглядные термины. Это делается с помощью оператора AS. select avg(salary) AS 'Средняя зарплата' from employee_data; На рис. 7.16. приведен результат запроса. Рис. 7.16. Вывод средней зарплаты с использованием псевдо-имен столбцов. Такие псевдо-имена могут сделать вывод более понятным для пользователей. Важно только помнить, что при задании псевдо-имен с пробелами необходимо заключать такие имена в кавычки. Вот еще один пример: select (SUM(perks)/SUM(salary) * 100) AS 'Процент надбавок' from employee_data; На рис. 7.17. приведен результат запроса. Рис. 7.17. Вывод процента зарплаты, которую сотрудники получают в качестве надбавок с использованием псевдо-имен Подсчет числа записей Агрегатная функция COUNT() подсчитывает и выводит общее число записей. Например, чтобы подсчитать общее число записей в таблице, выполните следующую команду. select COUNT(*) from employee_data; На рис. 7.18. приведен результат запроса. Рис. 7.18. Общее количество записей Как мы уже знаем, знак * означает "все данные". Теперь давайте подсчитаем общее число сотрудников, которые занимают должность "программист". select COUNT(*) from employee_data where title = 'программист'; На рис. 7.19. приведен результат запроса. Рис. 7.19. Общее количество сотрудников-программистов Группировка данных Предложение GROUP BY позволяет группировать аналогичные данные. Поэтому, чтобы вывести все уникальные должности в таблице, можно выполнить команду select title from employee_data GROUP BY title; На рис. 7.20. приведен результат запроса.

Последнее изменение этой страницы: 2016-03-17; Просмотров: 32; Нарушение авторского права страницы


lektsia.com 2007 - 2017 год. Все права принадлежат их авторам! (0.161 с.) Главная | Обратная связь