lampabc.com,lamp学习本应更简单, 互帮 互助 共享 ~~~

简明MySQL——语言结构


二、语言结构
1、文字值([变量]类型)
1.1、字符串
即用单引号或双引号引起来的字符序列。
如果SQL服务器模式启用了NSL_QUOTES,可以只用单引号引用字符串。用双引号引用的字符串被解释为一个识别符。
也包括有些转义字符即用反斜线("\")开始的一些,如\b 退格符,\n换行符,\r回车符,\t tab字符。

1.2、数值
即一系列阿拉伯数字表示的,浮点数使用”.“间隔,负值用”-“加在前面表示。
十六进制值,有两种写法,用x或用0x(此为数字0),例:x'4D7953514C'或0x4D7953514C表示均可以。0x语法基于ODBC,x语法基于标准SQL。可以用HEX()函数将一个字符串或数字转换为十六进制格式的字符串。
科学计数法,由整数或浮点数后跟“e”或“E”、一个符号(+或-)和一个整数指数来表示,如1.24E+12,22.2e-1是合法的表示方法,而1.11E12则是不合法的表示。

1.3、布尔值
常量TRUE等于1,常量FALSE等于0。常量名可以写成大写或小写。

1.4、位字段值
可以使用b'value'符号写位字段值。value是一个用0和1写成的二进制值。如:b'1010'
mysql> CREATE TABLE t (b BIT(8));
mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
+------+----------+----------+----------+

1.5、数据库、表、索引、列和别名等识别符
对大小密致敏感
识别符         最大长度(字节)         允许的字符

数据库         64                       目录允许的任何字符
表            64                       文件名允许的任何字符
列             64                       所有字符
索引           64                       所有字符
别名           255                      所有字符

2、变量
2.1、用户变量
用户变量形式为@var_name;
设置用户变量的一个途径是执行SET语句:SET @var_name = expr [, @var_name = expr] ...。
未赋值的变更为NULL,类型为字符串
select语句里赋值用:=例:SET @t1=0, @t2=0, @t3=0;SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
注释:在SELECT语句中,表达式发送到客户端后才进行计算。也即说明在子句中不能使用包含SELECT列表所设的变量的表达式。

2.2、系统变量
有两种全局变量和会话变量(客户端变量)
更改/设置全局变量可以通过SET GLOBAL var_name,更改全局变量,必须具有SUPPER权限,例:
mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;
更改/设置客户端变量可以通过SET SESSION var_name,也只能更改自己的会话变量(也可以用LOCAL替换SESSION一个意思),例:
mysql> SET SESSION sort_buffer_size=value;
mysql> SET @@session.sort_buffer_size=value;
mysql> SET sort_buffer_size=value;--如果设置变量时不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION

3、注释语法
'#' 注释,从该字符处至行尾
'-- ' 注释,从该字符序列至行尾,第二个破折号后至少跟一个空格符
'/**/' 注释,与C一样。

4、字符集

5、列类型
列的类型要比数据类型细化的多。
5.1、数值类型
包括严格数值类型(整型)有:integer(int),smallint,decimal(dec),numeric;以及近似数值类型(浮点):float,real,double precision;bit类型,保存位字段值。
tinyint非常小的整数 ----127-127;0-255(2的7次方,2的8次方),1字节
smallint较小整数----32768-32768;0-65535(2的15次方,21的16次方)2字节
mediumint中等大小整数----8388608-8388608,0-16777215(2的23次方,2的24次方)3字节
int标准整数----2147683648-2147683648,0-4294967295(2的31次方,2的32次方)4字节
bigint较大整数---(2的63次方,2的64次方)8字节
float(M,D)单精度浮点数---(±1.175494351E - 38)4字节
double(M,D)又精度浮点数---(±2.2250738585072014E - 308)8字节
decimal(M,D)一个串的浮点数---可变;其值的范围依赖于M 和D
说明:定义整形时,可以指定可选的显示尺寸,注意这是显示尺寸,M应该是一个1-255的整数。例:int(11)
浮点数可以指定最大显示尺寸M和小数位数(即常说的精度和小数点位数),对于flost/double这两值是可以选的但decimal是必须的。

5.2、字符串类型
char定长字符串(0-255)
varchar可变长字符串(0-65535)
tinyblob非常小的BLOB(二进制大对象)
blob小BlOB
mediumblob中等BLOB
longblob大BLOG
tinytext非常中的文本串
text小文本串
mediumtext中等文本串
longtext大文本串
enum枚举
set

5.2.1、CHAR和VARCHAR类型
它们在存储或检索过程中不进行大小写转换;但保存和检索的方式不同。
CHAR的长度固定为创建表时声明的长度,保存值时,若值较小会在右边填充空格也达到指定的长度,当检索时,尾部的空格被删除掉。
VARCHAR就保存值的真实内容,另加一个字节来记录长度(如果列声明的长度超过255,则使用两个字节)。
值    CHAR(4)    存储需求    VARCHAR(4)    存储需求
''    '    '    4个字节    ''        1字节
'ab'    'ab  '    4个字节    'ab'        3字节
'abcd'    'abcd'    4个字节    'abcd'        5字节
'abcdefghj''abcd'    4个字节    'abcd'        5字节
注意最后一行,如果MySQL运行下严格模式下,超过列长度的值不保存,并且会出现错误。
SELECT myname = 'Monty  '  FROM names; SELECT myname = 'Monty'  FROM names;得到的结果是一样的,因为MySQL会自动裁剪掉尾部的填充字符, 但记得了空格在前面就不行,如SELECT myname = '  Monty'  FROM names;得到的就不一样的结果了。

5.2.2、binary和varbinary类型
类似于char和varchar,不同的是包含的是二进制字符串而不是非二进制字符串;也说明它们没有字符集,并且排序和比较基于列值字节的数值值。

5.2.3、blob和text类型
BLOB是一个二进制大对象,可以容纳可变数量的数据,有4种类型:tinyblob,blob,mediumblob和longblob
text也有四种类型:tinytext,text,mediumtext和longtext,这些刚BLOB类型,有相同的最大长度和存储需求。

5.2.4、ENUM类型

5.2.5、SET类型
SET类型是一个字符串对象,可以有零个或多个值,其来自表创建时规定的允许的一列值。


5.3、日期和时间类型
5.3.1、一些日期时间函数:
当前时间函数now(),current_timestamp(),current_timestamp,localtime(),localtime,localtimestamp(),localtimestamp()
都可以获得当前日期+时间(date+time),一般就都用now(),因为函数名简短
+---------------------+
| now()               |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

获得当前日期+时间函数,sysdate()
sysdate()与now()区别在于,now()在执行开始时值就得到了,sysdate()在函数执行时动态得到值。
+---------------------+----------+---------------------+
| now()                         |sysdate()|            | sleep(3) | now()                        |sysdate()        |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 |  2008-08-08 22:28:21 |      0 | 2008-08-08 22:28:21 |2008-08-08 22:28:24 |
+---------------------+----------+---------------------+

获得当前日期(date),curdate()
curdate(),current_date(),current_date,可以获得一样的结果
| curdate()  |
+------------+
| 2008-08-08 |
+------------+

获得当前时间(time),curtime()
current_time(),current可以获得一样的结果
| curtime() |
+-----------+
| 22:41:30  |
+-----------+

获得当前UTC日期时间函数,utc_date(),utc_time(),now()
| utc_timestamp()     | utc_date() | utc_time() | now()               |
+---------------------+------------+------------+---------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11   | 2008-08-08 22:47:11 |
+---------------------+------------+------------+---------------------+

5.3.2、涉及到日期日间部分获取的
---------
选取日期时间的各部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒
set @dt = '2008-09-10 07:15:30.123456';

select date(@dt);        -- 2008-09-10
select time(@dt);        -- 07:15:30.123456
select year(@dt);        -- 2008
select quarter(@dt);     -- 3
select month(@dt);       -- 9
select week(@dt);        -- 36
select day(@dt);         -- 10
select hour(@dt);        -- 7
select minute(@dt);      -- 15
select second(@dt);      -- 30
select microsecond(@dt); -- 123456

Extract()函数,可以上面实现类似的功能
set @dt = '2008-09-10 07:15:30.123456';

select extract(year                from @dt); -- 2008
select extract(quarter             from @dt); -- 3
select extract(month               from @dt); -- 9
select extract(week                from @dt); -- 36
select extract(day                 from @dt); -- 10
select extract(hour                from @dt); -- 7
select extract(minute              from @dt); -- 15
select extract(second              from @dt); -- 30
select extract(microsecond         from @dt); -- 123456
select extract(year_month          from @dt); -- 200809
select extract(day_hour            from @dt); -- 1007
select extract(day_minute          from @dt); -- 100715
select extract(day_second          from @dt); -- 10071530
select extract(day_microsecond     from @dt); -- 10071530123456
select extract(hour_minute         from @dt); --    715
select extract(hour_second         from @dt); --    71530
select extract(hour_microsecond    from @dt); --    71530123456
select extract(minute_second       from @dt); --      1530
select extract(minute_microsecond  from @dt); --      1530123456
select extract(second_microsecond  from @dt); --        30123456

---------
dayof... 函数:dayofweek(), dayofmonth(), dayofyear()
分别返回日期参数,在一周、一月、一年中的位置。
set @dt = '2008-08-08';

select dayofweek(@dt);   -- 6
select dayofmonth(@dt);  -- 8
select dayofyear(@dt);   -- 221
日期 '2008-08-08' 是一周中的第 6 天(1 = Sunday, 2 = Monday, ..., 7 = Saturday);一月中的第 8 天;一年中的第 221 天。

---------
week... 函数:week(), weekofyear(), dayofweek(), weekday(), yearweek()
set @dt = '2008-08-08';

select week(@dt);        -- 31
select week(@dt,3);      -- 32
select weekofyear(@dt);  -- 32
select dayofweek(@dt);   -- 6
select weekday(@dt);     -- 4
select yearweek(@dt);    -- 200831
MySQL week() 函数,可以有两个参数,具体可看手册。 weekofyear() 和 week() 一样,都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。
MySQL weekday() 函数和 dayofweek() 类似,都是返回“某天”在一周中的位置。不同点在于参考的标准, weekday:(0 = Monday, 1 = Tuesday, ..., 6 = Sunday); dayofweek:(1 = Sunday, 2 = Monday, ..., 7 = Saturday)
MySQL yearweek() 函数,返回 year(2008) + week 位置(31)。

---------
返回星期和月份名称函数:dayname(), monthname()
set @dt = '2008-08-08';

select dayname(@dt);     -- Friday
select monthname(@dt);   -- August
思考,如何返回中文的名称呢?

---------
返回月份中的最后一天。
select last_day('2008-02-01');  -- 2008-02-29
select last_day('2008-08-08');  -- 2008-08-31

MySQL last_day() 函数非常有用,比如我想得到当前月份中有多少天,可以这样来计算:
mysql> select now(), day(last_day(now())) as days;
+---------------------+------+
| now()               | days |
+---------------------+------+
| 2008-08-09 11:45:45 |   31 |
+---------------------+------+

5.3.3,涉及到日期时间计算的
---------
日期增加一个时间间隔:date_add()
set @dt = now();

select date_add(@dt, interval 1 day);        -- add 1 day
select date_add(@dt, interval 1 hour);       -- add 1 hour
select date_add(@dt, interval 1 minute);     -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day);       -- sub 1 day
MySQL adddate(), addtime()函数,可以用 date_add() 来替代。下面是 date_add() 实现 addtime() 功能示例:
mysql> set @dt = '2008-08-09 12:12:33';
mysql> select date_add(@dt, interval '01:15:30' hour_second);
+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03                            |
+------------------------------------------------+
也可以select date_add(@dt, interval '1 01:15:30' day_second);
这样就分别为@dt增加了“1小时 15分 30秒” 和 “1天 1小时 15分 30秒”

---------
另类日期函数:period_add(P,N), period_diff(P1,P2)
函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”,第二个参数“N” 表示增加或减去 N month(月)。

---------
日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)
datediff(date1,date2):两个日期相减 date1 - date2,返回天数
timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值

5.3.4、日期转换函数、时间转换函数
(时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)
select time_to_sec('01:00:05');  -- 3605
select sec_to_time(3605);        -- '01:00:05'

(日期、天数)转换函数:to_days(date), from_days(days)
select to_days('0000-00-00');  -- 0
select to_days('2008-08-08');  -- 733627
select from_days(0);           -- '0000-00-00'
select from_days(733627);      -- '2008-08-08'