MySQL数据管理（二）

- 日理万妓 2024-04-29 11:06 54阅读 0赞

## 学习目标： ##

熟练使用完整性约束

熟练使用DML语句实现数据增删改

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

## 学习内容： ##

## 1.数据完整性 ##

### 数据完整性1-1 ###

\#数据库中的数据是从外界输入的，而数据的输入由于种种原因，会发生输入无效或错误信息。保证输入的数据符合规定，成为了数据库系统，尤其是多用户的关系数据库系统首要关注的问题。

\#它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

![4dfc524e79b14fdda666a73f3d245c58.png][]

### 数据完整性1-2 ###

数据完整性主要分为三类：实体完整性、域完整性、引用完整性。

![eee78dcbd4184ee281dc605d5032a2ef.png][]

### 实体完整性 ###

实体完整性是指保证表中所有的行唯一。

![f4ceb0716b0b455ba87314cb4e563863.png][]

![7c066293941d48c5aead074327a2be90.png][]

![c5f35c6fbbec4fb7af6de0a8d1ff9afd.png][]

显而易见这种是不能加上的

### 域完整性 ###

域完整性是指数据库表中的列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。

![1e99cd63d025499fa11eabf0f8751d49.png][]

### 引用完整性 ###

引用完整性是指保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。它涉及两个或两个以上表数据的一致性维护。

![66ee3643cb2a48f3b78f6eeeed2181d1.png][]

## 2.使用约束实现数据完整性 ##

数据库采用多种方法来保证数据完整性，包括约束、规则和触发器。

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;约束类型</td> 
   <td>关键字</td> 
   <td>说明</td> 
  </tr> 
  <tr> 
   <td>非空约束</td> 
   <td>NOT NULL</td> 
   <td>如果字段不允许为空，则需要设置NOT NULL 约束。</td> 
  </tr> 
  <tr> 
   <td>默认约束</td> 
   <td>DEFAULT&nbsp;</td> 
   <td>赋予某字段默认值，如果该字段没有赋值，则其值为默认值</td> 
  </tr> 
  <tr> 
   <td>唯一约束</td> 
   <td>UNIQUE KEY</td> 
   <td>设置改字段的值唯一的，允许为空</td> 
  </tr> 
  <tr> 
   <td>主键约束</td> 
   <td>PRIMARY KEY</td> 
   <td>设置该字段为表的主键，可以作为该表记录的唯一标识</td> 
  </tr> 
  <tr> 
   <td>外键约束</td> 
   <td>FOREIGN KEY&nbsp;</td> 
   <td>用于在两表之间建立联系，需要引用主表的哪一字段</td> 
  </tr> 
 </tbody> 
</table>

## 3.在数据表上添加约束 ##

语法

ALTER TABLE 表名
    ADD CONSTRAINT 约束名 约束关键字 具体的约束说明

注意 约束名的取名推荐采用：约束类型\_约束字段

（1）主键（Primary Key）约束：如PK\_stuNo

（2）唯一（Unique ）约束：如 UQ\_stuID

（3）外键(Foreign Key)约束：如 FK\_stuNo

### 3.1设置唯一约束 ###

语法

ALTER TABLE 表名
    ADD CONSTRAINT 约束名 UNIQUE(列名)

示例

ALTER TABLE STUDENT 
    ADD CONSTRAINT uq_stuid UNIQUE(identitycard)

使用场景：该字段的值是唯一的。

### 3.2使用SQLyog添加数据验证唯一约束的作用 ###

![05975d776c704c4fab9ef4402216adff.png][]

## 4.主键 ##

### 主键（PRIMARY KEY）4-1 ###

数据库主键：指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。

数据库主键的作用

1）保证实体的完整性;

2）加快数据库的操作速度；

3）添加新记录时，DBMS会自动检查新记录的主键值，不允许该值与其他记录的主键值重复；

4) DBMS自动按主键值的顺序显示记录。如果没有定义主键，则按输入记录的顺序显示记录

注意

1：一个表只能有一个主键，并且主键列不允许出现空值。

2：尽管有的表中允许没有主键，但是通常情况下，建议为表设置主键。

### 主键（PRIMARY KEY）4-2 ###

数据库主键的选择规则

1）最少性是指列数最少的键。如果可以从单个主键和复合主键中选择，应该选择单个主键，这是因为操作一列比操作多列要快。当然该规则也有例外，例如，两个整数类型的列的组合比一个很大的字符类型的列操作要快。

2）稳定性是指列中数据的特征。由于主键通常用来在两个表之间建立联系，所以主键的数据不能经常更新。理想情况下，应该永远不变。

如果两列或多列组合起来唯一地标识表中的每个行，则该主键叫做“复合主键”。

### 4.3设置主键约束 ###

语法

ALTER TABLE 表名
    ADD CONSTRAINT 约束名 PRIMARY KEY(列名1，列名2,......列名n)

示例

ALTER TABLE STUDENT 
    ADD CONSTRAINT PK_STUDENTNO PRIMARY KEY (STUDENTNO)

使用场景：如果一个字段即要求唯一又不能为null，可使用主键约束

### 4.4使用SQLyog添加数据验证主键约束的作用 ###

![0498e1fe08f14f9ca661dac9fae9905e.png][]

## 5.外键（FOREIGN KEY） ##

在数据库设计中，学生的信息和学生的考试成绩是存放在不同的数据表中。在成绩表中，可以存储学生的学号来表示是哪个学生的考试成绩，这又引发一个问题：“如果成绩表中输入的学号根本不存在或者把学号写错了” ，该怎么办 ？

这个时候，就应当建立一种“引用”的关系，确保“从表”中的某个数据项在“主表”中必须存在，以避免上述错误发生。

“外键”就是用来达到这个目的的，它是相对于主键而言的，就是“从表”中对应于“主表”中的列，在从表中称为外键或引用键，它的值要求与主表的主键相对应。

“外键”用来强制引用完整性。一个表可以有多个外键。

### 5.1设置外键约束 ###

语法

ALTER TABLE 从表名
    ADD CONSTRAINT 约束名 FOREIGN KEY(从表的列名)
    REFERENCES 主表名（主表的列名）

示例

ALTER TABLE RESULT 
    ADD CONSTRAINT fk_studentno FOREIGN KEY(studentno)
    REFERENCES student(studentno)

提示

主表中的字段必须为主键。

从表中的外键列和主表中的主键列的数据类型要保持一致，和字段名无关。

主表和从表的表类型为InnoDB。

## 6.删除约束 ##

语法

ALTER TABLE 表名 DROP PRIMARY KEY;#删除主键约束
    ALTER TABLE 表名 DROP KEY 唯一约束名;#删除唯一约束
    ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;#删除外键约束

示例

ALTER TABLE student 
    DROP FOREIGN KEY fk_studentno

使用场景：如果错误地添加了约束，还可以删除约束 。

### 7.数据库数据管理 ###

### 数据库意义 ###

数据存储

数据管理

### 管理数据库数据方法 ###

通过DML语句管理数据库数据

通过SQLyog等管理工具管理数据库数据

## 8.DML语言 ##

DML（数据操作语言）：用于操作数据库对象中所包含的数据。

DML包括：

INSERT ( 添加数据语句 )

UPDATE ( 更新数据语句 )

DELETE ( 删除数据语句 )

### 添加数据8-1 ###

#### 插入单行数据 ####

INSERT INTO 表名 [（字段一，字段2,字段3,...）] VALUES(值1,值2,值3,...);

注意

表的字段是可选的，如果省略，则依次插入所有的字段。

如果插入的是表中部分列的数据，字段名列表必须填写。

多个字段和多个值之间使用逗号分隔。

值列表必须和字段名列表数量相同且数据类型相符（字符串和日期类型的值要加单引号）。

值列表中的数据必须符合数据完整性的要求

### 添加数据8-2 ###

#### 插入多行数据 ####

语法

INSERT INTO 表名 [(字段1，字段2，字段3,..)] VALUES (值1,值2,值3,...),(值1,值2,值3,...)...;

示例

INSERT INTO `grade` (`gradeid`,`gradename`) VALUES (1,’S1’), (2,’S2’), (3,’Y2’);

###    添加数据8-3 ###

语法

CREATE TABLE 新表名 (SELECT 字段1,字段2,字段3,...FROM 原表)

示例

CREATE TABLE stuhistory (SELECT*FROM student)

注意：以上示例在执行查询操作的同时创建新表stuhistory，无须提前创建。

### 8.4修改数据 ###

#### UPDATE命令 ####

UPDATE 表名
    SET column_name = value[,column_name2 = value2,...] [WHERE condition];

注意

column\_name 为要更改的字段名。

value 为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果。

多个字段之间用逗号隔开。

提供的修改数据必须符合数据完整性的要求。

condition为筛选条件，如不指定则修改该表的所有列数据。

### SQL语句中的运算符2-1 ###

算术运算符

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>运算符</td> 
   <td>含义</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>加法运算，求两个数或表达式相加的和</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>减法运算，求两个数或表达式相减的差</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>乘法运算，求两个数或表达式相乘的积</td> 
  </tr> 
  <tr> 
   <td>/</td> 
   <td>除法运算，求两个数或表达式相除的商</td> 
  </tr> 
  <tr> 
   <td>%</td> 
   <td> <p>取模运算，求两个数或表达式相除的余数</p> </td> 
  </tr> 
 </tbody> 
</table>

算术运算符

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>运算符</td> 
   <td>含义</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>把一个数或变量或表达式赋值给另一个变量</td> 
  </tr> 
 </tbody> 
</table>

### SQL语句中的运算符2-2 ###

比较运算符

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>运算符</td> 
   <td>含义</td> 
   <td>范例</td> 
   <td>结果</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
   <td>5=6</td> 
   <td>false</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt; 或 !=</td> 
   <td>不等于</td> 
   <td>5!=6</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
   <td>5&gt;6</td> 
   <td>false</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
   <td>5&lt;6</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
   <td>5&gt;=6</td> 
   <td>false</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
   <td>5&lt;=6</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>BETWEEN</td> 
   <td>在某个范围之间</td> 
   <td>BETWEEN 5 AND 10 &nbsp;</td> 
   <td>-</td> 
  </tr> 
 </tbody> 
</table>

逻辑运算符

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>运算符</td> 
   <td>含义</td> 
   <td>范例</td> 
   <td>结果</td> 
  </tr> 
  <tr> 
   <td>AND</td> 
   <td>并且</td> 
   <td>5&gt;1 AND 1&gt;2</td> 
   <td>false</td> 
  </tr> 
  <tr> 
   <td>OR</td> 
   <td>或</td> 
   <td>5&gt;1 OR 1&gt;2</td> 
   <td>true</td> 
  </tr> 
  <tr> 
   <td>NOT</td> 
   <td>取反</td> 
   <td>NOT 1&gt;2</td> 
   <td>true</td> 
  </tr> 
 </tbody> 
</table>

### 8.5删除数据 ###

#### 1：DELETE命令 ####

DELETE FROM 表名 [WHERE condetion];

注意

DELETE语句删除的是整条记录，不会只删除单个列。

condition为筛选条件，如不指定则修改该表的所有列数据。

#### 2：TRUNCATE TABLE 命令 ####

TRUNCHTE TABLE 表名;

注意

TRUNCATE TABLE 删除表中所有的行。

TRUNCATE TABLE不能用于有外键约束引用的表。

## 9.数据库的备份 ##

-- 备份一个表的所有行的所有字段
    create table `student_bak` select * from `student`;
    -- 备份所有行的某些字段
    create table `student_bak` select `id`,`name`,`sex`,`classno` from `student`;
    -- 备份某些行的某些字段
    create table `student_bak` select `id`,`name`,`sex`,`classno` from `student` where id in (2,5,7,8);

## 10.以上所学知识的代码演示 ##

DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `ID` int(10) PRIMARY key auto_increment NOT NULL COMMENT '学号，主键列',
      `NAME` varchar(16) not null UNIQUE key COMMENT '学生姓名，不能为空',
      `AGE` int(10) unsigned DEFAULT NULL COMMENT '学生年龄',
      `SEX` char(1) NOT NULL DEFAULT '男' COMMENT '性别',
      `BIRTHDAY` date DEFAULT NULL COMMENT '学生生日',
      `PHONE` varchar(11) DEFAULT NULL COMMENT '学生联系方式',
      `ADDRESS` varchar(64) DEFAULT NULL COMMENT '家庭地址',
      `CLASSNO` int(3) unsigned zerofill DEFAULT NULL COMMENT '所在班级编号',
      `EMAIL` varchar(64) DEFAULT NULL COMMENT '邮箱'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='KD42班学生信息表';
    
    -- ALTER TABLE 表名  ADD CONSTRAINT 约束名  约束关键字  具体的约束说明
    -- 对已经创建好的表设置主键，一般不这样用，一般情况都是在创建表的时候就设置好主键
    -- 并且如果字段不是主键，不能给该字段设置自动递增
    ALTER TABLE `student`  ADD CONSTRAINT pk_id  PRIMARY KEY(id)
    -- 添加唯一约束
    ALTER TABLE `student`  ADD CONSTRAINT uq_name UNIQUE KEY(name)
    
    -- 删除唯一约束，如果唯一约束没有取名字或者是在创建表的时候写在行内的唯一约束，默认使用字段名作为约束名的
    ALTER TABLE `student`  DROP KEY `NAME`;  #删除唯一约束
    
    
    -- 添加
    -- 添加时，如果主键列设置了自动递增，则可以直接赋值null/default，在添加数据时，会自动取自动递增的那个值作为主键值
    INSERT INTO `STUDENT` VALUES (NULL,'admin',19,'男','2003-10-19',
    '13545698754','郑州市金水区',42,'13545698754@163.com');
    INSERT INTO `STUDENT` VALUES (default,'admin',19,'男','2003-10-19',
    '13545698754','郑州市金水区',42,'13545698754@163.com');
    INSERT INTO `STUDENT` VALUES (default,'root',19,DEFAULT,'2003-10-19',
    '13545698754','郑州市金水区',42,'13545698754@163.com');
    INSERT INTO `STUDENT` VALUES (default,'张三',19,'男生','2003-10-19',
    '13545698754','郑州市金水区',42,'13545698754@163.com'); # 报错Data too long for column 'SEX' at row 1
    -- 如果添加数据时，表名后面不写字段名，则需要按照表中的字段顺序，给所有的字段都赋值，否则会报错
    INSERT INTO `STUDENT` VALUES (NULL,'admin',19,'男','2003-10-19',
    '13545698754','郑州市金水区',42); #报错Column count doesn't match value count at row 1
    -- 添加数据时，不为空的字段是必须要进行赋值的，否则会报错
    -- 通常添加数据时，需要写清楚要添加的那些字段，若设置了添加字段的话，字段顺序可变，但是要求值的顺序跟字段的顺序保持一致
    INSERT INTO `STUDENT` (`NAME`,`SEX`,`CLASSNO`) VALUES ('李四','女','42');
    INSERT INTO `STUDENT` (`NAME`,`CLASSNO`,`SEX`) VALUES ('王五','42','女');
    -- 同时添加多条数据
    INSERT INTO `STUDENT` (`NAME`,`CLASSNO`,`SEX`) VALUES ('赵家康','42','男'),('任恒威','42','男'),('杨峻易','42','男');
    -- 备份一个表的所有行的所有字段
    create table `student_bak` select * from `student`;
    -- 备份所有行的某些字段
    create table `student_bak` select `id`,`name`,`sex`,`classno` from `student`;
    -- 备份某些行的某些字段
    create table `student_bak` select `id`,`name`,`sex`,`classno` from `student` where id in (2,5,7,8);
    
    
    -- 修改  (注意，一般来说修改都是要带上条件的，否则就会触发全表修改)
    update `student` set `age`=18 # 没有条件，所有的行的年龄都会被修改为18
    
    update `student` set `age`=20,classno=41,sex='女' where `name`='root';
    
    update `student` set `address`='郑州市中原区' where sex='男';
    
    update `student` set classno = 38 where age < 18;
    
    update `student` set address='惠济区' where classno in (38,41);
    
    update `student` set address='二七区' where classno not in (38,41);
    
    update `student` set address = '高新区' where (age-5)<15;
    
    update `student` set age=age+3 where address='二七区';
    
    update `student` set address='经济开发区' where id>=3 and id<=5;
    
    update `student` set address='郑东新区' where id BETWEEN 3 and 5;
    
    -- 删除   删除的条件用法与修改的条件用法一模一样,
    delete from `student` where `name` = 'admin';
    
    delete from `student` where `id` = 3;
    
    -- 如果删除不加条件是删除表中所有的数据，但是自增序列的值不会重置
    delete from `student`;
    
    -- 清空表 TRUNCATE TABLE   如果使用TRUNCATE TABLE，不仅会清空表中的数据，还会把序列的值重置
    TRUNCATE `student`;

[4dfc524e79b14fdda666a73f3d245c58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/507a96d2b2944d8cac470ab80bfab512.png
[eee78dcbd4184ee281dc605d5032a2ef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/c5537a9f68564236a56186b285616334.png
[f4ceb0716b0b455ba87314cb4e563863.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/383554ec25a54c11bec3e3dd6bd969cd.png
[7c066293941d48c5aead074327a2be90.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/0e3984437b2449f1b848bcee557010da.png
[c5f35c6fbbec4fb7af6de0a8d1ff9afd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/ed6a780657b4462d8bb00ce5d515e5b7.png
[1e99cd63d025499fa11eabf0f8751d49.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/11fbbd4bc49040bdbc39193910ce098c.png
[66ee3643cb2a48f3b78f6eeeed2181d1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/7e1d9f79887a4a198201b355e5e14c0c.png
[05975d776c704c4fab9ef4402216adff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/96594dfa09cd41fba994585b6bc12123.png
[0498e1fe08f14f9ca661dac9fae9905e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/29/627bc68253c04d878b101ce84245e35a.png