SQL学习之函数操作2

最新推荐文章于 2022-11-18 15:37:30 发布
原创 最新推荐文章于 2022-11-18 15:37:30 发布 · 229 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

前言

总有些需求是奇葩的，总有偷懒，是通过封装函数实现的，总有些骚操作，都是需要自定义的…

SQL自定义函数

  • 自定义函数是对MySQL的扩展，使用方式和内置函数相同
  • 函数必须要有参数和返回值
  • 函数可以接收任意类型的值，也可以接收这些类型的参数
  • 函数体由合法的SQL语句组成
  • 函数体可以是简单的SELECT语句或INSERT语句，如果是复合结构要用BEGIN…END
  • 函数体也可以包含声明，循环和流程控制
  • 返回值只能有一个

语法

-- 定义一个函数，返回中文的当前时间
CREATE FUNCTION ZNOW() RETURNS VARCHAR(255)
RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点%i分%s秒');

-- 调用自定义函数
SELECT ZNOW() -- 2019年01月21日 14点53分14秒

  • 可以接受参数的自定义函数

    -- 定义可以接收参数的函数
CREATE FUNCTION ZADD(num1 INT, num2 INT) RETURNS INT
RETURN num1 + num2

-- 调用函数 
SELECT ZADD(2,4) -- 6

  • 定义多行代码函数

    -- 定义多行代码函数
-- 准备一个数据表格
CREATE TABLE stu2(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50))

SELECT * FROM stu2;
DESC stu2;

-- 函数功能，传入一个名称，函数里吧这个名字插入到stu2中，然后返回新生成的ID号

CREATE FUNCTION ADD_USER(name VARCHAR(50)) RETURNS INT
BEGIN
INSERT INTO stu2(name) VALUES(name);
-- 调用last_insert_id拿到最新的id
RETURN LAST_INSERT_ID();
END

-- 调用函数
SELECT ADD_USER('LEO'), ADD_USER('JAY'), ADD_USER('JAYY');

模糊查询

就是查询的条件是模糊的，不是特别明确的

  • 通配符

    代替一个或多个真正的字符，与LIKE 关键字一起使用

    通配符解释示例符合条件
    _一个字符LIKE ‘a_’as, ad…
    %任意长度字符串LIKE ‘c%’code, cool …
    []括号中指定范围内一个字符LIKE ‘1[35]5’135 or 155
    [^]不在括号中所指定范围内的一个字符LIKE 1[^1-2]5’135, 145, 155…
    • 查询姓名里包含a的 %
    • 查看姓名是以a开头的 %
    • 查看姓名第三个字符是a的 _

  • 简单的使用demo

    -- 查询所有name为L开头的数据
SELECT * FROM stu2 WHERE name LIKE 'L%'

-- 查询所有name为Y为结尾的数据
SELECT * FROM stu2 WHERE name LIKE '%Y'

-- 查询所有name中包含a的结果
SELECT * FROM stu2 WHERE name LIKE '%A%'

-- 匹配单个字符
SELECT * FROM stu2 WHERE name LIKE '%A_'


-- 查询某一列在指定的规范内的记录，包括两个边界
SELECT * FROM score WHERE grade BETWEEN 60 AND 100;
-- 写法二
SELECT * FROM score WHERE grade >= 60 AND grade <= 100;


-- 查询某一个列的值是否在我们查询的列表中
SELECT * FROM student;
SELECT * FROM student WHERE city IN ('bj', 'gd');
SELECT * FROM student WHERE city='bj' OR city = 'gd';

-- 查询某一西字段为空的数据
SELECT * FROM student WHERE age IS NULL;

聚合函数

对一组值进行计算，并返回计算后的值，一般用来统计数据

  • 使用示例

    -- sum 求和
SELECT SUM(age) FROM student WHERE city='bj'

-- max计算最大值和最小值
SELECT MAX(age) FROM student WHERE city='bj'
SELECT MIN(age) FROM student WHERE city='bj'

-- 求平均值
SELECT AVG(age) FROM student WHERE city='bj';

-- 统计记录数
SELECT COUNT(age) FROM student WHERE city = 'bj'

-- 分组函数
-- 如果是分组的的话select后边只能更分组的列的聚合函数
SELECT student_id, MIN(grade)
FROM student
WHERE grade > 0 -- 用来过滤分组前的记录
GROUP BY student_id -- 分组字段
HAVING MIN(grade) < 80; -- 用来过滤分组后的数据
ORDER BY MIN(grade) ASC -- 排序 这里只能放聚合函数
LIMIT 1,2-- 过滤

  • 常见写法

    -- 统计每位同学的平均成绩表-单列分组
SELECT student_id, AVG(grade)
FROM score
GROUP BY student_id


-- 统计没门课程最高分，并按照分数从高到低排列
SELECT course_id, MAX(grade) AS grade
FROM score
GROUP BY course_id
ORDER BY grade DESC

-- 统计各省的男女同学人数- 多列分组
SELECT city, age, COUNT(id) FROM student
GROUP BY city, age



-- 统计学生人数超过1人的城市
SELECT city, COUNT(1) AS count
FROM student
GROUP BY city
HAVING count > 1

自查询

  • 自查询就是只出现在其他SQL语句中的select语句
  • outer QUERY/Inner QUERY
  • 子查询指嵌套在查询内部，且必须出现在圆括号中
  • 子查询可以包含多个关键字或条件
  • 子查询的外层查询可以是：SELECT，INSERT，UPDATE SET
  • 子查询可以返回常量，一行数据，一列数据或者其他子查询
-- 使用示例

-- 查询年龄大于平均年龄的同学
SELECT AVG(age) FROM student

SELECT * FROM student WHERE age > (SELECT AVG(age) FROM student)

-- ANY SOME ALL
-- ALL 大于所有值
-- 查询哪位同学的年龄
SELECT * FROM student WHERE age > ALL(SELECT age FROM student WHERE city='bj')

SELECT * FROM student WHERE age > ANY(SELECT age FROM student WHERE city='bj')

SELECT * FROM student WHERE age > SOME(SELECT age FROM student WHERE city='bj')



-- 查询有考试成绩的学生信息
SELECT * FROM score
SELECT * FROM student
SELECT * FROM student WHERE id IN (SELECT student_id FROM score)

-- 查询没有成绩的同学
SELECT * FROM student WHERE id NOT IN (SELECT student_id FROM score)

-- EXISTS not EXISTS
-- EXISTS 存在一条就不在查询了
SELECT * FROM student WHERE EXISTS(SELECT student_id FROM score WHERE student_id = student.id)

SELECT * FROM student WHERE NOT EXISTS(SELECT student_id FROM score WHERE student_id = student.id)

表连接

很多时候，更具不同的业务常见，我们会将数据保存在不同的数据表中，查询的时候大多数时候都是查询多个表

  • JOIN = INNER JOIN = CROSS JOIN内连接

  • LEFT [OUTER] JOIN 左外链接

  • RIGHT [OUTER] JOIN 右外链接

  • ON 连接条件

  • 使用demo

-- 多表查询
-- 内联查询取交集
SELECT * FROM score INNER JOIN student ON score.student_id = student.id
-- 模拟内联查询
SELECT * FROM score,student WHERE score.student_id = student.id

-- 指定列查询
SELECT student.name, score.grade FROM score INNER JOIN student ON score.student_id = student.id

-- 连接多表查询
SELECT student.name, score.grade, course.name FROM score INNER JOIN student ON score.student_id = student.id
INNER JOIN course ON score.course_id = course.id

-- 左连接 读取左边表的全部和右边表和左边表的交集展示
SELECT * FROM student LEFT JOIN score ON student.id = score.student_id

-- 右连接
SELECT * FROM student RIGHT JOIN score ON student.id = score.student_id

-- 新建表
CREATE TABLE class(
 id INT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(50),
 parent_id int(20)
)

ALTER TABLE class MODIFY COLUMN id INT NOT NULL;
DESC class;

INSERT INTO class(id, name, parent_id) 
VALUES (1, '数码产品', 0), (2, '服装', 0), (3, '食品', 0), (4, '文体办公', 0), (5, 'ipad', 1), (6, '美特邦威', 2), (7, '蛋糕', 3)

SELECT * FROM class

-- 自连接
SELECT c1.id, c1.name, c2.name '父类'
FROM class c1 INNER JOIN class c2 ON c1.parent_id = c2.id

SELECT c1.id, c1.name,
IF(c2.name != '', c2.name, '顶级分类') '父类'
FROM class c1
LEFT JOIN class c2 
ON c1.parent_id = c2.id


-- 查询重复记录

-- 多表查询
-- 准备数据

INSERT INTO class(id, name, parent_id) VALUES(8, 'ipad', 1), (9, '美特邦威', 2), (10, '蛋糕', 3)
-- 开始查询 `SELECT * FROM class c1` 这么写可以开辟独立空间，想互链接

-- 查询到所有的重复的链接
SELECT * FROM class c1
WHERE c1.name IN
(SELECT name FROM class c2 GROUP BY name HAVING COUNT(name) > 1)

-- 拿到重复的分组
SELECT * FROM class c1
WHERE c1.name IN
(SELECT name FROM class c2 GROUP BY name HAVING COUNT(name) > 1)
AND c1.id IN
(SELECT MIN(id) FROM class GROUP BY name HAVING COUNT(name) > 1)

-- 拿到所有重复的id最大的数据
SELECT * FROM class c1
WHERE c1.name IN
(SELECT name FROM class c2 GROUP BY name HAVING COUNT(name) > 1)
AND c1.id NOT IN
(SELECT MIN(id) FROM class GROUP BY name HAVING COUNT(name) > 1)

-- SELECT * FROM class c2 GROUP BY name HAVING COUNT(name) > 1 拿到重复的字段


-- 创建一个表
CREATE TABLE province(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(64)
)


SELECT * FROM province
-- 查询student省份字段，并插入province表
INSERT INTO province(name) SELECT DISTINCT province FROM student

DELETE  FROM province 


-- 多表联合更新
UPDATE student INNER JOIN province ON student.province = province.name
SET student.province = province.id
WHERE student.province = province.name

select * FROM student


-- 多变联合更新 设置类型
ALTER TABLE student
CHANGE province province_id SMALLINT UNSIGNED NOT NULL

总结

学过知道才知道很多东西能不能实现，这里也是一个简单的小demo，方便自己理解，但是还是写不出来比较复杂的SQL，终究是需要多加练习，

MySQL函数运用
weixin_66202611的博客
07-04 1276
目录一，函数的定义 二，常用函数 三，字符串函数四， 数学函数五，判断函数六、合并(union) （1）日期函数案例一： 《获取当前时间效果图》 《日期转换效果图》 案例二： 效果图参考 案例1： 效果图参考 案例2： 效果图 效果图 (1).IF(expr,v1,v2) 如果expr是TRUE则返回v1，否则返回v2 -----(行列转换) 需求：每一个学生的语数外分数 判断：如果是当前分数就去当前分数，不是则用0代替
SQL2005 学习笔记 窗口函数(OVER)
09-11
相比之下，在SQL2000中，完成相同任务需要创建临时表，先计算总数，再进行分组计算，最后通过连接操作获取结果，过程相对复杂。 4. **窗口函数的优势** - **效率**：窗口函数可以直接在单个查询中完成多步计算，...
SQL学习之函数操作
我是渣渣
01-19 403
嗯，函数，函数，函数。。。。 字符串函数 SQL提供了很多字符串操作方法，下班是一些常见的函数 函数名称 描述 CONCAT 字符串连接 CONCAT_WS 使用指定的分隔符进行字符连接 FORMAT 数字格式化 LOWER 转小写字母 UPPER 转大写字母 LEFT 返回字符串s开始的最左边n个字符 RIGHT 返回字符串s开始的最左边n个字符 ...
sql操作一般函数
weixin_30244681的博客
07-18 96
sql操作一般函数 函数一般语法：SELECT function(列) FROM 表 函数的基本类型是： Aggregate 合计函数：函数的操作面向一系列的值，并返回一个单一的值。 Scalar 函数：操作面向某个单一的值，并返回基于输入值的一个单一的值。 +SQL AVG 函数 AVG 函数返回数值列的平均值。NULL 值不包括在计算中。 SELECT AVG(co...
SQL基本操作——函数
weixin_30897079的博客
10-16 117
函数的类型：在 SQL 中，基本的函数类型和种类有若干种。函数的基本类型是：Aggregate 函数、Scalar 函数。 Aggregate 函数：操作面向一系列的值，并返回一个单一的值，下面是SQL语句： --计算 "OrderPrice" 字段的平均值 SELECT AVG(OrderPrice) AS OrderAverage FROM Orders --计算客户 "Carte...
sql常见函数和操作
洛江清的博客
11-18 529
一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。出现在其他语句中的select语句，成为子查询或内查询；隐式事务：事务没有明显的开启和结束的标记。显示事务：事务具有明显的开启和结束的标记。类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字。注：视图一般用于查询，而不是更新，具备以下特点的视图都不允许更新。
DB2-SQL数据库函数中文最新版本
最新发布
11-22
DB2-SQL数据库函数在进行数据操作时需要遵循一定的安全准则，文档中会对这些准则进行说明，并提供相应的案例分析，帮助用户建立安全意识。 整体而言，本文档是一个全面的DB2-SQL函数使用指南，旨在帮助用户熟练掌握...
HiveSQL基础窗口函数学习笔记
08-30
本学习笔记主要聚焦于HiveSQL中的窗口函数，这是进行复杂数据分析的重要工具。 窗口函数允许我们在一个数据集上进行计算，而不仅仅是基于单行记录，而是基于一个“窗口”内的多行数据。这个窗口可以是当前行及其...
SQL学习笔记七函数 数字，日期，类型转换，空值处理，case
12-15
在本篇SQL学习笔记中，我们将重点探讨数字函数、日期函数、类型转换以及空值处理，以及CASE语句的使用。 1. **数字函数**： - `ABS()`：返回一个数的绝对值，例如`ABS(-5)`会返回`5`。 - `CEILING()`：将数值向上...
mysql常用函数整理笔记
codipy的博客
12-02 233
一、数学函数 abs(x) 返回x的绝对值 bin(x) 返回x的二进制（oct返回八进制，hex返回十六进制） ceiling(x) 返回大于x的最小整数值 exp(x) 返回值e（自然对数的底）的x次方 floor(x) 返回小于x的最大整数值 greatest(x1,x2,…,xn)返回集合中最大的值 least(x1,x2,…,xn) 返回集合中最小的值 ln(x) 返回x的自然对数 log(x,y)返回x的以y为底的对数 mod
数据库-MySQL约束-笔记
weixin_45156610的博客
07-13 2550
MySQL约束 回顾 MySQL管理数据库 创建数据库 CREATE DATABASE IF NOT EXISTS 数据库名; 删除数据库 DROP DATABASE 数据库名; 表的管理 查看所有表 use 数据库名; 选中一个数据库 show tables; 创建表:student(整数id,字符串name,整数age) CREATE TABLE sutdent (id int, nam...
SQL函数大全汇总
热门推荐
樱花的博客
10-19 4万+
SQL中包含以下七种类型的函数： 一、聚合函数 聚合函数:返回汇总值（它对其应用的每个行集返回一个值） AVG(表达式)返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。 COUNT(表达式)返回表达式中非NULL值的数量。可用于数字和字符列。 COUNT(*)返回表中的行数(包括有NULL值的列)。 MAX(表达式)返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。 MIN(表达式)返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期...
sql 常用语法
道亦的博客
08-29 1179
一、创建数据库表 在序列表中，选择是标识，选择是，标识增量改为1，每次增加1 每次增加一列，序列加1 SQL 和T-SQL的区别 2.1SQL（结构化查询语言） 关系数据库的标准语言 SQL server、Oracle、DB2 非过程化语言 不许要指定对数据的存放方法 统一的语言 创建、修改以及删除数据对象（数据库、表等） 2....
SQL基础操作_8_基础概念
数据科学汇集
04-22 581
3 基础概念 3.1 数据库基础概念 数据库本质上是一款面向数据管理软件。我们也可以把它理解为存放数据的仓库，这个仓库是按照一定的存储结构来组织、存储数据的。按照数据库的数据组织形式一般分为三种：层次式数据库、网络式数据库和关系型数据库。一般数据库指的都是关系型数据库，即以简单的二元关系(二维表格形式)形式组织数据的。而二元关系的基础就是关系代数，它是 基于集合，提供了一系列的关系代数...
umiJS学习
我是渣渣
10-18 2万+
安装umiJS 小提示一句，如果说经常把yarn和npm混用的话会出现一些奇奇怪怪的问题，具体什么问题可以自己摸索 // yarn or npm yarn global add umi # or npm install -g umi 创建一个项目 新建项目目录 使用umi新建页面 使用umi命令创建 自己手动创建 启动项目 打包发布 // 1.新建目录 mkdir myapp c...
HTTP之proxy
我是渣渣
08-12 2万+
Web 代理是一种存在于网络中间的实体，提供各式各样的功能。现代网络系统中，Web 代理无处不在。 提高访问速度。 因为客户要求的数据存于代理服务器的硬盘中，因此下次这个客户或其它客户再要求相同目的站点的数据时，就会直接从代理服务器的硬盘中读取，代理服务器起到了缓存的作用，对热门站点有很多客户访问时，代理服务器的优势更为明显。 Proxy可以起到防火墙的作用。 因...
Umi分包加载配置
我是渣渣
11-22 1万+
webpack配置 在.umirc.js或者config.js中配置，使用webpack的优化模块optimization.splitChunks实现， 具体是api这里不做解释，自己去官网看看，说的很清楚，这是传送门 配置代码如下： config.optimization.splitChunks({ chunks: 'all', automaticNameD...
基于qiankunjs的微前端搭建
我是渣渣
12-15 6037
什么是qiankun qiankun: 一个基于 single-spa 的微前端实现库，旨在帮助大家能更简单、无痛的构建一个生产可用微前端架构系统。孵化自蚂蚁金融科技基于微前端架构的云产品统一接入平台 微前端基本架构 #mermaid-svg-IHseDMMMqVX3w5Iq .label{font-family:'trebuchet ms', verdana, arial;font-family:var(--mermaid-font-family);fill:#333;color:#333}#merm
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值