对一组值进行计算,并返回计算后的值,一般用来统计数据
累加所有行的值
计算ID=1的学生的的总分
select SUM(grade) as '总分' from score where student_id = 1;
计算所有行的平均值
计算ID=1的学生的的平均分
select AVG(grade) as '平均分' from score where student_id = 1;
select MAX(grade) 最高分,MIN(grade) 最低分 from score where student_id = 1;
select AVG(grade) as '平均分' 最低分 from score where student_id = 1;
select COUNT(*) from student;
select COUNT(1) from student;
select COUNT(name) from student;
select COUNT(NULL) from student;
分组查询就是按某列的值进行分组,相同的值分成一组,然后可以对此组内进行求平均、求和等计算
SELECT 列名,查询表达式
FROM <表名>
WHERE <条件>
GROUP BY <分组字段>
HAVING 分组后的过滤条件
ORDER BY 列名 [ASC,DESC]
LIMIT 偏移量,条数
SELECT列表中只能包含:
select student_id,avg(grade) from score group by student_id;
select course_id,max(grade) 平均分 from score group by course_id order by max(grade) desc
select province,gender,COUNT(*) from student group by province,gender
SELECT FROM <表名>
WHERE
GROUP BY {col_name|expr|position}
HAVING {col_name|expr|position}
ORDER BY {col_name|expr|position} [ASC|DESC]
LIMIT offset,row_count
统计学生人数超过1人的省份
select province,COUNT(*) from student group by province having COUNT(*)>1
不及格次数大于1次的学生
select student_id,COUNT(*) 不及格次数 from score where grade <60 group by student_id having COUNT(*)>1
>
大于>
= 大于等于SELECT ROUND(AVG(age),2) FROM student;
SELECT * from student WHERE age > (SELECT ROUND(AVG(age),2) FROM student)
= < <= = <> !=
年龄大于陕西省任何一位同学
SELECT * from student WHERE age > ANY (SELECT age FROM student WHERE province = '陕西省');
年龄大于陕西省某些同学
SELECT * from student WHERE age > SOME (SELECT age FROM student WHERE province = '陕西省');
年龄大于陕西省所有同学
SELECT * from student WHERE age > ALL (SELECT age FROM student WHERE province = '陕西省');
SELECT * FROM student where id in (SELECT distinct student_id from score);
SELECT * FROM student where EXISTS (SELECT distinct student_id from score where student.id = score.student_id )
使用ON关键字来设定连接条件,也可以使用WHERE来代替
显示左表和右表中符合条件的
SELECT * FROM student INNER JOIN score ON student.id = score.student_id;
显示左表的全部和右表符合条件的
SELECT * FROM student LEFT JOIN score ON student.id = score.student_id;
显示右表的全部和左表符合条件的
SELECT * FROM student RIGHT JOIN score ON student.id = score.student_id;
SELECT student.name,course.name,score.grade FROM score
INNER JOIN student ON student.id = score.student_id
INNER JOIN course ON course.id = score.course_id;
CREATE table category(
id int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
name varchar(50),
parent_id int(11)
)
INSERT INTO category(id,name,parent_id)
VALUES (1,'数码产品',0),(2,'服装',0),(3,'食品',0),
(4,'iPad',1),(5,'李宁',2),(6,'康师傅',3);
SELECT c1.id,c1.name,COUNT(1)
FROM category c1 INNER JOIN category c2 ON c1.id = c2.parent_id
WHERE c1.parent_id = 0
GROUP BY c1.id;
SELECT c1.id,c1.name,p.name
FROM category c1 LEFT JOIN category p ON c1.parent_id = p.id
INSERT INTO category(id,name,parent_id)
VALUES
(7,'iPad',1),
(8,'李宁',2),
(9,'康师傅',3);
SELECT * FROM category c1 LEFT JOIN
(SELECT id,name from category GROUP BY name HAVING COUNT(1)>1) c2
ON c1.name = c2.name WHERE c1.id != c2.id
SELECT * FROM category c1
WHERE c1.name IN
(SELECT name from category GROUP BY name HAVING COUNT(1)>1)
AND c1.id NOT IN
(SELECT MIN(id) from category GROUP BY name HAVING COUNT(1)>1)
DELETE FROM category
WHERE name IN
(SELECT NAME FROM ( SELECT name from category GROUP BY name HAVING COUNT(1)>1) AS T1 )
AND id NOT IN
(SELECT id FROM (SELECT MIN(id) id from category GROUP BY name HAVING COUNT(1)>1) AS T2)
CREATE TABLE province(id int PRIMARY KEY AUTO_INCREMENT,name varchar(50))
INSERT INTO province(name) SELECT DISTINCT province FROM student;
UPDATE student INNER JOIN province ON student.province=province.name
SET student.province=province.id
ALTER TABLE student
CHANGE COLUMN `province` `province_id` int(11);