写给前端的MySQL数据库入门（一）

前言

为什么需要数据库？

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

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

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

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

常见的数据库有哪些？

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

一张图看懂二者区别：

写给前端的MySQL数据库入门（一）

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

MySQL初体验

基本概念

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

写给前端的MySQL数据库入门（一）

安装

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

连接

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

写给前端的MySQL数据库入门（一）

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';

字符串类型

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

CHAR类型在创建表时为固定长度，长度可以是0到255之间的任何值；
- 在被查询时，会删除后面的空格；
VARCHAR类型的值是可变长度的字符串，长度可以指定为0到65535之间的值；
- 在被查询时，不会删除后面的空格；
BINARY和VARBINARY 类型用于存储二进制字符串，存储的是字节字符串；
- dev.mysql.com/doc/refman/…
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语句对数据库或者表进行：创建、删除、修改等操作；

查看并切换当前数据库

# 查看当前MySQL服务下的所有数据库：
SHOW DATABASES;

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

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

创建新的数据库

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

删除数据库

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

修改数据库

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

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

1. 查看所有表

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

2. 创建表

CREATE TABLE IF NOT EXISTS `users`(
  name VARCHAR(20),
  age INT,
  height DOUBLE
);

完整示例

# 创建一张表
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
);

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

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.删除表

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

4.修改表

# 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.插入数据

先创建产品表

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
);

?错误插入方法：表中字段名不可用 '' 包裹

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

正确写法

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

6.删除数据

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

7.修改数据

# 会修改表中所有的数据
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）。

查询语句格式

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.数据准备

建表

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.基本查询

# 查询所有的数据并且显示所有的字段：
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.基本条件查询

# 查询价格小于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.带逻辑运算符的条件查询

# 查询品牌是华为，并且小于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关键字的模糊查询

%表示匹配任意个的任意字符；
_表示匹配一个的任意字符；

# 查询所有以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：降序排列；

SELECT * FROM `products` WHERE brand = '华为' or price < 1000 ORDER BY price ASC;

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.聚合函数

# 华为手机价格的平均值
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.基本分组查询

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 关键字

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;

多表关联和外键

创建关联表

CREATE TABLE IF NOT EXISTS `brand`(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  website VARCHAR(100),
  worldRank INT
);

添加外键约束

# 如果是创建表添加外键约束，我们需要在创建表的()最后添加如下语句；
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；

# 更新相关联的记录；不允许删除
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id) ON UPDATE CASCADE ON DELETE RESTRICT;

前言

为什么需要数据库？

常见的数据库有哪些？

MySQL初体验

基本概念

安装

连接

MySQL 中的数据类型

数字类型

日期类型

字符串类型

MySQL中的表约束

MySQL常用命令

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

查看并切换当前数据库

创建新的数据库

删除数据库

修改数据库

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

1. 查看所有表

2. 创建表

3.删除表

4.修改表

5.插入数据

6.删除数据

7.修改数据

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

1.数据准备

2.基本查询

3.条件查询（WHERE）

a.基本条件查询

b.带逻辑运算符的条件查询

c.使用LIKE关键字的模糊查询

4.查询结果排序

5.分页查询

6.聚合函数

7.分组查询（GROUP BY）

a.基本分组查询

b.分组查询添加约束

多表关联和外键

常见问题FAQ

掘金(Orime小猪) SVIP

相关推荐

发表评论

如需帝国cms功能定制以及二次开发请联系我们

升级SVIP会员

工作时间

请选择支付方式