Time：2021-10-7

# MySQL table query statement

## Sort query

• Syntax: order by clause
• Sort by:

• ASC: ascending, default
• Desc: descending order.
• be careful:

• If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same

``SELECT * from userinfo ORDER BY age ASC,id DESC;``

## Aggregate function:

Take a column of data as a whole for longitudinal calculation

1. Count: count

``````select count(age) from userinfo; --  Return the number. Null will not be counted in this way
select count(ifnull(age,0)) from userinfo; --  Determine whether it is null. If yes, calculate by 0
select count(8) from userinfo； --  Query record (not recommended)``````
2. Max: calculate maximum

``select Max(age) from userinfo; --  The oldest person in the calculation table``
3. Min: calculate the minimum value

``select Min(age) from userinfo; --  The youngest in the calculation table``
4. Sum: Calculation and

``select sum(age) from userinfo; --  Calculate the sum of all ages in the table``
5. AVG: calculate average

``select avg(age) from userinfo; --  Average age in calculation table``

## Group query:

1. Syntax: group by group field;
2. be careful:

• Fields queried after grouping: grouping fields, aggregation functions
• What’s the difference between where and having?

1. Where is limited before grouping. If it does not meet the conditions, it will not participate in grouping. Having is limited after grouping. If the result is not satisfied, it will not be queried;
2. The where condition cannot be followed by the aggregate function. Having can judge the aggregate function
3. Implementation statement

``````--Groups were grouped according to sex, and the average age of each group was found
select sex,avg(age) from userinfo group by sex;
--Group according to sex, find out the average age of each group, and find out the number of people participating in the average age
select sex,avg(age),count(age) from userinfo group by sex;
--Group according to sex, find out the average age of each group, and find out the number of people participating in the average age 并且过滤掉大于20岁的人
select sex,avg(age),count(age) from userinfo where age>20 group by sex;
--Ditto and filter groups with less than 2 participants
select sex,avg(age),count(age) from userinfo where age>20 group by sex having count(age)<2;``````

## Paging query:

1. Syntax: index starting from limit, number of queries per page
2. Formula: (initial index – 1) * number of queries per page

``````select * from userinfo limit 0,3; --  first page
select * from userinfo limit 3,3; --  Page 2
select * from userinfo limit 6,3; --  Page 3``````
3. Limit is only applicable to MySQL; Each database has its own paging mode

## Constraints:

• #### Classification:

• Primary key constraint: primary key
• Non NULL constraint: not null
• Unique constraint: unique
• Foreign key constraint
• #### Non NULL constraint

• Add constraints when creating tables

``````CREATE TABLE userinfo(
id:INT,
Name: varchar (20) not null -- name is a non empty field
)``````
• After creating the table, add non empty fields to the fields in the table

``ALTER TABLE userinfo MODIFY NAME VARCHAR(20) NOT NULL;``
• Delete non empty fields

``ALTER TABLE userinfo MODIFY NAME VARCHAR(20);``
• #### Unique constraint: unique, the value cannot be repeated

• When creating tables, add unique constraints

``````CREATE TABLE userinfo(
id INT,
name:VARCHAR(20) UNIQUE
)``````
• Add a unique constraint after creation (Note: the unique constraint field can have multiple nulls)

``ALTER TABLE userinfo MODIFY NAME VARCHAR(20) UNIQUE;``
• Delete unique constraint

``````Alter table userinfo modify name varchar (20) -- this is wrong
Alter table userinfo drop index name -- this is correct``````
• #### Primary key constraint primary key

• be careful

• Meaning: non empty and unique
• A table can only have one primary key
• A primary key is the unique identifier of a record in a table
• Add a primary key constraint when creating a table

``````CREATE TABLE userinfo(
Id int primary key, -- add primary key
name VARCHAR(20)
)``````
• Add primary key after creating table

``ALTER TABLE userinfo MODIFY id INT PRIMARY KEY;``
• Delete primary key

``````ALTER TABLE userinfo MODIFY id int;  --  This is wrong
ALTER TABLE userinfo DROP primary key;  --  This is correct``````
• Automatic growth

• Concept: if a column is of type int, use auto_ Increment can complete automatic growth

``````CREATE TABLE userinfo(
id INT PRIMARY KEY AUTO_ Increment, -- add automatic growth
name VARCHAR(20)
)
--Note: automatic growth only keeps up with one item. For example, the previous item is 100 and the next item is 101
--Delete auto growth
ALTER TABLE userinfo MODIFY id int;
ALTER TABLE userinfo MODIFY id int auto_increment;``````
• #### The foreign key constrains the foreign key to generate a relationship between tables, so as to ensure the correctness of data

• grammar

``````create table userinfo (
....
Foreign key columns,
Constraint foreign key name foreign key references primary table name (primary table column name, that is, primary key name)
)``````
• Delete foreign key

``Alter table userinfo drop foreign key foreign key column name``
• Add foreign keys after creating tables

``Alter table userinfo add constraint foreign key name foreign key references main table name``

``Alter table userinfo add constraint foreign key name foreign key references main table name on update cascade on delete cascade-- On update cascade update on delete cascade delete``

## multi-table query

### Implicit join query:

``````Select * from EMP, Dept where EMP. Id = dept.rid -- emp.id is the foreign key, dept.rid is the primary key

select
t1.name,
t1.gender,
t2.name
from
emp t1,
dept t2
where
t1.id=t2.rid;``````

### Show internal connections

``````select * from emp inner join dept on emp.id=dept.rid;
select * from emp join dept on emp.id=dept.rid;  --  Inner is optional``````

### Internal connection query:

``````1. From which tables do you query data
2. What are the conditions?
3. Which fields to query``````

### External connection query

1. Left outer connection:
``````select * from emp left [outer] join dept on emp.id=dept.rid;  --  [outer] is optional
--The left outer join queries the left table and its intersection``````
1. Right outer connection
``````select * from emp right [[outer]] join dept on emp.id=dept.rid;
--The right outer join query is the right table and its intersection``````
1. Subqueries: nested queries
``````--Get the information of the maximum age
select Max(age) from userinfo; --  Find out the result set, for example: 56
select * from userinfo where age=56；
select * from userinfo where age=( select Max(age) from userinfo )；``````
• The result of subquery is single row and single column. Subquery can be used as a condition and judged by operators
``````--Query employees whose salary is less than the average salary
select * from userinfo where salary < ( select avg(salary) from userinfo )``````
• The result of subquery is single row and multi column
``````--Query all employee information of two departments
Select * from userinfo where rid in (select id from userinfo where name = "name of a department" or name = "name of another department";);``````
• The result of subquery is multi row and multi column
``````--Treat the subquery as a virtual table
select * from dept t1 ,(select * from emp where join_date>"2011-11-11") t2 where t1.id=t2.rid;``````

Update complete

## [JS reverse hundred examples] you haven’t seen the encryption of socialist core values

Focus on official account dry cargo WeChat public: K brother crawler, keep sharing crawler advance, JS/ Android reverse technology dry goods! statement All contents in this article are for learning and communication only. The packet capturing content, sensitive website and data interface have been desensitized. It is strictly prohibited to use them for commercial and […]