mysql时间字段中又t和z怎么处理_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...),

#用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