Создание баз данных

Для создания базы данных dbase_name достаточно в командной строке MySQL ввести

mysql> create database dbase_name;

mysql> create database phone;

Query OK, 1 row affected (0.03 sec)

Установка привилегий проводится с помощью команды GRANT

Синтаксис 
GRANT privileges [columns]
    ON item
    TO user_name [IDENTIFIED BY 'password']
    [WITH GRANT OPTION]

Привилегии пользователя представляют собой набор команд MySQL с правами на их выполнение.

Привилегия	Область применения	Описание
ALTER	таблицы	Разрешается изменять структуру таблиц, переименовывая, изменяя тип и добавляя столбцы
CREATE	базы данных, таблицы	Разрешается создавать новые базы данных или таблицы
DELETE	таблицы	Разрешается удалять существующие строки
DROP	базы данных, таблицы	Разрешается удалять новые базы данных или таблицы
INDEX	таблицы	Разрешается создавать и удалять индексы
INSERT	таблицы, столбцы	Разрешается вставлять новые строки в таблицы
SELECT	таблицы, столбцы	Разрешается выбирать строки в таблицах
UPDATE	таблицы, столбцы	Разрешается изменять значения в строках

Привилегии администратора

Привилегия	Описание
FILE	Разрешается помещать в таблицы данные из файлов и наоборот
PROCESS	Разрешается просматривать и удалять процессы на сервере
SHUTDOWN	Разрешается останавливать сервер MySQL
RELOAD	Разрешено изменять таблицы привилегий, подавлять привилегии.

Специальные привилегии

Привилегия	Описание
ALL	Разрешено все
USAGE	Запрещено все

Команда REVOKE имеет аналогичный синтаксис, что и GRANT и является его противоположностью. Приведем пример предоставления прав администратора

mysql> grant all
     -> on *
     -> to admin identified by 'mybase'
     -> with grant option;

mysql> grant select,insert,update,delete
     -> on mybase.*
     -> to client identified by 'mypassword';

Для определения базы данной, с которой требуется работать, нужно набрать строку

mysql> use mybase;

CREATE TABLE table_name(columns)

NOT NULL указывает, что все строки таблицы должны быть заполнены. В противном слечае, то есть если NULL, то строки могут быть не заполнены.

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

PRIMARY KEY указывает на столбец с первичных ключом. Этот столбец индексируется автоматически.

UNSIGNED описывает поле целочисленного типа с неотрицательными данными.

Пример создания таблиц базы Phone

mysql.exe < phone.sql

connect phone;
CREATE TABLE Clients (
        PhoneNumber VARCHAR(7) NOT NULL PRIMARY KEY,
    FirstName VARCHAR(15),  
    SecondName VARCHAR(15), 
    LastName VARCHAR(15) 
);
CREATE TABLE ConnectClients (
        PhoneNumber VARCHAR(7) NOT NULL,
    DateConnect DATE NOT NULL,  
    TimeConnect INT UNSIGNED, 
    Tariff FLOAT(5,2) 
);

mysql> use phone;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_phone |
+-----------------+
| clients         |
| connectclients  |
+-----------------+
2 rows in set (0.00 sec)

mysql> describe clients;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| PhoneNumber | varchar(7)  |      | PRI |         |       |
| FirstName   | varchar(15) | YES  |     | NULL    |       |
| SecondName  | varchar(15) | YES  |     | NULL    |       |
| LastName    | varchar(15) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

Работа с данными

Синтаксис 
INSERT [INTO] table [(column_1,column_2,...)]
       VALUES (value_1,value_2,...)
 

insert into Clients (PhoneNumber,FirstName,LastName) values
( "1-00-01", "Иван","Иванов"
);

insert into Clients 
set  
PhoneNumber="1-01-00",
FirstName="Петр",
LastName="Иваненко";

connect phone;
insert into ConnectClients 
set  
PhoneNumber="1-00-01",
DateConnect="2003-01-01",
TimeConnect=15,
Tariff=5.25;
insert into ConnectClients 
set  
PhoneNumber="1-01-00",
DateConnect="2003-01-15",
TimeConnect=20,
Tariff=15.00;
insert into ConnectClients 
set  
PhoneNumber="1-00-01",
DateConnect="2003-01-01",
TimeConnect=12,
Tariff=15.00;
insert into ConnectClients 
set  
PhoneNumber="1-00-01",
DateConnect="2003-08-10",
TimeConnect=2,
Tariff=15.00;
insert into ConnectClients 
set  
PhoneNumber="1-01-00",
DateConnect="2003-04-08",
TimeConnect=12,
Tariff=5.25;

Синтаксис 
SELECT items
FROM tables
     [WHERE condition]
     [GROUP BY group_type]
     [HAVING where_definition]
     [ORDER BY order_type]
     [LIMIT limit_type]

Пример просмотра полей PhoneNumber и LastName таблицы Clients

mysql> use phone;
Database changed
mysql> select phonenumber,lastname
    -> from clients;
+-------------+----------+
| phonenumber | lastname |
+-------------+----------+
| 1-00-01     | Иванов   |
| 1-01-00     | Иваненко |
+-------------+----------+

mysql> select *
    -> from clients;
+-------------+-----------+------------+----------+
| PhoneNumber | FirstName | SecondName | LastName |
+-------------+-----------+------------+----------+
| 1-00-01     | Иван      | NULL       | Иванов   |
| 1-01-00     | Петр      | NULL       | Иваненко |
+-------------+-----------+------------+----------+

Оператор	Описание
=,>,>=,<,<=,<> или !=	Проверка условия сравнения
IS NOT NULL, IS NULL	Сравнение с пустым полем
BETWEEN	Сравнение с диапазоном
IN, NOT IN	Проверка на содержание в множестве
LIKE, NOT LIKE	Проверка на соответствие образцу (шаблону)
REGEXP	Проверка на соответствие регулярному выражению в стиле POSIX.

При использовании соответствия LIKE образец может состоять из обычного текста со знаками процента (%) для указания совпадения с произвольным числом символов и символом подчеркивания (_) для указания совпадения с одним символом.

В случае использования нескольких критериев одновременно можно использовать операции AND или OR.

Пример

mysql> select * from clients where LastName like "Иван%" and PhoneNumber like "1-00-__";
+-------------+-----------+------------+----------+
| PhoneNumber | FirstName | SecondName | LastName |
+-------------+-----------+------------+----------+
| 1-00-01     | Иван      | NULL       | Иванов   |
+-------------+-----------+------------+----------+

Пример простого объединения двух таблиц.

mysql> select Clients.PhoneNumber, Clients.LastName, ConnectClients.DateConnect,ConnectClients.TimeC
onnect
    -> from ConnectClients, Clients
    -> where Clients.PhoneNumber=ConnectClients.PhoneNumber and Clients.LastName like "Иван%" and Co
nnectClients.TimeConnect>10;
+-------------+----------+-------------+-------------+
| PhoneNumber | LastName | DateConnect | TimeConnect |
+-------------+----------+-------------+-------------+
| 1-00-01     | Иванов   | 2003-01-01  |          15 |
| 1-00-01     | Иванов   | 2003-01-01  |          12 |
| 1-01-00     | Иваненко | 2003-01-15  |          20 |
| 1-01-00     | Иваненко | 2003-04-08  |          12 |
+-------------+----------+-------------+-------------+

При работе с базами часто возникает необходимость в различной информации о данных столбца - наибольшее или наименьшее значение, сумма всех данных и пр. С этой целью в MySQL реализованы функции агрегирования. Приведем наиболее употребительные функции агрегирования.

Функция	Описание
AVG	Среднее значение значений столбца
COUNT	Число ненулевых значений столбца. Если перед названием столбца поставить DISTING, то возвращает число конкретных значений в столбце, если указать COUNT(*) то подсчет количества столбцов будет проводиться независимо от значений.
MIN	минимальное значение в столбце
MAX	максимальное значение в столбце
SUM	сумма значений в столбце

Пример.

mysql> use phone;
Database changed
mysql> select avg(TimeConnect)
    -> from connectClients;
+------------------+
| avg(TimeConnect) |
+------------------+
|          12.2000 |
+------------------+

mysql> use phone;
Database changed
mysql> select PhoneNumber,sum(TimeConnect)
    -> from connectClients
    -> group by PhoneNumber;
+-------------+------------------+
| PhoneNumber | sum(TimeConnect) |
+-------------+------------------+
| 1-00-01     |               29 |
| 1-01-00     |               32 |
+-------------+------------------+
2 rows in set (0.03 sec)

При использовании функций агрегирования и функции группирования можно использовать выбор по условию. Для этой цели используется оператор HAVING. Синтаксис оператора HAVING похож на WHERE, но применяется к группам. Например, выберем все номера телефонов, суммарное время подключения которых больше 30.

mysql> use phone;
Database changed
mysql> select PhoneNumber,sum(TimeConnect)
    -> from connectClients
    -> group by PhoneNumber
    -> having sum(TimeConnect)>30;
+-------------+------------------+
| PhoneNumber | sum(TimeConnect) |
+-------------+------------------+
| 1-01-00     |               32 |
+-------------+------------------+
1 row in set (0.01 sec)

select Clients
for LastName
Limit 3,4;

mysql> use phone;
Database changed
mysql> select LastName, FirstName
    -> from Clients
    -> order by FirstName;
+----------+-----------+
| LastName | FirstName |
+----------+-----------+
| Иванов   | Иван      |
| Иваненко | Петр      |
+----------+-----------+
2 rows in set (0.00 sec)

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

Синтаксис 
UPDATE tablename
    SET column1=value1,column2=value2,...
    [WHERE condition]
    [LIMIT number]

Например, увеличение тарифов на 10% можно записать следующим образом

mysql> use phone;
Database changed
mysql> update ConnectClients
    -> set tariff=tariff*1.1;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

Синтаксис	Описание
ADD[COLUMN]column_name [FIRST | AFTER column]	Добавить новый столбец в указанное место. Если место не указано, то добавляется в конец.
ADD PRIMARY KEY(column,...)	Сделать указанный столбец (столбцы) первичных ключом таблицы.
ADD UNIQUE[index](column,...)	Добавить уникальный индекс в указанный столбец (столбцы).
CHANGE [COLUMN] column new_descripsion	Изменить столбец column так чтобы он получил новое описание new_descripsion (включая название).
MODIFY [COLUMN] column new_descripsion	То же, но без возможности изменить имя.
DROP [COLUMN] column	Удалить столбец.
DROP INDEX index	Удалить индекс
RENAME[AS] new_table_name	Переименовать таблицу.

Пример. Если поле для фамилии клиента не достаточно велико, его можно расширить, используя следующую запись.

mysql> use phone;
Database changed
mysql> alter table Clients
    -> modify LastName char(25);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

Работа PHP с базами данных MySQL

if (!$ClientQuery){
print "Повторите запрос";
exit;
}

@ $Db=mysql_pconnect($hostname,$username,$password); 

@ $Db=mysql_connect($hostname,$username,$password);

use phone;

mysql_select_db("phone");

$ClientQuery= "select * from Clients where Clients.LastName like 'Иван%'";

$result=mysql_query($ClientQuery);

mysql_db_query(NameDataBase,StringQuery,[int DataBaseConnection])

Для определения числа строк возвращаемых запросом используется функция mysql_numrows().
Пример использования

for ($i=0;$i < mysql_numrows($result);i++){
//обработка запроса
}

$InfoClient=mysql_fetch_array($result);
print "Телефон: ";
print $InfoClient["PhoneNumber"];

$InfoClient=mysql_result($result, $i, "PhoneNumber");

Рассмотрим пример обработки данных, полученных из формы html. Прежде всего создадим базу данных products

mysql.exe CREATE database  products

mysql.exe <products.txt

connect products;
CREATE TABLE clients (
    name VARCHAR(25), 
    email VARCHAR(25), 
    choise VARCHAR(8)
);

Создадим форму запроса -файл shop.html

<HTML><HEAD><TITLE>Запрос</TITLE><BODY><CENTER>
<h3>Информация о наших товарах</h3><P>
<TABLE WIDTH = 400><TR><TD align = right><FORM ACTION="email.php" METHOD="POST">
Ваша фамилия и имя:
<INPUT TYPE="text" NAME="name" SIZE="20" MAXLENGTH="30"><P>Ваш email:
<INPUT TYPE="text" NAME="email" SIZE="20" MAXLENGTH="30"><P>У нас можно приобрести:
<SELECT NAME="preference" ><OPTION value ="Стулья">Стулья 
<OPTION value = "Рога">Рога
<OPTION value = "Копыта">Копыта
</SELECT><P>
<INPUT TYPE="submit"  VALUE="Отправить запрос!">
</FORM></TD></TR></TABLE></CENTER></BODY></HTML>

Обработку запроса будем проводить в файле email.php

<?
$name=$HTTP_POST_VARS["name"];
$email=$HTTP_POST_VARS["email"]; 
$preference=$HTTP_POST_VARS["preference"];
// Переменные 
$hostname = "localhost";
$username = "";
$password = "";
$dbName = "products";
/* Таблица MySQL, в которой хранятся данные */
$userstable = "clients";
/* создать соединение */
MYSQL_CONNECT($hostname,$username,$password) OR DIE("Не могу создать соединение ");
@mysql_select_db("$dbName") or die("Не могу выбрать базу данных "); 
PRINT "<CENTER>";
PRINT "<h3>ООО Рога и копыта </h3>  приветствует Вас, $name. ";
PRINT "<p>";
PRINT "Вас интересуют $preference. <p> Подробную информацию мы пошлем вам на  email:<i> $email.</i>";
/* Вставить информацию о клиенте в таблицу */
$query = "INSERT INTO $userstable VALUES('$name','$email', '$preference')";
$result = MYSQL_QUERY($query);
PRINT "<p><i>Информация о вас занесена в базу данных.</i>";
PRINT "</CENTER>";
/* Закрыть соединение */
MYSQL_CLOSE();
?>

Файл просмотра статистики info.html

<HTML><HEAD><TITLE>Запрос информации</TITLE>
<BODY><CENTER><h3>Информация о наших клиентах</h3><P>
<TABLE WIDTH = 400><TR><TD align = right><FORM ACTION="stat.php" METHOD="POST">
<P>Меня интересуют клиенты выбравшие:
<SELECT NAME="preference" ><OPTION value ="Стулья">Стулья 
<OPTION value = "Рога">Рога
<OPTION value = "Копыта">Копыта
</SELECT><P>
<INPUT TYPE="submit"  VALUE="Отправить запрос!">
</FORM></TD></TR></TABLE></CENTER></BODY></HTML>

Файл обработки информации stat.php

<?/* Скрипт показывает клиентов*/
$preference=$HTTP_POST_VARS["preference"];
$hostname = "localhost";
$username = "";
$password = "";
$dbName = "products";
/* Таблица MySQL, в которой хранятся данные */
$userstable = "clients";
/* создать соединение */
$link=MYSQL_CONNECT($hostname,$username,$password) OR DIE("Не могу создать соединение ");
@mysql_select_db($dbName,$link) or die("Не могу выбрать базу данных "); 
/* Выбрать всех клиентов  */
$query = "SELECT * FROM $userstable WHERE choise = '$preference'";
$result = MYSQL_QUERY($query);
/* Количество клиентов */
$number = MYSQL_NUMROWS($result);
/* Напечатать список клиентов*/
$i = 0;
IF ($number == 0) {
PRINT "<CENTER><P>Желающих приобрести $preference нет</CENTER>";
} ELSEIF ($number > 0) 
{
PRINT "<CENTER><P>Количество клиентов желающих приобрести $preference : $number<BR><BR>";
WHILE ($i < $number){
$name = mysql_result($result,$i,"name");
$email = mysql_result($result,$i,"email");
PRINT "Клиент $name желает приобрести $preference.<BR>";
PRINT "Его Email: $email.";
PRINT "<BR><BR>";
$i++;
}
PRINT "</CENTER>";
}
?>

Функции MySQL

<?php
mysql_connect("marliesle");
echo mysql_errno().": ".mysql_error()."<BR>";
mysql_select_db("nonexistentdb");
echo mysql_errno().": ".mysql_error()."<BR>";
$conn = mysql_query("SELECT * FROM nonexistenttable");
echo mysql_errno().": ".mysql_error()."<BR>";
?>

<?php
mysql_connect("marliesle");
echo mysql_errno().": ".mysql_error()."<BR>";
mysql_select_db("nonexistentdb");
echo mysql_errno().": ".mysql_error()."<BR>";
$conn = mysql_query("SELECT * FROM nonexistenttable");
echo mysql_errno().": ".mysql_error()."<BR>";
?>

select t1.f1 as foo t2.f1 as bar from t1, t2

<?php 
mysql_connect($host,$user,$password);
$result = mysql_db_query("database","select * from table");
while($row = mysql_fetch_array($result)) {
    echo $row["user_id"];
    echo $row["fullname"];
}
mysql_free_result($result);
?>

<?php 
mysql_connect($host,$user,$password);
$result = mysql_db_query("database","select * from table");
while($row = mysql_fetch_object($result)) {
    echo $row->user_id;
    echo $row->fullname;
}
mysql_free_result($result);
?>

<?php 
mysql_connect("localhost:3306");
mysql_select_db("wisconsin");
$result = mysql_query("SELECT * FROM onek");
$fields = mysql_num_fields($result);
$rows   = mysql_num_rows($result);
$i = 0;
$table = mysql_field_table($result, $i);
echo "Your '".$table."' table has ".$fields." fields and ".$rows." records <BR>";
echo "The table has the following fields <BR>"; 
while ($i < $fields) {
    $type  = mysql_field_type  ($result, $i);
    $name  = mysql_field_name  ($result, $i);
    $len   = mysql_field_len   ($result, $i);
    $flags = mysql_field_flags ($result, $i);
    echo $type." ".$name." ".$len." ".$flags."<BR>";
    $i++;
}
mysql_close();
?>

<?php 
mysql_connect ("localhost:3306");
$result = mysql_listtables ("wisconsin");
$i = 0;
while ($i < mysql_num_rows ($result)) {
    $tb_names[$i] = mysql_tablename ($result, $i);
    echo $tb_names[$i] . "<BR>";
    $i++;
}
?>