文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

SQL操作系列

共 6 篇文章
1
SQL操作一
2
SQL操作二
3
SQL操作三
4
SQL操作四
5
SQL操作五
6
SQL操作六
清单首页SQL操作系列文章详情
清单「SQL操作系列」 04/06

SQL操作四

爱撒谎的男孩·蚂蚁金服P8

文章目录

  1. 1. Day 04
    1. 1.1. 创建数据库和表
      1. 1.1.1. 商城建表语句
      2. 1.1.2. 员工建表语句
    2. 1.2. group by
    3. 1.3. having子句
    4. 1.4. 子查询
      1. 1.4.1. Mysql子查询
      2. 1.4.2. 总结
    5. 1.5. 关联查询
      1. 1.5.1. 笛卡尔积
      2. 1.5.2. 等值连接/内连接
        1. 1.5.2.1. 等值连接
      3. 1.5.3. 内连接
      4. 1.5.4. 总结
    6. 1.6. 左外连接
    7. 1.7. 右外连接
    8. 1.8. 案例
    9. 1.9. 总结

Day 04

创建数据库和表

商城建表语句

代码语言：javascript
复制
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');

员工建表语句

代码语言：javascript
复制
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

分组查询 通常和聚合函数结合使用 通常查询每个部门(性别/分类) 就以部门(性别/分类)为分组条件 group by语句的位置 可以对多个字段进行分组 格式:select 字段 from 表名 where 条件 group by 分组字段 having 聚合函数条件 order by 字段 limit n,m 分页

  • 查询emp表中每个部门的编号(deptno)，人数，工资总和 最后根据人数进行升序排序，如果人数一致，根据工资总和降序排列
    • select deptno,count(*) c,sum(sal) s from emp group by deptno order by c asc,s desc;
  • 查询工资平均在1000-3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列
    • 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;
  • 每年的入职人数
    • 其中hiredate的格式是2015-01-02(%Y-%m-%d),因此这里需要用到日期截取的函数extract()，具体使用参看SQL操作三
    • select extract(year from hiredate) year,count(*) from emp group by y;

having子句

  • 聚合函数不可以对where结合使用
    • select deptno,avg(sal) a from emp where a>2000 group by deptno; 这句话是错误的，因为avg(sal)是聚合函数，不能和where结合使用
  • having中也是可以使用普通字段的过滤，不一定是聚合函数，但是聚合函数的过滤只能使用having，但是建议写在where条件语句中
  • 对聚合函数的结构进行条件过滤要使用having
  • having语句要写在group by 后面
    • select from where group by having order by limit
  • 查询每个部门中平均工资，只显示平均工资大于2000的
    • select deptno,avg(sal) a from emp group by deptno having a>2000;
  • 查询所有分类商品所对应的库存总量中，高于1000的总量
    • 这个就是查询每一种商品的库存(group by ) ，库存高于1000(having)
    • select category_id, sum(num) sum from t_item group by category_id having sum>1000;
  • 查询所有分类商品所对应的平均单价中，低于1000的均价
    • select category_id, avg(price) avg from t_item group by category_id having avg<100;
  • 查询编号238和编号917分类商品的平均单价
    • 这里没有查询编号就是category_id的值，没有涉及到聚合函数，因此可以使用where条件进行过滤
    • > select category_id, avg(price) avg from t_item where category_id in(238,917) group by category_id ;
    • 虽然没有涉及到聚合函数，但是我们也是可以使用having子句进行过滤（不建议）
    • select category_id, avg(price) avg from t_item group by category_id having category_id in(238,917);
  • 查询emp中，每个部门的平均工资高于2000的部门的编号，部门的人数，平均人数，平均工资，最后根据平均工资进行升序排列
    • select avg(sal) avg,deptno,count(*) from emp group by deptno having avg>2000 order by avg;
  • 查询emp表中名字中不是以k开头的信息，每个部门的最低工资高于1000的部门的编号，工资总和，平均工资以及最低工资，最后根据平均工资进行升序排列
    • 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;
  • 查询emp表中部门编号是10,30号部门的员工，每个职业的最高工资低于5000的职业的名称，人数，平均工资，最高工资，最后根据人数进行升序排列，如果人数一致，根据最高工资进行降序排列
    • 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;
  • 查询emp表中，每个部门的编号，人数，工资总和，最高工资以及最低工资，过滤掉最高工资是5000的部门，根据部门的人数进行升序排列，如果人数一致，则根据最高工资进行降序排列。
    • 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;
  • 查询emp表中工资在1000~3000之间的员工信息，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资进行升序排列
    • 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;
  • 查询emp表中名字不是以‘S’开头，每个职位的名字，人数，工资总和，最高工资，过滤掉工资是3000的职位，根据人数进行升序排列，如果人数一致，根据工资总和进行降序排列。
    • 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;
  • 查询emp表的信息，每个职位的名称，人数，平均工资，最低工资，过滤掉平均工资是3000的职位信息，根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
    • select job,count(*) c,avg(sal) avg,min(sal) min from emp group by job having avg !=3000 order by c desc,avg asc;

子查询

Mysql子查询

子查询 ： 嵌套到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);
  • 查询出有商品的分类信息
    • 子查询中返回的是t_item中不重复的category_id的值，这个就是在t_item_category中的id，因此我们只需要将t_item_category中的id值在t_item中的信息查询出来即可、
    • 由于子查询中可能返回的不是一个值，而是一组值，因此使用in
    • select * from t_item_category where id in(select category_id from t_item where category_id is not null);
  • 查询工资高于20号部门最高工资的员工的所有信息
    • select * from emp where sal>(select max(sal) from emp where deptno=20);
  • 查询emp表中姓名是‘KING’所属的部门的编号，名称
    • emp表中存放的是员工信息，dept表中存放的是部门信息，emp表中的deptno对应的dept表中的deptno(相当于外键)
    • 子查询是返回的emp表中ename为king的部门编号
    • select deptno,dname from dept where deptno = (select distinct deptno from emp where ename='king');
  • 查询部门名称是SALES的部门下所有员工的编号，姓名，职位，以及所属部门的编号
    • emp和dept表是以deptno关联的
    • select empno,ename,job,deptno from emp where deptno=(select deptno from dept where dname="sales");
  • 查询部门地址是DALLAS的部门下所有员工的所有信息
    • 仍然是emp和dept的查询
    • select * from emp where deptno in (select deptno from dept where loc='dallas');
  • 查询跟JONES同样工作的员工的所有信息（包含JONES）
    • select * from emp where job=(select job from emp where ename='jones');
  • 查询跟JONES同样工作的员工的所有信息（不包含JONES）
    • where句中可以包含子查询，也可以包含其他的条件，使用and或者or
    • 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 t_item_category where id =(select category_id from t_item group by category_id order by sum(num) desc limit 0,1);

总结

  • 子查询可以写在where后面作为查询条件
  • 可以写在from后面作为一张新表，作为新表时必须起别名
    • select * from (select * from t_item where title like '%广博%' limit 0,10) newtable;
    • 上面的select子句中返回的字段就是新表newtable的字段
  • 可以把子查询写在创建表的时候、
    • select table t_item_new as (select title,price from t_item from t_item where price>1000)
  • 子查询可以嵌套n层

关联查询

同时查询多张表信息中的字段 同时查询多张表的字段的时候，一定要指定关联关系，否则就会出现笛卡尔积的错误，比如外键关联，emp和dept表中的deptno是对应字段的关系（相当于外键）

  • 查看每个员工的名字以及所在部门的名字
    • select ename,dname from emp,dept where emp.deptno=dept.deptno;
  • 查询在new york 工作的员工
    • select ename from emp,dept where dept.loc='new york' and emp.deptno=dept.deptno;
  • 查看工资高于3000的员工，名字，工资，部门名，所在地
    • 这里的关联关系依然是两张表中都有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;
    • 上面的sql语句如果没写where中的子句，那么就会出现笛卡尔积的错误，因为没有设置关联关系

等值连接/内连接

等值连接

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;

  • 查询在new york 工作的员工
    • select * from emp e join dept d on e.deptno=d.deptno where d.loc='new york';
  • 查看工资高于3000的员工，名字，工资，部门名，所在地
    • 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; 内连接
  • 查询出所有的分类,以及与之匹配的商品
    • 这里侧重于查询分类，如果某一个分类中没有商品，那么使用等值连接和内连接就会导致查询不到所有的分类，因此这里可以使用左外连接或者右外连接，以分类所在的表(t_item_category)为主
    • 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; 右外连接
  • 查询出所有的商品,以及与之匹配的分类
    • 这里侧重于所有的商品，如果其中某一件商品与分类没有关联关系，即是没有指定分类，那么我们使用等值连接或者内连接就会查询不到这件商品，我们可以使用左外连接或者右外连接
    • 商品类(t_item) 分类（t_item_category）
    • 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;
  • 少于等于3个人的部门
    • 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;
  • DALLAS 市所有的员工信息
    • 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;
  • 查询员工信息和他的主管姓名
    • 员工信息和主管姓名在同一张表中，我们可以抽离出主管编号mgr和ename组成一张新表，那么我们就可以使用关联查询了
    • 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;
  • 员工信息，员工主管名字，部门名
    • 直接join（可以连接多张表）,直接在后面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;
    • 把上面的查询结果当成一张新表，和dept内连接即可
    • 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的后面，不影响结果

下一篇
举报

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号 | 京公网安备号11010802020287

领券