Как выбрать все поля из таблицы в базе данных кроме одного, не перечисляя их?
Как выбрать все поля из таблицы в базе данных кроме одного, не перечисляя их. Есть ли такая функция в MySQL?
Отслеживать
задан 24 июл 2015 в 12:04
61 1 1 золотой знак 1 1 серебряный знак 8 8 бронзовых знаков
Тут почитайте sql-ex.ru/help/select20.php?Lang=0
24 июл 2015 в 12:30
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
SELECT CONCAT( 'SELECT ', ( SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA. COLUMNS WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = 'table' AND COLUMN_NAME NOT IN ('field') ORDER BY ORDINAL_POSITION ), ' FROM table' ) INTO @asd; PREPARE newsql FROM @asd; EXECUTE newsql; где database — база данных, table — таблица, field — поле (столбец), который исключаем. Решение так себе, но работает.
Отслеживать
ответ дан 24 июл 2015 в 13:35
user179410 user179410
Никак. Нет такого способа. Придется вытаскивать все, либо перечислять.
UPD: Хотя на английском SO был предложен способ =)
Отслеживать
ответ дан 24 июл 2015 в 12:09
2,166 3 3 золотых знака 14 14 серебряных знаков 29 29 бронзовых знаков
-
Важное на Мете
Похожие
Подписаться на ленту
Лента вопроса
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
Дизайн сайта / логотип © 2023 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2023.11.15.1019
Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.
Синтаксис запросов SELECT
Все секции являются необязательными, за исключением списка выражений сразу после SELECT , о котором более подробно будет рассказано ниже.
Особенности каждой необязательной секции рассматриваются в отдельных разделах, которые перечислены в том же порядке, в каком они выполняются:
- Секция WITH
- Секция SELECT
- Секция DISTINCT
- Секция FROM
- Секция SAMPLE
- Секция JOIN
- Секция PREWHERE
- Секция WHERE
- Секция GROUP BY
- Секция LIMIT BY
- Секция HAVING
- Секция LIMIT
- Секция OFFSET
- Секция UNION ALL
- Секция INTERSECT
- Секция EXCEPT
- Секция INTO OUTFILE
- Секция FORMAT
Секция SELECT
Выражения указанные в секции SELECT анализируются после завершения всех вычислений из секций, описанных выше. Вернее, анализируются выражения, стоящие над агрегатными функциями, если есть агрегатные функции. Сами агрегатные функции и то, что под ними, вычисляются при агрегации ( GROUP BY ). Эти выражения работают так, как будто применяются к отдельным строкам результата.
Если в результат необходимо включить все столбцы, используйте символ звёздочка ( * ). Например, SELECT * FROM . .
Чтобы включить в результат несколько столбцов, выбрав их имена с помощью регулярных выражений re2, используйте выражение COLUMNS .
COLUMNS('regexp') Например, рассмотрим таблицу:
CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog Следующий запрос выбирает данные из всех столбцов, содержащих в имени символ a .
SELECT COLUMNS('a') FROM col_names ┌─aa─┬─ab─┐ │ 1 │ 1 │ └────┴────┘ Выбранные стоблцы возвращаются не в алфавитном порядке.
В запросе можно использовать несколько выражений COLUMNS , а также вызывать над ними функции.
SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names ┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐ │ 1 │ 1 │ 1 │ Int8 │ └────┴────┴────┴────────────────┘ Каждый столбец, возвращённый выражением COLUMNS , передаётся в функцию отдельным аргументом. Также можно передавать и другие аргументы, если функция их поддерживаем. Аккуратно используйте функции. Если функция не поддерживает переданное количество аргументов, то ClickHouse генерирует исключение.
SELECT COLUMNS('a') + COLUMNS('c') FROM col_names Received exception from server (version 19.14.1): Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus doesn't match: passed 3, should be 2. В этом примере, COLUMNS(‘a’) возвращает два столбца: aa и ab . COLUMNS(‘c’) возвращает столбец bc . Оператор + не работает с тремя аргументами, поэтому ClickHouse генерирует исключение с соответствущим сообщением.
Столбцы, которые возвращаются выражением COLUMNS могут быть разных типов. Если COLUMNS не возвращает ни одного столбца и это единственное выражение в запросе SELECT , то ClickHouse генерирует исключение.
Звёздочка
В любом месте запроса, вместо выражения, может стоять звёздочка. При анализе запроса звёздочка раскрывается в список всех столбцов таблицы (за исключением MATERIALIZED и ALIAS столбцов). Есть лишь немного случаев, когда оправдано использовать звёздочку:
- при создании дампа таблицы;
- для таблиц, содержащих всего несколько столбцов — например, системных таблиц;
- для получения информации о том, какие столбцы есть в таблице; в этом случае, укажите LIMIT 1 . Но лучше используйте запрос DESC TABLE ;
- при наличии сильной фильтрации по небольшому количеству столбцов с помощью PREWHERE ;
- в подзапросах (так как из подзапросов выкидываются столбцы, не нужные для внешнего запроса).
В других случаях использование звёздочки является издевательством над системой, так как вместо преимуществ столбцовой СУБД вы получаете недостатки. То есть использовать звёздочку не рекомендуется.
Экстремальные значения
Вы можете получить в дополнение к результату также минимальные и максимальные значения по столбцам результата. Для этого выставите настройку extremes в 1. Минимумы и максимумы считаются для числовых типов, дат, дат-с-временем. Для остальных столбцов будут выведены значения по умолчанию.
Вычисляются дополнительные две строчки — минимумы и максимумы, соответственно. Эти две дополнительные строки выводятся в форматах JSON* , TabSeparated* , и Pretty* отдельно от остальных строчек. В остальных форматах они не выводится.
Во форматах JSON* , экстремальные значения выводятся отдельным полем ‘extremes’. В форматах TabSeparated* , строка выводится после основного результата и после ‘totals’ если есть. Перед ней (после остальных данных) вставляется пустая строка. В форматах Pretty* , строка выводится отдельной таблицей после основного результата и после totals если есть.
Экстремальные значения вычисляются для строк перед LIMIT , но после LIMIT BY . Однако при использовании LIMIT offset, size , строки перед offset включаются в extremes . В потоковых запросах, в результате может учитываться также небольшое количество строчек, прошедших LIMIT .
Замечания
Вы можете использовать синонимы (алиасы AS ) в любом месте запроса.
В секциях GROUP BY , ORDER BY и LIMIT BY можно использовать не названия столбцов, а номера. Для этого нужно включить настройку enable_positional_arguments. Тогда, например, в запросе с ORDER BY 1,2 будет выполнена сортировка сначала по первому, а затем по второму столбцу.
Детали реализации
Если в запросе отсутствуют секции DISTINCT , GROUP BY , ORDER BY , подзапросы в IN и JOIN , то запрос будет обработан полностью потоково, с использованием O(1) количества оперативки. Иначе запрос может съесть много оперативки, если не указаны подходящие ограничения:
- max_memory_usage
- max_rows_to_group_by
- max_rows_to_sort
- max_rows_in_distinct
- max_bytes_in_distinct
- max_rows_in_set
- max_bytes_in_set
- max_rows_in_join
- max_bytes_in_join
- max_bytes_before_external_sort
- max_bytes_before_external_group_by
Подробнее смотрите в разделе «Настройки». Присутствует возможность использовать внешнюю сортировку (с сохранением временных данных на диск) и внешнюю агрегацию.
Модификаторы запроса SELECT
Вы можете использовать следующие модификаторы в запросах SELECT .
APPLY
Вызывает указанную функцию для каждой строки, возвращаемой внешним табличным выражением запроса.
Синтаксис:
SELECT expr> APPLY( func> ) FROM [db.]table_name Пример:
CREATE TABLE columns_transformers (i Int64, j Int16, k Int64) ENGINE = MergeTree ORDER by (i); INSERT INTO columns_transformers VALUES (100, 10, 324), (120, 8, 23); SELECT * APPLY(sum) FROM columns_transformers; ┌─sum(i)─┬─sum(j)─┬─sum(k)─┐ │ 220 │ 18 │ 347 │ └────────┴────────┴────────┘ EXCEPT
Исключает из результата запроса один или несколько столбцов.
Синтаксис:
SELECT expr> EXCEPT ( col_name1 [, col_name2, col_name3, ...] ) FROM [db.]table_name Пример:
SELECT * EXCEPT (i) from columns_transformers; ┌──j─┬───k─┐ │ 10 │ 324 │ │ 8 │ 23 │ └────┴─────┘ REPLACE
Определяет одно или несколько выражений алиасов. Каждый алиас должен соответствовать имени столбца из запроса SELECT * . В списке столбцов результата запроса имя столбца, соответствующее алиасу, заменяется выражением в модификаторе REPLACE .
Этот модификатор не изменяет имена или порядок столбцов. Однако он может изменить значение и тип значения.
Синтаксис:
SELECT expr> REPLACE( expr> AS col_name) from [db.]table_name Пример:
SELECT * REPLACE(i + 1 AS i) from columns_transformers; ┌───i─┬──j─┬───k─┐ │ 101 │ 10 │ 324 │ │ 121 │ 8 │ 23 │ └─────┴────┴─────┘ Комбинации модификаторов
Вы можете использовать каждый модификатор отдельно или комбинировать их.
Примеры:
Использование одного и того же модификатора несколько раз.
SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) from columns_transformers; ┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐ │ 2 │ 3 │ └──────────────────────────┴──────────────────────────┘ Использование нескольких модификаторов в одном запросе.
SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers; ┌─sum(plus(i, 1))─┬─sum(k)─┐ │ 222 │ 347 │ └─────────────────┴────────┘ SETTINGS в запросе SELECT
Вы можете задать значения необходимых настроек непосредственно в запросе SELECT в секции SETTINGS . Эти настройки действуют только в рамках данного запроса, а после его выполнения сбрасываются до предыдущего значения или значения по умолчанию.
Другие способы задания настроек описаны здесь.
Пример
SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1; Как выводить в запросе все столбцы кроме одного, не перечисляя их?
Другими словами, как исключить столбец с известным именем из результата запроса select * from Table?
Перечислять столбцы также приходится в операторе INSERT, исключая из списка автоинкрементируемые столбцы или столбцы, которым нужно присвоить значение по умолчанию.
Допустим, требуется выбрать все столбцы из таблицы Laptop кроме столбца code. Чтобы вывести все столбцы таблицы, достаточно написать
SELECT * FROM Laptop;
Но чтобы исключить из списка столбец code, мы вынуждены перечислить все остальные столбцы:
SELECT model, speed, ram, hd, price, screen FROM Laptop;
Было бы неплохо, если мы могли написать что-то типа
SELECT *[^code] FROM Laptop;
Подобная возможность позволила бы нам избежать рутинной работы с вероятностью ошибиться при наборе и пригодилась бы при динамическом формировании запроса, когда заранее не известна ни таблица, ни число столбцов в ней.
Но, увы, такой возможности у нас нет. Зато мы можем сформировать список столбцов с помощью вспомогательного скрипта, чтобы в дальнейшем использовать его результаты в запросах. Это мы можем сделать, используя стандартное представление метаданных, которое называется информационной схемой:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code');
Предикат NOT IN мы используем для того, чтобы можно было в перспективе исключать не один, а несколько столбцов. Теперь нам нужно получить результат в виде строки символов, представляющей собой разделенный запятыми перечень столбцов. Для этого нам уже придется использовать нестандартные средства.
SQL Server
В частности, для SQL Server мы можем это сделать при помощи конструкции FOR XML PATH, заменив попутно с помощью функции REPLACE пробел между именами столбцов на запятые:
SELECT REPLACE( (SELECT COLUMN_NAME AS 'data()' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION FOR XML PATH('')) ,' ',', ');
В принципе, мы в состоянии сформировать требуемый оператор целиком с тем, чтобы его можно было динамически использовать в коде приложения:
SELECT 'SELECT ' +REPLACE( (SELECT COLUMN_NAME AS 'data()' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION FOR XML PATH('')) ,' ',', ') + ' FROM Laptop';
Функция string_agg, появившаяся в SQL Server 2017, позволяет упростить логику запроса, заменив преобразование к XML:
SELECT 'SELECT ' + (SELECT string_agg(COLUMN_NAME,', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ) + ' FROM Laptop';
MySQL
В случае MySQL мы можем воспользоваться специальной агрегатной функцией GROUP_CONCAT:
SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'computers' AND TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION;
Следует отметить, что в MySQL информационная схема относится не к отдельной базе данных, а ко всему серверу. Поэтому на тот случай, если в разных базах сервера имеются таблицы с одинаковыми именами, в условия отбора нужно добавить предикат с указанием схемы (базы данных): TABLE_SCHEMA=’computers’.
Конкатенация в MySQL выполняется с помощью функции CONCAT. В итоге окончательное решение нашей задачи можно написать так:
SELECT CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='computers' AND TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION ), ' FROM Laptop');
PostgreSQL
В PostgreSQL задачу представления значений столбца в виде текстового списка можно решить при помощи двух встроенных функций: ARRAY и ARRAY_TO_STRING. Первая из них преобразует выборку в массив значений, а вторая преобразует массив в список. При этом вторым параметром функции ARRAY_TO_STRING задается символ, который будет являться разделителем элементов списка. Теперь решение нашей задачи можно записать в виде:
SELECT 'SELECT ' || ARRAY_TO_STRING(ARRAY(SELECT COLUMN_NAME::VARCHAR(50) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION ), ', ') || ' FROM Laptop';
Заметим, что для конкатенации строк в PostgreSQL используется стандартный оператор «||«, и отметим необходимость явного приведения типа данных столбца COLUMN_NAME (information_schema.sql_identifier) к типу CHAR/VARCHAR.
Oracle
Oracle не поддерживает стандартный формат представления метаданных. Поэтому решение использует обращение к системной таблице user_tab_columns:
SELECT 'SELECT ' || (SELECT LISTAGG(column_name,', ') WITHIN GROUP (ORDER BY column_id) FROM user_tab_columns WHERE table_name='LAPTOP' AND column_name NOT IN ('CODE') ) || ' FROM Laptop' FROM dual;
Выборка SQL все кроме
Mad Dog
Вскочил вот такой вопрос. Можно ли на SQL, конкретно на базе MySQL выбрать поля из таблицы по типу «все кроме».
Полей много и влом их писать, а потом при изменении структуры запросы переписывать.
Дополнено (1). А NOT IN разве не со значениями работает?
Дополнено (2). Может немного не понятен вопрос был. Мне нужно работать не со значениями полей, а именно с самими полями
Например у меня в таблице есть поля:
field1, field2, field3, field4, field5, field6, field7, field8, field9, field10.
Так вот мне нужно выбрать запросом все поля кроме field9
В простейшем случае это будет так
SELECT field1, field2, field3, field4, field5, field6, field7, field8, field10. FROM table
Так вот. Можно ли избежать такой портянки?
Kuper
NOT IN вроде бы
Terran
Выберет все продукты, кроме тех, у которых ид больше 1000
select id from products where id not in (select id from products where id>1000)
Гость
лучший ответ Нет, нету…
* — выборку всех полей делать не решение…
по другому, никак, точнее как Вы уже это делаете, отдельно указываем…
Японский Городовой
Так и думал, что большая часть местных дурачков вопрос не поймет.
Гость
Ну если бы их было 300 или 500, можно было возится, а так смысла нет. Алгоритм примерно такой:
• ненужное поле именуем как-нибудь иначе, ну например comments? А все нужные поля fldID, fldName, fldPhone и так далее То есть С неким префиксом fld;
• делаем запрос к СИСТЕМНОЙ таблице, содержащей список полей нужной Вам таблицы. Я не знаю как в MySQl организовано, но наверняка системная таблица содержащая поля пользовательской — должна быть. Если нету — метод не пойдет. Так вот — делаем по ней запрос (like fld%) и получаем список НУЖНЫХ полей;
• передаем результаты запроса в основной select (результат запроса по системной таблице) from НужнаяТаблица
Метод бредовый. Тем более что мастера запросов выведут Вам нужные поля гораздо проще. Это первый метод.
Второй: передать результаты полного запроса по таблице в некую промежуточную, в которой наименования полей, сделаны проще, например: f1, f2, f3, f4 и так далее Ну и по ней сделать нужный запрос. Метод такой же бредовый, как и первый. Так как Все это ляжет на производительность и прочее, прочее, прочее.
Наверняка Вы запросы не ежесекундно делаете, воспользуйтесь мастерами, в конце концов, сделайте некий сниппет (короткий кусок кода, содержащий нужные поля), в блокнот, что ли скопируйте их. Ну не знаю…
Гость
05.10.2019
Добавить доп.ячейку visible, куда передавать параметр 1 или 0. Тогда запрос будет такой SELECT * FROM table WHERE visible = ‘1’; А у поля field9 установить visible = 0.
Гость
22.03.2020
бред… Люди вообще вопрос читают? Сам ищу это решение. Но пока увы.
- Mysql выборка все кроме
- MySQL. Как вытащить ВСЕ поля, КРОМЕ одного? (см.)
- Как в MySQL выбрать из таблицы все колонки кроме нескольких определённых? Или как выбрать только числовые поля?
- Как из таблицы MySQL удалить все строки кроме первых 3-ех?
- MySQL] Возможно ли одним запросом вытащить ВСЕ поля, КРОМЕ одного