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

1.2 NULL约束

创建表的时候，可以指定某列不为空：

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

1.3UNIQUE:唯一约束

unique 唯一.不允许存在两行数据，在这个指定列上重复

drop table student1;
create table student1(id int unique, name varchar(20));

如果插入重复数据就会报错。

entry 在这里的意思是数目，记录；

1.4 DEFAULT：默认值约束

drop table student1;
create table student1(id int unique, name varchar(20) default '未命名');

使用指定列插入的时候，未被指定的列就会按照默认值来填充。
其中如果不修改默认值，默认情况下默认值就是null。

设定默认值：

1.5 PRIMARY KEY：主键约束

drop table student1;
create table student1(id int primary key , name varchar(20) );

PRIMARY KEY 主键约束，主键就是一条数据的身份标识~
通过这个约束，来指定某个列作为主键 1)非空 2)不能重复
一个表，只能有一个主键

自增主键：

主键，往往是一个整数类型的id，要求不能重复~
允许客户端再插入数据的时候，不手动指定主键的值，而是交给MySQL自行分配~确保分配出来的这个主键的值，是和之前不重复的~
非常简单粗暴~MySQL按照“自增”的形式，来分配主键id
必须搭配整数类型的主键使用~

这种插入操作是ok的！！！

此时id列是自增主键，设置为空，意思是让数据库自行分配一个id~~

自增主键，也是可以手动指定值的~

如果再插入一行空值的，id 的数值是4,5....还是11？？？

很明显id 是11

id分配是： MySQL会记录当前id最大值，下一个分配的id，就会在之前最大值的基础上自增

前面的4，5，6....就用不上了

编写代码的时候不要太‘小气’、‘抠搜’~~

一共可以表示42亿给数据，就算浪费几万个，也没啥事~~

如果MySQL是一个单个节点的系统，基于上策没有问题
如果MySQL是一共分布式系统，对于分布式系统下，像分配这种表示唯一性的id，就不能依赖MySQL的自增主键了~（分布式是搞了好几个主机，每个主机都有MySQL，让他们相互配合，此时，自增主键，就无法保证唯一性了，因为每个主机上的MySQL只知道自己这里存储的数据的最大值，就可能出现重复的情况~）为了解决上述问题，市面上也有一些分布式id的生成算法~ 核心公式： 目标是保证系统中的每个节点，生成的id都是唯一的，那么就可以把id看做出一共字符串，这个字符串由下面这个几个部分拼接成的~~
主机编号/机房编号
时间戳
随机因子 生成的字符串格式id就能保证分布式系统下的唯一性了~~

1.6 FOREIGN KEY：外键约束

外键用于关联其他表的主键或唯一键:
create table class(id int primary key auto_increment, name varchar(20));
drop table student1;
create table student1(id int primary key , name varchar(20),class_id int, foreign key (class_id) references class(id) );
先创建班级表
在创建学生表，一个学生对应一个班级，一个班级对应多个学生，用id作为主键，class_id作为外键，关联班级表的id
创建外键约束的时候是修改子表的代码，父表代码是不受影响的

插入或者修改子表中受约束的这一列的数据，就需要保障插入/修改的结果必须在父表中存在! 如果插入的数据不在就会出现以下的情况：
删除/修改父表中的记录，就需要看看这个记录是否在子表中使用，如果使用了，则不能进行删除/修改！
表中的primary换成unique也是可以的

1.7 CHECK约束

MySQL使用时不报错，但是忽略该约束：

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

2.表的设计

2.1 一对一

2.2一对多

2.3多对多

2.4没关系

3.新增

插入查询：

insert into 表(culumn,column2) select culumn,column2 from 表2;

创建表student1
create table student1 (id int ,name varchar(20));
创建表student2
create table student2 (id int ,name varchar(20));
向表student1插入数据
insert into student1 values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
将select查询的表student1的数据结果插入表student2
insert into student2 select * from student1;

此处的select查询的结果，得和你插入的那个表，得相对应（列的数目，类型，约束得匹配）

否则：

4.查询

4.1聚合查询

4.1.1常见的聚合函数

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

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

COUNT

--查询有多少个人
select count(*) from exam_result;
--查询name不为空的多少个
select count(name) from exam_result;

SUM

--查询语文成绩总和
select sum(chinese) from exam_result;

--查询名字总和
select sum(name) from exam_result;

AVG
```
select avg(math) from exam_result;
```
MAX
```
select max(math) from exam_result;
```
MIN
```
select min(math) from exam_result;
```

在sql里，聚合函数和（）必须紧紧挨在一起！！！不能有空格！！！

4.1.2GROUP BY 子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是 “ 分组依据字段 ” ，其他字段若想出现在 SELECT 中则必须包含在聚合函数中。

select column1, sum(column2), .. from table group by column1,column3;

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

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

在分组查询中，select中指定的列，必须是当前的group by 指定的列

如果select中想用到其他的列，其他的列必须放到聚合函数中

否则直接写，此时查询的结果是无意义的！！！

分组之前的条件where

--查询每个岗位的平均工资，除去周六的
select role,avg(salary) from emp where name !='周六' group by role;

分组之前的条件having

--显示平均工资低于1500的角色和它的平均工资
select role,avg(salary) from emp group by role having avg(salary)<1500;

可以结合使用

--查询每个岗位的平均薪资除了周六的，出去平均薪资小于1500的
select role,avg(salary) from emp where name !='周六' group by role having avg(salary)<1500;

4.2联合查询

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

针对任意的两张表都可以进行计算笛卡儿积，但一般来说如果两个表没有任何联系，计算的结果也是无意义的~~~

就像下图排列组合一起，然后选出适配的、 有意义的（班级表的classId = 学生表的clasId）

先使用学生表和班级表进行笛卡儿积
筛选有意义的

查询每个同学的总成绩

-- 创建班级表
CREATE TABLE class (
    classid INT PRIMARY KEY,
    classname VARCHAR(50) NOT NULL
);

-- 插入班级数据
INSERT INTO class (classid, classname) VALUES
(1, '高一(1)班'),
(2, '高一(2)班'),
(3, '高二(1)班'),
(4, '高二(2)班');

-- 创建学生表
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    classid INT,
    FOREIGN KEY (classid) REFERENCES class(classid)
);

-- 插入学生数据
INSERT INTO student (id, name, classid) VALUES
(101, '张三', 1),
(102, '李四', 1),
(103, '王五', 2),
(104, '赵六', 3);

-- 创建成绩表
CREATE TABLE score (
    scoreid INT PRIMARY KEY AUTO_INCREMENT,
    studentid INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    grade DECIMAL(5,2) NOT NULL,
    FOREIGN KEY (studentid) REFERENCES student(id)
);

-- 插入成绩数据（每个学生3门科目）
INSERT INTO score (studentid, subject, grade) VALUES
-- 张三的成绩
(101, '数学', 92.5),
(101, '语文', 88.0),
(101, '英语', 95.5),

-- 李四的成绩
(102, '数学', 78.0),
(102, '语文', 92.5),
(102, '英语', 85.0),

-- 王五的成绩
(103, '数学', 89.5),
(103, '语文', 76.0),
(103, '英语', 91.0),

-- 赵六的成绩
(104, '数学', 97.0),
(104, '语文', 88.5),
(104, '英语', 93.5);

--查询各个同学的总成绩
select student.id as studentId,student.name as studentName,sum(score.grade) as SUM,class.classname from student,class,score where student.classid=class.classid and student.id=score.studentid group by student.id,student.name,class.classname;

4.2.1内连接

是只返回两个表中匹配成功的记录

工作原理：像一个过滤器，只保留两个表中连接条件匹配的记录

结果集：只包含两个表交集部分的数据

图示：

内连接产生的结果移动是两个表都存在的数据（公共的部分）

语法：

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

上述的查询各个同学的成绩可以用这句：

select  s.id AS studentId,
    s.name AS studentName,
    SUM(sc.grade) AS totalScore,
    c.classname
FROM 
    student s
 JOIN class c ON s.classid = c.classid
 JOIN score sc ON s.id = sc.studentid
GROUP BY 
    s.id, s.name, c.classname;

4.2.2外连接

当两个表的数据，左侧表的每一条记录都能在右侧找到对应的

右侧表的每一条记录都能在左侧找到对应的

此时，针对这两个表，进行外连接和内连接的结果完全相同！！！

4.2.2.1左外连接

当这两表对应不上，内外连接就产生差别了！！

定义：返回左表的所有记录 + 右表匹配的记录

未匹配处理：右表列显示为 NULL

图示：

（查询王五成绩是null）
select * from student left join score on student.id=score.id;

4.2.2.2右外连接

定义：返回右表的所有记录 + 左表匹配的记录

未匹配处理：左表列显示为 NULL

图示：

查询成绩，对4号来说，虽然没有在student表体现出来，但是能查出成绩，姓名，学号为null）
select * from student right join score on student.id=score.id;

4.2.3 全连接

定义：返回左表和右表的所有记录

未匹配处理：缺失匹配的列显示为 NULL

图示：

这个操作，mysql不支持，oracle能支持！！
select * from student outer join score on student.id=score.id;

4.2.4自连接

本质上是自己和自己做笛卡儿积
是把“行之间的关系”改成“列之间的关系”

- emp_id: 员工ID，主键

- name: 员工姓名

- manager_id: 上级经理的ID（外键引用本表的emp_id）

- position: 职位

--查询所有员工及其经理姓名：
SELECT 
    e.name AS employee,
    e.position,
    m.name AS manager
FROM 
    employees e
LEFT JOIN 
    employees m ON e.manager_id = m.emp_id;

4.2.5子查询（嵌套查询）

查询和张三所在的班级ID

SELECT classes_id FROM student WHERE name = '张三';

查询不想放假的和张三同班的同学不包括张三

SELECT name FROM student WHERE classes_id = (子查询结果) AND name != '张三';

合并：

SELECT name FROM student WHERE classes_id = (SELECT classes_id FROM student WHERE name = '不想放假') AND name != '张三';

4.2.6合并查询

把多个select查询的结果集合，合并成一个集合~

能合并的迁移是两个查询的结果集，的列对应~

union（交集）

union all（并集）

-------------------------------------------~~~~学会释放压力~~~~--------------------------------

标签

#mysql #数据库