MySQL约束课堂笔记

待我称王封你为后i 2023-06-19 14:25 33阅读 0赞

# 今日内容 #

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

## 1.DQL:查询语句(重点掌握) ##

### 1.1 排序查询 ###

*  **语法**
    
        SELECT 字段名 FROM 表名 [WHERE条件] ORDER BY 字段名 [ASC|DESC];
    
     *  ASC: 升序，默认值
     *  DESC: 降序
 *  **注意**
    
     *  如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
 *  **例子**
    
     *  查询所有数据,使用年龄降序排序
        
            select * from student order by age desc;
     *  查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
        
            select * from student order by age desc, math asc;
 *  **补充案例**
    
     *  计算math 和 english总成绩 并按照总成绩降序排序
        
            SELECT NAME , math + IFNULL(english,0)  score FROM stu ORDER BY score DESC;
 *  **order by 总结**
    
     *  **order by 子句后可以支持那些内容？**
        
            表中的字段  函数
     *  **order by 执行顺序？**
        
            最后再执行  除了limit语句1

### 1.2 聚合函数 ###

*  **什么是sql函数**
    
    当我们学习编程语言的时候，经常会遇到函数。函数的好处是，它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了编写代码的效率，又提高了可维护性。在sql中函数主要要对数据进行处理！
 *  **常用的sql函数**
    
     *  算术函数
     *  字符串函数
     *  日期函数
     *  转换函数
     *  聚合函数/聚集函数
 *  **语法**
    
        SELECT 聚合函数(列名) FROM 表名;
 *  **五个聚合函数**
    
    <table> 
     <thead> 
      <tr> 
       <th>聚合函数</th> 
       <th>说明</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>count(*) | count(主键)</td> 
       <td>计算表中的总记录数</td> 
      </tr> 
      <tr> 
       <td>max</td> 
       <td>计算最大值</td> 
      </tr> 
      <tr> 
       <td>min</td> 
       <td>计算最小值</td> 
      </tr> 
      <tr> 
       <td>sum</td> 
       <td>计算和</td> 
      </tr> 
      <tr> 
       <td>avg</td> 
       <td>计算平均值</td> 
      </tr> 
     </tbody> 
    </table>
 *  **注意**：聚合函数的计算，排除null值。
 *  **解决方案**：
    
    1.  选择不包含非空的列进行计算
    2.  IFNULL函数
 *  **其他函数**
    
    <table> 
     <thead> 
      <tr> 
       <th>函数名</th> 
       <th>说明</th> 
       <th>作用</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>length(str)</td> 
       <td>字符函数</td> 
       <td>获取字符的字节个数</td> 
      </tr> 
      <tr> 
       <td>upper(str)</td> 
       <td>字符函数</td> 
       <td>将字符转换为大写字符</td> 
      </tr> 
      <tr> 
       <td>lower(str)</td> 
       <td>字符函数</td> 
       <td>将字符转换为小写字符</td> 
      </tr> 
      <tr> 
       <td>substring(str,pos)</td> 
       <td>字符函数</td> 
       <td>截取从指定索引处后面所有的字符</td> 
      </tr> 
      <tr> 
       <td>substring(str,pos,len)</td> 
       <td>字符函数</td> 
       <td>截取从pos索引开始截取len个字符</td> 
      </tr> 
      <tr> 
       <td>replace(str,from_str,to_str)</td> 
       <td>字符函数</td> 
       <td>将str中的字符 from_str字符替换成to_str字符</td> 
      </tr> 
      <tr> 
       <td>round(x)</td> 
       <td>数学函数</td> 
       <td>四舍五入</td> 
      </tr> 
      <tr> 
       <td>round(x,d)</td> 
       <td>数学函数</td> 
       <td>四舍五入 d:代表的是保留小数点后几位</td> 
      </tr> 
      <tr> 
       <td>cell(x)</td> 
       <td>数学函数</td> 
       <td>向上取整</td> 
      </tr> 
      <tr> 
       <td>floor(x)</td> 
       <td>数学函数</td> 
       <td>向下取整</td> 
      </tr> 
      <tr> 
       <td>mod(n,m)</td> 
       <td>数学函数</td> 
       <td>取余数 mod(10,3) 相当于: select 10 % 3</td> 
      </tr> 
      <tr> 
       <td>str_to_date(str,format)</td> 
       <td>日期函数</td> 
       <td>将日期字符转换成指定格式的日期 str_to_date(‘1990-11-11’,’%Y-%m-%d’);</td> 
      </tr> 
      <tr> 
       <td>date_format(date,format)</td> 
       <td>日期函数</td> 
       <td>将日期转换成字符 date_format(now(),’%Y/%m/%d’);</td> 
      </tr> 
     </tbody> 
    </table>
 *  **例子**:
    
     *  查询 id 字段，如果为 null，则使用 0 代替
        
            select ifnull(id,0) from student;
     *  查询总记录数-利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏
        
            select count(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 分组查询 ###

*  **语法**
    
        SELECT 字段 1,字段 2... FROM 表名 [where条件] GROUP BY 分组字段 [HAVING 条件] [order by];
 *  **注意**
    
     *  分组之后查询的字段：分组字段、聚合函数
     *  where 和 having 的区别？
        
         *  where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来 where 对基本的条件筛选
         *  where 后不可以跟聚合函数，having可以进行聚合函数的判断。
    
    where 操作的数据源: 原始表
    
    having 操作的数据源: 结果集
 *  **例子**
    
     *  按照性别分组。分别查询男、女同学的平均分
        
            SELECT sex , AVG(math) FROM student GROUP BY sex;
     *  按照性别分组。分别查询男、女同学的平均分,人数
        
            SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
     *  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
    
        SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
    
     *  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
        
            SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
            
            -- 最终写法
            SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
 *  **案例**
    
        按照address进行分组,并且math>60分的人员 , 分组完成之后,我只要人数>=2,并按照人数降序排序
        
        SELECT 
        	address,
        	COUNT(id) AS c
        FROM 
        	student
        WHERE
        	math > 60
        GROUP BY 
        	address
        HAVING 
        	c>=2
        ORDER BY
        	c DESC;
        	
        #######################
        SELECT address,COUNT(id) AS c
        FROM student
        WHERE math > 60
        GROUP BY address
        HAVING c>=1
        ORDER BY c DESC	;
    
    ### 1.4 分页查询 ###
 *  **语法**
    
        SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];
 *  **LIMIT语法格式**
    
     *  **格式**
        
            LIMIT offset,length;
        
         *  offset：起始行数，从 0 开始计数，如果省略，默认就是 0
         *  length： 返回的行数
 *  **计算公式**
    
        开始的索引 = （当前的页码 - 1） * 每页显示的条数
 *  **例子**
    
        -- 每页查询显示3条数据
        SELECT * FROM student LIMIT 0,3; -- 第1页
        SELECT * FROM student LIMIT 3,3; -- 第2页
        SELECT * FROM student LIMIT 6,3; -- 第3页

## 2. 约束 ##

### 2.1 约束简介 ###

#### 2.1.1 什么是约束 ####

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

#### 2.2.2 约束的分类 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">约束</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">primary key</td> 
   <td align="left">主键约束:非空且唯一</td> 
  </tr> 
  <tr> 
   <td align="left">not null</td> 
   <td align="left">非空约束 : 某一列的值不能为空</td> 
  </tr> 
  <tr> 
   <td align="left">unique</td> 
   <td align="left">唯一约束 : 某一列的值不能重复</td> 
  </tr> 
  <tr> 
   <td align="left">foreign key</td> 
   <td align="left">外键约束</td> 
  </tr> 
 </tbody> 
</table>

### 2.2 约束详解 ###

#### 2.2.1 非空约束 ####

create table 表名(

列名1 数据类型 约束,

);

*  **创建表时添加约束**
    
        CREATE TABLE stu(
        	id INT,
        	NAME VARCHAR(20) NOT NULL -- name为非空
        );
 *  **创建表完后，添加非空约束**
    
        ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
 *  **删除name的非空约束**
    
        ALTER TABLE stu MODIFY NAME VARCHAR(20);

#### 2.2.2 唯一约束 ####

*  **创建表时，添加唯一约束**
    
        CREATE TABLE stu(
         	id INT,
         	phone_number VARCHAR(20) UNIQUE -- 手机号
        );
    
     *  **注意**
        
         *  唯一约束可以有NULL值,也可以有多个null值
 *  **表创建完后，添加唯一约束**
    
        ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
 *  **删除唯一约束**
    
        ALTER TABLE stu DROP INDEX phone_number;

#### 2.2.3 主键约束 ####

*  **在创建表时，添加主键约束：primary key**
    
        create table stu(
        	id int primary key ,-- 给id添加主键约束
        	name varchar(20)
        );
 *  **注意**
    
     *  含义：非空且唯一
     *  一张表只能有一个字段为主键
     *  主键就是表中记录的唯一标识
 *  **创建完表后，添加主键**
    
        ALTER TABLE stu MODIFY id INT PRIMARY KEY;
 *  **创建完表后，删除主键**
    
        -- 错误 alter table stu modify id int ;
        ALTER TABLE stu DROP PRIMARY KEY;

#### 2.2.4 自动增长 ####

*  **概念**

如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

*  **在创建表时，添加主键约束，并且完成主键自增长**
    
        create table stu(
            id int primary key auto_increment,-- 给id添加主键约束
            name varchar(20)
        );
 *  **添加自动增长**
    
        ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
 *  **删除自动增长**
    
        ALTER TABLE stu MODIFY id INT;
 *  **综合案例**
    
        /*
          创建一个person表
        	pid 主键 自动增长
        	pname 不为空
        	phone_number 不为空,且唯一
        	address 不为空 
        	id_card 不为空,且唯一
        */
        
        -- 创建Person表
        CREATE TABLE person(
        	pid INT PRIMARY KEY AUTO_INCREMENT,
        	pname VARCHAR(30) NOT NULL,
        	phone_number VARCHAR(11) UNIQUE,
        	address VARCHAR(50) NOT NULL ,
        	id_card VARCHAR(20) NOT NULL UNIQUE
        );
        
        -- 添加一条数据
        INSERT INTO person(`name`,`phone_number`,`card_number`) VALUES 
        ('张三','13814381438','411411198311114123');

#### 2.2.4 外键约束 ####

*  **概念**
    
        foreign key,让表与表产生关系，从而保证数据的正确性。
 *  **数据准备**
    
        -- 创建部门表(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, -- 外键对应主表的主键
        	-- CONSTRAINT dept_emp_fk_id  可以省略不写
        	CONSTRAINT dept_emp_fk_id FOREIGN  KEY (dep_id) REFERENCES department (id)
        	 
        );
        
        
        -- 添加 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;
 *  **在创建表时，添加外键**
    
        -- 语法
        create table 表名(
            ....
            外键列
            constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
        );
 *  **创建表之后，添加外键**
    
        ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
 *  **删除外键**
    
        ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
 *  **级联操作**
    
        CREATE TABLE pp(
        	pid INT PRIMARY KEY AUTO_INCREMENT,
        	pname VARCHAR(30) NOT NULL,
        	phone VARCHAR(11) NOT NULL UNIQUE,
        	p_fk_id INT
        	-- 创建表的时候,添加外键和级联操作,两个级联操作可以同时加上,也可以单独加
        	CONSTRAINT pp_p_fk FOREIGN KEY (p_fk_id) REFERENCES p1(id) ON UPDATE CASCADE  ON DELETE CASCADE
        );
    
     *  **分类**
        
         *  级联更新：ON UPDATE CASCADE
         *  级联删除：ON DELETE CASCADE
     *  **添加级联**
        
            ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE
 *  总结:
    
     *  添加约束的时候,建议创建表的时候添加约束 , 不建议大家删除约束
     *  级联尽量避免设置级联 效率低
     *  设置外键 一般不设置外键(建立逻辑外键,不建立实际外键)

## 3.数据库的设计 ##

### 3.1 多表之间的关系(了解) ###

#### 3.1.1 分类 ####

*  **一对一(了解)**
    
        * 如：人和身份证
        * 分析：一个人只有一个身份证，一个身份证只能对应一个人
 *  **一对多(多对一)**

* 如：部门和员工
    * 分析：一个部门有多个员工，一个员工只能对应一个部门

*  **多对多**
    
        * 如：学生和课程
        * 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

#### 3.1.2 实现关系 ####

*  **一对多(多对一)**
    
        * 如：部门和员工
        * 实现方式：在多的一方建立外键，指向一的一方的主键。
 *  **多对多**
    
        * 如：学生和课程
        * 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
 *  **一对一(了解)**
    
        * 如：人和身份证
        * 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

#### 3.1.3 案例 ####

*  **创建旅游线路分类表 tab\_category**
    
        -- 创建旅游线路分类表 tab_category
        -- cid 旅游线路分类主键，自动增长
        -- cname 旅游线路分类名称非空，唯一，字符串 100
        CREATE TABLE tab_category (
            cid INT PRIMARY KEY AUTO_INCREMENT,
            cname VARCHAR(100) NOT NULL UNIQUE
        );
 *  **创建旅游线路表 tab\_route**
    
        -- rid 旅游线路主键，自动增长
        -- rname 旅游线路名称非空，唯一，字符串 100
        -- price 价格
        -- rdate 上架时间，日期类型
        -- cid 外键，所属分类
        CREATE TABLE tab_route(
            rid INT PRIMARY KEY AUTO_INCREMENT,
            rname VARCHAR(100) NOT NULL UNIQUE,
            price DOUBLE,
            rdate DATE,
            cid INT,
            FOREIGN KEY (cid) REFERENCES tab_category(cid)
        );
 *  **创建用户表 tab\_user**

-- uid 用户主键，自增长
    	-- username 用户名长度 100，唯一，非空
    	-- password 密码长度 30，非空
    	-- name 真实姓名长度 100
    	-- birthday 生日
    	-- sex 性别，定长字符串 1
    	-- telephone 手机号，字符串 11
    	-- email 邮箱，字符串长度 100
    	
        CREATE TABLE tab_user (
            uid INT PRIMARY KEY AUTO_INCREMENT,
            username VARCHAR(100) UNIQUE NOT NULL,
            PASSWORD VARCHAR(30) NOT NULL,
            NAME VARCHAR(100),
            birthday DATE,
            sex CHAR(1) DEFAULT '男',
            telephone VARCHAR(11),
            email VARCHAR(100)
        );

*  **收藏表 tab\_favorite**

-- rid 旅游线路 id，外键
    -- date 收藏时间
    -- uid 用户 id，外键
    -- rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
    
    CREATE TABLE tab_favorite (
        rid INT, -- 线路id
        DATE DATETIME,
        uid INT, -- 用户id
        -- 创建复合主键
        PRIMARY KEY(rid,uid), -- 联合主键
        FOREIGN KEY (rid) REFERENCES tab_route(rid),
        FOREIGN KEY(uid) REFERENCES tab_user(uid)
    );

*  总结:
    
     *  一个用户可以收藏多条线路 , 线路也可以被多个用户所收藏 用户与收藏线路表: 多对多的关系,必须建立中间表
     *  线路分类中可以有多条线路 线路分类表 和 收藏表 一对多的关系

### 3.2 数据库设计的范式 ###

*  **概念**
    
        设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求
        设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
        目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式)
 *  **分类**
    
     *  **第一范式（1NF）**：每一列都是不可分割的原子数据项
     *  **第二范式（2NF）**：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
        
         *  **概念**
            
             *  **函数依赖**：A–>B, 如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A  
                **例如**：学号–>姓名。 （学号，课程名称） --> 分数
             *  **完全函数依赖**：A–>B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
                
                **例如**：（学号，课程名称） --> 分数
             *  **部分函数依赖**：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。  
                **例如**：（学号，课程名称） – > 姓名
             *  **传递函数依赖**：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
            
            **例如**：学号–>系名，系名–>系主任
            
             *  **码**：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
                
                **例如**：该表中码为：（学号，课程名称）  
                **主属性**：码属性组中的所有属性  
                **非主属性**：除过码属性组的属性
     *  **第三范式（3NF）**：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

## 4.数据库的备份和还原 ##

### 4.1 使用命令行备份和还原 ###

*  **备份**
    
        mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
 *  **还原**
    
        1. 登录数据库
        2. 创建数据库
        3. 使用数据库
        4. 执行文件。source 文件路径

### 4.2 使用图形化工具 ###

1.  **选中要备份的数据库,右键选择【备份/导出】**

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eSjD28Pz-1575641076056)(图1.png)\]

1.  **然后在选择【备份数据库,转储到SQL】**

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qdQdZJ56-1575641076058)(图2.png)\]

1.  **在弹出的对话框中选择【结构和数据】在【Export to】选择保存的路径**

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PSSlRZBh-1575641076060)(图3.png)\]

1.  最后选择【导出】即可

定唯一C属性的值，则称 C 传递函数依赖于A

**例如**：学号-->系名，系名-->系主任
    
      * **码**：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
    
        **例如**：该表中码为：（学号，课程名称）
         **主属性**：码属性组中的所有属性
         **非主属性**：除过码属性组的属性

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