– preparation before database operation
– create database
– create database python_test_1 charset=utf8;

– using databases
– use python_test_1;

– students table
– create table students(
– id int unsigned primary key auto_increment not null,
– name varchar(20) default ",
– age tinyint unsigned default 0,
– height decimal(5,2),
– gender enum("male", "female", "neutral", "confidential") default "confidential",
– cls_id int unsigned default 0,
– is_delete bit default 0
– );

– classes table
– create table classes (
– id int unsigned auto_increment primary key not null,
– name varchar(30) not null
– );

– query exercise
– query all fields
– select * from table name;
select * from students;

-- Query specified fields
-- select Column 1,Column 2,... from Table name;
select name,age from students;


-- Use as Alias fields
-- select field as Name.... from Table name;
select name as "Full name",age as a from students;



-- select Table name.field .... from Table name;
select students.name from students;


-- Can pass as Alias table
-- select alias.field .... from Table name as alias;
select s.name from students as s;




-- Eliminate duplicate lines(Checking gender)

-- distinct field   #Don't remember.
select distinct  gender from students;

– condition query
– comparison operator
– select … . from table name where ..
– >
– search for information older than 18
select * from students where age > 18;

    -- <
    -- Query information younger than 18 years old
    select * from students where age < 18;



    -- >=
    -- <=
    -- Query information younger than or equal to 18 years old
    select * from students where  age <= 18;

    -- =
    -- Find out the names of all students aged 18
    select * from students where age = 18;


    -- != perhaps <>
    -- Search the names of all students who are not 18 years old
    select * from students where age != 18;  #Please remember this

    -- select * from students where age <> 18;



-- Logical operators
    -- and
    -- 18 All student information between and 28
    select * from students where age > 18 and age < 28;


    -- 18 Women over the age of
    select * from students where age > 18 and gender = "female";


    -- or
    -- 18 Above or over 180 in height(Contain)Above
    select * from students where age > 18 or height >= 180;


    -- not
    -- Information not in the range of women over 18
    -- select * from students where not (age>18 and gender=2);

    select * from students where not ( age > 18 and gender = 'female');  #In the future, if you don't understand the Chinese meaning of this sql statement, you can consult the project manager
    select * from students where not  age > 18 and gender = 'female';


-- Fuzzy query(where name like Data to query)
    -- like 
    -- % Replace any 
    -- _ Replace 1
    -- Query name with "Small" First name
    select * from students where name like 'Small%';


    -- Query names "Small" All names
    select * from students where name like '%Small%';


    -- Search for names with two words
    select * from students where name like "__";




    -- Search for names with three words
    select * from students where name like "___";

    -- Query names with at least 2 words
    select * from students where name like "__%";



-- Range query
    -- in (1, 3, 8)Indicates in a discontinuous range
    -- Search for names aged 18 or 34
    select * from students where age =18 or age = 34;
    select * from students where age in(18,34);


    -- not in Within the range of discontinuities
    -- Information that age is not 18 or 34
    select * from students where age not in(18,34);


    -- between ... and ...In a continuous range
    -- Query information between the ages of 18 and 34
    select * from students where age >= 18 and age <= 34;
    select * from students where age between 18 and 34;



    -- not between ... and ...Indicates not in a continuous range
    -- Query information that is not between 18 and 34 years old
    select * from students where age not  between 18 and 34;


-- Empty judgement
    -- Sentence blank is null
    -- Query information with empty height
    select * from students where height is null;


    -- Judgement is not empty is not null
    select * from students where height is not null;

Sorting
– order by field
– asc
– asc arranged from small to large, i.e. ascending
– desc
– desc sorting from large to small, i.e. descending
– query men aged between 18 and 34 by age
select * from students where (age between 18 and 34) and gender = "male" order by age asc;

-- For women aged between 18 and 34, the order of height is from high to low
select * from students where age between 18 and 34 and gender = "female" order by height desc;


-- order by Multiple fields
-- For women aged between 18 and 34, the order of height is from high to low, Rank by age if height is the same
select * from students where age between 18 and 34 and gender = "female" order by height desc,age asc;


-- If the age is the same, then according to id Sort from large to small
select * from students where age between 18 and 34 and gender = "female" order by height desc,age asc,id desc;

– functions grouped by aggregate functions
Total number
– count
– find out how many men there are
select count(*) from students where gender = "male";
select count(height) from students where gender = "male";

-- Maximum value
-- max
-- Query maximum age
select max(age) from students ;

-- Query the maximum height of women
select max(height) from students where gender = "female";


-- minimum value
-- min
select min(height) from students ;



-- Summation
-- sum
-- Calculate the sum of all people's ages
select sum(age) from students;


-- average value
-- avg
-- Calculate average age
select avg(age) from students;



-- Calculate average age sum(age)/count(*)
select sum(age)/count(age) from students;



-- Rounding round(123.23 , 1) Keep 1 decimal place
-- Calculate the average age of all people, keep 2 decimal places
select round(avg(age),2) from students ;


-- Keep 2 decimal places for calculating the average height of men
select round( avg(height) ,2 )from students where gender = "male";

Grouping

-- group by
-- Group by gender,Query all genders
select gender from students group by gender;


-- Count the number of people in each gender
select gender,count(gender) from students group by gender;

select gender,count(*) from students group by gender;

-- group_concat(...)
-- Search for names of the same gender
select gender,group_concat( name) from students group by gender;





-- Query the average age of each gender
select gender,avg(age) from students group by gender;



-- having(Be careful having and group by Continuous use having It is usually followed by aggregate function)
-- Search for gender with an average age of over 30 and name
--Group by gender,Queries grouped by gender,Gender groups with an average age over 30

select gender from students group by gender having avg(age) > 30 ; 



-- Query information with more than 2 people in each gender
select gender,group_concat(name) from students group by gender having count(*) > 2;




-- with rollup The role of aggregation(understand,Listen.)
--select gender,count(*) from students group by gender with rollup;
select gender,count(*) from students group by gender with rollup;

Paging
– limit start, count
– limit at the back (note) mysql dialect

Calculation of starting position of paging: (The number of pages-1)*Number of pages per page;

-- Limit the number of data queried
-- Query the first 5 data
select * from students limit 0,5;  #The first parameter starts from 0, and the second parameter is the number. Please note that

-- select * from students limit 5;

-- 2 per page, page 1
select * from students limit 0,2;



-- 2 per page, page 2
select * from students limit 2,2;

-- 2 per page, page 3
select * from students limit 4,2;

-- 2 per page, page 4
select * from students limit 6,2;


-- 2 per page, information on page 6, Sort by age
select * from students order by age asc limit 10,2 ; #jd will jump the data to the first page

– connection query
– inner join ... on
– select … from table A inner join table B;
– query the students and class information that can correspond to the class
Select * from students inner join classes
select * from students inner join classes on students.cls_id = classes.id;

select * from xxx where Data condition
select * from XXX group by Grouping having Grouping condition
select * from Table 1 inner join Table 2 on Connection condition(Table 1.field = Table 2.field) 


-- select * from students,classes where students.cls_id = classes.id;



-- Display name and class as required
select students.name,classes.name from  students inner join classes on students.cls_id = classes.id;


-- Name the data sheet
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;



-- query *，Only show the students and class information of the corresponding class in the class, and show all the information of the students students.Name classes.name.

select students.*,classes.name from students inner join classes on students.cls_id = classes.id;


-- In the above query, display the class name in column 1
select classes.name ,students.* from students inner join classes on students.cls_id = classes.id;


-- Query the students and class information that can correspond to the class, Sort by class name
select classes.name ,students.* from students inner join classes on students.cls_id = classes.id order by classes.name desc;



-- At that time, in the same class, according to the id Sort from small to large

select classes.name ,students.* from students inner join classes on students.cls_id = classes.id order by classes.name desc,students.id asc;



-- left join
-- Query the class information of each student
select * from students left join classes on students.cls_id = classes.id;




-- Query students without corresponding class information
select * from students left join classes on students.cls_id = classes.id where classes.name is null;



-- right join   on
-- Swap the name of data table with left join complete
select * from students right join classes on students.cls_id = classes.id;
select * from classes right join students on students.cls_id = classes.id;

– subquery
– scalar subquery: the result of subquery is a data (row by column)
- column subquery: the result returned is one column (one column and multiple rows)
- row subquery: the result returned is one row (one row and multiple columns)

-- Find out the information higher than the average height(height)
    ----1. Average height
    ----2. Height greater than average

    select avg(height) from students ;  --172


    select * from students where height > (select avg(height) from students );



-- Query the student's class number and the corresponding student name
select id from  classes;

select * from students where cls_id in(select id from  classes);


--Preparation before data operation
--Create database table
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);
--from sql Import data in file
-- source Specific address/areas.sql;
source areas.sql;


--How many provinces are there
select count(*) from areas where pid is null;

--Example 1: query all cities with the name of "Shanxi Province"
select aid from areas where atitle = "Shanxi Province";
select * from areas where pid = (select aid from areas where atitle = "Shanxi Province");

//Connect yourself

select a1.* from areas as a1 inner join areas as  a2 on a1.pid = a2.aid where a2.atitle = "Shanxi Province";


--Example 2: query all districts and counties with the name of "Guangzhou City"
select a1.* from areas as a1 inner join areas as  a2 on a1.pid = a2.aid where a2.atitle = "Guangzhou City";

select a1.* from areas as a1 inner join areas as  a2 on a1.pid = a2.aid where a2.atitle = "Hangzhou City";