Mysql 常用SQL语句集锦

最新推荐文章于 2024-07-23 17:32:59 发布

skarp_

最新推荐文章于 2024-07-23 17:32:59 发布

阅读量250

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/skarp_/article/details/77196436

版权

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) ";