文章目录
常见函数
-
调用：
# 普通使用 select 函数名() # 当函数的参数用到表的字段时 select 函数名(表的字段) from 表 -
分类：
单行函数
- 如：concat（拼接多个字符串）、length（返回字符串的长度）、ifnull…
字符函数
- length
-
用于获取参数的字节数（非字符数）
SELECT LENGTH('小王'); # 结果为：6 SELECT LENGTH('123'); # 结果为：3 # 可以查看当前系统的字符集 SHOW VARIABLES LIKE '%character%';
- concat
-
拼接字符串
SELECT CONCAT('whibin','_','tsang'); # 结果为：whibin_tsang # 拼接表中字段的数据 SELECT CONCAT(username,'_',balance) FROM account; /* 结果为： zhangsan_1000 lisi_2099 */
- upper/lower
-
将字符串变为大写/小写
SELECT UPPER('ppp') # 结果为：PPP SELECT LOWER('ABC') # 结果为：abc
- substr/substring
-
截取字符串（共有4个重载方法）
# 注意：sql语言中索引从1开始，每个索引表示一个字符长度 # str, len(从4开始截取到末尾) SELECT SUBSTR('whibin',4) # 结果：bin # str, begin, len SELECT SUBSTR('whibin',1,3) # 结果：whi
- instr
-
返回子串在字符串中第一次出现的索引，若找不到则返回0
SELECT INSTR('whibin','bin'); # 结果为：4
- trim
-
去前后指定的字符
# 默认为去掉空格 SELECT LENGTH(TRIM(' whibin ')); # 结果为：6 # 使用length函数来确认前后空格已被去除 # 去掉指定字符 SELECT TRIM('a' FROM 'aaaaaaaaaaawhibinaaaaaaaaaa'); # 结果为：whibin
- lpad/rpad
-
用指定的字符实现（左/右）填充指定的长度
# 原字符, 填充后的字符总长, 要填充的指定字符 SELECT LPAD('w',2,'1') # 结果为：1w # 由于总长为2，所以在填充后的长度超过2后，会从左向右截取长度为2的字符串 SELECT RPAD('w',2,'1') # 结果为：w1
- replace
-
替换指定字符串（可以替换该字符串里的所有指定字符子串）
# 原字符串, 旧字符串, 新字符串 SELECT REPLACE('whibin is handsome','handsome','gorgeous'); # 结果为：whibin is gorgeous
数学函数
- round
-
四舍五入
# 默认变成整数 SELECT ROUND(1.65789); # 结果为：2 # 变成指定的小数点后的位数 SELECT ROUND(1.889,2) # 结果为：1.89
- ceil
-
向上取整
SELECT CEIL(1.2) # 结果为：2
- floor
-
向下取整
SELECT FLOOR(1.234) # 结果为：1
- truncate
-
截断，保留小数点后指定的位数
SELECT TRUNCATE(1.234,2) # 结果为：1.23
- mod
-
取模
# a,b 返回值为a%b SELECT MOD(10,3) # 结果为：1 # 等同于： SELECT 10%3 # 结果为：1
日期函数
- now
-
返回当前系统日期时间
SELECT NOW() # 结果为：2020-06-04 10:42:08
- curdate
-
返回当前系统日期（不包含时间）
SELECT CURDATE() # 结果为：2020-06-04
- curtime
-
返回当前系统时间（不包含日期）
SELECT CURTIME() # 结果为：10:42:08
- year/month/day/hour/minute/second
-
获取指定的年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) # 结果为：2020 SELECT MONTH(NOW()) # 结果为：6 # 获取月份的英文 SELECT MONTHNAME(NOW()) # 结果为：June SELECT DAY(NOW()) # 结果为：4 SELECT HOUR(NOW()) # 结果为：11 SELECT MINUTE(NOW()) # 结果为：14 SELECT SECOND(NOW()) # 结果为：2
- str_to_date
-
将字符串通过指定格式转换成日期
# 原字符串, 解析该字符串的格式 SELECT STR_TO_DATE('2002-01-19','%Y-%m-%d') # 结果为：2020-01-19
- date_format
-
将日期转换成字符串
# 日期, 解析后的字符串格式 SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日') # 结果为：2020年6月4日
- datediff
-
用于求两个日期相差的天数
SELECT DATEDIFF(NOW(),'2002-01-19') # 结果为：6711
其他函数
# 获取版本号
SELECT VERSION() # 结果为：8.0.19
# 查看当前使用的数据库
SELECT DATABASE() # 结果为：dbtest
# 查看当前的用户
SELECT USER() # 结果为：root@localhost
流程控制函数
- if
# 与三目运算符类似
SELECT IF(10>5,'big','small') # 结果为：big
- case
-
使用一
-
类似于switch-case
-
使用格式：case 表达式 when 常量1 then 结果1 when 常量2 then 语句2; else 结果3; end
-
如果then后面是值，那么不用分号，如果是语句，则要分号；else相当于default；end用于结束case语句
-
案例：
/* id为偶数就是厉害的公司，id为奇数就是不厉害的公司 */ SELECT CASE id%2 WHEN 0 THEN name END AS 厉害的公司, CASE id%2 WHEN 1 THEN name END AS 不厉害的公司 FROM dep; -
-
使用二
-
类似于多重if
-
使用格式：case when 条件1 then 值1 when 条件2 then 语句2; else 值; end
-
案例：
# 查询年龄大于20的名字，如果不是则为无 SELECT CASE WHEN age>20 THEN NAME ELSE '无' END AS 姓名 FROM euser; -
分组函数
- 功能：做统计使用，又称：统计函数、聚合函数、组函数
- 分类：
- sum
- avg
- max
- min
- count
- 特点：分组函数都忽略null
- 和distinct的搭配使用：distinct表示去重
查询
-
基本语法：
SELECT 查询列表 FROM 表1 别名 （连接类型） JOIN 表2 别名 ON 连接条件 （where 筛选条件） （group BY 分组） （having 分组后筛选条件） （order BY 排序列表） -
连接类型：
- 内连接：(inner)
- 外连接：
- 左外连接：left (outer)
- 右外连接：right (outer)
- 全外连接：full (outer)
- 交叉连接：cross
多表的连接方式
内连接
-
案例：查询员工名称及其部门名称
SELECT e.name 员工名称,d.name 公司名称 FROM emp e INNER JOIN dep d ON d.id=e.dep_id;
外连接
左/右外连接
-
案例：查询员工名称及其部门名称
SELECT e.name 员工名称,d.name 公司名称 FROM emp e LEFT OUTER JOIN dep d ON d.id=e.dep_id ORDER BY e.`name`; SELECT e.name 员工名称,d.name 公司名称 FROM emp e RIGHT OUTER JOIN dep d ON d.id=e.dep_id ORDER BY e.`name`;
全外连接
- mysql不支持全外连接
- 全外连接查询的内容为：
- 主表与次表的交集
- 主表中与次表不匹配的部分（即为NULL）
- 次表与主表不匹配的部分（即为NULL）
交叉连接
- 查询出两表进行笛卡尔乘积后的数据
子查询
多行操作符
- IN/NOT IN
- NOT IN表示不等于该列表中的任意一个
- ANY/SOME
- 和子查询列表中的某一个值进行比较
- 例如：a > any(10,20,30)，只要a大于10，即可满足条件
- 上述例子可以被代替为：a > min(10,20,30)
- 由于该关键字比较晦涩，且可以用可读性更高的方式代替，所以该关键字不常用。
- ALL
- 和子查询列表中的所有值进行比较
- 例如：a > all(10,20,30)，只要a大于30，即可满足条件
- 上述例子可以被代替为：a > max(10,20,30)
exists关键字
- 相关子查询
select exists(查询语句)
# 返回结果为1/0
联合查询
-
概念：将多条查询语句的结果合并成一个
-
案例：
SELECT * FROM euser WHERE sex='男' UNION SELECT * FROM euser WHERE sex='女' -
特点：
-
要求多条查询语句的查询列数是一致的
-
多条查询语句查询的每一列的类型和顺序最好一致
-
自动去除重复项。若不愿去重，可以在union关键字后加all关键字
-
即：
SELECT * FROM euser WHERE sex='男' UNION ALL SELECT * FROM euser WHERE sex='女'
-
-
插入
插入数据与子查询的联系
-
在插入一整条完整的数据时，其数据源可以由子查询来提供
-
案例：
INSERT INTO emp SELECT 7,'tsang',24000,2;
删除
delete与truncate
- delete可以加where条件，truncate不能
- truncate删除整表的效率比delete高
- 使用delete删除数据后，自增序列从断点开始；truncate从1开始
- truncate删除无返回值，delete有
- truncate删除不能回滚，delete能
-
案例：
truncate table emp;
视图
介绍
- 视图是一张虚拟的表，其数据来自各个真实的表，是由各个真实的表的数据拼凑出来的
- 当某个查询的结果集需要经常被使用时，可以将其变成一个视图，使查询便捷
创建与使用
-
语法：
# 创建视图 CREATE VIEW my_v1 AS SELECT e.name ename, d.name dname FROM dep d INNER JOIN emp e ON e.dep_id=d.id; # 使用视图 SELECT * FROM my_v1 WHERE dname='alibaba'
修改
-
语法：
# 方式一： CREATE OR REPLACE VIEW my_v1 AS SELECT e.name ename, d.name dname FROM dep d INNER JOIN emp e ON e.dep_id=d.id; # 方式二： ALTER VIEW my_v1 AS SELECT e.name ename, d.name dname FROM dep d INNER JOIN emp e ON e.dep_id=d.id;
删除
-
语法：
DROP VIEW my_v1; # 可以一次性删除多个视图，在视图名称后加逗号，再加上新的视图名称即可
查看
-
语法：
# 查看表的结构信息 DESC my_v1; SHOW CREATE VIEW my_v1;
更新
- 其语法与操作表的数据类似
- 具备以下特点的视图是不允许更新的：
- 包含以下关键字的sql语句：
- 分组函数
- distinct
- group by
- having
- union/union all
- 常量视图
- select中包含子查询
- join
- from一个不能更新的视图
- where子句的子查询引用了from子句的表
- 包含以下关键字的sql语句：
变量
系统变量
-
分类：
- 全局变量
- 会话变量
-
概念：变量由系统提供，不由用户定义，属于服务器层面
-
使用语法：
-
查看所有系统变量：
# 查看全局变量 SHOW GLOBAL VARIABLES; # 查看会话变量 SHOW (SESSION) VARIABLES; -
查看满足条件的部分系统变量：
# 查看字符集 SHOW GLOBAL VARIABLES LIKE '%character%' -
查看指定的某个系统变量：
# 查看指定的全局变量"time_zone" SELECT @@global.time_zone; # 查看指定的会话变量 SELECT @@(session.)time_zone; -
为某个系统变量赋值
# 方式一： # 为全局变量赋值 SET GLOBAL time_zone='+8:00' # 为会话变量赋值 SET (SESSION) time_zone='+8:00' # 方式二： SET @@global.time_zone='+8:00' SET @@(session.)time_zone='+8:00'
-
-
作用域：
- 全局变量：服务器每次启动将为所有的全局变量赋初值，针对于所有的会话（连接）有效，不能跨重启
- 会话变量：仅仅针对于当前会话（连接）有效
自定义变量
- 分类：
- 用户变量
- 局部变量
- 概念：变量是用户自定义的，不由系统定义
用户变量
-
作用域：针对于当前会话（连接）有效，同于会话变量的作用域
-
使用步骤：
-
声明并初始化（要求声明时必须初始化）
# 三种方式： SET @test_v=0 SET @test_v:=1 SELECT @test_v:=2 -
更新用户变量
# 方式一： # 与声明并初始化相同 SET @test_v=0 SET @test_v:=1 SELECT @test_v:=2 # 方式二： # 从表中查询出一个值，赋值给变量 SELECT 字段 INTO @变量名 FROM 表名 -
使用
# 查看变量 SELECT @test_v;
-
局部变量
-
作用域：仅仅在定义它的begin end中有效
-
使用步骤：
-
声明
DECLARE var_name dataType DECLARE var_name dataType DEFAULT val -
赋值
SET test_v=0 SET test_v:=1 SELECT @test_v:=2 SELECT 字段 INTO 变量名 FROM 表名 -
使用
SELECT test_v;
-
存储过程和函数
存储过程
-
创建：
CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体 END # 参数列表： # 参数模式 参数名 参数类型 # IN id INT # 参数模式： IN # 该参数可以作为输入 OUT # 该参数可以作为输出 INOUT # 该参数既可以作为输入，也可以作为输出 # 如果存储过程体只有一句话，begin和end关键字可以省略 # 存储过程每条语句的结束符可以重新设置 DELIMITER . # 这样就把结束符设置为：. -
调用：
CALL 名(参数) -
使用案例：
# 输入员工的名字，输出员工的公司名 DELIMITER $ CREATE PROCEDURE e2d22(IN es VARCHAR(20)) BEGIN SELECT dep.`name` FROM emp INNER JOIN dep ON emp.`dep_id`=dep.`id` WHERE emp.name=es; END $ CALL e2d22('whibin') # 或者： DELIMITER $ CREATE PROCEDURE e2d1(IN es VARCHAR(20)) BEGIN DECLARE dep_name VARCHAR(20); SELECT dep.`name` INTO dep_name FROM emp INNER JOIN dep ON emp.`dep_id`=dep.`id` WHERE emp.name=es; SELECT dep_name; END $ CALL e2d1('whibin') # 或者： DELIMITER $ CREATE PROCEDURE e2d1o(IN es VARCHAR(20), OUT dep_name VARCHAR(20)) BEGIN SELECT dep.`name` INTO dep_name FROM emp INNER JOIN dep ON emp.`dep_id`=dep.`id` WHERE emp.name=es; END $ SET @dep_n; # 也可以不定义 CALL e2d1o('whibin',@dep_n); SELECT @dep_n # 输入员工的名字，输出员工公司名和id DELIMITER $ CREATE PROCEDURE e2d1oo(IN es VARCHAR(20), OUT dep_name VARCHAR(20), OUT depi INT) BEGIN SELECT dep.`name`, dep.`id` INTO dep_name, depi FROM emp INNER JOIN dep ON emp.`dep_id`=dep.`id` WHERE emp.name=es; END $ CALL e2d1oo('whibin',@dep_n,@depi); SELECT @dep_n,@depi # 输入两个值，翻倍后并返回： DELIMITER $ CREATE PROCEDURE dou(INOUT a INT, INOUT b INT) BEGIN SET a=a*2; SET b=b*2; END $ SET @a=2; SET @b=3; CALL dou(@a,@b); SELECT @a,@b; -
删除：
DROP PROCEDURE f; -
查看结构或信息：
SHOW CREATE PROCEDURE dou;
函数
-
创建：
DELIMITER $ CREATE FUNCTION function_name(arguments) RETURNS return_value # 参数的形式和存储过程的参数类似 BEGIN function_body END $ -
调用
SELECT function_name(arguments) -
使用案例：
DELIMITER $ CREATE FUNCTION fa() RETURNS INT BEGIN DECLARE s INT DEFAULT 0; SELECT COUNT(*) INTO s FROM emp; RETURN s; END $ SELECT fa() -
查看函数结构
SHOW CREATE FUNCTION fa; -
删除函数
DROP FUNCTION fa;
流程控制结构
if结构
-
应用范围：只能用在begin-end中
-
语法：
IF situation1 THEN sentence1; ELSEIF situation2 THEN sentence2; ... ELSE sentence_n; END IF;
循环结构
-
语法：
# while语法 label: WHILE situation DO body END WHILE label; # loop语法 label: LOOP body END LOOP label; # repeat语法 label: REPEAT body UNTIL end_situation END REPEAT label;
541
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
