1, Single table query
Query: users get the data they need from the database table in different ways
It is very important and frequent in database operation
1.1 create database
1. Create a new library company
2. Create EMP (employee table) in the company library
emp(Employee number empno
Employee name empname
Employee's position empjob
Employee leadership empleader
Employee entry date hiredate
Employee monthly salary salary
Employee's allowance allowance
Employee department number deptno)
3. Insert data into the table
#Create Library create database company charset utf8mb4; #Create table create table emp( empno int unsigned not null primary key auto_increment, empname varchar(20), empjob varchar(20), empleader varchar(20), hiredate date, salary decimal(10,3), allowance decimal(10,3), deptno tinyint unsigned ) mysql> desc emp; +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | empno | int(10) unsigned | NO | PRI | NULL | auto_increment | | empname | varchar(20) | YES | | NULL | | | empjob | varchar(20) | YES | | NULL | | | empleader | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | salary | decimal(10,3) | YES | | NULL | | | allowance | decimal(10,3) | YES | | NULL | | | deptno | tinyint(3) unsigned | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ # insert data insert into emp values (7369, 'Smith', 'clerk ', 7902,'1980-12-17',800, null, 20), (7499, 'Allen', 'salesman', 7698,'1981-02-20', 1600,300,30), (7521, 'Ward' , 'salesman', 7698, '1981-02-22',1250,500,30), (7566, 'Jones', 'manager', 7839,'1981-04-02',2975, null, 20), (7654, 'Maritn', 'salesman', 7698,'1981-09-28',1250, 1400,30), (7698,'Blake', 'manager', 7839,'1981-05-01', 2850,null, 30), (7782,'Clark' , 'manager', 7839,'1981-06-09', 2450, null, 10), (7788, 'Scott', 'analyst', 7566,'1987-04-19',3000, null, 20), (7839, 'King', 'president', null, '1981-11-17', 5000, null, 10), (7844, 'Turner' , 'salesman', 7698,'1981-09-08',1500,0,30), (7876, 'Adams', 'clerk', 7788,'1987-05-23',1100, null, 20), (7900, 'James', 'clerk', 7698,'1981-12-03', 950, null, 30), (7902,'Ford', 'analyst', 7566,'1981-12-03', 3000, null, 20), (7934, 'Miller' , ' clerk', 7782,'1982-01-23', 1300,null, 10); Note: due to hand error, the last line' clerk'One more blank was found in the middle of the experiment, and finally corrected, but it did not affect the experimental effect.
1.2 single table query
1.2.1 query all fields
Syntax:
select field name 1, field name 2... from table name
For example: query employee name, number, position, leader number, employment time, salary, bonus and department number in employee table
mysql> select empno,empname,empjob,empleader,hiredate,salary,allowance,deptno from emp; +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | +-------+---------+-----------+-----------+------------+----------+-----------+--------+
1.2.2 use wildcard * to query all fields
Syntax: select * from table name
For example: query all information in the employee table
select * from emp;
1.2.3 query specified fields
Syntax: select field name 1, field name 2... from table name
For example: query employee name, position, department number and salary in employee table
mysql> select empname,empjob,deptno,salary from emp; +---------+-----------+--------+----------+ | empname | empjob | deptno | salary | +---------+-----------+--------+----------+ | Smith | clerk | 20 | 800.000 | | Allen | salesman | 30 | 1600.000 | | Ward | salesman | 30 | 1250.000 | | Jones | manager | 20 | 2975.000 | | Maritn | salesman | 30 | 1250.000 | | Blake | manager | 30 | 2850.000 | | Clark | manager | 10 | 2450.000 | | Scott | analyst | 20 | 3000.000 | | King | president | 10 | 5000.000 | | Turner | salesman | 30 | 1500.000 | | Adams | clerk | 20 | 1100.000 | | James | clerk | 30 | 950.000 | | Ford | analyst | 20 | 3000.000 | | Miller | clerk | 10 | 1300.000 | +---------+-----------+--------+----------+
1.2.4 use of de duplication distinct
Syntax: select distinct field name 1, field name 2... From table name
For example: query employee's department number
Query which department numbers employees are in
mysql> select distinct deptno from emp; +--------+ | deptno | +--------+ | 20 | | 30 | | 10 | +--------+
distinct is used on multiple fields
For example: query employee department number and position
Compare the following two query methods
mysql> select distinct deptno,empjob from emp; +--------+-----------+ | deptno | empjob | +--------+-----------+ | 20 | clerk | | 30 | salesman | | 20 | manager | | 30 | manager | | 10 | manager | | 20 | analyst | | 10 | president | | 30 | clerk | | 10 | clerk | +--------+-----------+ 9 rows in set (0.00 sec) mysql> select deptno,empjob from emp; +--------+-----------+ | deptno | empjob | +--------+-----------+ | 20 | clerk | | 30 | salesman | | 30 | salesman | | 20 | manager | | 30 | salesman | | 30 | manager | | 10 | manager | | 20 | analyst | | 10 | president | | 30 | salesman | | 20 | clerk | | 30 | clerk | | 20 | analyst | | 10 | clerk | +--------+-----------+
Note: distinct is used for multiple fields. It is only used when the values of multiple fields are the same
1.2.5 using arithmetic operators in queries
For example: query employee's annual salary
mysql> select empname,salary*12 from emp; +---------+-----------+ | empname | salary*12 | +---------+-----------+ | Smith | 9600.000 | | Allen | 19200.000 | | Ward | 15000.000 | | Jones | 35700.000 | | Maritn | 15000.000 | | Blake | 34200.000 | | Clark | 29400.000 | | Scott | 36000.000 | | King | 60000.000 | | Turner | 18000.000 | | Adams | 13200.000 | | James | 11400.000 | | Ford | 36000.000 | | Miller | 15600.000 | +---------+-----------+
1.2.6. Alias fields
Syntax: select field name 1 [as] alias, field name 2..... From table name
For example: query the annual salary of an employee. The alias is annual salary
mysql> select empname as full name,salary*12 as Annual salary from emp; +--------+-----------+ | full name | Annual salary | +--------+-----------+ | Smith | 9600.000 | | Allen | 19200.000 | | Ward | 15000.000 | | Jones | 35700.000 | | Maritn | 15000.000 | | Blake | 34200.000 | | Clark | 29400.000 | | Scott | 36000.000 | | King | 60000.000 | | Turner | 18000.000 | | Adams | 13200.000 | | James | 11400.000 | | Ford | 36000.000 | | Miller | 15600.000 | +--------+-----------+
Note: if there are spaces in the alias, special symbols need to be enclosed in single quotes
For example, query an employee's annual salary, alias year sal
mysql> select empname as 'full name',salary*12 as 'year sal' from emp; +--------+-----------+ | full name | year sal | +--------+-----------+ | Smith | 9600.000 | | Allen | 19200.000 | | Ward | 15000.000 | | Jones | 35700.000 |
1.2.7 concat usage
mysql> select concat('full name:',empname,'position:',empjob) from emp;
+--------------------------------------------+
| concat('full name:',empname,'position:',empjob) |
+--------------------------------------------+
| full name:Smith position:clerk |
| full name:Allen position:salesman |
| full name:Ward position:salesman |
| full name:Jones position:manager |
| full name:Maritn position:salesman |
| full name:Blake position:manager |
| full name:Clark position:manager |
| full name:Scott position:analyst |
| full name:King position:president |
| full name:Turner position:salesman |
| full name:Adams position:clerk |
| full name:James position:clerk |
| full name:Ford position:analyst |
| full name:Miller position: clerk |
+--------------------------------------------+
2, Sort query results
The default sorting methods for ascending are asc (ascending order) and desc (descending order)
2.1 sorting in a single field
Syntax: select query content from table name order by field asc|desc
For example: query employee information and sort by employee salary in ascending order
mysql> select * from emp order by salary asc; +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | +-------+---------+-----------+-----------+------------+----------+-----------+--------+
2.2 using sorting in multiple fields
Syntax: select query content from table name order by field name 1 asc|desc, field name 2 asc|desc
For example, when querying employee information, sort by employee salary in ascending order first. If the salary is the same, sort by employment date in descending order
mysql> select * from emp order by salary asc,hiredate desc; +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | +-------+---------+-----------+-----------+------------+----------+-----------+--------+
Note: aliases can be used for sorting
For example, query the annual salary of employees and sort the alias yearsal according to the descending order of annual salary
mysql> select empname, salary*12 as yearsal from emp order by yearsal; +---------+-----------+ | empname | yearsal | +---------+-----------+ | Smith | 9600.000 | | James | 11400.000 | | Adams | 13200.000 | | Ward | 15000.000 | | Maritn | 15000.000 | | Miller | 15600.000 | | Turner | 18000.000 | | Allen | 19200.000 | | Clark | 29400.000 | | Blake | 34200.000 | | Jones | 35700.000 | | Scott | 36000.000 | | Ford | 36000.000 | | King | 60000.000 | +---------+-----------+
3, Condition query
Condition query: the user queries some records that meet the conditions according to certain conditions
Syntax: select query content from table where condition
3.1 use of comparison operators
For example: query employee information with department No. 20
mysql> select * from emp where deptno=20; +-------+---------+---------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+---------+-----------+------------+----------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | +-------+---------+---------+-----------+------------+----------+-----------+--------+
Query employee information with salary greater than or equal to 3000
mysql> select * from emp where salary>3000; +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | +-------+---------+-----------+-----------+------------+----------+-----------+--------+
Query the employee information whose name is smith
mysql> select * from emp where empname='smith'; +-------+---------+--------+-----------+------------+---------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+--------+-----------+------------+---------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | +-------+---------+--------+-----------+------------+---------+-----------+--------+
Note: MySQL is case insensitive by default. If you need to be case sensitive, you can use the binary keyword
For example:
# Empty mysql> select * from emp where binary empname='smith'; Empty set (0.01 sec) mysql> select * from emp where binary empname='Smith'; +-------+---------+--------+-----------+------------+---------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+--------+-----------+------------+---------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | +-------+---------+--------+-----------+------------+---------+-----------+--------+
3.2. [not]between and
You can query records in a certain range
For example: query employee information with salary between 1200-3000
select * from empty where salary between 1200 and 3000
mysql> select * from emp where salary between 1200 and 3000; +-------+---------+----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+----------+-----------+------------+----------+-----------+--------+ | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | +-------+---------+----------+-----------+------------+----------+-----------+--------+ perhaps mysql> select * from emp where salary > 1200 and salary < 3000;
Note: between is the beginning and after is the end, including the boundary
Query the name and salary of employees whose salary is not between 1200-3000
select ename,sal from empty where sal not between 1200 and 3000
mysql> select * from emp where salary not between 1200 and 3000; +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | +-------+---------+-----------+-----------+------------+----------+-----------+--------+
3.3. [not] in
Represents in the specified collection
Syntax: select query content from table name where field name in (value 1, value 2,...)
Query employee information whose names are smith, scott and king
mysql> select * from emp where empname in('smith','scott','kings');
+-------+---------+---------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob | empleader | hiredate | salary | allowance | deptno |
+-------+---------+---------+-----------+------------+----------+-----------+--------+
| 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 |
| 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 |
+-------+---------+---------+-----------+------------+----------+-----------+--------+
Query employee information whose name is not smith, scott or king
mysql> select * from emp where empname not in('smith','scott','king');
+-------+---------+----------+-----------+------------+----------+-----------+--------+
| empno | empname | empjob | empleader | hiredate | salary | allowance | deptno |
+-------+---------+----------+-----------+------------+----------+-----------+--------+
| 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 |
| 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 |
| 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 |
| 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 |
| 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 |
| 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 |
| 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 |
| 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 |
| 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 |
| 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 |
| 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 |
+-------+---------+----------+-----------+------------+----------+-----------+--------+
3.4 is null or is not null (case insensitive)
Judge whether a field is empty
For example: query the information of employees with allowances in employees
mysql> select * from emp where allowance is not null; +-------+---------+----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+----------+-----------+------------+----------+-----------+--------+ | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | +-------+---------+----------+-----------+------------+----------+-----------+--------+
Query employee information without allowance in employees
mysql> select * from emp where allowance is null; +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | +-------+---------+-----------+-----------+------------+----------+-----------+--------+
3.5 fuzzy query
You can query that starts with XX and ends with XX. Which number is X
Syntax:
select Query content from Table name where Field name like %: A character of any length(You can have it or not) _: Match one character
For example: query the employee information of employees whose names begin with S
mysql> select * from emp where binary empname like 'S%'; +-------+---------+---------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+---------+-----------+------------+----------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | +-------+---------+---------+-----------+------------+----------+-----------+--------+
Query the employee information whose name ends with S in the employee
mysql> select * from emp where binary empname like '%S'; Empty set (0.00 sec)
Query employee information with S in employee name
mysql> select * from emp where binary empname like '%S%'; +-------+---------+---------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+---------+-----------+------------+----------+-----------+--------+ | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | +-------+---------+---------+-----------+------------+----------+-----------+--------
Query the employee information whose second character is l
mysql> select * from emp where empname like '_l%'; +-------+---------+----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+----------+-----------+------------+----------+-----------+--------+ | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | +-------+---------+----------+-----------+------------+----------+-----------+--------+
3.6 logical operators in conditions
3.6.1 and
It refers to that only records that meet all query criteria can be queried during query
Syntax: select query content from table name where condition 1 and condition 2 and
For example: query the employee name, department number and position with department No. 20 and position clerk in the employee table
mysql> select empname,deptno,empjob from emp where empjob='clerk' and deptno=20; +---------+--------+--------+ | empname | deptno | empjob | +---------+--------+--------+ | Smith | 20 | clerk | | Adams | 20 | clerk | +---------+--------+--------+
3.6.2 or
It refers to that records that meet any condition can be queried during query
Syntax: select query content from table name where condition 1 or condition 2 or
Query the name, department number and position of the employee whose department number is 20 or position is clerk in the employee table
mysql> select empname,deptno,empjob from emp where empjob='clerk' or deptno=20; +---------+--------+---------+ | empname | deptno | empjob | +---------+--------+---------+ | Smith | 20 | clerk | | Jones | 20 | manager | | Scott | 20 | analyst | | Adams | 20 | clerk | | James | 30 | clerk | | Ford | 20 | analyst | +---------+--------+---------+
4, Paging query
Syntax:
select Query content
from Table name
where condition
order by Field name asc|desc
limit a,b
be careful:
1.limit which record to start from, the number of entries displayed on each page, and the default value of the first entry is a=0
For example, if page n is displayed, the syntax of m pieces of information on each page is
select * from table name limit (n-1) * m, M
2. If limit is used with order by, sort first and then page
For example, when querying the information of employees whose salary is greater than 1200, it is required to sort by salary descending order. Only the first page with three pieces of data per page is displayed.
mysql> select * from emp where salary>1200 order by salary desc limit 0,3; +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | +-------+---------+-----------+-----------+------------+----------+-----------+--------+
5, Use of functions
5.1 single line function
5.1.1 character function
5.1.1.1 concat() concatenation string
For example, query employee information and output it in the specified format
Employee name: XX, salary: XX, position: XX, entry date: XX, annual salary: XX
For example:
mysql> select concat('Employee name:',empname,'Employee position:',empjob,'Employee salary:',salary) from emp;
+---------------------------------------------------------------------------------+
| concat('Employee name:',empname, 'Employee position:',empjob, 'Employee salary:',salary) |
+---------------------------------------------------------------------------------+
| Employee name:Smith Employee position:clerk Employee salary:800.000 |
| Employee name:Allen Employee position:salesman Employee salary:1600.000 |
| Employee name:Ward Employee position:salesman Employee salary:1250.000 |
| Employee name:Jones Employee position:manager Employee salary:2975.000 |
| Employee name:Maritn Employee position:salesman Employee salary:1250.000 |
| Employee name:Blake Employee position:manager Employee salary:2850.000 |
| Employee name:Clark Employee position:manager Employee salary:2450.000 |
| Employee name:Scott Employee position:analyst Employee salary:3000.000 |
| Employee name:King Employee position:president Employee salary:5000.000 |
| Employee name:Turner Employee position:salesman Employee salary:1500.000 |
| Employee name:Adams Employee position:clerk Employee salary:1100.000 |
| Employee name:James Employee position:clerk Employee salary:950.000 |
| Employee name:Ford Employee position:analyst Employee salary:3000.000 |
| Employee name:Miller Employee position: clerk Employee salary:1300.000 |
+---------------------------------------------------------------------------------+
5.1.1.2 length() calculates the length of the string
For example: query the employee information of employees whose name length is 4
be careful:
length() in mysql counts the length according to bytes. The length of Chinese characters is related to the code, and English is not affected
len() in python counts the length according to characters. The length of Chinese characters has nothing to do with coding. The length of a Chinese character is 1, which has no effect on English
mysql> select * from emp where length(empname)=4; +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | +-------+---------+-----------+-----------+------------+----------+-----------+--------+
5.1.1.3 lower() upper() case conversion
For example: query employee name, upper case employee name and lower case employee name
mysql> select empname from emp; +---------+ | empname | +---------+ | Smith | | Allen | | Ward | | Jones | | Maritn | | Blake | | Clark | | Scott | | King | | Turner | | Adams | | James | | Ford | | Miller | +---------+ 14 rows in set (0.00 sec) mysql> select lower(empname) from emp; +----------------+ | lower(empname) | +----------------+ | smith | | allen | | ward | | jones | | maritn | | blake | | clark | | scott | | king | | turner | | adams | | james | | ford | | miller | +----------------+ 14 rows in set (0.01 sec) mysql> select upper(empname) from emp; +----------------+ | upper(empname) | +----------------+ | SMITH | | ALLEN | | WARD | | JONES | | MARITN | | BLAKE | | CLARK | | SCOTT | | KING | | TURNER | | ADAMS | | JAMES | | FORD | | MILLER | +----------------+
5.1.1.4 replace()
Replaces a substring with a new string in the specified string
Format: replace (target string, searched substring, new string)
For example, replace l with L in the employee name
mysql> select replace(empname,'l','L') from emp; +--------------------------+ | replace(empname,'l','L') | +--------------------------+ | Smith | | ALLen | | Ward | | Jones | | Maritn | | BLake | | CLark | | Scott | | King | | Turner | | Adams | | James | | Ford | | MiLLer | +--------------------------+
5.1.1.5 substring() intercepts substring
Substring (target string, start position, length)
Note: the start index starts from 1, not 0
For example: get the first four characters of an employee's name
mysql> select substring(empname,1,4) from emp; +------------------------+ | substring(empname,1,4) | +------------------------+ | Smit | | Alle | | Ward | | Jone | | Mari | | Blak | | Clar | | Scot | | King | | Turn | | Adam | | Jame | | Ford | | Mill | +------------------------+
5.1.2. Numerical function
5.1.2.1 abs()
Function: take absolute value
mysql> select abs(-1),abs(3); +---------+--------+ | abs(-1) | abs(3) | +---------+--------+ | 1 | 3 | +---------+--------+ 1 row in set (0.00 sec
5.1.2.2 pi()
Function: get pi
mysql> select pi(); +----------+ | pi() | +----------+ | 3.141593 | +----------+
5.1.2.3 mod()
effect:Take mold
For example: select mod(3,2)
mysql> select mod(3,2); +----------+ | mod(3,2) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> select mod(2,3); +----------+ | mod(2,3) | +----------+ | 2 | +----------+
5.1.2.4 pow()
Function: find the n-th power of a number
mysql> select power(3,2); +------------+ | power(3,2) | +------------+ | 9 | +------------+
5.1.2.5 ceil() floor()
- ceil(): round up
- floor(): round down
mysql> select ceil(3.14); +------------+ | ceil(3.14) | +------------+ | 4 | +------------+ 1 row in set (0.00 sec) mysql> select floor(3.99); +-------------+ | floor(3.99) | +-------------+ | 3 | +-------------+
5.1.2.6 round()
round(num): returns a rounded integer
round(num,n): returns a decimal number rounded to n digits
mysql> select round(5.4),round(6.8),round(5.34,1),round(6.789,2); +------------+------------+---------------+----------------+ | round(5.4) | round(6.8) | round(5.34,1) | round(6.789,2) | +------------+------------+---------------+----------------+ | 5 | 7 | 5.3 | 6.79 | +------------+------------+---------------+----------------+ 1 row in set (0.00 sec)
5.1.2.7 truncate
truncate(num,n) among n The value of can be 0,1,2..If n The value of 0 represents the truncated integer
mysql> select truncate(5.3,0),truncate(5.67,1),truncate(5.789,2); +-----------------+------------------+-------------------+ | truncate(5.3,0) | truncate(5.67,1) | truncate(5.789,2) | +-----------------+------------------+-------------------+ | 5 | 5.6 | 5.78 | +-----------------+------------------+-------------------+
5.1.2.8 rand()
effect:Gets a random number of floating point type,Range 0-1.0 This includes 0 but not 1
mysql> select rand(),rand(),rand(); +--------------------+---------------------+--------------------+ | rand() | rand() | rand() | +--------------------+---------------------+--------------------+ | 0.5739643895756121 | 0.11088874108240822 | 0.8325505674188496 | +--------------------+---------------------+--------------------+
5.1.3 date time function
5.1.3.1 now()
Get the current date and time, including month, day, hour, minute and second
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2021-11-11 16:25:24 | +---------------------+ 1 row in set (0.00 sec)
5.1.3.2 curdate()
Get the current date, only including mm / DD / yy
mysql> select curdate(); +------------+ | curdate() | +------------+ | 2021-11-11 | +------------+
5.1.3.3 curtime()
Gets the current time, including only hours, minutes and seconds
mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 16:27:22 | +-----------+
5.1.3.4 sysdate(),now()
Gets the date and time when the function was executed
now() gets the date and time when the SQL was executed
mysql> select sysdate(),now(); +---------------------+---------------------+ | sysdate() | now() | +---------------------+---------------------+ | 2021-11-11 16:43:16 | 2021-11-11 16:43:16 | +---------------------+---------------------+
mysql> select sysdate(),now(),sleep(2),sysdate(),now(); +---------------------+---------------------+----------+---------------------+---------------------+ | sysdate() | now() | sleep(2) | sysdate() | now() | +---------------------+---------------------+----------+---------------------+---------------------+ | 2021-11-11 16:43:58 | 2021-11-11 16:43:58 | 0 | 2021-11-11 16:44:00 | 2021-11-11 16:43:58 | +---------------------+---------------------+----------+---------------------+---------------------+
5.1.3.5 dayofyear(),week()
Gets the day of the year in which a date is
week() gets the week ordinal of the year in which a date is
For example: select dayofyear(now()),week(now())
mysql> select dayofyear(now()),week(now()); +------------------+-------------+ | dayofyear(now()) | week(now()) | +------------------+-------------+ | 315 | 45 | +------------------+-------------+
5.1.3.6 datediff()
Calculate the time interval between two dates
For example, calculate the time interval from 2021-1-1 to the present
mysql> select datediff('2021-1-1',now());
+----------------------------+
| datediff('2021-1-1',now()) |
+----------------------------+
| -314 |
+----------------------------+
5.1.3.7 date_add() date_sub() implements the addition and subtraction of dates
date_ Add (date, time interval type, keyword interval, expression corresponding to time interval type, time interval type)
For example, day_hour 1_12 stands for 1 day and 12 hours
year_month 2_1 represents 2 years and 1 month
For example, the current time plus two years and a month
mysql> select date_add(now(),interval '2_1' year_month); +-------------------------------------------+ | date_add(now(),interval '2_1' year_month) | +-------------------------------------------+ | 2023-12-11 16:57:30 | +-------------------------------------------+
Subtract three days from the present time
mysql> select date_sub(now(),interval '3' day); +----------------------------------+ | date_sub(now(),interval '3' day) | +----------------------------------+ | 2021-11-08 17:02:25 | +----------------------------------+
5.1.4 process control function
5.1.4.1 if (condition, t,f) if the condition is true, return T; otherwise, return F
For example: select if (1 > 2, '1 greater than 2', '1 less than 2')
mysql> select if(1>2,'1 Greater than 2','1 Less than 2'); +-------------------------------+ | if(1>2,'1 Greater than 2','1 Less than 2') | +-------------------------------+ | 1 Less than 2 | +-------------------------------+ 1 row in set (0.00 sec)
Query the employee's salary. If salary > = 3000, enter "high salary", otherwise "low salary"
mysql> select empname,salary,if(salary>3000,'High salary','Low salary') from emp; +---------+----------+-----------------------------------+ | empname | salary | if(salary>3000,'High salary','Low salary') | +---------+----------+-----------------------------------+ | Smith | 800.000 | Low salary | | Allen | 1600.000 | Low salary | | Ward | 1250.000 | Low salary | | Jones | 2975.000 | Low salary | | Maritn | 1250.000 | Low salary | | Blake | 2850.000 | Low salary | | Clark | 2450.000 | Low salary | | Scott | 3000.000 | Low salary | | King | 5000.000 | High salary | | Turner | 1500.000 | Low salary | | Adams | 1100.000 | Low salary | | James | 950.000 | Low salary | | Ford | 3000.000 | Low salary | | Miller | 1300.000 | Low salary | +---------+----------+-----------------------------------+
5.1.4.2 ifnull (value 1, value 2)
If the value 1 is not empty, the value 1 is returned; otherwise, the value 2 is returned
Query employee's year
select salary*12 from emp;
select empname,(salary+allowance)*12 from emp;
select empname,(salary+ifnull(allowance,0))*12 from emp; # Because allowance has a null value.
mysql> select empname,(salary+allowance)*12 from emp; +---------+-----------------------+ | empname | (salary+allowance)*12 | +---------+-----------------------+ | Smith | NULL | | Allen | 22800.000 | | Ward | 21000.000 | | Jones | NULL | | Maritn | 31800.000 | | Blake | NULL | | Clark | NULL | | Scott | NULL | | King | NULL | | Turner | 18000.000 | | Adams | NULL | | James | NULL | | Ford | NULL | | Miller | NULL | +---------+-----------------------+ have null Value is obviously unreasonable. You need to query as follows mysql> select empname,(salary+ifnull(allowance,0))*12 from emp; +---------+---------------------------------+ | empname | (salary+ifnull(allowance,0))*12 | +---------+---------------------------------+ | Smith | 9600.000 | | Allen | 22800.000 | | Ward | 21000.000 | | Jones | 35700.000 | | Maritn | 31800.000 | | Blake | 34200.000 | | Clark | 29400.000 | | Scott | 36000.000 | | King | 60000.000 | | Turner | 18000.000 | | Adams | 13200.000 | | James | 11400.000 | | Ford | 36000.000 | | Miller | 15600.000 | +---------+---------------------------------+
5.1.4.3 nullif (value 1, value 2)
Returns null if the value 1 is equal to the value 2, otherwise returns the value 1
For example, select nullif(1,2),nullif(1,1)
mysql> select nullif(1,2),nullif(1,1); +-------------+-------------+ | nullif(1,2) | nullif(1,1) | +-------------+-------------+ | 1 | NULL | +-------------+-------------+
5.1.4.4 case value
when Value 1 then Result 1 when Value 2 then Result 2 ... else Other results end
mysql> select case 1
-> when 1 then 'The result is 1'
-> when 2 then 'The result is 2'
-> else 'Other results'
-> end;
+----------------------------------------------------------------------------------+
| case 1
when 1 then 'The result is 1'
when 2 then 'The result is 2'
else 'Other results'
end |
+----------------------------------------------------------------------------------+
| The result is 1 |
+----------------------------------------------------------------------------------+
5.1.4.5 case conditions
when condition then Result 1 when condition then Result 2 ..... else Other results end
For example: when querying employee salary, if salary > = 3000, return "high salary", otherwise "low salary"
select salary,case
when sal>=3000 then 'High salary'
else 'Low salary'
end
as 'Pay level ' from emp;
mysql> select salary,case
-> when salary >=3000 then 'High salary'
-> else 'Low salary'
-> end
-> as'Pay level 'from emp;
+----------+--------------+
| salary | Pay level |
+----------+--------------+
| 800.000 | Low salary |
| 1600.000 | Low salary |
| 1250.000 | Low salary |
| 2975.000 | Low salary |
| 1250.000 | Low salary |
| 2850.000 | Low salary |
| 2450.000 | Low salary |
| 3000.000 | High salary |
| 5000.000 | High salary |
| 1500.000 | Low salary |
| 1100.000 | Low salary |
| 950.000 | Low salary |
| 3000.000 | High salary |
| 1300.000 | Low salary |
+----------+--------------+
5.2 multiline functions
1. Multi line function: operate a group of data (multi line records) to return a result, which is also called grouping function2. Multiline functions are mostly used for statistics
For example, count the number of employees in each department
What is the highest and lowest salary in each department
5.2.1 count()
Number of records in the statistical table
a. Total number of records in the statistics table count(*)
For example, query how many records there are in the employee table
mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+
b.count(exp) counts the number of records whose exp value is not empty
For example, query how many employees in the employee table have allowances
mysql> select count(allowance) from emp; +------------------+ | count(allowance) | +------------------+ | 4 | +------------------+
c.count(distinct(exp)) returns the total number of records whose value of the expression exp is not duplicate and non empty
For example: how many employees are leaders in the statistics employee table
select count(distinct(empleader)) from emp;– It counts the number of leaders other than the chairman (the chairman is null)
mysql> select count(distinct(empleader)) from emp; +----------------------------+ | count(distinct(empleader)) | +----------------------------+ | 6 | +----------------------------+
Count all leaders in the employee table
Ifnull (value 1, value 2) if value 1 is not null, return value 1, otherwise return value 2
select count(distinct(ifnull(empleader,1))) from emp; # Because the manager's leader is null
mysql> select count(distinct(ifnull(empleader,1))) from emp; +--------------------------------------+ | count(distinct(ifnull(empleader,1))) | +--------------------------------------+ | 7 | +--------------------------------------+
5.2.2 sum()
a.sum(exp): returns the sum of expression values
mysql> select sum(salary) as 'Total monthly salary' from emp; +-----------+ | Total monthly salary | +-----------+ | 29025.000 | +-----------+
b.sum(distinct(exp)) returns the sum of non repeating expressions exp
mysql> select sum(salary),sum(distinct(salary)) from emp; +-------------+-----------------------+ | sum(salary) | sum(distinct(salary)) | +-------------+-----------------------+ | 29025.000 | 24775.000 | +-------------+-----------------------+
5.2.3 avg()
a.avg(exp): returns the average value of the expression value
For example: select avg(salary) from emp;
mysql> select avg(salary) from emp; +--------------+ | avg(salary) | +--------------+ | 2073.2142857 | +--------------+
b.avg(distinct(exp)): returns the average value of the non repeated expression exp
For example, select avg(distinct(salary)) from emp;
mysql> select avg(distinct(salary)) from emp; +-----------------------+ | avg(distinct(salary)) | +-----------------------+ | 2064.5833333 | +-----------------------
5.2.4 max() min()
max(exp): returns the maximum value of the expression value
min(exp): returns the minimum value of the expression value
for example
mysql> select min(salary),max(salary) from emp; +-------------+-------------+ | min(salary) | max(salary) | +-------------+-------------+ | 800.000 | 5000.000 | +-------------+-------------+ 1 row in set (0.00 sec)
5.3 grouping statistics
1. Syntax:
select Query content from Table name [where condition] [group by Group field name 1,Group field name 2...] [order by Field name asc|desc] [limit]
2. Use examples
a. Ask for the number of people in each department
select deptno,count(*) from emp group by deptno;
mysql> select deptno,count(*) from emp group by deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+----------+
b. Find the average salary of each department
select deptno,avg(sal) from emp group by deptno;
mysql> select deptno,avg(salary) from emp group by deptno; +--------+--------------+ | deptno | avg(salary) | +--------+--------------+ | 10 | 2916.6666667 | | 20 | 2175.0000000 | | 30 | 1566.6666667 | +--------+--------------+
c. Seek the highest salary and number of people in each department
select deptno,max(sal),count(*) from emp group by deptno;
mysql> select deptno, max(salary),count(*) from emp group by deptno; +--------+-------------+----------+ | deptno | max(salary) | count(*) | +--------+-------------+----------+ | 10 | 5000.000 | 3 | | 20 | 3000.000 | 5 | | 30 | 2850.000 | 6 | +--------+-------------+----------+
d. Number of people per position
mysql> select empjob,count(*) as 'Total number' from emp group by empjob; +-----------+-----------+ | empjob | Total number | +-----------+-----------+ | analyst | 2 | | clerk | 4 | | manager | 3 | | president | 1 | | salesman | 4 | +-----------+-----------+
e. Displays the number of people in different positions in each department
3. Attentionmysql> select deptno,empjob,count(*) as 'Total number' from emp group by deptno,empjob; +--------+-----------+-----------+ | deptno | empjob | Total number | +--------+-----------+-----------+ | 10 | clerk | 1 | | 10 | manager | 1 | | 10 | president | 1 | | 20 | analyst | 2 | | 20 | clerk | 2 | | 20 | manager | 1 | | 30 | clerk | 1 | | 30 | manager | 1 | | 30 | salesman | 4 | +--------+-----------+-----------+
a. If the query field is not included in the multiline function, it must be a grouping field. The following query will report an error
select empname,empjob,sum(salary) from emp group by empjob;
b. Without group by, query fields cannot be queried together with multiline functions
select sal,empno from emp; legitimate
select sum(sal),empno from emp; wrongful
c. Multiline functions are not allowed in the where condition. If they are used, they must use having
5.4 having clause
1. Syntax:
select Query content from Table name [where condition] [group by Grouping field] [having condition] [order by] [limit]
select from --where filtering - group by – having filtering
2. Use examples
a. The number of people in different positions in each department, and the number is greater than 2
mysql> select deptno,empjob,count(*) from emp group by deptno,empjob having count(*) >=2; +--------+----------+----------+ | deptno | empjob | count(*) | +--------+----------+----------+ | 20 | analyst | 2 | | 20 | clerk | 2 | | 30 | salesman | 4 | +--------+----------+----------+
b. List the positions with minimum salary less than 2000 in the emp table
First query the minimum salary of each position
select empjob,min(salary) from emp group by empjob;
mysql> select empjob,min(salary) from emp group by empjob having min(salary)<2000; +----------+-------------+ | empjob | min(salary) | +----------+-------------+ | clerk | 800.000 | | salesman | 1250.000 | +----------+-------------+
c. List the Department and position mix with an average salary greater than 1200
First, find the average salary of different positions in each department
select avg(sal) from emp group by deptno,job
mysql> select deptno,empjob,avg(salary) from emp group by deptno,empjob having avg(salary)>12200; +--------+-----------+--------------+ | deptno | empjob | avg(salary) | +--------+-----------+--------------+ | 10 | clerk | 1300.0000000 | | 10 | manager | 2450.0000000 | | 10 | president | 5000.0000000 | | 20 | analyst | 3000.0000000 | | 20 | manager | 2975.0000000 | | 30 | manager | 2850.0000000 | | 30 | salesman | 1400.0000000 | +--------+-----------+--------------+