- select DATE_FORMAT(news_table.news_time, '%Y-%m-%d %H:%i:%s') news_time,
- news_table.flag, news_table.id, news_table.iconCls, news_table.news_title,
- news_table.news_content, news_table.news_filepath, news_table.news_publictime,
- user_table.user_real news_author, newstype_table.newstype_type news_type,
- news_table.news_other, news_table.news_isPublic from news_table, newstype_table,
- user_table where news_table.news_type = newstype_table.id
- and news_table.news_author = user_table.user_name order by news_time DESC limit 10,10
- -- 数据格式化 DATE_FORMAT
- SELECT * FROM meeing_record_table WHERE m_rd_id = 1 AND !FIND_IN_SET('004',repersion)
- -- 查询指定列不存在指定值的记录
- UPDATE shengyoubian SET shengyoubian.youbian = ''+RIGHT(shengyoubian.youbian,6)
- UPDATE shengyoubian SET shengyoubian.youbian = ''+LEFT(shengyoubian.youbian,6)
- -- 更新指定列值 为截取的值.
- SELECT SUBSTRING_INDEX(shengyoubian.youbian,'2',1);
- -- 查询索引值
- UPDATE user_table SET xb='1'WHERE SUBSTR(sfz,17,1)%2=1
- -- 身份证更新性别 截取指定值..
- UPDATE user_file_table SET rePersion=REPLACE(rePersion, ',1010', '')
- -- 通过更新的方法删除指定参数..
- {
- UPDATE shengyoubian
- -- SET shengyoubian.youbian = ''+RIGHT(shengyoubian.youbian,6)
- SET shengyoubian.dizhi=
- -- ''+RIGHT(str,len)
- REPLACE(shengyoubian.dizhi,shengyoubian.youbian,'')
- }-- 更新A列值去除B列已有的字符
- {
- Select a.dept_id, a.station_no,a.flag, a.xz_flag, b.`name` AS dept_name
- FROM dept_station_table AS a ,department_table AS by
- Where a.dept_id >=(Select dept_id From dept_station_table limit 0,1) AND a.dept_id = b.dept_id limit 5;
- }-- 较好的一种查询分页方式.先比较在截取
- IF('条件','true','false') AS checked
- --条件查询
- -- show ENGINES ;
- -- SHOW VARIABLES LIKE 'have%';
- -- show VARIABLES LIKE 'storage_engine'
- -- DESC user_table;
- -- ALTER TABLE user_table ad VARCHAR(10) NULL AFTER FIRST
- -- ALTER TABLE user_table DROP '列名'
- SELECT youbian ,GROUP_CONCAT(dizhi) FROM shengyoubian GROUP BY youbian;--分组查询 重复数据显示在后方.. ','连接
- SELECT youbian ,GROUP_CONCAT(dizhi) AS dizhi ,COUNT(dizhi) AS cou FROM shengyoubian GROUP BY youbian with ROLLUP;
- -- with ROLLUP 计算每组的记录数
- SELECT * FROM shengyoubian WHERE youbian REGEXP '^257'
- -- 正则表达式匹配 ^开始 $结束 .任意一个字符包括 回车/换行 [字符集] [^除了字符集] S1|S2|S3 任意一个字符串
- -- * 多个之前的字符 包括0和1 +代表多个字符之前的字符 包括1 字符串{N}字符串出现N次
- -- 字符串[M,N] 字符串至少 M次 最多 N次
- Insert Into table_A (A ,B ) Select Aa AS A ,Bb as b from tabe_B
- -- 查询B表中的值添加到A表
- Select A+B+C as a from tabe_B
- -- 算数运算符 + - * / DIV(同/) % MOD (取余)
- -- = <> <=> IS NULL IS NOT NULL IN LIKE
- -- && AND || OR ! XOR
- -- 按位 & | ~ 与或非 >> <<
- -- 函数 绝对值 ABS(X) CEIL(X) '获取整数' CEILING(X) SIGN(-1|0|1) SIN(X) COS(X) TAN(X) RAND(x)'获取随机数'
- -- ROUND(x) 四舍五入 ROUND(x.m) m 位小数 TRUNCATE(A.B) 截取B位小数 幂运算POW(x,y)
- -- CHAR_LENGTH(S) 字符串的字符数
- -- LENGTH(S) 字符串的长度
- -- LEFT(S,N) RIGHT(S,N) LTRIM(S) 去掉开始的空格 INSERT(s,s1)从s字符串查找 s1的位置
- -- REVERSE(s) 将字符串S的顺序反过来 SUBSTRING(S,N,Len) 截取字符串 MID(s,n,len) CONCAT(s1,s2) 合并字符串
- select a, DATE_FORMAT(b,'Y-m-d') from user_table;
- -- 日期时间格式化
- Select IF(a>10 ,'pass','false') from user_table;
- -- if函数
- Select IFNULL(a,'默认值') from user_table;
- -- IFNULL 函数
- Select a , CASE a when 90 then "默认值" when 80 then '' ELSE '' END AS '' FROM user_table;
- -- CASE 函数
- -- 系统参数 version() connection_id() user()
- -------------------------------
- Select PASSWORD(str);
- Select MD5('');
- Select ENCODE(str,pswd_str) , DECODE(str,pp);
- -- FORMAT(12321.223123,len)
- -- 加密函数password(str)
- -------------------------------
- GET_LOCK('name',time); IS_FREE_LOCK('name'); RELEASE_LOCK('name');
- --加锁函数..
- .
- >2014年5月29日22:01:13 .
- mysql 数据操作常用语句
- CONCAT(IFNULL(a.blqingkuang,'0'),</span> 字符串连接, DATE_FORMAT(ping_date,'%Y-%m') = DATE_FORMAT(CURDATE(),'%Y-%m') ";月份判断
- DATEDIFF(ds.end_date,curdate())>= 0 时间差
- int off = 0;
String sql = "update wkrj_model_dcdb_s set ";
if (dcdbs.getEnd_date() != null && dcdbs.getEnd_date() != "") {
if (off == 0) {
sql += " end_date ='" + dcdbs.getEnd_date() + "'";
off++;
} else {
sql += ", end_date ='" + dcdbs.getEnd_date() + "'";
}
}
if (dcdbs.getWork_station() != null && dcdbs.getWork_station() != "") {
if (off == 0) {
sql += " work_station ='" + dcdbs.getWork_station() + "'";
off++;
} else {
sql += ", work_station ='" + dcdbs.getWork_station() + "'";
}
}
if (dcdbs.getDept_work() != null && dcdbs.getDept_work() != "") {
if (off == 0) {
sql += " dept_work ='" + dcdbs.getDept_work() + "'";
off++;
} else {
sql += ", dept_work ='" + dcdbs.getDept_work() + "'";
}
}
if (dcdbs.getFg_yj() != null && dcdbs.getFg_yj() != "") {
if (off == 0) {
sql += " fg_yj ='" + dcdbs.getFg_yj() + "'";
off++;
} else {
sql += ", fg_yj ='" + dcdbs.getFg_yj() + "'";
}
}
sql+=" where dcdb_ids ='"+dcdbs.getDcdb_ids()+"'";
int info = 0;
System.out.println("sql-----------------"+sql);
info = jdbcTemplate.update(sql);
if (info > 0) {
return true;
}
sql 笔记
最新推荐文章于 2023-07-15 18:19:08 发布