MySQL table query statement
DQL: query statement
Sort query
- Syntax: order by clause
-
Sort by:
- ASC: ascending order, 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:
A column of data is taken as a whole for longitudinal calculation
-
Count: count
Select count (age) from userinfo; -- returns the number. This method does not include null Select count (if null (age, 0)) from userinfo; -- judge whether it is null, if yes, calculate by 0 Select count (8) from userinfo; -- query records (not recommended)
-
Max: calculate the maximum value
Select max (age) from userinfo
-
Min: calculate the minimum value
Select min (age) from userinfo; -- calculate the youngest in the table
-
Sum: calculate and
Select sum (age) from userinfo; -- calculate the sum of all ages in the table
-
AVG: calculate the average value
Select AVG (age) from userinfo; -- calculate the average age in the table
Group query:
- Syntax: group by group field;
-
be careful:
- Query fields after grouping: grouping fields, aggregate functions
-
What’s the difference between where and having?
- Where is defined before grouping. If it does not meet the conditions, it will not participate in grouping. Having is defined after grouping. If the result is not satisfied, it will not be queried;
- Where condition can not be followed by aggregate function. Having can judge aggregate function
-
Implementation statement
--They were grouped according to sex, and the average age of each group was found out 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 participants 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 participants in the average age 并且过滤掉大于20岁的人 select sex,avg(age),count(age) from userinfo where age>20 group by sex; --The same as above and filter the group 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:
- Syntax: index starting from limit, number of queries per page
-
Formula: (starting index – 1) * number of queries per page
Select * from userinfo limit 0,3; -- page 1 Select * from userinfo limit 3,3; -- page 2 Select * from userinfo limit 6,3; -- page 3
- Limit is only applicable to MySQL; each database has its own paging mode
Constraints:
-
Concept: limit the data in the table to ensure the correctness, validity and integrity of the data in the table;
-
Classification:
- Primary key constraint: primary key
- Non NULL constraint: not null
- Unique constraint: unique
- Foreign key constraints
-
Nonempty 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, value cannot be repeated
-
When creating a table, add a unique constraint
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) Alter table userinfo drop index name -- this is correct
-
-
Primary key constraint primary key
-
be careful
- Meaning: not 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 primary key constraint when creating 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 a wrong way to write Alter table userinfo drop primary key; -- this is correct
-
Automatic growth
-
Concept: if a column is of type int, use auto_ Increment can achieve automatic growth
CREATE TABLE userinfo( id INT PRIMARY KEY AUTO_ Increment, - add auto growth name VARCHAR(20) ) --Note: automatic growth is only related to 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;
-
-
-
Foreign key constraints foreign key, so that the relationship between tables, so as to ensure the correctness of the data
-
grammar
create table userinfo ( .... Foreign key columns, Foreign key name (foreign key column name) references main table name (primary key column name) )
-
Delete foreign key
Alter table userinfo drop foreign key column name
-
Add foreign key after creating table
Alter table userinfo add constraint foreign key name foreign key references main table name
-
Cascade update operation
Alter table userinfo add constraint foreign key name foreign key references main table name on update cascade on delete cascade; -- on update cascade cascade on delete cascade
-
multi-table query
Internal connection
Implicit join query:
select * from emp,dept where emp.id=dept .rid -- emp.id Is a 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. Which tables to query data from
2. What are the conditions?
3. Which fields to query
External connection query
- Left external connection:
select * from emp left [outer] join dept on emp.id=dept . rid; -- [outer] is optional
--Left outer join queries the left table and its intersection
- Right outer link
select * from emp right [[outer]] join dept on emp.id=dept.rid;
--The right outer join queries the right table and its intersection
- Subqueries: nested queries
--Get the information about the oldest 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 the subquery is single row and single column. A subquery can be used as a condition, using operator judgment
--Query the person 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 = the name of a department or name = the name of another department;);
- The result of subquery is multi row and multi column
--Think of a 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