mysql常用函数

日期格式化：
date_format(now(),'%Y-%m-%d %H:%i:%s')

添加外键

alter table locstock add foreign key locstock_ibfk2(stockid) references product(stockid)

locstock 为表名, locstock_ibfk2 为外键名 第一个括号里填写外键列名, product为表名,第二个括号里是写外键关联的列名

 

删除外键

alter table locstock drop foreign key locstock_ibfk2

update t_oa_wjmlxx t1 inner join t_oa_wjccxx t2  on t1.mlxxid=t2.parentid and t2.wjccxxid in ('2c90d8c35003b7eb015004187a0f0013') set t1.xgsj=now(), t1.xgr='admin'

select t.bh,
       t.clsj,
       t.dm,
       t.fjdz,
       t.isshpt,
       t.jlnf,
       t.lyzt,
       t.nr,
       t.sldw,
       t.title,
       date_format(t.tjsj, '%Y-%m-%d') as tjsj,
       t.tjzsf,
       t.twzxm,
       d.name,
       z.zdmc as ztmc,
       (select case
                 when t.lyzt = '00007' then
                  'green.jpg'
                 else
                  s.tblj
               end) as tblj
  from t_ygfw_message t
  left join t_ygfw_dept d
    on t.sldw = d.dm
  left join sjzd z
    on t.lyzt = z.zddm
  left join t_ygfw_sjdcsb s
    on (unix_timestamp(now()) - unix_timestamp(t.tjsj)) / (60 * 60 * 24) + 0.5 >=
       s.mintime
   and (unix_timestamp(now()) - unix_timestamp(t.tjsj)) / (60 * 60 * 24) + 0.5 <
       s.maxtime
 where 1 = 1
 order by t.tjsj desc, t.bh asc;

mysql查询行号:
rownum:=@rownum+1,

mysql设置查询超时时间：

[root@ ~]# mysql -h 192.168.0.* -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8174
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global wait_timeout=86400;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 86400 |
+---------------+-------+
1 row in set (0.00 sec)

 

interactive_timeout ：等待某个活动连接 关闭连接前等待的秒数

 

wait_timeout：等待某个非活动连接关闭连接前等待的秒数。

 

net_read_timeout：在中止读取之前等待来自连接的更多数据的秒数。

 

net_write_timeout：在中止写入之前等待块写入连接的秒数。