Dateien hier ablegen

SQL-Upload ( 0 ) x -

Seitenbezogene Einstellungen Klicken Sie auf die Leiste, um zum Anfang der Seite zu scrollen
Drücken Sie Strg+Enter, um die Abfrage auszuführen Drücken Sie Enter, um die Abfrage auszuführen
aufsteigend
absteigend
Reihenfolge:
SQL Debugger
Zähler
Ausführungsreihenfolge
Nötige Zeit
Sortieren nach:
Abfragen umgruppieren
Abfragen voneinander lösen
Zuklappen Aufklappen Nachverfolgung anzeigen Nachverfolgung ausblenden Anzahl: Nötige Zeit:
Lesezeichen
Aktualisieren
Hinzufügen
Keine Lesezeichen
Lesezeichen hinzufügen
Optionen
Standard festlegen
Zuklappen Aufklappen Erneut abfragen Bearbeiten Erklären Messen Lesezeichen Abfrage fehlgeschlagen Datenbank: Abfragezeit:
Anzeigemodus
Anzeigemodus anpassen.
Anzeigemodus
Dokumentation Verwende nur Icons, nur Text oder beides. Voreingestellten Wert wiederherstellen
Dokumentation Verwende nur Icons, nur Text oder beides. Voreingestellten Wert wiederherstellen
Dokumentation Ob dem Benutzer eine Schaltfläche „Alle (Datensätze) anzeigen” angezeigt werden soll. Voreingestellten Wert wiederherstellen
Dokumentation Anzahl der angezeigten Datensätze in einem Abfrage-Ergebnis. Wenn mehr Datensätze vorhanden sind, werden "Previous" and "Next" Links angezeigt. Voreingestellten Wert wiederherstellen
Dokumentation SMART - d.h. absteigende Sortierung für die Feldtypen TIME, DATE, DATETIME und TIMESTAMP, sonst aufsteigende Sortierung. Voreingestellten Wert wiederherstellen
Dokumentation Zeile unter Mauscursor hervorheben. Voreingestellten Wert wiederherstellen
Dokumentation Markierte Datensätze hervorheben. Voreingestellten Wert wiederherstellen
Dokumentation Voreingestellten Wert wiederherstellen
Dokumentation Voreingestellten Wert wiederherstellen
Dokumentation Kopfzeilen alle n Zellen anzeigen, 0 deaktiviert diese Option. Voreingestellten Wert wiederherstellen
Dokumentation Maximal dargestellte Zeichenzahl bei Anzeige einer nicht-numerischen Spalte in einer SQL-Abfrage. Voreingestellten Wert wiederherstellen
Dokumentation Dies sind Bearbeitungs-, Kopier- und Löschlinks. Voreingestellten Wert wiederherstellen
Dokumentation Ob Verlinkungen zu Tabellenzeilen auch in Abwesenheit eines eindeutigen Schlüssels angezeigt werden. Voreingestellten Wert wiederherstellen
Dokumentation Standard-Sortierreihenfolge für Tabellen mit einem Primärschlüssel. Voreingestellten Wert wiederherstellen
Dokumentation Während des Durchsehens der Tabellen wird die Sortierung zwischengespeichert. Voreingestellten Wert wiederherstellen
Dokumentation Für Anzeigeoptionen Voreingestellten Wert wiederherstellen
SELECT * FROM `proc`
Vollständige Texte 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
Bearbeiten Bearbeiten Kopieren Kopieren Löschen Löschen
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
Bearbeiten Bearbeiten Kopieren Kopieren Löschen Löschen
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
Bearbeiten Bearbeiten Kopieren Kopieren Löschen Löschen
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
Bearbeiten Bearbeiten Kopieren Kopieren Löschen Löschen
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
Bearbeiten Bearbeiten Kopieren Kopieren Löschen Löschen
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
Bearbeiten Bearbeiten Kopieren Kopieren Löschen Löschen
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
Bearbeiten Bearbeiten Kopieren Kopieren Löschen Löschen
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
Bearbeiten Bearbeiten Kopieren Kopieren Löschen Löschen
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
markierte: markierte:
Operationen für das Abfrageergebnis
SQL-Abfrage speichern SQL-Abfrage speichern