自建商城网站用什么技术好_小程序官网平台入口_百度云链接_长沙网站优化推广方案

一、数据库约束

1.1 概念以及作用

数据库的约束： 数据库的约束可以理解成，数据库提供的一种针对数据的合法性 ，验证的机制
数据库中经常会涉及到大量数据，也会涉及到大量的增删改查操作，如果数据库不提供这样的校验机制，就只能靠程序员手工来保证数据的靠谱性（只有是靠人工来保证的，基本上就认为是不靠谱的，通过自动化程序方式检测，才是更科学的）

1.2 约束类型

NOT NULL：指示某列不能存储 NULL 值。
UNIQUE ：保证某列的每行必须有唯一的值。
DEFAULT： 规定没有给列赋值时的默认值。
PRIMARY KEY ： NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY ：保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK ： 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

1.3 null 约束

创建表时，设定not null 说明列里面不能存储空值，表里这个内容就是必填项
这里创建一个student表，当我没有null约束时，是可以插入空值null的，如下：

这里我们使用 null 约束

create table 表名(字段名 字段类型 not null ,...);

查看表结构，发现Null下面是no，说明不允许存储空值

可以发现，当我们插入空值null的时候插入失败了

不仅是插入空值不行，修改也不能为空

1.4 unique 唯一约束

设定这一列的所有行的数据都得是唯一的（不能重复），每次插入/修改，都要先触发查询，如果当前插入/修改的值已经存在，就会插入/修改失败
使用unique约束之前，可以发现，数据允许重复

下面使用unique约束
查看表结构

发现Key下面有UNI标志，这里就是unique的简写，表示不能出现重复

这里可以发现，再插入第二条信息的时候报错了，因为两条信息相同，unique不允许
注意：插入/修改的信息，尽管有多个字段，只要一列中有重复的就不行

注意：加上unique约束之后，每次 插入/修改 都会需要先进行查询（因此执行效率低，但更安全），如果发现重复（已经存在），就会 插入/修改 失败 ；

1.5 default 默认值约束

指定默认值，如果不进行任何指定，默认值就是null
设置之后，后续进行指定列插入，未被指定的列就会保持默认值
使用default修饰后查看下表结构
这里
default下面就是默认值

这里可以看到，未被指定的列被设置为了默认值

1.6 primary key 主键约束（重要！！！）

主键：一条记录的身份标识，类似学号和身份证 非空（not null）且唯一（unique）
比较两个东西他俩是否是同一个，可以通过主键来区分

对id来设置主键后，查看表结构

发现id的null属性不能为空，key属性下面为PRI也就是primary，意思是为主键；

当对其进行插入2条数据时，第二条数据的id也就是带有主键属性的字段值重复了，所以插入失败

这里对id进行插入null值发现不能为空，插入失败
注意：

• 设置主键，一般都是使用数字（整数形式），很少使用字符串
• 一个表只能有一个表，但一个主键不一定只针对一个列（可以把多个列的内容联合在一起，共同组成一个主键，很少用这个功能）
• 当某个列集合了unique和not null，就成为了主键
  
• 自增主键
  对于整数类型的主键，常配搭自增长auto_increment来使用（为了解决插入主键时，容易重复的问题）。插入数据对应字段不给值时，使用最大值+1。

id INT PRIMARY KEY auto_increment

使用时，可以不用设定id值，写为null（这里的null并不是什么都不写，是让其自增；与主键不为空不冲突）


Extra下面的auto_increment就是自增主键的意思

以上可以看出，插入时，主键字段值写为null后，插入后自增主键

注意：当我手动输入主键值后，再用自增主键，则自增的主键值就会根据我手动后的主键值+1

当我把主键值为100和101两条数据删除之后，自增的主键仍是从101开始加，也就是102
（根据手动设置主键的最大值，依次往后自增，哪怕删除了也是如此）
自增主键的本质是MySQL服务器存储了当前表的主键的最大id，再进一步累加的

1.7 foreign key 外键约束

外键涉及到两个表之间的关系
如：有班级表、学生表，要求学生表中的数据，必须要在班级表中体现


这里创建了学生表和班级表，其中学生表和班级表都有字段classId，原则上来说学生表中的classId必须要和班级表中存在，这也插入的学生才是有意义的；
案例：
这里插入了3个班级

当没有外键约束时，我插入的学生，字段classId没有限制

可以看出，classid中的值不属于class表中的classid，这时候就没有了意义
为了解决这一问题，引入了外键约束
重新创建学生表，使用外键约束

foreign key (需要设置外键的字段) references 外键关联的表名 (关联的表的字段)

这样就设定好了外键，两表直接就建立了联系
当我插入的外键字段值不属于连接的表的字段值的范围，就会报错
插入失败

插入成功

不仅仅是插入，修改也要在其范围

此时，class表就对student表产生了制约；把制约别人的表class叫做父表（parent table）；把被制约的表student，称为子表（child table）
注意：被子表中外键约束的父表中的字段，若字段中存在被引用的值，则不能被修改或删除；并且此时父表也不能删除
父表中被引用的字段必须唯一（primary key或者unique特性）
删除信息失败

删除父表失败

但是如果必须要删除怎么办？
物理上无法删除，可以外加一个字段isOK，其中1表示有效数据，0表示无效数据，当我想删除某条被子表引用的信息时，可以将对应isOK置为0，达到逻辑上的删除，且不违背外键约束规则

“父亲约束儿子不能随意新增，儿子约束父亲不能随意删改”
外键约束和unique有类似的效果，都是要在插入/修改前进行查询，也会对运行效率有一定影响

1.8 check约束 （了解）

check 指定条件，插入/修改数据，数据符合条件才能插入/修改成功，不符合条件直接失败

drop table if exists test_user;
create table test_user (id int,name varchar(20),sex varchar(1),check (sex ='男' or sex='女')
);

二、表的设计

根据需求场景，能够结合场景设计出表：有几个表、每个表都有哪些属性，这些属性之间是否有一些关联关系…
设计表的通用步骤：
1.理解清楚需求场景中有哪些“实体”（entry）——本质就是面向对象涉及到的对象（需求中的关键性名词，都是针对实际问题中的一些实际情况，在代码中进行“抽象表示”）
2.理清楚实体之间的关系，很多场景中，都是涉及多个实体的，一旦多个实体存在联系了，此时表中就要做出一些额外的考量
把实体带入固定的关系当中，看看哪个更符合，实体之间一般有如下3个关系

2.1 一对一

如：在教务系统中，存在学生和账号两个实体
一个学生只能拥有一个账号，一个账号只能被一个学生所使用；
这里学生和账号就是一对一的关系

像这种表一般有以下几种设计方式：

1. 直接搞一张表，这张表既包含了学生信息，又包含了账号信息
   （这种做法仅适用于两个表的列都比较少，比较简单的情况，如果多了复杂了，就不太合适了）
2. 搞两个表student(id,name,classid,…)和account(accountId,username,password,…)；可以在学生表中设置一个外键accountId，或反之，使其关联起来

2.2 一对多

如：在教务系统中，存在学生和班级两个实体
一个学生只属于一个班级，但一个班级里有多个学生

设计方式也是设计两个表，通过外键关联

2.3 多对多

如：在教务系统中，存在学生和课程两个实体
一个学生可以上多门课程，且一个课程可以被多个学生选择

设计方式与上两种不同：
先设计两个表：student(id,name,…)和course(courseId,courseName,…)，
这里创建一张新的表student_course(id,courseId)，如插入id=1，courseid=2；就代表学生表中id为1的学生选了课程表中courseId为2的课程

上述都是固定套路

三、新增

插入和查询结合：可以把一个表的查询结果，插入到另一个表当中
已有一张student1表，内容如下：

再新建一个student2表后，向student2表插入student1表中的id和name

insert into student2 select id,name from student1;

这里注意，查询到的列数/类型要和被插入表的结构一致，其中列数要吻合；列的名字叫啥不影响

四、查询

4.1 聚合查询

之前提到的表达式查询，主要是列与列之间的运算，而聚合查询是行与行之间的运算
如：我想知道整个班级学生语文的总成绩，这里就需要行与行相加，就会用到聚合查询
对于行之间的运算，sql中提供了一些聚合函数来完成其运算

4.1.1 聚合函数

常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

注：聚合函数运算中，遇到null会跳过
distinct表示去重，如果加上，重复的数据只算一次
注意：聚合函数名和()之间不要留空格
案例：
已知表：

1. 查询表中有几条数据

select count(*) from student1;
//或者
select count(0) from student1;

也可以指定列查询，注意与count(*)是有区别的，因为聚合函数遇到null会跳过，有可能会比其少
针对某一列查询时就是看这一列有多少非null结果，而select count(*)不关注是否为null，都会计入

注意到关羽同学的math为null，所以不计入总数

2. 求所有同学的数学成绩之和

select sum(math) from student1 ;

3. 求全班同学总分的平均分

 select avg(math+chinese+english)as total from student1;

4. 求全班同学英语第一名

select name,english from student1 where english=(select max(english) from student1);

5. 查询班级中：英语、数学、语文的最高分，及其平均值

select max(chinese),max(english),max(math),avg(chinese),avg(english),avg(math) from student1;

4.1.2 group by 子句

指定某个列，针对这个列，把值相同的行，分到一组当中；
可以针对每个组，分别进行聚合查询
注意：非group by的列，不应该直接写到select查询的列当中（如果搭配聚合是可以的）
有如下一张表：

此处可以根据岗位来进行分组：开发、测试、…

1. 查询每个岗位下都有多少人

select role,count(id) from emp group by role;

执行过程：
1.先执行 select role,id from emp;

2.再根据group by role设定，按照role这一列的值，针对上述查询结果进行分组

3.针对每个组，分别进行count聚合操作

2. 计算每个部门工资的平均值

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

还可以给聚合查询指定条件

1. 聚合之前的条件

查询每个岗位的平均工资，但是刨除张三
1.每个岗位的平均工资

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

2.除去张三之后的平均工资

select role,avg(salary) from emp where name!='张三' group by role;

注意：where要放在group by的前面

2. 聚合之后的条件

查询每个岗位的平均工资，刨除平均工资超过两万的数据
这里需要用到having

4.1.3 having

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING——分组之后再进行条件判断需要用having，放到group by后面
显示平均工资低于20000的部门的平均工资

select role,avg(salary) from emp group by role having avg(salary)<=20000;

一个sql中，可以同时在分组前后都进行信息判断
计算每个岗位的平均工资，刨去马云，也刨除平均工资超过2w的部门

select role,avg(salary) from emp where name!='马云' group by role having avg(salary)<20000 ;

4.2 联合查询（多表查询）“重要!”

4.2.1 概述

以上介绍的都是“单表查询”，都是相对来说比较简单的一些操作，而多表查询难度要稍微比单表大一些
要学习多表查询，就要先理解笛卡尔积；
笛卡尔积就是简单的排列组合，依次将两个表的每行数据排列组合起来，新表的行数为之前两表行数的乘积（系统开销可能会很大，两个10条数据的表进行笛卡尔积就会产生100条临时信息）

在sql中，可以很方便的通过select来完成笛卡尔积

 select * from student,class;

上述结果中，存在一些无效数据/无意义的数据（数据不能反映客观的真实情况），如第1条数据
笛卡尔积本身就是全排列的过程，自然就会产生一些不符合实际情况的数据，但是可以去除
如：上述样例可以根据classid是否相同来作为判断基准

错误写法

如果直接写classid=classid，系统判断不出是哪张表的classid，这时候要在前面加上表名.即可
，字段名不一样时可以不加，如id和student_id

select * from student,class where student.classid=class.classid;

以上这种专门用来筛选出“有效数据”的条件叫做连接条件；上述多表查询的操作也可以称为“连接操作”

初始化测试数据：

create table classes(id int primary key auto_increment,name varchar(20),deesc varchar(100));
create table student(id int primary key auto_increment,sn varchar(20),name varchar(20),qq_mail varchar(20),classes_id int);
create table course(id int primary key auto_increment,name varchar(20));
create table score(score decimal(3,1),student_id int,course_id int);
insert into classes(name, deesc) 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);

classes表

course表

student表

score表

可以发现以上有4张表，3个实体
实体——学生表student，课程表course，班级表classes
分数表score——学生和课程之间的关联表
学生和班级是一对多；学生和课程是多对多

4.2.2 内连接

语法：

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

join on可以使其可读性更强
案例：
（1）查询“许仙”同学的 成绩

select student.name,score,course.name from student,score,course where student.name='许仙' and student.id=student_id and course_id=course.id;

除了上面的写法，还有另外一种写法
用join代替连接时的","，用on代替where

select student.name,score,course.name from student join score join course on student.name='许仙' and student.id=student_id and course_id=course.id;

（2）查询所有同学的总成绩，及同学的姓名：

select student.name,sum(score) from student,score where student.id=student_id group by name;

（3）查询所有同学的成绩，及同学的个人信息：

select student.name,course.name,score 
from student,course,score 
where student.id=student_id and course.id=course_id;

4.2.3 外连接

外连接和内连接一样，都是基于笛卡尔积的方式来计算的，不同之处在于对空值null的处理是存在区别的