前言

为什么需要数据库？

某一天，你接到了这样一个需求——管理一堆数据，该需求有以下特点：

  • 数据量庞大
  • 数据之间关系复杂
  • 需要方便进行增删改查，并且保证操作的原子性
  • 支持数据共享，比如跨平台管理
  • 支持高效的迁移、备份、恢复等操作
  • ……

机智的你打开了Excel。。。若干年后，熙熙攘攘的大街上出现了这样一条横幅落魄前端 在线炒粉

今天介绍的是数据管理软件——数据库

常见的数据库有哪些？

数据库主要分为关系型数据库和非关系型数据库

  • 一张图看懂二者区别：

如何在开发中选择他们呢？

  • 具体的选择会根据不同的项目进行综合的分析
    • 目前在公司进行后端开发（Node、Java、Go等），还是以关系型数据库为主；
    • 比较常用的用到非关系型数据库的，在爬取大量的数据进行存储时，会比较常见；

MySQL初体验

基本概念

  • 一个MySQL服务可以管理若干个数据库，而每个数据库中可以包含多个表，每个表中的一组数据称为一条记录

安装

  • 推荐大家下载并安装PhpStudy搭建MySQL开发环境
  • 推荐使用Navicate或其他数据库管理软件进行学习

连接

打开Navicate软件点击左上角连接，输入用户名和密码即可

MySQL 中的数据类型

数字类型

MySQL支持的数据类型有：数字类型，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数
据类型。

  • 整数数字类型：INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT；
  • 浮点数字类型：FLOAT，DOUBLE（FLOAT是4个字节，DOUBLE是8个字节）；
  • 精确数字类型：DECIMAL，NUMERIC（DECIMAL是NUMERIC的实现形式）；

日期类型

MySQL的日期类型也很多：

  • YEAR以 YYYY格式显示值 范围 1901到2155，和 0000。

  • DATE类型用于具有日期部分但没有时间部分的值：

    • DATE以格式YYYY-MM-DD显示值 ；
    • 支持的范围是 ‘1000-01-01’ 到 ‘9999-12-31’；

  • DATETIME类型用于包含日期和时间部分的值：

    • DATETIME以格式’YYYY-MM-DD hh:mm:ss’显示值；

    • 支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59;

  • TIMESTAMP数据类型被用于同时包含日期和时间部分的值：

    • TIMESTAMP以格式’YYYY-MM-DD hh:mm:ss’显示值；

    • 但是它的范围是UTC的时间范围：’1970-01-01 00:00:01’到’2038-01-19 03:14:07’;

另外：DATETIME或TIMESTAMP 值可以包括在高达微秒（6位）精度的后小数秒一部分
比如DATETIME表示的范围可以是’1000-01-01 00:00:00.000000’到’9999-12-31 23:59:59.999999’;

字符串类型

MySQL的字符串类型表示方式如下：

  • CHAR类型在创建表时为固定长度，长度可以是0到255之间的任何值；
    • 在被查询时，会删除后面的空格；
  • VARCHAR类型的值是可变长度的字符串，长度可以指定为0到65535之间的值；
    • 在被查询时，不会删除后面的空格；
  • BINARY和VARBINARY 类型用于存储二进制字符串，存储的是字节字符串；
  • BLOB用于存储大的二进制类型；
  • TEXT用于存储大的字符串类型；

MySQL中的表约束

  • 主键：PRIMARY KEY

    • 主键是表中唯一的索引；
    • 并且必须是NOT NULL的，如果没有设置 NOT NULL，那么MySQL也会隐式的设置为NOT NULL；
    • 主键也可以是多列索引，PRIMARY KEY( key_part, …)，我们一般称之为联合主键；
    • 建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键；

  • 唯一：UNIQUE

    • 某些字段在开发中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们可以使用UNIQUE来约束

  • 不能为空：NOT NULL

    • 某些字段我们要求用户必须插入值，不可以为空，这个时候我们可以使用 NOT NULL 来约束

  • 默认值：DEFAULT

    • 某些字段我们希望在没有设置值时给予一个默认值，这个时候我们可以使用 DEFAULT来完成；

  • 自动递增：AUTO_INCREMENT

    • 某些字段我们希望不设置值时可以进行递增，比如用户的id，这个时候可以使用AUTO_INCREMENT来完成；

  • 外键约束也是最常用的一种约束手段

MySQL常用命令

一、DDL（Data Definition Language）：数据定义语言；

  • 可以通过DDL语句对数据库或者表进行：创建、删除、修改等操作；

查看并切换当前数据库

1
2
3
4
5
6
7
8
# 查看当前MySQL服务下的所有数据库：
SHOW DATABASES;

# 使用某一个数据库
USE xxx(数据库名)

# 查看当前正在使用的数据库
SELECT DATABASE();

创建新的数据库

1
2
3
4
# 创建数据库语句
CREATE DATABASE bilibili;
CREATE DATABASE IF NOT EXISTS bilibili;
CREATE DATABASE IF NOT EXISTS bilibili DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

删除数据库

1
2
3
# 删除数据库
DROP DATABASE bilibili;
DROP DATABASE IF EXIT bilibili;

修改数据库

1
2
# 修改数据库的字符集和排序规则
ALTER DATABASE bilibili CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

二、DML（Data Manipulation Language）：数据操作语言；

1. 查看所有表

1
2
3
4
# 查看所有的数据表
SHOW TABLES;
# 查看某一个表结构
DESC user;

2. 创建表

1
2
3
4
5
CREATE TABLE IF NOT EXISTS `users`(
  name VARCHAR(20),
  age INT,
  height DOUBLE
);

  • 完整示例

    1
    2
    3
    4
    5
    6
    7
    # 创建一张表
    CREATE TABLE IF NOT EXISTS `products`(
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(20) NOT NULL,
      age INT DEFAULT 0,
      telPhone VARCHAR(20) DEFAULT '' UNIQUE NOT NULL
    );

  • 带默认新增时间和编辑时间

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE IF NOT EXISTS `users`(
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(20) NOT NULL,
     age INT DEFAULT 0,
     createAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     updateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )

3.删除表

1
2
3
# 删除数据库
DROP TABLE users;
DROP TABLE IF EXISTS users;

4.修改表

1
2
3
4
5
6
7
8
9
10
11
# 1.修改表名
ALTER TABLE `moments` RENAME TO `moment`;
# 2.添加一个新的列
ALTER TABLE `moment` ADD `publishTime` DATETIME;
ALTER TABLE `moment` ADD `updateTime` DATETIME;
# 3.删除一列数据
ALTER TABLE `moment` DROP `updateTime`;
# 4.修改列的名称
ALTER TABLE `moment` CHANGE `publishTime` `publishDate` DATE;
# 5.修改列的数据类型
ALTER TABLE `moment` MODIFY `id` INT;

接下来是对表中的数据进行操作（增删改）

5.插入数据

先创建产品表

1
2
3
4
5
6
7
create table if not EXISTS `products`(
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(40) NOT NULL,
  description VARCHAR(100),
  price DOUBLE NOT NULL,
  publishTime DATE
);

💣错误插入方法：表中字段名不可用 ‘’ 包裹

1
INSERT INTO `products` ('title', 'description', 'price', 'publishTime') VALUES ('iPhone', 'iPhone只要999.88', 999.88, '2021-02-18')

正确写法

1
INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) VALUES ('iPhone', 'iPhone只要998', 999.88, '2020-10-10')

6.删除数据

1
2
3
4
# 会删除表中所有的数据
DELETE FROM `products`;
# 会删除符合条件的数据
DELETE FROM `products` WHERE `title` = 'iPhone';

7.修改数据

1
2
3
4
# 会修改表中所有的数据
UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88;
# 会修改符合条件的数据
UPDATE `products` SET `title` = 'iPhone12', `price` = 1299.88 WHERE `title` = 'iPhone';

三、DQL：Data Query Language（数据查询语言）

  • SELECT用于从一个或者多个表中检索选中的行（Record）。
    查询语句格式
    1
    2
    3
    4
    5
    6
    7
    SELECT select_expr [, select_expr]...
      [FROM table_references]
      [WHERE where_condition]
      [ORDER BY expr [ASC | DESC]]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [GROUP BY expr]
      [HAVING where_condition]

1.数据准备

建表

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `products` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  brand VARCHAR(20),
  title VARCHAR(100) NOT NULL,
  price DOUBLE NOT NULL,
  score DECIMAL(2,1),
  voteCnt INT,
  url VARCHAR(100),
  pid INT
);
  • 数据插入随自己需要即可，也顺便复习了前面的插入语句

2.基本查询

1
2
3
4
5
6
7
# 查询所有的数据并且显示所有的字段：
SELECT * FROM `products`;
# 查询title、brand、price：
SELECT title, brand, price FROM `products`;
# 我们也可以给字段起别名：
 # 别名一般在多张表或者给客户端返回对应的key时会使用到；
SELECT title as t, brand as b, price as p FROM `products`;

3.条件查询（WHERE）

a.基本条件查询
1
2
3
4
5
6
7
8
9
10
# 查询价格小于1000的手机
SELECT * FROM `products` WHERE price < 1000;
# 查询价格大于等于2000的手机
SELECT * FROM `products` WHERE price >= 2000;
# 价格等于3399的手机
SELECT * FROM `products` WHERE price = 3399;
# 价格不等于3399的手机
SELECT * FROM `products` WHERE price = 3399;
# 查询华为品牌的手机
SELECT * FROM `products` WHERE `brand` = '华为';
b.带逻辑运算符的条件查询
1
2
3
4
5
6
7
8
9
10
11
12
# 查询品牌是华为，并且小于2000元的手机
SELECT * FROM `products` WHERE `brand` = '华为' and `price` < 2000;
SELECT * FROM `products` WHERE `brand` = '华为' && `price` < 2000;
# 查询1000到2000的手机（不包含1000和2000）
SELECT * FROM `products` WHERE price > 1000 and price < 2000;
# OR: 符合一个条件即可
# 查询所有的华为手机或者价格小于1000的手机
SELECT * FROM `products` WHERE brand = '华为' or price < 1000;
# 查询1000到2000的手机（包含1000和2000）
SELECT * FROM `products` WHERE price BETWEEN 1000 and 2000;
# 查看多个结果中的一个
SELECT * FROM `products` WHERE brand in ('华为', '小米');
c.使用LIKE关键字的模糊查询
  • %表示匹配任意个的任意字符；
  • _表示匹配一个的任意字符；
    1
    2
    3
    4
    5
    6
    # 查询所有以v开头的title
    SELECT * FROM `products` WHERE title LIKE 'v%';
    # 查询带M的title
    SELECT * FROM `products` WHERE title LIKE '%M%';
    # 查询带M的title必须是第三个字符
    SELECT * FROM `products` WHERE title LIKE '__M%';

4.查询结果排序

ORDER BY 进行查询结果排序，两个关键词

  • ASC：升序排列；
  • DESC：降序排列；
    1
    SELECT * FROM `products` WHERE brand = '华为' or price < 1000 ORDER BY price ASC;

5.分页查询

1
2
3
4
5
SELECT * FROM `products` LIMIT 30 OFFSET 0;
SELECT * FROM `products` LIMIT 30 OFFSET 30;
SELECT * FROM `products` LIMIT 30 OFFSET 60;
# 另外一种写法：offset, row_count
SELECT * FROM `products` LIMIT 90, 30;

6.聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
# 华为手机价格的平均值
SELECT AVG(price) FROM `products` WHERE brand = '华为';
# 计算所有手机的平均分
SELECT AVG(score) FROM `products`;
# 手机中最低和最高分数
SELECT MAX(score) FROM `products`;
SELECT MIN(score) FROM `products`;
# 计算总投票人数
SELECT SUM(voteCnt) FROM `products`;
# 计算所有条目的数量
SELECT COUNT(*) FROM `products`;
# 华为手机的个数
SELECT COUNT(*) FROM `products` WHERE brand = '华为';

7.分组查询（GROUP BY）

a.基本分组查询
1
2
3
4
5
6
7
SELECT brand,
  COUNT(*) as count,
  ROUND(AVG(price),2) as avgPrice,
  MAX(price) as maxPrice,
  MIN(price) as minPrice,
  AVG(score) as avgScore
FROM `products` GROUP BY brand;
b.分组查询添加约束
  • HAVING 关键字
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT brand,
      COUNT(*) as count,
      ROUND(AVG(price),2) as avgPrice,
      MAX(price) as maxPrice,
      MIN(price) as minPrice,
      AVG(score) as avgScore
    FROM `products` GROUP BY brand
    HAVING avgPrice < 4000 and avgScore > 7;

多表关联和外键

创建关联表

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `brand`(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  website VARCHAR(100),
  worldRank INT
);

添加外键约束

1
2
3
4
# 如果是创建表添加外键约束，我们需要在创建表的()最后添加如下语句；
FOREIGN KEY (brand_id) REFERENCES brand(id)
# 如果是表已经创建好，额外添加外键：
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id);

更新或删除的特殊设置

  • 由于两个表相互关联，那么删除一个表的过程中势必会在另一个表中产生冲突
  • 所以需要在建立关联时明确告诉数据库应对冲突的方案
    我们需要修改on delete或者on update的值；
    我们可以给更新或者删除时设置几个值：
  • RESTRICT（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的，不允许更新或删除；
  • NO ACTION：和RESTRICT是一致的，是在SQL标准中定义的；
  • CASCADE：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：
    • 更新：那么会更新对应的记录；
    • 删除：那么关联的记录会被一起删除掉；
  • SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL；
    1
    2
    # 更新相关联的记录；不允许删除
    ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id) ON UPDATE CASCADE ON DELETE RESTRICT;