~admin/CommunityServer.git

-- 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;
-----------------------------------------------------