sql常见函数和操作

常见函数 （1）单行函数

字符函数

Name	Description
LENGTH()	返回字符串的长度（以字节为单位）
CONCAT()	返回连接的字符串
SUBSTRING(), SUBSTR()	返回指定的子字符串
INSTR()	返回第一次出现子字符串的索引
TRIM()	删除前导和尾随空格
UPPER()	转换为大写
LOWER()	以小写返回参数
LPAD()	返回字符串参数，用指定的字符串向左填充
RPAD()	追加字符串指定的次数
REPLACE()	替换指定字符串的出现

数学函数

Name	Description
ROUND()	ROUND(X)将参数 X 四舍五入到最近的整数，然后返回。两个参数的形式是将一个数字四舍五入到 D 个小数后返回
RAND()	RAND(N)返回一个范围在 0 到 1.0 之间的随机浮点值。如果一个整数参数 N 被指定，它被当做种子值使用(用于产生一个可重复的数值)
TRUNCATE()	TRUNCATE(X,D)将数值 X 截到 D 个小数，然后返回。如果 D 为 0，结果将不包含小数点和小数部分
FLOOR()	返回小于或等于指定数值表达式的最大整数
CEILING()	返回大于或等于指定数值表达式的最小整数
MOD()	MOD(X, Y) 返回X被Y除后的余数，MOD() 对于带有小数部分的数值也起作用，它返回除法运算后的余数

日期函数

Name	Description
CURTIME()	返回当前时间
CURDATE()	返回当前日期
NOW()	返回当前日期和时间
DAY(), DAYOFMONTH()	返回某天是当月的第几天 （1-31）
YEAR()	返回日期参数中的年份
MONTH()	返回日期参数的月份
MONTHNAME()	返回月份的名字
HOUR()	提取小时
MINUTE()	由参数返回分钟
SECOND()	返回参数中的秒数 (0-59)
STR_TO_DATE()	将字符串转换为日期数据
DATE_FORMAT()	按照指定格式格式化日期
DATEDIFF()	将两个日期相减

常见函数 （2）分组函数

用作统计使用，又称为聚合函数或统计函数、组函数。

处理数值型	处理任何类型	共同点
SUM()、AVG()	MAX()、MIN()、COUNT()	① 都忽略null值；②可以和DISTINCT搭配实现去重的运算；③ 和分组函数一同查询的字段要求是GROUP BY 后的字段

分类	数据源	位置	关键字
分组前筛选	原始表	GROUP BY 子句的前面	WHERE
分组后筛选	分组后的结果集	GROUP BY 子句的后面	HAVING

分组函数做条件时放在HAVING子句中

查询

连接查询

分类	特点
内连接	①表的顺序可以调换；②内连接的结果=多表的交集；③n表连接至少需要n-1个连接条件；④可以实现等值连接、非等值连接、自连接
外连接	①查询的结果=主表中所有的行，其中从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null；②LEFT JOIN 左边的就是主表，RIGHT JOIN 右边的就是主表，FULL JOIN 两边都是主表；③一般用于查询除了交集部分的剩余的不匹配的行
交叉连接	类似于笛卡尔乘积

子查询

出现在其他语句中的select语句，成为子查询或内查询；外部的查询语句称为主查询或外查询。

分类	结果集行列数
标量子查询	一行一列
列子查询	一列多行
行子查询	一行多列
表子查询	多行多列

子查询的位置	支持查询类型	特点
SELECT语句后面	量子查询
FROM语句后面	表子查询
WHERE或HAVING语句后面❤️	标量子查询、列子查询、行子查询	①标量子查询一般搭配单行操作符使用：> < >= <= = <> ；②列子查询一般搭配多行操作符使用：IN ANY/SOME ALL
EXISTS语句后面	表子查询

分页查询

-----【语法】-----
SELECT 查询列表
FROM 表
JOIN TYPE JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序的字段
LIMIT offset, size;

offset:要显示条目的起始索引（从0开始）
size：要显示的条目个数

特点：①LIMIT语句放在查询语句的最后
②公式

要显示的页数 page，每页的条目数size
SELECT 查询列表
FROM 表
LIMIT （page-1)*size,size;

联合查询

将多条查询语句的结果合并成一个结果。应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

【语法】
查询语句1
UNION 
查询语句2

概念区分

delete和truncate

DELETE	TRUNCATE
可以加WHERE条件	不可以加WHERE条件
	效率更高
删除后再插入数据，自增长列的值从断点开始	删除后再插入数据，自增长列的值从1开始
有返回值	无返回值
可以回滚	不能回滚

列级约束和表级约束

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）

事务

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的ACID属性

属性	特征
原子性（Atomicity）	事务中的操作要么都发生，要么都不发生
一致性（Consistency）	事务必须使数据库从一个一致性状态变换到另外一个一致性状态
隔离性（Isolation）	一个事务内部的操作及使用的数据对并发的其他事务是隔离的
持久性（Durability）	一个事务一旦被提交，它对数据库中数据的改变是永久性的

事务的创建
隐式事务：事务没有明显的开启和结束的标记。如insert、update、delete语句。
显示事务：事务具有明显的开启和结束的标记。前提必须先设置自动提交功能为禁用。

-- 步骤一：开启事务
set autocommit = 0;
start transaction; -- 可选的

-- 步骤二： 编写事务中的sql语句（select insert update delete）
语句1;
语句2;

-- 步骤三：结束事务
commit; -- 提交事务
rollback; -- 回滚事务

视图

一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。应用场景：1）多个地方用到同样的查询结果；2）该查询结果使用的sql语句较复杂。
作用：实现sql语句的重用；简化复杂的sql操作；保护数据，提高安全性。
注：视图一般用于查询，而不是更新，具备以下特点的视图都不允许更新。
①包含分组函数、GROUP BY、DISTINCT、HAVING、UNION
②JOIN
③常量视图
④WHERE后的子查询用到了FROM中的表
⑤用到了不可更新的视图

【语法】
-- (1)视图的创建
create view 视图名
as
查询语句;

-- (2)视图的修改_方法1
create or replace view 视图名
as
查询语句;
-- (2)视图的修改_方法2
alter view 视图名
as
查询语句;

-- (3)删除视图
drop view 视图名1，视图名2...

-- (4)查看视图
desc 视图名;
show create view 视图名;

存储过程

类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字
好处：1.提高重用性 2.sql语句简单 3.减少了和数据库服务器连接的次数，提高了效率

1. 创建

CREATE PROCEDURE 存储过程名（参数模式 参数名 参数类型）
BEGIN  
	存储过程体
END

注：参数模式： IN、OUT、INOUT ，其中IN可以省略；
存储过程体中的每一条sql语句都需要用分号结尾。

2. 调用

CALL 存储过程名（实参列表）

举例：
调用IN模式的参数：CALL spl('值');
调用OUT模式的参数：SET @name; CALL spl(@name); SELECT @name;
调用INOUT模式的参数：SET @name=值; CALL spl(@name); SELECT @name;

3. 查看

SHOW CREATE PROCEDURE 存储过程名;

4. 删除

DROP PROCEDURE 存储过程名;