2022/07/24、25 day03/04：数据库2：DQL与约束

今日内容

1. DQL查询语句
2. 排序查询
3. 聚合函数
4. 分组查询
5. 分页查询
6. 约束
7. 多表之间的关系
8. 范式
9. 数据库的备份和还原

DQL:查询语句

1. 排序查询

1. 排序查询

   • 语法：order by 子句

     • order by 排序字段1 排序方式1 ， 排序字段2 排序方式2…

   • 排序方式：

     • ASC：升序，默认的。
     • DESC：降序。

   • 注意：

     • 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

2. 聚合函数

2. 聚合函数：将一列数据作为一个整体，进行纵向的计算。

   1. count：计算个数
      1. 一般选择非空的列：主键
      2. count(*)
   2. max：计算最大值
   3. min：计算最小值
   4. sum：计算和
   5. avg：计算平均值
   • 注意：聚合函数的计算，排除null值。
     解决方案：
     1. 选择不包含非空的列进行计算
     2. IFNULL函数

3. 分组查询

3. 分组查询:
   1. 语法：group by 分组字段；
   2. 注意：
      1. 分组之后查询的字段：分组字段、聚合函数
      2. where 和 having 的区别？
         1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
         2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

4. 分页查询

4. 分页查询
   1. 语法：limit 开始的索引,每页查询的条数;
   2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
   3. limit 是一个MySQL"方言"

SQL语句

# 1. 排序查询
USE test;
SELECT * FROM student;
//默认以升序 ASC
SELECT * FROM student ORDER BY math;-- 以math排序
//DESC 降序排序
SELECT * FROM student ORDER BY math DESC;

//按照数学成绩排名，如果数学成绩一样，再按照英语成绩排序（升序）
SELECT * FROM student ORDER BY math,english;


-----------------------------------------------------------


# 2. 聚合函数（纵向计算）
-- 2.1 统计个数
//纵向计算表中一共有多少个人（对象行）
SELECT COUNT(id) FROM student;
//聚合函数【排除null】值-- null值不会被计算进去
SELECT COUNT(english) FROM student;
//解决方案1：替换null语句
SELECT COUNT(IFNULL(english,0)) FROM student;
//解决方案2：count(*)-- 但是*并不对推荐使用
-- 所有横向有一条不为null，就算一条记录
SELECT COUNT(*) FROM student;
//解决方案3：统计不能为null的列 
-- 例如ID或是姓名【主键】
SELECT COUNT(id) FROM student;


-- 2.2 计算最大值最、最小值、求和及平均值
//计算数学成绩的最大值
SELECT MAX(math) FROM student;
//计算数学成绩的最小值
SELECT MIN(math) FROM student;
//计算数学成绩列的和
SELECT SUM(math) FROM student;
//计算数学成绩的平均值
SELECT AVG(math) FROM student;
//计算英语成绩的平均值
SELECT AVG(IFNULL(english,0)) FROM student;


# 3. 分组查询
//按照性别分组，分别查询男、女同学的数学的平均分
SELECT sex 性别, AVG(math) 数学平均值 FROM student GROUP BY sex;
//按照性别分组，统计人数|平均分
SELECT sex 性别,COUNT(id) 人数 , AVG(math) 数学平均值 FROM student GROUP BY sex;
//按照性别分组，统计人数|平均分，【低于70分的不参与分组】
SELECT sex 性别,COUNT(id) 人数 , AVG(math) 数学平均值 FROM student WHERE math>70 GROUP BY sex;
//【对分组之后的结果集进行限定】，分组之后，人数要大于3个人
SELECT sex 性别,COUNT(id) 人数 , AVG(math) 数学平均值 FROM student WHERE math>70 GROUP BY sex HAVING 人数>3;



# 4. 分页查询
-- limit 是一个MySQL‘方言’（其他数据库不认）
//每页显示3条记录，现在看第一页
SELECT * FROM student LIMIT 0,3;-- 第二页
SELECT * FROM student LIMIT 3,3;-- 第二页
//索引公式：开始的索引=(当前的页码 - 1) * 每页显示的条数
SELECT * FROM student LIMIT 9,3;-- 第三页

约束

• 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。

• 分类：

  1. 主键约束：primary key
  2. 非空约束：not null
  3. 唯一约束：unique
  4. 外键约束：foreign key

• 非空约束：not null，值不能为null

• 唯一约束：unique，值不能重复

2. 约束（分类）
  1. 主键约束：primary KEY
  2. 非空约束：not NULL
  3. 唯一约束：unique
  4. 外键约束：foreign KEY
  
# 1. 非空约束:not null，值不能为空
-- 先学简单的


-- 创建表时添加非空约束
//创建一个表  
CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL -- name为非空
	);
//查看表数据
SELECT * FROM stu;
//删除name上的约束（就是修改表这一列字段）
ALTER TABLE stu MODIFY NAME VARCHAR(20);

-- 创建表后添加非空约束
//再次为name添加约束（就是修改表这一列字段）
-- 注意：如果表中数据已经有null，则会出错
-- 先删除null列
DELETE FROM stu WHERE id = 3;
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;



# 2. 唯一约束：unique，值不能重复
//删除表stu
DROP TABLE stu;
-- 创建表时添加唯一约束
CREATE TABLE stu(
	id INT UNIQUE,
	NAME VARCHAR(20) NOT NULL
);
//查看表数据
SELECT * FROM stu;
-- 注意 null表示不确定，不会被指定为重复
//删除ID上的唯一约束（也是修改表这一列字段）
    //ALTER TABLE stu MODIFY id INT ; 
    -- 删除非空约束的方式不能删除唯一约束
ALTER TABLE stu DROP INDEX id;-- 修改表删除索引

-- 创建表后添加唯一约束
//再次为ID添加约束（就是修改表这一列字段）
-- 注意：如果表中数据已经有ID重复，则会出错
-- 先修改ID重复的列的ID
UPDATE stu SET id = 3 WHERE NAME = "张无忌";
ALTER TABLE stu MODIFY id INT UNIQUE;



```                                                                                   ——此文档为学习笔记！