Nurick
28.04.2014, 17:10
Для версий MySQL R5-R6, MySQL R33
Mysql connect
mysql_connect - Функция подключения плагина к серверу.
Переменные
mysql_connect(const host[], const user[], const database[], const password[])
const host[] IP или хост сервера.
const user[] Пользователь базы данных.
const database[] Имя базы данных.
const password[] Пароль от указанного пользователя.
Возвращает Номер подключения
Пример:
public OnGameModeInit()
{
mysql_connect("127.0.0.1","samper","samp","123");//Подключились
}
Mysql close
mysql_close - Функция закрытия соединения с базой данных.
Переменные
mysql_close(connectionHandle)
connectionHandle Номер подключения возвращённый функцией mysql_connect.
Возвращает Результат (0 или 1).
Пример:
public OnGameModeExit()
{
mysql_close();//Отключились
}
Mysql debug
mysql_debug - функция включение логирования действий MySQL плагина.
Переменные
mysql_debug(bool:enable)
enable Включить (1) либо выключить (0) логирование. По умолчанию 1.
Пример:
public OnGameModeInit ( )
{
mysql_debug(false);//Логирование выключено
}
Mysql ping
mysql_ping - Функция закрытия соединения с базой данных.
Переменные
mysql_ping(connectionHandle)
connectionHandle Номер подключения возвращённый функцией mysql_connect.
Возвращает Статус базы (0-соединение потеряно, или 1-соединение присутствует).
Пример:
public OnPlayerConnect(playerid)
{
switch(mysql_ping())
{
case 0:
{
SendClientMessage(playerid, 0x00FF00FF, "Нет доступа к базе данных... Закрытие соединения...");
Kick(playerid);
}
case 1: SendClientMessage(playerid, 0x00FF00FF, "Добро пожаловать на сервер.");
}
return 1;
}
Операторы:
Операторы определения данных (Data Definition Language, DDL)
CREATE создает объект БД (саму базу, таблицу, представление, пользователя и т. д.)
Стандарт SQL-92 определяет команду CREATE в вариантах:
CREATE ASSERTION — создание утверждения для проверки
CREATE CHARACTER SET — создание набора символов
CREATE COLLATION — создание правила сортировки для набора символов
CREATE DOMAIN — создание домена (пользовательского типа данных столбца).
CREATE SCHEMA — создание схемы (именованной группы объектов)
CREATE TABLE — создание таблицы базы данных
CREATE TRANSLATION — создание правила преобразования (трансляции) из одного набора символов в другой (используется в операторе TRANSLATE)
CREATE VIEW — создание представления данных
Пример:
CREATE TABLE Student (
Code INTEGER NOT NULL,
Name CHAR (30) NOT NULL ,
Address CHAR (50),
Mark DECIMAL
);
CREATE VIEW London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;
ALTER изменяет объект
ALTER TABLE - данный запрос используется для добавления, удаления или модификации колонки в уже существующей таблице.
Пример:
Для добавления колонки в таблицу, используйте следующий синтаксис:
ALTER TABLE table_name
ADD column_name datatype
Для удаления колонки в таблице, используйте следующий синтаксис (не все базы данных позволяют удалять одну колонку):
ALTER TABLE table_name
DROP COLUMN column_name
Для изменения типа данных колонки, используйте следующий синтаксис:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
DROP удаляет объект
DROP TABLE Удаляет одно или больше определений таблиц и все данные, индексы, триггеры, ограничения и разрешения для этих таблиц. Любое представление или хранимая процедура, ссылающаяся на удаленную таблицу, должна быть явно удалена с помощью инструкции
Структура:
DROP VIEW или DROP PROCEDURE. Получить отчет о зависимостях в таблице можно с помощью sys.dm_sql_referencing_entities.
DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
table_name [ ,...n ] [ ; ]
database_name Имя базы данных, в которой создана таблица.
schema_name Имя схемы, которой принадлежит таблица.
table_name Имя таблицы, предназначенной для удаления.
Замечания
Инструкцию DROP TABLE нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY. Сначала следует удалить ссылающееся ограничение FOREIGN KEY или ссылающуюся таблицу. Если и ссылающаяся таблица, и таблица, содержащая первичный ключ, удаляются с помощью одной инструкции DROP TABLE, ссылающаяся таблица должна быть первой в списке.
Несколько таблиц можно удалить из любой базы данных. Если удаляемая таблица ссылается на первичный ключ другой таблицы, которая также удаляется, ссылающаяся таблица с внешним ключом должна стоять в списке перед таблицей, содержащей указанный первичный ключ.
При удалении таблицы относящиеся к ней правила и значения по умолчанию теряют привязку, а любые связанные с таблицей ограничения или триггеры автоматически удаляются. Если таблица будет создана заново, нужно будет заново привязать все правила и значения по умолчанию, заново создать триггеры и добавить необходимые ограничения.
При удалении всех строк в таблице с помощью инструкции DELETE tablename или TRUNCATE TABLE таблица продолжает существовать, пока она не будет удалена.
Большие таблицы и индексы из более чем 128 экстентов удаляются в два этапа: логический и физический. На логическом этапе существующие единицы распределения, используемые в таблице, отмечаются для освобождения и остаются заблокированными до фиксации транзакции. На физическом этапе страницы IAM, отмеченные для освобождения, физически удаляются пакетами. Дополнительные сведения см. в разделе Удаление и повторная сборка больших объектов.
При удалении таблицы, которая содержит столбец VARBINARY(MAX) с атрибутом FILESTREAM, не будут удалены никакие данные, которые хранятся в файловой системе.
Примеры:
Удаление таблицы из текущей базы данных:
DROP TABLE ProductVendor1 ;
Удаление таблицы из другой базы данных
DROP TABLE AdventureWorks2008R2.dbo.SalesPerson2 ;
Для удаления строки с нужным нам значением:
DELETE FROM users WHERE name='Mark_Edisson'
DELETE FROM users WHERE pLevel=30
Операторы манипуляции данными (Data Manipulation Language, DML)
SELECT считывает данные, удовлетворяющие заданным условиям
SELECT("селект") — оператор DML языка SQL, возвращающий набор данных (выборку) из базы данных, удовлетворяющих заданному условию. В большинстве случаев, выборка осуществляется из одной или нескольких таблиц. В последнем случае говорят об операции слияния (англ. join). В тех СУБД, где реализованы представления и хранимые процедуры, также возможно получение соответствующих наборов данных.
При формировании запроса SELECT пользователь описывает ожидаемый набор данных: его вид (набор столбцов) и его содержимое (критерий попадания записи в набор, группировка значений, порядок вывода записей и т.п.).
Запрос выполняется следующим образом: сначала извлекаются все записи из таблицы, а, затем, для каждой записи набора проверяется её соответствие заданному критерию. Если осуществляется слияние из нескольких таблиц, то сначала составляется произведение таблиц, а уже затем из полученного набора отбираются требуемые записи.
Особую роль играет обработка NULL-значений, когда при слиянии, например, двух таблиц — главной (англ. master) и подчинённой (англ. detail) — имеются или отсутствуют соответствия между записями таблиц, участвующих в слиянии. Для решения этой задачи используются механизмы внутреннего (англ. inner) и внешнего (англ. outer) слияния.
Один и тот же набор данных может быть получен при выполнении различных запросов. Поиск оптимального плана выполнения данного запроса является задачей оптимизатора.
Структура оператора
Оператор SELECT имеет следующую структуру:
SELECT
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[FROM table_references]
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
Примеры:
Получить все данные из таблицы "Table"
SELECT * FROM TABLE;
Данный запрос получает все данные столбца "name" в таблице "Table"
SELECT name FROM TABLE;
Данный запрос получит все значения из таблицы "Table" у которой столбец "name" имеет значение "Mark_Edisson"
SELECT * FROM TABLE WHERE name = 'Mark_Edisson';
Данный запрос работает как и первый на получение всех данных из столбца но в обратном порядке (Я - А)
SELECT * FROM TABLE ORDER BY name DESC
Найти все элементы в базе где в поле pLevel удовлетворяет одному из значений
SELECT * FROM TABLE WHERE pLevel IN (4,7,8)
Ограничение возвращаемых строк
ROW_NUMBER()
Для возврата не более 10 строк
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY KEY ASC) AS rownumber,
COLUMNS
FROM tablename
) AS foo
WHERE rownumber <= 10
ROW_NUMBER может быть недетерминированным: если key не уникален, каждый раз при выполнении запроса возможно присвоение разных номеров строкам, у которых key совпадает. Когда key уникален, каждая строка будет всегда получать уникальный номер строки.
RANK()
Функция RANK() OVER работает почти так же, как ROW_NUMBER, но может вернуть более чем n строк при определённых условиях. Например, для получения top-10 самых молодых людей:
SELECT * FROM (
SELECT
RANK() OVER (ORDER BY age ASC) AS ranking,
person_id,
person_name,
age
FROM person
) AS foo
WHERE ranking <= 10
INSERT добавляет новые данные
INSERT — оператор языка SQL, который позволяет добавить строки в таблицу, заполняя их значениями. Значения можно вставлять перечислением с помощью слова values и перечислив их в круглых скобках через запятую или оператором SELECT.
Примеры использования
Создание столбцов (pLevel, pMoney) в таблице Table
INSERT INTO TABLE (pLevel, pMoney) VALUES (1,50000)
Используя перечисление значений, без указания столбцов: (записать pLevel = 2, pMoney = 3000 )
INSERT INTO TABLE VALUES (2, 3000)
Используя SELECT:
INSERT INTO TABLE SELECT pLevel,pMoney FROM TABLE
В последнем случае, в таблицу может вставиться более одной записи. Если в таблице есть другие поля требующие заполнения, но не указанные в операторе insert, для них будет установлено значение по умолчанию, либо null, если значение по умолчанию не указано.
Особенности
Во время выполнения оператора могут возникнуть ошибки:
Eсли при создании таблицы для поля был указан параметр not null и не было определено значение по умолчанию (см. create), то при **отсутствии для него вставляемого значения возникнет ошибка. Решение очевидно:
либо убрать параметр not null
либо указать значение по умолчанию
либо вставить значение
если произойдет попытка вставки в поле с типом identity (автоинкремент), то также произойдет ошибка. Решить проблему можно двумя способами:
не вставлять значение в это поле
указать опцию identity_insert on после чего вставить уникальное значение для этого столбца
Опция identity_insert
Включить опцию:
SET identity_insert ON
После включения этой опции можно вставлять значения в поля определенные как identity. Нужно учесть, что значение должно быть уникальным. Включать эту опцию не рекомендуется, её следует использовать в записях в которых нужно сменить некоторые столбцы, не поменяв её identity столбец (например если по этому столбцу подцепляется другая таблица)
Получение ключа
Разработчики, которые используют суррогатный ключ в качестве первичного ключа, часто сталкиваются со сценарием, когда необходимо получить первичный ключ (для использования в других запросах), сгенерированный базой данных в ответ на оператор SQL INSERT. Но большинство систем не позволяют оператору SQL INSERT возвращать данные. Возможные способы решения:
Использовать характерную для данной БД хранимую процедуру, которая генерирует суррогатный ключ, исполняет операцию INSERT, и возвращает сгенерированный ключ. Например, в Microsoft SQL Server, ключ возвращается специальной функцией SCOPE_IDENTITY(), а в SQLite функцией last_insert_rowid().
Использовать характерную для данной БД операцию SELECT над временной таблицей, содержащей последнюю добавленную строку (или строки). DB2 реализует эту возможность следующим образом:
SELECT *
FROM NEW TABLE ( INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) ) AS t
Используя оператор SELECT после оператора INSERT, со специальной функцией, которая в данной БД возвращает сгенерированный первичный ключ для последней добавленной строки.
Используя уникальную комбинацию полей, которые входили в исходный оператор SQL INSERT, в последующих вызовах оператора SELECT.
Используя GUID в операторе SQL INSERT, получить результат через оператор SELECT.
Используя функцию PHP mysql_insert_id() для MySQL после оператора INSERT.
Используя INSERT с последующим SELECT LAST_INSERT_ID() для MySQL.
Используя оператор INSERT с предложением OUTPUT (Transact-SQL) начиная с Microsoft SQL Server 2005.
DECLARE @TABLE TABLE (
[id] [SMALLINT] IDENTITY(1,1) NOT NULL,
[name] [NCHAR](50) NULL);
INSERT INTO @TABLE
OUTPUT INSERTED.id
VALUES ('Peter Doe');
Используя оператор INSERT с модификатором RETURNING для Oracle.
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id INTO v_pb_id
Используя оператор INSERT с модификатором RETURNING для PostgreSQL (с версии 8.2). Возвращаемый список идентичен результату оператора SELECT.
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id
Функция IDENTITY() в H2 возвращает последний добавленный (ключ?)
SELECT IDENTITY();
UPDATE изменяет существующие данные
UPDATE — оператор языка SQL, позволяющий обновить значения в заданных столбцах таблицы.
Общий вид команды
UPDATE [top(x)] <объект>
SET <присваивание1 [, присваивание2, ...]>
[WHERE <условие>]
[OPTION <хинт1 [, хинт2, ...]>]
top(x) — команда выполнится только х раз
<объект> — объект, над которым выполняется действие (таблица или представление(views))
<присваивание> — присваивание, которое будет выполняться при каждом выполнении условия <условие>, или для каждой записи, если отсутствует раздел where
<условие> — условие выполнения команды
<хинт> — инструкция программе как исполнить запрос
set- после ключевого слова должен идти список полей таблицы, которые будут обновлены и непосредственно сами новые значения в виде имя поля="значение"
Примеры:
UPDATE top(10) tbl_books SET price = 0 WHERE quantity = 0
OPTION (force GROUP, hash JOIN, force ORDER)
UPDATE per SET street = 'Nis 67', ci = 'Sand' WHERE lastname = 'Tjes' AND firs = 'Ja'
UPDATE emp a SET deptno =
(SELECT deptno FROM dept WHERE loc = ‘BOSTON’), (sal, comm) = (SELECT 1.1*AVG(sal), 1.5*AVG(comm)
FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = ‘DALLAS’ OR loc = ‘DETROIT’);
DELETE удаляет данные
DELETE — в языках, подобных SQL, DML-операция удаления записей из таблицы. Критерий отбора записей для удаления определяется выражением where. В случае, если критерий отбора не определён, выполняется удаление всех записей.
В СУБД, поддерживающих триггеры, операция Delete может вызывать их срабатывание;
При наличии на таблице внешних ключей все дочерние к удаляемым записи в подчинённых таблицах также должны быть удалены для обеспечения ссылочной целостности;
В СУБД, поддерживающих транзакции, выполнение операции Delete должно быть подтверждено (COMMIT), либо опровергнуто (ROLLBACK) вызовом соответствующих операций.
Синтаксис
Общий синтаксис команды:
DELETE FROM <Имя Таблицы> WHERE <Условие отбора записей>
Последствием выполнения такой команды будет удаление тех строк из таблицы <Имя Таблицы>, которые соответствуют условию <Условие отбора записей>. При этом никакого результата команда не возвращает и, следовательно, не может быть использована в качестве параметра в команде SELECT.
Удаление записей из нескольких таблиц
При удалении можно задавать конструкции JOIN связывающие несколько страниц, аналогично тому, как связываются таблицы в запросах выборки SELECT:
DELETE <Имя записи для удаления>
-> FROM <Имя Таблицы1> JOIN <Имя Таблицы2> ON <условие объединения>;
Mysql connect
mysql_connect - Функция подключения плагина к серверу.
Переменные
mysql_connect(const host[], const user[], const database[], const password[])
const host[] IP или хост сервера.
const user[] Пользователь базы данных.
const database[] Имя базы данных.
const password[] Пароль от указанного пользователя.
Возвращает Номер подключения
Пример:
public OnGameModeInit()
{
mysql_connect("127.0.0.1","samper","samp","123");//Подключились
}
Mysql close
mysql_close - Функция закрытия соединения с базой данных.
Переменные
mysql_close(connectionHandle)
connectionHandle Номер подключения возвращённый функцией mysql_connect.
Возвращает Результат (0 или 1).
Пример:
public OnGameModeExit()
{
mysql_close();//Отключились
}
Mysql debug
mysql_debug - функция включение логирования действий MySQL плагина.
Переменные
mysql_debug(bool:enable)
enable Включить (1) либо выключить (0) логирование. По умолчанию 1.
Пример:
public OnGameModeInit ( )
{
mysql_debug(false);//Логирование выключено
}
Mysql ping
mysql_ping - Функция закрытия соединения с базой данных.
Переменные
mysql_ping(connectionHandle)
connectionHandle Номер подключения возвращённый функцией mysql_connect.
Возвращает Статус базы (0-соединение потеряно, или 1-соединение присутствует).
Пример:
public OnPlayerConnect(playerid)
{
switch(mysql_ping())
{
case 0:
{
SendClientMessage(playerid, 0x00FF00FF, "Нет доступа к базе данных... Закрытие соединения...");
Kick(playerid);
}
case 1: SendClientMessage(playerid, 0x00FF00FF, "Добро пожаловать на сервер.");
}
return 1;
}
Операторы:
Операторы определения данных (Data Definition Language, DDL)
CREATE создает объект БД (саму базу, таблицу, представление, пользователя и т. д.)
Стандарт SQL-92 определяет команду CREATE в вариантах:
CREATE ASSERTION — создание утверждения для проверки
CREATE CHARACTER SET — создание набора символов
CREATE COLLATION — создание правила сортировки для набора символов
CREATE DOMAIN — создание домена (пользовательского типа данных столбца).
CREATE SCHEMA — создание схемы (именованной группы объектов)
CREATE TABLE — создание таблицы базы данных
CREATE TRANSLATION — создание правила преобразования (трансляции) из одного набора символов в другой (используется в операторе TRANSLATE)
CREATE VIEW — создание представления данных
Пример:
CREATE TABLE Student (
Code INTEGER NOT NULL,
Name CHAR (30) NOT NULL ,
Address CHAR (50),
Mark DECIMAL
);
CREATE VIEW London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;
ALTER изменяет объект
ALTER TABLE - данный запрос используется для добавления, удаления или модификации колонки в уже существующей таблице.
Пример:
Для добавления колонки в таблицу, используйте следующий синтаксис:
ALTER TABLE table_name
ADD column_name datatype
Для удаления колонки в таблице, используйте следующий синтаксис (не все базы данных позволяют удалять одну колонку):
ALTER TABLE table_name
DROP COLUMN column_name
Для изменения типа данных колонки, используйте следующий синтаксис:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
DROP удаляет объект
DROP TABLE Удаляет одно или больше определений таблиц и все данные, индексы, триггеры, ограничения и разрешения для этих таблиц. Любое представление или хранимая процедура, ссылающаяся на удаленную таблицу, должна быть явно удалена с помощью инструкции
Структура:
DROP VIEW или DROP PROCEDURE. Получить отчет о зависимостях в таблице можно с помощью sys.dm_sql_referencing_entities.
DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
table_name [ ,...n ] [ ; ]
database_name Имя базы данных, в которой создана таблица.
schema_name Имя схемы, которой принадлежит таблица.
table_name Имя таблицы, предназначенной для удаления.
Замечания
Инструкцию DROP TABLE нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY. Сначала следует удалить ссылающееся ограничение FOREIGN KEY или ссылающуюся таблицу. Если и ссылающаяся таблица, и таблица, содержащая первичный ключ, удаляются с помощью одной инструкции DROP TABLE, ссылающаяся таблица должна быть первой в списке.
Несколько таблиц можно удалить из любой базы данных. Если удаляемая таблица ссылается на первичный ключ другой таблицы, которая также удаляется, ссылающаяся таблица с внешним ключом должна стоять в списке перед таблицей, содержащей указанный первичный ключ.
При удалении таблицы относящиеся к ней правила и значения по умолчанию теряют привязку, а любые связанные с таблицей ограничения или триггеры автоматически удаляются. Если таблица будет создана заново, нужно будет заново привязать все правила и значения по умолчанию, заново создать триггеры и добавить необходимые ограничения.
При удалении всех строк в таблице с помощью инструкции DELETE tablename или TRUNCATE TABLE таблица продолжает существовать, пока она не будет удалена.
Большие таблицы и индексы из более чем 128 экстентов удаляются в два этапа: логический и физический. На логическом этапе существующие единицы распределения, используемые в таблице, отмечаются для освобождения и остаются заблокированными до фиксации транзакции. На физическом этапе страницы IAM, отмеченные для освобождения, физически удаляются пакетами. Дополнительные сведения см. в разделе Удаление и повторная сборка больших объектов.
При удалении таблицы, которая содержит столбец VARBINARY(MAX) с атрибутом FILESTREAM, не будут удалены никакие данные, которые хранятся в файловой системе.
Примеры:
Удаление таблицы из текущей базы данных:
DROP TABLE ProductVendor1 ;
Удаление таблицы из другой базы данных
DROP TABLE AdventureWorks2008R2.dbo.SalesPerson2 ;
Для удаления строки с нужным нам значением:
DELETE FROM users WHERE name='Mark_Edisson'
DELETE FROM users WHERE pLevel=30
Операторы манипуляции данными (Data Manipulation Language, DML)
SELECT считывает данные, удовлетворяющие заданным условиям
SELECT("селект") — оператор DML языка SQL, возвращающий набор данных (выборку) из базы данных, удовлетворяющих заданному условию. В большинстве случаев, выборка осуществляется из одной или нескольких таблиц. В последнем случае говорят об операции слияния (англ. join). В тех СУБД, где реализованы представления и хранимые процедуры, также возможно получение соответствующих наборов данных.
При формировании запроса SELECT пользователь описывает ожидаемый набор данных: его вид (набор столбцов) и его содержимое (критерий попадания записи в набор, группировка значений, порядок вывода записей и т.п.).
Запрос выполняется следующим образом: сначала извлекаются все записи из таблицы, а, затем, для каждой записи набора проверяется её соответствие заданному критерию. Если осуществляется слияние из нескольких таблиц, то сначала составляется произведение таблиц, а уже затем из полученного набора отбираются требуемые записи.
Особую роль играет обработка NULL-значений, когда при слиянии, например, двух таблиц — главной (англ. master) и подчинённой (англ. detail) — имеются или отсутствуют соответствия между записями таблиц, участвующих в слиянии. Для решения этой задачи используются механизмы внутреннего (англ. inner) и внешнего (англ. outer) слияния.
Один и тот же набор данных может быть получен при выполнении различных запросов. Поиск оптимального плана выполнения данного запроса является задачей оптимизатора.
Структура оператора
Оператор SELECT имеет следующую структуру:
SELECT
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[FROM table_references]
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
Примеры:
Получить все данные из таблицы "Table"
SELECT * FROM TABLE;
Данный запрос получает все данные столбца "name" в таблице "Table"
SELECT name FROM TABLE;
Данный запрос получит все значения из таблицы "Table" у которой столбец "name" имеет значение "Mark_Edisson"
SELECT * FROM TABLE WHERE name = 'Mark_Edisson';
Данный запрос работает как и первый на получение всех данных из столбца но в обратном порядке (Я - А)
SELECT * FROM TABLE ORDER BY name DESC
Найти все элементы в базе где в поле pLevel удовлетворяет одному из значений
SELECT * FROM TABLE WHERE pLevel IN (4,7,8)
Ограничение возвращаемых строк
ROW_NUMBER()
Для возврата не более 10 строк
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY KEY ASC) AS rownumber,
COLUMNS
FROM tablename
) AS foo
WHERE rownumber <= 10
ROW_NUMBER может быть недетерминированным: если key не уникален, каждый раз при выполнении запроса возможно присвоение разных номеров строкам, у которых key совпадает. Когда key уникален, каждая строка будет всегда получать уникальный номер строки.
RANK()
Функция RANK() OVER работает почти так же, как ROW_NUMBER, но может вернуть более чем n строк при определённых условиях. Например, для получения top-10 самых молодых людей:
SELECT * FROM (
SELECT
RANK() OVER (ORDER BY age ASC) AS ranking,
person_id,
person_name,
age
FROM person
) AS foo
WHERE ranking <= 10
INSERT добавляет новые данные
INSERT — оператор языка SQL, который позволяет добавить строки в таблицу, заполняя их значениями. Значения можно вставлять перечислением с помощью слова values и перечислив их в круглых скобках через запятую или оператором SELECT.
Примеры использования
Создание столбцов (pLevel, pMoney) в таблице Table
INSERT INTO TABLE (pLevel, pMoney) VALUES (1,50000)
Используя перечисление значений, без указания столбцов: (записать pLevel = 2, pMoney = 3000 )
INSERT INTO TABLE VALUES (2, 3000)
Используя SELECT:
INSERT INTO TABLE SELECT pLevel,pMoney FROM TABLE
В последнем случае, в таблицу может вставиться более одной записи. Если в таблице есть другие поля требующие заполнения, но не указанные в операторе insert, для них будет установлено значение по умолчанию, либо null, если значение по умолчанию не указано.
Особенности
Во время выполнения оператора могут возникнуть ошибки:
Eсли при создании таблицы для поля был указан параметр not null и не было определено значение по умолчанию (см. create), то при **отсутствии для него вставляемого значения возникнет ошибка. Решение очевидно:
либо убрать параметр not null
либо указать значение по умолчанию
либо вставить значение
если произойдет попытка вставки в поле с типом identity (автоинкремент), то также произойдет ошибка. Решить проблему можно двумя способами:
не вставлять значение в это поле
указать опцию identity_insert on после чего вставить уникальное значение для этого столбца
Опция identity_insert
Включить опцию:
SET identity_insert ON
После включения этой опции можно вставлять значения в поля определенные как identity. Нужно учесть, что значение должно быть уникальным. Включать эту опцию не рекомендуется, её следует использовать в записях в которых нужно сменить некоторые столбцы, не поменяв её identity столбец (например если по этому столбцу подцепляется другая таблица)
Получение ключа
Разработчики, которые используют суррогатный ключ в качестве первичного ключа, часто сталкиваются со сценарием, когда необходимо получить первичный ключ (для использования в других запросах), сгенерированный базой данных в ответ на оператор SQL INSERT. Но большинство систем не позволяют оператору SQL INSERT возвращать данные. Возможные способы решения:
Использовать характерную для данной БД хранимую процедуру, которая генерирует суррогатный ключ, исполняет операцию INSERT, и возвращает сгенерированный ключ. Например, в Microsoft SQL Server, ключ возвращается специальной функцией SCOPE_IDENTITY(), а в SQLite функцией last_insert_rowid().
Использовать характерную для данной БД операцию SELECT над временной таблицей, содержащей последнюю добавленную строку (или строки). DB2 реализует эту возможность следующим образом:
SELECT *
FROM NEW TABLE ( INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' ) ) AS t
Используя оператор SELECT после оператора INSERT, со специальной функцией, которая в данной БД возвращает сгенерированный первичный ключ для последней добавленной строки.
Используя уникальную комбинацию полей, которые входили в исходный оператор SQL INSERT, в последующих вызовах оператора SELECT.
Используя GUID в операторе SQL INSERT, получить результат через оператор SELECT.
Используя функцию PHP mysql_insert_id() для MySQL после оператора INSERT.
Используя INSERT с последующим SELECT LAST_INSERT_ID() для MySQL.
Используя оператор INSERT с предложением OUTPUT (Transact-SQL) начиная с Microsoft SQL Server 2005.
DECLARE @TABLE TABLE (
[id] [SMALLINT] IDENTITY(1,1) NOT NULL,
[name] [NCHAR](50) NULL);
INSERT INTO @TABLE
OUTPUT INSERTED.id
VALUES ('Peter Doe');
Используя оператор INSERT с модификатором RETURNING для Oracle.
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id INTO v_pb_id
Используя оператор INSERT с модификатором RETURNING для PostgreSQL (с версии 8.2). Возвращаемый список идентичен результату оператора SELECT.
INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
RETURNING phone_book_id
Функция IDENTITY() в H2 возвращает последний добавленный (ключ?)
SELECT IDENTITY();
UPDATE изменяет существующие данные
UPDATE — оператор языка SQL, позволяющий обновить значения в заданных столбцах таблицы.
Общий вид команды
UPDATE [top(x)] <объект>
SET <присваивание1 [, присваивание2, ...]>
[WHERE <условие>]
[OPTION <хинт1 [, хинт2, ...]>]
top(x) — команда выполнится только х раз
<объект> — объект, над которым выполняется действие (таблица или представление(views))
<присваивание> — присваивание, которое будет выполняться при каждом выполнении условия <условие>, или для каждой записи, если отсутствует раздел where
<условие> — условие выполнения команды
<хинт> — инструкция программе как исполнить запрос
set- после ключевого слова должен идти список полей таблицы, которые будут обновлены и непосредственно сами новые значения в виде имя поля="значение"
Примеры:
UPDATE top(10) tbl_books SET price = 0 WHERE quantity = 0
OPTION (force GROUP, hash JOIN, force ORDER)
UPDATE per SET street = 'Nis 67', ci = 'Sand' WHERE lastname = 'Tjes' AND firs = 'Ja'
UPDATE emp a SET deptno =
(SELECT deptno FROM dept WHERE loc = ‘BOSTON’), (sal, comm) = (SELECT 1.1*AVG(sal), 1.5*AVG(comm)
FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = ‘DALLAS’ OR loc = ‘DETROIT’);
DELETE удаляет данные
DELETE — в языках, подобных SQL, DML-операция удаления записей из таблицы. Критерий отбора записей для удаления определяется выражением where. В случае, если критерий отбора не определён, выполняется удаление всех записей.
В СУБД, поддерживающих триггеры, операция Delete может вызывать их срабатывание;
При наличии на таблице внешних ключей все дочерние к удаляемым записи в подчинённых таблицах также должны быть удалены для обеспечения ссылочной целостности;
В СУБД, поддерживающих транзакции, выполнение операции Delete должно быть подтверждено (COMMIT), либо опровергнуто (ROLLBACK) вызовом соответствующих операций.
Синтаксис
Общий синтаксис команды:
DELETE FROM <Имя Таблицы> WHERE <Условие отбора записей>
Последствием выполнения такой команды будет удаление тех строк из таблицы <Имя Таблицы>, которые соответствуют условию <Условие отбора записей>. При этом никакого результата команда не возвращает и, следовательно, не может быть использована в качестве параметра в команде SELECT.
Удаление записей из нескольких таблиц
При удалении можно задавать конструкции JOIN связывающие несколько страниц, аналогично тому, как связываются таблицы в запросах выборки SELECT:
DELETE <Имя записи для удаления>
-> FROM <Имя Таблицы1> JOIN <Имя Таблицы2> ON <условие объединения>;