Mysql 常用SQL语句集锦

mysql 专栏收录该内容
1 篇文章 0 订阅
/**
 * 格式化时间
 */
$sql = "select date_format(timestamp, '%Y-%m-%d') as day from table_name";
$sql = "select from_unixtime(timestamp, '%Y-%m-%d') as day from table_name";




/**
 * 条件判断
 * case函数与case搜索函数
 * --简单Case函数
 * CASE sex
 *          WHEN '1' THEN '男'
 *          WHEN '2' THEN '女'
 * ELSE '其他' END
 * --Case搜索函数
 * CASE WHEN sex = '1' THEN '男'
 *          WHEN sex = '2' THEN '女'
 * ELSE '其他' END              
 * 
 */
$sql = "select count(*) all, " ;
$sql .= " count(case when status = 1 then status end) status_1_num, ";
$sql .= " count(case when status = 2 then status end) status_2_num ";
$sql .= " from table_name";




/**
 * 联表修改、删除(看需求选择inner join/left join/right join)
 * Update Join
 * Delete Join 
 */
$sql = "update table_name_1 ";
$sql .= " inner join table_name_2 on table_name_1.id = table_name_2.uid ";
$sql .= " inner join table_name_3 on table_name_3.id = table_name_1.tid ";
$sql .= " set *** = *** ";
$sql .= " where *** ";




/**
 * 替换某字段指定内容 replace
 */
$sql = "update table_name set content = REPLACE(content, 'aaa', 'bbb') ";
$sql .= " where (content like '%aaa%')";




/**
 * 获取表中某字段包含某字符串的数据(sql中比like高效,可代替like做查询使用)
 * LOCATE(str,field) 返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在,返回值为 0
 * LOCATE(str,field,pos) 返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0 
 * POSITION(str IN field) 
 * INSTR(field, str) 
 */
$sql = "SELECT * FROM `table_name` WHERE LOCATE(str,field) ";




/**
 * 截取字符串
 * left(str, length) 
 * right(str, length) 
 * substring(str, pos) 
 * substring(str, pos, length) 
 */
$sql = "SELECT SUBSTRING('str',1,4) FROM table_name ";




/**
 * 查找表中多余的重复记录 
 */
//单个字段
$sql = "select * from table_name where field in ";
$sql .= "(select field from table_name group by field having count(field) > 1 )";


//多个字段
$sql = "select * from table_name where (field1,field2) in ";
$sql .= "(select field1,field2 from table_name group by field1,field2 having count(*) > 1 )";




/**
 * 删除表中多余的重复记录 
 */
//单个字段
$sql = "delete from table_name where field in ";
$sql .= "(select field from table_name group by field having count(field) > 1)  ";
$sql .= "and Primary not in ";
$sql .= "(select min(Primary) from table_name group by field having count(field)>1) ";


//多个字段
$sql = "delete from table_name where (field1,field2) in ";
$sql .= "(select field1,field2 from table_name group by field1,field2 having count(*) > 1) ";
$sql .= "and Primary not in ";
$sql .= "(select min(Primary) from table_name group by field1,field2 having count(*)>1) ";


  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值