Mysql练习_MySQL练习(一)

聚合函数:

COUNT(列名) :统计指定列不为null的行数 。

MAX(列名): 获得指定列中的最大值 , 如果是字符串 , 按照字符顺序 。

MIN(列名): 获得最小值 。

SUM(列名): 计算指定列的和值 , 计算非数字,结果是 0 。

AVG(列名): 计算指定列的平均值 , 计算非数字 , 结果是 0 。

聚合函数语法: select 聚合函数 from 表名 [where ...];

创建 stu 表:

CREATE TABLE `stu` (

`sid` int(11) DEFAULT NULL,

`sname` varchar(25) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`sex` char(6) DEFAULT NULL,

`score` int(11) DEFAULT NULL,

`cid` int(11) DEFAULT NULL,

`groupLeaderId` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

往 stu 表中添加数据:

INSERT INTO `stu` VALUES ('1001', '张三', '20', '男', '72', '1', '1003');

INSERT INTO `stu` VALUES ('1002', '李四', '15', '女', '78', '1', '1003');

INSERT INTO `stu` VALUES ('1003', '王五', '95', '男', '99', '1', '1010');

INSERT INTO `stu` VALUES ('1004', '赵六张', '65', '女', '60', '1', '1007');

INSERT INTO `stu` VALUES ('1005', '周七', '55', '男', '78', '3', '1007');

INSERT INTO `stu` VALUES ('1006', '茅十八', '75', '女', '96', '3', '1007');

INSERT INTO `stu` VALUES ('1007', '张三丰', '40', '男', '85', '3', '1010');

INSERT INTO `stu` VALUES ('1008', '李四方', '45', '女', '90', '2', '1010');

INSERT INTO `stu` VALUES ('1009', '艾三弗森', '45', '', '35', '4', '1008');

INSERT INTO `stu` VALUES ('1010', '三欧文', '35', '女', '49', '2', '1008');

创建 class 表:

CREATE TABLE `class` (

`cid` int(11) DEFAULT NULL,

`cname` varchar(255) COLLATE utf8_bin NOT NULL,

`caddress` varchar(255) COLLATE utf8_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

在 class 中添加数据:

INSERT INTO `class` VALUES ('1', 'BigData', '102');

INSERT INTO `class` VALUES ('2', 'HTML', '103');

INSERT INTO `class` VALUES ('3', 'VR', '104');

INSERT INTO `class` VALUES ('4', 'Java', '105');

查询 stu 表中记录数:

select count(sid) from stu;

将stu中sid为1001的score的值改为null:

update stu set score = null where sid = 1001;

计算stu中sex出现的次数:

select count(sex) from stu;

在列名后使用as给列取别名, as可以省略:

select count(sid) as'记录数' from stu;

select count(sid)'记录数' from stu;

查询stu表中有成绩的人数:

select count(score) '人数' from stu;

查询stu表中成绩大于60的人数:

select count(*) from stu where score > 60;

查询所有学生成绩和:

select sum(score) from stu;

select sum(score)'总成绩' from stu;

统计所有学生的平均成绩:

select AVG(score) from stu;

统计stu表中成绩大于60的平均分:

select AVG(score) from stu where score > 60;

查询最高成绩和最低成绩:

select MAX(score) '最高分' ,MIN(score)'最低分' from stu;

统计stu 表中成绩大于60的最高成绩和最低成绩:

select MAX(score) '最高分',MIN(score) '最低分' from stu where score > 60;

查询最高成绩,以及学生姓名:

与聚合函数一同出现的列名,必须出现在group by 后,反之,如果出现一个没有在group by 后出现的字段,那么查询结果不正常!

select MAx(score) '最高分', sname from stu;

查询年纪总和:

select SUM(age) from stu;

查询成绩总和:

select SUM(score) from stu;

计算总年龄与总成绩的和:

select SUM(age) + SUM(score) from stu;

select sum(age+score) from stu;

去重

去重函数 distinct(列)

将制定列的数据去重

不重复的年龄:

select distinct(age) from stu;

查询年龄不重复的共有多少人

select count(distinct age) from stu;

分组查询

当需要分组查询时,需要使用group by 语句。

语法:

select * from stu [where...] group by 字段[order by]

字符值相同的分到一组:

注意:

1.一般分组之后的操作的都是聚合操作

2.分成几组,虚拟表结果就是几行数据

3.聚合函数,是对每组进行单独聚合操作

与where的区别:

1. where是分组前过滤,having是分组后的过滤

2, where后不能使用聚合函数,having 可食用聚合函数

查询男生多少人,女生多少人?

select sex,count(*)'人数' from stu group by sex;

查询每个班级的班级编号和每个班级的成绩和:

select cid,SUM(score) from stu group by cid;

查询每个班级的班级编号以及每个班级的人数 .

select cid '班级' ,count(sid) '人数' from stu group by cid;

查询成绩总和大于200的班级编号以及成绩和 .

select cid,SUM(score) from stu group by cid having SUM >200;

查询成绩总和大于200的班级编号以及成绩和并根据成绩总和降序 .

select cid,sum(score) 'sum' from stu group by cid having sum > 200 order by sum asc;

限制输出

可以将查询好的结果,限制输出;

可以限制输出几条,从哪里输出;

语法:

在SQL语句集最后写limit offset,row_count

limit 开始下标,行数

例如: limit 0,2 —>从第一条输出,输出2条

输出前两条数据

select * from stu limit 0,2;

从第四条开始,输出三条

select * from stu limit 3,3;

分页: 每页3条数据

第一页:

select * from stu limit 0,3;

第二页:

select * from stu limit 3,3;

函数

流程函数

IF(expr1, expr2 , expr3)

如果expr1为真,则返回expr2,否则返回expr3,

isnull()函数: 判断是否为空;

isnull(字段): 如果是null,返回1,不是null返回0;

查询学生,id,姓名,成绩,如果成绩为null,显示缺考

select sid,aname,if(isnull(score)=1,'缺考',score) from stu;

is null : 是判断条件

isnull(): 是函数

修改stu中score为null的,并将null修改为72

update stu set score = 72 where score is null;

ifnull(expr1,expr2)如果expr1不是null,则放回expr1,否则返回expr2;

查询学生id,姓名,成绩,如果成绩为null,显示缺考

select sid,sname,ifnull(score,'缺考')'成绩' from stu;

case when [expr1] when [result1]...else[default] end:如果expr是真,返回result1,否则返回default;

示例:

case

when 条件 then 执行语句

when 条件 then 执行语句

...

else 执行语句

end

执行第一个when后的条件,如果是true,执行then后的语句;

如果when后的条件为false,执行第二,when后的条件

如果都是false,执行else后的语句

查询学生,id,姓名,成绩,以及等级(0-59:不及格; 60-69:中; 70-89 良; 90-100优);

select sid,sname,score, case

when score < 60 then '不及格'

when score < 70 then '中等'

when score < 90 then '良'

when isnull(score) = 1 then '缺考'

else '优秀'

end as '等级'

from stu;

类似于 switch case 结构

case

[value]

when [values] then [result]

...

else

[default]

end

如果value 等于value1,返回result1, 否则返回default

select case score

when 72 then '七十二'

when 78 then '七十八'

else '其他'

end

from stu;

总结:

书写语法:

select

selection_list--> 要查询的列

from

table_name--> 要查询的表名

where condition --> 过滤行条件

group by grouping_clumns--> 对结果按照列进行分组

having condition--> 分组后再过滤

order by sort_column--> 排序

limit offset,row_count--> 对结果限制

标签：练习,查询,stu,score,VALUES,MySQL,成绩,select

来源： https://blog.csdn.net/a1422655169/article/details/112384335