一、数据库的概念

  • 数据库：存储和管理数据的仓库，数据按照一定的格式进行存储，用户可以对数据库中的数据进行增加、修改、删除、查询等操作

  • 数据库的作用：存储和管理数据

  • 数据库的特点

    • 持久化存储
    • 读写速度极高
    • 保证数据的有效性

  • 数据库分类：分为关系型数据库和非关系型数据库

    • 关系型数据库：指采用了关系模型来组织数据的数据库（关系模型指的就是二维表格模型，例如Excel文件中的表格，强调使用表格的方式存储数据）
      • 关系型数据库的核心元素
        • 数据行
        • 数据列
        • 数据表
        • 数据库（数据表的集合）
      • 常用的关系型数据库
        • Oracle
        • Microsoft SQL Server
        • MySQL
        • SQLite
    • 非关系型数据库：又被称为NoSQL（Not Only SQL )，最普遍的定义是“非关联型的”，强调以 Key-Value 的方式存储数据
      • 常用的非关系型数据库
        • MongoDB
        • Redis

二、关系型数据库管理系统与SQL

  • 数据库管理系统（英语全拼：Relational Database Management System，简称RDBMS）是为管理关系型数据库而设计的软件系统

  • 关系型数据库管理系统可以分为：关系型数据库服务端软件和关系型数据库客户端软件

    • 关系型数据库服务端软件：主要负责管理不同的数据库，而每个数据库里面会有一系列数据文件，数据文件是用来存储数据的
    • 关系型数据库客户端软件：主要负责和关系型数据库服务端软件进行通信，向服务端传输数据或者从服务端获取数据

  • 关系数据库客户端借助网络使用SQL语言和关系型数据库服务端进行数据通信

  • SQL(Structured Query Language)是结构化查询语言，是一种用来操作RDBMS的数据库的语言。通过 SQL 可以操作关系型数据库

  • SQL的作用：实现数据库客户端和数据库服务端之间的通信，SQL就是通信的桥梁

  • SQL语言主要分为：

    • DQL：数据查询语言，用于对数据进行查询，如select
    • DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
    • TPL：事务处理语言，对事务进行处理，如begin transaction、commit、rollback
    • DCL：数据控制语言，进行授权与权限回收，如grant、revoke
    • DDL：数据定义语言，进行数据库、表的管理等，如create、drop

  • SQL语言不区分大小写

img

三、MySQL数据库的介绍

  • MySQL：是一个关系型数据库管理系统，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件，它是由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品，MySQL 是最流行的关系型数据库管理系统中的一个

  • MySQL的特点

    • MySQL是开源的，不需要支付额外的费用
    • MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库
    • MySQL使用标准的SQL数据语言形式
    • MySQL可以安装在不同的操作系统，并且提供多种编程语言的操作接口，包括C、C++、Python、Java、Ruby等等

  • MySQL客户端通过socket方式与MySQL服务端程序通信

  • 常用的MySQL数据库客户端软件有图形化界面客户端（如Navicat）和命令行客户端（如mysql）

  • MySQL相关命令

    • 显示MySQL服务端安装包信息：apt-cache show mysql-server

img

    • 显示MySQL客户端安装包信息：apt-cache show mysql-client

img

    • 查看MySQL服务：ps -aux | grep mysql
      • ps：查看当前系统中的进程
      • -a：表示所有用户
      • -u：表示显示用户名
      • -x：表示显示所有的执行程序
    • 查看MySQL服务状态：sudo service mysql status
    • 停止MySQL服务：sudo service mysql stop
    • 启动MySQL服务：sudo service mysql start
    • 重启MySQL服务：sudo service mysql restart
    • MySQL命令使用帮助：mysql --help

四、命令行客户端MySQL的使用

1. MySQL配置文件

    • 配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf
      • port：端口号，默认为3306
      • bind-address：服务器绑定的ip，默认为127.0.0.1
      • datadir：数据库保存路径，默认为/var/lib/mysql
      • log_error：错误日志，默认为/var/log/mysql/error.log

img

2. 登录和登出数据库

  • 登录数据库：sudo mysql -uroot -p

    • -u：后面是登录的用户名
    • -p：后面是登录密码, 如果不填写, 回车之后会提示输入密码

  • 登出数据库：quit 或 exit 或 ctrl + d

五、数据类型和约束

1. 数据类型

  • 数据类型：指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来
  • 使用数据类型的原则：取小不取大，节省存储空间
  • 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径

（1）整数类型

类型 字节大小 有符号范围(Signed) 无符号范围(Unsigned)
TINYINT 1 -128 ~ 127 0 ~ 255
SMALLINT 2 -32768 ~ 32767 0 ~ 65535
MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215
INT/INTEGER 4 -2147483648 ~2147483647 0 ~ 4294967295
BIGINT 8 -9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615

（2）小数类型

类型 说明
DECIMAL decimal(5, 2) 表示共存5位数，小数占 2 位

（3）字符串类型

类型 说明 使用场景
CHAR 固定长度，小型数据，如char(20) 身份证号、手机号、电话、密码
VARCHAR 可变长度，小型数据，如varchar(20) 姓名、地址、品牌、型号
TEXT 可变长度，字符个数大于 4000 存储小型文章或者新闻
LONGTEXT 可变长度， 极大型文本数据 存储极大型文本数据

（4）日期时间类型

类型 字节大小 说明
DATE 4 ‘2020-01-01’
TIME 3 ‘12:29:59’
DATETIME 8 ‘2020-01-01 12:29:59’
YEAR 1 ‘2017’
TIMESTAMP 4 ‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

（5）枚举类型：enum

2. 数据约束

  • 数据约束：指数据在数据类型限定的基础上额外增加的要求
  • 常见的约束如下
数据约束 说明
主键约束（primary key） 物理上存储的顺序，MySQL 建议所有表的主键字段都叫 id，类型为 int unsigned
非空约束（not null） 此字段不允许填写空值
惟一约束（unique） 此字段的值不允许重复
默认约束（default） 当不填写字段对应的值会使用默认值，如果填写则以填写为准
外键约束（foreign key） 对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

六、数据库操作

SQL语句 说明
show databases; 查看所有数据库
create database 数据库名 [charset=utf8]; 创建数据库
use 数据库名; 使用数据库
select database(); 查看当前使用的数据库
drop database 数据库名; 删除数据库
show create database 数据库名; 查看创库SQL语句

七、表结构操作

SQL语句 说明
show tables; 查看当前数据库中所有表
create table 表名(字段名 数据类型 约束条件); 创建表
alter table 表名 add 字段名 字段类型 约束; 修改表-添加字段
alter table 表名 modify 字段名 字段类型 约束; 修改表-修改字段类型/约束
alter table 表名 change 原字段名 新字段名 字段类型 约束[,change…]; 修改表-修改字段名/字段类型/约束
alter table 表名 drop 字段名; 修改表-删除字段
show create table 表名; 查看创表SQL语句
drop table 表名; 删除表

外键约束相关操作

SQL语句 说明
alter table 从表 add foreign key(外键字段) references 主表(主键字段); 对已存在的字段添加外键约束
create table 表名(… ,foreign key(外键字段) references 主表(主键字段)); 创建表时设置外键约束
alter table 表名 drop foreign key 外键名; 删除外键约束

外键名：该名称系统会自动生成，可以通过查看表创建语句来获取名称

八、表数据操作

1. 查询数据

（1）基础查询

SQL语句 说明
select * from 表名; 查询所有列
select 列1[,列2..] from 表名; 查询指定列
  • as关键字
SQL语句 说明
select 字段名 as 新字段名,[…] from 表名; 给字段起别名
select 字段名 from 表名 as 新表名; 给表起别名
  • distinct关键字
SQL语句 说明
select distinct 字段名 from 表名 去除重复行

（2）where条件查询

  • where条件查询：可以对表中的数据进行筛选，条件成立的记录会出现在结果集中

  • 查询格式：where 条件

  • 比较运算符

符号 说明
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
!= / <> 不等于
  • 逻辑运算符（多个条件判断想要作为一个整体，可以结合‘()’）
符号 说明
and 与
or 或
not 非
  • 模糊查询
符号 说明
like 模糊查询关键字
% 表示任意多个字符
_ 表示任意一个字符
  • 范围查询
符号 说明
between .. and .. 表示在一个连续的范围内查询
in 表示在一个非连续的范围内查询
  • 空判断查询
符号 说明
is null 判断为空
is not null 判断非空

（3）排序查询

  • 查询格式：order by 字段名 排序规则 [,字段名2 排序规则...]
排序规则 说明
asc 升序（默认）
desc 降序

（4）分页查询

  • 查询格式：limit [start,]count，start默认是0
SQL语句 说明
limit (n-1)*m,m 获取第n页数据

（5）分组查询

  • 分组查询：将查询结果按照指定字段进行分组，字段中数据相等的分为一组

  • 查询格式：group by 字段名 [having ..][with rollup]

    • having：用来过滤分组后的数据
    • with rollup：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

（6）表连接查询

  • 连接查询：可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成

    • 内连接：查询两个表中符合条件的共有记录
    • 左连接：以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充
    • 右连接：以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充
    • 自连接：左表和右表是同一个表，根据连接查询条件查询两个表中的数据

  • 连接查询条件：on 表1.字段名 = 表2.字段名

连接查询格式 说明
表1 inner join 表2 on … 内连接
表1 left join 表2 on … 左连接，表1是左表，表2是右表
表1 right join 表2 on … 左连接，表1是左表，表2是右表
表 as 表1别名 inner join 表 as 表2别名 on … 自连接，必须给表设置别名

（7）子查询

  • 子查询语句：在一个 select 语句中嵌入的另外一个 select 语句，通常被嵌入到一对小括号里面

  • 主查询语句：被嵌入的select语句

  • 主查询和子查询的关系

    • 子查询嵌入到主查询中
    • 子查询是辅助主查询的，充当条件或者数据源
    • 子查询是可以独立存在的语句，是一条完整的 select 语句

（8）相关函数

  • ifnull(字段名,填充值) ：表示判断指定字段的值是否为null，如果为空则使用提供的值
  • group_concat(字段名)：统计每个分组指定字段的信息集合，每个信息之间用逗号进行分割
  • 聚合函数
聚合函数 说明
count(*) 返回总行数，包含null值
count(字段名) 求指定列的总行数（非null）
max(字段名) 求指定列的最大值
min(字段名) 求指定列的最小值
sum(字段名) 求指定列的和
avg(字段名) 求指定列的平均值

2. 添加数据

  • 主键列是自动增长，但是在全列插入时需要占位，通常使用空值（0或者null或者default）
  • 全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值
  • 命令行导入数据：source 文件路径/文件名.sql
SQL语句 说明
insert into 表名 values(…)[,(…)…]; 全列插入，逗号隔开，值顺序与表结构字段顺序对应
insert into 表名(列1[,…]) values(值1[,…])[,(…)…]; 部分列插入，值顺序与给出列顺序对应
insert into 表名[(列1,…)] select SQL语句 表复制
create table (…) select SQL语句 创建表并插入表复制数据，需要对查询字段起别名

3. 修改数据

SQL语句 说明
update 表名 set 列1=值1[,列2=值2..] where 条件; 修改数据
update 表1 … join 表2 on … set 表1字段 = 表2字段 表连接更新数据

4. 删除数据

SQL语句 说明
delete from 表名 [where 条件]; 删除数据
truncate table 表名; 清空表

七、数据库设计

开发流程：先画出E-R模型，然后根据三范式设计数据库中的表结构

1. E-R模型

  • E-R模型由实体、属性、实体之间的关系构成，是描述数据库存储数据的结构模型

  • E-R模型的效果图

    • 实体：用矩形表示，并标注实体名称
    • 属性：用椭圆表示，并标注属性名称
    • 关系：用菱形表示，并标注关系名称

  • 关系也是一种数据，需要通过一个字段存储在表中

    • 1对1关系，在表A或表B中创建一个字段，存储另一个表的主键值
    • 1对多关系，在多的一方表中创建一个字段，存储另一个表的主键值
    • 多对多关系，新建一张表C，这个表只有两个字段，分别存储表A和表B的主键值

2. 表设计三范式

  • 范式: 对设计数据库提出的一些规范，目前有迹可寻的共有8种范式，一般遵守3范式即可。

    • 第一范式（1NF）：强调的是列的原子性，即列不能再分成其他几列
    • 第二范式（2NF）：满足 1NF，且包含两部分内容：①表必须有一个主键；②非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分
    • 第三范式（3NF）：满足 2NF，且非主键列必须直接依赖于主键，不能存在传递依赖（即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况）

八、事务

  • 事务：用户定义的一系列执行SQL语句的操作，这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元
  • 优点：事务能够保证数据的完整性和一致性，让操作更加安全

1. 事务的四大特性

  • 原子性(Atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
  • 一致性(Consistency)：数据库总是从一个一致性的状态转换到另一个一致性的状态
  • 隔离性(Isolation)：通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的
  • 持久性(Durability)：一旦事务提交，则其所做的修改会永久保存到数据库，不可撤销

2. 事务的使用

  • 表的存储引擎：就是提供存储数据一种机制，不同表的存储引擎提供不同的存储机制

  • 常用的表的存储引擎是 InnoDB 和 MyISAM

    • InnoDB 支持事务，MySQL数据库中表的存储引擎默认是 InnoDB 类型
    • MyISAM：不支持事务，优势是访问速度快，对事务没有要求，或以select、insert为主的都可以使用该存储引擎来创建表

  • 提交事务：将本地缓存文件中的数据提交到物理表中，完成数据的更新

  • 回滚事务：放弃本地缓存文件中的缓存数据, 表示回到开始事务前的状态

  • MySQL数据库默认采用自动提交(autocommit)模式，每条sql语句都会被当作一个事务执行提交的操作；设置set autocommit=0，则是取消自动提交事务模式，执行commit或者rollback才表示该事务结束

SQL语句 说明
show engines; 查看MySQL数据库支持的表的存储引擎
show create table 表名; 通过创表语句查看数据表的存储引擎
alter table 表名 engine = 引擎类型; 修改表的存储引擎
begin; / start transaction; 开启事务
commit; 提交事务
rollback; 回滚事务

开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不维护到物理表中

九、索引

1. 索引和联合索引

  • 索引：MySQL中也叫做“键”，它是一个特殊的文件，保存着数据表里所有记录的位置信息（类似目录），当数据库中数据量很大时，可以通过索引来提高数据库的查询效率
  • 联合索引：又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候
  • 联合索引优点：减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销
  • 联合索引的最左原则：在使用联合索引的查询数据时候一定要保证联合索引的最左侧字段出现在查询条件里面，否则联合索引失效
SQL语句 说明
show index from 表名; 查看表中已有索引（主键会自动创建索引）
alter table 表名 add index 索引名,..; 创建索引（不指定索引名时，默认使用字段名）
alter table 表名 add index 索引名(列名[,..]) 创建联合索引
alter table 表名 drop index 索引名; 删除索引（索引名可以在创建表语句中查看）

索引效率监测

SQL语句 说明
set profiling = 1; 开启运行时间监测
show profiles; 查看查询语句执行时间

2. MySQL中索引的使用原则

  • 优点：加快数据的查询速度

  • 缺点：创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加

  • 使用原则

    • 对经常更新的表避免过多索引的创建
    • 对经常用于查询的字段应该创建索引
    • 数据量小的表最好不要使用索引（因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果）
    • 字段内相同值较多不要建立索引，不同值较多可以建立索引

待补充

  • 代码片段
  • sql执行顺序

附录-参考资料

MySQL官方文档

https://dev.mysql.com/doc/refman/8.0/en/

MySQL官方文档：SQL语句

https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html