MySQL Query SELECT statement Rollup

Data Preparation:
To Create a table:
CREATE TABLE students (
ID int unsigned primary key auto_increment not NULL,
name varchar ("default"),
Age tinyint unsigned default 0,
height Decimal (5,2),
gender enum (' Male ', ' female ', ' shemale ', ' secrecy '),
cls_id int unsigned default 0,
isdelete bit default 0
        );
To create the data:
INSERT into students values
(0, ' Wei Shao ', 18,180.00,2,1,0),
(0, ' Xiao Yue Moon ', 18,180.00,2,2,1),
(0, ' Peng ', 29,185.00,1,1,0),
(0, ' Andy Lau ', 59,175.00,1,2,1),
(0, ' Hibiscus ', 38,160.00,2,1,0),
(0, ' chicken sister ', 28,150.00,4,2,1),
(0, ' condom ', 18,172.00,2,1,1),
(0, ' Jay Chou ', 36,null,1,1,0),
(0, ' Cheng ', 27,181.00,1,2,0),
(0, ' Liu Yifei ', 25,166.00,2,2,0),
(0, ' Venus ', 33,162.00,3,3,1),
(0, ' static incense ', 12,180.00,2,4,0),
(0, ' Zhou Jie ', 34,176.00,2,5,0);


1 Querying all fields:
select * from table name;
For example SELECT * from students;
--Query all information in the students table


2 Specify the field query:
Select column 1, column 2, ..., column n from table name;
For example Select Students.id, students.name from students
--Generally listed as a database. table. Columns, database, and table names can be omitted. Table names cannot be omitted when querying multiple tables

you can alias a table with the AS keyword:
For example: Select S.id, s.name from students as s;
You can also list aliases:
For example Select ID as number, name as name from students;

As can be omitted when used as an alias, column name or table name and alias:
For example: Select s.id number, S.name name from students s;

3 Eliminate duplicate row queries (deduplication) DISTINCT:
select DISTINCT column 1, column 2 from table name;
For example: SELECT DISTINCT ID, name, gender from students;
-When querying multiple columns, each piece of data is repeated as a whole. 


4 conditions where
select * FROM table name where condition;
For example SELECT * from students where id = 1;

multiple operators are supported in the Where: comparison operators, logical operators, fuzzy queries, scope queries, NULL judgments

4.1 comparison operator: equals = equals > less than < greater than equals >= less than or equal to <= not equal! = or <>
For example:
SELECT * FROM students where id = 1;
--The search ID in the students table is 1. 
SELECT * from students where ID >5;
--query IDs larger than 5 in the students table. 
SELECT * FROM students where name! = "Huang Rong";
--The query name is not Huang Rong information. 


4.2 logical operators: and OR not
SELECT * from students where ID > 3 and gender = 0;
--The query ID in the students table is larger than 3 and the gender is the first enumeration object. 

4.3 fuzzy query: Like, Rlike
% means any number of any characters
_ denotes an arbitrary character
For example:
SELECT * from students where name is like "yellow";
--inquire about the person surnamed Huang

SELECT * from students where name is like "Yellow _";
--Query the person whose name is two characters and the surname is yellow

SELECT * from students where name is like "% yellow";
--Query the person whose name contains yellow characters
SELECT * from students where name rlike "^ Yellow. *";
--Rlike followed by regular expressions. 
4.4 Range Query:
in means within a discontinuous range:
SELECT * from students where ID in (1,3,8);
--The query ID is 1 or 3 or 8 of everyone

SELECT * from students where ID between 3 and 8;
--Query number 3 to 8 of the person

SELECT * from students where ID between 3 and 8 and gender =1;
--Query numbers 3 to 8 for boys. 

Null to determine nulls:
determine if null is NULL
SELECT * from students where the height is null;
--Check the person who has not filled in the height. 

determining non-NULL is NOT NULL
SELECT * from students where height is not null;
--Information for people who fill their height

Priority level:
precedence from high to Low: parentheses > Not > Comparison operators, logical operators
and than or is the first operation, if it appears at the same time and you want to count or use parentheses. 

5 reviews sorted by
select * FROM table name
Order BY column 1 asc|desc, column 2 Asc|desc

1 Sort By column 1 first, column 1 is sorted by column 2. 
2 default by column values from the school to the big row sequence
3 ASC arranged from small to large, ascending
4 desc from large to small sort, descending

For example:

SELECT * FROM Students
where gender = 1
ORDER BY id desc;
--Query gender in the students table is 1 and is sorted in descending order of ID

SELECT * FROM Students
where Isdelete = 0
order by name;
--The information that is not deleted in the students table is sorted by name in ascending order. 

6 Aggregation functions:
1 count (*) query Total rows
Select COUNT (*) from students;
--Query the total number of rows of data in the students table. 
2 max (column) queries the maximum value of the column
select Max (age) from students;
--Query the maximum value of age in students
3 min (column) query the lowest value in the column
Select min (age) from students;
--Query the minimum data of age in students
4 sum (column) query the sum of the values of the column
select SUM (age) from students;
--Query the sum of age in the students table
5 AVG (column) query the average of the column
Select AVG (age) from students;
--Query the average of age in the students column

7 Grouping
grouping by field means that the same data in this field is placed in a group
Grouped by columns are displayed in the result set, and other columns are not displayed in the result set
you can count the grouped data and do the aggregation operations .

Select column 1, column 2, aggregation ... from table name Group BY column 1, column 2;

For example:
Select Gender as Gender, COUNT (*)
From students
GROUP by gender;
--Check the total number of people per gender

Select Age as, COUNT (*) as number
From students
group by age;
--Check the number of people per age

data filtering after grouping:
Select column 1, column 2, aggregation ... from table name
Group BY column 1, column 2, column 3 ...
Having column 1,... Aggregation ...
--The conditional operator behind the having the same as where

For example:
Select Gender as Gender, COUNT (*)
From students
GROUP BY gender
Having gender = 1;
--Query the total number of males,

compare where and have
where to filter data from the table specified after from, which is the filter for the original data
Having is filtering the results of group by

8 pagination Limit:
select * FROM table name
limit start, Count
--Skip previous start info to display count bar information
--Start can be omitted, default starting from 0

Case:
limit the display of M data per page, currently showing page n, the total number of pages. 
select * FROM table name
limit (n-1) *m, M;

9 Connection query:
MySQL supports three types of connection queries: Inner connection, right outer connection, left outer connection. 
select * FROM table name
[inner|across|left|right] Join table 2 on table 1. column = table 2. Column (condition)
Inner|across on behalf of internal connections
Left|right on behalf of external connections

internal connection: Strictly according to the conditions, two tables must all strictly meet the conditions. Any table that does not meet the criteria cannot enter the result. 
Select students.*, classes.*
From students
[Inner|across] Join classes
On students.cls_id = classes.id;
--Connect the two tables in the same class number and show the results
--a class.id or students.cls_id if there is no corresponding in the other table will not be detected

LEFT OUTER join: the right table, the right table all the data into the results, there is no left table matching the criteria of the data with null placeholder. 
Select students.*, classes.*
From students
Left [outer] Join classes
On students.cls_id = classes.id;
--The class is the main table, all data are displayed, if there is no qualifying in the students with null placeholder

right outer join: the left table will prevail, the left table all data will enter the result, there is no right table to match the criteria of the data with null placeholder. 
Select students.*, classes.*
From students
Right [outer] Join classes
On students.cls_id = classes.id;
--take students as the main table and all the data will be displayed. If there is no data in the classes table, a null placeholder is used. 

10 self-correlating:
The following conditions: Design province-City-district-County ... Of the database,
Province: Province:id Ptitle Province number, province name
City: City:id ctitle proid City number, city name and province
area: Area:id atitle Citid District Number, zone name and city number

we found that a province has multiple municipalities, and a city has multiple districts and counties. This creates three tables with essentially the same structure. 
and when it comes to multi-table operations, the difficulty is actually very large. 
so this is often the case with self-coupling. 

We change the table structure: Only one table is built for the provinces and cities:
Area:aid, Atitle, PID
Number name superior number for province and municipality PID is null

In this way we want to correlate the query to the subordinate data, need to use the self-correlation. 
To Create a table:
CREATE table area (
Aid int PRIMARY KEY,--own number
atitle varchar (20),--Own name
PID INT--The number of the superior
        );

Self-correlating statements:
Select City.*
 from area as City
INNER join area as province
On city.pid = province.id;
where province.atitle = "Shanxi province"
--Query all cities under the jurisdiction of Shanxi Province

Select dis.*
 from area as City
INNER join area as dis
On dis.pid = City.id
where city.atitle = "Guangzhou";
--Query all the areas under the jurisdiction of Guangzhou

11 sub-query:
Common keywords:
in (): Where column in () is present in parentheses to match the condition
any|some (): Where column = any () in parentheses
all (): Where column = All () column matches all inside
nesting Another SELECT statement in a SELECT statement

Sub-queries are divided into:
scalar subquery: A subquery Returns a single row of data (one column)
columns Query: A subquery Returns a column (multiple rows and columns)
Row subquery: A subquery returns a row (one row, multiple columns)
table-level subquery: A subquery returns a table (multiple rows and columns)

scalar Quantum query:
--Query students with a class greater than the average age
SELECT *
From students
where Students.age > (
Select AVG (age)
From students
            )

column-level subqueries:
--Query all student information for class named Python
SELECT *
From students
where students.cls_id in (
Select ID
From classes
where classes.name= "Python"
        );

row-level subqueries:
--Check for students with the highest class of age and Height
SELECT *
From students
where (age, height) in (
select Max (age), max (height)
From students
            );

Table-Level sub-query:
--Query student and class correspondence information
select * FROM (
Select Stu.*,pys.name as Clsname
From students as Stu
INNER JOIN Pythons as Pys
On stu.cls_id = Pys.id
) as T1;

many subqueries can be avoided and replaced with table joins. 
It is recommended to use a multi-table connection with clear statements and faster query times. 

MySQL Query SELECT statement Rollup