数据库复杂查询与表的约束

1、复杂查询

1.1排序

1.1.1语法

​ order by 排序字段1 排序方式1,排序字段2 排序方式2,…

1.1.2排序方式

​ ASC
​ 升序排列,默认排序方式
​ DESC
​ 降序排列

1.1.3注意事项

​ 排序的条件有多个,只有当第一个排序的字段值相同时,才比较第二个排序字段的值

1.2聚合函数

​ 1.介绍
​ 将一列数据作为一个整体,进行纵向的计算
​ 2.count
​ 计算总个数
​ 可以写成*,建议使用非空的列,例如主键
​ 3.max
​ 计算最大值
​ 4.min
​ 计算最小值
​ 5.sum
​ 计算总和
​ 6.avg
​ 计算平均数
​ 7.注意事项
​ 聚合函数计算排除null,可以使用ifnull函数解决

1.3分组

​ 1.3.1语法
​ group by 分组字段
​ 1.3.2注意事项
​ 1.分组后查询的字段最好是分组字段或者是聚合函数
​ 2.where与having的区别
​ 1.where在分组前进行限定,如果不符合条件,不参与分组
having在分组后进行限定,如果不符合条件,不会被查询
​ 2.where后面不能使用聚合函数,having后面可以使用聚合函数

1.4分页

​ 1.语法
​ limit 起始索引,每页条数
​ 2.注意事项
​ limit是MySQL数据库的方言
​ 起始索引 = (当前页数 - 1) * 每页条数

2、约束

2.1概念

​ 对表中的数据进行限定,保证数据的正确性,有效性,完整性

2.2分类

​ 1.主键约束
​ primary key
​ 2.非空约束
​ not null
​ 3.唯一约束
​ unique
​ 4.外键约束
​ foreign key

2.3非空约束

​ 1.概念
​ 某一列的值不能为null,关键字是not null
​ 2.创建表时添加
​ create table stu(id int, name varchar(32) not null);
​ 3.创建表后添加
​ alter table stu modify name varchar(32) not null
​ 4.删除非空约束
​ alter table stu modify name varchar(32)

2.4唯一约束

​ 1.概念
​ 指定列的值不能重复,关键字是unique
​ 2.创建表时添加
​ create table stu(id int,name varchar(32) unique);
​ 3.创建表之后添加
​ alter table stu modify name varchar(32) unique
​ 4.删除唯一约束
​ alter table stu drop index name
​ 5.注意
​ 添加唯一约束时,如果当前列有值不符合要求,则会报错,添加失败

2.5主键约束

​ 1.概念
​ 某一列的值非空且唯一,关键字是primary key
​ 2.创建表时添加
​ create table stu(id int primary key,name varchar(32));
​ 3.创建表之后添加
​ alter table stu modify id int primary key
​ 4.删除主键约束
​ alter table stu drop primary key

2.6自动增长

​ 1.概念
​ 某一列是数值类型,可以通过auto_increment实现值的自动增长
​ 2.创建表时添加
​ create table stu(id int primary key auto_increment,name varchar(32));
​ 3.创建表之后添加
​ alter table stu modify id int auto_increment
​ 4.删除自动增长
​ alter table stu modify id int
​ 5.注意事项
​ 一般与主键一起使用

2.7外键约束

​ 1.概念
​ 让表与表之间产生关系,保证数据的正确性,关键字是foreign key
​ 2.创建表时添加
​ create table stu(sid int primary key auto_increment,name varchar(32),tid int,constraint s_t_fk foreign key (tid) references teacher (id));
​ 3.创建表之后添加
​ alter table stu add constraint s_t_fk foreign key (tid) references teacher (id)
​ 4.删除外键约束
​ alter table stu drop foreign key s_t_fk
​ 5.级联操作
​ on update cascade
​ 级联更新
​ on delete cascade
​ 级联删除
​ 与外键一起使用

3、多表

3.1分类

1. 一对多
   示例
   部门和员工
   实现
   在多的一方建立外键,指向一的一方的主键

   2.多对多
   示例
   学生和课程
   实现
   借助第三张中间表,中间表至少包含两个字段,作为外键,分别指向两张表的主键

​ 3.一对一
​ 示例
​ 人和身份证
​ 实现
​ 在任意一方建立唯一的外键,指向另一方的主键

3.2联合主键

​ 1.介绍：
​ 多列组合到一起,作为主键
​ 2.创建表时添加：
​ create table stu(uid int,rid int,primary key(uid,rid));
​ 3.创建表之后添加：
​ alter table stu add constraint primary key(uid,rid);
​ 4.删除联合主键：
​ alter table stu drop primary key;

3.3数据库设计范式

​

3.3.1概念

​ 设计数据库时,需要遵循的规范
​

3.3.2范式分类

​ 1.第一范式(1NF)：
​ 每一列都是不可分割的原子数据项
​ 2.第二范式(2NF)：
​ 非码属性必须完全依赖于码(消除非主属性对主码的部分函数依赖)
​ 3.第三范式(3NF)：
​ 任何非主属性不能依赖于其他非主属性(消除传递依赖)
​

3.3.3函数依赖

​ 概念
​ 通过A属性(属性组)可以确定唯一的B属性的值
​ 完全函数依赖
​ 如果A是一个属性组,确定B属性的值需要依赖A属性组中所有的属性值
​ 部分函数依赖
​ 如果A是一个属性组,确定B属性的值需要依赖A属性组中的部分属性值即可
​ 传递函数依赖
​ 通过A属性的值可以确定唯一B属性的值,通过B属性的值可以确定唯一C属性的值
​ 码
​ 一张表中,一个属性或属性组的值被其他所有属性完全依赖,这个属性或属性组为该表的码
​ 主属性
​ 码属性组中所有的属性
​ 非主属性
​ 码属性组以外的其他属性

3.4数据的备份和还原

3.4.1命令行

​ 备份
​ mysqldump -u用户名 -p密码 数据库名 > 本地路径
​ 还原
​ 1.登录mysql数据库
​ 2.创建数据库
​ 3.使用数据库
​ 4.执行语句:source 本地sql文件路径

3.4.2图形化工具

​ 备份：
​ 选中数据库->右键->备份/导出->转储到sql->选择本地路径->保存
​ 还原：
​ 选中root@localhost->右键->执行sql脚本->选择本地sql文件路径->执行