4.数据库高级查询

1.去重，Like查询

# 去除重复的值DISTINCT
# 查询age不重复的值(某列)
SELECT DISTINCT age FROM teacher;

# 查询age不重复的值(某行)
SELECT DISTINCT id,name,age,scode,mScore FROM teacher;

# Like
SELECT * FROM teacher WHERE `name` LIKE 'weidong'
# like 带 %
# 查询teacher表中name字段值以w开头的name值
# %匹配任意长度的字符串
SELECT * FROM teacher WHERE `name` LIKE 'w%'

# 查询teacher表中name字段值以w开头并以g结尾的name值
SELECT * FROM teacher WHERE `name` LIKE 'w%g'

# 查询teacher表中name字段包含i字符的name值
SELECT * FROM teacher WHERE `name` LIKE '%i%'

# 查询teacher表中name字段以字符串“we”开始，“ong”结束，
# 并且两个字符串中间只有两个字符串的记录。
# we只能有两个字符ong

# like 带 _(下划线)
# 一个下划线代表一个字符
SELECT * FROM teacher WHERE `name` LIKE 'we__ong'

2.and查询

# AND 拼接多个条件,所有的条件必须满足

# 查询teacher表中id字段值小于5，并且 age 字段值为 25 的教师信息

SELECT * FROM teacher WHERE id < 5 AND age = 25;

# OR
# 查询 teacher 表中id字段值小于3，或者 age 字段值为 25 的教师信息
SELECT * FROM teacher WHERE id < 3 OR age = 25;

3.聚合查询

# 统计数据行数

# COUNT
SELECT COUNT(*) FROM teacher;

# SUM 求和
SELECT SUM(mScore) FROM teacher;

# AVG 求平均值 
SELECT AVG(age) FROM teacher;

# max  min 最大值  最小值

# 分数最高的人
SELECT MAX(mScore) FROM teacher;

# 分数最低的人
SELECT MIN(mScore) FROM teacher;

# 统计成绩大于60的教师有多少个？
SELECT COUNT(*) FROM teacher WHERE mScore > 60;

# 统计一个班级年龄、成绩的和
SELECT SUM(age),SUM(mScore) FROM teacher;

# 统计一个班级平均年龄 
SELECT SUM(age) / COUNT(*) FROM teacher;

4.排序查询

# 排序ORDER BY
# asc 升序
# desc 降序

# 按照年龄升序排序
SELECT * FROM teacher ORDER BY age ASC;

# 按照年龄降序排序
SELECT * FROM teacher ORDER BY age DESC;