简单sql语法总结

1.删除表中的所有的数据的同时，将自动增长清零。

truncate table SW_Admin_Log

2.不删除表的数据，直接重置自动增长的值

truncate table SW_Admin_Log;
DBCC CHECKIDENT ('SW_User', RESEED,10000)  

子查询

select (select Title from SW_ProductType a where p.TypeID=a.ID ) 
nextNum, p.* from SW_Product p 
where Model='Product'

select (select count(0) from SW_ProductType) 
nextNum, a.* from SW_ProductType a 
where ParentID=10354

//查询有几个子集
select (select count(0) from SW_ProductType where ParentID=a.ID) nextNum, a.* from SW_ProductType a 
where ParentID=0

去重查询并且查出重复数据个数并且排序

select p.*,t.Title from SW_ProductType as t inner join (
select count(*) as len,TypeID from SW_PicList 
 where RelatedProductID=18 and Model='UnitType' 
group by TypeID
) as p
on p.TypeID = t.id
order by p.TypeID asc

//房优网

左连接：LEFT JOIN

select b.Title TypeTitle,a.* from SW_Product a left join SW_ProductType b on a.TypeID=b.id 
where a.Model='Product'

右连接：RIGHT JOIN

select b.Title TypeTitle,a.* from SW_Product a right join SW_ProductType b on a.TypeID=b.id 
where a.Model='Product'

UNION

select b.Title TypeTitle,a.id from SW_Product a left join SW_ProductType b on a.TypeID=b.id 
where a.Model='Product'
UNION
select b.Title TypeTitle,a.id from SW_Product a right join SW_ProductType b on a.TypeID=b.id 
where a.Model='Product'

多表联查

select c.ThumbUrl,b.Title,a.ProductName from SW_Product a,SW_ProductType b,SW_PicList c 
where (a.TypeID=b.id and c.ProductID=a.id) and a.Model='product'  //所有数据一样

//不一样的数据
select b.*,u.nickname,a.name from balance_detail b
left join user u on u.id = b.user_id
left join admin a on a.id = b.admin_id
order by b.create_time Desc  

group by 分组查询

//sql server 语法
select convert(varchar,datepart(yy,AddDate))+'-'+convert(varchar,datepart(mm,AddDate)) a,count(0) num
from sw_product
group by convert(varchar,datepart(yy,AddDate))+'-'+convert(varchar,datepart(mm,AddDate))

//mysql 语法
select DATE_FORMAT(time,'%Y-%m') times from 
 user_login_log GROUP BY times

//mysql 复杂、连表、多条件分组查询

 SELECT oi.business_id,b.`business_name`,b.`parent_id`,b.`business_icon`,COUNT(1) num
        FROM order_info oi,business b
        WHERE user_id=80
        AND b.`business_id`=oi.`business_id`
        and (select is_shelf from business where business_id=b.parent_id)=1
        and b.is_shelf=1
        GROUP BY oi.business_id,b.business_name,b.`parent_id`,b.`business_icon`
        ORDER BY num DESC
        LIMIT 30
				

SQL HAVING 语法（筛选聚合函数条件）

//mysql
select DATE_FORMAT(time,'%Y-%m') times,count(0) cc
from user_login_log 
GROUP BY times
having cc>60

//sql server
select convert(varchar,datepart(yy,AddDate))+'-'+convert(varchar,datepart(mm,AddDate)) a,
count(0) num
from sw_product 
group by convert(varchar,datepart(yy,AddDate))+'-'+convert(varchar,datepart(mm,AddDate)) 
having count(0)>30

基本增删改查

insert into user_login_log (time,id,...) values(1,2,...),(1,2,.....)
update user_login_log set time='1',id=2
select * from  user_login_log
delete from user_login_log

mybaits foreach 用法

    <delete id="delLike">
        delete from user_business_like where user_id=#{userId}
    </delete>

    <insert id="addLike">
        insert into user_business_like
        (user_id,business_id)
        values
        <foreach  collection="list" item="item"  separator="," >
            (#{userId},#{item})
        </foreach>
    </insert>

//类似mysql：insert into user_business_like values (user_id,business_id) values (80,90),(80,90),(80,90)

mysql limit分页

select * from business limit 0,10
//page p 第几页
//size s 取多少条

//(p-1)*s,s   0,10  取第一页，每页10条
//(p-1)*s,s	10,10   取第二页，每页10条

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

获取本周的数据

   /**
     * @Author:  古木
     * @Description: 本周数据 无论今天是周几都是获取本周一至本周日的数据
     * @date:  2024/5/24 12:18
     */
    @Select("SELECT count(*) from yx_store_product_pink_big_detail "+
            " where is_del=0 and uid=#{uid} " +
            " AND create_time >= DATE_SUB(#{nowTime}, INTERVAL WEEKDAY(#{nowTime}) DAY) " +
            " AND create_time < DATE_ADD(DATE_SUB(#{nowTime}, INTERVAL WEEKDAY(#{nowTime}) DAY), INTERVAL 7 DAY) ")
    Integer weekDrawNum(@Param("uid") Long uid, @Param("nowTime") LocalDate nowTime);


SELECT * from yx_user_release_details  where is_del=0 AND create_time >= DATE_SUB('2024-07-07', INTERVAL WEEKDAY('2024-07-07') DAY)
 AND create_time < DATE_ADD(DATE_SUB('2024-07-07', INTERVAL WEEKDAY('2024-07-07') DAY), INTERVAL 7 DAY)