查询命令

模糊查询

• 关键字LIKE
• 通配符% _
  

非空 NOT NULL

限制条目

去重

不显示重复数据

分组

• 关键字 GROUP BY
• 只能展示分组字段和聚合函数
• HAVING是聚合函数过滤后条件查询,等同于过滤前的LIKE
• 聚合函数
  统计count求和sum平均avg最大max最小min
  

命令加强练习

-- 为空
SELECT*
FROM gy_user
WHERE weichat IS NULL;
-- 不为空
SELECT*
FROM gy_user
WHERE weichat IS NOT NULL;
-- 排序升序
SELECT*
FROM gy_user
ORDER BY sno;`
-- 排序降序
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;

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

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

-- 只去重真实姓名,显示其他选项
SELECT *
FROM gy_user
GROUP BY realname;

-- 统计条目数
SELECT COUNT(sno)
FROM gy_user;

-- 统计最大
SELECT MAX(sno)
FROM gy_user;

-- 最小
SELECT MIN(sno)
FROM gy_user;

-- 平均
SELECT AVG(sno)
FROM gy_user;

-- 求和
SELECT SUM(sno)
FROM gy_user;

练习题

-- 30.查询真实姓名,姓氏为张王李杨,按年龄倒序
SELECT realname,age
FROM gy_user
WHERE realname LIKE '张%' OR realname LIKE '王%' OR realname LIKE  '李%' OR realname LIKE  '杨%'
ORDER BY age DESC;

-- 31.查询用户名包含guoya的用户,根据真实姓名去重
SELECT DISTINCT realname,sname
FROM gy_user
WHERE sname LIKE '%guoya%';

-- 32.查询用户id在50 60 70 80,并真实姓名不为空的用户
SELECT *
FROM gy_user
WHERE sno IN (50,60,70,80) AND realname IS NOT NULL;

-- 33.查询用户表 年龄在20.30.40 姓名包含张,吴,显示真实姓名,年龄,地址
SELECT realname,age,address
FROM gy_user
WHERE age IN (20,30,40) AND (realname LIKE '%张%' OR realname LIKE '%吴%');

-- 34.查询用户表编号大于50 地址在上海   展示用户名,年龄,地址前100条数据
SELECT sname,age,address
FROM gy_user
WHERE sno>50 AND address LIKE '%上海%'
LIMIT 100;

-- 35.查询用户表姓名为单字 对真实姓名去重,根据年龄倒序
SELECT DISTINCT realname
FROM gy_user
WHERE realname LIKE '_'
ORDER BY age DESC;

-- 36.查询用户表 地址不为空,对用户名去重,显示前30条
SELECT DISTINCT sname
FROM gy_user
WHERE address IS NOT NULL
LIMIT 30;

-- 37.查询用户表 手机号码地址不为空,并且用户编号大于50,只显示第10-20条数据
SELECT *
FROM gy_user
WHERE sno>50 AND address IS NOT NULL AND phone IS NOT NULL
LIMIT 10,10;

-- 38.查询用户表,年龄大于20班级类型为1,以年龄正序id倒序排列
SELECT*
FROM gy_user
WHERE age>20 AND class_type=1
ORDER BY age ASC,sno DESC;

-- 39.查询微信号 手机号不为空 姓名包含王,以年龄倒序,显示真实姓名去重
SELECT DISTINCT realname,weichat
FROM gy_user
WHERE realname LIKE '%王%' AND phone IS NOT NULL
ORDER BY age DESC;

-- 40.查询用户编号100-500区间,微信号不为空,年龄倒序,显示用户名去重
SELECT DISTINCT realname
FROM gy_user
WHERE weichat IS NOT NULL 
ORDER BY age DESC
LIMIT 100,400

-- 求每一个班级的总年龄
SELECT SUM(age),class_type
FROM gy_user
GROUP BY class_type;

-- 求每一个班级的平均年龄
SELECT AVG(age),class_type
FROM gy_user
GROUP BY class_type;

-- 求每一个学历的最大年龄
SELECT SUM(age),education
FROM gy_user
GROUP BY education;

-- 求每一个学历的平均年龄
SELECT AVG(age),education
FROM gy_user
GROUP BY education;

-- 查询各班级平均年龄大于20的班级是
SELECT class_type, AVG(age)
FROM gy_user
GROUP BY class_type
HAVING AVG(age)>20;
-- 查询各班级最小年龄大于2的班级
SELECT MIN(age),class_type
FROM gy_user
GROUP BY class_type
HAVING MIN(age)>2;


-- 查询各班级平均年龄小于23的班级
SELECT AVG(age),class_type
FROM gy_user
GROUP BY class_type
HAVING AVG(age)<23;


-- 查询各班级年龄总和小于500的班级
SELECT class_type,SUM(age)
FROM gy_user
GROUP BY class_type
HAVING SUM(age)<500;