MySQL learning notes

DQL data query language

Simple query (query a single field)

select Field name from Table name;
//Field name and table name are identifiers, and select and from are keywords
//Stress: it is common for sql statements. sql is not case sensitive, and ';' ending
//		Chinese aliases need to be enclosed in quotation marks

Query multiple fields

//Separated by commas in English
select Field name,Field name from Table name;

Query all fields

You can write every field
```
select a,b,c,d,e,f... from Table name;
```
You can use '*'
```
select * from Table name;
```
Disadvantages: (first convert '*' into field name, and then query according to the first method)
1. Low efficiency
2. Poor readability
Not recommended in actual development.
Query annual salary
```
select ename, sal*12 from emp;
```
Conclusion: fields can participate in addition, subtraction, multiplication and division
Alias when querying (use 'as')

select deptno, dname as deptname from dept;
//'as' can be omitted. Note: there can be no spaces between aliases

select deptno, dname deptname from dept;
//You can use quotation marks ('' or '') if you have to add a 'space' to the alias

select deptno,dname 'dept name' from dept;
//Note: in all databases, strings are enclosed in single quotation marks. Single quotation marks are standard, and double quotation marks are not used in oracle database. But it can be used in mysql

be careful:

Only the column name of the displayed query result is displayed as deptname, and the original column name is still called: dname without changing
In all databases, strings are enclosed in single quotation marks. Single quotation marks are standard, and double quotation marks are not used in oracle database. But it can be used in mysql

Remember:

The select statement will never be modified. (because it is only responsible for query)

Condition query

What is a conditional query
- Not all the data in the table can be found. It is used to query qualified.

Syntax format

select 
	Field 1,Field 2,Field 3...
from
	Table name
where
	condition;

What are the conditions

// =Equals
// Query employees whose salary is equal to 800
select empno,ename from emp where sal = 800;

// Query an employee named smith
select empno,ename,sal from emp where ename = 'smith';

// < > or= Not equal to
// Query employees whose salary is not equal to 800
select empno,ename from emp where sal != 800;
select empno,ename from emp where sal <> 800;

// < less than
// Query employees whose salary is less than 2000
select empno,ename from emp where sal < 2000;

// < = less than or equal to
// Query employees whose salary is less than or equal to 2000
select empno,ename from emp where sal <= 2000;

// >Greater than
// Query employees whose salary is greater than 2000
select empno,ename from emp where sal > 2000;

// >=Greater than or equal to
// Query employees whose salary is greater than or equal to 2000
select empno,ename from emp where sal >= 2000;
// Between... And... Is equal to > = and<=

// Query employee information with salary between 2450 and 3000? Including 2450 and 3000
select empno,ename,sal from emp where sal between 2450 and 3000;
select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;

// is null or empty
// Query employee information with blank allowance
select empno,ename,sal,comm from emp where comm is null;

// and also
//Query the information of employees whose position is manager and whose salary is greater than 2500
select empno,ename,job,sal from emp where job = 'manager' and sal > 2500;

// Or or
//Query employees whose jobs are manager and salesman
select empno,ename,job from emp where job = 'manager' or job = 'salesman';

//Query employees whose salary is greater than 2500 and department number is 10 or 20
select deptno,ename,sal from emp where sal > 2500 and (deptno = 10 or deptno = 20);

// In contains, which is equivalent to multiple or (not in is not in this range)
//Employees of manager and Salman when querying jobs
select empno,ename,job from emp where job = 'manager' or job = 'salesman';
select empno,ename,job from emp where job in('manager','salesman');

//Query employee information with salary equal to 800 and 5000
select empno,ename,job,sal from emp where sal in(800,5000);

// like is called fuzzy query and supports% or underscore matching
// %Match any character
// Underline, an underline matches only one character
//(% is a special character, is also a special character)
// Find the one with o in the name?
select ename from emp where ename like '%o%';
// Find the one that ends with t in the name
select ename from emp where ename like '%t';
// Find the name that begins with k
select ename from emp where ename like 'k%';
// Find out that the second letter in the name is a
select ename from emp where ename like '_a%';
// Find out that the third letter in the name is r
select ename from emp where ename like '__r%'
// Find the one with "" in the name
// \Escape character
select name from t_student where name like '%\_%';

be careful:

When using between...and... Follow the principle of small left and large right
between..and... Is a closed interval
Null cannot be measured with an equal sign in the database. is null is required. Because null in the database represents nothing, it is not a value, so it cannot be measured by the equal sign.
When and and or appear at the same time, and has higher priority. If you want or to go first, you need to add parentheses
If you are not sure about the priority, you can add parentheses
in is not an interval. in is followed by a specific value
Find the data with "" in the name. You can use the escape character ''

sort

// Query all employee salaries, sort
select ename,sal from emp order by sal;

// Query all employee salaries and specify descending order
select ename,sal from emp order by sal desc;

// Query all employee salaries and specify ascending order
select ename,sal from emp order by sal asc;

// Query employee name and salary in ascending order. If the salary is the same, it will be sorted in ascending order by name
select ename,sal from emp
order by 
	sal asc, ename asc;//sal is in the front and plays a leading role
	
//You can also sort according to the position of the field
//Sort by sal in column 2 of query results
select ename,sal from emp order by 2;

//Find out the information of employees whose salary is between 1250 and 3000, and arrange them in descending order
//Execution order from -- > where -- > select -- > order by
select 
	ename,sal
from 
	emp 
where 
	sal between 1250 and 3000 
order by 
	sal desc;

Sort, ascending by default
It is not recommended to sort according to the position of fields in development, because it is not robust and the order of columns is easy to change

Group query (very important)

What is a query in a group

In practical applications, there may be such a requirement that you need to group first, and then operate on each group of data. At this time, you need to group query

//Combine all the previous keywords together
select
	...
from
	...
where
	...
group by
	...
order by
//Execution order from - > where - > group by - > Select - > order by

//Find out the salary and salary of each job
//First, query the data from the emp table, group it according to the job field, and then sum(sal) each group of data
select sum(sal) from emp group by job;
//Find out the highest salary for each department
//Group according to the department number and find the maximum value of each group
select deptno,max(sal) from emp group by deptno;
//Find out the highest salary for different positions in each department
select job,deptno,max(sal) from emp group by deptno, job;

//Find out the highest salary of each department and display the information with the highest salary greater than 3000
//Step 1: find out the highest salary for each department
select deptno,max(sal) from emp group by deptno;
//Step 2: it is required to display the maximum salary greater than 3000
// having can further filter the data after grouping
select deptno,max(sal) from emp group by deptno having max(sal) > 3000;
select deptno,max(sal) from emp where sal > 3000 group by deptno;
//Find out if the average salary of each department is higher than 2500
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;

select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
 
//Find out the average salary of each position. It is required to display the average salary greater than 1500. Except for the manager position, it is required to shoot according to the descending order of the average salary
select 
	job, avg(sal) as acgsal
from 
	emp
where
	job <> 'MANGER'
group by
	job
having
	avg(sal) > 1500
order by
	avgsal desc;

explain:

Grouping functions cannot be used directly after where

Because the grouping function can only be used after grouping.

When where is executed, there is no grouping, so the grouping function cannot appear after where
In a select statement, if there is a group by statement,

select can only be followed by fields participating in grouping and grouping functions

No one else can follow
having cannot be used alone. having cannot replace where. having must be used in conjunction with group by
If you can use where to filter out, try to use it
Where and having, choose where first, then having
Execution sequence:
1. from
2. where
3. group by
4. having
Query data from a table,

First filter out valuable data through where conditions

Group these valuable data

After grouping, you can use having to continue filtering

select to query.

Last sort output

Connection query (most important)

What is a join query
- Query from a single table is called single table query
- The emp table and the dept table are combined to query data. The employee name is taken from the emp table and the Department name is taken from the dept table. This cross table query, in which multiple tables join to query data, is called join query.
When two tables are connected without any restrictions, the number of final query results is the product of the number of two tables. This phenomenon is called Cartesian product phenomenon
How to avoid Cartesian product?
- Conditions are added during connection, and records that meet these conditions are filtered out
```
select ename,dname from emp,dept where emp.deptno = dept.deptno;
```
The number of final query results is 14, but during the matching process, the number of matches is not reduced, but one of four is selected

be careful:
- Through the Cartesian product phenomenon, it is concluded that the more the connection times of the table, the lower the efficiency, and try to avoid the Cartesian product phenomenon

Classification of join queries

According to the chronological classification of grammar;

SQL92: syntax that appeared in 1992
SQL99: syntax in 1999

Classification according to the connection mode of the table:

Internal connection (A and B are connected, and AB has no primary and secondary relationship)

Features: complete the data query that can match this condition
Equivalent connection
- The condition is equal quantity relationship, so it is equivalent connection
```
// Query the Department name of each employee, and display the employee name and department name
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;//(syntax of SQL92)
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
```
explain:
- SQL92 disadvantages: the results are not clear. The connection conditions of the table and the conditions for further screening in the later stage are placed after where.
- Advantages of SQL99: the conditions of table connection are independent. After connection, if further filtering is needed, continue to add the where conditions later
```
select
	...
from
	a
inner join
//Inner can be omitted. With better readability, inner is an inner connection
	b
on
	a and b Connection conditions
where
	Screening conditions
```

Non equivalent connection

The condition is not an equal quantity relationship, which is called non equivalent connection

//Find out the salary registration of each employee and display the employee name and salary grade
select
	e.ename, e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

Self connection

Tip: think of one table as two

// Query the superior leader of an employee. The employee name and corresponding leader name are required to be displayed
// Skill: look at two tables with one table
select
	a.ename as 'Employee name', b.ename as 'Leader name'
from
	emp a
join
	emp b
on
	a.mgr = b.empno;//Employee's leader number = leader's employee number

External connection
In the external connection, two tables are connected, resulting in a primary secondary relationship
- Right outer connection (right connection)
```
select
	e.ename,d.dname
from 
	emp e right outer join dept d
on
	e.deptno = d.deptno;
```
  - Right means that the table on the right side of the join keyword is regarded as the main table, which is mainly used to query all the data of this table, along with the table on the left side of the associated query
- Left outer connection (left connection)
```
select
	e.ename,d.dname
from
	emp e left outer join dept d
on
	e.deptno = d.deptno;
```
  - Any right connection has a left connection
  - Any left connection has a right connection
  - outer can be omitted with strong readability
- explain:
  - The number of query results of external connection must be > = the number of query results of internal connection
```
//Query the superior leader of each employee and display the names of all employees and leaders
select
	a.ename as 'Employee name',b.ename as 'Leader name'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;
```
Full connection (few)

How to connect three tables and four tables?

Syntax:

select
	...
from
	a
join
	b
on
	a and b Connection conditions
join
	c
on
	a and c Connection conditions
join
	d
on
	a and d Connection conditions

Internal and external connections in an SQL can be mixed. Can appear!

//Find out the Department name and salary grade of each employee. It is required to display employee name, salary, department name and salary grade
select
	e.ename,e.sal,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;
 
//Find out the Department name and salary grade of each employee, as well as the superior leaders. It is required to display the employee name, leader name, department name, salary and salary grade
select
	a.ename as 'Employee name',b.ename as 'Leader name',d.dname,
	a.sal,s.grade
from
	emp as a
left join
	emp as b
on
	a.mgr = b.empno
join
	dept as d
on
	a.deptno = d.deptno
join
	salgrade as s
on
	a.sal between s.losal and s.hisal
order by
	s.grade asc, a.sal desc;

Subquery

Select statements are nested in select statements. Nested select statements are called subqueries.

Occurrence position

select
	...(select)
from
	...(select)
where
	...(select)

Subquery in where clause

//Find out the names and wages of employees who are higher than the minimum wage
//Step 1: query the minimum wage
select min(sal) from emp;
//Step 2: find > 800
select ename,sal from where sal > 800;
//Step 3: Merge
select ename,sal from emp where sal > (select min(sal) from emp);

Subquery in from clause

//Find out the salary grade of the average salary of each position
//Step 1: find out the average salary of each position (average by position grouping)
select job,avg(sal) from emp group by job;
//Step 2: overcome psychological barriers and treat the above query results as a real table t
select 
	t.*,s.grade
from
	t
join
	salgrade as s
on
	t.avg(sal) between s.losal and s.hisal;
//Step 3:
select 
	t.*,s.grade
from
	(select job,avg(sal) as avgsal from emp group by job) t
join
	salgrade as s
on
	t.avgsal between s.losal and s.hisal;

Note: for the sub query after from, you can treat the query result of the sub query as a temporary table (technique)

Subquery after select

//Find out the Department name of each employee and display 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;

be careful:

For the sub query after select, this sub query can only return one result. If there is more than one result, an error will be reported

union merge query result set
- The number of columns in the two result sets is required to be the same
- Union: get the union of two query results, and automatically remove duplicate rows. No sorting
- union all: get the union of two query results without removing duplicate rows. No sorting
- intersect: get the intersection of two query results, and sort according to the first column of the result set
- minus: get the subtracted set of the two query results and sort by the first column
- oracle requires that the data types of columns and columns should be the same when merging result sets
```
//Query employees whose jobs are mananager and salesman
//Method 1:
select ename,job from emp where job = 'manager' or job = 'salesman';
//Method 2:
select ename,job from emp where job in('manager', 'salesman');
//Method 2:
select ename,job from emp where job = 'manager'
union
select ename,job from emp where job = 'salesman';
```
- union is more efficient. For table connection, every time a new table is connected, the number of matches satisfies Cartesian product and doubles...
- But union can reduce the number of matches. In the case of reducing the number of matches, the splicing of two result sets can also be completed
- union turns multiplication into addition
limit get some data
1. Full usage: limit startIndex, length
  - startIndex is the starting subscript and length is the length
  - Start subscript from 0
2. Default usage: limit 5// This is the first five
Part of the query result set is taken out, which is usually used in paging queries.

Paging is used to improve the user experience, because it is found all at once, and the user experience is poor,

You can turn pages page by page.
```
//Take out the top 5 employees in descending salary order
select
	ename,sal
from
	emp
order by
	sal desc
limit 5;
//Take out employees whose wages are between 3 and 5
select ename,sal from emp order by sal desc limit 2,3;
//Take out the employees with salary ranking in [5-9]
select ename,sal from emp order by sal desc limit 4, 5;
```
be careful:
- In mysal, limit is executed after order by!!!!!
paging

3 records per page

Page 1: limit 0,3

Page 2: limit 3,3

Page 3: limit 6,3

Page 4: limit 9,3

pageSize records per page

Page pageNo: limit (pageNo - 1) * PageSize, PageSize

//DQL statement summary
select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...
//Execution sequence
//1.from
//2.where
//3.group by
//4.having
//5.select
//6.order by
//7.limit..

Posted by sebjlan on Sun, 31 Oct 2021 20:13:45 -0700

Programmer Group

MySQL learning notes

DQL data query language

Simple query (query a single field)

Query multiple fields

Query all fields

Condition query

sort

Group query (very important)

Connection query (most important)

Hot Keywords