后端学习第五周

MySQL

一、数据库

DataBase(DB)，储存和管理数据的仓库。

由数据库管理系统操作：DataBase Management System (DBMS)，操纵和管理数据库的大型软件。

SQL : Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

数据库产品：

Oracle MySQL SQL Server PostgreSQL等，我们使用MySQL。

二、MySQL安装

1. 官网下载、解压、配置环境变量，与之前JDK等的安装相似。

2. 之后进行MySQL初始化：在cmd中输入mysqld --initialize-insecure。此时MySQL目录中出现data文件夹。

3. 注册MySQL服务：输入mysqld -install

4. 启用MySQL服务：输入net start mysql启动，输入net stop mysql停止

5. 修改默认账户密码：mysqladmin -u 用户名 password 密码

6. 登录MySQL：mysql [-h数据库服务器IP地址 -P端口号] -u用户名 -p

密码

即登陆成功。

三、MySQL数据模型

关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

可使用SQL语言经过数据库管理系统，创建数据库，在数据库中创建二维表存放信息。

四、SQL简介

SQL：

一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

基本用法：

1. SQL语句可以单行或多行书写，以分号结尾。

2. SQL语句可以使用空格/缩进来增强语句的可读性。

3. MySQL数据库的SQL语句不区分大小写。

4. 注释:

   1. 单行注释：--注释内容 或 # 注释内容(MySQL特有)

   2. 多行法释：/* */

分为四大类：DDL语言、DML语言、DQL语言、DCL语言

DDL：Data Definition Language：数据定义语言，用来定义数据库对象(数据库，表，字段) DML：Data Manipulation Language：数据操作语言，用来对数据库表中的数据进行增删改 DQL：Data Query Language：数据查询语言，用来查询数据库中表的记录 DCL：Data Control Language：数据控制语言，用来创建数据库用户、控制数据库的访问权限

五、DDL语言

DDL：Data Definition Language：数据定义语言，用来定义数据库对象(数据库，表，字段)

1. 对数据库操作

查询：查询所有数据库：show databases;

查询当前数据库：select database();

创建：创建数据库：create database[if not exists] 数据库名;

删除数据库：drop database [if exists] 数据库名;

注：以上database可替换为schema。

1. 数据类型

date 和 datetime 较常用。

1. 对表操作

创建：create table 表名 (

字段名1 字段类型 [约束] [comment 字段1注释]

...

字段名n 字段类型 [约束] [comment 字段n注释]

) [comment 表注释];

• 约束：

  • 概念：是作用于表中字段上的规则，用于限制存储在表中的数据。

  • 目的：保证数据库中数据的正确性、有效性和完整性。

  • not null非空约束：限制该字段值不能为null

  • unique唯一约束：保证字段的所有数据都是唯一、不重复的

  • primary key主键约束：主键是一行数据的唯一标识，要求非空且唯一

  • default默认约束：保存数据时，如果未指定该字段值，则采用默认值

查询：

• 查询当前数据库所有表： show tables;

• 查询表结构：desc 表名;

• 查询建表语句：show create table 表名;

修改：

• 添加字段： alter table 表名 add 字段名 类型 (长度) [comment注释] [约束];

• 修改字段类型：alter table 表名 modify 字段名 新数据类型 (长度) ;

• 修改字段名和字段类型： alter table 表名 change 旧字段名 新字段名 类型 (长度) [comment注释] [约束];

• 删除字段：alter table 表名 drop column 字段名;

• 修改表名：rename table 表名 to 新表名;

六、图形化工具

使用IDEA（集成了DataGrip的功能）进行数据库的操作。

配置：

打开IDEA右侧Database，加号，MySQL，填入Host、Port、User、Password等空。

点击Download下载数据库连接驱动，点击Test Connection进行能否连通的测试。

在IDEA中可以直接在Database栏中对数据库进行可视化查看，所以DDL语言使用减少。

七、DML语言

DML：Data Manipulation Language：数据操作语言，用来对数据库表中的数据进行增删改

1. insert语法

指定字段添加数据：insert into 表名 (字段名1,字段名2) values (值1,值2);

全部字段添加数据： insert into 表名 values (值1,值2,...);

批量添加数据（指定字段)： insert into 表名 (字段名1，字段名2) values (值1,值2), (值1，值2);

批量添加数据（全部字段)： insert into 表名 values (值1,值2，...), (值1,值2,...);

注：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

字符串和日期型数据应该包含在引号中。

插入的数据大小，应该在字段的规定范围内。

1. update语法

修改数据： update 表名 set 字段名1=值1，字段名2=值2 ,.... [ where 条件 ];

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

1. delete语法

删除数据：delete from 表名 [ where 条件 ];

DELETE语句不能删除某一个字段的值（如果要操作，可以使用UPDATE，将该字段的值置为NULL）。

八、DQL语言

DQL：Data Query Language：数据查询语言，用来查询数据库中表的记录

1. 基础查询：

查询多个字段：select 字段1,字段2,字段3 from表名;

查询所有字段（通配符) ：select * from表名;

设置别名： select 字段1 [ as别名1]， 字段2 [ as别名2] from 表名;

去除重复记录：select distinct 字段列表 from表名 ;

1. 条件查询

条件查询：select 字段列表 from 表名 where 条件列表;

> ：大于

>= ： 大于等于

< ：小于

<= ：小于等于

=：等于

!= 或 <> ：不等于

between ... and ...：在某个范围内（含最小、最大值 ）

in(...) 在in之后的列表中的值，多选一

like 占位符：模糊匹配（_匹配单个字符，%匹配任意个字符）

is null ：是 null

and 或 && ：并且（多个条件同时成立）

or 或 || ：或者（多个条件任意一个成立）

not 或 | ：非，不是

1. 聚合函数：

介绍：将一列数据作为一个整体，进行纵向计算。

语法：select 聚合函数 (字段列表) from 表名;

count ：统计数量

max：最大值

min：最小值

avg：平均值

sum：求和

注：

null值不参与所有聚合函数运算。

统计数量可以使用： count(*)、count(字段)、count(常量)，推荐使用count(*)。

1. 分组查询：

分组查询： select 字段列表 from 表名 [ where 条件] group by 分组字段名 [ having 分组后过滤条件];

where与having区别

1．执行时机不同： where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。

2．判断条件不同： where不能对聚合函数进行判断，而having可以。

注：

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

执行顺序： where >聚合函数>having 。

1. 排序查询：

排序查询：select 字段列表 from 表名 [ where 条件列表] [ group by 分组字段] order by 字段1 排序方式1，字段2 排序方式2... ;

排序方式：

ASC：升序（默认值）

DESC：降序

注：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询：

分页查询： select 字段列表 from 表名 limit 起始索引, 查询记录数;

注：

起始索引公式：（页码 - 1）* 每页展示记录数

不同的数据库有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

九、多表设计

表与表之间有三种关系：一对多、一对一、多对多

一对多：

一对多关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键，并用外键进行约束。

create table表名(

字段名 数据类型，

...

[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名) );

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (字段名);

实际可使用图形化添加

缺点：

影响增、删、改的效率（需要检查外键关系）。

仅用于单节点数据库，不适用与分布式、集群场景。

容易引发数据库的死锁问题，消耗性能。

所以实际使用中，我们用逻辑外键代替。

一对一：

多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多对多：

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。将多对多转化为多个一对多问题。

十、多表查询

多表查询：指从多张表中查询数据，在多表查询时，需要消除无效的笛卡尔积。

笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。

分类：

• 连接查询

  • 内连接 ：查询A、B交集部分的数据

  • 外连接

    • 左外连接：查询左表所有数据（包括两表交际部数据）

    • 右外连接：查询右表所有数据（包括两表交际部数据）

• 子查询

内连接：

隐式内连接：select 字段列表 from 表1, 表2 where 条件... ;

显式内连接：select 字段列表 from 表1 [ inner ] join 表2 on连接条件...;

SQL语句较长，可通过给表取别名来使语句简明。

外连接：

左外连接：select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件...;

右外连接：select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件...;

右外连接用的较少，可用左外连接代替。

子查询：

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

形式：select * from t1 where column1 = ( select column1 from t2 ...);

子查询外部的语句可以是insert / update / delete / select的任何一个，最常见的是select。

标量子查询：

返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > >= < <=

列子查询：

返回的结果是一列（可以是多行)

常用的操作符：in not in等

行子查询：

返回的结果是一行（可以是多列)。

常用的操作符：= 、<> 、 in 、 not in

表子查询；

返回的结果是多行多列，常作为临时表

常用的操作符：in