MySQL Must Know must Know – Concepts Section

MySQL must know must – Install application section

MySQL must know must – retrieve data sections

MySQL must know must know – join tables and advanced query sections

MySQL Must know must know – text search article

MySQL > select * from table_name

The database files used below are available in themysql_scriptsTo find it.

Using the view

A view is only a facility for viewing data stored elsewhere. It contains no data, and the returned data is retrieved from other tables. A view can change the format and presentation of data.

The operational view

CREATE VIEW Creates a VIEW

SHOW CREATE VIEW viewName VIEW the statement used to CREATE a VIEW

DROP VIEW viewName Deletes a VIEW

When updating a VIEW, you can DROP and then CREATE OR use CREATE OR REPLACE VIEW

It was useful to query the tables where prod_id was the buying user for TNT2, but you would have to look again if you wanted to see something else with prod_id. CREATE VIEW productcustomers AS SELECT CUST_NAME, CUST_contact, AND prod_id prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num; Select * from productCustomers; select * from productcustomers; +----------------+--------------+---------+ | cust_name | cust_contact | prod_id | +----------------+--------------+---------+ | Coyote Inc. | Y Lee | ANV01 | | Coyote Inc. | Y Lee | ANV02 | | Coyote Inc. | Y Lee | TNT2 | | Coyote Inc. | Y Lee | FB | | Coyote Inc. | Y Lee | FB | | Coyote Inc. | Y Lee | OL1 | | Coyote Inc. | Y Lee | SLING | | Coyote Inc. | Y Lee | ANV03 | | Wascals | Jim Jones | JP2000 | | Yosemite Place | Y Sam | TNT2 FC Fudd Fudd | | E | E | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + # # # if you want to query the prod_id for TNT2 customer information is very easy to SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2'; +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee |  | Yosemite Place | Y Sam | +----------------+--------------+Copy the code

Pay attention to

  • Views must be uniquely named (cannot be the same name as other views and tables)
  • There is no limit to the number of views that can be created.
  • Views can be nested, that is, a new view can be constructed using queries that retrieve data from other views.
  • ORDER BY can be used in a view, but if the SELECT that retrieves data from that view also contains ORDER BY, the ORDER BY in the view is overwritten.
  • Views cannot be indexed, have triggers or default values associated with them.
  • Views can be used with tables.
  • Views are generally used for retrieval (SELECT) rather than UPDATE (INSERT, UPDATE, DELETE), because updating a view is equivalent to updating its base table, and updates are not allowed if the base data to be updated cannot be correctly determined.


Using stored procedures

There are three main benefits to using stored procedures: simplicity, security, and high performance.

MySQL calls the execution of a stored procedure a CALL, so the statement that MySQL executes a stored procedure is called a CALL. CALL takes the name of the stored procedure and any parameters that need to be passed to it.

A simple example

If the stored procedure accepts parameters, they will be listed in the productPricing () parentheses. DELIMITER // CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS priceaverage FROM products; END// ### CALL productpricing; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | priceaverage | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 16.133571 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # to delete DROP PROCEDURE stored procedures productpricing; SHOW CREATE PROCEDURE productpricing;Copy the code

Examples with parameters

DELIMITER // CREATE PROCEDURE productpricing (OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2)) BEGIN SELECT Min(prod_price) INTO pl FROM products; SELECT Max(prod_price) INTO ph FROM products; SELECT Avg(prod_price) INTO pa FROM products; END // ### CALL productPricing (@pricelow,@pricehigh, @priceAverage); SELECT @pricelow,@pricehigh, @priceAverage; +-----------+------------+---------------+ | @pricelow | @pricehigh | @priceaverage | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | | 16.13 55.00 + 2.50 -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Build intelligent stored procedures

Order totals need to be obtained and sales tax added to certain customer totals.

DELIMITER // -- Name: orderTotal -- Parameters: onumber = order number -- taxable = 0 if not taxable, 1 if taxable -- ototal = order total variable CREATE PROCEDURE ordertotal ( IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2)) COMMENT 'Obtain order total, Optionally add tax' BEGIN -- Declare variable for total Declare total DECIMAL(8,2); -- Declare tax percentage DECLARE taxrate INT DEFAULT 6; -- Get the order total SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total; -- Is this taxable IF taxable THEN -- Yes, so add taxrate to the total SELECT total+(total/100*taxrate) INTO total; END IF; -- And finally, save to out variable SELECT total INTO ototal; CALL orderTotal (20005, 0, @total); CALL orderTotal (20005, 0, @total); SELECT @total; + -- -- -- -- -- -- -- -- + | @ total | + -- -- -- -- -- -- -- -- + | | + 149.87 -- -- -- -- -- -- -- -- + # # # sales CALL ordertotal (20005, 1, @ total); SELECT @total; + -- -- -- -- -- -- -- -- + | @ total | + -- -- -- -- -- -- -- -- + | | + 158.86 -- -- -- -- -- -- -- -- +Copy the code

Do the necessary explaining in the above code

  • Two PARAMETERS of type IN are added, where Taxable is Boolean.
  • --Add comments, which are necessary if the stored procedure is complex.
  • DECLARETo define local variables, you need to specify the variable name and data type. Optional defaults are supported
  • COMMENTKeyword, not required, if added, shown in the result of SHOW PROCEDURE STATUS.

Pay attention to

  • If you create a stored procedure on the mysql command line, you need to temporarily change the statement delimiter of the command line utility, because the stored procedure creation will use; As a statement separator, this causes syntax errors. Any character except the \ symbol can be used as a statement separator. You can use DELIMITER // as the new end-of-statement DELIMITER, but after creating the stored procedure, remember to use DELIMITER; Restores to the original statement separator.
  • After a stored procedure is created, it is kept on the server for use until it is deleted.
  • IF an error is reported when a stored PROCEDURE is deleted that does not exist, you can use DROP PROCEDURE IF EXISTS to delete the PROCEDURE only IF it EXISTS.
  • MySQL supports three types of arguments: IN (passed to stored procedures), OUT (passed from stored procedures), and INOUT (passed to and from stored procedures). The value retrieved by SELECT is saved to the corresponding variable through INTO. Note that the data type of the parameter cannot be a set, which is why the example uses three parameters to output three numbers.
  • If the stored procedure requires three parameters, exactly three parameters must be passed.
  • SHOW PROCEDURE STATUSYou can list all stored procedures, or you can specify a filtering mode using LIKE:SHOW PROCEDURE STATUS LIKE 'ordertotal';

Front- end-Basics: Watch, Star, Front-End-Basics: Front-End-Basics

MySql must know must know this article