©2020-2024 ioDraw All rights reserved
<>案例一
<>数据准备
CREATE TABLE exam( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20),
english INT, chinese INT, math INT ); INSERT INTO exam VALUES
(NULL,'张三',85,74,91); INSERT INTO exam VALUES (NULL,'李四',95,90,83); INSERT INTO
exam VALUES (NULL,'王五',85,84,59); INSERT INTO exam VALUES (NULL,'赵六',75,79,76);
INSERT INTO exam VALUES (NULL,'田七',69,63,98); INSERT INTO exam VALUES
(NULL,'李老八',89,90,83); #查询所有学生考试成绩信息 SELECT *FROM exam; #查询所有学生的姓名和英语成绩 SELECT
NAME,english FROM exam; #查询英语成绩信息（不显示重复的值） SELECT DISTINCT english FROM exam;
#查看学生姓名和学生的总成绩 SELECT NAME,SUM(english + chinese + math) sumGrade FROM exam
GROUP BY NAME; #查询学生的姓名和平均分,平均分用avg别名展示 SELECT NAME,(english+chinese+math)/3
AVG FROM exam GROUP BY NAME; #查询李四学生的成绩： SELECT NAME,english,chinese,math FROM
exam WHERE NAME ='李四'; #查询名称叫李四学生并且英文大于90分 SELECT NAME,english FROM exam WHERE
english>90 AND NAME ='李四'; #查询姓李的学生的信息 SELECT *FROM exam WHERE NAME LIKE '李%';
#查询英语成绩是69,75,89学生的信息 SELECT *FROM exam WHERE english IN (69,75,89);
#查询数学成绩在80-90之间的学生信息 SELECT *FROM exam WHERE math BETWEEN 80 AND 90;
#只要有一门不及格,就找出来 SELECT *FROM exam WHERE english< 60 OR chinese < 60 OR math <
60; #查询学生信息，并且按照语文成绩进行排序： SELECT *FROM exam ORDER BY chinese;
#查询学生信息，并且按照语文成绩倒序排序： SELECT *FROM exam ORDER BY chinese DESC;
#查询学生信息，先按照语文成绩进行倒序排序，如果成绩相同再按照英语成绩升序排序 SELECT *FROM exam ORDER BY chinese
DESC, english ASC; #查询姓李的学生的信息，按照英语成绩降序排序 SELECT * FROM exam WHERE NAME LIKE
'李%' ORDER BY english DESC; #查询学生信息,按照总成绩排序,只展示学生的姓名和总分(SUM) SELECT
NAME,SUM(english + chinese + math) SUM FROM exam GROUP BY NAME ORDER BY SUM;
#获取所有学生的英语成绩的总和: SELECT SUM(english) FROM exam; #获取所有学生的英语成绩和数学成绩总和： SELECT
SUM(english+math) FROM exam; #查询姓李的学生的英语成绩的总和 SELECT SUM(english) FROM exam
WHERE NAME LIKE '李%' ; #查询所有学生各科的总成绩： SELECT
SUM(english),SUM(chinese),SUM(math) FROM exam; #获得姓李的学生的个数 SELECT COUNT(*) FROM
exam WHERE NAME LIKE '李%'; #获得数学成绩的最高分： SELECT MAX(math) FROM exam; #获得语文成绩的最小值
SELECT MIN(chinese) FROM exam; #获取语文成绩的平均值 SELECT AVG(chinese) FROM exam;
<>案列二
<>数据准备
CREATE TABLE orderitem( id INT PRIMARY KEY AUTO_INCREMENT, product
VARCHAR(20), price DOUBLE ); INSERT INTO orderitem VALUES (NULL,'电视机',2999);
INSERT INTO orderitem VALUES (NULL,'电视机',2999); INSERT INTO orderitem VALUES
(NULL,'洗衣机',1000); INSERT INTO orderitem VALUES (NULL,'洗衣机',1000); INSERT INTO
orderitem VALUES (NULL,'洗衣机',1000); INSERT INTO orderitem VALUES
(NULL,'冰箱',3999); INSERT INTO orderitem VALUES (NULL,'冰箱',3999); INSERT INTO
orderitem VALUES (NULL,'空调',1999); #按商品名称统计，每类商品所购买的个数： SELECT product,COUNT(*)
FROM orderitem GROUP BY product; #按商品名称统计，每类商品所花费的总金额： SELECT
product,SUM(price) FROM orderitem GROUP BY product;
#按商品名称统计，统计每类商品花费的总金额在5000元以上的商品 SELECT product,SUM(price) SUM FROM orderitem
GROUP BY product HAVING SUM(price) > 5000 ;
#按商品名称统计，统计每类商品花费的总金额在5000元以上的商品，并且按照总金额升序排序 SELECT product,SUM(price) SUM FROM
orderitem GROUP BY product HAVING SUM(price) > 5000 ORDER BY SUM ASC;
#按商品名称统计，统计每类商品购买数量大于1的商品，并且按照购买数量降序排序,只展示商品和购买数量 SELECT product,COUNT(*) num
FROM orderitem GROUP BY product HAVING num>1 ORDER BY num DESC;