数据库：掌握SQL编程基础

SQL基础

结构化查询语言(Structured Query Language, SQL)是由美国国家标准协会(American National Standards Institute, ANSI)和国际标准化组织(International Standards Organization, ISO)定义的标准。MySQL致力于支持全套ANSI/ISO SQL标准，但不会以牺牲代码的速度和质量为代价。SQL是一系列操作数据库及数据库对象的命令语句，SQL语言主要包括常量和变量、表达式、运算符、控制语句等。

常量与变量

常量也称为文字值或标量值，是指程序运行中值始终不变的量。在SQL程序设计过程中，定义常量的格式取决于它所表示的值的数据类型。

# 常量类型及说明

字符串常量：包括单引号或双引号中，由字母、数字字符及特殊字符、at符和#字号组成。
十进制整型常量：使用不带小数点的十进制数据表示
十六进制整型常量：使用前缀0x后跟十六进制数字串表示
日期常量：使用单引号将日期时间字符串引起来。MySQL是按年-月-日的顺序表示日期的，中间的间隔符也可以使用其它特殊符号。
实型常量：有定点表示和浮点表示两种方式
位字段值：使用b'value'符号写位字段值。value是一个用0和1写成的二进制。直接显示b'value'的值可能是一系列特殊的符号。
布尔常量：只包含两个可能的值：TRUE和FALSE。FALSE的数字值为'0'，TRUE的数字值为'1'。
NULL值：可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义，并且不同于数字类型的“0”或字符串类型的空字符串。

变量是在程序执行过程中，其值是可以改变的量。可以利用变量存储程序执行过程中涉及的数据。变量是由变量名和变量值构成，其类型与常量一样。在MySQL系统中，存在3种类型的变量：系统变量、用户变量和局部变量。其中系统变量又分为全局变量(global)和会话变量(session)两种。

全局变量和会话变量

全局变量在MySQL启动时由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini文件来更改。会话变量在每次建立一次新的连接时，由MySQL初始化。MySQL会将当前所有全局变量的值复制一份作为会话变量。也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，则所有这些变量的值都是一样的。

全局变量与会话变量的区别就在于，全局变量主要影响整个MySQL实例的全局设置。大部分全局变量都是作为MySQL的服务器调节参数存在。对全局变量的修改会影响到整个服务器，但是对会话变量的修改只会影响到当前的会话，也就是当前的数据库连接。

大多数的系统变量应用于其他SQL语句中时，必须在名称前加上两个@符号，而为了与其他SQL产品保持一致，某些特定的系统变量要省略这两个@符号。

# 显示系统变量清单
SHOW [global|seesion] VARIABLES [LIKE '字符串'];       # 默认显示会话变量

# 修改系统变量的值
# 要想更改全局变量的值，需要拥有SUPER权限，并且修改后的值要大于等于修改前的值才可以生效。
SET system_var_name=expression
  | [global|session] system_var_name=expression
  | @@[global.|session.] system_var_name=expression;

说明：

• 改变了会话系统变量的值，同名的全局系统变量的值保持不变。

• 改变了全局系统变量的值，同名的会话系统变量的值保持不变。

要将一个系统变量值设置为MySQL默认值，可以使用default关键字。

用户变量

用户可以在表达式中使用自己定义的变量，这样的变量叫作用户变量。用户可以先在用户变量中保存值，再引用它，这样可以将值从语句传递到另一个语句。用户变量在使用前必须定义和初始化。如果使用没有初始化的变量，则它的值为NULL。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。用户变量被引用时要在其名称前加上标志@。

SET @user_variable1[:]=expression1 [, user_variable2=expression2, ...];
或
SELECT @user_variable:=expression1 [, user_variable2:=expression2, ...];

对于SET语句，可以使用“=”或“:=”作为分配符，分配给每个变量的值可以为整数、实数、字符串和NULL值。也可以用SELECT语句代替SET语句来为用户变量分配一个值，在这种情况下，分配符必须为“:=”而不能用“=”，因为在非SET语句中“=”被视为比较操作符。

在SELECT语句中，表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或ORDER BY子句中，不能使用包含SELECT列表中所设的变量的表达式。

局部变量

局部变量的作用范围是begin…end语句块。与用户变量不同的是，用户变量是以“@”开头的，局部变量不用该符号。需要注意的是，局部变量与begin…end语句块、流程控制语句只能用于函数、存储过程、触发器和事务的定义中。

DECLARE var_name [,...] type [DEFAULT value];

表达式

在SQL中，表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常都有返回值。与常量和变量一样，表达式的值也具有某种数据类型。根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期型表达式。表达式一般用在SELECT及SELECT语句的WHERE子句中。

SQL的流程控制

SQL的流程控制语句

SQL的基本结构是顺序结构、条件分支结构和循环结构。在SQL中，流程控制语句就是用来控制程序执行流程的语句，也称流控制语句或控制流语句。

# BEGIN...END语句块
BEGIN
{
    sql_statement|statement_block;
}
END;

# IF-ELSE条件语句
IF search_condition THEN        # search_condition是返回true或false的逻辑表达式
    statement_list
[ELSEIF search_condition THEN statement_list]...
[ELSE statement_list]
END IF;

# CASE分支语句
CASE 
    WHEN Boolean_expression THEN result_expression;
    [...n]
    [ELSE else_result_expression;]
END CASE;

# WHILE...END WHILE语句
[begin_label:]WHILE Boolean_expression DO
    {sql_statement|statement_block};
    [LEAVE begin_label;]
    {sql_statement|statement_block};
    [ITERATE begin_label;]
    {sql_statement|statement_block};
END WHILE;

# REPEAT...END REPEAT语句
[begin_label:]REPEAT
    {sql_statement|statement_block};
    [LEAVE begin_label;]        # 跳出循环
    {sql_statement|statement_block};
    [ITERATE begin_label;]      # 跳出本次循环
    {sql_statement|statement_block};
UNTIL Boolean_expression
END REPEAT;

# LOOP...END LOOP语句
begin_label:LOOP
    {sql_statement|statement_block};
    [LEAVE begin_label;]
    {sql_statement|statement_block};
    [ITERATE begin_label;]
    {sql_statement|statement_block};
END LOOP;

条件和处理程序的定义

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。

# 定义条件
DECLARE condition_name CONDITION FOR condition_value;
condition_value:
    SQLSTATE [VALUE] sqlstate_value|mysql_error_code

# 定义处理程序
DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement
handler_type:
    CONTINUE|EXIT|UNDO
condition_value:
    SQLSTATE [VALUE] sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code

注释

注释是程序代码中不被执行的文本字符串，是用于说明或解释代码的部分语句。MySQL系统支持三种注释方式：

• 井字符(#)

• 双连线字符(--)：双连线后一定要加一个空格。

• 正斜杠星号字符(/*...*/)

MySQL常用函数

从功能上可以分为以下几类函数：字符串函数、数学函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。

# MySQL常用函数表

字符串函数：CHAR_LENGTH(str)、CONCAT(str1,str2,...)、INSERT(str1,x,len,str2)、LEFT/RIGHT(str,n)、
    SPACE(n)、LOWER/UPPER(str)、LTRIM/RTRIM/TRIM(str)、REPLACE(str,str1,str2)、STR()、
    SUBSTRING(str,n,len)、REVERSE(str)

数学函数：ABS(x)、CEILING(x)、FLOOR(x)、ROUND(x,[,n])、SIGN(x)、RAND()/RAND(x)、SQRT(X)

日期和时间函数：CURDATE()/CURTIME()、NOW()、DAYNAME(date)、DAY(date)/YEAR(date)、WEEKDAY(date)、TIME_TO_SEC(time)

（最近更新：2019年09月03日）