The world.sql exercise on the mysql official website is used

DQL introduction

select
show

Application of select statement

select is used alone

select @@port;   --View port
select @@basedir; --View the directory where the program is located
select @@server_id; --see server_id

mysql> select database();  --View the current library
+------------+
| database() |
+------------+
| world      |
+------------+
1 row in set (0.00 sec)

mysql> select now();    --View current time
+---------------------+
| now()               |
+---------------------+
| 2021-12-03 07:43:11 |
+---------------------+
1 row in set (0.00 sec)

select general syntax (single table)

select column
from table
where condition
group by condition
having condition
order by condition
limit

select is used with the from clause

select column, column, column from table

example:

1. Query all information in the table

use world;
select * from city;

1. Query the values of name and Population in the table

select `Name`,Population from city;

select is used with the where clause

select column, column, column from table where filter condition

example:

• where equivalent condition query

1. Query the names and population of all cities in China

select `Name`,Population from city where CountryCode='CHN';

• where matches unequal query (> < > = < =)

1. Less than 100 people in the world are cities, city names and population numbers

select `Name`,Population from city where Population<100;

• where match logical connector (and or)

1. Query the name and population of cities with a population greater than 800w in China

select `Name`,Population from city where CountryCode='CHN' AND Population>8000000; 

1. Query the city name and population of China or the United States

select `Name`,Population from city where CountryCode='CHN' OR CountryCode='USA'; 

1. Query the city name and population with a population between 500w and 600w

select `Name`,Population from city where Population BETWEEN 5000000 AND 6000000;

• where combined with like clause to realize fuzzy query

1. Query the city information starting with C in CountryCode

select * from city where CountryCode like 'C%';
--%Don't appear in front, like'%C%',The query performance is affected because the index is not used. If there is a large demand in the business, use ES To replace

• where with in statement

1. Query city information in China or the United States

select `Name`,Population from city where CountryCode in ('CHN','USA');

select with group by + aggregate function application

Introduction to common aggregate functions

MAX(),MIN(),AVG(),COUNT(),SUM(): calculate the maximum, minimum and average values of the class
GROUP_CONCAT(): see example 6

GROUP BY

Divide the data rows with common conditions in a column into a group, and then operate the aggregate function

example:

1. Count the number of cities in each country

select CountryCode,COUNT(`Name`) from city GROUP BY CountryCode;

1. Count the total population of each country

select CountryCode,SUM(Population) from city GROUP BY CountryCode;

1. Count the number of provinces in each country

select CountryCode,COUNT(DISTINCT District) from city GROUP BY CountryCode;
--DISTINCT duplicate removal

1. Count the total population of each province in China

select District,SUM(Population) from city where CountryCode='CHN' GROUP BY District;

1. Count the number of cities in each province of China

select District,COUNT(id) from city where CountryCode='CHN' GROUP BY District;

1. List the names of cities in each province of China

select District,GROUP_CONCAT(`Name`) from city where CountryCode='CHN' GROUP BY District;

select with having application

The function is to filter after GROUP BY

example:

1. Count the total population of all countries and filter out those with a total population of more than 100 million

select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 100000000;

select with ORDER BY clause

Sort the final results, which can be used separately

example:

1. Count the total population of all countries, filter out those with a total population greater than 5000w, and arrange them in order from large to small

select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 50000000 ORDER BY SUM(Population) DESC;

select with limit clause

1. Count the total population of all countries, filter out those with a total population greater than 5000w, and arrange them in order from large to small, showing only the top three

select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 50000000 ORDER BY SUM(Population) DESC limit 3;

1. Count the total population of all countries, filter out those with a total population greater than 5000w, and arrange them in descending order, showing only 4-6 lines

select CountryCode,SUM(Population) from city GROUP BY CountryCode HAVING SUM(Population) > 50000000 ORDER BY SUM(Population) DESC limit 3,3;
--3,3: The first three lines are the first three lines of filtering, and the last three lines are the display lines

Union and union all

The function of merging and querying multiple result sets

example:

1. Query city information in China or the United States

select `Name`,Population from city where CountryCode='CHN'
UNION ALL
select `Name`,Population from city where CountryCode='USA';

The difference between union and union all
union all: do not repeat
union: will do to repeat

Multi table join query (inner join)

effect:
When single table data cannot meet query requirements

Example: query the country name, land area, city name and population of cities with less than 100 people in the world

select CountryCode,`Name`,Population from city WHERE Population<100;
--When finding the country name PCN Go again country Query in table
select `Name`,SurfaceArea from country where `Code`='PCN';

Basic syntax of multi table join query

The most important thing is to find the association condition columns between multiple tables
When writing column, it must be: table name. Column
All query columns involved are placed after select
Write all filter, grouping, sorting and other conditions after on in order

Example: query the country name, land area, city name and population of cities with less than 100 people in the world

SELECT country.`Name`,country.SurfaceArea,city.`Name`,city.Population
from city
JOIN country  --city Table Association country
ON city.CountryCode = country.`Code` --Association condition of two tables
WHERE city.Population<100;

Multi table Association

from A table
JOIN B table
ON A table. a=B table. A
JION C table
ON B table. b=C table. b

Alias application

• Table alias

SELECT cut.`Name`,cut.SurfaceArea,ct.`Name`,ct.Population
from city AS ct
JOIN country AS cut
ON ct.CountryCode = cut.`Code`
WHERE ct.Population<100;

Global call

• Column alias

SELECT cut.`Name`,cut.SurfaceArea AS the measure of area,ct.`Name`,ct.Population
from city AS ct
JOIN country AS cut
ON ct.CountryCode = cut.`Code`
WHERE ct.Population<100;
--When displayed cut.SurfaceArea The area will be displayed

It can be called by having and order