Лекция: Язык запросов SQL
Язык запросов SQL – основной язык для работы с реляционнми базами данных, построенными в соответствии с правилами реляционной алгебры. Состав языка SQL следующий:
Язык манипулирования данными состоящий из команд: SELECT (выбрать), INSERT (вставить), UPDATE (обновить), DELETE (удалить).
Язык определения данных используемый для создания и изменения структуры БД и ее составных частей – таблиц, индексов, представлений (виртуальных таблиц). Основными его командами являются: CREATE DATABASE (создать базу данных), CREATE TABLE(создать таблицу), CREATE INDEX(создать индекс), ALTER DATABASE (модифицировать базу данных), ALTER TABLE(модифицировать таблицу), ALTER INDEX(модифицировать индекс), DROP DATABASE (удалить базу данных), DROP TABLE(удалить таблицу), DROP INDEX(удалить индекс).
Язык управления данными (управления доступом) состоит из двух основных команд: GRANT (дать права), REVOKE (забрать права).
Наиболее важной и часто используемой командой языка манипулирования данными SQL является команда SELECT. Формат команды SELECT в языке SQL:
SELECT поля FROM таблицы WHERE условие;
Базовыми операциями команды SELECT являются: выборка, проекция, соединение, объединение.
Операция выборки позволяет получить все строки либо часть строк таблицы.
SELECT * FROM Student; – Получить все строки таблицы Student
SELECT * FROM Student WHERE Kurs=2; – Получить подмножество строк таблицы, удовлетворяющих условию Kurs=2. Точка с запятой является стандартным признаком конца команды, который вставляется автоматически.
Операция проекции позволяет выделить подмножество столбцов таблицы.
SELECT StudName FROM Student WHERE Kurs=2; – Получить имена студентов второго курса.
Операция соединения позволяет соединять строки из более чем одной таблицы:
SELECT StudName, Exammark FROM Students, Exams WHERE Students.Stud_Id =Exams.Stud_Id – Получить список студентов и экзаменационных оценок.
Операция объединения позволяет объединять результаты отдельных запросов. Предложение UNION объединяет вывод двух или более SQL-запросов.
SELECT name FROM employee WHERE country = «Беларусь» UNION SELECT contact_nаме, FROM customer WHERE country = «Беларусь»; – Получить список работников и заказчиков, проживающих в Беларуси.
Условия отбора. Директива WHERE содержит условия отбора (предикат). Запрос возвращает только строки, для которых предикат имеет значение true. Типы предикатов, используемых в предложении WHERE:
Сравнение: = (равно); <> (не равно); != (не равно); > (больше); < (меньше); >= (больше или равно); <= (меньше или равно); BETWEEN, IN, LIKE, CONTAINING, IS NULL, EXIST, ANY, ALL.
Предикат BETWEEN задает диапазон значений, для которого истинно значение выражения. Например:
SELECT StudName, Stipend FROM Student WHERE Stipend BETWEEN 120 AND 200 – получить список студентов стипендия которых больше 120 и меньше 200.
Тот же запрос с использованием операторов сравнения будет выглядеть следующим образом:
SELECT StudName, Stipend FROM Student WHERE Stipend>=120000 AND Stipend<=200000
Предикат IN (NOT IN) проверяет, входит ли заданное значение, предшествующее ключевому слову “IN”, в указанный в скобках список. Например:
SELECT name FROM employee WHERE job_code IN («VP», «Admin», «Finan») – получить список сотрудников, занимающих должности “вице-президент”, “администратор”, “финансовый директор”.
Предикат LIKE проверяет, соответствует ли данное символьное значение строке с указанной маской. В качестве маски используются все разрешенные символы (с учетом верхнего и нижнего регистров), а также специальные символы: % – замещает любое количество символов, _ – замещает только один символ. Например:
SELECT StudName FROM Student WHERE StudName LIKE «Ф%» – получить список студентов, фамилии которых начинаются с буквы ‘Ф’.
Предикат CONTAINING аналогичен предикату LIKE, однако он не чувствителен к регистру букв.
Предикат IS NULL принимает значение true только тогда, когда выражение слева от “IS NULL” имеет значение null (пусто, не определено).
Логические операторы. К логическим операторам относятся NOT, AND, OR.В одном предикате логические операторы выполняются в указанном порядке.
Преобразование типов. В SQL имеется возможность преобразовать значение к другому типу для выполнения операций сравнения. Для этого используется функция CAST.
Изменение порядка выводимых строк. Порядок выводимых строк может быть изменен с помощью предложения ORDER BY в конце SQL-запроса. Это предложение имеет вид: ORDER BY [ASC | DESC]
Способом по умолчанию является упорядочивание “по возрастанию” (ASC). Если указано “DESC”, упорядочивание производится “по убыванию”. Например:
SELECT StudName, Stipend FROM Student ORDER BY StudName – получить список в алфавитном порядке.
Операция соединения.
Внутреннее соединение возвращает только те строки, для которых условие соединения принимает значение true. Рассмотрим пример запроса:
SELECT StudName, ExamMark FROM Student, Exams WHERE Kurs=2 AND ExamMark=5 – получить список студентов 2-го курса, сдававших экзамен на 5.
Внешнее соединение возвращает все строки из одной таблицы и те строки из другой таблицы, для которых условие соединения принимает значение true. Существуют два вида внешнего соединения: в левом соединении (LEFT JOIN) запрос возвращает все строки изтаблицы, стоящей слева от LEFT JOIN и только те из правой таблицы, которые удовлетворяют условию соединения. Для правого соединения – все наоборот. Например:
SELECT name, department FROM employee e LEFT JOIN department d ON e.dept_no = d.dept_no – получить список сотрудников и название их отделов, включая сотрудников, еще не назначенных ни в какой отдел.
Команды SQL для создания баз данных и таблиц
CREATE DATABASE database_name — создать базу данных,
CREATE TABLEtable_name(Имя_поля1 тип1, Имя_поля2 тип2…)-создать таблицу,
DROP DATABASE database_name — удалить базу данных,
DROP TABLE table_name-удалить таблицу,
ALTER DATABASE database_name — модифицировать базу данных,
ALTER TABLEtable_name-модифицировать таблицу,
INSERT INTO table_name(Имя_поля1 тип1, Имя_поля2 тип2…) values(‘val1’,’val2’,…) — вставка записи со значениями полей val1, val2, … .
DELETE FROM table_name WHERE выражение -удаление записи, для которой выполнено выражение
UPDATE table_nameSET(Имя_поля1 ‘val1’, ‘Имя_поля2 val2’…) where выражение –обновить таблицу.
Для выполнения SQL запроса используется функция mysql_query($sql);. Сначала создается строка, содержащая SQL – запрос. Затем эта строка передается для выполнения. Рассмотрим пример выполнения команд SQL по созданию базы данных из PHP:
//соединяемся с сервером
int mysql_connect();
// создаем базу данных:
$sql=”create database ‘mydb’”;
mysql_query($sql);//выполнение директивы SQL
//Cоздаем таблицу:
$sql=”CREATE TABLE userstable
(name VARCHAR(25), email VARCHAR(25), choise VARCHAR(15))”
mysql_query($sql); );//выполнение директивы SQL
Аналогично порядок работы с MySQL для выборки данных из базы в PHP устанавливается следующий:
Создать соединение с сервером MySQL, расположенным на $hostname:
int mysql_connect([string $hostname],[string $username],[string password]);
или mysql_pconnect().
Здесь hostname — имя хоста (по умолчанию localhost), username — имя пользователя, password — пароль пользователя. Функция возвращает параметр ID соединения (link_identifier), который равен 0, если соединение не прошло успешно.
Выбрать базу данных для работы:
int mysql_select_db($database_name[, int link_identifier]);
Здесь: database_name — имя базы данных, link_identifier – необязательный параметр, содержащий ID соединения из mysql_connect. Если этот параметр отсутствует, выбирается последнее открытое соединение. Функция возвращает значение true или false.
Выполнить запрос к базе данных.
int mysql_query(string $query[, int $link_identifier]);
Здесь $query — строка, содержащая SQL запрос, link_identifier –ID соединения. Функция возвращает ID результата или 0, если произошла ошибка.
Закрыть соединение с MySQL.
int mysql_close(int link_identifier); Параметры: link_identifier — ID соединения. Функция возвращает значение true или false.
Пример: Создание базы данных и таблицы.
<html>
<head>
<title>Запрос информации</title>
<body>
<center> Введите Ваши данные <p>
<table width = 400><tr><td align = right>
<form action=«pr1BD.php» method=«post»>
Ваше имя:<BR>
<input type=«text» name=«name» size=«20» maxlength=«30»>
<P>
Ваш email:<BR>
<input type=«text» name=«email» size=«20» maxlength=«30»>
<p>
<input type=«submit» value=«Отправить запрос!»>
</form>
</td></tr></table></center>
</body>
</html>
<?php // pr1bd.php — скрипт создает БД и таблицу
$dbName ='mydb';
/* создать соединение */
$link = mysql_connect( «localhost», «root» ) or exit(«Could not connect»);;
/* уничтожить старую БД */
$sql= sprintf(«DROP DATABASE %s »,$dbName);
if (mysql_query($sql, $link)) {
echo «Database drop successfully\n»;
} else { echo 'Error drop database: '. mysql_error(). "\n"; }
/* создать новую БД */
$sql=«create database $dbName»;
//выполнение директивы SQL
if (mysql_query($sql, $link)) {
echo «Database created successfully\n»;
} else { echo 'Error creating database: '. mysql_error(). "\n"; }
//Cоздаем таблицу: mydb.clients
$tablename = 'clients' ;
$sql=sprintf(«CREATE TABLE %s.%s (
`name` VARCHAR( 25 ) NOT NULL, `email` VARCHAR( 25 ) NOT NULL ) ENGINE = MYISAM »,$dbName,$tablename);
if (mysql_query($sql, $link)) {
echo «Table created successfully\n»;
} else { echo 'Error creating Table: '. mysql_error(). "\n"; }
$name=$_POST['name'];
$email=$_POST['email'];
// вставка записи со значениями полей val1, val2,
$sql=sprintf(«INSERT INTO %s.%s values('$name', '$email')», $dbName, $tablename);
if (mysql_query($sql, $link)) {
echo «Table insert successfully\n»;
} else { echo 'Error insert Table: '. mysql_error(). "\n"; }
mysql_close($link); /* Закрыть соединение */
$link = mysql_connect( «localhost», «root» )or die(«Не могу создать соединение „);;
//Выбрать БД
$result =mysql_select_db(“$dbName»,$link) or die(«Не могу выбрать базу данных „);
//создать SQL — запрос
$sql=sprintf(“Select * from %s», $tablename);
$result = mysql_query($sql,$link);
if (!$result) { die('Invalid query: '. mysql_error()); }
$myrow=mysql_fetch_array($result); //установка первой записи
echo"<br>", $myrow['name'];//вывод поля “name” первой записи
echo $myrow['email'];
mysql_close($link);/* Закрыть соединение */
?>