MySQL query is enough

1. Conditions

Use where Clause to filter the data in the table , The result is true Lines will appear in the result set

• The grammar is as follows ：

select * from Table name where Conditions ;
example ：
select * from students where id=1;

• where A variety of operators are supported , To deal with the conditions
  • Comparison operator
  • Logical operators
  • Fuzzy query
  • Range queries
  • Empty judgment

Comparison operator

• be equal to : =
• Greater than : >
• Greater than or equal to : >=
• Less than : <
• Less than or equal to : <=
• It's not equal to : != or <>

example 1： The query number is greater than 3 Of the students

select * from students where id > 3;

example 2： The query number is not greater than 4 Of the students

select * from students where id <= 4;

example 3： It's not “ Huang Rong ” Of the students

select * from students where name != ' Huang Rong ';

example 4： Search for students who have not been deleted

select * from students where is_delete=0;

Logical operators

• and
• or
• not

example 5： The query number is greater than 3 My female classmate

select * from students where id > 3 and gender=0;

example 6： The query number is less than 4 Or students who have not been deleted

select * from students where id < 4 or is_delete=0;

Fuzzy query

• like
• % Represents any number of arbitrary characters
• _ Represents an arbitrary character

example 7： Look up the student surnamed Huang

select * from students where name like ' yellow %';

example 8： Check the surname Huang and “ name ” It's a student of one word

select * from students where name like ' yellow _';

example 9： Ask the students whose surname is Huang or Jing

select * from students where name like ' yellow %' or name like '% Jing ';

Range queries

• in In a discontinuous range

example 10： The inquiry number is 1 or 3 or 8 Of the students

select * from students where id in(1,3,8);

• between ... and ... In a continuous range

example 11： The query number is 3 to 8 Of the students

select * from students where id between 3 and 8;

example 12： The inquiry number is 3 to 8 Of boys

select * from students where (id between 3 and 8) and gender=1;

Empty judgment

• Be careful ：null And '' Is different
• Sentenced to empty is null

example 13： Look up the students who didn't fill in the height

select * from students where height is null;

• The verdict is not empty is not null

example 14： Look up the students who have filled in their height

select * from students where height is not null;

example 15： Look up the boys who fill in the height

select * from students where height is not null and gender=1;

priority

• The order of priority from high to low is ： parentheses ,not, Comparison operator , Logical operators
• and Than or Calculate first , If it appears at the same time and wants to calculate first or, Need to combine () Use

2. Sort

For the convenience of viewing data , You can sort the data

grammar ：

select * from Table name order by Column 1 asc|desc [, Column 2 asc|desc,...]

explain

• Line data by column 1 Sort , If some ranks 1 When the values of are the same , According to the column 2 Sort , And so on
• By default, columns are arranged from small to large （asc）
• asc From smallest to largest , In ascending order
• desc Sort from large to small , In descending order

example 1： Query not deleted boys information , In descending order of student number

select * from students where gender=1 and is_delete=0 order by id desc;

example 2： Show all student information , First, according to age --> Small order , At the same age From height to height --> Short sort

select * from students order by age desc,height desc;

3. Aggregate functions

In order to get statistics quickly , The following is often used 5 Aggregate functions

total

• count(*) To calculate the total number of lines , Write stars and column names in brackets , The result is the same

example 1： Check the total number of students

select count(*) from students;

Maximum

• max( Column ) To find the maximum value of this column

example 2： Query the maximum number of girls

select max(id) from students where gender=2;

minimum value

• min( Column ) To find the minimum value of this column

example 3： Query the minimum number of students not deleted

select min(id) from students where is_delete=0;

Sum up

• sum( Column ) Means to find the sum of this column

example 4： Query the total age of boys

select sum(age) from students where gender=1;
-- Average age
select sum(age)/count(*) from students where gender=1;

Average

• avg( Column ) Means to find the average value of this column

example 5： Query the average number of undeleted girls

select avg(id) from students where is_delete=0 and gender=2;

4. grouping

group by

1. group by The meaning of : Query results according to 1 One or more fields to group , The fields with the same value are a set of
2. group by Can be used to group individual fields , It can also be used to group multiple fields

group by + group_concat()

1. group_concat( Field name ) It can be used as an output field ,
2. After the group , According to the grouping result , Use group_concat() To place a set of values for a field in each group

select gender from students group by gender;
+--------+
| gender |
+--------+
| male |
| Woman |
| Neutral |
| A secret |
+--------+
select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name) |
+--------+-----------------------------------------------------------+
| male | peng , Lau Andy , Jay Chou , Cheng Kun , Guo Jing |
| Woman | Xiao Ming , Little moon , Huang Rong , Joey wong , Liu Yifei , Jingxiang , Zhou Jie |
| Neutral | Venus |
| A secret | feng |
+--------+-----------------------------------------------------------+
select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| male | 3,4,8,9,14 |
| Woman | 1,2,5,7,10,12,13 |
| Neutral | 11 |
| A secret | 6 |
+--------+------------------+

group by + Set function

1. adopt group_concat() Inspired by the , Now that we can count the set of values for a field in each group , So we can also do this with the set function Collection of values Do something

select gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age) |
+--------+----------------------+
| male | 29,59,36,27,12 |
| Woman | 18,18,38,18,25,12,34 |
| Neutral | 33 |
| A secret | 28 |
+--------+----------------------+
The gender is male / The average age of women
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| male | 32.6000 |
| Woman | 23.2857 |
| Neutral | 33.0000 |
| A secret | 28.0000 |
+--------+----------+
The gender is male / The number of women
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| male | 5 |
| Woman | 7 |
| Neutral | 1 |
| A secret | 1 |
+--------+----------+

group by + having

1. having Conditional expression ： Used to specify some conditions to output query results after grouping query
2. having Function and where equally , but having It can only be used for group by

select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| male | 5 |
| Woman | 7 |
+--------+----------+

group by + with rollup

1. with rollup The role of is ： Add a new line at the end , To record the sum of all records in the current column

select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| male | 5 |
| Woman | 7 |
| Neutral | 1 |
| A secret | 1 |
| NULL | 14 |
+--------+----------+
select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age) |
+--------+-------------------------------------------+
| male | 29,59,36,27,12 |
| Woman | 18,18,38,18,25,12,34 |
| Neutral | 33 |
| A secret | 28 |
| NULL | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+

5. Get some lines

When the amount of data is too large , Looking at data on one page is a very cumbersome thing

grammar

select * from Table name limit start,count

explain

• from start Start , obtain count Data

example 1： Before query 3 OK, boy information

select * from students where gender=1 limit 0,3;

Example ： Pagination

• It is known that ： Each page shows m Data , The current display is n page
• Find the total number of pages ： This logic will be followed by python To realize
  • Total number of queries p1
  • Use p1 Divide m obtain p2
  • If you divide it, then p2 For the total number of pages
  • If you don't divide, then p2+1 Is the total number of pages
• Please n Pages of data

select * from students where is_delete=0 limit (n-1)*m,m

6. Link query

When the columns of query results are from multiple tables , You need to join multiple tables into a large dataset , Select the appropriate column to return to

mysql Three types of join queries are supported , Respectively ：

• Internal connection query ： The result of the query is the data matched by the two tables

• Right connection query ： The result of the query is the data matched by the two tables , Data unique to the right table , For data that does not exist in the left table, use null fill

• Left connection query ： The result of the query is the data matched by the two tables , Data unique to the left table , For data that does not exist in the right table, use null fill

grammar

select * from surface 1 inner or left or right join surface 2 on surface 1. Column = surface 2. Column

example 1： Use the inner link to query the class table and the student table

select * from students inner join classes on students.cls_id = classes.id;

example 2： Use the left link to query the class table and student table

• Here we use as Name the table , The goal is to write simple

select * from students as s left join classes as c on s.cls_id = c.id;

example 3： Use the right link to query the class table and student table

select * from students as s right join classes as c on s.cls_id = c.id;

example 4： Query the names of students and classes

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

7. Subquery

Subquery

In a select In the sentence , Embedded in another select sentence , So embedded select Statements are called subquery statements

Main query

The main query object , Article 1 with a select sentence

The relationship between main query and sub query

• Subqueries are embedded in the main query
• The subquery is auxiliary to the main query , Or as a condition , Or act as a data source
• Subqueries are statements that can exist independently , It's a complete select sentence

Sub query classification

• Scalar subquery : The result returned by the subquery is a piece of data ( Line by line )
• Column query : The result returned is a column ( A column with many lines )
• Line sub query : The result is a line ( One row and many columns )

Scalar subquery

1. Query the average age of class students
2. Look for students older than average

Query the average height of class students

select * from students where age > (select avg(age) from students);

Column level subqueries

• Query all class names of students in the class
• 1. Find all the classes in the student list id
  2. Find the corresponding name in the class table

select name from classes where id in (select cls_id from students);

Row level subquery

• demand : Find the oldest class , The tallest student
• Row element : Synthesize multiple fields into a row element , Row elements... Are used in row level subqueries

select * from students where (height,age) = (select max(height),max(age) from students);

Specific keywords are used in subqueries

• in Range
  • Format : Main query where Conditions in ( Column query )

summary

The full format of the query

SELECT select_expr [,select_expr,...] [
FROM tb_name
[WHERE conditional ]
[GROUP BY {col_name | postion} [ASC | DESC], ...]
[HAVING WHERE conditional ]
[ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
[ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

• complete select sentence

select distinct *
from Table name
where ....
group by ... having ...
order by ...
limit start,count

• The order of execution is ：
  • from Table name
  • where ....
  • group by ...
  • select distinct *
  • having ...
  • order by ...
  • limit start,count
• In practice , It's just a combination of parts of a sentence , Not all of them

View

1. What is the view

Generally speaking , The view is just a line SELECT The result set returned after the statement is executed . So when we create views , The main job is to create this SQL On the query statement .

A view is a reference to several basic tables , A virtual table , Query the result of statement execution , Don't store specific data （ The basic table data has changed , The view changes as well ）;

Convenient operation , Especially query operations , Reduce the complexity of SQL sentence , enhance readability ;

2. Define views

It is suggested that v_ start

create view View name as select sentence ;

3. View view

The view table will list all the views as well

show tables;

4. Use view

The purpose of a view is to query

select * from v_stu_score;

5. Delete view

drop view View name ;
example ：
drop view v_stu_sco;

6. The function of view

1. Improved reusability , It's like a function
2. Refactoring the database , It doesn't affect the operation of the program
3. Improved security performance , It can be applied to different users
4. Make the data clearer

Business

Transaction , It's a sequence of operations , These operations are either performed , Either not , It is an indivisible unit of work .

Four characteristics of transaction ( abbreviation ACID)

 

• Atomicity （atomicity）

A transaction must be treated as an indivisible minimum unit of work , All operations in the entire transaction either all commit successfully , Either all failures roll back , For a transaction , It is impossible to perform only some of these operations , That's the atomicity of the transaction

• Uniformity （consistency）

Databases are always moving from one consistent state to another .（ In the previous example , Consistency ensures , Even in the execution of the third 、 The system crashes between four statements , There's no loss in checking accounts 200 dollar , Because the transaction didn't commit in the end , So the changes made in the transaction will not be saved to the database .）

• Isolation, （isolation）

Generally speaking , Changes made by a transaction are made before the final submission , Is invisible to other transactions .（ In the previous example , When the third statement is executed 、 Before the fourth sentence begins , At this point, another account aggregation program starts to run , The check account balance is not subtracted 200 dollar .）

• persistence （durability）

Once the transaction is committed , Its changes will be permanently saved to the database .（ Even if the system crashes , Modified data is not lost .）

Indexes

When there is a large amount of data in the database , Finding data can be slow

Optimization plan ： Indexes

1. What is the index

Index is a special kind of file (InnoDB An index on a data table is an integral part of a table space ), They contain reference pointers to all records in the data table .

More generally speaking , A database index is like a table of contents at the front of a book , Can speed up the query speed of the database

2. Use of index

• Look at the index

show index from Table name ;

• Create index
  • If the specified field is a string , You need to specify the length , The recommended length is the same as the length when defining the field
  • If the field type is not a string , You can leave out the length section

create index The index name on Table name ( Field name ( length ))

• Delete index ：

drop index The index name on Table name ;