MySQL_day02

DQL 查询表中记录

1. 查询语法：

   select 字段列表 from 表名列表

   where 条件列表 group by 分组字段

   having 分组之后的条件限定

   order by 排序 limit 分页限定

2. 基础查询

   1. 查询多个字段

      select 列1 , 列2 , 列n from 表;

   2. 去除重复

      select distinct 列名 from 表;

      多字段去重

3. 计算列

   select name ,math,english,math+english from student;

   当出现null时 结果就会null 所以要使用 ifnull（可能null的列，出现null赋的值）函数

   select name,math,english,math+ifnull(english,0) from student;

4. 起别名

   select name,math 数学,english 英语,math+ifnull(english,0) as total from student;

3. 条件查询

   1. wherre 自居后跟条件

   2. 运算符

      • < 、> 、<= 、 >= 、 = 、 <> !=不等号

      • between and

        包含边界

      • in（集合）

      • like 模糊查询

        占位符：

        • _：单个任意字符（1个）
        • %：多个任意字符（0或者多个）

      • is null

        null值不可以使用 = 来判断，要使用isnull

        不为null：is not null

      • 与 and &&

      • 或 or ||

      • 非 not !

4. 排序查询

   语法：order by 子句

   order by 排序字段1 排序方式1，排序字段2 排序方式2

   不写排序方式 默认升序

   排序方式：

   • ASC ：升序 ，默认
   • DESC：降序

   按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名

   select * from student order by math asc , english asc;

   第一排序规则按数学升序，如果数学成绩一样，按照英语成绩排

   注意：

   如果有多个排序条件，当前边条件值一样时 才会判断第二条件

5. 聚合函数

   将一列数据作为一个整体，进行纵向的计算

   • count：计算个数

     select count(name) from student;

     如果有null 不会计算其中

     可以改为

     select count(ifnull(name,0)) from student;

     解决null：

     解决方案：

     • 一般选择费控的列（主键）
     • count（*） 只要有不是null的就算一行记录

   • max：计算最大值

   • min：计算最小值

   • sum：求和

   • avg：计算平均值

   注意：聚合函数的计算会排出null值

6. 分组查询

   统计具有相同特征的数据，将数据作为整体分析。

   1. 语法：

      group by 分组字段：

   2. 按照性别分组 分别查询男、女的平均分

      select sex，avg(math) from student group by sex;

   3. 分组之后人数要大于2人 ----> 使用having

      select sex，avg(math) from student group

      by sex having count(id) > 2;

      注意：

      • 分组之后查询的字段：分组字段、聚合函数
      • where 和 having 的区别？
        1. where 在分组之前进行限定，如果不满足条件则不参与分组，having 在分组之后进行限定，不满足条件则不会查询出来
        2. where 后不可以加聚合函数 而having可以

7. 分页查询

   • 语法：

     limit 开始的索引 ，每页查询的条数

     select * from student limit 0,3; 第一页

     select * from student limit 3,3; 第二页

     select * from student limit 6,3; 第三页

   • 公式：

     开始的索引 = （当前的页码 - 1）* 每页的条数

   • limit语法 是mysql的一个“方言“

约束

1. 概念：对表中数据进行限定，保证数据的正确性，有效性和完整性

2. 分类：

   • 主键约束：primary key
   • 非空约束：not null
   • 唯一约束：unique
   • 外键约束：foreign key

3. 非空约束 not null

   值不能为null

   • 在创建表时添加约束

     create table stu(

     id int,

     name varchar(20) not null

     );

   • 删除name的非空约束

     alter table stu modify name varchar(20);

   • 创建表后添加非空约束

     alter table stu modify name varchar(20) not null;

4. 唯一约束 unique

   值不能重复

   • 创建唯一约束（建表时）

     create table stu(

     id int,

     phone_number varchar(20) unique

     );

   注意mysql中，唯一约束限定的列值可以多个null

   • 删除唯一约束：（特殊）

     alter table stu drop index phone_number;

   • 创建表后添加唯一约束：

     alter table stu modify phone_number varchar(20) unique;

5. 主键约束 primary key

   • 注意：

     1. 含义：非空且唯一
     2. 一张表只能有一个字段为主键
     3. 主键就是表中记录的唯一标识

   • 在创建表时 添加主键约束

     create table stu(

     id int primary key, -- 给id添加主键约束

     name varchar(20))

     ;

   • 删除主键：

     alter table stu drop primary key;

   • 创建表后添加主键

     alter table stu modify id int primary key;

   • 自动增长

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

   • 在创建表时 添加主键约束，并且完成主键自增长

     create table stu(

     id int primary key auto_increment,

     name varchar(20)

     );

     加自增长时 加数据使用null会默认上一个数据加一，如果自增长后，手动赋值非null的值，下一次自增长是根据手动赋的值来自增长，因为是默认上一条数据加一

• 删除自增长

  alter table stu modify id int;

• 添加自动增长

  alter table stu modify id int auto_increment;

6. 外检约束
   让表与表产生关系，从而保证正确性

   • 在数据冗余时（重复），解决：

     拆分表，将重复数据拆分成新表

   • 在创建表时，可以添加外键

     • 语法：create table 表名(

       ...

       外键列

       constraint 外键名称 foreign key 外键列名称 reference 主表名(主表列名称)

       );

   • 删除外键

     alter table 表名 drop foreign key 外键名;

     这里使用外键名称 不是列名

   • 再创建表之后添加外键

     alter table employee add constraint emp_dept_fk foreign key (dept_id) references department (id);

     给emp表 dept_id列 加外键 关联departemnt列id

     emp列：dept_id

     department列：id

     外键名：emp_dept_fk

     外键值可以为null 不可以为不存在的外键值

7. 级联操作：

   在添加外键时 设置级联更新

   alter table emp add contraint emp_dept_fk foreign key dept_id reference department (id) on update cascade;

   on update cascade

   级联删除：

   on delete cascade

   级联删除可以和级联更新一起设置

多表之间的关系

1. 一对一的关系

   例：一个人对一个身份证

2. 一对多的关系

   例：一个部门对多个员工，一个员工对一个部门

3. 多对多的关系

   例：一个学生对多个课程，一个课程对很多学生

4. 实现这些关系：

   1. 一对多（多对一）

      例：员工和部门

      image.png

      实现方式：

      在多的一方建立外键，指向一的一方

   2. 多对多

      例：学生和课程

      image.png

      实现方式：

      中间表

   3. 一对一

      例：学生和身份证

   image.png
   
   

   实现方式：
   * 任意一方添加外键 指向另一方主键并且添加Unique唯一约束
   * 主键相同

5. 案例：

   分析 旅游线路分类 旅游线路 用户的关系

   image.png

   数据库中SQL实现：

   [
   image.png

范式

1. 概念：设计数据库时，需要遵循的一些规范

2. 范式分类：

   • 第一范式（1NF）：每一列都是不可分割的原则数据项

     属性不可再分

   • 第二范式（2NF）：第一范式基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

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

3. 图示不合理的设计表

   image.png

4. 概念：

   • 函数依赖：

     A -----> B 通过A属性值可以确定B的值，我们称B依赖于A

     如果通过A属性（属性组）的值，可以确定唯一B属性的值

     例如：学号 -----> 姓名

   • 完全函数依赖

     A ----> B 如果A是一个属性组，则B属性值得确定需要依赖于A属性组的所有的属性值，成B完全依赖于A属性组

     例如：（学号、课程名称）----> 分数

   • 部分函数依赖

     A ------> B 如果A是一个属性组，则B属性值通过A属性组其中部分的属性值就可以确定，则成B部分依赖于A属性组

     例如：（学号，课程名）-----> 姓名

   • 传递函数依赖

     A -----> B ， B ---->C 如果通过A属性（属性组）可以唯一确定B属性的值，再通过B属性（属性组）的值可以确定C属性的值，我们成C传递函数依赖于A

     例如：学号------>姓名，姓名-------> 系主任

   • 码：如果在一张表中，一个属性或属性组被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码

     例如：该表中为 属性组（学号，课程名称）

     • 主属性：码属性组的所有属性

     • 非主属性：除过码属性组的属性

   在该表中，姓名、系名和系主任部分依赖于码，分数完全依赖于码

5. 消除部分依赖方案：

   表的拆分

   • 如图：
   image.png

   在选课表中 分数完全依赖于 码（学号，课程名）属性组

   在学生表中 姓名、系名和系主任完全依赖于学号（码）

   • 仍然存在问题：

image.png

6. 消除传递依赖（3NF）

   解决：再拆分表

   image.png

   学生表：系名、姓名完全依赖于学号

   系表：系主任完全依赖于系名

   刚刚所有的三个问题全部被解决掉

数据库的备份和还原

1. 防止数据丢失

2. 两种方式：

   • 命令行

     备份的语法：

     mysqldump -u用户名 -p密码 数据库名 > 保存的路径

     还原：

     1. 登录数据库

     2. 创建数据库

     3. 使用数据库

     4. 执行文件，source 文件路径

   • 图形化工具

     1. 右键导出

        image.png

     2. 还原：

        右键连接 执行sql脚本

     image.png