数据库、mysql数据库详解

矫情吗；* 2023-09-28 10:19 9阅读 0赞

## 数据库 ##

数据库就是数据的仓库。在数据库中提供了专门的管理系统，对数据库中的数据进行集中的控制和管理。能高效的对数据进行存储、检索。

关系型数据库：关系型数据库起源于关系模型。关系模型认为，世界是由实体和联系组成的。而关系型数据库，是一种以表作为实体，以主键和外键作为联系的数据库结构。

--------------------

**主键：**在关系型数据库中，用于标识每一行唯一性的列，称为主键列。

**主键有两个特点：不能重复，不能为空。**

**外键：**在关系型数据中，用于表达两个表之间联系的列，称为外键列。

在关系型数据库中，引用方称为从表，被引用方称为主表。从表利用外键，引用主表的主键，从而建立两个表之间的联系。所以，外键在从表中定义。

--------------------

关系型数据库的关系主要有三种：**一对一**，**一对多**，**多对多**

一对一：一条主表记录对应一条从表记录，同时一条从表记录对应一条主表记录。

一对多：一条主表记录对应多条从表记录，同时一条从表记录对应一条主表记录。

多对多：一条主表记录对应多条从表记录，同时一条从表记录对应多条主表记录，依靠中间表维护多对多的联系。

<table> 
 <thead> 
  <tr> 
   <th>多对多模型</th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>老师表</td> 
   <td></td> 
   <td></td> 
   <td>中间表</td> 
   <td></td> 
   <td></td> 
   <td>学生表</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>老师编号</td> 
   <td>姓名</td> 
   <td></td> 
   <td>老师编号</td> 
   <td>学生编号</td> 
   <td></td> 
   <td>学生编号</td> 
   <td>姓名</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>李老师</td> 
   <td></td> 
   <td>1</td> 
   <td>1</td> 
   <td></td> 
   <td>1</td> 
   <td>张1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>王老师</td> 
   <td></td> 
   <td>1</td> 
   <td>2</td> 
   <td></td> 
   <td>2</td> 
   <td>张2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>胡老师</td> 
   <td></td> 
   <td>1</td> 
   <td>3</td> 
   <td></td> 
   <td>3</td> 
   <td>张3</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>2</td> 
   <td>4</td> 
   <td></td> 
   <td>4</td> 
   <td>张4</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>2</td> 
   <td>1</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>2</td> 
   <td>2</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>2</td> 
   <td>3</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>3</td> 
   <td>4</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>3</td> 
   <td>1</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>3</td> 
   <td>2</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>一般情况下多方从表一方主表</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

--------------------

### DBMS ###

关系型数据库只是一个保存数据的容器，大多数数据库依靠一个称为数据库管理系统（Database Management System，简称DBMS）的软件来管理数据库中的数据。

### 数据库引擎 ###

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据库引擎进行创建、查询、更新和删除数据。

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

**InnoDB** 是事务型数据库的首选引擎，提供提交、回滚、崩溃恢复能力的事务安全能力，并可以实现并发控制。InnoDB 是默认的MySQL引擎。

**MyISAM** 拥有较高的插入、查询速度，但不支持事务。

**MEMORY** 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。主要用于数据量不大的临时数据。

### 结构化查询语言 ###

SQL是结构化查询语言（Structured Query Language）的英文缩写，是一种用于管理关系型数据库，并与数据库中的数据进行通讯的计算机语言。

SQL 与 RDBMS 协同工作，来定义数据库的结构、存储数据、操纵数据、获取数据、控制对数据的访问以及确保数据的完整性。

#### SQL 分类： ####

**数据定义语言（DDL）**\--- 创建、修改、删除数据库的内部数据结构 （define 定义）

**数据查询语言（DQL）**\--- 用于数据库中的数据查询 （query 查询）

**数据操作语言（DML）**\--- 用于数据库中数据的修改，包括添加。删除、修改等 （manipulation 操纵）

**数据控制语言（DCL）**\--- 用于控制数据的访问权限 （control 控制）

## SQL执行顺序 ##

后执行的子句，可以使用前执行子句的计算结果、定义的别名等  
执行顺序：  
1.执行from 查询表  
2.执行where 进条件筛选  
3.执行group by 分组  
4.执行select 查询列  
5.执行having 对分组结果进行筛选  
6.执行order by 排序  
7.执行limit 返回限定行

## 数据完整性 ##

**数据完整性=数据准确性+数据可靠性**

**为了防止垃圾数据的产生，从而影响数据库的执行效率**

**数据完整性分为：实体完整性、域完整性、引用完整性、自定义完整性**

#### **实体完整性：** ####

保证每行所代表的实体能互相区别，不能存在两条一模一样的记录。  
实体完整性实现方式：1、主键约束 2、唯一约束

**主键（primary key）**是表中的一到多个列，主键列不能为空，也不能重复。一个表中只能有一个主键。  
适合做主键的列，需要满足四个条件：

1、值必须唯一

2、值不能为空

3、不能带业务含义

4、值不能变化

**唯一约束** 是指给定列的所有值必须唯一，该列在表中每一行的值必须唯一。它和主键约束的区别在于该列可以为空。并且可以在一张表中给多个列设置唯一的约束。

create table 表名（
       phone VARCHAR（50） unique
     ）

#### 域完整性： ####

保证指定列的数据的有效性（mysql对域完整性支持不好）

实现方式：非空约束、默认值、检查约束

**非空约束：**制定列的值,不能插入null值。not null  
**默认值：**添加记录时，如果该列的值不做插入，那么以默认值插入。 default  
**检查约束：**检查插入列数据的有效值。check（mysql不支持）

CREATE TABLE t_em(
    	pk_emle INT PRIMARY KEY AUTO_INCREMENT;
        e_name VARCHAR(20) NOT NULL, -- 非空约束
        e_status ENUM('在职','离职') DEFAULT '在职' -- 默认值
        e_money INT CHECK(e_money>1000 AND e_money<10000) -- 检查约束
    );

#### 引用完整性： ####

从表当中外键出现的数据，必须在主键列出现  
实现方式：外键约束

外键和外键约束：  
外键是描述表与表之间联系的一个列。是表中的列  
外键约束是指在外键列上加上一个约束，强制外键列引用的数据是正确的。如果违反该约束，则不允许该条数据修改或插入。  
注意：没有外键约束，不表示没有外键

-- 添加外键约束
    ALTER TABLE t_car ADD CONSTRAINT fk_1 FOREIGN KEY(fk_manId)
    REFERENCES t_man(pk_manId);

## 数据库三范式 ##

**第一范式：**行不能重复（每条记录不能完全一样），列不可再分（一条记录的列只能有一个值）。  
**第二范式：**非主独立。非主键列必须依赖于主键列。二范式要求一个表只描述一个实体。  
**第三范式：**非主独立。非主键列之间不能有依赖关系。

## 数据库数据类型 ##

数据库数据类型决定了表中 列 存储数据的类型。分为五种：

整型（int）

浮点型（float、double、decimal(m、d)）

字符串型（char（定长字符串，如果内容不满以空格填充）、Varchar（可变长字符串，以内容的长度作为数据的长度））

日期型（date（年月日）、time（时分秒）、datetime（年月日时分秒））

二进制型（BLOB二进制型的长文本数据）

## MYSQL 操作 ##

#### **创建数据库** ####

CREATE DATABASE mydb;--mydb为数据库名

#### **删除数据库** ####

DROP DATABASE mydb;

#### **创建表** ####

CREATE TABLE t_student(
    	-- PRIMARY KEY 标识该列为主键列
    	-- pk表示主键列的缩写
    	-- AUTO_INCREMENT 表示自动增长列，该列的内容由DBMS分配
    	pk_studentId INT PRIMARY KEY AUTO_INCREMENT,
    	s_name VARCHAR(20),-- 姓名
    	s_birthday DATE,   -- 生日
    	s_money INT, 	   -- 金额
    	-- 性别 enum称为枚举，表示该列的值只能是选项中的值
    	s_gender ENUM('男','女'),
    	s_phone VARCHAR(20)-- 电话`db``db`
    );

#### 删除表 ####

DROP TABLE t_student;

#### 查询数据 ####

SELECT * FROM t_student;--*表示所有列
    SELECT s_name,s_phone FROM t_student;-- 查询指定列

#### 添加记录 ####

INSERT INTO t_student(s_name,s_birthday,s_money,s_gender,s_phone)
    VALUES('张无忌','1997-8-21',3000,'男','13272934889');
    
    --添加多条记录，只对mysql有效
    INSERT INTO t_student(s_name,s_birthday,s_money,s_gender,s_phone)
    VALUES('赵敏','1998-8-8',12000,'女','15288889999'),
    ('岳不群','1998-8-8',12000,'男','15287789889');

#### 修改记录 ####

-- 将张无忌的工资修改为4000，电话修改为13566664444
    UPDATE t_student SET s_money=4000,s_phone='13566664444' WHERE s_name='张无忌';

#### 删除记录 ####

DELETE FROM t_student;
    TRUNCATE TABLE t_student;

**区别：**  
1、TRUNCATE TABLE 会清空表内所有数据，而DELETE FROM 可以按条件删除；  
2、DELETE 会记录日志，意味着删除后的数据还可以恢复，但是效率低。TRUNCATE 不会记录日志，  
删除后数据不能恢复，但是效率高。

**条件删除：**

-- 删除编号是2的学生
    DELETE FROM t_student WHERE pk_studentId=2;
    
    -- 删除编号大于3的学生
    DELETE FROM t_student WHERE pk_studentId>3;

#### 操作列 ####

-- 添加学历列
    ALTER TABLE t_student ADD s_edu VARCHAR(20);
    
    -- 修改列
    ALTER TABLE t_student CHANGE s_edu s_eduInfo VARCHAR(20);
    
    -- 删除列
    ALTER TABLE t_student DROP COLUMN s_eduInfo;

#### 条件查询 ####

**if 函数** 类似于三目运算符 会显示符合条件的查询结果列

注意：该列只做查询显示，并没有在表中实际插入生成

-- 显示所有用户，并且显示该用户是否万元户
    SELECT stu.*,IF(s_money>=10000,'万元户','千元户') rank  FROM t_student stu;

**distinct 去除重复记录** 相同记录只显示一次

SELECT DISTINCT s_gender FROM t_student;

**limit 返回限定行** 第一个参数为起始记录数（第一条为0），第二个参数为显示记录数

--显示前三条记录
    SELECT * FROM t_student LIMIT 0,3;

**单条件查询 “ = ”** 放在**where** 后面，表示比较；放在**set** 后面，表示赋值。  
**多条件查询**  **and** （并且，两个条件都为真，结果为真，有一个为假，结果都为假）  
 **or** （或者，两个条件有一个为真，结果都为真，只有两个条件都为假，结果才是假）

-- 查询90后的人 
    SELECT * FROM t_student WHERE s_birthday>='1990-1-1' AND s_birthday<'2000-1-1';
    
    -- 查询张无忌和雷震子的信息
    SELECT * FROM t_student WHERE s_name='张无忌' OR s_name='雷震子';
    --即使没有‘雷震子’，也会显示张无忌的信息

**定义集合范围： in 或 not in**

-- 查询张无忌和雷震子的信息
    SELECT * FROM t_student WHERE s_name IN('张无忌','雷震子');
    
    -- 查询 除了 张无忌和雷震子的信息
    SELECT * FROM t_student WHERE s_name NOT IN('张无忌','雷震子');

**查询null 值不能用 = 、！= 只能用 is 、 is not**

-- 将张无忌和雷震子电话号码设置为空
    UPDATE t_student SET s_phone=NULL WHERE s_name IN('张无忌','雷震子');
    
    -- 查询没有电话号码的人
    SELECT * FROM t_student WHERE s_phone IS NULL;
    
    -- 查询有电话号码的人
    SELECT * FROM t_student WHERE s_phone IS NOT NULL;

#### 模糊查询   \-- “ \_ ” 通配符，匹配任何单个字符   \-- “ % ” 通配符，匹配零个或多个任意字符 ####

-- 查询所有姓张的人
    SELECT * FROM t_student WHERE s_name LIKE '张%'
    
    -- 查询姓张，姓名是两个字的人
    SELECT * FROM t_student WHERE s_name LIKE '张_';

#### 排序 ####

将表中的记录按某个列进行排序排列，可以指定升序（asc）或降序（desc），如果不做指定，默认为升序。

-- 单列排序
    -- 查询所有人，并且按工资降序排列
    SELECT * FROM t_student ORDER BY s_money DESC;
    
    -- 多列排序：先按某个列进行排序，如果两条记录中，该列的值相同，再按第二个列进行排序
    -- 查询所有人，并且按工资排序，如果工资相同，按成绩排序
    SELECT * FROM t_student ORDER BY s_money DESC,s_grade DESC;

#### 条件判断语句 ####

\-- 在mysql中，允许根据查询结果值的不同，返回不同的内容。  
\-- 双分支语句，相当于三目运算符，条件满足，返回一个结果。条件不满足，返回另一个结果。

-- 药品类商品上涨5元，食品类商品上涨2元，酒类商品上涨10元
    UPDATE t_product SET s_price=s_price+(CASE WHEN s_productType='食品类' THEN 2
    WHEN s_productType='药品类' THEN 5
    ELSE 10 END);

#### 聚合函数 ####

**count：**统计行的数量

**count（\*）：**统计所有符合条件的记录。

-- 统计有几种商品
    SELECT COUNT(*) FROM t_product;
    
    -- count（列名）：只统计非空列
    -- 统计参考学生数量
    SELECT COUNT(S_grade) FROM t_student;
    
    -- cont（distinct 列名）只统计非空、不重复列
    -- 统计有几类商品
    SELECT COUNT(DISTINCT s_productType) FROM t_product;

**sum（列名）：**统计某个列的总和

-- 统计商品的总价格
    SELECT SUM(s_price) FROM t_product;

**avg(列名) ：**统计某个列的平均值，只统计非空

-- 统计学生平均分
    SELECT AVG(s_grade),SUM(s_grade)/COUNT(*) FROM t_student;
    --前者表示参考学生平均分，后者表示所有学生平均分

**max（列名）：**统计某个列最大值

**min（列名）：**统计某个列最小值，不统计空值

-- 统计最高分和最低分
    SELECT MAX(s_grade),MIN(s_grade) FROM t_student;

#### 分组查询（group by） ####

\-- 分组就是将指定规则中相同的数据进行“小区域”（组）的划分，然后针对若干个小区域进行数据处理  
\-- 分组执行在where 以后，是对where条件筛选的结果进行分组

-- 统计每一类商品的数量
    SELECT s_productType,COUNT(*) '数量' FROM t_product GROUP BY s_productType;
    
    -- 统计价格在50元以上，每类商品的数量
    SELECT s_productType,COUNT(*) FROM t_product WHERE s_price>=50 
    GROUP BY s_productType;
    
    -- 多列分组：在group by 后面可以跟多个列，多个列的值完全相同算一个组
    -- 统计每个厂家生产不同类别商品数量
    SELECT s_factory,s_productType,COUNT(*) FROM t_product 
    GROUP BY s_factory,s_productType;

#### having ####

having 是对分组以后的结果进行筛选，执行在分组之后

-- 查看商品类别数量在3个以上的类别
    SELECT s_productType,COUNT(*) FROM t_product GROUP BY s_productType 
    HAVING COUNT(*)>3;

**注意：where和having的区别。where执行在分组之前，不能跟聚合函数。having执行在分组之后，是对分组结果进行筛选，一般使用聚合函数进行筛选。**

#### 子查询 ####

在一个查询语句中再嵌套一个查询语句，称为子查询

\-- 子查询的分类  
\-- 相关子查询 和 非相关子查询  
\-- 相关子查询：在外查询中，每查询一条记录，需要重新做一次子查询，这种称为相关子查询。相关子查询的执行，依赖于外部查询的数据，外部查询返回一行，子查询就执行一次。所以，相关子查询效率很低。  
\-- 非相关子查询：在主查询中，子查询只需要执行一次，子查询结果不再变化。子查询结果供帐户查询使用，这种查询方式称为非相关子查询。  
\-- 区分相关子查询，还是非相关子查询。主要看子查询是否能够独自运行，子查询是否依赖外查询的查询结果。如果子查询需要依赖外查询查询结果，不能单独运行，就是相关子查询。如果子查询不需要依赖外查询查询结果，可以单独运行，就是非相关子查询。

-- 查询比参苓白术散价格更高的单品
    SELECT * FROM t_product WHERE s_price>
    (SELECT s_price FROM t_product WHERE s_name='参苓白术散');
    
    -- 子查询嵌在from之后，相当于临时表。也就是将子查询查询的结果，当作表来用。
    -- 注意，子查询的结果必须加别名
    SELECT * FROM
    (SELECT e_name,(SELECT d_info FROM t_dept WHERE pk_deptId=fk_deptId) 
    FROM t_employee)
    eminfo WHERE e_name='张勇';
    
    -- 在where后嵌入子查询。如果子查询的结果是单行单列（查询结果只有一个值），
    -- 外查询才可以使用=、！=、< 、> 、>=、<=
    
    -- 查询和刘波一个部门的员工
    SELECT * FROM t_employee WHERE fk_deptId=
    (SELECT fk_deptId FROM t_employee WHERE e_name='刘波')
    
    -- 如果子查询返回的值是多行多列，外查询只能用in 或 not in
    
    -- 查询张勇和刘波同一个部门的员工
    SELECT * FROM t_employee WHERE fk_deptId IN
    (SELECT fk_deptId FROM t_employee WHERE e_name IN('张勇','刘波'))
    
    -- all：和子查询的结果逐一比较，必须全部满足时表达式的值才为真
    
    -- 查询所有比食品类商品价格更高的商品
    SELECT * FROM t_product WHERE s_price>
    ALL(SELECT s_price FROM t_product WHERE s_productType='食品类');
    
    -- 可替换
    SELECT * FROM t_product WHERE s_price>
    (SELECT MAX(s_price) FROM t_product WHERE s_productType='食品类');
    
    -- any：和子查询的结果逐一比较，其中一条记录满足条件则表达式就为真
    
    -- 查询比任意食品类商品价格高的商品
    SELECT * FROM t_product WHERE s_price>
    ANY(SELECT s_price FROM t_product WHERE s_productType='食品类')AND
    s_productType!='食品类';
    
    -- 可替换
    SELECT * FROM t_product WHERE s_price>
    (SELECT MIN(s_price) FROM t_product WHERE s_productType='食品类')AND
    s_productType!='食品类';

**-- exists 判断子查询是否存在数据，如果存在表达式则为真，反之为假  
\-- not exists 相反**

#### 删除主表记录 ####

删除主表记录时，如果该主表记录有从表记录引用。由于有外键约束存在，则无法删除主表记录。  
删除主表记录有三种方式  
1、级联删除。先将主表记录中关联的从表记录全部删除，再删除从表记录  
2、外键置空。先将主表记录中关联的从表记录外键设置为null，然后再删除主表记录  
3、改变状态。将主表记录添中标识符（有效/无效）。删除主表记录时，将状态修改为无效

#### 联表查询 ####

如果数据来自多个表，那么可以采用联接查询的方式来实现。表联接就是指将多个表联合在一起实现查询效果。

笛卡尔乘积是指将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积。

表联接分类：内联接、外联接、自联接

**内联接：**只能查询两个表之间有关联记录的数据。

-- 查询所有汽车，以及该汽车的主人姓名
    SELECT c.*,m.`m_name` FROM t_man m JOIN t_car c ON m.`pk_manId`=c.`pk_carId`;
    SELECT c.*,m.`m_name` FROM t_man m,t_car c WHERE m.`pk_manId`=c.`fk_manId`; 
    -- 如果有汽车没有主人，则不在查询结果中。内联接只能查询有主人的汽车。

**外联接：**可以查询出一个表中，所有符合条件的记录，无论该记录是否有关联数据。

left 或 right 实现

-- 显示所有公民，以及公民拥有的汽车数量
    SELECT m.*,COUNT(c.`c_type`) FROM t_car c LEFT JOIN t_man m ON 
    m.`pk_manId`=c.`fk_manId` GROUP BY c.`fk_manId`;

#### 视图 ####

视图可以看作是sql语句的封装，可以看做是临时表，视图查询的结果会随着真实表数据的变化而变化。视图只提供查询功能，不提供数据修改功能。

CREATE VIEW v_partyNum AS
    SELECT p.`partyName`,COUNT(m.`mspName`) FROM msp m RIGHT JOIN party p 
    ON m.`party`=p.`partyCode` GROUP BY p.`partyCode`;
    
    SELECT * FROM v_partyNum;