mysql常用操作

记录mysql常用操作

1.添加字段

ALTER TABLE table_name ADD column_name datatype

例如:alter table student add name varchar;

2.删除字段

ALTER TABLE table_name DROP COLUMN column_name

例如:alter table student drop column name;

删除多个字段

alter table table_name drop column column_name,

                                    drop column column_name2;

例如:alter table student drop column name,

                                      drop column age;

3.用sql生成sql

 

concat,inner join,left join ,is null,insert 这些就是下面需要使用的sql知识 

SELECT 
CONCAT("INSERT into records(nRecordType,nRecordFileType,nRecordStatus,strRecordName,nUserId,nCollectRegionId,startTime,endTime,streamNo,nRepairDevId,strCollectIp,dvrPort,dvrUserName,dvrPwd,nCollectChan,countNum) values ('1','1','2','",t.fileName,"','",t.userId,"','",t.regionId,"','",t.startTime,"','",t.endTime,"','",t.streamNo,"','",t.devId,"','",t.strIP,"','8000','admin', '807CAD0037D8ECFE0E7946D67DB38762','",t.devChannel,"','0');")
 from 
(SELECT p.fileName,p.userId,p.regionId ,p.startTime,p.endTime,p.streamNo,p.devId,d.strIP,p.devChannel from
prosaverecord p 
INNER JOIN device d on p.devId = d.id
LEFT JOIN records r on p.fileName = r.strRecordName and p.startTime = r.startTime and p.endTime = r.endTime 
where r.id is null) t;
=======生成的sql=======
INSERT into records(nRecordType,nRecordFileType,nRecordStatus,strRecordName,nUserId,nCollectRegionId,startTime,endTime,streamNo,nRepairDevId,strCollectIp,dvrPort,dvrUserName,dvrPwd,nCollectChan,countNum) values ('1','1','2','录像20190626100850-李思','7','5','2019-06-26 10:08:49','2019-06-26 10:09:10','1x.2x.13x.9x_8000_33','95','1x.2x.13x.9x','8000','admin', '807CAD0037D8ECFE0E7946D67DB38762','33','0');
===============
最新版本-更强的适应性,并且对空字段做了处理
===
select concat("insert into records(nHistoryRecordId,nCollectRegionId,nUserId,strRecordName,nRecordType,nRecordFileType,nRecordStatus,startTime,endTime,streamNo,nRepairDevId,strCollectIp,dvrPort,dvrUserName,dvrPwd,nCollectChan,screenshotSrc,countNum,dtBusinessTime) values ('",t.id,"','",t.regionId,"','",t.userId,"','",t.fileName,"','","1","','","1","','","2","','",t.startTime,"','",t.endTime,"','",t.streamNo,"','",t.devId,"','",t.strIp,"','",t.nPort,"','",t.strUser,"','",t.strPassword,"','",t.devChannel,"','",t.screenshotSrc,"','","0","','",t.dtBusinessTime,"');") 
 as cstr from (
SELECT p.id,p.regionId,p.userId, p.fileName,p.startTime,p.endTime,p.streamNo,p.devId,d.strIP,d.nPort,d.strUser,d.strPassword,p.devChannel,ifnull(p.screenshotSrc,'') screenshotSrc,p.dtBusinessTime from
prosaverecord p 
INNER JOIN device d on p.devId = d.id
where  p.cvr = 57 and p.upload = 1
) t ORDER BY t.id;

=========如果有的字段为空,会生产空的sql语句,所以用这个语句来检测是否生成了空的sql=========
select * from (
select concat("insert into records(nHistoryRecordId,nCollectRegionId,nUserId,strRecordName,nRecordType,nRecordFileType,nRecordStatus,startTime,endTime,streamNo,nRepairDevId,strCollectIp,dvrPort,dvrUserName,dvrPwd,nCollectChan,screenshotSrc,countNum,dtBusinessTime) values ('",t.id,"','",t.regionId,"','",t.userId,"','",t.fileName,"','","1","','","1","','","2","','",t.startTime,"','",t.endTime,"','",t.streamNo,"','",t.devId,"','",t.strIp,"','",t.nPort,"','",t.strUser,"','",t.strPassword,"','",t.devChannel,"','",t.screenshotSrc,"','","0","','",t.dtBusinessTime,"');") 
 as cstr from (
SELECT p.id,p.regionId,p.userId, p.fileName,p.startTime,p.endTime,p.streamNo,p.devId,d.strIP,d.nPort,d.strUser,d.strPassword,p.devChannel,ifnull(p.screenshotSrc,'') screenshotSrc,p.dtBusinessTime from
prosaverecord p 
INNER JOIN device d on p.devId = d.id
where  p.cvr = 57 and p.upload = 1
) t ORDER BY t.id
) tt
where tt.cstr is null;

然后用navicat导出

选择txt,并且选择文本限定符为无(否则生成的sql带有"")

最后得到可执行的sql