mysql 日期比较 between的用法的意思_Mysql 中现在仍旧不知道的小知识点

重点：

表结构的增删改：

alter table t_students add id int;
alter table t_students drop id;
alter table t_students modify id varchar(20);

表数据的增删改：

insert into table t_students (id,name)<可以省略> values(1,'关羽');
delte from table t_students where id =2;
update tabel t_students set id=2,name='刘备' where id=1;

表查询

select ...
from ...
where....
group by...
having  ...(先有groupby进行统计，后有having进行筛选
order by...
limit 1,5;  #这里1代表第二行数据，和python一样
优先级：
from >where>group by >select >having＞order by(选出来后再排序）>limit

多表查询（内外连接）

select a.name,b.id from emp a join emp1 b on a.add=b.add; 
#inner join 还可以有outer join ,left join ,right join ,区别在于inner求交集，left显示左边，right
显示右边，outer 显示两边

子查询,下面

1.在子查询中可以两个一起等于（行子查询,）

select playerno from players where (sex,town)=(select sex,town from players where playerno =100);

2.列子查询（通常和in (select ...) ,>any(select...),<all(select ...)连用）

#用in的情况，in+一列（获取球员性别为女的所有球员的球员号，名字及所在城市。）
select playerno,name,town from players where playerno in (select playerno from players where sex='f');
#用any的情况，这里也使用了两个表进行对比（获取至少比同城的另一球员年轻的所有球员的号码，日期和居住城市）
select playerno, birth_date, town from players as p1 where birth_date > any(select birth_date from players as p2 where p1.town = p2.town);
#用all的情况，这里使用了<=,意味着小于等于所有，只有一个值（获取最老球员的号码，名字及生日）
select playerno,birth_date,name from players where birth_date <=
all(select birth_date from players);

3.表子查询（从一个表中进行查询 from（select ...））

4.标量子查询（结果只有一个值 where sex=(select ....)）

5.exists型子查询(存在，返回True or False)

select playerno,inatials from players p1 where exists(select * from penalties p2
where p1.playerno =p2.playerno);

6.not exists查询（不存在）

select playerno,inatials from players p1 where not exists(select * from penalties p2
where p1.playerno =p2.playerno);

7.通配符不仅有%和_，还有[charlist]和[!charlist]

#选出以a或b或c开头的城市的记录
select * from persons where city like '[abc]%';
#选出不以a或b或c开头的城市的记录
select * from persons where city like '[！abc]%';
#还可以这样
select * from persons where city not like '[abc]%';

8.between ... and ...不仅可以用于数字和日期，还可以用于文本[按表格顺序排列的]

#mysql中between...and...范围包括前面不包括后面，其他的数据库暂时不知道
select * from persons where city between 'shanghai' and 'beijing';
#between还可以在前面加not表示不包括
select * from persons where city not between 'shanghai' and 'beijing';

9.内外左右链接

内为交集，外为并集，左连接为left join 左边的表的字段全部显示，右连接为right join 右边的字段全部显示（为交叉显示，不会不同的行合并到一起）

10.union 和union all ，union连接后只会列出不同的值，但是union all 连接后会显示所有的值

#语句一 union (all) 语句二
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

11. select into 语句（创建新的表和表的备份，但是mysql不支持）

#select into 语句的样式(选中所有的into 新的表中),后面可以接条件
select * into newtable from s;
#mysql虽然不支持，但是有相同效果的语句
create table newtable (select * from s);

12.主键和外键（不懂的话查询W3C）

主键主要用于数据的唯一标识，如身份证号码，学号等

外键用于该表格链接其他表格的主键，不能插入不同于那个表格主键的值

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)

13.SQL中的日期函数

#选取当前的时间
select now();
select curdate();当前日期
select curtime();当前小时和分钟数
#将完整的时间转化为日期格式（去掉time选项）
select date(now());
#将完整的时间中提取出相应的日期,year可以换成month,quarter(季度),week（一年中的第几周），day,hour,minuter,second等等
select extract(year from now()[这里可以使用列名]) from ....
###具体可以查看W3C的SQL教程

datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒。
-- 相差2天
select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');

TIMESTAMPDIFF函数，有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。

--相差1天
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--相差49小时
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差2940分钟
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

--相差176400秒

select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

14.用is null 和 is not null 来查找空值

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL;(这里也可以改成is null,和python中很相似)

15.null为空值不能参与计算，可以用isnull()来进行判断，Mysql中为ifnull(列名，0)

#原理为如果为空，那么为0
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

16.这里也有提取字符串的函数mid，用法和excel中是一样的

17.format(列名，格式)

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products

18.if和case when 的用法

(if(表达式，值1,值2)) ，如果表达式为True，输出值1,如果表达式错误，输出值2，和Excel中的if函数一模一样,使用时括起来 as 名字作为一个单独的值

(case when 表达式 then 值1 else 值2 end) 基本原理和上面一样

select reg_time,
sum(dur0) as dur0,
sum(dur1) as dur1,
sum(dur2) as dur2,
sum(dur3) as dur3,
sum(dur4) as dur4,
sum(dur5) as dur5
from (select use_id,reg_time,
(if(datediff(s.log_time,s.reg_time)=0,1,0)) as dur0,
(if(datediff(s.log_time,s.reg_time)=1,1,0)) as dur1,
(if(datediff(s.log_time,s.reg_time)=2,1,0)) as dur2,
(if(datediff(s.log_time,s.reg_time)=3,1,0)) as dur3,
(if(datediff(s.log_time,s.reg_time)=4,1,0)) as dur4,
(if(datediff(s.log_time,s.reg_time)=5,1,0)) as dur5
from (select reg.use_id,reg_time,log_time from reg join log on reg.use_id=log.use_id) as s) as L group by date(reg_time) order by date(reg_time) asc;

经自己测试成功的留存数目的sql语句，如果要求留存率的话，用dur1/dur0可以得出
主要思想为sum代替count来进行计数的思想
但是要先进行0和1的编码，符合要求的为1 ，不符合要求的为0，进行加和的话就可以求出相应的数量
if和Excel中的if语句一样，if可以用case when datediff(reg_time,log_time) =0 then 1 else 0 end 来代替。

case when 还可以进行多重嵌套组合

select t.name ,
(case when t.dept in (1,2) then 'Sci' else
(case when t.dept=3 then 'Art' else 'None' end) end) from teacher t left join
dept d on t.dept=d.id;
##这个和excel中的if多重嵌套组合是一样的

好像也可以不用多重嵌套，直接使用

case when....then ...
     when ...then...
    ...
     else ...
end
##用多个when ...then ..来代替，if在这方面不必case then 方便

19.coalesce函数

coalesce(x,y,z),原理为从左往右优先返回不是null的值如：

  COALESCE(x,y,z) = x if x is not NULL
  COALESCE(x,y,z) = y if x is NULL and y is not NULL
  COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
  COALESCE(x,y,z) = NULL if x and y and z are all NULL

20.substring_index()（可以相当于python的split来进行文字的分列）

substring_index（被截取字段，关键字，关键字出现的次数）

select substring_index（"blog.jb51.net"，"."，2） as abstract from my_content_t 
结果：blog.jb51 

--2可以使用这个函数分列 （先找前4个带有"|"的，再从这里面取倒数第一个）
select substring_index(substring_index(geo,'|',4),'|',-1) city,count(1) pv
from access_log_without_ip
group by substring_index(substring_index(geo,'|',4),'|',-1)
order by pv desc 
limit 10