给大家分享一下sql的查询语句 的基本用法:
记录的基本操作:
SELECT * FROM exam1;查询表中所有记录
INSERT INTO exam1 VALUES (NULL,'dd',NULL,70,70);添加所有记录
UPDATE exam1 SET english =99 WHERE id=7; 修改指定的记录
DELETE FROM exam1 where id=11;删除指定记录
SELECT NAME ,english FROM exam1; 查询姓名和英语
select DISTINCT english FROM exam1;查询不重复的英语
SELECT NAME,english+chinese+math FROM exam1;查询 每个人的三样成绩和
SELECT NAME,english+chinese+math sum FROM exam1;查询 每个人的三样成绩和并且给上sum名字
SELECT *FROM exam1 WHERE name = '李四';查询表中满足条件是李四的
SELECT * FROM exam1 WHERE name ='李四' AND english>90;查询表中满足条件是李四的并且大于90
SELECT SUM(english+chinese+math) from exam1;获取总成绩
select SUM(IFNULL(english,0)+chinese+math) FROM exam1;获取总成绩且判断有null的话变成0
SELECT SUM(english)+ sum(chinese)+SUM(math) FROM exam1;获取总成绩
SELECT * FROM exam1 ORDER BY chinese;表中的中文按抵增
SELECT * FROM exam1 ORDER BY english DESC, chinese ASC;英语成绩相同语文就递增
SELECT *FROM exam1 WHERE name LIKE '李%' ORDER BY math DESC; 把姓李的数学成绩递减排序
SELECT sum(chinese) FROM exam1;查询成绩和
SELECT COUNT(*) FROM exam1 查询记录个数
SELECT sum(chinese) , SUM(math) FROM exam1; 查询 语文成绩和 数学成绩和
SELECT SUM(math) FROM exam1 WHERE name LIKE'李%';查询姓李的数学成绩和
SELECT COUNT(*) FROM exam1 WHERE name LIKE '李%'; 查询姓李的个数
SELECT MAX(chinese) from exam1;最大值
SELECT min(chinese) from exam1;最小值
SELECT AVG(chinese) FROM exam1;平均值
SELECT product,COUNT(*) FROM orderitem GROUP BY product;按照产品分类,每个类有几个产品
SELECT product,SUM(price) FROM orderitem GROUP BY product;查询每个分类的总金额
SELECT product,SUM(price) FROM orderitem GROUP BY product
HAVING SUM(price)>5000;查询每个类的总金额并且金额大于5000的
SELECT product ,SUM(price) FROM orderitem GROUP BY product HAVING SUM(price)>5000 ORDER BY SUM(price) ASC; 查询每个类的总金额并且金额大于5000的 按照递增排序
SELECT product,SUM(price) FROM orderitem WHERE product != '电视机' GROUP BY product HAVING SUM(price)>5000 ; 查询每个类的总金额并且金额大于5000的 不包含 产品为电视机的
SELECT product,SUM(price) FROM orderitem GROUP BY product HAVING product != '电视机' AND SUM(price)>5000 ;查询每个类的总金额并且金额大于5000的 不包含 产品为电视机的
|
|
收藏
淘帖
踩
