这段时间将SQL学习指南这本书翻完了,买这本书的时候是2016年9月21日,时隔一年多啊,终于读完了。不过觉得也正常的,以当时的水平以及工作内容,似乎就算当时看完,也是看的稀里糊涂吧。现在看完,可能不是说学习新知识,毕竟这本书的确是比较入门的书,而更多的是系统性的掌握SQL,以及对相关方面体会更加深刻吧。下面就记录下看这本书得到的一些体会和有意思的东西。

日期时间数据

在数据库中存储时间数据,究竟存储哪种格式比较好呢。MySQL中有date, datetime, timestamp, year, time这几种类型,同时也还可以以字符串,数字的格式进行存储,那么究竟该如何选择呢。以前我是直接都用字符串,现在看来就有点太low了。这里就对这方面的东西做一个总结。

各类型的意义

  • date: 存储日期,不包含分秒等时间数据,可以用来存储生日之类日期数据
  • datetime: 包含日期和时间
  • timestamp: 包含日期和时间数据,MySQL可以在向表中增加或者修改数据行时候自动为timestamp列产生当前的日期时间
  • year: 只需要年份数据的列
  • time: 某项任务消耗的时间可以用这个类型,一般是两个datetime,记录开始和结束时间,这样可以算出来。

对于只存储日期或者时间或者年份的数据,一般来说没什么问题,这里主要探讨的对象是日期和时间数据一起存储。那么可以选择的就是datetime, timestamp或者int,字符串。

字符串是首先要排除的,因为第一个占用空间大,第二个检索慢,效率是最低的,在这几个选择中是最不应该采用的。

int

  1. 占用4个字节
  2. 建立索引之后,查询速度快
  3. 条件范围搜索可以使用使用between
  4. 不能使用mysql提供的时间函数
  5. 适合需要进行大量时间范围查询的数据表

缺陷是,不包含时区信息。

datetime

  1. 占用8个字节
  2. 允许为空值,可以自定义值,系统不会自动修改其值。
  3. 实际格式储存,以存储时候的格式存储,取出来也是存储的格式
  4. 与时区无关
  5. 不可以设定默认值,所以在不允许为空值的情况下,必须手动指定datetime字段的值才可以成功插入数据。
  6. 可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。

结论:datetime类型适合用来记录数据的原始的创建时间,因为无论你怎么更改记录中其他字段的值,datetime字段的值都不会改变,除非你手动更改它。查询出来都是UTC时间,查询时候可以根据需要进行改变处理。

timestamp

  1. 占用4个字节
  2. 允许为空值,但是不可以自定义值,所以为空值时没有任何意义。
  3. TIMESTAMP值不能早于1970或晚于2037。这说明一个日期,例如’1968-01-01’,虽然对于DATETIME或DATE值是有效的,但对于TIMESTAMP值却无效,如果分配给这样一个对象将被转换为0。
  4. 值以UTC格式保存
  5. 时区转化,存储时对当前的时区进行转换,检索时再转换回当前的时区。
  6. 默认值为CURRENT_TIMESTAMP(),其实也就是当前的系统时间。
  7. 数据库会自动修改其值,所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值,你只需要在设计表的时候添加一个timestamp字段即可,插入后该字段的值会自动变为当前系统时间。
  8. 以后任何时间修改表中的记录时,对应记录的timestamp值会自动被更新为当前的系统时间。

结论:timestamp类型适合用来记录数据的最后修改时间,因为只要你更改了记录中其他字段的值,timestamp字段的值都会被自动更新。它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。

一般项目上用timestamp,因为包含时区信息。show variables like '%time_zone%';查看时区信息,set time_zone='+1:00';修改时区信息。因此不论存储的时候时区是怎么设置的,MySQL中存储的都是UTC,因此只要改变时区设置,就可以得到正确的时间。

注: 在PostgreSQL中,对应有timestamptimestamptz两种类型,前者是不带时区信息的,也就是即使修改服务器的时区信息,该类型字段查出的值都是一样的。而后者会根据时区设置不同,查出来的值不同,类似于MySQL的timestamp`,不同的是,在存储的时候可以指定时区,postgresql会进行转换成UTC时间进行存储。

分组和聚集

使用group by对数据进行分组,比如SELECT open_emp_id FROM account GROUP BY open_emp_id;,并且可以使用聚集函数对分组进行统计,SELECT open_emp_id, COUNT(*) how_many FROM account GROUP BY open_emp_id;

对于过滤条件,如果是对于分组数据进行过滤,那么不能用WHERE,因为WHERE先于GROUP BY执行,所以执行WHERE的时候还没有创建好分组,因此需要使用HAVING,SELECT open_emp_id, COUNT(*) how_many FROM account GROUP BY open_emp_id HAVING COUNT(*) > 4;

聚集函数

  • MAX()
  • MIN()
  • SUM()
  • AVG()
  • COUNT()

隐式分组和显式分组

查询返回的每个值都是由聚集函数产生的,这样可以不用group by子句,因为是对所有行进行的,是一个隐式分组。比如SELECT COUNT(*), MAX(user_id) FROM users。但是如果除了聚集函数产生的列之外,还想获取额外的列,就需要显式分组,明确的表达按照哪些列进行分组。比如SELECT user_id, COUNT(*) FROM users group by user_id