MYSQL查询（模型关系、查询数据）

学习目标：

Mysql学习一、

---

学习内容：

1、模型关系
2、查询数据

---

1、模型关系

• 主键：对于关系表，是个很重要的约束，就是任意两条记录不能重复，不能重复不是指两条记录不完全相同，而是指能够通过某个字段(主键)唯一区分出不同的记录，主键是关系表中记录的唯一标识，主键也不应该允许NULL
• 外键：一个关系表的其中一列，作为另一个关系表的主键，形成一对多的关系，这个连接两个表的同一列就是外键
• 多对多：两个表通过一个第三个表(关系表)，进行连接形成多对多的关系
• 索引：关系数据库中对某一列或多个列的值进行预排序的数据结构

---

2、查询数据

1、基本查询

• SELECT * FROM 表名 ：*代表所有，这句语句就是查询表的所有数据

eg：SELECT * FROM student;（查询student表的所有数据）

• SELECT 也可以用来计算

eg：SELECT 100+200;

2、条件查询
SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录

• SELECT * FROM <表名> WHERE <条件表达式>

eg：SELECT * FROM students WHERE score >= 80; （查询student表，并且查找分数大于等于80的同学）

• SELECT * FROM <表名> WHERE <条件1> AND <条件2>

eg： SELECT * FROM students WHERE score >= 80 AND gender = ‘M’; （查询student表，并且找到分数大于的等于80的男生）

• SELECT * FROM <表名> WHERE <条件1> OR <条件2>

eg:SELECT * FROM students WHERE score >= 80 OR gender = ‘M’; （查询student表，并且找到分数大于的等于80或者男生满足条件之一即可）

• SELECT * FROM <表名> WHERE NOT <条件>

SELECT * FROM students WHERE NOT score >=80; （查询student表，除了分数大于等于80的男生）

• SELECT * FROM <表名> WHERE (条件语句)

eg：SELECT * FROM students WHERE ( score < 80 OR score > 90 ) AND gender = ‘M’;（查询student表，查找分数在80分以下和分数在90分以上的男生）

NOT优先级最高，其次是AND，最后是OR，between

3、投影查询
希望返回某些列的数据，而不是所有列的数据，让结果集仅包含指定列

• SELECT 列1, 列2, 列3 FROM …

SELECT id, score, name FROM students;（查询student表中的id、score、name）

• SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM …

SELECT id, score points, name FROM students; (查询student表中的id、score、name并且给score重新取名points)

• 配合WHERE使用

SELECT id, score points, name FROM students WHERE gender = ‘M’; （查询student表中男生的id、score和name，并且把score改为points）

4、排序
可以将输出的结果进行某种排序

• ORDER BY （ASC）：默认按照升序排列——ASC可以省略

SELECT id, name, gender, score FROM students ORDER BY score;（查询student表中的id、name、gender、score并且按照score升序排列）

• DESC：表示倒序排序

SELECT id, name, gender, score FROM students ORDER BY score DESC;（查询student表中的id、name、gender、score并且按照score倒序排列）

• ORDER BY 列1 DESC, 列2：先按列1进行倒序，如果有相同的再按列2进行倒序排列

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender; (查询student表中的id、name、gender、score并且按照score倒序排列，如果有相同的再按gender倒序)

• 当用上WHERE时，ORDER BY 需要排在WHERE后面

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
(查询student表中一班学生的id、name、gender、score并按倒序排列)

5、分页查询
使用SELECT查询时，如果结果集数据量很大，需要分页显示

• LIMIT OFFSET ：从N号开始，最多取M条

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
(查询student表中学生的id、name、gender和score按倒序排列，并且分页显示三条，这是第一页)

• 以此类推

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
(查询student表中学生的id、name、gender和score按倒序排列，并且分页显示三条，这是第二页)

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0
LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15
LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低

6、聚合查询
对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果

• 内置COUNT()函数：可以计算表的一共多少条记录

SELECT COUNT(*) FROM students; (计算查询student表中有多少条记录)

• COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)，使用聚合查询时，我们一般给列名设置一个别名

SELECT COUNT(*) num FROM students; (计算查询student表中有多少条记录,并给输出取了一个名num)

• COUNT(*)和COUNT(id)是一样的效果，聚合查询同样可以使用WHERE条件

SELECT COUNT(*) boys FROM students WHERE gender = ‘M’; (统计数student表中有多少男生)

• 其他函数
  SUM：计算某一列的合计值，该列必须为数值类型
  AVG：计算某一列的平均值，该列必须为数值类型
  MAX：计算某一列的最大值
  MIN：计算某一列的最小值

SELECT AVG(score) average FROM student WHERE gender = ‘M’; (查询计算student表中男生平均成绩)

• 聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

SELECT AVG(score) average FROM students WHERE gender = ‘X’; (X匹配不到任何行时就会返回NULL)

• 分组聚合按照班级进行分组

SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id; (查询students表的所有信息并按照班级分组)

• 分组聚合按class_id, gender分组
  SELECT class_id,gender,COUNT(*) num FROM students GROUP BY class_id,gender; (查询students表的所有信息并按照班级和性别分组)

7、多表查询(笛卡尔查询)
SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据,结果集的列数是表1和表2的列数之和，行数是表1和表2的行数之积

• SELECT * FROM <表1> <表2>

SELECT * FROM students, classes; (从表students和class表中查询)

• 多表查询，并使用投影查询取别名

SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
(从students表中和class表中查询信息)

• 多表查询时，要使用表名.列名这样的方式来引用列和设置别名，就避免了结果集的列名重复问题

SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c;
(从students表中和class表中查询信息)

• FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>,多表查询添加WHERE条件

SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c
WHERE s.gender = ‘M’ AND c.id = 1;
（分别查询students表和class表中的s.gender和c.id,并且取别名）

8、连接查询

• 连接查询是另一种类型的多表查询，连接查询对多个表进行JOIN运算，把其他表的行有选择性地“连接”在主表结果集上

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
把students表和class表联系起来查询信息

• 注意INNER JOIN查询的写法是：
  先确定主表，仍然使用FROM <表1>的语法
  再确定需要连接的表，使用INNER JOIN <表2>的语法
  然后确定连接条件，使用ON <条件…>，如果条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接
  可选：加上WHERE子句、ORDER BY等子句
• 有内连接就有外连接

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

INNER JOIN:只会返回连接表相同的部分
RIGHT JOIN:不仅会返回相同部分还会返回右表全部的部分
LEFT JOIN:不仅会返回相同部分还会返回左表全部的部分

• 使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，自动把对方不存在的列填充为NULL

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;