MySQL表的进阶知识

目录

一、数据库约束 1、not null

不能为空的约束，比如在学生表当中，学生的学号，姓名就不能为空。

使用演示：

 create table student1(id int not null,name varchar(20) not null);

2、unique

表示唯一约束，比如在学生表当中，学生的学号是惟一的，不能有两个学生的学号相同。

使用演示：

 create table student2(id int not null unique,name varchar(20) not null);

3、primary key

主键约束，主键约束就是不能为空，并且是惟一的，也就是含有前两个约束条件，在上述市里的student2表中，对id进行not null 和unique约束后，在查看表结构时，就出现id是primary key的结果。

在一张表中只能有一个字段为主键。

使用演示：

create table student3(id int primary key,name varchar(20) not null);

与student2的表结构相同。

4、auto_increment

主键自动增长约束，例如在插入数据时，未插入主键，则就在已插入元组的主键+1，作为新插入元组的主键，主要针对整型类型的主键，默认从一开始。

使用演示：

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

5、default

默认约束，通常在插入数据时，如果对某一字段没有赋值就默认为null，但是在使用default约束后，默认就是所设置的值。

使用演示：

create table student4(id int primary key,name varchar(20) default "无名氏");

插入一个名字为空的学生信息：

此时默认的名字不再是null，而是无名氏。

6、foreign key

foreign key(参照字段) references 被参照表名(被参照字段);

外键约束，就是一个表的某字段需要参照另一个表中的某一字段，例如：在学生表和班级表中，学生表中的班级就要参照班级表中的班级号。

使用演示：

 create table class(id int primary key,number int not null);
create table student(id int primary key,name varchar(20) not null,class int ,foreign key(class) references class(id));

注意：

7、check

check约束主要是插入元组时，对范围进行约束，例如在插入学生分数时，分数不能超过100 ，就可以写成如下所示：

 create table student2(id int primary key,name varchar(20) not null,score int,check(score<=100));

但是 MySQL8之前的版本会忽略check约束，也就是不生效。

二、插入数据

可以使用select语句的查询结果作为insert的数据进行插入。一般情况下select得到的就是插入表的外键。

这是student表和class表的结构：

例如将查询class表中的所有的id作为数据插入到student表中：

三、设计表

在实际应用过程中通常是在复杂的关系中设计表，需要分析表结构，表之间常见关系有一对一、一对多、多对多。

1、一对一

比如每个班级的班长是唯一的，一个班级只有一个班长，一个班长只能服务一个班级。

2、一对多

一个班级拥有多个学生，多个学生在一个班级。

3、多对多

比如课程和学生之间就是多对多的关系。一门课程对应多个学生，一个学生有多门课程。

在多对多关系中，一般需要建立三个表来进行联系。

对于学生和课程来说，需要建立学生表、课程表、分数表：

学生表：

create table student(id int primary key auto_increment,name varchar(20) not null,sex varchar(2) not null);

课程表：

create table course(id int primary key auto_increment,name varchar(20) not null);

分数表：

create table score(student_id int,course_id int,score double(3,1),
foreign key(student_id) references student(id),
foreign key(course_id) references course(id));

四、查询 1、聚合查询

以下演示以school表为基础：

a、聚合函数查询

聚合函数有：

涉及数字的聚合函数遇到null值会进行忽略。

聚合函数查询可以搭配where子句。

例如：统计school表中的元组个数：

select count(*) from school;

例如：求出学校表的姓张的平均工资：

 select avg(salary) from school where name like '张%';

例如：求出学校表的最高工资：

select max(salary) from school;

b、group by

分组查询：对表中的数组先进行分组mssql 在表中插入，再进行相关查询。

例如：查询每个职位的最低工资：

 select posts, min(salary) from school group by posts;

同样，group by也可以搭配where子句使用：

例如：查询除了院长的职位的最高工资：

 select posts, max(salary) from school where posts!="院长" group by(posts);

c、having

having对group by分组之后的结果进行筛选，而where是在group by分组之前进行筛选。

例如：查询school表中的平均工资在一万以上的：

select posts,avg(salary) from school group by posts having avg(salary)>10000;

2、联合查询

在实际开发中，通常使用的是联合查询，也就是联合多个表进行查询，那么就必须了解表之间的笛卡尔积：

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

那么两个表的笛卡尔积：

a,b两个表进行笛卡尔积，笛卡尔积表的列数=a的列数+b的列数，笛卡尔积表的行数=a的行数*b的行数，

通俗来讲，笛卡尔积就是两个表全排列的所有情况。

后续操作是在以下三个表的基础上：

学生表：

课程表：

分数表：

a、内连接

两个表之间的某一字段相等的连接。

语法格式：

select 字段名 from 表1，表2 where 连接条件 and 其他条件；

select 字段名 from 表1 join 表2 on 连接条件 and 其他条件；

连接条件指的是两个表中的相同字段名相等。

例如：查找Java成绩高于85的学生信息：

select student.name,course.name,score from student,course,score 
where student.id=score.student_id and course.id=score.course_id 
and course.name='Java' and score>85;
--或者
select student.name,course.name,score from student join course join score 
on  student.id=score.student_id and course.id=score.course_id 
and course.name='Java' and score>85;

查询结果：

例如：按照学生的平均分进行排名：

select student.id,student.name,avg(score) as avg from student,course,score 
where student.id=score.student_id and course.id=score.course_id
group by(student.id) order by(avg) desc;
--或者
select student.id,student.name,avg(score) as avg from student join course join score 
on student.id=score.student_id and course.id=score.course_id
group by(student.id) order by(avg) desc;

查询结果：

select student.name,course.name,score from student left join score on student.id=score.student_id left join course on course.id=score.course_id;

b、外连接

通俗来讲，就是把一张表的某些字段连接到另一张表上。

外连接可分为：左外连接和右外连接。

左外连接的语法格式：

select 字段名 from 表1 left join 表2 on 连接条件；

右外连接的语法格式：

select 字段名 from 表1 right join 表2 on连接条件；

例如：查询所有学生的各科成绩：

select student.name,course.name,score from student left join score on
 student.id=score.student_id left join course on course.id=score.course_id;

查询结果：

c、自连接

在同一张表中进行自身的查询，就是将行与行之间的查询转换成列与列之间的查询。

例如：在score表中查询Java成绩高于C++的:

select s1.student_id,s1.course_id,s1.score,s2.course_id,s2.score 
from score as s1,score as s2 where s1.student_id=s2.student_id 
and s1.course_id=12 and s2.course_id=13 and s1.score>s2.score;

查询结果：

3、子查询

是指在select查询语句中再嵌套一个查询语句。

例如：查询选修Java的学生姓名：

select student.name from student where id in
(select student_id from score where course_id=12);

4、合并查询

与 or 关键字查询结果基本一致，只是or是属于单表查询，而合并查询是多表查询。

语法格式：

select …… union select……：对查询结果去重

select …… union all select……：对查询结果不去重

例如：查询选修Java或者分数大于90的学生id：

select student_id from score where course_id=12
union
select student_id from score where score>90;

查询结果：

（编辑：92站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!