注册    登录
关注
Py学习  »  DATABASE

MySQL约束的概述

轩鸢无敌 • 3 年前 • 144 次点击  

MySQL约束

今日学习内容 

1.DQL：查询语句
    1.排序查询
    2.聚合函数
    3.分组查询
    4.分页查询
2.约束
3.多表关系
4.范式
5.数据库备份和还原
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

DQL查询语句

排序

通过ORDER BY子句，可以将查询出的结果进行排序 

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC：升序，默认值
DESC：降序
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

单列排序 ：只按某一个字段进行排序，单列排序 

-- 查询学生表中所有数据，使用年龄进行降序排序
select * from Student order by age desc
  • 1
  • 2
  • 1
  • 2

组合排序 ：同时对多个字段进行排序，如果第一个字段相等，则按第二个字段排序，往后推 

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
  • 1
  • 1
-- 查询学生表所有数据，在年龄上进行降序基础上，年龄相同则以分数升序排序
select * from student order by age desc,score asc;
  • 1
  • 2
  • 1
  • 2

聚合函数

聚合函数是纵向查询，是对一列的值进行计算，然后返回结果。

聚合函数会忽略空值NULL，解决：我们可以选择不包含非空的列计算，或者使用IFNULL函数 

IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。
  • 1
  • 1
-- 查询id字段，如果为null，则使用0代替
select ifnull(id,0) from student;
  • 1
  • 2
  • 1
  • 2

聚合函数

SQL中聚合函数 作用
max 求这一列的最大值
min 求这一列的最小值
avg 求这一列的平均值
count 统计这一列有多少条记录
sum 对这一列求总和 
-- 查询年龄大于18的总数
select count(*) from student where age>18;

-- 查询英语成绩总分
select sum(english) 总分 from student;

-- 查询英语成绩最低分
select min(english) 最低分 from student;

-- 查询英语成绩最高分
select max(english) 最高分 from student;

-- 查询英语成绩平均分
select avg(english) 平均分 from student;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

分组

对查询信息进行分组，相同数据作为一组 

SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
  • 1
  • 1

where和having的区别

1.where在分组之前进行限定，如果条件不满足，则不参与分组

having在分组之后进行限定，如果不满足结果，则不会被查询

2.where后不可跟聚合函数，having可以进行聚合函数的判断 

-- 按照性别分组。分别查询男、女同学的平均分
select sex ,avg(english) from student group by sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数
select sex ,avg(english), count(id) from student group by sex;

-- 按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于60分的人，不参与分组
select sex,avg(score),count(id) from student where score >=60 group by sex;A

--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于60分的人，不参与分组,分组之后。人数要大于3个人
select sex,avg(score),count(id) from student where score>=60 group by sex having count(id)>3;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

limit语句

LIMIT 的作用就是限制查询记录的条数。 

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


    
BY 子句][LIMIT 子句];
  • 1
  • 1

格式 

LIMIT offset,length;
-- offset：起始行数，从0开始，默认为0
-- length：返回的行数

-- 查询学生表信息，从第3行开始显示，显示5条
select * from student limit 2,5;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

分页查询 

limit 开始的索引,每页查询的条数;
开始的索引 = （当前的页码 - 1） * 每页显示的条数
-- 每页显示4条
SELECT * FROM student LIMIT 0,4; -- 第1页
		
SELECT * FROM student LIMIT 4,3; -- 第2页
		
SELECT * FROM student LIMIT 7,3; -- 第3页
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

约束

为什么要进行约束？ 

对表中的数据进行限制，保证了数据的正确性，有效性和完整性，添加了约束，错误的数据是无法被添加到表中的
  • 1
  • 1

约束分类

约束名 约束关键字
主键约束 primary key
唯一约束 uniqe
非空约束 not null
外键约束 foreign key
检查约束 check 备注：mysql不支持

主键约束

  • 主键的作用：用来唯一标识数据库的每一条记录

  • 通常不用业务字段作为主键，一般每张表会单独设计一个id字段，把id作为主键。主键是给数据库和程序服务的，不是给客户使用的

  • 主键的特点：非空(not null) 、唯一(不重复)

  • 主键自增：数据库自动生成主键字段的值，AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

创建主键 

1.创建表时添加主键
字段名 字段类型 primary key
2.在已有表中添加主键
alter table 表名 add primary key(字段名);

-- 创建学生表stu，包含字段（id，name，age）id作为主键
create table stu(
id int primary key, --id为主键
name varchar(20),
age int
)

desc stu;
--删除主键
alter table stu drop primary key;
-- 添加主键
alter table stu add primary key(id);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

修改自增长的默认起始值

默认自增长开始值是1，但是可以进行修改

创建表的起始值 

create table 表名(
 列名 int primary key auto_increment,
)auto_increment=起始值;

-- 指定起始值为100
create table stu1 (
 id int primary key auto_increment,
 name varchar(20)
) auto_increment = 100;

insert into stu1 values (null, '轩月');
select * from stu1;

结果： id    name
      100   轩月
     
-- 删除自动增长
alter table stu1 modify id int;

-- 添加自动增长
alter table stu1 modify id int auto_increment;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

唯一约束

unique，表中某一列不能出现重复值 

字段名 字段类型 uniqe
  • 1
  • 1

创建学生表stu2，包含字段（id，name）name列设置唯一约束，即不能出现同名的学生 

-- 创建表时，添加唯一约束
create table stu2 (
 id int,
 name varchar(20) unique
);
-- null 没有数据，不存在重复的问题 ,列值可以有多个null

-- 删除唯一约束
alter table stu2 drop index name;

--创建表后，添加唯一约束
alter table stu2 modify name varchar(20) unique;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

非空约束

某一列中不能为null 

字段名 字段类型 not null
  • 1
  • 1

创建学生表stu3，包含字段（id，name，age）name不能为空null 

-- 创建表时添加非空约束
create table


    
 stu3 (
id int,
name varchar(20) not null,
age int
);

-- 创建表后，添加非空约束
alter table stu3 modify name varchar(20) not null;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

外键约束

foreign key，让表与从表产生关系 

-- 创建表时，添加外键
create table 表名(
    ...
    外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(猪表列名称)
);

-- 删除外键
alter table 表名 drop foreign key 外键名称;

-- 创建表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主表名称(主表列名称);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

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

级联操作语法 描述
on update cascade 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键 列也自动同步更新
on delete cascade 级联删除 
-- 添加级联操作
alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade ;
  • 1
  • 2
  • 1
  • 2

数据库设计

表与表间的关系

表与表间的三种关系
一对一 (1:1) :员工表 简历表 等使用较少
一对多 (1:n)：员工和部门 最常用
多对多 (m:n)：学生选课表和学生表 ，一门课程多个学生选择，一个学生选择多门课程

数据规范化

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

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

一般用前三种范式

范式 特点
1NF 原子性：表中每列不可拆分
2NF 不产生局部依赖，一张表只描述一件事情，表中每一列都完全依赖于主键
3NF 不产生传递依赖，表中每一列都直接依赖于主键，而不是通过其他列间接依赖于主键

几个依赖的概念 

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 → C"的决定关系，则 C 传递依赖于 A  
    //学号-->系名，系名-->系主任
    
5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码  
    主属性：码属性组中的所有属性
    非主属性：除过码属性组的属性
    //例如表中码为：（学号，课程名称）
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

数据库的备份和还原 

-- 备份
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
-- 还原
1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件 source 文件路径
-- 还原格式
use 数据库;
source 导入文件的路径;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/99417
Reply
 
144 次点击  
登录后回复
关于   移动版   ·   三行代码   ·   今天看啥   ·   Code   ·   link之家   ·   卧龙搜索   ·   藏经阁   ·   小百科
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号