文章目录
DROP TABLE IF EXISTS `t_item`;
CREATE TABLE `t_item` (
`id` varchar(200) NOT NULL COMMENT '商品id',
`category_id` bigint(20) DEFAULT NULL COMMENT '分类id',
`item_type` varchar(100) DEFAULT NULL COMMENT '商品系列',
`title` varchar(100) DEFAULT NULL COMMENT '商品标题',
`sell_point` varchar(150) DEFAULT NULL COMMENT '商品卖点',
`price` bigint(20) DEFAULT NULL COMMENT '商品单价',
`num` int(10) DEFAULT NULL COMMENT '库存数量',
`barcode` varchar(30) DEFAULT NULL COMMENT '条形码',
`image` varchar(500) DEFAULT NULL COMMENT '图片路径',
`status` int(1) DEFAULT '1' COMMENT '商品状态 1:上架 2:下架 3:删除',
`priority` int(10) DEFAULT NULL COMMENT '显示优先级',
`created_time` datetime DEFAULT NULL COMMENT '创建时间',
`modified_time` datetime DEFAULT NULL COMMENT '最后修改时间',
`created_user` varchar(50) DEFAULT NULL COMMENT '创建人',
`modified_user` varchar(50) DEFAULT NULL COMMENT '最后修改人',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_item` VALUES
('10000000',238,'牛皮纸记事本','广博(GuangBo)10本装40张A5牛皮纸记事本子日记本办公软抄本GBR0731','经典回顾!超值特惠!',23,99999,NULL,'/images/portal/00GuangBo1040A5GBR0731/collect.png',1,53,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000001',238,'牛皮纸记事本','广博(GuangBo)10本装40张A5牛皮纸记事本子日记本办公软抄本GBR0731','经典回顾!超值特惠!',23,99999,NULL,'/images/portal/00GuangBo1040A5GBR0731/collect.png',1,62,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000002',238,'皮面日程本','广博(GuangBo)皮面日程本子 计划记事本效率手册米色FB60322','经典回顾!超值特惠!',46,99999,NULL,'/images/portal/001GuangBo)FB60322/collect.png',1,49,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000021',238,'皮面日程本','广博(GuangBo)皮面日程本子 计划记事本效率手册蓝色FB60321','经典回顾!超值特惠!',22,99999,NULL,'/images/portal/001GuangBo)FB60322/collect.png',1,73,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000003',238,'记事本日记本笔记本','广博(GuangBo)16K115页线圈记事本子日记本文具笔记本图案随机','经典回顾!超值特惠!',13,99999,NULL,'/images/portal/01GuangBo16K115FB60506/collect.png',1,58,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000004',241,'计算器','得力(deli)1548A商务办公桌面计算器 太阳能双电源','经典回顾!超值特惠!',58,99999,NULL,'/images/portal/002calculator1548A/collect.png',1,42,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000005',241,'圆珠笔','施耐德(Schneider) K15 经典款圆珠笔 (5支混色装)','经典回顾!超值特惠!',29,99999,NULL,'/images/portal/03SchneiderK15/collect.png',1,36,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000006',236,'票据网格拉链袋','三木(SUNWOOD) C4523 票据网格拉链袋/文件袋 12个装 颜色随机','经典回顾!超值特惠!',28,99999,NULL,'/images/portal/04_SUNWOODC452312/collect.png',1,53,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000007',163,'燃 7000经典版','戴尔Dell 燃700金色','下单赠12000毫安移动电源',32999,99999,NULL,'/images/portal/11DELLran7000gold/collect.png',1,59,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000008',163,'燃 7000经典版','戴尔Dell 燃700R1605银色','仅上海,广州,沈阳仓有货!预购从速!',4549,99999,NULL,'/images/portal/11DELLran7000R1605Ssilvery/collect.png',1,32,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000009',163,'燃 7000学习版','戴尔Dell 燃700金色','下单赠12000毫安移动电源',39929,99999,NULL,'/images/portal/11DELLran7000gold/collect.png',1,84,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000010',163,'燃 7000学习版','戴尔Dell 燃700R1605银色','仅上海,广州,沈阳仓有货!预购从速!',5559,99999,NULL,'/images/portal/11DELLran7000R1605Ssilvery/collect.png',1,21,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000011',163,'燃 7000高配版','戴尔Dell 燃700金色','下单赠12000毫安移动电源',3994,99999,NULL,'/images/portal/11DELLran7000gold/collect.png',1,56,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000012',163,'燃 7000高配版','戴尔Dell 燃700R1605银色','仅上海,广州,沈阳仓有货!预购从速!',6559,99999,NULL,'/images/portal/11DELLran7000R1605Ssilvery/collect.png',1,16,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000013',238,'A5优品商务笔记本','齐心(COMIX)C5902 A5优品商务笔记本子记事本日记本122张','下单即送10400毫安移动电源!再赠手机魔法盒!',41,99999,NULL,'/images/portal/02COMIXC5902A5122blue/collect.png',1,10,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000014',163,'XPS13-9360','戴尔(DELL)XPS13-9360-R1609 13.3','仅上海,广州,沈阳仓有货!预购从速!',4600,99999,NULL,'/images/portal/12(DELL)XPS13gold/collect.png',1,1,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000015',163,'XPS13-9360','戴尔(DELL)XPS13-9360-R1609 13.3','仅上海,广州,沈阳仓有货!预购从速!',4601,99999,NULL,'/images/portal/12DELLXPS13-silvery/collect.png',1,73,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000016',163,'XPS13-9360','戴尔(DELL)XPS13-9360-R1609 13.3','仅上海,广州,沈阳仓有货!预购从速!',4602,99999,NULL,'/images/portal/12(DELL)XPS13gold/collect.png',1,64,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000017',163,'XPS13-9360','戴尔(DELL)XPS13-9360-R1609 13.3','仅上海,广州,沈阳仓有货!预购从速!',4604,99999,NULL,'/images/portal/12DELLXPS13-silvery/collect.png',1,100,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000018',163,'XPS13-9360','戴尔(DELL)XPS13-9360-R1609 13.3','仅上海,广州,沈阳仓有货!预购从速!',4605,99999,NULL,'/images/portal/12(DELL)XPS13gold/collect.png',1,7,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000019',163,'XPS13-9360','戴尔(DELL)XPS13-9360-R1609 13.3','仅上海,广州,沈阳仓有货!预购从速!',4899,99999,NULL,'/images/portal/12DELLXPS13-silvery/collect.png',1,34,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000020',163,'IdeaPad310低配版','联想(Lenovo)IdeaPad310低配版','清仓!仅北京,武汉仓有货!',5119,99999,NULL,'/images/portal/13LenovoIdeaPad310_black/collect.png',1,50,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000021',163,'IdeaPad310低配版','联想(Lenovo)IdeaPad310低配版','清仓!仅北京,武汉仓有货!',5129,99999,NULL,'/images/portal/13LenovoIdeaPad310_silvery/collect.png',1,48,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000022',163,'IdeaPad310经典版','联想(Lenovo)IdeaPad310经典版','清仓!仅北京,武汉仓有货!',5119,99999,NULL,'/images/portal/13LenovoIdeaPad310_black/collect.png',1,90,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000023',163,'IdeaPad310经典版','联想(Lenovo)IdeaPad310经典版','清仓!仅北京,武汉仓有货!',5129,99999,NULL,'/images/portal/13LenovoIdeaPad310_silvery/collect.png',1,6,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000024',163,'IdeaPad310高配版','联想(Lenovo)IdeaPad310高配版','清仓!仅北京,武汉仓有货!',5119,99999,NULL,'/images/portal/13LenovoIdeaPad310_black/collect.png',1,60,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000025',163,'IdeaPad310高配版','联想(Lenovo)IdeaPad310高配版','清仓!仅北京,武汉仓有货!',5129,99999,NULL,'/images/portal/13LenovoIdeaPad310_silvery/collect.png',1,80,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000026',163,'YOGA710','联想(Lenovo)YOGA710 14英寸触控笔记本(i7-7500U 8G 256GSSD 2G独显 全高清IPS 360°翻转 正版office)金','【0元献礼】好评过万,销量传奇!经典蓝光电视,独有自然光技术专利,过大年带最好的回家!【0元白条试用,1001个拜年计划】',59999,99999,NULL,'/images/portal/14LenovoYOGA710 _gold/collect.png',1,19,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000027',163,'YOGA710','联想(Lenovo)YOGA710 14英寸触控笔记本(i7-7500U 8G 256GSSD 2G独显 全高清IPS 360°翻转 正版office)银','【0元献礼】好评过万,销量传奇!经典蓝光电视,独有自然光技术专利,过大年带最好的回家!【0元白条试用,1001个拜年计划】',59999,99999,NULL,'/images/portal/14LenovoYOGA710 _silvery/collect.png',1,55,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000028',163,'310低配版','联想(Lenovo)小新310低配版','清仓!仅北京,武汉仓有货!',4939,99999,NULL,'/images/portal/15Lenovo_xiaoxin_310_black/collect.png',1,19,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000029',163,'310低配版','联想(Lenovo)小新310低配版','清仓!仅北京,武汉仓有货!',4839,99999,NULL,'/images/portal/15Lenovo_xiaoxin_310_silvery/collect.png',1,27,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000030',163,'310经典版','联想(Lenovo)小新310经典版','清仓!仅北京,武汉仓有货!',4739,99999,NULL,'/images/portal/15Lenovo_xiaoxin_310_black/collect.png',1,78,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000031',163,'310经典版','联想(Lenovo)小新310经典版','清仓!仅北京,武汉仓有货!',4639,99999,NULL,'/images/portal/15Lenovo_xiaoxin_310_silvery/collect.png',1,9,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000032',163,'310高配版','联想(Lenovo)小新310高配版','清仓!仅北京,武汉仓有货!',4539,99999,NULL,'/images/portal/15Lenovo_xiaoxin_310_black/collect.png',1,9,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000033',163,'310高配版','联想(Lenovo)小新310高配版','清仓!仅北京,武汉仓有货!',4439,99999,NULL,'/images/portal/15Lenovo_xiaoxin_310_silvery/collect.png',1,18,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000034',163,'YOGA900','联想(Lenovo)YOGA900绿色','青春的活力 清新漂亮高端大气上档次',5200,99999,NULL,'/images/portal/16LenovoYOGA900green/collect.png',1,63,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000035',163,'YOGA900','联想(Lenovo)YOGA900粉色','青春的活力 清新漂亮高端大气上档次',5200,99999,NULL,'/images/portal/16LenovoYOGA900pink/collect.png',1,62,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000036',163,'YOGA900','联想(Lenovo)YOGA900红色','青春的活力 清新漂亮高端大气上档次',5200,99999,NULL,'/images/portal/16LenovoYOGA900red/collect.png',1,21,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000037',163,'小新13旗舰版','联想(Lenovo)小新Air13 Pro 13.3英寸14.8mm超轻薄笔记本电脑','青春的活力 青年专属',6439,99999,NULL,'/images/portal/17Lenovo)xiaoxinAir13Pro_gold/collect.png',1,16,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000038',163,'小新13旗舰版','联想(Lenovo)小新Air13 Pro 13.3英寸14.8mm超轻薄笔记本电脑','青春的活力 青年专属',6439,99999,NULL,'/images/portal/17Lenovo)xiaoxinAir13Pro_silvery/collect.png',1,17,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000039',163,'XPS15','戴尔(DELL) XPS15 银色','限时特价!好评过万条优秀产品!',3333,99999,NULL,'/images/portal/18(DELL)XPS15_silvery/collect.png',1,37,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000391',163,'XPS15','戴尔(DELL) XPS15 金色','限时特价!好评过万条优秀产品!',3333,99999,NULL,'/images/portal/18(DELL)XPS15_silvery/collect.png',1,81,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000040',163,'DELL 15MF Pro','戴尔(DELL)魔方15MF Pro-R2505TSS灵越','15.6英寸二合一翻转笔记本电脑 (i5-7200U 8GB 1TB IPS Win10)触控银',4443,99999,NULL,'/images/portal/19DELL15MF Pro/collect.png',1,35,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000401',163,'DELL 15MF Pro','戴尔(DELL)魔方15MF Pro-R2505TSS灵越','15.6英寸二合一翻转笔记本电脑 (i5-7200U 8GB 1TB IPS Win10)触控白',4443,99999,NULL,'/images/portal/19DELL15MF Pro/collect.png',1,86,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000402',163,'DELL 15MF Pro','戴尔(DELL)魔方15MF Pro-R2505TSS灵越','15.6英寸二合一翻转笔记本电脑 (i7-7200U 8GB 512GB IPS Win10)触控银',6443,99999,NULL,'/images/portal/19DELL15MF Pro/collect.png',1,84,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000403',163,'DELL 15MF Pro','戴尔(DELL)魔方15MF Pro-R2505TSS灵越','15.6英寸二合一翻转笔记本电脑 (i7-7200U 8GB 512GB IPS Win10)触控白',6443,99999,NULL,'/images/portal/19DELL15MF Pro/collect.png',1,63,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000041',163,'DELL XPS15-9550','戴尔(DELL) XPS15升级版 ','15.6英寸二合一翻转笔记本电脑 (i5-7200U 8GB 1TGB IPS Win10)触控',8443,99999,NULL,'/images/portal/20DellXPS15-9550/collect.png',1,61,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000411',163,'DELL XPS15-9550','戴尔(DELL) XPS15升级版 ','15.6英寸二合一翻转笔记本电脑 (i5-7200U 8GB 256GB IPS Win10)触控',8443,99999,NULL,'/images/portal/20DellXPS15-9550/collect.png',1,60,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000412',163,'DELL XPS15-9550','戴尔(DELL) XPS15升级版 ','15.6英寸二合一翻转笔记本电脑 (i7-7200U 8GB 1TB IPS Win10)触控',8443,99999,NULL,'/images/portal/20DellXPS15-9550/collect.png',1,13,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000413',163,'DELL XPS15-9550','戴尔(DELL) XPS15升级版 ','15.6英寸二合一翻转笔记本电脑 (i7-7200U 8GB 256GB IPS Win10)触控',8443,99999,NULL,'/images/portal/20DellXPS15-9550/collect.png',1,83,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000042',163,'ThinkPad New s1','联想ThinkPad New S2(01CD) i5 6代 红色','经典回顾!超值特惠!',4399,99999,NULL,'/images/portal/21ThinkPad_New_S1/collect.png',1,99,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000421',163,'ThinkPad New s1','联想ThinkPad New S2(01CD) i7 6700 红','经典回顾!超值特惠!',6399,99999,NULL,'/images/portal/21ThinkPad_New_S1/collect.png',1,74,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000422',163,'ThinkPad New s1','联想ThinkPad New S2(01CD) i5 6代 黄','经典回顾!超值特惠!',4399,99999,NULL,'/images/portal/21ThinkPad_New_S1/collect.png',1,23,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000424',163,'ThinkPad New s1','联想ThinkPad New S2(01CD) i5 6代 蓝','经典回顾!超值特惠!',4399,99999,NULL,'/images/portal/21ThinkPad_New_S1/collect.png',1,87,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('100000425',163,'ThinkPad New s1','联想ThinkPad New S2(01CD) i7 6700 蓝','经典回顾!超值特惠!',6399,99999,NULL,'/images/portal/21ThinkPad_New_S1/collect.png',1,59,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000043',917,'书包 bag','乐尚书包 电脑包 bag黑色','给你满载而归的喜悦!',89,99999,NULL,'/images/portal/22_LEXON_LNE6025B06T/collect.png',1,12,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin'),
('10000044',917,'书包 bag','乐尚书包 电脑包 bag粉色','给你满载而归的喜悦!',89,99999,NULL,'/images/portal/22_LEXON_LNE6025B06T/collect.png',1,62,'2017-10-25 15:08:55','2017-10-25 15:08:55','admin','admin');
DROP TABLE IF EXISTS `t_item_category`;
CREATE TABLE `t_item_category` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`parent_id` bigint(20) DEFAULT NULL COMMENT '父分类id',
`name` varchar(150) DEFAULT NULL COMMENT '名称',
`status` int(1) DEFAULT '1' COMMENT '状态 1:正常 2:删除',
`sort_order` int(4) DEFAULT NULL COMMENT '排序号',
`is_parent` tinyint(1) DEFAULT NULL COMMENT '是否是父分类 1:是 0:否',
`created_time` datetime DEFAULT NULL COMMENT '创建时间',
`modified_time` datetime DEFAULT NULL COMMENT '最后修改时间',
`created_user` varchar(50) DEFAULT NULL COMMENT '创建人',
`modified_user` varchar(50) DEFAULT NULL COMMENT '最后修改人',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1183 DEFAULT CHARSET=utf8;
INSERT INTO `t_item_category` VALUES
(163,162,'笔记本',1,1,0,'2017-10-25 15:00:55','2017-10-25 15:00:55','admin','admin'),
(236,229,'文件管理',1,7,0,'2017-10-25 15:00:55','2017-10-25 15:00:55','admin','admin'),
(238,229,'本册/便签',1,9,0,'2017-10-25 15:00:55','2017-10-25 15:00:55','admin','admin'),
(241,229,'笔类',1,12,0,'2017-10-25 15:00:55','2017-10-25 15:00:55','admin','admin'),
(917,913,'双肩包',1,4,0,'2017-10-25 15:00:55','2017-10-25 15:00:55','admin','admin');
create table EMP(
EMPNO int(4) primary key,
ENAME varchar(10) not null,
JOB varchar(9),
MGR int(4),
HIREdate date,
SAL double(7,2),
COMM double(7,2),
DEPTNO int(4)
);
create table Dept(
DEPTNO int(4) primary key,
DNAME varchar(14) not null unique,
LOC varchar(13)
);
insert into Dept VALUES (10,'ACCOUNTING','NEW YORK');
insert into Dept VALUES (20,'RESEARCH','DALLAS');
insert into Dept VALUES (30,'SALES','CHICAGO');
insert into Dept VALUES (40,'OPERATIONS','BOSTON');
SELECT * FROM DEPT;
insert into EMP VALUES
(7369,'SMITH','CLERK',7902,str_to_date('17-12-1980','%d-%m-%Y'),800,null,20);
insert into EMP VALUES
(7499,'ALLEN','SALESMAN',7698,str_to_date('20-2-1981','%d-%m-%Y'),1600,300,30);
insert into EMP VALUES
(7521,'WARD','SALESMAN',7698,str_to_date('22-2-1981','%d-%m-%Y'),1250,500,30);
insert into EMP VALUES
(7566,'JONES','MANAGER',7839,str_to_date('2-4-1981','%d-%m-%Y'),2975,NULL,20);
insert into EMP VALUES
(7654,'MARTIN','SALESMAN',7698,str_to_date('28-9-1981','%d-%m-%Y'),1250,1400,30);
insert into EMP VALUES
(7698,'BLAKE','MANAGER',7839,str_to_date('1-5-1981','%d-%m-%Y'),2850,NULL,30);
insert into EMP VALUES
(7782,'CLARK','MANAGER',7839,str_to_date('9-6-1981','%d-%m-%Y'),2450,NULL,10);
insert into EMP VALUES
(7839,'KING','PRESIDENT',NULL,str_to_date('17-11-1981','%d-%m-%Y'),5000,NULL,10);
insert into EMP VALUES
(7844,'TURNER','SALESMAN',7698,str_to_date('8-9-1981','%d-%m-%Y'),1500,0,30);
insert into EMP VALUES
(7900,'JAMES','CLERK',7698,str_to_date('3-12-1981','%d-%m-%Y'),950,NULL,30);
insert into EMP VALUES
(7902,'FORD','ANALYST',7566,str_to_date('3-12-1981','%d-%m-%Y'),3000,NULL,20);
insert into EMP VALUES
(7934,'MILLER','CLERK',7782,str_to_date('23-1-1982','%d-%m-%Y'),1300,NULL,10);
commit;
分组查询 通常和聚合函数结合使用 通常查询每个部门(性别/分类) 就以部门(性别/分类)为分组条件 group by语句的位置 可以对多个字段进行分组 格式:
select 字段 from 表名 where 条件 group by 分组字段 having 聚合函数条件 order by 字段 limit n,m 分页
select deptno,count(*) c,sum(sal) s from emp group by deptno order by c asc,s desc;
select deptno,avg(sal) a,min(sal),max(sal) from emp where sal between 1000 and 3000 group by deptno order by a;
select count(*) c,sum(sal),avg(sal) a,min(sal) from emp where mgr is not null group by job order by c desc,a asc;
select deptno,mgr ,count(*)from emp where mgr is not null group by deptno,mgr;
select job,avg(sal) from emp group by job;
%Y-%m-%d
),因此这里需要用到日期截取的函数extract()
,具体使用参看SQL操作三
select extract(year from hiredate) year,count(*) from emp group by y;
select deptno,avg(sal) a from emp where a>2000 group by deptno;
这句话是错误的,因为avg(sal)
是聚合函数,不能和where结合使用group by
后面
select from where group by having order by limit
select deptno,avg(sal) a from emp group by deptno having a>2000;
select category_id, sum(num) sum from t_item group by category_id having sum>1000;
select category_id, avg(price) avg from t_item group by category_id having avg<100;
where
条件进行过滤> select category_id, avg(price) avg from t_item where category_id in(238,917) group by category_id ;
select category_id, avg(price) avg from t_item group by category_id having category_id in(238,917);
select avg(sal) avg,deptno,count(*) from emp group by deptno having avg>2000 order by avg;
select deptno,sum(sal) sum,avg(sal) avg,min(sal) min from emp where ename not like 'k%' group by deptno having min>1000 order by avg;
select max(sal) max,job,avg(sal) avg,count(*) c from emp where deptno in(10,30) group by job having avg<5000 order by c asc,max desc;
select deptno,count(*) c,sum(sal) sum,max(sal) max,min(sal) min from emp group by deptno having max !=5000 order by c asc,max desc;
select deptno,sum(sal) sum,avg(sal) avg from emp where sal between 1000 and 3000 group by deptno having avg >=2000 order by avg;
select job,count(*) c,sum(sal) sum,max(sal) max from emp where ename not like 's%'and sal !=3000 group by job order by c asc,sum desc;
select job,count(*) c,avg(sal) avg,min(sal) min from emp group by job having avg !=3000 order by c desc,avg asc;
子查询 : 嵌套到sql语句里面的查询sql语句称为”子查询” 子查询中返回的字段一定要和查询的判断条件字段类型一致,否则没有意义,比如 : 这里的最高员工工资,那么子查询返回的一定是一个字段并且这个字段是最高的工资。当然子查询中也是可以返回多个值,那么此时需要使用in关键字判断 如果子查询中返回的是一个值,比如最大工资,那么我们可以使用
=
>
<
!=
如果子查询中返回的是一组值,那么我们就不能使用 = 或者 != 我们可以使用in
关键字
select * from emp where sal=(select max(sal) from emp);
select * from emp where sal > (select avg(sal) from emp);
select * from emp where hiredate=(select max(hiredate) from emp);
in
select * from t_item_category where id in(select category_id from t_item where category_id is not null);
select * from emp where sal>(select max(sal) from emp where deptno=20);
select deptno,dname from dept where deptno = (select distinct deptno from emp where ename='king');
select empno,ename,job,deptno from emp where deptno=(select deptno from dept where dname="sales");
select * from emp where deptno in (select deptno from dept where loc='dallas');
select * from emp where job=(select job from emp where ename='jones');
select * from emp where job=(select job from emp where ename='jones') and ename!='jones';
(select * from t) new
这个可以当做一个新表进行查询,也是一种嵌套查询select * from dept where deptno in (select deptno from (select max(a),deptno from (select avg(sal) a,deptno from emp group by deptno)new)n); ``` - 简单写法 - 查询每个部门的deptno,然后对平均工资进行降序排序,之后取其中的第一条数据,那么就是平均工资最大的。 ```sql select * from dept where deptno=(select deptno from emp group by deptno order by avg(sal) desc limit 0,1); select * from (select * from t_item where title like '%广博%' limit 0,10) newtable;
select table t_item_new as (select title,price from t_item from t_item where price>1000)
同时查询多张表信息中的字段 同时查询多张表的字段的时候,一定要指定关联关系,否则就会出现笛卡尔积的错误,比如外键关联,emp和dept表中的deptno是对应字段的关系(相当于外键)
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select ename from emp,dept where dept.loc='new york' and emp.deptno=dept.deptno;
select ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno and sal>3000;
笛卡尔积通常是一种错误的查询结果 笛卡尔积是在不谢关联关系的情况下,查询出来的两张表的乘积
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select * from A,B where A.x=B.x and age>18;
select * from A [inner] join B on A.x=B.x where age>18;
select * from emp e join dept d on e.deptno=d.deptno where d.loc='new york';
select ename ,sal,dname,loc from emp e join dept d on e.deptno=d.deptno where sal>3000;
等值连接和内连接只能查询有关联关系的数据,如果其中的数据没有关联关系,那么没有关联关系的数据查询不出来
两张表中其中有数据没有关联关系,那么使用等值连接和内连接就查询不出来,因此需要使用左外连接 查询结构以左边的表为主,内容全部显示,左边的只是显示有关系的
select * from emp e left join dept d on e.deptno=d.deptno;
两张表中其中有数据没有关联关系,那么使用等值连接和内连接就查询不出来,因此需要使用左外连接 查询结构以右边的表为主,内容全部显示,左边的只是显示有关系的
select * from emp e right join dept d on e.deptno=d.deptno;
select c.name , t.* from t_item t,t_item_category c where t.category_id=c.id;
等值连接select t.* ,c.name from t_item t join t_item_category c on t.category_id = c.id;
内连接select c.name,t.* from t_item_category c left join t_item t on t.category_id=c.id;
左外连接select c.name,t.* from t_item t right join t_item_category c on t.category_id=c.id;
右外连接select t.title,c.name from t_item t left join t_item_category c on c.id=t.category_id;
左外连接select t.title,c.name from t_item_category c right join t_item t on c.id=t.category_id;
右外连接select deptno count(*) c from emp group by deptno order by c;
select deptno,mgr,count(*) from emp group by deptno,mgr;
select job,avg(sal) avg from emp group by job;
select extract(year from hiredate) year, count(*) from emp group by year;
select deptno, count(*) c from emp group by deptno having c<=3;
select * from emp having sal=min(sal);
(不推荐)select * from emp having sal=(select min(sal) from emp);
select e.* from emp e join (select count(*) c ,mgr from emp group by mgr having c=1)newtable on newtable.mgr=e.empno;
select deptno from emp group by deptno order by avg(sal) desc limit 0,1
select * from emp group by mgr order by count(*) desc limit 0,1;
select * from emp where sal=(select min(sal) from emp);
select * from emp where hiredate=(select max(hiredate) from emp);
select * from emp where sal>(select avg(sal) from emp);
select e.*,d.dname from emp e join dept d on e.deptno=d.deptno;
select e.*,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno;
select e.* from emp e join dept d on e.deptno=d.deptno where d.loc='DALLAS';
select loc ,count(*) from (select e.*,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno) newtable group by newtable.loc;
select d.loc,count(*) from emp e join dept d on e.deptno=d.deptno group by loc;
select e.*,n.ename '主管名字' from emp e join (select mgr,ename from emp) n on e.empno=n.mgr;
select e.*,n.ename mgrname from emp e join emp n on e.empno=n.mgr;
select e.*,n.ename mgrname,dname from emp e join emp n on e.empno=n.mgr join dept d on e.deptno=d.deptno;
select new.*,d.dname from (select e.*,n.ename mgrname from emp e join emp n on e.empno=n.mgr) new join dept d on new.deptno=d.deptno;
如果涉及到两张表,甚至多张表,想要查询某张表的所有信息,此时就需要使用左/右外连接,因为如果某条数据没有关联关系,那么使用等值连接或者内连接将会缺失没有关联关系的数据。 如果涉及到多张表的时候,使用内连接可以连接多张表,直接在后面添加join即可,比如
select e.*,n.ename mgrname,dname from emp e join emp n on e.empno=n.mgr join dept d on e.deptno=d.deptno;
分组(group by)是对前面查询到的结果进行分组,因此在where条件语句后,也是在内连接之后,因为必须查询到完整的一张表才能进行分组 推荐使用内连接,不使用等值连接 使用内连接的时候,where条件语句一定要放在 on的后面,即使是多个内连接(多个join),也必须放在最后一个join的on的后面,不影响结果