19.1、javaweb19案例的SQL代码

下面存放的是javaweb19案例的SQL代码

set names utf8;
drop table if exists tab_favorite;
drop table if exists tab_route_img;
drop table if exists tab_route;
drop table if exists tab_category;
drop table if exists tab_seller;
drop table if exists tab_user;
/* Table: tab_category                                          */
create table tab_category
(
   cid                  int not null auto_increment,
   cname                varchar(100) not null,
   primary key (cid),
   unique key AK_nq_categoryname (cname)
);
/* Table: tab_favorite                                          */
create table tab_favorite
(
   rid                  int not null,
   date                 date not null,
   uid                  int not null,
   primary key (rid, uid)
);
/* Table: tab_route                                             */
create table tab_route
(
   rid                  int not null auto_increment,
   rname                varchar(500) not null,
   price                double not null,
   routeIntroduce       varchar(1000),
   rflag                char(1) not null,
   rdate                varchar(19),
   isThemeTour          char(1) not null,
   count                int default 0,
   cid                  int not null,
   rimage               varchar(200),
   sid                  int,
   sourceId             varchar(50),
   primary key (rid),
   unique key AK_nq_sourceId (sourceId)
);
/* Table: tab_route_img                                         */
create table tab_route_img
(
   rgid                 int not null auto_increment,
   rid                  int not null,
   bigPic               varchar(200) not null,
   smallPic             varchar(200),
   primary key (rgid)
);
/* Table: tab_seller                                            */
create table tab_seller
(
   sid                  int not null auto_increment,
   sname                varchar(200) not null,
   consphone            varchar(20) not null,
   address              varchar(200),
   primary key (sid),
   unique key AK_Key_2 (sname)
);
/* Table: tab_user                                              */
create table tab_user
(
   uid                  int not null auto_increment,
   username             varchar(100) not null,
   password             varchar(32) not null,
   name                 varchar(100),
   birthday             date,
   sex                  char(1),
   telephone            varchar(11),
   email                varchar(100),
   status               char(1) ,
   code					varchar(50),
   primary key (uid),
   unique key AK_nq_username (username),
   unique key AK_nq_code (code)
);
alter table tab_favorite add constraint FK_route_favorite foreign key (rid)
      references tab_route (rid) on delete restrict on update restrict;
alter table tab_favorite add constraint FK_user_favorite foreign key (uid)
      references tab_user (uid) on delete restrict on update restrict;
alter table tab_route add constraint FK_category_route foreign key (cid)
      references tab_category (cid) on delete restrict on update restrict;
alter table tab_route add constraint FK_seller_route foreign key (sid)
      references tab_seller (sid) on delete restrict on update restrict;
alter table tab_route_img add constraint FK_route_routeimg foreign key (rid)
      references tab_route (rid) on delete restrict on update restrict;  
insert  into `tab_category`(`cid`,`cname`) values (8,'全球自由行'),(5,'国内游'),(4,'处境游'),(7,'抱团定制'),(6,'港澳游'),(2,'酒店'),(1,'门票'),(3,'香港车票');
insert  into `tab_seller`(`sid`,`sname`,`consphone`,`address`) values (1,'程序员','12345678901','javaEE');
/*Data for the table `tab_route` */
insert  into `tab_route`(`rid`,`rname`,`price`,`routeIntroduce`,`rflag`,`rdate`,`isThemeTour`,`count`,`cid`,`rimage`,`sid`,`sourceId`) values (1,'【旅展 半价特惠 重走丝路•漫游宁夏 双飞4天】银川西部影视城 穆民新村 中卫沙坡头【品美酒 回族学唱花儿 感悟民俗】',999,'走进【宁夏沙坡头】，感受西北大漠风情、体会“大漠孤烟直，长河落日圆”的塞上风光！','1','2018-02-09 01:13:16','0',0,5,'img/product/small/m304b69a4c8328f7d6b8d5dadef020fe07.jpg',1,'23677'),(2,'【官网专享 立减¥500 张家界天门山+大峡谷+凤凰古城+玻璃栈道+玻璃桥 高铁4天 无自费5钻】印象鲵宴-赶年宴+2晚蓝湾博格酒店',1799,'官网专线，顶级品质！全程超豪华住宿，2晚入住超豪华铂金-蓝湾博格国际酒店，独家尊享金马VIP贵宾专用楼层。','1','2018-02-09 01:13:17','0',990,5,'img/product/small/m34866f055de8630e94e25c40f277a79ba.jpg',1,'22066'),(3,'【官网专享 5折预售 越南下龙湾+河内+芒街 高铁4天 高级团】双导游服务 免收服务小费【越超值·美食越南】',1199,'双导游服务，免收服务小费，周全照顾贴心服务随心出游！品尝越南特色国宝美食，升级一餐越式炸鸡火锅宴！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m3db4d2277b5df3d98597f79082ef92d6d.jpg',1,'21998'),(4,'【官网专享 送箱 ¥1099秒杀 华东五市+乌镇+南浔 双飞6天 南京进上海出】升级1晚豪华酒店 漫步西湖【水墨江南】',1099,'升级入住1晚豪华酒店；畅玩江南两大经典水乡——乌镇水乡和南浔水乡，体验这里的历史文化底蕴、清丽婉约的水乡古镇风貌。','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m3d91ef60e0c7fdeee97a4e2d3e5a42e84.jpg',1,'21943'),(5,'梅州 双飞3天2晚 自由行套票【含广州直飞梅州早对晚含税往返机票+2晚梅州市区酒店】',999,'含广州直飞梅州早对晚含税往返机票+2晚梅州市区酒店！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m3ac1aa10b493b4b22221e19ba39f7e6a1.jpg',1,'21917'),(6,'【宁夏银川 双飞4天】 银川中华回乡文化园 中卫沙坡头 宁夏回族自治区博物馆 西关清真寺',1299,'中卫升级1晚豪华酒店！走进【宁夏沙坡头】，感受西北大漠风情、体会“大漠孤烟直，长河落日圆”的塞上风光！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m312cf97538027a813a4fb0bd6980a7fea.jpg',1,'16968'),(7,'【¥1099秒杀 约惠华东五市+乌镇+木渎+灵山大佛 双飞6天 无锡进上海出】升级2晚豪华酒店',1099,'畅玩乌镇水乡和木渎水乡，游览上海迪士尼小镇，其中升级入住2晚豪华酒店，品尝杭州特色宴“乾隆御茶宴”，送品尝金秋肥美大闸蟹一只！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m370365f2ea91b638695218a6df13e3819.jpg',1,'14974'),(8,'【¥1099秒杀 特惠华东五市+乌镇+木渎+灵山大佛 双飞6天 上海进杭州出】升级2晚豪华酒店',1099,'升级两晚豪华酒店，畅游双水乡乌镇+木渎古镇，南京中山陵，杭州西湖！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m30971b3d22ab37242636088780cfb8eb5.jpg',1,'13580'),(9,'【豪叹喜来登 桂林 阳朔西街高铁4天纯玩 高级团】漓江 西山公园 象鼻山 银子岩',1299,'1晚指定入住福朋喜来登酒店，船游全程大漓江，游览世界溶洞奇观银子岩','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m35c17b5b73d618bbdd2abe231f1307838.jpg',1,'2702'),(10,'【春节 湖南郴州莽山+东江湖+冰雪森林温泉 高铁3天 顶贵团】欢迎年宴+温泉养生宴+农家菜【金狗闹郴晚·冰天雪地泡温泉】',2399,'莽山景区入住“森林中的皇宫”之美誉的莽山森林温泉酒店；市区入住享“酒店就是景区”美誉的国际奢华品牌酒店---温德姆至尊豪庭酒店！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m3c9823bc50368af0fe83eff14a5587c0a.jpg',1,'20523'),(11,'【春节 莽山+东江湖+冰雪森林温泉 高铁3天 贵宾团】年夜欢迎晚宴+温泉养身宴+特色火锅+湘南风味餐+莽山农家菜',1999,'独家首创“画中游”游览方式—-徒步醉美小东江景区，颁发徒步证书；特别赠送：美味·东江鱼品尝装礼盒！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m3501326413840ce82b9ba14db3a3a5eea.jpg',1,'20517'),(12,'【立减¥400 桃花季包列 西藏拉萨+日喀则+林芝 双卧12天】青藏铁路 布达拉宫 东方瑞士鲁朗 圣湖羊卓雍措 扎什伦布寺',5599,'【包列进藏】乘坐世界最先进的青藏列车，观世界坐标级景观带，有氧车厢进入西藏，由低海拔至高海拔逐步适应，安全保障。畅享拉林高等级公路沿途“颜值担当”雪域江南风光！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m32bdf1849b307712c4a0843b89ed83b5f.jpg',1,'20060'),(13,'【立减¥200 桃花季包列 西藏拉萨+林芝桃花季双卧10天】青藏铁路景观带 布达拉宫 东方瑞士鲁朗 嘎拉桃花村',4399,'【包列进藏】乘坐世界最先进的青藏列车，观世界坐标级景观带，有氧车厢进入西藏，由低海拔至高海拔逐步适应，安全保障。畅享拉林高等级公路沿途“颜值担当”雪域江南风光！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m3f535b6065c0085e6e69d1df7337bc60c.jpg',1,'20059'),(14,'【¥999/2人 广西北海双飞4天 南航往返】北海银滩 北海海景大道 北部湾广场 侨港镇 南迈渔村',999,'南航飞机直达北海不用1个半小时，畅游真正“天下第一滩”北海十里银滩！漫步北海老街，探秘南迈渔村！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m3d62aa9f19c84f99a8b700f12824b195b.jpg',1,'13498'),(15,'【¥1999秒杀 新疆乌鲁木齐+天山天池+鄯善+火焰山 双飞5天 超贵团】库姆塔格沙漠 坎儿井',1999,'升级一晚超豪华酒店！鄯善沙漠哈密瓜的故乡，沙漠中的绿洲吐 鲁 番，畅游天山天池！','1','2018-02-09 01:13:17','0',0,5,'img/product/small/m3b73ad0712746ef03019e34fb9757401d.jpg',1,'13344');


/*Data for the table `tab_route_img` */

insert  into `tab_route_img`(`rgid`,`rid`,`bigPic`,`smallPic`) values (1,1,'img/product/size4/m421d66d4446940b1c80a0092c91e75184.jpg','img/product/size2/m221d66d4446940b1c80a0092c91e75184.jpg'),(2,1,'img/product/size4/m4227f0cde1b021a77c56c1c18a8f2e7fd.jpg','img/product/size2/m2227f0cde1b021a77c56c1c18a8f2e7fd.jpg'),(3,1,'img/product/size4/m412cf97538027a813a4fb0bd6980a7fea.jpg','img/product/size2/m212cf97538027a813a4fb0bd6980a7fea.jpg'),(4,1,'img/product/size4/m47fe3031a3df75ab5e6711ae2338c18f7.jpg','img/product/size2/m27fe3031a3df75ab5e6711ae2338c18f7.jpg'),(5,2,'img/product/size4/m44866f055de8630e94e25c40f277a79ba.jpg','img/product/size2/m24866f055de8630e94e25c40f277a79ba.jpg'),(6,3,'img/product/size4/m4db4d2277b5df3d98597f79082ef92d6d.jpg','img/product/size2/m2db4d2277b5df3d98597f79082ef92d6d.jpg'),(7,4,'img/product/size4/m4d91ef60e0c7fdeee97a4e2d3e5a42e84.jpg','img/product/size2/m2d91ef60e0c7fdeee97a4e2d3e5a42e84.jpg'),(8,5,'img/product/size4/m4ac1aa10b493b4b22221e19ba39f7e6a1.jpg','img/product/size2/m2ac1aa10b493b4b22221e19ba39f7e6a1.jpg'),(9,5,'img/product/size4/m4a3f85cf3b49c183ea3cc5cd4b0424532.jpg','img/product/size2/m2a3f85cf3b49c183ea3cc5cd4b0424532.jpg'),(10,5,'img/product/size4/m41a8a1be2a84a99147278e5cf8ed432d0.jpg','img/product/size2/m21a8a1be2a84a99147278e5cf8ed432d0.jpg'),(11,5,'img/product/size4/m40c291dba8e45063fbfcecea64b3e873f.jpg','img/product/size2/m20c291dba8e45063fbfcecea64b3e873f.jpg'),(12,6,'img/product/size4/m412cf97538027a813a4fb0bd6980a7fea.jpg','img/product/size2/m212cf97538027a813a4fb0bd6980a7fea.jpg'),(13,6,'img/product/size4/m404b69a4c8328f7d6b8d5dadef020fe07.jpg','img/product/size2/m204b69a4c8328f7d6b8d5dadef020fe07.jpg'),(14,6,'img/product/size4/m4b0c7f8ed9ff7ec21d7c9f88da639f29d.jpg','img/product/size2/m2b0c7f8ed9ff7ec21d7c9f88da639f29d.jpg'),(15,6,'img/product/size4/m470c5066e87b574c1c3faef60243053a3.jpg','img/product/size2/m270c5066e87b574c1c3faef60243053a3.jpg'),(16,7,'img/product/size4/m470365f2ea91b638695218a6df13e3819.jpg','img/product/size2/m270365f2ea91b638695218a6df13e3819.jpg'),(17,7,'img/product/size4/m40971b3d22ab37242636088780cfb8eb5.jpg','img/product/size2/m20971b3d22ab37242636088780cfb8eb5.jpg'),(18,7,'img/product/size4/m49f5c81e544230b89c9115c98a9c69d55.jpg','img/product/size2/m29f5c81e544230b89c9115c98a9c69d55.jpg'),(19,7,'img/product/size4/30e3d193d2c28770e9bd5aadd4d4f473.jpg','img/product/size2/m230e3d193d2c28770e9bd5aadd4d4f473.jpg'),(20,7,'img/product/size4/m407dfe2072837d106371e75ad134df7ff.jpg','img/product/size2/m207dfe2072837d106371e75ad134df7ff.jpg'),(21,8,'img/product/size4/m470365f2ea91b638695218a6df13e3819.jpg','img/product/size2/m270365f2ea91b638695218a6df13e3819.jpg'),(22,8,'img/product/size4/m486b0e214cac64a12e1930cfb201e6333.jpg','img/product/size2/m286b0e214cac64a12e1930cfb201e6333.jpg'),(23,8,'img/product/size4/m40971b3d22ab37242636088780cfb8eb5.jpg','img/product/size2/m20971b3d22ab37242636088780cfb8eb5.jpg'),(24,8,'img/product/size4/m407dfe2072837d106371e75ad134df7ff.jpg','img/product/size2/m207dfe2072837d106371e75ad134df7ff.jpg'),(25,9,'img/product/size4/m45c17b5b73d618bbdd2abe231f1307838.jpg','img/product/size2/m25c17b5b73d618bbdd2abe231f1307838.jpg'),(26,9,'img/product/size4/m44eea54c83a3d8fd60292587019dbcabe.jpg','img/product/size2/m24eea54c83a3d8fd60292587019dbcabe.jpg'),(27,9,'img/product/size4/m45c4df861ce9e612314a1eb5ac7e6a51d.jpg','img/product/size2/m25c4df861ce9e612314a1eb5ac7e6a51d.jpg'),(28,9,'img/product/size4/m4326bd67f479df1078731770a42701203.jpg','img/product/size2/m2326bd67f479df1078731770a42701203.jpg'),(29,10,'img/product/size4/3adc11d2765807c38f4ddfc5e2ee31fc.jpg','img/product/size2/m23adc11d2765807c38f4ddfc5e2ee31fc.jpg'),(30,10,'img/product/size4/7e7d96dcf1052c4cb7b749703784fd42.jpg','img/product/size2/m27e7d96dcf1052c4cb7b749703784fd42.jpg'),(31,10,'img/product/size4/m402caec5f3f80f1fc8122b6aeed2b0a16.jpg','img/product/size2/m202caec5f3f80f1fc8122b6aeed2b0a16.jpg'),(32,10,'img/product/size4/m4c9823bc50368af0fe83eff14a5587c0a.jpg','img/product/size2/m2c9823bc50368af0fe83eff14a5587c0a.jpg'),(33,10,'img/product/size4/m4e58cd1b85e778b6f57a3745037704b35.jpg','img/product/size2/m2e58cd1b85e778b6f57a3745037704b35.jpg'),(34,11,'img/product/size4/m4d76a0028afabf41b371d596004709031.jpg','img/product/size2/m2d76a0028afabf41b371d596004709031.jpg'),(35,11,'img/product/size4/m4c9823bc50368af0fe83eff14a5587c0a.jpg','img/product/size2/m2c9823bc50368af0fe83eff14a5587c0a.jpg'),(36,11,'img/product/size4/m4e2bca5a2135d82d87406d4c997ab21a0.jpg','img/product/size2/m2e2bca5a2135d82d87406d4c997ab21a0.jpg'),(37,11,'img/product/size4/m4a1c3be962560ab5e4bc39146f4749973.jpg','img/product/size2/m2a1c3be962560ab5e4bc39146f4749973.jpg'),(38,12,'img/product/size4/m42bdf1849b307712c4a0843b89ed83b5f.jpg','img/product/size2/m22bdf1849b307712c4a0843b89ed83b5f.jpg'),(39,12,'img/product/size4/m4f535b6065c0085e6e69d1df7337bc60c.jpg','img/product/size2/m2f535b6065c0085e6e69d1df7337bc60c.jpg'),(40,12,'img/product/size4/m4d4e24bd288c8b9ecbd81d20dbf9fe5bc.jpg','img/product/size2/m2d4e24bd288c8b9ecbd81d20dbf9fe5bc.jpg'),(41,12,'img/product/size4/m4285ab2afad8d9c78aa6f37895597a795.jpg','img/product/size2/m2285ab2afad8d9c78aa6f37895597a795.jpg'),(42,13,'img/product/size4/m4f535b6065c0085e6e69d1df7337bc60c.jpg','img/product/size2/m2f535b6065c0085e6e69d1df7337bc60c.jpg'),(43,13,'img/product/size4/m4d4e24bd288c8b9ecbd81d20dbf9fe5bc.jpg','img/product/size2/m2d4e24bd288c8b9ecbd81d20dbf9fe5bc.jpg'),(44,13,'img/product/size4/m40055cde93431912b5a767eb0b4d2f769.jpg','img/product/size2/m20055cde93431912b5a767eb0b4d2f769.jpg'),(45,13,'img/product/size4/m4958e1b9c2b8b43da1c3440b71155dc7d.jpg','img/product/size2/m2958e1b9c2b8b43da1c3440b71155dc7d.jpg'),(46,14,'img/product/size4/d62aa9f19c84f99a8b700f12824b195b.jpg','img/product/size2/m2d62aa9f19c84f99a8b700f12824b195b.jpg'),(47,14,'img/product/size4/m4d25620958f486c463412f437a5d5fd0a.jpg','img/product/size2/m2d25620958f486c463412f437a5d5fd0a.jpg'),(48,14,'img/product/size4/m4b452e26d476d3c6322cc5d10af088e21.jpg','img/product/size2/m2b452e26d476d3c6322cc5d10af088e21.jpg'),(49,14,'img/product/size4/m46abd52cb8e0a8dfae8f19ca0f1f5c193.jpg','img/product/size2/m26abd52cb8e0a8dfae8f19ca0f1f5c193.jpg'),(50,15,'img/product/size4/m45b7321df30ac79f995200ec4a2fa2202.jpg','img/product/size2/m25b7321df30ac79f995200ec4a2fa2202.jpg'),(51,15,'img/product/size4/m461767acd0d3c1379f51c3aeec34271e5.jpg','img/product/size2/m261767acd0d3c1379f51c3aeec34271e5.jpg');