💖✨MySQL
This is the fifth 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.
☠️ You have to work very hard to believe that you really are powerless
👋 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. Equivalent connection of internal connection
1.1 The biggest characteristic is: the condition is equal.
1.2 Querying the department name of each employee. The employee name and department name must be displayed
SQL92: (too old to use)
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
Copy the codeSQL99: (common)
The inner can be left out to make it easier to read.
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
Copy the codeThis is the way to write without leaving out the inner,
select
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
Copy the code
Syntax format:
. A join B on where...Copy the codeSQL99 syntax is a little clearer: the join condition of the table is separated from the later WHERE condition.
2. Non-equivalent connection of internal connection
2.1 The biggest characteristic is that the relation in the connection condition is not equal.
2.2 Case: Find out the salary grade of each employee, and ask to display employee name, salary and salary grade.
Select e.name,e.sal, s.gallade from emp e join salgrade s on e.sal between s.sal and s.sal;Copy the code
3. Self-connection of internal connections
3.1 The biggest characteristics: a table as two tables. Connect yourself.
3.2 Find out the superior leader of each employee and ask to display the name of the employee and the corresponding leader
select empno,ename,mgr from emp;
EMP A table: employee table
EMP B table: Leadership table
| empno | ename |
|---|---|
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7902 | FORD |
Employee leader number = Employee number of the leader
Select a.ename as' employee name ', B.ename as' leader name 'from emp a inner join emp b on A.ename = b.ename;Copy the codeNote: This one has 13 pieces of data, because KING has no superior
4. Outside connection
4.1 What is the outer join and the difference between the inner join
4.2 Find out the supervisor of each employee (all employees should search out, KING should also search out)
EMP A table: employee table
EMP B table: Leadership table
| empno | ename |
|---|---|
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7902 | FORD |
The connection:
Select a.ename 'employee ', B.ename' leader 'from EMp a join emp b on A.ename = B.ename;Copy the codeExternal connection: (left external connection/left connection)
Select a.e.name, B.e.name from emp a left outer join emp b on A.e.gr = B.e.mpno; I can leave out the outerCopy the codeExternal connection :(right external connection/right connection)
Select a.ename, B.ename from EMp b right outer join EMp A on A.ename = B.ename; I can leave out the outerCopy the codeImportant: Left and right distinguish between a left table and a right table.
Note: this has 14 pieces of data, where KING has no parent and will automatically match NULL
The most important feature of an outer join is that the main data is unconditionally all queried out.
4.3 Case: Find out which department has no employees
select
d.*
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
e.sal is null;
Copy the code
5. Join three tables for query
5.1 Case: Find out the department name and salary grade of each employee
select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;
Copy the codeNote: The EMP table first joins with the DEPT table and then joins with the Salgrade table
5.2 Find out the department name, salary grade, and supervisor of each employee.
select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and hisal
left join
emp e1
on
e.mgr = e1.empno;
Copy the code
6. The subquery
6.1 What is a subquery
Select statements are nested within a select statement. A nested SELECT statement is a subquery.
6.2 Where can subqueries appear
select .. (slect). from .. (select). where .. (select).Copy the code6.3 Subqueries are used in the WHERE clause
Case study: Find out about employees with above-average salaries
// The group function cannot be used directly after where.
select * from emp where sal > avg(sal);
Step 1: Find out the average wage
select avg(sal) from emp;
Step 2: Filter where
Select sal from emp where sal > 2073.214286;
Step 1 and Step 2 merge:
select * from emp where sal > (select avg(sal) from emp);
6.4 Nested subqueries after from
Example: Find out the pay grade of the average salary in each department.
Step 1: Find the average salary of each department (group by department number and find the average of Sal)
select deptno,avg(sal) as avgsal from emp group by deptno;
Step 2: Make a temporary table t and join the osal and salgrade table, when: t.avirgsal between s.losal and s.sal;
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
Copy the code
6.5 Nesting subqueries after SELECT
Example: Find out the name of each employee’s department and ask to show the employee name and department name
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
7. Union (you can add the query result sets)
Case: Find out the employees who work for SALESMAN and MANAGER
The first: or
select ename,job from emp where job = ‘MANAGER’ or ‘SALESMAN’;
Second: in
select ename,job from emp where job in(‘MANAGER’,’SALESMAN’);
Third: Union
select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = 'SALESMAN';
Copy the code
Note: Data from two unrelated tables is displayed together.
select ename from emp
union
select dname from dept;
Copy the code
8. Limit (generic paging/paging query)
8.1 limit (most important of all)
8.2 limit is unique to mysql. Other limits are not available in the database. (Oracle has a similar mechanism called rownum.)
8.3 LIMIT takes part of the data in the result. This is what it does.
8.4 Syntax mechanism:
limit startIndex,length
Copy the codeNote:
- StartIndex is the starting position? It starts at 0. 0 is the first data.
- Length is how many.
8.4 Case: Take out the top 5 employees in salary (idea: take out the top 5 in descending order)
Step 1: Rank wages in descending order
select ename,sal from emp order by sal desc ;
Step 2: Take out the top 5 employees
select ename.sal from emp order by sal desc limit 0 , 5;
**8.5 limit is the last step of SQL statement execution: **
select 5 ... from 1 ... where 2 ... group by 3 ... having 4 ... order by 6 ... limit 7 ... ;Copy the code8.6 Cases: Find out the employees who rank fourth to ninth in salary
Select ename,sal from emp order by sal desc limit 3,6;
8.7 Common standard paging SQL
Display 3 records per page:
Page 1:0, 3
Page 2:3, 3
Page 3:6
Page 4:9, 3
Page 5:12, 3
Pagesize = pagesize = page
Page: (pageNO – 1) * pagesize, pagesize
What is pagesize? Is how many records to display per page.
What is pageNo? Display page number.
int pageNo = 2; // Page 2
int pagesize = 10; // Display 10 items per page
limit 10.10;
Copy the codeToday 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 💪!!
