First, SQL foundation

• Database concept: A container that holds organized data.
• Table: A structured list of data of a particular type.
• Schema: Information about the layout and features of databases and tables. Tables have features that define how data is stored in a table, such as what kind of data can be stored, how the data is decomposed, how the pieces of information are named, and so on.
• Column: A field in a table. All tables are composed of one or more columns.
• Data type: The type of data allowed. Each table column has a corresponding data type that limits (or allows) the data stored in that column.
• Row: A record in a table.
• Primary key: A column (or set of columns) whose value uniquely distinguishes each row in a table.
  • A primary key is used to represent a particular row. Without a primary key, updating or deleting a particular row in a table is difficult because there is no secure way to ensure that only the relevant rows are involved.
  • Best practice for primary keys: Do not update values in primary key columns; Do not reuse primary key column values; Do not use values that can change in primary key columns.
• Structured Qurey Language (SQL) : Structured query Language.

MySQL foundation

• The server part is the one piece of software responsible for all data access and processing. The software runs on a computer called a database server.
• Only server software deals with data files. All requests for data, data addition, deletion, and data updates are made by the server software. These requests or changes come from the computer running the client software.
• mysql -u root -proot -P 3306The password and port number of user root are root and 3306 respectively
• Command input inmysql>after
• Command to use;or\gThe end of the
• helpor\hGet help. Such ashelp select,\h select.
• The inputexitorquitExit the command line utility.

3. SQL syntax

3.1 Selecting a Database.

• The database must be opened before it can be read.

USE database_name;   Select the database named database_name
Copy the code

3.2 Display all existing database names

SHOW DATABASES;
Copy the code

3.3 Display all tables in a database

USE bookstore;
SHOW TABLES;
Copy the code

3.4 Displaying information about columns in a table

SHOW COLUMNS FROM book_list;  Display all column information in one table
DESCRIBE book_list;  The two commands are equivalent
Copy the code

Output, showing the field name, data type, whether NULL is allowed, key information, default values, and other information (such as auto_INCREMENT for field CUST_ID).

+-----------+------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar (100) | NO | | NULL | | | price | a decimal (10, 2) unsigned | NO | | NULL | | | author |  varchar(100) | YES | | NULL | | | sales | int(11) | YES | | NULL | | | inventory | int(11) | YES | | NULL | | +-----------+------------------------+------+-----+---------+----------------+Copy the code

3.5 Automatic Increment

• Some table columns require unique values, and as each row is added to a table, MySQL can automatically assign the next available number to each row instead of manually assigning a unique value when a row is added.auto_increment

3.6 Display extensive server status information

SHOW STATUS;
Copy the code

Fourth, retrieve data

4.1 Retrieving a single column

SELECT id
FROM book_list;
Copy the code

The returned data may or may not be in the order in which it was added to the table. As long as the same number of rows are returned, this is normal.

4.2 Retrieve multiple columns

SELECT id, `name`
FROM book_list;
Copy the code

4.3 Retrieve all columns

SELECT *
FROM book_list;
Copy the code

While using wildcards may save you the trouble of explicitly listing the required columns, retrieving unneeded columns often reduces the performance of retrieving and unused programs. So, better not use it.

4.4 Retrieve different rows

SELECT DISTINCT author
FROM book_list;
Copy the code

Show only different lines, not duplicate ones.

4.5 Limit the number of lines in the output result

SELECT *
FROM book_list
LIMIT 5;  Only the first 5 lines are displayed
Copy the code

SQL default first behavior 0.

SELECT *
FROM book_list
LIMIT 5.5;  -- Start line, number of lines
Copy the code

SELECT *
FROM book_list
LIMIT 4 OFFSET 3;  -- Display 4 lines, starting with line 4
Copy the code

Five, sorting,

5.1 Ascending order

SELECT id, `name`, price
FROM book_list
ORDER BY price;  -- In ascending order of price
Copy the code

5.2 Sort by multiple columns

SELECT id, `name`, price, sales
FROM book_list
ORDER BY price, sales;  -- First by price, then by sales, both in ascending order
Copy the code

5.3 In descending order

SELECT id, `name`, price, sales
FROM book_list
ORDER BY price DESC;  -- DESC in descending order, ASC in ascending order
Copy the code

Sorting is case insensitive by default

5.4 Finding the Maximum Value

SELECT id, `name`, price, sales
FROM book_list
ORDER BY price DESC
LIMIT 1;   Only the first line is displayed, so it's time to find the maximum value
Copy the code

Six, filtering,

6.1 Select rows that meet the conditions

SELECT id, `name`, price, sales
FROM book_list
WHERE price=20;  -- Select all items with a price of 20
Copy the code

• Single quotes are used to qualify strings, and if values are compared to columns of string type, you need to qualify quotes. Values used to compare with numeric columns do not need to be quoted.
• At the same timeORDER BYandWHEREShould letORDER BYLocated in theWHEREAfter that, otherwise an error will be generated.

  SELECT id, `name`, price, sales
  FROM book_list
  WHERE `name` REGEXP '[a-z]'
  ORDER BY price;
  Copy the code

6.2 Checking range values

SELECT id, `name`, price, sales
FROM book_list
WHERE price BETWEEN 20 AND 100;  -- Left closed right closed interval
Copy the code

6.3 Null Value Check

SELECT id, `name`, price, sales
FROM book_list
WHERE sales IS NULL;  -- Select the content controlled by Sales
Copy the code

6.4 combinationwhereclause

SELECT id, `name`, price, sales
FROM book_list
WHERE id < = 30 AND price < = 20;  -- Multi-condition combination judgment
Copy the code

SELECT id, `name`, price, sales
FROM book_list
WHERE id = 26 OR id = 30;  -- Or judge, remember to use '='
Copy the code

• SQL in the treatment ofORBefore the operator, precedence is givenANDOperators. Therefore, to be on the safe side, it is best to use if you are dealing with multiple logical operators(a)Expansion.

6.5 INThe operator

SELECT id, `name`, price, sales
FROM book_list
WHERE id IN (20.30);  -- all rows with id 20 or 30, enumeration conditions inside parentheses
Copy the code

7. Wildcards

7.1 Filter By Wildcard

7.1.1 percent%The wildcard

% represents zero, one, or more characters at a given position.

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` LIKE 'java%'; -- '%' tells MySQL to accept any character after Java, no matter how many characters it has.
Copy the code

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` LIKE '%java%'; -- '%' tells MySQL to accept any character containing 'Java', no matter how many characters it has.
Copy the code

7.1.2 the underline_The wildcard

_ Matches a single character, no more or less

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` LIKE 'java_'; -- '%' tells MySQL to accept a string with only one character after 'Java'
Copy the code

7.1.3 Techniques for using Wildcards

• Wildcard searches generally take longer to process than the other searches discussed earlier.
• Don’t overuse wildcards. Other operators should be used if they serve the same purpose.
• When you do need to use wildcards, don’t use them at the beginning of a search pattern unless absolutely necessary. The wildcard is the slowest to be searched at the beginning of the search pattern.
• Pay close attention to the wildcard positions. If misplaced, the desired data may not be returned.

Regular expressions

• Regular expressions: Are special strings (sets of characters) used to match text. Regular expressions are supported by all kinds of programming languages, text editors, operating systems, and so on.

8.1 Basic Character Matching

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` REGEXP 'java';   Matches the row containing the string 'Java' in this column
Copy the code

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` REGEXP '.ava';   --. Is a special character in the regular expression language that matches any character
Copy the code

Regular expressions are case-insensitive in MySQL. To be case-sensitive, you need to add regular expressions before matched stringsBINARYThe keyword

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` REGEXP BINARY 'java';
Copy the code

8.2 toORmatching

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` REGEXP BINARY 'java|JAVA';  - ` | ` character representation OR operations, said matching contains ` Java ` OR ` Java ` string
Copy the code

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` REGEXP '[tj]';  Matches lines in the string that contain t or j
Copy the code

SELECT id, `name`, price, sales
FROM book_list
WHERE `name` REGEXP '[a-z]';  Matches lines containing any letters in the string
Copy the code

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\ \.  -- Special characters, which must be represented by the escape character '\\'
Copy the code

Matching character class

class	instructions
[:alpha:]	Any character, same as[a-zA-Z]
[:lower:]	Any lowercase letter, same as[a-z]
[:upper:]	Any capital letter, same as[A-Z]
[:alnum:]	Any letters and numbers, same as[a-zA-Z0-9]
[:digit:]	Any number, same as[0-9]
[:xdigit:]	Any hexadecimal number, the same as[a-fA-F0-9]
[:blank:]	Space and tabulation, the same[\\t]
[:space:]	Any whitespace character, including Spaces, is the same as[\\f\\n\\r\\t\\v]
[:cntrl:]	ASCII control characters (ASCII 0 to 31 and 127)
[:print:]	Any printable character
[:graph:]	with[:print:]Same, but excluding Spaces
[:punct:]	neither[:alnum:], nor[:cntrl:]Any character in

8.3 Matching Multiple Instances

Repeated metacharacter

metacharacters	instructions
*	Zero or more matches
+	One or more matches, equal to{1,}
?	Zero or one matches, equal to{0, 1}
{n}	Specifies the number of matches
{n,}	Not less than a specified number of matches
{n, m}	Range of matches (m does not exceed 255)

SELECT prob_name
FROM products
-- \\(match (
-- [0-9] Matches numbers
-? Matches one or zero arbitrary characters
-- \\) Match)
WHERE prob_name REGEXP '\\([0-9] sticks? \ \] '
Copy the code

SELECT prob_name
FROM products
-- [: Digit :] Matches any number
-- {4} requires exactly four occurrences of the preceding character
WHERE prob_name REGEXP '[[:digit:]]{4}'
ORDER BY prob_name;
Copy the code

8.4 locator

metacharacters	instructions
^	The beginning of a text
$	End of text
[[: < :]]	The beginning of the term
[[: > :]]	The end of the term

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^ [0-9 \ \]'  //Matches a string that begins with a number or decimal pointCopy the code

^in[]Where means to negate the set, and take the inverse of the set, as[123] ^; Otherwise used at the beginning of the string

9. Calculation fields

9.1 Calculating Fields

• Much of the transformation and formatting that can be done within SQL statements can be done directly within the client application. But in general, it is much faster to do these operations on the database server than on the client, because the DBMS is designed to do these processes quickly and efficiently.

9.2 Splicing Fields

SELECT CONCAT('a'.'b'.'c');  -- result is' ABC '
Copy the code

Most DBMS using + or | | to implement stitching, MySQL is usedCONCAT()Function to implement.

9.3 Clearing Unnecessary Spaces on the Left and Right Sides

SELECT RTRIM('FADFA ');  -- Clear the space on the right
SELECT LTRIM(' FADFA');  -- Clear the left margin
SELECT TRIM('FADFA');  -- Clear all Spaces
Copy the code

9.4 Using An Alias

To better refer to a column, you can alias a column

SELECT CONCAT(RTRIM(vend_name), '(', RTRIM(vend_country), ') ') AS vend_title  -- AS is followed by the alias
FROM vendors;
Copy the code

Ten, functions,

10.1 the function

• Functions are not as portable as SQL, and almost every major DBMS implementation supports functions that are not supported by other implementations, sometimes with great differences. Using some functions that are not very portable makes it difficult to specify a database management system dynamically.

10.2 Text processing functions

function	instructions
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
Lower()	Converts the string to lowercase
Upper()	Converts the string to uppercase
LTrim()	Removes the character on the left side of the string
RTrim()	Removes the character to the right of the string
Locate()	Find a string of strings
Soundex()	Returns the SOUNDEX value of the string
SubString()	Returns the character of the string

-- Counting from the left, cut the first n characters of the string
SELECT LEFT('foobarbar'.5) -- -> 'fooba'
-- Counting from the right, n after the string is cut
SELECT RIGHT('foobarbar'.4) -- -> 'rbar'
Copy the code

Locates the first position of the substring in the string, starting at 1. If not, return 0
SELECT LOCATE('bar'.'foobarbar')  -- - > 4
SELECT LOCATE('xbar'.'foobar') -- - > 0
SELECT LOCATE('bar'.'foobarbar'.5) -- - > 7
Copy the code

-- Substring, truncated from NTH
SELECT SUBSTRING('Quadratically'.5)  -- -> 'ratically'
-- Substring, truncated from NTH
SELECT SUBSTRING('foobarbar' FROM 4) -- -> 'barbar'
-- Substrings, truncated from n to m
SELECT SUBSTRING('Quadratically'.5.6) -- -> 'ratica'
-- Substring, truncated from the NTH to the end of the string
SELECT SUBSTRING('Sakila'.- 3) -- -> 'ila'
-- Substring, starting from the NTH to the last and truncated by m
SELECT SUBSTRING('Sakila'.- 5.3) -- -> 'aki'
-- Substring, starting from the NTH to the last and truncated by m
SELECT SUBSTRING('Sakila' FROM 4 - FOR 2) ---> 'ki'
Copy the code

SELECT SOUNDEX('Hello')  -- -> 'H400'
SELECT SOUNDEX('Quadratically')  ---> 'Q36324'
Find matches all strings with similar sounds
SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');
Copy the code

10.3 Date and time handlers

function	instructions
CurDate()	Return current date
CurTime()	Return current time
Now()	Returns the current date and time
Date()	Returns the date portion of the date time
Time()	Returns the time portion of the date time
Year()	Returns the year portion of the date time
Month()	Returns the month portion of the date time
Day()	Returns the number of days of the date time
Hour()	Returns the hour portion of the date time
Minute()	Returns the minute portion of the date time
Second()	Returns the second portion of the date time
AddDate()	Add a date (day, week, etc.)
AddTime()	Add a time (hour, grade)
DateDiff()	Returns the difference between two dates
Date_Add()	Highly flexible date manipulation function
Date_Format()	Returns a formatted date or time string
DayOfWeek()	For a date, return the day of the week

SELECT NOW()  -- - > '2007-12-15 23:50:26'
SELECT NOW() + 0  -- - > 20071215235026.000000
SELECT ADDDATE('2008-01-02'.31) -- - > '2008-02-02'
SELECT ADDTIME('the 2007-12-31 23:59:59. 999999'.': 1. 000002') -- - > '2008-01-02 01:01:01. 000001'
SELECT ADDTIME('01:00:00. 999999'.'02:00:00. 999998') -- - > '03:00:01. 999997'
SELECT DATEDIFF('the 2007-12-31 23:59:59'.'2007-12-30') -- - > 1
SELECT DATEDIFF('the 2010-11-30 23:59:59'.'2010-12-31') > - 31
SELECT DAYOFWEEK('2007-02-03') -- - > 7
Copy the code

• Whether inserting or updating table values or usingWHEREClause to filter, the date must beyyyy-mm-ddFormat.
• However, usingWHERE order_date = '2005-09-01'Is not reliable. becauseorder_timeIs of the data typedatatime, this type stores date and time values. The default time value is00:00:00. Therefore, throughWHEREWhen screening the date, it is easy to filter out because the time does not match. It is safer to:

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';
Copy the code

• If you want a date, useDate()This is a good habit.

-- Filter rows for a month
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
Copy the code

10.4 Numerical Processing functions

function	instructions
Abs()	Return absolute value
Sin()	Returns the sine of an Angle (in radians)
Cos()	Returns the cosine of an Angle
Tan()	Returns the tangent of an Angle
Exp()	Returns the index value of an Angle
Mod()	Returns the remainder of the division operation
Sqrt()	Returns the square root of a number
Pi()	Returns PI
Rand()	Returns a random number

10.5 Aggregation Function

function	instructions
COUNT()	Returns the number of rows in a column
MAX()	Returns the maximum value of a column
MIN()	Returns the minimum value of a column
AVG()	Returns the average value of a column
SUM()	Returns the sum of the values of a column

• When evaluating, ignore the value ofnullThe line.
• Such as the useCOUNT()Function, if usedCOUNT(*), will count all rows, but when usedCOUNT(COLUMNS_NAME), if the column has some behaviornull, is ignored directly.
• useAVG()Function, if some behaviornull, will not be included in the scope of calculation, neither accounting for the number, nor has an impact on the value, will be directly ignored.
• DISTINCTOnly computations that contain different values.

SELECT COUNT(DISTINCT price)
FROM book_list;

SELECT AVG(DISTINCT price)
FROM book_list;
Copy the code

• These functions are designed to be efficient, and they generally return results much faster than client applications.

11. Data grouping

11.1 Group Counting

• Calculate how many of them cost the same.

SELECT price, COUNT(price)
FROM book_list
GROUP BY price;
Copy the code

The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause. That is, first filter, then group, finally sort.

SELECT price, COUNT(price)
FROM book_list
GROUP BY price
ORDER BY price;
Copy the code

11.2 Filtering And Grouping

SELECT price, COUNT(price)
FROM book_list
GROUP BY price
HAVING COUNT(price) > 8;
Copy the code

• HAVINGSupport allWHEREOperators. What I have learnedWHEREAll of these techniques and options are availableHAVING. They have the same sentence pattern except for the key words.
• Another way to think about it,WHEREFilter data before grouping it,HAVINGFilter data after grouping. This is an important distinction,WHEREExcluded rows are not included in the grouping. This may alter the calculated value, thereby affectingHAVINGThe grouping filtered based on these values in the clause.
• Generally in useGROUP BYClause should also be givenORDER BYClause, which is the only way to ensure that the data is sorted correctly. Don’t just rely on itGROUP BYSort data.
• So far,SELECTThe order of the neutrons in a statement.

clause	instructions	Is it mandatory to use
SELECT	The column or expression to return	is
FROM	A table from which data is retrieved	Used only when selecting data from a table
WHERE	Row-level filter	no
GROUP BY	Group show	Use only when calculating aggregation by group
HAVING	Group level filter	no
ORDER BY	Output sort order	no
LIMIT	Number of rows to retrieve	no

Subquery

12.1 the subquery

• That is, queries nested within other queries.

12.2 Application Scenarios

• Two tables, one for user information and the other for order information. The connection between the two tables is made by user ID. When you need to query the user information corresponding to the user ID in the specified order, you need to use the sub-query.

Query all users' TN2 'names
SELECT user_name
FROM user_info
WHERE id in (
        SELECT id
        FROM orders
        WHERE prod_id = 'TN2'
);
Copy the code

• Subqueries are always processed from the inside out, firstinThe query inside the parentheses, and then the outside query.
• inWHEREClause can be used to write powerful and flexible SQL statements. However, in practice, too many sub-queries cannot be nested due to performance limitations
• Related subqueries: Subqueries that involve external queries. Fully qualified column names are required

WHERE orders.cust_id = customers.cust_id;
Copy the code

Xiii. Connection

13.1 connecting

• One of the most powerful features of SQL is the ability to join (join) table.
• Having the same data more than once is never a good thing, and this factor is fundamental to relational database design. Relational tables are designed to ensure that information is decomposed into multiple tables, one table for each type of data. The tables are related to each other by some common values, that is, relationships in relational design.
• Each row in a table has a unique identifier, called a primary key. Another table joins the previous table by this identifier, which is called a foreign key in this table.

13.2 Foreign Keys

• A foreign key is a column in a table that contains another primary key value and defines the relationship between two tables.

CREATE TABLE departments(
        dept_id INT PRIMARY KEY AUTO_INCREMENT,
        dept_name VARCHAR(20) NOT NULL
);

CREATE TABLE employees (
        emp_id INT PRIMARY KEY AUTO_INCREMENT,
        emp_name VARCHAR(20) NOT NULL,
        gender VARCHAR(10),
        email VARCHAR(30),
        dept_id INT.CONSTRAINT fk_emp_dept  - the foreign key
        FOREIGN KEY(dept_id) REFERENCES departments(dept_id)
);
Copy the code

13.3 Creating a Connection

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

• Of this statementFROMThe clause lists two tables, which are this oneSELECTThe names of the two tables to which the statement joins. Two tablesWHEREClause normal join.
• WHEREAs a filter condition after join, if not usedWHEREEach row of the first table is combined with each row of the second table. (Cartesian product)
• Cartesian product: The result returned by a table relationship with no join condition is the Cartesian product, where 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.

13.4 Internal connection

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

• throughINNER JOIN... ON...Method coupling.
• ANSI SQL specification preferredINNER JOINSyntax.

13.5 Equivalent Connection

• The effect of the above two methods is exactly the same. In fact, the part of the two tables with the same value is taken, and the part without the same value is discarded directly. (That is, intersection)

13.6 Joining Multiple tables

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
        AND orderitems.prod_id = products.prod_id
        AND order_name = 20005;
Copy the code

13.7 since the connection

• That is, self connecting with self. Usually used when a row of data has an attribute and you need to look up all the data in the table with the same attribute.
• This can be done using subqueries or self-joins.

-- Subquery method
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                    FROM products
                    WHERE prod_id = 'DTNTR'
);

-- the way of self-connection
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 = 'DTNDR';
Copy the code

• Use self-joins instead of subqueries: Self-joins are often used as external statements instead of subqueries when retrieving data from the same table. Although the end result is the same, sometimes processing joins is much faster than processing subqueries. Two methods should be tried to determine which performs better.

13.8 Natural connection

• There should be at least one list now in more than one table, standard joins return all data, even if the same column appears multiple times, natural joins exclude multiple occurrences so that each column returns only once.
• Implementation: Typically by using wildcards on tables (SELECT *), using an explicit subset of the columns of other tables.

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';
Copy the code

13.9 External Connections

• Many joins associate rows in one table with rows in another table, but sometimes you need to include rows that are not associated. (For example, some customers do not have orders, but exist in the customer list and need to be displayed.) Joins contain rows that are not associated with each other in related tables. This type of join is called an external join.

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
Copy the code

• In the use ofOUTER JOINMust be usedRIGHTorLEFTKeyword specifies a table that contains all its rows.RIGHTPoint out isOUTER JOINThe table on the right,LEFTI’m pointing to the table on the left. The two types actually have the same effect, just in a different order.
• External join is takingRIGHT/LEFTMerges all of the intersections of the What doesn’t exist on the other side, just use itnullTo represent.

13.9 Use joins with aggregation functions

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_name) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
Copy the code

Fourthly, combination query

• Combined query: after multiple queries are executed, theUNION) to merge multiple query results.

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price < = 5
UNION  -- Combination key
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001.1002);
Copy the code

14.1 UNIONRules:

• Must consist of two or moreSELECTStatement composition, with keywords between statementsUNIONSpace.
• UNIONEach query in must contain the same column, expression, or aggregate function. (The columns don’t need to appear in the same order, though.)
• Column data must be compatible: the type does not have to be identical, but it must be a type that the DBMS can implicitly convert.
• inUNIONIn, duplicate lines are automatically cancelled. If you don’t want to remove duplicate rows, useUNION ALL
• Sort composite query results: add on lastORDER BYStatement.

15. Full-text search

15.1 Full-text Search

• Not all search engines support full-text search. The two most commonly used search engines: MyISAM supports full-text search and InnoDB does not.
• In order to do a full-text search, the indexed columns must be indexed and reindexed continuously as the data changes. After table columns are properly designed, MySQL automatically does all indexing and re-indexing.

15.2 Enable full-text search when creating a table

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),   -- Specifies the primary key of the table
        FULLTEXT(note_id) -- Full-text search index
) ENGINE=MyISAM;   -- Specify the search engine
Copy the code

• After definition, MySQL automatically maintains the index. Indexes update automatically as rows are added, changed, and deleted.
• Do not turn on full-text search before importing data. Full-text search should be started after navigating the data. Because the index needs to be updated each time the data is navigated, the time to update each data index individually is longer than the time to update all data indexes.

15.2 Performing Full-text Search

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');   -- Search for all columns containing 'rabbit' in the note_text column
Copy the code

• The search will be case insensitive.
• A full-text search returns data sorted by a good degree of text matching. If both lines contain ‘rabbit’, the third word ‘rabbit’ will have precedence over the twentieth.

15.3 Query Expansion

• Query extension is used to try to broaden the range of full-text search results returned.
• First, do a basic full-text search to find all the rows that match the search criteria.
• Second, MySQL checks these matching lines and selects any useful words.
• Next, MySQL does a full-text search again, this time using not only the original criteria, but also all the useful words.

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);	
Copy the code

15.4 Boolean text search

• In Boolean fashion, you can provide details about:
  • Words to match
  • Words to exclude
  • Permutation hints (specifying that some words are more important than others and that more important words rank higher)
  • Expression grouping
  • Something else
• Even if there is noFULLTEXTIndexes can also be used, but this is a very slow operation.

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);  -- Use Boolean text search
Copy the code

SELECT note_text
FROM productnotes
WHERE Match(note_text) Aganist('heavy -rope*' IN BOOLEAN MODE); -- Matches 'heavy' but does not match sentences containing words beginning with 'rope'
Copy the code

Boolean operator	instructions
+	Contain, the word must exist
–	Exclusion, the word must not appear
>	Contains, and increases the rank value
<	Contains, and reduces the level value
(a)	Grouping words into subexexpressions (allowing these subexexpressions to be included, excluded, arranged, etc., as a group)
~	Cancels the sorting value of a word
*	A wildcard character at the end of a word
“”	Define a phrase (unlike a list of single words, which matches the entire phrase to include or exclude it)

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE); -- Lines containing the words 'rabbit' and 'bait' (both must be present)
Copy the code

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE); -- Lines containing the word 'rabbit' or 'bait' (at least one)
Copy the code

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE) -- contains search matching phrase 'rabbit bait' instead of matching two words 'rabbit' and 'bait'
Copy the code

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE) Include Rabbit or Carrot and increase the level of Rabbit and decrease the level of Carrot
Copy the code

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE) - Must contain two words and reduce the grade of 'combination'
Copy the code

Insert data

16.1 Inserting Data

INSERT INTO customers
VALUES(NULL.'Pep'.'100'.'Los Angeles'.'CA'.'90046'.'USA'.NULL.NULL);
Copy the code

• If you don’t want to give a value, but you can’t ignore a column, you can give itNULLValue. While this syntax is simple, it is not safe and should be avoided as much as possible. The SQL above relies heavily on the order in which the columns in the table are defined, and on information that their order is easily available, and there is no guarantee that the columns will remain in exactly the same order after the next table structure change.
• The safer way is as follows:

INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_eamil)
VALUES('Pep'.'100'.'Los Angeles'.'CA'.'90046'.'USA'.NULL.NULL);
Copy the code

• When a row is inserted, the first value in MySQL corresponds to the first specified column name. The second value corresponds to the second column name, so.
• Do not use lists that are not explicitly listedINSERTStatement, using a list of columns allows SQL code to continue to function even if the table structure changes.
• If you do not give a value for a column in the table that does not allow NULL values and does not have a default value, MySQL will generate an error message and the corresponding row will not be inserted successfully.
• If data retrieval is of Paramount importance, it can be done through theINSERTandINTOAdd keywords betweenLOW_PRIORITY, indicating that MySQL is downINSERTThe priority of the statement (also appliesUPDATEandDELETE) :

INSERT LOW_PRIORITY INTO
Copy the code

16.2 Inserting Multiple Rows

• A singleINSERTHandle multiple inserts than use multiple insertsINSERTStatements quickly.

INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_eamil)
VALUES('Pep'.'100'.'Los Angeles'.'CA'.'90046'.'USA'.NULL.NULL),
('M'.The '42'.'New York'.'NY'.'11213'.'USA');
Copy the code

16.3 Inserting the Query Result

• theSELECTThe resulting results are inserted into the 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;
Copy the code

INSERT SELECT inserts by one-to-one mapping of column positions. Column names are not necessarily the same.

Update data

17.1 UPDATEPart of the

• The table to update
• Column names and their new values
• Determine the filter criteria for the row to be updated

UPDATE customers
SET cust_email = '[email protected]'  -- Set a new value
WHERE cust_id = 1005;  -- Screening conditions
Copy the code

UPDATE customers
SET cust_name = 'The Fudds'.-- Set multiple columns
        cust_email = '[email protected]'
WHERE cust_id = 1005;  -- Screening conditions
Copy the code

17.2 IGNOREThe keyword

• If you useUPDATEStatement update more than one row error, also continue to update, can be usedIGNOREThe keyword.

UPDATE IGNORE customers...
Copy the code

17.3 Deleting a Value in a Column

• It can be set toNULL

UPDATE customers
SET cust_email = NULL  -- Removes the value of a column
WHERE cust_id = 10005;
Copy the code

Delete data

18.1 Deleting a Row

DELETE FROM customers
WHERE cust_id = 10006;
Copy the code

18.2 Delete all rows in a table

DELETE FROM customers;
Copy the code

• A faster method that deletes the entire table and creates a new one without rollback:

TRUNCATE TABLE customers;
Copy the code

18.3 the use ofUPDATEandDELETEHabits followed by:

• Never leave out every row unless you really intend to update or delete itWHERE
• Ensure that each table has a primary key,WHEREClauses are filtered using primary keys
• inUPDATEandDELETEBefore, useSELECTClause to ensure that the filtered data is correct.

Create tables and manipulate tables

19.1 create a table

• Columns are separated by commas, and the definition of each column begins with the column name, followed by the data type of the column.

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,
        cust_email		char(255)	NULL.PRIMARY KEY(cust_id)
) ENGINE=InnoDB;
Copy the code

• Column definitions are indented appropriately for reading and editing.
• When creating a new table, the specified table name must not exist or an error will occur. To prevent errors, you can delete them before creating them.

DROP TABLE IF EXISTS customers; 
Copy the code

• You can create a table when it does not exist

CREATE TABLE customers IF NOT EXISTS
(
        ...
);
Copy the code

19.2 the use ofNULLvalue

• Each table column orNULLA column, or a columnNOT NULLColumns, whose state is specified by the table’s definition at creation time. useNOT NULLDoes not accept a column that has no value for that column; in other words, the column must have a value when a row is inserted or updated.
• NULLIs the default if not specifiedNOT NULLIs considered to be specifiedNULL.

19.3 Using primary Keys

• The primary key value must be unique. That is, each row in the table must have a unique primary key value. If the primary key uses a single column, its value must be unique. If multiple columns are used, the combined values of the columns must be unique.

PRIMARY KEY (vend_id)
PRIMARY KEY (order_num, order_item)
Copy the code

19.4 the use ofAUTO_INCREMENT

• AUTO_INCREMENTTell MySQL that this column is automatically incremented each time a row is added. Only one table is allowed per tableAUTO_INCREMENTColumn, and it must be indexed.
• If a column is specified asAUTO_INCREMENT, it can be simplyINSERTSpecify a value as long as it is unique. Subsequent increments will start using the manually inserted value.
• The nextAUTO_INCREMENTThe value of the

SELECT LAST_INSERT_ID();
Copy the code

19.5 Specifying a Default Value

CREATE TABLE orderitems
(
        order_num		int				NOT NULL,
        order_item		int				NOT NULL,
        prod_id			char(10)		NOT NULL,
        quantity		int				NOT NULL DEFAULT 1.The default value is 1
        item_price		decimal(8.2)	NOT NULL.PRIMARY KEY	(order_num, order_item)
) ENGINE=InnoDB;
Copy the code

• Like most DBMSS, MySQL does not allow functions as default values; it only supports constants.
• Many database developers use defaults instead ofNULLThis is especially true for columns, especially those used for computation or grouping data.

19.6 Engine Types

• InnoDB: is a reliable transaction engine that does not support full-text search.
• MEMORY: Functions as MyISAM, but is fast (especially for temporary tables) because the data is stored in MEMORY (not disk)
• MyISAM is a very high performance engine that supports full-text search but does not support transaction processing.

19.7 update the table

• Ideally, after data is stored in a table, the table should not be updated. During the table design process, it takes a lot of time to consider, so that the table does not change significantly later.
• Add columns

ALTER TABLE vendors
ADD vend_phone CHAR(20);  Add columns -
Copy the code

• Delete the column

ALTER TABLE vendors
DROP COLUMN vend_phone;  - delete columns
Copy the code

• To define a foreign key

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

Complex table structures typically require a manual deletion process, which involves the following steps:

• Create a new table with the new column layout
• useINSERT SELECTStatement copies data from the old table to the new table.
• Verify the new table that contains the required data
• Rename/drop old tables
• Rename the new table with the old table name
• Re-create triggers, stored procedures, indexes, and foreign keys as needed.

19.8 delete table

DROP TABLE customers2;
Copy the code

19.9 Renaming a Table

RENAME TABLE customers2 to customers;
RENAME TABLE customers2 to customers,
                        customers3 to customers2,
                        customers4 to customers3;
Copy the code

20. Use views

20.1 the view

• Are virtual tables, and unlike tables that contain data, views contain only queries that dynamically retrieve data when used.

20.2 View Common Applications

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

20.3 The view itself does 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.

20.4 Rules and Restrictions of views

• Views can be nested, that is, one view can be constructed from queries that retrieve data from other views
• ORDER BYCan be used in a view, but if the data is retrieved from that viewSELECTThe statement also containsORDER BY, then in this viewORDER BYWill be overwritten
• Views cannot be indexed, have triggers or default values associated with them
• Views can be used with tables. For example, write a join table and viewSELECTstatements

20.5 Using Views

• View withCREATE VIEWStatement to create
• useSHOW CREATE VIEW viewnameTo view the statement that creates the view
• DROP VIEW viewnameDelete the view
• Can be used when updating the viewDROPThen useCREATEOr you can use it directlyCREATE OR REPLACE VIEW

CREATE VIEW productcustomers AS  Create view
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id  Join three tables
AND orderitems.order_num = orders.order_num;
Copy the code

• useview

SELECT cust_name, cust_contact   - column
FROM productcustomers   -- VIEW
WHERE prod_id = 'TNT2';   -- Screening conditions
Copy the code

With views, you can write basic SQL once and then use it as many times as necessary.

20.6 Format the retrieved data

CREATE VIEW vendorlocations AS  Create view
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ') ')   Format the retrieved data
FROM vendors
ORDER BY vend_name;
Copy the code

SELECT *   Call the view
FROM vendorlocations
Copy the code

20.7 Filter unwanted data

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

20.8 Use computed fields

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

20.9 Updating views

• Views are updatable and can be used against themINSERT,UPDATE,DELETEOperation. If you add or delete rows to a view, you are actually adding or deleting rows from its base table.
• The view cannot be updated if it contains the following operations: grouping, join, subquery, merge, aggregate function (Min(),Count(),Sum()Etc.)
• DISTINCE
• Export column

In general, views should be used for retrieval, not updates

21. Stored procedures

21.1 Stored Procedures

• A collection of one or more MySQL statements saved for later use. You can think of them as batch files, although their role is not limited to batch processing

21.2 Reasons for using stored procedures

• Simplify complex operations by encapsulating processing in easy-to-use units
• All developers use the same stored procedure to prevent errors
• Simplify management of change. If the table name, column name, or business logic changes, you only need to change the code of the stored procedure, and the people using it don’t even need to know about the changes (encapsulation)
• Improve performance. Using stored procedures is faster than using individual SQL statements

21.3 Creating a Stored Procedure

CREATE PROCEDURE productpricing()   Define the stored procedure, if it accepts arguments, in parentheses
BEGIN    -- Restrict the process body
        SELECT Avg(prod_price) AS priceaverage
        FROM products;
END;
Copy the code

• However, this operation causes an error because;As statement delimiters, also inside;, will cause the process body to end ahead of time misunderstanding. Therefore, you need to temporarily modify the statement delimiter.

DELIMITER //    -- Temporarily change the delimiter to '//'

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

DELIMITER ;   -- Change the delimiter back
Copy the code

21.4 Executing a Stored Procedure

CALL productpricing();
Copy the code

21.5 Deleting a Stored Procedure

DROP PROCEDURE productpricing;
DROP PROCEDURE IF EXISTS productpricing;   -- Delete it if it exists
Copy the code

21.6 Using Parameters

CREATE PROCEDURE productpricing(
        OUT pl DECIMAL(8.2),   -- Output variable
        OUT ph DECIMAL(8.2),
        OUT pa DECIMAL(8.2))BEGIN
        SELECT Min(prod_price)
        INTO pl    Save the query results in pl
        FROM products;
        SELECT Max(prod_price)
        INTO ph
        FROM products;
        SELECT Avg(prod_price)
        INTO pa
        FROM products;
END;
Copy the code

• MySQL supportIN(passed to stored procedures),OUT(outgoing from the stored procedure) andINOUT(incoming and outgoing to stored procedures) type.

CALL productpricing(@pricelow.All MySQL variables must start with @
                                        @pricehigh.@priceage
);
Copy the code

SELECT @pricelow;   -- Displays the search results
Copy the code

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;
Copy the code

CALL ordertotal(20005. @total);   Call the stored procedure
SELECT @total;  -- Check the result
Copy the code

21.7 Establishing intelligent Stored Procedures

CREATE PROCEDURE ordertotal(
        IN 		onumber		INT.IN 		taxable 	BOOLEAN.OUT		ototal		DECIMAL(8.2)
) COMMENT 'Obtain order total, optionally adding tax'   -- 'SHOW PROCEDURE STATUS' will display this sentence
BEGIN
        DECLARE total DECIMAL(8.2);   Declare a variable for the total amount
        DECLARE taxrate	INT DEFAULT 6;   -- State the percentage of tax rate

        SELECT Sum(item_price*quantity)
        FROM orderitems
        WHERE order_num = onumber
        INTO total;    Store the calculated total in the total variable

        IF taxable THEN   -- Taxable or not
                SELECT total+(total/100*taxrate) INTO total;
        END IF;

        SELECT total INTO ototal;   -- Save the result on ototal
END;
Copy the code

Call:

CALL ordertotal(20005.0.@total);   Call the stored procedure
SELECT @total;  -- Display results
Copy the code

21.8 Checking Stored Procedures

SHOW CREATE PROCEDURE ordertotal;  -- Displays the CREATE statement used to CREATE a stored procedure
SHOW PROCEDURE STATUS; -- displays information about all existing stored procedures, including details about when and by whom they were created
SHOW PROCEDURE STATUS LIKE 'search_user';  Filter by wildcard
Copy the code

22. Use a cursor

22.1 the cursor

• Cursors are primarily used in interactive applications, where users need to scroll through data on the screen and browse or make changes to the data.

22.2 Using cursors

CREATE PROCEDURE processorders()
BEGIN
        DECLARE ordernumbers CURSOR  -- Declare cursors
        FOR
        SELECT order_num FROM orders;

        OPEN ordernumbers;   -- Open the cursor
        CLOSE ordernumbers;   -- Close the cursor
END;
Copy the code

22.4 Using cursor data

• useFETCHTo retrieve the data for the current row (starting with the first row by default)

DECLARE o INT;   Declare a variable
OPEN ordernumbers;   -- Open the cursor
FETCH ordernumbers INTO o;  Get and save the data for the current row in variable O
CLOSE ordernumbers;   -- Close the cursor
Copy the code

• Loop to retrieve

CREATE PROCEDURE processorders()
BEGIN
        DECLARE done BOOLEAN DEFAULT 0;   Declare local variables and complete flag bits
        DECLARE o INT;   -- Local variables
        DECLARE ordernumbers CURSOR  -- Declare cursors
        FOR
        SELECT order_num FROM orders;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;  Set done to 1 if SQLSTATE = '02000'
        OPEN ordernumbers;   -- Open the cursor

        -- Start loop
        REPEAT
                FETCH ordernumbers INTO o; -- Save data in local variables
        UNTIL done END REPEAT;   -- Closed loop condition (done=1)
        CLOSE ordernumbers;   -- Close the cursor
END;
Copy the code

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1; This statement defines a CONTINUE HANDLER, which is executed when a condition occurs. Here, it says SET done=1 when SQLSTATE ‘02000’ appears

23. Use triggers

• If you want a statement (or statements) to be executed automatically when an event occurs, you need to use triggers.
• Triggers are fired when the specified table changes, for the operationsINSERT,UPDATE,DELETETriggers can be set before or after an event.
• To create a trigger, you need to give four pieces of information
  • Unique trigger name
  • Table associated with the trigger
  • The activity that the trigger should respond to (INSERT,UPDATE,DELETE)
  • When the trigger executes (before or after processing)

23.1 Creating a Trigger

CREATE TRIGGER newproduct AFTER INSERT ON products  -- Trigger name 'newProduct', execute after trigger, associated table 'products'
FOR EACH ROW SELECT 'Product added';  Output 'Product Added 'when each row is changed
Copy the code

23.2 Triggers only support tables, not views or temporary tables

23.3 Deleting triggers

DROP TRIGGER newproduct;
Copy the code

23.4 INSERTThe trigger

• inINSERTWithin the trigger code, a name namedNEWTo access the inserted row
• inBEFORE INSERTIn the flip-flop,NEWValues in can also be updated (allows you to update inserted values, implementing pre-formatted inserted data)
• forAUTO_INCREMENTThe column,NEWinINSERTContains 0 before execution, inINSERTContains new auto-generated values after execution.

  CREATE TRIGGER neworder AFTER INSERT ON orders
  FOR EACH ROW SELECT NEW.order_num;    -- Query the automatically generated value order_num after insert
  Copy the code

23.5 DELETEThe trigger

• inDELETEWithin the trigger code, you can reference a trigger namedOLDTo access the deleted rows.
• OLDValues in are read-only and cannot be updated.

-- Use OLD to save rows to be deleted in an archive table
CREATE TRIGGER deleteorder BEFORE DELETE ON orders   -- Before deleting
FOR EACH ROW
BEGIN
        INSERT INTO achieve_orders(order_num, order_datae, cust_id)  -- Save the old values in a table called 'achieve_Orders'
        VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
Copy the code

The advantage of using BEFORE DELETE instead of AFTER DELETE is that the order cannot be archived for any reason.

23.6 UPDATEThe trigger

• inUPDATETrigger code, can referenceNEWAccess the newly updated value and can also be referencedOLDAccess the previous value.
• inBEFORE UPDATEIn the flip-flop,NEWValues in May also be updated (allow updates to be usedUPDATEThe value of the)
• OLDAll values are read-only and cannot be updated

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);   Before updating, change all inserts to uppercase
Copy the code

Xxiv. Management affairs

24.1 Transaction processing

• Used to maintain database integrity, it ensures that batch MySQL operations are either performed completely or not performed at all.

24.2 Engines that support transaction processing

• InnoDB

24.3 Transaction

• A set of SQL statements

24.4 Rolling Back the rollback

• The process of revoking a specified SQL statement

24.5 Committing (Commit)

• Write the result of an unstored SQL statement to a database table

24.6 SavePoint

• Refers to a temporary place-holder set in a transaction that can be published back

24.7 the use ofROLLBACK

SELECT * FROM ordertotals;
START TRANSACTION;   -- Start transaction
DELETE FROM ordertotals;   - to empty table
SELECT * FROM ordertotals;   -- The query table is empty
ROLLBACK;   - the rollback
SELECT * FROM ordertotals;    The table is not empty
Copy the code

• ROLLBACKUndo can be implemented, rolling back to the state before the transaction was started.
• Transactions are used for managementINSERT,DELETE,UPDATE(add, delete, change) operation. Can’t backCREATE,DROPOperation.

24.8 the use ofCOMMIT

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;   -- Commit the transaction. The implementation commits a transaction only if there are no errors in the transaction
Copy the code

24.9 Use reservation points

In order to partially roll back. By reserving points, you can fall back to a placeholder.

SAVEPOINT delete1;   - keep points
Copy the code

ROLLBACK TO delete1;  Rollback to the specified reservation point
Copy the code

• The more retention points the better, the more retention points, the more flexible
• Retention point at transaction completion (execute oneROLLBACKorCOMMIT) will be released automatically.

24.10 Change the default commit behavior

• The default MySQL behavior is to commit all changes automatically. To indicate that MySQL does not commit changes automatically, use the following statement:

SET autocommit=0;
Copy the code

-- Start transaction
SET autocommit=0; -- Turn off automatic submission
START TRANSACTION; Can be omitted,

USE girls;
- the transaction
UPDATE boys
SET userCP=2000
WHERE id=7;

UPDATE boys
SET boyName=Aaron Kwok
WHERE id=6;

COMMIT; - submit
-- rollback; -- executes the transaction
Copy the code

Globalization and Localization

25.1 character set

• Is a collection of letters and symbols

25.2 coding

• Is an internal representation of a character set member

25.3 check

• An instruction specifying how characters are compared

25.4 Displays all available character sets and the description and default collation for each character set

SHOW CHARACTER SET;
Copy the code

25.5 View the complete list of supported proofreading

SHOW COLLATION;
Copy the code

25.6 Specifying character Set and collation

CREATE TABLE mytable
(
        column1		INT,
        column2		VARCHAR(10))DEFAULT CHARACTER SET hebrew,  Set character set
COLLATE hebrew_general_ci;   -- Setting check
Copy the code

26. Safety management

26.1 Security Basics

• Users should have adequate access to the data they need, neither more nor less. Example:
  • Most users only need to read and write tables, but a few need to create and drop tables
  • Some users need to read tables, but may not need to update tables
  • Allow some users to add data, but not delete data
  • Some users (administrators) need to handle user account permissions, but most do not
  • Give users access to the data through stored procedures, but do not allow them to access the data directly
  • Restrict access to certain functions based on where the user is logged in

26.2 Managing Users

USE mysql;
SELECT user FROM user;  Get a list of all user accounts
Copy the code

• User accounts and information are stored in a mysql database called mysql

26.3 Creating a User Account

CREATE USER ben IDENTIFIED BY 'ben';  Create a new user named Ben with password Ben
Copy the code

IDENTIFIED BY will encrypt the specified password

26.4 Renaming an Account

RENAME USER ben TO ben1;
Copy the code

26.5 Deleting a User Account

DROP USER ben1;
Copy the code

26.6 Setting Access Rights

After the user account is created, access must be assigned. The newly created user account has no access permission. They can log in to MySQL, but they can’t see data or perform any database operations. View the permission of a user account

SHOW GRANTS FOR ben;
Copy the code

Set permissions, giving at least the following information: — Permissions to grant — database or table to grant access — username

GRANT SELECT ON crashcourse.* TO ben;  Grant crashCourse SELECT to Ben. That is, the Ben user has the read-only rights for all tables in the CrashCourse database
REVOKE SELECT ON crashcourse.* FROM ben;  Ben will be denied read-only rights to the CrashCourse database
Copy the code

GRANT ALL ON bookstore.* TO ben;  -- Will give Ben all rights to the Bookstore database
REVOKE ALL ON bookstore.* FROM ben;
Copy the code

GRANT SELECT.INSERT ON bookstore.* TO ben; Multiple permissions are separated by commas
Copy the code

26.7 Changing passwords

SET PASSWORD FOR ben = Password('ben');   -- Change password
SET PASSWORD = Password('ben');   -- Set the login password of the current user
Copy the code

27. Database maintenance

27.1 Backing up Data

• Use the command line utilitymysqldumpDump all database contents to an external file
• Command line utilities are availablemysqlhotcopyCopy all data from a database
• The use of MySQLBACKUP TABLEorSELECT INTO OUTFILEDump all data to an external file

27.2 Maintaining the Database

ANALYZE TABLE orders; -- Used to check whether the table key is correct
CHECK TABLE orders, orderitems;   -- Used to check tables for a variety of problems
Copy the code

27.3 Troubleshooting Startup Problems

• Common command line options

--help # show help --safe-mode # Load minus some of the best configured servers --verbose # show full text messages --version # Show version information and exitCopy the code

27.4 Viewing log Files

• Error log: This includes details of startup and shutdown problems and any critical errors. Usually calledhostname.errLocated in the data directory, this log name is available--log-errorCommand line options changed.
• Query log: This logs all MySQL activity. Usually calledhostname.logLocated in the data directory, this log name is available--logCommand line options changed.
• Binary log: Records all statements that update data. Usually calledhostname-binLocated in the data directory, this log name is available--log-binCommand line options changed.
• Slow query log: This log records any queries that are executed slowly. Usually calledhostname-slow.log, located in the data directory. This name can be passed--log-slow-queriesCommand line options changed.

28. Improve performance

• Key production DBMSS should run on their own dedicated servers.
• There is always more than one way to write the same entrySELECTStatement, should experiment with joins, unions, subqueries, and so on to find the best way.
• Stored procedures execute faster than executing each MySQL statement one by one
• You should always use the correct data type
• Never retrieve more data than you need. That is, don’t use itSELECT *
• When importing data, autocommit should be turned off. Drop indexes and rebuild after the import (to speed up the import)
• Database tables must be indexed to improve database retrieval performance. If a simpleWHEREIf the clause takes too long to return the result, you can conclude that the column (or columns) used in it is the object that needs to be indexed.
• SELECTIf there’s a lot ofORConditions. By using multipleSELECTStatement and join themUNIONStatement, you can see great performance improvements.
• Indexes improve the performance of data retrieval, but impair the performance of data insertion, deletion, and update. If some tables are not searched very often, it is necessary not to index them before.
• LIKEIt’s slow. In general, it’s best to useFULLTEXTRather thanLIKE