05-mysql中的查询(第一章)

准备数据，创建数据表

• 创建学生信息表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('男','女','人妖','保密'),
  cls_id int unsigned default 0
  );

• 创建班级表classes

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

• 准备数据

  insert into students values
  (0,'小红',18,180.00,2,1),
  (0,'小静',18,180.00,2,2),
  (0,'张三',29,185.00,1,1),
  (0,'李四',59,175.00,1,2),
  (0,'韩梅梅',38,160.00,2,1),
  (0,'凤姐',28,150.00,4,2),
  (0,'小芳',18,172.00,2,1),
  (0,'尔康',36,NULL,1,1),
  (0,'王五',27,181.00,1,2),
  (0,'亦菲',25,166.00,2,2),
  (0,'银星',33,162.00,3,3),
  (0,'香瓜',12,180.00,2,4),
  (0,'周杰',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');

• 查询所有的字段

  select * from students;

  

• 查询指定的字段

  --语法：select 列名1,列名2,..... from 表名;
  select name,age,height from students;

  

• 在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中

  --语法：select 字段1 as 别名,字段2 as 别名,... from 表名;
  select name as 姓名,age as 年龄 from students;

  

• 查询的完整格式

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

条件查询where

• 使用where自居对表中的数据进行筛选，结果为True的行会出现在结果集中

  select * from 表名 where 条件;

  例如查询id是4的学生的信息

  select * from students where id=4;

  

• where后面支持多种运算符，进行条件的处理

  • 比较运算符
  • 逻辑运算符
  • 模糊查询
  • 范围查询
  • 空判断

• 比较运算符

  • 等于: =

  select * from students where id=4;

  

  • 大于: >

  select * from students where age>18;

  

  • 大于等于: >=

  select * from students where age>=18;

  

  • 小于: <

  select * from students where age<18;

  

  • 小于等于: <=

  select * from students where age<=18;

  

  • 不等于: != 或 <>

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

  

• 逻辑运算符

  • and且

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

  

  • or或者

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

  

  • not非

  select * from students where not age=18;

  

• 模糊查询

  • like

  • %表示任意多个字符

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

  

  • _表示一个任意字符

  select * from students where name like '周_';

  

• 范围查询

  • in表示在一个非连续的范围内

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

  

  • between….and….表示在一个连续的范围内

  select * from students where age between 18 and 30;

  

• 空判断

  • 判空is null

  select * from students where height is null;

  

  • 判断非空is not null

  select * from students where height is not null;

  

• 优先级

  • 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符、
  • and比or先运算，如果同时出现并希望先算or，需要结合()使用