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

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Вебмастеру (http://forum.oszone.net/forumdisplay.php?f=22)

- - Научите меня правильно строить сложные запросы к базе данных (http://forum.oszone.net/showthread.php?t=31177)

vadimiron

22-05-2004 00:25 213822

Пhедположим такая ситуация
Есть 2 таблицы: users, ids
В первой есть строки name, email
Во второй: name, id
Задача запроса: вывести name, email из первой таблицы когда name из 1 равен name из 2 и id равен 5

Я пишу:
select A.name, A.email from users A, ids B where A.name=B.name and B.ids=5

Но такой вариант не проходит
Подскажите плиззз где у меня ошибка

[s]Исправлено: vadimiron, 1:48 22-05-2004[/s]

Prisoner

22-05-2004 05:14 213823

Для MySql (есть стандарт, но его, имхо, мало кто придерживается или стандартов много :)):

Код:

select A.name, A.email from users as A, ids as B where (A.name=B.name) and (B.id=5)

На счет запросов понятно... непонятна организация данных. ID это уникальный номер пользователя? Тогда вторая таблица вообще не нужна, вторая таблица в большинстве таких случаев необходима для связи "1-n" - один ко многим. К примеру нужно хранить права конкретного человека на редактирование тех или иных частей сайта, файло или еще чего. То есть таких "еще чего" - много, вот и получается связь один ко многим. Вообще существует механизм (математический) по построению БД. Надо проанализировать рабочую область данных, выявить сущности (объекты области, скажем пользователь, страница и т. д.) ее атрибуты (они в последствии станут полями), связи (связи в последствии могут стать отдельными таблицами в зависимости от вида связи), построить ER-диаграмму (часто опционально, но жутко полезно), потом провести проверку на Нормальные Формы: 1НФ, 2НФ, 3НФ и "иногда" НФ Бойса-Кодда. В общем это надо до создания работать еще, правда на не сложных БД можно сделать все и так - просто интуитивно ясно где и как организовать таблицы и их поля, интуитивно избегнуть избыточности и интуитивно же понять где избыточность компенсируется быстродейсвием.

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

[s]Исправлено: Prisoner, 5:16 22-05-2004[/s]

vadimiron

22-05-2004 13:36 213824

Да я учил всё это, но с моим объёмом и сложностью базы данных, я не стал так серьёзно подходить к делу :)

Этот вариант тоже не проходит :(
В браузер выводится: Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource - то есть запрос почему то не правильно построен

Может я что с индексами начудил???

первая таблица: много информации о юзере (в том числе name, email-которые мне сейчас нужны, верней только email, так как name я уже знаю)
вторая таблица: три столбца по сессии: id-уникальный номер сессии, name-имя юзера, который открыл сессию, time-время открытия сессии

И вот как мне сделать подобный запрос к таким таблицам???

И ещё как правильно здесь было бы организовать индексы???

Добавлено:

УУУУУУпс :gigi:

у меня была одна лишняя скобка :biggrin:
Поэтому ничего не работало

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

И почему создатели PHP не сделали хотя простейшую проверку синтаксиса запросов mysql??
Тогда бы я полдня бы не ломал голову, хотя эо было всего лишь лишняя скобка

mar	22-05-2004 21:31 213825

1) насчет id (и всего остального - респект :) - согласна с Prisoner (соответственно, связь через id)
2) id скорее всего должен быть auto_increment и PRIMARY KEY (те генериться автоматически и быть первичным ключем - те тоже своего рода индексом)
3) я вообще предпочитаю стиль, когда свой такой id вводится в каждой таблице
4) в другой таблице надо соответственно ввести поле - что-то вроде user_id и на него посадить индекс
3) индексом стоит наградить все поля, по которым будут идти выборки, но следует иметь в виду, что при небольшом количестве записей работать они (индексы), при запросах все равно скорей всего не будут
4) Проверить что и как работает можно при помощи оператора EXPLAIN *
работать у тебя будет, кстати говоря и без индексов, но при увеличении объема базы работать будет все медленнее (для того индексы в бд и существуют, чтобы по ним быстро шерстить)
Кстати, в апрельском номере PHP inside
опубликован перевод очень неплохой статьи "Шаблоны баз данных в MySQL" Рассела Дайера. *

vadimiron

23-05-2004 01:54 213826

mar

id у меня не связан с юзером, id-это номер сессии, а уже в этой же таблице есть столбец name, который указывает на открывшего данную сессию пользователя

mar	23-05-2004 20:29 213827

vadimiron
я об этом и писала - подумай сам: какие поля занимают больше места? какие поля легче индексируются? наконец за уникальностью каких полей легче следить?
в твоем случае проще ихмо что-то вроде:
Первая таблица:

Код:

CREATE TABLE users (

 *id int(11) NOT NULL auto_increment, -- уникальный id 

 *name varchar(32) NOT NULL default '', -- логон

 *password varchar(32) NOT NULL default '', -- пароль

 *perms varchar(255) NOT NULL default 'user', -- права (если разные)

 *realname text NOT NULL, -- настоящее имя

 *email varchar(255) NOT NULL default '',

 *comments text, -- комментарии

 *created_by int(11) NOT NULL default '0', -- кем создана запись

 *is_enabled int(11) NOT NULL default '1', -- активна

 *deleted int(11) NOT NULL default '0', -- удалена

-- и всякое прочее по вкусу и необходимости

 *PRIMARY KEY *(id),

 *KEY email (email),

 *KEY username (name) 

 *-- и прочие ключи, какие там еще

) TYPE=MyISAM; -- или в зависимости от версии MySQL

Вторая таблица:

Код:

CREATE TABLE ids (

 *id int(11) NOT NULL auto_increment, -- уникальный id 

 *user_id, -- id юзера из первой таблицы

 *date timestamp(14) NOT NULL, -- время открытия сессии

 *PRIMARY KEY *(id),

 *KEY user_id (user_id),

 *KEY date (date)

) TYPE=MyISAM; -- или в зависимости от версии MySQL

[s]Исправлено: mar, 20:31 23-05-2004[/s]

vadimiron

24-05-2004 02:07 213828

Всё хорошо и красиво только у id я не смогу auto_increment поставить, так как id устанавливается функцией rand при заходе юзера, то есть я усвтанавливаю свои значения для id, auto_increment не подходит

Vlad Drakula

26-05-2004 22:06 213829

vadimiron
пример сложного запроса:

Код:

//чдение из базы информации о постах

$postlist=mysql_query('SELECT post.post,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;post.n as postn,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;post.date,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;post.ip,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;avatar.avatar,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.name,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.id,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.nposts,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.regdate,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.email,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.showemail,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.showip,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.signature,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.undername,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.nrewords,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.admin,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users.moder

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM post,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;users,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;avatar

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE post.topicid='.$topicdata['id'].'

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and users.id=post.userid

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and avatar.id=users.avatar

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY post.n

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;LIMIT '.$start.', '.$end.' ;');

vadimiron
скорее всего проблемма не в запросе, а в самом скрипте!

можеть выложить весь кусок кода?

vadimiron
ты не правильно понял смысл ошибки!

Prisoner

29-05-2004 01:14 213830

Ой, а можно я тоже "сложным" запросом похвастаюсь? :) А то самокритика ведет - 333:1 :).
             

Код:

SELECT DISTINCT

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `news`.`n_id`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `news`.`n_desc`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `news`.`n_dt`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `regions`.`r_name`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `news`.`n_cap`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `categories`.`c_name`

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `news`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `n_c`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `n_r`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `regions`,

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `categories`

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`news`.`n_id` = `n_c`.`n_id`) AND

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`news`.`n_id` = `n_r`.`n_id`) AND

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`news`.`n_dt` &lt;= \''.$DateB.'\') AND

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`news`.`n_dt` &gt;= \''.$DateE.'\') AND

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`n_r`.`r_id` = `regions`.`r_id`) AND

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`n_c`.`c_id` = `categories`.`c_id`) AND

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`n_r`.`r_id` = 1) AND

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`n_c`.`c_id`&gt;100) AND

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (`n_c`.`c_id`&lt;200)

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '.$QCatReg.'

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY

 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; `news`.`n_dt` DESC'

На счет проверки синтаксиса запроса нужно обращаться по адресу :). Зачем php об этом думать, если это не его задача? Другой вопрос, что он знает об ошибке последнего запроса, знает и молчит, заставит же его говорить об этом функция mysql_error().

Vlad Drakula

29-05-2004 02:24 213831

Prisoner
я думаю что у vadimiron в функцию mysql_fetch_row() передается не то чно нужно!

т.к. PHP выдает свою ошибку а не ошибку MySQL!

vadimiron

29-05-2004 02:28 213832

Vlad Drakula

Сенкс, но же написал что сам запрос был неправильно составлен, то есть с лишними скобками, и естественно mysql_fetch_row() выдавал ошибку, так как не получал нормальной инфы из запроса

Сейчас всё работает

Vlad Drakula

29-05-2004 02:29 213833

Prisoner
а что означает DISTINCT

Добавлено:

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

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

на мой взгляд это будет наиболее оптимальный вариант!

mar	29-05-2004 13:43 213834

Vlad Drakula

Цитата:

DISTINCT

- значит выбор несовпадающего

Prisoner

30-05-2004 17:09 213835

Выбор несовпадающего... угу. Вообще говоря

Цитата:

Цитата MySQL manual

Параметры (опции) DISTINCT, DISTINCTROW и ALL указывают, должны ли возвращаться дублирующиеся записи. По умолчанию установлен параметр (ALL), т.е. возвращаются все встречающиеся строки. DISTINCT и DISTINCTROW являются синонимами и указывают, что дублирующиеся строки в результирующем наборе данных должны быть удалены.

На счет организации данных. Исходя из того, что сессия в общем-то необходима одна ;), то получается, что можно сделать одну таблицу:

Код:

CREATE TABLE `users` (

 &nbsp;`u_id` TINYINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,

 &nbsp;`s_id` CHAR(32) NOT NULL,

 &nbsp;`s_init_time` TIMESTAMP(14) NOT NULL,

 &nbsp;`u_name` CHAR(100) NOT NULL,

 &nbsp;`u_mail` CHAR(50) NOT NULL,

 &nbsp;PRIMARY KEY (`u_id`),

 &nbsp;UNIQUE KEY `u_id` (`u_id`),

 &nbsp;UNIQUE KEY `s_id` (`s_id`),

 &nbsp;UNIQUE KEY `u_mail` (`u_mail`)

);

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

vadimiron

30-05-2004 21:36 213836

Не, у меня в голове не очень укладывается, как это всё в одну таблицу запихнуть, читсо логически: сессии-это что то очень динамическое, если юзер определённое время не был активен то есть не делал никаких действий, то сессия стирается, таким образом всё время какие то движения происходят там, что то добавляется, что то стирается, а таблица с юзерами статична, изменения редко бывают, а теперь представим, что у меня записано 1000 юзеров, и что, каждый раз придётся проверять все 1000 строк, чтобы узнать устарела ли сессия или нет

Добавлено:

Верней, чтобы узнать какие сессии стирать можно  а какие ещё рано

Prisoner

30-05-2004 22:21 213837

А как ты выявляешь факт устаревании сессии? Ну пусть какой-то такой факт есть. Скажем это время - пусть сессия выделяется на 5 часов (сразу прикинь на каком типе ресурсов это окажется незаменимым :)) и факт нажатия на ссылку "выход". В любом случае перед уничтожением сессии опустоши в таблице поле этой сессии для конкретного пользователя, по той логике, что была предложена выше это означает, что сессия закрыта. Еще ко всему, каждый раз когда делаешь это опустошай (и удаляй сессии PHP) поле сессий которые зарегестрированы более 5ти часов назад. Для этого достаточно выполнить всего два запроса.
Для шага (а)

Код:

UPDATE `users` SET `s_id` = '' WHERE `u_id` = какой_то_там

Для шага (б)

Код:

UPDATE `users` SET `s_id` = '' WHERE `s_init_time` < (CURRENT_TIMESTAMP() - 18000)

Причем следует знать, что первый в таблице столбец типа timestamp означивается в операциях INSERT, UPDATE текущей датой в том случае, если он явно не указан в этих операциях (есть еще уточняющие моменты, но их можно найти в мануале). Это нам не мешает, а даже наруку.

Время: 12:18.