CERATE TABLE t_stu(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(200),
s_age INT,
s_phone BIGINT,
s_grade INT,
s_c_id int
);
SELEECT COUNT(ALL s_id) FROM t_stu; //查看多少人,统计,DISTINCT统计的时候可以去掉重复的数据,ALL默认可以省略不写
sum函数的使用
SELECT SUM(s_grade) FROM t_stu; //求总和
AVG函数的使用
SELECT AVG(t_grade) FROM t_stu; //求平均值,除去空值,空值不在计算之内。
SELECT MAX(s_grade)FROM t_stu;
SELECT MIN(s_grade)FROM t_stu;
SELECT s_name,s_age FROM t_stu;
分组查询
SELECT s_age FROM t_stu GROUP BY s_age;
SELECT s_c_id,COUNT(s_id) AS'班级人数' FROM t_stu GROUP BY s_c_id;
SELECT s_c_id,COUNT(*) AS'班级人数' FROM t_stu GROUP BY s_c_id
having子句
SELECT s_c_id,COUNT(*) FROM t_stu GROUP BY s_c_id HAVING COUNT(*)>2; //having子句的使用
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(200),
s_age INT,
s_phone BIGINT,
s_grade INT,
s_c_id int
);
SELEECT COUNT(ALL s_id) FROM t_stu; //查看多少人,统计,DISTINCT统计的时候可以去掉重复的数据,ALL默认可以省略不写
sum函数的使用
SELECT SUM(s_grade) FROM t_stu; //求总和
AVG函数的使用
SELECT AVG(t_grade) FROM t_stu; //求平均值,除去空值,空值不在计算之内。
SELECT MAX(s_grade)FROM t_stu;
SELECT MIN(s_grade)FROM t_stu;
SELECT s_name,s_age FROM t_stu;
分组查询
SELECT s_age FROM t_stu GROUP BY s_age;
SELECT s_c_id,COUNT(s_id) AS'班级人数' FROM t_stu GROUP BY s_c_id;
SELECT s_c_id,COUNT(*) AS'班级人数' FROM t_stu GROUP BY s_c_id
having子句
SELECT s_c_id,COUNT(*) FROM t_stu GROUP BY s_c_id HAVING COUNT(*)>2; //having子句的使用