开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 带你学懂MySql数据库 -> 正文阅读

[大数据]带你学懂MySql数据库

学懂MySql数据库

作者：贤大蛋
目标：前面学完node基础和express框架，学习完数据库就可以造接口啦！
之前写的那篇太久很多操作忘了，现在重新学习顺便记录一下

概念：用于存储和管理数据的仓库。
特点：
1. 持久化存储数据的。其实数据库就是一个文件系统
2. 方便存储和管理数据
3. 使用了统一的方式操作数据库 – SQL

SQL语句

什么是SQL？
- Structured Query Language：结构化查询语言
- 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。
- 事实上，常见的关系型数据库SQL语句都是比较相似的，所以你学会了MySQL中的SQL语句，之后去操作比如 Oracle或者其他关系型数据库，也是非常方便的
SQL通用语法
- SQL 语句可以单行或多行书写，以分号结尾。
- 可使用空格和缩进来增强语句的可读性。
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
- 3 种注释

单行注释: – 注释内容或
#注释内容(mysql 特有)
多行注释: /* 注释 */

SQL分类
- DDL(Data Definition Language)数据定义语言：用来定义数据库对象：数据库，表，列等。
  
  关键字：create, drop,alter 等
- DML(Data Manipulation Language)数据操作语言：用来对数据库中表的数据进行增删改。
  
  关键字：insert, delete, update等
- DQL(Data Query Language)数据查询语言**（重点）**：用来查询数据库中表的记录(数据)。
  
  关键字：select, where等
- DCL(Data Control Language)数据控制语言(了解)
  用来定义数据库的访问权限和安全级别，及创建用户。
  
  关键字：GRANT， REVOKE 等

数据库的操作

查看当前的数据库

#查看所有的数据
SHOW DATABASES;
#使用某一个数据
USE ELVAHUB;
# 查看当前正在使用的数据库
SELECT DATABASE();

创建新的数据库

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

删除数据库

# 删除数据库
DROP DATABASE student；
DROP DATABASE IF EXIT  student;

修改数据库

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

数据表的操作

查看数据表

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

创建数据表

#加了反引号是为了不然SQL语句将数据表当成关键字显示
CREATE TABLE IF NOT EXISTS `user`(
name VARCHAR(20),
age INT,
height DOUBLE
);

Sql的数据类型-数字类型

? 我们知道不同的数据会划分为不同的数据类型，在数据库中也是一样：

MySQL支持的数据类型有：数字类型，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型。
数字类型：
MySQL的数字类型有很多： p 整数数字类型：INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT；
浮点数字类型：FLOAT，DOUBLE（FLOAT是4个字节，DOUBLE是8个字节）；
精确数字类型：DECIMAL，NUMERIC（DECIMAL是NUMERIC的实现形式）。
日期类型：
- 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’

Sql的数据类型-字符串类型

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

CHAR类型在创建表时为固定长度，长度可以是0到255之间的任何值；在被查询时，会删除后面的空格；
VARCHAR类型的值是可变长度的字符串，长度可以指定为0到65535之间的值；在被查询时，不会删除后面的空格；
BINARY和VARBINARY 类型用于存储二进制字符串，存储的是字节字符串； https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html
BLOB用于存储大的二进制类型；
TEXT用于存储大的字符串类型。

表约束

1. 主键：PRIMARY KEY

一张表中，我们为了区分每一条记录的唯一性，必须有一个字段是永远不会重复，并且不会为空的，这个字段我们通常会将它设置为主键：

主键是表中唯一的索引；
并且必须是NOT NULL的，如果没有设置 NOT NULL，那么MySQL也会隐式的设置为NOT NULL；
主键也可以是多列索引，PRIMARY KEY(key_part, …)，我们一般称之为联合主键；

建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键；

2. 唯一：UNIQUE

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

使用UNIQUE约束的字段在表中必须是不同的；
对于所有引擎，UNIQUE 索引允许NULL包含的列具有多个值NULL。

3. 不能为空：NOT NULL

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

**4. 默认值：DEFAULT **

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

5. 自动递增：AUTO-INCREMENT

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

DDL代码实践时间

创建一个完整的数据表

#完整的创建表语法
CREATE TABLE IF NOT EXISTS `users`(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 0,
telPhone VARCHAR(20) DEFAULT '' NOT NULL
);

修改表

# 1. 修改表名
ALTER TABLE `users` RENAME TO `user`;
# 2. 添加一个新的列
ALTER TABLE `user` ADD `updateTime` TIMESTAMP;
# 3. 修改字段的名称
ALTER TABLE `user` CHANGE `telPhone` `phoneNum` VARCHAR(30);
# 4. 修改字段的类型
ALTER TABLE `user` MODIFY `name` VARCHAR(30);
#5.删除某一个字段
ALTER TABLE `user` DROP `age`;

DML代码实践时间

创建一张新的表

CREATE TABLE IF NOT EXISTS `products`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`title` VARCHAR(20),
`description` VARCHAR(200),
`price` DOUBLE,
`publishTime` DATETIME
);

插入数据

#插入数据
INSERT INTO products(title,description,price,publishTime)
VALUES('iPhone','iPhone只要998',988.88,'2022-5-17');
INSERT INTO `products` (`title`, `description`, `price`, `publishTime`) 
VALUES ('huawei', 'P40只要888', 888.88, '2022-5-11'

删除数据

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

修改数据

# 修改数据
# 修改表中所有数据
UPDATE `products` SET `title` = 'iPhone15',`price`=1299.88;
# 修改符合条件的数据
UPDATE `products` SET `title` = 'iPhone12',`price`=1299.99 WHERE `title`='huawei';

补充

# 我们希望修改之后可以直接显示最新的更新时间
ALTER TABLE `products` ADD `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

DQL代码实践时间

创建products表，准备数据

#创建表结构
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
);

完成如下表结构创建

注意：这里之后需要自己插入数据

基本查询

# 1. 基本查询
SELECT *FROM `products`;
# 2. 查询指定的字段
SELECT title,price FROM `products`;
# 3. 对字段起一个别名
SELECT title as phoneTitle,price as currentPrice FROM `products`;

在开发中，我们希望根据条件来筛选我们的数据，这个时候我们要使用条件查询。

where 的比较运算符

# where 比较运算符
# 条件判断语句
# 案例：查询价格小于1000的手机
SELECT title, price FROM `products` WHERE price < 1000;
# 案例二：价格等于999的手机
SELECT * FROM `products` WHERE price = 999;
# 案例三：价格不等于999的手机
SELECT * FROM `products` WHERE price != 999;
SELECT * FROM `products` WHERE price <> 999;
# 案例四：查询品牌是华为的手机
SELECT * FROM `products` WHERE brand = '华为';

where的逻辑运算符

# where 逻辑语句
# 案例一：查询1000到2000之间的手机
SELECT * FROM `products` WHERE price > 1000 AND price < 2000;
SELECT * FROM `products` WHERE price > 1000 && price < 2000;
# BETWEEN AND 包含等于
SELECT * FROM `products` WHERE price BETWEEN 1099 AND 2000;
# 案例二：价格在5000以上或者是品牌是华为的手机
SELECT * FROM `products` WHERE price > 5000 || brand = '华为';

模糊查询：询使用LIKE关键字，结合两个特殊的符号

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

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

# 模糊查询
SELECT *FROM `products` WHERE title LIKE '%M%';
SELECT * FROM `products` WHERE title LIKE '%P%';
SELECT * FROM `products` WHERE title LIKE '_P%';

对查询结果进行排序

我们希望讲结果按照某种方式进行排序，这个时候使用的是ORDER BY，ORDER BY有两个常用的值：

ASC：升序排列
DESC：降序排列

# 对结果进行排序
SELECT *FROM `products` WHERE brand = '华为'||brand = '小米';
SELECT *FROM `products` WHERE brand = '华为'||brand = '小米' ORDER BY price ASC;

分页查询

当数据库中的数据非常多时，一次性查询到所有的结果进行显示是不太现实的：

在真实开发中，我们都会要求用户传入offset、limit或者page等字段；
它们的目的是让我们可以在数据库中进行分页查询；
它的用法有[LIMIT {[offset,] row_count | row_count OFFSET offset}]

# 分页查询
SELECT *FROM `products` LIMIT 20 OFFSET 0;
SELECT * FROM `products` LIMIT 30 OFFSET 30;
# 另外一种写法：offset, row_count
SELECT * FROM `products` LIMIT 90, 30;

聚合函数

聚合函数表示对值集合进行操作的组（集合）函数

# 聚合函数的使用
# 求所有手机的价格的总和
SELECT SUM(price) totalPrice FROM `products`;
# 求一下华为手机的价格的总和
SELECT SUM(price) FROM `products` WHERE brand = '华为';
# 求华为手机的平均价格
SELECT AVG(price) FROM `products` WHERE brand = '华为';
# 最高手机价格和最低手机价格
SELECT MAX(price) FROM `products`;
SELECT MIN(price) FROM `products`;
# 求华为手机的个数
SELECT COUNT(*) FROM `products` WHERE brand = '华为';
SELECT COUNT(*) FROM `products` WHERE brand = '苹果';
SELECT COUNT(url) FROM `products` WHERE brand = '苹果';
SELECT COUNT(price) FROM `products`;
SELECT COUNT(DISTINCT price) FROM `products`;

认识Group By

事实上聚合函数相当于默认将所有的数据分成了一组，如果我们希望划分多组，这个时候就需要使用Group By。GROUP BY通常和聚合函数一起使用：表示我们先对数据进行分组，再对每一组数据，进行聚合函数的计算

#GROUP BY
SELECT brand, AVG(price), COUNT(*), AVG(score) FROM `products` GROUP BY brand;

**注意：**这里的brand不是随便加的，根据brand分类才能根据brand显示出来

补充：HAVING 的使用

# 在产品表中根据品牌分类，计算商品的个数，平均价格（重命名为avgPrice），平均测评分并且筛选出平均价格大于2000的商品
SELECT brand, AVG(price) avgPrice, COUNT(*), AVG(score) FROM `products` GROUP BY brand HAVING avgPrice > 2000;