数据库入门

基本概念

数据库：DataBase简称DB，是一个用于存储和管理数据的仓库。
就像我们每天访问的网站Bilibili，OA、ERP和CRM等内部管理系统，以及我们每天使用的腾讯新闻和Tiktok应用程序一样，我们看到的数据实际上存储在数据库中。最后，这些数据只显示在浏览器或应用程序中，最终数据的存储和管理由数据库负责。
数据存储在数据库中，那么我们如何操作数据库和存储在其中的数据呢？
这将涉及一个软件：数据库管理系统（DBMS）
DBMS是一种用于操作和管理数据库的大型软件。只需操作此软件即可操作和管理数据库。
但是DBMS如何知道要对哪个数据库和数据进行操作呢？是修改还是查询数据？
因此，我们需要向DBMS软件发送一条指令，告诉它我们想执行什么样的操作以及对哪些数据进行操作

---

在介绍 Mysql的数据模型之前，需要先了解以下概念。

---

关系数据库系统

概念：基于关系模型，由多个相互连接的二维表组成的数据库。
所谓二维表是指由行和列组成的表，如下所示：
二维表格的优点：
使用表以统一格式存储数据，便于维护。
使用SQL语言操作，标准化，易于使用，适用于复杂查询。
结论：基于二维表存储数据的数据库变成了关系数据库。如果它不是一个基于二维表存储数据的数据库，那么它就是一个非关系型数据库。

数据模型

在介绍了关系数据库之后，让我们来看看数据是如何存储在MySQL数据库中的，这就是MySQL数据模型。
MySQL是一个基于二维表存储数据的关系数据库。具体结构图如下：
通过MySQL客户端连接到数据库管理系统DBMS，然后通过DBMS操作数据库。
使用MySQL客户端，向数据库管理系统发送SQL语句，数据库管理系统将根据SQL语句指令对数据库中的表结构和数据进行操作。
可以在数据库服务器中创建多个数据库，数据库也可以包含多个表，每个表包含多行记录。

SQL

SQL：结构化查询语言。一种用于操作关系数据库的编程语言，它定义了操作所有关系数据库的统一标准。
在学习特定的SQL语句之前，让我们先了解SQL语言的语法。

SQL通用语法

1.SQL语句可以写在一行或多行上，并以分号结尾。

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

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

4.注释：
单行注释：--注释内容或#注释内容（MySQL特定）
多行评论：/*评论内容*/
以上是SQL语句的一般语法。目前，每个人都清楚地了解这些通用语法。当我们稍后解释每种类型的SQL语句时，我们还将强调一般语法。

SQL分类

SQL语句根据其功能被分为四大类：DDL、DML、DQL、DCL

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

项目开发流程

需求文件：

在我们开发项目或项目中的模块之前，我们将首先收到产品经理提供的页面原型和需求文档。

设计：

在收到产品原型和需求文档后，我们的第一步不是编码，而是设计项目，包括概要设计、详细设计、界面设计、数据库设计等。
基于产品原型和需求文档，数据库设计应分析每个模块中涉及的表结构、表结构之间的关系以及表结构的详细信息。最终，我们需要设计和创建数据库以及其中的表结构。

开发/测试：

根据页面原型和需求进行编码，以实现业务功能。在这个过程中，我们需要操作设计的数据库表结构来完成添加、删除、修改和查询等业务操作。
 

在线部署:

项目的功能开发和测试完成后，可以启动和运行项目。如果项目在后期遇到性能瓶颈，它仍然需要优化。优化的一个重要部分是数据库优化，包括在数据库中建立索引、优化SQL以及执行分区数据库和表等操作。

在上述过程中，对于数据库，主要包括三个阶段：

数据库设计阶段
基于页面原型和需求文档设计数据库表结构.
数据库操作阶段
根据业务功能的实现，编写SQL语句来执行数据表中的添加、删除、修改和查询数据等操作.
数据库优化阶段
通过数据库优化提高数据库访问性能。优化方法：索引、SQL优化、分片和表分区等。

数据库操作

我们在进行数据库设计，需要使用到刚才所介绍SQL分类中的DDL语句。

DDL英文全称是Data Definition Language(数据定义语言)，用来定义数据库对象(数据库、表)。

DDL中数据库的常见操作：查询、创建、使用、删除。

查询数据库

查询所有数据库：

show databases;

查询当前数据库：

select database();

我们要操作某一个数据库，必须要切换到对应的数据库中。
通过指令：select database() ，就可以查询到当前所处的数据库.

创建数据库

语法：

create database [ if not exists ] 数据库名;
案例： 创建一个itcast数据库。

create database itcast;

注意：在同一个数据库服务器中，不能创建两个名称相同的数据库，否则将会报错。

可以使用if not exists来避免这个问题

 数据库不存在,则创建该数据库；如果存在则不创建

create database if not extists itcast; 

使用数据库

语法：

use 数据库名 ;

我们要操作某一个数据库下的表时，就需要通过该指令，切换到对应的数据库下，否则不能操作。
案例：切换到itcast数据

use itcast;

删除数据库

语法：

drop database [ if exists ] 数据库名 ;

如果删除一个不存在的数据库，将会报错。
可以加上参数 if exists ，如果数据库存在，再执行删除，否则不执行删除。

案例：删除itcast数据库

drop database if exists itcast; 

 itcast数据库存在时删除
 

说明：上述语法中的database，也可以替换成 schema
如：create schema db01;
如：show schemas;

图形化工具

介绍

先前我们解释了DDL中与数据库操作相关的SQL语句。当我们编写这些SQL语句时，它们都是在命令行上完成的。在实践中，可能还会在命令行上键入这些SQL语句非常不方便，主要有以下三个原因：

没有代码提示。（仅依靠内存，很容易输入错误的字母并导致执行错误）
操作繁琐，影响开发效率。（所有函数操作都是通过SQL语句完成的）
无法保存已编写的SQL代码。

在项目开发中，为了提高开发效率，通常使用现有的图形管理工具来操作数据库。

目前MySQL主流的图形化界面工具DataGrip。

连接数据库

1、打开IDEA自带的Database

2、配置MySQL

3、输入相关信息

4、下载MySQL连接驱动

5、测试数据库连接

6、保存配置

默认情况下，连接上了MySQL数据库之后， 数据库并没有全部展示出来。 需要选择要展示哪些数据库。具体操作如下：

创建数据库

有了图形化界面工具后，就可以方便的使用图形化工具：创建数据库，创建表、修改表等DDL操作。
其实工具底层也是通过DDL语句操作的数据库，只不过这些SQL语句是图形化界面工具帮我们自动完成的。

查看所有数据库

表操作
学习完了DDL语句当中关于数据库的操作之后，接下来我们继续学习DDL语句当中关于表结构的操作。

关于表结构的操作也是包含四个部分：创建表、查询表、修改表、删除表。

创建

语法

create table  表名(
    字段1  字段1类型 [约束]  [comment  字段1注释 ],
    字段2  字段2类型 [约束]  [comment  字段2注释 ],
    ......
    字段n  字段n类型 [约束]  [comment  字段n注释 ] 
)

[ comment  表注释 ] ;

[ ] 中的内容为可选参数； 最后一个字段后面没有逗号
案例：创建tb_user表

对应的结构如下：

• id 是一行数据的唯一标识

• username 用户名字段是非空且唯一的

• name 姓名字段是不允许存储空值的

• gender 性别字段是有默认值，默认为男

• 建表语句：

• create table tb_user (
      id int comment 'ID,唯一标识',   # id是一行数据的唯一标识（不能重复）
      username varchar(20) comment '用户名',
      name varchar(10) comment '姓名',
      age int comment '年龄',
      gender char(1) comment '性别'
  ) comment '用户表';

约束

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

作用：就是来保证数据库当中数据的正确性、有效性和完整性。

在MySQL数据库当中，提供了以下5种约束：

约束            描述                                                                                               关键字
非空约束    限制该字段值不能为null                                                        not null
唯一约束    保证字段的所有数据都是唯一、不重复的                           unique
主键约束    主键是一行数据的唯一标识，要求非空且唯一                   primary key
默认约束    保存数据时，如果未指定该字段值，则采用默认值           default
外键约束    让两张表的数据建立连接，保证数据的一致性和完整性   foreign key

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

案例：创建tb_user表

在上述的表结构中:

id 是一行数据的唯一标识
username 用户名字段是非空且唯一的
name 姓名字段是不允许存储空值的
gender 性别字段是有默认值，默认为男
建表语句：

create table tb_user (id int primary key comment 'ID,唯一标识', username varchar(20) not null unique comment '用户名',name varchar(10) not null comment '姓名',age int comment '年龄',gender char(1) default '男' comment '性别'
) comment '用户表';

数据表创建完成，接下来测试一下表中字段上的约束是否生效
大家有没有发现一个问题：id字段下存储的值，如果由我们自己来维护会比较麻烦(必须保证值的唯一性)。MySQL数据库为了解决这个问题，给我们提供了一个关键字：auto_increment（自动增长）

主键自增：auto_increment
每次插入新的行记录时，数据库自动生成id字段(主键)下的值
具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)
create table tb_user (
    id int primary key auto_increment comment 'ID,唯一标识', #主键自动增长
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户表';
测试：主键自增

数据类型

在上面建表语句中，我们在指定字段的数据类型时，用到了int 、varchar、char，那么在MySQL中除了以上的数据类型，还有哪些常见的数据类型呢？ 接下来,我们就来详细介绍一下MySQL的数据类型。

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型

类型            大小            有符号(SIGNED)范围            无符号(UNSIGNED)范围                        描述
TINYINT    1byte                   (-128，127)                                (0，255)                               小整数值
SMALLINT    2bytes          (-32768，32767)                       (0，65535)                            大整数值
MEDIUMINT    3bytes    (-8388608，8388607)             (0，16777215)                          大整数值
INT/INTEGER    4bytes    (-2147483648，2147483647)    (0，4294967295)                大整数值
BIGINT        8bytes               (-2^63，2^63-1)                            (0，2^64-1)                极大整数值
FLOAT    4bytes    (-3.402823466 E+38，3.402823466351 E+38)                                                                      0 和 (1.175494351 E-38，3.402823466 E+38)                                                  单精度浮点数值
DOUBLE    8bytes    (-1.7976931348623157 E+308，1.7976931348623157 E+308)    0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)    双精度浮点数值
DECIMAL        依赖于M(精度)和D(标度)的值    依赖于M(精度)和D(标度)的值    小数值(精确定点数)
示例: 
    年龄字段 ---不会出现负数, 而且人的年龄不会太大
    age tinyint unsigned
    
    分数 ---总分100分, 最多出现一位小数
    score double(4,1)

字符串类型

类型    大小    描述
CHAR    0-255 bytes    定长字符串(需要指定长度)
VARCHAR    0-65535 bytes    变长字符串(需要指定长度)
TINYBLOB    0-255 bytes    不超过255个字符的二进制数据
TINYTEXT    0-255 bytes    短文本字符串
BLOB    0-65 535 bytes    二进制形式的长文本数据
TEXT    0-65 535 bytes    长文本数据
MEDIUMBLOB    0-16 777 215 bytes    二进制形式的中等长度文本数据
MEDIUMTEXT    0-16 777 215 bytes    中等长度文本数据
LONGBLOB    0-4 294 967 295 bytes    二进制形式的极大文本数据
LONGTEXT    0-4 294 967 295 bytes    极大文本数据
char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性能会更高些。

示例： 
    用户名 username ---长度不定, 最长不会超过50
    username varchar(50)
    
    手机号 phone ---固定长度为11
    phone char(11)
日期时间类型

类型    大小    范围    格式    描述
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:01 至 2038-01-19 03:14:07    YYYY-MM-DD HH:MM:SS    混合日期和时间值，时间戳
示例: 
    生日字段  birthday ---生日只需要年月日  
    birthday date
    
    创建时间 createtime --- 需要精确到时分秒
    createtime  datetime

2.3.1.5 设计表流程

通过上面的案例，我们明白了，设计一张表，基本的流程如下：

阅读页面原型及需求文档

基于页面原则和需求文档，确定原型字段(类型、长度限制、约束)

再增加表设计所需要的业务基础字段(id主键、插入时间、修改时间)

说明：
create_time：记录的是当前这条数据插入的时间。
update_time：记录当前这条数据最后更新的时间。

查询

关于表结构的查询操作，工作中一般都是直接基于图形化界面操作。
查询当前数据库所有表

show tables;

查看指定表结构

desc 表名 ;#可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息
查询指定表的建表语句

show create table 表名 ;

修改

关于表结构的修改操作，工作中一般都是直接基于图形化界面操作。
添加字段

alter table 表名 add  字段名  类型(长度)  [comment 注释]  [约束];
案例： 为tb_emp表添加字段qq，字段类型为 varchar(11)

alter table tb_emp add  qq  varchar(11) comment 'QQ号码';
图形化操作：添加字段
修改数据类型

alter table 表名 modify  字段名  新数据类型(长度);
alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];
案例：修改qq字段的字段类型，将其长度由11修改为13

alter table tb_emp modify qq varchar(13) comment 'QQ号码';
案例：修改qq字段名为 qq_num，字段类型varchar(13)

alter table tb_emp change qq qq_num varchar(13) comment 'QQ号码';
图形化操作：修改数据类型和字段名
删除字段

alter table 表名 drop 字段名;
案例：删除tb_emp表中的qq_num字段

alter table tb_emp drop qq_num;
图形化操作：删除字段
修改表名

rename table 表名 to  新表名;
案例：将当前的tb_emp表的表名修改为emp

rename table tb_emp to emp;
图形化操作：修改表名

删除

关于表结构的删除操作，工作中一般都是直接基于图形化界面操作。
删除表语法：

drop  table [ if exists ]  表名;
if exists ：只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不加该参数项，删除一张不存在的表，执行将会报错)。
案例：如果tb_emp表存在，则删除tb_emp表

drop table if exists tb_emp;  -- 在删除表时，表中的全部数据也会被删除。
图形化操作：删除表

数据库操作-DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

增加

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：向tb_emp表的username、name、gender字段插入数据

因为设计表时create_time, update_time两个字段不能为NULL，所以也做为要插入的字段
insert into tb_emp(username, name, gender, create_time, update_time)
values ('san', '张三', 1, now(), now());

案例2：向tb_emp表的所有字段插入数据

insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)
values (null, 'si', '123', '李四', 2, '1.jpg', 1, '2010-01-01', now(), now());

Insert操作的注意事项：

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

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

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

修改

update语法：

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;

案例1：将tb_emp表中id为1的员工，姓名name字段更新为'张三'

update tb_emp set name='张三',update_time=now() where id=1;

案例2：将tb_emp表的所有员工入职日期更新为'2010-01-01'

update tb_emp set entrydate='2010-01-01',update_time=now();

注意事项:
修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。

删除

delete语法：

delete from 表名  [where  条件] ;
案例1：删除tb_emp表中id为1的员工

delete from tb_emp where id = 1;

案例2：删除tb_emp表中所有员工

delete from tb_emp;