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