SQL 基础 入门 MYSQL

select * from testTavble where   name ='test'   limit 0,100;

sql   时间处理    

时间范围查询

select * from testTable where EstimatedTime between '2019-07-20' and '2019-07-21';

时间查询

select * from testTable where date_format(EstimatedTime,'%Y-%m-%d')='2019-07-20';

sql 清空表

truncate table  testTable

sql 删除表数据

delete from  testTable where  name='test'

sql  联表查询

distinct  去重数据

select  distinct a.*  ,b.*   from  testTable   a  inner join  tb_test  b on   a.id=b.id 
				inner join  tb_test2  c   on  b.id=c.id
				inner join  tb_test3  d   on  c.id=d.id
				where  a.name='' and  (b.name or c.name)  status = 'A'

.....左右连

sql 插入数据

insert into tb_goodsorders (id,name,time,status,seq)
select ifnull (max(id),0)+1,'name','time,'A','0' from tb_goodsorders;

sql  批量插入数据

insert into  tb_test  (id,name,time,status,seq)  values ( max(id)+1,'name','time','A','0' ),(max(id)+1,'name','time','A','0')

sql  更新数据记录

update tb_test  set time=now(),name='test' where status='A';

sql  计数

select  count(*) from  tb_test 

sql  分组 

select * from `tb_test`  where  status='A'   group by  name

sql    两时间的天数做查询  DateDiff 函数 计算天数

 select * from `tb_test` where DateDiff(end_date,start_date)=1  and  status='B' 

sql    TIMESTAMPDIFF 函数     语法   TIMESTAMPDIFF (unit,datetime_expr1,datetime_expr2)

unit 参数 值

SECOND：秒

MINUTE：分钟

HOUR：小时

DAY：天

WEEK：星期

MONTH：月

QUARTER：季度

YEAR：年

select   timestampdiff(MINUTE,now(),activity_start_time),id    from  tb_text

个人笔记，有不对的地方，感谢大佬们提醒矫正