day02 数据库索引&关联查询

爱看星星的猪_

已于 2024-10-11 19:44:53 修改

阅读量959

点赞数 36

分类专栏： MYSQL 文章标签：数据库 sql

于 2024-06-13 14:25:12 首次发布

本文链接：https://blog.csdn.net/Liang_z_/article/details/139653267

版权

MYSQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

索引

作用：

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
全文检索字段进行搜索优化

分类：

主键索引(PRIMARY KEY)
唯一索引(UNIQUE)
常规索引(INDEX)
全文索引(FULLTEXT)

索引名称	作用
primary key	主键索引,唯一标识，不能重复，不允许为空
unique	唯一索引,值不能重复,可以为空
index	常规索引
fulltext	全文索引,只能用于字符串类型的字段

主键索引

主键：某一个属性组能唯一标识一条记录

特点：

最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

表创建后添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

唯一索引（UNIQUE）

作用：

避免同一个表中某数据列中的值重复

与主键索引的区别：

主键索引只能有一个，唯一索引可有多个

create table admin2(
admin_id int primary key auto_increment,
name varchar(20) not null,
age int,
id_card varchar(30),
unique(name,age)
)

常规索引（INDEX）

作用：

速定位特定数据

注意：

index和key关键字都可设置常规索引
应加在查找条件的字段
不宜添加太多常规索引，影响数据的插人、删除和修改操作

create table s1(
    id int PRIMARY KEY ,
    #id int index #不可以这样加索引，因为index只是索引，没有约束一说，
    #不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20) ,
    age int,
    email varchar(30),
    index(age),
    index(id) #主键可以作为索引
    );

删除索引

drop index id on s1;#删除s1表的id索引
drop index i_name on s1;#删除s1表的name索引

全文索引（FULLTEXT）

作用：

快速定位特定数据

注意：

能用于MyISAM类型的数据表
只能用于CHAR、VARCHAR、TEXT数据列类型
适合大型数据集

索引准则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字

约束

在表的字段上面加上约束，用来保证数据的有效性和完整性。

约束种类：

非空约束not null：字段不能为空
唯一性约束unique：该字段的内容不能够重复，可以为空
主键约束primary key：不能为空，也不能够重复，类似非空约束和唯一性约束的结合。
外键约束foreign key：该字段的取值范围是固定的，一类与另一个字段的取值。

当两表都存在时,创建外键的方式:

ALTER TABLE table2 ADD CONSTRAINT fk_table2_deptId 
FOREIGN KEY(deptId)  REFERENCES  table1(id);

删除外键

ALTER TABLE table2 DROP FOREIGN KEY fk_table2_deptId;

表设计

一对一关系

表设计

考虑一个简化的学校管理系统，包括两个表：students（学生）和 student_profiles（学生档案）。

students 表存储学生的基本信息。
student_profiles 表存储每个学生的详细档案信息，与 students 表是一对一关系。

CREATE TABLE students (
    student_id INT PRIMARY KEY auto_increment,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10)
);

CREATE TABLE student_profiles (
    profile_id INT PRIMARY KEY,
    student_id INT UNIQUE,
    address VARCHAR(100),
    contact_number VARCHAR(15)
);

关系说明

students.student_id 和 student_profiles.student_id 之间建立一对一关系。

一对多关系

表设计

继续考虑学校管理系统，这次添加一个 courses 表，与 students 表建立一对多关系。

students 表存储学生的基本信息。
courses 表存储课程信息。
student_courses 表存储学生选修的课程，与 students 和 courses 表是一对多关系。

 CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE student_courses (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT
);

关系说明

students.student_id 和 student_courses.student_id 之间建立一对多关系。

关联查询

作用：

如需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询

分类：

内连接（inner join) 值和非等值的连接查询自身连接查询在表中至少一个匹配时，则返回记录

select stu.*,sd.phone,sd.addr,sd.email from student stu 
inner join stu_detail sd on stu.id=sd.stu_id;

#inner join的另一种写法
select stu.*,sd.phone,sd.addr,sd.email from student stu,stu_detail sd where stu.id=sd.stu_id;
select stu.*,sd.phone,sd.addr,sd.email from student stu,stu_detail sd

外连接（out join）左连接（LEFT JOIN）右连接（RIGHTJOIN)

左外链接：从左表（table_1）中返回所有的记录，即便在右（table_2）中没有匹配的行

SELECT字段1，字段2.FROMtable_1LEFT[OUTER] JOIN table_2 ON table_1.字段x= table_2.字段y;

右外连接：从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行

SELECT字段1，字段2...FROMvb table_1RIGHT [OUTER] JOIN table_2 ON table_1.字段x=table_2.字段y;

内外连接区别

Order By排序

对SELECT语句查询得到的结果，按某些字段进行排序与DESC或ASC搭配使用，默认为ASC

union关键字

union是连接两个查询的语句的,可以使用union做到全连接，即左右无论是否匹配都会显示(笛卡尔积)

#全连接
SELECT * FROM student s left join classes c on s.classId=c.id union SELECT * FROM student s right join classes c on s.classId=c.id

mysql常用聚合函数

函数名称	描述
COUNT()	返回满足SELECT条件的记录总和数，如SELECTCOUNT(*)..
SUM()	返回数字字段或表达式列作统计，返回一列的总和
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段、字符字段或表达式列作统计，返回最大的值
MIN	可以为数值字段、字符字段或表达式列作统计，返回最小的值

select count(0) from student where id>1;#得到满足条件的个数
select sum(score) from student where classId=2;#查询2班的总成绩
select max(score) from student;#最大值
select min(score) from student;#最小值

select ceil(3/2);#向上取整
select floor(3/2);#向下取整
select round(3/2);#四舍五入

随堂测

题1 MySQL中有哪些常见索引及其特点

主键索引

最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

唯一索引

避免同一个表中某数据列中的值重复
可以有多个

常规索引

可以速定位特定数据
index和key关键字都可设置常规索引
应加在查找条件的字段

全文索引

可以快速定位特定数据
能用于MyISAM类型的数据表
只能用于CHAR、VARCHAR、TEXT数据列类型
适合大型数据集

题2 解释表一对一和一对多关系的含义

一对一关系表示一个实体对象只能与另一个实体对象相关联。例如，每个人可以拥有一个唯一的身份证号码，而每个身份证号码也只能与一个人相关联。

一对多关系表示一个实体对象可以与多个不同的实体对象相关联。例如，一个作者可以写多本书，而每本书只能有一个作者。

题3 关联查询中内连接外连接区别

内连接只返回符合条件的行，而外连接则会返回符合条件的行以及未匹配的行，并以 NULL 值填充未匹配的数据。

题4 MySQL中常见的聚合函数及其含义

函数名称	描述
COUNT()	返回满足SELECT条件的记录总和数，如SELECTCOUNT(*)..
SUM()	返回数字字段或表达式列作统计，返回一列的总和
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段、字符字段或表达式列作统计，返回最大的值
MIN	可以为数值字段、字符字段或表达式列作统计，返回最小的值

题5 having关键字的作用及搭配where order by group写法顺序

having关键字用于在 SQL 查询中对分组后的结果集进行筛选，类似于 WHERE 关键字，但HAVING 用于过滤分组后的数据，而 WHERE用于过滤行级别的数据。

先where，在数据分组前进行过滤

再group by最数据进行分组

然后having对分组后的数据进行筛选

最后order by对结果集进行排序