数据库查询(二)

一.MySQL中的函数

(1)字符串函数

概念：用于控制返回给用户的字符串

功能演示 

-- 拼接字符串
SELECT CONCAT('啊啊','阿胶','哥哥');
-- 字符长度
SELECT LENGTH('fgvgvd');
-- 字符转大写
SELECT UPPER('sfffgds');
-- 转小写
SELECT LOWER('ADAF');
--  去除左边空格
SELECT LTRIM('       gvrg');
--  去除左边空格
SELECT RTRIM('bergg            ');
-- 去除两端空格
SELECT TRIM('       hrhb     ');
-- 替换
SELECT REPLACE('hello,word','word','世界');
-- 有条件的替换
SELECT INSERT('hello,word',5,1,'世界');

(2)日期函数

  概念：用于日期操作

功能演示 

-- 日期函数  系统日期 年月日
SELECT CURDATE();
-- 日期和时间
SELECT NOW();
-- 时间 时分秒
SELECT CURTIME();
-- 添加日期
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
-- 减少日期
SELECT DATE_SUB(NOW(),INTERVAL 5 DAY);
-- 日期格式化  Y 完整年  Y两位数的年
SELECT DATE_FORMAT(CURDATE(),'%Y年%m月%d日');
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日%H时%i分%s秒');

(3)数学函数

   概念:用于对数值进行代数运算

 功能演示 

-- 返回0-1之间随机数
SELECT RAND();
-- 绝对值
SELECT ABS('-55');
-- 取最大整数
SELECT CEILING('43.5');
-- 取小于整数
SELECT FLOOR('43.5');
-- 幂值
SELECT POWER(3,4);
-- 四舍五入
SELECT ROUND('35.689');
-- 返回值 正数返回+1,负数返回-1
SELECT SIGN(-56);
-- 开平方
SELECT SQRT('25');

二 综合函数

1.案例

某公司印了一批充值卡,卡的密码是随机生成的,现在出现这个问题:

卡里面的字母'o和数字0'、'字母i和数字1',用户反映说看不清楚,公司决定,把存储在数据库中的

密码中所有的所有'o'都改成'0',把所有的'i'都改成'1';

update student set login_pwd=replace(login_pwd,'o','0');
update student set login_pwd=replace(login_pwd,'o','0');
update student set login_pwd=replace(login_pwd,'i','1');
update student set login_pwd=replace(login_pwd,'i','1');

案例分析:密码更新功能用update,涉及到字符串的替换,用到字符串函数replace(),把字母o改成0,字母i改成1

2.日期函数练习

-- 查询年龄超过20的学生
select * from student where birthday<=date_sub(curdate(),interval 20 year);

-- 获取月份
select month(now());

-- 查询月份是一月过生日的学生
select * from student where month(birthday)='01'


----今天过生日的学生
select * from student  where month(birthday)=month(now()) and day (borthday)=day(now())

3.数字函数练习

--1000~999999随机数
select rand()*(999999-1000)+1000;

-- 取整
select floor(rand()*(999999-1000)+1000);


-- 拼接
select concat(floor(rand()*(999999-1000)+1000),'@qq.com')

 4.字符串函数练习

-- 截取字符串
select right('1234@qq.com');
--反转
select reverse('123@qq.com');
--获取字符的位置
select instr('moc.qq@321','.');



select right(email,instr(reverse(email),'.')-1) from student

三.聚合函数

定义:对一组值进行计算,并返回计算后的值,具有统计数据的作用

1.总和 

关键词:sum

SELECT SUM(student_result) FROM result;

2.平均分

关键词:avg

SELECT AVG(student_result) FROM result;

3.最大值、最小值

关键词:max,min

SELECT MAX(student_result),MIN(student_result) FROM result;

4.计数

关键词:count

SELECT COUNT(*) FROM student;

三.替换查询

写法:

case;

when 条件1 then 表达式1

when 条件2 then 表达式2

else 表达式

end

注:一个表达式为一个字段,应在前后完成整个语句内容.

SELECT result_id,student_result,
CASE
WHEN student_result<60  AND student_result>=0 THEN '不及格'
WHEN student_result>=60  AND student_result<=80 THEN '及格'
WHEN student_result>80  AND student_result<=100 THEN '优秀'
ELSE'作弊'
END AS '成绩等级'
FROM result;

四.分组查询

1.分组查询 

注:group by有一个原则,就是select后面的所有列中,没有使用聚合函数的列必须出现在group by 后面.

写法: select...from<表名>where....group by ...;

-- 每个科目的平均分
SELECT subject_id,AVG (student_result) FROM result 
GROUP BY subject_id;

2.多列分组

-- 每个班级的总人数
SELECT grade_id,sex,COUNT(*)FROM student  GROUP BY grade_id,sex 
ORDER BY grade_id;

3.分组筛选

写法:select...from<表名>where....group by..... having....

-- 班级的总人数>15的班级
SELECT grade_id,COUNT(*)FROM student  GROUP BY grade_id
HAVING COUNT(*)>3;