SQL Syntax
I miei comandi SQL più usati
DELETE from Contact where contactid=139 UPDATE tabella1 SET dato1 = nuovo_valore WHERE dato1=5
SELECT COUNT(*) FROM Contact where userid=24 SELECT COUNT(DISTINCT store_name) FROM Store_Information SELECT * FROM tabella1 ORDER BY dato1 SELECT SUM(dato1) FROM tabella1 SELECT AVG(dato1) FROM tabella1 SELECT MAX(dato1) FROM tabella1 SELECT MIN(dato1) FROM tabella1
INSERT INTO tabella1 (dato1, dato2, dato3) VALUES (valore1, valore2, valore3)
SELECT Contact.ContactId FROM ContactGroup INNER JOIN ContactGroupMember ON ContactGroupMember.ContactGroupId = ContactGroup.ContactGroupId INNER JOIN Contact ON Contact.ContactId = ContactGroupMember.ContactId WHERE ContactGroup.ContactGroupId = :groupId
Qualche query annidata di SELECT e DELETE
select * from MessageRecipient where MessageId IN (select MessageId from Message m where m.Userid=27) delete from MessageRecipient where MessageId IN (select MessageId from Message m where m.Userid=27)
Piccola nota nella delete con heidy sql 4.0 e db mysql 5 mi da errore di sintassi se uso gli alias per le tabelle
Per la sintassi delle join si possono vedere su questo link
Store procedure
Un bellissima store procedure fatta da Simone che utilizza anche variabili dato che con l'if non funzionava il codice complesso
BEGIN SET @closed = (select count(d.`deal_id`) from deals as d WHERE d.`deal_id` = deal_id AND CURRENT_TIMESTAMP() > DATE_ADD(DATE_ADD(d.`start_date`,INTERVAL d.`deadline_hours` HOUR),INTERVAL d.`deadline_minutes` MINUTE)); IF (@closed = 0) THEN SELECT d.`deal_id`, d.`expiration_date`, i.`image_path`, id.`type` FROM deals d, images i, image_deal id WHERE d.`deal_id` = deal_id AND id.`id_deal` = d.`deal_id` AND id.`id_image` = i.`id_image` AND id.`TYPE` = "open" ; ELSE SELECT d.`deal_id`, d.`expiration_date`, i.`image_path`, id.`type` FROM deals d, images i, image_deal id WHERE d.`deal_id` = deal_id AND id.`id_deal` = d.`deal_id` AND id.`id_image` = i.`id_image` AND id.`TYPE` = "closed" ; END IF; END
versione della pagina: 10, ultima modifica: 16 Jun 2010 09:24