MySQL must know must read reading notes _0

Posted May 27, 20207 min read

MySQL \ _Note \ _0

Summary

  • The content is currently up to the end of Chapter 9 "Searching with Regular Expressions" in the book "MySQL Must Know"
  • The sequence of knowledge points is not completely consistent with the book:first the proposition, then the SQL statement
  • The expression may not be suitable for general reading habits
  • For the writing of SQL statements, the writing method in this book is more recommended

Content Overview

Keyword Function Introduction Nature Points to Note(Rules)
USE Select Database Statement
SHOW Understanding the database Statement
SELECT Retrieve Database Statement
DISTINCT Retrieve different rows Keyword Place before column name to be decorated
LIMIT Limit result Clause Followed by the number x is interpreted as a limit x line, followed by(x, y) is interpreted as a limit y line starting from line x
ORDER BY Specify the sort direction Clause Should be placed after the WHERE clause
ASC Ascending order Keyword Can not be written as the default value, MySQL will also be sorted by default(ascending order)
DESC Descending order Keyword Not default, when there are multiple columns as sorting basis, each column needs to have a DESC keyword modified after the column name
WHERE Specify search criteria Clause Should be placed in front of ORDER BY clause
BETWEEN Range value check Operator Used with AND-BETWEEN a AND b(between a and b, including a, b)
AND Two conditions before and after the connection Operator The conditions before and after the connection must be established at the same time
NULL means that there is no element that is not 0 Value The meaning of a special null value is neither 0 nor a null character
IN Specified condition range Operator The following condition range can be seen as a list of legal values
NOT All conditions after negation Operator There is one and only one function that follows the condition after negation
LIKE Fuzzy search mode Operators Combine literals, wildcards, or both to form search criteria
* Unspecified interpretation is arbitrary Wildcards Should not be used often, and sometimes unknown columns can be retrieved
% Similar to *, matches any arbitrary character Wildcard means any arbitrary character, but cannot be compared with NULL
_ Similar to%, matches a single character Wildcard Indicates a single arbitrary character, cannot be compared with NULL

Start using the database(when you do not use the visual operation database management tool)

  1. Win + R key
  2. Enter MySQL -uroot -p in cmd
  3. Type the password(not visible during input) and press Enter

-Note-

There should be no spaces between uroot, root is the administrator username set when installing MySQL

Both uppercase and lowercase can be used when using MySQL, but in order to develop a good habit, we should try to distinguish the built-in words in MySQL from pure uppercase to pure lowercase words that can be defined by ourselves.

Each sentence must end with ";" to be recognized as the end of the sentence

Start using MySQL statements

  1. After entering MySQL, all available databases(within current user rights) are displayed

     SHOW DATABASES;
  2. Use a database, assuming the library name is database \ _name

     USE database_name;
  3. Display available data tables in database database__name

     SHOW TABLES FROM database_name;
  4. Display information of all columns in data table table \ _name

     SHOW COLUMNS FROM table_name;

    The above statement can also be replaced by the DESCRIBE statement

     DESCRIBE table_name;
  5. Display all data items in data table table \ _name

     SHOW * FROM table_name;

    *is called a wildcard

  6. Retrieve all data items in column \ _1, column \ _2 in table \ _name

     SELECT column_1, column_2 FROM table_name;

    _When retrieving multiple columns, every two column names are separated by "," _

  7. Retrieve data items with different values in column column in data table table \ _name

     SELECT DISTINCT column FROM table_name;
  8. Retrieve all data items in column column of data table table \ _name and display no more than 5 rows

     SELECT column FROM table_name LIMIT 5;
  9. Retrieve all data items in column column of data table table \ _name and display 5 rows starting from row 5

     SELECT column FROM table_name LIMIT 5,5;
  10. Retrieve all the data items in the column column of the data table table \ name and display the 5 lines starting from the 5th line(note the difference between _line 5 and line 5)

    SELECT column FROM table_name LIMIT 4,5;
  11. Retrieve all contents in column column of data table table \ _name(table \ _name in database \ _name database)

    SELECT table_name.column FROM database_name.table_name;
  12. Retrieve all data items in column \ _1, column \ _2 in table \ _name in alphabetical order of column \ _1

    SELECT column_1, column_2 FROM table_name ORDER BY column_1;
  13. Retrieve all data items in column \ _1, column \ _2 in table \ _name, first arrange them according to column \ _2, then arrange them according to column \ _1

    SELECT column_1, column_2 FROM table_name ORDER BY column_2, column_1;

Specify the sorting direction

  1. Retrieve all data items in column column of table \ _name, sorted in ascending order of column column

     SELECT column FROM table_name ORDER BY column [ASC];
  2. Retrieve all data items in column column of table \ _name, in descending order of column column

     SELECT column FROM table_name ORDER BY column DESC;

    The ASC keywords in the above 1, 2 can be default, DESC must be marked, if there are multiple columns, the keywords must be modified after each column name

  3. Retrieve all the data items in column \ _1, column \ _2 in table \ _name, first sort them in descending order of column \ _1, and sort them in descending order of column \ _2 when column \ _1 has the same value

     SELECT column_1, column_2 FROM table_name ORDER BY column_1 DESC, column_2 DESC;
  4. Retrieve all data items in column \ _1, column \ _2 in table \ _name, first sort them in descending order of column \ _1, and sort them in ascending order of column \ _2 when column \ _1 has the same value

     SELECT column_1, column_2 FROM table_name ORDER BY column_1 DESC, column_2 [ASC];

Retrieve the most value in the column

_Combine the sequence and limit results, that is, the combined effect of ORDER BY, LIMIT _

  1. Retrieve the maximum value in column column in table \ _name

     SELECT column FROM table_name ORDER BY column_1 DESC LIMIT 1;
  2. Retrieve the minimum value in column column in table \ _name

     SELECT column FROM table_name ORDER BY column_1 [ASC]LIMIT 1;

Specify search criteria

  1. Retrieve all information of data items with value para \ _value in column column of table \ _name

     SELECT * FROM table_name WHERE column = para_value;
  2. Retrieve all information of data items whose value in column column of table \ _name is not para \ _value

     SELECT * FROM table_name WHERE column <> para_value;
  3. Retrieve all the information of data items whose value is not "String" in the column of table \ _name

     SELECT * FROM table_name WHERE column = "String";

    In MySQL, String and string string values are the same, and the matching is not case sensitive by default

Range value check

  1. Retrieve all information of the data items in the column of table \ _name whose value is between x-y(including x, y)

     SELECT column FROM table_name BETWEEN x AND y;

Null value check

  1. Retrieve all the information in column \ _1, column \ _3 of the data items of table \ _name whose column \ _2 column value is empty(column \ _2 field is NULL)

     SELECT column_1, column_3 FROM table_name WHERE column_2 IS NULL;

_Here we need to distinguish between the concept of NULL and mismatch _

Combine WHERE clauses to enhance data filtering

  • In order to filter more than one column, use the AND and OR operators for filtering control
  1. Retrieve all information for all rows of table \ _name whose column \ _1 column value is empty and whose column \ _2 column value is less than x

     SELECT * FROM table_name WHERE column_1 IS NULL AND column_2 <x;

    When using more than two filter conditions that need to be established at the same time, each additional one will add an AND

  2. Retrieve all information of all rows of table \ _name whose column \ _1 is empty and whose value of column \ _2 is less than x

     SELECT * FROM table_name WHERE column_1 IS NULL AND column_2 <x;
  3. Retrieve all information of all rows of table \ _name's column \ _1 column value is empty, or column \ _2 column value is not less than x

     SELECT * FROM table_name WHERE column_1 IS NULL OR column_2> = x;
  • _WHERE clause can contain any number of AND and OR operators, and use _ in combination
  1. Retrieve all information for all rows of table \ _name whose column \ _1 column value is empty or equal to para \ _value and the value of column \ _2 column is not less than x

     SELECT * FROM table_name WHERE(column_1 IS NULL OR column_1 = para_value) AND column_2> = x;

    _The above parentheses have higher priority than AND and OR, which can make the two data filtering conditions of column \ _1 take precedence, and the operators will not be combined by mistake

  • The _IN operator specifies a range of conditions, and each condition in the range can be matched _
  1. Retrieve all information of all rows of table \ _name's column \ _1 whose value is value \ _0 and equal to value \ _1

     SELECT * FROM table_name WHERE column_1 IN(value_0, value_1);

    You can also see the parentheses after the IN operator as a list of legal values of filter conditions separated by commas

  • _Combining OR and ORDER BY operators _
  1. Retrieve all the information of all rows of table \ _name's column \ _1 whose value is value \ _0 or equal to value \ _1 are arranged in the order of column \ _3

     SELECT * FROM table_name WHERE column_1 = value_0 OR column_1 = value_1 ORDER BY column_3;

    _ In fact, you can continue to return to the usage of the IN operator to implement the function of OR _

     SELECT * FROM table_name WHERE column_1 IN(value_0, value_1) ORDER BY column_3;

It can be seen that the legal value list of the IN operator is actually more intuitive and easier to read than OR, which is one of the advantages of IN

NOT operator:negate any conditions that follow

  1. Retrieve all information of all rows of table \ _name whose column \ _1 column value is not value \ _0 or value \ _1

     SELECT * FROM table_name WHERE column_1 NOT IN(value_0, value_1);

LIKE operator

  • Introduce wildcards and search mode

    • Special characters used to match part of the value-Wildcards
    • "Search" conditions consisting of literal values, wildcards or a combination of both

Percent sign % wildcard

% Means any character appears any number of times

  • All values beginning with the letters xyz can be written as xyz%
  • %xyz%-matches any text containing xyz anywhere
  • %xyz-matches text whose text ends with xyz
  • x%z-matches text that starts with x and ends with z

The search condition here is case sensitive, xyz% and Xyz% are different values
_%can match 0, 1 or more characters, and matching 0 characters involves tail space

_%cannot match NULL, i.e. _

SELECT * FROM table_name WHERE column LIKE '%';

It is unable to match the search for the row with the value of NULL

Underscore_ Wildcard

Usage is similar to `%, but only for matching a single character

Notes on wildcards

  • Processing time is longer than other operators
  • Do n t overuse, use other operators instead
  • Pay attention to the correct position