1. Basic query

(1). concept

[1]. An important function of database management system is data query. Data query should not only simply return the data stored in the database, but also filter the data according to needs and determine what format the data is displayed.

[2].MySQL provides powerful and flexible statements to realize these operations

[3].MySQL database uses select statement to query data.

(2). Syntax format

select
  [all|distinct]
  <Expression 1 for target column> [alias],
  <Expression 1 for target column> [alias] ...
from <Table name or view name> [alias],  <Table name or view name> [alias] ...
[where<Conditional expression>]
[group by <Listing>
[having <Conditional expression>]]
[order by <Listing> [asc|desc]]
[limit <Number or list>]

Simplified syntax:

select *| Listing from surface where condition

(3). Examples

[1]. Data preparation
[a]. Create databases and tables

-- Create databases and tables
create database if not exist mydb2;
use mydb2;
-- Create item table:
create table product(
  pid int primary key auto_increment, -- Item number
  pname varchar(20) not null, -- Commodity name
  price double, -- commodity price
  category_id varchar(20) -- Commodity classification  
);

[b]. Add data

insert into product values(null, 'Haier washing machine', 5000, 'c001');
insert into product values(null, 'Midea refrigerator', 3000, 'c001');
insert into product values(null, 'Gree air conditioner', 5000, 'c001');
insert into product values(null, 'Jiuyang rice cooker', 5000, 'c001');

insert into product values(null, 'Woodpecker shirt', 300, 'c002');
insert into product values(null, 'Hengyuanxiang trousers', 800, 'c002');
insert into product values(null, 'Playboy jacket', 440, 'c002');
insert into product values(null, 'Jinba casual pants', 266, 'c002');
insert into product values(null, 'Hailan home sweater', 180, 'c002');
insert into product values(null, 'Jack Jones Sweatpants', 430, 'c002');

insert into product values(null, 'Lancome face cream', 300, 'c003');
insert into product values(null, 'Estee Lauder essence water', 200, 'c003');
insert into product values(null, 'Chanel perfume', 350, 'c003');
insert into product values(null, 'SK-II GHb ', 350, 'c003');
insert into product values(null, 'Shiseido liquid foundation', 180, 'c003');

insert into product values(null, 'Old Beijing instant noodles', 56, 'c004');
insert into product values(null, 'Liangpin shop kelp silk', 17, 'c004');
insert into product values(null, 'Three squirrel nuts', 88, null);

[2]. Simple query

-- 1.Query all products
select * from product;
-- 2.Query commodity name and price
select pname, price from product;
-- 3.The keyword used in alias query is as (as Can be omitted)
-- 3.1 Table alias
select * from product as p;
-- 3.2 Column alias
select pname as pn from product;
-- 4.Remove duplicate values
select distinct price from product;
-- 5.The query result is an expression(Operation query):Price of all goods+10 element
select pname, price+10 from product;

2. Operator

(1). brief introduction

After the table structure in the database is established, the meaning of the data represented in the table has been determined. Through the operation of MySQL operator, you can obtain another kind of data other than the table structure.

For example, there is a birth field in the student table, which indicates the year of birth of the student. Using MySQL operator to subtract the student's year of birth from the current year will get the student's actual age data

(2).MySQL supports four operators

[1]. Arithmetic operator

[2]. Comparison operator

[3]. Logical operator

[4]. Bitwise Operators

(3). Arithmetic operator

[1]. classification

Arithmetic operator	explain
+	Addition operation
-	Subtraction operation
*	Multiplication
/Or DIV	Division, return quotient
%Or MOD	The remainder operation returns the remainder

[2]. Examples

select 6 + 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;

-- Add 10 to the price of each item
select name, price + 10 as new_price from product;
-- Raise the price of all goods by 10%
select pname, price * 1.1 as new_price from product;

(4). Comparison operator

[1]. classification

Comparison operator	explain
=	be equal to
< and<=	Less than and less than or equal to
>And >=	Greater than and equal to
<=>	When both opcodes are NULL, the obtained value is 1; When an opcode is NULL, the resulting value is 0
< > or=	Not equal to
IS NULL or ISNULL	Determine whether a value is NULL
IS NOT NULL	Determine whether a value is not NULL
LEAST	Returns the minimum value when there are two or more parameters
GAEATEST	Returns the maximum value when there are two or more parameters
BETWEEN AND	Judge whether a value falls between two values
IN	Judge whether a value is any value IN the IN list
NOT IN	Judge whether a value is not any value IN the IN list
LIKE	Wildcard matching
REGEXP	Regular Expression Matching

[2]. Examples

-- The query product name is"Haier washing machine"All information about the product
select * from product where pname = 'Haier washing machine';

-- Inquire about goods with a price of 800
select * from product where price = 800;

-- Query all items whose price is not 800
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not(price = 800);

-- Query all commodity information with commodity price greater than 60 yuan
select * from product where price > 60

-- Query all products with commodity prices between 200 and 1000
select * from product where price >= 200 and price <= 1000;
select * from product where price between 200 and 1000;

-- Query all items with a price of 200 or 800
select * from product where price = 200 or price = 800;
select * from product where price in (200, 800);

-- Query contains'shoes'All goods of the word
select * from product where pname like '%shoes%';

-- Query to'sea'All items at the beginning
select * from product where pname like 'sea%';

-- The second word of query is'Cardamom'All goods
select * from product where pname like '_Cardamom%';

-- query category_id by null Commodity
select * from product where category_id is null;

-- query category_id Not for null Commodity
select * from product where category_id is not null;

-- use least Find the minimum value
select least(10, 20, 30); -- 10
select least(10, null, 30); -- null

-- use greatest Find the maximum value
select greatest(10, 20, 30); -- 30
select greatest(10, null, 30); -- null

(5). Logical operator

[1]. classification

Logical operator	explain
NOT or|	Logical non
AND or&&	Logic and
OR or||	Logical or
XOR	Logical XOR

(6). Bit operation

[1]. classification

Bitwise Operators	explain
|	Bitwise OR
&	Bitwise AND
^	Bitwise XOR
<<	Shift left by bit
>>	Shift right by bit
~	Invert by bit, inverting all bits

Note: bitwise operators are operators that evaluate on binary numbers.

[2]. Examples

select 3&5; -- Bit and
select 3|5; -- Bit or
select 3^5; -- Bit exclusive or
select 3>>1; -- Bit shift left
select 3<<1; -- sftr 
select ~3; -- Bit inversion

3. Sort query

(1). concept

If we need to sort the read data, we can use the order by clause of MySQL to set the sort method of the field you want to sort, and then return the search results.

(2). grammar

select 
   Field name 1, Field name 2, ...
from Table name
order by Field name 1 [asc|desc], Field name 2 [asc|desc] ... 

(3). characteristic

[1].asc stands for ascending order and desc for descending order. If you don't write, the default ascending order is.

[2].order by is used in clauses. It can support single fields, multiple fields, expressions, functions and aliases

[3]. The order by clause is placed at the end of the query statement, except the LIMIT clause

(4). Examples

-- 1.Use price order(Descending order)
select * from product order by price desc;
-- 2.Sort in price(Descending order)On the basis of classification(Ascending order)
select * from product order by price desc, category asc;
-- 3.Displays the price of the item(To repeat)，And sort(Descending order)
 select distinct price from product order by price desc;

4. Aggregate query

(1). concept

The previous queries are horizontal queries, which are judged according to the conditions row by row, while the query using aggregate function is vertical query, which is calculated by the value of a column, and then returns a single value; In addition, the aggregate function ignores null values.

(2). Aggregate function

Aggregate function	effect
count()	Counts the number of record rows whose specified column is not NULL
sum()	Calculates the numerical sum of the specified column. If the specified column type is not a numerical type, the calculation result is 0
max()	Calculate the maximum value of the specified column. If the specified column is of string type, the string sorting operation is used;
min()	Calculate the minimum value of the specified column. If the specified column is of string type, the string sorting operation is used;
avg()	Calculates the average value of the specified column. If the specified column type is not a numeric type, the calculation result is 0

(3). Examples

-- 1.Total number of items queried
select count(pid) from product;
select count(*) from product;

-- 2.Query the total number of goods with a price greater than 200
select count(pid) from product where price > 200;

-- 3.Query classification is'c001'The sum of all commodity prices
select sum(price) from product where category_id = 'c001';

-- 4.Query the maximum price of goods
select max(price) from product;

-- 5.Query the minimum price of goods
select min(price) from product;

-- 6.Query classification is'c002'Average price of all goods
select avg(price) from product where category_id = 'c002';

(4).Null value processing

[1]. brief introduction

[a]. Processing of null value by count function:

If the parameter of the count function is an asterisk (*), the number of all records will be counted. If the parameter is a field, the number of records with null value is not counted.

[b] Processing of null value by sum and avg functions

These two functions ignore the existence of null value as if the record did not exist.

​ [c]. Processing of null value by Max and min functions

The max and min functions also ignore the existence of null values

[2]. Examples

-- Create table
create table test_null(
  c1 varchar(20),
  c2 int  
);

-- insert data
insert into test_null values('aaa', 3);
insert into test_null values('bbb', 3);
insert into test_null values('ccc', null);
insert into test_null values('ddd', 6);

-- test
select count(*), count(1), count(c2) from test_null;
select sum(c2), max(c2), min(c2), avg(c2) from test_null;

5. Group query

(1). brief introduction

Grouping query refers to grouping query information using the group by clause

(2). format

select Field 1, Field 2 ... from Table name group by Grouping field having Grouping conditions;

(3). Examples

-- 1.Count the number of commodities in each category
select category_id, count(*) from product group by category_id;

(4). Conditional filtering after grouping - having

[1]. If the statistical results are filtered after grouping, you must use having instead of where

[2]. The where clause is used to filter the rows generated by the operation specified in the FROM clause

[3]. The group by clause is used to group the output of the WHERE clause

[4]. The having clause is used to filter rows from the grouping results.

-- Count the number of commodities in each category, and only display the information with the number greater than 4
select category_id, count(*) from product group by category_id having count(*) > 4;

6. Paging query

(1). brief introduction

Paging query is common in projects. Due to the large amount of data and the limited length of the display screen, the data is displayed in paging. For example, there are 30 data items in total, 5 items are displayed on each page, 15 items are displayed on the first page and 610 items are displayed on the second page

(2). format

-- Mode 1 - Before display n strip
select Field 1, Field 2 ... from Table name limit n
-- Mode 2 - Pagination display
select Field 1, Field 2 ... from Table name limit m, n
 parameter:
  m: integer, Indicates the index from which to calculate(Current page - 1) * Number of items displayed per page
  n: integer, Indicates how many pieces of data to query   

(3). Examples

-- query product The first five records in the table
select * from product limit 5

-- Display from 4 to 5
select * from product limit 3,5

7.insert into select statement

(1). brief introduction

To import data from one table into another, you can use the insert into select statement

(2). format

insert into table2(field1, field2, ...) select value1, value2, ... from table1;
perhaps
insert into table2 select * from table1;

8. Examples

(1). Case 1

[1]. Data preparation

-- Create database
create database mydb2;
use mydb2;

-- Create table
create table student(
    id int,
    name varchar(20),
    gender varchar(20),
    chinese int,
    english int,
    math int  
);

-- insert data
insert into student(id, name, gender, chinese, english, math) values(1, 'Zhang Ming', 'male', 89, 78, 90); 
insert into student(id, name, gender, chinese, english, math) values(2, 'Li Jin', 'male', 67, 53, 95); 
insert into student(id, name, gender, chinese, english, math) values(3, 'Wang Wu', 'female', 87, 78, 77); 
insert into student(id, name, gender, chinese, english, math) values(4, 'Li Yi', 'female', 88, 98, 92); 
insert into student(id, name, gender, chinese, english, math) values(5, 'Li Cai', 'male', 82, 84, 67); 
insert into student(id, name, gender, chinese, english, math) values(6, 'Zhang Bao', 'male', 55, 85, 45); 
insert into student(id, name, gender, chinese, english, math) values(7, 'Huang Rong', 'female', 75. 65, 30); 
insert into student(id, name, gender, chinese, english, math) values(7, 'Huang Rong', 'female', 75, 65, 30); 

[2]. query

-- Query the information of all students in the table
select * from student;

-- Query all students in the table and their corresponding English scores
select name, english from student;

-- Filter duplicate data in the table
select distinct * from student;

-- Count the total score of each student
select name, (chinese + english + math) as total_score from student;

-- Add 10 points to the total score of all students and 10 points for special skills
select name, (chinese + english + math + 10) as total_score from student;

-- Use alias to represent student scores
select name, chinese 'grade scores of Chinese', english 'English achievement', math 'Mathematics achievement' from student

-- Query students with English scores greater than 90
select * from student where english > 90;

-- Query all students whose total score is greater than 200
select * from student where (chinese + english + math) > 200;

-- Query English score at 80~90 Students between
select * from student where english between 80 and 90;
perhaps
select * from student where english >=80 and english <= 90;

-- Query English score is not 80~90 Between classmates
select * from student where not (english between 80 and 90);
perhaps
select * from student where english not between 80 and 90;
perhaps
select * from student where not (english >=80 and english <= 90);
perhaps
select * from student where english < 80 or english >90;

-- The query math score is 89, 90, 91 My classmate
select * from student where math in (89, 90, 91);

-- Query math score is 89, 90, 91 My classmate
select * from student where math not in (89, 90, 91);

-- Check the English scores of all students surnamed Li
select name, english from student where name like 'Lee%';

-- Query the students who are divided into 80 in mathematics and 80 in Chinese
select * from student where math = 80 and chinese = 80;

-- Query English 80 or 200 students
select * from student where math = 80 or (chinese + math + english) = 200;

-- Sort and output the math scores in descending order
select * from student order by math desc;

-- Output after sorting the total score, and then output from high to low
select * from student order by (chinese + math + english) desc;

-- Sort and output the total score of students surnamed Li
select * from student where name like 'Lee%' order by (chinese + math + english) desc;

-- Query the number of boys and girls respectively, sort and output the number in descending order, and query the gender number information with the number greater than 4
select gender, count(*) as total_cnt from student group by gender having total_cnt >4 order by total_cnt desc;

(2). Case 2

[1]. Prepare data

create table emp(
     empno INT, -- Employee number
     ename varchar(50), -- Employee name
     job varchar(50), -- Job name
     mgr INT, -- Superior leader No
     hiredate DATE, -- Entry date
     sal INT, -- salary
     comm INT, -- bonus
     deptno INT -- Department number
);
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2450, NULL, 10);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 3000, NULL, 20);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 5000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 2000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-19-08', 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

[2]. Sort the information of employees who do not work in department 10 in ascending order of employee number

select * from emp where deptno != 10 order by empno asc; 

[3]. Query the information of employees whose second letter of name is not 'A' and whose salary is greater than 1000 yuan, in descending order of annual salary

 -- Annual salary:12*a monthly salary + bonus 
 select * from emp where ename not like '_A%' and sal > 1000 order by(12*sal + ifnull(comm, 0)) desc;

[4]. Find the average salary of each department

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

[5]. Ask for the highest salary in each department

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

[6]. Ask for the highest salary for each position in each department

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

[7]. Find the department number with average salary greater than 2000

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

[8]. The average salary of the Department is listed in descending order, and the average salary of the Department is greater than 1500

select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 2000 order by avg_sal desc;  

[9]. Select the name and salary of employees with bonus in the company

select * from emp where comm is not null;

[10]. Query the gap between the maximum wage and the minimum wage of employees

select max(sal) - min(sal) as 'Pay gap' from emp;