1. Data preparation and basic query (review)

Create database

create database python_test charset=utf8;

View database:

show databases;

  Use database:

use python_test;

Displays which database is currently in use:

select database();

  Create a data table:

create table student( 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', 'secrecy') default 'secrecy', cls_id int unsigned default 0, is_delete bit default 0 );

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

View data sheet:

show tables;

Shows how to create:

show create table student;

Insert data:

insert into studuent values (0,'Xiao Ming',18,168.00,2,1,0), (0,'Xiao Huang',17,175.00,1,2,0), (0,'Xiao Hong',14,177.00,2,3,0), (0,'Little Han',11,180.00,3,4,0), (0,'Small eight',12,187.00,3,5,0), (0,'Xiao Jiu',13,182.00,4,6,0), (0,'Small ten',18,188.00,3,7,0), (0,'Xiaozhi',17,186.00,2,8,0), (0,'Junior one',10,188.00,2,9,0), (0,'waiter',15,182.00,3,9,0), (0,'the other woman',18,184.00,2,6,0), (0,'Xiao Si',19,185.00,4,4,0), (0,'Small five',13,190.00,2,3,0), (0,'Xiao Liu',14,189.00,2,4,0), (0,'Xiao Qi',15,178.00,2,5,0), (0,'eleven',15,167.00,1,7,0), (0,'twelve',18,176.00,1,2,0);

insert into classes values (0, "python01 stage"), (0, "python02 stage"), (0, "python04 stage");

The databases of student s and classes are obtained respectively:

 

  Custom query:

select id as Serial number,name as full name,height as height from student;

  At the same time, you can name the table through as to achieve the same effect

select s.name,s.age from student as s;

Eliminate duplicate lines: the distinct field. Take the query gender as an example:

select distinct gender from student；

2. Query criteria

When the amount of data is large, do not use select * from student; It will occupy too much memory; Therefore, conditional query is adopted;

2.1 comparison operator

Syntax: Select... From table name where

The comparison operator > > = < = is OK

Query information older than 18

select * from student where age>18;

select name,gender from student where age>18;

  Note the following for the equal sign:=

select * from student where age=18;

2.2 logical operators

Logical operator and or not

18-28 Year old student information

select * from students where age>18 and age<28;

18 Over the age of or over 180 in height

select * from students where age>18 or height>180;

Women not over 18

select * from students where not (age>18 and gender='female');
select * from students where not (age>18 and gender=2);

Not over 18 years old and female
select * from students where not age>18 and gender='female';

One example:

  2.3 fuzzy query

Fuzzy query like rlike

like:% replace one or more_ Replace 1 [low efficiency]

Find names that start with small

select name from student where name like "Small%";

  All names with small in the query name

select name from student where name like "%Small%";

The results are the same as above;

The query has two word names: two underscores

select name from students where name like "__"

Query a three word name: three underscores

select name from students where name like "___"

Query a name of at least two words

select name from student where name like "__%"

rlike: regular expression lookup

Find names that start with a small: ^ indicates the beginning and the middle*

select name from students where name rlike "^Small.*";

Find names that begin and end with week:$

select name from student where name rlike "^week.*Lun $";

  2.4 range query (discontinuous query)

Find names aged 12, 18, 34

select name from student where age=12 or age=18 or age=34;

select name from student where age in (12,18,34);

Not the names of 12, 18, 34

select name from students where age not in (12,18,34);

Continuous query between and

--Find age 18～34 Year old name

select name from student where age between 18 and 34;

--Find age not 18～34 Year old name

select name from student where age not between 18 and 34;

 

Null judgment

Judge whether the height is empty

select * from student where height is null;

3. Sorting

order by field [sort by component by default]

asc ascending from small to large (default) ascend

desc descending descend

Query men aged 18-34, sorted by age.

select * from student where (age between 18 and 34) and gender=1;

select * from student where (age between 18 and 34) and gender=1 order by age desc;

Query women aged 18-34, sorted by height from large to small.

select * from student where (age between 18 and 34) and gender=2 order by height desc;

When the values are the same, the primary key (id) asc row is used;

order by multiple fields

Query the height of women aged 18-34 from high to low. If the height is the same, they are sorted by age from small to large. If the age is the same, from big to small according to id

select * from student where (age between 18 and 34) and gender=2 order by height desc,age asc,id desc;

  Look at the follow-up under the same conditions as before;

According to age from small to large, height from high to low;

select * from student order by age asc, height desc;

  4. Aggregate function

Total count

How many men are there

select count(*) as Number of men from student where gender=1;

max,min general

Who is the oldest

select max(age) from student;

Query the maximum height of women

select max(height) from student where gender=2;

sum: sum

Ask for the sum of all people's ages

select sum(age) from student;

Average

Find the average age of everyone.

select avg(age) from student;

be equal to   avg(age)     sum(age)/count(*)

round(data,2) to two digits

Find the average age of everyone.

select round(avg(age),2) from student;

For decimal storage, it is recommended to multiply by 100 to expand to an integer to ensure accuracy

  5. Grouping

group by syntax: grouping data before querying

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

The number of persons of each sex in the statistics department is divided according to gender.

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

Who are there by gender in the statistics department.

group_concat(name,age...) can view a variety of

select gender,group_concat(name) from student group by gender;

By sex, the age distribution of each sex in the statistics department

select gender,avg(age) from students group by gender;

Male details

select gender,group_concat(name,' ',age,'',id) from student where gender=1 group by gender;

having: condition judgment for grouping:

Query gender with average age over 30, and name.

select gender,group_concat(name) from student group by gender having avg(age)>16;

  Only groups with an average age greater than 16 are displayed

Query information about more than two people of each gender.

select gender,group_concat(name) from student group by gender having count(*)>2;

The difference between where and having:

• where use filtering before grouping
• having is used for filtering after grouping