💖✨MySQL

This is the fourth 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.

☠️ Daily toxic chicken soup: The earliest chicken soup for the soul, such as job in the Bible.

👋 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. Single-line processor

1.1 What is a single-line processor?

  • One line in, one line out.

1.2 Calculate the annual salary of each employee

select ename,(sal+comm) * 12 as yearsal from emp; // Error writing

Use the ifnull function to handle:

select ename,(sal + ifnull(comm,0)) * 12 as yearsal from emp; // Write correctly

Key: all databases are so specified, as long as there is NULL participation of the result of the operation must be NULL.

1.3 Ifnull () null handler function?

Syntax format:

Ifnull (data that may be NULL, what is treated as) : belongs to the single-row processing function.

1.4 Convert NULL in allowances to 0

select ename,ifnull(comm,0) as comm from emp;

2. Query group by and having

  • Group by: group by a field or certain fields.
  • Having: Filters the grouped fields again.

2.1 Find out the highest salary for each position

select max(sal) from emp group by job;

Note:

  • Group functions are usually used in conjunction with group by. That’s why it’s called a grouping function.
  • And any grouping function (count,sum, Max,min,avg) is executed after the group by statement has finished.
  • When an SQL statement does not have group by, the entire table is grouped by itself.

2.2 Grouping Multiple Fields

select ename,max(sal),job from emp group by job;

/ / in mysql above, the query result is some, but the result is meaningless, complains in the Oracle database, syntax errors.

Note: Remember the rule that when a statement contains group by, select can only be followed by the group function and the field participating in the group.

2.3 Average salary per job

select obj,avg(sal) from emp group by job;

2.4 Combine multiple fields into a group to find the highest salaries for different jobs in each department.

select deptno,job,max(sal) from emp group by deptno,job;

2.5 Find out the highest salary in each department and ask to show the data of salary greater than 2900

Step 1: Find out the top salary in each department

select deptno,max(sal) from emp group by deptno;

Step 2: Find out if the salary is greater than 2,900

select deptno,max(sal) from emp group by deptno having max(sal) > 2900;

Perfect solution: more efficient and require data with salary greater than 2900.

select deptno,max(sal) from emp where sal > 2900 group by deptno;

2.6 Find out the average salary for each department

Step 1: Find out the average salary for each department

select deptno,avg(sal) from emp group by deptno;

Step 2: Ask for data showing salaries greater than 2000

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

// Error, where the grouping function cannot be used. In this case, use the having filter only

select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno; / / error

3. How to summarize a complete DQL statement

  select
  ...
  from
  ...
  where
  ...
  group by
  ...
  having
  ...
  order by
  ... 
Copy the code

4. Connect query

4.1 De-duplicating query Results (the keyword distinct is used to remove duplicate records)

select distinct job from emp;

4.2 The following statement is incorrect

select ename,distinct job from emp;

Important: DISTINCT can only appear at the beginning of all fields.

4.3 Joint Deweighting of Multiple Fields

select distinct deptno,job from emp;

4.4 Count the number of posts

select count(distinct job) from emp;

5. The concept of linked queries

  • In the actual development, most of the cases are not from a single table query data, is generally multiple tables joint query to get the final result.
  • In the actual development, generally a business will correspond to multiple tables, such as: students and classes, at least two tables.

If students and class information are stored in the same table, the result will be like above, and there will be a lot of duplication of data, resulting in data overload.

5.1 Classification of connection queries

5.1.1 According to the age of grammar, it includes:

  • SQL92 (some older DBAs may still use this syntax. DataBase Administrator (DBA)
  • SQL99 (relatively new syntax)

5.1.2 Tables are divided according to the joining mode, including:

  • The connection:
    1. Contour connection
    2. Nonequivalent connection
    3. Since the connection
  • Outer join:
    1. Left outer connection (left connection)
    2. Right outer connection (right connection)
  • Full connection (this is not used very often!!)

5.1.3 Join query has a phenomenon: Cartesian product phenomenon.

5.2 Case: Find out the department name of each employee and ask to display the employee name and department name.

select ename.deptno from emp;

The EMP table:

select deptno,dname from dept;

The DEPT table:

The ename and Dname should be displayed together and glued together.

select ename,dname from emp,dept;

There are 14 * 4 rows of data, with four department names for each name

Principle: Two tables are queried and joined using deptno

Cartesian product phenomenon: when two tables are joined in a query, there is no restriction on any condition, the final query result is the product of the number of entries in the two tables.

6. On table aliases:

select e.ename,d.dname from emp e,dept d; (As is omitted here)

6.1 What are the benefits of table aliases:

  • High execution efficiency

  • Read well

  • How do you avoid the Cartesian product? Filter by adding conditions, of course

  • Does avoiding cartesian product reduce the number of matches recorded? 1. No, the number of times is still 56, but they are all valid records.

6.2 Find out the department name of each employee. The employee name and department name must be displayed.

Select e.name,dname from EMp e,dept D where e.deptno = D.deptnoCopy the code

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 💪!!