# Таблицы

**Типы таблиц:**

- Постоянные таблицы (т .е. созданные с помощью инструкции create table)
- Производные таблицы (т .е. строки, возвращаемые подзапросом и хранящиеся в памяти)
- Временные таблицы (т.е. изменяемые данные, хранящиеся в памяти)
- Виртуальные таблицы (т.е. созданные с помощью инструкции create view).

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

```sql
create table person (
  person_id smallint unsigned, 
  fname varchar(20), 
  lname varchar(20), 
  eye_color char(2), 
  constraint pk_person primary key (person_id)
);
```

изменение струтуры таблицы

```
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT; 
```

какие таблицы ссылаются по внешним ключам на таблицу X

```
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'X';
```

```
DROP TABLE favorite_food;
```

**Ограничения (constraint)**

Первичный ключ, может быть любое кол-во столбцов

```
constraint pk_person primary key (person_id)
```

Создание связи

```
CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id)
```

Ограничение набора в столбце

```
enum('ht', 'hy')
create table person (... eye_color ENUM('BR','BL','GR', ....);
```

AUTO\_INCREMENT в описании ключа автоматическое увеличение номера  
desc person - описание таблицы  
not null при создании столбца запрещает быть пустым   
проверка ограничений

```sql
SELECT * FROM information_schema.check_constraints WHERE table_schema = 'db name' and table_name = 'table name';
```

просмотр скрипта создания таблицы со всеми внесенными изменениями

```sql
SHOW CREATE TABLE mytable;
```

**Получение/изменение данных**

```sql
INSERT INTO person (person_id, fname, lname, eye_color, birth_date) 
   VALUES (null, 'William','Turner', 'BR', '1972-05-27');
ORDER BY - группировка в селекте

UPDATE person 
SET street = '1225 Tremont St.', city = Boston', state =  'MA', country = 'USA', postal_code = '02138' WHERE person_id = 1;

DELETE FROM person WHERE person_id = 2;
```

**Структура select**

<table border="1" id="bkmrk-select-%D0%9E%D0%BF%D1%80%D0%B5%D0%B4%D0%B5%D0%BB%D1%8F%D0%B5%D1%82%2C%D0%BA%D0%B0" style="border-collapse: collapse; width: 100%;"><colgroup><col style="width: 10.8526%;"></col><col style="width: 89.1454%;"></col></colgroup><tbody><tr><td>select</td><td>Определяет,какие столбцы следует включить в результирующий набор запроса</td></tr><tr><td>from</td><td>Определяет таблицы, из которых следует выбирать данные, а также таблицы, которые должны быть соединены</td></tr><tr><td>where</td><td>Отсеивает ненужные данные</td></tr><tr><td>group by</td><td>Используется для группировки строк по общим значениям столбцов</td></tr><tr><td>having</td><td>Отсеивает ненужные данные</td></tr><tr><td>order by</td><td>Сортирует строки окончательного результирующего набора по одному или нескольким столбцам</td></tr></tbody></table>

**Фильтрация**

Логические операции в where: and, or, not, для группировки круглые скобки, операторы сравнения, такие как =,!=, &lt;, &gt;,о,like,in и between; арифметические операторы, такие как +, -, \* и /.

Подзапросы в фильтрах:

```sql
... where film_id = (SELECT film_id FROM film WHERE title = ’RIVER OUTLAW’) ...
```

Двухсторонний диапазон. Сначала нижняя граница включительно.

```sql
WHERE rental date BETWEEN '2005-06-14 AND '2005-06-16';
```

```sql
WHERE amount BETWEEN 10.0 AND 11.99;
WHERE last name BETWEEN 'FA' AND 'FR' - FRA не войдет. Можно 'FRB'
```

множество IN, NOT IN - WHERE rating IN ('G', 'PG');  
первая буква Q - WHERE left(last name, 1) = 'Q';  
регулярки: WHERE last\_name REGEXP '^\[QY\]'  
 WHERE return\_date IS NULL; (is not null) Если WHERE return\_date = NULL; - ошибки не будет, но пустая выборка  
on результат BETWEEN 180 AND 240;  
exist - наличие хотя-бы одного значения в подмножестве

```sql
SELECT с.first_name, c.last_name 
FROM customer c WHERE EXISTS
    (SELECT 1 FROM rental r 
    WHERE r.customer_id = c.customer id 
    AND date(r.rental date) < '2005-05-25');
```