MySQL约束与设计

分手后的思念是犯贱 2022-12-19 15:25 112阅读 0赞

# 1、DQL查询语句 #

## 1.1、排序 ##

通过ORDER BY 子句，可以将查询的结果进行排序(排序只是显示方式，不会影响数据库数据的顺序)

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 排序字段 ASC|DESC

ASC：升序，默认值

DESC：降序

## 1.1.1、单列排序 ##

就是只按某一个字段进行排序。

select * from students order by id asc;

## 1.1.2、组合排序 ##

同时对多个字段进行排序，如果第一个字段相等，则按第二个字段排序，以此类推。

SELECT 字段名 FROM 表名 ORDER BY 字段1 \[asc|desc\],字段2 \[asc|desc\];

select * from students order by id asc ,age desc;

## 1.2、聚合函数 ##

前面的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数是纵向查询，它是对一列的值进行计算，然后返回一个结果值，聚合函数都会忽略NULL值。

### 五个聚合函数 ###

<table> 
 <thead> 
  <tr> 
   <th>聚合函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>max(列名)</td> 
   <td>求这一列的最大值</td> 
  </tr> 
  <tr> 
   <td>min(列名)</td> 
   <td>求这一列的最小值</td> 
  </tr> 
  <tr> 
   <td>avg(列名)</td> 
   <td>求这一列的平均值</td> 
  </tr> 
  <tr> 
   <td>count(列名)</td> 
   <td>统计这一列有多少条记录</td> 
  </tr> 
  <tr> 
   <td>sum(列名)</td> 
   <td>对这一列进行求和</td> 
  </tr> 
 </tbody> 
</table>

语法：  
SELECT 聚合函数(列名) FROM 表名;

聚合函数对于NULL的记录不会统计，但是如果需要把NULL也统计进去怎么办？

IFNULL(列名,默认值) 如果列名不为空，返回列值，如果为NULL，则返回默认值

select ifnull(age,0) from students;
    
    select count(ifnull(age,0)) from students;

select count(age) from students;
    
    select max(age) from students;
    
    select min(age) from students;
    
    select sum(age) from students;
    
    select avg(age) from students;
    
    select max(stuName) from students;
    
    select min(stuName) from students;

## 1.3、分组 ##

分组查询是指使用GROUP BY 语句对查询信息进行分组，相同数据作为一组。

SELECT 字段1,字段2,… FROM 表名 GROUP BY 分组字段 \[HAVING 条件\]

-- 根据年龄分组，查询每组的人数
    select count(id),age from students group by age;
    
    select count(id),birthday From students group by birthday;
    
    -- 根据年龄分组，查询每组的人数，以及平均年龄>15
    select count(id),age from students   group by age having avg(age)>20;

### having与where的区别 ###

<table> 
 <thead> 
  <tr> 
   <th>名</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>where子句</td> 
   <td>对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组<br>where后面不可以使用聚合函数</td> 
  </tr> 
  <tr> 
   <td>having子句</td> 
   <td>having子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤<br>having后面可以使用聚合函数</td> 
  </tr> 
 </tbody> 
</table>

## 1.4、limit语句 ##

limit是限制的意思，所以limit的作用是限制查询记录的条数

### 语法格式 ###

SELECT \*|字段列表 \[as 别名\] FROM 表名 \[WHERE 子句\] \[GROUP BY 子句\]\[HAVING 子句\]\[ORDER BY 子句\] \[limit 子句\]

LIMIT offset,length;  
offset：起始行数，从0开始计数，如果省略，默认就是0  
length：返回的行数

select \* from students limit 2,2;

**LIMIT的使用场景：分页查询**

# 2、数据库备份和还原 #

## 2.1、备份的应用场景 ##

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

## 2.2、备份与还原的语句 ##

### 2.2.1、备份格式 ###

DOS下，不要登录MYSQL数据库，执行：  
mysqldump –u用户名 –p密码 数据库>文件的路径

### 2.2.2、还原格式 ###

mysql中的命令，需要登录后才可以操作  
USE 数据库;  
SOURCE 导入文件的路径;

备份
    mysqldump -uroot -pRootbdit628@# db1>e:\db1.sql
    还原，需要登录数据库
    use db1;
    source e:\db1.sql

# 3、数据库表的约束 #

## 3.1、约束概述 ##

### 3.1.1、约束作用 ###

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

### 3.1.2、约束分类 ###

<table> 
 <thead> 
  <tr> 
   <th>约束名</th> 
   <th>约束关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键</td> 
   <td>primary key</td> 
  </tr> 
  <tr> 
   <td>唯一</td> 
   <td>unique</td> 
  </tr> 
  <tr> 
   <td>非空</td> 
   <td>not null</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>foreign key</td> 
  </tr> 
  <tr> 
   <td>检查约束</td> 
   <td>check</td> 
  </tr> 
 </tbody> 
</table>

## 3.2、主键约束 ##

### 3.2.1、主键作用 ###

用来唯一标识数据库中的每一条记录。

### 3.2.2、那个字段应该做为表的主键 ###

通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。  
如：身份证，学号等都不建议做成主键

### 3.2.3、创建主键 ###

主键关键字：primary key

主键的特点：非空、唯一

**创建主键的方式：**

1、在创建表的时候给字段添加主键

字段名 字段类型 primary key

2、在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名)

-- 给已存在的表中的字段添加主键
    alter table students add primary key(id);
    
    create table teachers(
     id int primary key,
     name varchar(20)
    );
    
    insert into teachers(id,name) values(1001,'张三');

### 3.2.4、删除主键约束 ###

alter table 表名 drop primary key;

### 3.2.5、主键自增 ###

主键如果让我们自己添加很有可能重复，我们希望每次插入新纪录时，数据库自动生成主键字段的值。

在mysql数据库中，可以通过AUTO\_INCREMENT在创建表的时候指定主键自增。AUTO\_INCREMENT默认值是1

create table teachers(
     id int primary key auto_increment,
     name varchar(20)
    );

### 3.2.6、修改AUTO\_INCREMENT的默认值 ###

创建表时指定起始值：

create table teachers(
     id int primary key auto_increment,
     name varchar(20)
    )AUTO_INCREMENT=起始值;

创建好以后修改起始值：  
ALTER TABLE 表名 AUTO\_INCREMENT=起始值;

### 3.2.7 、DELETE和TRUNCATE对自增的影响 ###

DELETE,删除所有的记录之后，自增没有影响

TRUNCATE,删除以后，自增又重新开始

## 3.3、唯一约束 ##

就是表中某一列不能出现重复的值

### 唯一约束的基本格式 ###

字段名 字段类型 UNIQUE

-- 唯一约束
    create table teachers(
     id int primary key auto_increment,
     name varchar(20) unique
    )auto_increment=1001;
    insert into teachers(name) values('张三');
    insert into teachers(name) values('李四');

## 3.4、非空约束 ##

就是某一列不能为null

### 3.4.1、 基本语法 ###

字段名 字段类型 NOT NULL

create table teachers(
     id int primary key auto_increment,
     name varchar(20) unique,
     age int not null
    )auto_increment=1001;
    insert into teachers(name) values('张三');

### 3.4.2 、默认值 ###

字段名 字段类型 DEFAULT 默认值;

create table teachers(
     id int primary key auto_increment,
     name varchar(20) unique,
     age int not null default 0
    )auto_increment=1001;
    insert into teachers(name) values('张三');

如果一个字段设置了非空与唯一约束，该字段与主键的区别？

1、主键在一个表中，只能有一个，不能出现多个主键，主键可以是单列，也可以是多列（复合主键）

2、自增长只能用在主键上

## 3.5、外键约束 ##

### 3.5.1、单表的特点 ###

创建一个员工表包含(id,name,age,dept\_name,dept\_location)，id主键并且自增

use db1;
    CREATE TABLE emp(
     id INT PRIMARY KEY auto_increment,
     name varchar(20),
     age int,
     dept_name varchar(30),
     dept_location varchar(30)
    );
    
    INSERT INTO emp(name,age,dept_name,dept_location) values('张三',20,'开发部','北京');
    INSERT INTO emp(name,age,dept_name,dept_location) values('李四',21,'开发部','北京');
    INSERT INTO emp(name,age,dept_name,dept_location) values('王五',22,'开发部','北京');
    INSERT INTO emp(name,age,dept_name,dept_location) values('赵六',20,'销售部','上海');
    INSERT INTO emp(name,age,dept_name,dept_location) values('田七',21,'销售部','上海');
    INSERT INTO emp(name,age,dept_name,dept_location) values('小明',22,'销售部','上海');

以上数据表的缺点：数据冗余、后期还会出现增删改的问题

### 3.5.2、解决方案 ###

分成两张表，分别是员工表(emp)、部门表(dept)

-- 部门表  主表
    CREATE TABLE dept(
     id int primary key auto_increment,
     dept_name varchar(30),
     dept_location varchar(30)
    );
    
    -- 员工表
    CREATE TABLE employee(
     id int primary key auto_increment,
     name varchar(20),
     age int,
     dept_id int
    );
    
    -- 添加部门
    INSERT INTO dept(dept_name,dept_location) values('开发部','北京');
    INSERT INTO dept(dept_name,dept_location) values('销售部','上海');
    
    -- 添加员工
    INSERT INTO employee(name,age,dept_id) values('张三',20,1);
    INSERT INTO employee(name,age,dept_id) values('李四',21,1);
    INSERT INTO employee(name,age,dept_id) values('王五',22,1);
    INSERT INTO employee(name,age,dept_id) values('赵六',20,2);
    INSERT INTO employee(name,age,dept_id) values('田七',21,2);
    INSERT INTO employee(name,age,dept_id) values('小明',22,2);
    
    select * from employee;

问题：当我们在employee的dept\_id里面输入不存在的部门，数据依然可以添加，但是并没有对应的部门。实际应用中不能出现这种情况，employee的dept\_id中的数据只能是dept表中存在的id。

目标：需要约束employee中的dept\_id只能是dept表中已经存在的id

解决方式：使用外键约束

### 3.5.3、什么是外键约束 ###

什么是外键：在从表中与主表主键对应的那一列，如 员工表中的dept\_id

主表：用来约束别人的表

从表：被别人约束的表

### 3.5.4、添加外键约束 ###

新建表时添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

### 3.5.5、删除外键约束 ###

ALTER TABLE 从表 drop foreign key 外键约束名称;

-- 删除employee表的外键约束
    alter table employee drop foreign key dept_fk_emp;
    
    -- 表存在的情况下添加外键约束
    alter table employee add constraint emp_deptid_fk foreign key (dept_id) references dept(id);

### 3.5.6、外键的级联 ###

出现新的问题

-- 要把部门表中的id值为2，改成5，能不能直接更新呢？
    -- Cannot delete or update a parent row: a foreign key constraint fails
    update dept set id=5 where id=2;
    
    -- 要删除部门id等于1的部门，能不能直接删除呢？
    -- Cannot delete or update a parent row: a foreign key constraint fails
    delete from dept where id=1;

什么是级联操作：  
在修改和删除主表的主键时，同时更新或删除从表的外键值，称为级联操作

<table> 
 <thead> 
  <tr> 
   <th>级联操作</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ON UPDATE CASCADE</td> 
   <td>级联更新，只能是创建表的时候创建级联关系，更新主表中的主键，从表中的外键列也自动同步更新</td> 
  </tr> 
  <tr> 
   <td>ON DELETE CASCADE</td> 
   <td>级联删除</td> 
  </tr> 
 </tbody> 
</table>

use db1;
    -- 部门表  主表
    CREATE TABLE dept(
     id int primary key auto_increment,
     dept_name varchar(30),
     dept_location varchar(30)
    );
    
    -- 员工表
    CREATE TABLE employee(
     id int primary key auto_increment,
     name varchar(20),
     age int,
     dept_id int,
     constraint dept_fk_emp foreign key(dept_id) references dept(id) on update cascade on delete cascade
    );
    
    -- 添加部门
    INSERT INTO dept(dept_name,dept_location) values('开发部','北京');
    INSERT INTO dept(dept_name,dept_location) values('销售部','上海');
    
    -- 添加员工
    INSERT INTO employee(name,age,dept_id) values('张三',20,1);
    INSERT INTO employee(name,age,dept_id) values('李四',21,1);
    INSERT INTO employee(name,age,dept_id) values('王五',22,1);
    INSERT INTO employee(name,age,dept_id) values('赵六',20,2);
    INSERT INTO employee(name,age,dept_id) values('田七',21,2);
    INSERT INTO employee(name,age,dept_id) values('小明',22,2);
    INSERT INTO employee(name,age,dept_id) values('小红',22,5);
    delete from employee where name='小红';
    select * from employee;
    
    -- 删除employee表的外键约束
    alter table employee drop foreign key emp_deptid_fk;
    
    -- 表存在的情况下添加外键约束
    alter table employee add constraint emp_deptid_fk foreign key (dept_id) references dept(id);
    
    select * from employee;
    
    select * from dept;
    
    -- 要把部门表中的id值为2，改成5，能不能直接更新呢？
    -- Cannot delete or update a parent row: a foreign key constraint fails
    update dept set id=5 where id=2;
    
    -- 要删除部门id等于1的部门，能不能直接删除呢？
    -- Cannot delete or update a parent row: a foreign key constraint fails
    delete from dept where id=1;

# 4、表与表之间的关系 #

## 表与表关系的概述 ##

现实生活中，实体与实体之间有一定的关系，比如：部门和员工，老师和学生等，那么我们在设计表的时候，就应该体现出表与表之间的这种关系。

**表和表之间的三种关系：**

（1）一对多：最常用的关系 部门和员工

（2）多对多：学生表和选课表，一门课程可以有多个学生选择，一个学生可以选择多门课程

（3）一对一：相对使用比较少，公民和身份证号码

# 5、数据库设计规范 #

## 5.1、什么是范式 ##

好的数据设计对数据的存储性能和后期的程序开发，都会产生至关重要的影响。建立科学的，规范化的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

## 5.2、三大范式 ##

目前关系型数据库有六种范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式、第四范式(4NF)、第五范式(5NF)，又称为完美范式。