MySQL query

Cascade:
On delete cascade
on update csacde update cascade
When deleting data: you need to delete the foreign key table before deleting the primary key table
When entering data: enter the primary key first, and then enter the foreign key

When the master table data is deleted, the master table data changes with the slave table, and when the master table data is modified, the slave table is modified

Query:

select Column name 1, column name 2 
from Table name
where Conditional expression
group by Listing
having Conditional expression
order by Listing
limit ?，? paging

When querying a table, you can specify to query some columns, and the order can be customized (query all columns with 'select *')
For example: select name,age,address,sex from student;
Columns and tables can be aliased, and columns in queries support operations

De duplication query (use as little as possible, affecting performance)

select distinct Listing from Table name

Condition query:
Conditional operators: = > < > = < = unequal: < > and=
Arithmetic operator: + - * / not supported: + + - match assignment + =-=
Logical operators:
And (& &): and: true if multiple conditions are true at the same time; otherwise, false is returned
Not (!): not: negative
Or (|): or, if only one of multiple conditions is true, the result is true, but none of them is false

Range query:
And operator connection condition: between a and b; (query data according to the range of a and b)

select * from studentinfo where age between 19 and 21; 

in (within...); Matching the value of a field is one of several possible option values, not the range, but the determined value

select * from student where age in(15,17,20); 

Fuzzy query

Wildcard:
"." matches any character
"[]" matches any character within []
'[^]' matches any character that is not within []
"*" matches zero or more characters before it
"+" matches the character before + one or more times
'{n}' matches the preceding character at least N times
"^" matches the start character of the text
"$" matches the end character of the text

For example: / / match names that start with a and end with c

select * from student where name regexp '^a.c$';

Fuzzy query like keyword
Use Wildcards
'% a' data ending in a
'a%' data starting with a
'% a%' contains data with a, three digits and a in the middle
‘_ S two digits and ends with a
‘ a_ ’ Two digits and starts with a

%: indicates any 0 or more characters, which can match characters of any type length. In case of Chinese, please use two percent signs (%%)
_ : Represents any single character. A statement that matches a single arbitrary character and is often used to limit the character length of an expression

For example:
– check that Zhang's name is two characters

select * from student where `name` like 'Zhang_'; 

– query names with King characters

select * from student where `name` like '%king%';

– query names with Chinese characters in the middle

select * from student where `name` like '_in_'; 

- there's Zhou Kou in Li's address

select * from student where `name` like 'Lee%' and address like '%Zhoukou%'; 

Sort operation

order by Listing asc Ascending (default) desc Descending order

Aggregate function

count(*)If the column name is empty, it will not be included in the total number
max(Column name) takes the maximum value in the column
min (Column name) takes the minimum value in the column
sum(Column name) summation
avg(Column name) takes the average value

Grouping query

group by column name is to group the same data rows in a column

Having: filter the grouped data. group by column name having condition

Paging query limit
To prevent explosion caused by too much data, you can query some data
Usage: select * from student limit (3, 5); (index, number of queries)

Common query syntax:

select Listing from Table name
where condition 
group by Group column names 
having Filter condition 
order by Sort column name
limit start ，length；

where equivalent connection: conditions must be added to make the query results meaningful

select * from student,classInfo
where student.classId = classinfo.classId

inner join (equivalent join)

select * from Table 1 alias 1
[inner] join Table 2 alias 2
on Alias 1.Peer data column = Alias 2.Peer data column # Conditions must be added
where condition
group by column
having condition
order by column
limit position,Number of records
limit position,Number of records

left outer join
The left table is the main table, and all the data in the left table will be displayed. The data in the right table will be displayed if it matches, and those that do not match will not be displayed

select s*,c* 
from student s			 # The table written in front is called the left table
left outer join classInfo c		 #The table written later is called the right table
on s.classId = c.classId

#right outer join
The data in the right table is mainly displayed. All the data in the right table will be displayed. The data in the left table will be displayed. Those that do not match will not be displayed

select s.*,c.*
from student s # The table written in front is called the left table
right outer join classInfo c #The table written later is called the right table
on s.classId = c.classId

#Query all students' before: class name, student name, gender, course name, score, and arrange them in reverse order of each subject

select
ci.className , s.name , s.sex , co.courseName , e.score
from student s , classInfo ci , course co , exam e
where s.classId = ci.classId
and s.studentId = e.studentId
and co.courseId = e.courseId
order by co.courseName asc,e.score desc

inner join connection

select ci.className,s.name,s.sex,co.courseName,e.score
from student s 
join classInfo ci
on s.classId = ci.classId # When two tables are connected, they become one table
join exam e 
on s.studentId = e.studentId
join course co
on e.courseId = co.courseId

Subquery
Nest another query (dolly) in the query

For example, the student's class number is equal to the class number of A1

select * from student
where cid = (
  select cid from classInfo where cname = 'A1' #It is mainly used to obtain the qualified student id, and then used to query the student table
)

Students who query Chinese courses and score more than 78

select * from student 
where studentId in ( #Query student information according to student id
  select studentId from exam
  where courseId = ( #Query the student id according to the course id
    select courseId from course where courseName = 'chinese' #Get course id
  ) and score>78
)

Multi table Association:
Subsection:
Only peer columns can be connected between multiple tables
where equivalent connection: the data of two tables that match each other will be displayed, and those that do not match will not be displayed
Inner join: inner join (equivalent effect)
Left outer join: left outer join (mainly the left table, and all the data in the left table)
Right outer join: right outer join (the right table is the main table, and all the data in the right table comes out)
Cross join: cross join, Cartesian product, connection without conditions