随笔分类 -  SQL

mysql授权登录用户
摘要：创建用户并授权 CREATE USER 'voctrals'@'%' IDENTIFIED BY 'some_password'; 允许远程访问 GRANT ALL PRIVILEGES ON *.* TO 'voctrals'@'%' IDENTIFIED BY 'some_password' W 阅读全文
posted @ 2016-09-02 13:59 VoctrALs 阅读(1141) 评论(0) 推荐(0)
sql(join中on与where区别)
摘要：转：http://zzstudy.offcn.com/archives/2146left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。inner join: 内连接，又叫等值连接，只返回两个表... 阅读全文
posted @ 2015-07-24 15:45 VoctrALs 阅读(258) 评论(0) 推荐(0)
mysql批量更新
摘要：UPDATE ta INNER JOIN tb ON ta.id=tb.idSETta.col1=tb.col1,ta.col2=tb.col2以上代码用来批量更新mysql中的记录 阅读全文
posted @ 2015-05-06 11:06 VoctrALs 阅读(169) 评论(0) 推荐(0)
SQL执行顺序
摘要：FROM ：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1ON ：对VT1应用ON筛选器。只有那些使为真的行才被插入VT2。OUTER(JOIN) ：如 果指定了OUTER JOIN（相对于CROSS JOIN 或... 阅读全文
posted @ 2015-04-28 17:31 VoctrALs 阅读(128) 评论(0) 推荐(0)
表复制
摘要：INSERT INTO ... SELECT ... FROM ...INSERT INTO des(col1, col2...) SELECT colA, colB, ... FROM src需要des表存在SELECT ... INTO ... FROM ...SELEC... 阅读全文
posted @ 2015-03-10 16:25 VoctrALs 阅读(134) 评论(0) 推荐(0)
MySQL lpad cast ifnull regexp
摘要：SELECT lpad( (CAST(IFNULL(MAX(ITEM_ID) , 0) as unsigned int) + 1), 3, '0') AS ITEM_IDFROM REQUEST_MST_ITEMWHERE REQUEST_TYPE = #{requestType} AN... 阅读全文
posted @ 2014-04-24 15:30 VoctrALs 阅读(421) 评论(0) 推荐(0)
MYSQL变量
摘要：select @ROWNUM:= @ROWNUM + 1 ROWNUM, RM.* from REQUEST_MST RM, (SELECT @ROWNUM:=0) TEST WHERE RM.`LANGUAGE` = 'cn'OK, this will be enough for those wh... 阅读全文
posted @ 2014-04-23 08:46 VoctrALs 阅读(125) 评论(0) 推荐(0)