SQL DML and DDL. Commands form the DML part of SQL. Типы данных. Правила первичных ключей

SQL DML and DDL

SQL можно разделить на две части: ● язык манипулирования данными (Data Manipulation Language — DML).

● язык определения данных (Data Definition Language — DDL).

DML

Commands form the DML part of SQL:

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

DDL

CREATE DATABASE  — создать базу данных.

CREATE TABLE  — создать таблицу.

ALTER TABLE  — модифицировать таблицу.

RENAME TO  — переименовать таблицу.

CHANGE COLUMN — изменить имя и тип данных столбца.

MODIFY COLUMN — изменить тип данных или позицию  столбца.

ADD COLUMN — добавить столбец в таблицу.

DROP COLUMN — удалить столбец из таблицы.

DROP TABLE  — удалить таблицу.

Типы данных

CHAR — набор символов фиксированной длины. VARCHAR — текстовые данные длиной до 255 символов.

INT— целое число.

DEC — число с заданной точностью.

DATE — дата.

DATETIME — дата и время.

BLOB — массив двоичных данных.

CREATE TABLE

CREATE  TABLE my_contacts

(

  contact_id INT NOT NULL AUTO_INCREMENT,

last_name varchar(30) default NULL,

first_name varchar(20) default NULL,

 PRIMARY KEY (contact_id)

);

ALTER TABLE и добавление первичного ключа

ALTER  TABLE my_contacts

ADD COLUMN contact_id INT NOT NULL

AUTO_INCREMENT FIRST, ADD PRIMARY KEY (contact_id);

Правила первичных ключей

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

●  Не может содержать NULL.

●  Значение должно задаваться при вставке записи.

●  Должен быть компактным.

●  Значение должно оставаться неизменным.

INSERT INTO

1)  INSERT INTO my_contacts (contact_id, first_name, last_name) VALUES (NULL, 'Дональд', 'Кнут');

2)  INSERT INTO my_contacts (contact_id, first_name, last_name) VALUES (1, ' ', 'Тим Бернерс Ли- ');

3)  INSERT INTO my_contacts                      VALUES ('', 'Бьерн', 'Cтрауструп');

4)  INSERT INTO my_contacts (first_name, last_name) VALUES ('Джеймс', 'Гослинг');

5)  INSERT INTO my_contacts (contact_id, last_name, first_name) VALUES (99, 'Брин', 'Сергей');

INSERT INTO 2

my_contacts

contact_id	first_name	last_name
1	Дональд	Кнут
2	Бьерн	Страуструп
3	Джеймс	Гослинг
99	Сергей	Брин

UPDATE

Persons

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Tjessem	Jakob	NULL	NULL

UPDATE Persons

SET Address='Nissestien 67', City='Sandnes'

WHERE LastName='Tjessem' AND FirstName='Jakob';

UPDATE 2

UPDATE my_table

SET _имя нового столбца_  =

CASE

WHEN cтолбец1 = значение 1

THEN новое значение_ 1

WHEN cтолбец2 = значение2

THEN новое значение_ 2

ELSE значение3

END;

DELETE

Persons

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Tjessem	Jakob	Nissestien 67	Sandnes

DELETE FROM Persons

WHERE LastName='Tjessem' AND FirstName='Jakob';

SELECT with LIKE

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger

SELECT with IN

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger

SELECT * FROM Persons

WHERE LastName IN

('Hansen','Pettersen');

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

SQL SUM() Example

Orders

O_Id	OrderDate	OrderPric e	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

OrderTotal

5700

SELECT SUM(OrderPrice)

AS OrderTotal FROM

Orders;

SUM() — суммирует значения столбца. AVG() — среднее значение в столбце MIN() — наименьшее значение в столбце.

MAX() — наибольшее значение в столбце.

COUNT() — количество записей в столбце.

DISTINCT — выводит значения без повторов.

SQL GROUP BY Example

SELECT

O_Id	OrderDate	OrderPric e	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Customer,SUM(OrderPrice)

FROM Orders

GROUP BY Customer;

Customer	SUM(OrderPrice)
Hansen	2000
Nilsen	1700
Jensen	2000

Different SQL JOINs

СROSS JOIN: Перекрестное соединение возвращает ,  комбинации каждой записи первой таблицы с      каждой записью второй таблицы   .

INNER JOIN: Внутренним соединением называется   перекрестное соединение, из результатов которого часть записей исключена по условию      запроса.

LEFT JOIN:  В левом внешнем соединении для КАЖДОЙ     ЗАПИСИ ЛЕВОЙ таблицы ищется соответствие среди      записей правой таблицы  .

RIGHT JOIN: Правое внешнее соединение ищет в     левой таблице соответствия для правой таблицы     .

INNER JOIN 1

toys

boy_id boy toy_id 1 Дейви 3 2 Бобби 5 3 Бивер 2 4 Ричи 1

toy_id toy 1 обруч 2 вертолет 3 солдатики 4 баян 5 бита

SELECT boys.boy, toys.toy

boy	toy
Ричи	обруч
Бивер	вертолет
Дейви	солдатики
Бобби	бита

FROM boys   INNER JOIN   toys

ON boys.toy_id = toys.toy_id;

Эквивалентное соединение — внутреннее соединение с проверкой равенства

INNER JOIN 2

boy	toy
Бивер	обруч
Бивер	солдатики
Бивер	баян
Бивер	бита
Бобби	солдатики
Бобби	баян
Бобби	обруч
Бобби	вертолет
...	…

SELECT boys.boy, toys.toy

FROM boys   INNER JOIN   toys

ON boys.toy_id <> toys.toy_id

ORDER BY boys.boy;

Неэквивалентное соединение — Проверяет нсовпадение значений.

LEFT OUTER JOIN toys

boy_id boy toy_id 1 Дейви 3 2 Бобби 4 3 Бивер 1

toy_id toy 1 обруч 2 вертолет 3 солдатики 4 баян 5 бита

SELECT b.boy, t.toy

FROM boys b

LEFT OUTER JOIN toys t

boy	toy
Бивер	обруч
Дейви	солдатики
Бобби	баян

ON b.toy_id = t.toy_id;

LEFT OUTER JOIN 2

toys

boy_id boy toy_id 1 Дейви 3 2 Бобби 4 3 Бивер 1

toy_id toy 1 обруч 2 вертолет 3 солдатики 4 баян 5 бита

SELECT b.boy, t.toy

FROM toys t

LEFT OUTER JOIN boys b

boy	toy
Бивер	обруч
NULL	вертолет
Дейви	солдатики
Бобби	баян
NULL	бита

ON b.toy_id = t.toy_id;

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

boy_id	boy	boss_id
1	Дейви	3
2	Бобби	2
3	Бивер	2
4	Скутер	5
5	Элси	2
6	Бейб	1

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

boy	boss
Дейви	Бивер
Бобби	Бобби
Бивер	Бобби
Скутер	баян
Элси	Бобби
Бейб	Дейви

SELECT b1.boy, b2.boy AS boss

FROM boys b1

INNER JOIN boys b2

ON b1.boss_id = b2.boy_id;

Подзапросы

SELECT some_column,another_column

FROM table

WHERE column = (SELECT column

FROM table );

Подзапросы  могут использоваться в командах:

●  INSERT,

●  DELETE,

●  UPDATE, ● SELECT.

Пример

Дана таблица numbers, состоящая из одного столбца n (тип данных int), заполненного числами от 1 до 20.

Необходимо одним запросом вывести из таблицы    _numbers numbers числа