-- SELECT * FROM ZRZ WHERE XMMC LIKE '%{0}%' ORDER BY XMMC LIMIT {1} OFFSET {2};
-- select id,idnum,length(idnum) from household hh where length(IdNum)<18 limit 1;
-- VACUUM;


-- 1.ÐÐÕþÇø
create table Region(
  Id integer primary key, -- Ö÷¼ü
  RegionCode text(30), -- ÇøÓò±àÂë
  Province text(30), -- Ê¡
  City text(30), -- ÊÐ
  County text(30), -- ÏØ/Çø
  Street text(60), -- ÏçÕò/½ÖµÀ
  Community text(60), -- ÉçÇø
  Village text(60) -- ´å
);
insert into Region values (1,'110108','±±¾©ÊÐ','±±¾©ÊÐ','º£µíÇø','±±Ì«Æ½×¯½ÖµÀ','ÂÞׯÉçÇø','');

-- drop table Region;
-- delete from Region;
select * from Region;

-- 2.Â¥
create table Building(
  Id integer primary key, -- Ö÷¼ü
  Region int, -- ÐÐÕþÇø
  Name text(50), -- Ãû³Æ
  Height int, -- ¸ß¶È
  Floor int, -- ²ãÊý
  Charge text(20), -- ¸ºÔðÈË
  Phone text(20), -- ÁªÏµ·½Ê½
  Remark text(100) -- ±¸×¢
);
-- drop table Building;
insert into Building values (25,1,'¶«Æ½·¿',3,1,'','','');
insert into Building values (31,1,'±ÌÐËÔ°1ºÅÂ¥',72,24,'','','');
insert into Building values (32,1,'±ÌÐËÔ°2ºÅÂ¥',72,24,'','','');
insert into Building values (33,1,'±ÌÐËÔ°3ºÅÂ¥',72,24,'','','');
insert into Building values (19,1,'ÂÞׯÄÏÀï1ºÅÂ¥',18,6,'','','');
insert into Building values (8,1,'ÂÞׯÄÏÀï2ºÅÂ¥',18,6,'','','');
insert into Building values (17,1,'ÂÞׯÄÏÀï3ºÅÂ¥',18,6,'','','');
insert into Building values (18,1,'ÂÞׯÄÏÀï4ºÅÂ¥',18,6,'','','');
insert into Building values (23,1,'ÂÞׯÄÏÀï5ºÅÂ¥',18,6,'','','');
insert into Building values (20,1,'ÂÞׯÄÏÀï6ºÅÂ¥',18,6,'','','');
insert into Building values (9,1,'ÂÞׯÄÏÀï7ºÅÂ¥',18,6,'','','');
insert into Building values (24,1,'ÂÞׯÄÏÀï8ºÅÂ¥',18,6,'','','');
insert into Building values (21,1,'ÂÞׯÄÏÀï¼×1ºÅÂ¥',9,3,'','','');
insert into Building values (22,1,'ÂÞׯÄÏÀï¼×1ºÅÂ¥',18,6,'','','');
insert into Building values (1,1,'ÂÞׯÉçÇø¾Óί»á',3,1,'','','');
insert into Building values (14,1,'ÂÞׯÎ÷Àï10ºÅÂ¥',54,18,'','','');
insert into Building values (13,1,'ÂÞׯÎ÷Àï11ºÅÂ¥',54,18,'','','');
insert into Building values (16,1,'ÂÞׯÎ÷Àï12ºÅÂ¥',18,6,'','','');
insert into Building values (6,1,'ÂÞׯÎ÷Àï13ºÅÂ¥',12,4,'','','');
insert into Building values (26,1,'ÂÞׯÎ÷Àï1ºÅÂ¥',18,6,'','','');
insert into Building values (27,1,'ÂÞׯÎ÷Àï2ºÅÂ¥',18,6,'','','');
insert into Building values (28,1,'ÂÞׯÎ÷Àï3ºÅÂ¥',27,9,'','','');
insert into Building values (29,1,'ÂÞׯÎ÷Àï3ºÅÂ¥',27,9,'','','');
insert into Building values (30,1,'ÂÞׯÎ÷Àï3ºÅÂ¥',27,6,'','','');
insert into Building values (10,1,'ÂÞׯÎ÷Àï4ºÅÂ¥',18,6,'','','');
insert into Building values (3,1,'ÂÞׯÎ÷Àï5ºÅÂ¥',12,4,'','','');
insert into Building values (4,1,'ÂÞׯÎ÷Àï5ºÅÂ¥',6,2,'','','');
insert into Building values (7,1,'ÂÞׯÎ÷Àï5ºÅÂ¥',6,2,'','','');
insert into Building values (11,1,'ÂÞׯÎ÷Àï6ºÅÂ¥',27,9,'','','');
insert into Building values (5,1,'ÂÞׯÎ÷Àï7ºÅÂ¥',9,3,'','','');
insert into Building values (15,1,'ÂÞׯÎ÷Àï8ºÅÂ¥',54,18,'','','');
insert into Building values (12,1,'ÂÞׯÎ÷Àï9ºÅÂ¥',54,18,'','','');
insert into Building values (2,1,'Î÷ƽ·¿',3,1,'','','');

-- 3.ÎïÒµ
create table Property(
  Id integer primary key, -- Ö÷¼ü
  Name text(50), -- Ãû³Æ
  Charge text(50), -- ¸ºÔðÈË
  Phone text(20), -- ÁªÏµ·½Ê½
  Remark text(100) -- ±¸×¢
);
-- drop table Property;

select * from Property;

insert into Property values (1,'ÓùèëÎïÒµ','','','');
insert into Property values (2,'Îå¿óÎïÒµ','','','');
insert into Property values (3,'ÌìÔËÖÐÐË','','','');
insert into Property values (4,'Ê×èëÎïÒµ','','','');
insert into Property values (5,'Àͼ¼ÖÐÐÄ','','','');
insert into Property values (6,'¼»ÃÅÎïÒµ','','','');
insert into Property values (7,'¶«´ïÎïÒµ','','','');
insert into Property values (8,'³ÏÖÁÎïÒµ','','','');
insert into Property values (9,'±ÌÐËÔ°ÎïÒµ','','','');

select * from Property;

-- 4.·¿ÎÝ
create table House(
  Id integer primary key, -- Ö÷¼ü  
  Building int, -- ¥
  HouseState int, -- ·¿ÎÝ״̬
  FullName text(150), -- È«Ãû
  BuildNum text(50), -- Â¥ºÅ
  Unit text(20), -- µ¥Ôª
  HouseNum text(30), -- ÃÅÅƺÅÂë
  Remark text(100) -- ±¸×¢
);
-- drop table House;

select * from House;

-- 5.·¿ÎÝ״̬
create table HouseState(
  Id integer primary key, -- Ö÷¼ü
  Status text(20) -- ״̬
);
-- drop table HouseState;
insert into HouseState values (1,'×Ôס');
insert into HouseState values (2,'³ö×â');
insert into HouseState values (3,'ÏÐÖÃ');

select * from HouseState;

-- 6.ס»§
create table Household(
  Id integer primary key, -- Ö÷¼ü
  House int, -- ·¿ÎÝ
  HoldType int, -- ס»§Àà±ð
  Name text(30), -- ÐÕÃû
  Gender text(30), -- ÐÔ±ð
  Nation text(30), -- Ãñ×å
  Political text(30), -- ÕþÖÎÃæò
  IdNum text(30), -- Éí·ÝÖ¤ºÅ
  Addr text(100), -- »§¼®ËùÔÚµØ
  Phone text(20), -- ÁªÏµ·½Ê½
  Remark text(100) -- ±¸×¢
);
-- drop table Household;

select * from Household;

-- 7.ס»§Àà±ð
create table HoldType(
  Id integer primary key, -- Ö÷¼ü
  Status text(20) -- ״̬
);
insert into HoldType values (1,'·¿Ö÷');
insert into HoldType values (2,'¼ÒÍ¥³ÉÔ±');
insert into HoldType values (3,'³Ð×âÈË');
insert into HoldType values (4,'¶þ·¿¶«');
insert into HoldType values (5,'Öнé');

select * from HoldType;

select id,idnum,length(idnum) from household hh where length(IdNum)<18;

-- 8.ÉãÏñÍ·
create table Camera(
  Id integer primary key, -- Ö÷¼ü
  CType text(20), -- ÀàÐÍ
  CName text(50), -- Ãû³Æ
  Remark text(100) -- ±¸×¢
);

insert into Camera values (1,'ǹ»ú','Camera1','');
insert into Camera values (2,'ǹ»ú','Camera2','');
insert into Camera values (3,'Çò»ú','Camera3','');
insert into Camera values (4,'Çò»ú','Camera4','');
insert into Camera values (5,'Çò»ú','Camera5','');
insert into Camera values (6,'ǹ»ú','Camera6','');
insert into Camera values (7,'ǹ»ú','Camera7','');
insert into Camera values (8,'ǹ»ú','Camera8','');
insert into Camera values (9,'ǹ»ú','Camera9','');
insert into Camera values (10,'ǹ»ú','Camera10','');
insert into Camera values (11,'ǹ»ú','Camera11','');
insert into Camera values (12,'ǹ»ú','Camera12','');
insert into Camera values (13,'ǹ»ú','Camera13','');
insert into Camera values (14,'ǹ»ú','Camera14','');
insert into Camera values (15,'ǹ»ú','Camera15','');
insert into Camera values (16,'ǹ»ú','Camera16','');
insert into Camera values (17,'ǹ»ú','Camera17','');
insert into Camera values (18,'Çò»ú','Camera18','');
insert into Camera values (19,'ǹ»ú','Camera19','');
insert into Camera values (20,'ǹ»ú','Camera20','');
insert into Camera values (21,'ǹ»ú','Camera21','');
insert into Camera values (22,'ǹ»ú','Camera22','');
insert into Camera values (23,'ǹ»ú','Camera23','');
insert into Camera values (24,'ǹ»ú','Camera24','');
insert into Camera values (25,'Çò»ú','Camera25','');
insert into Camera values (26,'ǹ»ú','Camera26','');
insert into Camera values (27,'ǹ»ú','Camera27','');
insert into Camera values (28,'ǹ»ú','Camera28','');
insert into Camera values (29,'ǹ»ú','Camera29','');
insert into Camera values (30,'ǹ»ú','Camera30','');
insert into Camera values (31,'ǹ»ú','Camera31','');
insert into Camera values (32,'Çò»ú','Camera32','');
insert into Camera values (33,'ǹ»ú','Camera33','');
insert into Camera values (34,'ǹ»ú','Camera34','');
insert into Camera values (35,'ǹ»ú','Camera35','');
insert into Camera values (36,'ǹ»ú','Camera36','');
insert into Camera values (37,'ǹ»ú','Camera37','');
insert into Camera values (38,'ǹ»ú','Camera38','');
insert into Camera values (39,'ǹ»ú','Camera39','');
insert into Camera values (40,'ǹ»ú','Camera40','');
insert into Camera values (41,'ǹ»ú','Camera41','');
insert into Camera values (42,'ǹ»ú','Camera42','');
insert into Camera values (43,'ǹ»ú','Camera43','');
insert into Camera values (44,'ǹ»ú','Camera44','');
insert into Camera values (45,'ǹ»ú','Camera45','');
insert into Camera values (46,'ǹ»ú','Camera46','');
insert into Camera values (47,'ǹ»ú','Camera47','');
insert into Camera values (48,'ǹ»ú','Camera48','');
insert into Camera values (49,'ǹ»ú','Camera49','');
insert into Camera values (50,'ǹ»ú','Camera50','');
insert into Camera values (51,'ǹ»ú','Camera51','');
insert into Camera values (52,'ǹ»ú','Camera52','');
insert into Camera values (53,'ǹ»ú','Camera53','');
insert into Camera values (54,'ǹ»ú','Camera54','');
insert into Camera values (55,'ǹ»ú','Camera55','');
insert into Camera values (56,'Çò»ú','Camera56','');
insert into Camera values (57,'ǹ»ú','Camera57','');
insert into Camera values (58,'ǹ»ú','Camera58','');
insert into Camera values (59,'ǹ»ú','Camera59','');
insert into Camera values (60,'ǹ»ú','Camera60','');
insert into Camera values (61,'Çò»ú','Camera61','');
insert into Camera values (62,'ǹ»ú','Camera62','');
insert into Camera values (63,'ǹ»ú','Camera63','');
insert into Camera values (64,'ǹ»ú','Camera64','');
insert into Camera values (65,'ǹ»ú','Camera65','');
insert into Camera values (66,'ǹ»ú','Camera66','');
insert into Camera values (67,'ǹ»ú','Camera67','');
insert into Camera values (68,'ǹ»ú','Camera68','');
insert into Camera values (69,'ǹ»ú','Camera69','');
insert into Camera values (70,'ǹ»ú','Camera70','');
insert into Camera values (71,'ǹ»ú','Camera71','');
insert into Camera values (72,'ǹ»ú','Camera72','');
insert into Camera values (73,'ǹ»ú','Camera73','');
insert into Camera values (74,'ǹ»ú','Camera74','');
insert into Camera values (75,'Çò»ú','Camera75','');
insert into Camera values (76,'ǹ»ú','Camera76','');
insert into Camera values (77,'ǹ»ú','Camera77','');
insert into Camera values (78,'ǹ»ú','Camera78','');
insert into Camera values (79,'ǹ»ú','Camera79','');
insert into Camera values (80,'ǹ»ú','Camera80','');
insert into Camera values (81,'ǹ»ú','Camera81','');
insert into Camera values (82,'ǹ»ú','Camera82','');
insert into Camera values (83,'ǹ»ú','Camera83','');
insert into Camera values (84,'ǹ»ú','Camera84','');
insert into Camera values (85,'ǹ»ú','Camera85','');
insert into Camera values (86,'ǹ»ú','Camera86','');
insert into Camera values (87,'ǹ»ú','Camera87','');
insert into Camera values (88,'ǹ»ú','Camera88','');
insert into Camera values (89,'ǹ»ú','Camera89','');
insert into Camera values (90,'ǹ»ú','Camera90','');
insert into Camera values (91,'ǹ»ú','Camera91','');
insert into Camera values (92,'ǹ»ú','Camera92','');
insert into Camera values (93,'ǹ»ú','Camera93','');
insert into Camera values (94,'ǹ»ú','Camera94','');
insert into Camera values (95,'ǹ»ú','Camera95','');
insert into Camera values (96,'ǹ»ú','Camera96','');
insert into Camera values (97,'ǹ»ú','Camera97','');
insert into Camera values (98,'ǹ»ú','Camera98','');
insert into Camera values (99,'ǹ»ú','Camera99','');
insert into Camera values (100,'ǹ»ú','Camera100','');

select * from Camera;

select ifnull(max(Id),0)+1 MaxId from House;

select b.Id,r.Province,r.City,r.County,r.Street,r.Community,b.Name from Building b inner join Region r on b.Region=r.Id where b.Id=8;

select h.Id,h.FullName,s.Status,h.Remark from House h inner join Building b on h.Building=b.Id
inner join HouseState s on h.HouseState=s.Id where b.Name='ÂÞׯÄÏÀï2ºÅÂ¥' and h.Unit='1' and h.HouseNum='201';

select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark from Household h;
inner join HoldType t on h.HoldType=t.Id where House=115;

select unit,substr(unit,1,1),substr(unit,4) from house where buildnum='¼×1';

select * from house where building=31 and housenum is null;
update house set housenum=unit,unit='' where building=31 and housenum is null;

select * from household where length(Gender)=0 and length(Nation)=0 and length(Political)=0 and length(IdNum)=0 and length(Addr)=0 and length(Phone)=0;

select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark from Household h inner join HoldType t on h.HoldType=t.Id where h.Name like '%Íõ%' limit 20;

select * from household where length(IdNum) between 1 and 17;

select h.Id,h.FullName,s.Status,h.Remark from House h inner join Building b on h.Building=b.Id inner join HouseState s on h.HouseState=s.Id where b.Name='ÂÞׯÎ÷Àï3ºÅÂ¥' and h.Unit='8' and h.HouseNum='403'

select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark,o.Id --1245
from Household h inner join HoldType t on h.HoldType=t.Id inner join House o on h.House=o.Id inner join Building b on o.Building=b.Id
where b.Name='ÂÞׯÎ÷Àï3ºÅÂ¥' and o.Unit='8' and o.HouseNum='403';

select * from House h inner join HouseHold d on d.House=h.id where d.Id=8;

select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark,b.Name BName,o.Unit,o.HouseNum
from Household h inner join HoldType t on h.HoldType=t.Id inner join House o on h.House=o.Id inner join Building b on o.Building=b.Id
where h.Id=8;

select h.Id,t.Status,h.Name,h.Gender,h.Nation,h.Political,h.IdNum,h.Addr,h.Phone,h.Remark,b.Name BName,o.Unit,o.HouseNum
from Household h inner join HoldType t on h.HoldType=t.Id inner join House o on h.House=o.Id inner join Building b on o.Building=b.Id
where o.Id=(select House from Household where Id=8);
-----------------------------------------------------
-- ͳ¼ÆÄÐÅ®
select Gender,Count(1) Count from Household group by Gender order by Count;
select hh.Gender,Count(1) Count from Building b inner join House h on h.Building=b.Id inner join Household hh on hh.House=h.Id where b.Name='ÂÞׯÎ÷Àï3ºÅÂ¥' group by hh.Gender order by Count;

-- ͳ¼ÆÃñ×å
select Nation,Count(1) Count from Household group by Nation having count(1)>2 order by Count;
select hh.Nation,Count(1) Count from Building b inner join House h on h.Building=b.Id inner join Household hh on hh.House=h.Id where b.Name='ÂÞׯÎ÷Àï3ºÅÂ¥' group by Nation having count(1)>2 order by Count;

-- ͳ¼ÆÕþÖÎÃæò
select Political,Count(1) Count from Household group by Political order by Count;
select hh.Political,Count(1) Count from Building b inner join House h on h.Building=b.Id inner join Household hh on hh.House=h.Id where b.Name='ÂÞׯÎ÷Àï3ºÅÂ¥' group by Political order by Count;

-- ͳ¼Æ»§Êý
select Count(1) Count from Building b inner join House h on h.Building=b.Id where b.Name='ÂÞׯÎ÷Àï3ºÅÂ¥';

-- ͳ¼Æס»§Àà±ð
select t.Status,Count(1) Count from Household hh inner join HoldType t on hh.HoldType=t.Id group by HoldType order by Count;

-- ¼¶Áª¸üÐÂ
update House set BuildNum=(select name from building where id=building);

select t.Status,Count(1) Count from Household hh inner join HoldType t on hh.HoldType=t.Id inner join House h on hh.House=h.Id inner join Building b on h.Building=b.Id where b.Name='ÂÞׯÎ÷Àï3ºÅÂ¥' group by HoldType order by Count;
-----------------------------------------------------
select ifnull(max(Id),0)+1 MaxId from House;

select * from Household;
select Political,count(*) from Household group by Political;

update Household set Political='µ³Ô±' where Political='µ³Ô±Ë¶Ê¿';
update Household set Political='' where length(Political)>0 and Political not in ('µ³Ô±','ȺÖÚ','ÍÅÔ±')
select Political from household where length(Political)>0 and Political not in ('µ³Ô±','ȺÖÚ','ÍÅÔ±')

select nation from Household where instr(nation,'×å')=0 and length(nation)>0
update Household set nation='' where instr(nation,'×å')=0 and length(nation)>0

select * from House h where instr(h.HouseNum,'A')>0 order by BuildNum,HouseNum;
-----------------------------------------------------