字段

• 字段为空
  SELECT*
  FROM gy_user
  WHERE sname IS NULL;

• 字段不为空
  SELECT *
  FROM gy_user
  WHERE sname IS NOT NULL;

查询升序降序

• 升序
  SELECT *
  FROM gy_user
  ORDER BY age ;
• 降序
  SELECT *
  FROM gy_user
  ORDER BY age DESC ;
• 带判断条件降序排列
  SELECT *
  FROM gy_user
  WHERE realname LIKE '%吴%'
  ORDER BY age DESC ;

指定

• 指定限制前10条
  SELECT *
  FROM gy_user
  LIMIT 10;

• 指定查询100-110 limit数字
  SELECT *
  FROM gy_user
  LIMIT 100,10;

• 指定查询 50-80条
  SELECT *
  FROM gy_user
  LIMIT 50,30;

• 指定字段去重
  SELECT DISTINCT realname
  FROM gy_user ;

顺序

• SELECT 显示内容 3
• DISTINCT 4
• FROM 表名 1
• WHERE 判断条件 2
• ORDER BY 5
• LIMIT 6

查询

image.png

image.png

统计

• 1. 统计条目数 count()
     SELECT COUNT(sno)
     FROM gy_user
• 2.统计最大
  SELECT MAX(age)
  FROM gy_user
• 3. 统计最小
     SELECT MIN(age)
     FROM gy_user
• 4. 求平均值
     SELECT AVG(age)
     FROM gy_user
• 5.求和
  SELECT SUM(age)
  FROM gy_user

举例

• 1.求每一个班级的 总年龄
  SELECT SUM(age),class_type
  FROM gy_user
  GROUP BY class_type;
  • 2.求每一个班级的平均年龄
    SELECT AVG(age),class_type
    FROM gy_user
    GROUP BY class_type;
  • 3.求每一个学历的最大年龄
    SELECT MAX(age),education
    FROM gy_user
    GROUP BY education;
  • 4.求每一个学历的 平均年龄
    SELECT AVG(age),education
    FROM gy_user
    GROUP BY education;

举例查询

• 1. 查询各班级中最大年龄大于32的班级是
     SELECT class_type ,MAX(age)
     FROM gy_user
     GROUP BY class_type
     HAVING MAX(age)>32;
• 2.查询各班级中平均年龄大于22的班级是
  SELECT class_type,AVG(age)
  FROM gy_user
  GROUP BY education
  HAVING AVG(age)>22;
• 3.查询不同学历的各自人数
  SELECT COUNT(sno) ,education
  FROM gy_user
  GROUP BY education
• 4. 查询不同学历 最小年龄 小于30的学历是
     SELECT education,MIN(age)
     FROM gy_user
     GROUP BY education
     HAVING MIN(age)<30;
• 5.查询各班级中平均年龄大于20的班级是
  SELECT AVG(age),class_type
  FROM gy_user
  GROUP BY class_type
  HAVING age>2
• 6. 查询各班级中最小年龄大于2的班级是
     SELECT class_type,MIN(age)
     FROM gy_user
     GROUP BY class_type
     HAVING MIN(age)>2;
• 7.查询各班级中平均年龄小于23的班级是
  SELECT class_type,MIN(age)
  FROM gy_user
  GROUP BY class_type
  HAVING MIN(age)<23;
• 8. 查询各班级 中年龄总和小于500的班级是
     SELECT class_type ,SUM(age)
     FROM gy_user
     GROUP BY class_type
     HAVING SUM(age)<500;

数据库基础

• 1.模糊查询

  
  
  image.png

• 2.非

  
  
  image.png

• 3.null

• 4.in

  
  
  image.png

• 5.添加注释

  
  
  image.png

• 6.排序

  
  
  image.png

• 7.limit

  
  
  image.png

• 8.去重

  
  
  image.png

• 9.分组

  
  
  image.png