mysql建立索引和檢視

為不為發表於2018-05-25

索引建立:
1.選表,設計表,選擇欄位
這裡寫圖片描述
2.建檢視:
新建檢視,寫檢視程式碼

select `parkingmessage`.`id` AS `id`,`parkingmessage`.`version` AS `version`,`parkingmessage`.`sname` AS `sname`,`parkingmessage`.`iddep` AS `iddep`,`parkingmessage`.`idowner` AS `idowner`,`parkingmessage`.`createdby` AS `createdby`,`parkingmessage`.`created` AS `created`,`parkingmessage`.`updatedby` AS `updatedby`,`parkingmessage`.`updated` AS `updated`,`parkingmessage`.`starttime` AS `starttime`,`parkingmessage`.`endtime` AS `endtime`,`parkingmessage`.`platenumber` AS `platenumber`,`parkingmessage`.`parkinglotbelong` AS `parkinglotbelong`,`parkingmessage`.`parkingtime` AS `parkingtime`,`parkingmessage`.`paymentstate` AS `paymentstate`,`parkingmessage`.`paymenttype` AS `paymenttype`,`parkingmessage`.`note` AS `note`,`parkingmessage`.`platephoto` AS `platephoto`,`parkingmessage`.`platephoto_title` AS `platephoto_title`,`parkingmessage`.`parkingspace` AS `parkingspace`,(case when isnull(`parkingmessage`.`endtime`) then `calc_fee`(`parkingmessage`.`parkinglotbelong`,`parkingmessage`.`cartype`,`parkingmessage`.`starttime`) else `parkingmessage`.`parkingfee` end) AS `parkingfee`,`parkingmessage`.`ordernum` AS `ordernum`,`parkingmessage`.`parkingphoto` AS `parkingphoto`,`parkingmessage`.`parkingphoto_title` AS `parkingphoto_title`,`parkingmessage`.`lcfs` AS `lcfs`,`parkingmessage`.`parkarea` AS `parkarea`,`parkingmessage`.`parkstate` AS `parkstate`,`parkingmessage`.`parkuserbelong` AS `parkuserbelong`,`parkingmessage`.`idbook` AS `idbook`,`parkingmessage`.`address` AS `address`,`parkingmessage`.`booktime` AS `booktime`,`parkingmessage`.`isbook` AS `isbook`,`parkingmessage`.`paytime` AS `paytime`,`parkingmessage`.`prepay` AS `prepay`,`parkingmessage`.`cartype` AS `cartype`,`parkingmessage`.`qrcode` AS `qrcode`,`parkingmessage`.`prepaytime` AS `prepaytime`,`parkingmessage`.`payfee` AS `payfee`,`parkingmessage`.`backfee` AS `backfee`,`parkingmessage`.`supplementaryfee` AS `supplementaryfee`,`parkingmessage`.`supplementarytime` AS `supplementarytime`,`parkingmessage`.`pushtoken` AS `pushtoken`,`parkingmessage`.`maninput` AS `maninput` from `parkingmessage` where ((`parkingmessage`.`endtime` > curdate()) or isnull(`parkingmessage`.`endtime`)) union all select `hparkingmessage`.`id` AS `id`,`hparkingmessage`.`version` AS `version`,`hparkingmessage`.`sname` AS `sname`,`hparkingmessage`.`iddep` AS `iddep`,`hparkingmessage`.`idowner` AS `idowner`,`hparkingmessage`.`createdby` AS `createdby`,`hparkingmessage`.`created` AS `created`,`hparkingmessage`.`updatedby` AS `updatedby`,`hparkingmessage`.`updated` AS `updated`,`hparkingmessage`.`starttime` AS `starttime`,`hparkingmessage`.`endtime` AS `endtime`,`hparkingmessage`.`platenumber` AS `platenumber`,`hparkingmessage`.`parkinglotbelong` AS `parkinglotbelong`,`hparkingmessage`.`parkingtime` AS `parkingtime`,`hparkingmessage`.`paymentstate` AS `paymentstate`,`hparkingmessage`.`paymenttype` AS `paymenttype`,`hparkingmessage`.`note` AS `note`,`hparkingmessage`.`platephoto` AS `platephoto`,`hparkingmessage`.`platephoto_title` AS `platephoto_title`,`hparkingmessage`.`parkingspace` AS `parkingspace`,`hparkingmessage`.`parkingfee` AS `parkingfee`,`hparkingmessage`.`ordernum` AS `ordernum`,`hparkingmessage`.`parkingphoto` AS `parkingphoto`,`hparkingmessage`.`parkingphoto_title` AS `parkingphoto_title`,`hparkingmessage`.`lcfs` AS `lcfs`,`hparkingmessage`.`parkarea` AS `parkarea`,`hparkingmessage`.`parkstate` AS `parkstate`,`hparkingmessage`.`parkuserbelong` AS `parkuserbelong`,`hparkingmessage`.`idbook` AS `idbook`,`hparkingmessage`.`address` AS `address`,`hparkingmessage`.`booktime` AS `booktime`,`hparkingmessage`.`isbook` AS `isbook`,`hparkingmessage`.`paytime` AS `paytime`,`hparkingmessage`.`prepay` AS `prepay`,`hparkingmessage`.`cartype` AS `cartype`,`hparkingmessage`.`qrcode` AS `qrcode`,`hparkingmessage`.`prepaytime` AS `prepaytime`,`hparkingmessage`.`payfee` AS `payfee`,`hparkingmessage`.`backfee` AS `backfee`,`hparkingmessage`.`supplementaryfee` AS `supplementaryfee`,`hparkingmessage`.`supplementarytime` AS `supplementarytime`,`hparkingmessage`.`pushtoken` AS `pushtoken`,`hparkingmessage`.`maninput` AS `maninput` from `hparkingmessage` where (`hparkingmessage`.`endtime` > curdate())

相關文章