xdmy/doc/mcs/sql/mcs.sql

-- select * from apisql  where  sqlname  ='checkDeviceId';
-- select * from apisql  where  sqlname  ='addDeviceInformation';
-- select * from apisql  where  sqlname  ='updateUserDevice';
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='updateDeviceInformation';
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='updateUser';
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='user_mainthenance';
-- "insert into user_mainthenance( user_id,maintenance_id) 
-- select DISTINCT  ?, ? from DUAL  where NOT EXISTS 
-- (select maintenance_id from user_mainthenance where maintenance_id = ?
--  and user_id = ?)"
--  "delete from user_mainthenance            where user_id = 1"

-- select *  from user_mainthenance limit 10;
-- select * from video;

-- show create table device_information;
-- select distinct maintenancePerson from user ;
-- where id =2494;
-- select distinct maintenancePerson from mainthenance limit 10;

-- select  if((select id from device_information  where deviceId = 'apptest2')is null,'','已被绑定') vmsg;
-- INSERT INTO device_information(empId,uId,deviceId,`password`)  VALUES(3206,'PC','apptest2','mcs8@666');
-- update `user` set device = 1 where empid = ?
-- update user set username=? ,empid=?,sort=?,enable=?,maintenancePerson=?,keeper=? where id=? 
-- update `user` set  device = 1 where empId = empIdIN;
-- update  device_information  set  empId = empIdIN  where  deviceId = deviceIdIN;

-- update  device_information set empid=3206 where deviceId='apptest2';
-- show create procedure update_device;
-- delete from  device_information  where deviceId ='apptest3'
-- call update_device(3206,'apptest2');
-- call update_device(1,'apptest2');
-- delete from device_information where  id =21;
-- select * from device_information ;
-- select * from user where device=1;
-- -- select v.*,bk.upkeepCode from video v left join bigupkeep bk on v.upkeepId =bk.id;
-- select
-- case 
--       when (select  id  from video where  upkeepId = 183064 ) is null then '未录制'
--       when (select  id  from video where  upkeepId = 183064 and endRecord=2 ) is not null then '录制中'
--       when (select  id  from video where  upkeepId = 183064 and newCreateTime=createTime and location <>'') is not null then '已录制'
--       else '已录制上传中'
-- end as videoTxt;
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='upkeepChargDone';
-- select * from apisql  where sqlname ='getMcsAccounts';
-- select table_name from information_schema.TABLES 
-- where table_schema=schema() and table_name like '%app%';
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='updateEquseEmpId';
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='deleteutupbyBigid';
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='insertSpotList';
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='insertutup';
-- select sqlname,params,sqlstr from apisql  where  sqlname  ='completeUpkeep';



-- select params,sqlstr from apisql  where  sqlname  ='getBigupkeepListAPP';
select params,sqlstr from apisql  where  sqlname  ='getBigupkeepList';
-- select params,sqlstr from apisql  where  sqlname  ='addDeviceVideo';
-- select params,sqlstr from apisql  where  sqlname  ='updateUserDevice';
-- show create procedure add_device_video;
-- describe video;
-- update user set device=1 where username ='02820' and empid=3206;
-- select * from emp where id =3206;
-- select *  from user where username ='02820' and empid=3206;
-- select * from video ;
-- where upkeepId  =4657;
-- delete from video;
-- select  upkeepId  ,count(*)  from video  group by upkeepId;
-- delete from video where  upkeepId = 4657;
-- alter table user
-- add column deviceId varchar(50) not  null default '' after device;
-- alter table video 
-- add column endRecord int(11) not null default 0;
-- add unique key `upkeepId` (`upkeepId`);
-- add column newCreateTime datetime default '0000-00-00 00:00:00';
-- show index from video;
-- show  create table  video;