# Multi-condition coordination operation import: where odd number [group by department having average salary] order by [average] salary limit 1# compare matching mysql>: select * from emp where salary>5; # Query all records of salary>5 mysql>: select * from emp where id%2=0; # Query all records whose id is even mysql>: select * from emp where id%2= 1; # Query all records whose id is an odd number# Interval matching mysql>: select * from emp where salary between 6 and 9; # Query records with salary in the range of 6-9# Fuzzy matching mysql>: select * from emp where name like "%w%"; # Match the record that contains w in the name field mysql>: select * from emp where name like "_w%"; # Match the record that contains an arbitrary character w in the name field mysql>: select * from emp where name like "__e%"; # matches records that contain two arbitrary characters w in the name field# sql only supports partial regular syntax mysql>: select * from emp where name regexp'.*[0-9]'; # support []Syntax

Six, aggregate function

• max(): Maximum value
• min(): Minimum value
• avg( ): Average
• sum(): Sum
• count(): Count
• group_concat(): Field splicing in the group

Emphasis: The aggregation function aggregates the content of the group, if not Group, the default group.

# Find the number of all records mysql>: select count(*) from emp;# Find the maximum data of the field salary mysql>: select max(salary) from emp;# Find the minimum data of the field salary mysql>: select min(salary) from emp;# Find the average data of the field salary mysql>: select avg(salary) from emp;# Sum the data of the field salary mysql>: select sum(salary) from emp;

Seven, grouping and filtering (group by | having)

7.1 Group by

To process multiple pieces of data uniformly, this method is called aggregation

Who are in each department, the highest salary, The lowest salary and average salary are called aggregation results-the results of aggregation function operations

Note: The fields participating in the grouping are also attributed to the aggregation results.

After grouping, the table The data considered in the middle is not a single record, because each group contains multiple records. Refer to the group field to process multiple records in each group in a unified manner.

# Modify the my.ini configuration and restart the mysql service sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION: There is no meaning under sql_UPLY_FULL_SELECT, and the result can be reported wrong. * from emp group by dep;# eg: group by department, query who are in each department, the highest salary, the lowest salary, average salary, how many people in the group mysql>: select dep department, group_concat(name ) Members, max(salary) maximum salary, min(salary) minimum salary, avg(salary) average salary, sum(salary) total salary, count(salary) number of departments from emp group by dep;# eg: group by department, query each The record with the oldest group age mysql>: select dep department, max(age) maximum age from emp group by dep;

7.2 the difference between where and having

• Without grouping, where and having the same result
• Key point: having can filter the aggregated results

< pre class="mysql"># No grouping to filter, no difference mysql>: select * from emp where salary> 5;mysql>: select * from emp having salary> 5;

7.3 having after grouping

• Key point: having can filter the aggregated results

# eg: group by department, query records in each department with the lowest salary less than 5 mysql>: select dep department, group_concat(name) member, max(salary) highest salary, min(salary) lowest salary, avg(salary) Average salary, count(salary) department number from emp group by dep having min(salary) <5;# having can filter the aggregation results again, where not

8. Order (order by)

• Ascending order (from low to high): asc (default ascending order)
• Descending order (from high to Low): desc

8.1 sort rule

order by sort field [asc|desc], sort field 1 [asc|desc], ..., sort field n [ asc|desc]

8.2 Under the ungrouped state

mysql>: select * from emp# Ascending order by age (from lowest To high) mysql>: select * from emp order by age asc;# Descending order by age (from high to low) mysql>: select * from emp order by age desc;

8.3 In the grouping state

# After grouping by department, the number of the department is in descending order (from high to low) mysql>: select dep department, group_concat(name) member, max(salary ) Maximum salary, min(salary) minimum salary, avg(salary) average salary, sum(salary) total salary, count(salary) number of departments from emp group by dep order by number of departments desc;# After grouping by department, sort departments The number of people is in ascending order (from low to high) mysql>: select dep department, group_concat(name) members, max(salary) maximum salary, min(salary) minimum salary, avg(salary) average salary, sum(salary) total salary, count(salary) number of department from emp group by dep order by number of department; # default ascending order

nine, limit limit

Syntax: limit number of entries | limit offset, number of entries

# Syntax: limit number of entries | limit offset, number of entries mysql>: select name, salary from emp where sa lary<8 order by salary desc limit 1;mysql>: select * from emp limit 5,3; # First offset 5 records that meet the conditions, and then query 3 records

10. Query with tables

• Connect: Connect multiple tables that are related to each other through an association (only if there is a connection, not necessarily a foreign key) field , The formal parameter is a large table
• Concatenated table query: Query on the basis of a large table is called a linked table query
• Combine the table with There are four ways to establish a connection: Internal connection, Left connection, Right connection, Full connection

Data preparation

mysql>: create table dep( id int primary key auto_increment, name varchar(16), work varchar(16));create table emp( id int primary key auto_increment, name varchar(16) , salary float, dep_id int); insert into dep values(1,'marketing department','sales'), (2,'teaching department','teaching'), (3,'management department','driving') ;insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2 ),('liujie', 8.0, 1),('yingjie', 1.2, 0);

Cartesian product

# Cartesian product: Set X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}mysql>: select * from emp, dep;# Summary: two table records All permutations and combinations of, the data has no use value

Inner join

• Keywords:inner join on (inner can be omitted)
• Syntax:from A table inner join B table on A table. Associated field=B table. Associated field

mysql>: select emp.id,emp.name,salary,dep.name,work from dep inner join emp on dep.id=emp.dep_id; # The inner of the inner connection can be omitted

Summary: Only keep the data related to the two tables

Left connection

• Keyword:left join on

• Syntax:< /strong>from table A left join table B on table A. Associated fields=Table B. Associated fields

mysql >: select emp.id,emp.name,salary,dep.name,work from emp left join dep on emp.dep_id = dep.id order by emp.id;

mysql>: select emp.i d,emp.name,salary,dep.name,work from emp right join dep on emp.dep_id = dep.id order by emp.id;

mysql>: select emp.id,emp.name,salary,dep.name,work from emp right join dep on emp.dep_id = dep.id order by emp.id;mysql>: select emp.id,emp .name,salary,dep.name,work from dep left join emp on emp.dep_id = dep.id order by emp.id;

Connect the left connection and the right connection through the keyword to realize the full connection

p>

Keyword:union

mysql>: select emp.id,emp.name,salary,dep .name,work from emp left join dep on emp.dep_id = dep.id unionselect emp.id,emp.name,salary,dep.name,work from emp right join dep on emp.dep_id = dep.id order by id; 

Summary: The data in the left table and the right table are all retained, and the corresponding relationship between each other is displayed normally, and there is no corresponding relationship between each other and empty fill each other

One-to-one and one-to-many situations are the same

create table author( id int, name varchar(64), detail_id int); create table author_detail( id int, phone varchar(11)); insert into author values(1,'Bob', 1), (2,'Tom', 2) , (3,'ruakei', 2);insert into author_detail values(1, '13344556677'), (2, '14466779988'), (3, '12344332255');select author.id,name,phone from author join author_detail on author.detail_id = author_detail.id order by author.id;select author.id,name,phone from author left join author_detail on author.detail_id = author_detail.idunionselect author.id,name,phone from author right join author_detail on author .detail_id = author_detail.idorder by id;

many-to-many

create table author( id int, name varchar(64) , detail_id int); insert into author values(1,'Bob', 1), (2,'Tom', 2), (3,'ruakei', 0);create table book( id int, name varchar(64 ), price decimal(5,2)); insert into book values(1,'python', 3.66), (2,'Linux', 2.66), (3,'Go', 4.66);create table author_book( id int, aut hor_id int, book_id int);# Data: author-book: 1-1,2 2-2,3 3-1,3 insert into author_book values(1,1,1),(2,1,2),(3 ,2,2),(4,2,3),(5,3,1),(6,3,3);# Many-to-many select book.name, book.price, author.name from book join author_book on book.id = author_book.book_id join author on author_book.author_id = author.id;# many-to-many-to-one select book.name, book.price, author.name, author_detail.phone from book join author_book on book.id = author_book. book_idjoin author on author_book.author_id = author.idleft join author_detail on author.detail_id = author_detail.id;

directory

• One, single-table query syntax and keyword execution Order
  • 1.1 Single-table query syntax
  • 1.2 Priority of keyword execution
• Second, simple query
  • 2.1 Data preparation
• 3. Deduplication (distinct)
• 4. Common functions
• 5. Constraints (where )
• Six, aggregate functions
• Seven, grouping and filtering (group by | having)
  • 7.1 Group by query (group by)
  • 7.2 The difference between where and having
  • 7.3 Having after grouping
• Eight, order (order by)
  • 8.1 Sorting rules< /li>
  • 8.2 in ungrouped state
  • 8.3 in grouped state
• 9. Limit
• 10. Table query
  • Cartesian product
  • Inner connection
  • Left connection
  • Right connection
  • Left and right connections can be mutually connected Conversion
  • Full connection
• One-to-one and one-to-many situations are the same
• Many to many