mysql sum 条件_MySQL学习记录

• mySQL，Navicat的安装
• 熟悉创建表，更改设置的基本操作的练习

1.1准备

1. 安装MySQL

mysql是数据库的管理系统。官网下载安装，直接next，除了密码验证选择旧版本5.7....的方式是第二个（如果没注意全是默认第一个Next请看3.），安装完成之后，就会弹出以下界面吗，确认安装成功。

如果不小心关闭这个，从开始菜单里搜索MySQL 8.0 Command Line Client - Unicode

2.可视化工具Navicat

这个软件与mysql建立联结之后，就可以把这些语句转化成表单式操作，十分简便。

安装完成之后点击mysql，输入密码连接就可以用了。

3.如果连接不上，报错显示2059，也就是密码验证方式问题，旧版mysql连接完全没问题，只需要把验证改成旧版的，怎么改呢？参考https://blog.csdn.net/pan_zzq/article/details/81078564

此时此刻，我们已经有了前面的步骤，直接从3.3步骤开始：

 use mysql;
 select user,plugin from user where user ='root';

更改加密方式：

ALTER USER'root'@'localhost' IDENTIFIED BY '替换password' PASSWORD EXPIRE NEVER;

更新用户密码：

ALTER USER'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; 

分别执行后如下，重新执行3.3查询结果如下，加密方式已经该改变。

3.5 刷新权限：FLUSH PRIVILEGES;

3.6 重新打开Navicat Premium 12，可连接成功。

1.2练习

查询数据

关于ex3:

开始简单的练习很简单，点一点就知道怎么回事了。

另外，新建表的时候会设置列的数据类型，如果要更改，鼠标右键，设计表即可。

下面是输入语句的操作。

---

• 接下来开始select基本语句练习
• where查询
• 练习题

2.1语句学习

2.2练习

必备练习网站，十分简便：SQLZOO

• 错题：

---

• 汇总：count,sum,avg,max,min,（对数据）order by（直接对结果排序）desc,asc，limit
• 分组：group by,having

3.1如何从数据库调取有用的信息呢？

1. 比如有时候需要知道符合要求数据多少行
2. 比如涉及求平均值，需要排序
3. 比如数据一对多

分析思路是先把要求精确化，然后写出对应函数，再根据条件完成语句。这个类似分析汉语的句子。

我 （user） 吃了（select查询） 三块以上（条件1） 大大的 （条件2） 西瓜（查询结果）。

例子：

统计 每门课程的学生选修人数( 超过2人的课程才统计)，要求输出 课程号和 选修人数
查询结果按人数降序排序，若人数相同，按课程号升序排序。

• 课程号，选修人数------select from
• 每门课程------ group by
• 超过两人------having,count
• 结果降序------orderby desc,asc

select 课程号, count(学号) as '选修人数'
from score
group by 课程号
having count(学号)>2
order by count(学号) desc,课程号 asc;

3.2练习

---

• 视图
• 子查询
• 标量子查询
• 关联子查询
• 常用函数汇总图

4.1子查询

理论和规则：把一个查询分解成一系列的逻辑步骤。提供一个列表作为WHERE子句和 IN / ANY / ALL 的目标对象。嵌套子查询只朝一个方向进行——要么返回一个而用于外部查询的值，要么返回一个与IN运算符一起使用的的值的列表。嵌套子查询中，内部查询只处理一次。

如何理解？

select语句要分三次查询，一次查的结果返回值作为另一次的where语句条件。

用AS 新表名好处是简洁。

关系数据库的设计基础知识：

• 重复数据出现多次不是一件好事（更改不便、浪费空间、纰漏），就是要把信息拆解成多个表，一类数据一个表。通过共同的值关联，也叫联结。
• 联结两个表的时候实际上是把表1的每一行与表2的每一行配对。where作为筛选条件，只包含匹配关联条件的行，没有where的话，每一行交叉出笛卡尔积结果，逻辑上就乱了。
• where x. continent= y.continent,列名限定，然后句点起到隔断，免得逻辑混乱，DBMS报错。

4.2练习

关联子查询还需要再练习

---

• 交叉联结，内联结，左连接，右联结（带函数）,case
• MySQL突然连不上错误代码2003（计算机--属性---服务---mysql---手动启动）

5.1联结的语法和功能图：

内联结：a,b表各抽取部分信息

左联结：对左边的表格联结，按表一来补充数据。把左边的数据全部取出。

5.2case

用于自定义的那种分组，比如筛选各科不及格人数

select 课程号,
sum(case  when 成绩<60  then 1 
         else 0 
          end) as 不及格人数,
sum(case when 成绩>=60 then 1
            else 0 
          end ) as 及格人数
from course 
group by 课程号;

select a.课程号,b.课程名称,
SUM(case when 成绩>60 then 1 else 0 end)as '<60',
sum(case when 60<=成绩 and 成绩<70 then 1 else 0 end)as '70-60',
sum(case when 成绩>=70 and 成绩<85 then 1 else 0 end)as '85-70',
sum(case when 成绩>=85 and 成绩<=100 then 1 else 0 end)as '100-85'
from course as a  right join score  as b
on a.课程号=b.课程号
group by a.课程号,b.`课程名称`; -- select 列名只能是group by 里面的

5.3sqlzoo错题

不等号！=

错误示范:distinct

第二种解法

这里是left join 不是join

---

• 简单查询
• 汇总分析
• 复杂查询
• 多表查询
• *行列转换综合应用

6.1语句

• 查询姓小的学生姓名

• 查询名字是红的学生姓名

• 查询名字有红的姓名

• 查询姓孟的老师姓名

• 查询0002课程的总成绩

• 查询选课同学人数

• 查询各科最高成绩，最低成绩

• 查询各科人数

• 统计男女人数

• 查询平均成绩大于60的学生和平均成绩

• 统计学生人数大于2人的学科

• 查询同名同姓的学生姓名

6.2*行列转换

【面试题类型总结】这类题目属于行列如何互换，解题思路如下：

【解答】

1）第1步，使用常量列输出目标表的结构

可以看到查询结果已经和目标表非常接近了

select 学号,'课程号0001','课程号0002','课程号0003'
from score;

2）第2步，使用case表达式，替换常量列为对应的成绩

select 学号,
(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',
(case 课程号 when '0002' then 成绩 else 0 end) as  '课程号0002',
(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'
from score;

在这个查询结果中，每一行表示了某个学生某一门课程的成绩。比如第一行是'学号0001'选修'课程号00001'的成绩，而其他两列的'课程号0002'和'课程号0003'成绩为0。每个学生选修某门课程的成绩在下图的每个方块内。我们可以通过分组，取出每门课程的成绩。

3）第3步，分组

分组，并使用最大值函数max取出上图每个方块里的最大值

select 学号,
max(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',
max(case 课程号 when '0002' then 成绩 else 0 end) as '课程号0002',
max(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'
from score
group by 学号

结果：