A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

© B.L.tao 初级黑马   /  2018-5-16 11:34  /  880 人查看  /  0 人回复  /   0 人收藏 转载请遵从CC协议 禁止商业使用本文

  给大家分享一下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的 不包含 产品为电视机的


0 个回复

您需要登录后才可以回帖 登录 | 加入黑马