💖✨MySQL

This is the second day of my participation in the August More text Challenge. For details, see:August is more challenging.

👨🎓 By The Java Academic Party

🏦 repositories: Github, Gitee

✏️ blogs: CSDN, Nuggets, InfoQ, Cloud + Community

💌 Public account: Java Academic Party

🚫 special statement: the original is not easy, shall not be reproduced without authorization or plagiarism, if need to be reproduced can contact xiaobian authorization.

🙏 Copyright notice: some of the text or pictures in this article are from the Internet and Baidu Encyclopedia. If there is any infringement, please contact xiaobian as soon as possible. Wechat search public account Java Academic party contact xiaobian.

☠️ The beauty of a flower is that it once withered.

👋 Hello everyone! I am your old friend Java Academic party, today continue to share with you xiaobian carefully for you to sort out 10,000 words MySQL database core knowledge, in the next few days for you to continue to share, 💘 attention don’t get lost!! 💘. A database is a warehouse where data is stored. It has a lot of storage space for millions, tens of millions, hundreds of millions of pieces of data. But the database is not to store the data randomly, there are certain rules, otherwise the efficiency of the query will be very low. A database is a computer software system that stores and manages data in a data structure. 🦄

1. Sort (ascending, descending)

Syntax format:

Select value 1, value 2... From table name order by field value;Copy the code

1.1 Find out the names and salaries of employees in ascending order of salary

select ename,sal from emp order by sal; (This is the default, default ascending)

Note: The default is ascending. How do I specify ascending or descending? Asc indicates the ascending order and desc indicates the descending order.

select ename,sal from emp order by sal; (Default ascending)

select ename,sal from emp order by sal asc;

select ename,sal from emp order by sal desc;

1.2 In descending order of wages, and in ascending order of names when salaries are the same.

select ename,sal from emp order by sal desc,ename asc;

Note: The more advanced the field, the more dominant it is. Later fields are enabled only if the current field cannot be sorted.

Eyes:

select ename,sal from emp order by 1;

select ename,sal from emp order by 2;

select * from emp order by 6;

1.3 Find out the jobs of SALESMAN employees and ask them to be ranked in descending order of salary

 select
     ename,job,sal
 from
    emp
 where
    job = 'SALESMAN'
 order by 
    sal desc;
Copy the code

Execution order: execute from first, then where, then select, then order by.

Select * from tablename where (select * from tablename) where (select * from tablename) order by... 4Copy the code

Order by is executed last.

2. Grouping functions

2.1 Grouping function/aggregate function/multi-line processing function

  • Count count
  • The sum sum
  • Avg average
  • Max maximum
  • Min min

Remember: all grouping functions operate on a “set” of data.

2.2 Find out the sum of wages?

select sum(sal) from emp;

2.3 Find the highest salary

select max(sal) from emp;

2.4 Find out the minimum wage

select min(sal) from emp;

2.5 Find the average wage

select avg(sal) from emp;

2.6 Find the total number of people

select count(*) from emp;

select count(ename) from emp;

There are five grouping functions. Grouping functions have another name: multiline handlers. The characteristic of multi-line processing function: input multiple lines, the final output result is 1 line.

2.7 Grouping functions automatically ignore NULL.

select count(comm) from emp;

select sun(comm) from emp;

Unnecessary: There is no need to add this extra filter, and the grouping function automatically ignores null

select sum(comm) from emp where comm is not null;

2.8 Find employees whose salaries are above the average

Step 1: Find out the average wage

select avg(sal) from emp;

Step 2: Find employees with above-average salaries

select ename,sal from emp where sal > (select avg(sal) from emp);

Error message: Invalid grouping function used.

**SQL has a syntax rule that grouping functions cannot be used directly in the WHERE clause.

Because group by is executed after where is executed.

 select  5
 ...
 from    1
 ... 
 where   2
 ...
 group by  3
 ...
 having   4  
 ...
 order by  6
 ...
Copy the code

3. What is the difference between count(*) and count(specific field)?

  • Count (*) : Indicates the total number of records, not the number of data in a certain field. (Independent of a field)

  • Count (comm) : indicates the total number of non-null data in the COMM field.

    select count(*) from emp;

select count(comm) from emp;

select count(job) from emp;

4. Grouping functions can also be combined

select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

Today to share here, tomorrow to continue to share with you, pay attention to do not get lost, we see tomorrow 😊.

Source code for the above project,Click on the planet 🌍 to get it for freeplanet(making address)If you don’t have your Github buddies. You can search 🔍 wechat official account:Java academic lie prone, 📭 send MySQL, free to send to everyone project source code, code is tested by xiaobian 🔧, absolutely reliable, free to use.

——–💘 after watching the big guys can pay attention to xiaobian, will always update tips, free to share with you!! 💝 — — — — — — — — —

Click 🌍 for quick access to Github planet!! There are more fun technology inside, waiting for you to explore yo 💪!!