MySQL 内置函数与聚合函数 单表复杂查询

-- 内置函数（内置方法）
-- 1.时间处理函数
SELECT YEAR(birthday)FROM tb_student

SELECT MONTH(birthday)FROM tb_student

SELECT DAY(birthday)FROM tb_student

SELECT DAY('2022-9-9')
-- 2.数值处理函数
-- 绝对值
SELECT ABS(-10)
SELECT ABS(age) FROM tb_student

-- 向上取整 ceil
SELECT CEIL(2.2)
-- 向下取整 floor
SELECT FLOOR(2.8)
-- 取余 取模
SELECT MOD(10,3)

-- 随机数 [0——1)之间一个小数  左闭右开
SELECT RAND()
SELECT CEIL(RAND()*10)

-- 四舍五入
SELECT ROUND(4.4);
SELECT ROUND(4.9);

-- 合并字符串
SELECT CONCAT(id,name) FROM tb_student

-- 去左右空格   去左空格ltrim   去右rtrim
SELECT TRIM('    12     ')

-- 截取字符串FROM FOR用 ，也可以  
SELECT SUBSTRING('我爱java' FROM 1 FOR 3)
-- 返回字符串长度
SELECT LENGTH('我爱java')

-- AS  在内存中的 物理表没有改变
SELECT NAME AS "姓名" FROM tb_student AS 学生表

-- DISTINCT 去重 删掉重复记录 只能单独使用
SELECT DISTINCT(nationality) FROM tb_student

-- 只输出名字的前两个字
SELECT SUBSTRING(name FROM 1 FOR ((LENGTH(name)-3))/3) FROM tb_student

-- 聚合函数 只返回一条结果 不能配合其他字段使用 但可以和别的聚合一起查询
-- count(*) 返回条数 
SELECT COUNT(*) FROM tb_student
-- 计算平均值
SELECT AVG(age) FROM tb_student
-- 计算最大值
SELECT MAX(age) FROM tb_student
-- 计算最小值
SELECT MIN(age) FROM tb_student
-- 计算总和 sum
SELECT SUM(age) FROM tb_student
SELECT AVG(age)*COUNT(age) FROM tb_student

SELECT SUM(age),MIN(age),AVG(age) FROM tb_student

-- 排序 ORDER BY 默认asc升序   降序desc
SELECT*FROM tb_student ORDER BY age 

SELECT*FROM tb_student ORDER BY age DESC
SELECT*FROM tb_student WHERE sex="男" ORDER BY age DESC
-- 年龄相同的情况下 根据id排序且记得写排序规则（加个,）
SELECT*FROM tb_student WHERE sex="男" ORDER BY age DESC,id ASC

-- LIMIT  从0开始 查10条  可以把0省略
SELECT*FROM tb_student LIMIT 0,10
--  从10开始  连续查询10
SELECT*FROM tb_student LIMIT 10,10
SELECT*FROM tb_student ORDER BY age DESC LIMIT 0,10

-- GROUP BY 分组

SELECT
    COUNT(*),
  nationality
FROM
    tb_student
GROUP BY
    nationality
ORDER BY
    COUNT(0) DESC

SELECT
    COUNT(0),
birthday 
FROM
    tb_student
GROUP BY
    birthday
ORDER BY
    COUNT(0) DESC

-- HAVING   进行分组之后的筛选

SELECT*FROM tb_student WHERE age=25 HAVING sex='男'

SELECT COUNT(*) FROM tb_student  GROUP BY sex HAVING count(*)>10000

-- 编写顺序   少妇无鬼混，欧了
SELECT       s
FROM         f
WHERE        w
GROUP BY     g
HAVING       h
ORDER BY     o
LIMIT        l

-- 练习
SELECT*FROM tb_student WHERE nationality="满族" ORDER BY age LIMIT 0,10
SELECT*FROM tb_student WHERE nationality="满族" ORDER BY age DESC LIMIT 0,10

SELECT nationality FROM tb_student GROUP BY nationality 
SELECT DISTINCT(nationality) FROM tb_student 
SELECT nationality,COUNT(*) FROM tb_student GROUP BY nationality 

SELECT DAY(birthday),COUNT(*) FROM tb_student GROUP BY DAY(birthday)

SELECT DAY(birthday),COUNT(*) FROM tb_student GROUP BY DAY(birthday) LIMIT 15

SELECT DAY(birthday),COUNT(*) FROM tb_student WHERE DAY(birthday)<=15 GROUP BY DAY(birthday)

SELECT DAY(birthday),COUNT(*) FROM tb_student  GROUP BY DAY(birthday) HAVING COUNT(0)>1000

-- 执行顺序  附近欧文肝癌还是斗殴了
SELECT AVG(age),MAX(age),MIN(age) from tb_student WHERE sex="女"

SELECT COUNT(*) from tb_student WHERE sex="男"

SELECT sex,AVG(age),MAX(age),MIN(age) from tb_student GROUP BY sex

SELECT nationality,COUNT(*) from tb_student WHERE sex="男" GROUP BY nationality

SELECT nationality,COUNT(*) from tb_student  GROUP BY nationality HAVING COUNT(*)>30

SELECT nationality,COUNT(*) from tb_student  GROUP BY nationality HAVING COUNT(*)>30 ORDER BY COUNT(*) DESC LIMIT 5