今日学习内容

  1. DQL查询语句

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

1 DQL 查询语句

1.1 排序查询

  • 语法:order by 子句

    -- 通过ORDER BY子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序)
    SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
    ASC: 升序,默认值
    DESC: 降序
    
    eg:
    -- 查询所有数据,使用年龄降序排序
    SELECT * FROM student ORDER BY age DESC;
    
    -- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序
    SELECT * FROM student ORDER BY age DESC, math ASC;
  • 注意:

    • 如果有多个排序条件,第1个条件相等时,按第2个条件进行排序,依次类推

1.2 聚合函数

  • 概念:将一列数据作为一个整体,进行纵向的计算

    聚合函数作用
    max(列名)求这一列的最大值
    min(列名)求这一列的最小值
    avg(列名)求这一列的平均值
    count(列名)统计这一列有多少条数据
    sum(列名)求这一列的总和
  • 注意:

    • 聚合函数的计算,排除null值
    • 解决方案:

      • 选择不包含非空的列进行计算
      • IFNULL函数
-- 查询学生总数
SELECT COUNT(id) AS 总人数 FROM student;
SELECT COUNT(*) AS 总人数 FROM student;
-- 查询id字段,如果为null,则使用0代替
SELECT IFNULL(id,0) FROM student;
-- 查询年龄大于20的总数
SELECT COUNT(*) FROM student WHERE age>20;
-- 查询数学成绩总分
SELECT SUM(math) 总分 FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) 平均分 FROM student;
-- 查询数学成绩最高分
SELECT MAX(math) 最高分 FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) 最低分 FROM student;

1.3 分组查询

  • 语法:group by 分组字段;

    分组查询是指使用 GROUP BY语句对查询信息进行分组,相同数据作为一组
    SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
  • GROUP BY将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。

    -- 按性别进行分组,求男生和女生数学的平均分
    SELECT sex, AVG(math) FROM student GROUP BY sex;
    -- 查询男女各多少人
    SELECT sex, COUNT(*) FROM student3 GROUP BY sex;
  • 注意:

    where 和 having 的区别

    子句名作用
    where1) 对查询结果进行分组前,将不符合where条件的行去掉,即先过滤后分组
    2) where 后面不可以使用聚合函数
    having1) having 子句的作用是筛选满足条件的租,即先分组后过滤
    2) having 后面可以使用聚合函数
    • 分组之后查询的字段:分组字段、聚合函数

1.4 分页查询

  • LIMIT是限制的意思,所以LIMIT的作用就是限制查询记录的条数。
  • 语法:limit 开始的索引,每页查询的条数;

    SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];
  • 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数

    --- 每页显示3条记录 
    
    SELECT * FROM student LIMIT 0,3; -- 第1页
    
    SELECT * FROM student LIMIT 3,3; -- 第2页
    
    SELECT * FROM student LIMIT 6,3; -- 第3页
  • limit 是一个MySQL"方言"

2 约束

  • 概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。
  • 分类

    • 主键约束:primary key
    • 非空约束:not null
    • 唯一约束:unique
    • 外键约束:foreign key

2.1 非空约束:not null

  • 在创建表时,添加约束

    -- 创建表添加非空约束
    CREATE TABLE stu(
        id INT,
        NAME VARCHAR(20) NOT NULL -- name 为非空
    )
  • 创建表完后,添加非空约束

    -- 创建表完后,添加非空约束
    ALTER TABLE stu MODIFY NAME vatchar(20) NOT NULL;
  • 删除name的非空约束

    -- 删除name的非空约束
    ALTER TABLE stu MODIFY NAME VARCHAR(20);

2.2 唯一约束:unique,值不能重复

  • 创建表格时,添加唯一约束

    -- 创建表格时,添加唯一约束
    CREATE TABLE stu(
        id INT,
        phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
    );
  • 删除唯一约束

    -- 删除唯一约束
    ALTER TABLE stu DROP INDEX phone_number;
  • 在创建表后,添加唯一约束

    -- 在创建表后,添加唯一约束
    ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

2.3 主键约束:primary key

  • 注意:

    • 含义:非空且唯一
    • 一张表只能有一个字段为主键
    • 主键就是表中记录的唯一标识
  • 在创建表时,添加主键约束

    -- 在创建表时,添加主键约束
    CREATE TABLE stu(
        id INT PRIMARY KEY, -- 给id添加主键约束
        NAME VARCHAR(20)
    );
  • 删除主键

    -- 删除主键
    ALTER TABLE stu MODIFY id INT; -- 错误方式
    ALTER TABLE stu DROP PRIMARY KEY; -- 正确方式
  • 创建完表后,添加主键

    -- 创建完表后,再添加主键
    ALTER TABLE stu MODIFY id INT PRIMARY KEY;

2.3 自动增长:auto_increment

  • 概念:

    • 如果某一列是数值类型的,使用 auto_increment 可以来完成值的自动增长
    • 一般和主键一起使用
  • 在创建表时,添加主键约束,并且完成主键自增长

    CREATE TABLE stu(
        id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束
        NAME VARCHAR(20)
    );
  • 删除自动增长

    -- 删除自动增长
    ALTER TABLE stu MODIFY id INT;
  • 添加自动增长

    -- 添加自动增长 
    ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

2.4 外键约束:foreign key

外键约束.png

  • 让表与表产生关系,保证数据的正确性
  • 在创建表时,可以添加外键

    • 语法
    [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
    
    eg:
    CREATE TABLE employee(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20),
        age INT,
        dep_id INT, -- 外键对应主表的主键
        -- 创建外键约束
        CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id)
    )
  • 删除外键

    -- 删除外键
    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  • 在创建表之后,添加外键

    -- 添加外键
    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES department(主表列名称);
  • 具体操作

    -- 1) 删除副表/从表 employee
    drop table employee;
    18 / 26
    -- 2) 创建从表 employee 并添加外键约束emp_depid_fk
    -- 多方,从表
    create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    -- 创建外键约束
    constraint emp_depid_fk foreign key (dep_id) references department(id)
    )
    -- 3) 正常添加数据
    INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
    INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
    select * from employee;
    -- 4) 部门错误的数据添加失败
    -- 插入不存在的部门
    -- Cannot add or update a child row: a foreign key constraint fails
    INSERT INTO employee (NAME, age, dep_id) VALUES ('老张', 18, 6);

2.5 级联操作

  • 什么是级联操作:
    在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作

    级联操作语法
    
    ON UPDATE CASCADE
    -- 级联更新,只能是创建表的时候创建级联关系。更新主表中的主键,从表中的外键列也自动同步更新
    ON DELETE CASCADE
    -- 级联删除
  • 添加级联操作

    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES department(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
  • 注意

    1. 实际工作中,级联删除,禁制使用
    2. 实际工作中,对于外键,我们一般会有一个原则"建立逻辑外键,而不建立实际外键"

3 数据库的设计

3.1 多表之间的关系

1.分类

  • 一对一:

    • 如:人和身份证
    • 分析:一个人只有一个身份证,一个身份证只能对应一个人
  • 一对多(多对一):

    • 如:部门和员工
    • 分析:一个部门有多个员工,一个员工只能对应一个部门
  • 多对多:

    • 如:学生和课程
    • 分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择

2.实现关系

  • 一对多(多对一):

    • 如:部门和员工
    • 实现方式:在多的一方建立外键,指向另方的主键
  • 多对多:

    • 如:学生和课程
    • 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个外键,分别指向多对多双方的主键
  • 一对一:

    • 如:人和身份证
    • 实现方式:一对一关系实现,可以在任意一方添加"唯一外键"指向另一方的主键

3.2 数据库设计的范式

  • 概念:设计数据库时,需要遵循的一些规范

    第1范式(1NF):每一列都是不可分割的原子数据项
    第2范式(2NF):在第1范式的基础上,消除部分依赖,也要消除非依赖。
    第3范式(3NF):在第2范式的基础上,消除传递依赖。

4 数据库的备份和还原

4.1 备份应用的场景

  • 在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补与估量的。

4.2 命令行的方式

  • 语法

    • 备份

      mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
      
      eg:
      -- 备份day21数据库中的数据到d:\day21.sql文件中
      mysqldump -uroot -proot day21 > d:/day21.sql
    • 还原

      1. 登录数据库
      2. 创建数据库
      3. 使用数据库
      4. 执行文件:source 文件路径

        use day21;
        source d:/day21.sql;