Sql工作小记(1)

sql语句类型

• DDL 数据定义语言(create)
• TPL 事务处理语言(commit)
• DCL 数据控制语言()
• DML 数据操作语言(select,insert,update,delete)

1.Join从句

• 内连接 INNER

  • 选取两张表交集

• 左外连接 LEFT OUTER

  • SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON A.KEY=B.KEY
    查询A表中的数据(包含AB交集)
  • SELECT * FROM TABLEB B LEFT JOIN TABLEB B ON A.KEY = B.KEY WHERE B.KEY IS NULL
    查询仅存在与A表中的数据(排除AB交集)，优化NOT IN

• 右外连接 RIGHT OUTER

  • 与LEFT JOIN对应，以B表为核心来查询

• 全外连接 FULL OUTER

  • 左右连接的合集，查询所有AB表的数据
  • 排除AB表公共部分
  • 注意，MYSQL中不支持FULL JOIN，需要利用UNION ALL连接LEFT，RIGHT连接

• 交叉连接 CROSS

  • 又称笛卡尔连接或叉乘
  • SELECT A.CLUMN,B.CLUMN FROM TABLEA A CROSS JOIN TABLEB B

2.一些小技巧

• 如何更新From从句中的表（自身表）

  UPDATE USER1 A JOIN
  (
      SELECT B.USER_NAME FROM USER1 A 
      JOIN USER2 B 
      ON A.USER_NAME = B.USER_NAME
  ) B 
  ON A.USER_NAME = B.USER_NAME 
  SET A.AGE = '30'；
  

---

• 以年/月/日为周期，分别统计周期时间内数量 ##

  • 订单表order
  • 订单创建时间create_time

  • 现需要统计”每天有多少订单生成”

    SELECT COUNT(order_id) AS total,
    FROM_UNIXTIME(create_time,"%d") AS day_num
    FROM order GROUP BY day_num
    

  (1).FROM_UNIXTIME函数，可将时间戳格式化为”YYYY-MM-DD”格式，其中的两个参数，前者为时间字段名，后者为期望值。

  (2).FROM_UNIXTIME函数的期望值可根据以下修饰符来设置(仅罗列出一些常用的)：

  %M 月名字(January……December)

  %W 星期名字(Sunday……Saturday)

  %Y 年, 数字, 4 位

  %y 年, 数字, 2 位

  %a 缩写的星期名字(Sun……Sat)

  %d 月份中的天数, 数字(00……31)

  %e 月份中的天数, 数字(0……31)

  %m 月, 数字(01……12)

  %b 缩写的月份名字(Jan……Dec)

  %j 一年中的天数(001……366)

  %H 小时(00……23)

  %k 小时(0……23)

  %h 小时(01……12)

  %l 小时(1……12)

  %i 分钟, 数字(00……59)

  %r 时间,12 小时(hh:mm:ss [AP]M)

  %T 时间,24 小时(hh:mm:ss)

  %s 秒(00……59)

  %p AM或PM