数据库高级解析【Mysql】

第一章 数据完整性

1.1 数据库的完整性

*主键约束：primary key
*唯一约束：unique [key]
*自动增长：auto_increment
非空约束：not null
默认约束：default
外键约束: foreign key

1.2 实体完整性

实体完整性的作用：标识每一行数据不重复

1 主键约束（primary key）

注：每个表中要有一个主键。
特点：数据唯一，且不能为null

第一种添加方式

create table student( id int primary key, name varchar(50) );

第二种添加方式：此种方式优势在于，可以创建联合主键

create table student( id int, name varchar(50), primary key(id) ); 
create table student( classid int, stuid int, name varchar(50), primary key(classid,stuid) );

第三种添加方式：

create table student( id int, name varchar(50) );
alter table student ADD PRIMARY KEY (id);

1.3 域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)

1.3.1 数据类型

数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号 ） | 用途 |
| ------------- | ---------------- | ----------------------- | ------------------ | ------ |
| tinyint | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| smallint | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| mediumint | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT| 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| bigint | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| float | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| double | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |

日期类型:
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性
| 类型 | 大小(字节) | 范围 | 格式 | 用途 |
| --------- | ------ | ---------------------------------------- | ------------------- | ----------- |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 *2147483647* 秒，北京时间 *2038-1-19 11:14:07*，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 ,当更新数据的时候自动添加更新时间

字符串类型:
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
| 类型 | 大小 | 用途 |
| ---------- | ----------------- | ------------------ |
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

1.3.2 非空约束(not null)

1.3.3 默认值约束(defaul)

1.4 引用完整性（参照完整性）

外键约束：foreign key
格式： constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
外键列的数据类型一定要与主键的类型一致

示例：

CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default '男' ); 
create table score( 
    id int primary key, 
    score int, sid int , 
    constraint fk_score_sid foreign key(sid) references student(id) 
);

第二种添加外键方式

alter TABLEscore1 add constraint fk_stu_score foreign key(sid) references stu(id);

第二章 多表查询

2.1 多表的关系

2.1.1 一对多/多对一关系

客户和订单，分类和商品，部门和员工.
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键

2.1.2 多对多关系

学生和课程

多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

2.1.3 一对一关系

在实际的开发中应用不多.因为一对一可以创建成一张表.

两种建表原则：

唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.

主键对应：让一对一的双方的主键进行建立关系

2.2 多表查询

多表查询有如下几种：

1.合并结果集:UNION 、 UNION ALL
2.连接查询
2.1内连接 [INNER] JOIN ON
2.2外连接 OUTER JOIN ON
-左外连接 LEFT [OUTER] JOIN
-右外连接 RIGHT [OUTER] JOIN
-全外连接（MySQL不支持）FULL JOIN
2.3 自然连接 NATURAL JOIN
3.子查询

2.2.1 合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！
注意：被合并的两个结果：列数、列类型必须相同。

合并结果集有两种方式：

l UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；

l UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

2.2.2 连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),

(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过

条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

1. 内连接

语法：

select 列名 
from 表1 
inner join 表2 
on 表1.列名=表2.列名 //外键列的关系 
where.....

等价于：

select 列名 
from 表1,表2 
where 表1.列名=表2.列名 and ...(其他条件)

注:<1>表1和表2的顺序可以互换

<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。

<3>点操作符表示“的”,格式:表名.列名

<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名

三表联查：

语法(SQL标准的内连接)：

select 列名 
from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1或表2.列名=表3.列名 
where

等价于:

select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

内连接的特点：查询结果必须满足条件。

insert into student2 values(1,‘花儿’,'111111,‘1990-2-9’);

实例操作：

1.显示出花儿的考试成绩以及对应科目
select score,subjectName 
from student2,subject2,scores2
where student2.stuid=scores2.studentid and subject2.subjectid=scores2.subject 
and student2.stuname='花儿';

 2.显示出所有考试学生的信息
select * 
from student2,subject2,scores2
where student2.stuid=scores2.studentid and subject2.subjectid=scores2.subject

 3.查询出mysql的考试信息
select * 
from student2,subject2,scores2
where student2.stuid=scores2.studentid and subject2.subjectid=scores2.subject
and subject2.subjectName='mysql';

 4.查询出考试学员的总分
select stuname,SUM(score) from  student2,scores2 
where student2.stuid=scores2.studentid 
GROUP BY stuid;

 5.查询每科的平均分
select subjectName,AVG(score) from subject2,scores2
where subject2.subjectid=scores2.subject
GROUP BY subjectName;

2. 外连接

左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

注:
1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
2.主表和次表不能随意调换位置

3. 自然连接(NATURAL INNER JOIN)

自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列

2.2.3 子查询(嵌套查询)

子查询出现的位置：
 a. where后，作为条为被查询的一条件的一部分；
 b. from后，作表；
 当子查询出现在where后作为条件时，还可以使用如下关键字：
 a. any
 b. all
子查询结果集的形式：
 a. 单行单列（用于条件）
 b. 单行多列（用于条件）
 c. 多行单列（用于条件）
 d. 多行多列（用于表）

第三章：扩展

3.1 多行新增

insert into 表名(列名) values (列值),(列值),(列值)；

3.2 多表更新

 (1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他 限定条件
 (2)update 表1
 inner join 表2 on 表1.列名=表2.列名
 set 列名=列值
 where 限定条件

示例：

update employee e,salary s
set title='助工',salary=1000
where e.empid=s.empid and name='李四'

3.3 多表删除

语法：

 delete 被删除数据的表 from 删除操作中使用的表 where 限定条件

注:多张表之间使用逗号间隔

示例：

 //删除人事部的信息
 delete d,e,s from department d,employee e,salary s
 where d.depid=e.depid and s.empid=e.empid and depname='人事部';

3.4 日期运算函数

 now() 获得当前系统时间
 year(日期值) 获得日期值中的年份
 date_add(日期,interval 计算值 计算的字段);

注：计算值大于0表示往后推日期，小于0表示往前推日期

示例：

date_add(now(),interval -40 year);//40年前的日期

第四章：数据库优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致

引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin' ;

可以这样查询：

select id from t where num = 10 
union all 
select id from t where Name = 'admin';

in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3);

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3;

很多时候用 exists 代替 in 是一个好的选择

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

提示：这里可以添加本文要记录的大概内容：
例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、pandas是什么？

示例：pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。

二、使用步骤

1.引入库

代码如下（示例）：

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')
import  ssl
ssl._create_default_https_context = ssl._create_unverified_context

2.读入数据

代码如下（示例）：

data = pd.read_csv(
    'https://labfile.oss.aliyuncs.com/courses/1283/adult.data.csv')
print(data.head())

该处使用的url网络请求的数据。

---

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。