MySQL must know must read notes

2020-02-21

Retrieve the data

create database if not exists my_db; Create database
use my_db; Select database
show tables; -- Displays all tables in the current library

create table if not exists user(
    u_id int primary key auto_increment comment 'id',
    name varchar(20) not null comment 'number',
    gender int(1) not null default 0 comment 'gender'.-- 0 male 1 female
    birthday date not null comment 'birthday',
    salary double not null default 0
)ENGINE = INNODB character set utf8;

insert into
    user(name, gender, birthday, salary)
values
    ('Joe'.0.'2000-05-28'.50000),
    ('bill'.1.'2000-07-15'.4200.5),
    ('Cathy'.0.'2000-05-28'.8000),
    ('Daisy'.1.'2000-07-15'.8000);

select * from user;

select name, gender, birthday, salary from user;

select distinct salary from user; -- To retrieve
select distinct salary, name from user; # Cannot be partially usedDISTINCT DISTINCTThe keyword applies to all columns, not just the column that precedes it. # If givenselect distinctSalary, name, all rows will be retrieved unless both columns specified are different.LIMIT 5 indicates that MySQL should return no more than 5 rows
select name from user limit 5; # LIMIT with a value always starts on the first line and gives the number of rows returned. A LIMIT with two values can specify that the line number starts at the first value. [0,n); The subscripts start at zeroselect * from user limit 1.3; Use fully qualified table namesselect * from my_db.user;
Copy the code

2020-02-22

Sorting retrieval data

-- Sort search
select * from my_db.user order by salary; -- Default ascending order

Sort by multiple columns
select * from my_db.user order by gender, salary; When a local sort condition is the same, compare the second sort condition

-- Specifies the sort direction

-- Ascending ASC(default) descending DESC
select * from my_db.user order by salary desc; -- Descending search

Use a combination of ORDER BY and LIMIT to find the highest or lowest value in a column.

select * from my_db.user order by salary desc limit 1;
Copy the code

Filter the data

-- Filter data
-- Use the WHERE clause
-- WHERE clause operator
select * from my_db.user where salary > = 8000;

select * from my_db.user where salary ! = 8000;  - is not equal to
select * from my_db.user where salary <> 8000;  - is not equal to

-- Check a single value
select * from my_db.user where salary = 8000;

-- Range check
select * from my_db.user where salary between 4000 and 10000;

-- Null check
select * from my_db.user where name is null;
Copy the code

Data filtering

-- Data filtering
Combine the WHERE clause
MySQL allows multiple WHERE clauses for stronger filtering control. These clauses can be used in two ways: as an AND clause OR as an OR clause.

select * from user where gender = 0 and salary > 5000;

select * from user where gender = 1 or salary > 10000;

-- And has a higher priority than and
select * from user where (gender = 1 or salary > 5000) and  birthday = '2000-05-28';

The -- IN operator
select * from user where salary in (50000.8000) order by salary;

The -- NOT operator
select * from user where salary not in (50000.8000) order by salary;
Copy the code

Filter with wildcard characters

Filter with wildcard characters
-- Percent (%) wildcard, in the search string, % represents any number of occurrences of any character. 0 ~ up
select * from user where salary like '8%';
select * from user where birthday like '2% 8';

-- Underscore (_) wildcard. Underscore serves the same purpose as %, but only matches a single character instead of multiple characters.
select * from user where name like 'a _';
Copy the code

As you can see, MySQL’s wildcards are useful. But this functionality comes at a cost: Wildcard searches generally take longer to process than the other searches discussed earlier. Here are some tips to keep in mind when using wildcards.

• 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. Placing wildcards at the beginning of a search pattern is the slowest.
• Pay close attention to the wildcard positions. If misplaced, the desired data may not be returned.

2021-02-23

Search with regular expressions

select * from user where salary REGEXP '8' order by salary; The salary column contains all rows of text 8

select * from user where name REGEXP 'the king.;-. Is a special character in the regular expression language. It means to match any character

-- LIKE matches the entire column. If the matched text appears in a column value, LIKE will not find it, and the corresponding line is not returned (unless wildcard characters are used). REGEXP matches within the column value, and if the matched text appears in the column value, REGEXP will find it and the corresponding row will be returned. This is a very important distinction.

-- Perform OR matching
select * from user where salary REGEXP '8000 | 9000'; - as one of the two search string (or for the string, or another string), using |. | for regular expressions OR operator

Matches one of several characters
select * from user where salary REGEXP '[89] 000'; -- [] is another form of the OR statement. In fact, the regular expression [123] Ton for Ton | 2 | 3 [1], can also use the latter.

select * from user where salary REGEXP '[9] ^ 000'; Character sets can also be negated, i.e., they will match anything but the specified character. To negate a character set, place an ^ at the beginning of the set.

-- Matching range
-- [0123456789] equals [0-9]
select * from user where salary REGEXP '[8-9] 000';

-- Matches special characters
The regular expression language consists of special characters with specific meanings. We've seen that. , [], | and - and so on, there are some other characters.
To match special characters, you must start with \\. \\- means to find -, \\. Means search. .
To match the backslash (\) character itself, use \\\.
select * from user where salary REGEXP '\ \.;

-- Matches character classes
select * from user where salary REGEXP '[[:digit:]]000'; - need to [[]]


select * from user where salary REGEXP '[[:digit:]]{5}'; -- Indicates five consecutive digits

select * from user where salary REGEXP '^ [88]';
select * from user where salary REGEXP '[5] $';
Copy the code

2021-02-24

Create computed fields

-- Concatenation field
- most of the DBMS using + or | | to implement stitching, MySQL is using Concat () function to implement. Keep this distinction in mind when converting SQL statements into MySQL statements.
select concat(name,'(', major, ') ') from stu;

The -- RTrim() function removes all Spaces to the right of the value
MySQL supports RTrim() (as you can see, it removes whitespace from the right side of the string), LTrim() (removes whitespace from the left side of the string), and Trim() (removes whitespace from the left side of the string).
select rtrim(concat(name, ' ', stu_num, ' ')) as name_id from stu;

-- Use alias
select name as 'name' from stu;

-- Perform arithmetic calculations
select concat(name, '(',mysql_score + java_score + linux_score,') ') asTotal scorefrom stu;
Copy the code