MySQL 数据库的增删查改 (2)

本篇文章继承与 MySQL 表的增删改查(1)

一. 数据库约束

1. 约束类型

NOT NULL -- 表示某一行不能存放 NULL 值.

UNIQUE -- 保证每列每行都有唯一的值
.
DEFAULT -- 用来规定列的默认初始值.

PRIMARY KEY -- NOT NULL 和 UNIQUE 的结合.**用来确保某列(或者多个列)有唯一表识**,有助于更加快速的找到列表中的特定记录值.

FOREIGN KEY -- 用来保证一个表中的数据匹配另一个表中的值的参照完整性.
1
2
3
4
5
6
7
8
9

2.NULL 约束

创建表时,指定某一列不可以为空.

在这里首先我们需要创建一个相关的表,如图:

这时我们插入元素,

3.UNIQUE 约束

创建表时,指定某一列的值不能重复出现.

创建一个相关的表,如图:

此时插入元素:

4.DEFAULT 约束

指定插入元素时,某一列为空时,默认值为 自己设定的元素

创建一个相关的表, 如图:

插入元素这里需要注意如下问题

这里如果要出现自定义的默认值,就不可以在设定元素处插入任何形式的元素

5. PRIMARY KEY 约束

称之为主键约束
主键字段: 在该字段上添加了主键约束.
主键值: 主键字段中的每一个值都叫做主键值.

这里我们创建一个相关的表:

对于整数类型的主键，常配搭自增 auto_increment 来使用。插入数据对应字段不给值时，使用最大值+1。

– 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
id INT PRIMARY KEY auto_increment

6.FOREIGN KEY 约束

用于关联其他表的主键的关键字.

foreign key (字段名) reference 主表(列).

首先创建一个班级表,

之后创建一个学生表,因为一个班级对应多个学生,一个学生对应一个班级. 所以使用 id 为主键,class_id 为外键,关联班级表 id.

此处外键的含义: 要求 student 里的 class_id 务必要在 class 表中的 id 列中存在.

二.表的设计

表的设计有以下三大范式

1. 一对一
   
2. 一对多
   
3. 多对多

创建课程表:

创建学生中间课程表和考试成绩表

三.插入

这里实现了将一个表中的部分元素插入到另一张表中.

这里先创建了一个学生表,

创建一个用户表

将学生表中的部分元素复制到用户表

insert into test_user(name, email)select name, qq_mail from student;
1

四.查询

1.聚合查询

1. 聚合函数
   常见的统计总数,计算平均值等操作,都可以使用聚合函数实现,常见的聚合函数有如下:

• COUNT 查询到的元素个数

//统计班级中有多少学生
select count(*) from students;
select count(0) from students;

//统计班级中的email有几个人(这里count与括号之间不能有空格存在)
select count(email) from students;
1
2
3
4
5
6

• SUM 查询到的元素数据之和

成绩表如下;

//统计数学成绩总分
select sum(math) from exam_result;

//统计不及格的总分 < 60 ,如果没有 返回null
select sum(math) from exam_result where math < 60;
1
2
3
4
5

• AVG 统计平均分

select avg(chinese+math+english) 品均分 from exam_result;
1

• MAX 返回最高分

// 返回英语的最高分
select max(english) from exam_result;
1
2

• MIN 返回最小值

//返回语文中的最小值
select max(chinese) from exam_result;

//返回 >70 分以上的数学最低分
select min(math) from exam_result where math>70;
1
2
3
4
5

2. GROUP BY 子句

select 中使用 group by 子句时,可以对指定的列进行分组查询.

满足条件: 使用 group by 进行分组查询时,select 指定的字段是 “分组依据字段”,其余字段若要出现在 select 中则必须要包含在 聚合函数中

注: 所谓分组依据字段, 就是指该列元素是将各个对象划分出层次的字段

首先,创建一个测试表,

在插入对应的元素,

//查询每个角色的最高工资\最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;
1
2

3. HAVING 子句

group by 子句进行分组后,需要对分组结果在进行条件过滤时,不能使用 where 语句, 而需要用having子句

// 显示平均工资低于1500的人与其平均工资
select role,max(salary),min(salary) from emp group by role having1 avg(salary)<1500;
1
2

2.联合查询

在实际开发中,数据往往来自不同的表,因此需要多表的联合查询.多表查询就是对多张表的数据取笛卡尔积

这里我们先建立几个之间有关联的表

班级表:

课程表:

成绩表:

学生表:

1. 内连接

语法:

• select 字段 from 表1 别名1 join 表2 别名2 on 连接条件 and 其他条件;
• select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

示例
(1) 查询许仙同学的成绩

select sco.score from student stu inner join score sco on stu.id=sco.student_id
and stu.name='许仙';
1
2

select sco.score from student stu, score sco where stu.id=sco.student_id and
stu.name='许仙';
1
2

(2) 查询所有学生的总成绩,以及学生的个人信息:
注: 查询的总成绩以学生的 id 进行分组

SELECT
 stu.sn,
 stu.NAME,
 stu.qq_main,
 sum( sco.score ) 
FROM
 student stu
 JOIN score sco ON stu.id = sco.student_id
GROUP BY
 sco.student_id;
1
2
3
4
5
6
7
8
9
10

2. 外连接

外连接分为左外连接和右外连接
如果联合查询, 左侧的表完全显示,我们就说是左外连接，反之是右外连接.

• 左外连接，表1完全显示
  select 字段名 from 表名1 left join 表名2 on 连接条件;
• 右外连接，表2完全显示
  select 字段 from 表名1 right join 表名2 on 连接条件;

左外连接:

select * from student stu left join score sco on stu.id=sco.student_id;
1

表1 student 表中的信息完全显示

右外连接:

select * from student stu right join score sco on stu.id=sco.student_id;
1

表2 score 表中的信息完全显示,同样,老外学中文因为没有成绩也就不显示其信息

3. 子查询

子查询是指嵌套在其他 sql 语句中的 select 语句 , 也叫嵌套查询.

• 单行子查询 : 返回一行记录的子查询.

select * from student where classes_id=(select classes_id from student where name = '不想毕业');
1

• 多行子查询

[NOT] IN 关键字:

使用 in 关键字

select * from score where course_id in (select id from course where
name='语文' or name='英文');
1
2

使用 not in 关键字

select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');
1
2

这里就是排除 指定的科目的成绩

[NOT] EXISTS 关键字

使用 EXISTS 关键字

select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);
1
2

使用 not exists
同理,显示除要求之外的信息

select * from score sco where not exists (select sco.id from course cou 
where (name!='语文' and name!='英文') and cou.id = sco.course_id);
1
2

3.合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用 UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致。

• union
  该操作符用来获取两个结果的并集.使用该操作时会自动去除结果中重复的元素.

select * from course where id<3
union
select * from course where name='英文';
1
2
3

• union all

该操作是用来取得两个结果的并集.当使用该操作符时, 不会去除掉结果中重复的元素.

如有不足,欢迎提出.如有问题,欢迎指正!