SQL, reading notes, MySQL must know

This is MySQL must know must know [1] Reading notes are used to summarize knowledge points and framework. They are only for reference and exchange. Please contact us if you have any problems. As the software version is updated, some codes in the book are no longer applicable. This paper mainly deals with the addition and deletion of SQL basic statements, window functions and other operations. Software version used: MySQL Server 8, Navicat Premium12.1 [2]

  • Preparation before study:
    01 enable MySQL service [3]:
    Enter mysql editing status:: mysql -uroot -p
    02 create, delete, view all databases:
    Create database: create database if not exists;
    Delete database: drop database database name;
    View: show databases;
    03 executing sql statements in Navicat Premium [4]
    (1) Executing sql statement with command line interface
    Right click Database > command line interface to open the mysql command execution window. Create and modify tables
    (2) Enter query state to execute sql statement
    Go to connect [local] - > click the database to be queried [test] - [query] - [new query]
    Note: reference to table creation statement and data statement of data table in this book [5] , it is recommended to build in advance.

(8)SELECT   (9)DISTINCT<select_list>
(1)FROM<left_table>    
(3)<join_type>JOIN<right_table>
(2)ON<join_condition>
(4)WHERE<where_condition>
(5)GROUP BY<group_by_list>
(6)WITH{CUBE|ROLLUP}
(7)HAVING<having_condition>
(10)ORDER BY<order_by_list>
(11)LIMIT<limit_number>
clause explain Is it necessary to use
select Column or expression to return yes
from Table from which to retrieve data Use only when selecting data from a table
where Row level filtering no
group by Group description Use only when aggregating by group
having Group level filtering no
order by Output sort order no
limit Number of rows to retrieve no

catalog

Find class

Chapter 4 retrieving data

4.1 - 4.4 retrieve column: select statement

1. SQL statements are not case sensitive, but for ease of reading and debugging, uppercase is used for all SQL keywords, and lowercase is used for all column and table names. Multiple SQL statements must be separated by semicolons (;).
2. select statement: retrieve single, multiple, all columns

SELECT prod_id,prod_price FROM products;
SELECT * FROM products; #Retrieve all columns

4.5 retrieving different lines: distinct keyword

distinct keyword: applies to all columns, not just the columns that precede it.

SELECT DISTINCT vend_id FROM products;

4.6 limit result: limit clause

1. limit clause: Return
2. Line 0: the first line retrieved is line 0, not line 1. For example, limit 1,1 retrieves line 2.

SELECT prod_name FROM products LIMIT 5,5;

4.7 use fully qualified table name (use both table name and column name)

SELECT products.prod_name FROM crashcourse.products;

Chapter 5 sorting and retrieving data

5.1 - 5.2 sorting data: order by clause

#sort
SELECT prod_name FROM products ORDER BY prod_name;

#Sort by multiple columns
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

5.3 specify sorting direction: desc keyword

1. Default ascending order.
2. desc keyword: specifies to arrange in descending order. Applies only to column names directly before them.

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

Chapter 6 filtering data

6.1 where clause

1. The where clause should precede the order by clause, otherwise an error will be reported.
2. Clause operator:
Not equal to: < >! =
Between the two values specified: between
3. Single quotes are used to qualify strings. When comparing values with columns of string type, you need to qualify quotes.

#Check individual values
SELECT prod_name, prod_price FROM products WHERE prod_name ='fuses';
SELECT prod_name, prod_price FROM products WHERE prod_price <10;

#Mismatch check
SELECT ven_id, prod_name FROM products WHERE vend_id <>1003;

#Range value check
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

#Null check
SELECT cust_id FROM customers WHERE cust_email IS NULL;

Chapter 7 data filtering

7.1 combine where clause: and clause or or clause

1. The and operator takes precedence, so parenthesis is required.

SELECT prod_name, prod_price FROM products
WHERE (vend_id = 1002 OR vend_id =1003) AND prod_price >=10;

7.2 specify condition range: in operator

SELECT prod_name, prod_price FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

7.3 find out the mismatched rows in the condition list: not operator

MySQL supports not to negate in, between, and exists clauses.

SELECT prod_name, prod_price FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

The eighth chapter uses wildcard to filter

8.1 like operator

1. Using wildcards [2] (special characters used to match a part of a value) creates a search pattern that compares specific data.
2. Search mode: a search condition consisting of literal characters, wildcards, or a combination of both.

8.1.1% sign (%) wildcard

#Start with the word jet
SELECT prod_id, prod_name FROM products
WHERE prod_name LIKE 'jet%';

#Match anywhere contains
SELECT prod_id, prod_name FROM products
WHERE prod_name LIKE '%anvil%';

8.1.2 underline () wildcard

A kind of Always match a character, no more or no less.

#Match anywhere contains
SELECT prod_id, prod_name FROM products
WHERE prod_name LIKE '_anvil';

Chapter 9 search with regular expression

9.1-9.2 regular expression

9.2.1 basic character matching: regexp

1. '. 000' is a special character in the regular expression language that matches any character.
2. The difference between like and regexp [3]:
(1) LIKE matches the entire column. If the matched text only appears in the column value, LIKE will not find it and the corresponding row will not be returned (except for the use of wildcards, of course). REGEXP matches in the column value. If the matched text appears in the column value, REGEXP will find it, and the corresponding row will be returned. This is a very important difference (of course, if the positioning symbols ^ and $, REGEXP can match the whole column instead of the subset of the column).
3. Case sensitivity: regular expression matching in MySQL (since version 3.23.4) is not case sensitive. If you want to be case sensitive, you should use the binary keyword, such as where post_name REGEXP BINARY 'Hello .000'

SELECT prod_name FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

9.2.2 or matching:|

SELECT prod_name FROM products
WHERE prod_name REGEXP '2000|3000'
ORDER BY prod_name;

9.2.3 - 9.2.4 matching characters and ranges: []

1,[123]Ton=[1|2|3]Ton
2. Match other than specified characters: [^ 123]

#Match one of several characters
SELECT prod_name FROM products
WHERE prod_name REGEXP '[123]Ton'
ORDER BY prod_name;

#Matching range
SELECT prod_name FROM products
WHERE prod_name REGEXP '[1-5]Ton'
ORDER BY prod_name;

9.2.5 matching special characters: \ \ (matching)

1. \ \. Can match
2. Blank metacharacter:
\f: Page feed, \ \ n: line feed, r: enter, t: tabulate, v vertical tabulate

SELECT vend_name FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

9.2.6 matching character class: [: Class:]

[: alnum:] any letter and number (general [a-zA-Z0-9]
[: alpha:] any character (same as [a-zA-Z])
[: blank:] spaces and tabs (same as [\ t])
[: digit:] any number (the same as [0-9])
[: lower:] any lowercase letter
[: upper:] any capital letter
[: space:] any whitespace character, including spaces

9.2.7 match multiple instances: * +? {n} {n,} {n,m}

(1) * 0 or more matches
(2) + 1 or more matches (equal to {1,})
(3) ? 0 or 1 matches (equal to {0,1})
(4) The number of matches specified by {n}
(5) {n,} no less than the specified number of matches
(6) The range of {n,m} matches (m no more than 255)

#Match the four digits together
SELECT prod_name FROM products 
WHERE prod_name REGEXP '[[:DIGIT:]]{4}' 
ORDER BY prod_name;

9.2.8 positioning metacharacters: ^ $[[[: <:]] [[: >:]]

^Start of text
&End of text
The beginning of [[: <:]] words
End of [[: >:]] word

SELECT prod_name FROM products 
WHERE prod_name REGEXP '^[0-9\\.]' 
ORDER BY prod_name;

Chapter 10 creating calculation fields

10.1-10.2 calculation field and splicing field: Concat()

1. Remove extra space
(1) Remove extra space to the left of data ltrim()
(2) Remove extra space rtrim() to the right of data
(3) Delete extra space trim() on both sides of data

SELECT concat(vend_name,' (',vend_country,')') FROM vendors 
ORDER BY vend_name;  

SELECT concat(rtrim(vend_name),' (',rtrim(vend_country),')') FROM vendors 
ORDER BY vend_name;

2. Column name as

SELECT concat(rtrim(vend_name),' (',rtrim(vend_country),')') AS vend_title 
FROM vendors 
ORDER BY vend_name;

10.3 perform arithmetic calculation

SELECT prod_id,quantity,item_price,quantity * item_price AS expanded_price
FROM orderitems WHERE order_num = 20005;  # Calculate total expanded_price

Chapter 11 using data processing functions

11.1-11.2 functions

11.2.1 text processing function

#    left() returns the character to the left of the string 
#    length() returns the length of the string 
#    locate() finds a substring of the string 
#    lower() converts the string to lowercase
#    ltrim() removes the space to the left of the string
#    right() returns the character to the right of the string 
#    rtrim() removes the space to the right of the string  
#    soundex() returns the soundex value of the string
#    substring() returns the character of the substring 
#    upper() converts the string to uppercase

SELECT vend_name, upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

SELECT cust_name,cust_contact FROM customers WHERE cust_contact = 'Y. Lie';  # No return 
SELECT cust_name,cust_contact FROM customers WHERE soundex(cust_contact) = soundex('Y. Lie'); # Search by pronunciation 

11.2.2 date and time processing functions

1. Preferred date format yyyy MM DD to avoid ambiguity [3]
2. Pit: order_date is the datetime data type, which contains time information. If the time information is not 00:00:00, there is no result in the previous sentence. Therefore, date() is used to filter the date information.

#    adddate() add a date (day, week, etc.)
#    addtime() add a time (hour, minute, etc.)
#    curdate() returns the current date 
#    curtime() returns the current time 
#    date() returns the date part of the date time     
#    datediff() calculates the difference between two dates 
#    date_add() highly flexible date operation function 
#    date_format() returns a formatted date or time string 
#    day() returns the days part of a date     
#    dayofweek() returns the corresponding day of the week for a date 
#    hour() returns the hour part of a time 
#    minute() returns the minute part of a time 
#    month() returns the month part of a date 
#    now() returns the current date and event 
#    second() returns the second part of a time 
#    time() returns the time part of a date time 
#    year() returns the year part of a date 

SELECT cust_id,order_num FROM orders WHERE Date(order_date) = "2005-09-01";

# Retrieve orders placed in September 2005 
SELECT cust_id,order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

11.2.3 numerical processing function

#abs() returns the absolute value of a number
 #cos() returns the cosine of an angle
 #exp() returns the index value of a number
 #mod() returns the remainder of the division operation
 #pi() returns the PI    
#sin() returns the sine of an angle 
#sqrt() returns the square root of a number 
#tan() returns the tangent of an angle 

Chapter 12 summary data

12.1 aggregate function

# avg() returns the average value of a column, ignoring rows with null column value bits.
# Count() returns the number of rows in a column, which is divided into count() and count (*)
# max() returns the maximum value of a column 
# min() returns the minimum value of a column 
# sum() returns the sum of the values of a column, ignoring the row with a null column value bit.

# COUNT(*) counts the number of rows in the table without ignoring null values 
SELECT Count(*) AS num_cust FROM customers; 
# Use COUNT(column) to count rows with values in a specific column, ignoring NULL values
SELECT Count(cust_email) AS num_cust FROM customers;  

# When used for text data, MAX() returns the last row if the data is sorted by the corresponding column
select max(prod_name) from products; 
# When used for text data, MIN() returns the first row if the data is sorted by the corresponding column
select min(prod_name) from products; 

12.2 aggregate different values distinct

distinct can only be used for count(), not count (*)

SELECT Avg(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

12.3 combined aggregate function: multiline

SELECT 
    COUNT(*) AS num_items,
    MIN(prod_price) AS price_min,
    MAX(prod_price) AS price_max,
    AVG(prod_price) AS price_avg
FROM
    products;

Chapter 13 grouping data

13.1-13.2 create data: group by clause

1. The group by clause must appear after the where clause and before the order by clause.
2. With roll up keyword [1] : can generate summary value

# Press vend_id sort and group data
select vend_id, count(*) as num_prods from products group by vend_id;

# Using the WITH ROLLUP keyword, you can get the summary value and total value of each group
select vend_id, count(*) as num_prods from products group by vend_id with rollup;

13.3 filter group: having clause

where is filtered before data grouping and having is filtered after data grouping.

# Those groups with count (*) > = 2 (more than two orders)
select cust_id, count(*) as orders from orders
group by cust_id having count(*)>=2;

-- where and having Combined use 
#List suppliers with more than 2 (including) products and more than 10 (including) products
select vend_id,count(*) as num_prods from products 
where prod_price >=10 
group by vend_id having count(*)>=2;

#Without where condition, the result is different 
SELECT vend_id,count(*) AS num_prods FROM products
GROUP BY vend_id HAVING count(*) >=2;


13.4 grouping and sorting: group by and order by

# Retrieve order number and total order price for orders with total order price greater than or equal to 50
SELECT 
    order_num,sum(quantity * item_price) AS ordertotal FROM orderitems 
GROUP BY order_num HAVING sum(quantity * item_price) >=50;

# Sort output by total order price
SELECT 
    order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems
GROUP BY order_num HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;

13.5 summary of clauses

clause explain Is it necessary to use
select Column or expression to return yes
from Table from which to retrieve data Use only when selecting data from a table
where Row level filtering no
group by Group description Use only when aggregating by group
having Group level filtering no
order by Output sort order no
limit Number of rows to retrieve no

Chapter 14 using subqueries (nesting)

14.1-14.2 filter by sub query

# List all customers who ordered TNT2
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'TNT2'));

(1) Table 1: orderitems

(2) After the first layer operation:

(3) Table 2: orders

(4) After layer 2 operation:

(5) Table 3: customers

(6) After the third layer operation:

14.3 use subquery as calculation field

# Displays the total number of orders per customer in the customers table
SELECT cust_name,
       cust_state, 
       (SELECT count(*) FROM orders 
       WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers 
ORDER BY cust_name;

Chapter 15 connection table

15.1 - 15.2 creating a join

1. Cartesian product: the result returned by a table relation without a join condition is Cartesian product. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table.

SELECT vend_name,prod_name,prod_price 
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

2. Internal join: table to table equality test
inner join: both tables have corresponding data at the same time, i.e. no data will be displayed if either side is missing.

SELECT vend_name,prod_name,prod_price 
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

Chapter 16 creating advanced joins

16.1 using table aliases

SELECT cust_name,cust_contact 
FROM customers as c,orders as o,orderitems as oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';

16.2 use of different types of frontal connections

16.2.1 self connection

Processing joins is much faster than processing subqueries.

#Method: subquery 
select prod_id,prod_name from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');
 #Method: use join 
select p1.prod_id,p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id
and p2.prod_id = 'DTNTR';

16.2.2 natural connection

Standard join (such as internal join) returns all data or even the same column multiple times. Natural join causes each column to return only once.
In fact, every internal connection we have established so far is a natural connection, and it is likely that we will never use an internal connection that is not a natural connection.

#Wildcards are used only for the first table, all other columns are explicitly listed.
select c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price
from customers as c,orders as o,orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'FB';

16.2.3 external connection

A join contains rows that do not have associated rows in the related tables. It is called an outer join.

# Retrieve all customers and their orders, including those without orders
# 01: left outer link
select customers.cust_id,orders.order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;

# 03: use the right external connection to change the positions of two tables
select customers.cust_id,orders.order_num
from orders right outer join customers
on customers.cust_id = orders.cust_id;

16.3 using joins with aggregate functions

# Retrieve the number of orders corresponding to all customers, inner join 
select customers.cust_name,
       customers.cust_id,
       count(orders.order_num) as num_ord
from customers inner join orders 
on customers.cust_id = orders.cust_id
group by customers.cust_id; 

# Retrieve the number of orders corresponding to all customers, including customers without orders, left outer join 
 select customers.cust_name,
       customers.cust_id,
       count(orders.order_num) as num_ord
from customers left outer join orders 
on customers.cust_id = orders.cust_id
group by customers.cust_id; 

Chapter 17 combined query

17.1 - 17.2 combined query union

1. union greatly simplifies complex where clauses.
2. Union automatically removes duplicate rows by default; union all, matches all rows, and does not cancel duplicate rows.

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <=5
UNION
SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002)
ORDER BY vend_id,prod_price;

Chapter 18 full text search

18.1 - 18.2 using full text search

18.2.1 enable full text search: fulltext(note_text)

1. The difference between engine=innodb and engine=myisam in mysql [1]
① ISAM performs read operations quickly and does not consume a lot of memory and storage resources. The two main disadvantages of ISAM are that it does not support transaction processing and is not fault tolerant.
② InnoDB provides transaction control function, which ensures that a group of commands are executed successfully, or the results of all commands are backed off when any command is wrong. It can be imagined that transaction control ability is very important in e-banking. Support COMMIT, ROLLBACK, and other transaction features.

CREATE TABLE productnotes
(
 note_id int NOT NULL AUTO_INCREMENT,
 prod_id char(10) NOT NULL,
 note_data datetime NOT NULL,
 note_text text NULL,
 PRIMARY KEY(note_id),
 FULLTEXT(note_id)
)ENGINE=MyISAM;

18.2.2 full text search: match(), against()

1. match() specifies the column to be searched, and against() specifies the search expression to use.

# match(),against()
select note_text from productnotes where match(note_text) against('rabbit');
# If you use the like statement 
select note_text from productnotes where note_text like '%rabbit%';

2. Sorting: rank
Because of the reserved word conflict, it is named rank 1 to distinguish.

select note_text, match(note_text) against('rabbit') as 'rank' from productnotes; 

18.2.3 with query expansion

The query expansion function not only returns search values (first column), but also lists other values.

select note_text from productnotes where match(note_text) against('anvils' with query expansion);

18.2.4 Boolean text search: in boolean mode

Boolean operator explain
+ Include, word must exist
- Exclude, words must not appear
> Include, and increase level value
< Include and reduce level value
() Group words into subexpressions (allow these expressions to be included, excluded, arranged, etc. as a group)
~ To cancel the sorting value of a word
* Wildcard at the end of a word
" " Define a phrase (unlike a single word list, which matches the entire phrase and includes or excludes the phrase on one side)
# No operator specified, plain single quotes, search for a line that matches at least one word in rabbit and bait
select note_text from productnotes where match(note_text) against('rabbit bait' in boolean mode);

# With double quotes, search for the matching phrase rabbit bait instead of the two words rabbit and bait. 
select note_text from productnotes where match(note_text) against('"rabbit bait"' in boolean mode);

# -Rope * excludes lines containing rope * (any words starting with rope, including ropes)
select note_text from productnotes where match(note_text) against('heavy -rope*' in boolean mode);

# Match lines containing the words rabbit and bait
select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode);

# Match rabbit and carrot, increase the level of the former and decrease the level of the latter
select note_text from productnotes where match(note_text) against('>rabbit <carrot' in boolean mode);

# The words safe and combination must be matched to reduce the level of the latter
select note_text from productnotes 
where match(note_text) against('+safe +(<combination)' in boolean mode);

Data operation class

Chapter 19 insert data: Insert

19.3 inserting multiple rows

A single insert statement has multiple sets of values, each set of values is enclosed by a pair of parentheses and separated by commas.

# Method 1: submit multiple insert statements
insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
values('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA');
insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
values('M. Martian','42 Galaxy Way','New York','NY','11213','USA');

# Method 2: as long as the column names (and order) in each INSERT statement are the same, you can combine the statements as follows
insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
values('Pep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA'),('M. Martian','42 Galaxy Way','New York','NY','11213','USA');

19.4 inserting retrieved data

#Note that there is no period or values before select
insert into customers (cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
select cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from custnew;

Chapter 20 update and delete data: update, delete

1. UPDATE statement: delete or update the specified column
2. Delete statement: delete an entire row instead of a column
3. TRUNCATE table statement: if you want to DELETE all rows from the table, do not use DELETE. You can use TRUNCATE table statement. TRUNCATE actually deletes the original table and recreates a table, rather than deleting the data in the table row by row.

# Update: customer 10005 now has an email address
UPDATE customers 
SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
# Updating: multiple columns 
UPDATE customers 
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

# Delete: specified as NULL
update customers set cust_email = null where cust_id = 10005;

# Remove a row from the customers table
delete from customers where cust_id = 10006;

Chapter 21 creating and manipulating tables

1. CREATE TABLE
If the ENGINE = statement is omitted, the default ENGINE (most likely MyISAM) is used, which is used by most SQL statements by default. InnoDB is a reliable transaction ENGINE, which does not support full-text search; MyISAM is a high-performance ENGINE, which supports full-text search, but does not support transaction processing.

CREATE TABLE students
(
   stu_id INT NOT NULL AUTO_INCREMENT,
   stu_name CHAR(50) NOT NULL,
   stu_address CHAR(50) NULL,
   PRIMARY KEY(stu_id)
 )ENGINE=INNODB

2. Update table: alter table
Add a column, delete a column, define a foreign key

ALTER TABLE orderitemsss
ADD vend_phone CHAR(20);
DROP COLUMN vend_phone;

#Define foreign key
ADD CONSTRAINT fk_orderitemsss_orders
FOREIGN KEY(order_num) REFERENCES orders (order_num);

3. Delete table: drop table
4. rename table... to

Chapter 22 use view

1. The view is a virtual table, which contains a query that does not retrieve data as needed. Equivalent to function call in programming language.
2. Rules and restrictions for views
3. Function:
(1) Using views to simplify complex joins
(2) Reformat retrieved data with views
(3) Filtering unwanted data with views
(4) Use calculated fields of view

CREATE VIEW ProductCustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderItems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num;

Chapter 23 using stored procedures

Stored procedure, like a script, is stored and called directly every time. Its advantages are simple, safe and high performance.

-- Execute stored procedure
CALL productpricing
(
    @pricelow,
    @pricehigh,
    @priceaverage
);

-- Create stored procedure
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

-- Call stored procedure
CALL productpricing();

-- Delete stored procedure
DROP PROCEDURE productpricing;

Chapter 24 using cursors

Cursors are mainly used for interactive applications, in which users need to scroll the data on the screen, and browse and change the data.

CREATE PROCEDURE processorders()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8, 2);
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    DECLARE CONTINUE HANDLER FOR SQLTATE '02000' SET done=1;

    CREATE TABLE IF NOT EXISTS ordertotals
    (order_num INT, total DECIMAL(8, 2));
    OPEN ordernumbers;
    REPEAT
        FETCH ordernumbers INTO o;
        CALL ordertotal(o, 1, t);
        INSERT INTO ordertotals(order_num, total)VALUES(o, t);
        UNTIL done END REPEAT;
        CLOSE ordernumbers;
END;

Chapter 25 using triggers

Triggers automatically execute statements based on conditions. Only DELETE, UPDATE and INSERT are supported. It is best to use a unique trigger name in a database.
Triggers only support each event of each table, with a maximum of 6 events per table.

## Create trigger
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

## Delete trigger 
DROP TRIGGER newproduct;
## Using triggers 
## INSERT trigger
## Reference a NEW virtual table, access the inserted row, analogy this pointer
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
## BEFORE is usually used for data validation and purification

## DELETE trigger
## Reference to an OLD virtual table, access to the deleted row, read-only, cannot be changed
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW 
BEGIN
    INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

## UPDATE trigger
## BEFORE: NEW,AFTER: OLD
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

Question bank:

Tags: MySQL SQL Database Stored Procedure

Posted on Sun, 07 Jun 2020 04:26:29 -0400 by renegade33