Node.js数据库交互：MySQL实用指南

1、配置数据库 MySQL

1.1.1 MySQL及其安装

PhpStudy下载安装使用教程，图文教程

Database Client VsCode连接Mysql

1.1.2基本命令

所有的语句都要以分号结尾

show databases; --查看当前所有的数据库
use 数据库名; --打开指定的数据库
show tables; --查看所有的表
describe/desc 表名; --显示表的信息
create database 数据库名; --创建一个数据库
exit --退出连接

1.2.1操作数据库

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;

2.删除数据库

DROP DATABASE [if EXISTS] 数据库名;

3.使用数据库

--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;

4.查看数据库

SHOW DATABASES;

1.2.2数据库的列类型

1.数值

数据类型	描述	大小
tiyint	十分小的数据	1字节
smallint	较小的数据	2字节
mediumint	中等大小的数据	3字节
int	标准的数据	4字节
bigint	较大的数据	8字节
float	浮点数	4字节
double	浮点数	8字节
decimal	字符串形式的浮点数

2.字符串

数据类型	描述	大小
char	字符串固定大小	0-255
varchar	可变字符串	0-65535
tinytext	卫星文本	2阶乘8-1
text	文本串	2阶乘16-1

3.时间日期

数据类型	描述	格式
date	日期格式	YYYY-MM-DD
time	时间格式	HH:mm:ss
datetime	最常用的时间格式	YYYY-MM-DD HH:mm:ss
timestamp	时间戳
year	年份表示

4.null

• 没有值，未知
• 不要使用NULL值进行计算

1.2.3创建数据库表

-- IF NOT EXISTS 如果不存在
-- NOT NULL 非空
-- AUTO_INCREMENT 自增 通常设置给id
-- DEFAULT 默认值
-- COMMENT 解释
-- PRIMARY KEY 主键 搭配自增使用
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)

注意点：

• 表名和字段尽量使用``括起来
• AUTO_INCREMENT 代表自增
• 所有的语句后面加逗号，最后一个不加
• 字符串使用单引号括起来
• 主键的声明一般放在最后，便于查看

常用命令

SHOW CREATE DATABASE 数据库名;-- 查看创建数据库的语句
SHOW CREATE TABLE 表名;-- 查看表的定义语句
DESC 表名;-- 显示表的具体结构

1.2.4修改数据库

1.修改

修改表名 :

• ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 :

• ALTER TABLE 表名 ADD字段名 列属性[属性]

修改字段 :

• ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
• ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段：

• ALTER TABLE 表名 DROP 字段名

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);
-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名
-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;

2.删除

语法：DROP TABLE [IF EXISTS] 表名

• IF EXISTS为可选 , 判断是否存在该数据表
• 如删除不存在的数据表会抛出错误

-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS teachers;

2、DML语言（增删改查）

2.1添加（insert）

INSERT INTO `student`(`name`) VALUES ('zsr');
-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),
('gcc','000421','女');
-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武
汉','1412@qq.com',1);

语法：

INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

注意：

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是值必须完整且一一对应
3. 可以同时插入多条数据，VALUES后面的值需要使用逗号隔开

2.2修改（update）

-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;
-- 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';
-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;
-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';

语法：

UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

关于WHERE条件语句：

操作符	含义
=	等于
<>或！=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
between…and…	闭合区间
and	和
or	或

2.3删除（delete）

-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` WHERE id=1;

语法：

DELETE FROM 表名 [WHERE 条件]

关于 DELETE 删除的问题，重启数据库现象：

• INNODB 自增列会从1开始（存在内存当中，断电即失）
• MYISAM 继续从上一个子增量开始（存在内存当中，不会丢失）

TRUNCATE

作用：完全清空一个数据库表，表的结构和索引约束不会变！

2.4查询数据（select）

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条
至哪条

• 查询数据库数据 , 如SELECT语句
• 简单的单表查询或多表的复杂查询和嵌套查询
• 是数据库语言中最核心,最重要的语句
• 使用频率最高的语句

2.4.1基础查询

语法：

SELECT 查询列表 FROM 表名;

• 查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数
• 查询结果是一个虚拟的表格

-- 查询全部学生
SELECT * FROM student;
-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;
-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;
-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;
-- 查询系统版本(函数)
SELECT VERSION();
-- 用来计算(计算表达式)
SELECT 100*53-90 AS 计算结果;
-- 查询自增步长(变量)
SELECT @@auto_increment_increment;
-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` FROM result;

2.4.2条件查询

where 条件字句：检索数据中 符合条件 的值

语法：

select 查询列表 from 表名 where 筛选条件;

-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;
-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;
-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';
-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';
-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);

2.4.3分组查询

语法：

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

区别：

	使用关键字	筛选的表	位置
分组前筛选	where	原始表	group by的前面
分组后筛选	having	分组后的结果	group by的后面

-- 查询不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根据不同的课程进行分组
SELECT
SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;

2.4.4排序和分页

1.排序

语法：

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc

order by的位置一般放在查询语句的最后

asc	升序，如果不写默认升序
desc	降序

SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;

2.分页

语法：

select 查询列表
from 表
limit offset,pagesize;
-- 第一页 limit 0 5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页 limit (n-1)*pagesize,pagesize
-- pagesize:当前页面大小
-- (n-1)*pagesize:起始值
-- n:当前页面
-- 数据总数/页面大小=总页面数
-- limit n 表示从0到n的页面

• offset代表的是起始的条目索引，默认从0开始
• size代表的是显示的条目数
• offset=(n-1)*pagesize

2.4.5MYSQL函数

1.常用函数

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1
-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置
开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串
-- 时间日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT now(); -- 获取当前时间
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE(); -- 系统时间
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
-- 系统信息
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

2.聚合函数

函数	描述
max	最大值
min	最小值
sum	和
avg	平均值
count	计算个数

SELECT COUNT(StudentName) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;

总结：本文详细介绍了MySQL数据库的配置、基本命令、数据类型、表的创建与修改、DML语言（增删改查）以及常用函数的应用，为数据库管理和操作提供了全面的指导