MySQL使用操作点总结

Posted on 2021-08-20 17:07  小愉  阅读(55)  评论(0)  编辑  收藏  举报

创建数据库：create database 数据库名；

删除数据库：drop database 数据库名；

选择数据库：use 数据库名；

数据类型：

类型大小范围（有符号）范围（无符号）用途
TINYINT 1 byte (-128，127) (0，255) 小整数值
SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间：

类型大小 ( bytes)范围格式用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型：

类型大小用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

创建数据表：CREATE TABLE 数据表名 (column_name column_type);

删除数据表：drop table 数据表名；

在数据表中插入数据：insert into 数据表名(field1,field2)values(value1,value2)*字符+"";

where操作符：

操作符描述实例
= 等号，检测两个值是否相等，如果相等返回true (A = B) 返回false。
<>, != 不等于，检测两个值是否相等，如果不相等返回true (A != B) 返回 true。
> 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true (A > B) 返回false。
< 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true (A < B) 返回 true。
>= 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true (A >= B) 返回false。
<= 小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true (A <= B) 返回 true。

在数据表中读取数据：select * from 数据表 where field="";(可以使用binary区分大小写)

在数据表中更新数据：update 数据表 set 改变的信息(field="") where 索引的信息(field="");

删除数据表中的数据：delete from 数据表 where field="";

在数据表中获取数据：select * from 数据表 where field like '%?'；

在数据表中筛选数据：select field from 数据表 union（union all全部包括重复数据）select field from 数据表 country；

在数据表对数据排序：select * from 数据表 order by field asc/desc；（升序/降序）

对数据表中数据分组：select field(同后), count(*) from 数据表 group by field(同前);

在数据表中分组计数：select field1, sum(field2) as singin_count from bat group by field1 with rollup;

在数据表中连接数据：select a.field1,a.field2 b.field1 from 表2 a inner/left/right join 表1 b on a.field2=b.field2;(内连接，左连接，右连接)

在数据表中查询null：select * from 数据表 where field(空的null) is null;

在数据表中排除null：select * from 数据表 where field(空的null) is not null;

查询字段：show columns from 数据表；

正则表达式应用于regexp操作符：

模式描述
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
. 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。
[...] 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...] 负值字符集合。匹配未包含的任意字符。例如， 'abc' 可以匹配 "plain" 中的'p'。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+ 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

MySQL事务（ACID）

原子性：一个事务中的所有操作，全部完成/不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精准度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交、读提交、可重复读和串行化。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句：

  • begin或start transaction显式地开启一个事务；

  • commit 也可以使用 commit work，不过二者是等价的。commit会提交事务，并使已对数据库进行的所有修改成为永久性的；

  • rollback也可以使用rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

  • savepoint identifier，savepoint 允许在事务中创建一个保存点，一个事务中可以有多个savepoint；

  • release savepoint identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

  • rollback to identifier 把事务回滚到标记点；

  • set transaction 用来设置事务的隔离级别。innoDB存储引擎提供事务的隔离级别有read uncommitted、read commited、repeatable read和serializable。

MySQL事务处理主要有两种方法：

  1. 用begin，rollback，commit来实现

    • begin开始一个事务

    • rollback事务回滚

    • commit事务确认

  2. 直接用set来改变MySQL的自动提交模式

    • set autocommit=0禁止自动提交

    • set autocommit=1开启自动提交

alter命令增删改字段

删：alter table 数据表 drop field；

增：alter table 数据表 add field 数据类型；

first 和 after 关键字可用于 add 与 modify 子句，所以如果你想重置数据表字段的位置就需要先使用 drop 删除字段然后使用 add 来添加字段并设置位置。

改：alter table 数据表 modify field 数据类型（char(10)）;

change子句：

alter table 数据表 change 字段 新字段 类型；

修改字段默认null值：

alter table 数据表 field set defaulf 数值;

alter table 数据表 alter field drop default；

修改表名：alter table 数据表原名 rename to 改后数据表名;

索引？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？

临时表：

 

MySQL元数据：

  • 查询结果信息：select，update，delete语句影响的记录数

  • 数据库和数据表的信息：包含了数据库及数据表的结构信息

  • MySQL服务器信息：包含了数据库服务器的当前状态，版本号

获取服务器元数据

命令描述
SELECT VERSION( ) 服务器版本信息
SELECT DATABASE( ) 当前数据库名 (或者返回空)
SELECT USER( ) 当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES 服务器配置变量

MySQL序列使用：

使用auto_increment数据表中无需指定值实现自动增长

重置序列

设置序列的开始值

MySQL处理重复数据

设置主键表示唯一性，双主键的话默认值不能为null，设置not null

insert ignore into插入数据如果发生重叠，会跳过，不会报错

replace into 存在primary或unique相同记录，会先删除在插入新数据（另一种设置数据唯一性的方法就是添加一个unique索引）

统计重复数据：

查询重复值执行操作：

  • 确定那一列包含的值可能会重复

  • 在列选择列表使用count(*)列出的那些列

  • 在group by 子句中列出的列

  • having子句设置重复数大于1

过滤重复数据：

删除重复数据：

也可以数据表中添加index（索引）和primary key（主键）删除

SQL注入~~~~

导入数据&导出数据-------------------------------

 

MySQL 字符串函数

函数描述实例
ASCII(s) 返回字符串 s 的第一个字符的 ASCII 码。 返回 CustomerName 字段第一个字母的 ASCII 码：SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
CHAR_LENGTH(s) 返回字符串 s 的字符数 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
CHARACTER_LENGTH(s) 返回字符串 s 的字符数 返回字符串 RUNOOB 的字符数SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn) 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 合并多个字符串，并添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2...) 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 返回字符串 c 在列表值中的位置：SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置 返回字符串 c 在指定字符串中的位置：SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n) 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 格式化数字 "#,###.##" 形式：SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
INSERT(s1,x,len,s2) 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com
LOCATE(s1,s) 从字符串 s 中获取 s1 的开始位置 获取 b 在字符串 abc 中的位置：SELECT LOCATE('st','myteststring'); -- 5返回字符串 abc 中 b 的位置：SELECT LOCATE('b', 'abc') -- 2
LCASE(s) 将字符串 s 的所有字母变成小写字母 字符串 RUNOOB 转换为小写：SELECT LCASE('RUNOOB') -- runoob
LEFT(s,n) 返回字符串 s 的前 n 个字符 返回字符串 runoob 中的前两个字符：SELECT LEFT('runoob',2) -- ru
LOWER(s) 将字符串 s 的所有字母变成小写字母 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len 将字符串 xx 填充到 abc 字符串的开始处：SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s) 去掉字符串 s 开始处的空格 去掉字符串 RUNOOB开始处的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len) 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
POSITION(s1 IN s) 从字符串 s 中获取 s1 的开始位置 返回字符串 abc 中 b 的位置：SELECT POSITION('b' in 'abc') -- 2
REPEAT(s,n) 将字符串 s 重复 n 次 将字符串 runoob 重复三次：SELECT REPEAT('runoob',3) -- runoobrunoobrunoob
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc
REVERSE(s) 将字符串s的顺序反过来 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba
RIGHT(s,n) 返回字符串 s 的后 n 个字符 返回字符串 runoob 的后两个字符：SELECT RIGHT('runoob',2) -- ob
RPAD(s1,len,s2) 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len 将字符串 xx 填充到 abc 字符串的结尾处：SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s) 去掉字符串 s 结尾处的空格 去掉字符串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB
SPACE(n) 返回 n 个空格 返回 10 个空格：SELECT SPACE(10);
STRCMP(s1,s2) 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 比较字符串：SELECT STRCMP("runoob", "runoob"); -- 0
SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING_INDEX(s, delimiter, number) 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 SELECT SUBSTRING_INDEX('a*b','*',1) -- a SELECT SUBSTRING_INDEX('a*b','*',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c
TRIM(s) 去掉字符串 s 开始和结尾处的空格 去掉字符串 RUNOOB 的首尾空格：SELECT TRIM(' RUNOOB ') AS TrimmedString;
UCASE(s) 将字符串转换为大写 将字符串 runoob 转换为大写：SELECT UCASE("runoob"); -- RUNOOB
UPPER(s) 将字符串转换为大写 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB

 

MySQL 数字函数

函数名描述实例
ABS(x) 返回 x 的绝对值 返回 -1 的绝对值：SELECT ABS(-1) -- 返回1
ACOS(x) 求 x 的反余弦值(参数是弧度) SELECT ACOS(0.25);
ASIN(x) 求反正弦值(参数是弧度) SELECT ASIN(0.25);
ATAN(x) 求反正切值(参数是弧度) SELECT ATAN(2.5);
ATAN2(n, m) 求反正切值(参数是弧度) SELECT ATAN2(-0.8, 2);
AVG(expression) 返回一个表达式的平均值，expression 是一个字段 返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x) 返回大于或等于 x 的最小整数 SELECT CEIL(1.5) -- 返回2
CEILING(x) 返回大于或等于 x 的最小整数 SELECT CEILING(1.5); -- 返回2
COS(x) 求余弦值(参数是弧度) SELECT COS(2);
COT(x) 求余切值(参数是弧度) SELECT COT(6);
COUNT(expression) 返回查询的记录总数，expression 参数是一个字段或者 * 号 返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x) 将弧度转换为角度 SELECT DEGREES(3.1415926535898) -- 180
n DIV m 整除，n 为被除数，m 为除数 计算 10 除于 5：SELECT 10 DIV 5; -- 2
EXP(x) 返回 e 的 x 次方 计算 e 的三次方：SELECT EXP(3) -- 20.085536923188
FLOOR(x) 返回小于或等于 x 的最大整数 小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...) 返回列表中的最大值 返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
LEAST(expr1, expr2, expr3, ...) 返回列表中的最小值 返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
LN 返回数字的自然对数，以 e 为底。 返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453
LOG(x) 或 LOG(base, x) 返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。 SELECT LOG(20.085536923188) -- 3 SELECT LOG(2, 4); -- 2
LOG10(x) 返回以 10 为底的对数 SELECT LOG10(100) -- 2
LOG2(x) 返回以 2 为底的对数 返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156
MAX(expression) 返回字段 expression 中的最大值 返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression) 返回字段 expression 中的最小值 返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y) 返回 x 除以 y 以后的余数 5 除于 2 的余数：SELECT MOD(5,2) -- 1
PI() 返回圆周率(3.141593） SELECT PI() --3.141593
POW(x,y) 返回 x 的 y 次方 2 的 3 次方：SELECT POW(2,3) -- 8
POWER(x,y) 返回 x 的 y 次方 2 的 3 次方：SELECT POWER(2,3) -- 8
RADIANS(x) 将角度转换为弧度 180 度转换为弧度：SELECT RADIANS(180) -- 3.1415926535898
RAND() 返回 0 到 1 的随机数 SELECT RAND() --0.93099315644334
ROUND(x) 返回离 x 最近的整数 SELECT ROUND(1.23456) --1
SIGN(x) 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 SELECT SIGN(-10) -- (-1)
SIN(x) 求正弦值(参数是弧度) SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x) 返回x的平方根 25 的平方根：SELECT SQRT(25) -- 5
SUM(expression) 返回指定字段的总和 计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x) 求正切值(参数是弧度) SELECT TAN(1.75); -- -5.52037992250933
TRUNCATE(x,y) 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） SELECT TRUNCATE(1.23456,3) -- 1.234

MySQL 日期函数

函数名描述实例
ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期 SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25
ADDTIME(t,n) n 是一个时间表达式，时间 t 加上时间表达式 n 加 5 秒：SELECT ADDTIME('2011-11-11 11:11:11', 5); ->2011-11-11 11:11:16 (秒)添加 2 小时, 10 分钟, 5 秒:SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26
CURDATE() 返回当前日期 SELECT CURDATE(); -> 2018-09-19
CURRENT_DATE() 返回当前日期 SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME 返回当前时间 SELECT CURRENT_TIME(); -> 19:59:02
CURRENT_TIMESTAMP() 返回当前日期和时间 SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43
CURTIME() 返回当前时间 SELECT CURTIME(); -> 19:59:02
DATE() 从日期或日期时间表达式中提取日期值 SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数 SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
DATE_ADD(d，INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期，type 值可以是：MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06-25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE); -> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-04-15
DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。 Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
DAY(d) 返回日期值 d 的日期部分 SELECT DAY("2017-06-15"); -> 15
DAYNAME(d) 返回日期 d 是星期几，如 Monday,Tuesday SELECT DAYNAME('2011-11-11 11:11:11') ->Friday
DAYOFMONTH(d) 计算日期 d 是本月的第几天 SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11
DAYOFWEEK(d) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
DAYOFYEAR(d) 计算日期 d 是本年的第几天 SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
EXTRACT(type FROM d) 从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11
FROM_DAYS(n) 计算从 0000 年 1 月 1 日开始 n 天后的日期 SELECT FROM_DAYS(1111) -> 0003-01-16
HOUR(t) 返回 t 中的小时值 SELECT HOUR('1:2:3') -> 1
LAST_DAY(d) 返回给给定日期的那一月份的最后一天 SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
LOCALTIME() 返回当前日期和时间 SELECT LOCALTIME() -> 2018-09-19 20:57:43
LOCALTIMESTAMP() 返回当前日期和时间 SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year) 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 SELECT MAKEDATE(2017, 3); -> 2017-01-03
MAKETIME(hour, minute, second) 组合时间，参数分别为小时、分钟、秒 SELECT MAKETIME(11, 35, 4); -> 11:35:04
MICROSECOND(date) 返回日期参数所对应的微秒数 SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23
MINUTE(t) 返回 t 中的分钟值 SELECT MINUTE('1:2:3') -> 2
MONTHNAME(d) 返回日期当中的月份名称，如 November SELECT MONTHNAME('2011-11-11 11:11:11') -> November
MONTH(d) 返回日期d中的月份值，1 到 12 SELECT MONTH('2011-11-11 11:11:11') ->11
NOW() 返回当前日期和时间 SELECT NOW() -> 2018-09-19 20:57:43
PERIOD_ADD(period, number) 为 年-月 组合日期添加一个时段 SELECT PERIOD_ADD(201703, 5); -> 201708
PERIOD_DIFF(period1, period2) 返回两个时段之间的月份差值 SELECT PERIOD_DIFF(201710, 201703); -> 7
QUARTER(d) 返回日期d是第几季节，返回 1 到 4 SELECT QUARTER('2011-11-11 11:11:11') -> 4
SECOND(t) 返回 t 中的秒钟值 SELECT SECOND('1:2:3') -> 3
SEC_TO_TIME(s) 将以秒为单位的时间 s 转换为时分秒的格式 SELECT SEC_TO_TIME(4320) -> 01:12:00
STR_TO_DATE(string, format_mask) 将字符串转变为日期 SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10
SUBDATE(d,n) 日期 d 减去 n 天后的日期 SELECT SUBDATE('2011-11-11 11:11:11', 1) ->2011-11-10 11:11:11 (默认是天)
SUBTIME(t,n) 时间 t 减去 n 秒的时间 SELECT SUBTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:06 (秒)
SYSDATE() 返回当前日期和时间 SELECT SYSDATE() -> 2018-09-19 20:57:43
TIME(expression) 提取传入表达式的时间部分 SELECT TIME("19:30:10"); -> 19:30:10
TIME_FORMAT(t,f) 按表达式 f 的要求显示时间 t SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM
TIME_TO_SEC(t) 将时间 t 转换为秒 SELECT TIME_TO_SEC('1:12:00') -> 4320
TIMEDIFF(time1, time2) 计算时间差值 SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01
TIMESTAMP(expression, interval) 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 SELECT TIMESTAMP("2017-07-23", "13:10:11"); -> 2017-07-23 13:10:11
TO_DAYS(d) 计算日期 d 距离 0000 年 1 月 1 日的天数 SELECT TO_DAYS('0001-01-01 01:01:01') -> 366
WEEK(d) 计算日期 d 是本年的第几个星期，范围是 0 到 53 SELECT WEEK('2011-11-11 11:11:11') -> 45
WEEKDAY(d) 日期 d 是星期几，0 表示星期一，1 表示星期二 SELECT WEEKDAY("2017-06-15"); -> 3
WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期，范围是 0 到 53 SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45
YEAR(d) 返回年份 SELECT YEAR("2017-06-15"); -> 2017
YEARWEEK(date, mode) 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 SELECT YEARWEEK("2017-06-15"); -> 201724

MySQL 高级函数

函数名描述实例
BIN(x) 返回 x 的二进制编码 15 的 2 进制编码:SELECT BIN(15); -- 1111
BINARY(s) 将字符串 s 转换为二进制字符串 SELECT BINARY "RUNOOB"; -> RUNOOB
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 SELECT CASE 　　WHEN 1 > 0 　　THEN '1 > 0' 　　WHEN 2 > 0 　　THEN '2 > 0' 　　ELSE '3 > 0' 　　END ->1 > 0
CAST(x AS type) 转换数据类型 字符串日期转换为日期：SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29
COALESCE(expr1, expr2, ...., expr_n) 返回参数中的第一个非空表达式（从左向右） SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); -> runoob.com
CONNECTION_ID() 返回唯一的连接 ID SELECT CONNECTION_ID(); -> 4292835
CONV(x,f1,f2) 返回 f1 进制数变成 f2 进制数 SELECT CONV(15, 10, 2); -> 1111
CONVERT(s USING cs) 函数将字符串 s 的字符集变成 cs SELECT CHARSET('ABC') ->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk
CURRENT_USER() 返回当前用户 SELECT CURRENT_USER(); -> guest@%
DATABASE() 返回当前数据库名 SELECT DATABASE(); -> runoob
IF(expr,v1,v2) 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 SELECT IF(1 > 0,'正确','错误') ->正确
IFNULL(v1,v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 SELECT IFNULL(null,'Hello Word') ->Hello Word
ISNULL(expression) 判断表达式是否为 NULL SELECT ISNULL(NULL); ->1
LAST_INSERT_ID() 返回最近生成的 AUTO_INCREMENT 值 SELECT LAST_INSERT_ID(); ->6
NULLIF(expr1, expr2) 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 SELECT NULLIF(25, 25); ->
SESSION_USER() 返回当前用户 SELECT SESSION_USER(); -> guest@%
SYSTEM_USER() 返回当前用户 SELECT SYSTEM_USER(); -> guest@%
USER() 返回当前用户 SELECT USER(); -> guest@%
VERSION() 返回数据库的版本号 SELECT VERSION() -> 5.6.34

MySQL 运算符

MySQL 的运算符及运算符的优先级。 MySQL 主要运算符：

  • 算术运算符

  • 比较运算符

  • 逻辑运算符

  • 位运算符

算术运算符

MySQL 算术运算符:

运算符作用
+ 加法
- 减法
* 乘法
/ 或 DIV 除法
% 或 MOD 取余

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

1、加

mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+

2、减

mysql> select 1-2;
+-----+
| 1-2 |
+-----+
|  -1 |
+-----+

3、乘

mysql> select 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+

4、除

mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+

5、商

mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+

6、取余

mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+

比较运算符

比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

符号描述备注
= 等于  
<>, != 不等于  
> 大于  
< 小于  
<= 小于等于  
>= 大于等于  
BETWEEN 在两值之间 >=min&&<=max
NOT BETWEEN 不在两值之间  
IN 在集合中  
NOT IN 不在集合中  
<=> 严格比较两个NULL值是否相等 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE 模糊匹配  
REGEXP 或 RLIKE 正则式匹配  
IS NULL 为空  
IS NOT NULL 不为空  

1、等于

mysql> select 2=3;
+-----+
| 2=3 |
+-----+
|   0 |
+-----+
​
​
mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

2、不等于

mysql> select 2<>3;
+------+
| 2<>3 |
+------+
|    1 |
+------+

3、安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+


mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

        
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+

4、小于

mysql> select 2<3;
+-----+
| 2<3 |
+-----+
|   1 |
+-----+

5、小于等于

mysql> select 2<=3;
+------+
| 2<=3 |
+------+
|    1 |
+------+

6、大于

mysql> select 2>3;
+-----+
| 2>3 |
+-----+
|   0 |
+-----+

7、大于等于

mysql> select 2>=3;
+------+
| 2>=3 |
+------+
|    0 |
+------+

8、BETWEEN

mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+

9、IN

mysql> select 5 in (1,2,3,4,5);
+------------------+
| 5 in (1,2,3,4,5) |
+------------------+
|                1 |
+------------------+

10、NOT IN

mysql> select 5 not in (1,2,3,4,5);
+----------------------+
| 5 not in (1,2,3,4,5) |
+----------------------+
|                    0 |
+----------------------+

11、IS NULL

mysql> select null is NULL;
+--------------+
| null is NULL |
+--------------+
|            1 |
+--------------+

mysql> select 'a' is NULL;
+-------------+
| 'a' is NULL |
+-------------+
|           0 |
+-------------+

12、IS NOT NULL

mysql> select null IS NOT NULL;
+------------------+
| null IS NOT NULL |
+------------------+
|                0 |
+------------------+

        
mysql> select 'a' IS NOT NULL;
+-----------------+
| 'a' IS NOT NULL |
+-----------------+
|               1 |
+-----------------+

13、LIKE

mysql> select '12345' like '12%';
+--------------------+
| '12345' like '12%' |
+--------------------+
|                  1 |
+--------------------+

mysql> select '12345' like '12_';
+--------------------+
| '12345' like '12_' |
+--------------------+
|                  0 |
+--------------------+

14、REGEXP

mysql> select 'beijing' REGEXP 'jing';
+-------------------------+
| 'beijing' REGEXP 'jing' |
+-------------------------+
|                       1 |
+-------------------------+

mysql> select 'beijing' REGEXP 'xi';
+-----------------------+
| 'beijing' REGEXP 'xi' |
+-----------------------+
|                     0 |
+-----------------------+

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

运算符号作用
NOT 或 ! 逻辑非
AND 逻辑与
OR 逻辑或
XOR 逻辑异或

1、与

mysql> select 2 and 0;
+---------+
| 2 and 0 |
+---------+
|       0 |
+---------+

        
mysql> select 2 and 1;   
+---------+     
| 2 and 1 |      
+---------+      
|       1 |      
+---------+

2、或

mysql> select 2 or 0;
+--------+
| 2 or 0 |
+--------+
|      1 |
+--------+

mysql> select 2 or 1;
+--------+
| 2 or 1 |
+--------+
|      1 |
+--------+

mysql> select 0 or 0;
+--------+
| 0 or 0 |
+--------+
|      0 |
+--------+

mysql> select 1 || 0;
+--------+
| 1 || 0 |
+--------+
|      1 |
+--------+

3、非

mysql> select not 1;
+-------+
| not 1 |
+-------+
|     0 |
+-------+

mysql> select !0;
+----+
| !0 |
+----+
|  1 |
+----+

4、异或

mysql> select 1 xor 1;
+---------+
| 1 xor 1 |
+---------+
|       0 |
+---------+

mysql> select 0 xor 0;
+---------+
| 0 xor 0 |
+---------+
|       0 |
+---------+

mysql> select 1 xor 0;
+---------+
| 1 xor 0 |
+---------+
|       1 |
+---------+

mysql> select null or 1;
+-----------+
| null or 1 |
+-----------+
|         1 |
+-----------+

mysql> select 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
|     1 |
+-------+

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

运算符号作用
& 按位与
| 按位或
^ 按位异或
! 取反
<< 左移
>> 右移

1、按位与

mysql> select 3&5;
+-----+
| 3&5 |
+-----+
|   1 |
+-----+

2、按位或

mysql> select 3|5;
+-----+
| 3|5 |
+-----+
|   7 |
+-----+

3、按位异或

mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+

4、按位取反

mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
|                     3 |
+-----------------------+

5、按位右移

mysql> select 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+

6、按位左移

mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

运算符优先级

最低优先级为： :=。

最高优先级为： !、BINARY、 COLLATE。