MySQL重要知识点

最新推荐文章于 2024-09-05 09:59:10 发布

misthee

最新推荐文章于 2024-09-05 09:59:10 发布

阅读量752

点赞数 1

分类专栏：数据库文章标签： MySQL

本文链接：https://blog.csdn.net/qq_35312171/article/details/83277758

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

十六进制：十六进制值的默认类型是字符串。如果想要确保该值能作为数字处理，可以使用CAST(...AS UNSIGNED)：
SELECT 0x41，CAST(0x41 AS UNSIGNED)；
HEX() 将一个字符串或数字转换为十六进制格式的字符串。
位域：用b'value'符号表达位域型。value是一个用0和1写成的二进制值。
识别符：识别符可以使用引号引起来也可以不引起来。如果识别符是一个保留字或包含特殊字符，无论何时使用，必须将它引起来。识别符的引用符是反勾号(‘`’)，如果SQL服务器模式包括ANSI_QUOTES模式选项，还可以用双引号将识别符引起来：
SET sql_mode='ANSI_QUOTES';
CREATE TABLE "test" (col INT);
用户变量：@var_name 设置用户变量的一个途径是执行SET语句：
SET @var_name = expr [, @var_name = expr] ...
对于SET语句，可以使用=或:=作为分配符；在非SET语句中=被视为比较操作符，此时分配符必须为:=。
数值类型：如果为一个数值列指定ZEROFILL，MySQL将自动为该列添加UNSIGNED属性；
SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的一个别名；
在进行两个整数的减法运算时，如果其中一个整数的属性为UNSIGNED，那么减法的结果也是无符号的；
日期类型：
DATE：YYYY-MM-DD
DATETIME[(fsp)]：YYYY-MM-DD HH:MM:SS，fsp取值范围是0-6可以指定精确到秒的分位数，默认的是0，指精确到秒。
TIMESTAMP[(fsp)]：时间戳。范围是1970-01-01 00:00:00到2038-01-19 03:14:07.999999。TIMESTAMP列用于执行INSERT或UPDATE操作时记录日期和时间。
TIME：HH:MM:SS
YEAR[(2|4)]： 两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000。在两位格式中，允许的值是70到69，表示从1970年到2069年。
比较操作符：<>和!=都是不等于，<>是现在的标准；
对于行比较，(a, b) = (x, y)相当于：(a = x) AND (b = y)；
<=> 空值安全的等号：在两个操作码均为NULL时，其返回至为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL；
对于行比较，(a, b) <> (x, y)相当于：(a <> x) OR (b <> y)。
比较函数：IS (NOT) TRUE/FALSE/UNKNOWN；
expr (NOT) BETWEEN min AND max；
COALESCE(value,...) 返回参数列表当中的第一个非NULL值，在没有非NULL值的情况下返回值NULL；
GREATEST(value1,value2,...)返回值为最大(最大值的)参数；
expr IN (value,...)若expr为IN列表中的任意一个值，则其返回值为1, 否则返回值为0，在左侧表达式为NULL的情况下，或是表中找不到匹配项或是表中一个表达式为NULL的情况下，IN的返回值均为NULL；
ISNULL(expr)；
INTERVAL(N,N1,N2,N3,...)假如N < N1，则返回值为0；假如N < N2 等，则返回值为1；假如N为NULL，则返回值为-1。必须满足N1 < N2 < N3 < ……< Nn，其原因是使用了二分查找(极快速)。
逻辑运算符：NOT，！：逻辑NOT。当操作数为0时，所得值为1 ；当操作数为非零值时，所得值为0，而当操作数为NULL时，所得的返回值为NULL。
AND，&&：逻辑AND。有一个操作数为NULL时返回NULL；
OR，||：逻辑OR。（SELECT 0 || NULL）返回NULL，（SELECT 1 || NULL）返回1；
XOR：逻辑XOR。任意一个操作数为NULL时，返回值为NULL。假如有奇数个操作数为非零值，则计算所得结果为 1 ，否则为 0 。
控制流程函数：SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
ELSE部门可以省略，此时如果没有匹配的会返回NULL；
IF(expr1,expr2,expr3) 如果expr1是TRUE(expr1 <> 0 and expr1 <> NULL)，则IF()的返回值为expr2; 否则返回值则为expr3。
IFNULL(expr1,expr2) 假如expr1不为NULL，则IFNULL()的返回值为 expr1；否则其返回值为expr2。
NULLIF(expr1,expr2) 如果expr1 = expr2成立，那么返回值为NULL，否则返回值为expr1。
字符串函数：
ASCII(str)返回值为字符串str的最左字符的数值。假如str为空字符串，则返回值为 0。假如str为NULL，则返回值为NULL。
BIN(N) 返回值为N的二进制值的字符串表示，其中N为一个longlong (BIGINT)型数字。
OCT(N) 八进制。
HEX(N_or_S) 转换成16进制，如果是字符串，每个字符转换为ASCII值。
BIT_LENGTH(str) 返回值为二进制的字符串str 长度（比特数）。
CHAR(N,... [USING charset]) CHAR()将每个参数N理解为一个整数，其返回值为一个由这些参数转换为字符后组成的字符串。大于255的CHAR()参数被转换为多个字符。
CHAR_LENGTH(str) 返回值为字符串str的长度，长度单位为字符。对于一个包含五个二字节字符集,LENGTH()返回值为10,而CHAR_LENGTH()的返回值为5。
LENGTH(str) 返回值为字符串str的长度，单位为字节。
CONCAT(str1,str2,...) 返回结果为连接参数产生的字符串。
CONCAT_WS(separator,str1,str2,...) 使用分隔符连接。
ELT(N,str1,str2,str3,...) 返回第N个字符串，若N小于1或大于参数的数目，则返回值为NULL。
EXPORT_SET(bits,on,off[,separator[,number_of_bits]]) 返回值为字符串。bits中的比特值按照从右到左的顺序接受检验 (低位比特到高位比特的顺序)。字符串被分隔字符串分开(默认为逗号',')，按照从左到右的顺序被添加到结果中。
FIELD(str,str1,str2,str3,...) 返回值为str1, str2,str3,……列表中的str所在位置。在找不到str的情况下，返回值为0。
FIND_IN_SET(str,strlist) SELECT FIND_IN_SET('b','a,b,c,d');
FORMAT(X,D) 将数字X的格式设置为'#,###,###.##',以四舍五入的方式保留到小数点后D位, 返回结果为一个字符串。
INSERT(str,pos,len,newstr) 返回字符串str中起始于pos位置被字符串newstr替换长度为len 后的字符串。
INSTR(str,substr) 返回字符串str中子字符串substr第一次出现的位置。
LEFT(str,len) 返回从字符串str左边数前len个字符。
LOCATE(substr,str) 在没有参数pos时，返回为字符串str中子字符串substr的第一次出现的位置。反之，返回字符串str中以起始位置为pos开始的子字符串substr的第一次出现的位置。如若substr不在str中，则返回值为0。
LOWER(str) 转为全小写。
LPAD(str,len,padstr) 由padstr从左边填补直到长度为len。
LTRIM(str) 删除左边的空格。
MAKE_SET(bits,str1,str2,...) bits可以输入一个整数，会被转化为比特。返回一个（一个包含被‘,’号分开的字符串）由在bits集合中具有相应的比特的字符串组成的设定值。
ORD(str) 若字符串str的最左边的字符是一个多字节字符，则返回该字符的代码(1st byte code)+(2nd byte code × 256)+(3rd byte code × 256 × 256) ...；否则跟ASCII()一样。
QUOTE(str)
REPEAT(str,count) str重复3次。
REPLACE(str,from_str,to_str) 使用to_str替换str中的from_str。
REVERSE(str) 逆序
SUBSTRING(str,pos,len) 从pos位置起长度为len的子串。
SUBSTRING_INDEX(str,delim,count) 若count为正值，则返回str中第count个定界符delim(从左边开始)左边的一切内容。
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 删除所有remstr前缀或后缀。未指定remstr时，删除空格。
数学函数：DIV 整数除法。SELECT 5 DIV 2;
ABS(X) 绝对值
ACOS(X) 反余弦
ATAN(Y,X) , ATAN2(Y,X) 返回点（Y，X）的反正切。注意先纵坐标再横坐标。
CEILING(X),CEIL(X)  返回不小于X的最小整数值。
FLOOR(X)  返回不大于X的最大整数值。
CRC32(expr)  计算循环冗余码校验值并返回一个32位无符号值。
DEGREES(X) 弧度转化为度
RADIANS(X) 度转化为弧度
EXP(X) 自然指数
LN(X) 自然对数，同LOG(X)。
LOG(B,X) 返回X对于任意基数B的对数。
LOG2(X)  返回X的基数为2的对数。
MOD(N,M) , N % M ，N MOD M  模操作。
PI()
POW(X,Y) , POWER(X,Y)  返回X的Y乘方的结果值。
RAND(N)返回一个范围在0到1之间的随机数。N用作种子值，用来产生重复序列。若要在7到 12 的范围（包括7和12）内得到一个随机整数：SELECT FLOOR(7 + (RAND() * 6));
ROUND(X),ROUND(X,D)  返回与参数X最接近的整数。在有两个参数的情况下，返回保留到小数点后D位的X，而第D位的保留方式为四舍五入。若要返回保留X值小数点左边的D位，可将D设为负值。
SIGN(X)  返回参数X的符号，负、零或正。
SQRT(X)  返回非负数X的二次方根。
BIT_COUNT(N)  返回参数N中1的数量。
日期和时间函数：
ADDDATE(date,INTERVAL expr type)等价于DATE_ADD()：SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);若expr为整数，type默认为day，上述指令等价于SELECT ADDDATE('1998-01-02', 31);
DATE_SUB(date,INTERVAL expr type)
SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
```
 mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
     -> INTERVAL 1 DAY);

 mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
     -> INTERVAL '1:1' MINUTE_SECOND);

 mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
     -> INTERVAL '1 1:1:1' DAY_SECOND);

 mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
     -> INTERVAL '-1 10' DAY_HOUR);

 mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
     -> INTERVAL '1.999999' SECOND_MICROSECOND);
```
SELECT ADDTIME('1997-12-31 22:00:00', '1 1:1:20'); SELECT ADDTIME('01:00:00', '02:00:00');
CONVERT_TZ(dt,from_tz,to_tz) 将时间日期值dt从from_tz给出的时区转到to_tz给出的时区。SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
CURDATE() 将当前日期按照'YYYY-MM-DD'格式返回，等价于CURRENT_DATE,CURRENT_DATE();
CURDATE() + 0 按YYYYMMDD格式返回。
CURTIME()将当前时间以'HH:MM:SS'格式返回。
DATEDIFF(expr,expr2) 返回起始时间expr和结束时间expr2之间的天数。
DATE_FORMAT(date,format)
```
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
   ->'%D %y %a %d %m %b %j');

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
   -> '%H %k %I %r %T %S %w');

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
```
DAYNAME(date)  返回星期几。
EXTRACT(type FROM date)  从日期中提取其部分。
FROM_DAYS(N)  给定一个天数N，返回一个DATE类型的值。
FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)
GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL') 返回一个格式字符串。
```
mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));

mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
```
HOUR(time) 提取小时部分
LAST_DAY(date)  返回该月最后一天对应的值。
MAKEDATE(year,dayofyear)  给出年份值和一年中的天数，最后返回一个日期。
MAKETIME(hour,minute,second)
NOW()
PERIOD_ADD(P,N)  添加 N个月至周期P(格式为YYMM 或YYYYMM)，返回值的格式为 YYYYMM。注意周期参数P不是日期值。
PERIOD_DIFF(P1,P2)  返回周期P1和P2之间的月份数。
QUARTER(date)  返回date 对应的一年中的季度值，范围是从 1到 4。
SEC_TO_TIME(seconds)
UTC_DATE, UTC_DATE()  返回当前UTC日期值
UTC_TIME, UTC_TIME()
WEEK(date[,mode])  该函数返回date 对应的周数。
YEARWEEK(date)  返回date 对应的年份和周数。

全文搜索：
MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])

mysql> CREATE TABLE articles (
   ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   ->   title VARCHAR(200),
   ->   body TEXT,
   ->   FULLTEXT (title,body)
   -> )
   -> engine=MyISAM 
   ->;
mysql> INSERT INTO articles (title,body) VALUES
   -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
   -> ('How To Use MySQL Well','After you went through a ...'),
   -> ('Optimizing MySQL','In this tutorial we will show ...'),
   -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
   -> ('MySQL vs. YourSQL','In the following database comparison ...'),
   -> ('MySQL Security','When configured properly, MySQL ...');
mysql> SELECT * FROM articles
   -> WHERE MATCH (title,body) AGAINST ('database');
mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
   -> FROM articles;
mysql> SELECT id, body, MATCH (title,body) AGAINST
   -> ('Security implications of running MySQL as root') AS score
   -> FROM articles WHERE MATCH (title,body) AGAINST
   -> ('Security implications of running MySQL as root');
mysql> SELECT * FROM articles
   -> WHERE MATCH (title,body) AGAINST ('MySQL');

CAST操作符和函数：
BINARY操作符将后面的字符串转换成一个二进制字符串。这使得比较区分大小写，即使该列不被定义为BINARY或 BLOB类型。
SELECT BINARY 'a' = 'A';
CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name) 获取一个类型的值，转化为另一个被指定类型的值。带有USING的CONVERT()被用来在不同的字符集之间转化数据。