MySQL must know must learn notes

MySQL must know must learn notes

1. SQL basics

  • Database concept: a container for storing organized data.
  • Table: A structured list of a certain type of data.
  • Schema: Information about the layout and characteristics of databases and tables. The table has some characteristics, which define how the data is stored in the table, such as what kind of data can be stored, how the data is decomposed, how to name each part of the information, and so on.
  • Column: A field in the 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, which limits (or allows) the data stored in the column.
  • Row: A record in the table.
  • Primary key: A column (or a set of columns) whose value can uniquely distinguish each row in the table.
    • The primary key is used to represent a specific row. Without a primary key, it is difficult to update or delete specific rows in a table because there is no safe way to ensure that only related rows are involved .
    • The best habit of the primary key: do not update the value in the primary key column; do not reuse the value of the primary key column; do not use the value that may change in the primary key column.
  • SQL (Structured Qurey Language), a structured query language.

2. MySQL basics

  • The server part is a piece of software responsible for all data access and processing. This 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 update are completed by the server software. These requests come from or change from the computer running the client software.
  • mysql -u root -proot -P 3306
    Log in to the root user, the password is root, and the port number is 3306
  • The command is entered in
    mysql>
    after that
  • Command
    ;
    or
    \g
    the end
  • help
    or
    \h
    Get help. Such as
    help select
    ,
    \h select
    .
  • enter
    exit
    or
    quit
    Exit the command line utility.

3. SQL syntax

3.1 Select the database.

  • You must open the database before you can read the data.
Database_name the USE;    - select the database, called database_name 
copy the code

3.2 Display the names of all existing databases

SHOW DATABASES;
 copy code

3.3 Display all tables in a database

USE bookstore;
 SHOW TABLES;
 copy code

3.4 Display the information listed in the table

SHOW the COLUMNS the FROM book_list;   - information about all the columns in a table, 
the DESCRIBE book_list;   - upper and lower two commands are equivalent 
to copy the code

Output, respectively display the field name, data type, whether to allow null, key information, default value and other information (such as field

cust_id
of
auto_increment
).

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

3.5 Auto increment

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

3.6 Display extensive server status information

SHOW STATUS;
 copy code

4. retrieve data

4.1 Retrieving a single column

SELECT id
 FROM book_list;
 copy code

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

4.2 Retrieve multiple columns

The SELECT ID, `name`
 the FROM book_list;
 duplicated code

4.3 Retrieve all columns

SELECT  * 
FROM book_list;
 copy the code

Although using wildcards may save you trouble by not explicitly listing the required columns, retrieving unneeded columns usually reduces the performance of retrieval and unused programs. Therefore, it is best not to use it.

4.4 Retrieving different rows

SELECT  DISTINCT author
 FROM book_list;
 copy code

Only show different rows, not duplicates.

4.5 Limit the number of rows of output results

SELECT  * 
FROM book_list
LIMIT 5 ;   - Only display the first 5 lines of 
copy code

SQL defaults to the first line as 0.

SELECT  * 
FROM book_list
The LIMIT . 5 , . 5 ;   - the starting line, the number of lines 
copy the code
SELECT  * 
FROM book_list
LIMIT 4  OFFSET  3 ;   - Display 4 lines, 
copy the code from line 4

5. sort

5.1 Sorting in ascending order

SELECT id, `name`, price
 FROM book_list
 ORDER  BY price;   - sort the 
code in ascending order by price

5.2 Sort by multiple columns

The SELECT the above mentioned id, `name`,. Price, Sales
 the FROM book_list
 the ORDER  BY . Price, Sales;   - first Sort by price, followed by sorting by sales, are ascending 
copy the code

5.3 Sort in descending order

The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the ORDER  BY . Price DESC ;   - DESC in descending order, ASC ascending 
duplicated code

In sorting, the default is case-insensitive

5.4 Find the maximum value

The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the ORDER  BY . Price DESC 
the LIMIT . 1 ;    - show only the first line, it is to find the maximum value of the 
duplicated code

6. filter

6.1 Filter out the rows that meet the conditions

The SELECT the above mentioned id, `name`,. Price, Sales
 the FROM book_list
 the WHERE . Price = 20 ;   - 20 screened price of all goods 
copy the code
  • Single quotation marks are used to qualify strings. If you compare a value with a column of string type, you need to qualify quotation marks. The value used for comparison with the numeric column does not need to be quoted.
  • At the same time
    ORDER BY
    with
    WHERE
    Clause, you should let
    ORDER BY
    lie in
    WHERE
    After that, otherwise an error will be generated. 
    SELECT id, `name`, price, sales
 FROM book_list
 WHERE `name` REGEXP '[az]' 
ORDER  BY price;
 Copy code

6.2 Range value check

The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE . Price the BETWEEN  20 is  the AND  100 ;   - closing the left and right closed interval 
copy the code

6.3 Null value check

The SELECT ID, `name`,. Price, sales
 the FROM book_list
 the WHERE sales the IS  NULL ;   - the selection of the control content of the sales 
copy the code

6.4 Combination
where
Clause

The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ID <=  30  the AND . Price <=  20 is ;   - a combination of multiple conditions is determined 
duplicated code
The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ID =  26 is  OR ID =  30 ;   - or determination, with the number equal to remember =` ` 
duplicated code
  • SQL is processing
    OR
    Prior to operator processing
    AND
    Operator. Therefore, to be on the safe side, if you are dealing with multiple logical operators, it is best to use
    ()
    Expand up.

6.5
IN
Operator

The SELECT id, `name`,. Price, Sales
 the FROM book_list
 the WHERE id the IN ( 20 , 30 );   - id of all rows 20 or 30, the conditions enumerated in parentheses 
duplicated code

7. wildcard

7.1 Use wildcards to filter

7.1.1 Percent sign
%
Wildcard

%
Represents 0, 1, or more characters at a given position.

The SELECT the above mentioned id, `name`,. Price, Sales
 the FROM book_list
 the WHERE ` name` the LIKE  '% the Java' ; -% `` tell MySQL to accept any characters after the java, no matter how many characters. 
Copy code
SELECT id, `name`, price, sales
 FROM book_list
 WHERE `name` LIKE  '%java%' ; - `%` tells MySQL to accept any character that contains "java", no matter how many characters it has. 
Copy code

7.1.2 Underscore
_
Wildcard

_
Match a single character, no more and no less

The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ` name` the LIKE  'java_' ; - ``% MySQL told to accept "java" back with only a character string and 
copy the code

7.1.3 Tips for using wildcards

  • The processing of wildcard searches generally takes longer than the other searches discussed earlier.
  • Don't overuse wildcards. If other operators can achieve the same purpose, other operators should be used.
  • When you really need to use wildcards, don't use them at the beginning of the search pattern unless absolutely necessary. Put the wildcard at the beginning of the search pattern, the search is the slowest.
  • Pay careful attention to the location of wildcards. If it is misplaced, the desired data may not be returned.

8. regular expressions

  • Regular expression: It is a special string (character set) used to match text. All kinds of programming languages, text editors, operating systems, etc. support regular expressions.

8.1 Basic character matching

The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ` name` REGEXP, 'java' ;    - matches the column containing 'java' string row 
copy the code
The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ` name` REGEXP, '.ava' ;    -. Regular expression language is a special character, it matches any character	 
duplicated code

Regular expressions are not case-sensitive in MySQL. If you want to be case-sensitive, you need to add it in front of the matched string

BINARY
Keyword

The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ` name` REGEXP, BINARY  'Java' ;
 duplicated code

8.2 Proceed
OR
match

The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ` name` REGEXP, BINARY  'Java | the JAVA' ;   - `|` character represents an OR operator, or represents `java` comprising matching string` JAVA` 
copy the code
The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ` name` REGEXP, '[TJ]' ;   - t contains the matching string or row j 
copy the code
The SELECT ID, `name`,. Price, Sales
 the FROM book_list
 the WHERE ` name` REGEXP, '[AZ]' ;   - the line matching the string of letters contain any 
copy the code
SELECT vend_name
 FROM vendors
 WHERE vend_name REGEXP '\\.'   - special characters, need to pass escape character `\\` to indicate 
copy code

Match character class

classDescription
[:alpha:]
Any character, same as
[a-zA-Z]
[:lower:]
Any lowercase letters, the same
[az]
[:upper:]
Any uppercase letter, same as
[AZ]
[:alnum:]
Any letters and numbers, the same
[a-zA-Z0-9]
[:digit:]
Any number, the same
[0-9]
[:xdigit:]
Any hexadecimal number, same as
[a-fA-F0-9]
[:blank:]
Space and tabulation, the same
[\\t]
[:space:]
Any blank characters including spaces, the same as
[\\f\\n\\r\\t\\v]
[:cntrl:]
ASCII control characters (ASCII 0 to 31 and 127)
[:print:]
Any printable character
[:graph:]
versus
[:print:]
Same, but excluding spaces
[:punct:]
Neither
[:alnum:]
, Not in
[:cntrl:]
Any character in

8.3 Matching multiple instances

Repeating metacharacters

MetacharacterDescription
*
0 or more matches
+
1 or more matches, equal to
{1,}
?
0 or 1 match, equal to
{0, 1}
{n}
Specified number of matches
{n,}
Not less than the specified number of matches
{n, m}
Range of matching number (m does not exceed 255)
SELECT prob_name
 FROM products
 -\\( match ( 
- [0-9] match numbers 
-? Match one or 0 arbitrary characters 
-\\) match) 
WHERE prob_name REGEXP '\\([0-9] sticks ?\\)' 
Copy code
The SELECT prob_name
 the FROM Products
 - [: digit for:] matches any number 
- {4} exactly in front of the character appears in claim 4 times 
the WHERE prob_name REGEXP, '[[: digit for:]] {4}' 
the ORDER  BY prob_name;
 duplicated code

8.4 Locator

MetacharacterDescription
^
Start of text
$
End of text
[[:<:]]
The beginning of the word
[[:>:]]
End of word
The SELECT PROD_NAME
 the FROM Products
 the WHERE PROD_NAME REGEXP, '^ [0-9\\.]'   // Match begin or decimal string
 copy the code

^
in
[]
Means to negate the set and take the opposite of the set, such as
[^123]
; Otherwise it refers to the beginning of the string

9. calculated fields

9.1 Calculated fields

  • Many of the conversion and formatting tasks that can be done in SQL statements can be done directly in the client application. But generally speaking, completing these operations on the database server is much faster than on the client, because the DBMS is designed to complete these processes quickly and efficiently.

9.2 Splicing fields

The SELECT CONCAT ( 'A' , 'B' , 'C' );   - the result is 'abc' 
duplicated code

Most DBMS use + or || to achieve splicing, MySQL uses

CONCAT()
Function to achieve.

9.3 Clear the extra spaces on the left and right sides

The SELECT RTRIM ( 'FADFA' );   - clear a space on the right of 
the SELECT LTRIM ( 'FADFA' );   - clear a space on the left 
the SELECT  the TRIM ( 'FADFA' );   - remove all spaces 
copy the code

9.4 Using aliases

In order to better refer to a column, you can take an alias for a column

SELECT CONCAT(RTRIM(vend_name), '(' , RTRIM(vend_country), ')' ) AS vend_title   - behind AS is the alias 
FROM vendors;
 copy the code

10. Function

10.1 Functions

  • Functions are not as portable as SQL. Almost every major DBMS implementation supports functions that other implementations do not support, and sometimes the differences are quite large. After using some functions that are not very portable, it is not conducive to dynamically specifying the database management system.

10.2 Text processing functions

functionDescription
Left()
Returns the character to the left of the string
Right()
Returns the character on the right side of the string
Length()
Returns the length of the string
Lower()
Convert string to lowercase
Upper()
Convert string to uppercase
LTrim()
Remove the characters on the left side of the string
RTrim()
Remove the characters on the right side of the string
Locate()
Find a string of string
Soundex()
Returns the SOUNDEX value of the string
SubString()
Returns the characters of the string
- Counting from the left, intercept the first n 
SELECT  LEFT ( 'foobarbar' , 5 ) - ->'fooba' 
- Count from the right, intercept the n 
SELECT  RIGHT ( 'foobarbar' , . 4 ) - -> 'RBAR' 
duplicated code
- Locate the first position of the substring in the string, starting from 1. If not found, return 0 
SELECT LOCATE( 'bar' , 'foobarbar' )   ---> 4 
SELECT LOCATE( 'xbar' , 'foobar' ) - -> 0 
SELECT LOCATE( 'bar' , 'foobarbar' , 5 ) - -> 7 
duplicated code
- Substring, intercepted from the nth 
SELECT  SUBSTRING ( 'Quadratically' , 5 )   - ->'ratically' 
- Substring, intercepted from the nth 
SELECT  SUBSTRING ( 'foobarbar'  FROM  4 ) - -->'barbar' 
- substring, intercept m 
SELECT  SUBSTRING ( 'Quadratically' , 5 , 6 ) --->'ratica' 
- substring starting from the nth Truncated to the end of the string 
SELECT  SUBSTRING ( 'Sakila' , -3 ) - ->'ila'
- Substring, starting from the nth from the bottom, intercepting m 
SELECT  SUBSTRING ('Sakila' , -5 , 3 ) - ->'aki' 
- substring, starting from the nth from the bottom, intercepting m 
SELECT  SUBSTRING ( 'Sakila'  FROM  -4  FOR  2 ) ---> 'ki'Copy 
code
SELECT SOUNDEX( 'Hello' )   - ->'H400' 
SELECT SOUNDEX( 'Quadratically' )   --->'Q36324' 
- Find matching all similarly pronounced strings 
SELECT cust_name, cust_contact
 FROM customers
 WHERE Soundex(cust_contact) = Soundex( 'Y Lie' );
 copy the code

10.3 Date and time processing functions

functionDescription
CurDate()
Return current date
CurTime()
Return current time
Now()
Return the current date and time
Date()
Returns the date part of the date and time
Time()
Returns the time part of the date and time
Year()
Returns the year part of the date and time
Month()
Returns the month part of the date and time
Day()
Returns the days part of the date and time
Hour()
Returns the hour part of the date and time
Minute()
Returns the minute part of the date and time
Second()
Returns the second part of the date and time
AddDate()
Add a date (day, week, etc.)
AddTime()
Add a time (hour, minute, etc.)
DateDiff()
Returns the difference between two dates
Date_Add()
Highly flexible date calculation function
Date_Format()
Return a formatted date or time string
DayOfWeek()
For a date, return the corresponding 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( '2007-12-31 23:59:59.999999' , '1 1:1: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( '2007-12-31 23:59:59' , '2007-12-30') - -> 1 
SELECTThe DATEDIFF ( '2010-11-30 23:59:59' , '2010-12-31' ) ---> -31 
the SELECT the DAYOFWEEK ( '2007-02-03' ) - ->. 7 
duplicated code
  • Whether it is to insert or update table values or use
    WHERE
    Clause to filter, the date must be
    yyyy-mm-dd
    format.
  • However, use
    WHERE order_date = '2005-09-01'
    Unreliable. because
    order_time
    The data type is
    datatime
    , This type stores date and time values. The default time value is
    00:00:00
    . Therefore, by
    WHERE
    When filtering the date, it is easy to be unable to filter out because the time is not correct. The safer approach is:
SELECT cust_id, order_num
 FROM orders
 WHERE  Date (order_date) =  '2005-09-01' ;
 Copy code
  • If you want the date, use
    Date()
    , This is a good habit.
- Filter rows of a certain 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 code

10.4 Numerical processing functions

functionDescription
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()
Return to pi
Rand()
Returns a random number

10.5 Aggregate functions

functionDescription
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 values in a column
  • When calculating, ignore the value
    null
    Line.
  • Such as using
    COUNT()
    Function, if you use
    COUNT(*)
    , Will count all rows, but when using
    COUNT(COLUMNS_NAME)
    Time, if a certain behavior in the column
    null
    , It will be ignored directly.
  • use
    AVG()
    Function, if a certain behavior
    null
    , It will not be included in the calculation range, it will neither account for the number nor affect the value, and will be directly ignored.
  • DISTINCT
    Only the calculation contains different values.
SELECT  COUNT ( DISTINCT price)
 FROM book_list;

SELECT  AVG ( DISTINCT price)
 FROM book_list;
 copy code
  • These functions are designed to be highly efficient, and they generally return results much faster than client applications.

11. Data grouping

11.1 Packet count

  • Calculate how many have the same price.
SELECT price, COUNT (price)
 FROM book_list
 GROUP  BY price;
 copy code

GROUP BY
Clause must appear in
WHERE
After the clause,
ORDER BY
Before the clause. That is, first filter, then group, and finally sort.

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

11.2 Filter group

SELECT price, COUNT (price)
 FROM book_list
 GROUP  BY price
 HAVING  COUNT (price) >  8 ;
 Copy code
  • HAVING
    Support all
    WHERE
    Operator. What you have learned about
    WHERE
    All these technologies and options are applicable to
    HAVING
    . They have the same sentence structure, but the keywords are different.
  • Another way of understanding,
    WHERE
    Filter before data grouping,
    HAVING
    Filter after data grouping. This is an important difference,
    WHERE
    The excluded rows are not included in the grouping. This may change the calculated value, thereby affecting
    HAVING
    The groupings filtered out based on these values in the clause.
  • Generally in use
    GROUP BY
    Clause, it should also be given
    ORDER BY
    Clause, this is the only way to ensure that the data is sorted correctly. Don't just rely on
    GROUP BY
    Sort the data.
  • Until now,
    SELECT
    The order of clauses in the statement.
ClauseDescriptionDo you have to use
SELECT
The column or expression to returnYes
FROM
The table from which to retrieve dataOnly used when selecting data from the table
WHERE
Row-level filteringno
GROUP BY
Group descriptionOnly used when calculating aggregation by group
HAVING
Group level filteringno
ORDER BY
Output sort orderno
LIMIT
The number of rows to retrieveno

12. Subqueries

12.1 Subqueries

  • That is, queries nested in other queries.

12.2 Application scenarios

  • Two tables, one table stores user information, and the other table stores order information. The connection between the two tables is through the user ID. When you need to query the user information corresponding to the user ID in the specified order, you need to pass a subquery.
- Query the names of all users who purchased `TN2` 
SELECT user_name
 FROM user_info
 WHERE id in (
         SELECT id
         FROM orders
         WHERE prod_id =  'TN2'
);
Copy code
  • Subqueries are always processed from the inside out, first
    in
    The query inside the brackets is then processed outside the query.
  • in
    WHERE
    Using subqueries in clauses can write powerful and flexible SQL statements. But in actual use, due to performance limitations, too many subqueries cannot be nested
  • Related subqueries: subqueries involving external queries. Need to use fully qualified column names
WHERE orders.cust_id = customers.cust_id;
 copy code

13. Connection

13.1 Connection

  • One of the most powerful features of SQL is the ability to connect in the execution of data retrieval queries (
    join
    )table.
  • It is by no means a good thing that the same data appears multiple times. This factor is the basis of relational database design. The design of relational tables is to ensure that the information is broken down into multiple tables, one table for each type of data. The tables are related to each other through some commonly used values (that is, relationships in relationship design).
  • Each row in a table has a unique identifier, which is called the primary key. Another table is connected with the previous table through this identifier, and this identifier is called a foreign key in this table.

13.2 Foreign key (foreign key)

  • A foreign key is a column in a table that contains another primary key value and defines the relationship between the 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   - 
        FOREIGN KEY(dept_id) REFERENCES departments(dept_id)
);
Copy code

13.3 Create a connection

The SELECT vend_name, PROD_NAME, prod_price
 the FROM vendors, Products
 the WHERE vendors.vend_id = products.vend_id
 the ORDER  BY vend_name, PROD_NAME;
 duplicated code
  • Of this sentence
    FROM
    The clause lists two tables, they are this
    SELECT
    The names of the two tables joined by the statement. For two tables
    WHERE
    The clauses are connected normally.
  • WHERE
    As a filter condition after linking, if you don t use
    WHERE
    If it is, each row of the first table and each row of the second table will appear one by one in a combined form. (Cartesian Product)
  • Cartesian product: The result returned by the table relationship without join conditions is the 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.

13.4 Internal connection

The SELECT vend_name, PROD_NAME, prod_price
 the FROM vendors the INNER  the JOIN Products
 the ON vendors.vend_id = products.vend_id;
 duplicated code
  • by
    INNER JOIN...ON...
    The method connection.
  • ANSI SQL specification preferred
    INNER JOIN
    syntax.

13.5 Equivalent connection

  • The effects of the above two methods are exactly the same. In fact, the parts that have the same value in the two tables are taken, and the parts that do not have the same value are directly discarded. (That is, the intersection)

13.6 Join 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 code

13.7 Self-connection

  • That is to connect with oneself. It is usually used when a row of data has a certain attribute, and it is necessary to find all data with the same attribute in the table.
  • It can be realized by subquery or by self-join.
- Subquery method 
SELECT prod_id, prod_name
 FROM products
 WHERE vend_id = ( SELECT vend_id
                     FROM products
                     WHERE prod_id =  'DTNTR'
);

- Self-connection method 
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 code
  • Use self-join instead of sub-query : Self-join is usually used as an external statement to replace the sub-query used when retrieving data from the same table. Although the final result is the same, sometimes processing joins is much faster than processing subqueries. You should try two methods to determine which one has better performance.

13.8 Natural Connection

  • There should be at least one list. Now in more than one table, the standard join returns all data, even if the same column appears multiple times, the natural join excludes multiple occurrences, so that each column returns only once.
  • Implementation method: generally through the use of wildcards for the table (
    SELECT *
    ), to complete the use of explicit subsets 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 code

13.9 External connection

  • Many joins associate rows in one table with rows in another table, but sometimes it is necessary to include rows that are not related. (For example, some customers do not have orders, but they exist in the customer list and need to be displayed.) Connections include rows that have no associated rows in the related table. This type of connection is called an external connection.
SELECT customers.cust_id, orders.order_num
 FROM customers LEFT  OUTER  JOIN orders
 ON customers.cust_id = orders.cust_id;
 copy code
  • currently using
    OUTER JOIN
    When you must use
    RIGHT
    or
    LEFT
    The keyword specifies the table including all its rows.
    RIGHT
    Pointed out that
    OUTER JOIN
    The table on the right,
    LEFT
    Pointed out is the table on the left. The effect of the two types is actually the same, but the order is different.
  • External connection is to take
    RIGHT
    /
    LEFT
    All of the intersections with the other side are merged. Use the part that does not exist on the other side
    null
    To represent.

13.9 Using joins with aggregate functions

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

14. Combination query

  • Combined query: After executing multiple queries, pass and (
    UNION
    ), to achieve the merging of multiple query results.
SELECT vend_id, prod_id, prod_price
 FROM products
 WHERE prod_price <=  5 
UNION   - combined keywords 
SELECT vend_id, prod_id, prod_price
 FROM products
 WHERE vend_id IN ( 1001 , 1002 );
 copy code

14.1
UNION
rule:

  • Must consist of two or more
    SELECT
    Statement composition, use keywords between statements
    UNION
    Separated.
  • UNION
    Each query in must contain the same columns, expressions, or aggregate functions. (However, the columns do not need to appear in the same order.)
  • Column data must be compatible: the types need not be exactly the same, but they must be types that the DBMS can implicitly convert.
  • in
    UNION
    , The duplicate rows are automatically cancelled. If you don t want to remove duplicate rows, use
    UNION ALL
  • Sort the combined query results: add at the end
    ORDER BY
    Statement.

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 support full-text search.
  • In order to perform a full-text search, the indexed column must be indexed, and the index must be continuously reindexed as the data changes. After proper design of the table columns, MySQL will automatically perform all indexing and re-indexing.

15.2 Start a 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),    - specify the primary key of the table 
        FULLTEXT(note_id) - full text search index 
) ENGINE = MyISAM;    - specify the search engine 
duplicated code
  • After the definition, MySQL automatically maintains the index. When adding, changing, or deleting rows, the index is automatically updated accordingly.
  • Do not start full text search before importing data. You should start the full-text search after importing the data. Because every time data is imported, the index needs to be updated once, and the time to update each data index separately will be greater than the time to update all data indexes.

15.2 Perform a full text search

SELECT note_text
 FROM productnotes
 WHERE  Match (note_text) Against( 'rabbit' );    - Search all columns containing'rabbit' in the note_text column
 copy the code
  • The search will not be case sensitive.
  • Full-text search returns data sorted by how well the text matches. If both lines contain'rabbit', then the third word'rabbit' will have a higher priority than the twentieth word'rabbit'.

15.3 Perform query expansion

  • Query expansion is used to try to broaden the range of full-text search results returned.
  • 1. perform a basic full-text search to find all lines that match the search criteria.
  • 2. MySQL checks these matching rows and selects all useful words.
  • Secondly, MySQL performed a full-text search again, this time using not only the original conditions, but also all useful words.
The SELECT note_text
 the FROM productnotes
 the WHERE  Match (note_text) Against ( 'anvils'  the WITH the QUERY EXPANSION);	
 duplicated code

15.4 Boolean text search

  • In Boolean mode, you can provide details about the following:
    • Word to match
    • Words to be excluded
    • Arrangement hints (specify certain words are more important than others, and more important words have a higher level)
    • Expression grouping
    • Some other content
  • Even if not
    FULLTEXT
    Indexes can also be used, but this is a very slow operation.
The SELECT note_text
 the FROM productnotes
 the WHERE  Match (note_text) Against ( 'Heavy'  the IN  BOOLEAN the MODE);   - text searches using Boolean 
duplicated code
The SELECT note_text
 the FROM productnotes
 the WHERE  Match (note_text) aganist ( 'Heavy -rope *'  the IN  BOOLEAN the MODE); - sentence matching 'heavy' but does not match contain 'rope' at the beginning of the word 
copy the code
Boolean operatorsDescription
+Contains, word must exist
-Exclude, the word must not appear
>Include, and increase the level value
<Include, and reduce the level value
()Group words into sub-expressions (allow these sub-expressions as a group to be included, excluded, arranged, etc.)
~Cancel the sort value of a word
*Wildcard at the end of a word
""Define a phrase (not the same as a list of single words, it matches the entire phrase in order to include or exclude the phrase)
The SELECT note_text
 the FROM productnotes
 the WHERE  Match (note_text) Against ( '+ + Rabbit Bait'  the IN  BOOLEAN the MODE); - comprising word 'rabbit', and 'bait' lines (both must) 
copying the code
The SELECT note_text
 the FROM productnotes
 the WHERE  Match (note_text) Against ( 'Rabbit Bait'  the IN  BOOLEAN the MODE); - the line containing the word 'rabbit' or 'bait' to (at least one) 
copy the code
The SELECT note_text
 the FROM productnotes
 the WHERE  Match (note_text) Against ( ' "Rabbit Bait"'  the IN  BOOLEAN the MODE) - Search comprising matching phrase 'rabbit bait' instead of matching the two words 'rabbit', and 'bait' 
duplicated code
The SELECT note_text
 the FROM productnotes
 the WHERE  Match (note_text) Against ( '> rabbit <carrot'  the IN  BOOLEAN the MODE) - or rabbit comprising carrot, and increased levels of rabbit, carrot reduced level 
duplicated code
The SELECT note_text
 the FROM productnotes
 the WHERE  Match (note_text) Against ( 'Safe + + (<Combination)'  the IN  BOOLEAN the MODE) - must contain two words, and reduces the 'combination' level 
duplicated code

16. Insert data

16.1 Insert data

The INSERT  the INTO the Customers
 the VALUES ( NULL , 'of Pep' , '100' , 'Los Angeles' , 'the CA' , '90046' , 'USA' , NULL , NULL );
 duplicated code
  • You don t want to give a value, but you can t ignore a column, then you can give
    NULL
    value. Although this syntax is simple, it is not safe and should be avoided as much as possible. The above SQL is highly dependent on the definition order of the columns in the table, and also relies on the information that is easily available in its order, and it cannot guarantee that the columns will remain in exactly the same order after the next table structure change.
  • The safer method 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 inserting a row, the first value in MySQL corresponds to the first specified column name. The second value corresponds to the second column name, and so on.
  • Generally do not use the list without explicit columns
    INSERT
    Statements, using a list of columns can make the SQL code continue to function, even if the table structure changes.
  • If no value is given for a column that does not allow NULL values and does not have a default value in the table, MySQL will generate an error message and the corresponding row will not be inserted successfully.
  • If data retrieval is the most important, you can
    INSERT
    with
    INTO
    Add keywords between
    LOW_PRIORITY
    , Instructs MySQL to lower
    INSERT
    The priority of the statement (also applies to
    UPDATE
    with
    DELETE
    ):
INSERT LOW_PRIORITY INTO 
copy the code

16.2 Insert multiple rows

  • Single
    INSERT
    Handling multiple inserts than using multiple
    INSERT
    The statement is fast.
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' , '42 is' , 'New York' , 'NY' , '11213' , 'USA' );
 duplicated code

16.3 Insert the result of the query

  • Coming soon
    SELECT
    Insert the result into the table
The INSERT  the INTO the Customers (the cust_id, cust_contact, the CUST_EMAIL, CUST_NAME, cust_address, cust_city, cust_state, cust_zip, cust_country)
 the SELECT the cust_id, cust_contact, the CUST_EMAIL, CUST_NAME, cust_address, cust_city, cust_state, cust_zip, cust_country
 the FROM custnew;
 duplicated code

INSERT SELECT
Insert through the one-to-one correspondence of column positions, the column names do not have to be the same.

17. Update data

17.1
UPDATE
component

  • Table to update
  • Column names and their new values
  • Determine the filter criteria for the rows to be updated
The Customers the UPDATE
 the SET the CUST_EMAIL =  'elmer@fudd.com'   - setting a new value of 
the WHERE the cust_id =  1005 ;   - Filters 
duplicated code
The Customers the UPDATE
 the SET CUST_NAME =  'of The Fudds' ,    - plurality of rows 
        the CUST_EMAIL =  'elmer@fudd.com' 
the WHERE the cust_id =  1005 ;   - Filters 
duplicated code

17.2
IGNORE
Keyword

  • If you use
    UPDATE
    When the statement updates multiple rows, one or more rows have an error, and continue to update, you can use
    IGNORE
    Keywords.
UPDATE IGNORE customers...
 copy code

17.3 Delete a value in a column

  • Can be set to
    NULL
UPDATE customers
 SET cust_email =  NULL   - remove the value of a column 
WHERE cust_id =  10005 ;
 copy the code

18. Delete data

18.1 Delete a row

DELETE  FROM customers
 WHERE cust_id =  10006 ;
 Copy code

18.2 Delete all rows in the table

DELETE  FROM customers;
 copy code
  • A faster method, this method directly deletes the entire table and then creates a new table, and this method cannot be rolled back:
TRUNCATE  TABLE customers;
 copy code

18.3 Use
UPDATE
with
DELETE
Habits followed when:

  • Unless you really intend to update or delete every row, don t leave it without
    WHERE
  • Ensure that each table has a primary key,
    WHERE
    The clause uses the primary key to filter
  • in
    UPDATE
    with
    DELETE
    Before, use
    SELECT
    The clauses are tested to ensure that the filtered data is correct.

19. Create table and manipulate table

19.1 Create Table

  • Use a comma between each column. The definition of each column starts 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 = the InnoDB;
 duplicated code
  • The column definitions are properly indented for easy reading and editing.
  • When creating a new table, the specified table name must not exist, otherwise an error will occur. To prevent errors, you can delete and then create.
The DROP  TABLE the IF EXISTS the Customers; 
 duplicated code
  • Can be created when the table does not exist
CREATE  TABLE customers IF NOT  EXISTS
(
        ...
);
Copy code

19.2 Use
NULL
value

  • Each table column is or
    NULL
    Column, or
    NOT NULL
    Column, this state is specified by the definition of the table when it is created. use
    NOT NULL
    The column of does not accept columns that do not have a value in the column, in other words, the column must have a value when inserting or updating a row.
  • NULL
    Is the default setting, if not specified
    NOT NULL
    , It is assumed that the specified
    NULL
    .

19.3 Using the primary key

  • The primary key value must be unique. That is, each row in the table must have a unique primary key value. If a single column is used for the primary key, its value must be unique. If multiple columns are used, the combined value of these columns must be unique.
A PRIMARY KEY (vend_id)
 a PRIMARY KEY (order_num, Order_Item)
 copy the code

19.4 Use
AUTO_INCREMENT

  • AUTO_INCREMENT
    Tell MySQL that this column automatically increments whenever a row is added. Only one per table is allowed
    AUTO_INCREMENT
    Column, and it must be indexed.
  • If a column is specified as
    AUTO_INCREMENT
    , Then it can simply
    INSERT
    Specify a value as long as it is unique. Subsequent increments will begin to use the manually inserted value.
  • Next
    AUTO_INCREMENT
    Value of
SELECT LAST_INSERT_ID();
 Copy code

19.5 Specify default values

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 = the InnoDB;
 duplicated code
  • Like most DBMSs, MySQL does not allow functions to be used as default values, it only supports constants.
  • Many database developers use default values instead of
    NULL
    Columns, especially columns used for calculations or data grouping.

19.6 Engine type

  • InnoDB: Is a reliable transaction processing engine, it does not support full text search.
  • MEMORY: The function is equivalent to MyISAM, but because the data is stored in memory (not disk), the speed is very fast (especially suitable for temporary tables)
  • MyISAM is an extremely high-performance engine, it supports full-text search , but does not support transaction processing.

19.7 Update table

  • In an ideal state, when data is stored in the table, the table should not be updated. In the design process of the table, it takes a lot of time to consider, so that no major changes are made to the table later.
  • Add column
The ALTER  TABLE vendors
 the ADD vend_phone CHAR ( 20 is );   - adding columns 
duplicated code
  • Delete column
The ALTER  TABLE vendors
 DROP  the COLUMN vend_phone;   - delete columns 
copy the code
  • Define foreign keys
The ALTER  TABLE OrderItems
 the ADD  CONSTRAINT fk_orderitems_orders
 a FOREIGN KEY (order_num) the REFERENCES Orders (order_num);
 duplicated code

Complex table structure generally requires manual deletion process, which involves the following steps:

  • Create a new table with new column layout
  • use
    INSERT SELECT
    The statement copies data from the old table to the new table.
  • Verify the new table containing the required data
  • Rename/delete old table
  • Rename the new table with the name of the old table
  • Re-create triggers, stored procedures, indexes, and foreign keys as needed.

19.8 Delete table

The DROP  TABLE Customers2;
 duplicated code

19.9 Rename table

RENAME TABLE customers2 to customers;
RENAME TABLE customers2 to customers,
                        customers3 to customers2,
                        customers4 to customers3;
 copy code

Twenty, use the view

20.1 View

  • It is a virtual table. Unlike a table that contains data, a view only contains queries that dynamically retrieve data when used.

20.2 View common applications

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

20.3 The view itself does not contain data

  • Therefore, the data they return is retrieved from other tables. When adding or changing data in these tables, the view will return the changed data.

20.4 View rules and restrictions

  • Views can be nested, that is, you can use queries that retrieve data from other views to construct a view
  • ORDER BY
    Can be used in the view, but if the data is retrieved from the view
    SELECT
    The sentence also contains
    ORDER BY
    , Then the
    ORDER BY
    Will be overwritten
  • The view cannot be indexed, nor can it have associated triggers or default values
  • Views can be used with tables. For example, write a link table and view
    SELECT
    Statement

20.5 Using views

  • View use
    CREATE VIEW
    Statement to create
  • use
    SHOW CREATE VIEW viewname
    To see the statement that created the view
  • DROP VIEW viewname
    Delete view
  • When updating the view, you can use
    DROP
    Reuse
    CREATE
    , Can also be used directly
    CREATE OR REPLACE VIEW
CREATE  VIEW productcustomers AS   - create a 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 code
  • use
    view
The SELECT CUST_NAME, cust_contact    - column 
the FROM productcustomers    - the VIEW 
the WHERE the prod_id =  'TNT2' ;    - Filters 
duplicated code

Using views, basic SQL can be written once and then used as many times as needed.

20.6 Format the retrieved data

The CREATE  the VIEW vendorlocations the AS   - Create View 
the SELECT Concat (RTrim (vend_name), '(' , RTrim (vend_country), ')' )    - formatted data retrieved 
the FROM vendors
 the ORDER  BY vend_name;
 duplicated code
The SELECT  *    - calling view 
the FROM vendorlocations
 copy the code

20.7 Filter unwanted data

The CREATE  the VIEW customeremaillist the AS 
the SELECT the cust_id, CUST_NAME, the CUST_EMAIL
 the FROM the Customers
 the WHERE the CUST_EMAIL the IS  the NOT  NULL ;
 duplicated code

20.8 Using calculated fields

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

20.9 Update view

  • Views are updatable, you can use them
    INSERT
    ,
    UPDATE
    ,
    DELETE
    operating. If you add or delete rows to the view, you are actually adding or deleting rows to its base table.
  • If the view contains the following operations, it cannot be updated: grouping, join, subquery, union, aggregate function (
    Min()
    ,
    Count()
    ,
    Sum()
    Wait)
  • DISTINCE
  • Export column

Generally, the view should be used for retrieval, not for update

Twenty-one, stored procedures

21.1 Stored Procedure

  • It is a collection of one or more MySQL statements saved for future use. 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 the management of changes. If the table name, column name or business logic changes, only the code of the stored procedure needs to be changed, and the people who use it do not even need to know these changes (encapsulation)
  • Improve performance. Using stored procedures is faster than using separate SQL statements

21.3 Create a stored procedure

The CREATE  PROCEDURE productpricing ()    - definition of stored procedures, if accepted parameters are defined in the parentheses 
the BEGIN     - body of limiting 
        the SELECT  Avg. (Prod_price) the AS priceaverage
         the FROM Products;
 the END ;
 duplicated code
  • However, this operation will cause an error because
    ;
    As a statement separator, there are also internal
    ;
    , Will cause the misunderstanding that the process body ends early. Therefore, you need to temporarily modify the statement separator.
DELIMITER //    - Temporarily modify the separator to'//'

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

DELIMITER;    - modified delimiters back 
copy the code

21.4 Executing a stored procedure

CALL productpricing();
 Copy code

21.5 Delete stored procedures

DROP  PROCEDURE productpricing;
 DROP  PROCEDURE IF EXISTS productpricing;    - if it exists delete 
copy the code

21.6 Use 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 code
  • MySQL support
    IN
    (Passed to the stored procedure),
    OUT
    (From the stored procedure) and
    INOUT
    Parameters of type (incoming and outgoing to stored procedures).
CALL productpricing( @pricelow ,     - All MySQL variables must start with 
                                        @@pricehigh ,
                                         @priceage
);
Copy code
The SELECT  @pricelow ;    - displays the search results 
duplicated code
CREATE  PROCEDURE ordertotal(
         IN onumber INT ,
         OUT ototal DECIMAL ( 8 , 2 )
)
The BEGIN 
        the SELECT  the Sum (ITEM_PRICE * Quantity)
         the FROM OrderItems
         the WHERE order_num = onumber
         the INTO ototal;
 the END ;
 duplicated code
The CALL OrderTotal ( 20005.  @total );    - call a stored procedure 
the SELECT  @total ;   - View Results 
copy the code

21.7 Establishing an intelligent stored procedure

CREATE  PROCEDURE ordertotal(
         IN  		onumber		 INT ,
         IN  		taxable 	 BOOLEAN ,
         OUT 		ototal		 DECIMAL ( 8 , 2 )
) COMMENT'Obtain order total, optionally adding tax'    - This sentence will be displayed on `SHOW PROCEDURE STATUS` 
BEGIN 
        DECLARE total DECIMAL ( 8 , 2 );    - Declare the variable 
        DECLARE taxrate	 INT  DEFAULT  6 ;    - Declared tax rate percentage

        The SELECT  the Sum (ITEM_PRICE * Quantity)
         the FROM OrderItems
         the WHERE order_num = onumber
         the INTO total;     - the calculated amount is stored in the variable total

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

        SELECT total INTO ototal;    - save the result on ototal 
END ;
 copy the code

transfer:

The CALL OrderTotal ( 20005 , 0 , @total );    - stored procedure calls 
the SELECT  @total ;   - shows the results of 
duplicated code

21.8 Check the stored procedure

SHOW  CREATE  PROCEDURE OrderTotal;   - displaying a CREATE statement to create a stored procedure 
SHOW  PROCEDURE the STATUS; - all the information stored in the existing procedure, including when, by whom to create other details are shown 
SHOW  PROCEDURE the STATUS the LIKE  'search_user ' ;   - wildcard screened by 
copying the code

Twenty-two, use the cursor

22.1 Cursor

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

22.2 Using cursors

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

        OPEN ordernumbers;   --  
        CLOSE ordernumbers;   --  
END;

22.4

  • FETCH
DECLARE o INT;   --  
OPEN ordernumbers;   --  
FETCH ordernumbers INTO o;  --  o 
CLOSE ordernumbers;   --  
 
CREATE PROCEDURE processorders()
BEGIN
        DECLARE done BOOLEAN DEFAULT 0;   --  
        DECLARE o INT;   --  
        DECLARE ordernumbers CURSOR  --  
        FOR
        SELECT order_num FROM orders;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;  --  SQLSTATE = '02000' done 1 
        OPEN ordernumbers;   --  

        --  
        REPEAT
                FETCH ordernumbers INTO o; - save the data in a local variable 
        UNTIL done END REPEAT;    - close the loop condition (done=1) 
        CLOSE ordernumbers;    - close the cursor 
END ;
 copy the code

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
This sentence defines a
CONTINUE HANDLER
, The statement that is executed when the condition occurs. Here, it states that when
SQLSTATE '02000'
When it appears,
SET done=1

Twenty-three, use triggers

  • If you want to automatically execute a certain statement (or certain statements) when an event occurs, you need to use a trigger.
  • The trigger is triggered when the specified table is changed, and the targeted operations are
    INSERT
    ,
    UPDATE
    ,
    DELETE
    , Triggers can be set before and after the event.
  • To create a trigger, 4 pieces of information need to be given
    • Unique trigger name
    • Trigger associated table
    • The activity that the trigger should respond to (
      INSERT
      ,
      UPDATE
      ,
      DELETE
      )
    • When the trigger is executed (before or after processing)

23.1 Create Trigger

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

23.2

23.3 

DROP TRIGGER newproduct;

23.4
INSERT

  • INSERT
    NEW
  • BEFORE INSERT
    NEW
  • AUTO_INCREMENT
    NEW
    INSERT
    0
    INSERT
    CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;    --  order_num insert

23.5
DELETE

  • DELETE
    Within the trigger code, you can reference a file named
    OLD
    The virtual table to access the deleted row.
  • OLD
    The values in are read-only and cannot be updated.
- Use OLD to save the rows to be deleted in an archive table 
CREATE  TRIGGER deleteorder BEFORE DELETE  ON orders    - 
FOR  EACH  ROW 
BEGIN 
        INSERT  INTO achieve_orders(order_num, order_datae, cust_id) before deleting - Save the old values  in a
         file   called 'achieve_orders' table the VALUES (OLD.order_num, OLD.order_date, OLD.cust_id);
 the END ;
 duplicated code

use

BEFORE DELETE
Without using
AFTER DELETE
The advantage is to prevent the order from being archived for some reason.

23.6
UPDATE
trigger

  • in
    UPDATE
    In the trigger code, you can reference
    NEW
    Access the newly updated value, you can also reference
    OLD
    Access the previous value.
  • in
    BEFORE UPDATE
    In the trigger,
    NEW
    The value in may also be updated (allowing the update to be used for
    UPDATE
    Value)
  • OLD
    The values are all read-only and cannot be updated
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);   --

24.1

  • MySQL

24.2

  • InnoDB

24.3 transaction

  • SQL

24.4 rollback

  • SQL

24.5 commit

  • SQL

24.6 savepoint

  • place-holder

24.7
ROLLBACK

SELECT * FROM ordertotals;
START TRANSACTION;   --  
DELETE FROM ordertotals;   --  
SELECT * FROM ordertotals;   --  
ROLLBACK;   --  
SELECT * FROM ordertotals;    --
  • ROLLBACK
  • INSERT
    DELETE
    UPDATE
    CREATE
    DROP

24.8
COMMIT

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

24.9 

SAVEPOINT delete1;   --  
 
ROLLBACK TO delete1;  --
  • ROLLBACK
    COMMIT

24.10

  • MySQL MySQL 
SET autocommit=0;
 
--  
SET autocommit=0; --  
START TRANSACTION; --  

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

UPDATE boys
SET boyName=' '
WHERE id=6;

COMMIT; --  
-- rollback; --

25.1

25.2

25.3

25.4 

SHOW CHARACTER SET;

25.5 

SHOW COLLATION;

25.6 

CREATE TABLE mytable
(
        column1		INT,
        column2		VARCHAR(10)
) DEFAULT CHARACTER SET hebrew,  --  
COLLATE hebrew_general_ci;   --

26. Safety Management

26.1 Security Fundamentals

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

26.2 Manage users

MySQL the USE;
 the SELECT  the User  the FROM  the User ;   - obtain a list of all user accounts 
Copy the code
  • User accounts and information are stored in a MySQL database named mysql

26.3 Create User Account

The CREATE  the USER ben IDENTIFIED BY  'ben' ;   - Create a new user named ben, ben password to 
copy the code

IDENTIFIED BY
The specified password will be encrypted

26.4 Rename account

RENAME USER ben TO ben1;
 copy code

26.5 Delete user account

DROP  USER ben1;
 copy code

26.6 Set access permissions

After creating a user account, you must then assign access rights. The newly created user account does not have access rights. They can log in to MySQL, but they cannot see the data and cannot perform any database operations. View user account permissions

SHOW GRANTS FOR ben;

-- -- -- 

GRANT SELECT ON crashcourse.* TO ben;  --  crashcourse SELECT ben ben crashcourse 
REVOKE SELECT ON crashcourse.* FROM ben;  --  ben crashcourse 
 
GRANT ALL ON bookstore.* TO ben;  --  ben bookstore 
REVOKE ALL ON bookstore.* FROM ben;
 
GRANT SELECT, INSERT ON bookstore.* TO ben; --

26.7 

SET PASSWORD FOR ben = Password('ben');   --  
SET PASSWORD = Password('ben');   --

27.1

  • mysqldump
  • mysqlhotcopy
  • MySQL
    BACKUP TABLE
    SELECT INTO OUTFILE

27.2 

ANALYZE TABLE orders; --  
CHECK TABLE orders, orderitems;   --

27.3 

--help  #  
--safe-mode #  
--verbose  #  
--version  #

27.4

  • hostname.err
    data
    --log-error
  • MySQL
    hostname.log
    data
    --log
  • hostname-bin
    data
    --log-bin
  • hostname-slow.log
    data
    --log-slow-queries

  • DBMS
  • SELECT
  • MySQL
  • SELECT *
  • WHERE
  • SELECT
    OR
    SELECT
    UNION
  • LIKE
    FULLTEXT
    LIKE