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

Первые пять пользователей пометить как удаленные

  • автор:

Форум пользователей MySQL

Всем привет! Возникла ситуация не удалять ничего а помечать как удаленные. Собственно вопрос как это можно сделать с помощью триггеров, чтоб он помечал is_delete=1 и не удалял при DELETE
И вопрос можно ли чтоб он не учитывался при SELECT UPDATE это глупость и сказа конечно. Но это решило бы все проблемы.

Отредактированно nekulin (10.10.2012 16:30:44)

#2 10.10.2012 16:49:04

vasya Архат Откуда: Орел Зарегистрирован: 07.03.2007 Сообщений: 5827

Re: не удалять а помечать как удаленные

nekulin написал:

Всем привет! Возникла ситуация не удалять ничего а помечать как удаленные. Собственно вопрос как это можно сделать с помощью триггеров, чтоб он помечал is_delete=1 и не удалял при DELETE

А почему не сделать update вместо delete?

nekulin написал:

И вопрос можно ли чтоб он не учитывался при SELECT UPDATE это глупость и сказа конечно. Но это решило бы все проблемы.

Не понял смысл предложения.

#3 10.10.2012 17:07:56

nekulin Завсегдатай Зарегистрирован: 06.04.2012 Сообщений: 56

Re: не удалять а помечать как удаленные

vasya написал:
nekulin написал:

Всем привет! Возникла ситуация не удалять ничего а помечать как удаленные. Собственно вопрос как это можно сделать с помощью триггеров, чтоб он помечал is_delete=1 и не удалял при DELETE

А почему не сделать update вместо delete?

Потому что придется переделывать много функционала

nekulin написал:

И вопрос можно ли чтоб он не учитывался при SELECT UPDATE это глупость и сказа конечно. Но это решило бы все проблемы.

Не понял смысл предложения.

Это глупый вопрос, но все же.
если мы у записи указали is_delete=1 то он не должен ее выдавать в результате при select and update

#4 10.10.2012 23:31:30

vasya Архат Откуда: Орел Зарегистрирован: 07.03.2007 Сообщений: 5827

Re: не удалять а помечать как удаленные

nekulin написал:

Потому что придется переделывать много функционала

А придется.
Можно попробовать извратиться:
тригерр before delete добавляет/изменяет данные во второй таблице, после чего вылетает с ошибкой (signal), т.о. отменяя удаление.
во второй таблице триггер изменяет данные в первой (is_delete=1)

Но это вариант — гланды через задницу.

nekulin написал:

Это глупый вопрос, но все же.
если мы у записи указали is_delete=1 то он не должен ее выдавать в результате при select and update

select . where is_delete=0;
Или, если не хотите переделывать функционал, то можно попробовать через view, но там есть масса ограничений.

#5 10.10.2012 23:36:21

nekulin Завсегдатай Зарегистрирован: 06.04.2012 Сообщений: 56

Re: не удалять а помечать как удаленные

vasya написал:
nekulin написал:

Потому что придется переделывать много функционала

А придется.
Можно попробовать извратиться:
тригерр before delete добавляет/изменяет данные во второй таблице, после чего вылетает с ошибкой (signal), т.о. отменяя удаление.
во второй таблице триггер изменяет данные в первой (is_delete=1)

Но это вариант — гланды через задницу.

Спасибо, придеться править код (

nekulin написал:

Это глупый вопрос, но все же.
если мы у записи указали is_delete=1 то он не должен ее выдавать в результате при select and update

select . where is_delete=0;
Или, если не хотите переделывать функционал, то можно попробовать через view, но там есть масса ограничений.

Вьюшка не дело.
select . where is_delete=0; Это понятно что через where Я имел виду просто select без явного указания where is_delete=0/ В чем смысл, чтоб не лезсть в код и недополнять условием дополнительным.

Урок 1. Первые SQL запросы

Добро пожаловать на первый урок по реляционным базам данных и языку SQL.

Реляционные базы данных представляют собой набор таблиц с информацией.
Вроде такой:

Таблица products

id name count price
1 Телевизор 3 43200.00
2 Микроволновая печь 4 3200.00
3 Холодильник 3 12000.00
4 Роутер 1 1340.00
5 Компьютер 0 26150.00
Таблица users

id first_name last_name birthday age
1 Дмитрий Иванов 1996-12-11 20
2 Олег Лебедев 2000-02-07 17
3 Тимур Шевченко 1998-04-27 19
4 Светлана Иванова 1993-08-06 23
5 Олег Ковалев 2002-02-08 15
6 Алексей Иванов 1993-08-05 23
7 Алена Процук 1997-02-28 18

Каждая таблица состоит из столбцов и строк.

Посмотрим внимательней на таблицу products, которая хранит данные о товарах в интернет-магазине. Таблица содержит 4 столбца: id, name, count и price. Каждый из столбцов отвечает за какой-то определенный тип информации: id — это уникальный номер товара, name — его имя, count — количество, price — цена.

Строка отвечает за конкретный товар в таблице. Если мы посмотрим на третью строку, то найдем там «Холодильник» с ценой 12 000 рублей в количестве 3 штук.

Другая таблица — это users, которая хранит данные о пользователях в системе. В таблице 5 столбцов: также уникальный номер пользователя id, имя, фамилия, возраст — age и дата рождения — birthday.

Как я уже говорил, каждый столбец отвечает за какую-то информацию и эта информация относится к определенному типу данных. Столбцы first_name и last_name строковые, age и id содержат числа, а birthday — дату.

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

А вот записи таблицы (или строки) заполняются в процессе её использования. Поэтому столбцов у нас жестко 5. А строк может быть сколько угодно. Зарегистрировался пользователь на сайте — добавили строку. Привезли новые товары в магазин — таблица растет.

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

SQL — это язык общения с базами данных.

Давайте попробуем получить информацию из таблицы users. Для этого надо написать и выполнить такой SQL-запрос:

SELECT * FROM users

Получили всех пользователей из таблицы users:

Результат выполнения SQL запроса

id first_name last_name birthday age
1 Дмитрий Иванов 1996-12-11 20
2 Олег Лебедев 2000-02-07 17
3 Тимур Шевченко 1998-04-27 19
4 Светлана Иванова 1993-08-06 23
5 Олег Ковалев 2002-02-08 15
6 Алексей Иванов 1993-08-05 23
7 Алена Процук 1997-02-28 18

Рассмотрим SQL запрос подробнее.

Оператор SELECT говорит, что мы будем извлекать данные. После него идет список столцов, которые мы хотим получить. Если указать звездочку (*), как у нас, то получим все столбцы в том порядке, в котором они определены в таблице: id, first_name, last_name и тд. Далее идет конструкция FROM users, которая буквально означает ИЗ users.

То есть вся SQL конструкция читается как ВЫБРАТЬ все столбцы ИЗ таблицы users.

Теперь вместо звездочки напишем: last_name, first_name, birthday, чтобы у нас получился такой SQL-запрос:

SELECT last_name, first_name, birthday FROM users

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

Результат выполнения SQL запроса

id last_name first_name birthday
1 Иванов Дмитрий 1996-12-11
2 Лебедев Олег 2000-02-07
3 Шевченко Тимур 1998-04-27
4 Иванова Светлана 1993-08-06
5 Ковалев Олег 2002-02-08
6 Иванов Алексей 1993-08-05
7 Процук Алена 1997-02-28

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

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

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

SELECT last_name, first_name, birthday FROM users WHERE age > 18

Если его выполнить, то мы получим список пользователей которым уже исполнилось 19 лет:

Результат выполнения SQL запроса

id last_name first_name birthday
1 Иванов Дмитрий 1996-12-11
3 Шевченко Тимур 1998-04-27
4 Иванова Светлана 1993-08-06
6 Иванов Алексей 1993-08-05

Конструкция WHERE позволяет фильтровать исходные данные в соответствии с нашими условиями. В данном случае мы получаем данные из таблицы users ГДЕ (WHERE) в столбце age значение больше 18.

Так как age — это числовой столбец, то его уместно сравнивать с числами. Если заменить знак больше на равно и снова запустить, то получим всех 18 летних пользователей. А если поставим >= , то получим совершеннолетних пользователей:

SELECT last_name, first_name, birthday FROM users WHERE age >= 18
Совершеннолетние пользователи

id last_name first_name birthday
1 Иванов Дмитрий 1996-12-11
3 Шевченко Тимур 1998-04-27
4 Иванова Светлана 1993-08-06
6 Иванов Алексей 1993-08-05
7 Процук Алена 1997-02-28

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

А теперь самое время потренироваться в SQL, для этого к каждому уроку привязано несколько задач, которые вы можете решать в специальном тренажере прямо на сайте.

Следующий урок

Урок 2. Составные условия

В этом уроке вы узнаете как формировать сложные условия в SQL-запросах с использованием операторов AND и OR.

Полный курс с практикой

  • 57 уроков
  • 261 задание
  • Сертификат
  • Поддержка преподавателя
  • Доступ к курсу навсегда

Как удалить первые 10000 записей в mysql phpmyadmin

А * зачем ? phpclub.ru/mysql/doc/delete.html и вы определитесь все id которые меньше 10к или первые 10к. это разные вещи, особенно если среди id есть пропуски в нумерации. в принципе я оба варианта написал

5 ноя 2016 в 11:21

@Mike, может добавите ответом? Я бы проголосовал за имеющиеся ответы, раз уж вопрос поднят Духом сообщества, но они меня не устраивают.

8 ноя 2019 в 18:37

3 ответа 3

Сортировка: Сброс на вариант по умолчанию

Важно понимать, что именно нужно сделать — удалить первые 10000 id или все id, которые меньше 10000.

Для первого варианта необходимо использовать код ниже:

delete from test order by id limit 10000 

Для второго — этот:

delete from test where id 

Отслеживать
ответ дан 10 июл 2020 в 7:11
Novitskiy Denis Novitskiy Denis
1,532 11 11 серебряных знаков 27 27 бронзовых знаков

Для удаления первых 10тыс записей, выполните запрос вида:

DELETE FROM table WHERE condition LIMIT 10000 

Отслеживать
ответ дан 6 ноя 2016 в 10:26
464 2 2 серебряных знака 4 4 бронзовых знака
Это удаление 10000 случайных строк.
6 ноя 2016 в 12:07

delete from [таблица] where [id] in (select [id] from [таблица] order by [id] ASC limit 10000) [id] - Ваше уникальное поле [таблица] - Название таблицы ASC - с начала (id - число) 
delete from customers where CustomerID in (select CustomerID from Customers order by CustomerID ASC limit 2) 

nurgizakkuzhin / Задача и теория по SQL, MySQL, PostgreSQL и базам даннх вообще.md

Save nurgizakkuzhin/23cdfaf48c632705b800b04d96cbb4eb to your computer and use it in GitHub Desktop.

Задачка и теория по SQL (изучаем базы данных)

Этот урок переехал по адресу https://github.com/codedokode/pasta/blob/master/db/databases.md . Копия ниже устарела и не будет больше обновляться.

Что такое базы данных

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

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

Есть разные виды баз данных, но этот урок посвящен базам данных, поддерживающим язык SQL. В них любые операции над данными - добавление, удаление, поиск - делаются с помощью отправки SQL-запросов. Сам язык достаточно простой и запросы на нем напоминают обычные предложения на английском языке. Ну к примеру, запрос на удаление из БД пользователя с email ivan@example.com выглядит так: DELETE FROM users WHERE email = 'ivan@exaple.com' . Если знать английский ("удалить из пользователей где email равен 'ivan@example.com'"), то смысл запроса легко понять, даже не зная SQL. Запросы может отправлять как сам разработчик вручную, так и написанная им программа.

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

Программы, реализующие базу данных

Есть разные программы, которые позволяют создавать и управлять базой данных. Они называются СУБД (системы управления БД). Из бесплатных самые известные - это MySQL и PostgreSQL. MySQL (в 2016 году) более распространена, а в PostgreSQL больше интересных нестандартных возможностей (а также, считается что она более полно поддерживает стандарт).

Есть и коммерческие СУБД - например, MSSQL, Oracle DB.

Наконец, есть еще встраиваемые СУБД, которые используются не отдельно, а встраиваются в другую программу и используются только ей. Ну например, (в 2016 году) встроенную бесплатную СУБД SQLite использовали браузер Chrome, который хранил с ее помощью историю и закладки, Skype для хранения сообщений и множество мобильных приложений под Android и iOS.

Со всеми этими БД можно работать, зная язык SQL.

Устройство базы данных

База данных хранит данные в таблицах. Таблицы создает разработчик, и обычно каждая из них предназначается для своей сущности - например, таблица со списком пользователей, таблица тем на форуме, таблица сообщений на форуме. Таблица состоит из колонок, каждая из которых имеет определенных тип (число, строка). Ну к примеру, таблица для хранения информации о пользователях форума может выглядеть так:

id name email password_hash salt registered
1 Администратор admin@example.com abbs09s7s6s6 gt9xbxvx4x30 2014-08-02
2 Иван ivan@example.com hd6bc00c8c7c665ce gs65s4s4sb0x 2015-01-01

При регистрации скрипт добавляет в нее информацию о новом пользователе, а при логине - проверяет введенные email и пароль. Мы, конечно, в целях безопасности не храним в базе сами пароли в открытом виде, а получаем из них хеш с солью и сохраняем их в колонках password_hash и salt (по которым можно проверить правильность введенного при логине пароля, но нельзя восстановить его). Также, мы присваиваем каждому пользователю уникальный числовой идентификатор ( id ), который еще называют первичный ключ - это позволяет потом в других таблицах ссылаться на него (например, в таблице сообщений мы можем хранить id автора сообщения, по которому можно достать информацию о нем).

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

id author_id posted text
1 1 2014-08-03 Добро пожаловать на наш форум! Жду ваших сообщений.
2 1 2014-08-04 Что-то никого нету.
3 1 2014-08-05 Ни души.
4 2 2015-01-01 Всем привет. Я новый тут.

Здесь колонка id хранит идентификатор сообщения, author_id - идентификатор автора сообщения (по которому можно найти его имя, email в первой таблице), posted - дату отправки и text - тело сообщения. Первые 3 сообщения оставил Администратор, а четвертое - Иван.

Все операции с таблицами, включая их создание и заполнение делаются с помощью запросов на языке SQL. Подробнее о том, как это делать, написано ниже по ссылкам.

Работа с базой данных

Как правило сам сервер базы данных (программа, которая обеспечивает ее работу) не имеет своего интерфейса и каких-то окошек, кнопочек, чтобы с ним взаимодействовать. Управление базой данных делается с помощью запуска программы-клиента, который подсоединяется к серверу, пересылает ему SQL запросы и выводит полученные ответы. Одновременно к БД может подсоединиться несколько клиентов.

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

Те, кто освоил основы, могут использовать и более сложные программы-клиенты с графическим интерфейсом. Они могут отображать информацию из базы данных в виде таблиц, перемещаться по ним, менять значения в них. При этом можно запускать и вручную написанные SQL запросы. Я не буду тут писать названия конкретных программ, но их легко найти по словам вроде "mysql gui", "mysql admin", "postgresql gui" и так далее. Я бы советовал сначала научиться работать исключительно в клиенте командной строки, а только потом переходить к этим программам.

Наконец, подсоединяться и отправлять запросы к БД можно из программы. Например, скрипт на языке PHP может таким образом выбирать данные из базы и отображать на веб-странице. Для этого нужна библиотека или расширение-клиент для базы данных. В PHP есть даже 2 расширения для этого (PDO и MySQLi), я рекомендую использовать расширение PDO, так как оно поддерживает исключения, за счет чего при какой-то ошибке проще получить информацию о ней.

Изучаем базы данных

Теория и туториалы для начинающих:

  • основы и туториал по MySQL (немного старый, но еще актуальный): http://phpclub.ru/mysql/doc/tutorial.html
  • руководство на русском по PostgreSQL: https://postgrespro.ru/docs/postgresql
  • большой учебник по SQL: http://www.pyramidin.narod.ru/rusql/index.htm

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

  • отношения между таблицами в БД: http://jtest.ru/bazyi-dannyix/sql-dlya-nachinayushhix-chast-3.html
  • внешние ключи: http://denis.in.ua/foreign-keys-in-mysql.htm

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

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

  • https://habrahabr.ru/post/129195/
  • https://habrahabr.ru/post/254773/
  • http://club.shelek.ru/viewart.php?id=177
  • http://alexvolkov.ru/database-normalizatio.html

Поскольку нормализация - это очень важная тема, я наверно напишу еще ниже про нее своими словами, на случай, если кто-то не прочел статьи, но не все понял.

А пока еще несколько полезных ссылок:

  • сборник запросов на все случаи жизни (англ): http://www.artfulsoftware.com/infotree/queries.php
  • таблицы отличий в диалектах SQL в разных СУБД (англ): http://en.wikibooks.org/wiki/SQL_dialects_reference
  • манга-учебник про SQL в картинках: http://www.nostarch.com/mg_databases.htm

Под Windows в командной строке не работают русские буквы

Надо выполнить команду SET NAMES cp866; после соединения: http://gahcep.github.io/blog/2013/01/05/mysql-utf8/

Еще ссылки на тему кодировок при соединении с mysql из php:

  • http://fstrange.ru/coder/mysql/kodirovka-krakozyably.html
  • http://phpfaq.ru/charset

На что стоит обратить внимание?

Вот список понятий, которые стоит знать если ты хочешь очень хорошо разбираться в MySQL:

  • управление базами данных: CREATE DATABASE, DROP DATABASE, SHOW DATABASES
  • управление таблицами: CREATE TABLE, ALTER TABLE, DROP TABLE, SHOW TABLES, SHOW CREATE TABLE, DESC table, TRUNCATE table
  • управление правами доступа: GRANT, SHOW GRANTS
  • типы колонок: ENUM, SET, CHAR, VARCHAR, TEXT, DATE, TIME, DATETIME, TIMESTAMP, INT, FLOAT, TINYINT, DECIMAL, MEDIUMTEXT, LONGTEXT. В чем разница между TIMESTAMP и DATETIME? Между FLOAT и DECIMAL? CHAR и VARCHAR?
  • DECIMAL — тип с фиксированной точностью. В отличие от FLOAT/DOUBLE, которые приближенные и могут терять знаки после запятой, DECIMAL хранит заданное число знаков. Используется например, для хранения суммы денег.
  • NULL и троичная логика (в БД NULL значит «неизвестно». Например, возраст пользователя неизвестен. Соответственно, все операции с NULL это учитывают: NULL + 5 тоже дает в итоге NULL (5 + неизвестное число дает неизвестное число), сравнение (NULL = NULL) возвращает ложь, чтобы проверить равно ли поле NULL надо использовать IS NULL/IS NOT NULL. http://ru.wikipedia.org/wiki/NULL_(SQL))
  • можно ли искать пустые поля условием WHERE x = NULL?
  • при создании таблицы можно сделать поля обязательными для заполнения, указав NOT NULL
  • SELECT/INSERT/DELETE/UPDATE
  • порядок выполнения запроса выборки: FROM+JOIN, WHERE, GROUP, HAVING, ORDER, LIMIT, SELECT (его надо знать наизусть)
  • REPLACE, INSERT IGNORE, INSERT .. ON DUPLICATE KEY UPDATE
  • выборка данных: DISTINCT, JOIN, ORDER BY, GROUP BY, HAVING, LIMIT
  • группировка и аггрегатные функции: GROUP BY, COUNT, MAX, MIN, AVG, SUM
  • транзакции: BEGIN, ROLLBACK, COMMIT
  • внешние ключи: FOREIGN KEY. Внешний ключ — это поле, которое хранит id записи в другой таблице
  • первичный ключ: естественный и искуственный
  • обычные и уникальные индексы (ключи)
  • оптимизация запросов, команда EXPLAIN
  • отличие InnoDB от MyISAM

Теория по проектированию БД

Чтобы уметь проектировать базы данных и новые таблицы, нужно знать следующее:

  • виды отношений между таблицами: один-к-одному, один-ко-многим, многие-ко-многим
  • принципы нормализации БД. В интернете можно найти статьи где "нормальные формы" объясняют простыми словами, например http://club.shelek.ru/viewart.php?id=311 или https://habrahabr.ru/post/193756/
  • способы хранения древовидных (иерархических) данных в БД. Ну например, это нужно для реализации дерева комментариев к статье или дерева категорий товаров в интернет-магазине. Есть такие паттерны: Adjacency List, Closure Path, Nested Sets, Materialized Path. Вот мой урок про них: https://github.com/codedokode/pasta/blob/master/db/trees.md
  • способы реализации наследования таблиц (когда есть похожие, но не одинаковые сущности с общим набором свойств: например Пользователи и Администраторы, или несколько видов приложений к сообщению: Видеозапись, Аудиозапись, Файл, Ссылка на сайт). Для таких случаев есть паттерны Single Table Inheritance, Concrete table Inheritance, Class Table Inheritance
  • паттерн EAV (Entity-Attribute-Value), описание на англ., на русском. Этот паттерн можно исплоьзовать в тех случаях, когда есть сущности с разным набором свойств, и свойства могут добавляться (например объявление: объявления о сдаче квартиры и продаже машины имеют разный набор свойств). Также, в интернете можно найти много обсуждений по поводу того, зло это или нет. Есть также альтернативные подходы, например в PostgreSQL можно использовать индексируемые hstore (англ.) или json (англ.) колонки

Вот цикл статей на Хабре, который подойдет в качестве вступления: 1-3, 4-6, 7-9, 10-13, 14-15, бонус

Чем отличаются движки для таблиц MyISAM и InnoDB?

  • http://rtfm.co.ua/mysql-otlichiya-mezhdu-myisam-i-innodb/
  • http://itif.ru/otlichiya-myisam-innodb/

Если кратко: MyISAM более простой и не поддеживает внешние ключи и транзакции. А они нужны почти всегда. Потому в 99% случаев тебе нужен InnoDB.

Индексы

SELECT a, b FROM table ORDER BY y LIMIT 10 

то без индекса MySQL вынуждена будет прочитать с диска в память миллион значений, отсортировать их только ради того, чтобы взять первые 10. Если же есть индекс по полю y (который хранит отсортированные по возрастанию значения этого поля) то MySQL просто возьмет из него первые 10 записей. Разница в скорости работы будет огромная.

Вот статьи для начинающих про индексы:

  • http://ruhighload.com/post/%D0%A0%D0%B0%D0%B1%D0%BE%D1%82%D0%B0+%D1%81+%D0%B8%D0%BD%D0%B4%D0%B5%D0%BA%D1%81%D0%B0%D0%BC%D0%B8+%D0%B2+MySQL
  • http://www.mysql.ru/docs/man/MySQL_indexes.html
  • http://habrahabr.ru/post/211022/

Если ты все прочел внимательно, ответь на вопрос, может ли индекс (если да, то какой) ускорить такие запросы:

  • SELECT * FROM table WHERE x <> 1
  • SELECT * FROM table WHERE x + y < 100
  • SELECT MAX(a) FROM table WHERE b = 2
  • SELECT * FROM table WHERE name LIKE '%Иван%'
  • SELECT * FROM table WHERE b = 1 AND a < 10

Задачка про лайки

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

  • ид пользователя
  • имя
  • лайков получено
  • лайков поставлено
  • взаимных лайков

Далее, выведи список всех пользователей, которые лайкнули пользователей A и B, но при этом не лайкнули пользователя C. Тут есть несколько вариантов решения.

Сложно? Ну ок, давай начнем с более простой задачи: просто выведи 5 самых популярных пользователей.

  • Если ты используешь несколько связанных друг с другом таблиц, связи необходимо пометить с помощью внешних ключей
  • Желательно на уровне БД запретить возможность ставить пользователю лайк другому пользователю дважды
  • Подсказка: эта задача решается без подзапросов
  • Подсказка: достаточно использовать всего 2 джойна и группировку

Усложненная (но более жизненная) задача про лайки

В воображаемой социальной сети есть Пользователи (id, имя), Фото (id, название, автор) и Комментарии К Фото (id, текст, автор, к какому Фото относится). Необходимо добавить возможность для Пользователей ставить лайки другим Пользователям, Фото или Комментариям К Фото. Нужно реализовать такие возможности:

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

Для начала, нужно решить задачу без оглядки на производительность. Очень желательно следовать принципам нормализации и помечать связи внешними ключами (а также на уровне Бд предотвратить возможность повторной отправки лайка). Далее, можно дополнить решение комментариями по поводу оптимизаций производительности.

Тут есть несколько вариантов решения.

Задачка про кинотеатр

Вот дополнительная, более сложная задачка. Есть кинотеатр, в нем идут фильмы. У фильма есть название, длительность (пусть для простоты будет 60, 90 или 120 минут), цена билета (в разное время и дни может быть разная), время начала сеанса (один фильм может быть показан несколько раз в разное время за разную цену). Также, есть информация о купленных билетах (номер билета, на какой сеанс).

  • составь грамотную нормализованную схему хранения этих данных в БД. Внеси в нее 4-5 фильмов, расписание на один день и несколько проданных билетов.

Сделай запросы, считающие и выводящие в понятном виде:

  • ошибки в расписании (фильмы накладываются друг на друга), отсортированные по возрастанию времени. Выводить надо колонки «фильм 1», «время начала», «длительность», «фильм 2», «время начала», «длительность».
  • перерывы больше или равные 30 минут между фильмами, выводятся по уменьшению длительности перерыва. Выводить надо колонки «фильм 1», «время начала», «длительность», «время начала второго фильма», «длительность перерыва».
  • список фильмов, для каждого указано общее число посетителей за все время, среднее число зрителей за сеанс и общая сумма сбора по каждому, отсортированные по убыванию прибыли. Внизу таблицы должна быть строчка «итого», содержащая данные по всем фильмам сразу.
  • число посетителей и кассовые сборы, сгруппированные по времени начала фильма: с 9 до 15, с 15 до 18, с 18 до 21, с 21 до 00:00. (то есть сколько посетителей пришло с 9 до 15 часов, сколько с 15 до 18 и т.д.).

Сложная задача про календарь

Решил предыдущие задачи и они слишком простые? Ок, давай возьмемся за действительно сложную задачу. Напиши SQL-код, выводящий календарь на текущий месяц в виде:

Пн Вт Ср Чт Пт Сб Вс
| | | | | 1 | 2 

3 | 4 | 5 | 6 | 7 | 8 | 9 10 | 11 | 12 | 13 | 14 | 15 | 16 17 | 18 | 19 | 20 | 21 | 22 | 23 24 | 25 | 26 | 27 | 28 | 29 | 30

  • Подсказка: ты можешь делать запросы без таблиц, например SELECT 2 + 3, 'Hello'
  • Подсказка: здесь не надо использовать циклы или процедуры
  • Подсказка: функции работы с датой и временем ты можешь найти тут http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html (англ.)
  • Подсказка: для сокращения объема кода ты можешь использовать переменные (создаются командой SET )

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *