Yotc/sql_for_beginner.md at master · kolei/yotc

После создания пустых таблиц следующим логическим шагом является заполнение их данными и обновление этих данных. Для этого в Transact-SQL предназначена пара инструкций INSERT – UPDATE.

Однако, часто на позднем этапе проектирования возникает необходимость изменить саму схему таблиц. Например, если изменился первичный ключ или тип данных столбца. Чтобы не удалять старые таблицы и не создавать их заново с помочью CREATE TABLE c правильными параметрами, применяется инструкция ALTER TABLE. Применение этих трех конструкций рассматривается ниже.

SQL — добавление данных в таблицу

От автора: инструкция SQL INSERT INTO используется для того, чтобы произвести добавление данных в таблицу SQL, а точнее, для добавления новых строк.

Синтаксис

Существуют два основных синтаксиса инструкции INSERT INTO:

INSERT INTO ИМЯ_ТАБЛИЦЫ (столбец1, столбец2, столбец3,... столбец N)  

VALUES (значение1, значение2, значение3,... значение N);

Здесь столбец1, столбец2, столбец3,… столбец N — это названия столбцов в таблице, в которые вы хотите вставить данные. Вы можете не указывать имя столбца в SQL-запросе, если добавляете значения для всех столбцов таблицы. Но убедитесь, что порядок значений соответствует порядку столбцов в таблице.

Yotc/sql_for_beginner.md at master · kolei/yotc

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля

Получить курс сейчас!

В таком случае синтаксис инструкции SQL INSERT INTO будет следующим:

INSERT INTO TABLE_NAME VALUES (значение1, значение2, значение3,... значение N);

Пример

Следующие инструкции создадут шесть записей в таблице CUSTOMERS.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, ‘Ramesh’, 32, ‘Ahmedabad’, 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2, ‘Khilan’, 25, ‘Delhi’, 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3, ‘kaushik’, 23, ‘Kota’, 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (4, ‘Chaitali’, 25, ‘Mumbai’, 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (5, ‘Hardik’, 27, ‘Bhopal’, 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (6, ‘Komal’, 22, ‘MP’, 4500.00 );

Вы можете создать записи в таблице CUSTOMERS, используя второй синтаксис, как показано ниже.

INSERT INTO CUSTOMERS

VALUES (7, ‘Muffy’, 24, ‘Indore’, 10000.00 );

Все приведенные выше инструкции создадут те же записи в таблице CUSTOMERS, как показано ниже.

SQL — добавление данных в таблицу

Заполнение одной таблицы с использованием другой таблицы

Вы можете заполнить данные таблицы через инструкцию select для другой таблицы; если в другой таблице есть тот же набор полей, которые должны быть заполнены в первой. Синтаксис:

INSERT INTO имя_первой_таблицы [(столбец1, столбец2,... столбец N)]

   SELECT столбец1, столбец2,... столбец N

   FROM имя_второй_таблицы

   [WHERE условие];

Источник: //www.tutorialspoint.com/

Редакция: Команда webformyself.

Yotc/sql_for_beginner.md at master · kolei/yotc

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля

Получить курс сейчас!

Yotc/sql_for_beginner.md at master · kolei/yotc

На уроке будет рассмотрена тема «Язык sql создание таблиц». Будут разобраны конкретные примеры создания таблиц

Для создания таблиц используется оператор CREATE TABLE.

Для примера используем ранее созданные таблицы.
Итак, в результате в базе данных Institute мы собираемся получить следующие таблицы:

Используем следующие правила:

  1. имя таблицы указывается после ключевого слова CREATE TABLE (если имя состоит из нескольких слов, то его следует заключить в одинарные кавычки);
  2. далее в круглых скобках следуют имена столбцов таблицы (полей), после которых указывается тип данных, которому будет принадлежать поле;
  3. не обязательно: затем указывается может ли поле содержать пустые значения (NULL — может быть пустым или NOT NULL — обязательно для заполнения);
  4. одно из полей назначается первичным ключом (Primary key);
  5. поля отделяются запятыми.

Первая таблица называется teachers, соответственно первая строка кода:

     

Название таблицы может быть написано и без кавычек, так как состоит из одного слова.


Далее создаем поле id, тип данных которого — целые числа (ограничим их одиннадцатью INT(11)), поле обязательно для заполнения, поэтому добавим NOT NULL:

Поле name будет строковым, ограничим его в 25 символов (VARCHAR(25)), поле тоже обязательно для заполнения, поэтому добавим NOT NULL:

   

Поле zarplata, тип данных которого — целые числа (ограничим их одиннадцатью INT(11)). То же самое касается поля premia:

 
 

После перечисления всех полей указываем ключевое поле:

Важно: желательно для каждой таблицы базы данных выбирать ключевое поле. Это поле уникально идентифицирует каждую запись таблицы, т.е. значения этого поля для каждой записи (строки) должны быть уникальными и не могут повториться для нескольких строк. Обычно ключевое поле используется для организации связей между таблицами

Получаем код создания таблицы teachers:

   
     
     
   
   
    
;

Для заполнения таблицы данными используется оператор INSERT языка SQL.

  teachers 
      
     
     ;

SQL table 1. Аналогичным образом создайте таблицы базы данных lessons и courses.

Обновление таблиц: удаление и добавление полей

Обновление таблиц выполняется при помощи ключевых слов sql ALTER TABLE. Обновляя таблицу можно:

  • удалять поля
  • добавлять поля
  1. Добавление поля в SQL таблицу, ADD
  2. Пример: В таблицу teachers добавить поле phoneдля номеров телефонов

      teachers  phone  ;

    а в правое окно:

    В итоге получаем:

  3. Удаление поля из SQL таблицы, DROP COLUMN
  4. Пример: необходимо удалить поле phone из таблицы teachers

  teachers   phone



База данных — централизованное хранилище данных, обеспечивающее хранение, доступ, первичную обработку и поиск информации.

Базы данных разделяются на:

  • Иерархические
  • Сетевые
  • Реляционные
  • Объектно-ориентированные

SQL (Structured Query Language) — представляет из себя структурированный язык запросов (перевод с английского). Язык ориентирован на работу с реляционными (табличными) базами данных. Язык прост и, по сути, состоит из команд (интерпретируемый), посредством которых можно работать с большими массивами данных (базами данных), удаляя, добавляя, изменяя информацию в них и осуществляя удобный поиск.

Для работы с SQL кодом необходима система управления базами данных (СУБД), которая предоставляет функционал для работы с базами данных.

Система управления базами данных (СУБД) — совокупность языковых и программных средств, предназначенных для создания, ведения и совместного использования БД многими пользователями.

Важно: При работе с реляционными или табличными базами данных строки таблицы будем называть записями, а столбцы — полями.

Каждый столбец должен иметь свой тип данных, т.е. должен быть предназначен для внесения данных определенного типа. Типы данных в MySQL описаны в одном из уроков данного курса.

Составляющие языка SQL

Язык SQL состоит из следующих составных частей:

  1. язык манипулирования данными (Data Manipulation Language, DML);
  2. язык определения данных (Data Definition Language, DDL);
  3. язык управления данными (Data Control Language, DCL).

1.
Язык манипулирования данными состоит из 4 главных команд:

Язык определения данных используется для создания и изменения структуры базы данных и ее составных частей — таблиц, индексов, представлений (виртуальных таблиц), а также триггеров и сохраненных процедур.

Мы будем рассматривать лишь несколько из основных команд языка. Ими являются:

  • создание базы данных — CREATE DATABASE
  • создание таблицы — CREATE TABLE
  • изменение таблицы (структуры) — ALTER TABLE
  • удаление таблицы — DROP TABLE

Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде.

Как сделать sql запрос в phpmyadmin

  1. Запустить ярлык start denwer.
  2. В адресной строке браузера набрать http://localhost/tools/phpmyadmin.
  3. В левой части окна выбрать интересующую базу данных или создать ее (если еще не создана). Создание базы данных в phpmyadmin рассмотрено ниже.
  4. Если известна таблица, с которой будет работать запрос — в левой части окна выбрать эту таблицу.
  5. Выбрать вкладку SQL и начать вводить запрос.

как сделать sql запрос в phpmyadmin

Создание базы данных в phpmyadmin

Для начала необходимо выполнить первые два пункта из предыдущего задания.
Затем:

  • в открывшемся веб-интерфейсе выбрать вкладку Базы данных;
  • в поле Создать базу данных ввести название базы;
  • щелкнуть по кнопке Создать;
  • теперь для продолжения работы в phpMyAdmin в созданной базе данных можно перейти к уроку создания таблиц.

phpmyadmin создать базу данных

Работа в сервисе sql fiddle

Онлайн проверка sql запросов возможна при помощи сервиса sqlFiddle.
Самый простой способ организации работы состоит из следующих этапов:

  1. В верхней части рабочей области сервиса выбираем язык: SQLite(WebSQL);
    Онлайн проверка sql
  2. Открывшаяся рабочая область разделена визуально на 3 окна: левое — для кода создания таблиц и заполнения их данными, правое — для кода запросов, нижнее — для отображения результатов запросов.

  3. В левое окно помещается код для создания таблиц и вставки в них данных (пример кода расположен ниже). Затем щелкается кнопка «Build Schema».
  4. Онлайн проверка sql запросов

  5. После того как схема построена (об этом сигнализирует надпись на зеленом фоне «Schema Ready»), в правое окошко вставляется код запроса и щелкается кнопка Run SQL.
  6. онлайн проверка SQL sqlFiddle

Еще пример:

Теперь некоторые пункты рассмотрим подробнее.

Создание таблиц:

Пример: создайте сразу три таблицы (teachers, lessons и courses); добавьте по нескольку значений в каждую таблицу.

* для тех, кто незнаком с синтаксисом — просто скопировать полностью код и вставить в левое окошко сервиса

* урок по созданию таблиц в языке SQL далее

 
   
     
     
   
   
   
    
;
  teachers        ;
 

   
     
   
   
   
  
;
  lessons     ;
 

   
     
   
   
   
  
;
  courses       ;

В результате получим таблицы с данными:

Отправка запроса:
Для того чтобы протестировать работоспособность сервиса, добавьте в правое окошко код запроса.

Пример: при помощи запроса выберите все данные из таблицы teachers, касаемые учителя с фамилией Иванов

       ;

На дальнейших уроках SQL будет использоваться та же схема, поэтому необходимо будет просто копировать схему и вставлять в левое окно сервиса.

Онлайн визуализации схемы базы данных

  1. Создать свой аккаунт (войти в него, если уже есть).
  2. Щелкнуть по кнопке Go to Application.
  3. Скопировать и вставить в появившееся окно код создания и заполнения таблиц базы данных

Онлайн визуализации схемы базы данных



Оператор CREATE

Создание таблицы

PostgreSQL

Types

Используйте тип serial для первичного ключа!
В PostgreSQL serial — автоинкрементированный тип, т.е. он будет увеличиваться на 1 с каждой добавленной записью. Такое поведение можно сделать при помощи Integer, но тогда придётся в ручную создать последовательность. Serial не совсем тип данных, это скорее короткая запись. Поэтому в таблицах, которые ссылаются на таблицу с serial должен использоваться уже тип integer (bigserial -> bigInt соответственно)!!!

Date/Time:
timestamp without time zone
timestamp with time zone
date
time without time zone
time with time zone
interval

character:
varchar(n) — текст с n — максимальным значением символов
text — текст без ограничений длины

Create синтаксис

   (
	id  ,
	name (),
	city (),-- DEFAULT 'Москва',
	street (),
	house (),
	phone (),
	email ()  UNIQUE
)

   (
	id ,
	name (),
	city (),
	street (),
	house (),
	phone (),
	email ()  UNIQUE,
	(id)
)

<div data-snippet-clipboard-copy-content="CREATE TABLE supplier (
id serial,
name varchar(255),
city varchar(100),
street varchar(100),
house varchar(20),
phone varchar(20),
email varchar(100) NOT NULL UNIQUE,
—rating integer(1) CHECK (1 <= rating

   (
	id ,
	name (),
	city (),
	street (),
	house (),
	phone (),
	email ()  UNIQUE,
	--rating integer(1) CHECK (1 &lt;= rating &lt;= 5),
	 supplier_id_pk  (id)
)

Foreign Key

Допустим, мы хотим добавить таблицу supplier_info, в которой есть ссылка на таблицу supplier. Это можно сделать следующими способами:

  1. На той же строчке, что и атрибут, без упоминания атрибута с которым связываем. Удобно, когда в supplier не составной первичный ключ. (Обратите внимание, что supplier_id — integer, а не serial! Это важно)

   (
	id  ,
	supplier_id   supplier,
	info ()
)
  1. На той же строке, но с указанием атрибута, с которым связываем

   (
	id  ,
	supplier_id   supplier (id),
	info ()
)
  1. Внутри Create Table, но уже после указания атрибутов. Этот вариант даёт возможность самостоятельно назвать внешний ключ

   (
	id  ,
	supplier_id ,
	info (),
	 supplier_info_supplier_id_fk  (supplier_id)
		 supplier (id)
)

Оператор CREATE служит для создания любого типа объектов, из которых состоит база данных, в том числе таблиц.

Синтаксис команды создания таблицы:

CREATE  TABLE  имя_таблицы(
поле тип [ограничения],
[поле тип [ограничения],
…]);

Возможные ограничения в таблицах:

  • NOT NULL – значение атрибута должно быть определено (опция NOT NULL);
  • UNIQUE – значения атрибутов являются уникальными (уникальный ключ);
  • PRIMARY KEY – атрибут является первичным ключом (первичный ключ);
  • CHECK – определяет условие, которому должны удовлетворять значения атрибута (домен);
  • DEFAULT – присвоение значений «по умолчанию» для атрибутов.

Используя приведенный выше синтаксис, вместе с созданием таблицы можно вместе с атрибутами таблицы сразу же задать и ограничения декларативной и ссылочной целостности, но только в том случае, если они касаются только одного конкретного атрибута. Если же первичный, уникальный или вторичный ключ должны быть составными, для их создания нужно применять команду ALTER TABLE.

Например, для создания таблицы Students достаточно выполнить следующую команду:

   (
    IDACH (,),
	NAME (),
	SURNAME (),
	N_GROUP (,),
	ADDRESS (),
	date_birth ,
	AVERAGE_SCORE (,),
	 STUDENTS_PK  (IDACH)
  USING INDEX  ENABLE
   )

Обратите внимание. Если при создании вы используете «, например «student» (именно маленькими), то название таблицы/атрибута станет чувствительным к регистру. Плюсы — можно написать зарезервированное слово, например «count». Минусы — придётся в любом запросе ещё использовать кавычки, что не особо удобно.

Создание копии таблицы

Иногда необходимо создать таблицу в точности или частично по структуре и содержанию копирующую какую-либо из существующих таблиц. В этом случае используется следующий синтаксис:

CREATE TABLE имя_таблицы  оператор 

Например, для того чтобы создать копию таблицы Students можно выполнить следующую команду:

       STUDENTS;

Оператор ALTER

Оператор ALTER служит для изменения структуры любых объектов, из которых состоит база данных. В зависимости от типа объекта, изменяются и параметры команды ALTER.

Добавление атрибута таблицы

Для добавления атрибута к таблице применяется следующий синтаксис:

  имя_таблиц_ ADD поле тип [ограничения];

Например, для добавления к таблице Students поля n_group можно выполнить следующую команду:

  student ADD n_group ();

Удаление атрибута таблицы

Для удаления атрибута таблицы применяется следующий синтаксис:

  имя_таблицы DROP COLUMN поле;

Изменение типа атрибута, размера типа и наличия ограничения NOT NULL

Для изменения типа данных атрибута, размера типа данных или наличия опции NOT NULL используется следующие синтаксис:

   имя_таблицы MODIFY  поле  тип [ограничения];

При изменении типов данных и размеров типов данных атрибутов следует соблюдать осторожность. Дело в том, что при «сужении» области допустимых значений, который может принимать атрибут, могут быть утеряны («обрезаны») данные, выходящие за пределы новой области допустимых значений.

Создание первичного ключа

Для создания первичного ключа используется следующий синтаксис:

  имя_таблицы ADD  имя_ограничения PRIMARY  KEY(список_полей);

Например, для создания первичного ключа таблицы Students можно выполнить следующую команду:

  Students ADD  Students_PK PRIMARY  KEY(id);

Если создаваемый первичный ключ является составным, то есть состоит из нескольких атрибутов, атрибуты перечисляются через запятую.

Создание уникального ключа

Для создания уникального ключа используется следующий синтаксис:

  имя_таблицы ADD  имя_ограничения UNIQUE(_список_полей_);

Например, для создания уникального ключа в таблице Students, гарантирующего уникальность значений в атрибуте Comments, можно выполнить следующую команду:

  Students ADD  Students_Comm_uk UNIQUE (Comment);

Создание домена

Для создания домена используется следующий синтаксис:

  имя_таблицы ADD  имя_ограничения (_условное_выражение_);

При наличии домена в таблице, каждый раз при изменении или добавлении значений в атрибуты, затрагиваемые условным выражением, СУБД будет проверять новое значение атрибутов на удовлетворение указанному выражению. Новое значение будет присвоено атрибуту только в том случае, если значение удовлетворяет указанному условию. При составлении условных выражений можно использовать логические операции &gt;, &lt;, &gt;=, &lt;=, &lt;&gt;, =, функции IN (проверка вхождения в множество), BETWEEN (проверка вхождения в диапазон), LIKE (проверка на удовлетворению шаблону). Кроме того, несколько условных выражений можно комбинировать при помощи логических операторов AND, OR и NOT.

= 0);»>

  Students ADD  Students_Ngr_check (n_group  );

Создание внешнего ключа

Для добавления внешнего ключа используется следующий синтаксис:

    имя_подчиненной_таблицы
ADD    имя_ограничения
FOREIGN  KEY(список_полей_подчиненной_таблицы)
  имя_главной_таблицы(список_полей_главной_таблицы)
[  CASCADE |   |  DEFAULT | NO ACTION]
[  CASCADE |   |  DEFAULT | NO ACTION];

Параметры ON UPDATE и ON DELETE указываются при необходимости осуществлять каскадные действия при, соответственно, изменении или удалении значений атрибутов первичного ключа главной таблицы. При попытке изменения значения атрибута, входящего в состав первичного ключа, или удаления строки, на которую ссылаются строки из подчиненных таблиц, СУБД принимает решение либо о выполнении одного из каскадных действий, либо о запрещении SQL-оператора, изменяющего или удаляющего строку в главной таблице.

Если параметры ON UPDATE или ON DELETE равны NO ACTION или они не указаны совсем, СУБД запретит выполнение SQL-оператора, нарушающего ссылочную целостность.

Если после параметров ON UPDATE или ON DELETE стоит значение CASCADE, то произойдет каскадное изменение или удаление строк в подчиненных таблицах. Иными словами, при удалении строки из главной таблицы, будут удалены все строки в подчиненных таблицах, которые ссылались на удаляемую строку. При изменении значения первичного ключа главной таблицы и наличии параметра ON UPDATE со значением CASCADE все значения внешних ключей, ссылающихся на изменяемый атрибут, также изменят свое значение.

Использовать значение CASCADE параметров ON UPDATE и ON DELETE следует с особой осторожностью, поскольку одна ошибочная команда, например, удаления строки в одной из таблиц может повлечь за собой необратимые последствия, которые могут привести к полной неработоспособности приложений базы данных. Если все-таки необходимо выполнять каскадное изменение или удаление данных в подчиненных таблицах, рекомендуется, по возможности, реализовывать это посредством хранимых процедур.

Указание значения SET NULL для параметров ON UPDATE или ON DELETE приведет к тому, что значения атрибутов внешних ключей, ссылающихся на изменяемое значение первичного ключа, установится в NULL.

Указание значения SET DEFAULT для параметров ON UPDATE или ON DELETE приведет к тому, что значения атрибутов внешних ключей, ссылающихся на изменяемое значение первичного ключа, примут значение по умолчанию, заданное при создании таблицы.

Пример создания внешнего ключа, связывающего таблицы Students_Hobby и Students можно выполнить следующую команду:

  Students_Hobby ADD  Students_Hobby_fk (id)  Students(id);

Удаление ограничений целостности

Для удаления ограничения целостности (первичного ключа, уникального ключа или внешнего ключа) применяется следующий синтаксис:

  имя_таблицы DROP  имя_ограничения;

Например, для удаления внешнего ключа связывающего таблицы Students_Hobby и Students можно выполнить следующую команду:

  Students_Hobby DROP  Students_Hobby_fk;

Оператор DROP

Оператор DROP служит для удаления объектов из базы данных. Синтаксис удаления любого типа объекта из базы данных, в том числе таблицы, выглядит следующим образом:

DROP тип объекта имя объекта;

В данном случае таблица успешно удалится только в том случае, если на нее не ссылаются внешний ключи других таблиц. В том случае, если внешние ключи существуют, но удалить таблицу все-таки необходимо, можно, либо сначала удалить все внешние ключи, ссылающиеся на удаляемую таблицу, после чего удалить таблицу, либо используя параметр CASCADE CONSTRAINTS:

  Имя_Таблицы CASCADE CONSTRAINTS;

В последнем случае, если внешние ключи, ссылающиеся на удаляемую таблицу, существуют, они будут удалены без изменений в полях внешних ключей подчиненных таблиц, даже если при создании внешних ключей были указаны параметры, задающие каскадные действия.

Создание базы данных.

Подключение к базе данных

Прежде чем что-то сделать с базой данных нужно к ней подключиться:

  1. Запустите программу MySQL Workbench

  2. На стартовом окне программы кликните по значку «+»

    Yotc/sql_for_beginner.md at master · kolei/yotc

  3. В параметрах подключения заполните поля:

    Yotc/sql_for_beginner.md at master · kolei/yotc

    • Store in vault — открывает окно для ввода и СОХРАНЕНИЯ пароля. Дома можете настроить, чтобы каждый раз не вводить пароль, но в классе лучше не использовать.

    • Test connection — тест соединения. Программа запросит пароль и попытается подключиться к серверу используя введенные параметры. Если все параметры введены правильно, то появится окно подтверждения.

      Yotc/sql_for_beginner.md at master · kolei/yotc

    • В окне настроек соединения жмите «OK»

Создание БД используя скрипт

  1. Yotc/sql_for_beginner.md at master · kolei/yotc

  2. Сформируйте скрипт для создания БД: File -&gt; Export -&gt; Forward Engineer SQL CREATE Script. В окнах настроек и объектов пока всё оставляем по-умолчанию. Скопируйте скрипт в буфер обмена.

  3. Откройте ранее созданное подключение к БД. Обычно при открытии БД одновременно открывается окно Query (запросы). Если его нет, но откройте новое:

    Yotc/sql_for_beginner.md at master · kolei/yotc

  4. Вставьте в окно запросов скрипт из буфера обмена и закомментируйте строку с созданием схемы (у вас нет прав на создание новых схем)

    Yotc/sql_for_beginner.md at master · kolei/yotc

  5. Если всё нормально, то будут созданы все таблицы и связи, которые вы описали в EER

    Yotc/sql_for_beginner.md at master · kolei/yotc

На демо-экзамене в ресурсах будет аналогичный файл скрипта базы данных, но в нём не укзана база данных. Нужно в начале скрипта вписать команду use &lt;название вашей базы&gt;

Импорт данных.

Стандартом де-факто для импорта/экспорта данных является формат CSV.

Что такое CSV-файлы

Формат CSV используют, чтобы хранить таблицы в текстовых файлах. Данные очень часто упаковывают именно в таблицы, поэтому CSV-файлы очень популярны.

CSV расшифровывается как comma-separated values — «значения, разделенные запятыми». Но разделителями столбцов в CSV-файле могут служить и точки с запятой, и знаки табуляции. Это все равно будет CSV-файл.

У CSV куча плюсов перед тем же форматом Excel: текстовые файлы просты как пуговица, открываются быстро, читаются на любом устройстве и в любой среде без дополнительных инструментов.

Из-за своих преимуществ CSV — сверхпопулярный формат обмена данными, хотя ему уже лет 40. CSV используют прикладные промышленные программы, в него выгружают данные из баз.

Одна беда — текстового редактора для работы с CSV мало. Еще ничего, если таблица простая: в первом поле ID одной длины, во втором дата одного формата, а в третьем какой-нибудь адрес. Но когда поля разной длины и их больше трех, начинаются мучения.

Еще хуже с анализом данных — попробуй «Блокнотом» хотя бы сложить все числа в столбце. Я уж не говорю о красивых графиках.

Поэтому CSV-файлы анализируют и редактируют в Excel и аналогах: Open Office, LibreOffice и прочих.

Как Excel портит данные: из классики

Все бы ничего, но Excel, едва открыв CSV-файл, начинает свои лукавые выкрутасы. Он без спроса меняет данные так, что те приходят в негодность. Причем делает это совершенно незаметно. Из-за этого в свое время мы схватили ворох проблем.

Большинство казусов связано с тем, что программа без спроса преобразует строки с набором цифр в числа.

Округляет. Например, в исходной ячейке два телефона хранятся через запятую без пробелов: «5235834,5235835». Что сделает Excel? Лихо превратит номера́ в одно число и округлит до двух цифр после запятой: «5235834,52». Так мы потеряем второй телефон.

Приводит к экспоненциальной форме. Excel заботливо преобразует «123456789012345» в число «1,2E+15». Исходное значение потеряем напрочь.

Проблема актуальна для длинных, символов по пятнадцать, цифровых строк. Например, КЛАДР-кодов (это такой государственный идентификатор адресного объекта: го́рода, у́лицы, до́ма).

Удаляет лидирующие плюсы. Excel считает, что плюс в начале строки с цифрами — совершенно лишний символ. Мол, и так ясно, что число положительное, коль перед ним не стоит минус. Поэтому лидирующий плюс в номере «+74955235834» будет отброшен за ненадобностью — получится «74955235834». (В реальности номер пострадает еще сильнее, но для наглядности обойдусь плюсом).

Потеря плюса критична, например, если данные пойдут в стороннюю систему, а та при импорте жестко проверяет формат.

Разбивает по три цифры. Цифровую строку длиннее трех символов Excel, добрая душа, аккуратно разберет. Например, «8 495 5235834» превратит в «84 955 235 834».

Форматирование важно как минимум для телефонных номеров: пробелы отделяют коды страны и города от остального номера и друг от друга. Excel запросто нарушает правильное членение телефона.

Удаляет лидирующие нули. Строку «00523446» Excel превратит в «523446».
А в ИНН, например, первые две цифры — это код региона. Для Республики Алтай он начинается с нуля — «04». Без нуля смысл номера исказится, а проверку формата ИНН вообще не пройдет.

Меняет даты под локальные настройки. Excel с удовольствием исправит номер дома «1/2» на «01.фев». Потому что Windows подсказал, что в таком виде вам удобнее считывать даты.

Побеждаем порчу данных правильным импортом

Если серьезно, в бедах виноват не Excel целиком, а неочевидный способ импорта данных в программу.

По умолчанию Excel применяет к данным в загруженном CSV-файле тип «General» — общий. Из-за него программа распознает цифровые строки как числа. Такой порядок можно победить, используя встроенный инструмент импорта.

Запускаем встроенный в Excel механизм импорта.

Выбирем CSV-файл с данными, открывается диалог. В диалоге кликем на тип файла Delimited (с разделителями). Кодировка — та, что в файле, обычно определяется автоматом. Если первая строка файла — шапка, отмечем «My Data Has Headers».

На третьем шаге выбираем формат полей, ради него все и затевалось. Для всех столбцов выставляем тип «Text». Кстати, если кликнуть на первую колонку, зажать шифт и кликнуть на последнюю, выделятся сразу все столбцы. Удобно.

Дальше Excel спросит, куда вставлять данные из CSV — можно просто нажать «OK», и данные появятся в открытом листе.

Но! Если мы планируем экспортировать данные в CSV из Excel, придется сделать еще кое-что.

После импорта нужно принудительно привести все-все ячейки на листе к формату «Text». Иначе новые поля приобретут все тот же тип «General».

  • Нажимаем два раза Ctrl+A, Excel выбирает все ячейки на листе;
  • кликаем правой кнопкой мыши;
  • выбираем в контекстном меню «Format Cells»;
  • в открывшемся диалоге выбираем слева тип данных «Text».

Разбор импорта данных на примере прошлогоднего демо-экзамена

Файлы, используемые в этом разборе, лежат в каталоге data этого репозитория

Создание таблиц из скрипта

  1. Откройте соединение с БД

  2. Откройте вкладку SQL-скрипта, если её ещё нет

    Yotc/sql_for_beginner.md at master · kolei/yotc

  3. Скопируйте в буфер обмена содержимое файла my.sql (на демо-экзамене в ресурсах будет два файла: my.sql для MySQL и ms.sql для MSSQL) и вставьте его во вкладку SQL-скрипта, открытую в предыдущем пункте.

  4. Добавьте в начало скрипта команду USE &lt;название вашей базы данных&gt;;, для моей БД это будет USE ekolesnikov; (обращаю внимание, в MySQL команды должны завершаться точкой с запятой) и выполните скрипт (кликните по значку молнии)

    Yotc/sql_for_beginner.md at master · kolei/yotc

    После выполнения скрипта в вашей БД должны появиться новые таблицы (пустые)

    Yotc/sql_for_beginner.md at master · kolei/yotc

Импорт данных

В ресурсах у нас есть три файла для импорта:

  • materials_short_k_import.txt
  • products_k_import.csv
  • productmaterial_k_import.xlsx

При реальной разработке вам придется самостоятельно разбираться в какую таблицу импортировать данные, но в рамках демо-экзамена имена импортируемых файлов соответсвуют таблицам БД

Импорт материалов

Процесс импорта состоит из нескольких этапов:

  • подготовка данных
  • импорт во временную таблицу
  • перенос данных из временной таблицы в нужную таблицу(ы)
Подготовка данных
  1. Открываем или импортируем файл с исходными данными

    Файл материалов у нас в формате TXT. Такой формат напрямую из Excel открыть нельзя — загружайте через импорт данных (в разных версиях Excel меню может называться по0разному)

    Yotc/sql_for_beginner.md at master · kolei/yotc

    • Выберите файл для импорта

      В мастере импорта на первом шаге выберите правильную кодировку (в предварительном просмотре должен быть читаемый текст) и поставьте, если нужно, галочку «мои данные содержат заголовки»

      Yotc/sql_for_beginner.md at master · kolei/yotc

    • Выберите разделитель (обычно используется запятая или точка с запятой, но могут втретиться и более экзотические символы)

      Yotc/sql_for_beginner.md at master · kolei/yotc

  2. После импорта данных смотрим всё-ли нормально с данными (Смотрим на структуру БД и просто включаем логику). В этой таблице в колонке «стоимость» явно лишние слова «руб.» и «рублей»

    Yotc/sql_for_beginner.md at master · kolei/yotc

    • выделяем редактируемую колонку

      Yotc/sql_for_beginner.md at master · kolei/yotc

    • открываем диалог «Найти и заменить» (Ctrl+F)

      и вырезаем всё лишнее

      Yotc/sql_for_beginner.md at master · kolei/yotc

    • меняем формат ячейки на «числовой»

      Видим, что некоторые строки не распознались как числа.

      Yotc/sql_for_beginner.md at master · kolei/yotc

      Дело в том, что в русской локали разделителем разрядов является запятая, а в исходном файле резделитель точка — меняем точки на запятые

    • в одном из демо-экзаменов в базе названия картинок с расширением .jpg, а реальные файлы были с расширением .jpeg — обращайте внимание, в Excel-e это исправить проще, чем в базе

  3. ВАЖНО!!! Нигде не написано, но названия полей при импорте должны быть латиницей, поэтому переименовываем заголовки столбов.

    MySQL может импортировать данные только в CSV-формате. Файл либо «сохраняем как», либо «экспортируем»

    Yotc/sql_for_beginner.md at master · kolei/yotc

    Yotc/sql_for_beginner.md at master · kolei/yotc

Проверка кодировки

ВАЖНО!!! Кодировка импортируемых данных должна быть такой же, как кодировка базы данных (сейчас практически везде используется UTF-8)

Excel экспортирует текстовые данные в кодировке ANSI (1251)

Для перекодировки файла можно воспользоваться программой Notepad++ — она будет на демо-экзамене

Yotc/sql_for_beginner.md at master · kolei/yotc

  1. Обращаем внимание на кодировку файла. У меня, например, с какого-то перепугу решило что Macintosh.

    Yotc/sql_for_beginner.md at master · kolei/yotc

    Меняем на «Windows-1251»

    Yotc/sql_for_beginner.md at master · kolei/yotc

  2. Кодировка теперь нормальная, но по содержимому видно, что у некоторых столбцов есть лидирующий пробел. Это не есть хорошо.

    Yotc/sql_for_beginner.md at master · kolei/yotc

    Сдесь же в Notepad++ заменим «; » на «;»

    Yotc/sql_for_beginner.md at master · kolei/yotc

  3. Если визуально всё нормально, то меняем кодировку файла на UTF-8

    Yotc/sql_for_beginner.md at master · kolei/yotc

    и сохраняем файл

Импорт во временную таблицу

В контекстном меню базы данных запускаем «мастер импорта данных»

Yotc/sql_for_beginner.md at master · kolei/yotc

Импортируем в новую таблицу с уникальным именем

Yotc/sql_for_beginner.md at master · kolei/yotc

Смотрим, всё ли правильно определилось. Судя по тому, что цену как число не распознало MySQL по-умолчанию ждёт точку в качестве разделителя.

Yotc/sql_for_beginner.md at master · kolei/yotc

Меняем тип поля на double и в появившемся поле «Decimal Separator» пишем запятую.

Yotc/sql_for_beginner.md at master · kolei/yotc

После импорта открываем таблицу и смотрим всё-ли нормально

Yotc/sql_for_beginner.md at master · kolei/yotc

перенос данных из временной таблицы в нужную таблицу(ы)

По структуре таблицы Material видно, что одно поле словарное (MaterialTypeID)

Yotc/sql_for_beginner.md at master · kolei/yotc

т.е. нам сначала нужно заполнить таблицу MaterialType уникальными значениями типов материалов.

Вспомиаем, что для выбора уникальных значений используется ключевое слово DISTINCT

Но, кроме названия метериала (Title) в таблице MaterialType есть ещё какой-то DefectedPercent. Смотрим структуру данных:

Yotc/sql_for_beginner.md at master · kolei/yotc

и видим, что поле это обязательное.

Тут есть два варианта:

  • добавить фиксированное значение, взятое «с потолка»
  • отменить обязательность этого поля (NN = Not NULL)

Мы будем использовать первый вариант.

  1. Открываем окно с SQL-запросами (возможно оно ещё открыто после импорта структуры базы)

  2. Пишем запрос для вставки уникальных значений типов материалов из временной таблицы.

    Современные клиенты СУБД позволяют облегчить написание этих запросов:

    • в контекстном меню таблицы MaterialType выбираем «Send to SQL Editor» -&gt; «Insert Statement»

      Yotc/sql_for_beginner.md at master · kolei/yotc

      В акивное окно SQL-запросов вставится следующий запрос:

      <div data-snippet-clipboard-copy-content="INSERT INTO `ekolesnikov`.`MaterialType`
      (`ID`,
      `Title`,
      `DefectedPercent`)
      VALUES
      (,
      ,
      );»>

       .
      (,
      ,
      )
      
      ({ID: },
      {Title: },
      {DefectedPercent: });