SQL是一种声明式的高级查询语言。使用SQL时，只需要描述希望怎样获取数据，而不用考虑具体的算法实现。



1、变量



1.1、用户变量

用户变量和连接相关。用户变量和在函数和存储过程中通过DECLARE语句声明的局部变量不同：

用户变量：

  • 一个客户端定义的变量仅限于该客户端连接使用
  • 当客户端退出时，该客户端连接的所有变量将自动释放

局部变量：

  • 和客户端连接无关
  • 声明周期仅限于“BEGIN ... END”代码块内

使用用户变量的语法为：

SET @var_name=expr[,@var_name=expr]...

其中变量的值可以为整数、实数、字符串或者NULL。

例如：

sql server 引用变量 sql使用变量的前提是什么_MySQL

还可以在sql语句中引用用户变量：

sql server 引用变量 sql使用变量的前提是什么_数据库_02

也可以通过SQL语句对用户变量进行赋值，但是注意，变量值只能是NULL或者单个值，而不能是一个表。例如：

sql server 引用变量 sql使用变量的前提是什么_python_03



1.2、系统变量

MySQL服务器维护着两种系统变量：

  • 全局变量：影响MySQL服务的整体运行方式
  • 会话变量：影响具体客户端连接的操作


1.2.1、全局变量

当服务器启动时，它将所有的全局变量初始化为默认值（默认值可以通过选项文件中或者在命令行中修改）。

当服务器启动后，可以通过SQL语句动态修改，具体语法如下：

SET GLOBAL var_name = value
SET @@global.var_name = value

检索一个GLOBAL变量的语法如下：

SELECT @@global.var_name;
SELECT GLOBAL VARIABLES like 'var_name';

注意：

  • 想要通过SQL语句动态修改全局变量的话，必须要有SUPER权限。
  • 访问全局变量的所有客户端都能看到对全局变量所有的修改。
  • 但是注意，全局变量的修改只会影响在修改之后建立的连接。也就是说，对于已经打开的长连接或者连接池来说，由于连接从未关闭重启，全局变量的修改不会有任何效果。


1.2.2、会话变量

服务器为每个连接的客户端维护一系列的会话变量，在连接时使用相应全局变量的当前值对客户端的会话变量进行初始化。每个客户端可以修改自己的会话变量，具体语法如下：

SET SESSION var_name = value;
SET @@session.varname = value;
/*也可以不带任何符号，直接用变量名*/
SET var_name = value;

检索一个SESSION变量的语法如下：

SELECT @@varname;
SELECT @@sesion.varname;
SHOW VARIABLES LIKE 'varname';

注意：

  • 不过是会话变量，还是全局变量，都是系统预设的变量，不能凭空捏造变量名

     

sql server 引用变量 sql使用变量的前提是什么_python_04

  • 在使用SHOW VARIABLES时，如果不指定，默认查找session变量。 


1.3、总结

  • @用来标注用户变量
  • @@用来标注系统变量
  • 使用@@var_name而不加上作用域（GLOBAL/SESSION）时，默认指SESSION变量


2、注释

MySQL中有三种注释：

  • 单行注释：#
  • 单行注释（不推荐）： -- 注释内容，注意--后至少要有一个空格
  • 多行注释/**/

多行注释还有一个用处，就是注释掉只能在MySQL中运行的部分SQL扩展。这样这部分扩展在MySQL中仍然可以运行，同时在其他数据库中则会被忽略而不会引起错误。



3、数据类型

MySQL支持常用的数据类型：数值、日期/时间和字符串。



3.1、数值类型



3.1.1、整型

MySQL中的整型和Java中相同，默认是有符号数，可以在类型后面加上UNSIGNED关键字表明是无符号数。

不同的整型占据的字节数，以及大小范围如下：

类型

字节

最小值

最大值

对应的Java类型

TINYINT

1

-128

127

byte

TINYINT UNSIGNED 

1

0

255

/

SMALLINT

2

-32768

32767

short

SMALLINT UNSIGNED

2

0

65535

/

MEDIUMINT

3

-8388608

8388607

/

MEDIUMINT UNSIGNED

3

0

16777215

/

INT

4

-2147483648

2147483647

int

INT UNSIGNED

4

0

4294963647

/

BIGINT

8

-9223372036854775808

9223372036854775807

long

BIGINT UNSINGED

8

0

18446744073709551615

/

注意：

  • INT(11)中的11并不代表数值范围，实际上它对数字大小并没有影响，而只影响交互工具的显示精度，比如在MySQL客户端中的显示精度。
  • 由于MySQL的内部类型对于时间只支持到秒级别的精度，可以使用BIGINT来存储精度到毫秒的时间戳。


3.1.2、定点数

定点数指的是小数点的位置不变的数据。

MySQL中，NUMERIC和DECIMAL都可以表示定点数，两者等价。在声明时，通常需要指定精度和标度。

比如在 DECIMAL（M,D）中，M是精度，表示数据的总长度（包括整数部分和小数部分），D是标度，表示小数点后数字的长度。例如，如果要表示9000.00的工资：

salary DECIMAL(6,2)

注意：

  • 和整数中不同，这里的精度和标度会限制数字大小，插入超出范围的整数部分会报错，而插入超出范围的小数部分会四舍五入。比如在上面的例子中插入10000.00，就不报ERROR，插入失败；插入100.11111就会报WARN，虽然插入成功，但是会截断小数部分（四舍五入）：

sql server 引用变量 sql使用变量的前提是什么_sql server 引用变量_05

  • 定点数不利于同时表达特别大的数或者特别小的数。


3.1.3、浮点数

浮点数是属于有理数中某个特定子集的数的表示法，在计算机中用于近似地表示某个实数。具体地来说，这个实数是由一个整数或者一个定点数乘上某个基数（计算机中通常是2）的整数次幂得到的。

在MySQL中，FLOAT表示单精度浮点数（4字节），DOUBLE表示双精度浮点数（8字节）。

和定点数相同，我们一样可以通过FLOAT（M,D）规定其范围，超出范围同样会报错或进行四舍五入。

注意：

  • 浮点数存在误差，应该尽量避免做浮点数比较
  • 为了保证可移植性，一般不规定浮点数的范围
  • 定点数相比浮点数占据更多的空间，除非在描述货币等确切精度的值，一般不用定点数


3.2、日期/时间类型

MySQL中表示时间值的日期和时间类型有DATETIME、DATE、TIMESTAMP、TIME和YEAR等。每一种都有其特定的范围，其中TIMESTAMP还有特有的自动更新特性。

注意：MySQL中的时间最多精确到秒，如果需要存取精确到毫秒的时间，可以考虑使用BigInt类型。



支持的日期/时间类型如下：

类型

格式

存储空间大小

支持范围

适用场景

DATETIME

以'yyyy-MM-dd HH-mm-ss'格式检索和显示DATETIME值

8字节

'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

需要同时包含日期和时间信息的值时

DATE

以'yyyy-MM-dd'格式检索和显示DATE值

3字节

'1000-01-01 '到'9999-12-31'

只需要日期值而不需要时间部分时

TIMESTAMP

TIMESTAMP的显示格式和DATETIME相同。在存储时会转为UTC存储，查询时转回所属时区。

4字节

'1970-01-01 00:00:01'UTC 到 '2038-01-09 03:14:07'UTC 

在进行INSERT或者UPDATE时记录日期和时间。TIMESTAMP允许自动更新。

TIME

‘HHH:mm:ss’ 

3字节

'-838:59:59'到'838:59:59'

只存时间，不存日期

YEAR

'YYYY'

1

1901~2155

只存年份

注意：

  • TIMESTAMP一般只用于记录插入或者更新数据的日期和时间
  • 可以将TIMESTAMP字段定义为“default current_timestamp on update  current_timestamp”，这样就可以实现TIMESTAMP的自动更新。比如：

sql server 引用变量 sql使用变量的前提是什么_java_06

sql server 引用变量 sql使用变量的前提是什么_python_07

  • 在MySQL中，日期的解释总是'年月日’，特别的，针对只有两位年值的日期，比如09-01-01，其中的年份解释遵从一下规则：
  • 70~99表示1970~1999
  • 0~69表示2000~2069
  • 在为DATETIME、DATE或者TIMESTAMP赋值的时候，允许不严格的语法，可以用使用任意的分隔符。即下面两条语句是一样的：

注意：

  • 在和Java中的类型进行映射时，ResultSet类的getDate()方法只获取日期，getTime()只获取时分秒，getTimeStamp()获取完整的日期和时分秒。


3.3、字符串类型



3.3.1、char和varchar类型

类型

存取方法

大小范围

适用场景

char(M)

固定长度，存入时自动在右侧添加空格补长，取出时自动去掉空格。

0-M，M最大不能超过255

  1. 长度变化不大的情况（如MD5值）
  2. 很短的字符串

varchar(M)

不定长，存入原字符串并使用1~2字节存储字符串长度

0-M，M最大不能超过65535

最长记录的长度要比平均长度长得多的情况

注意:

  • 不管是char(M)还是varchar(M)，其中的M都代表最大的字符数（不是字节数），超出范围会自动进行截短，并抛出一个warn。


3.3.2、BINARY和VARBINARY类型

BINARY和VARBINARY类型与CHAR/VARCHAR类似，差别之处在于它们存储的是“字节字符串”而不是“字符字符串”。它们没有字符集，排序和比较也是基于字节的二进制值进行的。



3.3.3、BLOB和TEXT类型

BLOB包括四种类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

TEXT同样包括四种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

注意：

  • BLOB用于存储字节字符串，TEXT用于存储字符字符串
  • 使用BLOB和TEXT，当字段过大时，可能会产生“磁盘临时表”，导致性能问题，磁盘的查询要比内存慢得多。


3.4、数据类型的使用原则

一般来说，数据类型的选择遵从一下原则：

  1. 各表使用一致的数据类型
  2. 占用空间越小的类型往往更好
  3. 简单的类型往往更好（比如整型比字符型运算的更快，可以使用无符号整数来存储IP地址）
  4. 尽可能地避免使用NULL值（建议显示声明字段为NOT NULL（当然并不是必须要这样做）。可以使用一些0、空字符等一些特殊值替代“NULL”）


4、函数简介



4.1、数值函数

  • 支持基本运算符，包括：+、-、*、/、DIV（整除）
  • ABS(X)：绝对值
  • CEIL(X)：向上取整、FLOOR（X）：向下取整，ROUND(X)/ROUND(X,D)：四舍五入
  • CRC(X)：计算循环冗余码校验值，并返回一个32bit无符号值
  • RAND()、RAND(N)：产生从从0~1（包括0和1）的随机数（N是seed）。注意这个方法不适用于进行随机排序，比如下面的语句效率会很差：
  • SELECT * FROM table_name ORDER BY RAND() LIMIT 1；
  • SIGN(x)：返回X的符号
  • TRUNCATE(X,D)：返回被舍去至小数点后D位的数字X。


4.2、字符函数

  • CHAR_LENGTH(str)：字符串的字符长度
  • LENGTH(str)：字符串的字节长度
  • CONCAT(str1,str2,str3,...)：连接多个字符串
  • LEFT(str,len)、RIGHT(str,len)：返回从左/右数的len个字符
  • SUBSTRING(str,pos)、SUBSTRING(str,pos,len)：截取自字符串，起始于pos位置
  • LOWER(str)：全部转为小写，UPPER(str)：全部转为大写


4.3、日期和时间函数

  • NOW()：返回当前日期和时间
  • CURTIME()：返回当前时间
  • CURDATE()：返回当前日期
  • DATEDIFF(expr1,expr2)：返回expr1和expr2之间相差的天数
  • DATE_ADD(date, INTERVAL expr type)、DATE(date, INTERVAL expr type)：增加或者减少指定的时间长度，type可选项有： SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、YEAR。比如：
  • SELECT DATE_ADD('2017-07-23 23:59:59', INTERVAL 1 SECOND);
  • DATE_FROMAT(date,format)和STR_TO_DATE(str,format)：用于日期格式和字符串之间的相互转换


5、MySQL练习用示例数据库

MySQL官方提供了一个练习用的示例数据库，可以在GitHub上下载安装：