Mysql > query (conditional query, subquery, fuzzy query, join query...)

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

This article also participated in the “Digitalstar Project” to win a creative gift package and creative incentive money

Code shrimp is a sand carving and funny boy who likes listening to music, playing games and writing as well as most of his friends. The days are still very long, let’s refuel our efforts together 🌈

If you feel well written, ball ball a concern oh 😉

Create databases and tables

dept.sql

Department of table

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `DEPTNO` int(2) NOT NULL,
  `DNAME` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `LOC` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL.PRIMARY KEY (`DEPTNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10.'ACCOUNTING'.'NEW YORK');
INSERT INTO `dept` VALUES (20.'RESEARCH'.'DALLAS');
INSERT INTO `dept` VALUES (30.'SALES'.'CHICAGO');
INSERT INTO `dept` VALUES (40.'OPERATIONS'.'BOSTON');

SET FOREIGN_KEY_CHECKS = 1;

Copy the code

emp.sql

The employee table

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `JOB` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `MGR` int(4) NULL DEFAULT NULL,
  `HIREDATE` date NULL DEFAULT NULL,
  `SAL` double(7.2) NULL DEFAULT NULL,
  `COMM` double(7.2) NULL DEFAULT NULL,
  `DEPTNO` int(2) NULL DEFAULT NULL.PRIMARY KEY (`EMPNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (7369.'SMITH'.'CLERK'.7902.'1980-12-17'.800.00.NULL.20);
INSERT INTO `emp` VALUES (7499.'ALLEN'.'SALESMAN'.7698.'1981-02-20'.1600.00.300.00.30);
INSERT INTO `emp` VALUES (7521.'WARD'.'SALESMAN'.7698.'1981-02-22'.1250.00.500.00.30);
INSERT INTO `emp` VALUES (7566.'JONES'.'MANAGER'.7839.'1981-04-02'.2975.00.NULL.20);
INSERT INTO `emp` VALUES (7654.'MARTIN'.'SALESMAN'.7698.'1981-09-28'.1250.00.1400.00.30);
INSERT INTO `emp` VALUES (7698.'BLAKE'.'MANAGER'.7839.'1981-05-01'.2850.00.NULL.30);
INSERT INTO `emp` VALUES (7782.'CLARK'.'MANAGER'.7839.'1981-06-09'.2450.00.NULL.10);
INSERT INTO `emp` VALUES (7788.'SCOTT'.'ANALYST'.7566.'1987-04-19'.3000.00.NULL.20);
INSERT INTO `emp` VALUES (7839.'KING'.'PRESIDENT'.NULL.'1981-11-17'.5000.00.NULL.10);
INSERT INTO `emp` VALUES (7844.'TURNER'.'SALESMAN'.7698.'1981-09-08'.1500.00.0.00.30);
INSERT INTO `emp` VALUES (7876.'ADAMS'.'CLERK'.7788.'1987-05-23'.1100.00.NULL.20);
INSERT INTO `emp` VALUES (7900.'JAMES'.'CLERK'.7698.'1981-12-03'.950.00.NULL.30);
INSERT INTO `emp` VALUES (7902.'FORD'.'ANALYST'.7566.'1981-12-03'.3000.00.NULL.20);
INSERT INTO `emp` VALUES (7934.'MILLER'.'CLERK'.7782.'1982-01-23'.1300.00.NULL.10);

SET FOREIGN_KEY_CHECKS = 1;

Copy the code

salgrade.sql

Classification table

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade`  (
  `GRADE` int(11) NULL DEFAULT NULL,
  `LOSAL` int(11) NULL DEFAULT NULL,
  `HISAL` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES (1.700.1200);
INSERT INTO `salgrade` VALUES (2.1201.1400);
INSERT INTO `salgrade` VALUES (3.1401.2000);
INSERT INTO `salgrade` VALUES (4.2001.3000);
INSERT INTO `salgrade` VALUES (5.3001.9999);

SET FOREIGN_KEY_CHECKS = 1;

Copy the code

Two, simple query

Syntax: ==select field 1, field 2, field 3,…… From the name of the table; = =

Query the salary of each employee

SELECT ename,sal from emp;
Copy the code

Alias the query column

SELECT ename,sal as 'wages' from emp;
Copy the code

Note: Strings should be enclosed in single quotation marks, not double quotation marks

Three, condition query

A conditional query requires a WHERE statement, which must be followed by a FROM statement.

== The syntax is as follows ==

selectField, field...fromThe name of the tablewhereConditions;Copy the code

== Conditional queries support the following operator ==

The operator	instructions
=	Is equal to the
< > and! =	Is not equal to
<	Less than
>	Is greater than
< =	Less than or equal to
> =	Greater than or equal to
between … and	Between the two values,Equals >= and <=
is null	null
and	and
or	or
in	Include, equivalent to more than one or(not in)
not	Not can take not, mainly in is or in
like	Like is called a fuzzy query and supports % or underscore matching

== Query the name of the employee whose salary is 5000 ==

select 
	ename,sal 
from 
	emp 
where 
	sal = 5000;
Copy the code

== Query the name of the employee whose salary is less than 3000

select 
	ename,sal 
from 
	emp 
where 
	sal < 3000;
Copy the code

For the rest, refer to the previous table. I’m not going to do too much here.

Query between and

Between and must be smaller on the left and larger on the right

select 
	ename,sal 
from 
	emp 
where 
	sal between 2000 and 3000;
Copy the code

Between and can also be used in strings (left is closed, right is open)

select 
	ename 
from 
	emp 
where 
	ename between 'A' and 'C';
Copy the code

Conditional query is NULL, is not NULL

In a database, NULL is not a value. It means nothing. Empty is not a value and cannot be identified by an equal sign. You must use is NULL or IS not NULL to determine the value.

Query the data whose COMM is NULL

select 
	ename,comm 
from 
	emp 
where 
	comm is null;
Copy the code

Query comm for data that is not NULL

select 
	ename,comm 
from 
	emp 
where 
	comm is not null;
Copy the code

Conditional query in

select 
	ename,sal 
from 
	emp 
where 
	sal in(3000.5000);
Copy the code

In: data with sal values 3000 and 5000

select 
	ename,sal 
from 
	emp 
where 
	sal not in(3000.5000);
Copy the code

Not in: sal is not in (3000,5000)

Four, fuzzy query

== uses == with %

% represents a wildcard for one or more characters

(1)

select 
	ename,sal 
from 
	emp 
where 
	ename like '%S%';
Copy the code

ename like ‘%S%’; The name with an S in the name

(2)

select 
	ename,sal 
from 
	emp 
where 
	ename like 'S%';
Copy the code

Query data whose name starts with S

(3)

select 
	ename,sal 
from 
	emp 
where 
	ename like '%S';
Copy the code

Data whose name ends with S

== with _ use ==

_ represents a wildcard character of only one character

select 
	ename,sal 
from 
	emp 
where 
	ename like 'KIN_';
Copy the code

Five, group query

Group by: Group by a field or fields having: Having filters the grouped data again.

select 
	AVG(sal),deptno 
from 
	emp 
group by 
	deptno;
Copy the code

select 
	deptno 
from 
	emp 
group by 
	deptno 
having 
	deptno > = 20;
Copy the code

== Precautions ==

GROUP BY cannot be followed BY a column alias
A number cannot be followed BY GROUP BY
GROUP BY cannot be followed BY column names that do not exist after SELECT
If grouping functions are used in a SELECT, any columns (expressions) that are not in the grouping function must be in GROUP BY

Six, connection query

In the use of database query statement, sometimes the single table query can not meet the business requirements of the project, in the process of project development, there are many requirements are related to the multi-table connection query

== Join query category ==

In the connection
- Contour connection
- Unequal connection
- Since the connection
Outer join
- Left external connection (left connection)
- Right outer connection (right outer connection)
All connection

In the connection

Contour connection

Characteristic: condition is equal quantity relation

Query the department name of each employee, asking to display the employee name and department name

select 
	e.ename,d.dname 
from 
	emp e 
join 
	dept d
on 
	e.deptno = d.deptno;
Copy the code

Unequal connection

Characteristic: The connection condition is not equal quantity relation

Find out the salary grade of each employee and ask to display the employee’s name, salary 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;
Copy the code

Since the connection

Features: One table as two tables. Connect yourself

Find the supervisor of each employee and ask for the employee name and the corresponding supervisor name

select 
	e.ename,m.ename
from 
	emp e
left join 
	emp m 
on 
	e.mgr = m.empno;
Copy the code

Outer join

If A and B are joined, and the outer join is used, one of the two tables AB is the primary table and the other is the secondary table, the primary table is queried, with incidental query of the secondary table. When the data in the secondary table does not match the data in the primary table, the secondary table automatically simulates NULL to match it.

Outer connection classification

Left outer join (left join) : Indicates that the table on the left is the primary table.
Right outer join (right join) : indicates that the table on the right is the primary table.

Basic syntax left tableleft/right joinTo the right tableonLeft table. Field=Right table. FieldCopy the code

Seven, sub-query

==1, where subquery ==

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

Select * from (select * from (select * from (select * from (select * from)))

List the minimum wage for each job and the name of the employee performing the job

select 
	e.ename,t.* 
from 
	emp e 
join 
	(select job,min(sal) as minsal from emp group by job) t 
on 
	e.job=t.job and e.sal=t.minsal;
Copy the code

Select * from (select * from (select * from (select * from (select * from)))

Example Query the department name of each employee

select 
	e.ename,d.dname,
	(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;
Copy the code

💖 finally

I am aCode pipi shrimp, a prawns lover who loves to share knowledge, will update useful blog posts in the future, looking forward to your attention!!

Creation is not easy, if this blog is helpful to you, I hope you can key three even oh! Thank you for your support. See you next time

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Mysql > query (conditional query, subquery, fuzzy query, join query…)

Create databases and tables

Two, simple query

Three, condition query

Query between and

Conditional query is NULL, is not NULL

Conditional query in

Four, fuzzy query

Five, group query

Six, connection query

In the connection

Contour connection

Unequal connection

Since the connection

Outer join

Seven, sub-query

💖 finally

Mysql > query (conditional query, subquery, fuzzy query, join query…)

Create databases and tables

Two, simple query

Three, condition query

Query between and

Conditional query is NULL, is not NULL

Conditional query in

Four, fuzzy query

Five, group query

Six, connection query

In the connection

Contour connection

Unequal connection

Since the connection

Outer join

Seven, sub-query

💖 finally

Related Posts

Spring study Notes

Hand-in-hand takes you from zero Encapsulation Gin framework (nine) : Token renewal & Encapsulation distributed lock

Install LongHorn (Distributed Block Storage) on K8S