Creating tables in SQL
There are many ways to insert data into a database. Most are automated and exists through the creation of an app’s user data. I can’t speak to these processes. However, I am very interested in learning about these!
In this blog, I want to show two manual ways of creating a SQL table and adding data to that table. So, there are two goals of this post:
- Goal: learn how to insert data into a table for SQL toy examples, and
- Goal: learn how to create a SQL table from a csv file.
Again, I am going to use data from astronomical objects as examples. Let’s begin.
First, let’s cover creating a table of data for a toy example. Suppose you want to recreate the black_holes table I have used in many of my SQL posts, one of which is Classifying black holes with SQL. Let’s use the same data, which I have provided again, here.
Now create a table with the data columns specified. Here, the columns are:
- name — the name associated with the black hole (a record); this is recorded as TEXT data type.
- base_mass and power — the mass of the record, each recorded as an INT (integer). To understand this, use scientific notation: base_mass x 10^ power . The unit of mass for these black holes is in solar masses, or “the mass of the Sun”. For example, the Sombrero galaxy supermassive black hole is 1×10⁹ times the mass of the Sun.
And, we will include one more column:
- num — the unique number of the record, which is recorded as an INT .
To create a table in the database, we need to create a table object with the CREATE TABLE statement. There is a simple syntax for creating a SQL Server table —
where data_type is what kind of data that is in the column (for example, INT , TEXT , CHAR , etc.). The [NULL|NOT NULL] option specifies if the column needs a value when a record is inserted.
- If a column is defined to be NOT NULL then we cannot create a record without an actual value in the column.
- If a column is defined to be NULL then we can create a row without an actual value in the column.
Here is an example.
Finally, we can insert the data for the four records into the table with the INSERT INTO statement.
Now, let’s cover creating a data table from a csv file. For this example, I will use statistics for near-Earth asteroids and comets discovered by NASA’s WISE mission. A csv file for this data can be downloaded here. I will also be using a flavor of SQL — PostgreSQL — for the statements.
When creating a SQL table from a csv file, we need to know the order of columns and type of data in the columns. This can be found in a data dictionary, but sometimes you need to contact the subject matter experts of the data within the company to discover this information. Luckily, we have all the information we need listed on the link referenced above for the data download. Again, we use the CREATE TABLE statement with column names, data types, and the [NULL|NOT NULL] features. The code follows.
Once you have the table created, inserting the data is easy using the COPY statement. COPY moves data between tables and standard files; where COPY FROM copies data from a file to a table.
Note that DELIMITER specifies the character that separates columns within each row of the file and HEADER specifies that the file contains a header line with the names of each column in the file.
There you have it, folks! Two more tools for your SQL toolbox. Have questions? Comments? Let’s discuss this in the chat!
For your reference, I have a series of tutorials that cover basic and intermediate SQL topics. Take a look!
CREATE TABLE в SQL
Таблица используется для хранения записей (данных). Для создания таблицы в базе данных используется оператор CREATE TABLE. Например:
Здесь мы создаем таблицу с именем Companies. Таблица содержит столбцы (поля) id, name, address, email и phone.
Типы int , varchar(50) и text указывают, какие данные могут храниться в этих полях.
| Тип данных | Описание | Пример |
| int | Содержит целые числа. | 500, -200. |
| varchar(x) | Содержит символьные переменные с максимальной длиной x. | Tom Yam, System Update for Windows. |
| text | Содержит текст до 65 535 символов. | A computer program is a set of instructions that the computer can perform in order to perform some task. |
Примечание: Мы должны явно указывать типы данных для каждого столбца при создании таблицы.
CREATE TABLE IF NOT EXISTS
Оператор CREATE TABLE IF NOT EXISTS используется для создания таблицы, если такой (с указанным именем) не существует на момент выполнения команды. Например:
Как изучить SQL за ночь или шпаргалка для системного аналитика
Помните, как вы были студентами, и готовились к экзаменам по ночам?
Предлагаю вашему вниманию простую шпаргалку по SQL с теорией и практикой, которой вы сможете воспользоваться в любое время.
Статья пригодится:
ИТ специалисту, которому необходимо быстро освоить минимальный уровень SQL для выполнения рабочих задач,
Системному аналитику, которому требуется освежить знания перед собеседованием или научиться, если раньше не было задач с SQL.
В статье есть:
Минимум теории для задач на работе или собеседовании (прим. операторов в SQL больше, но в своей работе использовала чаще всего эти);
Практические задания, которые можно выполнить у себя на ПК бесплатно, предварительно установив сервер баз данных.
SQL теория
SQL (structured query language) — язык структурированных запросов, который позволяет работать с данными (найти, изменить, удалить или создать) в реляционной базе данных (БД).
Реляционные БД — это базы, где связанная информация, представленная в виде двумерных таблиц (например, Postgres, Mysql, Oracle и др.).
СУБД — система управления БД, программа с помощью которой можно создавать, наполнять и просматривать БД .
ER диаграммы (Entity-Relationship model) — показывает структуру и связи таблиц в БД. Помогает в написании SQL запросов.
Для работы мозга студенту нужна энергия. Проще всего ее получить из сладкого. Значит будем учиться на примере базы данных сладостей. Изучать теорию мы с вами будем на реальном примере.
Наша БД состоит из таблиц:
ER диаграмма базы данных сладостей
(прим. показана часть БД с необходимыми таблицами для выполнения практических заданий)
Создание таблиц. Оператор CREATE TABLE . Примеры
Перед изучением данной темы рекомендуется ознакомиться с темой:
Содержание
- 1. Оператор CREATE TABLE . Создание таблицы. Общая форма
- 2. Примеры создания таблиц
- 2.1. Пример создания простейшей таблицы учета товаров в магазине
- 2.2. Пример создания таблицы учета телефонов абонентов
- 2.3. Пример создания таблицы учета заработной платы и отчислений в организации
Поиск на других ресурсах:
1. Оператор CREATE TABLE . Создание таблицы. Общая форма
Таблица является основным элементом любой реляционной базы данных. Вся необходимая информация, которая должна храниться в базе данных, размещается в таблицах. Таблицы могут быть связаны между собой. Количество таблиц в базе данных не ограничено и зависит от сложности решаемой задачи.
Для создания таблицы в языке SQL используется оператор CREATE TABLE . В простейшем случае общая форма оператора CREATE TABLE следующая
- Table_Name – имя таблицы базы данных. Если в базе данных есть таблица с таким именем, то возникнет ошибка;
- Field_Name_1 , Field_Name_2 , Field_Name_N – имена полей (столбцов) таблицы базы данных. Имя каждого поля должно быть уникальным. В разных таблицах имена полей могут совпадать;
- Type_1 , Type_2 , Type_N – соответственно типы полей Field_Name_1 , Field_Name_2 , Field_Name_N такие как INTEGER , DECIMAL , DATE и другие.
На поля Field_Name_1 , Field_Name_2 , Field_Name_N могут накладываться ограничения. Каждое ограничение указывается после имени поля. В этом случае общая форма оператора CREATE TABLE выглядит примерно следующим образом:
- Attribute_1 , Attribute_2 , Attribute_N – ограничения, накладываемые на поля Field_Name_1 , Field_Name_2 , Field_Name_N . Ограничения задаются одним из возможных слов: NULL , NOT NULL , UNIQUE , CHECK , PRIMARY KEY , FOREIGN KEY и других.
Рассмотрение существующих полей атрибутов в языке SQL не является предметом изучения данной темы.
2. Примеры создания таблиц
2.1. Пример создания простейшей таблицы учета товаров в магазине
Условие задачи.
- Используя средства языка SQL создать таблицу с именем Product, которая будет отображать следующую информацию об учете товаров в магазине
- Таблица обязана содержать первичный ключ и обеспечивать уникальность записей.
Решение.
- Для обеспечения уникальности записей в таблице нужно создать дополнительное поле, которое будет являться счетчиком (автоинкрементом). Название поля – ID_Product . Это поле есть первичным ключом. Также это поле имеет ограничение NOT NULL (непустое поле).
- Следующим шагом решения есть назначение имен полям таблицы. В связи с тем, что не все системы управления базами данных (СУБД) поддерживают символы кириллицы, имена полей таблицы будут задаваться латинскими символами. В нашем случае формируются имена и типы данных, сформированные в следующей таблице:
- Написание кода на языке SQL с учетом особенностей предыдущих шагов. Текст программы создания таблицы на языке SQL следующий
В Microsoft SQL Server допускается задавать имена полей без их обрамления в квадратные скобки [] . То есть, предыдущая команда может быть переписана следующим образом
В приведенном выше коде для поля ID_Product задаются ограничения Not Null и Primary Key . В результате запуска программы на SQL будет создана следующая таблица
ID_Product Name Price Count Date Note … … … … … … На рисунке 1 отображаются поля созданной таблицы в Microsoft SQL Server Management Studio.
Рисунок 1. Этапы создания таблицы Product в Microsoft SQL Server Management Studio 18: 1 – создание файла; 2 – набор SQL-запроса; 3 – запуск запроса на выполнение; 4 – результирующая таблица
Конечно, можно создать таблицу, в которой нет поля ID_Product и нету первичного ключа. Такая таблица не будет обеспечивать уникальность записей, поскольку возможна ситуация, когда данные в двух записях могут совпасть. В этом случае SQL код программы имеет вид
2.2. Пример создания таблицы учета телефонов абонентов
Условие задачи.
Используя средства языка SQL (T-SQL) создать таблицу учета телефонов абонентов.
Name Address Phone Number 1 Phone Number 2 Phone Number 3 Ivanov I.I. New York 123456 067-1234567 – Johnson J. Kiev 789012 033-7777778 102 Petrenko P.P. Warshaw 044-2521412 – – … … … … … Обеспечить уникальность и корректное сохранение записей таблицы.
Решение.
Для обеспечения уникальности записей нужно создать дополнительное поле – счетчик. Это поле будет увеличивать свое значение на 1 при каждом добавлении новой записи. Если запись будет удаляться, текущее максимальное значение счетчика не будет уменьшаться. Таким образом, все числовые значения этого поля будут различаться между собой (будут уникальными). В нашем случае добавляется поле ID_Subscriber . Это поле не допускает нулевые значения (NULL).
Для полей Name и [Phone Number 1] целесообразно задать ограничение (атрибут) NOT NULL . Это означает, что в эти поля обязательно нужно ввести значение. Это логично, поскольку абонент в базе данных должен иметь как минимум имя и хотя бы один номер телефона.
После внесенных изменений поля таблицы будут иметь следующие свойства
Название поля Тип данных Объяснение ID_Subscriber INTEGER Первичный ключ, счетчик, NOT NULL Name VARCHAR(50) Фамилия и имя абонента Address VARCHAR(100) Адрес [Phone Number 1] VARCHAR(20) NOT NULL [Phone Number 2] VARCHAR(20) [Phone Number 3] VARCHAR(20) Учитывая вышесказанное, команда CREATE TABLE на языке Transact-SQL (T-SQL) будет выглядеть следующим образом
В запросе имена полей
обязательно должны быть в квадратных скобках [] , поскольку имена состоят из нескольких слов (между словами есть символ пробела).
На рисунке 2 показаны этапы создания таблицы в системе Microsoft SQL Server Management Studio.
Рисунок 2. Окно Microsoft SQL Server Management Studio. Этапы формирования запроса: 1 — создание файла «SQL Query 2.sql» ; 2 — набор SQL-запроса; 3 — выполнение; 4 — результирующая таблица
2.3. Пример создания таблицы учета заработной платы и отчислений в организации
Условие задачи
Используя язык SQL сделать таблицу Account , в которой ведется учет начисленной заработной платы в некой организации. Образец таблицы следующий
Name Position Accrued salary Date of employment Gender Johnson J. Manager 3200.00 01.02.2128 M Petrova M.P. Clerk 2857.35 02.03.2125 F Williams J. Secretary 3525.77 01.08.2127 F Wilson K. Recruiter 1200.63 22.07.2125 F … … … … … Таблицу реализовать так, чтобы обеспечивалась уникальность записей.
Решение.
Чтобы обеспечить уникальность записей, создается дополнительное поле-счетчик ID_Account типа Int. Это поле целесообразно выбрать первичным ключом, если нужно будет использовать данные этой таблицы в других связанных таблицах.
После модификации поля таблицы будут иметь следующие свойства.
Название поля (атрибут) Тип данных Дополнительные объяснения ID_Account Int Автоинкремент (счетчик), первичный ключ (Primary Key), ненулевое поле (Not Null), обеспечивает уникальность записей [Name] VARCHAR(50) Фамилия и имя, не нулевое поле (Not Null) Position VARCHAR(100) Должность, не нулевое поле (Not Null) Salary DECIMAL Тип, предназначенный для сохранения денежных величин [Employment Date] DATE Дата, не нулевое поле (Not Null) Gender CHAR(1) Стать, не нулевое поле (Not Null) В модифицированной таблице поле Salary допускает нулевые ( Null ) значения. Таким случаем может быть, например, когда человек принят на работу, но заработная плата ему еще не начислена. В данной ситуации временно устанавливается Null-значение. Все остальные поля обязательны для заполнения.