{"draftJsState":{"blocks":[{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"egu0t"},{"data":{"image":{"id":"62e3d3235492c906eefe2932"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"2da2h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Основные команды SQL не ограничиваются стандартными CREATE, UPDATE и DELETE. Данная статья будет полезна тем, кто хочет освежить свои знания по SQL.","type":"unstyled","key":"fsvlo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Обратите внимание, что в примерах используется MySQL, поэтому в конце каждого оператора ставится точка с запятой.","type":"unstyled","key":"a5i18"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Примечание: вы читаете улучшенную версию некогда выпущенной нами статьи","type":"blockquote","key":"c4bf4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настройка базы данных","type":"header-three","key":"2dg2s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Перед началом создайте БД с тестовыми данными. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После установите MySQL, откройте терминал и войдите в консоль MySQL с помощью команды:","type":"unstyled","key":"4g35a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"mysql -u root -p","type":"unstyled","key":"bgqtq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Затем введите пароль и выполните следующую команду. Назовём базу данных «university»:","type":"unstyled","key":"fhpd2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CREATE DATABASE university;\nUSE university;\nSOURCE ;\nSOURCE ;","type":"unstyled","key":"d04d5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SHOW DATABASES","type":"header-three","key":"1a5rg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команда, которая отвечает за просмотр доступных баз данных.","type":"unstyled","key":"4tc50"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"header-three","key":"7tus8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CREATE DATABASE","type":"header-three","key":"bu2ha"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Команда для создания новой базы данных.","type":"unstyled","key":"6o5si"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"USE","type":"header-three","key":"btd57"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"С помощью этой SQL-команды USE выбирается база данных, необходимая для дальнейшей работы с ней.","type":"unstyled","key":"c6s2a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SOURCE","type":"header-three","key":"fio2v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"А SOURCE позволит выполнить сразу несколько SQL-команд, содержащихся в файле с расширением .sql.","type":"unstyled","key":"6v438"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DROP DATABASE","type":"header-three","key":"f9nrm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Стандартная SQL-команда для удаления целой базы данных.","type":"unstyled","key":"8kc4p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SHOW TABLES","type":"header-three","key":"ck2vb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"С помощью этой несложной команды можно увидеть все таблицы, которые доступны в базе данных.","type":"unstyled","key":"2a7oi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CREATE TABLE","type":"header-three","key":"7pbvt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команда для создания новой таблицы:","type":"unstyled","key":"e0sip"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CREATE TABLE (\n ,\n ,\n \n PRIMARY KEY(),\n FOREIGN KEY() REFERENCES ()\n);","type":"unstyled","key":"57s75"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"header-three","key":"925d2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Ограничения целостности при использовании CREATE TABLE","type":"header-three","key":"2989n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:","type":"unstyled","key":"2i2pi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ячейка таблицы не может иметь значение NULL;","type":"unordered-list-item","key":"5oifm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"первичный ключ — PRIMARY KEY(col_name1, col_name2, …);","type":"unordered-list-item","key":"fdf21"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"внешний ключ — FOREIGN KEY(col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn).","type":"unordered-list-item","key":"b1f4u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.","type":"unstyled","key":"7f8qc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"66m9b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Создайте таблицу «instructor»:","type":"unstyled","key":"8hqo8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CREATE TABLE instructor (\n ID CHAR(5),\n name VARCHAR(20) NOT NULL,\n dept_name VARCHAR(20),\n salary NUMERIC(8,2),\n PRIMARY KEY (ID),\n FOREIGN KEY (dept_name) REFERENCES department(dept_name)\n);","type":"unstyled","key":"2c02g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DESCRIBE","type":"header-three","key":"9cleq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"С помощью DESCRIBE можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы.","type":"unstyled","key":"9hdbl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INSERT","type":"header-three","key":"353gr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Команда INSERT INTO в SQL отвечает за добавление данных в таблицу:","type":"unstyled","key":"10s8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INSERT INTO (, , , …)\n VALUES (, , , …);","type":"unstyled","key":"1eu81"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.","type":"unstyled","key":"1que0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"INSERT INTO \n VALUES (, , , …);","type":"unstyled","key":"6siel"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UPDATE","type":"header-three","key":"3726g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команда для обновления данных таблицы:","type":"unstyled","key":"efaeh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"UPDATE \n SET = , = , ...\n WHERE ;","type":"unstyled","key":"3sbse"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DELETE","type":"header-three","key":"3eulj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команда DELETE FROM используется для удаления данных из таблицы.","type":"unstyled","key":"aebbb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DROP TABLE","type":"header-three","key":"aspro"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"А так можно удалить всю таблицу целиком.","type":"unstyled","key":"5hbia"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT","type":"header-three","key":"8303m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Далее мы рассмотрим основные команды SQL, которые позволяют работать непосредственно с данными. К одной из таких SQL-команд относится SELECT для получения данных из выбранной таблицы:","type":"unstyled","key":"fj91f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , …\n FROM ;","type":"unstyled","key":"9ps9m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Следующей командой можно вывести все данные из таблицы:","type":"unstyled","key":"6ga79"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT * FROM ;","type":"unstyled","key":"bavl4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT DISTINCT","type":"header-three","key":"ef227"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.","type":"unstyled","key":"70tes"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT DISTINCT , , …\n FROM ;","type":"unstyled","key":"f9sbc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WHERE","type":"header-three","key":"a74um"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:","type":"unstyled","key":"d0r5o"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , …\n FROM \n WHERE ;","type":"unstyled","key":"5vhm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В запросе можно задавать следующие условия:","type":"unstyled","key":"6oqbo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"сравнение текста;","type":"unordered-list-item","key":"e2e4b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"сравнение численных значений;","type":"unordered-list-item","key":"536nm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"логические операции AND (и), OR (или) и NOT (отрицание).","type":"unordered-list-item","key":"dta1e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"eapqo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE:","type":"unstyled","key":"bf3k8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT * FROM course WHERE dept_name=’Comp. Sci.’;\nSELECT * FROM course WHERE credits>3;\nSELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3;","type":"unstyled","key":"1itsu"},{"data":{"image":{"id":"62e3d3232a2ced5598897bb9"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команды: пример вывода с WHERE","type":"atomic:image","key":"112bg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"GROUP BY","type":"header-three","key":"10bae"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT, MAX, MIN, SUM и AVG, для группировки выходных значений.","type":"unstyled","key":"900qk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , …\n FROM \n GROUP BY ;","type":"unstyled","key":"tanp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"7qji7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведем количество курсов для каждого факультета:","type":"unstyled","key":"7nc8k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT COUNT(course_id), dept_name\n FROM course\n GROUP BY dept_name;","type":"unstyled","key":"2cgpa"},{"data":{"image":{"id":"62e3d323b26655565f0f0348"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команды: пример вывода с GROUP BY","type":"atomic:image","key":"47uv7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"HAVING","type":"header-three","key":"78p9f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Ключевое слово HAVING было добавлено в SQL по той причине, что WHERE не может использоваться для работы с агрегатными функциями.","type":"unstyled","key":"5fv1r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , ...\n FROM \n GROUP BY \n HAVING ","type":"unstyled","key":"84dfv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"85gdm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведем список факультетов, у которых более одного курса:","type":"unstyled","key":"coihl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT COUNT(course_id), dept_name\n FROM course\n GROUP BY dept_name\n HAVING COUNT(course_id)>1;","type":"unstyled","key":"86kj"},{"data":{"image":{"id":"62e3d323a0c6cf0f0048bb38"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команды: пример вывода с HAVING","type":"atomic:image","key":"9ndgu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ORDER BY","type":"header-three","key":"6t2mg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC.","type":"unstyled","key":"7hpol"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , …\n FROM \n ORDER BY , , … ASC|DESC;","type":"unstyled","key":"3b4th"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"1g5fc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведем список курсов по возрастанию и убыванию количества кредитов:","type":"unstyled","key":"b3aud"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT * FROM course ORDER BY credits;\nSELECT * FROM course ORDER BY credits DESC;","type":"unstyled","key":"59oqv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"BETWEEN","type":"header-three","key":"32t3l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.","type":"unstyled","key":"75da2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , …\n FROM \n WHERE BETWEEN AND ;","type":"unstyled","key":"d6dpk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"2c7e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:","type":"unstyled","key":"1u6s3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT * FROM instructor\n WHERE salary BETWEEN 50000 AND 100000;","type":"unstyled","key":"d0vgu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LIKE","type":"header-three","key":"3qc28"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оператор LIKE используется в WHERE, чтобы задать шаблон поиска похожего значения.","type":"unstyled","key":"b4ffc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Есть два свободных оператора, которые используются в LIKE:","type":"unstyled","key":"1k9qi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"% (ни одного, один или несколько символов);","type":"unordered-list-item","key":"38u59"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"_ (один символ).","type":"unordered-list-item","key":"9cemf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , …\n FROM \n WHERE LIKE ;","type":"unstyled","key":"20vvl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"5bgr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведем список курсов, в имени которых содержится «to», и список курсов, название которых начинается с «CS-»:","type":"unstyled","key":"bhfhs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT * FROM course WHERE title LIKE ‘%to%’;\nSELECT * FROM course WHERE course_id LIKE 'CS-___';","type":"unstyled","key":"kj81"},{"data":{"image":{"id":"62e3d3235492c906eefe2933"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команды: пример вывода с LIKE","type":"atomic:image","key":"8okle"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"IN","type":"header-three","key":"2btpk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"С помощью IN можно указать несколько значений для оператора WHERE:","type":"unstyled","key":"qp0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , …\n FROM \n WHERE IN (, , …);","type":"unstyled","key":"4kfrr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"3g2s3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:","type":"unstyled","key":"4dvi7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT * FROM student\n WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);","type":"unstyled","key":"19s1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN","type":"header-three","key":"7uhn7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:","type":"unstyled","key":"6b7hl"},{"data":{"image":{"id":"62e3d323094781424e62a53c"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команды: схема использования JOIN","type":"atomic:image","key":"34lb1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT , , …\n FROM \n JOIN \n ON = ;","type":"unstyled","key":"ef29j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"cumgt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Выведем список всех обязательных курсов и детали о них:","type":"unstyled","key":"cgvnk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT prereq.course_id, title, dept_name, credits, prereq_id\n FROM prereq\n LEFT OUTER JOIN course\n ON prereq.course_id=course.course_id;","type":"unstyled","key":"e3d08"},{"data":{"image":{"id":"62e3d3243bcfb952658df954"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SQL-команды: пример вывода с JOIN","type":"atomic:image","key":"9sk91"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"VIEW","type":"header-three","key":"409uf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"VIEW — это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. VIEW всегда показывает самую свежую информацию из базы данных.","type":"unstyled","key":"40eg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Создание","type":"header-three","key":"fqqtb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"CREATE VIEW AS\n SELECT , , …\n FROM \n WHERE ;","type":"unstyled","key":"aels1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Удаление","type":"header-three","key":"elq5b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DROP VIEW ;","type":"unstyled","key":"ffsnt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Агрегатные функции","type":"header-three","key":"1v96o"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Это не совсем основные команды SQL, однако знать их тоже желательно. Агрегатные функции используются для получения совокупного результата, относящегося к рассматриваемым данным:","type":"unstyled","key":"e7eo3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"COUNT(col_name) — возвращает количество строк;","type":"unordered-list-item","key":"6p0s2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SUM(col_name) — возвращает сумму значений в данном столбце;","type":"unordered-list-item","key":"3e7is"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"AVG(col_name) — возвращает среднее значение данного столбца;","type":"unordered-list-item","key":"eo163"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"MIN(col_name) — возвращает наименьшее значение данного столбца;","type":"unordered-list-item","key":"bte9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"MAX(col_name) — возвращает наибольшее значение данного столбца.","type":"unordered-list-item","key":"em6i2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Вложенные подзапросы","type":"header-three","key":"4sr7h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT, FROM и WHERE, вложенные в другой запрос.","type":"unstyled","key":"988i6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример","type":"header-three","key":"58trj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:","type":"unstyled","key":"4neu7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT DISTINCT course_id\n FROM section\n WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (\n SELECT course_id\n FROM section\n WHERE semester = ‘Spring’ AND year= 2010\n );","type":"unstyled","key":"fg7me"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"marker","key":"ZSFaP6Oro5JqBbdHcjmp"}],"entityMap":{}},"mentions":[]}