MySQL数据库的常用函数

-- 创建数据库
CREATE DATABASE IF NOT EXISTS exercise DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
-- 使用数据库
USE exercise;
-- 创建一个员工表
CREATE TABLE IF NOT EXISTS emp(
    id BIGINT(20) AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '员工编号',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
    age TINYINT(3) UNSIGNED NOT NULL COMMENT '年龄',
    dept VARCHAR(20) NOT NULL COMMENT '所属部门',
    salary DOUBLE(10, 2) NOT NULL COMMENT '薪资'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='员工表';
-- 插入测试数据
INSERT INTO emp(id, name ,sex, age, dept, salary) VALUES(DEFAULT, '张三', '男',22, '研发部', 13000);
INSERT INTO emp(name ,sex, age, dept, salary) VALUES('李刚', '男', 24, '研发部',14000);
INSERT INTO emp VALUES(DEFAULT, '金凤', '女', 23, '财务部', 8000);
INSERT INTO emp(name ,sex, age, dept, salary) VALUES('肖青', '女', 26, '财务部',9000), ('张华', '男', 28, '研发部', 15000),('董钰', '女', 24, '研发部', 12000);
INSERT INTO emp VALUES(DEFAULT, '吴梅', '女', 24, '测试部', 9000),(DEFAULT, '王玲','女', 26, '测试部', 9500);
-- 吴梅因工作出色而被提升为测试主管，薪资调整为11000
UPDATE emp SET salary=11000 WHERE name='吴梅';
-- 研发部金凤离职
DELETE FROM emp WHERE name='金凤';
-- 从员工表中查询出平均年龄小于25的部门
SELECT dept FROM emp GROUP BY dept HAVING AVG(age)<25;
-- 从员工表中统计研发部的最高薪资、最低薪资、平均薪资和总薪资
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM emp WHERE dept='研发部';
-- 从员工表中统计各个部门的员工数量
SELECT dept, COUNT(*) FROM emp GROUP BY dept; 
-- 从员工表中查询薪资在10000以上的员工信息并按薪资从高到低排列
SELECT * FROM emp WHERE salary>10000 ORDER BY salary DESC;
-- 从员工表中分页查询员工信息，每页显示5条员工信息，按薪资从高到低排列，查询第2页员工信息
SELECT * FROM emp ORDER BY salary DESC LIMIT 5,5;

--MySQL修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

MySQL常用函数:
1.常用数学函数
ABS(X):返回x绝对值
FLOOR(X):返回不大于x的最大整数
CEIL(X):返回不小于x的最小整数
TRUNCATE(X,D):返回数值x保留到小数点后D位的值，截断时不进行四舍五入
ROUND(X):返回离x最近的整数，截断时进行四舍五入
ROUND(X,D):返回数值x保留到小数点后D位的值，截断时进行四舍五入
RAND():返回0~1的随机数
MOD(N,M):返回N除以M以后的余数
2.常用字符串函数
CHAR_LENGTH(str):计算字符串字符个数
LENGTH(str):返回字符串字节数
CONCAT(s1,s2...):字符串拼接，其中任意一个为NULL则返回值为NULL
LOWER(str):将str中字母转换为小写
UPPER(str):将str中字母转换为大写
LEFT(str,n):返回从左边开始n个字符
RIGHT(str,n):返回从右边开始n个字符
LTRIM(s):去除左边空格
RTRIM(s):去除右边空格
TRIM(s):去除两端空格
REPLACE(s,s1,s2):返回一个字符串，用s2替代s中所有s1
SUBSTRING(s,n,len):从s中返回一个第n个字符开始，长度为len的字符串

-- 查询计科和软工各有多少人
SELECT LEFT(class,2), COUNT(*) FROM stu GROUP BY LEFT(class,2);
-- 查询名字有4个字的学生信息
SELECT * FROM stu WHERE CHAR_LENGTH(`name`)=4;
-- 查询成绩能够被10整除的考试信息
SELECT * FROM score WHERE MOD(score,10)=0;

3.常用日期和时间函数
CURDATE():返回当前日期 CURRENT_DATE()
CURTIME():返回当前时间 CURRENT_TIME()
NOW():返回当前日期和时间
CURRENT_TIMESTAMP():返回当前日期和时间
SYSDATE():返回当前日期和时间
YEAR(d):返回d中的年份值
MONTH(d):返回d是哪个月
WEEK():返回d是哪一周
DAYOFYEAR(d):返回给定日期是当年的第几天
DAYOFMONTH(d):返回给定日期是当月的第几天
DAYOFWEEK(d):返回给定日期是d当周的第几天
HOUR(d):返回d的小时数
MINUTE(d):返回d的分钟数
SECOND(d):返回d的秒数
ADDDATE(d,n):返回起始日期加上n天的日期，n可为负数
TIMESTAMPDIFF(INTERVAL expr type,d1,d2):返回d1，d2时间差
-- 求年份差，TIMESTAMPDIFF(YEAR,d1,d2);
DATE_FORMAT(d,f):返回给定日期格式的字符串
-- 十二小时用h，DATE_FORMAT(d,'%Y-%m-%d %h:%i:%s');
-- 二十四小时用h，DATE_FORMAT(d,'%Y-%m-%d %H:%i:%s');

-- 查询年龄在20岁以上的学生信息
SELECT * FROM stu WHERE TIMESTAMPDIFF(YEAR,birthday,NOW())>20;
-- 查询今天过生日的学生信息
SELECT * FROM stu WHERE MONTH(birthday)=MONTH(NOW()) AND DAYOFMONTH(birthday)=DAYOFMONTH(NOW());
-- 查询本周过生日的学生信息
-- 起始时间 RIGHT(DATE_FORMAT(ADDDATE(NOW(),-DAYOFWEEK(NOW())),'%Y-%m-%d'),5)
-- 截止日期 RIGHT(DATE_FORMAT(ADDDATE(NOW(),7-DAYOFWEEK(NOW())),'%Y-%m-%d'),5)
SELECT * FROM stu WHERE RIGHT(birthday) > RIGHT(DATE_FORMAT(ADDDATE(NOW(),-DAYOFWEEK(NOW())),'%Y-%m-%d'),5)
AND RIGHT(birthday) < RIGHT(DATE_FORMAT(ADDDATE(NOW(),7-DAYOFWEEK(NOW())),'%Y-%m-%d'),5);

4.条件判断函数
1.IF函数:
(1)IF(条件, 表达式1, 表达式2):如果条件满足,则使用表达式1,否则使用表达式2
-- 将学生成绩展示为及格和不及格
SELECT id,stu_name,course, IF(score>=60,'及格','不及格') score FROM score;
-- 注：IF后score为别名

(2)IFNULL(字段, 表达式):如果字段值为空，则使用表达式，否则，使用字段值
-- 将未参加考试的学生成绩展示为缺考
SELECT id,stu_name,course, IFNULL(score,'缺考') score FROM score;

2.CASE...WHEN 语句
(1)CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 ...] ELSE 表达式n END:
如果条件1满足，则使用表达式1；【如果条件2满足，则使用表达式2， ... 】否则，使用表达式n。-- 类似于多重if...else
-- 行转列:查询每位学生的各课程成绩
SELECT
    stu_name,
    course,
    MAX(CASE WHEN (course = 'Java') THEN score ELSE 0 END) Java,
    MAX(CASE WHEN (course = 'Html') THEN score ELSE 0 END) Html,
    MAX(CASE WHEN (course = 'Jsp') THEN score ELSE 0 END) Jsp,
    MAX(CASE WHEN (course = 'Spring') THEN score ELSE 0 END) Spring
FROM score
GROUP BY stu_name;

(2)CASE 表达式 WHEN 值1 THEN 表达式1 [WHEN 值2 THEN 表达式2 ...] ELSE 表达式n END:
如果表达式的执行结果为值1，则使用表达式1；【执行结果为值2，则使用表达式2， ... 】否则，使用表
达式n。-- 类似于switch
-- 行转列:查询每位学生的各课程成绩
SELECT
    stu_name,
    course,
    MAX(CASE course WHEN 'Java' THEN score ELSE 0 END) Java,
    MAX(CASE course WHEN 'Html' THEN score ELSE 0 END) Html,
    MAX(CASE course WHEN 'Jsp' THEN score ELSE 0 END) Jsp,
    MAX(CASE course WHEN 'Spring' THEN score ELSE 0 END) Spring
FROM score
GROUP BY stu_name;
-- 查询各班级性别人数
SELECT
class,
SUM(CASE sex WHEN 0 THEN 1 ELSE 0 END) '男',
SUM(CASE sex WHEN 1 THEN 1 ELSE 0 END) '女', 
SUM(CASE sex WHEN 2 THEN 1 ELSE 0 END) '其他'
FROM stu
GROUP BY class;
5.系统信息函数
VERSION():获取数据库版本号
CONNECTION_ID():获取服务器连接数
DATABASE()/SCHEMA():获取当前数据库名称
USER()/SYSTEM_USER()/SESSION_USER():获取当前用户名
CURRENT_USER()/CURRENT_USER:获取当前用户名
-- 注:CURRENT_USER不是函数
6.数字格式化函数
FORMAT(X,D):返回数值x保留到小数点后D位的值，截断时进行四舍五入