MySQL must know and know PDF network disk download + summary of each chapter

Book download

Link:/s/18g0XgavniXQzp6RlOxnwnQ
Password: j8pm

MySQL must know and know

Chapter 2 Connecting to mysql

Connect mysql in terminal and enter the following command:

mysql -u root -p yourpassword

---

Chapter 3 Connecting to the database

USE crashhouses;
SHOW DATABASES:
SHOW TABLES
SHOW COLUMNS FROM customers;
SHOW STATUS

---

Chapter 4 Retrieve Data

SELECT, DISTINCT, LIMIT statements

SELECT name FROM products;  --Select a single column
SELECT name,id FROM products; --Select multiple columns
SELECT * FROM products;  --Select all columns

SELECT DISTINCT ven_id FROM products  --Return only different values
LIMIT 5;   --Restrictions return no more than 5 rows

SELECT products.name FROM products;  --Use fully qualified table names

---

Chapter 5 Sorting Data

ORDER BY statement

SELECT name FROM products ORDER BY name;  --Sort
SELECT name,id FROM products ORDER BY name，id; --Select multiple columns to sort

--DESC keyword only applies to the column names before it, and cannot partially use DESC.
SELECT name FROM products ORDER BY name DESC，id; --Specify descending order

--LIMIT must be after ORDER BY

---

Chapter 6 Filtering Data

WHERE statement

--ORDER BY is located after WHERE
SELECT name,id FROM products WHERE name = 'lucky' ORDER BY id; 
-- The operators of WHERE include >,=,<,!=,BETWEEN, etc.

-- NULL null value check
SELECT id FROM customers WHERE email IS NULL;

---

Chapter 7 Data Filtering

AND, OR, IN, NOT operators

SELECT id, price FROM products
WHERE id=1003 AND price<=10;

SELECT id, price FROM products
WHERE id=1003 OR id=1000;

-- brackets change the order of calculations
SELECT id, price FROM products
WHERE (id=1003 or id=1000) AND price<=10;

SELECT id, price FROM products
WHERE id IN(1003,1006);

SELECT id, price FROM products
WHERE id NOT IN(1003,1006);

---

Chapter 8: Filtering with wildcards

LIKE operator

-- '%' means any number of times any character appears; '_' only matches a single character
SELECT name FROM products
WHERE name LIKE '_jet%';

---

Chapter 9 Search with regular expressions

REGEXP

LIKE matches the entire column, REGEXP matches within the column value
Specific rules referenceRegular symbols

SELECT name FROM products
WHERE name REGEXP '^jet.*';

---

Chapter 10 Creating Calculated Fields

Concat() splicing string

Perform arithmetic calculations

SELECT id*3+2 AS new_id，
Concat(RTrim(name),'(',LTrim(country),')')
AS vend_title
FROM venders
ORDER BY name;

---

Chapter 11 Using Data Processing Functions

Text processing functions

The function acts directly on the selected column

• Upper(), Lower(), Locate(), Length()
• Left(), Right(), LTrim(), RTrim()
• Soundex(), SubString()

Date time processing function

Search for specific functions when used

Numerical processing functions

• Abs(), Cos(), Exp(), Mod()
• Pi(), Rand(), Sin(), Sqrt(), Tan()

---

Chapter 12 Summary of data

Aggregation processing function

• AVG(), COUNT(), MAX(), MIN(), SUM()

---

Chapter 13 Grouped Data

GROUP BY statement

• When GROUP BY, all columns are calculated together
• The GROUP BY clause contains any number of columns
• Each column listed in the GROUP BY clause must be a search column or a valid expression (can't be an aggregate function). If an expression is used in SELECT, the same expression must be used in the GROUP BY clause instead of the alias
• In addition to the aggregate calculation statement, each column selected in SELECT must be given in the GROUP BY clause.
• NULL returns as a group
• In the GROUP BY clause, after WHERE, before ORDER BY.

SELECT id, name, COUNT(*) AS num_prods
FROM products
WHERE id>1000 AND id < 1005
GROUP BY id, name
ORDER BY id;

HAVING statement

WHERE filter row, HAVING filter group
HAVING does not use alias

SELECT id, name, COUNT(*) AS num_prods
FROM products
WHERE id>1000 AND id < 1005
GROUP BY id, name
HAVING COUNT(*)>= 2
ORDER BY id;

SELECT clause order

The order is as follows: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

---

Chapter 14 Using Subquery

One SELECT clause can be nested in another SELECT statement. Where, the columns must match, that is, they have the same number of columns, and multiple columns can be used.

Using subqueries is not always the most efficient way to execute this type of data. A link table can be used.

Personally, I think that subqueries are a bit cumbersome and can basically be replaced by links.

SELECT id FROM orders
WHERE num IN (	SELECT num FROM orderitems 
				WHERE id ='TNT2');

---

Chapter 15 Connection Table

Use WHERE link

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

Internal connection INNER JOIN …ON

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

Connect multiple tables

---

Chapter 16 Create Advanced Connections

Use alias for tables

Alias ​​the table used, and use alias when applying it.

Use different types of connections

Self-connection

SELECT p1.id,p1.name 
FROM products AS p1, products AS p2
WHERE p1.id=p2.id AND p2.id='DTNTR';

External linkage

LEFT INNER…ON, RIGHT INNER… ON

---

Chapter 17 Combination Query

UNION operator

Add UNION between two SELECT statements

SELECT id FROM products WHERE id>1003
UNION
SELECT id FROM products WHERE id<100;

Contain or cancel duplicate lines

UNION ALL Returns all matching rows, including duplicate rows; default UNION, duplicate rows will be cancelled

---

Chapter 18 Full text search

Match() Against() Full text search

When creating TABLE, the column of FULLTEXT() is for full text search;
The value of Match() must be the same as in the FULLTEXT() definition;

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('rabbit');

Extend WITH QUERY EXPANSION using query

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('rabbit')
WITH QUERY EXPANSION;

Boolean text search IN BOOLEAN MODE

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('rabbit' IN BOOLEAN MODE)

---

Chapter 19 Insert Data

INSERT

// Insert multiple rows
INSERT INTO Customers(name,age)
VALUES('lucky','18')，
('Larry','23');
//Insert the retrieved data
INSERT INTO Customers(name,age)
SELECT(name,age) FROM CustomersOld;

---

Chapter 20 Update and delete data

UPDATE

Can be used to delete specified columns

UPDATE customers
SET name='lucky',age='18'
WHERE id=1000;
//Delete the specified column
UPDATE customers
SET mail=NULL;

DELETE

Can be used to delete specified rows

DELETE FROM customets
WHERE id=10000;
//Quickly delete all rows
TRUNCATE TABLE customers;

---

Chapter 21 Creating and Manipulating Tables

---

Chapter 22 Using Views

---

Chapter 23 Using stored procedures

---

Chapter 24 Using Cursors

---

Chapter 25 Using Triggers

---

Chapter 26: Management of transactions

---

The following chapters are omitted, please read the original book for details.