前言
在日常开发中，sql语言是必须要掌握的。根据个人的经验，实现业务功能很简单，但是要合理的、高效的实现业务功能就需要自己精益求精。能完成这个要求，起码在sql层面，首先需要一定的经验，然后就是理解不同sql的含义。结合具体使用场景，合理的运用sql函数，高效的完成业务需求
以下是我总结的日常使用的sql函数.后续还会写一下自己日常使用到的基本的sql调优规则 （数据库使用之前学习使用数据库）
-- 借鉴的表哈
CREATE TABLE `pms_attr` (
`attr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '属性id',
`attr_name` char(30) DEFAULT NULL COMMENT '属性名',
`search_type` tinyint DEFAULT NULL COMMENT '是否需要检索[0-不需要，1-需要]',
`value_type` tinyint DEFAULT NULL COMMENT '值类型[0-为单个值，1-可以选择多个值]',
`icon` varchar(255) DEFAULT NULL COMMENT '属性图标',
`value_select` char(255) DEFAULT NULL COMMENT '可选值列表[用逗号分隔]',
`attr_type` tinyint DEFAULT NULL COMMENT '属性类型[0-销售属性，1-基本属性，2-既是销售属性又是基本属性]',
`enable` bigint DEFAULT NULL COMMENT '启用状态[0 - 禁用，1 - 启用]',
`catelog_id` bigint DEFAULT NULL COMMENT '所属分类',
`show_desc` tinyint DEFAULT NULL COMMENT '快速展示【是否展示在介绍上；0-否 1-是】，在sku中仍然可以调整',
PRIMARY KEY (`attr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品属性';
一、基础sql函数
1.sql中‘+’号的作用
mysql中的+号只有一个作用，运算符
--两个操作数都为数值型，则做加法运算
select 500 + 400
--如果一方是字符，则先做转换，如果转换成功继续加法运算
select '123' + 321
--如果转换失败，则将字符型转换成0
select 'join' + 321
--如果一方为null，则结果肯定为null
select null + '321'
2.DISTINCT关键字
DISTINCT一般用作去重
SELECT DISTINCT * FROM pms_attr
-- 直接把关键字加到需要去重字段前即可
SELECT DISTINCT search_type FROM pms_attr
3.条件相关判断函数
3.1：IF
IF（表达式1，表达式2，表达式3） 如果表达式1为Ture 则返回表达式2，否则返回表达式3
SELECT IF(attr_id > 10,'大于10','小等于10') as ne,attr_id,attr_name FROM pms_attr
3.2:IFNULL
判断是否为NULL并返回指定值
-- if(表达式1，表达式2)，如果表达式1为null，则返回表达式2
SELECT IFNULL(icon,'此数据为null') as ifNullIcon,attr_id,attr_name FROM pms_attr
3.3:ISNULL
判断是否为NULL，是则返回1，否则返回0
SELECT ISNULL(icon)as ifNullIcon,attr_id,attr_name FROM pms_attr
3.4:case函数
CASE `department_id`
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees
#CASE 函数 第二种用法，同多重if类似
SELECT `last_name`,salary,
CASE
WHEN salary>20000 THEN 'a'
WHEN salary>15000 THEN 'b'
WHEN salary>10000 THEN 'c'
ELSE 'd'
END AS 级别
FROM employees
ORDER BY 级别
3.拼接替换相关函数
3.1:CONCAT拼接函数
注意concat函数不能拼接null值 只能配合ifnull（str1，str2）使用
SELECT CONCAT('attr_id:',attr_id,'----') as result FROM pms_attr
3.2:GROUP_CONCAT拼接函数
GROUP_CONCAT（expr1,expr2... separator ';'） 将多个结果拼接到一个结果中，默认以逗号分隔。 separator函数可以设置自定义分隔符
注意： 此函数受默认字段默认长度限制，如果需要，需要手动扩容
SET SESSION group_concat_max_len = 1000000;
SELECT GROUP_CONCAT('attr_id:',attr_id,'----') as result FROM pms_attr
4.模糊查询
-- % 代表模糊查询符
SELECT attr_name FROM pms_attr WHERE attr_name LIKE '%a%'
SELECT attr_name FROM pms_attr WHERE attr_name LIKE '__p_a%'
-- escape用在sql语句中的意思就是使escape后的字符失效，变成个特殊字符来处理。
SELECT attr_name FROM pms_attr WHERE attr_name LIKE '_$_%' ESCAPE '$'
-- BETWEEN 1 AND 10 查询1-10之间的数据
SELECT * FROM pms_attr WHERE attr_id BETWEEN 1 AND 10
-- NOT BETWEEN 1 AND 10 不在1-10之间
SELECT attr_name,attr_id FROM pms_attr WHERE attr_id NOT BETWEEN 8000 AND 17000 ORDER BY attr_id DESC
-- IN 包含多条件，表示字段在范围内
SELECT * FROM pms_attr WHERE job_id IN ('FI_MGR','AC_MGR','MK_MAN')
5.字符串处理函数
-- UPPER()转大写、LOWER()转小写、CONCAT()拼接字符、LENGTH()返回字节长度
SELECT
UPPER(last_name) as 大写姓名,
LOWER(`first_name`) AS 小写姓名,
LENGTH(last_name) AS 字段长度
FROM pms_attr
-- SUBSTR(str,起始索引,截取长度)截取指定长度的字符；截取从指定索引处后面所有字符
SELECT
SUBSTR(attr_name,1,1),
SUBSTR(`value_select`,2)
FROM pms_attr
--TRIM()过滤前后指定字符，没有指定则过滤前后空格
SELECT TRIM('a' FROM 'aaaaaaaaaaaaaaaGZNaaaaaaaaaaaaaaaaa') AS test
-- 填充长度小于字符长度，则返回填充长度的字符
SELECT LPAD('ABC',10,'*') AS 左填充指定字符
SELECT LPAD('ABCD',2,'*') AS 返回填充长度的字符
-- RPAD()向右填充指定长度的字符
SELECT RPAD('ABC',10,'*') AS 右填充指定字符
-- REPLACE(str,指定字符,替换字符)指定字符替换
SELECT REPLACE('askfjslkdfklwsdjsjsask','s','--') AS 替换字符
-- LOCATE(substr,str)
-- 返回substr字符串在str里第一次出现的位置，没有返回0
SELECT LOCATE('askfjslkdfklwsdjsjsask','s') AS 是否包含
6.数字类型操作函数
--ROUND 四舍五入
SELECT ROUND(1.56) #结果：2
SELECT ROUND(-1.56) #结果：-2
SELECT ROUND(1.567,2) #结果：1.57（保留2位小数）
--CEIL 向上取整
SELECT CEIL(1.56) #结果：2
SELECT CEIL(-1.2) #结果：-1
--FLOOR 向下取整
SELECT FLOOR(-9.99) #结果：-10
SELECT FLOOR(1.56) #结果：1
--TRUNCATE()截断
SELECT TRUNCATE(1.568,2) #结果：1.56（保留2位小数）
--MOD()取余
SELECT MOD(10,3) #结果：1
SELECT SUM(salary) AS 求和,AVG(salary) AS 平均值,MAX(salary) AS 最大值,
MIN(salary) AS 最小值,COUNT(*) AS 总行数
FROM pms_attr
-- TRUNCATE(*column*|*expression*,*n*) 函数
-- TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者缺少，值被截断为整数。如果第二个参数是 2，值被截断为两位小数。如果第二个参数是–2，值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。
SELECT TRUNCATE(45.923,2);
-- HAVING 理解为存在则返回
SELECT * FROM pms_attr HAVING attr_id > 10
7.日期相关函数
-- NOW()返回当前系统日期+时间 2024-10-12 10:22:55
SELECT NOW()
-- CURDATE()返回当前系统日期，不包含时间 2024-10-12
SELECT CURDATE()
-- CURTIME()返回当前系统时间，不包含日期 10:23:05
SELECT CURTIME()
-- 返回指定部分：年、月、日、时、分、秒
SELECT YEAR(CURDATE()) #结果：2024
SELECT MONTH(CURDATE()) #结果：10
-- DATEDIFF()返回两个日期相差的天数 966 可以设置表内数据 返回相差天数 可以为负数
SELECT DATEDIFF('2025-05-03','2022-09-10') AS 相差天数
-- STR_TO_DATE()将字符通过指定格式转换成日期
SELECT STR_TO_DATE('4-3-1992','%m-%d-%Y') as date1
-- DATE_FORMAT()将日期转换成字符 2024年10月12日
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') as date2
-- 将指定时间转换为时间戳 也可以用作比较大小
SELECT UNIX_TIMESTAMP(NOW()) as date3
8.连接函数
8.1：多表连接
查询多张表 FROM Table1 ，Table2 。。。
#为表起别名，相同字段用表别名限定（已定义表别名，将不可再用表名做限定）
SELECT last_name,e.job_id,job_title FROM employees AS e,jobs AS j
WHERE e.`job_id` = j.`job_id`
#加筛选条件
SELECT last_name,`department_name`,commission_pct FROM employees AS e,departments AS d
WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL
#加分组查询 group by
SELECT COUNT(*),city FROM departments AS d,`locations` AS l
WHERE d.`location_id` = l.`location_id`
GROUP BY city
#加排序 order by
SELECT job_title,COUNT(*) AS co FROM jobs AS j,employees AS e
WHERE j.`job_id` = e.`job_id`
GROUP BY job_title ORDER BY co DESC
#三表连接
SELECT last_name,`department_name`,city FROM employees AS e,departments AS d,`locations` AS l
WHERE e.`department_id` = d.`department_id` AND d.`location_id` = l.`location_id`
AND city LIKE '%s%' ORDER BY `department_name` DESC
8.2:不等值链接
#不等值连接
SELECT last_name,salary,`grade_level` FROM employees AS e,`job_grades` AS j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` ORDER BY `grade_level` DESC,salary
8.3:自链接
#自连接
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM employees AS e,employees AS m
WHERE e.`manager_id` = m.`employee_id`
8.4:内链接
#省略inner
SELECT last_name,`department_name`
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
#分组+筛选+排序
SELECT `department_name`,COUNT(*) co
FROM departments AS d
INNER JOIN employees AS e
ON d.`department_id` = e.`department_id`
GROUP BY e.`department_id`
HAVING co > 3
ORDER BY co DESC
#三表连接 多表链接可以根据业务链接不同表 e,d,j
SELECT last_name,`department_name`,`job_title`
FROM employees e
INNER JOIN departments d ON e.`department_id` = d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY `department_name` DESC
#非等值连接
SELECT last_name,salary,`grade_level`
FROM employees e
JOIN `job_grades` j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
ORDER BY `grade_level`
#非等值连接:分组+筛选+排序
SELECT `grade_level`,COUNT(*) AS co
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING co > 20
ORDER BY grade_level DESC
#自连接
SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%'
8.5:外链接
#左连接+筛选
SELECT g.`name` FROM beauty g
LEFT JOIN boys b ON g.`boyfriend_id` = b.`id`
WHERE b.`id` IS NULL
#右连接
SELECT b.`name`
FROM boys AS bo
RIGHT JOIN beauty AS b
ON b.`boyfriend_id` = bo.`id`
WHERE bo.id IS NULL
8.6:交叉连接
#交叉连接：实现笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo
9.其他函数
9.1:系统函数
系统函数还有很多，举例都是一些常用的例子
在日常开发中如果想了解，编辑索引，表结构等可以查看SQL执行日志
#查看Mysql版本号
SELECT VERSION()
#查看当前数据库
SELECT DATABASE()
#查看当前用户
SELECT USER()
#查询表中所有字段名
SELECT COLUMN_NAME FROM test.COLUMNS WHERE table_name = 'test'
#修改表中字段名
ALTER TABLE mall_user CHANGE uname username VARCHAR (30 ) NOT NULL;
9.2:分组函数
#GROUP BY HAVING 分组后筛选，查询那个部门的员工数>2
SELECT COUNT(*) AS co,`department_id` FROM employees GROUP BY `department_id` HAVING COUNT(*) >2
9.3:子查询
构建虚拟表查询
-- 单行子查询
#单行子查询
SELECT last_name,job_id,salary FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);
SELECT last_name,job_id,salary FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE `employee_id` = 141)
AND salary > (SELECT salary FROM employees WHERE `employee_id` = 143)
#单行子查询 分组+筛选
SELECT MIN(salary),`department_id` FROM employees
GROUP BY `department_id`
HAVING MIN(salary) > (
SELECT MIN(salary) FROM employees WHERE `department_id` = 50
)
-- 多行自查询
#IN() 在子查询范围中
SELECT last_name FROM employees
WHERE `department_id`
IN (SELECT `department_id` FROM departments WHERE `location_id` IN(1400,1700))
#ANY() 满足任意一个
SELECT `employee_id`,last_name,job_id,salary
FROM employees
WHERE salary < ANY (
SELECT salary FROM employees WHERE `job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG'
#替代解法
SELECT `employee_id`,last_name,job_id,salary
FROM employees
WHERE salary < (
SELECT MAX(salary) FROM employees WHERE `job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG'
#ALL() 满足所有
SELECT `employee_id`,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
SELECT salary FROM employees WHERE job_id = 'IT_PROG'
)
# 替代解法
SELECT `employee_id`,last_name,job_id,salary
FROM employees
WHERE salary < (
SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG'
)
-- 行子查询
SELECT * FROM employees
WHERE (`employee_id`,salary) = (
SELECT MIN(`employee_id`),MAX(salary) FROM employees
)
-- 在select后面
SELECT d.*,(SELECT COUNT(*) FROM employees e
WHERE e.`department_id` = d.`department_id`)
FROM `departments` d
-- 在from后面
SELECT d.*, grade_level FROM job_grades j,
(SELECT TRUNCATE(AVG(salary),2) av,`department_id` FROM employees
GROUP BY `department_id`) AS d
WHERE d.av BETWEEN j.`lowest_sal` AND j.`highest_sal`
9.4:EXISTS函数
EXISTS() 判断是否有结果 返回：1 或 0 此函数内容会优先执行
SELECT `department_name` FROM departments d
WHERE EXISTS(
SELECT `employee_id` FROM employees e
WHERE d.`department_id` = e.`department_id`
)
#类似解法 in()
SELECT `department_name` FROM departments d
WHERE `department_id` IN(
SELECT `department_id` FROM employees
)
#NOT EXISTS()
SELECT `boyName` FROM boys bo
WHERE NOT EXISTS(
SELECT id FROM beauty b
WHERE bo.`id` = b.`boyfriend_id`
)
9.5:分页查询
SELECT * FROM employees LIMIT 0,5;
# 从0开始，可省略起始索引
SELECT * FROM employees LIMIT 5
10.增、改、删
插入insert
#方式一：
INSERT INTO demo(NAME,sex,`phone`) VALUE('徐凤年','男','13825425632');
#支持插入多行
INSERT INTO demo(NAME,sex,phone)
VALUE('徐凤年1','男','13825425632'),
('徐凤年2','男','13825425632'),
('徐凤年3','男','13825425632')
#支持子查询
INSERT INTO demo(NAME,sex,phone)
SELECT `boyName`,'男','1654616516'
FROM boys
#方式二
INSERT INTO demo SET NAME = '陈芝豹',sex = '男',phone = '13654875896';
修改update
#修改多表记录
#内联实现
UPDATE demo b
JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
SET b.`phone` = '114'
WHERE bo.`boyName` = '张无忌';
#左联实现
UPDATE demo b
LEFT JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
SET b.`boyfriend_id` = 2
WHERE bo.`id` IS NULL
删除delete
#单表的删除
DELETE FROM demo WHERE `phone` LIKE '%9';
#多表的删除
#单表
DELETE b FROM demo b
JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName` = '张无忌';
#双表
DELETE b,bo FROM demo b
JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName` = '黄晓明';
结束语
以上就是自己总结出来的sql中日常高频使用的sql的一些用法，后续遇到还会持续新增进来。各位能看出来，前几个函数对应存在截图，考虑了一下，后面的函数就不加了，不然成裹脚布了。大家如果感兴趣，可以自己建表执行一下
总结的也会有些不全面，可能有部分描述不准确，欢迎各位大佬的批评和指正;
今天的分享就到此结束啦😀!
