强化sql练习
建表的sql语句
/*
Navicat MySQL Data Transfer
Source Server : mysql
Source Server Version : 50713
Source Host : localhost:3306
Source Database : xx
Target Server Type : MYSQL
Target Server Version : 50713
File Encoding : 65001
Date: 2019-09-17 00:14:09
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tblcourse
-- ----------------------------
DROP TABLE IF EXISTS `tblcourse`;
CREATE TABLE `tblcourse` (
`courseId` varchar(3) NOT NULL,
`courseName` varchar(20) NOT NULL,
`teaId` varchar(3) NOT NULL,
PRIMARY KEY (`courseId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tblcourse
-- ----------------------------
INSERT INTO `tblcourse` VALUES ('001', '企业管理', '002');
INSERT INTO `tblcourse` VALUES ('002', '马克思', '008');
INSERT INTO `tblcourse` VALUES ('003', 'UML', '006');
INSERT INTO `tblcourse` VALUES ('004', '数据库', '007');
INSERT INTO `tblcourse` VALUES ('005', '逻辑电路', '006');
INSERT INTO `tblcourse` VALUES ('006', '英语', '003');
INSERT INTO `tblcourse` VALUES ('007', '电子电路', '005');
INSERT INTO `tblcourse` VALUES ('008', '毛泽东思想概论', '004');
INSERT INTO `tblcourse` VALUES ('009', '西方哲学', '012');
INSERT INTO `tblcourse` VALUES ('010', '线性代数', '017');
INSERT INTO `tblcourse` VALUES ('011', '计算机基础', '013');
INSERT INTO `tblcourse` VALUES ('012', 'AUTO CAD制图', '015');
INSERT INTO `tblcourse` VALUES ('013', '平面设计', '011');
INSERT INTO `tblcourse` VALUES ('014', 'Flash动漫', '001');
INSERT INTO `tblcourse` VALUES ('015', 'JAVA开发', '009');
INSERT INTO `tblcourse` VALUES ('016', 'C#基础', '002');
INSERT INTO `tblcourse` VALUES ('017', 'Oracl数据库原理', '010');
-- ----------------------------
-- Table structure for tblscore
-- ----------------------------
DROP TABLE IF EXISTS `tblscore`;
CREATE TABLE `tblscore` (
`stuId` varchar(5) NOT NULL,
`CourseId` varchar(3) NOT NULL,
`Score` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tblscore
-- ----------------------------
INSERT INTO `tblscore` VALUES ('1001', '003', '90');
INSERT INTO `tblscore` VALUES ('1001', '002', '87');
INSERT INTO `tblscore` VALUES ('1001', '001', '96');
INSERT INTO `tblscore` VALUES ('1001', '010', '85');
INSERT INTO `tblscore` VALUES ('1002', '003', '70');
INSERT INTO `tblscore` VALUES ('1002', '002', '87');
INSERT INTO `tblscore` VALUES ('1002', '001', '42');
INSERT INTO `tblscore` VALUES ('1002', '010', '65');
INSERT INTO `tblscore` VALUES ('1003', '006', '78');
INSERT INTO `tblscore` VALUES ('1003', '003', '70');
INSERT INTO `tblscore` VALUES ('1003', '005', '70');
INSERT INTO `tblscore` VALUES ('1003', '001', '32');
INSERT INTO `tblscore` VALUES ('1003', '010', '85');
INSERT INTO `tblscore` VALUES ('1003', '011', '21');
INSERT INTO `tblscore` VALUES ('1004', '007', '90');
INSERT INTO `tblscore` VALUES ('1004', '002', '87');
INSERT INTO `tblscore` VALUES ('1005', '001', '23');
INSERT INTO `tblscore` VALUES ('1006', '015', '85');
INSERT INTO `tblscore` VALUES ('1006', '006', '46');
INSERT INTO `tblscore` VALUES ('1006', '003', '59');
INSERT INTO `tblscore` VALUES ('1006', '004', '70');
INSERT INTO `tblscore` VALUES ('1006', '001', '99');
INSERT INTO `tblscore` VALUES ('1007', '011', '85');
INSERT INTO `tblscore` VALUES ('1007', '006', '84');
INSERT INTO `tblscore` VALUES ('1004', '004', '42');
INSERT INTO `tblscore` VALUES ('1008', '004', '34');
INSERT INTO `tblscore` VALUES ('1013', '016', '86');
INSERT INTO `tblscore` VALUES ('1013', '016', '44');
INSERT INTO `tblscore` VALUES ('1000', '014', '75');
INSERT INTO `tblscore` VALUES ('1002', '016', '110');
INSERT INTO `tblscore` VALUES ('1004', '001', '83');
INSERT INTO `tblscore` VALUES ('1008', '013', '97');
INSERT INTO `tblscore` VALUES ('1007', '003', '72');
INSERT INTO `tblscore` VALUES ('1007', '002', '87');
INSERT INTO `tblscore` VALUES ('1008', '001', '94');
INSERT INTO `tblscore` VALUES ('1008', '012', '85');
INSERT INTO `tblscore` VALUES ('1008', '006', '32');
INSERT INTO `tblscore` VALUES ('1009', '003', '90');
INSERT INTO `tblscore` VALUES ('1009', '002', '82');
INSERT INTO `tblscore` VALUES ('1009', '001', '96');
INSERT INTO `tblscore` VALUES ('1009', '010', '82');
INSERT INTO `tblscore` VALUES ('1009', '008', '92');
INSERT INTO `tblscore` VALUES ('1010', '003', '90');
INSERT INTO `tblscore` VALUES ('1010', '002', '87');
INSERT INTO `tblscore` VALUES ('1010', '001', '96');
INSERT INTO `tblscore` VALUES ('1011', '009', '24');
INSERT INTO `tblscore` VALUES ('1011', '009', '25');
INSERT INTO `tblscore` VALUES ('1012', '003', '30');
INSERT INTO `tblscore` VALUES ('1013', '002', '37');
INSERT INTO `tblscore` VALUES ('1013', '001', '16');
INSERT INTO `tblscore` VALUES ('1013', '007', '55');
INSERT INTO `tblscore` VALUES ('1013', '006', '42');
INSERT INTO `tblscore` VALUES ('1013', '012', '34');
INSERT INTO `tblscore` VALUES ('1000', '004', '16');
INSERT INTO `tblscore` VALUES ('1002', '004', '55');
-- ----------------------------
-- Table structure for tblstudent
-- ----------------------------
DROP TABLE IF EXISTS `tblstudent`;
CREATE TABLE `tblstudent` (
`stuId` varchar(5) NOT NULL,
`stuName` varchar(10) NOT NULL,
`stuAge` int(11) DEFAULT NULL,
`stuSex` char(1) NOT NULL,
PRIMARY KEY (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tblstudent
-- ----------------------------
INSERT INTO `tblstudent` VALUES ('1000', '张无忌', '18', '男');
INSERT INTO `tblstudent` VALUES ('1001', '周芷若', '19', '女');
INSERT INTO `tblstudent` VALUES ('1002', '杨过', '19', '男');
INSERT INTO `tblstudent` VALUES ('1003', '赵敏', '18', '女');
INSERT INTO `tblstudent` VALUES ('1004', '小龙女', '17', '女');
INSERT INTO `tblstudent` VALUES ('1005', '张三丰', '18', '男');
INSERT INTO `tblstudent` VALUES ('1006', '令狐冲', '19', '男');
INSERT INTO `tblstudent` VALUES ('1007', '任盈盈', '20', '女');
INSERT INTO `tblstudent` VALUES ('1008', '岳灵珊', '19', '女');
INSERT INTO `tblstudent` VALUES ('1009', '韦小宝', '18', '男');
INSERT INTO `tblstudent` VALUES ('1010', '康敏', '17', '女');
INSERT INTO `tblstudent` VALUES ('1011', '萧峰', '19', '男');
INSERT INTO `tblstudent` VALUES ('1012', '黄蓉', '18', '女');
INSERT INTO `tblstudent` VALUES ('1013', '郭靖', '19', '男');
INSERT INTO `tblstudent` VALUES ('1014', '周伯通', '19', '男');
INSERT INTO `tblstudent` VALUES ('1015', '瑛姑', '20', '女');
INSERT INTO `tblstudent` VALUES ('1016', '李秋水', '21', '女');
INSERT INTO `tblstudent` VALUES ('1017', '黄药师', '18', '男');
INSERT INTO `tblstudent` VALUES ('1018', '李莫愁', '18', '女');
INSERT INTO `tblstudent` VALUES ('1019', '冯默风', '17', '男');
INSERT INTO `tblstudent` VALUES ('1020', '王重阳', '17', '男');
INSERT INTO `tblstudent` VALUES ('1021', '郭襄', '18', '女');
-- ----------------------------
-- Table structure for tblteacher
-- ----------------------------
DROP TABLE IF EXISTS `tblteacher`;
CREATE TABLE `tblteacher` (
`TeaId` varchar(3) NOT NULL,
`TeaName` varchar(10) NOT NULL,
PRIMARY KEY (`TeaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tblteacher
-- ----------------------------
INSERT INTO `tblteacher` VALUES ('001', '姚明');
INSERT INTO `tblteacher` VALUES ('002', '叶平');
INSERT INTO `tblteacher` VALUES ('003', '叶开');
INSERT INTO `tblteacher` VALUES ('004', '孟星魂');
INSERT INTO `tblteacher` VALUES ('005', '独孤求败');
INSERT INTO `tblteacher` VALUES ('006', '裘千仞');
INSERT INTO `tblteacher` VALUES ('007', '裘千尺');
INSERT INTO `tblteacher` VALUES ('008', '赵志敬');
INSERT INTO `tblteacher` VALUES ('009', '阿紫');
INSERT INTO `tblteacher` VALUES ('010', '郭芙蓉');
INSERT INTO `tblteacher` VALUES ('011', '佟湘玉');
INSERT INTO `tblteacher` VALUES ('012', '白展堂');
INSERT INTO `tblteacher` VALUES ('013', '吕轻侯');
INSERT INTO `tblteacher` VALUES ('014', '李大嘴');
INSERT INTO `tblteacher` VALUES ('015', '花无缺');
INSERT INTO `tblteacher` VALUES ('016', '金不换');
INSERT INTO `tblteacher` VALUES ('017', '乔丹');
1.使用 in 子句实现查询存在员工的部门
SELECT tw.did from t_work tw WHERE eid IN (SELECT tp.eid from t_emp tp) GROUP BY tw.did ;
2.查询 Emp 表中的第 3 到 5 条纪录
SELECT * FROM t_emp LIMIT 2,3;
3)查询拥有最多的员工的部门的基本信息(要求只取出一个部门的信息),如果有多个部门人数一样,那么取出部门编号最小的那个部门的基本信息
SELECT * FROM t_dept td where td.did =
(SELECT tw.did wid from t_work tw GROUP BY tw.did
ORDER BY COUNT(tw.eid) DESC,tw.did ASC LIMIT 1);
4查询出工资比其所在部门平均工资高的所有职工信息
SELECT * FROM t_emp te WHERE te.eid IN
(SELECT eid FROM t_work a LEFT JOIN
(SELECT tw.did id,AVG(tw.salary) money from t_work tw GROUP BY tw.did) n
ON a.did = n.id
WHERE a.salary>n.money);
5.查询所有员工,如果 SEX 为 F 显示为 女 M 显示为 男 ””,否则显示为 不确定
SELECT tm.eid,tm.ename,tm.bdate,CASE tm.sex
WHEN 'F' THEN '女'
WHEN 'M' THEN '男'
ELSE '不确定'
END
,tm.city FROM t_emp tm;
t_book表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_book
-- ----------------------------
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
`bookid` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`bookname` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '书名',
`authorname` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '作者',
`price` decimal(2,0) DEFAULT NULL COMMENT '单价',
`inventory` int(11) DEFAULT NULL COMMENT '库存',
PRIMARY KEY (`bookid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of t_book
-- ----------------------------
INSERT INTO `t_book` VALUES ('1', 'java编程思想', '张思康', '36', '300');
INSERT INTO `t_book` VALUES ('2', 'jsp入门', '李瑞', '42', '240');
INSERT INTO `t_book` VALUES ('3', 'SQL2008', '赵迈迈', '50', '200');
INSERT INTO `t_book` VALUES ('4', '软件测试', '刘琴', '25', '100');
INSERT INTO `t_book` VALUES ('5', '项目管理', '魏远', '32', '230');
INSERT INTO `t_book` VALUES ('6', 'Struts入门', '张翔', '56', '300');
t_order订单表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_orders
-- ----------------------------
DROP TABLE IF EXISTS `t_orders`;
CREATE TABLE `t_orders` (
`orderid` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单号',
`cutomername` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '客户姓名',
`createdate` datetime(3) DEFAULT NULL COMMENT '订购日期',
`status` char(1) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '订单状态(C:已确认;P:已发货)',
PRIMARY KEY (`orderid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of t_orders
-- ----------------------------
INSERT INTO `t_orders` VALUES ('1', '张三', '2010-09-30 00:00:00.000', 'P');
INSERT INTO `t_orders` VALUES ('2', '李思思', '2010-10-23 00:00:00.000', 'P');
INSERT INTO `t_orders` VALUES ('3', '王武', '2010-10-24 00:00:00.000', 'P');
INSERT INTO `t_orders` VALUES ('4', '麦克', '2010-10-26 00:00:00.000', 'P');
INSERT INTO `t_orders` VALUES ('5', '李思思', '2010-11-02 00:00:00.000', 'C');
INSERT INTO `t_orders` VALUES ('6', '王武', '2010-11-23 00:00:00.000', 'C');
t_orderitem订单详情表
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_orderitem
-- ----------------------------
DROP TABLE IF EXISTS `t_orderitem`;
CREATE TABLE `t_orderitem` (
`itemid` int(11) NOT NULL AUTO_INCREMENT,
`orderid` int(11) DEFAULT NULL COMMENT '订单id',
`bookid` int(11) DEFAULT NULL COMMENT '图书编号',
`quantity` int(11) DEFAULT NULL COMMENT '订购数量',
PRIMARY KEY (`itemid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of t_orderitem
-- ----------------------------
INSERT INTO `t_orderitem` VALUES ('1', '1', '1', '20');
INSERT INTO `t_orderitem` VALUES ('2', '1', '2', '40');
INSERT INTO `t_orderitem` VALUES ('3', '2', '3', '30');
INSERT INTO `t_orderitem` VALUES ('4', '2', '2', '30');
INSERT INTO `t_orderitem` VALUES ('5', '2', '4', '40');
INSERT INTO `t_orderitem` VALUES ('6', '3', '3', '20');
INSERT INTO `t_orderitem` VALUES ('7', '4', '5', '40');
INSERT INTO `t_orderitem` VALUES ('8', '5', '5', '40');
INSERT INTO `t_orderitem` VALUES ('9', '5', '1', '20');
INSERT INTO `t_orderitem` VALUES ('10', '6', '5', '30');
7.查询所有订单,列出订单编号、客户姓名、 订单总价 、订购日期
SELECT tos.orderid,tos.cutomername,tos.createdate,s.`总价`
FROM t_orders tos LEFT JOIN
(SELECT tom.orderid,SUM(tom.quantity*tb.price) 总价 FROM t_orderitem tom,t_book tb
WHERE tb.bookid = tom.bookid GROUP BY tom.orderid)s
ON tos.orderid = s.orderid;
8、 查询年销售量从高到底的顺序列出图书基本信息
SELECT
tb.bookid,
tb.bookname,
tb.authorname,
tb.price,
s.`销售量`
FROM
t_book tb
LEFT JOIN (
SELECT
bookid,
SUM(tom.quantity) 销售量
FROM
t_orderitem tom
GROUP BY
tom.bookid
) s ON tb.bookid = s.bookid
ORDER BY
s.`销售量` DESC
;