mysql--Data Lookup

-  Basic Query |  query all columns  select * from students;|  Specify criteria query  select * from  students where is_delete=1;|  query a specified column  select name, gender from  The order of the students;|  fields  select gender, name from students;| as the use of #  while querying the specified column, Modify Field name display select  field name  as  display name  from  list;select name as  name, gender  as  sex  from students;#  Temporarily modifies table names in SQL statements, reducing the amount of code Select students.name, students.gender  from students;select s.name, s.gender from students as;|  Eliminate duplicate rows   distinct  Fields  select gender from students; select distinct gender  from students;-  conditions |  comparison operators >select * from students where age  > 18;<=select * from students where age <= 18;|  Logical operator Andselect * from students where age>18 and gender=2;orselect * from students  Where age>18 or height>=180;notselect * from students where not   (age>18 and gender=2);|  fuzzy query--like "%" to find a name that starts with a character select name from  students where name like  "Small%"; " _ "   query has a few words of the name select name from students where name like " __ "; select name from students where name like  "___";--rlike (support Regular) select  name from students where name rlike  "^ Week";select name from  students where name rlike  "^ Week. * Lun $";|  range query-- in  represents a non-contiguous range select name  from students where age in  (18,34);-- not inselect name   age from students where age not in  (18,34), "attached: About two numbers must beMust be contained in the table "-- between ...and ...  means within a continuous range select * from students where  age between 18 and 34;-- not between ...and.   No parentheses required Select * from students where age not between 18 and  34; "left and right two numbers no limit" |  null judgment--judgment is null   judgment is empty select * from students  where height is null;--Judge is not null  judgment is not empty select * from students  where height is not null;-  sort order by  Field acs  ascending   (no ASC also default ascending)  select * from students where  (age between 18 and 34)   and gender=1 order by age; select * from students where  (age &NBSP;BETWEEN&NBSP;18&NBSP;AND&NBSP;34)  and gender=1 order by age asc;desc  Descending  select * from students where  (age between 18 and 34)  and gender=2 order by  height desc;#  which sort operation before, the first to do which age from small to large, height from high to short sort select * from students order  by age asc, height desc; first to height descending, if the weight is the same, age ascending select * from students where   (age between 18 and 34)  and gender=2 order by height  Total number of desc, age asc;-  aggregate functions   (statistics)  count select * from students  Where gender=1; select count (*)  as  number of boys  from students where  Gender=1; select count (*)  as  number of girls  from students where gender=2;  # * efficiency is higher than the selected field  select count (name)  as  Number of men  from students where  Gender=1, Max  max select age from students; select max (age)  from  Students; selecT max (height)  from students where gender=2; min  min select sum (age)  from students; sum  sum select sum (age)  from students; average  avg  Select sum (age)/count (*)  from students; select avg (age)  from students; rounding  round (number, reserved digits)  select round (AVG (age)  ,2)  from students where gender=1 ;  calculates the age of everyone, retains two decimal places  select round (avg (age)  ,2)  from students;-  Group--group  By the Select field, the data is de-weighed into a group according to the gender grouping select * from students group by gender; #  Fatal error SELECT&NBSP;GENDER&NBSP;FROM&NBSP;STUDENTS&NBSP;GROUP&NBSP;BY&NBSP;GENDER;--GROUP_ Concat Remove Data Select gender, group_concat (name) from the other fields corresponding to the group  from studentsgroup by  Gender;select gender, group_concat (name, age)  from studentsgroup by gender; --having additional queries the average age of each gender select gender, avg (ages)  from studentsgroup by gender; query average age over 30 years old sex Select gender, group_concat (name)  from studentsgroup by gender having avg (age) >30;--with rollup--in the last new row, Records the sum of all the numbers in the current column select gender, count (*)  from students group by gender with  rollup;-Paging (limit start, count     0 = first) 1-operation query first 5 data  select  * from students limit 0,5;  Querying id4-8 Data  select * from  students limit 3,5;  Show 2 per page, p/a page  select * from students limit  0,2;----Quantity per page * (page-1)  select * from students limit 2,2; select *  from students limit 4,2;  Show 2 per page, display the information on page 3rd, sorted by age from small to large  select * from  students limit 2* (4-1),2;  failure  select * from students limit  6,2 order by age asc;  Failure   select * from students order by age asc limit  6,2 ;2-speed up Find-  even table query-inner join ...on. Inside connection, take intersection--to Inner join ...on ... Understanding of select * from goods inner join goods_brandson goods.id=goods_brands.id; Analysis: Find data from a range of goods, which is the intersection of Goods_brands, and must meet the conditions on the back-show fields as required Select goods.name from goods  inner join goods_brandson goods.id=goods_brands.id;--The Request field is displayed, insert the other field data select b.* in the table, G.name from goods as g inner join goods_brands as bon g.id= b.id;--a short name to the data sheet select g.name from goods as g inner join goods_brands  as bon g.id=b.id;-left join ...on ... Left join to the left table is the main--left join, based on left table, matching the other table data,--the number of NULL representation select b.*,g.name from goods as g  left join goods_brands as bon g.id=b.id;--clear null for data SElect b.*,g.name from goods as g left join goods_brands as  bon g.id=b.id where b.name is not null;-self-correlating (to be supplemented) create table areas (     aid int primary key,    atitle varchar (20),     pid int); check all provinces  select * from areas where pid  is null;  query all cities in Shandong province  select * from areas as city inner join  areas as province on city.pid=province.aid where province.atitle= "Shandong Province"; Database import data Command--source  file .sql-  subquery-  scalar subquery Returns a single row of data (one column) select * from students  Height > avg (height);  failure  select * from students where height  > select avg (height)  from students;   failure  select * from  students where height >  (select avg (height)  from students);-  column-level subquery Returns the result of a column (more than one row) select  name from classes where id in  (select cls_id from students);-   Row-level subquery Returns the result is a row (one row, multiple columns) select * from students where  (height,age)  =  (select  max (height), max (age)  from students);-  combination union-- , automatically handles coincident      select nickname from a union select name from b--  combination, do not handle coincident      select nickname from a union all select name from  b

Finding the mysql--data