数据库sql语句练习 基础篇

学习数据库，做的练习题记录下来，方便那些想要练习的人

数据库建表

/*

数据库名 : productorder

共五个表 customer ，order ，product ,vender , recruid

*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

1.customer表

---

DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer`  (
  `cid` int(0) NOT NULL AUTO_INCREMENT COMMENT '唯一顾客ID，自动增长',
  `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '顾客姓名',
  `caddress` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '顾客地址',
  `ccity` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '顾客所在城市',
  `cstate` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '顾客所在州',
  `czip` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '邮政编码',
  `ccountry` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '顾客所在国家',
  `ccontact` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '顾客联系姓名',
  `cemail` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '顾客邮箱',
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

---

– Records of customer

---

INSERT INTO `customer` VALUES (1, '马云', '大马路一号', '杭州', '浙江', '110120', '中国', '阿云', 'mayun@alibaba.com');
INSERT INTO `customer` VALUES (2, '刘强东', '中关村上地一街2号', '北京', '北京', '100000', '中国', '奶茶妹妹', 'liuqiangdong@jd.com');
INSERT INTO `customer` VALUES (3, '马化腾', '你懂得', '东莞', '广东', '300000', '中国', '马化腾', 'mahuateng@qq.com');

---

2. product

---

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `pid` int(0) NOT NULL AUTO_INCREMENT COMMENT '唯一的产品ID,自动增长',
  `pname` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '产品名',
  `pprice` decimal(8, 2) NOT NULL COMMENT '产品价格',
  `pdesc` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '产品描述',
  `pcount` int(0) NOT NULL COMMENT '库存数量',
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

---

– Records of product

---

INSERT INTO `product` VALUES (1, '小米Note2', 2799.00, '小米Note2 双曲面', 100);
INSERT INTO `product` VALUES (2, '小米Mix', 3499.00, '我世界最牛的手机，手机中的大哥大', 100);
INSERT INTO `product` VALUES (3, 'iPhone7', 5388.00, '4.7屏  玫瑰金色', 100);
INSERT INTO `product` VALUES (4, 'iPhone6s', 4588.00, '分期每月最低约RMB382元', 100);

---

3.order表

---

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `oid` int(0) NOT NULL AUTO_INCREMENT COMMENT '订单号，自动增长',
  `pid` int(0) NOT NULL COMMENT '产品ID',
  `cid` int(0) NOT NULL COMMENT '顾客ID',
  `ocount` int(0) NOT NULL DEFAULT 1 COMMENT '物品数量',
  `oprice` decimal(8, 2) NOT NULL COMMENT '物品单价',
  `odate` datetime(0) NOT NULL COMMENT '订单时间',
  PRIMARY KEY (`oid`) USING BTREE,
  INDEX `pid`(`pid`) USING BTREE,
  INDEX `cid`(`cid`) USING BTREE,
  CONSTRAINT `fk_order_cid` FOREIGN KEY (`cid`) REFERENCES `customer` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_order_pid` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

---

– Records of order

---

INSERT INTO `order` VALUES (1, 1, 1, 1, 2999.00, '2016-12-02 15:41:39');
INSERT INTO `order` VALUES (2, 3, 2, 2, 5888.00, '2016-12-01 15:42:42');
INSERT INTO `order` VALUES (3, 4, 2, 100, 4888.00, '2016-12-06 15:43:26');
INSERT INTO `order` VALUES (4, 1, 1, 2, 2999.00, '2016-12-31 15:43:26');
INSERT INTO `order` VALUES (6, 1, 1, 16, 2655.00, '2017-01-25 01:23:55');
INSERT INTO `order` VALUES (11, 1, 1, 2, 2999.00, '2017-01-01 01:59:59');

---

4.vendor表

---

DROP TABLE IF EXISTS `vendor`;
CREATE TABLE `vendor`  (
  `vid` int(0) NOT NULL AUTO_INCREMENT COMMENT '唯一的供应商ID，自动增长',
  `vname` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '供应商姓名',
  `vaddress` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '供应商地址',
  `vcity` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '供应商所在城市',
  `vstate` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '供应商所在州',
  `vzip` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '供应商地址邮政编码',
  `vcountry` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '供应商所在国家',
  PRIMARY KEY (`vid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

---

– Records of vendor

---

INSERT INTO `vendor` VALUES (1, '雷军', '蟠桃园', '仙桃', '湖北', '4000000', '中国');
INSERT INTO `vendor` VALUES (2, '库克', 'Apple 1 Infinite Loop Cupo', '库币', '加利福尼亚周', '8000000', '美国');
INSERT INTO `vendor` VALUES (3, '三星', NULL, '首尔', '韩国', '3203000', '韩国');

SET FOREIGN_KEY_CHECKS = 1;

5. recruit表

---

DROP TABLE IF EXISTS `recruit`;
CREATE TABLE `recruit`  (
  `rid` int(0) NOT NULL AUTO_INCREMENT COMMENT '入库ID，自动增长',
  `pid` int(0) NOT NULL COMMENT '物品ID',
  `vid` int(0) NOT NULL COMMENT '供应商ID',
  `rprice` decimal(10, 0) NOT NULL COMMENT '单价',
  `rcount` int(0) NOT NULL COMMENT '数量',
  `returncount` int(0) NULL DEFAULT NULL COMMENT '退货数量',
  `rdecp` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '订单描述',
  `rdate` datetime(0) NOT NULL COMMENT '入库时间',
  PRIMARY KEY (`rid`) USING BTREE,
  INDEX `fk_recruit_vid`(`vid`) USING BTREE,
  INDEX `fk_recruit_pid`(`pid`) USING BTREE,
  CONSTRAINT `fk_recruit_pid` FOREIGN KEY (`pid`) REFERENCES `product` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_recruit_vid` FOREIGN KEY (`vid`) REFERENCES `vendor` (`vid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

---

– Records of recruit

---

INSERT INTO `recruit` VALUES (1, 4, 2, 4588, 50, 0, '50个iPhone6s', '2016-12-01 16:07:57');
INSERT INTO `recruit` VALUES (2, 4, 2, 4388, 200, 0, '200个iPhone6s', '2016-12-04 16:08:41');
INSERT INTO `recruit` VALUES (3, 3, 2, 5388, 100, 0, '100个iPhone', '2016-12-06 00:00:00');
INSERT INTO `recruit` VALUES (4, 3, 1, 5288, 50, 0, '雷军 50个IPhone', '2016-11-16 16:10:20');
INSERT INTO `recruit` VALUES (5, 2, 1, 3000, 50, 1, '小米Mix50个', '2016-11-16 16:10:45');
INSERT INTO `recruit` VALUES (6, 2, 1, 2800, 150, 2, '小米Note2 150个', '2016-11-21 16:11:01');
INSERT INTO `recruit` VALUES (7, 1, 1, 2500, 10, 5, '小米Note2 10个', '2016-11-21 16:11:17');
INSERT INTO `recruit` VALUES (8, 1, 1, 2399, 20, 15, '小米Note2 20个', '2016-12-13 16:11:35');

---

数据库习题单表查询

– 一定要一步一步写步骤

– 数据库productorder
– 1.customer
– 1.customer表
– 1).查询所有顾客信息。

SELECT * FROM customer

– 2).查询姓名和联系人相同的顾客信息

SELECT * FROM customer 
WHERE cname = ccontact

– 3).查询所在城市是北京的顾客姓名，顾客地址，顾客所在城市，邮编。。

SELECT cname,caddress,ccity,cemail
FROM customer 
WHERE ccity = '北京'

– 2.product表：
– 1).查询所有产品信息

SELECT * FROM product

– 2).查询产品名中带有“小米”的商品信息

SELECT *
FROM product
WHERE pname LIKE '%小米%' 

– 3).查询产品名中以“小”开头或者产品描述中带有“玫瑰”的产品信息

SELECT * 
FROM product
WHERE pname LIKE '小%' OR pdesc LIKE '%玫瑰%'

– 4).查询价格在3000以上的的产品名和单价（使用别名）

SELECT pro.pname , pro.pcount 
FROM product pro
where pro.pprice > 3000

– 5).查询价格在3000以上并且库存数量在80以上的产品的名字和单价

SELECT pname , pprice
FROM product
WHERE pprice > 3000 AND pcount > 80

– 3.order表：
– 1).查询所有订单

SELECT * FROM `order`

– 2).查询物品数量大于10的订单信息

SELECT *
FROM `order`
WHERE ocount > 10

– 3).分别使用IN和OR的方式查询pid为2和4的商品销售记录
– (1) IN

SELECT odate 
FROM `order`
WHERE pid in (2,4)

– (2) OR

SELECT odate
FROM `order`
where pid = 2 OR pid = 4

– 4)查询2016-12-05到2016-12-15之间的订单详情(使用两种方法查询)
– (1)

SELECT *
FROM `order`
WHERE odate BETWEEN '2016-12-05' AND '2016-12-15'

– (2)

SELECT *
FROM `order`
WHERE odate > '2016-12-05' AND odate < '2016-12-15'

– 5)查询订单编号为4的交易总金额（请使用别名）

SELECT ord.ocount * ord.oprice
FROM `order` ord
WHERE oid = 4

– 4.vendor表：
– 1).查询所有供应商信息

SELECT * FROM vendor

– 2).找出供应商地址以A起头、 以o结尾的所有供应商

SELECT *
FROM vendor
WHERE vaddress like 'A%o' 

– 3).查询供应商地址为空的供应商信息

SELECT * 
FROM vendor 
where vaddress is NULL

– 4).查询出所有供应商的姓名，地址，城市（使用别名）

SELECT ven.vname '供应商姓名',ven.vaddress '地址',ven.vcity '城市'
FROM vendor ven

– 5.recruit表：
– 1).查询所有库存信息

SELECT * FROM recruit

– 2)查询退货数量大于10的物品信息

SELECT * 
FROM recruit
WHERE returncount > 10

– 3)查询所有的供应商编号(需过滤重复行)

SELECT DISTINCT vid 
from recruit

数据库习题单表查询加聚合方法

– product表
– 1.统计所有库存商品的总价值

SELECT SUM(pro.pprice * pro.pcount)  totalPrice
FROM product pro

– 2.查询库存商品中，最高单价、最低单价分别是多少

SELECT MAX(pprice) maxPrice,MIN(pprice) minPrice
FROM product

– 3.检索产品价格大于3000的有几个

SELECT count(pid) number
FROM product
where pprice > 3000

– 4.按产品价格降序并提取前2条数据

SELECT *
FROM product
ORDER BY pprice DESC
LIMIT 0,2

– order表
– 1.检索所有订单订购物品的总数

SELECT SUM(ocount) sum
FROM `order`

– 2查询所有销售信息，并按销售时间倒序排列

SELECT *
FROM `order`
ORDER BY odate DESC

– 3查询数量大于10的订单信息，并按照数量倒序排序

SELECT *
FROM `order`
WHERE ocount	> 10
ORDER BY ocount DESC

– 4查询2016年12月份订单数量最高的订单

SELECT MAX(ocount) 
FROM `order` 
WHERE odate BETWEEN '2016-12-01' AND '2016-12-31'

– 5.统计所有商品的销售量

SELECT pid,SUM(ocount) sum
FROM `order`
GROUP BY pid

– 6.统计销售量大于50的所有商品

SELECT pid, sum(ocount)
 FROM `order` 
GROUP BY pid HAVING sum(ocount) > 50;

– 7.查询2016-12-05之后销售数量最高的两笔销售信息

SELECT *
FROM `order`
WHERE odate > '2016-12-05'
ORDER BY ocount DESC
LIMIT 0,2

– recruit表
– 1.查询进货量最高的三笔进货信息

SELECT *
FROM recruit
ORDER BY rcount DESC
LIMIT 0,3

– 2.计算供应商ID 为1的所有产品的平均价格 有些不确定

SELECT avg(rprice) '平均价格'
FROM recruit
WHERE vid = 1

– 3.查询2016-12-01之后进货信息，并按时间降序排列

SELECT *
FROM recruit
WHERE rdate > '2016-12-01'
ORDER BY rdate DESC

– 4.统计所有商品的进货量，并按倒序排列

SELECT pid,SUM(rcount) count
FROM recruit
GROUP BY pid
ORDER BY count DESC

SELECT pid, sum(rcount) 
FROM recruit 
GROUP BY pid 
ORDER BY sum(rcount) DESC;

– 5.统计12月1 日以后进货量大于100的所有供应商ID，并按进货总量倒序排列
– GROUP BY 分类了，那就不能直接查询数的数据了，必须用sum,AVG,MAX统计了

SELECT vid,SUM(rcount)	count
FROM recruit
where rdate >= '2016-12-1' 
GROUP BY vid HAVING count > 100
ORDER BY count DESC

– customer 表
– 1.获取顾客表每位邮箱的长度

SELECT cus.cname, LENGTH(cemail) AS 长度
 FROM customer cus

– 2.提取顾客邮件信息,起始位置为7，长度为5

select cus.cname,substring(cemail,7,5)  as 内容 
FROM customer cus

– 3.获取今年是哪一年，这个月是这一年的那个月，这一周是这一年的那一周，今天是这一年那一天。

SELECT
YEAR(CURDATE()) as yr,
MONTH(CURDATE()) as mon,
WEEK(CURDATE()) as wk,
DAYOFYEAR(CURTIME()) AS yd;

多表查询和子查询练习
数据库sql语句练习 基础篇2