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				--> 对结果限制