MySQL基础不牢地动山摇— 在线教育从后端到APP全链路学习实战

本文链接：https://blog.csdn.net/tuyuan2012/article/details/114009898

数据库基础

1.1 为什么要使用数据库

持久化(Persistence)：把数据保存到可掉电式存储设备中以供之后使用。持久化的大多数时候是将内存中的数据存储在数据库中，当然也可以存储在磁盘文件、XML数据文件中。方便管理数据（例如：快速的检索等）;
在iOS端App中的持久化方案：Plist文件、Preference、NSKeyedArchiver、SQLite、FMDB、CoreData。
在这里插入图片描述

1.2 什么是数据库

DB：数据库（Database）即存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS：数据库管理系统（Database Management System）：是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。
目前互联网上常见的数据库管理软件有Sybase、DB2、Oracle、MySQL、Access、Visual Foxpro（面向对象型）、MS SQL Server、Informix、PostgreSQL（最符合SQL标准，但是性能差）这几种。以下是2020年DB-Engines Ranking 对各数据库受欢迎程度进行调查后的统计结果：（查看数据库最新排名: https://db-engines.com/en/ranking ）。
在这里插入图片描述
MySQL：开源免费的数据库，小型的数据库，已经被Oracle收购了。MySQL6.x版本也开始收费。后来Sun 公司收购了 MySQL，而 Sun 公司又被 Oracle 收购。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，使得很多互联网公司选择了MySQL作为网站数据库（Facebook, Twitter, YouTube，阿里的蚂蚁金服，去哪儿，魅族，百度外卖，腾讯）。

1.3 关系数据库

关系型数据库，采用关系模型来组织数据，简单来说，关系模型指的就是二维表格模型。
类似于Excel工作表。非关系型数据库，可看成传统关系型数据库的功能阉割版本，基于键值对存储数据，通过减少很少用的功能，来提高性能。
关系型数据库设计规则：
遵循ER模型和三范式

E entity 代表实体的意思对应到数据库当中的一张表
R relationship 代表关系的意思
三范式：1、列不能拆分 2、唯一标识 3、关系引用主键
具体体现：
将数据放到表中，表再放到库中。
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python中 “类”的设计。
表由列组成，我们也称为字段。每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和字段名。每个字段类似java 或者python中的“实例属性”。
表中的数据是按行存储的，一行即为一条记录。每一行类似于java、OC、Swift 、Python等高级语言中的“对象”。

2 MySQL安装和环境配置

参考《开发环境搭建—在线教育从App到后端全链路学习实战》

3 MySQL的使用

3.1 服务的启动和停止

关系型数据库分为桌面文件共享型数据库，例如Access，和C/S架构的网络共享型数据库，例如：MySQL，Oracle等。MySQL软件的服务器端必须先启动，客户端才可以连接和使用使用数据库。

3.1.1 方式一：图形化方式

系统偏好设置里，找到“MySQL图标”点击进入MySQL服务的控制页面，点击“Start MySQL Server”启动或“Stop MySQL Server”停止MySQL数据服务。
在这里插入图片描述

3.1.2 方式二：命令行

sudo /usr/local/mysql/support-files/mysql.server start 
sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server restart

3.2 客户端登录

3.2.1 方式一：命令行

mysql -h  主机IP地址  -P  端口号  -u 用户名 -p回车
Enter Password: 密码

在这里插入图片描述

3.2.2 方式二：可视化工具

例如：Navicat Preminum、SQLyogEnt等工具。还有其他工具：mysqlfront、phpMyAdmin。我们推荐使用Navicat Preminum，前面已经教大家如何安装了。
点击“Navicat Preminum”启动程序。新建连接：
在这里插入图片描述
选择“MySQL”，输入相关ip、用户名、密码等信息:

4 MySQL数据类型

4.1 常使用的数据类型如下表：

在这里插入图片描述

4.2 详细的数据类型如下表：

在这里插入图片描述

5 SQL的概念

5.1 什么是SQL

Structured Query Language 结构化查询语言

5.2 SQL作用

5.3 SQL语句分类

Data Definition Language (DDL 数据定义语言) 如:建库，建表
Data Manipulation Language(DML 数据操纵语言)，如:对表中的记录操作增删改
Data Query Language(DQL 数据查询语言)，如:对表中的查询操作
Data Control Language(DCL 数据控制语言)，如:对用户权限的设置

5.4 MySQL的语法

每条语句以分号结尾
SQL中不区分大小写，关键字中认为大写和小写是一样的
3种注释：

6 DDL操作数据库

6.1 创建数据库

6.1.1 创建数据库的几种方式

创建数据库

CREATE DATABASE 数据库名;

判断数据库是否已经存在，不存在则创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

创建数据库并指定字符集

CREATE DATABASE 数据库名DEFAULT CHARACTER SET 字符集;

6.1.2 案例

#直接创建数据库 maya
CREATE DATABASE maya;

#判断是否存在数据库maya，如果没有则创建
CREATE DATABASE IF NOT EXISTS maya;

#创建数据库maya并指定字符集为gbk
CREATE DATABASE maya DEFAULT CHARACTER SET GBK;

6.2 查看数据库

#查看所有的数据库
SHOW DATABASE;

#查看某个数据库的定义信息
show create database maya;

6.3 修改数据库

6.3.1 修改数据库默认的字符集

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

6.4 删除数据库

DROP DATABASE 数据库名;

6.5 使用数据库

6.5.1 查看正在使用的数据库

#使用的一个mysql中的全局函数
SELECT DATABASE();

6.5.2 使用/切换数据库

USE 数据库名;

6.5.3 案例

# 查看正在使用的数据库
select database();

# 改变要使用的数据库
use maya;

7 DDL操作表结构

上面我们主要讲的是通过终端进行数据库的操作，在实际开发中我们主要是借助工具（比如：Navicat），我们先通过Navicat创建moya数据库，方便后面进行表的相关操作。
在这里插入图片描述

7.1 创建表

7.1.1 创建表的格式

CREATE TABLE 表名 (
字段名0  字段类型0,
字段名1  字段类型1
);

7.1.2 关键字说明

创建表的关键字	描述
CREATE	创建
TABLE	表

7.1.3 案例

创建学生表t_stu包含sid、name、gender等字段

#创建学生表
CREATE TABLE t_stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(100) NOT NULL,
gender CHAR NOT NULL DEFAULT '男',
card_id CHAR(18) NOT NULL UNIQUE,
birthday DATE,
address VARCHAR(200)
);

在这里插入图片描述

7.2 查看表

7.2.1 查看某个数据库中的所有表

USE 数据库名;
SHOW TABLES;

7.2.2 查看表结构

DESC 表名;

7.2.3 案例

USE maya;
SHOW TABLES;
SHOW CREATE TABLE t_stu;

在这里插入图片描述

7.3 删除表

7.3.1 直接删除表

DROP TABLE 表名;

7.3.2 判断表是否存在，如果存在则删除表

DROP TABLE IF EXISTS 表名;

7.3.3 具体操作

# 直接删除表maya
drop table maya;

# 判断表是否存在并删除maya表
drop table if exists maya

7.4 修改表结构

7.4.1 添加表列ADD

ALTER TABLE 表名 ADD 列名 类型;

7.4.2 修改列类型MODIFY

ALTER TABLE 表名 MODIFY 列名 新的类型;

7.4.3 修改列名CHANGE

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

7.4.4 删除列DROP

ALTER TABLE 表名 DROP 列名;

7.4.5 修改表名

RENAME TABLE 表名 TO 新表名;

7.4.6 修改字符集character set

ALTER TABLE 表名 character set 字符集;
将t_stu表的编码修改成gbk
alter table t_stu character set gbk;

7.5 约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据的完整性要从以下四个方面考虑：
实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门。
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。
根据约束的特点，分为几种：
键约束：主键约束、外键约束、唯一键约束
Not NULL约束：非空约束
Check约束：检查约束
Default约束：缺省约束

7.5.1 查看某个表的约束和索引

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
SHOW INDEX FROM 表名称;
SHOW CREATE TABLE 表名;

7.5.2 主键约束PRIMARY KEY

主键：Primary key，简称PK，数据库主键作用保证实体的完整性，可以是一个列或多列的组合。
在这里插入图片描述

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值，如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
每个表有且最多只允许一个主键约束。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，MySQL默认在对应的列上建立主键索引。删除主键时，也会直接删除主键索引。
如何建立主键？

7.5.3 唯一键Unique key，简称UK

同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。
MySQL会给唯一约束的列上默认创建一个唯一索引。
删除唯一键只能通过删除唯一索引的方式删除，删除时需要指定唯一索引名，唯一索引名就是唯一约束名一样。如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同，如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
如何建立唯一性约束？

如何删除唯一性约束？

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
#注意：如果忘记名称，可以通过“show index from 表名称;”查看

主键和唯一键的区别：
（1）主键是非空，唯一键允许空
（2）主键一个表只能一个，唯一键可以有多个

7.5.3 外键：Foreign key，简称FK（了解）

外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。
删除外键时，关于外键列上的普通索引需要单独删除。
注意：
在从表上建立外键，而且主表要先存在。
从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键）。
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样
一个表可以建立多个外键约束
从表的外键值必须"在主表中能找到"或者为空，从而约束了从表的外键列的值的添加和修改。
当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制。

（1）默认情况下，主表和从表是严格依赖关系RESTRICT。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
（2）但是有一种是级联“修改、删除”：
（3） ON DELETE SET NULL(级联置空)：当外键设置了SET NULL,当主表的相关记录删除时，从表对应的字段改为NULL。注意从表外键字段得允许为空才行
（4） ON DELETE CASCADE(级联删除)：当外键设置了CASCADE（级联），当主表的相关记录删除时，从表对应的行都删除了。
（5） 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

如果要删除表，需要先删除从表，才能删除主表

如何建立外键约束？
创建外键

CREATE TABLE t_department(
	did INT PRIMARY KEY,
	dname VARCHAR(100) NOT NULL UNIQUE,
	description VARCHAR(200) NOT NULL
);
CREATE TABLE t_employee(
	eid INT PRIMARY KEY,
	ename VARCHAR(100) NOT NULL,
	dept_id INT,
	CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT
);
#其中CONSTRAINT fk_emp_dept_did可以省略
#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

一个表可以有多个外键，而且主表和从表可以是一张表

CREATE TABLE t_emp(
	eid INT PRIMARY KEY,
	ename VARCHAR(100) NOT NULL,
	manager_id INT,
	dept_id INT,
	CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did)  ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT fk_emp_mid_eid FOREIGN KEY(manager_id) REFERENCES t_emp(eid)  ON UPDATE CASCADE ON DELETE RESTRICT
);
#其中CONSTRAINT fk_emp_dept_did可以省略
#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

建表后创建外键

alter table表名称 add 【constraint 约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名);
ALTER TABLE t_emp ADD CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did)  ON UPDATE CASCADE ON DELETE RESTRICT;
#其中CONSTRAINT fk_emp_dept_did可以省略
#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

在这里插入图片描述

如何删除外键约束？

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
ALTER TABLE t_emp DROP FOREIGN KEY fk_emp_dept_did;

查看约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

如何删除外键列上的索引？需要单独删除

ALTER TABLE 表名称 DROP INDEX 外键列索引名; 
ALTER TABLE t_emp DROP INDEX dept_id;

查看索引名
show index from 表名称;

7.6 索引

索引：索引是对数据库表中一列或多列的值进行排序的一种结构。索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。由此可知，索引是要消耗数据库空间的。而约束是一种逻辑概念。
在这里插入图片描述
例如：一本字典，如何快速找到某个字，可以给字典加目录，对数据库来说,索引的作用即是给"数据"加目录。
设有N条随机记录,不用索引,平均查找N/2次,那么用了索引之后呢。如果是btree(二叉树)索引，，如果是hash(哈希)索引，时间复杂度是1。
索引好处：加快了查询速度(select )
索引坏处：降低了增,删,改的速度(update/delete/insert)，增大了表的文件大小(索引文件甚至可能比数据文件还大)
MySQL提供多种索引类型供选择：

普通索引：
唯一性索引：
主键索引：只有一个主键索引
全文索引：MySQL5.X版本只有MyISAM存储引擎支持FULLTEXT，并且只限于CHAR、VARCHAR和TEXT类型的列上创建。
MySQL的索引方法：
HASH
BTREE
MySQL中多数索引都以BTREE的形式保存。

索引的使用原则：
（1）不过度索引
（2）索引条件列(where后面最频繁的条件比较适宜索引)
（3）索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大

CREATE INDEX 索引名 ON 表名称 (column_name,[column_name...]);  最左边的列最关键
alter table 表名称 drop index 索引名;

在这里插入图片描述

7.7 自增列

关于自增长auto_increment：
（1）整数类型的字段才可以设置自增长。
（2）当需要产生唯一标识符或顺序值时，可设置自增长。
（3）一个表最多只能有一个自增长列
（4）自增长列必须非空
（5）自增长列必须是主键列或唯一键列。
（6）InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。

案例：

CREATE TABLE t_stu(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(100) NOT NULL,
	gender CHAR NOT NULL DEFAULT '男',
	birthday DATE,
	address VARCHAR(200)
);

在这里插入图片描述