MySQL学习第二天

MySQL 学习第二天

文章目录

• MySQL 学习第二天
• • 1. DQL(后续)
  • • 1. 排序查询
  • 2. 聚合函数
  • • 2.1 解决排除null值计算的问题
  • 3. 分组查询
  • 4. 分页查询
  • 5. 约束
  • • 5.1 非空约束（not null）
    • • 5.1.1 删除表的非空约束
      • 5.1.2 添加表的非空约束
    • 5.2 唯一约束(unique)
    • • 注意点
      • 5.2.1 在创建表的时候添加唯一性约束
      • 5.2.2 删除表的唯一性约束
      • 5.2.3 添加表的唯一性约束
    • 5.3 主键约束(primary key)
    • • 5.3.1 主键约束的概念
      • 5.3.2 在创建表的时候添加主键约束
      • 5.3.3 删除主键约束
      • 5.3.4 添加主键约束
      • 5.3.5 主键约束_自动增长
      • • 注意点
        • 删除自动增长
        • 添加自动增长
    • 5.4 外键约束
    • • 解决方案1(实际上不能解决，看似可以,两张表之间并没有关联)
      • 5.4.1 外键约束的语法
      • • 从表（多方，被别人约束的表，使用别人的数据（存在外键））
        • 主表（一方，用来约束别人的表）
        • 添加记录
      • 5.4.2 删除外键
      • 5.4.3 添加外键
      • 5.4.4 添加级联操作
      • • 级联更新（on update cascade）
        • 级联删除
  • 6. 表与表之间的关系
  • 7.一个旅游案例
  • 8.数据库范式
  • 9.数据库的备份与还原

1. DQL(后续)

• 排序查询
• 聚合函数
• 分组查询
• 分页查询

1. 排序查询

• 语法

order by 字段1 排序方式1,字段2 排序方式2...

• 排序方式

  • 升序：ASC(默认)
  • 降序：DESC

• 升序（默认）、
  

• 降序

• 升序排列，第一排序条件（math）相同时，按第二排序（english）条件，即数学成绩相同时，再按英语成绩排序

2. 聚合函数

• 将一列数据作为一个整体，进行纵向的计算。

  • count：计算个数
  • max：计算最大值
  • min：计算最小值
  • sum：计算和
  • avg：计算平均值

• 聚合函数的计算排除了null值

• 计算前的表信息

• count函数计算（排除null值）english

• 不排除null值(没有null值的一列)

2.1 解决排除null值计算的问题

• 选择不包含null值的一列
• 使用 ifnull 函数
• count(*)不推荐

• count（*）

• max()
  

• min()

• sum()

• avg()

3. 分组查询

• 语法

group by 分组字段;

• 注意点

  • 分组之后的select的字段要么是分组字段，要么是聚合函数

  • where 和 having的区别

    • where在分组之前进行限定，如果不满足条件，则不参与分组
    • having在分组之后进行限定，如果不满足条件，则不会被查询出来
    • where 后不可以跟聚合函数的判断，而having可以

• 示例：按性别进行分组，查询平均分和人数

• 示例：按性别进行分组，但数学成绩低于70的不参与分组

• 示例：having的使用

• 示例：给聚合函数起别名，便于判断

4. 分页查询

• 语法（是一个MySQL的关键字，只能在MySQL中使用）

limit 开始的索引,每页查询的条数;

• 一个公式

开始的索引=（当前的页码-1）*每页显示的条数

• 示例

5. 约束

• 概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性
• 分类
  • 主键约束：primary key
  • 非空约束：not null
  • 唯一约束：unique
  • 外键约束：foreign key

5.1 非空约束（not null）

• 某一列的值不能为空

-- 创建一个新的表
 
CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL -- 非空约束
);
 
 
 
SELECT *FROM stu;
 
INSERT INTO stu(id,NAME) VALUES(1,'李华');
 
INSERT INTO stu(id,NAME) VALUES(2,NULL);

• 效果图

5.1.1 删除表的非空约束

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

• 效果图

5.1.2 添加表的非空约束

-- 添加表的非空约束
 
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

5.2 唯一约束(unique)

• 某一列的值不能重复

注意点

• 唯一性约束可以有null值，但是只能有一条记录为null

5.2.1 在创建表的时候添加唯一性约束

-- 创建表示添加唯一约束
 
CREATE TABLE stu(
	id INT,
	phoneNumber VARCHAR(20) UNIQUE -- 唯一约束，手机号不能重复
);
 

• 效果图：多次添加重复的手机号

5.2.2 删除表的唯一性约束

-- 删除唯一性约束
 
ALTER TABLE stu DROP INDEX phoneNumber;

5.2.3 添加表的唯一性约束

-- 添加表的唯一性约束
 
ALTER TABLE stu MODIFY phoneNumber VARCHAR(20) UNIQUE;

5.3 主键约束(primary key)

5.3.1 主键约束的概念

• 非空且唯一
• 主键就是表中记录的唯一标识

5.3.2 在创建表的时候添加主键约束

CREATE TABLE stu(
	id INT PRIMARY KEY, -- 添加主键约束
	NAME VARCHAR(20)
);

• 唯一

• 非空

5.3.3 删除主键约束

-- 删除主键
 
ALTER TABLE stu DROP PRIMARY KEY;

5.3.4 添加主键约束

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

5.3.5 主键约束_自动增长

• 概念：如果某一列是数值类型的，使用auto_increment 可以来完成值的自动增长（配合int类型的主键使用）

• 在创建表的时候添加自动增长

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

• 插入数据，自动增长

注意点

• 自动增长是相当于上一条记录自动增长

删除自动增长

alter table stu modify id int;

添加自动增长

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

5.4 外键约束

• 单表的缺点

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    dep_name VARCHAR(30),
    dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

• 效果图

• 缺点：数据冗余（研发部，广州）和（销售部，深圳）

解决方案1(实际上不能解决，看似可以,两张表之间并没有关联)

-- 解决方案：分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
    id int primary key auto_increment,
    dep_name varchar(20),
    dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int -- 外键对应主表的主键
);
-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;
-- 添加员工,dep_id 表示员工所在的部门
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;
 

5.4.1 外键约束的语法

• 在创建表的时候添加外键约束，即主表和从表产生关联

• 语法

constraint 外键约束名称 foreign key (外键字段名) references 主表名(主键字段名);

从表（多方，被别人约束的表，使用别人的数据（存在外键））

 
-- 员工从表，部门数据在部门表（主表）
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT,
    dep_id INT, -- 外键对应主表的主键
    
);

主表（一方，用来约束别人的表）

-- 主表 部门表
CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dep_name VARCHAR(20),
    dep_location VARCHAR(20)
);

添加记录

-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
 
-- 添加员工,dep_id 表示员工所在的部门
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);

• 删除主表的id为1的记录错误效果图

5.4.2 删除外键

-- 删除外键
 
ALTER TABLE employee DROP FOREIGN KEY emp_dep_id;

5.4.3 添加外键

-- 添加外键
 
ALTER TABLE employee ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(id);

5.4.4 添加级联操作

• 概念：修改和删除主表的主键时，同时更新和删除从表的外键值
• 级联更新：修改主表的主键，从表的外键也改变
• 级联删除：删除主表的一个记录，从表的外键值是主表的主键值的记录被删除

级联更新（on update cascade）

CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE

• 效果图

级联删除

CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE  -- 这里不仅有级联更新，也有级联删除

6. 表与表之间的关系

1. 多表之间的关系
   1. 分类：
      1. 一对一(了解)：
         • 如：人和身份证
         • 分析：一个人只有一个身份证，一个身份证只能对应一个人
      2. 一对多(多对一)：
         • 如：部门和员工
         • 分析：一个部门有多个员工，一个员工只能对应一个部门
      3. 多对多：
         • 如：学生和课程
         • 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
   2. 实现关系：

      1. 一对多(多对一)：

         • 如：部门和员工
         • 实现方式：在多的一方建立外键，指向一的一方的主键。
         • 图解

         

      2. 多对多：

         • 如：学生和课程
         • 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
         • 图解

         

      3. 一对一(了解)：

         • 如：人和身份证
         • 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。
         • 图解

         

7.一个旅游案例

 
 
-- 创建一个旅游分类表
 
CREATE TABLE cat_tours(
	cid INT PRIMARY KEY AUTO_INCREMENT,  -- 旅游分类id
	cname VARCHAR(20) NOT NULL UNIQUE   -- 旅游分类的类名
);
 
 
-- 旅游分类(1)和旅游线路（n）
 
-- 旅游线路添加外键
 
-- 创建一个旅游线路表
 
CREATE TABLE tours_routes(
	rid INT PRIMARY KEY AUTO_INCREMENT, -- 旅游线路的id
	rname VARCHAR(20) NOT NULL UNIQUE,   -- 旅游线路的名称
	price DOUBLE(5,2),        -- 旅游线路的价格
	cid INT,                 -- 外键
	CONSTRAINT rid_cid FOREIGN KEY(cid) REFERENCES cat_tours(cid) -- 添加外键约束
	
);
 
 
-- 创建一个用户表
 
-- 用户（n）和旅游线路（m）
 
-- 使用第3张表
 
CREATE TABLE users(
	uid INT PRIMARY KEY AUTO_INCREMENT,
	uname VARCHAR(20) NOT NULL UNIQUE,
	psw VARCHAR(20) NOT NULL
	
);
 
 
CREATE TABLE users_link_routes(
	uid INT,
	rid INT,
	col_time DATETIME,
	
	-- 联合主键
	PRIMARY KEY(uid,rid),
	
	CONSTRAINT l_uid FOREIGN KEY(uid) REFERENCES users(uid),
	CONSTRAINT l_rid FOREIGN KEY(rid) REFERENCES tours_routes(rid)
 
);

8.数据库范式

2. 数据库设计的范式

   • 概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

     设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
     目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

   • 分类：

     1. 第一范式（1NF）：每一列都是不可分割的原子数据项

     2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

        • 几个概念：
          1. 函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
             例如：学号–>姓名。 （学号，课程名称） --> 分数
          2. 完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
             例如：（学号，课程名称） --> 分数
          3. 部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
             例如：（学号，课程名称） – > 姓名
          4. 传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
             例如：学号–>系名，系名–>系主任
          5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
             例如：该表中码为：（学号，课程名称）
             • 主属性：码属性组中的所有属性
             • 非主属性：除过码属性组的属性

     3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

• 图解

9.数据库的备份与还原

1. 命令行：
   • 语法：
     • 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
     • 还原：
       1. 登录数据库
       2. 创建数据库(之前备份的，如果删除)
       3. 使用数据库(刚才创建的)
       4. 执行文件。source 文件路径
2. 图形化工具：