本文介绍: 引入约束规则,是为了更强的数据检查/校验.数据一定要保证正确性.(如果后续插入/修改数据不符合要求,就会报错)执行效率(代码机器上跑的快不快)

目录

数据库约束

约束的定义

约束类型

null约束

unique:唯一约束

default:默认值约束

primary key:主键约束(重要)

foreign key:外键约束(描述两个表之间的关联)

表的设计

一般思路

三大范式

一对一

一对多

​编辑

多对多

​编辑

新增

查询

聚合查询

聚合函数

group by字句

having

联合查询(也称多表查询)

内连接

外连接

自连接

子查询

合并查询


数据库约束

约束定义

定义:创建表时,给这个表制定一些规则,后续插入/修改/删除都要保证数据能够遵守这些规则.

引入约束的规则,是为了更强的数据检查/校验.数据一定要保证正确性.(如果后续插入/修改的数据不符合要求,就会报错)

执行效率(代码机器上跑的快不快)

开发效率(程序员代码所花费的时间)

我们约束也是牺牲了执行效率,换来了开发效率.因为开发效率更重要:因为人力成本高于机器硬件成本.

约束类型

分为not null, unique, default, primary key, check几种

null约束

定义:指示某列不能存储null

创建表时,就是指定某列不为空,当指定插入null会报错


重新设置学生结构
DROP TABLE
IF EXISTS student;
CREATE TABLE
student (
  id
INT
NOT
NULL
,
  sn
INT
,
  name
VARCHAR
(
20
),
  qq_mail
VARCHAR
(
20
)
);

unique:唯一约束

定义:保证某列的每行必须有唯一的值.

指定sn为唯一的,不重复的(即在这一列都不会出现):

— 重新设置学生结构
DROP TABLE
IF EXISTS student;
CREATE TABLE
student (
  id
INT
NOT
NULL
,
  sn
INT
UNIQUE,
  name
VARCHAR
(
20
),
  qq_mail
VARCHAR
(
20
)
);

default:默认值约束

定义:规定没有给列赋值时赋默认值.(默认情况下的默认值设置为null)

指定插入数据时,name为空,默认值unknown


重新设置学生表结构
DROP TABLE
IF EXISTS student;
CREATE TABLE
student (
  id
INT
NOT
NULL
,
  sn
INT
UNIQUE,
  name
VARCHAR
(
20
) DEFAULT
‘unkown
,
  qq_mail
VARCHAR
(
20
)
);

primary key:主键约束(重要)

定义:not null和unique结合.确保某列(或两个列,多个列的组合)有唯一标识(就是一个记录身份标识),有助于更容易更快速地找到表中一个特定记录.(通常用xxx id作为主键,通常一个表中只有一个主键).

指定id列为主键:


重新设置学生表结构
DROP TABLE
IF EXISTS student;
CREATE TABLE
student (
  id
INT
NOT
NULL
PRIMARY KEY,
  sn
INT
UNIQUE,
  name
VARCHAR
(
20
) DEFAULT
‘unkown
,
  qq_mail
VARCHAR
(
20
)
);
自增主键的介绍:auto_increment(对id(主键)的+1操作)
需要用户自己指定(可以放个null),可交给数据库进行分配(1,2,3…)
其实,程序员可以自行设定id亦可
每次使用null方式插入自增主键时,都是数据库根据这一列的最大值,在这个基础上,继续递增,不会重复利用之前上述的自增主键,只能在当前单个数据库生效
如果数据库是由多个mysql服务器构成集群时,自增主键就无效
在实际业务当中,引入的主键是不希望重复的.
id INT PRTMARY KEY auto_increment,

foreign key:外键约束(描述两个之间关联)

定义:保证一个表中数据匹配一个表中的值的参照完整性.
外键用于关联其他表的主键或者唯一键,语法:
字段名是指本表的哪个列
— 主表是指被关联的表
— 列就是被关联的表的哪个列
注:外键约束是写到最后,把前面定义好之后,在最后通过foreign key创建外键约束.
创建班级classes,id为主键:
创建班级表,有使用
MySQL
关键字作为字段时,需要使用

标识
DROP TABLE
IF EXISTS classes;
CREATE TABLE
classes (
        id
INT
PRIMARY KEY auto_increment,
        name
VARCHAR
(
20
),
        `desc`
VARCHAR
(
100
)
);
创建学生student,一个学生对应一个班级,一个班级对应多少学生.使用id为主键,classes_id为外键,关联班级表id

重新设置学生表结构
DROP TABLE
IF EXISTS student;
CREATE TABLE
student (
  id
INT
PRIMARY KEY auto_increment,
  sn
INT
UNIQUE,
  name
VARCHAR
(
20
) DEFAULT
‘unkown
,
  qq_mail
VARCHAR
(
20
),
classes_id
int
,
FOREIGN KEY (classes_id) REFERENCES classes(id)
); 

1.把约束别的表的表称为”父表”.

   把被约束的表,称为”子表“.

2.外键约束,也是双向的,要想删除父表的记录,就必须先删除表中相关数据,以确保子表中没有数据.

3.使用外键约束时,操作子表,要查询父表;操作父表,也要查询子表.子表和父表中被引用的列都要带有”索引“.表里有了主键之后,就会自动创建出索引,加快查询速度

4.扩展知识:对电脑上的文件进行删除,也只是逻辑上的删除,不是真的删除.把文件删除掉,其实也是在系统中,把硬盘对应的盘块数据标记无效了(所以不是真正意义上的删除) 

表的设计

一般思路

 1.先根据需求,找到实体(一些关键性质的对象),梳理清需求,提取关键字名字,一般来说,每个实体,都得安排个表.

2.梳理实体间的关系(不同关系下,有不同设计表的方式),造句,向里面套,能套上哪个,就用哪种方式创建数据表(定式)

三大范式

一对

方案一:搞一个大表,把这些信息都放在一起(前提是这两个表都很简单(即列很少,可以考虑合并))

方案二:分两个表,使用id引用过来,建立联系(可用于表很复杂的情况) 

一对

多对多

创建课程表


创建课程表
DROP TABLE
IF EXISTS course;
CREATE TABLE
course (
  id
INT
PRIMARY KEY auto_increment,
  name
VARCHAR
(
20
)
);

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


创建课程学生中间表:考试成绩
DROP TABLE
IF EXISTS score;
CREATE TABLE
score (
  id
INT
PRIMARY KEY auto_increment,
  score
DECIMAL
(
3
,
1
),
  student_id
int
,
  course_id
int
,
  FOREIGN KEY (student_id) REFERENCES student(id),
  FOREIGN KEY (course_id) REFERENCES course(id)
);

新增

插入查询结果

语法(将插入语句查询语句结合到一起了):

insert into table_name [(column [, column…])] select 

查询结果集合就代替了values

案例:创建一张用户表,设计有name姓名,email邮箱,sex性别,mobile手机号字段.需要把已有的学生数据复制进来,可以复制字段有name, qq_mail.


创建用户
DROP TABLE
IF EXISTS test_user;
CREATE TABLE
test_user (
  id
INT
primary key auto_increment,
  name
VARCHAR
(
20
) comment

姓名

,
  age
INT
comment

年龄

,
  email
VARCHAR
(
20
) comment

邮箱

,
sex
varchar
(
1
) comment

性别

,
mobile
varchar
(
20
) comment

手机号
);

将学生表中的所有数据复制用户
insert into
test_user(name, email)
select
name, qq_mail
from
student;

查询

聚合查询

定义:就是”行和行”之间运算,但此处行之间运算具有一定限制,不像表达式查询(表达式查询是操作列).

聚合函数

使用聚合函数时,列和列之间,已经被”打散了”,如果查询中包含聚合函数,和非聚合的列,各自是各自的.大部分情况,聚合的列和非聚合的列是不能混用的,一种情况除外(group by(后面讲))

函数 说明
count([distinct] expr) 返回查询到数据的数量
sum([distinct] expr) 返回查询到的数据的总和,不是数字则没有意义
avg([distinct] expr) 返回查询到数据的平均值,不是数字则没有意义
max([distinct] expr) 返回查询到数据的最大值,不是数字则没有意义
min([distinct] expr)

返回查询到数据的最小值,不是数字则没有意义

案例:

count:


统计班级共有多少同学
SELECT COUNT
(*)
FROM
student;
SELECT COUNT
(
0
)
FROM
student;

统计班级收集
qq_mail
多少个,
qq_mail

NULL
的数据不会计入结果
SELECT COUNT
(qq_mail)
FROM
student;
注意:当使用count(*) 时,即使是全为null的行,也会被记录次数
        当使用count(列名)时,如果中间有为null的行,则不会被记录次数

sum:


计数成绩总分
SELECT SUM
(math)
FROM
exam_result;

不及格
< 60
总分,没有结果返回
NULL
SELECT SUM
(math)
FROM
exam_result
WHERE
math <
60
;
注意:
1.sum函数会将null自动忽略
2.sql很多时候会将字符串当作数字进行算术运算,都会将字符串转为数字
(eg.允许”100″转换为100)
avg:
SELECT AVG
(chinese + math + english)
平均总分
FROM
exam_result;

max:

返回英语最高分

SELECT MAX
(english)
FROM
exam_result;

min:


返回
&gt; 70
分以上的数学最低分
SELECT MIN
(math)
FROM
exam_result
WHERE
math &gt;
70
;

group by字句

select中使用group by可以对指定列进行分组查询.需要满足:使用group by进行分组查询时,select指定的字段必须是”分组依据字段”, 其它字段如想出现在select中,必须包含聚合函数中.

实际效果就是把这个指定的列,值相同的记录划分一组,针对这些组就可以分别聚合查询了,分组操作,往往是和”聚合“配合使用的.

 — column1是分组依据

案例:

准备测试表及数据:职员表,有id(主键), name(姓名), role(角色), salary(薪水)

id
int
primary key auto_increment,
name
varchar
(
20
)
not
null
,
role
varchar
(
20
)
not
null
,
salary
numeric
(
11
,
2
)
);
insert into
emp(name, role, salary)
values
(

马云

,

服务

,
1000.20
),
(

马化腾

,

游戏陪玩

,
2000.99
),
(

孙悟空

,

游戏角色

,
999.11
),
(

猪无能

,

游戏角色

,
333.5
),
(

沙和尚

,

游戏角色

,
700.33
),
(

隔壁老王

,

董事长

,
12000.66
);

查询每个角色的最高工资,最低工资和平均工资

select
role,max(salary),min(salary),avg(salary)
from
emp
group by
role;

having

group字句进行分组之后,需要对分组结果再进行条件过滤时,不能使用where语句,而需要having语句.(即分组之前的条件,使用where表示;分组之后的条件,使用having表示)

显示平均工资低于1500的角色和它的平均工资

select
role,max(salary),min(salary),avg(salary)
from
emp
group by
role
having
avg(salary)<
1500
;

联合查询(也称多表查询)

所谓多表联合查询,核心操作为笛卡尔积,然后指定一些条件之类的,来实现需求中的一些查询结果.

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

笛卡尔积列数为之前列数之和,行数两个表的行数之积.

但实际上,笛卡尔积得到的结果,绝大部分是无效数据.

 注意:关联查询可以对关联表使用别名.

我们来看一下下面的案例(可以尝试自己敲一下):

insert into
classes(name, `desc`)
values
(

计算机
2019

1


,

学习计算机原理
C

Java
语言数据结构算法

),
(

中文
2019

3


,

学习中国传统文学

),
(

自动化
2019

5


,

学习了机械自动化

);
insert into
student(sn, name, qq_mail, classes_id)
values
(
‘09982’
,

黑旋风李逵

,
xuanfeng@qq.com
,
1
),
(
‘00835’
,

菩提老祖

,
null
,
1
),
(
‘00391’
,

白素贞

,
null
,
1
),
(
‘00031’
,

许仙

,
xuxian@qq.com
,
1
),
(
‘00054’
,

不想毕业

,
null
,
1
),
(
‘51234’
,

好好说话

,
‘say@qq.com
,
2
),
(
‘83223’
,
‘tellme’
,
null
,
2
),
(
‘09527’
,

老外学中文

,
foreigner@qq.com’
,
2
);
);
insert into
course(name)
values
(
‘Java’
),(

中国传统文化

),(

计算机原理

),(

语文

),(

高阶数学

),(

英文
‘);
insert into
score(score, student_id, course_id)
values

黑旋风李逵
(
70.5
,
1
,
1
),(
98.5
,
1
,
3
),(
33
,
1
,
5
),(
98
,
1
,
6
),

菩提老祖
(
60
,
2
,
1
),(
59.5
,
2
,
5
),

白素贞
(
33
,
3
,
1
),(
68
,
3
,
3
),(
99
,
3
,
5
),

许仙
(
67
,
4
,
1
),(
23
,
4
,
3
),(
56
,
4
,
5
),(
72
,
4
,
6
),

不想毕业
(
81
,
5
,
1
),(
37
,
5
,
5
),

好好说话
(
56
,
6
,
2
),(
43
,
6
,
4
),(
79
,
6
,
6
),
— tellme
(
80
,
7
,
2
),(
92
,
7
,
6
);
注意:在进行多表查询时,始终贯穿着这四部曲(由繁入简),筛选符合条件的数据:
1.笛卡尔积
2.连接条件
3.根据需求指定其他条件(筛选行)
4.针对列进行精简(筛选列)

连接

定义:是内连接查询中一种特殊的等值连接,所谓的自连接就是指表与其自己当前表进行连接自己自己连接

语法:

select 字段 from 表1 别名1 [inner] 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
=

许仙

;

或者
select
sco
.score
from
student stu, score sco
where
stu
.id
=sco
.student_id
and
stu
.name
=

许仙

;
(2)查询所有同学的总成绩,以及同学的个人信息

成绩表对学生表是多对
1
关系,查询总成绩是根据成绩表的同学
id
来进行分组的
SELECT
stu
.sn
,
stu
.NAME
,
stu
.qq_mail
,
sum( sco
.score
)
FROM
student stu
JOIN
score sco
ON
stu
.id
= sco
.student_id
GROUP BY
sco
.student_id
;

(3)查询所有同学的成绩,以及同学的个人信息:

— 查询出来的都是有成绩的同学,

老外学中文

同学 没有显示
select
*
from
student stu
join
score sco
on
stu
.id
=sco
.student_id
;

学生表、成绩表、课程表
3
张表关联查询
SELECT
stu
.id
,
stu
.sn
,
stu
.NAME
,
stu
.qq_mail
,
sco
.score
,
sco
.course_id
,
cou
.NAME
FROM
student stu
JOIN
score sco
ON
stu
.id
= sco
.student_id
JOIN
course cou
ON
sco
.course_id
= cou
.id
ORDER BY
        stu.id;

连接

外连接分为左外连接和右外连接.如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接. 外连接也是多表查询的一种表现形式,一般使用比较少,属于特殊情况特殊处理

语法:

— 左外连接,表一完全显示

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

— 右外连接,表二完全显示

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

为了形象展示内连接,左外连接和右外连接的区别,下面我们来看这一组图:

案例:查询所有学生的成绩,及同学的个人信息,如果该同学没有成绩,也需要显示

— “
老外学中文

同学 没有考试成绩,也显示出来了
select
*
from
student stu left
join
score sco
on
stu
.id
=sco
.student_id
;

对应的右外连接为:
select
*
from
score sco right
join
student stu
on
stu
.id
=sco
.student_id
;

学生表、成绩表、课程表
3
张表关联查询
SELECT
stu
.id
,
stu
.sn
,
stu
.NAME
,
stu
.qq_mail
,
sco
.score
,
sco
.course_id
,
cou
.NAME
FROM
student stu
LEFT
JOIN
score sco
ON
stu
.id
= sco
.student_id
LEFT
JOIN
course cou
ON
sco
.course_id
= cou
.id
ORDER BY
stu
.id
;

自连接

顾名思义,自连接就是指在同一张表中进行查询.

案例:

显示所有”计算机原理“成绩比”Java”成绩高的成绩信息(在这个例子中,要想完成不同科目的比较,就需要比较行之间大小,因为sql是无法实现这个功能的,所以只能将行转为列)

— 先查询

计算机原理


“Java”
课程的
id
select
id,name
from
course
where
name=
‘Java’
or
name=

计算原理

;
— 让我们使用四部曲来写一下
(1)将所有内容进行笛卡尔积
select * from score as s1, score as s2;
(2)连接条件:两张表student_id相同
select * from score as s1, score as s2 where s1.student_id = s2.student_id;
(3)只筛选出要比较的课程id的数据
 select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1;
(4)找出结果
 select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

子查询

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询(这个查询方法虽然很装逼但不建议用,所以了解这个即可,自己写的时候尽量不要这样写)

举个例子:查询与”不想毕业”同班的同学:

select
*
from
student
where
classes_id=(
select
classes_id
from
student
where
name=

不想毕业

);

合并查询

在实际应用中,为了合并多个select的执行结果,可以使用集合操作符union, union all.使用union和union all时,前后查询结果集中,字段需要一致.(合并查询其实用的很少,了解即可)

案例:查询id 小于3,或者名字英文的课程

select
*
from
course
where
id<
3
union
select
*
from
course
where
name=

英文

;

或者使用
or
实现
select
*
from
course
where
id<
3
or
name=

英文

;

原文地址:https://blog.csdn.net/asdssadddd/article/details/134318145

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任

如若转载,请注明出处:http://www.7code.cn/show_31656.html

如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注