Solte os arquivos aqui

Envio de SQL ( 0 ) x -

Configurações da página Clique na barra para rolar até o topo da página
Pressione Ctrl+Enter para executar consulta Pressione Enter para executar consulta
ascendente
descendente
Ordem:
Depurar SQL
Contagem
Ordem de execução
Tempo gasto
Ordenar por:
Agrupar consultas
Desagrupar consultas
Recolher Expandir Mostrar rastro Ocultar rastro Contagem Tempo gasto
Favoritos
Atualizar
Adicionar
Sem marcador(es)
Adicionar marcador
Opções
Definir padrão
Recolher Expandir Repetir consulta Editar Demonstrar SQL Perfil Marcadores Falha na consulta Banco de dados: Tempo de execução da consulta:
Modo de navegação
Personalizar modo de navegação.
Modo de navegação
Documentação Usar apenas ícones, apenas texto ou ambos. Restaurar valor padrão
Documentação Usar apenas ícones, apenas texto ou ambos. Restaurar valor padrão
Documentação Se exibe ao usuário o botão "exibir tudo(linhas)". Restaurar valor padrão
Documentação Número de linhas exibidas na visualização do resultado encontrado. Se o resultado conter mais linhas, os links 'Anterior' e 'Próximo' serão apresentados. Restaurar valor padrão
Documentação SMART - i.e. ordem decrescente para colunas do tipo TIME, DATE, DATETIME e TIMESTAMP, caso contrário, ordem ascendente. Restaurar valor padrão
Documentação Destacar linha apontada pelo cursor do mouse. Restaurar valor padrão
Documentação Destacar linhas selecionadas. Restaurar valor padrão
Documentação Restaurar valor padrão
Documentação Restaurar valor padrão
Documentação Repetir cabeçalhos a cada X células, 0 desativa esta funcionalidade. Restaurar valor padrão
Documentação Número máximo de caracteres apresentados em qualquer coluna não-numérica em modo de navegação. Restaurar valor padrão
Documentação Estes são os links de Editar, Copiar e Apagar. Restaurar valor padrão
Documentação Se os links de linha devem ser mostrados mesmo na ausência de chave única. Restaurar valor padrão
Documentação Ordenação predeterminada para tabelas com uma chave primária. Restaurar valor padrão
Documentação Quando navegar por tabelas, a organização de cada tabela será lembrada. Restaurar valor padrão
Documentação Para exibir as Opções Restaurar valor padrão
SELECT * FROM `proc`
Editar em linha ] [ Editar ] [ Demonstrar SQL ] [ Criar código PHP ] [ Atualizar ]
Textos completos db name type specific_name language sql_data_access is_deterministic security_type param_list returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8
Editar Editar Copiar Copiar Remover Remover
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'rewards_report' AND `proc`.`type` = 'PROCEDURE'
sakila rewards_report PROCEDURE rewards_report SQL READS_SQL_DATA NO DEFINER

    IN min_monthly_purchases TINYINT UNSIGNED
    , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
    , OUT count_rewardees INT

proc: BEGIN
    
    DECLARE last_month_start DATE;
    DECLARE last_month_end DATE;

    
    IF min_monthly_purchases = 0 THEN
        SELECT 'Minimum monthly purchases parameter must be > 0';
        LEAVE proc;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
        LEAVE proc;
    END IF;

    
    SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
    SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
    SET last_month_end = LAST_DAY(last_month_start);

    
    CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);

    
    INSERT INTO tmpCustomer (customer_id)
    SELECT p.customer_id 
    FROM payment AS p
    WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased
    AND COUNT(customer_id) > min_monthly_purchases;

    
    SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

    
    SELECT c.* 
    FROM tmpCustomer AS t   
    INNER JOIN customer AS c ON t.customer_id = c.customer_id;

    
    DROP TABLE tmpCustomer;
END
root@localhost 2013-12-18 15:04:09 2013-12-18 15:04:09 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... Provides a customizable report on best customers utf8 utf8_general_ci latin1_swedish_ci
proc: BEGIN
    
    DECLARE last_month_start DATE;
    DECLARE last_month_end DATE;

    
    IF min_monthly_purchases = 0 THEN
        SELECT 'Minimum monthly purchases parameter must be > 0';
        LEAVE proc;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
        LEAVE proc;
    END IF;

    
    SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
    SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
    SET last_month_end = LAST_DAY(last_month_start);

    
    CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);

    
    INSERT INTO tmpCustomer (customer_id)
    SELECT p.customer_id 
    FROM payment AS p
    WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased
    AND COUNT(customer_id) > min_monthly_purchases;

    
    SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

    
    SELECT c.* 
    FROM tmpCustomer AS t   
    INNER JOIN customer AS c ON t.customer_id = c.customer_id;

    
    DROP TABLE tmpCustomer;
END
Editar Editar Copiar Copiar Remover Remover
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'get_customer_balance' AND `proc`.`type` = 'FUNCTION'
sakila get_customer_balance FUNCTION get_customer_balance SQL READS_SQL_DATA YES DEFINER
p_customer_id INT, p_effective_date DATETIME
decimal(5,2)
BEGIN

       
       
       
       
       
       

  DECLARE v_rentfees DECIMAL(5,2); 
  DECLARE v_overfees INTEGER;      
  DECLARE v_payments DECIMAL(5,2); 

  SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
    FROM film, inventory, rental
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

  SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
        ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
    FROM rental, inventory, film
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;


  SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
    FROM payment

    WHERE payment.payment_date <= p_effective_date
    AND payment.customer_id = p_customer_id;

  RETURN v_rentfees + v_overfees - v_payments;
END
root@localhost 2013-12-18 15:04:09 2013-12-18 15:04:09 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... utf8 utf8_general_ci latin1_swedish_ci
BEGIN

       
       
       
       
       
       

  DECLARE v_rentfees DECIMAL(5,2); 
  DECLARE v_overfees INTEGER;      
  DECLARE v_payments DECIMAL(5,2); 

  SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
    FROM film, inventory, rental
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;

  SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
        ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
    FROM rental, inventory, film
    WHERE film.film_id = inventory.film_id
      AND inventory.inventory_id = rental.inventory_id
      AND rental.rental_date <= p_effective_date
      AND rental.customer_id = p_customer_id;


  SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
    FROM payment

    WHERE payment.payment_date <= p_effective_date
    AND payment.customer_id = p_customer_id;

  RETURN v_rentfees + v_overfees - v_payments;
END
Editar Editar Copiar Copiar Remover Remover
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'film_in_stock' AND `proc`.`type` = 'PROCEDURE'
sakila film_in_stock PROCEDURE film_in_stock SQL READS_SQL_DATA NO DEFINER
IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id);

     SELECT FOUND_ROWS() INTO p_film_count;
END
root@localhost 2013-12-18 15:04:09 2013-12-18 15:04:09 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... utf8 utf8_general_ci latin1_swedish_ci
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id);

     SELECT FOUND_ROWS() INTO p_film_count;
END
Editar Editar Copiar Copiar Remover Remover
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'film_not_in_stock' AND `proc`.`type` = 'PROCEDURE'
sakila film_not_in_stock PROCEDURE film_not_in_stock SQL READS_SQL_DATA NO DEFINER
IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id);

     SELECT FOUND_ROWS() INTO p_film_count;
END
root@localhost 2013-12-18 15:04:09 2013-12-18 15:04:09 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... utf8 utf8_general_ci latin1_swedish_ci
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id);

     SELECT FOUND_ROWS() INTO p_film_count;
END
Editar Editar Copiar Copiar Remover Remover
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'inventory_held_by_customer' AND `proc`.`type` = 'FUNCTION'
sakila inventory_held_by_customer FUNCTION inventory_held_by_customer SQL READS_SQL_DATA NO DEFINER
p_inventory_id INT
int(11)
BEGIN
  DECLARE v_customer_id INT;
  DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;

  SELECT customer_id INTO v_customer_id
  FROM rental
  WHERE return_date IS NULL
  AND inventory_id = p_inventory_id;

  RETURN v_customer_id;
END
root@localhost 2013-12-18 15:04:09 2013-12-18 15:04:09 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... utf8 utf8_general_ci latin1_swedish_ci
BEGIN
  DECLARE v_customer_id INT;
  DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;

  SELECT customer_id INTO v_customer_id
  FROM rental
  WHERE return_date IS NULL
  AND inventory_id = p_inventory_id;

  RETURN v_customer_id;
END
Editar Editar Copiar Copiar Remover Remover
DELETE FROM proc WHERE `proc`.`db` = 'sakila' AND `proc`.`name` = 'inventory_in_stock' AND `proc`.`type` = 'FUNCTION'
sakila inventory_in_stock FUNCTION inventory_in_stock SQL READS_SQL_DATA NO DEFINER
p_inventory_id INT
tinyint(1)
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    
    

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END
root@localhost 2013-12-18 15:04:09 2013-12-18 15:04:09 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D... utf8 utf8_general_ci latin1_swedish_ci
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    
    

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END
Editar Editar Copiar Copiar Remover Remover
DELETE FROM proc WHERE `proc`.`db` = 'mysql' AND `proc`.`name` = 'DropGeometryColumn' AND `proc`.`type` = 'PROCEDURE'
mysql DropGeometryColumn PROCEDURE DropGeometryColumn SQL CONTAINS_SQL NO INVOKER
catalog varchar(64), t_schema varchar(64),
   t_name varchar(64), geometry_column varchar(64)
begin
  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
root@localhost 2019-10-30 15:45:43 2019-10-30 15:45:43 latin1 latin1_swedish_ci latin1_swedish_ci
begin
  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
Editar Editar Copiar Copiar Remover Remover
DELETE FROM proc WHERE `proc`.`db` = 'mysql' AND `proc`.`name` = 'AddGeometryColumn' AND `proc`.`type` = 'PROCEDURE'
mysql AddGeometryColumn PROCEDURE AddGeometryColumn SQL CONTAINS_SQL NO INVOKER
catalog varchar(64), t_schema varchar(64),
   t_name varchar(64), geometry_column varchar(64), t_srid int
begin
  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
root@localhost 2019-10-30 15:45:43 2019-10-30 15:45:43 latin1 latin1_swedish_ci latin1_swedish_ci
begin
  set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end
Com marcados: Com marcados:
Operações resultantes das consultas
Marcar essa consulta SQL Marcar essa consulta SQL