mysql 使用

复制表

CREATE TABLE A LIKE B

此种方式在将表B复制到A时候会将表B完整的字段结构和索引复制到表A中来。

CREATE TABLE A AS SELECT x,x,x,xx FROM B LIMIT 0

此种方式只会将表B的字段结构复制到表A中来，但不会复制表B中的索引到表A中来。这种方式比较灵活可以在复制原表表结构的同时指定要复制哪些字段，并且自身复制表也可以根据需要增加字段结构。
两种方式在复制表的时候均不会复制权限对表的设置。比如说原本对表B做了权限设置，复制后，表A不具备类似于表B的权限。
留空，接下来会探讨两种复制表的性能

索引

ALTER TABLE e_patentfamily0000001 ADD INDEX doindex (docnumber )  

建立索引后，删除索引，并没有删除索引的名字

http://blog.csdn.net/xluren/article/details/32746183
http://blog.csdn.net/huangjin0507/article/details/49421427

启动关闭mysql

#启动
systemctl start  mysqld.service
#关闭，输入命令后输入密码
mysqladmin -u root -p shutdown
#查看是否启动成功
ps -ef|grep mysql
#进入mysql
mysql -uroot -p
查看端口和进程号
netstat -antup |grep 3306

nivcat出问题，可以把mysql关闭，重启mysql和nivcat
https://blog.csdn.net/king_1421484363/article/details/73277771

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因是sql_mode有问题，修改，路径是vi /etc/my.cnf
https://blog.csdn.net/wulantian/article/details/8905573

所有在使用“utf8”的 MySQL 和 MariaDB 用户都应该改用“utf8mb4”，永远都不要再使用“utf8”

https://mp.weixin.qq.com/s?__biz=MzIwMzg1ODcwMw==&mid=2247487968&idx=1&sn=2ff7b511f6727c7816ab02fc0e1c0361&chksm=96c9a780a1be2e961cd5e7c5e5ff32961cd2b6c1bac480f8f1c3f281e5bf1504fecebdd59d48#rd

#找出重复的
Select * From attrInfo as a Where a.trackletId In 
(Select a.trackletId From attrInfo as a where a.startEachTime>"2018-03-21 16:05:51"
Group By a.trackletId Having COUNT(a.trackletId)>1 ) 
and a.startEachTime>"2018-03-21 16:05:51"
ORDER BY a.trackletId DESC

#统计数量 AND a.nodeName='gpu-task-nod2'
SELECT a.trackletId,a.startEachTime FROM attrInfo as a where  a.startEachTime>"2018-05-11 12:16:49" AND a.endEachTime<"2018-05-12 12:19:11"  ORDER BY a.startEachTime ASC GROUP BY a.nodeName_gpu   and a.isMultiGpu like "Multi%"

SELECT COUNT(DISTINCT a.trackletId) FROM attrInfo as a where  a.startEachTime>"2018-11-18 22:16:50"

Select * From attrInfo as a Where a.trackletId In (Select a.trackletId From attrInfo as a Group By a.trackletId Having Count(*)>1) ORDER BY a.startEachTime

SELECT COUNT(DISTINCT a.trackletId) FROM attrInfo as a where a.videoName="CAM01-20140301103814-20140301105026" and a.startEachTime>"2018-03-12 20:13:09"

SELECT COUNT(*) from reIdInfo as a WHERE a.hashCode =695292156
SELECT COUNT(*) from reIdInfo as a WHERE a.hashCode =123578865

SELECT DISTINCT a.nodeName_gpu from attrInfo as a WHERE a.startEachTime>"2018-11-15 08:13:09" AND a.endEachTime<"2018-05-10 16:10:00" 

SELECT * from attrInfo as a WHERE a.startEachTime>"2018-04-20 09:52:29"

SELECT * from reIdInfo as a WHERE a.startEachTime>"2018-04-20 10:20:48"

SELECT COUNT(DISTINCT trackInfo.videoName) FROM trackInfo WHERE trackInfo.startEachTime>"2018-11-18 22:16:50"

#相加某一列的值
SELECT sum(a.trackletSize) from trackInfo as a WHERE a.startEachTime>"2018-04-27 13:03:56" AND a.endEachTime<"2018-04-02 16:10:03" 

SELECT count(DISTINCT a.trackletId) from trackInfo as a WHERE a.startEachTime>"2018-11-18 22:16:50" AND a.endEachTime<"2018-08-03 11:10:03" 

SELECT sum(a.borrowObjectTime) from attrInfo as a WHERE a.startEachTime>"2018-03-22 11:05:15"

#找不相等的要用内连接
SELECT  d.c,d.taskID,e.taskID,e.trackletSize from 
(SELECT count( a.trackletId) as c,a.taskID FROM attrInfo as a GROUP BY a.taskID) as d
 inner JOIN trackInfo as e on d.taskID = e.taskID and d.c<>e.trackletSize

SELECT trackInfo.videoName from trackInfo WHERE trackInfo.taskID="d197d78d-b24a-4bbc-b979-54c833f011b6"

#计算时间的差
SELECT MIN(a.startEachTime),MAX(a.endEachTime),TIMESTAMPDIFF(SECOND, MIN(a.startEachTime),MAX(a.endEachTime)) 
from attrInfo as a where  a.startEachTime>"2018-08-8 10:40:39" AND a.endEachTime<"2018-05-10 16:10:00" 

SELECT SUM(d.timeA) from (SELECT TIMESTAMPDIFF(SECOND, a.startEachTime,a.endEachTime) 
as timeA from trackInfo as a WHERE a.startEachTime>"2018-03-16 10:39:56" AND 
a.endEachTime<"2018-03-16 15:51:24") as d
a.videoName="CAM01-20140321154534-20140321155614"

SELECT * from trackInfo WHERE trackInfo.videoName="CAM01-20140321154534-20140321155614"

#增加一列
alter table attrInfo add  column borrowObjectTime datetime;
alter table reIdInfo add  column borrowObjectTime datetime;

#清空某一列数据
update attrInfo set borrowObjectTime=null

alter table attrInfo MODIFY borrowObjectTime bigint; 　

SELECT * from reIdInfo as a where  a.startEachTime>"2018-05-10 16:10:17" AND a.endEachTime<"2018-05-10 17:00:42" and a.borrowObjectTime<=10 and a.borrowObjectTime<200


SELECT tr.trackletId from (select t.trackletId FROM trackInfo as t where  t.startEachTime>"2018-04-25 15:30:30") as tr LEFT JOIN  
(select a.trackletId  from attrInfo as a where  a.startEachTime>"2018-04-25 15:30:30") as att
ON tr.trackletId=att.trackletId where att.trackletId IS NULL

当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.