本文共 2990 字，大约阅读时间需要 9 分钟。

一、基础语法练习

题目

答案

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 query
SELECT * 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 = '蔬菜';

完

转载地址：http://xnpuk.baihongyu.com/