قم إفلات الملفات هنا

SQL upload ( 0 ) x -

الإعدادات المتعلقة بالصفحة انقر على الشريط للتمرير إلى أعلى الصفحة
Press Ctrl+Enter to execute query Press Enter to execute query
ascending
descending
Order:
تصحيح SQL
Count
Execution order
Time taken
Order by:
Group queries
Ungroup queries
Collapse Expand Show trace Hide trace Count: الوقت المستغرق:
Bookmarks
تحديث
إضافة
لا توجد إشارات مرجعية
إضافة للمفضلة
خيارات
Set default
Collapse Expand Requery تعديل Explain جانبي Bookmark Query failed قاعدة البيانات: Queried time:
وضع الإستعراض
تخصيص وضع التصفح.
وضع الإستعراض
مستندات وثائقية إستخدم الأيقونات او النصوص فقط , أو الإثنان. إستعادة القيمة الإفتراضية
مستندات وثائقية إستخدم الأيقونات او النصوص فقط , أو الإثنان. إستعادة القيمة الإفتراضية
مستندات وثائقية Whether a user should be displayed a "show all (rows)" button. إستعادة القيمة الإفتراضية
مستندات وثائقية Number of rows displayed when browsing a result set. If the result set contains more rows, "Previous" and "Next" links will be shown. إستعادة القيمة الإفتراضية
مستندات وثائقية SMART - i.e. descending order for columns of type TIME, DATE, DATETIME and TIMESTAMP, ascending order otherwise. إستعادة القيمة الإفتراضية
مستندات وثائقية توضيح الصفوف المشار إليها بمؤشر الفأرة. إستعادة القيمة الإفتراضية
مستندات وثائقية توضيح الصفوف المختارة. إستعادة القيمة الإفتراضية
مستندات وثائقية إستعادة القيمة الإفتراضية
مستندات وثائقية إستعادة القيمة الإفتراضية
مستندات وثائقية Repeat the headers every X cells, 0 deactivates this feature. إستعادة القيمة الإفتراضية
مستندات وثائقية الحد الأقصى لعدد الأحرف المعروضة في أي عمود غير رقمي في طريقة عرض الاستعراض. إستعادة القيمة الإفتراضية
مستندات وثائقية هذه هي روابط التعديل والنسخ والحذف. إستعادة القيمة الإفتراضية
مستندات وثائقية Whether to show row links even in the absence of a unique key. إستعادة القيمة الإفتراضية
مستندات وثائقية ترتيب الفرز الافتراضي للجداول ذات المفتاح الأساسي. إستعادة القيمة الإفتراضية
مستندات وثائقية When browsing tables, the sorting of each table is remembered. إستعادة القيمة الإفتراضية
مستندات وثائقية لخيارات العرض إستعادة القيمة الإفتراضية
SELECT * FROM `proc`
نصوص كاملة 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
تعديل تعديل نسخ نسخ حذف حذف
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
تعديل تعديل نسخ نسخ حذف حذف
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
تعديل تعديل نسخ نسخ حذف حذف
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
تعديل تعديل نسخ نسخ حذف حذف
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
تعديل تعديل نسخ نسخ حذف حذف
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
تعديل تعديل نسخ نسخ حذف حذف
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
تعديل تعديل نسخ نسخ حذف حذف
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
تعديل تعديل نسخ نسخ حذف حذف
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
مع المحدد: مع المحدد:
عمليّات على نتائج الإستعلام
اجعل علامة مرجعية SQL-استعلام اجعل علامة مرجعية SQL-استعلام