MySQL第四天0319 日期函数，流程函数，多表连接查询~等值、非等值、自身连接，ANSL SQL~自然、using、on、外连接

• 日期函数
  • 1、NOW()：返回服务器的当前日期和时间
  • 2、CURDATE():返回当前日期;
  • 3、CURTIME():返回当前时间，只包含时分秒
    • select now(),curdate(),curtime(),utc_date(),utc_time()
    • select now(),now()+10,now()+90
    • select curdate(),curdate()+0,curdate()+20,
    • date_add(curdate(), interval 10 day),
    • date_add(curdate(), interval 20 day)
    • select curtime(),curtime()+10
  • 4、TIMEDIFF(expr1, expr2)：返回两个时间的时间差
    • select timediff('09:50:20','08:15:10')
  • 5、DATEDIFF(expr1, expr2)：返回两个日期相差的【天数】
    • select datediff('2020-3-19','2020-02-17')
  • 6、DATE_ADD(date, INTERVAL expr unit) 给date增加一个间隔
    • 增加20年、月、日
      • select curdate(),date_add(curdate(), interval 20 year)
      • select curdate(),date_add(curdate(), interval 20 month)
      • select curdate(),date_add(curdate(), interval 20 day)
    • 增加20小时、分钟
      • select now(),date_add(now(), interval 20 hour)
      • select now(),date_add(now(), interval 20 minute)
    • 增加10年5个月
      • select now(),date_add(now(), interval '10-5' YEAR_MONTH)
    • 增加10小时20分钟5秒
      • select now(),date_add(now(), interval '10:20:5' HOUR_SECOND)
    • date_add()简写为+
      • select now(), now()+interval '10-5' YEAR_MONTH
      • select now(), now()+interval '10:20:5' HOUR_SECOND
  • 7、DATE_SUB(date, INTERVAL expr unit) 给date减去一个间隔
    • 减少20天
      • select curdate(),date_sub(curdate(), interval 20 day)
  • 8、提取日期时间的各个部分：
    • date() time()
    • year() month() day() quarter() week()
    • hour() minute() second() microsecond()
    • 也可以使用 EXTRACT(unit FROM date)
      • select now(),date(now()),time(now()),year(now())
      • select year(now()), day(now()), week(now()), extract(year from now())
      • select hiredate, year(hiredate), extract(year from hiredate) from emp
  • 9、dayofweek(date)、dayofmonth(date)、dayofyear(date)
    • select now(),dayofweek(now()),dayofmonth(now()),dayofyear(now())
  • 10、日期时间格式化函数：DATE_FORMAT(date,format)、TIME_FORMAT(time,format)
    • %Y：代表4位的年份
    • %y：代表2为的年份
    • %m：代表月,格式为(01……12)
    • %c：代表月,格式为(1……12)
    • %d：代表天数,格式为(00……31)
    • %e：代表天数,格式为(0……31)
    • %H：代表小时,格式为(00……23)
    • %h：代表小时,格式为(01……12)
    • %i：代表分钟,格式为(00……59)
    • %s：代表秒,格式为(00……59)
    • select now(), DATE_FORMAT(now(),'%Y/%m/%d---%H:%i:%s')
  • 11、补充函数：TIMESTAMPDIFF(type,开始时间,结束时间)
    • type取值：year、month、day、hour、minute、second、week、quarter
      • select timestampdiff(month,'20000101',now())
  • 12、补充函数：FORMAT(data,精度)
    • select sal,format(sal,2) from emp
• 流程函数
  • 1、case
    • select sal,
    • case
      • when sal<1000 then 'low'
      • when sal>=1000 and sal<3000 then 'middle'
      • when sal>=3000 and sal<4000 then 'high'
      • else 'very high'
    • end
    • from emp
    •  
    • select sal,
    • case sal<1000
    • when true then 'low'
    • when false then 'high'
    • end
    • from emp
  • 2、if、ifnull
    • SELECT IF(1<2,2,3);
    • SELECT IF(1<2,'success','fail');
    • select ifnull(comm,0) from emp
  • 3、其他
    • 明文 ename
    • 秘文 md5(ename)
      • select ename, md5(ename) from emp
• 多表连接查询
  • 基本写法
    • 笛卡尔积
      • 两个表所有行都发生连接
      • 连接条件不足
    • 等值连接
      • and
      • 多表
    • 限制歧义列
      • 查询工作地点在NEW YORK的员工编号，姓名，部门编号，工作地点
      • deptno是歧义列，因为emp表中有deptno，dept表中有deptno，到底哪个？
      • 需要明确指出哪个表的deptno，否则报错
    • 表的别名
      • emp e， emp m
    • 非等值连接
    • 自身连接
      • emp e, emp p 理解成类和对象
  • ANSI SQL
    • 交叉连接
      • cross join
    • 自然连接
      • natural join 对两个表之间相同名字和数据类型的列进行的等值连接
    • using 子句
      • select * from emp join dept using (deptno)
    • on子句
      • select * from emp join dept on(emp.deptno=dept.deptno)
    • 外部连接
      • left outer join
      • right outer join