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 in.

MySQL provides powerful and flexible statements to realize these operations.

MySQL database uses select statement to query data.

Syntax format

select 
    [all|distinct]
Expression 1 alias of the target column,
Expression 2 alias for target column
from table name or view name alias, table name or view name alias
[where conditional expression]
[group by column name]
[having conditional expression]
[order by column name [asc|deac]]
[limit number or list];

Simplified syntax

select * | column name from table where condition;

Data preparation

-- 1.Create database
create database if not exists mydb2;
use mydb2;
-- 2.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
);
-- 3.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',400,'c002');
insert into product values(null,'Jinba casual pants',530,'c002');
insert into product values(null,'Hailan home sweater',580,'c002');
insert into product values(null,'Jack Jones Sweatpants',480,'c002');

insert into product values(null,'Lancome cream',600,'c003');
insert into product values(null,'Estee Lauder essence water',680,'c003');
insert into product values(null,'Chanel perfume',360,'c003');
insert into product values(null,'SK-ⅡGHb ',580,'c003');
insert into product values(null,'Shiseido foundation solution',180,'c003');

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

Simple query

Query all products

select * from product;

Query commodity name and price

select pname,price from product;

The keyword used in alias query is as (as can be omitted)

-- Table alias
select * from product as p;
select * from product p;

-- Column alias
select pname,price from product;
select pname as 'Trade name',price 'commodity price' from product;

Remove duplicate values

select distinct price from product;
select distinct * from product;-- Remove duplicate values from all columns

The query result is an expression (arithmetic query)

select pname,price+10 new_price from product;

operator

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.

MySQL supports four operators:

Arithmetic operator

Comparison operator

Logical operator

Bitwise Operators

Bitwise operators are operators that evaluate on binary numbers. Bit operation will first convert the operand into binary for bit operation. Then change the calculation result from binary number back to decimal number.

Operator operation - arithmetic operator

select 6+2;
select 6-2;
select 6*2;
select 6/2;
select 6%2;
select pname,price+10 as new_price from product;
select pname,price*1.1 as new_price from product;

Operator operation - conditional operator

-- Query all the information of the product named Haier washing machine
select * from product where pname='Haier washing machine';
-- Inquire about goods with a price of 800
select * from product where price=800;
-- Query all goods 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 or equal to 80 yuan
select * from product where price>=80;
-- Query all products with commodity prices between 200 and 1000
select * from product where price>=200 and price<=1000;
select * from product where price>=200 && 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 in(200,800);
select * from product where price=200 or price=800;
select * from product where price=200 || price=800;
-- Query contains'pants'All goods of the word
select * from product where pname like '%pants%'-- %Used to match any character
-- Query to'sea'All items beginning with
select * from product where pname like 'sea%';
-- The second word of query is'Cardamom'All goods
select * from product where pname like '_Cardamom%';-- Underline matches a single character
-- 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,5,30) as small_number;
select least(10,null,30);-- If the minimum value is calculated, the value is null，No comparison will be made, and the result is directly null
-- use greatest Find the maximum value
select greatest(10,20,30) as big_number;
-- If the maximum value is calculated, the value is null，No comparison will be made, and the result is directly null

Operator operation - bitwise operator (understand)

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

Sort query

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

select
Field 1, field 2
from table name
order by field 1 asc|desc, field 2 asc|desc

characteristic

1.asc stands for ascending order, desc stands for descending order, if not written, the default ascending order

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

3. The order by clause is placed at the end of the query statement. Except for the limit clause

Use price descending sort

select * from product order by price desc;

On the basis of price descending order, sort in descending order by category

select * from product order by price desc,category_id desc;

Display the price of goods (de duplication) and sort them in descending order

select distinct price from product order by price desc;

Aggregate query

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 calculates the value of a column and then returns a single value

Total number of items queried

select count(pid) from product;
select count(*) from product;

Query the total number of goods with a price greater than 200

select count(pid) from product where price>200;

Query the sum of all goods classified as' c001 '

select sum(price) from product where category_id='c001';

Query the maximum price of goods

select max(price) from product;

Query the minimum price of goods

select min(price) from product;

Query the average price of all goods classified as' c002 '

select avg(price) from product where category_id='c002';

Aggregate query - processing of NULL values

1. 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.

2. 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.

3. Processing of null value by max and min functions

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