MySQL心得6--MySQL语言结构--常量、变量

1.在MySQL数据库中，SQL语言由以下几部分组成。

（1）数据定义语言（DDL）。

用于执行数据库的任务，对数据库及数据库中的各种对象进行创建(create)、删除(drop)、修改(alter)等操作。如前所述，数据库对象主要包括：表、默认约束、规则、视图、触发器、存储过程等。不同数据库对象，其create、drop等语句的语法形式不同

（2）数据操纵语言（DML）。

用于操纵数据库中各种对象，检索和修改数据。DML包括的主要语句及功能如下表所示。

语 句	功 能	说 明
SELECT	从表或视图中检索数据	是使用最频繁的SQL语句之一
INSERT	将数据插入到表或视图中
UPDATE	修改表或视图中的数据	既可修改表或视图的一行数据，也可修改一组或全部数据
DELETE	从表或视图中删除数据	可根据条件删除指定的数据

（3）数据控制语言（DCL）。

用于安全管理，确定哪些用户可以查看或修改数据库中的数据，DCL包括的主要语句及功能如下表所示。

语 句	功 能	说 明
grant	授予权限	可把语句许可或对象许可的权限授予其他用户和角色
revoke	收回权限	与GRANT的功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限

（4） MySQL增加的语言元素。这部分不是SQL标准所包含的内容，而是为了用户编程的方便增加的语言元素。这些语言元素包括常量、变量、运算符、函数、流程控制语句和注解等。每个SQL语句都以分号结束，并且SQL处理器忽略空格、制表符和回车符。

2. 常量

1）. 字符串常量

字符串是指用单引号或双引号括起来的字符序列，分为ASCII字符串常量和Unicode 字符串常量。

ASCII字符串常量是用单引号括起来的，由ASCII字符构成的符号串。举例：‘hello’ ‘How are you!’

Unicode 字符串常量与ASCII字符串常量相似，但它前面有一个N标志符（N代表 SQL-92标准中的国际语言（National Language））。N前缀必须为大写。只能用单引号括起字符串。举例：N‘hello’

Unicode 数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储。

在字符串中不仅可以使用普通的字符，也可使用几个转义序列，它们用来表示特殊的字符，见下表。每个转义序列以一个反斜杠（“\”）开始，指出后面的字符使用转义字符来解释，而不是普通字符。注意 NUL 字节与 NULL 值不同，NUL为一个零值字节，而 NULL 代表没有值。

序 列	含 义
\0	一个ASCII 0 (NUL)字符
\n	一个换行符
\r	一个回车符（Windows中使用\r\n作为新行标志）
\t	一个定位符
\b	一个退格符
\Z	一个ASCII 26字符（CTRL+Z）
\'	一个单引号（“'”）
\"	一个双引号(“"”)
\\	一个反斜线（“\”）
\%	一个“%”符。它用于在正文中搜索“%”的文字实例，否则这里“%”将解释为一个通配符
\_	一个“_”符。它用于在正文中搜索“_”的文字实例，否则这里“_”将解释为一个通配符

有以下几种方式可以在字符串中包括引号：

● 在字符串内用单引号“'”引用的单引号“'”可以写成“''”（两个单引号）。

● 在字符串内用双引号“"”引用的双引号“"”可以写成“""”（两个双引号）。

● 可以在引号前加转义字符（“\” ）。

● 在字符串内用双引号“"”引用的单引号“'”不需要特殊处理，不需要用双字符或转义。同样，在字符串内用单引号“'”引用的双引号“"”也不需要特殊处理。

2）. 数值常量

数值常量可以分为整数常量和浮点数常量。

整数常量即不带小数点的十进制数，例如： 2，+1453，–2147483648。

浮点数常量是使用小数点的数值常量，例如： -1.39，1.5E5，0.5E-2。

3）. 十六进制常量

MySQL支持十六进制值。一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母“X”或小写字“x”。在引号中只可以使用数字“0”到“9”及字母“a”到“f”或“A”到“F”。x'4D7953514C'表示字符串MySQL。

十六进制数值不区分大小写，其前缀“X”或“x”可以被“0x”取代而且不用引号。即X'41'可以替换为0x41，注意：“0x”中x一定要小写。

十六进制值的默认类型是字符串。如果想要确保该值作为数字处理，可以使用cast（...AS UNSIGNED）。

例： 执行如下语句： SELECT0x41, CAST(0x41 AS UNSIGNED);

如果要将一个字符串或数字转换为十六进制格式的字符串，可以用hex()函数。

例：将字符串CAT转换为16进制。 SELECT HEX('CAT');

4）. 日期时间常量

日期时间常量：用单引号将表示日期时间的字符串括起来构成。日期型常量包括年、月、日，数据类型为DATE，表示为“1999-06-17”这样的值。时间型常量包括小时数、分钟数、秒数及微秒数，数据类型为TIME，表示为“12:30:43.00013”这样的值。MySQL 还支持日期/时间的组合，数据类型为DATETIME或TIMESTAMP，如“1999-06-17 12:30:43”。DATETIME和TIMESTAMP的区别在于：DATETIME的年份在1000～9999之间，而TIMESTAMP的年份在1970～2037之间，还有就是TIMESTAMP在插入带微秒的日期时间时将微秒忽略。TIMESTAMP还支持时区，即在不同时区转换为相应时间。

需要要特别注意的是，MySQL 是按年-月-日的顺序表示日期的。中间的间隔符“-”也可以使用如“\”、“@”或“%”等特殊符号。

如下是日期时间常量的例子：'2008-05-12 14:28:24:00'

日期时间常量的值必须符合日期和时间的标准，如这样的日期是错误的：'1996-02-31'。

5）. 位字段值

可以使用b'value'符号写位字段值。value是一个用0和1写成的二进制值。直接显示b'value'的值可能是一系列特殊的符号。例如，b'0'显示为空白，b'1'显示为一个笑脸图标。

使用bin函数可以将位字段常量显示为二进制格式。使用oct函数可以将位字段常量显示为数值型格式。

例： SELECT BIN(b'111101'+0), OCT(b'111101'+0);

6）. 布尔值

布尔值只包含两个可能的值：TRUE和FALSE。FALSE的数字值为“0”，TRUE的数字值为“1”。

例： 获取TRUE和FALSE的值。 SELECT TRUE, FALSE;

7）. NULL值

NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义，并且不同于数字类型的“0”或字符串类型的空字符串。

3. 变量

1）. 用户变量

用户可以在表达式中使用自己定义的变量，这样的变量叫做用户变量。

用户可以先在用户变量中保存值，然后在以后引用它，这样可以将值从一个语句传递到另一个语句。在使用用户变量前必须定义和初始化。如果使用没有初始化的变量，它的值为NULL。

用户变量与连接有关。也就是说，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

定义和初始化一个变量可以使用SET语句，语法格式为：

SET @user_variable1＝expression1 [,user_variable2= expression2 , …]

其中，user_variable1、user_variable2为用户变量名，变量名可以由当前字符集的文字数字字符、“.”、“_”和“$”组成。当变量名中需要包含了一些特殊符号（如空格、#等）时，可以使用双引号或单引号将整个变量括起来。expression1、expression2为要给变量赋的值，可以是常量、变量或表达式。

例: 创建用户变量name并赋值为“王林”。

SET @name='王林';

注意：@符号必须放在一个用户变量的前面，以便将它和列名区分开。“王林”是给变量name指定的值。name的数据类型是根据后面的赋值表达式自动分配的。也就是说，name的数据类型跟 '王林' 的数据类型是一样的，字符集和校对规则也是一样的。如果给name变量重新赋不同类型的值，则name的数据类型也会跟着改变。

(1_1).还可以同时定义多个变量，中间用逗号隔开。

例:创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3。 SET @user1=1, @user2=2,@user3=3;

(1‑2).定义用户变量时变量值可以是一个表达式。

例:创建用户变量user4，它的值为user3的值加1。

SET @user4=@user3+1;

(1_3).在一个用户变量被创建后，它可以以一种特殊形式的表达式用于其他SQL语句中。变量名前面也必须加上符号@。

例1:查询上例中创建的变量name的值。 SELECT @name;

例2： 使用查询给变量赋值。

USE XSCJ；

SET @student=(SELECT 姓名 FROMXS WHERE 学号='081101');

例3： 查询表XS中名字等于例2中student值的学生信息。 SELECT 学号, 姓名, 专业名, 出生日期

FROM XSWHERE 姓名=@student;

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

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

例： 执行如下语句，结果t2的值为7。

SELECT @t2:=(@t2:=2)+5AS t2;

2）. 系统变量

MySQL有一些特定的设置，当MySQL数据库服务器启动的时候，这些设置被读取来决定下一步骤。例如，有些设置定义了数据如何被存储，有些设置则影响到处理速度，还有些与日期有关，这些设置就是系统变量。和用户变量一样，系统变量也是一个值和一个数据类型，但不同的是，系统变量在MySQL服务器启动时就被引入并初始化为默认值。附录G中列出了绝大多数的系统变量。

例： 获得现在使用的MySQL版本。 SELECT @@VERSION ;

大多数的系统变量应用于其他SQL语句中时，必须在名称前加两个@符号，而为了与其他SQL产品保持一致，某些特定的系统变量是要省略这两个@符号的。如CURRENT_DATE（系统日期）、CURRENT_TIME（系统时间）、CURRENT_TIMESTAMP（系统日期和时间）和CURRENT_USER（SQL用户的名字）。

例： 获得系统当前时间。 SELECT CURRENT_TIME;

3)在MySQL中，有些系统变量的值是不可以改变的，例如VERSION和系统日期。而有些系统变量是可以通过SET语句来修改的，例如SQL_WARNINGS。 语法格式为：

SET system _var_name =expr

| [global | session] system_var_name = expr

| @@ [global.| session.] system_var_name = expr

说明：system_var_name为系统变量名，expr为系统变量设定的新值。名称的前面可以添加GLOBAL或SESSION等关键字。

指定了GLOBAL或@@global.关键字的是全局系统变量（globalsystem variable）。指定了SESSION或@@session.关键字的则为会话系统变量（local systemvariable)。SESSION和@@session.还有一个同义词LOCAL和@@local.。如果在使用系统变量时不指定关键字，则默认为会话系统变量。

（3_1）.全局系统变量

当MySQL启动的时候，全局系统变量就初始化了，并且应用于每个启动的会话。如果使用GLOBAL（要求SUPER权限）来设置系统变量，则该值被记住，并被用于新的连接，直到服务器重新启动为止。

例: 将全局系统变量sort_buffer_size的值改为25000。

SET @@global.sort_buffer_size=25000;

注意：如果在使用SET GLOBAL时同时使用了一个只能与SETSESSION同时使用的变量，或者如果在设置一个全局变量时未指定GLOBAL（或@@），则MySQL会产生错误。

（3_2）.会话系统变量

会话系统变量（session system variable）只适用于当前的会话。大多数会话系统变量的名字和全局系统变量的名字相同。当启动会话的时候，每个会话系统变量都和同名的全局系统变量的值相同。一个会话系统变量的值是可以改变的，但是这个新的值仅适用于正在运行的会话，不适用于所有其他会话。

例: 将当前会话的SQL_WARNINGS变量设置为TRUE。

SET @@SQL_WARNINGS =ON;

说明：这个系统变量表示如果不正确的数据通过一条INSERT语句添加到一个表中，MySQL是否应该返回一条警告。默认情况下，这个变量是关闭的，设为ON表示返回警告。

例: 对于当前会话，把系统变量SQL_SELECT_LIMIT的值设置为10。这个变量决定了SELECT语句的结果集中的最大行数。

SET@@SESSION.SQL_SELECT_LIMIT=10;

SELECT @@LOCAL .SQL_SELECT_LIMIT;

说明：在这个例子中，关键字SESSION放在系统变量的名字前面（SESSION和LOCAL可以通用）。这明确地表示会话系统变量SQL_SELECT_LIMIT和SET语句指定的值保持一致。但是，名为SQL_SELECT_LIMIT的全局系统变量的值仍然不变。同样地，改变了全局系统变量的值，同名的会话系统变量的值保持不变。

MySQL对于大多数系统变量都有默认值。当数据库服务器启动的时候，就使用这些值。也可以在C盘MYSQL文件夹下的my.ini选项文件中修改这些值。当数据库服务器启动的时候，这个文件被自动读取。

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

例: 把SQL_SELECT_LIMIT的值恢复为默认值。

SET@@LOCAL.SQL_SELECT_LIMIT=DEFAULT;

(2).使用SHOWVARIABLES语句可以得到系统变量清单。SHOW GLOBAL VARIABLES返回所有全局系统变量，而SHOW SESSION VARIABLES返回所有会话系统变量。如果不加关键字就默认为SHOWSESSION VARIABLES。

例: 得到系统变量清单。SHOW VARIABLES;

(3)要获得与样式匹配的具体的变量名称或名称清单，需使用LIKE子句，语句如下：

SHOW VARIABLES LIKE 'max_join_size';

SHOW GLOBAL VARIABLES LIKE 'max_join_size';

(4)要得到名称与样式匹配的变量的清单，需使用通配符“%”，例如： SHOW VARIABLESLIKE 'character%';