1.视图
a.
sql;">
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
sql SECURITY INVOKER
VIEW `sakila`.`actor_info` AS
SELECT
`a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,GROUP_CONCAT(DISTINCT CONCAT(`c`.`name`,': ',(SELECT
GROUP_CONCAT(`f`.`title`
ORDER BY `f`.`title` ASC
SEPARATOR ',')
FROM
((`sakila`.`film` `f`
JOIN `sakila`.`film_category` `fc` ON ((`f`.`film_id` = `fc`.`film_id`)))
JOIN `sakila`.`film_actor` `fa` ON ((`f`.`film_id` = `fa`.`film_id`)))
WHERE
((`fc`.`category_id` = `c`.`category_id`)
AND (`fa`.`actor_id` = `a`.`actor_id`))))
ORDER BY `c`.`name` ASC
SEPARATOR '; ') AS `film_info`
FROM
(((`sakila`.`actor` `a`
LEFT JOIN `sakila`.`film_actor` `fa` ON ((`a`.`actor_id` = `fa`.`actor_id`)))
LEFT JOIN `sakila`.`film_category` `fc` ON ((`fa`.`film_id` = `fc`.`film_id`)))
LEFT JOIN `sakila`.`category` `c` ON ((`fc`.`category_id` = `c`.`category_id`)))
GROUP BY `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`
b.
sql;">
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
sql SECURITY DEFINER
VIEW `sakila`.`staff_list` AS
SELECT
`s`.`staff_id` AS `ID`,CONCAT(`s`.`first_name`,_UTF8' ',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,`s`.`store_id` AS `SID`
FROM
(((`sakila`.`staff` `s`
JOIN `sakila`.`address` `a` ON ((`s`.`address_id` = `a`.`address_id`)))
JOIN `sakila`.`city` ON ((`a`.`city_id` = `sakila`.`city`.`city_id`)))
JOIN `sakila`.`country` ON ((`sakila`.`city`.`country_id` = `sakila`.`country`.`country_id`)))
2.存储过程
a.
sql;">
CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(IN p_film_id INT,IN p_store_id INT,OUT p_film_count INT)
READS sql DATA
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
b.
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;
/* Determine start and end time periods */
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 a temporary storage area for
Customer IDs.
*/
CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
/*
Find all customers meeting the
monthly purchase requirements
*/
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;
/* Populate OUT parameter with count of found customers */
SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
/*
Output ALL customer information of matching rewardees.
Customize output as needed.
*/
SELECT c.*
FROM tmpCustomer AS t
INNER JOIN customer AS c ON t.customer_id = c.customer_id;
/* Clean up */
DROP TABLE tmpCustomer;
END
3.函数
a.
sql;">
CREATE DEFINER=`root`@`localhost` FUNCTION `get_customer_balance`(p_customer_id INT,p_effective_date DATETIME) RETURNS decimal(5,2)
READS sql DATA
DETERMINISTIC
BEGIN
#OK,WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
# 1) RENTAL FEES FOR ALL PREVIoUS RENTALS
# 2) ONE DOLLAR FOR EVERY DAY THE PREVIoUS RENTALS ARE OVERDUE
# 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE,CHARGE THE REPLACEMENT_COST
# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS
DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIoUSLY
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,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
b.
0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END
以上所述是小编给大家介绍的MysqL 5.7 create VIEW or FUNCTION or PROCEDURE，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对编程之家网站的支持！
原文链接：https://www.f2er.com/mssql/62782.html