🔎 MySQL notes

Retrieve data

• Retrieve different rows

  select distinct vend_id from products
  

• Limit the result, that is, limit the number of rows output

  select prod_name from products LIMIT 4,5
  //Indicates that five rows of data are retrieved from the fourth row. The first is the starting position and the second is the number of rows to be retrieved
  

  The first row retrieved is limit 0, 1. LIMIT 1,1 retrieves the second row instead of the first row

Sort and retrieve data

If you need to sort the retrieved data, you can use ORDER BY. ORDER BY takes the name of one or more columns and sorts the output accordingly.

select prod_name from products ORDER BY prod_name

The columns here are not necessarily the retrieved columns, but also the columns that have not been retrieved

If you need to sort multiple columns, you can add the corresponding column to the row. For example, you need to sort the retrieved goods by price first, and sort the goods with the same price by name. You can use the following statement:

select prod_id，prod_price，prod_name from products ORDER BY prod_price，prod_name；

The default sorting is ascending and descending DESC

Filter data

Database tables generally contain a large amount of data, and rarely retrieve all rows in the table. Usually, only a subset of the table data will be extracted according to the needs of specific operations or reports. To retrieve only the required data, you need to specify search criteria, which is also called filter criteria

In the SELECT statement, the data is filtered according to the search criteria specified in the where clause. If ORDER BY and where are used at the same time, where should be the first. Otherwise, an error will occur

Mismatch check

If you want to retrieve all products not manufactured by supplier 1003, you should use the following statement

select vend_id,prod_name from products where vend_id <> 1003

Check NULL value

select prod_name from products where prod_price IS NULL

Data filtering

The previous filtering data adopts a single condition. In order to carry out stronger filtering control, MYSQL allows multiple WHERE sentences, which can be used in the way of AND OR

1. AND operator

select prod_id，prod_price，prod_name from products where vend_id =1003 AND prod_price<=10

1. OR operator

select prod_name，prod_price from products where vend_id=1002 OR vend_id=1003

If AND and AND OR appear at the same time, AND will be processed first. Therefore, if you want to process OR first, you can add () to the OR sentence, AND the parentheses have higher priority

1. IN operator

Parentheses are also used in WHERE sentences to specify the range of conditions, and each condition in the range can be matched

select prod_name，prod_price from products where vend_id IN (1002,1003) ORDER BY prod_name

1. NOT operator

The NOT operator in the WHERE clause has only one function. That's any condition after negation

select prod_name，prod_price from products where vend_id NOT IN (1002,1003) ORDER BY prod_name

Wildcard filtering

Wildcard: used to match special characters that are worth a portion

Search mode: search criteria consisting of literals, wildcards, or a combination of both

The wildcard itself is actually a character with special meaning in the WHERE clause of SQL

The LIKE operator must be used to use wildcards in search sentences

• %Wildcard

  %Indicates that any character occurs any number of times, including 0 times. For example, the following statement indicates that all products starting with jet are found

  select prod_id,prod_name from products where prod_name LIKE 'jet%'
  

• Underscore () wildcard

  Indicates a match of one character

Create calculated field

The data stored in the database table is generally not in the format required by the application. If you want to get the format you want, you can use the calculation field, which does not actually exist in the database table. Calculated fields are created within a SELECT statement at run time

Splice field

In the SELECT statement of MYSQL, you can use the Concat() function to splice two columns

select Concat(vend_name,'(',vend_country,')') from vendors ORDER BY vend_name

At the same time, you can use trim (field name) to eliminate the spaces at both ends of the field. If you only want to delete the one on the right, use RTrim(), and the one on the left is LTrim().

SQL supports aliases. Aliases are assigned with the AS keyword

Perform arithmetic calculations

Another purpose of calculation field is to perform arithmetic calculation on the retrieved data

select prod_id，quantity，item_price,quantity*item_price AS expand_price  from orderitems where order_num=2005;

Using data processing functions

function

SQL supports the use of functions to process data. Functions are generally executed on data, which provides convenience for data conversion and processing

Text processing function

Upper()

Date and time handler

Date and time are stored in corresponding data types and special formats, so that they can be sorted or filtered quickly and effectively, and save physical storage space

If the Date format in the database table is yyyy MM DD HH: mm: SS, how to match the data? If you only know the specific year, month and day, but do not know the hour, minute and second, you can use the Date function to extract the year, month and day of the Date to match.

select cust-id,order_num from orders where Date(order_date) = '2005-09-01'

If you want to match the data of a month

select cust_id,order_num from orders WHERE YEAR(order_date) = 2005 AND Month(order_date) = 9;

Numerical processing function

Numerical processing functions generally only process numerical data

Summary data

Aggregate function

We often need to aggregate data without actually retrieving them

An aggregate function is a function that runs on a row group and calculates and returns a single value

1. AVG() function

   Rows with NULL values are ignored

   This function is mainly used to calculate the average value of the column

   select AVG(prod_price) AS avg_price from products
   

   It can also be used to calculate the average value of a specific column

   select AVG(prod_price) as avg_price from products where vend_id = 1003
   

2. COUNT() function

   COUNT (*) counts the number of rows, regardless of whether the table column contains NULL values

   COUNT (column) counts the values in a specific column, excluding NULL values

3. MAX() function

   MAX() returns the maximum value in the specified column

4. The MIN() function returns the minimum value in the specified column

5. SUM() function

   Returns the specified column value and ignores NULL values

Data grouping

Grouping allows us to divide data into multiple logical groups so that aggregation calculations can be performed on each group

Create group

Grouping is established in the GROUP BY clause. The GROUP BY clause knowledge Mysql groups data, and then aggregates each group instead of the whole result

select vend_id,COUNT(*) AS num_prods from products GROUP BY vend_id

The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause

Filter grouping

In addition to grouping data with GROUP BY, MYSQL also allows filtering groups, specifying which groups to include and which groups to exclude. HAVING is similar to WHERE, except that HAVING filters groups but WHERE filters rows

select cust_id,COUNT(*) AS orders from orders GROUP BY cust_id HAVING COUNT(*)>=2

Difference between HAVING and WHERE

where filters before data grouping and HAVING filters after data grouping

Grouping and sorting

SELECT clause order

Use subquery

Subqueries are queries nested in other queries

select cust_id from orders where order_num IN (select order_num from orderitems WHERE prod_id = 'TNT2')

Join table

Create join

select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name

You must use fully qualified column names (table and column names separated by a dot) when the referenced column may be ambiguous

You should ensure that all joins have a WHERE clause. Otherwise, a lot of useless data will be queried

Internal connection

select vend_name,prod_name,prod_price from vendors INNER JOIN products ON vendors.vend_id = products.vend_id

When using INNER JOIN, the connection condition is ON instead of WHERE

Create advanced join

Use table alias

select cust_name,cust_contat from customers AS c,orders AS o,orderitems AS oi where c.cust_id = o.cust_id AND oi.order_num = o.order_num

Self connection

select prod_id, prod_name from products WHERE vend_id = (select vend_id from products WHERE prod_id  = 'DTNTR')

Same query using 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'

Self join is usually used as an external statement instead of a subquery statement used to retrieve data from the same table. Although the final result is the same, using joins is much faster than processing subqueries

outer join

LEFT OUTER JOIN queries all rows on the left side of a statement, while RIGHT OUTER JOIN queries all rows on the right side of a statement

Combined query

Most SQL queries contain only a single SELECT statement of the data returned in one or more tables. MYSQL also allows multiple queries (multiple SELECT statements) to be executed and the results to be returned as a single query result set. These combined queries are often referred to as union or compound queries

Use UNION

Give each query statement and put the keyword UNION between each statement

Single statement

select vend_id,prod_id,prod_price from products WHERE prod_price<=5

select vend_id,prod_id,prod_price from products WHERE vend_id IN (1001,1002)

Combined statement

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)

⚠️ be careful

• A UNION must consist of two or more SELECT statements separated by the keyword UNION
• Each query of UNION must contain the same column, expression or aggregation function

Include or remove duplicate rows

When using UNION, duplicate rows will be automatically removed. If you want all matching rows, you can use UNION ALL

Sort combined query results

When using UNION, only one ORDER BY clause can be used. It must appear after the last SELECT statement. Multiple ORDER BY clauses are not allowed

insert data

The inserted data mainly includes the following parts:

1. Insert complete row
2. Insert part of row
3. Insert multiple rows
4. Insert the results of some queries

Insert complete row

Insert a complete record. You only need to specify the table name and the inserted value

INSERT INTO Customers VALUES (NULL,'zs','100 main street','USA');

Although this syntax is simple, it is not safe and should be avoided as far as possible. The above SQL statements are highly dependent on the definition order of the columns in the table and the information obtained from their order

The safer way is as follows

INSERT INTO  Customers ('state','name','Address','state') VALUES (NULL,'zs','100 main street','USA');

Insert multiline data

Multiple insert data statements can be separated by semicolons

Insert retrieved data

Another form of INSERT statement is to INSERT the selected statement into the table, which is the so-called INSERT SELECT

Update and delete data

Update data

UPDATE statement can be used to update the data in the table. There are two ways to update

1. Update specific rows in the table
2. Update all rows in the table

The update statement consists of three parts:

• Table to update
• Column names and their new values
• Determine the filter criteria for the row to update

UPDATE customers SET cust_name = 'zs',cust_eamil = '268@qq.com' WHERE cust_id = 12

Delete data

DELETE statement is used to DELETE data, which is mainly divided into two methods; DELETE a specific row; DELETE all rows

DELETE FROM customers where cust_id  = 12;

Creating and operating tables

Create table

Create table base

To create a table using CREATE TABLE, you need to give the following information:

• The name of the new table is given after the above keyword
• Names and definitions of table columns, separated by commas

CREATE TABLE customers
(
cust_id    int   NOT NULL AUTO_INCREMENT,
cust_name  char(50) NOT NULL,
cust_city  char(50) NULL,
PRIMARY KEY (cust_id)
) ENGINE = InnoDB

If you need to specify a default value, add DEFAULT xxx after NULL or NOT NULL

Update table

To update the definition of a table, you can use the ALTER TABLE statement

• Add a column

  ALTER TABLE venders ADD vend_phone CHAR(20);
  

• Delete column

  ALTER TABLE venders DROP COLUMN vend_phone
  

• Define foreign keys

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

Delete table

DROP TABLE xxx

Use view

view

A view is a virtual table. Unlike a table containing data, a view only contains queries for dynamically retrieved data

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

Why use views

Common applications of view:

• Reuse SQL statements
• Simplify complex SQL operations. After writing a query, you can easily reuse it without knowing its basic details
• Use parts of the table instead of the entire table
• To protect data, users can be granted access to specific parts of the table instead of the entire table
• Change the data format and presentation. Views can return data that is different from the representation and format of the underlying table

Rules and restrictions of view

1. Views must be uniquely named
2. There is no limit to the number of views that can be created
3. In order to create a view, you must have sufficient access rights
4. Views can be nested, that is, a view can be constructed by querying the data retrieved from other views
5. ORDER BY can be used in views
6. Views cannot be indexed or have associated triggers or defaults
7. Views can be used with tables, for example, by writing a join statement and a SELECT statement for a view
8. Views can be used with tables

Use view

View creation

Use the CREATE VIEW statement to create

Use DROP VIEW viewname to delete the view

Using views to simplify complex joins

CREATE VIEW productcustomers AS SELECT cust_name，cust_contact，pro_id from customers，orders，orderitems 
WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num

Use views to filter unwanted data

CREATE VIEW customeremailList AS SELECT cust_id,cust_name,cust_eamil FROM custmoers WHERE cust_eamil IS NOT NULL

Using stored procedures

stored procedure

A stored procedure is simply a collection of one or more MYSQL statements saved for future use. They can be considered batch files, although their role is not limited to batch processing

Why use stored procedures

• Complex operations are simplified by encapsulating processing in easy-to-use units
• Since it is not required to repeatedly establish a series of processing steps, this ensures the integrity of the data. If all developers and applications use the same stored procedure, the code used is the same
• Simplify the management of changes. If the table name, column name or business logic changes, you only need to change the code of the stored procedure
• Improve performance because using stored procedures is faster than using separate SQL statements

In other words, using stored procedures has three main benefits: simplicity, security, and high performance

Execute stored procedure

MYSQL calls the execution CALL of a stored procedure, so the statement executing the stored procedure is CALL. CALL accepts the name of the stored procedure and any parameters that need to be passed to it

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

Stored procedures can display results or not

Create stored procedure

eg: 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 there are parameters, write them in ()

Delete stored procedure

DROP PROCEDURE productpricing;

There is no ()

Use parameters

Generally, the stored procedure does not display the result, but returns the result to the variable you specify

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
	FORM products；
	SELECT Avg(prod_price)
	INTO pa
	FROM products;
END;

When called, this statement does not display any data. It returns variables that can be displayed later

Displays the average price of the product

select @priceaverage

There are input types

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

Call the above stored procedure call orderotal (2005, @ total)

Using triggers

MYSQL statements are executed when needed, as are stored procedures. If you want a statement to execute when an event occurs, you can use a trigger

For example:

• Whenever a customer's phone number is added to the data sheet, check whether its mobile phone number format is correct
• Whenever a product is ordered, the ordered quantity is subtracted from the data quantity
• Whenever a row is deleted, a copy is kept in an archive table

All of the above involve the use of triggers

A trigger is a MYSQL statement (or a group of statements between BEGIN and END statements) that MYSQL automatically executes in response to any of the following statements

DELETE INSERT UPDATE these statements support triggers

Create trigger

Information required to create trigger

1. Unique trigger name
2. Table associated with trigger
3. The activity that the trigger should respond to
4. When does the trigger execute

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'

Each table supports up to 6 triggers (before and after each INSERT, UPDATE and DELETE)

Delete trigger

DROP TRIGGER newproduct；

Using triggers

INSERT trigger

• Within the INSERT trigger code, you can reference a virtual table named NEW. Access the inserted row
• In the BEFORE INSERT trigger, the value in NEW can also be updated (it is allowed to change the inserted value)
• For Auto_ In the increment column, NEW contains 0 before the execution of INSERT and NEW automatically generated values after the execution of INSERT

CREATE TRIGGER neworder AFTER INSERST ON orders FOR EACH ROW SELECT NEW.order_num

Management transactions

Using ROLLBACK

select * from ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

Using COMMIT

General MYSQL statements are directly executed and written for data tables. This is the so-called implicit COMMIT, that is, the COMMIT is automatic, but the COMMIT will not be implicit in the transaction block. For explicit COMMIT, use the COMMIT statement

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20021;
COMMIT;