Основные команды SQL, которые должен знать каждый программист
Рассмотрим основные команды SQL, включая агрегатные функции и вложенные подзапросы, на примере работы с MySQL.
Основные команды SQL не ограничиваются стандартными CREATE , UPDATE и DELETE . Данная статья будет полезна тем, кто хочет освежить свои знания по SQL перед собеседованием на работу.
Обратите внимание, что в примерах используется MySQL, поэтому в конце каждого оператора ставится точка с запятой.
Примечание Вы читаете улучшенную версию некогда выпущенной нами статьи.
Настройка базы данных
Перед началом создайте БД с тестовыми данными. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После установите MySQL, откройте терминал и войдите в консоль MySQL с помощью команды:
mysql -u root -p Затем введите пароль и выполните следующую команду. Назовём базу данных «university»:
CREATE DATABASE university; USE university; SOURCE ; SOURCE ; SHOW DATABASES
SQL-команда, которая отвечает за просмотр доступных баз данных.
CREATE DATABASE
Команда для создания новой базы данных.
USE
С помощью этой SQL-команды USE выбирается база данных, необходимая для дальнейшей работы с ней.
SOURCE
А SOURCE позволит выполнить сразу несколько SQL-команд, содержащихся в файле с расширением .sql.
DROP DATABASE
Стандартная SQL-команда для удаления целой базы данных.
SHOW TABLES
С помощью этой несложной команды можно увидеть все таблицы, которые доступны в базе данных.
CREATE TABLE
SQL-команда для создания новой таблицы:
CREATE TABLE ( , , PRIMARY KEY(), FOREIGN KEY() REFERENCES () ); Ограничения целостности при использовании CREATE TABLE
Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:
- ячейка таблицы не может иметь значение NULL ;
- первичный ключ — PRIMARY KEY(col_name1, col_name2, …) ;
- внешний ключ — FOREIGN KEY(col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .
Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.
Пример
Создайте таблицу «instructor»:
CREATE TABLE instructor ( ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name) ); DESCRIBE
С помощью DESCRIBE можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы.
INSERT
Команда INSERT INTO в SQL отвечает за добавление данных в таблицу:
INSERT INTO (, , , …) VALUES (, , , …); При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.
INSERT INTO VALUES (, , , …); UPDATE
SQL-команда для обновления данных таблицы:
UPDATE SET = , = , . WHERE ; DELETE
SQL-команда DELETE FROM используется для удаления данных из таблицы.
DROP TABLE
А так можно удалить всю таблицу целиком.
SELECT
Далее мы рассмотрим основные команды SQL, которые позволяют работать непосредственно с данными. К одной из таких SQL-команд относится SELECT для получения данных из выбранной таблицы:
SELECT , , … FROM ; Следующей командой можно вывести все данные из таблицы:
SELECT * FROM ; SELECT DISTINCT
В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.
SELECT DISTINCT , , … FROM ; WHERE
Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:
SELECT , , … FROM WHERE ; В запросе можно задавать следующие условия:
- сравнение текста;
- сравнение численных значений;
- логические операции AND (и), OR (или) и NOT (отрицание).
Пример
Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE :
SELECT * FROM course WHERE dept_name=’Comp. Sci.’; SELECT * FROM course WHERE credits>3; SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3; SQL-команды: пример вывода с WHERE
GROUP BY
Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.
SELECT , , … FROM GROUP BY ; Пример
Выведем количество курсов для каждого факультета:
SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name; SQL-команды: пример вывода с GROUP BY
HAVING
Ключевое слово HAVING было добавлено в SQL по той причине, что WHERE не может использоваться для работы с агрегатными функциями.
SELECT , , . FROM GROUP BY HAVING Пример
Выведем список факультетов, у которых более одного курса:
SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1; SQL-команды: пример вывода с HAVING
ORDER BY
ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .
SELECT , , … FROM ORDER BY , , … ASC|DESC; Пример
Выведем список курсов по возрастанию и убыванию количества кредитов:
SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC; BETWEEN
BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.
SELECT , , … FROM WHERE BETWEEN AND ; Пример
Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:
SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000; LIKE
Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.
Есть два свободных оператора, которые используются в LIKE :
- % (ни одного, один или несколько символов);
- _ (один символ).
SELECT , , … FROM WHERE LIKE ; Пример
Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-» :
SELECT * FROM course WHERE title LIKE ‘%to%’; SELECT * FROM course WHERE course_id LIKE 'CS-___'; SQL-команды: пример вывода с LIKE
IN
С помощью IN можно указать несколько значений для оператора WHERE :
SELECT , , … FROM WHERE IN (, , …); Пример
Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:
SELECT * FROM student WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’); JOIN
JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:
SQL-команды: схема использования JOIN
SELECT , , … FROM JOIN ON = ; Пример
Выведем список всех обязательных курсов и детали о них:
SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id; SQL-команды: пример вывода с JOIN
VIEW
VIEW — это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. VIEW всегда показывает самую свежую информацию из базы данных.
Создание
CREATE VIEW AS SELECT , , … FROM WHERE ; Удаление
DROP VIEW ; Агрегатные функции
Это не совсем основные команды SQL, однако знать их тоже желательно. Агрегатные функции используются для получения совокупного результата, относящегося к рассматриваемым данным:
- COUNT(col_name) — возвращает количество строк;
- SUM(col_name) — возвращает сумму значений в данном столбце;
- AVG(col_name) — возвращает среднее значение данного столбца;
- MIN(col_name) — возвращает наименьшее значение данного столбца;
- MAX(col_name) — возвращает наибольшее значение данного столбца.
Вложенные подзапросы
Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос.
Пример
Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:
SELECT DISTINCT course_id FROM section WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN ( SELECT course_id FROM section WHERE semester = ‘Spring’ AND year= 2010 ); Как посмотреть таблицу в sql
Настало время перейти от теории к практике. Для дальнейшего изучения SQL Server создадим маленькую БД. Для создания БД Вам необходимо скачать с проект для шага с файлом SQLByStep.sql.
- найти свободное пространство на диске — БД требует примерно 2 МБайта
- создать каталог для размещения БД — по умолчанию используется C:\SQLByStep\data, если желаете выбрать другой путь — отредактируйте файл ( в команде CREATE DATABASE исправьте пути к файлам данных и лога)
- запустить Query Analyzer
- выберите меню File->Open и загрузите файл SQLByStep.sql
- выполните его (нажмите F5)
| SNUM | уникальный код, назначенный каждому торговому агенту |
| SNAME | имя торгового агента |
| CITY | расположение агента (город) |
| COMM | комиссионные продавца |
Таблица Customers (Заказчики)
| Поле | Содержание |
|---|---|
| CNUM | уникальный номер, назначенный каждому заказчику |
| CNAME | имя заказчика |
| CITY | расположение заказчика (город) |
| RATING | код, указывающий уровень предпочтения данного заказчика перед другими, более высокий уровень указывает на большее предпочтение |
| SNUM | код агента, назначенного этому заказчику (из таблицы Salespeople |
Таблица Orders (Заказы):
| Поле | Содержание |
|---|---|
| ONUM | уникальный номер каждого заказа |
| AMT | сумма заказа (в условных единицах конечно же 🙂 |
| ODATE | дата заказа |
| CNUM | номер заказчика, делающего заказ (из таблицы Customers) |
| SNUM | номер агента, принявшего заказ (из таблицы Salespeople) |
Теперь можно продемонстрировать как с помощью Transact-SQL извлекать информацию из таблиц. Все запросы выполняются одной командой SELECT. В самой простой форме команда SELECT просто инструктирует БД, чтобы извлесь информацию из таблицы. Например, чтобы вывести содержимое таблицы Агентов (Salespeople) надо ввести следующее:
SELECT snum, sname, city, comm FROM Salespeople
Эта команда просто выводит все данные из таблицы. Вывод для этого запроса приведен ниже:
snum sname city comm ----- -------- ----------- ----- 1001 Иванов Москва 12 1002 Петров Хабаровск 13 1003 Егоров Караганда 10 1004 Сидоров Сочи 11 1007 Шилин Красноярск 15
- SELECT — ключевое слово
- snum, sname — список столбцов из таблицы, которые выбираются запросом. Любые другие столбцы, не указанные в этом списке игнорируются.
- FROM — ключевое слово, после него перечисляются таблицы — источник данных
Если вы хотите видеть все столбцы таблицы используйте специальное сокращение:
SELECT * FROM Salespeople
Выведена информация будет также как и в преыдущем случае.
В общем случае команда SELECT начинается с ключевого слова SELECT, за ним идет список имен столбцов, которые Вы хотите видеть, или *(звездочка) если Вы хотите видеть все столбцы. За тем идет ключевое слово FROM, далее имя таблицы, к которой делается запрос.
Что будет дальше? Дальше предлагается углубится в команду SELECT: сортировка данных, сложные логические выражения и специальные операторы.
Просмотр и редактирование таблиц SQL Server в графическом режиме
Иногда бывает необходимо произвести некоторые элементарные действия с базой данных, например найти некое значение и\или изменить его. Для тех, кто постоянно работает с базами и владеет языком запросов, эта задача не составит труда, но если вы видите SQL Server в первый раз, то проще всего просмотреть и отредактировать данные в графическом режиме.
Для этого надо открыть SQL Server Management Studio, найти в разделе «Databases» нужную базу и раскрыть ее. Затем в разделе «Tables» выбрать таблицу и правой клавишей мыши вызвать контекстное меню. В этом меню есть два пункта — «Select Top 1000 Rows» и «Edit Top 200 Rows».
Select Top 1000 Rows, как следует из названия, выводит первые 1000 строк таблицы
а Edit Top 200 Rows открывает для редактирования первые 200 строк таблицы. Это очень удобно, так как таблицу можно быстро пролистать, найти требуемую информацию и изменить ее.
При необходимости дефолтные значения 200\1000 можно изменить. Для этого надо открыть меню «Tools», перейти к пункту «Options»
открыть вкладку «SQL Server Object Explorer» и в разделе «Table and View Options» установить необходимые значения. А если поставить 0, то будет выводиться все содержимое базы без ограничений.
Все вышеописаное применимо ко всем более-менее актуальным версиям, начиная с SQL Server 2008 и заканчивая SQL Server 2016.
Как получить список всех таблиц в базе данных Microsoft SQL Server?
Если у Вас встала задача определить количество таблиц в базе данных Microsoft SQL Server, то данная статья Вам поможет, так как в ней мы рассмотрим несколько способов реализации того, как можно получить список всех пользовательских таблиц, включая некоторые их характеристики с помощью SQL запроса.
Всю информацию о таблицах и других объектах SQL сервера можно посмотреть в графической среде SQL Server Management Studio, но иногда требуется выгрузить данную информацию или просто получить к ней доступ, для того чтобы использовать ее, например, в своих SQL инструкциях, это можно сделать несколькими способами, и сейчас мы их рассмотрим.
Получаем список всех таблиц с помощью представления информационной схемы
В Microsoft SQL Server есть специальная схема, предназначенная для получения информации о метаданных — это INFORMATION_SCHEMA. Подробно о ней мы говорили в материале – «Представления информационной схемы Microsoft SQL Server».
Для того чтобы получить информацию о таблицах в БД, существует представление информационной схемы TABLES. Допустим, нам нужно получить просто перечень таблиц, для этого пишем следующий SQL запрос (в моей тестовой базе всего одна таблица).
SELECT TABLE_NAME AS [Название таблицы] FROM INFORMATION_SCHEMA.TABLES WHERE table_type='BASE TABLE'
В данном случае мы указали условие table_type=’BASE TABLE’, так как данное представление содержит еще и информацию о представлениях (VIEW).
Недостатком данного способа является то, что никакой больше полезной информации в представлении INFORMATION_SCHEMA.TABLES нет.
Получаем список всех таблиц с помощью системного представления sys.tables
Альтернативным способом и более удачным, если Вам нужна дополнительная информация о таблицах, является способ с использованием системного представления sys.tables. Давайте сейчас с помощью этого представления выведем список таблиц, а также получим дополнительные сведения о дате создания таблицы и дате последнего редактирования этой таблицы.
SELECT name AS [Название таблицы], create_date AS [Дата создания], modify_date AS [Дата редактирования] FROM sys.tables
Получаем список всех таблиц с помощью системной процедуры
Еще одним способом получения списка таблиц в базе данных Microsoft SQL Server является использование системной процедуры sp_Tables, но, на мой взгляд, данный способ менее удобный, к тому же он возвращает также немного сведений, по сути, только название таблиц.
Для того чтобы получить сведения о таблицах в БД нам нужно указать параметр @table_type со значением ‘TABLE’, так как данная процедура возвращает еще данные о представлениях и системных таблицах. Следует обратить внимание на то, что значение параметра нужно заключать в двойные кавычки, а каждое значение типа в одиночные кавычки, так как через запятую возможно указывать несколько типов (например, для того чтобы получить таблицы и представления, в значение параметра нужно указать — «‘TABLE’, ‘VIEW’»). Также мы укажем параметр @table_owner, для того чтобы ограничиться одним владельцем.
EXEC sp_Tables @table_owner = 'dbo', @table_type = "'TABLE'";
Вот мы с Вами и рассмотрели три возможности получения списка таблиц в Microsoft SQL Server. Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL.
У меня все, пока!
Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.