MySQL statement collation (ii)

Prepare database before operation--create DATABASE python_test_1 Charset=utf8; --Using the database-use python_test_1; --Students table--CREATE TABLE students (--     ID int unsigned primary key auto_increment not NULL,--     name varchar de Fault ',--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 (in) not null--); --Query --Query all fields --select * from table name;SELECT * from students; --Query the specified field --Select column 1, column 2,... from table name;Select Name,age from students; --use as to alias a field --Select field as name .... from table name;Select name as "Name", age from students; --Select table name. field .... from table name;Select Students.name,students.age from students; --can be used as a nickname for the table --Select alias. Field .... from table name as alias;Select S.name, s.age from students as s; --Elimination of duplicate rows (gender-check) --distinct fieldSelect distinct gender from students; --Conditional query --Comparison operators --select .... from table name where ... .. -->--Query for information older than 18 years old select * from students where age > 18; --<--Query for information younger than 18 years old select * from students where age < 18; -->=,<=--Query information less than or equal to 18 years old Selec * Tfrom students where age <= 18;         -- =--Query the name of all students aged 18 years SELECT * from students where age = 18; --! = or <>--Check the names of all students who are not 18 years old--select * from students where age! = 18; --select * from students where age <> 18; --Logical operators -- and--So student information between 18 and 28 select * from students where age >-age < 28; -Women over 18 years old select * from students where age > and gender = "female"; --or--More than 18 or taller than 180 (inclusive) SELECT * from students where age > or height >= 180; -- not--not in the range of women over the age of 18--select * from students where not (age>18 and gender=2);            SELECT * from students where isn't (age > Gender = "female"); SELECT * from students where isn't age > gender = "female"; --Fuzzy query (where name like the data to query) -- like --% replacement of any --_ Replacement of 1--Query names starting with "small" select * from students where name ' small% ';        --Query name with "small" all names select * from students where name "% small%";        --The query has a name of 2 words SELECT * FROM students where name is like ' __ ';        --Query has a name of 3 words SELECT * FROM students where name is like "___"; --Query the name of at least 2 characters select * from students where name is like "__%"; --Range query --In (1, 3, 8) within a discontinuous range--Query the name of the age of 18 or 34 select * from students where ages in (18,34); -not within non-contiguous range --age is not 18 or 34 years old informationSELECT * from students where age is not in (18,34);--Between ... and ... Expressed in a contiguous range--Query information between 18 and 34 select * from students where age between and 34; --not between ... and ... Indicates that it is not in a contiguous range--Query for information that is not between 18 and 34 select * from students where ages not between and 34; --Null judgment --Empty is null--Query height for empty information select * from students where the height is null;  --a non-null is NOT NULLSELECT * from students where height is not null;--Sort--Order By field --ASC --ASC arranges from small to large, ascending --desc --desc from large to small sort, i.e. descending--query men aged between 18-34 years old, from small to large to sort --ORDER BY ASC DescSELECT * from students where age between and the order by age ASC; --query for women aged between 18-34 years, height from high to low sort select * from students where age between and gender = "female" ORDER by height desc; --ORDER by multiple fields--query for women aged between 18-34 years old, height from high to short sort, if the height of the same case according to age from small to large sort select * from students where ages between and gender = "female"    ORDER BY height desc,age asc; --If the age is the same then follow the ID from the big to the small sort select * from students where age between and gender = "female" ORDER by height desc,age ASC , id desc; --Aggregation function grouping function --Total --count ()--Query Male How many people select count (*) from students where gender = "male"; --Maximum value --MAX ()--Query The maximum age of select Max from students; --Query The maximum height of a female select Max from students where gender = "female"; --Minimum value --min ()Select min (age) from students; --Summation --sum ()--Calculates the sum of the ages of all select sum (age) from students; --Average --AVG ()--Calculates the mean age of select AVG (ages) from students;--Calculates the mean age sum (ages)/count (*) Select SUM (age)/count (*) from students; --Rounded round (123.23, 1) reserved 1 decimal places--Calculates the average age of all, retains 2 decimal places select round (AVG (age), 2) from students; --Calculate the average height of the male reserved 2 decimal places select round (avg (height), 2) from students where gender = "male"; --Group --Group by--Search all genders by gender subgroup Select gender from students group by gender; #以谁分组就在select中写什么字段 --Select Group field from table name Group by Group field;--Calculate the number of persons per gender select Gender,count (*) from students group by gender; --Group_concat (...)--Query the name of the same sex select Gender,group_concat (name) from the students group by gender; --Query the average age of each group of genders select Gender,avg from students group by gender; -having (note that having and group by is used in conjunction with the aggregate function)--Query the gender of the average age over 30 years, and the name Select AVG (ages) from students;    Select gender from students group by gender have AVG (age) > 30; --Query information for more than 2 people per gender select gender from students group by gender have count (*) > 2; --The role of the WITH Rollup Summary (learn)Select Gender,count (*) from students group by gender with rollup; --Pagination --Limit start, Count --limit on the last side (note) Start Page Calculation: (page count-1) * Number of pages per page--Limit the number of data queried--Query the first 5 data select * From students limit 0, 5; --Show 2 per page, display the information on page 6th, according to the age of small to large order select * from Students ORDER by aging ASC limit 10, 2; --Connection Query --INNER join ... on --Select ... from table a INNER join table B;    --query for students with class and class information     select * from students inner JOIN classes on students.cls_id = classes.id;    --Display Name, class     select Students.name as required, Classes.name from students inner JOIN classes on students.cls_id = classes.id;    --name the data table  & Nbsp;  select * FROM students as s inner joins classes as C on s.cls_id = c.id;    --query has the ability to Students in the class and class information, display all the information of students students.*, only show class name Classes.name.    select Students.*,classes.name from Students inner JOIN classes on students.cls_id = classes.id;    --in the above query, the class name is displayed in column 1th    & Nbsp;select classes.name, Students.*from students inner JOIN classes on students.cls_id = classes.id;   & nbsp;--query can correspond to the class of students and class information, sorted by class name      select classes.name, students.*from students inner JOIN C lasses on students.cls_id = classes.id ORDER by classes.name;    --when the same class, according to the student's ID from small to large sort     select classes.name, students.* From students inner JOIN classes on students.cls_id = classes.id ORDER by Classes.name Asc,students.id asc;  --left join on--Query the corresponding class information for each student select * from students left JOIN classes on students.cls_id = Classes.id; --Query for students who do not have class information select * from students left JOIN classes on students.cls_id = classes.id where classes.name is null; --Right join on--Swap the data table name with the LEFT JOIN to complete SELECT * from students right JOIN classes on students.cls_id = Classes.id; --sub-query --Scalar quantum query: The result returned by a subquery is one data (one row) --Column query: The result returned is a row (more than one row) --ROW subquery: The result returned is a row (one row multiple columns)--Query for information above average height (height) Select avg (height) from students;    SELECT * from students where height > (select AVG (height) from students);    --Query student's class number can correspond to student name select ID from classes; SELECT * from students where cls_id in (select ID from classes);

MySQL statement collation (ii)