Prepare data, create data tables

• Creating Student Information Table students

  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','Simon?','secrecy'),
  cls_id int unsigned default 0
  );

• Create class tables

  create table classes(
  cls_id int unsigned primary key auto_increment not null,
  cls_name varchar(10) default ''
  )

• Preparing data

  insert into students values
  (0,'Xiaohong',18,180.00,2,1),
  (0,'Xiaojing',18,180.00,2,2),
  (0,'Zhang San',29,185.00,1,1),
  (0,'Li Si',59,175.00,1,2),
  (0,'Han Meimei',38,160.00,2,1),
  (0,'Miss Luo Yu Feng',28,150.00,4,2),
  (0,'Xiaofang',18,172.00,2,1),
  (0,'Er Kang',36,NULL,1,1),
  (0,'Wang Wu',27,181.00,1,2),
  (0,'Yifei',25,166.00,2,2),
  (0,'silver star',33,162.00,3,3),
  (0,'Muskmelon',12,180.00,2,4),
  (0,'Zhou Jie',34,176.00,2,5);

  insert into classes values
  (0,'mysql-01'),
  (0,'mysql-02'),
  (0,'mysql-03'),
  (0,'mysql-04'),
  (0,'mysql-05'),
  (0,'mysql-06');

• Query all fields

  select * from students;

  

• Query the specified fields

  --Grammar: select List 1,List 2,..... from Table name;
  select name,age,height from students;

  

• In the column name section after select, you can use as to alias the column, which appears in the result set

  Grammar: select field 1 as alias, field 2 as alias,... from table name;
  select name as name, age as age from students;

  

• Complete Format of Queries

  SELECT select_expr [,select|_expr,...] [      
      FROM tb_name
      [WHERE Conditional Judgment]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE Conditional Judgment]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
  ]

Conditional Query where

• Using where to filter the data in the table, rows with the result of True appear in the result set

  select * from table name where condition;

  For example, query the information of students whose id is 4

  select * from students where id=4;

  

• where is followed by multiple operators for conditional processing

  • Comparison operator
  • Logical Operator
  • Fuzzy Query
  • Scope query
  • Empty Judgment

• Comparison operator

  • Equivalent to:=

  select * from students where id=4;

  

  • More than: >.

  select * from students where age>18;

  

  • More than or equal to: >=

  select * from students where age>=18;

  

  • Less than:<

  select * from students where age<18;

  

  • Less than or equal to:<=

  select * from students where age<=18;

  

  • Not equal to:!= or <>

  select * from students where age!=18;
  select * from students where age<>18;

  

• Logical Operator

  • And and

  select * from students where age>18 and gender=1;

  

  • Or or

  select * from students where age>18 or gender=2;

  

  • not non

  select * from students where not age=18;

  

• Fuzzy Query

  • like

  • % Represents any number of characters

  select * from students where name like 'Small%';

  

  • _ Represents an arbitrary character

  select * from students where name like 'week_';

  

• Scope query

  • In is expressed in a discontinuous range

  select * from students where age in(12,18,25,34);

  

  • between... and... Represents in a continuous range.

  select * from students where age between 18 and 30;

  

• Empty Judgment

  • Empty judgment is null

  select * from students where height is null;

  

  • Judging non-null is not null

  select * from students where height is not null;

  

• priority

  • The order of priority from high to low is: parentheses, not, comparison operator, logical operator,
  • And first arithmetic than or, if it appears at the same time and wants to arithmetic or first, it needs to be combined with ()