Mysql - базовое описание

Автор: Morty.


SQL - язык структурированных запросов для доступа к базам данных и их управления.
SQL может: выполнять запросы к базе данных
      извлекать данные из базы данных
      добавить записи в базу данных
      обновить записи в базе данных
      удалить записи из базы данных
      создавать новые базы данных
      создавать новые таблицы в базе данных
      создавать хранимые процедуры в базе данных
      настроить разрешения для таблиц
Принятые переменные:
table_name - имя таблицы
column_name - колонки(поля)
value - значения
pattern - шаблон
database_name - имя базы данных (БД)

SQL является стандартным - но ...
Несмотря на то, что SQL придерживаеться ANSI (Американского национального института стандартов)
стандарта, существует много различных диалектов языка SQL. Вместе с тем, чтобы быть
совместимым со стандартом ANSI, все они поддерживают, по крайней мере, основные
команды (например, SELECT, UPDATE, DELETE, INSERT, WHERE). Большинство из реляционных
баз данных имеют свои собственные расширения(функций, команд...) в дополнение к SQL стандарту!
В данном описании я буду рассматривать язык SQL в разрезе MySQL.
Чаще всего база данных содержит одну или несколько таблиц ;-). Каждая таблица
имеет определенное имя (например, "people" или "cars"). Таблицы содержат
поля (колонки) с записями(строки). Сейчас забежим немного наперед, тоесть создадим
сразу БД, табличку и заполним её. Т.к. я не знал каким образом лучше построить
материал, решил что лучший вариант это наглядный, тоесть писать в
примерах опираясь на тестовую БД.Поэтому весь описанный материал будет опираться на бд
которая приведена ниже...



mysql> create database mytest; Query OK, 1 row affected (0.00 sec) mysql> use mytest; Database changed mysql> CREATE TABLE people(id int,Name varchar(255),LastName varchar(255 -> addr varchar(255), telnumb int,birthdaydate date); Query OK, 0 rows affected (0.03 sec) mysql> show fields from people; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | Name | varchar(255) | YES | | NULL | | | LastName | varchar(255) | YES | | NULL | | | addr | varchar(255) | YES | | NULL | | | telnumb | int(11) | YES | | NULL | | | birhthdaydate | date | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> insert into people(id,Name,LastName,addr,telnumb,birthdaydate) -> values (1,'Vasia','Pupkin','selo Slavne','452','1922-10-23'); Query OK, 1 row affected, 1 warning (0.00 sec) ........ mysql> select * from people; +----+-------+-----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+-----------+-------------+---------+--------------+ | 1 | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +----+-------+-----------+-------------+---------+--------------+ 4 rows in set (0.00 sec) mysql>

Таблица содержит 4 строки и 6 колонок (id,Name, Lastname,addr,telnumb,birthdaydate).

Все действия в базе данных осуществляется с помощью SQL запросов.
Например следующий SQL запрос выведет все записи из таблицы "people" :
SELECT * from people;
(Для SQLя не важен регистр символов, заглавные, прописные, все равны
(SELECT и select - одно и тоже), также важно помнить что MySQL
требует чтобы каждый запрос заканчивался символом ";")

Для более легкого запоминания и понимания можно запросы разделить на
3 типа: DML(Data Manipulation Language) - управление данными(УД),
и определение(вид) данных DDL(Data Definition Language)(ОД).
И еще пожалуй системные запросы(СЗ)DCL (Data Control Language).Также необходимо всегда иметь под рукой информацию о том какие типы данных
применяються в таблицах MySQL. И в конце концов определить пользователей и их права.При необходимости написания скриптов, можно посмотреть 2 простых примера
К УД можно отнести:
SELECT - извлечение(выборка) данных из БД
UPDATE - обновление данных в БД
DELETE - удаление данных из БД
INSERT INTO - вставка(заполнение) новыми данными БД
к ОД:
CREATE DATABASE - создать новую БД
CREATE TABLE - создать новую таблицу
ALTER TABLE - изменить таблицу
DROP TABLE - удалить таблицу
CREATE INDEX - создать индекс (поисковый ключ)
к СЗ:
show processlist - показать процессы БД




SELECT - используеться для того чтобы делать выборки из БД
синтаксис:


SELECT column_name(s) FROM table_name;

column_names - если необходимо выбрать несколько полей,
они должны указываться через запятую, например


mysql> SELECT Name, LastName from people; +-------+-----------+ | Name | LastName | +-------+-----------+ | Vasia | Pupkin | | Petia | Zatochkin | | Semen | Krivoy | | Masha | Zolotaya | +-------+-----------+ 4 rows in set (0.00 sec) mysql>

* - всё
SELECT * from people;
выведет всё(все поля) что есть в таблице people;

Иногда в таблице, некоторые из колонок могут содержать повторяющиеся значения.
Это не проблема, т.к. всегда можно сделать выборку лишь тех значений
который разняться между собой(не дублируються). Ключевое слово DISTINCT может быть использовано для этих целей.
синтаксис:


SELECT DISTINCT column_name(s) FROM table_name;

например:


mysql> SELECT DISTINCT addr FROM people; +-------------+ | addr | +-------------+ | selo Slavne | | g.Zhitomir | | pgt. Pobeda | +-------------+ 3 rows in set (0.00 sec)

WHERE - используется для получения только тех записей,
которые соответствуют определенному критерию.
синтаксис:


SELECT column_name(s) FROM table_name WHERE column_name=value;

mysql> SELECT * FROM people WHERE Name='Vasia'; +------+-------+----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +------+-------+----------+-------------+---------+--------------+ | 1 | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | +------+-------+----------+-------------+---------+--------------+ 1 row in set (0.00 sec) mysql>

Здесь также могут использоваться операторы:
=    равно
<>    не равно
>    больше
<    меньше
>=    больше либо равно
<=    меньше либо равно
...
здесь еще много чего может быть... см. оффиц сайт либо любую книгу по MySQL
Операторы AND & OR
Оператор AND отображает данные если выполняются оба условия.
Оператор OR отображает данные если выполняеться хотябы одно условие.


mysql> SELECT * FROM people WHERE Name='Vasia' AND LastName='Pupkin'; +----+-------+----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+----------+-------------+---------+--------------+ | 1 | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | +----+-------+----------+-------------+---------+--------------+ 1 row in set (0.01 sec) mysql>

mysql> SELECT * FROM people WHERE Name='Semen' OR LastName='Zolotaya'; +----+-------+----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+----------+-------------+---------+--------------+ | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +----+-------+----------+-------------+---------+--------------+ 2 rows in set (0.00 sec) mysql>

Смешивание AND & OR


mysql> SELECT * FROM people WHERE Name='Petia' AND (LastName='Zatochkin' OR -> -> LastName='Krivoy'); +----+-------+-----------+------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+-----------+------------+---------+--------------+ | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | +----+-------+-----------+------------+---------+--------------+ 1 row in set (0.01 sec) mysql>

ORDER BY - оператор сортировки, используется для сортировки результатов,
установленных указанной колонки.ORDER BY по умолчанию сортирует
записи в возрастающем порядке.(ASC)
Если вы хотите сортировать записи в (обратном)порядке убывания,
вы можете использовать ключевое слово DESC.
синтаксис:


SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

mysql> SELECT * FROM people ORDER BY Name; +----+-------+-----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+-----------+-------------+---------+--------------+ | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 1 | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | +----+-------+-----------+-------------+---------+--------------+ 4 rows in set (0.01 sec)

в обратном порядке


mysql> SELECT * FROM people ORDER BY Name DESC; +----+-------+-----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+-----------+-------------+---------+--------------+ | 1 | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +----+-------+-----------+-------------+---------+--------------+ 4 rows in set (0.00 sec)

LIMIT - используеться для вывода определенной группы(кол-ва) данных
синтаксис:


SELECT column_name(s) FROM table_name LIMIT a,b;

a - число , с какой позиции выдавать данные
b - кол-во выводимых строк
примечание: также можно задавать 1 цифру, не две.


mysql> SELECT * from people LIMIT 1,3; +----+-------+-----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+-----------+-------------+---------+--------------+ | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +----+-------+-----------+-------------+---------+--------------+ 3 rows in set (0.00 sec)

LIKE - этот оператор используеться для поиска данных в полях по шаблону.
синтаксис:


SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

mysql> SELECT * from people WHERE Name LIKE '%em%'; +----+-------+----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+----------+-------------+---------+--------------+ | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | +----+-------+----------+-------------+---------+--------------+ 1 row in set (0.01 sec) mysql> SELECT * from people WHERE telnumb LIKE '2%'; +----+-------+-----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+-----------+-------------+---------+--------------+ | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | +----+-------+-----------+-------------+---------+--------------+ 2 rows in set (0.01 sec) mysql>

IN - позволяет задавать несколько значений в поиск
синтаксис:


SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)

mysql> SELECT * from people WHERE LastName IN('Krivoy','Zolotaya'); +----+-------+----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+----------+-------------+---------+--------------+ | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +----+-------+----------+-------------+---------+--------------+ 2 rows in set (0.00 sec)

BETWEEN - оператор с помощью которого можно вывести диапазон данных
который ограниченный двумя значениями полей.
синтаксис:


SELECT column_name(s) FROM table_name WHERE \ column_name BETWEEN value1 AND value2;

mysql> select * from people where id between 1 and 3; +----+-------+-----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+-----------+-------------+---------+--------------+ | 1 | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | +----+-------+-----------+-------------+---------+--------------+ 3 rows in set (0.00 sec)

Для того чтобы двигаться дальше создадим еще одну табличку...
Еще одна таблица cars в тойже БД mytest


+------+----------------+-----------+ | C_id | Cmodel | GosNumber | +------+----------------+-----------+ | 1 | GAZ | 2211 | | 2 | Zaporogets | 4521 | | 3 | Volga-supercar | 7711 | +------+----------------+-----------+

mysql> create table cars(C_id int, Cmodel varchar(255), GosNumber int); Query OK, 0 rows affected (0.01 sec) mysql> show columns from cars; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | C_id | int(11) | YES | | NULL | | | Cmodel | varchar(255) | YES | | NULL | | | GosNumber | int(11) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into cars values(1,'GAZ','2211'),(2,'Zaporogets','4521'), ->(3,'Volga-supercar','7711'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from cars; +------+----------------+-----------+ | C_id | Cmodel | GosNumber | +------+----------------+-----------+ | 1 | GAZ | 2211 | | 2 | Zaporogets | 4521 | | 3 | Volga-supercar | 7711 | +------+----------------+-----------+ 3 rows in set (0.00 sec) mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | cars | | people | +------------------+ 2 rows in set (0.00 sec) mysql>

JOIN - используется для запроса(вывода) данных из двух или более таблиц.


mysql> SELECT * from cars join people;

...вывод убрал, т.к. не очень красиво он тут "вписываеться"
UNION - оператор сочетает вывод двух и более SELECT операторов.
примечание: каждый запрос SELECT в сочетании с UNION должны иметь одинаковое
число столбцов. Колонки также должны иметь аналогичные типы данных.
синтаксис:


SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2;

делаем дополнительную таблицу cars2
+------+-----------+--------+
| id   | model     | number |
+------+-----------+--------+
|    1 | Mers      |   3411 |
|    2 | BMW       |   3317 |
|    3 | Velosiped |   3456 |
+------+-----------+--------+


mysql> SELECT * from cars UNION SELECT * FROM cars2; +------+----------------+-----------+ | C_id | Cmodel | GosNumber | +------+----------------+-----------+ | 1 | GAZ | 2211 | | 2 | Zaporogets | 4521 | | 3 | Volga-supercar | 7711 | | 1 | Mers | 3411 | | 2 | BMW | 3317 | | 3 | Velosiped | 3456 | +------+----------------+-----------+ 6 rows in set (0.00 sec)

UPDATE - используется для обновления существующих записей в таблице.
синтаксис:


UPDATE table_name SET column1=value, column2=value2,.. \ WHERE some_column=some_value;

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


UPDATE people SET Name='Vasia', LastName='Mikolich';

приведет таблицу к такому виду


mysql> update people set Name='Vasia', LastName='Mikolich'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from people; +----+-------+----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+----------+-------------+---------+--------------+ | 1 | Vasia | Mikolich | selo Slavne | 452 | 1922-10-23 | | 2 | Vasia | Mikolich | g.Zhitomir | 23 | 1995-04-12 | | 3 | Vasia | Mikolich | pgt. Pobeda | 231 | 1992-02-11 | | 4 | Vasia | Mikolich | selo Slavne | 5551232 | 1980-05-05 | +----+-------+----------+-------------+---------+--------------+ 4 rows in set (0.01 sec)

т.е. если необходимо обновить только поле где id=1
то нужно писать так:


mysql> UPDATE people SET Name='Mikola', LastName='Mikolich' WHERE id='1'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from people; +----+--------+-----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+--------+-----------+-------------+---------+--------------+ | 1 | Mikola | Mikolich | selo Slavne | 452 | 1922-10-23 | | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +----+--------+-----------+-------------+---------+--------------+ 4 rows in set (0.00 sec) mysql>

т.к. здесь присутствует WHERE соответсвенно можно комбинировать с AND & OR.
DELETE- удаление записей
синтаксис:


DELETE FROM table_name WHERE some_column=some_value;

mysql> delete from people where id=1; Query OK, 1 row affected (0.00 sec) mysql> slect * from people; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slect * from people' at line 1 mysql> select * from people; +----+-------+-----------+-------------+---------+--------------+ | id | Name | LastName | addr | telnumb | birthdaydate | +----+-------+-----------+-------------+---------+--------------+ | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +----+-------+-----------+-------------+---------+--------------+ 3 rows in set (0.00 sec) mysql>

INSERT - вставка(заполнение) данных в полях таблицы
синтаксис:


INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);

columnN - названия полей в таблице table_name
valueN  - значения полей, т.е. содержимое, те данные которые будут в поле
пример показан выше, где заполняеться таблица cars


mysql> insert into cars values(1,'GAZ','2211'), ->(2,'Zaporogets','4521'),(3,'Volga-supercar','7711'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from cars; +------+----------------+-----------+ | C_id | Cmodel | GosNumber | +------+----------------+-----------+ | 1 | GAZ | 2211 | | 2 | Zaporogets | 4521 | | 3 | Volga-supercar | 7711 | +------+----------------+-----------+ 3 rows in set (0.00 sec)


CREATE DATABASE - создает базу данных
синтаксис:


CREATE DATABASE database_name;

CREATE TABLE - для создания таблиц в БД
синтаксис:


CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... );

где table_name - имя таблицы, column_name1 - имя столбца,
data_type - тип данных для этого солбца;
Какие типы данных могут быть в MySQL описано в разделе "Типы данных"
пример создания таблицы cars:


CREATE TABLE cars(C_id int,CModel varchar(255),Gos_Number int);

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


CREATE TABLE people(id int,Name varchar(255),LastName varchar(255), addr varchar(255), telnumb int,birhthdaydate date);

понятно что таблицы будут пустые , и их предстоит заполнить.
ALTER TABLE - используется для изменения структуры таблиц.
Для того чтобы добавить(ADD)
колонку в таблицу необходимо выполнить следующее:
синтаксис:
'Добавить'


ALTER TABLE table_name ADD column_name datatype;

По умолчанию новый столбец вставляется в конец таблицы.


mysql> alter table people add country varchar(255); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from people; +---+-------+----------+------------+---------+--------------+---------+ |id | Name | LastName | addr | telnumb | birthdaydate | country | +---+-------+----------+------------+---------+--------------+---------+ | 1 | Vasia | Pupkin | selo Slavne| 452 | 1922-10-23 | NULL | | 2 | Petia | Zatochkin| g.Zhitomir | 23 | 1995-04-12 | NULL | | 3 | Semen | Krivoy | pgt. Pobeda| 231 | 1992-02-11 | NULL | | 4 | Masha | Zolotaya | selo Slavne| 5551232 | 1980-05-05 | NULL | +---+-------+----------+------------+---------+--------------+---------+ 4 rows in set (0.00 sec) mysql>

Если необходимо, чтобы столбец встал в начало таблицы,
нужно после параметров вставляемого столбца написать ключевое слово FIRST:


ALTER TABLE table_name ADD column_name datatype FIRST;

mysql> alter table people add country varchar(255) FIRST; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from people; +---------+------+-------+-----------+-------------+---------+--------------+ | country | id | Name | LastName | addr | telnumb | birthdaydate | +---------+------+-------+-----------+-------------+---------+--------------+ | NULL | 1 | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | | NULL | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | NULL | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | NULL | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +---------+------+-------+-----------+-------------+---------+--------------+ 4 rows in set (0.01 sec) mysql>

Если необходимо, чтобы столбец был вставлен не в начале таблицы,
и не в конце, а после определенного столбца, то нужно применить
ключевое слово AFTER 'имя столбца', после которого будет установлен
новый столбец:


ALTER TABLE table_name ADD column_name datatype AFTER column_name2;

добавит колонку column_name следом за колонкой column_name2


mysql> alter table people add country varchar(255) after id; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from people; +----+---------+-------+-----------+-------------+---------+--------------+ | id | country | Name | LastName | addr | telnumb | birthdaydate | +----+---------+-------+-----------+-------------+---------+--------------+ | 1 | NULL | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | | 2 | NULL | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | NULL | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 4 | NULL | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +----+---------+-------+-----------+-------------+---------+--------------+ 4 rows in set (0.00 sec) mysql>

Если нужно дописать к таблице не один, а несколько столбцов,
то для каждого столбца нужно ADD column_name datatype записать через запятую:


ALTER TABLE table_name ADD column_name datatype, ADD column_name2 datatype, ADD column_name3 datatype;

mysql> alter table people add country varchar(255), add state varchar(255); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from people; +---+-------+-----------+-------------+---------+-------------+--------+------+ |id | Name | LastName | addr | telnumb |birthdaydate | country| state| +---+-------+-----------+-------------+---------+-------------+--------+------+ | 1 | Vasia | Pupkin | selo Slavne | 452 |1922-10-23 | NULL | NULL | | 2 | Petia | Zatochkin | g.Zhitomir | 23 |1995-04-12 | NULL | NULL | | 3 | Semen | Krivoy | pgt. Pobeda | 231 |1992-02-11 | NULL | NULL | | 4 | Masha | Zolotaya | selo Slavne | 5551232 |1980-05-05 | NULL | NULL | +---+-------+-----------+-------------+---------+-------------+--------+------+ 4 rows in set (0.00 sec)

CHANGE - Изменить параметр
синтаксис:


ALTER TABLE table_name CHANGE column_name_old column_name_new parametrs;

где
 table_name - имя таблицы, в которой находится изменяемый столбец;
 column_name_old - имя столбца изменяемого столбца;
 column_name_new - новое имя изменяемого столбца (должно равняться column_name_old,
 если мы не хотим поменять имя столбца);
 parametrs - новые параметры столбца.


mysql> alter table people CHANGE LastName Familia varchar(255); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from people; +------+-------+-----------+-------------+---------+--------------+ | id | Name | Familia | addr | telnumb | birthdaydate | +------+-------+-----------+-------------+---------+--------------+ | 1 | Vasia | Pupkin | selo Slavne | 452 | 1922-10-23 | | 2 | Petia | Zatochkin | g.Zhitomir | 23 | 1995-04-12 | | 3 | Semen | Krivoy | pgt. Pobeda | 231 | 1992-02-11 | | 4 | Masha | Zolotaya | selo Slavne | 5551232 | 1980-05-05 | +------+-------+-----------+-------------+---------+--------------+ 4 rows in set (0.01 sec) mysql>

DROP- Для удаления колонки:
синтаксис:


ALTER TABLE table_name DROP column_name;

если необходимо удалить несколько колонок из таблицы - указываем все через
запятую.
Для того чтобы удалить 'индекс' необходимо поступить следующим образом:


ALTER TABLE table_name DROP INDEX index_name;

чтобы узнать имя индекса необходимо выполнить следующее:


SHOW KEYS from table_name;


DROP TABLE - удаляет табличку.
синтаксис:


DROP TABLE table_name;

CREATE INDEX
Индекс может быть создан для таблицы,это даст возможность
более быстро и эффективно работать.Пользователю индекс не
виден, он просто используется для ускорения поисков / запросов.
Примечание: Обновление таблицы с индексами занимает больше времени,
чем обновление таблицы без (поскольку индексы также нуждаются в обновлении).
Таким образом, индексы стоит создавать только на столбцы (таблицы),
по которым будет часто производиться поиск и запросы.
синтаксис:


CREATE INDEX index_name ON table_name (column_name,column_name2,..);

mysql> create index indx on people (Name); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0

до


mysql> show columns from people; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | Name | varchar(255) | YES | | NULL | | | LastName | varchar(255) | YES | | NULL | | | addr | varchar(255) | YES | | NULL | | | telnumb | int(11) | YES | | NULL | | | birthdaydate | date | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)

после


mysql> show columns from people; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | Name | varchar(255) | YES | MUL | NULL | | | LastName | varchar(255) | YES | | NULL | | | addr | varchar(255) | YES | | NULL | | | telnumb | int(11) | YES | | NULL | | | birthdaydate | date | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)

также более конкретно(имена индексов) можно посмотреть так


SHOW KEYS from table_name;

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


CREATE UNIQUE INDEX index_name ON table_name (column_name);


SHOW - используеться для отображения информации о полях, таблицах в БД;
самым популярным являеться
SHOW DATABASES - просмотрет какие есть базы в MySQL сервере
SHOW TABLES - показать таблицы Бд
SHOW FIELDS(COLUMNS)  - показать информацию о том какие поля есть в таблице,
каких они типов, пример:


SHOW columns from people;

SHOW KEYS from table_name - вот так и переводиться -) отобразить ключи таблицы
SHOW PROCESSLIST; - показать процессы сервера MySQL.




GRANT - Создание пользователей и назначение прав
синтаксис:


GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE, REFERENCES (column list), USAGE)) ON table TO user,... [WITH GRANT OPTION] [IDENTIFIED BY [PASSWORD] 'password']] WITH w_options

GRANT OPTION =
 | MAX_QUERIES_PER_HOUR count
 | MAX_UPDATES_PER_HOUR count
 | MAX_CONNECTIONS_PER_HOUR count
 | MAX_USER_CONNECTIONS count
table = *.*
       db_name.tbl_name
user = 'name'@'hostname'
w_options
 | MAX_QUERIES_PER_HOUR count
 | MAX_UPDATES_PER_HOUR count
 | MAX_CONNECTIONS_PER_HOUR count
 | MAX_USER_CONNECTIONS count
пример: (создать пользователя web(подкл. только с localhost), назначить пароль webpass,
назначить доп опции на максимально возможное кол-во запросов, соединений...)


GRANT USAGE ON *.* TO 'web'@'localhost' IDENTIFIED BY 'webpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ;

пример: (дать все права на бд web_base, пользователю web. База должна существовать.)


GRANT ALL PRIVILEGES ON `web_base`.* TO 'web'@'localhost' WITH GRANT OPTION;

SET PASSWORD - Установка пароля
синтаксис:


SET PASSWORD FOR 'user'@'hostname' = password('newpass');

аналог


GRANT USAGE ON *.* TO user@host IDENTIFIED BY 'password';

DROP USER - Удаление пользователя
синтаксис:


DROP USER user [, user] ...

SHOW GRANTS - Показать права


SHOW GRANTS FOR 'root'@'localhost';

RENAME USER - Переименовать пользователя
синтаксис:


RENAME USER old_user TO new_user;


Несколько примеров по работе с функциями:
AVG() - возвращает среднее значение


SELECT AVG(id) FROM people;

COUNT() - считает что прикажешь


SELECT COUNT(column_name) FROM table_name; SELECT COUNT(*) FROM table_name;

MAX() - возвращает максимальное значение
пример:


SELECT MAX(column_name) FROM table_name;

MIN() - возвращает минимальное значение
пример:


SELECT MIN(column_name) FROM table_name;

SUM() - функция суммирования
пример:


SELECT SUM(column_name) FROM table_name;

UCASE() - ковертирует символы в верхний регистр
пример:


SELECT UCASE(column_name) FROM table_name;

LCASE() - конвертирует символы в нижний регистр
пример:


SELECT LCASE(column_name) FROM table_name;


Целые числа


Тип Описание TINYINT Может хранить числа от -128 до 127 SMALLINT Диапазон от -32768 до 32767 MEDIUMINT Диапазон от -8388608 до 8388607 INT Диапазон от -2147483648 до 2147483647 BIGINT Диапазон от -9223372036854775808 до 9223372036854775807

Дробные числа


Тип Описание FLOAT Число с плавающей точкой небольшой точности DOUBLE Число с плавающей точкой двойной точности REAL Синоним для DOUBLE DECIMAL Дробное число хранящееся в виде строки NUMERIC Синоним для DECIMAL

Строки (символьные данные)


Тип Описание VARCHAR Может хранить не более 255 символов TINYTEXT Может хранить не более 255 символов TEXT Может хранить не более 65 535 символов MEDIUMTEXT Может хранить не более 16 777 215 символов LONGTEXT Может хранить не более 4 294 967 295 символов

Бинарные данные(учитываеться регистр символов)


Тип Описание TINYBLOB Может хранить не более 255 символов BLOB Может хранить не более 65 535 символов MEDIUMBLOB Может хранить не более 16 777 215 символов LONGBLOB Может хранить не более 4 294 967 295 символов

Дата и время


Тип Описание DATE Дата в формате ГГГГ-ММ-ДД TIME Время в формате ЧЧ:ММ:СС DATETIME Дата и время в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС TIMESTAMP Дата и время в формате ГГГГММДДЧЧММСС


mysql - SQL шелл.Может работать как в интерактивном режиме, т.е.
дает возможность подключиться  к базе и посредством SQL команд и запросов
производить какие-либо действия с БД, так и не интерактивном тоесть вызвано исполнение кода из шелл-скрипта например.
пример:


mysql --user=user_name --password=your_password db_name

подключиться к указанной бд определеннім пользвоателем.


mysql --user=user_name --password=your_password db_name < db_structure.sql

таким образом можно восстанавливать БД по имеющемуся дампу БД.


mysql --user=user_name --password=your_password \ db_name--execute="sql commands"

может быть использвана в шелл-скриптах если необходимо производить какие-то запросы из скрипта.
myisamchk - тулза для работы с MyISAM таблицами. Может чекать, восстанавливать и оптимизировать таблицы.(Работает с файлами  .MYD и .MYI)
Перед использование желательно делать бэкапы.
пример:


myisamchk *.MYI

mysql_config - показывает опции с которыми был скомпилирован Mysql.
mysql_fix_privilege_tables - скрипт, который обновляет все таблицы с привилегиями.
mysql_install_db - скрипт который инсталит базы,таблицы которые нужны для работы MYSQL.
mysql_tzinfo_to_sql - загружает таблицы временных зон (time zones).
mysql_upgrade - запускаеться каждый раз при обновелнии сервера MySQL. Проверяет все таблицы во всех базах на совместимость с текущей версией.
Чтобы проверить и восстановить таблицы, и апгрейднуть системные таблицы mysql_upgrade
выполняет следующую команду:


mysqlcheck --check-upgrade --all-databases --auto-repair mysql_fix_privilege_tables

mysql_waitpid - запуск с ожиданим программы чей pid и время её ожидания можно утсановить


mysql_waitpid [options] #pid #time

mysqladmin - тулза для администрирования MySQL сервера.
mysqladmin create db_name - создать новую БД с именем db_name
drop db_name - удалить базу
extended-status - расширенная информация о состоянии сервера
flush-hosts - очистить всю информацию о хостах в кэше
flush-logs - очистить все логи
flush-privileges - перезагрузить таблицы с привилегиями (тоже что и reload)
flush-status - очистить статус переменных
flush-tables - очистить все таблицы
password - устанавливает пароль для БД
shutdown - остановить сервер
Для случая когда сервер настроен с подчиненными серверами (репликация)
startslave - запустить репликацию на подчиненном сервере (slave)
stop-slave - остановить репликацию на подчиненом сервере (slave)
status - отображает краткую информацию о статусе сервера
variables - отобразить переменные сервера и их значения
threads - покажет кол-во активных клиентских трэдов (threads)
--host=host_name - (-h hostname )подключиться к MySQL на укзанный хост
--password=password (-p[password]) - указать пароль на подключение
--port=port_num (-P port_num) - указать порт подключения
--protocol={TCP|SOCKET|PIPE|MEMORY} - указывает протокол/метод с
помощью которого происходит подключение      
--socket=path (-S path) - подключение на файл-сокет
--ssl* - защищенное подключение, через ssl
--user=user_name, -u user_name - подключающейся пользователь
mysqlbinlog - инструмент для обработки бинарных логов MySQL'я
Сохранить все логи в файл


mysqlbinlog binlog.000001 > /tmp/statements.sql

mysqlbug   - скрипт, баг-репортер
mysqlcheck - тулза "чекер" и восстановитель таблиц
--all-databases (-A) прочекать все таблицы во всех базах
--auto-repair - автоматически восттанавливать(чинить) таблицы
--optimize -o - оптимизировать таблицы
--repair (-r) - починить таблицы
mysqld_safe - скрипт запуска сервера(рекомендуемый)
mysqldump - инструмент для бэкапирования баз


mysqldump -u[username] -p[password] -h[host] db_name [tables] > backup.sql

--ignore-table=db_name.tbl_name - игнорить определенную таблицу при бэкапе.
--lock_tables (-l) лочить таблицы перед бєкапированием.
--xml, (-X) сохранить бэкап в формате xml
mysqlimport - прога для импорта данных, в различных форматах
mysqlshow - тулза для быстрого просмотра баз, таблиц, полей.




Парочка простых примеров:
Выполнение запросов из шела:


#!/bin/sh /usr/local/bin/mysql --host=localhost --user=exim --password=exim \ --database=exim --execute="DELETE FROM \ \`sended_list\` WHERE \`last_mail_timestamp\` \ < (UNIX_TIMESTAMP() - 60*24*3600)"

#!/bin/sh user="dbuser" pass="dbpassword" db="dbnme" host="localhost" mysql --host="$host" -u "$user" -p"$pass" "$db" <<EOF sql-statement-1; sql-statement-2; EOF

#!/bin/sh user="dbuser" pass="dbpassword" db="dbnme" host="localhost" sql="select * from tal_name" mysql --host="$host" -u "$user" -p"$pass" <<EOF use $db; $sql; EOF

пример для PHP (создание БД):


<?php $sql="CREATE DATABASE db_name"; mysql_query($sql); ?>

Документация (первоисточник):
http://dev.mysql.com/doc/
http://www.mysql.ru/docs/man/
Функции в MySQL

  Этот информационный блок появился по той простой причине, что многие считают нормальным, брать чужую информацию не уведомляя автора (что не так страшно), и не оставляя линк на оригинал и автора — что более существенно. Я не против распространения информации — только за. Только условие простое — извольте подписывать автора, и оставлять линк на оригинальную страницу в виде прямой, активной, нескриптовой, незакрытой от индексирования, и не запрещенной для следования роботов ссылки.
  Если соизволите поставить автора в известность — то вообще почёт вам и уважение.

© lissyara 2006-10-24 08:47 MSK