MySQL Basics (1): Basic applications

Keep a record of some grammar that may be used but easily forgotten.

first, the common grammar

1, "+": do not use string concatenation, only as an operator, other values +null or null

2, CONCAT (value1, value2);

3, ifnull (value, 0);

4, Length (str), gets the number of parameter bytes, the letter 1 bytes in the UTF8, the kanji 3 bytes

5, SUBSTRING (str, index, len) intercept str, starting with index, intercept len length, index starting from 1

6, InStr (str, SUBSTR), returns the first occurrence of the sub-string subscript, cannot find return 0, index starting from 1

7, replace (str, FROM_STR, TO_STR), from_str all replaced with TO_STR

8. Replace into has the ability to replace duplicate data with a unique index or primary key index, that is, if the unique index of data inserted using replace into or the primary key index is duplicated with the previous data, the original data will be deleted before being added.

REPLACE into Users (id,name,age) VALUES (123, ' Chao ', 50);

9. Numeric function

ROUND (1.567), rounded

ROUND (1.567,2), reserved 2 decimal places

Ceil (x), floor (x), rounding up and down

MOD (10,3), take surplus

RAND (), gets the random number, returns the decimal between 0-1

10. Date type Conversion

Str_to_date (Time, '%y-%m-%d '), string to date

Date_format (Time, '%y-%m-%d '), date to string

DATEDIFF (now (), ' 2018-9-1 '), two date difference in number of days

　　　　

11. Encryption

PASSWORD (str), the password form of the returned character

MD5 (str), return string MD5 encrypted form

12. Operators:

(1) > < = = = <> >= <=

(2) and or not

(3) "like", "between and", "in", "is null"

Like with wildcard characters, default is case-insensitive;

% any number of characters, _ account for a single character;

\ escape character, or escape can be declared by escape;

Between and will contain 0 bounds values;

In cannot be used in conjunction with wildcards, the efficiency is similar to or;

Is null can only judge null value,<=> can judge both null and ordinary value;

13. Process Control function

(1) IF (10<5, ' big ', ' small ')

(2) case, two ways

14. Grouping functions

Sum, AVG, Max, Min, count, all ignore null values and can be paired with distinct

Second, the connection

92 standard supports internal connection only, 99 standard full support

1, internal connection: Take two table intersection

SELECT * FROM table 1, table 2 where connection conditions

or "inner" join on join condition

Equivalent connections, non-equivalent connections (filter conditions are not used =, but range), self-linking (such as taking superior data)

2. External connection:

(1) LEFT outer: Left JOIN, Main table, show all data of primary table, sub-table mismatch part with NULL

(2) Right outside: Right join, Main table

(3) All-out: outer JOIN

Full OUTER join = INNER JOIN result + table 1 but table 2 not shown in table 2 but not in table 1

3. Cross-Joins: Two tables for Cartesian product

Third, sub-query

The subquery executes first, and the condition of the main query uses the result of the subquery

1. Can be paired with grouping function after select (only one row is supported)

　　

2, can be placed after from or exists (multi-row multi-column), when the virtual table used, need to Alias

　　

3, can put where or have after,

Line-by-row grouping functions > < >= <= = <>

A multi-line collocation in

There are limitations to a row of multiple columns, which require the same comparison operators used by each field, such as =, >, <

iv. pagination, joint query

1. SQL execution sequence

　　　　

(1) Order by, such as sorting by more than one field, first sort by first field, sort the same part, and then sort by the second field

(2) A table field that can be queried after group By,select must appear after the group by expression, grouped by multiple fields, and multiple fields consistently represent a group of

(3) Having, after putting group by, the query result set after grouping is filtered again

(4) Limit, put the query statement at the end, the page formula: pages to display page, number of entries per page size

　　　　Limit (page-1) *size, size

(5) Under MyISAM storage engine, COUNT (*) is high efficiency. InnoDB storage Engine, COUNT (*) and COUNT (1) are almost as efficient as count (*).

2. Joint Inquiry

Union: Query results come from multiple tables, and multiple tables do not have a direct connection relationship, but the query information is consistent, merging the results of multiple query statements into one result

Grammar:

Query Statement 1 Union

Query Statement 2 Union ....

(1) The number of query columns that require multiple query statements is consistent, and the type and order of the query columns are consistent

(2)the Union keyword is reset by default, and union all contains duplicates

　　

MySQL Basics (1): Basic applications