create database `carsys4` default charset utf8;

use `carsys4`;

create table `tb_car` (
	`no` int(11) not null auto_increment,
	`carno` varchar(10) unique not null,
	`owner` varchar(20) not null,
	`brand` varchar(20) not null,
	primary key (`no`)
);

create table `tb_record` (
	`no` integer not null AUTO_INCREMENT,
	`reason` varchar(200) not null,
	`punish` varchar(200) not null,
	`makedate` date not null,
	`dealt` boolean default 0,
	`car_id` integer not null,
	`is_deleted` boolean default 0,
	`deleted_time` datetime,
	`updated_time` datetime,
	primary key (`no`),
	foreign key (`car_id`) references `tb_car` (`no`)
);

insert into `tb_car`
	(`no`, `carno`, `owner`, `brand`)
values
	(1,'川A12345','王大锤','QQ'),
	(2,'川A250SS','白元芳','Benz'),
	(3,'川B52088','狄仁杰','BMW'),
	(4,'川A54321','张小虎','Auto'),
	(5,'川B203T5','张佩琪','RR'),
	(6,'川AN9980','李乔治','Jetta'),
	(7,'川A13788','李佩佩','Swift');

insert into `tb_record`
	(`is_deleted`, `deleted_time`, `updated_time`, `no`, `reason`, `punish`, `makedate`, `dealt`, `car_id`)
values
	(0,NULL,NULL,1,'逆向行驶','扣3分，罚款500元','2019-01-01',0,1),
	(0,NULL,NULL,2,'超速行驶','扣2分，罚款300元','2019-01-10',0,2),
	(0,NULL,NULL,3,'违章停车','罚款100元','2019-03-15',0,1),
	(0,NULL,NULL,4,'殴打交警','吊销驾照，拘留15天','2019-04-01',0,1),
	(0,NULL,NULL,5,'酒驾','扣6分','2019-01-10',0,3),
	(0,NULL,NULL,6,'醉驾','扣12分，罚款3000元','2019-01-20',0,4),
	(0,NULL,NULL,7,'逆向行驶','扣3分，罚款500元','2018-12-15',0,1),
	(0,NULL,NULL,8,'殴打交警','吊销驾照，拘留15天','2019-04-01',0,2),
	(0,NULL,NULL,9,'酒驾','扣6分','2019-01-10',0,4),
	(0,NULL,NULL,10,'醉驾','扣12分，罚款3000元','2019-01-20',0,3),
	(0,NULL,NULL,11,'逆向行驶','扣3分，罚款500元','2018-12-15',0,5),
	(0,NULL,NULL,12,'逆向行驶','扣3分，罚款500元','2018-07-23',0,7),
	(0,NULL,NULL,13,'遮挡车牌','扣6分','2019-05-10',0,5),
	(0,NULL,NULL,14,'超速行驶','扣2分，罚款300元','2018-10-30',0,6);