Добавление столбца sql: ALTER TABLE — изменение таблицы в SQL и работа со столбцами (add delete rename column)

SQL и PANDAS

Время прочтения: 6 мин.

PANDAS — это библиотека на языке Python, созданная для
анализа и обработки данных. Имеет открытый исходный код и поддерживается
разработчиками Anaconda. Эта библиотека хорошо подходит для
структурированных (табличных) данных.

Для начала импортируем библиотеки, которые пригодятся нам по ходу работы:

import pandas as pd
import numpy as np

И для наглядности возьмём csv таблицу:

Далее мы записываем информацию из csv в DataFrame, который назовем test_csv, и убедимся, что полученная таблица будет иметь тоже имя и структуру, как и оригинальный csv:

Ввод: test_csv = pd.read_csv('test.csv')
      test_csv.head()
Вывод: 
   ID  FIO             ID_STATUS   OPEN        CLOSE       PRICE   SALE
0  45  Горбунов В.Ф.   В работе    02/05/2020  NaN         1500    0.8
1  49  Нестерова В.В.  В работе    02/05/2020  NaN         2300    0.9
2  52  Горбунов В.Ф.   Выполнено   02/05/2020  04/05/2020  3500    1
3  54  Нестерова В. В.  В работе    03/05/2020  NaN         750     0.6
4  55  Мамонтов Б.А.   Выполнено   03/05/2020  06/05/2020  1230    0.95

SELECT

В SQL выборка необходимых нам столбцов происходит перечислением имен этих столбцов через запятую или с помощью * для выбора всех столбцов:

SELECT ID, ID_STATUS, CLOSE
FROM test_csv
LIMIT 5;

В Pandas выбор столбцов происходит с помощью перечисления необходимых названий столбцов в списке в нашем DataFrame:

Ввод: test_csv[['ID', 'ID_STATUS', 'CLOSE']].head(5)
Вывод:  
   ID  ID_STATUS   CLOSE      
0  45  В работе    NaN         
1  49  В работе    NaN        
2  52  Выполнено   04/05/2020  
3  54  В работе    NaN         
4  55  Выполнено   06/05/2020  

А если же мы вызываем DataFrame без листа с названиями столбцов, то это отобразить все столбцы словно * в SQL.

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

SELECT *, PRICE*SALE as SUM
FROM test_csv
LIMIT 5;

В Pandas  для добавления столбца с расчетами мы воспользуемся DataFrame. assign():

Ввод: test_csv.assign(SUM=test_csv['PRICE'] / test_csv['SALE']).head(5)
Вывод: 
   ID  FIO             ID_STATUS   OPEN        CLOSE       PRICE   SALE   SUM
0  45  Горбунов В.Ф.   В работе    02/05/2020  NaN         1500    0.8    1200
1  49  Нестерова В.В.  В работе    02/05/2020  NaN         2300    0.9    2070
2  52  Горбунов В.Ф.   Выполнено   02/05/2020  04/05/2020  3500    1      3500
3  54  Нестерова В.В.  В работе    03/05/2020  NaN         750     0.6    450
4  55  Мамонтов Б.А.   Выполнено   03/05/2020  06/05/2020  1230    0.95   1168.5

WHERE

Фильтрация в SQL происходит при помощи WHERE:

SELECT *
FROM test_csv
WHERE ID_STATUS = 'Выполнено'
LIMIT 3;

DataFrame же может быть отфильтрован несколькими способами, но самыми частым из них является логическое сравнение:

Ввод: test_csv[test_csv['ID_STATUS'] == 'Выполнено'].head(3)
Вывод: 
   ID  FIO             ID_STATUS   OPEN        CLOSE       PRICE   SALE
1  52  Горбунов В. Ф.   Выполнено   02/05/2020  04/05/2020  3500    1
2  55  Мамонтов Б.А.   Выполнено   03/05/2020  06/05/2020  1230    0.95
3  56  Горбунов В.Ф.   Выполнено   03/05/2020  07/05/2020  767     0.35

Также, как и в SQL, в DataFrame мы можем использовать операторы И/ИЛИ:

SELECT *
FROM test_csv
WHERE ID_STATUS = 'Выполнено' AND PRICE > 1000;
Ввод: test_csv[(test_csv['ID_STATUS'] == 'Выполнено') & (test_csv['PRICE'] > 1000)]
Вывод: 
   ID  FIO             ID_STATUS   OPEN        CLOSE       PRICE   SALE
0  52  Горбунов В.Ф.   Выполнено   02/05/2020  04/05/2020  3500    1
1  55  Мамонтов Б.А.   Выполнено   03/05/2020  06/05/2020  1230    0.95

Для проверки наличия в значении NULL, мы используем notna() и isna(). Для примера создадим DataFrame с NULL значениями.

Ввод: test_1 = pd.DataFrame({'C1': ['1', '1', np.NaN, '1', '1'],
                             'C2': ['2', np.NaN, '2', '2', np.NaN],
                             'C3': [np.NaN, '3', '3', '3', np.NaN]})
      test_1
Вывод: 
    C1   C2   С3
0    1    2  NaN 
1    1  NaN    3
2  NaN    2    3
3    1    2    3
4    1  NaN  NaN

И теперь для примера выберем все строки, где С2 IS NULL:

SELECT *
FROM test_1
WHERE C2 IS NULL;
Ввод: test_1[test_1['C2']. isna()]
Вывод: 
    C1   C2   С3
0    1  NaN    3
1    1  NaN  NaN

Для получения IS NOT NULL значений по столбцу С3 воспользуемся notna():

SELECT *
FROM test_1
WHERE C3 IS NOT NULL;
Ввод: test_1[test_1['C3'].notna()]
Вывод: 
    C1   C2   С3
0    1  NaN    3
1  NaN    2    3
2    1    2    3

UNION

UNION ALL в PANDAS осуществляется с помощью concat():

Ввод: set_1 = pd.DataFrame({'name': ['Банан', 'Арбуз', 'Яблоко'],
                            'price': [90, 30, 150]})
      set_2 = pd.DataFrame({'name': ['Арбуз', 'Ананас', 'Груша'],
                            ''price': [30, 190, 80]})
SELECT name, price
FROM set_1
UNION ALL
SELECT name, price
FROM set_2;
/*
         name  price
        Банан     90
        Арбуз     30
       Яблоко    150
        Арбуз     30
       Ананас    190
        Груша     80
*/
Ввод: pd.concat([set_1, set_2])
Вывод: 
         name  price
0       Банан     90
1       Арбуз     30
2      Яблоко    150
3       Арбуз     30
4      Ананас    190
5       Груша     80

UNION из SQL похожа по функционалу на UNION ALL с отличием только в том, что UNION удаляет дубликаты строк.

SELECT name, price
FROM set_1
UNION
SELECT name, price
FROM set_2;
/*
         name  price
        Банан     90
        Арбуз     30
       Яблоко    150
       Ананас    190
        Груша     80
*/

В PANDAS мы можем использовать concat() в сочетании с drop_duplicates():

Ввод: pd.concat([set_1, set_2]).drop_duplicates()
Вывод: 
         name  price
0       Банан     90
1       Арбуз     30
2      Яблоко    150
3      Ананас    190
4       Груша     80

UPDATE

С помощью UPDATE мы можем “обновить” значения:

SQL:
UPDATE test_csv
SET SALE = SALE*0.9
WHERE SALE > 0.5;
PYTHON:
test_csv.loc[test_csv['SALE'] > 0.5, 'SALE'] *= 0.9

DELETE

В SQL удаление с условием выглядит так:

DELETE FROM test_csv
WHERE SALE > 0.75;

В PANDAS же мы выбираем какие столбцы остаются, а не удаляются как это сделано в SQL:

Ввод: test_csv = test_csv. loc[test_csv['SALE'] <= 0.75]

ЗАДАЧИ

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

Пусть у нас имеется csv таблица work:

• ID – ID работника
• FIO – ФИО работника
• DEPT – Отдел
• CHIED_ID – Непосредственный руководитель
• SALARY – Заработная плата

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

В SQL это будет выглядит так:

SELECT a.*
FROM   work a
WHERE  a.SALARY = ( SELECT MAX(SALARY) 
                    FROM work b
                    WHERE  b.DEPT = a.DEPT )

Для python одним из вариантов будет:

import pandas as pd
import numpy as np
work_csv = pd.read_csv('work.csv', header=0)
unique_dept = pd.unique(df['DEPT']).tolist()
for ud in unique_dept:
   ud_df = work_csv[(work_csv['DEPT'] == ud)]
   max_salary. append(ud_df.iloc[ud_df['SALARY'.idxmax()])
print(max_salary)

Для данной задачи мы получим вот такой ответ:

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

В SQL это будет выглядит так:

SELECT DEPT
FROM work
GROUP BY DEPT
HAVING COUNT(*) <= 3

Для python одним из вариантов будет:

import pandas as pd
import numpy as np
work_csv = pd.read_csv('work.csv', header=0)
unique_dept = pd.unique(df['DEPT']).tolist()
for ud in unique_dept:
   if len(work_csv[(work_csv['DEPT'] == ud)] <= 3:
      print(ud)

И для данной задачи мы получим ответ:
0 и 2, т.к. только они и удовлетворяют условиям нашей задачи.

Мы рассмотрели основные функции SQL в рамках PANDAS на примерах, закрепили полученные знания на практике и теперь с уверенностью
можем покорять новые горизонты!

НОУ ИНТУИТ | Лекция | Создание базы данных и проектирование таблиц

Аннотация: Определяется процесс создания базы данных. Описываются операторы
создания, изменения базы данных. Рассматривается возможность указания
имени файла или нескольких файлов для хранения данных, размеров и
местоположения файлов. Анализируются операторы создания, изменения,
удаления пользовательских таблиц. Приводится описание параметров для
объявления столбцов таблицы. Дается понятие и характеристика
индексов. Рассматриваются операторы создания и изменения индексов.
Определяется роль индексов в повышении эффективности выполнения
операторов SQL.

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

База данных

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

В различных СУБД процедура создания баз данных обычно закрепляется
только за администратором баз данных. В однопользовательских системах
принимаемая по умолчанию база данных может быть сформирована
непосредственно в процессе установки и настройки самой СУБД. Стандарт
SQL не определяет, как должны создаваться базы данных, поэтому в
каждом из диалектов языка SQL обычно используется свой подход. В
соответствии со стандартом SQL, таблицы и другие объекты базы данных
существуют в некоторой среде. Помимо всего прочего, каждая среда
состоит из одного или более каталогов, а каждый каталог – из набора схем. Схема представляет собой поименованную коллекцию объектов базы
данных, некоторым образом связанных друг с другом (все объекты в базе
данных должны быть описаны в той или иной схеме ). Объектами схемы
могут быть таблицы, представления, домены, утверждения,
сопоставления, толкования и наборы символов. Все они имеют одного и
того же владельца и множество общих значений, принимаемых по
умолчанию.

Стандарт SQL оставляет за разработчиками СУБД право выбора
конкретного механизма создания и уничтожения каталогов, однако
механизм создания и удаления схем регламентируется посредством
операторов CREATE SCHEMA и DROP SCHEMA. В стандарте также указано,
что в рамках оператора создания схемы должна существовать возможность
определения диапазона привилегий, доступных пользователям создаваемой схемы. Однако конкретные способы определения подобных привилегий в
разных СУБД различаются.

В настоящее время операторы CREATE SCHEMA и DROP SCHEMA реализованы в
очень немногих СУБД. В других реализациях, например, в СУБД MS SQL
Server, используется оператор CREATE DATABASE.

Создание базы данных в среде MS SQL Server

Процесс создания базы данных в системе SQL-сервера состоит из двух
этапов: сначала организуется сама база данных, а затем принадлежащий
ей журнал транзакций. Информация размещается в соответствующих
файлах, имеющих расширения *.mdf (для базы данных ) и *.ldf. (для журнала транзакций ). В файле базы данных записываются сведения об
основных объектах ( таблицах, индексах, представлениях и т.д.), а в файле журнала транзакций – о процессе работы с транзакциями (контроль
целостности данных, состояния базы данных до и после выполнения
транзакций).

Создание базы данных в системе SQL-сервер осуществляется командой CREATE DATABASE. Следует отметить, что процедура создания базы данных
в SQL-сервере требует наличия прав администратора сервера.

<определение_базы_данных> ::= 
   CREATE DATABASE имя_базы_данных
   [ON [PRIMARY]
   [ <определение_файла>  [,...n] ]
   [,<определение_группы> [,...n] ] ]
   [ LOG ON {<определение_файла>[,...n] } ]
   [ FOR LOAD | FOR ATTACH ]

Рассмотрим основные параметры представленного оператора.

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

intuit.ru/2010/edi»>При создании и изменении базы данных можно указать имя файла, который
будет для нее создан, изменить имя, путь и исходный размер этого
файла. Если в процессе использования базы данных планируется ее
размещение на нескольких дисках, то можно создать так называемые вторичные файлы базы данных с расширением *.ndf. В этом случае
основная информация о базе данных располагается в первичном ( PRIMARY )
файле, а при нехватке для него свободного места добавляемая
информация будет размещаться во вторичном файле. Подход, используемый
в SQL-сервере, позволяет распределять содержимое базы данных по
нескольким дисковым томам.

Параметр ON определяет список файлов на диске для размещения
информации, хранящейся в базе данных.

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

Параметр LOG ON определяет список файлов на диске для размещения журнала транзакций. Имя файла для журнала транзакций генерируется на
основе имени базы данных, и в конце к нему добавляются символы _log.

При создании базы данных можно определить набор файлов, из которых
она будет состоять. Файл определяется с помощью следующей
конструкции:

<определение_файла>::=
   ([ NAME=логическое_имя_файла,]
   FILENAME='физическое_имя_файла'
   [,SIZE=размер_файла ]
   [,MAXSIZE={max_размер_файла |UNLIMITED } ]
   [, FILEGROWTH=величина_прироста ] )[,...n]

Здесь логическое имя файла – это имя файла, под которым он будет
опознаваться при выполнении различных SQL-команд.

Физическое имя файла предназначено для указания полного пути и
названия соответствующего физического файла, который будет создан на
жестком диске. Это имя останется за файлом на уровне операционной
системы.

Параметр SIZE определяет первоначальный размер файла; минимальный
размер параметра – 512 Кб, если он не указан, по умолчанию
принимается 1 Мб.

Параметр MAXSIZE определяет максимальный размер файла базы данных.
При значении параметра UNLIMITED максимальный размер базы данных
ограничивается свободным местом на диске.

При создании базы данных можно разрешить или запретить автоматический
рост ее размера (это определяется параметром FILEGROWTH ) и указать
приращение с помощью абсолютной величины в Мб или процентным
соотношением. Значение может быть указано в килобайтах, мегабайтах, гигабайтах, терабайтах или процентах (%). Если указано число без суффикса МБ, КБ или %, то по умолчанию используется значение MБ. Если размер шага роста указан в процентах (%), размер увеличивается на заданную часть в процентах от размера файла. Указанный размер округляется до ближайших 64 КБ.

Дополнительные файлы могут быть включены в группу:

<определение_группы>::=FILEGROUP имя_группы_файлов
    <определение_файла>[,. ..n]

Пример 3.1. Создать базу данных, причем для данных определить три
файла на диске C, для журнала транзакций – два файла на диске C.

CREATE DATABASE Archive
ON PRIMARY ( NAME=Arch2,
  FILENAME=’c:\user\data\archdat1.mdf’,
SIZE=100MB, MAXSIZE=200, FILEGROWTH=20),
(NAME=Arch3,  
  FILENAME=’c:\user\data\archdat2.mdf’,
  SIZE=100MB, MAXSIZE=200, FILEGROWTH=20),
(NAME=Arch4, 
  FILENAME=’c:\user\data\archdat3.mdf’,
  SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)
LOG ON
(NAME=Archlog1,
  FILENAME=’c:\user\data\archlog1.ldf’,
    SIZE=100MB, MAXSIZE=200, FILEGROWTH=20),
(NAME=Archlog2,
  FILENAME=’c:\user\data\archlog2.ldf’,
    SIZE=100MB, MAXSIZE=200, FILEGROWTH=20)

Пример
3.1.
Создание базы данных.

Изменение базы данных

Большинство действий по изменению конфигурации базы данных
выполняется с помощью следующей конструкции:

<изменение_базы_данных> ::=  
  ALTER DATABASE имя_базы_данных
  { ADD FILE <определение_файла>[,. ..n] 
    [TO FILEGROUP имя_группы_файлов ]
  | ADD LOG FILE <определение_файла>[,...n]
  | REMOVE FILE логическое_имя_файла
  | ADD FILEGROUP имя_группы_файлов
  | REMOVE FILEGROUP имя_группы_файлов
  | MODIFY FILE <определение_файла>
  | MODIFY FILEGROUP имя_группы_файлов
  <свойства_группы_файлов>}

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

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

Параметры файлов и групп файлов можно изменять ( MODIFY ).

Для удаления из базы данных файлов или групп файлов используется
параметр REMOVE. Однако удаление файла возможно лишь при условии его
освобождения от данных. В противном случае сервер не разрешит
удаление.

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

READONLY – группа файлов используется только для чтения; READWRITE –
в группе файлов разрешаются изменения; DEFAULT – указанная группа
файлов принимается по умолчанию.

Удаление базы данных

Удаление базы данных осуществляется командой:

DROP DATABASE имя_базы_данных [,...n]

Удаляются все содержащиеся в базе данных объекты, а также файлы, в
которых она размещается. Для исполнения операции удаления базы данных
пользователь должен обладать соответствующими правами.

Как проверить, существует ли столбец в таблице SQL Server

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

В демонстрационных целях я использовал указанные ниже приложения и создал новую базу данных под названием «Учебник» 9.0003

1. MS SQL Management Studio версии 18.5.1
2. MS SQL Server 2019 Developer Edition (64-разрядная версия)

Для начала создадим новую таблицу. Скрипт для создания таблицы ниже

  СОЗДАТЬ ТАБЛИЦУ dbo.Employee
(
    ID INT IDENTITY(1,1),
    Имя VARCHAR(50)
)  

Используя этот сценарий, мы можем создать таблицу с именем «Сотрудник» с двумя столбцами «Идентификатор» и «Имя».

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

  INSERT INTO Сотрудник (Имя)
SELECT 'Питер' Имя UNION ALL
SELECT 'Роберт' Имя UNION ALL
ВЫБЕРИТЕ «Майкл» Имя  

Пока все хорошо. Верно. Теперь мы увидим несколько способов проверить, существует ли столбец в таблице.

Предположим, что мы собираемся добавить новый столбец «Фамилия» в таблицу «Сотрудник».

Здесь мы собираемся обсудить три подхода, с помощью которых мы можем проверить, существует ли столбец в таблице.

Использование COL_LENGTH

Эта функция возвращает заданную длину столбца в байтах.

Приведенный ниже сценарий может использоваться для проверки «Фамилии» в таблице сотрудников

  ЕСЛИ COL_LENGTH('dbo.Employee', 'LastName') НЕ NULL
PRINT 'Столбец - Фамилия существует'
ЕЩЕ
PRINT 'Column- LastName не существует'  

Пожалуйста, обратитесь к документации Microsoft для получения более подробной информации о COL_LENGTH

Использование sys.columns

Приведенный ниже сценарий можно использовать для проверки существования столбца в таблице. В нашем примере мы используем «Фамилия» в качестве столбца и «Сотрудник» в качестве таблицы

.