MySQL语言结构的总结

MYSQL语言结构

常量和变量
常量:是指在程序运行过程中值不变的量.
字符串常量:是指用单引号或双引号括起来的字符序列,分为ASCII字符串常量和Unicode字符串常量.
ASCII字符串常量例如:'hello'  'How are you!' 
UNicode字符串常量例如:N'hello' N'How are you!'
每个转义序列以一个反斜杠"/"开始,指出后面的字符使用转义字符来解释,而不是如同字符.
注意:NUL字节与NULL值不同,NUL为一个零值字节,而NULL代表没有值.
数值常量:可以分为整数常量和浮点数常量.
整形常量例如:1894,2,[phone],-2147483648.
浮点数常量例如:5.26,-1.39,101.5E5,0.5E-2.
十六进制常量:例如:select hex('cat');
日期时间常量:日期时间常量的值必须符合日期和时间的标准.例如:'2012-06-07 08:00:00:00'
位字段值:例如:select bin(b'111101'+0),oct(b'111101'+0);
布尔值:例如:select true false;
NULL:通常表示'没有值','无数据'.


变量:是在程序运行中可以变的,可以分为用户变量和系统变量.
用户变量:用户可以在表达式中使用自己定义的变量.这种变量就是用户变量.
例如:
创建用户变量:
set @name='王林';
set @user1=1,@user2=2,@user3=3;
set @user4=@user3+1;
查询用户变量:
select @name;
使用查询给变量赋值:
use xscj
set @student=(select 姓名 from xs where 学号='081101');
查询:select 学号,姓名,专业名,出生日期 from xs where 姓名=@student;
select @t2:=(@t2:=2)+5 as t2; 结果t2的值为7.
系统变量:
获取mysql的版本:
select @@version;
获取当前系统的时间:
select current_time;
全局系统变量:
set @@global.sort_buffer_size=25000;
会话系统变量:
将当前会话的sql.warnings变量设置为true:set @@SQL_warnings=on;
将系统变量的SQL_select_limit变量设置为10:
set @@session.SQL_select_limit=10;
select @@local.sql_select_limit;
将系统变量的SQL_select_limit变量设置为默认值:
set @@local.sql_select_limit=derault;
得到系统变量清单:
show variables;
show variables like 'char%';


运算符与表达式
算术运算符:+,-,*,/,%.
比较运算符:=,<>,<=,>=,<,>.
逻辑运算符:
NOT运算符:select not 1,not 0,not(1=1),not(10>9);
AND运算符:select (1=1) and (9>10),('a'='a') and ('c'='d');
OR运算符:select (1=1) or (9>10),('a'='b') or (1>2);
XOR运算符:select (1=1) xor (2=3),(1<2) xor (9>10);
位运算符:
(1)"|"运算符和"&"运算符
select 13|28,3|4,13&28,3&4;
(2)<<和>>运算符
select 1<<<7,64>>1;
(3)"^"运算符
select 1^0,12^5,123^23;
(4)"~"运算符
select ~18446744073709551614,~1;


系统内置函数
数学函数:
(1)greatest()和least()函数分别获得最大值和最小值.
select greatest(10,9,128,1),least(1,2,3);结果为128,1
(2)floor()和ceiling()函数分别获得小于一个数的最大数值和大于一个数的最小数值.
select floor(-1.2),ceiling(-1.2),floor(9.9),ceiling(9.9);结果为-2,-1,9,10.
(3)round()和truncate()函数获取一个数的四舍五入的整数值和把一个数字截取一个指定小数个数的数字.
select round(5.1),round(25.501),round(9.8);
select truncate(1.54578,2),truncate(-76.12,5);
(4)abs()函数获的一个数的绝对值
select abs(-878),abs(-8.345);
(5)sign()函数返回的结果是整数(1),负数(-1),零(0).
select sign(-2),sign(2),sign(0);
(6)sqrt()函数返回一个数的平方根.
select sqrt(25),sqrt(15),sqrt(1);
(7)pow()函数以一个数作为另一个数的指数.
select pow(2,2),pow(10,-2),pow(0,3);
(8)sin(),cos(),tan()函数返回一个角度的正弦,余弦,正切值.
select sin(1),cos(1),tan(radians(45));
(9)asin(),acos(),atan()函数返回一个角度的反正弦,反余弦,反正切值.
select asin(1),acos(1),atan(radians(45));
(10)bin(),otc(),hex()函数
select bin(2),oct(12),hex(80);


聚合函数
字符串函数:
(1)ASCII (char)
返回字符表达式最左端字符的ASCII值。参数char的类型为字符型的表达式，返回值为整型。
返回字母A的ASCII码值:SELECT ASCII('A');
(2)CHAR()函数
语法格式：
CHAR (x1,x2,x3,…) 
将x1、x2……的ASCII码转换为字符，结果组合成一个字符串。参数x1，x2，x3……为介于0～255之间的整数，返回值为字符型。
返回ASCII码值为65、66、67的字符，组成一个字符串:SELECT CHAR(65,66,67);
(3)LEFT和RIGHT函数
语法格式：
LEFT | RIGHT ( str ,x ) 
分别返回从字符串str左边和右边开始指定x个字符。
返回KC表中课程名最左边的3个字符。
USE XSCJ
SELECT LEFT(课程名, 3) 
FROM KC;
（4）TRIM、LTRIM 和RTRIM函数
语法格式：
TRIM | LTRIM | RTRIM(str)
使用LTRIM和RTRIM分别删除字符串中前面的空格和尾部的空格，返回值为字符串。参数str为字符型表达式，返回值类型为varchar。
TRIM删除字符串首部和尾部的所有空格。
SELECT TRIM('  MySQL   ');
（5）RPAD和LPAD函数
语法格式：
RPAD | LPAD( str, n, pad)
使用RPAD和LPAD分别用字符串pad对字符串str的右边和左边进行填补直至str中字符数目达到n个，最后返回填补后的字符串。若str中的字符个数大于n，则返回str的前n个字符。
SELECT RPAD('中国加油',8, '!'), LPAD('welcome',10, '*');
（6）REPLACE函数
语法格式：
REPLACE (str1 , str2 , str3 )
REPLACE函数用于用字符串str3替换str1中所有出现的字符串str2。最后返回替换后的字符串。
SELECT REPLACE('Welcome to CHINA', 'o', 'K');
（7）CONCAT函数
语法格式：
CONCAT(s1,s2,…sn)
CONCAT函数用于连接指定的几个字符串。
SELECT CONCAT('中国', '加油');
（8）SUBSTRING函数
语法格式：
SUBSTRING (expression , Start, Length ) 
返回expression中指定的部分数据。参数expression可为字符串、二进制串、text、image字段或表达式。Start、Length均为整型，前者指定子串的开始位置，后者指定子串的长度（要返回字节数）。如果 expression 是字符类型和二进制类型，则返回值类型与expression的类型相同。如果为text类型，返回的是varchar类型。
说明：LENGTH函数的作用是返回一个字符串的长度。
（9）STRCMP函数
语法格式：
STRCMP(s1,s2)
STRCMP函数用于比较两个字符串，相等返回0，s1大于s2返回1，s1小于s2返回?1。
SELECT STRCMP('A', 'A'), STRCMP('ABC', 'OPQ'),STRCMP('T', 'B');


日期和时间函数
（1）NOW()
使用NOW()函数可以获得当前的日期和时间，它以YYYY-MM-DD HH∶MM∶SS的格式返回当前的日期和时间：
SELECT NOW();
（2）CURTIME()和CURDATE()
CURTIME()和CURDATE()函数比NOW更为具体化，它们分别返回的是当前的时间和日期，没有参数：
SELECT CURTIME(),CURDATE();
（3）YEAR()
YEAR()函数分析一个日期值并返回其中关于年的部分：
SELECT YEAR(20080512142800),YEAR('1982-11-02');
（4）MOTNTH()和MONTHNAME()
MOTNTH()和MONTHNAME()函数分别以数值和字符串的格式返回月的部分：
SELECT MONTH(20080512142800), MONTHNAME('1982-11-02');
（5）DAYOFYEAR()，DAYOFWEEK()和DAYOFMONTH()
DAYOFYEAR()，DAYOFWEEK()和DAYOFMONTH()函数分别返回这一天在一年、一星期及一个月中的序数：
SELECT DAYOFYEAR(20080512),DAYOFMONTH('2008-05-12');
（6）DAYNAME()
和MONTHNAME()相似，DAYNAME()以字符串形式返回星期名：
SELECT DAYNAME('2008-06-01');
（7）WEEK()和YEARWEEK()
WEEK()函数返回指定的日期是一年的第几个星期，而YEARWEEK()函数返回指定的日期是哪一年的哪一个星期：
SELECT WEEK('2008-05-01'),YEARWEEK(20080501);
（8）HOUR()、MINUTE()和SECOND()
HOUR()，MINUTE()和SECOND()函数分别返回时间值的小时、分钟和秒的部分：
SELECT HOUR(155300),MINUTE('15:53:00'),SECOND(143415);
（9）DATE_ADD()和DATE_SUB()
DATE_ADD()和DATE_SUB()函数可以对日期和时间进行算术操作，它们分别用来增加和减少日期值
DATE_ADD()和DATE_SUB()函数的语法格式为：
DATE_ADD | DATE_SUB(date, INTERVAL int keyword)
date是需要的日期和时间，INTERVAL关键字表示一个时间间隔。int表示需要计算的时间值，keyword已经在表6.9中列出。DATE_ADD函数是计算date加上间隔时间后的值，DATE_SUB则是计算date减去时间间隔后的值。
举例：
SELECT DATE_ADD('1986-08-08', INTERVAL 17 DAY);

SELECT DATE_SUB('1998-08-20 10:25:35', INTERVAL 20 MINUTE);

加密函数
（1）AES_ENCRYPT和AES_DECRYPT函数
语法格式为：
AES_ENCRYPT | AES_DECRYPT(str,key)
AES_ENCRYPT函数返回的是密钥key对字符串str利用高级加密标准（AES）算法加密后的结果，结果是一个二进制的字符串，以BLOB类型存储。而AES_DECRYPT函数用于对用高级加密方法加密的数据进行解密。若检测到无效数据或不正确的填充，函数会返回NULL。AES_ENCRYPT和AES_DECRYPT函数可以被看做MySQL中普遍使用的最安全的加密函数。
（2）ENCODE和DECODE函数
语法格式为：
ENCODE | DECODE(str,key)
ENCODE函数用来对一个字符串str进行加密，返回的结果是一个二进制字符串，以BLOB类型存储。DECODE函数使用正确的密钥对加密后的值进行解密。与上面的AES_ENCRYPT和AES_DECRYPT函数相比，这两个函数加密程度相对较弱。
（3）ENCRYPT函数
使用UNIX crypt()系统加密字符串，ENCRYPT(str，salt)函数接收要加密的字符串和用于加密过程的salt（一个可以确定唯一口令的字符串）。在Windows上不可用。
（4）PASSWORD函数
格式为：
PASSWORD(str)
返回字符串str加密后的密码字符串，适合于插入到MySQL的安全系统。该加密过程不可逆，和UNIX密码加密过程使用不同的算法。主要用于MySQL的认证系统。
返回字符串“MySQL”的加密版本。

SELECT PASSWORD('MySQL');

控制流函数
（1）IFNULL和NULLIF函数
IFNULL函数的语法格式为：
IFNULL(expr1,expr2)
此函数的作用是：判断参数expr1是否为NULL，当参数expr1为NULL时返回expr2，不为NULL时返回expr1。IFNULL的返回值是数字或字符串。
SELECT IFNULL(1,2), IFNULL(NULL, 'MySQL'), IFNULL(1/0, 10);
NULLIF函数的语法格式为：
NULLIF(expr1,expr2)
NULLIF函数用于检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等就返回第一个参数。
SELECT NULLIF(1,1), NULLIF('A', 'B'), NULLIF(2+3, 3+4);
（2）IF函数
和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件    测试。
语法格式如下：
IF(expr1,expr2,expr3)
这个函数有3个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数；如果为假，IF()将会返回第三个参数。
判断2*4是否大于9–5，是则返回“是”，否则返回“否”。
SELECT IF(2*4>9–5, '是', '否');

格式化函数

（1）FORMAT()函数
语法格式为：
FORMAT(x, y)
FORMAT()函数把数值格式化为以逗号间隔的数字序列。FORMAT()的第一个参数x是被格式化的数据，第二个参数y是结果的小数位数。
例如：
SELECT FORMAT(11111111111.23654,2), FORMAT(?5468,4);
（2）DATE_FORMAT()和TIME_FORMAT()函数
DATE_FORMAT()和TIME_FORMAT()函数可以用来格式化日期和时间值。
语法格式如下：
DATE_FORMAT/ TIME_FORMAT(date | time, fmt)
其中，date和time是需要格式化的日期和时间值，fmt是日期和时间值格式化的形式
注意：这两个函数是对大小写敏感的。
（3）INET_NTOA()和INET_ATON()函数
MySQL中的INET_NTOA()和INET_ATON()函数可以分别把IP地址转换为数字或者进行相反的操作。如下面的例子所示：
SELECT INET_ATON('192.168.1.1');


类型转换函数
语法格式：
CAST(expr, AS type)
expr是CAST函数要转换的值，type是转换后的数据类型。
在CAST函数中MySQL支持这几种数据类型：BINARY、CHAR、DATE、TIME、DATETIME、SIGNED和UNSIGNED。
SELECT 1+'99', 1+CAST('99' AS SIGNED);
字符串可以指定为BINARY类型，这样它们的比较操作就成为大小写敏感的。使用CAST()函数指定一个字符串为BINARY和字符串前面使用BINARY关键词具有相同的作用。
SELECT 'a'=BINARY 'A', 'a'=CAST('A' AS BINARY);
说明：两个表达式的结果都为零表示两个表达式都为假。
MySQL还可以强制将日期和时间函数的值作为一个数而不是字符串输出。
将当前日期显示成数值形式。

SELECT CAST(CURDATE() AS SIGNED);

系统信息函数

(1.)DATABASE()、USER()和VERSION()函数可以分别返回当前所选数据库、当前用户和MySQL版本信息：
SELECT DATABASE(),USER(), VERSION();
（2）BENCHMARK() 函数用于重复执行n次表达式expr。它可以被用于计算MySQL 处理表达式的速度。结果值通常为零。另一种用处来自 MySQL客户端内部，能够报告问询执行的次数，根据经过的时间值可以推断服务器的性能。例如：
SELECT BENCHMARK(10000000, ENCODE('hello','goodbye'));
（3）FOUND_ROWS()函数用于返回最后一个SELECT语句返回的记录行的数目。如最后执行的SELECT语句是：
SELECT * FROM XS;
之后执行如下语句：
SELECT FOUND_ROWS();
说明：SELECT语句可能包括一个LIMIT子句，用来限制服务器返回客户端的行数。在有些情况下，需要不用再次运行该语句而得知在没有LIMIT 时到底该语句返回了多少行。为了知道这个行数，包括在SELECT 语句中选择SQL_CALC_FOUND_ROWS，随后调用FOUND_ROWS()。
例如，执行如下语句：
SELECT SQL_CALC_FOUND_ROWS * FROM XS WHERE 性别=1 LIMIT 5; 
之后可以使用FOUND_ROWS()函数，显示在没有LIMIT子句的情况下SELECT语句所返回的行数。