一、基础语法练习

题目

图片描述

答案

DROP TABLE IF EXISTS t_shop_xs;CREATE TABLE t_shop_xs(    id INT PRIMARY KEY AUTO_INCREMENT,    销售编号 VARCHAR(16) NOT NULL,    销售日期 VARCHAR(16) NOT NULL,    销售数量 INT NOT NULL,    商品单价 DOUBLE NOT NULL,    销售总金额 DOUBLE NOT NULL,    销售员工 VARCHAR(16) NOT NULL);INSERT INTO `t_shop_xs` VALUES (1, 'xsl001', '2013/12/2', 124, 134.5, 16678, '张三');INSERT INTO `t_shop_xs` VALUES (2, 'xsl002', '2013/12/2', 50, 80, 4000, '李四');INSERT INTO `t_shop_xs` VALUES (3, 'xsl003', '2013/12/5', 66, 55, 3630, '张三');INSERT INTO `t_shop_xs` VALUES (4, 'xsl001', '2013/11/20', 10, 134.5, 1345, '张三');INSERT INTO `t_shop_xs` VALUES (5, 'xsl001', '2013/11/2', 20, 134.5, 2690, '王五');INSERT INTO `t_shop_xs` VALUES (6, 'xsl002', '2013/11/5', 30, 80, 2400, '张三');INSERT INTO `t_shop_xs` VALUES (7, 'xsl002', '2013/11/9', 23, 80, 1840, '王五');INSERT INTO `t_shop_xs` VALUES (8, 'xsl003', '2013/12/11', 10, 55, 550, '李四');INSERT INTO `t_shop_xs` VALUES (9, 'xsl003', '2013/12/12', 50, 55, 2750, '王五');INSERT INTO `t_shop_xs` VALUES (10, 'xsl004', '2013/11/30', 45, 100, 4500, '张三');-- 查询满足条件的数据BEGIN TRANSACTION;    -- limiting where clause    SELECT * FROM `t_shop_xs` WHERE 销售员工 = '张三' LIMIT 10;    -- specific date query    SELECT * FROM `t_shop_xs` WHERE DATE_FORMAT(销售日期,'%m') = '12' LIMIT 10;    -- amount in specific month    SELECT * FROM `t_shop_xs` WHERE 销售总金额 > 2000 AND DATE_FORMAT(销售日期,'%m') = '12';COMMIT;-- limit querySELECT * FROM `t_shop_xs` LIMIT 10;-- 某特销售编号的数据SELECT * FROM `t_shop_xs` WHERE 商品编号 = 'xsl001' LIMIT 10;--条件查询SELECT sales编号, 销售总金额, 销售员工 FROM `t_shop_xs` WHERE 销售员工 = '李四' LIMIT 5;--时间范围查询SELECT * FROM `t_shop_xs` WHERE DATE_FORMAT(销售日期, '%Y-%m-%d') BETWEEN '2013-11-20' AND '2013-12-10';-- 挥子条件查询，并且具有偏移SELECT * FROM `t_shop_xs` WHERE 商品单价 > 100 OR 销售数量 > 50 LIMIT 8 OFFSET 2;

二、基础函数练习

题目

图片描述

答案

CREATE TABLE t_shop(    s_id INT PRIMARY KEY AUTO_INCREMENT,    s_shopcode VARCHAR(30),    s_name VARCHAR(40),    s_price INT,    s_class VARCHAR(50));-- 插入数据INSERT INTO t_shop(s_shopcode, s_name, s_price, s_class) VALUES ('n11','橙子',9,'水果'),('x330','血橙',11,'水果'),('yx673','柚子',7,'水果'),('n12','白菜',2,'蔬菜'),('a13','冬瓜',3,'蔬菜'),('n14','西瓜',4,'水果'),('n15','丝瓜',5,'蔬菜'),('c16','苦瓜',6,'蔬菜'),('m17','南瓜',5,'蔬菜'),('d18','茄子',6,'蔬菜');-- 查询明文中包含"瓜"的记录SELECT * FROM t_shop WHERE s_name LIKE "%瓜%";--价格在1到8之间的产品SELECT * FROM t_shop WHERE s_price BETWEEN 1 AND 8;-- 获取某一列的最大值SELECT MAX(s_price) FROM t_shop;--降序排序前三名SELECT * FROM t_shop ORDER BY s_price DESC LIMIT 3;--获取某一列的平均值SELECT AVG(s_price) FROM t_shop;--特定条件平均值SELECT AVG(s_price) FROM t_shop WHERE s_name LIKE "%瓜%";-- 启发式查询最大值作为商(SELECT MAX(s_price) FROM t_shop) / (SELECT MIN(s_price) FROM t_shop);-- 统计特定关键词的记录数SELECT COUNT(s_name) FROM t_shop WHERE s_name LIKE "%橙%";-- 更新数据（注意：WHERE必须存在）UPDATE t_shop SET s_price = 2 WHERE s_name = '西瓜';-- 删除数据范围查询DELETE FROM t_shop WHERE s_id IN (4,9,1);-- 获取特定类别的最大价格SELECT MAX(s_price) FROM t_shop WHERE s_class = '蔬菜';