学习目标:
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;