Advanced and Summary of Common sql Statements in Mysql Database

In this paper, the common sql statements of Mysql database are described by examples. Share it for your reference, as follows:

The Mysql sql basic statement was described earlier. Here we continue to summarize the advanced contents of SQL statement under 1.

SQL Statement Advanced

1. Query fields:

---Query all fields

select * from  Table name ;

----Query the specified field

select  Field name , Field name …  from  Table name ;

When multiple data tables join queries

select  Table name . Field name , Table name . Field name   …  from  Table name ;

----Alias tables using as

select  Table alias . Field name  from  Table name  as  Table alias ;

--Eliminate duplicate lines (distinct)

select distinct  Field name  from  Table name ;

2. Criteria query:

--Comparison operator ( > , < , =,! =)

select * from  Table name  where age >18; 

( < > Also means! =)

--Logical operators (and, or, not)

select * from  Table name  where age>18 and age<28; ( 18

3. Sort:

---Ascending order

select * from  Table name  order by asc; (Default to increased demand asc Can be omitted asc ) 

--descending order

select * from  Table name  order by desc;

4. Aggregate functions:

--Total count

select count(*) from  Table name ;

---Max max

select  Field name , Field name …  from  Table name ;

0

---Minimum min

select min(age) from  Table name ;

--Sum sum

select sum(age) from  Table name ;

Average avg

select  Field name , Field name …  from  Table name ;

3

---4 round 5 into reserved decimal round

select  Field name , Field name …  from  Table name ;

4

5. Clustering (focus):

--Grouping group by

select  Field name , Field name …  from  Table name ;

5

--Grouped queries (aggregate function, group_concat (), having)

select  Field name , Field name …  from  Table name ;

6

--Summary with rollup

select gender count(*) from  Table name  group by gender with rollup; (Last added 1 Row to display summary results) 

6. Paging:

---n data before query (limit1 is written in the best, indicating that the data after operation is displayed)

select * from  Table name  limit n;

----Paged display

select  Field name , Field name …  from  Table name ;

9

7. Connect queries (focus):

---inner join … on (internal connection)

select * from  Table name 1 inner join  Table name 2 on  Table name 1.cls_id= Table name 2.id; (Put the table 1cls.id And table 2id The same connection is in the 1 From)  
select  Table name 1. Field name 1, Table name 2. Field name .2 from  Table name 1 inner jion  Indicate 2 on  Condition ;

--left/right join … on (left/right/external connection)

select * from  Table name 1 left/right join  Table name 2 on  Table name 1.cls_id= Table name 2.id; The result of the query is the data matched by the two tables and the data unique to the left table. For the left table, / Data that does not exist in the right table uses null Fill) 

8. Subquery:

--Scaled quantum query (the result of subquery is 1 data (1 row and 1 column))

select * from  Table name  where age > (select avg(age) from  Table name );

--Column subquery (the result returned is 1 column (1 column with multiple rows))

select name from  Table name 1 where id in (select cls_id from  Table name 2);

--Row subquery (1 row (1 row with multiple columns) returned)

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

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Encyclopedia", "MySQL Stored Procedure Skills Encyclopedia" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.