paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL - Usefull selects

This post resumes several of my sql statments that I have used during these years. In some cases I details more information about the use of them
The post is continuing update and it will be push on the top of mysql list, every time I'll add a new statment.
Note: Some old descriptions are in Italian language.

  • Select to check if a day is for weekday

  • I have these fields that can have only the value 0/1.
    +--------+---------+-----------+----------+--------+----------+--------+
    | monday | tuesday | wednesday | thursday | friday | saturday | sunday |
    +--------+---------+-----------+----------+--------+----------+--------+
    |      0 |       1 |         1 |        0 |      1 |        0 |      1 |
    |      1 |       1 |         0 |        0 |      0 |        1 |      0 |
    |      1 |       0 |         1 |        0 |      1 |        0 |      1 |
    |      1 |       0 |         0 |        1 |      0 |        0 |      1 |
    |      0 |       1 |         1 |        1 |      0 |        0 |      0 |
    |      1 |       1 |         1 |        1 |      1 |        1 |      1 |
    |      0 |       0 |         1 |        0 |      0 |        1 |      0 |
    |      1 |       1 |         0 |        0 |      1 |        1 |      1 |
    +--------+---------+-----------+----------+--------+----------+--------+
    

    I need to check if a day, for example 2013-08-30, is on true/false for own day of week.
    step 1 I create this select statment

    SELECT SUBSTRING(b.completeWeek,INSTR(b.completeWeek, CONCAT(b.dayWeek,'-'))+2,1)
    FROM (
          SELECT CONCAT('1','-',a.monday,'_',
                    '2','-',a.tuesday,'_', 
                    '3','-',a.wednesday,'_',
                    '4','-',a.thursday,'_',
                    '5','-',a.friday,'_',
                    '6','-',a.saturday,'_',
                    '7','-',a.sunday) as completeWeek,
                    (select DAYOFWEEK('2013-08-30') from DUAL) as dayWeek
        FROM service_calendar as a
       WHERE weekly_event = 1) as b
    

    which return this

    +-----------------------------+---------+
    | completeWeek                | dayWeek |
    +-----------------------------+---------+
    | 1-0_2-1_3-1_4-0_5-1_6-0_7-1 |       5 |
    | 1-1_2-1_3-0_4-0_5-0_6-1_7-0 |       5 |
    | 1-1_2-0_3-1_4-0_5-1_6-0_7-1 |       5 |
    | 1-1_2-0_3-0_4-1_5-0_6-0_7-1 |       5 |
    | 1-0_2-1_3-1_4-1_5-0_6-0_7-0 |       5 |
    | 1-1_2-1_3-1_4-1_5-1_6-1_7-1 |       5 |
    | 1-0_2-0_3-1_4-0_5-0_6-1_7-0 |       5 |
    | 1-1_2-1_3-0_4-0_5-1_6-1_7-1 |       5 |
    +-----------------------------+---------+
    


    step 2 I put that statment into the final where condition

    SELECT COUNT(*)
    FROM (
          SELECT CONCAT('1','-',a.monday,'_',
                    '2','-',a.tuesday,'_', 
                    '3','-',a.wednesday,'_',
                    '4','-',a.thursday,'_',
                    '5','-',a.friday,'_',
                    '6','-',a.saturday,'_',
                    '7','-',a.sunday) as completeWeek,
                    (select DAYOFWEEK('2013-08-30') from DUAL) as dayWeek
             FROM service_calendar as a
            WHERE weekly_event = 1) as b
    WHERE SUBSTRING(b.completeWeek,INSTR(b.completeWeek, CONCAT(b.dayWeek,'-'))+2,1)=0
    


  • Select Range
  • I have this data (Note: id 37) and i want checking if a date is into one (or more) ranges

    +----+------------+------------+
    | id | start_date | end_date   |
    +----+------------+------------+
    | 21 | 2013-06-01 | 2013-09-01 |
    | 32 | 2013-01-01 | 2013-05-30 |
    | 33 | 2013-12-20 | 2013-12-31 |
    | 34 | 2013-01-01 | 2013-06-01 |
    | 35 | 2013-06-02 | 2013-12-31 |
    | 36 | 2013-01-01 | 2013-12-31 |
    | 37 | 2013-12-15 | 2013-04-02 |
    +----+------------+------------+
    

    This is the select

    SELECT ssr.start_date,
           ssr.end_date
    FROM
        (SELECT CONCAT(YEAR(CURDATE()),'-',LPAD(start_month,2,0),'-',LPAD(start_day,2,0)) as start_date,
                CONCAT(YEAR(CURDATE()),'-',LPAD(end_month,2,0),'-',LPAD(end_day,2,0)) as end_date
           FROM service_season_ranges
          WHERE CONCAT(LPAD(start_month,2,0),LPAD(start_day,2,0)) < CONCAT(LPAD(end_month,2,0),LPAD(end_day,2,0))
          UNION
         SELECT CONCAT(YEAR(CURDATE()),'-',LPAD(1,2,0),'-',LPAD(1,2,0)) as start_date,
                CONCAT(YEAR(CURDATE()),'-',LPAD(end_month,2,0),'-',LPAD(end_day,2,0)) as end_date
           FROM service_season_ranges
          WHERE CONCAT(LPAD(start_month,2,0),LPAD(start_day,2,0)) > CONCAT(LPAD(end_month,2,0),LPAD(end_day,2,0))
          UNION
         SELECT CONCAT(YEAR(CURDATE()),'-',LPAD(start_month,2,0),'-',LPAD(start_day,2,0)) as start_date,
                CONCAT(YEAR(CURDATE()),'-',LPAD(12,2,0),'-',LPAD(31,2,0)) as end_date
           FROM service_season_ranges
          WHERE CONCAT(LPAD(start_month,2,0),LPAD(start_day,2,0)) > CONCAT(LPAD(end_month,2,0),LPAD(end_day,2,0))                      
        ) as ssr
    WHERE (
          (
            (UNIX_TIMESTAMP('".$startDateInput."') between UNIX_TIMESTAMP(ssr.start_date) AND UNIX_TIMESTAMP(ssr.end_date)) OR
            (UNIX_TIMESTAMP('".$endDateInput."') between UNIX_TIMESTAMP(ssr.start_date) AND UNIX_TIMESTAMP(ssr.end_date))
          ) 
          OR
          (
            (UNIX_TIMESTAMP(ssr.start_date) between UNIX_TIMESTAMP('".$startDateInput."') AND UNIX_TIMESTAMP('".$endDateInput."')) OR
            (UNIX_TIMESTAMP(ssr.end_date)   between UNIX_TIMESTAMP('".$startDateInput."') AND UNIX_TIMESTAMP('".$endDateInput."'))
          )
         )
    


  • Select per dati ad albero
  • Ho utilizzato questa select per creare un menu' ad albero, con profondita' fino a 4 livelli.
    Questa la tabella
    CREATE TABLE category(
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    parent INT DEFAULT NULL);
    


    e questa la select di esempio

    SELECT t1.name AS lev1, 
           t2.name as lev2, 
           t3.name as lev3, 
           t4.name as lev4
    FROM category AS t1
    LEFT JOIN category AS t2 ON t2.parent = t1.category_id
    LEFT JOIN category AS t3 ON t3.parent = t2.category_id
    LEFT JOIN category AS t4 ON t4.parent = t3.category_id
    WHERE t1.name = 'ELECTRONICS';
    



  • Modifiche di n-righe in tabella

  • Questa istruzione di update permette di modificare solo alcune righe di una tabella (nell'esempio 790) iniziando il conteggio dalla riga 50
    UPDATE tabella_paolo SET ID_01=915
     WHERE ID_02 IN (
         SELECT ID_02 FROM (
             SELECT ID_02 FROM tabella_paolo
             LIMIT 50, 790
         ) temp
     );
    


    In quest'altra select, l'obiettivo e' quello di estrarre 5 elementi appartenente ad un insieme (Promotion) dando priorita' a quelli che in questo insieme possedevano una determinata carrateristica (essere parte dell'insieme di un determinato Macrosegmento di quella Promotion - id 129). Nel caso in cui il numero di questi elementi non fosse sufficiente per completare i 5 richiesti, completavo la lista con elementi random tra quelli che non fanno parte di quel Macrosegmento per quella Promotion).
    Da notare l'uso del prefisso "1-" e "2-" per ordinare.

    SELECT  uni.promotion_id,
            uni.promotion_id_order FROM
    (
     SELECT promotion.ID AS promotion_id,
            CONCAT('1-',promotion.ID) AS promotion_id_order
      FROM promotion
     WHERE promotion.MACROSEGMENTO_ID = (SELECT promotion.MACROSEGMENTO_ID
                                           FROM promotion
                                          WHERE promotion.ID=129)
       AND promotion.ID!=129
       AND promotion.FECHA_CADUCIDAD < NOW()
    UNION
     SELECT casu.promotion_id AS promotion_id,
            CONCAT('2-',casu.promotion_id) AS promotion_id_order
       FROM (
               SELECT promotion.ID AS promotion_id
                 FROM promotion
                WHERE promotion.MACROSEGMENTO_ID != (SELECT promotion.MACROSEGMENTO_ID
                                                     FROM promotion
                                                    WHERE promotion.ID=129)
                  AND promotion.FECHA_CADUCIDAD > NOW()
             ORDER BY RAND() LIMIT 5
            ) as casu 
    ) AS uni
    ORDER BY uni.promotion_id_order ASC
    LIMIT 0,5;
    


    Quest'altra select deriva dalla precedente, con l'aggiunta dell'ordinamento percentuale e dalla diversa condizione di appartenenza degli elementi da estrarre (non mi soffermo sulle tabelle e le condizioni di Join implementate

    SELECT  uni.promotion_id,
            uni.promotion_id_order
      FROM(
    	SELECT perc.promotion_id AS promotion_id,
                   CONCAT('1-',perc.promotion_id) AS promotion_id_order
    	FROM (
    		SELECT promotion.idpromotion AS promotion_id,
    		       COUNT(*) AS TOTA,
    		       (100*(COUNT(*)/(SELECT COUNT(*)
    					 FROM invoices 
    					 LEFT JOIN purchase USING (idinvoices)
    					 LEFT JOIN promotion USING (idpromotion)
    					WHERE invoices.status='AUTHORISED'
    					  AND promotion.idpromotion!=129
    					  AND promotion.fecha_caducidad < NOW()
    					  AND invoices.telefono IN 
    							(SELECT DISTINCT(invoices.telefono)
    							  FROM invoices 
    							  LEFT JOIN purchase USING (idinvoices)
    							  LEFT JOIN promotion USING (idpromotion)
    							WHERE invoices.status='AUTHORISED'
    							  AND promotion.idpromotion=129
    							  AND promotion.fecha_caducidad < NOW())))) AS PERC
    		  FROM invoices 
    		  LEFT JOIN purchase USING (idinvoices)
    		  LEFT JOIN promotion USING (idpromotion)
    		WHERE invoices.status='AUTHORISED'
    		  AND promotion.idpromotion!=129
    		  AND promotion.fecha_caducidad < NOW()
    		  AND invoices.telefono IN 
    					(SELECT DISTINCT(invoices.telefono)
    					  FROM invoices 
    					  LEFT JOIN purchase USING (idinvoices)
    					  LEFT JOIN promotion USING (idpromotion)
    					WHERE invoices.status='AUTHORISED'
    					  AND promotion.idpromotion=129
    					  AND promotion.fecha_caducidad < NOW())
    		GROUP BY promotion.idpromotion
    		ORDER BY PERC DESC
               ) AS perc
    UNION
     SELECT casu.promotion_id AS promotion_id,
            CONCAT('2-',casu.promotion_id) AS promotion_id_order
       FROM (
    	   SELECT idpromotion AS promotion_id,
    	     FROM promotion
    	    WHERE idpromotion!=129
    	      AND fecha_caducidad < NOW()
             ORDER BY RAND() LIMIT 5
            ) as casu
    ) AS uni
    ORDER BY uni.promotion_id_order ASC
    LIMIT 0,5
    



  • Select da UPPER a LOWER campi e tabelle

  • In questa select creo le istruzioni di ALTER e RENAME table per rinominare da maiuscolo a minuscolo, prima i campi delle tabelle e poi le tabelle stesse. Notare l'uso di information_schema.columns e information_schema.tables per recuperare le informazioni rispettivamente delle colonne e delle tabelle dello schema "paolo"
    Per i nomi dei campi
    SELECT CONCAT('ALTER TABLE ', table_name, ' CHANGE ', column_name, ' ', LOWER(column_name), ' ', column_type, ' ', extra,
                          CASE WHEN IS_NULLABLE = 'YES' 
                          THEN  ' NULL' 
                          ELSE ' NOT NULL' 
                        END, ';') AS line 
      FROM information_schema.columns
     WHERE table_schema = 'paolo' 
      AND data_type IN ('char', 'varchar','INT', 'TINYINT', 'datetime','text','double','MEDIUMTEXT') 
    ORDER BY line
    

    Per le tabelle

    select concat('rename table ', table_name, ' to ' , lower(table_name) , ';') 
      from information_schema.tables 
     where table_schema = 'paolo'