install

mac: go to the official website to download the installation package and install it
Install - > system preferences – > Click mysql  start mysql server  / usr/local/MySQL/bin/mysql -u root -p at the bottom*******

mysql engine

• InnoDB is a reliable transaction processing engine (see Chapter 26), which does not support full-text search;
• MEMORY is equivalent to MyISAM in function, but because the data is stored in MEMORY (not disk), it is very fast (especially suitable for temporary tables);
• MyISAM is a high-performance engine that supports full-text search (see Chapter 18), but does not support transaction processing.
• Mixed engine type.

where

The where keyword is commonly used in the database to filter queries in the initial table and obtain specified records. It is a constraint declaration that is used to constrain data and takes effect before returning the result set.

group by

Create group

Grouping: grouping the result set of the select query according to one or more columns., Get a set of groups, and then take the value of a specified field or expression from each group. On grouped columns, we can use COUNT, SUM, AVG, and other functions.

    SELECT column_name, function(column_name)
    FROM table_name
    WHERE column_name operator value
    GROUP BY column_name;

WITH ROLLUP The same statistics can be made on the basis of grouped statistics( SUM,AVG,COUNT...). 
For example, query the total login times of each person,

    SELECT coalesce(name, 'total'), SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

• The GROUP BY clause can contain any number of columns. This makes it possible to nest packets and provide more detailed control over data packets.
• If a group is nested in the GROUP BY clause, the data will be summarized on the last specified group. In other words, when grouping is established, all specified columns are calculated together (so data cannot be retrieved from individual columns).
• Each column listed in the GROUP BY clause must be a retrieval column or a valid expression (but not an aggregate function). If you use an expression in SELECT, you must specify the same expression in the GROUP BY clause. Alias cannot be used.
• Except for the aggregate calculation statement, each column in the SELECT statement must be given in the GROUP BY clause.
• If there is a NULL value in the grouping column, NULL is returned as a grouping. If there are multiple rows of NULL values in a column, they are grouped.
• The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.

Filtering grouping

    SELECT cust_id, count(*) as orders from orders groupby cust_id having count(*) > 2;

It is used to filter the groups queried by where and group by to find out the grouping results that meet the conditions. It is a filter declaration, which is used to filter the query results after the query returns the result set.
MySQL provides another clause for this purpose, that is, the HAVING clause. HAVING is very similar to WHERE. In fact, all types of WHERE clauses learned so far can be replaced by HAVING. The only difference is that WHERE filters rows while HAVING filters groups. WHERE filters before data grouping and HAVING filters after data grouping.

Execution sequence

select -> from –> where –> group by –> having –> order by -> limit

Case

mysql is not case sensitive by default and can be specified through the binaer keyword;

select prod_name from products WHERE prod_name REGEXP BINARY 'JetPack .000';

and / or

Update data update

Statement can be used to modify the data in the table. In short, the basic form of use is

    update Table name set Column name=New value where update criteria;

The following is an example in the table students: change the mobile phone number with id 5 to the default -:

    update students set tel=default where id=5;

Increase everyone's age by 1:

    update students set age=age+1;

Change the name of the mobile phone number 13288097888 to "Xiao Ming" and the age to 19:

    update students set name="Xiao Ming", age=19 where tel="13288097888";

When we need to batch modify a specific string in the field to other strings, we can use the following operations:

    UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') [WHERE Clause]；

The following example will update runoob_ Runoob with ID 3_ Replace "C + +" with "Python" for the title field value:

    UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') where runoob_id = 3;

delete

Delete records in MySQL data table,

delete，drop，truncate

Both have the function of deleting tables. The difference is:

• 1. Delete and truncate only delete the table data. Drop deletes the table data and table structure together. For example, delete is a single kill, truncate is a group kill, and drop drops the computer.
• 2. Delete is a DML statement. After the operation, if there is no transaction that you don't want to commit, you can roll back. truncate and drop are DDL statements that take effect immediately after the operation and can't roll back. For example, delete is sending wechat to say goodbye and regret can be withdrawn. truncate and drop slap and roll back directly.
• 3. In terms of execution speed, drop > truncate > delete. For example, drop is the Shenzhou rocket, truncate is the harmony motor car, and delete is the bicycle.

   delete from customers where cust_id = 10086;

like

Match / fuzzy match, will match% and_ Combined use. The percent sign% character is used in the SQL LIKE clause to represent any character, similar to the asterisk * in UNIX or regular expressions. If the percent sign is not used, the LIKE clause has the same effect as the equal sign =. Set runoob_ Get runoob from TBL table_ All records ending in COM in the author field:

    SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';

• '% a' / / data ending with a
• 'a%' / / data starting with a
• '% a%' / / data containing a
• 'a' / / three digits and the middle letter is a
• '_ A '/ / two digits and ending with a
• 'a_' // Two digits with the initial letter A

In the conditional query of where like, SQL provides four matching methods.

• %: indicates any 0 or more characters. It can match characters of any type and length. In some cases, if it is Chinese, please use two percent signs (%%).
• _: Represents any single character. Matches a single arbitrary character, which is often used to limit the character length of an expression.
• []: represents one of the characters listed in parentheses (similar to regular expression). Specify a character, string, or range that requires any of them to match.
• [^]: indicates a single character that is not listed in parentheses. Its value is the same as [], but it requires the matched object to be any character other than the specified character.
  When the query content contains wildcards, we query the special characters "%", "" and "" The statement of "[" cannot be realized normally, but the normal query can be carried out by enclosing the special character with "[]".

regexp matches a regular expression

Find all data starting with 'st' in the name field:

SELECT name FROM person_tbl WHERE name REGEXP '^st';

Find the name field with ' All data starting with t ':

SELECT name FROM person_tbl WHERE name REGEXP '^\\.t'; --Match special characters .t

The difference between like and regexp

// todo
Assume that the current products are as follows:

prod_name
JetPack 1000
JetPack 2000

select prod_name from products where prod_name like '1000' order by prod_name; -- No result
select prod_name from products where prod_name regexp '1000' order by prod_name;  -- return JetPack 1000

Combined query UNION

There are two basic situations in which you need to use a combined query:

• Return data with similar structure from different tables in a single query;
• Execute multiple queries on a single table and return data according to a single query.
  Used to display the data queried in the same column in different tables; (excluding duplicate data);
  MySQL UNION operator is used to connect the results of more than two SELECT statements and combine them into a result set. Multiple SELECT statements delete duplicate data. The application form is as follows:

    -- SELECT Column name FROM Table name UNION SELECT Column name FROM Table name ORDER BY Column name;
    -- Query a list of all items with a price less than or equal to 5
    select vend_id, prod_name, prod_price from products where prod_price <= 5 union select vend_id, prod_name, prod_price from products where vend_id in (1001, 1002) order by prod_price;
    select vend_id, prod_name, prod_price from products where prod_price <= 5 or vend_id in (1001, 1002) order by prod_price;

UNION DISTINCT: optional to delete duplicate data in the result set. By default, the UNION operator has deleted duplicate data, so the DISTINCT modifier has no effect on the result.
UNION ALL: optional, return all result sets, including duplicate data;

• UNION must consist of two or more SELECT statements separated by the keyword UNION (therefore, if four SELECT statements are combined, three UNION keywords will be used).
• Each query in UNION must contain the same columns, expressions, or aggregation functions (although the columns do not need to be listed in the same order).
• Column data types must be compatible: the types need not be identical, but must be types that the DBMS can implicitly convert (for example, different numeric types or different date types).

order by

Sort the query results, DESC descending and ASC ascending; By default, it is arranged in ascending order.

join table

Join by foreign key

Create join

    ---You must use a fully qualified column name when the referenced column may be ambiguous(Table and column names separated by a dot). All connections should be guaranteed WHERE clause
    select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;
    -- The same effect as the previous sentence, inner connection (equivalent connection)
    select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id order by vend_name, prod_name;
    ---Cartesian product(cartesian product) The result returned from a table relationship without a join condition is a 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 order by vend_name, prod_name;
    -- Displays the items in the order number 20005
    select vend_name, prod_name, prod_price, quantity from vendors, orderitems, products where vendors.vend_id = products.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005;

inner join

Get the record of field matching relationship in two tables. Connect the above two tables to read runoob_ All runoobs in TBL table_ The author field is in tcount_ Runoob corresponding to TBL table_ Count field value,

    SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; 

The intersection of two tables.

Self connection

    --Find goods ID by DTNTR Use alias for all products of the supplier
    select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = 'DTNTR');
    -- 👇🏻
    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';

natural join

Standard joins (internal joins introduced in the previous chapter) return all data, even if the same column appears multiple times.
Natural join excludes multiple occurrences, so that each column returns only once.

    -- In this example, wildcards are used only for the first table. All other columns are explicitly listed, so no duplicate columns are retrieved.
    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';

outer join

Joins contain rows that have no associated rows in the associated table. This type of connection is called an external connection.

    -- Retrieve all customers and their orders --> inner Join 
    select customers.cust_id, orders.order_num from customers inner join orders on customers.cust_id = orders.cust_id;
    -- Retrieve all customers, including those without orders. Those that do not exist will be displayed null --> inner Join 
    select customers.cust_id, orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;

External connection -- left join

Get all records in the left table, even if there is no corresponding matching record in the right table. Return the intersection data of all coordinate data and left and right tables;

External connection -- right join

In contrast to LEFT JOIN, it is used to obtain all records in the right table, even if there is no corresponding matching record in the left table.

Join with aggregate function

    -- Retrieve all customers and the number of orders placed by each customer
    select customers.cust_id, customers.cust_name, 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 all customers and the number of orders placed by each customer,Including customers who don't have orders
    select customers.cust_id, customers.cust_name, 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;

null processing

In mysql, the comparison of NULL value with any other value (even NULL) always returns NULL, that is, NULL = NULL returns NULL. IS NULL and IS NOT NULL operators are used to handle NULL in MySQL.

    SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;

transaction

It is mainly used to process data with large amount of operation and high complexity; In MySQL, only databases or tables that use the Innodb database engine can support transactions. Transaction processing can be used to maintain the integrity of the database and ensure that batch SQL statements are either executed or not executed. Transactions are used to manage insert, update and delete statements. Generally speaking, transactions must meet four conditions (ACID): Atomicity (or indivisibility), Consistency, Isolation (also known as independence) and Durability.

• Transaction refers to a set of SQL statements;
• Rollback refers to the process of revoking a specified SQL statement;
• Commit refers to writing the results of SQL statements that are not stored into the database table;
• A savepoint is a temporary place holder set in a transaction. You can post a fallback to it (different from the whole transaction).

    start transaction;
    set autocommit = 0; --Do not automatically commit changes
    operation ZZZ;
    rollback; -- Direct rollback
    operations XXX;
    savepoint A;
    operations YYY;
    commit;
    rollback to A;
    savepoint B;
    release savepoint B; --Delete assignment savepoint

alter

(add new column, drop delete column, modify modify column type, change modify column name, alter modify field default value, rename modify table name)
Modify the data table name or modify the data table field; Delete the i field of the table:

ALTER TABLE testalter_tbl  DROP i; 

ADD clause to ADD columns to the data table:

ALTER TABLE testalter_tbl ADD i INT; 

You can add FIRST or AFTER columnName after the statement to specify the location of the new column.

ALTER TABLE testalter_tbl ADD i INT AFTER c; 

If you need to MODIFY the field type and name, you can use the MODIFY or CHANGE clause in the ALTER command.

ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE i j BIGINT; 

If you need to modify the default value of the field,

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

Modify data table type:

ALTER TABLE testalter_tbl ENGINE = MYISAM;  

Modify table name:

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Define foreign keys:

ALTER TABLE orderitems add constraint fk_orderitems_orders foreign key(order_num) references orders (order_num);

Index index

Indexing can greatly improve the retrieval speed of MySQL; The index is divided into single column index and combined index. Single column index, that is, an index contains only a single column. A table can have multiple single column indexes, but this is not a composite index. Composite index, that is, an index contains multiple columns. In fact, the index is also a table that holds the primary key and index fields and points to the records of the entity table. All the above have said the benefits of using indexes, but excessive use of indexes will lead to abuse. Therefore, the index also has its disadvantages: Although the index greatly improves the query speed, it will reduce the speed of updating the table, such as inserting, updating and deleting the table. Because when updating tables, MySQL should not only save data, but also save index files. Index files that take up disk space when indexing.

    create index indexName on table_name (columnName); // establish
    ALTER table tableName ADD INDEX indexName(columnName) // Modify table structure
    CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL,INDEX [indexName] (username(length))); // Specify when creating
    DROP INDEX [indexName] ON mytable;  // Delete index

Copy table

SHOW CREATE TABLE tableName; Display the statement details of create table. Copy and paste.

    CREATE TABLE targetTable LIKE sourceTable;
    INSERT INTO targetTable SELECT * FROM sourceTable;

Metadata query result information, database and data table information, mysql server information;

Export data

select * from tableName into outfile 'a.txt';

Import data

load data infile；

Calculation field

The data stored in the table is not required by the application. We need to retrieve the converted, calculated or formatted data directly from the database; Instead of retrieving the data and reformatting it in a client application or reporter.

Splice concat

Links values together to form a single value.

    select concat(prod_name,' (',prod_id, ')') from products order by prod_name; --commodity(id)

RTrim

Delete all spaces to the right of the value.

    select concat(RTrim(prod_name),' (', RTrim(prod_id), ')') from products order by prod_name; --commodity(id)

LTrim

Delete all spaces to the left of the value.

    select concat(LTrim(prod_name),' (', LTrim(prod_id), ')') from products order by prod_name; --commodity(id)

Trim

Delete all spaces on the left and right sides of the value.

    select concat(Trim(prod_name),' (', Trim(prod_id), ')') from products order by prod_name; --commodity(id)

New alias for calculated field

For example, change the column name from concat (trim (prod_name), '(', trim (prod_id), '') to prod_title.

    select concat(Trim(prod_name),' (', Trim(prod_id), ')') as prod_title from products order by prod_name; --commodity(id)

Perform arithmetic calculations

Mainly including +, -, *, /, abs(), Now()

    select prod_name, quantity, item_price,  quantity * item_price as expanded_price from products order by prod_name = 'test'; --Of the product

Data processing function (MYSQL must know and know - Chapter 11)

https://forta.com/books/0672327120/

    select prod_name, Upper(prod_name) from products; --Convert to uppercase

Text function	explain
Left()	Returns the character to the left of the string
Right()	Returns the character to the right of the string
Length()	Returns the length of the string
Locate()	Find a substring of the string
Lower()	Convert string to lowercase
Upper()	Convert string to uppercase
Soundex()	Returns the SOUNDEX value of the string
SubString()	Returns the character of the substring
Date function	explain
DateDiff()	Calculate the difference between two dates
Day()	Returns the days portion of a date
Year()	Returns the year portion of a date
CurTime()	Returns the current time
Numerical processing function	explain
Abs()	Returns the absolute value of a number
Cos()	Returns the cosine of an angle
Exp()	Returns the exponent of a number
Sqrt()	Returns the square root of a number

Note: SOUNDEX is an algorithm that converts any text string into an alphanumeric pattern that describes its voice representation. SOUNDEX considers similar pronunciation characters and syllables, making it possible to compare strings rather than letters. Although SOUNDEX is not an SQL concept, MySQL (like most DBMS) provides support for SOUNDEX.
Date format used by MySQL. Whenever you specify a date, whether inserting or updating table values or filtering with the WHERE clause, the date must be in the format yyyy mm DD.

Aggregate data - aggregate function

function	explain
avg()	Returns the average of a column
count()	Return the number of rows in a column
max()	Returns the maximum value of a column
min()	Returns the minimum value of a column
sum()	Return a column and

    select avg(distinct prod_price) from products where vend_id = 1003;

Order database

Database description of the example in this section: the database table is a relational table.
vendors table: stores suppliers who sell products

column	explain
vend_id	Unique supplier ID, auto incremental primary key
vend_name	Supplier name
vend_address	Supplier's address
vend_city	Supplier's City
vend_state	Supplier's state
vend_zip	Postal code of the supplier
vend_country	Supplier's country

products table: contains product catalogs, one product per row

column	explain
prod_id	Unique product ID
vend_id	Product supplier ID (associated with vend_id in vendors table)
prod_name	Product name
prod_price	product price
prod_desc	Product description

Customers table: stores information about all customers

column	explain
cust_id	Unique customer ID, auto incremental primary key
cust_name	Customer name
cust_address	Customer's address
cust_city	Customer's City
cust_state	Customer's state
cust_zip	Customer's postal code
cust_country	Customer's country
cust_contact	Customer contact name
cust_email	Customer's contact email address

Orders table: store customer orders (but not order details). Each order is uniquely numbered (order_num column). Cust for order_ The ID column, which is associated with the unique ID of the customer in the customer table, is associated with the corresponding customer.

column	explain
order_num	Unique order number, primary key of automatic increment
order_date	Order date
cust_id	Customer ID of the order, (related to the cust_id of the customers table)

orderitems table: stores the actual items in each order, and each item in each order occupies one row. order_num and order_item as its primary key, prod_ Define a foreign key on ID and associate it with prod of products_ id.

column	explain
order_num	Order number (order_num associated to the orders table)
order_item	Order item number (order in an order)
prod_id	Product ID (prod_id associated with the products table)
quantity	Number of items
item_price	Item price

Product notes table: stores comments related to a specific product. Not all products have relevant notes, while some products may have many relevant notes. Column note_ Textmust be indexed for FULLTEXT search. Since this table uses full-text search, ENGINE=MyISAM must be specified.

column	explain
note_id	Unique comment ID, primary key
prod_id	Product ID (corresponding to prod_id in the products table)
note_date	Date the note was added
note_text	Note Text

Subquery

Example 1: list all customers who ordered item TNT2

    select * from customers where cust_id in
        (select cust_id from orders where order_num in
            (select order_num from orderitems where  prod_id = 'TNT2'));
    select * from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'TNT2';
        

IN a SELECT statement, subqueries are always processed from the inside out. Subquery is generally used IN combination with IN operator, but it can also be used to test = < >, etc.

Example 2: you need to display the total number of orders for each customer in the customers table.

    -- Related sub query: sub query involving external query. This syntax must be used whenever column names may be ambiguous.
    -- The two columns need to be compared to correctly match the order with their corresponding customers, and a new calculation field is generated orders
    select cust_name, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;
    
    select cust_name, (select count(*) as order_num from orders where orders.cust_id = customers.cust_id group by cust_name) as orderss  from customers order by cust_name;

• First sentence second sentence
  +----------------+--------+ | +----------------+---------+
  | cust_name | orders | | | cust_name | orderss |
  +----------------+--------+ | +----------------+---------+
  | Coyote Inc. | 2 | | | Coyote Inc. | 2 |
  | E Fudd | 1 | | | E Fudd | 1 |
  | Mouse House | 0 | | | Mouse House | NULL |
  | Wascals | 1 | | | Wascals | 1 |
  | Yosemite Place | 1 | | | Yosemite Place | 1 |
  +----------------+--------+ +----------------+---------+

Full text search

MySQL supports several basic database engines. Not all engines support the full-text search described in this book. The two most commonly used engines are MyISAM and InnoDB. The former supports full-text search and the latter does not. This is why although most of the sample tables created in this book use InnoDB, one sample table (product notes table) uses MyISAM.
LIKE keyword, which uses wildcard operators to match text (and partial text). With LIKE, you can find rows that contain special or partial values.
Regular expression text-based search as a further introduction to matching column values. Using regular expressions, you can write very complex matching patterns that find the desired rows.
While these search mechanisms are very useful, there are several important limitations:

• Performance - wildcard and regular expression matching usually requires MySQL to try to match all rows in the table (and these searches rarely use table indexes). Therefore, due to the increasing number of rows being searched, these searches can be very time-consuming.
• Explicit control -- using wildcards and regular expression matching makes it difficult (and not always possible) to explicitly control what matches and what does not match. For example, specify that a word must match, a word must not match, and a word can match or not match only if the first word does match.
• Intelligent results - although search based on wildcards and regular expressions provides very flexible search, none of them can provide an intelligent way to select results. For example, a search for a particular word will return all rows containing that word without distinguishing between rows containing a single match and rows containing multiple matches (ranking them according to possible better matches). Similarly, a search for a particular word will not find lines that do not contain that word but contain other related words.
  When using full-text search, MySQL does not need to view each row separately, and does not need to analyze and process each word separately. MySQL creates an index of each word in the specified column, and the search can be conducted for these words. In this way, MySQL can quickly and effectively determine which words match (which lines contain them), which words do not match, how often they match, and so on.

In order to carry out full-text search, the searched columns must be indexed and re indexed as the data changes. After properly designing the table columns, MySQL will automatically update and re index all indexes.
After indexing, SELECT can be used with Match() and Against() to actually perform the search.

Enable full text search support

When creating a table, add FULLTEXT to the column to be searched. For full-text search, MySQL indexes it according to the instruction of the clause full text (note_text). FULLTEXT here indexes a single column, and multiple columns can be specified if necessary. After definition, MySQL automatically maintains the index. The index is automatically updated as rows are added, updated, or deleted.

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

Note: do not use FULLTEXT to update the index when importing data. It takes time, although not a lot, but it takes time after all. If you are importing data into a new table, FULLTEXT indexing should not be enabled at this time. You should import all data first, and then modify the table to define FULLTEXT. This helps to import data faster (and makes the total time to index data less than the total time required to index each row separately when importing).

Full text search

After indexing, a full-text search is performed using the two functions Match() and Against(), where Match() specifies the column to be searched and Against() specifies the search expression to use.
The value passed to Match() must be the same as in the FULLTEXT() definition. If you specify multiple columns, they must be listed (and in the correct order).

    select note_text from productnotes where note_te like '%rabit%';
    select note_text from productnotes where note_te REGEXP BINARY 'rabit';
    select note_text from productnotes where match(note_text) against('rabit'); -- An important part of full-text search is to sort the results. Rows with higher levels are returned first
    -- todo
    select note_text, match(note_text) against('rabit') as rank from productnotes; -- All rows are returned(Because no WHERE clause),Match()and Against() Used to create a calculated column(Alias rank)，This column contains the rank values calculated by the full-text search. Grade by MySQL It is calculated according to the number of words in the row, the number of unique words, the total number of words in the whole index and the number of rows containing the word. As you can see, no words are included rabbit The row level of is 0(Therefore, it is not used in the previous example WHERE Clause selection). It does contain words rabbit Each of the two lines of the text has a level value. The level value of the line with the front word in the text is higher than that of the line with the back word.

Full text search provides functions that simple LIKE search cannot provide. Moreover, because the data is indexed, full-text search is quite fast.

Use query extension

Query extensions are used to try to broaden the range of full-text search results returned. When using query extension, MySQL scans the data and index twice to complete the search:

• First, conduct a basic full-text search to find all the lines that match the search criteria;
• Secondly, MySQL checks these matching lines and selects all useful words (we will briefly explain how MySQL determines what is useful and what is useless).
• Secondly, MySQL conducts full-text search again, this time not only using the original conditions, but also using all useful words.

    select note_text from productnotes where match(note_text) against('anvils');
    select note_text from productnotes where match(note_text) against('anvils' with query expansion) ; --Seven lines are returned this time. The first line contains words anvils，Therefore, the level is the highest. Second line and anvils Irrelevant, but because it contains two words in the first line(customer and recommend)，So it was also retrieved. Line 3 also contains these two same words, but they are farther back and separated in the text, so it also contains this line, but the level is third. The third line does not involve anvils(By their product name). 

Boolean text search

    select note_text from productnotes where match(note_text) against('anvils -rope*' in boolean mode); -- Match contains anvils But does not contain any rope Line of the beginning word

Full text Boolean operator

Full text Boolean operator	explain
+	Contains, word must exist
-	Exclusion, word must not appear
>	Include, and increase the level value
<	Include and reduce the level value
()	Form phrases into subexpressions (allow these subexpressions to be included, excluded, arranged, etc. as a group)
~	Cancel the sort value of a word
*	Wildcard at the end of the word
""	Define a phrase (unlike a list of individual words, it matches the entire phrase to include or exclude it)

View view

Views are virtual tables. Unlike tables that contain data, views contain only queries that dynamically retrieve data when used.

    select * from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'TNT2';              

This query is used to retrieve customers who have ordered a specific product. Anyone who needs this data must understand the structure of related tables and know how to create queries and join tables. In order to retrieve the same data for other products (or multiple products), the last WHERE clause must be modified.
Now, if you can wrap the entire query into a virtual table called productcustomers, you can easily retrieve the same data as follows:

    select * from productcustomers where prod_id = 'TNT2';              

Product customers is a view. As a view, it does not contain any columns or data that should be in the table. It contains an SQL query (the same query used to correctly join the table above).
View application scenario:

• Reuse SQL statements.
• Simplify complex SQL operations. After writing a query, you can easily reuse it without knowing its basic query details.
• Use parts of the table instead of the entire table.
• 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.
  After views are created, they can be leveraged in much the same way as tables. You can SELECT views, filter and sort data, join views to other views or tables, and even add and update data.
  A view is simply a facility for viewing data stored elsewhere. Views themselves do not contain data, so the data they return is retrieved from other tables. When you add or change data in these tables, the view returns the changed data.

Rules and restrictions for Views:

• Like tables, views must be uniquely named (Views cannot be given the same name as other views or tables).
• There is no limit to the number of views that can be created.
• In order to create a view, you must have sufficient access rights. These restrictions are usually granted by the database administrator.
• Views can be nested, that is, a view can be constructed using queries that retrieve data from other views.
• ORDER BY can be used in a view, but if the data retrieved from the view also contains ORDER BY, the ORDER BY in the view will be overwritten.
• Views cannot be indexed or have associated triggers or defaults.
• Views can be used with tables. For example, write a SELECT statement that joins tables and views.

    -- Create view create view viewname as sql sentence;
    create view productcustomers as select * from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num ;
    -- Show view statements
    show create view viewname;
    -- Delete view
    drop view;
    -- Update the view. Not all views are updatable. There are group queries, sub queries, joins, unions, aggregation functions distinct,The update cannot be performed for the export operation
    create or replace view viewname as XXX;

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.
Using stored procedures has three main benefits: simplicity, security and high performance.

Create stored procedure

    -- Stored procedure that returns the average price of a product
    create procedure productpricing() 
    begin 
        select avg(prod_price) as proceaverage from products;
    end;
    -- Pass parameter version
    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;
    -- Input version
    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
    call ordertotal(20005, @total);
    select  @total;

This stored procedure is called productpricing and is defined with the CREATE PROCEDURE productpricing() statement. If stored procedures accept parameters, they are listed in (). This stored procedure has no parameters, but the () following it is still required. BEGIN and END statements are used to define the stored procedure body. The procedure body itself is only a simple SELECT statement.

Execute stored procedure

CALL accepts the name of the stored procedure and any parameters that need to be passed to it.

    call productpricing(@pricelow, @pricehigh, @priceaverage); --All MySQL All variables must be@start
    select @priceaverage;

Execute a stored procedure called productpricing, which calculates and returns the lowest, highest, and average prices of the product.

Delete stored procedure

After the stored procedure is created, it is saved on the server for use until it is deleted.

    drop procedure productpricing if exists;

Use cursor

Unlike most DBMS, MySQL cursors can only be used for stored procedures (and functions).
Cursors are created with the DECLARE statement (see Chapter 23). DECLARE names the cursor and defines the corresponding SELECT statement with WHERE and other clauses as needed. For example, the following statement defines a cursor named ordernumbers and uses a SELECT statement that can retrieve all orders:

    -- Wear vernier
    create procedure processorders()
    begin
        declare ordernumbers cursor
        for
        select order_num from orders;
    end;
    -- Open cursor
    open ordernumbers;
    -- Close cursor
    close ordernumbers;

After the stored procedure is processed, the cursor disappears (because it is limited to the stored procedure).
CLOSE frees up all internal memory and resources used by cursors, so each cursor should be closed when it is no longer needed.

trigger

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.
  Triggers are not supported by other MySQL statements.
  Only one trigger per table per event is allowed at a time. Therefore, each table supports up to six triggers (before and after each INSERT, UPDATE, and DELETE).
  When creating a trigger, you need to give four pieces of information:
• Unique trigger name. The trigger name must be unique in each table, but not in each database;
• For the tables associated with triggers, only tables support triggers, and views do not support triggers (neither do temporary tables);
• The activity that the trigger should respond to (DELETE, INSERT or UPDATE);
• When the trigger executes (before or after processing).

    create trigger newproduct after insert/update/delete on products for each row select 'product added';
    create trigger newproduct before insert/update/delete on products for each row select 'product added';
    drop trigger newproduct; --Triggers cannot be updated or overwritten

create trigger is used to create a new trigger named newproduct. The trigger can be executed before or after an operation. AFTER INSERT is given here, so this trigger will be executed after the INSERT statement is successfully executed. This trigger also specifies FOR EACH ROW, so the code executes on each inserted row. In this example, the text Product added will be displayed once for each inserted line.

Globalization and localization

This paper introduces the basic knowledge of MySQL dealing with different character sets and languages.
Database tables are used to store and retrieve data. Different languages and character sets need to be stored and retrieved in different ways. Therefore, MySQL needs to adapt to different character sets (different letters and characters) and different methods of sorting and retrieving data.
MySQL supports many character sets. To view a complete list of supported character sets, use the following statement: show character set; This statement displays all available character sets and the description and default proofing for each character set.
View the complete list of supported proofreading show collation;

security management

Provide users with the access they need and only the access they need. This is called access control. To manage access control, you need to create and manage user accounts.

    select * from user;
    create user A identified by 'p@qqqqq'

Database maintenance

improve performance

git address