MySQL must know must know 19-23 chapters

Chapter 19-23

The nineteenth chapter

INSERT is used to INSERT (or add) rows to database tables. There are several ways

·Insert full row

·Insert part of row

·Insert multiple rows

·Insert results of some queries

MySQL security can be used for each table or user to prohibit the use of INSERT statements

Insert the complete row, requiring the table name and the value of the new row to be inserted

INSERT INTO customers
VALUES(NULL,
       'Pep E. LaPew',
       '100 Main Street',
       'Los Angeles',
       'CA',
       '90046',
       'USA',
       NULL,
       NULL);

In this format, each column must provide a value, and the order should be the same as that of the defined column. If there is no value, fill in NULL (assuming that the column is allowed to be NULL). If a column is automatically incremented by MySQL, it is also specified as NULL, but this syntax is not safe (the structure of the table may change in the future), so you should avoid using it as much as possible

A safer way

INSERT INTO customers(cust_name,
            cust_address,
            cust_city,
            cust_state,
            cust_zip,
            cust_country,
            cust_contact,
            cust_email)
VALUES('Pep E. LaPew',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL);

The VALUES in VALUES are filled in the previously specified order (not in the order of the original table). This method can work correctly even if the structure of the table changes. Because the value of cust "ID is unnecessary, it can be avoided

One of the following conditions can be omitted in the INSERT statement

·The column is defined to allow NULL values (no value or NULL value)

·Give a default value in the table definition, meaning that if no value is given, the default value will be used

If data retrieval is more important, you can add the keyword "low" priority between INSERT and INTO to reduce the priority of INSERT

INSERT multiple rows can be inserted multiple times with multiple INSERT statements, or as long as the column names and order in each INSERT statement are the same, it can be in the following form

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'
);

Each set of values is enclosed by a pair of parentheses separated by commas, which is faster than using multiple INSERT statements

INSERT the retrieved data, which is composed of an INSERT statement and a SELECT statement. For example, if we want to merge the customer list from a table into the customers table, we don't need to read one row at a time and INSERT it again. (suppose there is a table named customer, whose structure is the same as that of customers, please note that the customer ID in the customer table can't be the same as that in customers or omit the value of this column in the import to another MySQL generated when table)

INSERT INTO customers(cust_id,
	cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT cust_id,
	   cust_contact,
	   cust_email,
	   cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country
FROM custnew;

If this table is empty, it will not be inserted, which is legal. You can also omit cust "ID column in INSERT and SELECT, with MySQL generated value

The column names in INSERT and SELECT do not have to be the same. MySQL uses the location of the column. The first column in SELECT will be used to fill in the first column specified by INSERT This is useful for importing data from tables with different column names

In INSERT SELECT, the SELECT statement can contain a WHERE clause to filter the inserted data

The twentieth chapter

UPDATE (modify) the data in the table can use UPDATE, which can UPDATE specific rows or UPDATE all rows

UPDATE is mainly composed of the updated table, column name, new value and filter condition to determine the updated row. Pay attention to the importance of WHERE clause

If customer 10005 now has an email address, it needs to be updated

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

If there is no WHERE clause, all rows will be updated

Update more columns

UPDATE customers
SET cust_name = 'The Fudds',
	cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

Subqueries can also be used in UPDATE

When updating multiple rows, cancel the whole operation to avoid errors in one or more rows (the previously updated rows will be restored to the original values after cancellation) and write UPDATE IGNORE customers

To delete the value of a column, you can set it to null (if NULL is allowed)

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

Use DELETE to DELETE a specific row or all rows. Also, do not omit the WHERE clause

DELETE FROM customers
WHERE cust_id = 10006;

Without the WHERE clause, every customer in the table will be deleted

DELETE deletes the entire row instead of the column. UPDATE is used for deleting the column, but DELETE does not DELETE the table itself

Delete all rows with TRUNCATE TABLE statement, which is faster (actually delete the original table and rebuild a table, rather than delete row by row)

Principles for updating and deleting

·Unless you do intend to modify each row, you must use the WHERE clause

·Ensure that each table has a primary key, and use it as much as possible like the WHERE clause

·You can first use SELECT to test whether the used WHERE filters the correct rows to ensure the correctness

·Use a database that enforces referential integrity so that MySQL will not allow rows with data associated with other tables to be dropped

·MySQL has no undo button. Use UPDATE and DELETE carefully

Chapter 21

You can use CREATE TABLE to create tables. In fact, when using interactive tools, the interface tools generate corresponding MySQL statements and execute them

To create a new table, you need to give the name of the table, the name and definition of the table column, or you can give more information

CREATE TABLE customers
(
	cust_id			int			NOT NULL AUTO_INCREMENT,
    cust_name		char(50)	NOT NULL,
    cust_address	char(50)	NULL,
    cust_city		char(50)	NULL,
    cust_state		char(5)		NULL,
    cust_zip		char(10)	NULL,
    cust_country	char(50)	NULL,
    cust_contact	char(50)	NULL,
    custy_email		char(255)	NULL,
    PRIMARY KEY (cust_id)
)	ENGINE=InnoDB;

Indent size and space between columns are not specified

The table name follows CREATE TABLE. The definition of the table is in parentheses. The definition of each column starts with the column name, followed by the data type of the column. The primary key of the table can be specified when creating the table

When creating a new table, the specified table name must not exist. To prevent accidental overwriting of the existing table, delete the table manually first. You can give if not exist after the table name to check whether the table name exists and create it when the table name does not exist

If NOT NULL is specified, there must be a value at the time of insertion. NULL is the default setting. If NOT NULL is not specified, it is considered as NULL

Note that NULL is not an empty string, and the NULL value has no value. If you specify '' (two single quotes, no characters in between), this is allowed in the NOT NULL column

If the primary key uses more than one column, the combined values of these columns must be unique, and the columns are separated by commas when the table is created

CREATE TABLE orderitems
(
	order_num		int			NOT NULL,
 	order_item		int			NOT NULL,
    prod_id			char(10)	NOT NULL,
    quantity		int			NOT NULL,
    item_price		decimal(8,2)NOT NULL,
    PRIMARY KEY(order_num, order_item)
)	ENGINE=InnoDB;

The primary key can also be defined after the table is created. Only columns that do not allow NULL can be used

Auto increment tells MySQL that when a row is added to this column, it will be automatically incremented. In this way, each row will be assigned a unique cust? ID. only one auto increment column is allowed for each table, and it must be indexed (for example, by making it a primary key)

To override auto increment, you can simply specify a value in the INSERT statement. As long as it does not appear before, the value will replace the automatically generated value, and subsequent increments will start to use the manually inserted value

SELECT last_insert_id()

The statement returns the last auto increment value

If no value is given when inserting a row, MySQL allows you to specify the default value to use at this time

CREATE TABLE orderitems
(
	order_num	int			NOT NULL,
    order_item	char(10)	NOT NULL,
    prod_id		char(10)	NOT NULL,
    quantity	int			NOT NULL DEFAULT 1,	
    item_price	decimal(8,2)NOT NULL,
    PRIMARY KEY (order_num, order_item)
)	ENGINE=InnoDB; 

Use quantity 1 without giving quantity, but function is not allowed as default value, only constant is supported

MySQL has an internal ENGINE for managing and processing data. When using CREATE TABLE or SELECT statements, the ENGINE processes requests. MySQL has multiple engines with different functions and features. If the ENGINE = statement is ignored, the default ENGINE (most likely MyISAM) is used

·InnoDB is a reliable transaction engine and does not support full-text search

·MEMORY is functionally equivalent to MyISAM, but data is stored in MEMORY (not disk), which is fast and suitable for temporary tables

·MyISAM has very high performance. It supports full-text search, but does not support transaction processing

Engine types can be mixed, such as MyISAM for one table and InnoDB for the other, but there is a flaw that foreign keys cannot cross engines, that is, tables of one engine cannot reference foreign keys of tables of different engines

You can use ALTER TABLE to update tables

To add a column named vend_phone to the vendors table, you must specify its data type

ALTER TABLE vendors
ADD vend_phone CHAR(20);

Delete the column you just added

ALTER TABLE vendors
DROP COLUMN vend_phone;

You can also define foreign keys

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) 
REFERENCES orders (order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products
FOREIGN KEY (prod_id) 
REFERENCES products (prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (cust_id) 
REFERENCES customers (cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY (vend_id) 
REFERENCES vendors (vend_id);

If you make multiple changes to a single table, you can use a single ALTER TABLE statement, with each change separated by commas

Generally, complex table structure changes need to be deleted manually. You should make a backup before using ALTER TABLE to change the table. Changes to database tables cannot be undone

Delete table (the whole table but not its contents), can't be undone, permanently delete

DROP TABLE customers2;

rename table

RENAME TABLE customers2 TO customers;

Rename multiple tables

RENAME TABLE backup_customers TO customers,
			 backup_vendors TO vendors,
			 backup_products TO products;

Chapter Twenty-Two

A view is a virtual table. The difference is that a view contains only queries that retrieve data dynamically when in use

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

It would be very convenient to wrap the whole query as a virtual table named product customers

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

As a view, it does not contain any columns or data in the table. It contains an SQL query

You can use them in the same way as tables, you can perform SELECT operations, filter sorting data, join to other tables or views, and so on

Using view can simplify operation, and can be reused many times after writing query. It only uses part of table to protect data

Rules and restrictions on the use of views

·The name should be unique, but there is no limit to the number

·Views can be nested, queries that retrieve data from other views to construct a view

·ORDER BY can be used in a view. If the view's SELECT statement also contains ORDER BY, the original ORDER BY will be overwritten

View is created with the CREATE VIEW statement

Use SHOW CREATE VIEW VIEWNAME; to view the statement that created the view

Using DROP VIEW viewname; to delete a view

When updating a view, you can use DROP first and then CREATE, or you can directly use CREATE OR REPLACE VIEW. If the view to be updated does not exist, the second update statement will CREATE a view, and if it exists, it will replace it

One of the most common applications of views is to hide complex SQL, which usually involves joins

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;    

This view returns a list of all customers who have ordered any product

implement

SELECT * FROM productcustomers

Customers who have ordered any product will be listed

To retrieve customers for product TNT2

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

You can reformat the retrieved data with a view

CREATE VIEW vendorlocations AS
SELECT Contact(RTrim(vend_name), ' (', RTrim(vend_country), ')')
	   AS vend_title
FROM vendors
ORDER BY vend_name;

This statement creates a view using the same query as the previous SELECT statement, in order to retrieve the data to create all mail labels

SELECT *
FROM vendorlocations;

You can use views to filter unwanted data, such as customers without email

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

You can now use this view as you would any other table

SELECT *
FROM customeremaillist;

If you also have a WHERE clause when using a view, it is automatically combined with the WHERE clause in the view

Views are particularly useful for simplifying the use of calculated fields

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
	   prod_id,
	   quantity,
	   item_price,
	   quantity*item_price AS expanded_price
FROM orderitems;

To retrieve the details of order 20005

SELECT *
FROM orderitemexpanded
WHERE order_num = 20005;

The view is updatable. You can use INSERT, UPDATE, and DELETE to update a view. If you add or delete rows to a view, you are actually adding or deleting rows to its base table. However, not all views can be updated. If you have the following operations in the view definition, you cannot update the view

·GROUP BY and HAVING

Connectionist

Sub query

Merge

·Aggregate function (Min(), Count(), Sum(), etc.)

· DISTINCT

·Export (calculate) columns

Chapter 23

A stored procedure is simply a collection of one or more MySQL statements saved for later use

Advantages of using stored procedures: simple, safe and high performance

MySQL calls the execution of a stored procedure as a CALL, and uses CALL to accept the name of the stored procedure and any such parameter that needs to be passed to it

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

Stored procedures can display results or not

Create a stored procedure that returns the average price of a product

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

If the stored procedure accepts parameters, they can be listed in (). BEGIN and END are used to define the stored procedure body. The created procedure does not return data, but is created for later use

In the command line mode, because there are two semicolons in the code, there will be errors. You can temporarily change the separator, and any character can be used as statement separator except \

DELIMITER //

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END //

DELIMITER ;

Use this process

CALL productpricing();

Stored procedure is actually a function

After the stored procedure is created, it is saved on the server for use until it is deleted. The deletion statement is as follows

DROP PROCEDURE productpricing;

Note that there is no ()

If you are not sure whether the procedure to be deleted exists, use DROP PROCEDURE IF EXISTS

The general stored procedure does not display the result, but passes the result to the specified variable

The following is a modified version of productpricing (to be deleted if it already exists)

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;

In the SELECT statement, the order of the INTO clause and the FROM clause is interchangeable

This stored procedure accepts three parameters, each of which must have the specified type. Here, decimal system is used. The keyword OUT indicates that the corresponding parameter is used to transfer a value from the stored procedure. MySQL also supports IN (pass to stored procedure), INOUT (pass IN and pass OUT), SELECT statement to retrieve the value and save it to the corresponding variable through INTO

Call this stored procedure

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

To display the average price of retrieved products

SELECT @priceaverage;

Get three values

SELECT @pricehigh, @pricelow, @priceaverage;

Using the IN and OUT parameters, ordertotal accepts the order number and returns the total of the order

CREATE PROCEDURE ordertotal(
	IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity)
	FROM order_items
	WHERE order_num = onumber
	INTO ototal;
END;

INTO clause can follow SELECT

Call new stored procedure

CALL ordertotal(20005, @total);

Display total

SELECT @total;

To get the total display of another order, call the stored procedure again and display the variable again

CALL ordertotal(20009, @total);
SELECT @total;

Building intelligent stored procedures

CREATE PROCEDURE ordertotal(
	IN onumber INT
	IN taxable BOOLEAN
	OUT ototal DECIMAL(8,2)
)	COMMENT 'Obtain order total, optionally adding tax'
BEGIN

	DECLARE total DECIMAL(8,2);
	DECLARE taxrate INT DEFAULT 6;
	
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	
	IF taxable THEN
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
	
	SELECT total INTO ototal;
	
END;

IF statement checks whether business tax needs to be increased to the local variable total, and finally saves the total to total with another SELECT statement

The COMMENT value is not required, if given, it will be displayed in the result of SHOW PROCEDURE STATUS

Test this stored procedure

CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;

BOOLEAN value non-zero (including negative number) is true, 0 is false

IF also supports ELSE IF (the former uses the THEN clause, the latter does not)

To display the CREATE statement used to CREATE a stored procedure

SHOW CREATE PROCEDURE ordertotal;

To get a list of stored procedures with details such as when and by whom

SHOW PROCEDURE STATUS

The above statement lists all stored procedures, which can be filtered with LIKE

SHOW PROCEDURE STATUS LIKE 'ordertotal';
76 original articles published, 7 praised, 9478 visited
Private letter follow

Tags: Stored Procedure MySQL Database SQL

Posted on Mon, 24 Feb 2020 03:02:21 -0800 by sathyan